1 package C4::Accounts2; #assumes C4/Accounts2
4 # Copyright 2000-2002 Katipo Communications
6 # This file is part of Koha.
8 # Koha is free software; you can redistribute it and/or modify it under the
9 # terms of the GNU General Public License as published by the Free Software
10 # Foundation; either version 2 of the License, or (at your option) any later
13 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
14 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
15 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License along with
18 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
19 # Suite 330, Boston, MA 02111-1307 USA
28 use C4::Circulation::Circ2;
29 use vars qw($VERSION @ISA @EXPORT);
31 # set the version for version checking
32 $VERSION = do { my @v = '$Revision$' =~ /\d+/g;
36 C4::Accounts - Functions for dealing with Koha accounts
44 The functions in this module deal with the monetary aspect of Koha,
45 including looking up and modifying the amount of money owed by a
55 @EXPORT = qw(&checkaccount &recordpayment &fixaccounts &makepayment &manualinvoice
56 &getnextacctno &reconcileaccount);
60 $owed = &checkaccount($env, $borrowernumber, $dbh, $date);
62 Looks up the total amount of money owed by a borrower (fines, etc.).
64 C<$borrowernumber> specifies the borrower to look up.
66 C<$dbh> is a DBI::db handle for the Koha database.
74 #check accounts and list amounts owing
75 my ($env,$bornumber,$dbh,$date)=@_;
76 my $select="SELECT SUM(amountoutstanding) AS total
78 WHERE borrowernumber = ?
79 AND amountoutstanding<>0";
80 my @bind = ($bornumber);
81 if ($date && $date ne ''){
82 $select.=" AND date < ?";
86 my $sth=$dbh->prepare($select);
88 my $data=$sth->fetchrow_hashref;
89 my $total = $data->{'total'} || 0;
91 # output(1,2,"borrower owes $total");
93 # # output(1,2,"borrower owes $total");
95 # reconcileaccount($env,$dbh,$bornumber,$total);
104 &recordpayment($env, $borrowernumber, $payment);
106 Record payment by a patron. C<$borrowernumber> is the patron's
107 borrower number. C<$payment> is a floating-point number, giving the
108 amount that was paid. C<$env> is a reference-to-hash;
109 C<$env-E<gt>{branchcode}> is the code of the branch where payment was
112 Amounts owed are paid off oldest first. That is, if the patron has a
113 $1 fine from Feb. 1, another $1 fine from Mar. 1, and makes a payment
114 of $1.50, then the oldest fine will be paid off in full, and $0.50
115 will be credited to the next one.
120 #here we update both the accountoffsets and the account lines
121 my ($env,$bornumber,$data)=@_;
122 warn "in accounts2.pm";
123 my $dbh = C4::Context->dbh;
126 my $branch=$env->{'branchcode'};
128 my $amountleft = $data;
130 my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
131 # get lines with outstanding amounts to offset
132 my $sth = $dbh->prepare("select * from accountlines
133 where (borrowernumber = ?) and (amountoutstanding<>0)
135 $sth->execute($bornumber);
136 # offset transactions
137 while (($accdata=$sth->fetchrow_hashref) and ($amountleft>0)){
138 if ($accdata->{'amountoutstanding'} < $amountleft) {
140 $amountleft -= $accdata->{'amountoutstanding'};
142 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
145 my $thisacct = $accdata->{accountno};
146 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
147 where (borrowernumber = ?) and (accountno=?)");
148 $usth->execute($newamtos,$bornumber,$thisacct);
150 $usth = $dbh->prepare("insert into accountoffsets
151 (borrowernumber, accountno, offsetaccount, offsetamount)
153 $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
157 my $usth = $dbh->prepare("insert into accountlines
158 (borrowernumber, accountno,date,amount,description,accounttype,amountoutstanding)
159 values (?,?,now(),?,'Payment,thanks','Pay',?)");
160 $usth->execute($bornumber,$nextaccntno,0-$data,0-$amountleft);
162 UpdateStats($env,$branch,'payment',$data,'','','',$bornumber);
168 &makepayment($borrowernumber, $acctnumber, $amount, $branchcode);
170 Records the fact that a patron has paid off the entire amount he or
173 C<$borrowernumber> is the patron's borrower number. C<$acctnumber> is
174 the account that was credited. C<$amount> is the amount paid (this is
175 only used to record the payment. It is assumed to be equal to the
176 amount owed). C<$branchcode> is the code of the branch where payment
181 # FIXME - I'm not at all sure about the above, because I don't
182 # understand what the acct* tables in the Koha database are for.
184 #here we update both the accountoffsets and the account lines
185 #updated to check, if they are paying off a lost item, we return the item
186 # from their card, and put a note on the item record
187 my ($bornumber,$accountno,$amount,$user,$branch)=@_;
189 $env{'branchcode'}=$branch;
190 my $dbh = C4::Context->dbh;
192 my $nextaccntno = getnextacctno(\%env,$bornumber,$dbh);
194 my $sth=$dbh->prepare("Select * from accountlines where borrowernumber=? and accountno=?");
195 $sth->execute($bornumber,$accountno);
196 my $data=$sth->fetchrow_hashref;
201 SET amountoutstanding = 0
202 WHERE borrowernumber = $bornumber
203 AND accountno = $accountno
208 INSERT INTO accountoffsets
209 (borrowernumber, accountno, offsetaccount,
211 VALUES ($bornumber, $accountno, $nextaccntno, $newamtos)
215 my $payment=0-$amount;
217 INSERT INTO accountlines
218 (borrowernumber, accountno, date, amount,
219 description, accounttype, amountoutstanding)
220 VALUES ($bornumber, $nextaccntno, now(), $payment,
221 'Payment,thanks - $user', 'Pay', 0)
224 # FIXME - The second argument to &UpdateStats is supposed to be the
226 # UpdateStats is now being passed $accountno too. MTJ
227 UpdateStats(\%env,$user,'payment',$amount,'','','',$bornumber,$accountno);
229 #check to see what accounttype
230 if ($data->{'accounttype'} eq 'Rep' || $data->{'accounttype'} eq 'L'){
231 returnlost($bornumber,$data->{'itemnumber'});
237 $nextacct = &getnextacctno($env, $borrowernumber, $dbh);
239 Returns the next unused account number for the patron with the given
242 C<$dbh> is a DBI::db handle to the Koha database.
248 # FIXME - Okay, so what does the above actually _mean_?
250 my ($env,$bornumber,$dbh)=@_;
252 my $sth = $dbh->prepare("select * from accountlines
253 where (borrowernumber = ?)
254 order by accountno desc");
255 $sth->execute($bornumber);
256 if (my $accdata=$sth->fetchrow_hashref){
257 $nextaccntno = $accdata->{'accountno'} + 1;
260 return($nextaccntno);
265 &fixaccounts($borrowernumber, $accountnumber, $amount);
269 # FIXME - I don't understand what this function does.
271 my ($borrowernumber,$accountno,$amount)=@_;
272 my $dbh = C4::Context->dbh;
273 my $sth=$dbh->prepare("Select * from accountlines where borrowernumber=?
275 $sth->execute($borrowernumber,$accountno);
276 my $data=$sth->fetchrow_hashref;
277 # FIXME - Error-checking
278 my $diff=$amount-$data->{'amount'};
279 my $outstanding=$data->{'amountoutstanding'}+$diff;
284 SET amount = '$amount',
285 amountoutstanding = '$outstanding'
286 WHERE borrowernumber = $borrowernumber
287 AND accountno = $accountno
291 # FIXME - Never used, but not exported, either.
293 my ($borrnum,$itemnum)=@_;
294 my $dbh = C4::Context->dbh;
295 my $borrower=borrdata('',$borrnum);
296 my $sth=$dbh->prepare("Update issues set returndate=now() where
297 borrowernumber=? and itemnumber=? and returndate is null");
298 $sth->execute($borrnum,$itemnum);
300 my @datearr = localtime(time);
301 my $date = (1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
302 my $bor="$borrower->{'firstname'} $borrower->{'surname'} $borrower->{'cardnumber'}";
303 $sth=$dbh->prepare("Update items set paidfor=? where itemnumber=?");
304 $sth->execute("Paid for by $bor $date",$itemnum);
310 &manualinvoice($borrowernumber, $itemnumber, $description, $type,
313 C<$borrowernumber> is the patron's borrower number.
314 C<$description> is a description of the transaction.
315 C<$type> may be one of C<CS>, C<CB>, C<CW>, C<CF>, C<CL>, C<N>, C<L>,
317 C<$itemnumber> is the item involved, if pertinent; otherwise, it
318 should be the empty string.
322 # FIXME - Okay, so what does this function do, really?
324 my ($bornum,$itemnum,$desc,$type,$amount,$user)=@_;
325 my $dbh = C4::Context->dbh;
329 my $accountno=getnextacctno('',$bornum,$dbh);
330 my $amountleft=$amount;
332 if ($type eq 'CS' || $type eq 'CB' || $type eq 'CW'
333 || $type eq 'CF' || $type eq 'CL'){
334 my $amount2=$amount*-1; # FIXME - $amount2 = -$amount
335 $amountleft=fixcredit(\%env,$bornum,$amount2,$itemnum,$type,$user);
340 if ($type eq 'L' && $desc eq ''){
344 $amountleft=refund('',$bornum,$amount);
348 my $sth=$dbh->prepare("INSERT INTO accountlines
349 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding, itemnumber)
350 VALUES (?, ?, now(), ?,?, ?,?,?)");
351 # $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft, $data->{'itemnumber'});
352 $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft, $itemnum);
354 my $sth=$dbh->prepare("INSERT INTO accountlines
355 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding)
356 VALUES (?, ?, now(), ?, ?, ?, ?)");
357 $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft);
362 # $amountleft = &fixcredit($env, $bornumber, $data, $barcode, $type, $user);
364 # This function is only used internally.
365 # FIXME - Figure out what this function does, and write it down.
367 #here we update both the accountoffsets and the account lines
368 my ($env,$bornumber,$data,$barcode,$type,$user)=@_;
369 my $dbh = C4::Context->dbh;
372 my $amountleft = $data;
374 my $item=getiteminformation($env,'',$barcode);
375 my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
376 my $query="Select * from accountlines where (borrowernumber=?
377 and itemnumber=? and amountoutstanding > 0)";
379 $query.=" and (accounttype = 'L' or accounttype = 'Rep')";
380 } elsif ($type eq 'CF'){
381 $query.=" and (accounttype = 'F' or accounttype = 'FU' or
382 accounttype='Res' or accounttype='Rent')";
383 } elsif ($type eq 'CB'){
384 $query.=" and accounttype='A'";
387 my $sth=$dbh->prepare($query);
388 $sth->execute($bornumber,$item->{'itemnumber'});
389 $accdata=$sth->fetchrow_hashref;
391 if ($accdata->{'amountoutstanding'} < $amountleft) {
393 $amountleft -= $accdata->{'amountoutstanding'};
395 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
398 my $thisacct = $accdata->{accountno};
399 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
400 where (borrowernumber = ?) and (accountno=?)");
401 $usth->execute($newamtos,$bornumber,$thisacct);
403 $usth = $dbh->prepare("insert into accountoffsets
404 (borrowernumber, accountno, offsetaccount, offsetamount)
406 $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
410 my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
411 # get lines with outstanding amounts to offset
412 my $sth = $dbh->prepare("select * from accountlines
413 where (borrowernumber = ?) and (amountoutstanding >0)
415 $sth->execute($bornumber);
417 # offset transactions
418 while (($accdata=$sth->fetchrow_hashref) and ($amountleft>0)){
419 if ($accdata->{'amountoutstanding'} < $amountleft) {
421 $amountleft -= $accdata->{'amountoutstanding'};
423 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
426 my $thisacct = $accdata->{accountno};
427 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
428 where (borrowernumber = ?) and (accountno=?)");
429 $usth->execute($newamtos,$bornumber,$thisacct);
431 $usth = $dbh->prepare("insert into accountoffsets
432 (borrowernumber, accountno, offsetaccount, offsetamount)
434 $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
438 $env->{'branch'}=$user;
439 $type="Credit ".$type;
440 UpdateStats($env,$user,$type,$data,$user,'','',$bornumber);
446 # FIXME - Figure out what this function does, and write it down.
448 #here we update both the accountoffsets and the account lines
449 my ($env,$bornumber,$data)=@_;
450 my $dbh = C4::Context->dbh;
453 # my $branch=$env->{'branchcode'};
454 my $amountleft = $data *-1;
457 my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
458 # get lines with outstanding amounts to offset
459 my $sth = $dbh->prepare("select * from accountlines
460 where (borrowernumber = ?) and (amountoutstanding<0)
462 $sth->execute($bornumber);
464 # offset transactions
465 while (($accdata=$sth->fetchrow_hashref) and ($amountleft<0)){
466 if ($accdata->{'amountoutstanding'} > $amountleft) {
468 $amountleft -= $accdata->{'amountoutstanding'};
470 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
474 my $thisacct = $accdata->{accountno};
475 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
476 where (borrowernumber = ?) and (accountno=?)");
477 $usth->execute($newamtos,$bornumber,$thisacct);
479 $usth = $dbh->prepare("insert into accountoffsets
480 (borrowernumber, accountno, offsetaccount, offsetamount)
482 $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
490 END { } # module clean-up code here (global destructor)