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=C4::Members::borrdata('',$borrnum); #from C4::Members;
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($dbh,$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;
412 $desc.=" Credited for overdue item:".$itemnum. " by:".$user;
413 my $sth=$dbh->prepare("INSERT INTO accountlines
414 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding, itemnumber,offset)
415 VALUES (?, ?, now(), ?,?, ?,?,?,?)");
416 $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft, $itemnum,$oldaccount);
418 my $sth=$dbh->prepare("INSERT INTO accountlines
419 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding,offset)
420 VALUES (?, ?, now(), ?, ?, ?, ?,?)");
421 $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft,$oldaccount);
430 #here we update both the accountoffsets and the account lines
431 my ($dbh,$bornumber,$data,$itemnumber,$type,$user)=@_;
434 my $amountleft = $data;
436 my $query="Select * from accountlines where (borrowernumber=?
437 and amountoutstanding > 0)";
440 $query.=" and (accounttype = 'L' or accounttype = 'Rep')";
441 } elsif ($type eq 'CF'){
442 $query.=" and ( itemnumber= ? and (accounttype = 'FU' or accounttype='F') )";
444 } elsif ($type eq 'CN'){
445 $query.=" and ( accounttype = 'N' )";
446 } elsif ($type eq 'CR'){
447 $query.=" and ( itemnumber= ? and ( accounttype='Res' or accounttype='Rent'))";
449 }elsif ($type eq 'CM'){
450 $query.=" and ( accounttype = 'M' )";
451 }elsif ($type eq 'CA'){
452 $query.=" and ( accounttype = 'A' )";
455 my $sth=$dbh->prepare($query);
456 if ($exectype && $itemnumber ne ''){
457 $sth->execute($bornumber,$itemnumber);
459 $sth->execute($bornumber);
461 $accdata=$sth->fetchrow_hashref;
465 if ($accdata->{'amountoutstanding'} < $amountleft) {
467 $amountleft -= $accdata->{'amountoutstanding'};
469 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
472 my $thisacct = $accdata->{accountno};
473 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
474 where (borrowernumber = ?) and (accountno=?)");
475 $usth->execute($newamtos,$bornumber,$thisacct);
480 # get lines with outstanding amounts to offset
481 my $sth = $dbh->prepare("select * from accountlines
482 where (borrowernumber = ?) and (amountoutstanding >0)
484 $sth->execute($bornumber);
486 # offset transactions
487 while (($accdata=$sth->fetchrow_hashref) and ($amountleft>0)){
488 if ($accdata->{'amountoutstanding'} < $amountleft) {
490 $amountleft -= $accdata->{'amountoutstanding'};
492 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
495 my $thisacct = $accdata->{accountno};
496 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
497 where (borrowernumber = ?) and (accountno=?)");
498 $usth->execute($newamtos,$bornumber,$thisacct);
504 return($amountleft,1,$accdata->{'accountno'});
510 # FIXME - Figure out what this function does, and write it down.
512 #here we update both the accountoffsets and the account lines
513 my ($env,$bornumber,$data)=@_;
514 my $dbh = C4::Context->dbh;
517 # my $branch=$env->{'branchcode'};
518 my $amountleft = $data *-1;
521 # get lines with outstanding amounts to offset
522 my $sth = $dbh->prepare("select * from accountlines
523 where (borrowernumber = ?) and (amountoutstanding<0)
525 $sth->execute($bornumber);
527 # offset transactions
528 while (($accdata=$sth->fetchrow_hashref) and ($amountleft<0)){
529 if ($accdata->{'amountoutstanding'} > $amountleft) {
531 $amountleft -= $accdata->{'amountoutstanding'};
533 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
537 my $thisacct = $accdata->{accountno};
538 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
539 where (borrowernumber = ?) and (accountno=?)");
540 $usth->execute($newamtos,$bornumber,$thisacct);
548 #Funtion to manage the daily account#
550 sub dailyAccountBalance {
552 my $dbh = C4::Context->dbh;
557 $sth = $dbh->prepare("SELECT * FROM dailyaccountbalance WHERE balanceDate = ?");
558 $sth->execute($date);
559 my $data = $sth->fetchrow_hashref;
560 if (!$data->{'balanceDate'}) {
561 $data->{'noentry'} = 1;
567 $sth = $dbh->prepare("SELECT * FROM dailyaccountbalance WHERE balanceDate = CURRENT_DATE()");
571 return ($sth->fetchrow_hashref);
575 $sth = $dbh->prepare("SELECT currentBalanceInHand FROM dailyaccountbalance ORDER BY balanceDate DESC LIMIT 1");
578 ($hash{'initialBalanceInHand'}) = $sth->fetchrow_array;
579 $hash{'currentBalanceInHand'} = $hash{'initialBalanceInHand'};
581 $hash{'initialBalanceInHand'} = 0;
582 $hash{'currentBalanceInHand'} = 0;
584 #gets the current date.
585 my @nowarr = localtime();
586 my $date = (1900+$nowarr[5])."-".($nowarr[4]+1)."-".$nowarr[3];
588 $hash{'balanceDate'} = $date;
589 $hash{'initialBalanceInHand'} = sprintf ("%.2f", $hash{'initialBalanceInHand'});
590 $hash{'currentBalanceInHand'} = sprintf ("%.2f", $hash{'currentBalanceInHand'});
597 sub addDailyAccountOp {
598 my ($description, $amount, $type, $invoice) = @_;
599 my $dbh = C4::Context->dbh;
600 unless ($invoice) { $invoice = undef};
601 my $sth = $dbh->prepare("INSERT INTO dailyaccount (date, description, amount, type, invoice) VALUES (CURRENT_DATE(), ?, ?, ?, ?)");
602 $sth->execute($description, $amount, $type, $invoice);
603 my $accountop = $dbh->{'mysql_insertid'};
604 $sth = $dbh->prepare("SELECT * FROM dailyaccountbalance WHERE balanceDate = CURRENT_DATE()");
607 $sth = $dbh->prepare("SELECT currentBalanceInHand FROM dailyaccountbalance ORDER BY balanceDate DESC LIMIT 1");
609 my ($blc) = $sth->fetchrow_array;
610 unless ($blc) {$blc = 0}
611 $sth = $dbh->prepare("INSERT INTO dailyaccountbalance (balanceDate, initialBalanceInHand, currentBalanceInHand) VALUES (CURRENT_DATE(), ?, ?)");
612 $sth->execute($blc, $blc);
615 $amount = -1 * $amount;
617 $sth = $dbh->prepare("UPDATE dailyaccountbalance SET currentBalanceInHand = currentBalanceInHand + ? WHERE balanceDate = CURRENT_DATE()");
618 $sth->execute($amount);
622 sub getDailyAccountOp {
624 my $dbh = C4::Context->dbh;
627 $sth = $dbh->prepare("SELECT * FROM dailyaccount WHERE date = ?");
628 $sth->execute($date);
630 $sth = $dbh->prepare("SELECT * FROM dailyaccount WHERE date = CURRENT_DATE()");
635 while (my $row = $sth->fetchrow_hashref) {
636 $row->{'num'} = $count++;
637 $row->{$row->{'type'}} = 1;
639 $row->{'invoice'} =~ /(\w*)\-(\w*)\-(\w*)/;
640 $row->{'invoiceNumber'} = $1;
641 $row->{'invoiceSupplier'} = $2;
642 $row->{'invoiceType'} = $3;
644 push @operations, $row;
646 return (scalar(@operations), \@operations);
649 END { } # module clean-up code here (global destructor)