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
27 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 &reconcileaccount);
60 $owed = &checkaccount($env, $borrowernumber, $dbh, $date);
62 Looks up the total amount of money owed by a borrower (fines, etc.).
64 C<$borrowernumber> specifies the borrower to look up.
66 C<$dbh> is a DBI::db handle for the Koha database.
74 #check accounts and list amounts owing
75 my ($env,$bornumber,$dbh,$date)=@_;
76 my $select="SELECT SUM(amountoutstanding) AS total
78 WHERE borrowernumber = ?
79 AND amountoutstanding<>0";
80 my @bind = ($bornumber);
81 if ($date && $date ne ''){
82 $select.=" AND date < ?";
86 my $sth=$dbh->prepare($select);
88 my $data=$sth->fetchrow_hashref;
89 my $total = $data->{'total'} || 0;
91 # output(1,2,"borrower owes $total");
93 # # output(1,2,"borrower owes $total");
95 # reconcileaccount($env,$dbh,$bornumber,$total);
104 &recordpayment($env, $borrowernumber, $payment);
106 Record payment by a patron. C<$borrowernumber> is the patron's
107 borrower number. C<$payment> is a floating-point number, giving the
108 amount that was paid. C<$env> is a reference-to-hash;
109 C<$env-E<gt>{branchcode}> is the code of the branch where payment was
112 Amounts owed are paid off oldest first. That is, if the patron has a
113 $1 fine from Feb. 1, another $1 fine from Mar. 1, and makes a payment
114 of $1.50, then the oldest fine will be paid off in full, and $0.50
115 will be credited to the next one.
120 #here we update both the accountoffsets and the account lines
121 my ($env,$bornumber,$data)=@_;
122 warn "in accounts2.pm";
123 my $dbh = C4::Context->dbh;
126 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)
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.
184 #here we update both the accountoffsets and the account lines
185 #updated to check, if they are paying off a lost item, we return the item
186 # from their card, and put a note on the item record
187 my ($bornumber,$accountno,$amount,$user,$branch)=@_;
189 $env{'branchcode'}=$branch;
190 my $dbh = C4::Context->dbh;
192 my $nextaccntno = getnextacctno(\%env,$bornumber,$dbh);
194 my $sth=$dbh->prepare("Select * from accountlines where borrowernumber=? and accountno=?");
195 $sth->execute($bornumber,$accountno);
196 my $data=$sth->fetchrow_hashref;
201 SET amountoutstanding = 0
202 WHERE borrowernumber = $bornumber
203 AND accountno = $accountno
208 INSERT INTO accountoffsets
209 (borrowernumber, accountno, offsetaccount,
211 VALUES ($bornumber, $accountno, $nextaccntno, $newamtos)
215 my $payment=0-$amount;
217 INSERT INTO accountlines
218 (borrowernumber, accountno, date, amount,
219 description, accounttype, amountoutstanding)
220 VALUES ($bornumber, $nextaccntno, now(), $payment,
221 'Payment,thanks - $user', 'Pay', 0)
224 # FIXME - The second argument to &UpdateStats is supposed to be the
226 # UpdateStats is now being passed $accountno too. MTJ
227 UpdateStats(\%env,$user,'payment',$amount,'','','',$bornumber,$accountno);
229 #check to see what accounttype
230 if ($data->{'accounttype'} eq 'Rep' || $data->{'accounttype'} eq 'L'){
231 returnlost($bornumber,$data->{'itemnumber'});
237 $nextacct = &getnextacctno($env, $borrowernumber, $dbh);
239 Returns the next unused account number for the patron with the given
242 C<$dbh> is a DBI::db handle to the Koha database.
248 # FIXME - Okay, so what does the above actually _mean_?
250 my ($env,$bornumber,$dbh)=@_;
252 my $sth = $dbh->prepare("select * from accountlines
253 where (borrowernumber = ?)
254 order by accountno desc");
255 $sth->execute($bornumber);
256 if (my $accdata=$sth->fetchrow_hashref){
257 $nextaccntno = $accdata->{'accountno'} + 1;
260 return($nextaccntno);
265 &fixaccounts($borrowernumber, $accountnumber, $amount);
269 # FIXME - I don't understand what this function does.
271 my ($borrowernumber,$accountno,$amount)=@_;
272 my $dbh = C4::Context->dbh;
273 my $sth=$dbh->prepare("Select * from accountlines where borrowernumber=?
275 $sth->execute($borrowernumber,$accountno);
276 my $data=$sth->fetchrow_hashref;
277 # FIXME - Error-checking
278 my $diff=$amount-$data->{'amount'};
279 my $outstanding=$data->{'amountoutstanding'}+$diff;
284 SET amount = '$amount',
285 amountoutstanding = '$outstanding'
286 WHERE borrowernumber = $borrowernumber
287 AND accountno = $accountno
291 # FIXME - Never used, but not exported, either.
293 my ($borrnum,$itemnum)=@_;
294 my $dbh = C4::Context->dbh;
295 my $borrower=borrdata('',$borrnum);
296 my $sth=$dbh->prepare("Update issues set returndate=now() where
297 borrowernumber=? and itemnumber=? and returndate is null");
298 $sth->execute($borrnum,$itemnum);
300 my @datearr = localtime(time);
301 my $date = (1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
302 my $bor="$borrower->{'firstname'} $borrower->{'surname'} $borrower->{'cardnumber'}";
303 $sth=$dbh->prepare("Update items set paidfor=? where itemnumber=?");
304 $sth->execute("Paid for by $bor $date",$itemnum);
310 &manualinvoice($borrowernumber, $itemnumber, $description, $type,
313 C<$borrowernumber> is the patron's borrower number.
314 C<$description> is a description of the transaction.
315 C<$type> may be one of C<CS>, C<CB>, C<CW>, C<CF>, C<CL>, C<N>, C<L>,
317 C<$itemnumber> is the item involved, if pertinent; otherwise, it
318 should be the empty string.
322 # FIXME - Okay, so what does this function do, really?
324 my ($bornum,$itemnum,$desc,$type,$amount,$user)=@_;
325 my $dbh = C4::Context->dbh;
329 my $accountno=getnextacctno('',$bornum,$dbh);
330 my $amountleft=$amount;
332 if ($type eq 'CS' || $type eq 'CB' || $type eq 'CW'
333 || $type eq 'CF' || $type eq 'CL'){
334 my $amount2=$amount*-1; # FIXME - $amount2 = -$amount
335 $amountleft=fixcredit(\%env,$bornum,$amount2,$itemnum,$type,$user);
340 if ($type eq 'L' && $desc eq ''){
344 $amountleft=refund('',$bornum,$amount);
347 #FIXME to use ? before uncommenting
348 # my $sth=$dbh->prepare("Select * from items where barcode='$itemnum'");
350 # my $data=$sth->fetchrow_hashref;
353 my $sth=$dbh->prepare("INSERT INTO accountlines
354 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding, itemnumber)
355 VALUES (?, ?, now(), ?,?, ?,?,?)");
356 # $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft, $data->{'itemnumber'});
357 $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft, $itemnum);
359 $desc=$dbh->quote($desc);
360 my $sth=$dbh->prepare("INSERT INTO accountlines
361 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding)
362 VALUES (?, ?, now(), ?, ?, ?, ?)");
363 $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft);
368 # $amountleft = &fixcredit($env, $bornumber, $data, $barcode, $type, $user);
370 # This function is only used internally.
371 # FIXME - Figure out what this function does, and write it down.
373 #here we update both the accountoffsets and the account lines
374 my ($env,$bornumber,$data,$barcode,$type,$user)=@_;
375 my $dbh = C4::Context->dbh;
378 my $amountleft = $data;
380 my $item=getiteminformation($env,'',$barcode);
381 my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
382 my $query="Select * from accountlines where (borrowernumber=?
383 and itemnumber=? and amountoutstanding > 0)";
385 $query.=" and (accounttype = 'L' or accounttype = 'Rep')";
386 } elsif ($type eq 'CF'){
387 $query.=" and (accounttype = 'F' or accounttype = 'FU' or
388 accounttype='Res' or accounttype='Rent')";
389 } elsif ($type eq 'CB'){
390 $query.=" and accounttype='A'";
393 my $sth=$dbh->prepare($query);
394 $sth->execute($bornumber,$item->{'itemnumber'});
395 $accdata=$sth->fetchrow_hashref;
397 if ($accdata->{'amountoutstanding'} < $amountleft) {
399 $amountleft -= $accdata->{'amountoutstanding'};
401 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
404 my $thisacct = $accdata->{accountno};
405 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
406 where (borrowernumber = ?) and (accountno=?)");
407 $usth->execute($newamtos,$bornumber,$thisacct);
409 $usth = $dbh->prepare("insert into accountoffsets
410 (borrowernumber, accountno, offsetaccount, offsetamount)
412 $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
416 my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
417 # get lines with outstanding amounts to offset
418 my $sth = $dbh->prepare("select * from accountlines
419 where (borrowernumber = ?) and (amountoutstanding >0)
421 $sth->execute($bornumber);
423 # offset transactions
424 while (($accdata=$sth->fetchrow_hashref) and ($amountleft>0)){
425 if ($accdata->{'amountoutstanding'} < $amountleft) {
427 $amountleft -= $accdata->{'amountoutstanding'};
429 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
432 my $thisacct = $accdata->{accountno};
433 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
434 where (borrowernumber = ?) and (accountno=?)");
435 $usth->execute($newamtos,$bornumber,$thisacct);
437 $usth = $dbh->prepare("insert into accountoffsets
438 (borrowernumber, accountno, offsetaccount, offsetamount)
440 $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
444 $env->{'branch'}=$user;
445 $type="Credit ".$type;
446 UpdateStats($env,$user,$type,$data,$user,'','',$bornumber);
452 # FIXME - Figure out what this function does, and write it down.
454 #here we update both the accountoffsets and the account lines
455 my ($env,$bornumber,$data)=@_;
456 my $dbh = C4::Context->dbh;
459 # my $branch=$env->{'branchcode'};
460 my $amountleft = $data *-1;
463 my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
464 # get lines with outstanding amounts to offset
465 my $sth = $dbh->prepare("select * from accountlines
466 where (borrowernumber = ?) and (amountoutstanding<0)
468 $sth->execute($bornumber);
470 # offset transactions
471 while (($accdata=$sth->fetchrow_hashref) and ($amountleft<0)){
472 if ($accdata->{'amountoutstanding'} > $amountleft) {
474 $amountleft -= $accdata->{'amountoutstanding'};
476 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
480 my $thisacct = $accdata->{accountno};
481 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
482 where (borrowernumber = ?) and (accountno=?)");
483 $usth->execute($newamtos,$bornumber,$thisacct);
485 $usth = $dbh->prepare("insert into accountoffsets
486 (borrowernumber, accountno, offsetaccount, offsetamount)
488 $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
496 END { } # module clean-up code here (global destructor)