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->{accountid};
146 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
148 $usth->execute($newamtos,$thisacct);
152 my $usth = $dbh->prepare("insert into accountlines
153 (borrowernumber, accountno,date,amount,description,accounttype,amountoutstanding)
154 values (?,?,now(),?,'Payment,thanks','Pay',?)");
155 $usth->execute($bornumber,$nextaccntno,0-$data,0-$amountleft);
157 # UpdateStats($env,$branch,'payment',$data,'','','',$bornumber);
163 &makepayment($borrowernumber, $acctnumber, $amount, $branchcode);
165 Records the fact that a patron has paid off the an amount he or
168 C<$borrowernumber> is the patron's borrower number. C<$acctnumber> is
169 the account that was credited. C<$amount> is the amount paid (this is
170 only used to record the payment. C<$branchcode> is the code of the branch where payment
175 # FIXME - I'm not at all sure about the above, because I don't
176 # understand what the acct* tables in the Koha database are for.
179 #here we update the account lines
180 #updated to check, if they are paying off a lost item, we return the item
181 # from their card, and put a note on the item record
182 my ($bornumber,$accountno,$amount,$user,$type)=@_;
187 $desc="Payment,received by -". $user;
190 $desc="Written-off -by". $user;
193 my $dbh = C4::Context->dbh;
195 my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
197 my $sth=$dbh->prepare("Select * from accountlines where borrowernumber=? and accountno=?");
198 $sth->execute($bornumber,$accountno);
199 my $data=$sth->fetchrow_hashref;
204 SET amountoutstanding = amountoutstanding-$amount
205 WHERE borrowernumber = $bornumber
206 AND accountno = $accountno
212 my $payment=0-$amount;
213 if ($data->{'itemnumber'}){
214 $desc.=" ".$data->{'itemnumber'};
217 INSERT INTO accountlines
218 (borrowernumber, accountno, itemnumber,date, amount,
219 description, accounttype, amountoutstanding,offset)
220 VALUES ($bornumber, $nextaccntno, $data->{'itemnumber'},now(), $payment,
221 '$desc', '$pay', 0,$accountno)
225 INSERT INTO accountlines
226 (borrowernumber, accountno, date, amount,
227 description, accounttype, amountoutstanding,offset)
228 VALUES ($bornumber, $nextaccntno, now(), $payment,
229 '$desc', '$pay', 0,$accountno)
233 # FIXME - The second argument to &UpdateStats is supposed to be the
235 # UpdateStats($env,'MAIN',$pay,$amount,'','','',$bornumber);
237 #check to see what accounttype
238 if ($data->{'accounttype'} eq 'Rep' || $data->{'accounttype'} eq 'L'){
239 returnlost($bornumber,$data->{'itemnumber'});
245 $nextacct = &getnextacctno($env, $borrowernumber, $dbh);
247 Returns the next unused account number for the patron with the given
250 C<$dbh> is a DBI::db handle to the Koha database.
256 # FIXME - Okay, so what does the above actually _mean_?
258 my ($env,$bornumber,$dbh)=@_;
260 my $sth = $dbh->prepare("select * from accountlines
261 where (borrowernumber = ?)
262 order by accountno desc");
263 $sth->execute($bornumber);
264 if (my $accdata=$sth->fetchrow_hashref){
265 $nextaccntno = $accdata->{'accountno'} + 1;
268 return($nextaccntno);
273 &fixaccounts($borrowernumber, $accountnumber, $amount);
277 # FIXME - I don't know whether used
279 my ($borrowernumber,$accountno,$amount)=@_;
280 my $dbh = C4::Context->dbh;
281 my $sth=$dbh->prepare("Select * from accountlines where borrowernumber=?
283 $sth->execute($borrowernumber,$accountno);
284 my $data=$sth->fetchrow_hashref;
285 # FIXME - Error-checking
286 my $diff=$amount-$data->{'amount'};
287 my $outstanding=$data->{'amountoutstanding'}+$diff;
292 SET amount = '$amount',
293 amountoutstanding = '$outstanding'
294 WHERE borrowernumber = $borrowernumber
295 AND accountno = $accountno
299 # FIXME - Never used, but not exported, either.
301 my ($borrnum,$itemnum)=@_;
302 my $dbh = C4::Context->dbh;
303 my $borrower=C4::Members::borrdata('',$borrnum); #from C4::Members;
304 my $sth=$dbh->prepare("Update issues set returndate=now() where
305 borrowernumber=? and itemnumber=? and returndate is null");
306 $sth->execute($borrnum,$itemnum);
312 &manualinvoice($borrowernumber, $description, $type,
315 C<$borrowernumber> is the patron's borrower number.
316 C<$description> is a description of the transaction.
317 C<$type> may be one of C<CS>, C<CB>, C<CW>, C<CF>, C<CL>, C<N>, C<L>,
325 my ($bornum,$desc,$type,$amount,$user)=@_;
326 my $dbh = C4::Context->dbh;
329 my $accountno=getnextacctno('',$bornum,$dbh);
330 my $amountleft=$amount;
337 if ($type eq 'L' && $desc eq ''){
343 $amountleft=refund('',$bornum,$amount);
344 my $sth=$dbh->prepare("INSERT INTO accountlines
345 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding)
346 VALUES (?, ?, now(), ?, ?, ?, ?)");
347 $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft);
352 my ($bornum,$accountid,$desc,$type,$amount,$user,$oldaccount)=@_;
353 my $dbh = C4::Context->dbh;
355 my $accountno=getnextacctno('',$bornum,$dbh);
356 # my $amountleft=$amount;
359 if ($type eq 'CN' || $type eq 'CA' || $type eq 'CR'
360 || $type eq 'CF' || $type eq 'CL' || $type eq 'CM'){
361 my $amount2=$amount*-1;
362 ( $amountleft, $noerror,$oldaccount)=fixcredit($dbh,$bornum,$amount2,$accountid,$type,$user);
366 ## find the accountline desc
367 my $sth2=$dbh->prepare("select description from accountlines where accountid=?");
368 $sth2->execute($accountid);
369 my $desc2=$sth2->fetchrow;
370 $desc.=" Credited for ".$desc2." by ".$user;
373 my $sth=$dbh->prepare("INSERT INTO accountlines
374 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding,offset)
375 VALUES (?, ?, now(), ?, ?, ?, ?,?)");
376 $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft,$oldaccount);
385 #here we update both the accountoffsets and the account lines
386 my ($dbh,$bornumber,$data,$accountid,$type,$user)=@_;
389 my $amountleft = $data;
391 my $query="Select * from accountlines where accountid=? and amountoutstanding > 0";
392 my $sth=$dbh->prepare($query);
393 $sth->execute($accountid);
394 $accdata=$sth->fetchrow_hashref;
398 if ($accdata->{'amountoutstanding'} < $amountleft) {
400 $amountleft -= $accdata->{'amountoutstanding'};
402 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
405 my $thisacct = $accdata->{accountid};
406 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
408 $usth->execute($newamtos,$thisacct);
412 # get lines with outstanding amounts to offset
413 my $sth = $dbh->prepare("select * from accountlines
414 where (borrowernumber = ?) and (amountoutstanding >0)
416 $sth->execute($bornumber);
418 # offset transactions
419 while (($accdata=$sth->fetchrow_hashref) and ($amountleft>0)){
420 if ($accdata->{'amountoutstanding'} < $amountleft) {
422 $amountleft -= $accdata->{'amountoutstanding'};
424 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
427 my $thisacct = $accdata->{accountid};
428 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
430 $usth->execute($newamtos,$thisacct);
436 return($amountleft,1,$accdata->{'accountno'});
445 #here we update both the accountoffsets and the account lines
446 my ($env,$bornumber,$data)=@_;
447 my $dbh = C4::Context->dbh;
450 # my $branch=$env->{'branchcode'};
451 my $amountleft = $data *-1;
454 # get lines with outstanding amounts to offset
455 my $sth = $dbh->prepare("select * from accountlines
456 where (borrowernumber = ?) and (amountoutstanding<0)
458 $sth->execute($bornumber);
460 # offset transactions
461 while (($accdata=$sth->fetchrow_hashref) and ($amountleft<0)){
462 if ($accdata->{'amountoutstanding'} > $amountleft) {
464 $amountleft -= $accdata->{'amountoutstanding'};
466 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
470 my $thisacct = $accdata->{accountid};
471 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
473 $usth->execute($newamtos,$thisacct);
478 return($amountleft*-1);
481 #Funtion to manage the daily account#
483 sub dailyAccountBalance {
485 my $dbh = C4::Context->dbh;
490 $sth = $dbh->prepare("SELECT * FROM dailyaccountbalance WHERE balanceDate = ?");
491 $sth->execute($date);
492 my $data = $sth->fetchrow_hashref;
493 if (!$data->{'balanceDate'}) {
494 $data->{'noentry'} = 1;
500 $sth = $dbh->prepare("SELECT * FROM dailyaccountbalance WHERE balanceDate = CURRENT_DATE()");
504 return ($sth->fetchrow_hashref);
508 $sth = $dbh->prepare("SELECT currentBalanceInHand FROM dailyaccountbalance ORDER BY balanceDate DESC LIMIT 1");
511 ($hash{'initialBalanceInHand'}) = $sth->fetchrow_array;
512 $hash{'currentBalanceInHand'} = $hash{'initialBalanceInHand'};
514 $hash{'initialBalanceInHand'} = 0;
515 $hash{'currentBalanceInHand'} = 0;
517 #gets the current date.
518 my @nowarr = localtime();
519 my $date = (1900+$nowarr[5])."-".($nowarr[4]+1)."-".$nowarr[3];
521 $hash{'balanceDate'} = $date;
522 $hash{'initialBalanceInHand'} = sprintf ("%.2f", $hash{'initialBalanceInHand'});
523 $hash{'currentBalanceInHand'} = sprintf ("%.2f", $hash{'currentBalanceInHand'});
530 sub addDailyAccountOp {
531 my ($description, $amount, $type, $invoice) = @_;
532 my $dbh = C4::Context->dbh;
533 unless ($invoice) { $invoice = undef};
534 my $sth = $dbh->prepare("INSERT INTO dailyaccount (date, description, amount, type, invoice) VALUES (CURRENT_DATE(), ?, ?, ?, ?)");
535 $sth->execute($description, $amount, $type, $invoice);
536 my $accountop = $dbh->{'mysql_insertid'};
537 $sth = $dbh->prepare("SELECT * FROM dailyaccountbalance WHERE balanceDate = CURRENT_DATE()");
540 $sth = $dbh->prepare("SELECT currentBalanceInHand FROM dailyaccountbalance ORDER BY balanceDate DESC LIMIT 1");
542 my ($blc) = $sth->fetchrow_array;
543 unless ($blc) {$blc = 0}
544 $sth = $dbh->prepare("INSERT INTO dailyaccountbalance (balanceDate, initialBalanceInHand, currentBalanceInHand) VALUES (CURRENT_DATE(), ?, ?)");
545 $sth->execute($blc, $blc);
548 $amount = -1 * $amount;
550 $sth = $dbh->prepare("UPDATE dailyaccountbalance SET currentBalanceInHand = currentBalanceInHand + ? WHERE balanceDate = CURRENT_DATE()");
551 $sth->execute($amount);
555 sub getDailyAccountOp {
557 my $dbh = C4::Context->dbh;
560 $sth = $dbh->prepare("SELECT * FROM dailyaccount WHERE date = ?");
561 $sth->execute($date);
563 $sth = $dbh->prepare("SELECT * FROM dailyaccount WHERE date = CURRENT_DATE()");
568 while (my $row = $sth->fetchrow_hashref) {
569 $row->{'num'} = $count++;
570 $row->{$row->{'type'}} = 1;
572 $row->{'invoice'} =~ /(\w*)\-(\w*)\-(\w*)/;
573 $row->{'invoiceNumber'} = $1;
574 $row->{'invoiceSupplier'} = $2;
575 $row->{'invoiceType'} = $3;
577 push @operations, $row;
579 return (scalar(@operations), \@operations);
582 END { } # module clean-up code here (global destructor)