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);
30 # set the version for version checking
35 C4::Accounts - Functions for dealing with Koha accounts
43 The functions in this module deal with the monetary aspect of Koha,
44 including looking up and modifying the amount of money owed by a
52 @EXPORT = qw(&recordpayment &fixaccounts &makepayment &manualinvoice
53 &getnextacctno &reconcileaccount);
57 &recordpayment($borrowernumber, $payment);
59 Record payment by a patron. C<$borrowernumber> is the patron's
60 borrower number. C<$payment> is a floating-point number, giving the
63 Amounts owed are paid off oldest first. That is, if the patron has a
64 $1 fine from Feb. 1, another $1 fine from Mar. 1, and makes a payment
65 of $1.50, then the oldest fine will be paid off in full, and $0.50
66 will be credited to the next one.
73 #here we update both the accountoffsets and the account lines
74 my ( $borrowernumber, $data ) = @_;
75 my $dbh = C4::Context->dbh;
78 my $branch = C4::Context->userenv->{'branch'};
79 my $amountleft = $data;
82 my $nextaccntno = getnextacctno($borrowernumber);
84 # get lines with outstanding amounts to offset
85 my $sth = $dbh->prepare(
86 "SELECT * FROM accountlines
87 WHERE (borrowernumber = ?) AND (amountoutstanding<>0)
90 $sth->execute($borrowernumber);
93 while ( ( $accdata = $sth->fetchrow_hashref ) and ( $amountleft > 0 ) ) {
94 if ( $accdata->{'amountoutstanding'} < $amountleft ) {
96 $amountleft -= $accdata->{'amountoutstanding'};
99 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
102 my $thisacct = $accdata->{accountno};
103 my $usth = $dbh->prepare(
104 "UPDATE accountlines SET amountoutstanding= ?
105 WHERE (borrowernumber = ?) AND (accountno=?)"
107 $usth->execute( $newamtos, $borrowernumber, $thisacct );
109 $usth = $dbh->prepare(
110 "INSERT INTO accountoffsets
111 (borrowernumber, accountno, offsetaccount, offsetamount)
114 $usth->execute( $borrowernumber, $accdata->{'accountno'},
115 $nextaccntno, $newamtos );
120 my $usth = $dbh->prepare(
121 "INSERT INTO accountlines
122 (borrowernumber, accountno,date,amount,description,accounttype,amountoutstanding)
123 VALUES (?,?,now(),?,'Payment,thanks','Pay',?)"
125 $usth->execute( $borrowernumber, $nextaccntno, 0 - $data, 0 - $amountleft );
127 UpdateStats( $branch, 'payment', $data, '', '', '', $borrowernumber );
133 &makepayment($borrowernumber, $acctnumber, $amount, $branchcode);
135 Records the fact that a patron has paid off the entire amount he or
138 C<$borrowernumber> is the patron's borrower number. C<$acctnumber> is
139 the account that was credited. C<$amount> is the amount paid (this is
140 only used to record the payment. It is assumed to be equal to the
141 amount owed). C<$branchcode> is the code of the branch where payment
147 # FIXME - I'm not at all sure about the above, because I don't
148 # understand what the acct* tables in the Koha database are for.
151 #here we update both the accountoffsets and the account lines
152 #updated to check, if they are paying off a lost item, we return the item
153 # from their card, and put a note on the item record
154 my ( $borrowernumber, $accountno, $amount, $user, $branch ) = @_;
155 my $dbh = C4::Context->dbh;
158 my $nextaccntno = getnextacctno($borrowernumber);
162 "SELECT * FROM accountlines WHERE borrowernumber=? AND accountno=?");
163 $sth->execute( $borrowernumber, $accountno );
164 my $data = $sth->fetchrow_hashref;
169 SET amountoutstanding = 0
170 WHERE borrowernumber = $borrowernumber
171 AND accountno = $accountno
177 INSERT INTO accountoffsets
178 (borrowernumber, accountno, offsetaccount,
180 VALUES ($borrowernumber, $accountno, $nextaccntno, $newamtos)
184 my $payment = 0 - $amount;
186 INSERT INTO accountlines
187 (borrowernumber, accountno, date, amount,
188 description, accounttype, amountoutstanding)
189 VALUES ($borrowernumber, $nextaccntno, now(), $payment,
190 'Payment,thanks - $user', 'Pay', 0)
193 # FIXME - The second argument to &UpdateStats is supposed to be the
195 # UpdateStats is now being passed $accountno too. MTJ
196 UpdateStats( $user, 'payment', $amount, '', '', '', $borrowernumber,
200 #check to see what accounttype
201 if ( $data->{'accounttype'} eq 'Rep' || $data->{'accounttype'} eq 'L' ) {
202 returnlost( $borrowernumber, $data->{'itemnumber'} );
208 $nextacct = &getnextacctno($borrowernumber);
210 Returns the next unused account number for the patron with the given
216 # FIXME - Okay, so what does the above actually _mean_?
218 my ($borrowernumber) = @_;
220 my $dbh = C4::Context->dbh;
221 my $sth = $dbh->prepare(
222 "SELECT * FROM accountlines
223 WHERE (borrowernumber = ?)
224 ORDER BY accountno DESC"
226 $sth->execute($borrowernumber);
227 if ( my $accdata = $sth->fetchrow_hashref ) {
228 $nextaccntno = $accdata->{'accountno'} + 1;
231 return ($nextaccntno);
236 &fixaccounts($borrowernumber, $accountnumber, $amount);
241 # FIXME - I don't understand what this function does.
243 my ( $borrowernumber, $accountno, $amount ) = @_;
244 my $dbh = C4::Context->dbh;
245 my $sth = $dbh->prepare(
246 "SELECT * FROM accountlines WHERE borrowernumber=?
249 $sth->execute( $borrowernumber, $accountno );
250 my $data = $sth->fetchrow_hashref;
252 # FIXME - Error-checking
253 my $diff = $amount - $data->{'amount'};
254 my $outstanding = $data->{'amountoutstanding'} + $diff;
259 SET amount = '$amount',
260 amountoutstanding = '$outstanding'
261 WHERE borrowernumber = $borrowernumber
262 AND accountno = $accountno
266 # FIXME - Never used, but not exported, either.
268 my ( $borrowernumber, $itemnum ) = @_;
269 my $dbh = C4::Context->dbh;
270 my $borrower = C4::Members::GetMember( $borrowernumber, 'borrowernumber' );
271 my $sth = $dbh->prepare(
272 "UPDATE issues SET returndate=now() WHERE
273 borrowernumber=? AND itemnumber=? AND returndate IS NULL"
275 $sth->execute( $borrowernumber, $itemnum );
277 my @datearr = localtime(time);
279 ( 1900 + $datearr[5] ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3];
281 "$borrower->{'firstname'} $borrower->{'surname'} $borrower->{'cardnumber'}";
282 $sth = $dbh->prepare("UPDATE items SET paidfor=? WHERE itemnumber=?");
283 $sth->execute( "Paid for by $bor $date", $itemnum );
289 &manualinvoice($borrowernumber, $itemnumber, $description, $type,
292 C<$borrowernumber> is the patron's borrower number.
293 C<$description> is a description of the transaction.
294 C<$type> may be one of C<CS>, C<CB>, C<CW>, C<CF>, C<CL>, C<N>, C<L>,
296 C<$itemnumber> is the item involved, if pertinent; otherwise, it
297 should be the empty string.
302 # FIXME - Okay, so what does this function do, really?
304 my ( $borrowernumber, $itemnum, $desc, $type, $amount, $user ) = @_;
305 my $dbh = C4::Context->dbh;
309 my $accountno = getnextacctno($borrowernumber);
310 my $amountleft = $amount;
318 my $amount2 = $amount * -1; # FIXME - $amount2 = -$amount
320 fixcredit( $borrowernumber, $amount2, $itemnum, $type, $user );
322 if ( $type eq 'N' ) {
325 if ( $type eq 'F' ) {
328 if ( $type eq 'A' ) {
329 $desc .= "Account Management fee";
331 if ( $type eq 'M' ) {
335 if ( $type eq 'L' && $desc eq '' ) {
339 if ( $type eq 'REF' ) {
340 $desc .= "Cash Refund";
341 $amountleft = refund( '', $borrowernumber, $amount );
343 if ( ( $type eq 'L' )
347 or ( $type eq 'M' ) )
352 if ( $itemnum ne '' ) {
353 $desc .= " " . $itemnum;
354 my $sth = $dbh->prepare(
355 "INSERT INTO accountlines
356 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding, itemnumber,notify_id)
357 VALUES (?, ?, now(), ?,?, ?,?,?,?)");
358 $sth->execute($borrowernumber, $accountno, $amount, $desc, $type, $amountleft, $itemnum,$notifyid) || return $sth->errstr;
360 my $sth=$dbh->prepare("INSERT INTO accountlines
361 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding,notify_id)
362 VALUES (?, ?, now(), ?, ?, ?, ?,?)"
364 $sth->execute( $borrowernumber, $accountno, $amount, $desc, $type,
365 $amountleft, $notifyid );
372 $amountleft = &fixcredit($borrowernumber, $data, $barcode, $type, $user);
374 This function is only used internally, not exported.
375 FIXME - Figure out what this function does, and write it down.
381 #here we update both the accountoffsets and the account lines
382 my ( $borrowernumber, $data, $barcode, $type, $user ) = @_;
383 my $dbh = C4::Context->dbh;
386 my $amountleft = $data;
387 if ( $barcode ne '' ) {
388 my $item = GetBiblioFromItemNumber( '', $barcode );
389 my $nextaccntno = getnextacctno($borrowernumber);
390 my $query = "SELECT * FROM accountlines WHERE (borrowernumber=?
391 AND itemnumber=? AND amountoutstanding > 0)";
392 if ( $type eq 'CL' ) {
393 $query .= " AND (accounttype = 'L' OR accounttype = 'Rep')";
395 elsif ( $type eq 'CF' ) {
396 $query .= " AND (accounttype = 'F' OR accounttype = 'FU' OR
397 accounttype='Res' OR accounttype='Rent')";
399 elsif ( $type eq 'CB' ) {
400 $query .= " and accounttype='A'";
404 my $sth = $dbh->prepare($query);
405 $sth->execute( $borrowernumber, $item->{'itemnumber'} );
406 $accdata = $sth->fetchrow_hashref;
408 if ( $accdata->{'amountoutstanding'} < $amountleft ) {
410 $amountleft -= $accdata->{'amountoutstanding'};
413 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
416 my $thisacct = $accdata->{accountno};
417 my $usth = $dbh->prepare(
418 "UPDATE accountlines SET amountoutstanding= ?
419 WHERE (borrowernumber = ?) AND (accountno=?)"
421 $usth->execute( $newamtos, $borrowernumber, $thisacct );
423 $usth = $dbh->prepare(
424 "INSERT INTO accountoffsets
425 (borrowernumber, accountno, offsetaccount, offsetamount)
428 $usth->execute( $borrowernumber, $accdata->{'accountno'},
429 $nextaccntno, $newamtos );
434 my $nextaccntno = getnextacctno($borrowernumber);
436 # get lines with outstanding amounts to offset
437 my $sth = $dbh->prepare(
438 "SELECT * FROM accountlines
439 WHERE (borrowernumber = ?) AND (amountoutstanding >0)
442 $sth->execute($borrowernumber);
445 # offset transactions
446 while ( ( $accdata = $sth->fetchrow_hashref ) and ( $amountleft > 0 ) ) {
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 );
472 $type = "Credit " . $type;
473 UpdateStats( $user, $type, $data, $user, '', '', $borrowernumber );
475 return ($amountleft);
481 # FIXME - Figure out what this function does, and write it down.
487 #here we update both the accountoffsets and the account lines
488 my ( $borrowernumber, $data ) = @_;
489 my $dbh = C4::Context->dbh;
492 my $amountleft = $data * -1;
495 my $nextaccntno = getnextacctno($borrowernumber);
497 # get lines with outstanding amounts to offset
498 my $sth = $dbh->prepare(
499 "SELECT * FROM accountlines
500 WHERE (borrowernumber = ?) AND (amountoutstanding<0)
503 $sth->execute($borrowernumber);
506 # offset transactions
507 while ( ( $accdata = $sth->fetchrow_hashref ) and ( $amountleft < 0 ) ) {
508 if ( $accdata->{'amountoutstanding'} > $amountleft ) {
510 $amountleft -= $accdata->{'amountoutstanding'};
513 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
518 my $thisacct = $accdata->{accountno};
519 my $usth = $dbh->prepare(
520 "UPDATE accountlines SET amountoutstanding= ?
521 WHERE (borrowernumber = ?) AND (accountno=?)"
523 $usth->execute( $newamtos, $borrowernumber, $thisacct );
525 $usth = $dbh->prepare(
526 "INSERT INTO accountoffsets
527 (borrowernumber, accountno, offsetaccount, offsetamount)
530 $usth->execute( $borrowernumber, $accdata->{'accountno'},
531 $nextaccntno, $newamtos );
535 return ($amountleft);
538 END { } # module clean-up code here (global destructor)