1 package C4::Accounts2; #assumes C4/Accounts2
4 # Copyright 2000-2002 Katipo Communications
6 # This file is part of Koha.
8 # Koha is free software; you can redistribute it and/or modify it under the
9 # terms of the GNU General Public License as published by the Free Software
10 # Foundation; either version 2 of the License, or (at your option) any later
13 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
14 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
15 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License along with
18 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
19 # Suite 330, Boston, MA 02111-1307 USA
26 use C4::Circulation::Circ2;
28 use vars qw($VERSION @ISA @EXPORT);
30 # set the version for version checking
31 $VERSION = 0.01; # FIXME - Should probably be different from
32 # the version for C4::Accounts
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
55 @EXPORT = qw(&checkaccount &recordpayment &fixaccounts &makepayment &manualinvoice
56 &getnextacctno &manualcredit
58 &dailyAccountBalance &addDailyAccountOp &getDailyAccountOp);
62 $owed = &checkaccount($env, $borrowernumber, $dbh, $date);
64 Looks up the total amount of money owed by a borrower (fines, etc.).
66 C<$borrowernumber> specifies the borrower to look up.
68 C<$dbh> is a DBI::db handle for the Koha database.
76 #check accounts and list amounts owing
77 my ($env,$bornumber,$dbh,$date)=@_;
78 my $select="SELECT SUM(amountoutstanding) AS total
80 WHERE borrowernumber = ?
81 AND amountoutstanding<>0";
82 my @bind = ($bornumber);
84 $select.=" AND date < ?";
88 my $sth=$dbh->prepare($select);
90 my $data=$sth->fetchrow_hashref;
91 my $total = $data->{'total'};
93 # output(1,2,"borrower owes $total");
95 # # output(1,2,"borrower owes $total");
97 # reconcileaccount($env,$dbh,$bornumber,$total);
106 &recordpayment($env, $borrowernumber, $payment);
108 Record payment by a patron. C<$borrowernumber> is the patron's
109 borrower number. C<$payment> is a floating-point number, giving the
110 amount that was paid. C<$env> is a reference-to-hash;
111 C<$env-E<gt>{branchcode}> is the code of the branch where payment was
114 Amounts owed are paid off oldest first. That is, if the patron has a
115 $1 fine from Feb. 1, another $1 fine from Mar. 1, and makes a payment
116 of $1.50, then the oldest fine will be paid off in full, and $0.50
117 will be credited to the next one.
122 #here we update both the accountoffsets and the account lines
123 my ($env,$bornumber,$data)=@_;
124 my $dbh = C4::Context->dbh;
127 my $branch=$env->{'branchcode'};
128 my $amountleft = $data;
130 my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
131 # get lines with outstanding amounts to offset
132 my $sth = $dbh->prepare("select * from accountlines
133 where (borrowernumber = ?) and (amountoutstanding<>0)
135 $sth->execute($bornumber);
136 # offset transactions
137 while (($accdata=$sth->fetchrow_hashref) and ($amountleft>0)){
138 if ($accdata->{'amountoutstanding'} < $amountleft) {
140 $amountleft -= $accdata->{'amountoutstanding'};
142 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
145 my $thisacct = $accdata->{accountno};
146 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
147 where (borrowernumber = ?) and (accountno=?)");
148 $usth->execute($newamtos,$bornumber,$thisacct);
150 # $usth = $dbh->prepare("insert into accountoffsets
151 # (borrowernumber, accountno, offsetaccount, offsetamount)
152 # values (?,?,?,?)");
153 # $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
157 my $usth = $dbh->prepare("insert into accountlines
158 (borrowernumber, accountno,date,amount,description,accounttype,amountoutstanding)
159 values (?,?,now(),?,'Payment,thanks','Pay',?)");
160 $usth->execute($bornumber,$nextaccntno,0-$data,0-$amountleft);
162 # UpdateStats($env,$branch,'payment',$data,'','','',$bornumber);
168 &makepayment($borrowernumber, $acctnumber, $amount, $branchcode);
170 Records the fact that a patron has paid off the entire amount he or
173 C<$borrowernumber> is the patron's borrower number. C<$acctnumber> is
174 the account that was credited. C<$amount> is the amount paid (this is
175 only used to record the payment. It is assumed to be equal to the
176 amount owed). C<$branchcode> is the code of the branch where payment
181 # FIXME - I'm not at all sure about the above, because I don't
182 # understand what the acct* tables in the Koha database are for.
185 #here we update the account lines
186 #updated to check, if they are paying off a lost item, we return the item
187 # from their card, and put a note on the item record
188 my ($bornumber,$accountno,$amount,$user,$type)=@_;
193 $desc="Payment,received by -". $user;
196 $desc="Written-off -by". $user;
199 my $dbh = C4::Context->dbh;
201 my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
203 my $sth=$dbh->prepare("Select * from accountlines where borrowernumber=? and accountno=?");
204 $sth->execute($bornumber,$accountno);
205 my $data=$sth->fetchrow_hashref;
210 SET amountoutstanding = amountoutstanding-$amount
211 WHERE borrowernumber = $bornumber
212 AND accountno = $accountno
217 # INSERT INTO accountoffsets
218 # (borrowernumber, accountno, offsetaccount,
220 # VALUES ($bornumber, $accountno, $nextaccntno, $newamtos)
224 my $payment=0-$amount;
225 if ($data->{'itemnumber'}){
226 $desc.=" ".$data->{'itemnumber'};
229 INSERT INTO accountlines
230 (borrowernumber, accountno, itemnumber,date, amount,
231 description, accounttype, amountoutstanding,offset)
232 VALUES ($bornumber, $nextaccntno, $data->{'itemnumber'},now(), $payment,
233 '$desc', '$pay', 0,$accountno)
237 INSERT INTO accountlines
238 (borrowernumber, accountno, date, amount,
239 description, accounttype, amountoutstanding,offset)
240 VALUES ($bornumber, $nextaccntno, now(), $payment,
241 '$desc', '$pay', 0,$accountno)
245 # FIXME - The second argument to &UpdateStats is supposed to be the
247 # UpdateStats($env,'MAIN',$pay,$amount,'','','',$bornumber);
249 #check to see what accounttype
250 if ($data->{'accounttype'} eq 'Rep' || $data->{'accounttype'} eq 'L'){
251 returnlost($bornumber,$data->{'itemnumber'});
257 $nextacct = &getnextacctno($env, $borrowernumber, $dbh);
259 Returns the next unused account number for the patron with the given
262 C<$dbh> is a DBI::db handle to the Koha database.
268 # FIXME - Okay, so what does the above actually _mean_?
270 my ($env,$bornumber,$dbh)=@_;
272 my $sth = $dbh->prepare("select * from accountlines
273 where (borrowernumber = ?)
274 order by accountno desc");
275 $sth->execute($bornumber);
276 if (my $accdata=$sth->fetchrow_hashref){
277 $nextaccntno = $accdata->{'accountno'} + 1;
280 return($nextaccntno);
285 &fixaccounts($borrowernumber, $accountnumber, $amount);
289 # FIXME - I don't understand what this function does.
291 my ($borrowernumber,$accountno,$amount)=@_;
292 my $dbh = C4::Context->dbh;
293 my $sth=$dbh->prepare("Select * from accountlines where borrowernumber=?
295 $sth->execute($borrowernumber,$accountno);
296 my $data=$sth->fetchrow_hashref;
297 # FIXME - Error-checking
298 my $diff=$amount-$data->{'amount'};
299 my $outstanding=$data->{'amountoutstanding'}+$diff;
304 SET amount = '$amount',
305 amountoutstanding = '$outstanding'
306 WHERE borrowernumber = $borrowernumber
307 AND accountno = $accountno
311 # FIXME - Never used, but not exported, either.
313 my ($borrnum,$itemnum)=@_;
314 my $dbh = C4::Context->dbh;
315 my $borrower=borrdata('',$borrnum); #from C4::Search;
316 my $sth=$dbh->prepare("Update issues set returndate=now() where
317 borrowernumber=? and itemnumber=? and returndate is null");
318 $sth->execute($borrnum,$itemnum);
320 my @datearr = localtime(time);
321 my $date = (1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
322 my $bor="$borrower->{'firstname'} $borrower->{'surname'} $borrower->{'cardnumber'}";
323 $sth=$dbh->prepare("Update items set paidfor=? where itemnumber=?");
324 $sth->execute("Paid for by $bor $date",$itemnum);
330 &manualinvoice($borrowernumber, $itemnumber, $description, $type,
333 C<$borrowernumber> is the patron's borrower number.
334 C<$description> is a description of the transaction.
335 C<$type> may be one of C<CS>, C<CB>, C<CW>, C<CF>, C<CL>, C<N>, C<L>,
337 C<$itemnumber> is the item involved, if pertinent; otherwise, it
338 should be the empty string.
342 # FIXME - Okay, so what does this function do, really?
344 my ($bornum,$itemnum,$desc,$type,$amount,$user)=@_;
345 my $dbh = C4::Context->dbh;
349 my $accountno=getnextacctno('',$bornum,$dbh);
350 my $amountleft=$amount;
357 if ($type eq 'L' && $desc eq ''){
362 $amountleft=refund('',$bornum,$amount);
367 my $sth=$dbh->prepare("INSERT INTO accountlines
368 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding, itemnumber)
369 VALUES (?, ?, now(), ?,?, ?,?,?)");
370 $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft, $itemnum);
372 $desc=$dbh->quote($desc);
373 my $sth=$dbh->prepare("INSERT INTO accountlines
374 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding)
375 VALUES (?, ?, now(), ?, ?, ?, ?)");
376 $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft);
380 my ($bornum,$itemnum,$desc,$type,$amount,$user,$oldaccount)=@_;
381 my $dbh = C4::Context->dbh;
385 my $accountno=getnextacctno('',$bornum,$dbh);
386 # my $amountleft=$amount;
389 if ($type eq 'CN' || $type eq 'CA' || $type eq 'CR'
390 || $type eq 'CF' || $type eq 'CL' || $type eq 'CM'){
391 my $amount2=$amount*-1; # FIXME - $amount2 = -$amount
392 ( $amountleft, $noerror,$oldaccount)=fixcredit(\%env,$bornum,$amount2,$itemnum,$type,$user);
396 $desc.="Card fee credited by:".$user;
399 $desc.="Other fees credited by:".$user;
402 $desc.="Resrvation fee credited by:".$user;
405 $desc.="Managenent fee credited by:".$user;
407 if ($type eq 'CL' && $desc eq ''){
408 $desc="Lost Item credited by:".$user;
413 $desc.=" Credited for overdue item:".$itemnum. " by:".$user;
414 my $sth=$dbh->prepare("INSERT INTO accountlines
415 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding, itemnumber,offset)
416 VALUES (?, ?, now(), ?,?, ?,?,?,?)");
417 $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft, $itemnum,$oldaccount);
419 # $desc=$dbh->quote($desc);
420 my $sth=$dbh->prepare("INSERT INTO accountlines
421 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding,offset)
422 VALUES (?, ?, now(), ?, ?, ?, ?,?)");
423 $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft,$oldaccount);
432 # $amountleft = &fixcredit($env, $bornumber, $data, $barcode, $type, $user);
434 # This function is only used internally.
435 # FIXME - Figure out what this function does, and write it down.
437 #here we update both the accountoffsets and the account lines
438 my ($env,$bornumber,$data,$barcode,$type,$user)=@_;
439 my $dbh = C4::Context->dbh;
442 my $amountleft = $data;
444 # my $item=getiteminformation($env,'',$barcode);
445 my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
446 my $query="Select * from accountlines where (borrowernumber=?
447 and amountoutstanding > 0)";
450 $query.=" and (accounttype = 'L' or accounttype = 'Rep')";
451 } elsif ($type eq 'CF'){
452 $query.=" and ( itemnumber= ? and (accounttype = 'FU' or accounttype='F') )";
454 } elsif ($type eq 'CN'){
455 $query.=" and ( accounttype = 'N' )";
456 } elsif ($type eq 'CR'){
457 $query.=" and ( itemnumber= ? and ( accounttype='Res' or accounttype='Rent'))";
459 }elsif ($type eq 'CM'){
460 $query.=" and ( accounttype = 'M' )";
461 }elsif ($type eq 'CA'){
462 $query.=" and ( accounttype = 'A' )";
465 my $sth=$dbh->prepare($query);
466 if ($exectype && $barcode ne ''){
467 $sth->execute($bornumber,$barcode);
469 $sth->execute($bornumber);
471 $accdata=$sth->fetchrow_hashref;
475 if ($accdata->{'amountoutstanding'} < $amountleft) {
477 $amountleft -= $accdata->{'amountoutstanding'};
479 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
482 my $thisacct = $accdata->{accountno};
483 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
484 where (borrowernumber = ?) and (accountno=?)");
485 $usth->execute($newamtos,$bornumber,$thisacct);
487 # $usth = $dbh->prepare("insert into accountoffsets
488 # (borrowernumber, accountno, offsetaccount, offsetamount)
489 # values (?,?,?,?)");
490 # $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
494 my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
495 # get lines with outstanding amounts to offset
496 my $sth = $dbh->prepare("select * from accountlines
497 where (borrowernumber = ?) and (amountoutstanding >0)
499 $sth->execute($bornumber);
501 # offset transactions
502 while (($accdata=$sth->fetchrow_hashref) and ($amountleft>0)){
503 if ($accdata->{'amountoutstanding'} < $amountleft) {
505 $amountleft -= $accdata->{'amountoutstanding'};
507 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
510 my $thisacct = $accdata->{accountno};
511 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
512 where (borrowernumber = ?) and (accountno=?)");
513 $usth->execute($newamtos,$bornumber,$thisacct);
515 # $usth = $dbh->prepare("insert into accountoffsets
516 # (borrowernumber, accountno, offsetaccount, offsetamount)
517 # values (?,?,?,?)");
518 # $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
522 # $env->{'branch'}=$user;
523 # $type="Credit ".$type;
524 # UpdateStats($env,$user,$type,$data,$user,'','',$bornumber);
526 return($amountleft,1,$accdata->{'accountno'});
532 # FIXME - Figure out what this function does, and write it down.
534 #here we update both the accountoffsets and the account lines
535 my ($env,$bornumber,$data)=@_;
536 my $dbh = C4::Context->dbh;
539 # my $branch=$env->{'branchcode'};
540 my $amountleft = $data *-1;
543 my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
544 # get lines with outstanding amounts to offset
545 my $sth = $dbh->prepare("select * from accountlines
546 where (borrowernumber = ?) and (amountoutstanding<0)
548 $sth->execute($bornumber);
550 # offset transactions
551 while (($accdata=$sth->fetchrow_hashref) and ($amountleft<0)){
552 if ($accdata->{'amountoutstanding'} > $amountleft) {
554 $amountleft -= $accdata->{'amountoutstanding'};
556 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
560 my $thisacct = $accdata->{accountno};
561 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
562 where (borrowernumber = ?) and (accountno=?)");
563 $usth->execute($newamtos,$bornumber,$thisacct);
565 # $usth = $dbh->prepare("insert into accountoffsets
566 # (borrowernumber, accountno, offsetaccount, offsetamount)
567 # values (?,?,?,?)");
568 # $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
575 #Funtion to manage the daily account#
577 sub dailyAccountBalance {
579 my $dbh = C4::Context->dbh;
584 $sth = $dbh->prepare("SELECT * FROM dailyaccountbalance WHERE balanceDate = ?");
585 $sth->execute($date);
586 my $data = $sth->fetchrow_hashref;
587 if (!$data->{'balanceDate'}) {
588 $data->{'noentry'} = 1;
594 $sth = $dbh->prepare("SELECT * FROM dailyaccountbalance WHERE balanceDate = CURRENT_DATE()");
598 return ($sth->fetchrow_hashref);
602 $sth = $dbh->prepare("SELECT currentBalanceInHand FROM dailyaccountbalance ORDER BY balanceDate DESC LIMIT 1");
605 ($hash{'initialBalanceInHand'}) = $sth->fetchrow_array;
606 $hash{'currentBalanceInHand'} = $hash{'initialBalanceInHand'};
608 $hash{'initialBalanceInHand'} = 0;
609 $hash{'currentBalanceInHand'} = 0;
611 #gets the current date.
612 my @nowarr = localtime();
613 my $date = (1900+$nowarr[5])."-".($nowarr[4]+1)."-".$nowarr[3];
615 $hash{'balanceDate'} = $date;
616 $hash{'initialBalanceInHand'} = sprintf ("%.2f", $hash{'initialBalanceInHand'});
617 $hash{'currentBalanceInHand'} = sprintf ("%.2f", $hash{'currentBalanceInHand'});
624 sub addDailyAccountOp {
625 my ($description, $amount, $type, $invoice) = @_;
626 my $dbh = C4::Context->dbh;
627 unless ($invoice) { $invoice = undef};
628 my $sth = $dbh->prepare("INSERT INTO dailyaccount (date, description, amount, type, invoice) VALUES (CURRENT_DATE(), ?, ?, ?, ?)");
629 $sth->execute($description, $amount, $type, $invoice);
630 my $accountop = $dbh->{'mysql_insertid'};
631 $sth = $dbh->prepare("SELECT * FROM dailyaccountbalance WHERE balanceDate = CURRENT_DATE()");
634 $sth = $dbh->prepare("SELECT currentBalanceInHand FROM dailyaccountbalance ORDER BY balanceDate DESC LIMIT 1");
636 my ($blc) = $sth->fetchrow_array;
637 unless ($blc) {$blc = 0}
638 $sth = $dbh->prepare("INSERT INTO dailyaccountbalance (balanceDate, initialBalanceInHand, currentBalanceInHand) VALUES (CURRENT_DATE(), ?, ?)");
639 $sth->execute($blc, $blc);
642 $amount = -1 * $amount;
644 $sth = $dbh->prepare("UPDATE dailyaccountbalance SET currentBalanceInHand = currentBalanceInHand + ? WHERE balanceDate = CURRENT_DATE()");
645 $sth->execute($amount);
649 sub getDailyAccountOp {
651 my $dbh = C4::Context->dbh;
654 $sth = $dbh->prepare("SELECT * FROM dailyaccount WHERE date = ?");
655 $sth->execute($date);
657 $sth = $dbh->prepare("SELECT * FROM dailyaccount WHERE date = CURRENT_DATE()");
662 while (my $row = $sth->fetchrow_hashref) {
663 $row->{'num'} = $count++;
664 $row->{$row->{'type'}} = 1;
666 $row->{'invoice'} =~ /(\w*)\-(\w*)\-(\w*)/;
667 $row->{'invoiceNumber'} = $1;
668 $row->{'invoiceSupplier'} = $2;
669 $row->{'invoiceType'} = $3;
671 push @operations, $row;
673 return (scalar(@operations), \@operations);
676 END { } # module clean-up code here (global destructor)