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
28 use vars qw($VERSION @ISA @EXPORT);
31 # set the version for version checking
36 &recordpayment &fixaccounts &makepayment &manualinvoice
37 &getnextacctno &reconcileaccount &getcharges &getcredits
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 both the accountoffsets and 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_?
221 my ($borrowernumber) = @_;
223 my $dbh = C4::Context->dbh;
224 my $sth = $dbh->prepare(
225 "SELECT * FROM accountlines
226 WHERE (borrowernumber = ?)
227 ORDER BY accountno DESC"
229 $sth->execute($borrowernumber);
230 if ( my $accdata = $sth->fetchrow_hashref ) {
231 $nextaccntno = $accdata->{'accountno'} + 1;
234 return ($nextaccntno);
239 &fixaccounts($borrowernumber, $accountnumber, $amount);
244 # FIXME - I don't understand what this function does.
246 my ( $borrowernumber, $accountno, $amount ) = @_;
247 my $dbh = C4::Context->dbh;
248 my $sth = $dbh->prepare(
249 "SELECT * FROM accountlines WHERE borrowernumber=?
252 $sth->execute( $borrowernumber, $accountno );
253 my $data = $sth->fetchrow_hashref;
255 # FIXME - Error-checking
256 my $diff = $amount - $data->{'amount'};
257 my $outstanding = $data->{'amountoutstanding'} + $diff;
262 SET amount = '$amount',
263 amountoutstanding = '$outstanding'
264 WHERE borrowernumber = $borrowernumber
265 AND accountno = $accountno
269 # FIXME - Never used, but not exported, either.
271 my ( $borrowernumber, $itemnum ) = @_;
272 my $dbh = C4::Context->dbh;
273 my $borrower = C4::Members::GetMember( $borrowernumber, 'borrowernumber' );
274 my $sth = $dbh->prepare(
275 "UPDATE issues SET returndate=now() WHERE
276 borrowernumber=? AND itemnumber=? AND returndate IS NULL"
278 $sth->execute( $borrowernumber, $itemnum );
280 my @datearr = localtime(time);
282 ( 1900 + $datearr[5] ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3];
284 "$borrower->{'firstname'} $borrower->{'surname'} $borrower->{'cardnumber'}";
285 ModItem({ paidfor => "Paid for by $bor $date" }, undef, $itemnum);
290 &manualinvoice($borrowernumber, $itemnumber, $description, $type,
293 C<$borrowernumber> is the patron's borrower number.
294 C<$description> is a description of the transaction.
295 C<$type> may be one of C<CS>, C<CB>, C<CW>, C<CF>, C<CL>, C<N>, C<L>,
297 C<$itemnumber> is the item involved, if pertinent; otherwise, it
298 should be the empty string.
303 # FIXME - Okay, so what does this function do, really?
305 my ( $borrowernumber, $itemnum, $desc, $type, $amount, $user ) = @_;
306 my $dbh = C4::Context->dbh;
310 my $accountno = getnextacctno($borrowernumber);
311 my $amountleft = $amount;
319 my $amount2 = $amount * -1; # FIXME - $amount2 = -$amount
321 fixcredit( $borrowernumber, $amount2, $itemnum, $type, $user );
323 if ( $type eq 'N' ) {
326 if ( $type eq 'F' ) {
329 if ( $type eq 'A' ) {
330 $desc .= "Account Management fee";
332 if ( $type eq 'M' ) {
336 if ( $type eq 'L' && $desc eq '' ) {
340 if ( $type eq 'REF' ) {
341 $desc .= "Cash Refund";
342 $amountleft = refund( '', $borrowernumber, $amount );
344 if ( ( $type eq 'L' )
348 or ( $type eq 'M' ) )
353 if ( $itemnum ne '' ) {
354 $desc .= " " . $itemnum;
355 my $sth = $dbh->prepare(
356 "INSERT INTO accountlines
357 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding, itemnumber,notify_id)
358 VALUES (?, ?, now(), ?,?, ?,?,?,?)");
359 $sth->execute($borrowernumber, $accountno, $amount, $desc, $type, $amountleft, $itemnum,$notifyid) || return $sth->errstr;
361 my $sth=$dbh->prepare("INSERT INTO accountlines
362 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding,notify_id)
363 VALUES (?, ?, now(), ?, ?, ?, ?,?)"
365 $sth->execute( $borrowernumber, $accountno, $amount, $desc, $type,
366 $amountleft, $notifyid );
373 $amountleft = &fixcredit($borrowernumber, $data, $barcode, $type, $user);
375 This function is only used internally, not exported.
376 FIXME - Figure out what this function does, and write it down.
382 #here we update both the accountoffsets and the account lines
383 my ( $borrowernumber, $data, $barcode, $type, $user ) = @_;
384 my $dbh = C4::Context->dbh;
387 my $amountleft = $data;
388 if ( $barcode ne '' ) {
389 my $item = GetBiblioFromItemNumber( '', $barcode );
390 my $nextaccntno = getnextacctno($borrowernumber);
391 my $query = "SELECT * FROM accountlines WHERE (borrowernumber=?
392 AND itemnumber=? AND amountoutstanding > 0)";
393 if ( $type eq 'CL' ) {
394 $query .= " AND (accounttype = 'L' OR accounttype = 'Rep')";
396 elsif ( $type eq 'CF' ) {
397 $query .= " AND (accounttype = 'F' OR accounttype = 'FU' OR
398 accounttype='Res' OR accounttype='Rent')";
400 elsif ( $type eq 'CB' ) {
401 $query .= " and accounttype='A'";
405 my $sth = $dbh->prepare($query);
406 $sth->execute( $borrowernumber, $item->{'itemnumber'} );
407 $accdata = $sth->fetchrow_hashref;
409 if ( $accdata->{'amountoutstanding'} < $amountleft ) {
411 $amountleft -= $accdata->{'amountoutstanding'};
414 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
417 my $thisacct = $accdata->{accountno};
418 my $usth = $dbh->prepare(
419 "UPDATE accountlines SET amountoutstanding= ?
420 WHERE (borrowernumber = ?) AND (accountno=?)"
422 $usth->execute( $newamtos, $borrowernumber, $thisacct );
424 $usth = $dbh->prepare(
425 "INSERT INTO accountoffsets
426 (borrowernumber, accountno, offsetaccount, offsetamount)
429 $usth->execute( $borrowernumber, $accdata->{'accountno'},
430 $nextaccntno, $newamtos );
435 my $nextaccntno = getnextacctno($borrowernumber);
437 # get lines with outstanding amounts to offset
438 my $sth = $dbh->prepare(
439 "SELECT * FROM accountlines
440 WHERE (borrowernumber = ?) AND (amountoutstanding >0)
443 $sth->execute($borrowernumber);
446 # offset transactions
447 while ( ( $accdata = $sth->fetchrow_hashref ) and ( $amountleft > 0 ) ) {
448 if ( $accdata->{'amountoutstanding'} < $amountleft ) {
450 $amountleft -= $accdata->{'amountoutstanding'};
453 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
456 my $thisacct = $accdata->{accountno};
457 my $usth = $dbh->prepare(
458 "UPDATE accountlines SET amountoutstanding= ?
459 WHERE (borrowernumber = ?) AND (accountno=?)"
461 $usth->execute( $newamtos, $borrowernumber, $thisacct );
463 $usth = $dbh->prepare(
464 "INSERT INTO accountoffsets
465 (borrowernumber, accountno, offsetaccount, offsetamount)
468 $usth->execute( $borrowernumber, $accdata->{'accountno'},
469 $nextaccntno, $newamtos );
473 $type = "Credit " . $type;
474 UpdateStats( $user, $type, $data, $user, '', '', $borrowernumber );
476 return ($amountleft);
482 # FIXME - Figure out what this function does, and write it down.
488 #here we update both the accountoffsets and the account lines
489 my ( $borrowernumber, $data ) = @_;
490 my $dbh = C4::Context->dbh;
493 my $amountleft = $data * -1;
496 my $nextaccntno = getnextacctno($borrowernumber);
498 # get lines with outstanding amounts to offset
499 my $sth = $dbh->prepare(
500 "SELECT * FROM accountlines
501 WHERE (borrowernumber = ?) AND (amountoutstanding<0)
504 $sth->execute($borrowernumber);
507 # offset transactions
508 while ( ( $accdata = $sth->fetchrow_hashref ) and ( $amountleft < 0 ) ) {
509 if ( $accdata->{'amountoutstanding'} > $amountleft ) {
511 $amountleft -= $accdata->{'amountoutstanding'};
514 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
519 my $thisacct = $accdata->{accountno};
520 my $usth = $dbh->prepare(
521 "UPDATE accountlines SET amountoutstanding= ?
522 WHERE (borrowernumber = ?) AND (accountno=?)"
524 $usth->execute( $newamtos, $borrowernumber, $thisacct );
526 $usth = $dbh->prepare(
527 "INSERT INTO accountoffsets
528 (borrowernumber, accountno, offsetaccount, offsetamount)
531 $usth->execute( $borrowernumber, $accdata->{'accountno'},
532 $nextaccntno, $newamtos );
536 return ($amountleft);
540 my ( $borrowerno, $timestamp, $accountno ) = @_;
541 my $dbh = C4::Context->dbh;
542 my $timestamp2 = $timestamp - 1;
544 my $sth = $dbh->prepare(
545 "SELECT * FROM accountlines WHERE borrowernumber=? AND accountno = ?"
547 $sth->execute( $borrowerno, $accountno );
550 while ( my $data = $sth->fetchrow_hashref ) {
558 my ( $date, $date2 ) = @_;
559 my $dbh = C4::Context->dbh;
560 my $sth = $dbh->prepare(
561 "SELECT * FROM accountlines,borrowers
562 WHERE amount < 0 AND accounttype <> 'Pay' AND accountlines.borrowernumber = borrowers.borrowernumber
563 AND timestamp >=TIMESTAMP(?) AND timestamp < TIMESTAMP(?)"
566 $sth->execute( $date, $date2 );
568 while ( my $data = $sth->fetchrow_hashref ) {
569 $data->{'date'} = $data->{'timestamp'};
577 my ( $date, $date2 ) = @_;
578 my $dbh = C4::Context->dbh;
580 my $sth = $dbh->prepare(
581 "SELECT *,timestamp AS datetime
582 FROM accountlines,borrowers
583 WHERE (accounttype = 'REF'
584 AND accountlines.borrowernumber = borrowers.borrowernumber
585 AND date >=? AND date <?)"
588 $sth->execute( $date, $date2 );
591 while ( my $data = $sth->fetchrow_hashref ) {
597 END { } # module clean-up code here (global destructor)