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
28 use C4::Circulation::Circ2;
29 use vars qw($VERSION @ISA @EXPORT);
31 # set the version for version checking
32 $VERSION = do { my @v = '$Revision$' =~ /\d+/g;
33 shift(@v) . "." . join("_", map {sprintf "%03d", $_ } @v); };
37 C4::Accounts - Functions for dealing with Koha accounts
45 The functions in this module deal with the monetary aspect of Koha,
46 including looking up and modifying the amount of money owed by a
56 @EXPORT = qw(&checkaccount &recordpayment &fixaccounts &makepayment &manualinvoice
57 &getnextacctno &reconcileaccount);
61 $owed = &checkaccount($env, $borrowernumber, $dbh, $date);
63 Looks up the total amount of money owed by a borrower (fines, etc.).
65 C<$borrowernumber> specifies the borrower to look up.
67 C<$dbh> is a DBI::db handle for the Koha database.
75 #check accounts and list amounts owing
76 my ($env,$bornumber,$dbh,$date)=@_;
77 my $select="SELECT SUM(amountoutstanding) AS total
79 WHERE borrowernumber = ?
80 AND amountoutstanding<>0";
81 my @bind = ($bornumber);
82 if ($date && $date ne ''){
83 $select.=" AND date < ?";
87 my $sth=$dbh->prepare($select);
89 my $data=$sth->fetchrow_hashref;
90 my $total = $data->{'total'} || 0;
92 # output(1,2,"borrower owes $total");
94 # # output(1,2,"borrower owes $total");
96 # reconcileaccount($env,$dbh,$bornumber,$total);
105 &recordpayment($env, $borrowernumber, $payment);
107 Record payment by a patron. C<$borrowernumber> is the patron's
108 borrower number. C<$payment> is a floating-point number, giving the
109 amount that was paid. C<$env> is a reference-to-hash;
110 C<$env-E<gt>{branchcode}> is the code of the branch where payment was
113 Amounts owed are paid off oldest first. That is, if the patron has a
114 $1 fine from Feb. 1, another $1 fine from Mar. 1, and makes a payment
115 of $1.50, then the oldest fine will be paid off in full, and $0.50
116 will be credited to the next one.
121 #here we update both the accountoffsets and the account lines
122 my ($env,$bornumber,$data)=@_;
123 warn "in accounts2.pm";
124 my $dbh = C4::Context->dbh;
127 my $branch=$env->{'branchcode'};
129 my $amountleft = $data;
131 my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
132 # get lines with outstanding amounts to offset
133 my $sth = $dbh->prepare("select * from accountlines
134 where (borrowernumber = ?) and (amountoutstanding<>0)
136 $sth->execute($bornumber);
137 # offset transactions
138 while (($accdata=$sth->fetchrow_hashref) and ($amountleft>0)){
139 if ($accdata->{'amountoutstanding'} < $amountleft) {
141 $amountleft -= $accdata->{'amountoutstanding'};
143 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
146 my $thisacct = $accdata->{accountno};
147 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
148 where (borrowernumber = ?) and (accountno=?)");
149 $usth->execute($newamtos,$bornumber,$thisacct);
151 $usth = $dbh->prepare("insert into accountoffsets
152 (borrowernumber, accountno, offsetaccount, offsetamount)
154 $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
158 my $usth = $dbh->prepare("insert into accountlines
159 (borrowernumber, accountno,date,amount,description,accounttype,amountoutstanding)
160 values (?,?,now(),?,'Payment,thanks','Pay',?)");
161 $usth->execute($bornumber,$nextaccntno,0-$data,0-$amountleft);
163 UpdateStats($env,$branch,'payment',$data,'','','',$bornumber);
169 &makepayment($borrowernumber, $acctnumber, $amount, $branchcode);
171 Records the fact that a patron has paid off the entire amount he or
174 C<$borrowernumber> is the patron's borrower number. C<$acctnumber> is
175 the account that was credited. C<$amount> is the amount paid (this is
176 only used to record the payment. It is assumed to be equal to the
177 amount owed). C<$branchcode> is the code of the branch where payment
182 # FIXME - I'm not at all sure about the above, because I don't
183 # understand what the acct* tables in the Koha database are for.
185 #here we update both the accountoffsets and 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,$branch)=@_;
190 $env{'branchcode'}=$branch;
191 my $dbh = C4::Context->dbh;
193 my $nextaccntno = getnextacctno(\%env,$bornumber,$dbh);
195 my $sth=$dbh->prepare("Select * from accountlines where borrowernumber=? and accountno=?");
196 $sth->execute($bornumber,$accountno);
197 my $data=$sth->fetchrow_hashref;
202 SET amountoutstanding = 0
203 WHERE borrowernumber = $bornumber
204 AND accountno = $accountno
209 INSERT INTO accountoffsets
210 (borrowernumber, accountno, offsetaccount,
212 VALUES ($bornumber, $accountno, $nextaccntno, $newamtos)
216 my $payment=0-$amount;
218 INSERT INTO accountlines
219 (borrowernumber, accountno, date, amount,
220 description, accounttype, amountoutstanding)
221 VALUES ($bornumber, $nextaccntno, now(), $payment,
222 'Payment,thanks - $user', 'Pay', 0)
225 # FIXME - The second argument to &UpdateStats is supposed to be the
227 # UpdateStats is now being passed $accountno too. MTJ
228 UpdateStats(\%env,$user,'payment',$amount,'','','',$bornumber,$accountno);
230 #check to see what accounttype
231 if ($data->{'accounttype'} eq 'Rep' || $data->{'accounttype'} eq 'L'){
232 returnlost($bornumber,$data->{'itemnumber'});
238 $nextacct = &getnextacctno($env, $borrowernumber, $dbh);
240 Returns the next unused account number for the patron with the given
243 C<$dbh> is a DBI::db handle to the Koha database.
249 # FIXME - Okay, so what does the above actually _mean_?
251 my ($env,$bornumber,$dbh)=@_;
253 my $sth = $dbh->prepare("select * from accountlines
254 where (borrowernumber = ?)
255 order by accountno desc");
256 $sth->execute($bornumber);
257 if (my $accdata=$sth->fetchrow_hashref){
258 $nextaccntno = $accdata->{'accountno'} + 1;
261 return($nextaccntno);
266 &fixaccounts($borrowernumber, $accountnumber, $amount);
270 # FIXME - I don't understand what this function does.
272 my ($borrowernumber,$accountno,$amount)=@_;
273 my $dbh = C4::Context->dbh;
274 my $sth=$dbh->prepare("Select * from accountlines where borrowernumber=?
276 $sth->execute($borrowernumber,$accountno);
277 my $data=$sth->fetchrow_hashref;
278 # FIXME - Error-checking
279 my $diff=$amount-$data->{'amount'};
280 my $outstanding=$data->{'amountoutstanding'}+$diff;
285 SET amount = '$amount',
286 amountoutstanding = '$outstanding'
287 WHERE borrowernumber = $borrowernumber
288 AND accountno = $accountno
292 # FIXME - Never used, but not exported, either.
294 my ($borrnum,$itemnum)=@_;
295 my $dbh = C4::Context->dbh;
296 my $borrower=borrdata('',$borrnum);
297 my $sth=$dbh->prepare("Update issues set returndate=now() where
298 borrowernumber=? and itemnumber=? and returndate is null");
299 $sth->execute($borrnum,$itemnum);
301 my @datearr = localtime(time);
302 my $date = (1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
303 my $bor="$borrower->{'firstname'} $borrower->{'surname'} $borrower->{'cardnumber'}";
304 $sth=$dbh->prepare("Update items set paidfor=? where itemnumber=?");
305 $sth->execute("Paid for by $bor $date",$itemnum);
311 &manualinvoice($borrowernumber, $itemnumber, $description, $type,
314 C<$borrowernumber> is the patron's borrower number.
315 C<$description> is a description of the transaction.
316 C<$type> may be one of C<CS>, C<CB>, C<CW>, C<CF>, C<CL>, C<N>, C<L>,
318 C<$itemnumber> is the item involved, if pertinent; otherwise, it
319 should be the empty string.
323 # FIXME - Okay, so what does this function do, really?
325 my ($bornum,$itemnum,$desc,$type,$amount,$user)=@_;
326 my $dbh = C4::Context->dbh;
330 my $accountno=getnextacctno('',$bornum,$dbh);
331 my $amountleft=$amount;
333 if ($type eq 'CS' || $type eq 'CB' || $type eq 'CW'
334 || $type eq 'CF' || $type eq 'CL'){
335 my $amount2=$amount*-1; # FIXME - $amount2 = -$amount
336 $amountleft=fixcredit(\%env,$bornum,$amount2,$itemnum,$type,$user);
341 if ($type eq 'L' && $desc eq ''){
345 $amountleft=refund('',$bornum,$amount);
349 my $sth=$dbh->prepare("INSERT INTO accountlines
350 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding, itemnumber)
351 VALUES (?, ?, now(), ?,?, ?,?,?)");
352 # $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft, $data->{'itemnumber'});
353 $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft, $itemnum);
355 my $sth=$dbh->prepare("INSERT INTO accountlines
356 (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding)
357 VALUES (?, ?, now(), ?, ?, ?, ?)");
358 $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft);
363 # $amountleft = &fixcredit($env, $bornumber, $data, $barcode, $type, $user);
365 # This function is only used internally.
366 # FIXME - Figure out what this function does, and write it down.
368 #here we update both the accountoffsets and the account lines
369 my ($env,$bornumber,$data,$barcode,$type,$user)=@_;
370 my $dbh = C4::Context->dbh;
373 my $amountleft = $data;
375 my $item=getiteminformation($env,'',$barcode);
376 my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
377 my $query="Select * from accountlines where (borrowernumber=?
378 and itemnumber=? and amountoutstanding > 0)";
380 $query.=" and (accounttype = 'L' or accounttype = 'Rep')";
381 } elsif ($type eq 'CF'){
382 $query.=" and (accounttype = 'F' or accounttype = 'FU' or
383 accounttype='Res' or accounttype='Rent')";
384 } elsif ($type eq 'CB'){
385 $query.=" and accounttype='A'";
388 my $sth=$dbh->prepare($query);
389 $sth->execute($bornumber,$item->{'itemnumber'});
390 $accdata=$sth->fetchrow_hashref;
392 if ($accdata->{'amountoutstanding'} < $amountleft) {
394 $amountleft -= $accdata->{'amountoutstanding'};
396 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
399 my $thisacct = $accdata->{accountno};
400 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
401 where (borrowernumber = ?) and (accountno=?)");
402 $usth->execute($newamtos,$bornumber,$thisacct);
404 $usth = $dbh->prepare("insert into accountoffsets
405 (borrowernumber, accountno, offsetaccount, offsetamount)
407 $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
411 my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
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->{accountno};
428 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
429 where (borrowernumber = ?) and (accountno=?)");
430 $usth->execute($newamtos,$bornumber,$thisacct);
432 $usth = $dbh->prepare("insert into accountoffsets
433 (borrowernumber, accountno, offsetaccount, offsetamount)
435 $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
439 $env->{'branch'}=$user;
440 $type="Credit ".$type;
441 UpdateStats($env,$user,$type,$data,$user,'','',$bornumber);
447 # FIXME - Figure out what this function does, and write it down.
449 #here we update both the accountoffsets and the account lines
450 my ($env,$bornumber,$data)=@_;
451 my $dbh = C4::Context->dbh;
454 # my $branch=$env->{'branchcode'};
455 my $amountleft = $data *-1;
458 my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
459 # get lines with outstanding amounts to offset
460 my $sth = $dbh->prepare("select * from accountlines
461 where (borrowernumber = ?) and (amountoutstanding<0)
463 $sth->execute($bornumber);
465 # offset transactions
466 while (($accdata=$sth->fetchrow_hashref) and ($amountleft<0)){
467 if ($accdata->{'amountoutstanding'} > $amountleft) {
469 $amountleft -= $accdata->{'amountoutstanding'};
471 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
475 my $thisacct = $accdata->{accountno};
476 my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
477 where (borrowernumber = ?) and (accountno=?)");
478 $usth->execute($newamtos,$bornumber,$thisacct);
480 $usth = $dbh->prepare("insert into accountoffsets
481 (borrowernumber, accountno, offsetaccount, offsetamount)
483 $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
491 END { } # module clean-up code here (global destructor)