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
29 use vars qw($VERSION @ISA @EXPORT);
31 # set the version for version checking
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
53 @EXPORT = qw(&recordpayment &fixaccounts &makepayment &manualinvoice
54 &getnextacctno &reconcileaccount);
58 &recordpayment($borrowernumber, $payment);
60 Record payment by a patron. C<$borrowernumber> is the patron's
61 borrower number. C<$payment> is a floating-point number, giving the
64 Amounts owed are paid off oldest first. That is, if the patron has a
65 $1 fine from Feb. 1, another $1 fine from Mar. 1, and makes a payment
66 of $1.50, then the oldest fine will be paid off in full, and $0.50
67 will be credited to the next one.
74 #here we update both the accountoffsets and the account lines
75 my ( $borrowernumber, $data ) = @_;
76 my $dbh = C4::Context->dbh;
79 my $branch = C4::Context->userenv->{'branch'};
80 my $amountleft = $data;
83 my $nextaccntno = getnextacctno($borrowernumber);
85 # get lines with outstanding amounts to offset
86 my $sth = $dbh->prepare(
87 "SELECT * FROM accountlines
88 WHERE (borrowernumber = ?) AND (amountoutstanding<>0)
91 $sth->execute($borrowernumber);
94 while ( ( $accdata = $sth->fetchrow_hashref ) and ( $amountleft > 0 ) ) {
95 if ( $accdata->{'amountoutstanding'} < $amountleft ) {
97 $amountleft -= $accdata->{'amountoutstanding'};
100 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
103 my $thisacct = $accdata->{accountno};
104 my $usth = $dbh->prepare(
105 "UPDATE accountlines SET amountoutstanding= ?
106 WHERE (borrowernumber = ?) AND (accountno=?)"
108 $usth->execute( $newamtos, $borrowernumber, $thisacct );
110 $usth = $dbh->prepare(
111 "INSERT INTO accountoffsets
112 (borrowernumber, accountno, offsetaccount, offsetamount)
115 $usth->execute( $borrowernumber, $accdata->{'accountno'},
116 $nextaccntno, $newamtos );
121 my $usth = $dbh->prepare(
122 "INSERT INTO accountlines
123 (borrowernumber, accountno,date,amount,description,accounttype,amountoutstanding)
124 VALUES (?,?,now(),?,'Payment,thanks','Pay',?)"
126 $usth->execute( $borrowernumber, $nextaccntno, 0 - $data, 0 - $amountleft );
128 UpdateStats( $branch, 'payment', $data, '', '', '', $borrowernumber );
134 &makepayment($borrowernumber, $acctnumber, $amount, $branchcode);
136 Records the fact that a patron has paid off the entire amount he or
139 C<$borrowernumber> is the patron's borrower number. C<$acctnumber> is
140 the account that was credited. C<$amount> is the amount paid (this is
141 only used to record the payment. It is assumed to be equal to the
142 amount owed). C<$branchcode> is the code of the branch where payment
148 # FIXME - I'm not at all sure about the above, because I don't
149 # understand what the acct* tables in the Koha database are for.
152 #here we update both the accountoffsets and the account lines
153 #updated to check, if they are paying off a lost item, we return the item
154 # from their card, and put a note on the item record
155 my ( $borrowernumber, $accountno, $amount, $user, $branch ) = @_;
156 my $dbh = C4::Context->dbh;
159 my $nextaccntno = getnextacctno($borrowernumber);
163 "SELECT * FROM accountlines WHERE borrowernumber=? AND accountno=?");
164 $sth->execute( $borrowernumber, $accountno );
165 my $data = $sth->fetchrow_hashref;
170 SET amountoutstanding = 0
171 WHERE borrowernumber = $borrowernumber
172 AND accountno = $accountno
178 INSERT INTO accountoffsets
179 (borrowernumber, accountno, offsetaccount,
181 VALUES ($borrowernumber, $accountno, $nextaccntno, $newamtos)
185 my $payment = 0 - $amount;
187 INSERT INTO accountlines
188 (borrowernumber, accountno, date, amount,
189 description, accounttype, amountoutstanding)
190 VALUES ($borrowernumber, $nextaccntno, now(), $payment,
191 'Payment,thanks - $user', 'Pay', 0)
194 # FIXME - The second argument to &UpdateStats is supposed to be the
196 # UpdateStats is now being passed $accountno too. MTJ
197 UpdateStats( $user, 'payment', $amount, '', '', '', $borrowernumber,
201 #check to see what accounttype
202 if ( $data->{'accounttype'} eq 'Rep' || $data->{'accounttype'} eq 'L' ) {
203 returnlost( $borrowernumber, $data->{'itemnumber'} );
209 $nextacct = &getnextacctno($borrowernumber);
211 Returns the next unused account number for the patron with the given
217 # FIXME - Okay, so what does the above actually _mean_?
219 my ($borrowernumber) = @_;
221 my $dbh = C4::Context->dbh;
222 my $sth = $dbh->prepare(
223 "SELECT * FROM accountlines
224 WHERE (borrowernumber = ?)
225 ORDER BY accountno DESC"
227 $sth->execute($borrowernumber);
228 if ( my $accdata = $sth->fetchrow_hashref ) {
229 $nextaccntno = $accdata->{'accountno'} + 1;
232 return ($nextaccntno);
237 &fixaccounts($borrowernumber, $accountnumber, $amount);
242 # FIXME - I don't understand what this function does.
244 my ( $borrowernumber, $accountno, $amount ) = @_;
245 my $dbh = C4::Context->dbh;
246 my $sth = $dbh->prepare(
247 "SELECT * FROM accountlines WHERE borrowernumber=?
250 $sth->execute( $borrowernumber, $accountno );
251 my $data = $sth->fetchrow_hashref;
253 # FIXME - Error-checking
254 my $diff = $amount - $data->{'amount'};
255 my $outstanding = $data->{'amountoutstanding'} + $diff;
260 SET amount = '$amount',
261 amountoutstanding = '$outstanding'
262 WHERE borrowernumber = $borrowernumber
263 AND accountno = $accountno
267 # FIXME - Never used, but not exported, either.
269 my ( $borrowernumber, $itemnum ) = @_;
270 my $dbh = C4::Context->dbh;
271 my $borrower = C4::Members::GetMember( $borrowernumber, 'borrowernumber' );
272 my $sth = $dbh->prepare(
273 "UPDATE issues SET returndate=now() WHERE
274 borrowernumber=? AND itemnumber=? AND returndate IS NULL"
276 $sth->execute( $borrowernumber, $itemnum );
278 my @datearr = localtime(time);
280 ( 1900 + $datearr[5] ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3];
282 "$borrower->{'firstname'} $borrower->{'surname'} $borrower->{'cardnumber'}";
283 ModItem({ paidfor => "Paid for by $bor $date" }, undef, $itemnum);
288 &manualinvoice($borrowernumber, $itemnumber, $description, $type,
291 C<$borrowernumber> is the patron's borrower number.
292 C<$description> is a description of the transaction.
293 C<$type> may be one of C<CS>, C<CB>, C<CW>, C<CF>, C<CL>, C<N>, C<L>,
295 C<$itemnumber> is the item involved, if pertinent; otherwise, it
296 should be the empty string.
301 # FIXME - Okay, so what does this function do, really?
303 my ( $borrowernumber, $itemnum, $desc, $type, $amount, $user ) = @_;
304 my $dbh = C4::Context->dbh;
308 my $accountno = getnextacctno($borrowernumber);
309 my $amountleft = $amount;
317 my $amount2 = $amount * -1; # FIXME - $amount2 = -$amount
319 fixcredit( $borrowernumber, $amount2, $itemnum, $type, $user );
321 if ( $type eq 'N' ) {
324 if ( $type eq 'F' ) {
327 if ( $type eq 'A' ) {
328 $desc .= "Account Management fee";
330 if ( $type eq 'M' ) {
334 if ( $type eq 'L' && $desc eq '' ) {
338 if ( $type eq 'REF' ) {
339 $desc .= "Cash Refund";
340 $amountleft = refund( '', $borrowernumber, $amount );
342 if ( ( $type eq 'L' )
346 or ( $type eq 'M' ) )
351 if ( $itemnum ne '' ) {
352 $desc .= " " . $itemnum;
353 my $sth = $dbh->prepare(
354 "INSERT INTO accountlines
355 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding, itemnumber,notify_id)
356 VALUES (?, ?, now(), ?,?, ?,?,?,?)");
357 $sth->execute($borrowernumber, $accountno, $amount, $desc, $type, $amountleft, $itemnum,$notifyid) || return $sth->errstr;
359 my $sth=$dbh->prepare("INSERT INTO accountlines
360 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding,notify_id)
361 VALUES (?, ?, now(), ?, ?, ?, ?,?)"
363 $sth->execute( $borrowernumber, $accountno, $amount, $desc, $type,
364 $amountleft, $notifyid );
371 $amountleft = &fixcredit($borrowernumber, $data, $barcode, $type, $user);
373 This function is only used internally, not exported.
374 FIXME - Figure out what this function does, and write it down.
380 #here we update both the accountoffsets and the account lines
381 my ( $borrowernumber, $data, $barcode, $type, $user ) = @_;
382 my $dbh = C4::Context->dbh;
385 my $amountleft = $data;
386 if ( $barcode ne '' ) {
387 my $item = GetBiblioFromItemNumber( '', $barcode );
388 my $nextaccntno = getnextacctno($borrowernumber);
389 my $query = "SELECT * FROM accountlines WHERE (borrowernumber=?
390 AND itemnumber=? AND amountoutstanding > 0)";
391 if ( $type eq 'CL' ) {
392 $query .= " AND (accounttype = 'L' OR accounttype = 'Rep')";
394 elsif ( $type eq 'CF' ) {
395 $query .= " AND (accounttype = 'F' OR accounttype = 'FU' OR
396 accounttype='Res' OR accounttype='Rent')";
398 elsif ( $type eq 'CB' ) {
399 $query .= " and accounttype='A'";
403 my $sth = $dbh->prepare($query);
404 $sth->execute( $borrowernumber, $item->{'itemnumber'} );
405 $accdata = $sth->fetchrow_hashref;
407 if ( $accdata->{'amountoutstanding'} < $amountleft ) {
409 $amountleft -= $accdata->{'amountoutstanding'};
412 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
415 my $thisacct = $accdata->{accountno};
416 my $usth = $dbh->prepare(
417 "UPDATE accountlines SET amountoutstanding= ?
418 WHERE (borrowernumber = ?) AND (accountno=?)"
420 $usth->execute( $newamtos, $borrowernumber, $thisacct );
422 $usth = $dbh->prepare(
423 "INSERT INTO accountoffsets
424 (borrowernumber, accountno, offsetaccount, offsetamount)
427 $usth->execute( $borrowernumber, $accdata->{'accountno'},
428 $nextaccntno, $newamtos );
433 my $nextaccntno = getnextacctno($borrowernumber);
435 # get lines with outstanding amounts to offset
436 my $sth = $dbh->prepare(
437 "SELECT * FROM accountlines
438 WHERE (borrowernumber = ?) AND (amountoutstanding >0)
441 $sth->execute($borrowernumber);
444 # offset transactions
445 while ( ( $accdata = $sth->fetchrow_hashref ) and ( $amountleft > 0 ) ) {
446 if ( $accdata->{'amountoutstanding'} < $amountleft ) {
448 $amountleft -= $accdata->{'amountoutstanding'};
451 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
454 my $thisacct = $accdata->{accountno};
455 my $usth = $dbh->prepare(
456 "UPDATE accountlines SET amountoutstanding= ?
457 WHERE (borrowernumber = ?) AND (accountno=?)"
459 $usth->execute( $newamtos, $borrowernumber, $thisacct );
461 $usth = $dbh->prepare(
462 "INSERT INTO accountoffsets
463 (borrowernumber, accountno, offsetaccount, offsetamount)
466 $usth->execute( $borrowernumber, $accdata->{'accountno'},
467 $nextaccntno, $newamtos );
471 $type = "Credit " . $type;
472 UpdateStats( $user, $type, $data, $user, '', '', $borrowernumber );
474 return ($amountleft);
480 # FIXME - Figure out what this function does, and write it down.
486 #here we update both the accountoffsets and the account lines
487 my ( $borrowernumber, $data ) = @_;
488 my $dbh = C4::Context->dbh;
491 my $amountleft = $data * -1;
494 my $nextaccntno = getnextacctno($borrowernumber);
496 # get lines with outstanding amounts to offset
497 my $sth = $dbh->prepare(
498 "SELECT * FROM accountlines
499 WHERE (borrowernumber = ?) AND (amountoutstanding<0)
502 $sth->execute($borrowernumber);
505 # offset transactions
506 while ( ( $accdata = $sth->fetchrow_hashref ) and ( $amountleft < 0 ) ) {
507 if ( $accdata->{'amountoutstanding'} > $amountleft ) {
509 $amountleft -= $accdata->{'amountoutstanding'};
512 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
517 my $thisacct = $accdata->{accountno};
518 my $usth = $dbh->prepare(
519 "UPDATE accountlines SET amountoutstanding= ?
520 WHERE (borrowernumber = ?) AND (accountno=?)"
522 $usth->execute( $newamtos, $borrowernumber, $thisacct );
524 $usth = $dbh->prepare(
525 "INSERT INTO accountoffsets
526 (borrowernumber, accountno, offsetaccount, offsetamount)
529 $usth->execute( $borrowernumber, $accdata->{'accountno'},
530 $nextaccntno, $newamtos );
534 return ($amountleft);
537 END { } # module clean-up code here (global destructor)