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
17 # with Koha; if not, write to the Free Software Foundation, Inc.,
18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
22 #use warnings; FIXME - Bug 2505
26 use C4::Circulation qw(ReturnLostItem);
28 use vars qw($VERSION @ISA @EXPORT);
31 # set the version for version checking
36 &recordpayment &makepayment &manualinvoice
37 &getnextacctno &reconcileaccount &getcharges &ModNote &getcredits
38 &getrefunds &chargelostitem
41 recordpayment_selectaccts
47 C4::Accounts - Functions for dealing with Koha accounts
55 The functions in this module deal with the monetary aspect of Koha,
56 including looking up and modifying the amount of money owed by a
63 &recordpayment($borrowernumber, $payment);
65 Record payment by a patron. C<$borrowernumber> is the patron's
66 borrower number. C<$payment> is a floating-point number, giving the
69 Amounts owed are paid off oldest first. That is, if the patron has a
70 $1 fine from Feb. 1, another $1 fine from Mar. 1, and makes a payment
71 of $1.50, then the oldest fine will be paid off in full, and $0.50
72 will be credited to the next one.
79 #here we update the account lines
80 my ( $borrowernumber, $data ) = @_;
81 my $dbh = C4::Context->dbh;
84 my $branch = C4::Context->userenv->{'branch'};
85 my $amountleft = $data;
87 $manager_id = C4::Context->userenv->{'number'} if C4::Context->userenv;
90 my $nextaccntno = getnextacctno($borrowernumber);
92 # get lines with outstanding amounts to offset
93 my $sth = $dbh->prepare(
94 "SELECT * FROM accountlines
95 WHERE (borrowernumber = ?) AND (amountoutstanding<>0)
98 $sth->execute($borrowernumber);
100 # offset transactions
101 while ( ( $accdata = $sth->fetchrow_hashref ) and ( $amountleft > 0 ) ) {
102 if ( $accdata->{'amountoutstanding'} < $amountleft ) {
104 $amountleft -= $accdata->{'amountoutstanding'};
107 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
110 my $thisacct = $accdata->{accountno};
111 my $usth = $dbh->prepare(
112 "UPDATE accountlines SET amountoutstanding= ?
113 WHERE (borrowernumber = ?) AND (accountno=?)"
115 $usth->execute( $newamtos, $borrowernumber, $thisacct );
117 # $usth = $dbh->prepare(
118 # "INSERT INTO accountoffsets
119 # (borrowernumber, accountno, offsetaccount, offsetamount)
122 # $usth->execute( $borrowernumber, $accdata->{'accountno'},
123 # $nextaccntno, $newamtos );
128 my $usth = $dbh->prepare(
129 "INSERT INTO accountlines
130 (borrowernumber, accountno,date,amount,description,accounttype,amountoutstanding,manager_id)
131 VALUES (?,?,now(),?,'Payment,thanks','Pay',?,?)"
133 $usth->execute( $borrowernumber, $nextaccntno, 0 - $data, 0 - $amountleft, $manager_id );
135 UpdateStats( $branch, 'payment', $data, '', '', '', $borrowernumber, $nextaccntno );
141 &makepayment($borrowernumber, $acctnumber, $amount, $branchcode);
143 Records the fact that a patron has paid off the entire amount he or
146 C<$borrowernumber> is the patron's borrower number. C<$acctnumber> is
147 the account that was credited. C<$amount> is the amount paid (this is
148 only used to record the payment. It is assumed to be equal to the
149 amount owed). C<$branchcode> is the code of the branch where payment
155 # FIXME - I'm not at all sure about the above, because I don't
156 # understand what the acct* tables in the Koha database are for.
159 #here we update both the accountoffsets and the account lines
160 #updated to check, if they are paying off a lost item, we return the item
161 # from their card, and put a note on the item record
162 my ( $borrowernumber, $accountno, $amount, $user, $branch ) = @_;
163 my $dbh = C4::Context->dbh;
165 $manager_id = C4::Context->userenv->{'number'} if C4::Context->userenv;
168 my $nextaccntno = getnextacctno($borrowernumber);
172 "SELECT * FROM accountlines WHERE borrowernumber=? AND accountno=?");
173 $sth->execute( $borrowernumber, $accountno );
174 my $data = $sth->fetchrow_hashref;
177 if($data->{'accounttype'} eq "Pay"){
181 SET amountoutstanding = 0, description = 'Payment,thanks'
182 WHERE borrowernumber = ?
186 $udp->execute($borrowernumber, $accountno );
192 SET amountoutstanding = 0
193 WHERE borrowernumber = ?
197 $udp->execute($borrowernumber, $accountno );
201 my $payment = 0 - $amount;
206 INTO accountlines (borrowernumber, accountno, date, amount, itemnumber, description, accounttype, amountoutstanding, manager_id)
207 VALUES ( ?, ?, now(), ?, ?, 'Payment,thanks', 'Pay', 0, ?)"
209 $ins->execute($borrowernumber, $nextaccntno, $payment, $data->{'itemnumber'}, $manager_id);
213 # FIXME - The second argument to &UpdateStats is supposed to be the
215 # UpdateStats is now being passed $accountno too. MTJ
216 UpdateStats( $user, 'payment', $amount, '', '', '', $borrowernumber,
218 #from perldoc: for SELECT only #$sth->finish;
220 #check to see what accounttype
221 if ( $data->{'accounttype'} eq 'Rep' || $data->{'accounttype'} eq 'L' ) {
222 C4::Circulation::ReturnLostItem( $borrowernumber, $data->{'itemnumber'} );
228 $nextacct = &getnextacctno($borrowernumber);
230 Returns the next unused account number for the patron with the given
236 # FIXME - Okay, so what does the above actually _mean_?
237 sub getnextacctno ($) {
238 my ($borrowernumber) = shift or return undef;
239 my $sth = C4::Context->dbh->prepare(
240 "SELECT accountno+1 FROM accountlines
241 WHERE (borrowernumber = ?)
242 ORDER BY accountno DESC
245 $sth->execute($borrowernumber);
246 return ($sth->fetchrow || 1);
249 =head2 fixaccounts (removed)
251 &fixaccounts($borrowernumber, $accountnumber, $amount);
254 # FIXME - I don't understand what this function does.
256 my ( $borrowernumber, $accountno, $amount ) = @_;
257 my $dbh = C4::Context->dbh;
258 my $sth = $dbh->prepare(
259 "SELECT * FROM accountlines WHERE borrowernumber=?
262 $sth->execute( $borrowernumber, $accountno );
263 my $data = $sth->fetchrow_hashref;
265 # FIXME - Error-checking
266 my $diff = $amount - $data->{'amount'};
267 my $outstanding = $data->{'amountoutstanding'} + $diff;
272 SET amount = '$amount',
273 amountoutstanding = '$outstanding'
274 WHERE borrowernumber = $borrowernumber
275 AND accountno = $accountno
277 # FIXME: exceedingly bad form. Use prepare with placholders ("?") in query and execute args.
283 # lost ==1 Lost, lost==2 longoverdue, lost==3 lost and paid for
284 # FIXME: itemlost should be set to 3 after payment is made, should be a warning to the interface that
285 # a charge has been added
286 # FIXME : if no replacement price, borrower just doesn't get charged?
287 my $dbh = C4::Context->dbh();
288 my ($borrowernumber, $itemnumber, $amount, $description) = @_;
290 # first make sure the borrower hasn't already been charged for this item
291 my $sth1=$dbh->prepare("SELECT * from accountlines
292 WHERE borrowernumber=? AND itemnumber=? and accounttype='L'");
293 $sth1->execute($borrowernumber,$itemnumber);
294 my $existing_charge_hashref=$sth1->fetchrow_hashref();
297 unless ($existing_charge_hashref) {
299 $manager_id = C4::Context->userenv->{'number'} if C4::Context->userenv;
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($borrowernumber);
304 my $sth2=$dbh->prepare("INSERT INTO accountlines
305 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding,itemnumber,manager_id)
306 VALUES (?,?,now(),?,?,'L',?,?,?)");
307 $sth2->execute($borrowernumber,$accountno,$amount,
308 $description,$amount,$itemnumber,$manager_id);
316 &manualinvoice($borrowernumber, $itemnumber, $description, $type,
319 C<$borrowernumber> is the patron's borrower number.
320 C<$description> is a description of the transaction.
321 C<$type> may be one of C<CS>, C<CB>, C<CW>, C<CF>, C<CL>, C<N>, C<L>,
323 C<$itemnumber> is the item involved, if pertinent; otherwise, it
324 should be the empty string.
329 # FIXME: In Koha 3.0 , the only account adjustment 'types' passed to this function
332 # 'FOR' = FORGIVEN (Formerly 'F', but 'F' is taken to mean 'FINE' elsewhere)
335 # 'A' = Account Management fee
341 my ( $borrowernumber, $itemnum, $desc, $type, $amount, $note ) = @_;
343 $manager_id = C4::Context->userenv->{'number'} if C4::Context->userenv;
344 my $dbh = C4::Context->dbh;
347 my $accountno = getnextacctno($borrowernumber);
348 my $amountleft = $amount;
356 # my $amount2 = $amount * -1; # FIXME - $amount2 = -$amount
358 # fixcredit( $borrowernumber, $amount2, $itemnum, $type, $user );
360 if ( $type eq 'N' ) {
361 $desc .= " New Card";
363 if ( $type eq 'F' ) {
366 if ( $type eq 'A' ) {
367 $desc .= " Account Management fee";
369 if ( $type eq 'M' ) {
373 if ( $type eq 'L' && $desc eq '' ) {
375 $desc = " Lost Item";
377 # if ( $type eq 'REF' ) {
378 # $desc .= " Cash Refund";
379 # $amountleft = refund( '', $borrowernumber, $amount );
381 if ( ( $type eq 'L' )
385 or ( $type eq 'M' ) )
391 $desc .= ' ' . $itemnum;
392 my $sth = $dbh->prepare(
393 'INSERT INTO accountlines
394 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding, itemnumber,notify_id, note, manager_id)
395 VALUES (?, ?, now(), ?,?, ?,?,?,?,?,?)');
396 $sth->execute($borrowernumber, $accountno, $amount, $desc, $type, $amountleft, $itemnum,$notifyid, $note, $manager_id) || return $sth->errstr;
398 my $sth=$dbh->prepare("INSERT INTO accountlines
399 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding,notify_id, note, manager_id)
400 VALUES (?, ?, now(), ?, ?, ?, ?,?,?,?)"
402 $sth->execute( $borrowernumber, $accountno, $amount, $desc, $type,
403 $amountleft, $notifyid, $note, $manager_id );
408 =head2 fixcredit #### DEPRECATED
410 $amountleft = &fixcredit($borrowernumber, $data, $barcode, $type, $user);
412 This function is only used internally, not exported.
416 # This function is deprecated in 3.0
420 #here we update both the accountoffsets and the account lines
421 my ( $borrowernumber, $data, $barcode, $type, $user ) = @_;
422 my $dbh = C4::Context->dbh;
425 my $amountleft = $data;
426 if ( $barcode ne '' ) {
427 my $item = GetBiblioFromItemNumber( '', $barcode );
428 my $nextaccntno = getnextacctno($borrowernumber);
429 my $query = "SELECT * FROM accountlines WHERE (borrowernumber=?
430 AND itemnumber=? AND amountoutstanding > 0)";
431 if ( $type eq 'CL' ) {
432 $query .= " AND (accounttype = 'L' OR accounttype = 'Rep')";
434 elsif ( $type eq 'CF' ) {
435 $query .= " AND (accounttype = 'F' OR accounttype = 'FU' OR
436 accounttype='Res' OR accounttype='Rent')";
438 elsif ( $type eq 'CB' ) {
439 $query .= " and accounttype='A'";
443 my $sth = $dbh->prepare($query);
444 $sth->execute( $borrowernumber, $item->{'itemnumber'} );
445 $accdata = $sth->fetchrow_hashref;
447 if ( $accdata->{'amountoutstanding'} < $amountleft ) {
449 $amountleft -= $accdata->{'amountoutstanding'};
452 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
455 my $thisacct = $accdata->{accountno};
456 my $usth = $dbh->prepare(
457 "UPDATE accountlines SET amountoutstanding= ?
458 WHERE (borrowernumber = ?) AND (accountno=?)"
460 $usth->execute( $newamtos, $borrowernumber, $thisacct );
462 $usth = $dbh->prepare(
463 "INSERT INTO accountoffsets
464 (borrowernumber, accountno, offsetaccount, offsetamount)
467 $usth->execute( $borrowernumber, $accdata->{'accountno'},
468 $nextaccntno, $newamtos );
473 my $nextaccntno = getnextacctno($borrowernumber);
475 # get lines with outstanding amounts to offset
476 my $sth = $dbh->prepare(
477 "SELECT * FROM accountlines
478 WHERE (borrowernumber = ?) AND (amountoutstanding >0)
481 $sth->execute($borrowernumber);
484 # offset transactions
485 while ( ( $accdata = $sth->fetchrow_hashref ) and ( $amountleft > 0 ) ) {
486 if ( $accdata->{'amountoutstanding'} < $amountleft ) {
488 $amountleft -= $accdata->{'amountoutstanding'};
491 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
494 my $thisacct = $accdata->{accountno};
495 my $usth = $dbh->prepare(
496 "UPDATE accountlines SET amountoutstanding= ?
497 WHERE (borrowernumber = ?) AND (accountno=?)"
499 $usth->execute( $newamtos, $borrowernumber, $thisacct );
501 $usth = $dbh->prepare(
502 "INSERT INTO accountoffsets
503 (borrowernumber, accountno, offsetaccount, offsetamount)
506 $usth->execute( $borrowernumber, $accdata->{'accountno'},
507 $nextaccntno, $newamtos );
511 $type = "Credit " . $type;
512 UpdateStats( $user, $type, $data, $user, '', '', $borrowernumber );
514 return ($amountleft);
520 #FIXME : DEPRECATED SUB
521 This subroutine tracks payments and/or credits against fines/charges
522 using the accountoffsets table, which is not used consistently in
523 Koha's fines management, and so is not used in 3.0
529 #here we update both the accountoffsets and the account lines
530 my ( $borrowernumber, $data ) = @_;
531 my $dbh = C4::Context->dbh;
534 my $amountleft = $data * -1;
537 my $nextaccntno = getnextacctno($borrowernumber);
539 # get lines with outstanding amounts to offset
540 my $sth = $dbh->prepare(
541 "SELECT * FROM accountlines
542 WHERE (borrowernumber = ?) AND (amountoutstanding<0)
545 $sth->execute($borrowernumber);
548 # offset transactions
549 while ( ( $accdata = $sth->fetchrow_hashref ) and ( $amountleft < 0 ) ) {
550 if ( $accdata->{'amountoutstanding'} > $amountleft ) {
552 $amountleft -= $accdata->{'amountoutstanding'};
555 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
560 my $thisacct = $accdata->{accountno};
561 my $usth = $dbh->prepare(
562 "UPDATE accountlines SET amountoutstanding= ?
563 WHERE (borrowernumber = ?) AND (accountno=?)"
565 $usth->execute( $newamtos, $borrowernumber, $thisacct );
567 $usth = $dbh->prepare(
568 "INSERT INTO accountoffsets
569 (borrowernumber, accountno, offsetaccount, offsetamount)
572 $usth->execute( $borrowernumber, $accdata->{'accountno'},
573 $nextaccntno, $newamtos );
577 return ($amountleft);
581 my ( $borrowerno, $timestamp, $accountno ) = @_;
582 my $dbh = C4::Context->dbh;
583 my $timestamp2 = $timestamp - 1;
585 my $sth = $dbh->prepare(
586 "SELECT * FROM accountlines WHERE borrowernumber=? AND accountno = ?"
588 $sth->execute( $borrowerno, $accountno );
591 while ( my $data = $sth->fetchrow_hashref ) {
598 my ( $borrowernumber, $accountno, $note ) = @_;
599 my $dbh = C4::Context->dbh;
600 my $sth = $dbh->prepare('UPDATE accountlines SET note = ? WHERE borrowernumber = ? AND accountno = ?');
601 $sth->execute( $note, $borrowernumber, $accountno );
605 my ( $date, $date2 ) = @_;
606 my $dbh = C4::Context->dbh;
607 my $sth = $dbh->prepare(
608 "SELECT * FROM accountlines,borrowers
609 WHERE amount < 0 AND accounttype <> 'Pay' AND accountlines.borrowernumber = borrowers.borrowernumber
610 AND timestamp >=TIMESTAMP(?) AND timestamp < TIMESTAMP(?)"
613 $sth->execute( $date, $date2 );
615 while ( my $data = $sth->fetchrow_hashref ) {
616 $data->{'date'} = $data->{'timestamp'};
624 my ( $date, $date2 ) = @_;
625 my $dbh = C4::Context->dbh;
627 my $sth = $dbh->prepare(
628 "SELECT *,timestamp AS datetime
629 FROM accountlines,borrowers
630 WHERE (accounttype = 'REF'
631 AND accountlines.borrowernumber = borrowers.borrowernumber
632 AND date >=? AND date <?)"
635 $sth->execute( $date, $date2 );
638 while ( my $data = $sth->fetchrow_hashref ) {
646 my ( $borrowernumber, $accountno ) = @_;
647 my $dbh = C4::Context->dbh;
649 my $sth = $dbh->prepare('SELECT amountoutstanding FROM accountlines WHERE borrowernumber = ? AND accountno = ?');
650 $sth->execute( $borrowernumber, $accountno );
651 my $row = $sth->fetchrow_hashref();
652 my $amount_outstanding = $row->{'amountoutstanding'};
654 if ( $amount_outstanding <= 0 ) {
655 $sth = $dbh->prepare('UPDATE accountlines SET amountoutstanding = amount * -1, description = CONCAT( description, " Reversed -" ) WHERE borrowernumber = ? AND accountno = ?');
656 $sth->execute( $borrowernumber, $accountno );
658 $sth = $dbh->prepare('UPDATE accountlines SET amountoutstanding = 0, description = CONCAT( description, " Reversed -" ) WHERE borrowernumber = ? AND accountno = ?');
659 $sth->execute( $borrowernumber, $accountno );
663 =head2 recordpayment_selectaccts
665 recordpayment_selectaccts($borrowernumber, $payment,$accts);
667 Record payment by a patron. C<$borrowernumber> is the patron's
668 borrower number. C<$payment> is a floating-point number, giving the
669 amount that was paid. C<$accts> is an array ref to a list of
670 accountnos which the payment can be recorded against
672 Amounts owed are paid off oldest first. That is, if the patron has a
673 $1 fine from Feb. 1, another $1 fine from Mar. 1, and makes a payment
674 of $1.50, then the oldest fine will be paid off in full, and $0.50
675 will be credited to the next one.
679 sub recordpayment_selectaccts {
680 my ( $borrowernumber, $amount, $accts ) = @_;
682 my $dbh = C4::Context->dbh;
685 my $branch = C4::Context->userenv->{branch};
686 my $amountleft = $amount;
688 $manager_id = C4::Context->userenv->{'number'} if C4::Context->userenv;
689 my $sql = 'SELECT * FROM accountlines WHERE (borrowernumber = ?) ' .
690 'AND (amountoutstanding<>0) ';
692 $sql .= ' AND accountno IN ( ' . join ',', @{$accts};
695 $sql .= ' ORDER BY date';
697 my $nextaccntno = getnextacctno($borrowernumber);
699 # get lines with outstanding amounts to offset
700 my $rows = $dbh->selectall_arrayref($sql, { Slice => {} }, $borrowernumber);
702 # offset transactions
703 my $sth = $dbh->prepare('UPDATE accountlines SET amountoutstanding= ? ' .
704 'WHERE (borrowernumber = ?) AND (accountno=?)');
705 for my $accdata ( @{$rows} ) {
706 if ($amountleft == 0) {
709 if ( $accdata->{amountoutstanding} < $amountleft ) {
711 $amountleft -= $accdata->{amountoutstanding};
714 $newamtos = $accdata->{amountoutstanding} - $amountleft;
717 my $thisacct = $accdata->{accountno};
718 $sth->execute( $newamtos, $borrowernumber, $thisacct );
722 $sql = 'INSERT INTO accountlines ' .
723 '(borrowernumber, accountno,date,amount,description,accounttype,amountoutstanding,manager_id) ' .
724 q|VALUES (?,?,now(),?,'Payment,thanks','Pay',?,?)|;
725 $dbh->do($sql,{},$borrowernumber, $nextaccntno, 0 - $amount, 0 - $amountleft, $manager_id );
726 UpdateStats( $branch, 'payment', $amount, '', '', '', $borrowernumber, $nextaccntno );
730 # makepayment needs to be fixed to handle partials till then this separate subroutine
732 sub makepartialpayment {
733 my ( $borrowernumber, $accountno, $amount, $user, $branch ) = @_;
735 $manager_id = C4::Context->userenv->{'number'} if C4::Context->userenv;
736 if (!$amount || $amount < 0) {
739 my $dbh = C4::Context->dbh;
741 my $nextaccntno = getnextacctno($borrowernumber);
744 my $data = $dbh->selectrow_hashref(
745 'SELECT * FROM accountlines WHERE borrowernumber=? AND accountno=?',undef,$borrowernumber,$accountno);
746 my $new_outstanding = $data->{amountoutstanding} - $amount;
748 my $update = 'UPDATE accountlines SET amountoutstanding = ? WHERE borrowernumber = ? '
749 . ' AND accountno = ?';
750 $dbh->do( $update, undef, $new_outstanding, $borrowernumber, $accountno);
753 my $insert = 'INSERT INTO accountlines (borrowernumber, accountno, date, amount, '
754 . 'description, accounttype, amountoutstanding, itemnumber, manager_id) '
755 . ' VALUES (?, ?, now(), ?, ?, ?, 0, ?, ?)';
757 $dbh->do( $insert, undef, $borrowernumber, $nextaccntno, $amount,
758 "Payment, thanks - $user", 'Pay', $data->{'itemnumber'}, $manager_id);
760 UpdateStats( $user, 'payment', $amount, '', '', '', $borrowernumber, $accountno );
767 END { } # module clean-up code here (global destructor)