3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along with
17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
18 # Suite 330, Boston, MA 02111-1307 USA
26 use C4::Circulation qw(MarkIssueReturned);
28 use vars qw($VERSION @ISA @EXPORT);
31 # set the version for version checking
36 &recordpayment &makepayment &manualinvoice
37 &getnextacctno &reconcileaccount &getcharges &getcredits
38 &getrefunds &chargelostitem
39 ); # removed &fixaccounts
44 C4::Accounts - Functions for dealing with Koha accounts
52 The functions in this module deal with the monetary aspect of Koha,
53 including looking up and modifying the amount of money owed by a
60 &recordpayment($borrowernumber, $payment);
62 Record payment by a patron. C<$borrowernumber> is the patron's
63 borrower number. C<$payment> is a floating-point number, giving the
66 Amounts owed are paid off oldest first. That is, if the patron has a
67 $1 fine from Feb. 1, another $1 fine from Mar. 1, and makes a payment
68 of $1.50, then the oldest fine will be paid off in full, and $0.50
69 will be credited to the next one.
76 #here we update the account lines
77 my ( $borrowernumber, $data ) = @_;
78 my $dbh = C4::Context->dbh;
81 my $branch = C4::Context->userenv->{'branch'};
82 my $amountleft = $data;
85 my $nextaccntno = getnextacctno($borrowernumber);
87 # get lines with outstanding amounts to offset
88 my $sth = $dbh->prepare(
89 "SELECT * FROM accountlines
90 WHERE (borrowernumber = ?) AND (amountoutstanding<>0)
93 $sth->execute($borrowernumber);
96 while ( ( $accdata = $sth->fetchrow_hashref ) and ( $amountleft > 0 ) ) {
97 if ( $accdata->{'amountoutstanding'} < $amountleft ) {
99 $amountleft -= $accdata->{'amountoutstanding'};
102 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
105 my $thisacct = $accdata->{accountno};
106 my $usth = $dbh->prepare(
107 "UPDATE accountlines SET amountoutstanding= ?
108 WHERE (borrowernumber = ?) AND (accountno=?)"
110 $usth->execute( $newamtos, $borrowernumber, $thisacct );
112 # $usth = $dbh->prepare(
113 # "INSERT INTO accountoffsets
114 # (borrowernumber, accountno, offsetaccount, offsetamount)
117 # $usth->execute( $borrowernumber, $accdata->{'accountno'},
118 # $nextaccntno, $newamtos );
123 my $usth = $dbh->prepare(
124 "INSERT INTO accountlines
125 (borrowernumber, accountno,date,amount,description,accounttype,amountoutstanding)
126 VALUES (?,?,now(),?,'Payment,thanks','Pay',?)"
128 $usth->execute( $borrowernumber, $nextaccntno, 0 - $data, 0 - $amountleft );
130 UpdateStats( $branch, 'payment', $data, '', '', '', $borrowernumber, $nextaccntno );
136 &makepayment($borrowernumber, $acctnumber, $amount, $branchcode);
138 Records the fact that a patron has paid off the entire amount he or
141 C<$borrowernumber> is the patron's borrower number. C<$acctnumber> is
142 the account that was credited. C<$amount> is the amount paid (this is
143 only used to record the payment. It is assumed to be equal to the
144 amount owed). C<$branchcode> is the code of the branch where payment
150 # FIXME - I'm not at all sure about the above, because I don't
151 # understand what the acct* tables in the Koha database are for.
154 #here we update both the accountoffsets and the account lines
155 #updated to check, if they are paying off a lost item, we return the item
156 # from their card, and put a note on the item record
157 my ( $borrowernumber, $accountno, $amount, $user, $branch ) = @_;
158 my $dbh = C4::Context->dbh;
161 my $nextaccntno = getnextacctno($borrowernumber);
165 "SELECT * FROM accountlines WHERE borrowernumber=? AND accountno=?");
166 $sth->execute( $borrowernumber, $accountno );
167 my $data = $sth->fetchrow_hashref;
172 SET amountoutstanding = 0
173 WHERE borrowernumber = $borrowernumber
174 AND accountno = $accountno
180 # INSERT INTO accountoffsets
181 # (borrowernumber, accountno, offsetaccount,
183 # VALUES ($borrowernumber, $accountno, $nextaccntno, $newamtos)
187 my $payment = 0 - $amount;
189 INSERT INTO accountlines
190 (borrowernumber, accountno, date, amount,
191 description, accounttype, amountoutstanding)
192 VALUES ($borrowernumber, $nextaccntno, now(), $payment,
193 'Payment,thanks - $user', 'Pay', 0)
196 # FIXME - The second argument to &UpdateStats is supposed to be the
198 # UpdateStats is now being passed $accountno too. MTJ
199 UpdateStats( $user, 'payment', $amount, '', '', '', $borrowernumber,
203 #check to see what accounttype
204 if ( $data->{'accounttype'} eq 'Rep' || $data->{'accounttype'} eq 'L' ) {
205 returnlost( $borrowernumber, $data->{'itemnumber'} );
211 $nextacct = &getnextacctno($borrowernumber);
213 Returns the next unused account number for the patron with the given
219 # FIXME - Okay, so what does the above actually _mean_?
220 sub getnextacctno ($) {
221 my ($borrowernumber) = shift or return undef;
222 my $sth = C4::Context->dbh->prepare(
223 "SELECT accountno+1 FROM accountlines
224 WHERE (borrowernumber = ?)
225 ORDER BY accountno DESC
228 $sth->execute($borrowernumber);
229 return ($sth->fetchrow || 1);
232 =head2 fixaccounts (removed)
234 &fixaccounts($borrowernumber, $accountnumber, $amount);
237 # FIXME - I don't understand what this function does.
239 my ( $borrowernumber, $accountno, $amount ) = @_;
240 my $dbh = C4::Context->dbh;
241 my $sth = $dbh->prepare(
242 "SELECT * FROM accountlines WHERE borrowernumber=?
245 $sth->execute( $borrowernumber, $accountno );
246 my $data = $sth->fetchrow_hashref;
248 # FIXME - Error-checking
249 my $diff = $amount - $data->{'amount'};
250 my $outstanding = $data->{'amountoutstanding'} + $diff;
255 SET amount = '$amount',
256 amountoutstanding = '$outstanding'
257 WHERE borrowernumber = $borrowernumber
258 AND accountno = $accountno
260 # FIXME: exceedingly bad form. Use prepare with placholders ("?") in query and execute args.
266 my ( $borrowernumber, $itemnum ) = @_;
267 C4::Circulation::MarkIssueReturned( $borrowernumber, $itemnum );
268 my $borrower = C4::Members::GetMember( $borrowernumber, 'borrowernumber' );
269 my @datearr = localtime(time);
270 my $date = ( 1900 + $datearr[5] ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3];
271 my $bor = "$borrower->{'firstname'} $borrower->{'surname'} $borrower->{'cardnumber'}";
272 ModItem({ paidfor => "Paid for by $bor $date" }, undef, $itemnum);
277 # http://wiki.koha.org/doku.php?id=en:development:kohastatuses
278 # lost ==1 Lost, lost==2 longoverdue, lost==3 lost and paid for
279 # FIXME: itemlost should be set to 3 after payment is made, should be a warning to the interface that
280 # a charge has been added
281 # FIXME : if no replacement price, borrower just doesn't get charged?
283 my $dbh = C4::Context->dbh();
284 my ($itemnumber) = @_;
285 my $sth=$dbh->prepare("SELECT * FROM issues, items WHERE issues.itemnumber=items.itemnumber and issues.itemnumber=?");
286 $sth->execute($itemnumber);
287 my $issues=$sth->fetchrow_hashref();
289 # if a borrower lost the item, add a replacement cost to the their record
290 if ( $issues->{borrowernumber} ){
292 # first make sure the borrower hasn't already been charged for this item
293 my $sth1=$dbh->prepare("SELECT * from accountlines
294 WHERE borrowernumber=? AND itemnumber=? and accounttype='L'");
295 $sth1->execute($issues->{'borrowernumber'},$itemnumber);
296 my $existing_charge_hashref=$sth1->fetchrow_hashref();
299 unless ($existing_charge_hashref) {
300 # This item is on issue ... add replacement cost to the borrower's record and mark it returned
301 # Note that we add this to the account even if there's no replacement price, allowing some other
302 # process (or person) to update it, since we don't handle any defaults for replacement prices.
303 my $accountno = getnextacctno($issues->{'borrowernumber'});
304 my $sth2=$dbh->prepare("INSERT INTO accountlines
305 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding,itemnumber)
306 VALUES (?,?,now(),?,?,'L',?,?)");
307 $sth2->execute($issues->{'borrowernumber'},$accountno,$issues->{'replacementprice'},
308 "Lost Item $issues->{'title'} $issues->{'barcode'}",
309 $issues->{'replacementprice'},$itemnumber);
313 #FIXME : Should probably have a way to distinguish this from an item that really was returned.
314 warn " $issues->{'borrowernumber'} / $itemnumber ";
315 C4::Circulation::MarkIssueReturned($issues->{borrowernumber},$itemnumber);
322 &manualinvoice($borrowernumber, $itemnumber, $description, $type,
325 C<$borrowernumber> is the patron's borrower number.
326 C<$description> is a description of the transaction.
327 C<$type> may be one of C<CS>, C<CB>, C<CW>, C<CF>, C<CL>, C<N>, C<L>,
329 C<$itemnumber> is the item involved, if pertinent; otherwise, it
330 should be the empty string.
335 # FIXME: In Koha 3.0 , the only account adjustment 'types' passed to this function
338 # 'FOR' = FORGIVEN (Formerly 'F', but 'F' is taken to mean 'FINE' elsewhere)
341 # 'A' = Account Management fee
347 my ( $borrowernumber, $itemnum, $desc, $type, $amount, $user ) = @_;
348 my $dbh = C4::Context->dbh;
352 my $accountno = getnextacctno($borrowernumber);
353 my $amountleft = $amount;
361 # my $amount2 = $amount * -1; # FIXME - $amount2 = -$amount
363 # fixcredit( $borrowernumber, $amount2, $itemnum, $type, $user );
365 if ( $type eq 'N' ) {
366 $desc .= " New Card";
368 if ( $type eq 'F' ) {
371 if ( $type eq 'A' ) {
372 $desc .= " Account Management fee";
374 if ( $type eq 'M' ) {
378 if ( $type eq 'L' && $desc eq '' ) {
380 $desc = " Lost Item";
382 # if ( $type eq 'REF' ) {
383 # $desc .= " Cash Refund";
384 # $amountleft = refund( '', $borrowernumber, $amount );
386 if ( ( $type eq 'L' )
390 or ( $type eq 'M' ) )
395 if ( $itemnum ne '' ) {
396 $desc .= " " . $itemnum;
397 my $sth = $dbh->prepare(
398 "INSERT INTO accountlines
399 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding, itemnumber,notify_id)
400 VALUES (?, ?, now(), ?,?, ?,?,?,?)");
401 $sth->execute($borrowernumber, $accountno, $amount, $desc, $type, $amountleft, $itemnum,$notifyid) || return $sth->errstr;
403 my $sth=$dbh->prepare("INSERT INTO accountlines
404 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding,notify_id)
405 VALUES (?, ?, now(), ?, ?, ?, ?,?)"
407 $sth->execute( $borrowernumber, $accountno, $amount, $desc, $type,
408 $amountleft, $notifyid );
413 =head2 fixcredit #### DEPRECATED
415 $amountleft = &fixcredit($borrowernumber, $data, $barcode, $type, $user);
417 This function is only used internally, not exported.
421 # This function is deprecated in 3.0
425 #here we update both the accountoffsets and the account lines
426 my ( $borrowernumber, $data, $barcode, $type, $user ) = @_;
427 my $dbh = C4::Context->dbh;
430 my $amountleft = $data;
431 if ( $barcode ne '' ) {
432 my $item = GetBiblioFromItemNumber( '', $barcode );
433 my $nextaccntno = getnextacctno($borrowernumber);
434 my $query = "SELECT * FROM accountlines WHERE (borrowernumber=?
435 AND itemnumber=? AND amountoutstanding > 0)";
436 if ( $type eq 'CL' ) {
437 $query .= " AND (accounttype = 'L' OR accounttype = 'Rep')";
439 elsif ( $type eq 'CF' ) {
440 $query .= " AND (accounttype = 'F' OR accounttype = 'FU' OR
441 accounttype='Res' OR accounttype='Rent')";
443 elsif ( $type eq 'CB' ) {
444 $query .= " and accounttype='A'";
448 my $sth = $dbh->prepare($query);
449 $sth->execute( $borrowernumber, $item->{'itemnumber'} );
450 $accdata = $sth->fetchrow_hashref;
452 if ( $accdata->{'amountoutstanding'} < $amountleft ) {
454 $amountleft -= $accdata->{'amountoutstanding'};
457 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
460 my $thisacct = $accdata->{accountno};
461 my $usth = $dbh->prepare(
462 "UPDATE accountlines SET amountoutstanding= ?
463 WHERE (borrowernumber = ?) AND (accountno=?)"
465 $usth->execute( $newamtos, $borrowernumber, $thisacct );
467 $usth = $dbh->prepare(
468 "INSERT INTO accountoffsets
469 (borrowernumber, accountno, offsetaccount, offsetamount)
472 $usth->execute( $borrowernumber, $accdata->{'accountno'},
473 $nextaccntno, $newamtos );
478 my $nextaccntno = getnextacctno($borrowernumber);
480 # get lines with outstanding amounts to offset
481 my $sth = $dbh->prepare(
482 "SELECT * FROM accountlines
483 WHERE (borrowernumber = ?) AND (amountoutstanding >0)
486 $sth->execute($borrowernumber);
489 # offset transactions
490 while ( ( $accdata = $sth->fetchrow_hashref ) and ( $amountleft > 0 ) ) {
491 if ( $accdata->{'amountoutstanding'} < $amountleft ) {
493 $amountleft -= $accdata->{'amountoutstanding'};
496 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
499 my $thisacct = $accdata->{accountno};
500 my $usth = $dbh->prepare(
501 "UPDATE accountlines SET amountoutstanding= ?
502 WHERE (borrowernumber = ?) AND (accountno=?)"
504 $usth->execute( $newamtos, $borrowernumber, $thisacct );
506 $usth = $dbh->prepare(
507 "INSERT INTO accountoffsets
508 (borrowernumber, accountno, offsetaccount, offsetamount)
511 $usth->execute( $borrowernumber, $accdata->{'accountno'},
512 $nextaccntno, $newamtos );
516 $type = "Credit " . $type;
517 UpdateStats( $user, $type, $data, $user, '', '', $borrowernumber );
519 return ($amountleft);
525 #FIXME : DEPRECATED SUB
526 This subroutine tracks payments and/or credits against fines/charges
527 using the accountoffsets table, which is not used consistently in
528 Koha's fines management, and so is not used in 3.0
534 #here we update both the accountoffsets and the account lines
535 my ( $borrowernumber, $data ) = @_;
536 my $dbh = C4::Context->dbh;
539 my $amountleft = $data * -1;
542 my $nextaccntno = getnextacctno($borrowernumber);
544 # get lines with outstanding amounts to offset
545 my $sth = $dbh->prepare(
546 "SELECT * FROM accountlines
547 WHERE (borrowernumber = ?) AND (amountoutstanding<0)
550 $sth->execute($borrowernumber);
553 # offset transactions
554 while ( ( $accdata = $sth->fetchrow_hashref ) and ( $amountleft < 0 ) ) {
555 if ( $accdata->{'amountoutstanding'} > $amountleft ) {
557 $amountleft -= $accdata->{'amountoutstanding'};
560 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
565 my $thisacct = $accdata->{accountno};
566 my $usth = $dbh->prepare(
567 "UPDATE accountlines SET amountoutstanding= ?
568 WHERE (borrowernumber = ?) AND (accountno=?)"
570 $usth->execute( $newamtos, $borrowernumber, $thisacct );
572 $usth = $dbh->prepare(
573 "INSERT INTO accountoffsets
574 (borrowernumber, accountno, offsetaccount, offsetamount)
577 $usth->execute( $borrowernumber, $accdata->{'accountno'},
578 $nextaccntno, $newamtos );
582 return ($amountleft);
586 my ( $borrowerno, $timestamp, $accountno ) = @_;
587 my $dbh = C4::Context->dbh;
588 my $timestamp2 = $timestamp - 1;
590 my $sth = $dbh->prepare(
591 "SELECT * FROM accountlines WHERE borrowernumber=? AND accountno = ?"
593 $sth->execute( $borrowerno, $accountno );
596 while ( my $data = $sth->fetchrow_hashref ) {
604 my ( $date, $date2 ) = @_;
605 my $dbh = C4::Context->dbh;
606 my $sth = $dbh->prepare(
607 "SELECT * FROM accountlines,borrowers
608 WHERE amount < 0 AND accounttype <> 'Pay' AND accountlines.borrowernumber = borrowers.borrowernumber
609 AND timestamp >=TIMESTAMP(?) AND timestamp < TIMESTAMP(?)"
612 $sth->execute( $date, $date2 );
614 while ( my $data = $sth->fetchrow_hashref ) {
615 $data->{'date'} = $data->{'timestamp'};
623 my ( $date, $date2 ) = @_;
624 my $dbh = C4::Context->dbh;
626 my $sth = $dbh->prepare(
627 "SELECT *,timestamp AS datetime
628 FROM accountlines,borrowers
629 WHERE (accounttype = 'REF'
630 AND accountlines.borrowernumber = borrowers.borrowernumber
631 AND date >=? AND date <?)"
634 $sth->execute( $date, $date2 );
637 while ( my $data = $sth->fetchrow_hashref ) {
643 END { } # module clean-up code here (global destructor)