X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=C4%2FAccounts.pm;h=e46e39df4a7c63d4cbbef3094658e9bfabcbf51f;hb=3de59aba2776184a6fd2e9943d86a7c66e8f7648;hp=e385443899eb4f7c6a01427eb298f1bce272c5cd;hpb=e1353fb639f4d4755e8172f5d5e59b3ae239f23d;p=koha_gimpoz diff --git a/C4/Accounts.pm b/C4/Accounts.pm old mode 100755 new mode 100644 index e385443899..e46e39df4a --- a/C4/Accounts.pm +++ b/C4/Accounts.pm @@ -17,19 +17,27 @@ package C4::Accounts; # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place, # Suite 330, Boston, MA 02111-1307 USA -# $Id$ use strict; -require Exporter; use C4::Context; use C4::Stats; use C4::Members; +use C4::Items; + #use C4::Circulation; use vars qw($VERSION @ISA @EXPORT); -# set the version for version checking -$VERSION = do { my @v = '$Revision$' =~ /\d+/g; -shift(@v) . "." . join("_", map {sprintf "%03d", $_ } @v); }; +BEGIN { + # set the version for version checking + $VERSION = 3.02; + require Exporter; + @ISA = qw(Exporter); + @EXPORT = qw( + &recordpayment &fixaccounts &makepayment &manualinvoice + &getnextacctno &reconcileaccount &getcharges &getcredits + &getrefunds + ); +} =head1 NAME @@ -47,13 +55,6 @@ patron. =head1 FUNCTIONS -=cut - -@ISA = qw(Exporter); -@EXPORT = qw(&recordpayment &fixaccounts &makepayment &manualinvoice -&getnextacctno &reconcileaccount); - - =head2 recordpayment &recordpayment($borrowernumber, $payment); @@ -70,49 +71,64 @@ will be credited to the next one. =cut #' -sub recordpayment{ - #here we update both the accountoffsets and the account lines - my ($borrowernumber,$data)=@_; - my $dbh = C4::Context->dbh; - my $newamtos = 0; - my $accdata = ""; - my $branch=C4::Context->userenv->{'branch'}; - my $amountleft = $data; - # begin transaction - my $nextaccntno = getnextacctno($borrowernumber); - # get lines with outstanding amounts to offset - my $sth = $dbh->prepare("SELECT * FROM accountlines +sub recordpayment { + + #here we update both the accountoffsets and the account lines + my ( $borrowernumber, $data ) = @_; + my $dbh = C4::Context->dbh; + my $newamtos = 0; + my $accdata = ""; + my $branch = C4::Context->userenv->{'branch'}; + my $amountleft = $data; + + # begin transaction + my $nextaccntno = getnextacctno($borrowernumber); + + # get lines with outstanding amounts to offset + my $sth = $dbh->prepare( + "SELECT * FROM accountlines WHERE (borrowernumber = ?) AND (amountoutstanding<>0) - ORDER BY date"); - $sth->execute($borrowernumber); - # offset transactions - while (($accdata=$sth->fetchrow_hashref) and ($amountleft>0)){ - if ($accdata->{'amountoutstanding'} < $amountleft) { - $newamtos = 0; - $amountleft -= $accdata->{'amountoutstanding'}; - } else { - $newamtos = $accdata->{'amountoutstanding'} - $amountleft; - $amountleft = 0; - } - my $thisacct = $accdata->{accountno}; - my $usth = $dbh->prepare("UPDATE accountlines SET amountoutstanding= ? - WHERE (borrowernumber = ?) AND (accountno=?)"); - $usth->execute($newamtos,$borrowernumber,$thisacct); - $usth->finish; - $usth = $dbh->prepare("INSERT INTO accountoffsets + ORDER BY date" + ); + $sth->execute($borrowernumber); + + # offset transactions + while ( ( $accdata = $sth->fetchrow_hashref ) and ( $amountleft > 0 ) ) { + if ( $accdata->{'amountoutstanding'} < $amountleft ) { + $newamtos = 0; + $amountleft -= $accdata->{'amountoutstanding'}; + } + else { + $newamtos = $accdata->{'amountoutstanding'} - $amountleft; + $amountleft = 0; + } + my $thisacct = $accdata->{accountno}; + my $usth = $dbh->prepare( + "UPDATE accountlines SET amountoutstanding= ? + WHERE (borrowernumber = ?) AND (accountno=?)" + ); + $usth->execute( $newamtos, $borrowernumber, $thisacct ); + $usth->finish; + $usth = $dbh->prepare( + "INSERT INTO accountoffsets (borrowernumber, accountno, offsetaccount, offsetamount) - VALUES (?,?,?,?)"); - $usth->execute($borrowernumber,$accdata->{'accountno'},$nextaccntno,$newamtos); - $usth->finish; - } - # create new line - my $usth = $dbh->prepare("INSERT INTO accountlines + VALUES (?,?,?,?)" + ); + $usth->execute( $borrowernumber, $accdata->{'accountno'}, + $nextaccntno, $newamtos ); + $usth->finish; + } + + # create new line + my $usth = $dbh->prepare( + "INSERT INTO accountlines (borrowernumber, accountno,date,amount,description,accounttype,amountoutstanding) - VALUES (?,?,now(),?,'Payment,thanks','Pay',?)"); - $usth->execute($borrowernumber,$nextaccntno,0-$data,0-$amountleft); - $usth->finish; - UpdateStats($branch,'payment',$data,'','','',$borrowernumber); - $sth->finish; + VALUES (?,?,now(),?,'Payment,thanks','Pay',?)" + ); + $usth->execute( $borrowernumber, $nextaccntno, 0 - $data, 0 - $amountleft ); + $usth->finish; + UpdateStats( $branch, 'payment', $data, '', '', '', $borrowernumber, $nextaccntno ); + $sth->finish; } =head2 makepayment @@ -133,53 +149,61 @@ was made. #' # FIXME - I'm not at all sure about the above, because I don't # understand what the acct* tables in the Koha database are for. -sub makepayment{ - #here we update both the accountoffsets and the account lines - #updated to check, if they are paying off a lost item, we return the item - # from their card, and put a note on the item record - my ($borrowernumber,$accountno,$amount,$user,$branch)=@_; - my $dbh = C4::Context->dbh; - # begin transaction - my $nextaccntno = getnextacctno($borrowernumber); - my $newamtos=0; - my $sth=$dbh->prepare("SELECT * FROM accountlines WHERE borrowernumber=? AND accountno=?"); - $sth->execute($borrowernumber,$accountno); - my $data=$sth->fetchrow_hashref; - $sth->finish; - - $dbh->do("UPDATE accountlines +sub makepayment { + + #here we update both the accountoffsets and the account lines + #updated to check, if they are paying off a lost item, we return the item + # from their card, and put a note on the item record + my ( $borrowernumber, $accountno, $amount, $user, $branch ) = @_; + my $dbh = C4::Context->dbh; + + # begin transaction + my $nextaccntno = getnextacctno($borrowernumber); + my $newamtos = 0; + my $sth = + $dbh->prepare( + "SELECT * FROM accountlines WHERE borrowernumber=? AND accountno=?"); + $sth->execute( $borrowernumber, $accountno ); + my $data = $sth->fetchrow_hashref; + $sth->finish; + + $dbh->do( + "UPDATE accountlines SET amountoutstanding = 0 WHERE borrowernumber = $borrowernumber AND accountno = $accountno - "); + " + ); -# print $updquery; - $dbh->do(" + # print $updquery; + $dbh->do( " INSERT INTO accountoffsets (borrowernumber, accountno, offsetaccount, offsetamount) VALUES ($borrowernumber, $accountno, $nextaccntno, $newamtos) - "); + " ); - # create new line - my $payment=0-$amount; - $dbh->do(" + # create new line + my $payment = 0 - $amount; + $dbh->do( " INSERT INTO accountlines (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding) VALUES ($borrowernumber, $nextaccntno, now(), $payment, 'Payment,thanks - $user', 'Pay', 0) - "); - - # FIXME - The second argument to &UpdateStats is supposed to be the - # branch code. - # UpdateStats is now being passed $accountno too. MTJ - UpdateStats($user,'payment',$amount,'','','',$borrowernumber,$accountno); - $sth->finish; - #check to see what accounttype - if ($data->{'accounttype'} eq 'Rep' || $data->{'accounttype'} eq 'L'){ - returnlost($borrowernumber,$data->{'itemnumber'}); - } + " ); + + # FIXME - The second argument to &UpdateStats is supposed to be the + # branch code. + # UpdateStats is now being passed $accountno too. MTJ + UpdateStats( $user, 'payment', $amount, '', '', '', $borrowernumber, + $accountno ); + $sth->finish; + + #check to see what accounttype + if ( $data->{'accounttype'} eq 'Rep' || $data->{'accounttype'} eq 'L' ) { + returnlost( $borrowernumber, $data->{'itemnumber'} ); + } } =head2 getnextacctno @@ -194,18 +218,20 @@ borrower number. #' # FIXME - Okay, so what does the above actually _mean_? sub getnextacctno { - my ($borrowernumber)=@_; - my $nextaccntno = 1; - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("SELECT * FROM accountlines + my ($borrowernumber) = @_; + my $nextaccntno = 1; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare( + "SELECT * FROM accountlines WHERE (borrowernumber = ?) - ORDER BY accountno DESC"); - $sth->execute($borrowernumber); - if (my $accdata=$sth->fetchrow_hashref){ - $nextaccntno = $accdata->{'accountno'} + 1; - } - $sth->finish; - return($nextaccntno); + ORDER BY accountno DESC" + ); + $sth->execute($borrowernumber); + if ( my $accdata = $sth->fetchrow_hashref ) { + $nextaccntno = $accdata->{'accountno'} + 1; + } + $sth->finish; + return ($nextaccntno); } =head2 fixaccounts @@ -217,41 +243,46 @@ sub getnextacctno { #' # FIXME - I don't understand what this function does. sub fixaccounts { - my ($borrowernumber,$accountno,$amount)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("SELECT * FROM accountlines WHERE borrowernumber=? - AND accountno=?"); - $sth->execute($borrowernumber,$accountno); - my $data=$sth->fetchrow_hashref; - # FIXME - Error-checking - my $diff=$amount-$data->{'amount'}; - my $outstanding=$data->{'amountoutstanding'}+$diff; - $sth->finish; - - $dbh->do(<dbh; + my $sth = $dbh->prepare( + "SELECT * FROM accountlines WHERE borrowernumber=? + AND accountno=?" + ); + $sth->execute( $borrowernumber, $accountno ); + my $data = $sth->fetchrow_hashref; + + # FIXME - Error-checking + my $diff = $amount - $data->{'amount'}; + my $outstanding = $data->{'amountoutstanding'} + $diff; + $sth->finish; + + $dbh->do(<dbh; - my $borrower=GetMember($borrowernumber,'borrowernumber'); - my $sth=$dbh->prepare("UPDATE issues SET returndate=now() WHERE - borrowernumber=? AND itemnumber=? AND returndate IS NULL"); - $sth->execute($borrowernumber,$itemnum); - $sth->finish; - my @datearr = localtime(time); - my $date = (1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3]; - my $bor="$borrower->{'firstname'} $borrower->{'surname'} $borrower->{'cardnumber'}"; - $sth=$dbh->prepare("UPDATE items SET paidfor=? WHERE itemnumber=?"); - $sth->execute("Paid for by $bor $date",$itemnum); - $sth->finish; +sub returnlost { + my ( $borrowernumber, $itemnum ) = @_; + my $dbh = C4::Context->dbh; + my $borrower = C4::Members::GetMember( $borrowernumber, 'borrowernumber' ); + my $sth = $dbh->prepare( + "UPDATE issues SET returndate=now() WHERE + borrowernumber=? AND itemnumber=? AND returndate IS NULL" + ); + $sth->execute( $borrowernumber, $itemnum ); + $sth->finish; + my @datearr = localtime(time); + my $date = + ( 1900 + $datearr[5] ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3]; + my $bor = +"$borrower->{'firstname'} $borrower->{'surname'} $borrower->{'cardnumber'}"; + ModItem({ paidfor => "Paid for by $bor $date" }, undef, $itemnum); } =head2 manualinvoice @@ -270,58 +301,71 @@ should be the empty string. #' # FIXME - Okay, so what does this function do, really? -sub manualinvoice{ - my ($borrowernumber,$itemnum,$desc,$type,$amount,$user)=@_; - my $dbh = C4::Context->dbh; - my $notifyid=0; - my $insert; - $itemnum=~ s/ //g; - my $accountno=getnextacctno($borrowernumber); - my $amountleft=$amount; - - if ($type eq 'CS' || $type eq 'CB' || $type eq 'CW' - || $type eq 'CF' || $type eq 'CL'){ - my $amount2=$amount*-1; # FIXME - $amount2 = -$amount - $amountleft=fixcredit($borrowernumber,$amount2,$itemnum,$type,$user); - } - if ($type eq 'N'){ - $desc.="New Card"; - } - if ($type eq 'F'){ - $desc.="Fine"; - } - if ($type eq 'A'){ - $desc.="Account Management fee"; - } - if ($type eq 'M'){ - $desc.="Sundry"; - } - - if ($type eq 'L' && $desc eq ''){ - - $desc="Lost Item"; - } - if ($type eq 'REF'){ - $desc.="Cash Refund"; - $amountleft=refund('',$borrowernumber,$amount); - } - if(($type eq 'L') or ($type eq 'F') or ($type eq 'A') or ($type eq 'N') or ($type eq 'M') ){ - $notifyid=1; - } - - if ($itemnum ne ''){ - $desc.=" ".$itemnum; - my $sth=$dbh->prepare("INSERT INTO accountlines +sub manualinvoice { + my ( $borrowernumber, $itemnum, $desc, $type, $amount, $user ) = @_; + my $dbh = C4::Context->dbh; + my $notifyid = 0; + my $insert; + $itemnum =~ s/ //g; + my $accountno = getnextacctno($borrowernumber); + my $amountleft = $amount; + + if ( $type eq 'CS' + || $type eq 'CB' + || $type eq 'CW' + || $type eq 'CF' + || $type eq 'CL' ) + { + my $amount2 = $amount * -1; # FIXME - $amount2 = -$amount + $amountleft = + fixcredit( $borrowernumber, $amount2, $itemnum, $type, $user ); + } + if ( $type eq 'N' ) { + $desc .= "New Card"; + } + if ( $type eq 'F' ) { + $desc .= "Fine"; + } + if ( $type eq 'A' ) { + $desc .= "Account Management fee"; + } + if ( $type eq 'M' ) { + $desc .= "Sundry"; + } + + if ( $type eq 'L' && $desc eq '' ) { + + $desc = "Lost Item"; + } + if ( $type eq 'REF' ) { + $desc .= "Cash Refund"; + $amountleft = refund( '', $borrowernumber, $amount ); + } + if ( ( $type eq 'L' ) + or ( $type eq 'F' ) + or ( $type eq 'A' ) + or ( $type eq 'N' ) + or ( $type eq 'M' ) ) + { + $notifyid = 1; + } + + if ( $itemnum ne '' ) { + $desc .= " " . $itemnum; + my $sth = $dbh->prepare( + "INSERT INTO accountlines (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding, itemnumber,notify_id) VALUES (?, ?, now(), ?,?, ?,?,?,?)"); -# $sth->execute($borrowernumber, $accountno, $amount, $desc, $type, $amountleft, $data->{'itemnumber'}); - $sth->execute($borrowernumber, $accountno, $amount, $desc, $type, $amountleft, $itemnum,$notifyid); + $sth->execute($borrowernumber, $accountno, $amount, $desc, $type, $amountleft, $itemnum,$notifyid) || return $sth->errstr; } else { my $sth=$dbh->prepare("INSERT INTO accountlines (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding,notify_id) - VALUES (?, ?, now(), ?, ?, ?, ?,?)"); - $sth->execute($borrowernumber, $accountno, $amount, $desc, $type, $amountleft,$notifyid); - } + VALUES (?, ?, now(), ?, ?, ?, ?,?)" + ); + $sth->execute( $borrowernumber, $accountno, $amount, $desc, $type, + $amountleft, $notifyid ); + } + return 0; } =head2 fixcredit @@ -333,82 +377,103 @@ sub manualinvoice{ =cut -sub fixcredit{ - #here we update both the accountoffsets and the account lines - my ($borrowernumber,$data,$barcode,$type,$user)=@_; - my $dbh = C4::Context->dbh; - my $newamtos = 0; - my $accdata = ""; - my $amountleft = $data; - if ($barcode ne ''){ - my $item=GetBiblioFromItemNumber('',$barcode); - my $nextaccntno = getnextacctno($borrowernumber); - my $query="SELECT * FROM accountlines WHERE (borrowernumber=? +sub fixcredit { + + #here we update both the accountoffsets and the account lines + my ( $borrowernumber, $data, $barcode, $type, $user ) = @_; + my $dbh = C4::Context->dbh; + my $newamtos = 0; + my $accdata = ""; + my $amountleft = $data; + if ( $barcode ne '' ) { + my $item = GetBiblioFromItemNumber( '', $barcode ); + my $nextaccntno = getnextacctno($borrowernumber); + my $query = "SELECT * FROM accountlines WHERE (borrowernumber=? AND itemnumber=? AND amountoutstanding > 0)"; - if ($type eq 'CL'){ - $query.=" AND (accounttype = 'L' OR accounttype = 'Rep')"; - } elsif ($type eq 'CF'){ - $query.=" AND (accounttype = 'F' OR accounttype = 'FU' OR + if ( $type eq 'CL' ) { + $query .= " AND (accounttype = 'L' OR accounttype = 'Rep')"; + } + elsif ( $type eq 'CF' ) { + $query .= " AND (accounttype = 'F' OR accounttype = 'FU' OR accounttype='Res' OR accounttype='Rent')"; - } elsif ($type eq 'CB'){ - $query.=" and accounttype='A'"; - } -# print $query; - my $sth=$dbh->prepare($query); - $sth->execute($borrowernumber,$item->{'itemnumber'}); - $accdata=$sth->fetchrow_hashref; - $sth->finish; - if ($accdata->{'amountoutstanding'} < $amountleft) { - $newamtos = 0; - $amountleft -= $accdata->{'amountoutstanding'}; - } else { - $newamtos = $accdata->{'amountoutstanding'} - $amountleft; - $amountleft = 0; - } - my $thisacct = $accdata->{accountno}; - my $usth = $dbh->prepare("UPDATE accountlines SET amountoutstanding= ? - WHERE (borrowernumber = ?) AND (accountno=?)"); - $usth->execute($newamtos,$borrowernumber,$thisacct); - $usth->finish; - $usth = $dbh->prepare("INSERT INTO accountoffsets + } + elsif ( $type eq 'CB' ) { + $query .= " and accounttype='A'"; + } + + # print $query; + my $sth = $dbh->prepare($query); + $sth->execute( $borrowernumber, $item->{'itemnumber'} ); + $accdata = $sth->fetchrow_hashref; + $sth->finish; + if ( $accdata->{'amountoutstanding'} < $amountleft ) { + $newamtos = 0; + $amountleft -= $accdata->{'amountoutstanding'}; + } + else { + $newamtos = $accdata->{'amountoutstanding'} - $amountleft; + $amountleft = 0; + } + my $thisacct = $accdata->{accountno}; + my $usth = $dbh->prepare( + "UPDATE accountlines SET amountoutstanding= ? + WHERE (borrowernumber = ?) AND (accountno=?)" + ); + $usth->execute( $newamtos, $borrowernumber, $thisacct ); + $usth->finish; + $usth = $dbh->prepare( + "INSERT INTO accountoffsets (borrowernumber, accountno, offsetaccount, offsetamount) - VALUES (?,?,?,?)"); - $usth->execute($borrowernumber,$accdata->{'accountno'},$nextaccntno,$newamtos); - $usth->finish; - } - # begin transaction - my $nextaccntno = getnextacctno($borrowernumber); - # get lines with outstanding amounts to offset - my $sth = $dbh->prepare("SELECT * FROM accountlines + VALUES (?,?,?,?)" + ); + $usth->execute( $borrowernumber, $accdata->{'accountno'}, + $nextaccntno, $newamtos ); + $usth->finish; + } + + # begin transaction + my $nextaccntno = getnextacctno($borrowernumber); + + # get lines with outstanding amounts to offset + my $sth = $dbh->prepare( + "SELECT * FROM accountlines WHERE (borrowernumber = ?) AND (amountoutstanding >0) - ORDER BY date"); - $sth->execute($borrowernumber); -# print $query; - # offset transactions - while (($accdata=$sth->fetchrow_hashref) and ($amountleft>0)){ - if ($accdata->{'amountoutstanding'} < $amountleft) { - $newamtos = 0; - $amountleft -= $accdata->{'amountoutstanding'}; - } else { - $newamtos = $accdata->{'amountoutstanding'} - $amountleft; - $amountleft = 0; - } - my $thisacct = $accdata->{accountno}; - my $usth = $dbh->prepare("UPDATE accountlines SET amountoutstanding= ? - WHERE (borrowernumber = ?) AND (accountno=?)"); - $usth->execute($newamtos,$borrowernumber,$thisacct); - $usth->finish; - $usth = $dbh->prepare("INSERT INTO accountoffsets + ORDER BY date" + ); + $sth->execute($borrowernumber); + + # print $query; + # offset transactions + while ( ( $accdata = $sth->fetchrow_hashref ) and ( $amountleft > 0 ) ) { + if ( $accdata->{'amountoutstanding'} < $amountleft ) { + $newamtos = 0; + $amountleft -= $accdata->{'amountoutstanding'}; + } + else { + $newamtos = $accdata->{'amountoutstanding'} - $amountleft; + $amountleft = 0; + } + my $thisacct = $accdata->{accountno}; + my $usth = $dbh->prepare( + "UPDATE accountlines SET amountoutstanding= ? + WHERE (borrowernumber = ?) AND (accountno=?)" + ); + $usth->execute( $newamtos, $borrowernumber, $thisacct ); + $usth->finish; + $usth = $dbh->prepare( + "INSERT INTO accountoffsets (borrowernumber, accountno, offsetaccount, offsetamount) - VALUE (?,?,?,?)"); - $usth->execute($borrowernumber,$accdata->{'accountno'},$nextaccntno,$newamtos); - $usth->finish; - } - $sth->finish; - $type="Credit ".$type; - UpdateStats($user,$type,$data,$user,'','',$borrowernumber); - $amountleft*=-1; - return($amountleft); + VALUE (?,?,?,?)" + ); + $usth->execute( $borrowernumber, $accdata->{'accountno'}, + $nextaccntno, $newamtos ); + $usth->finish; + } + $sth->finish; + $type = "Credit " . $type; + UpdateStats( $user, $type, $data, $user, '', '', $borrowernumber ); + $amountleft *= -1; + return ($amountleft); } @@ -418,54 +483,122 @@ sub fixcredit{ =cut -sub refund{ - #here we update both the accountoffsets and the account lines - my ($borrowernumber,$data)=@_; - my $dbh = C4::Context->dbh; - my $newamtos = 0; - my $accdata = ""; - my $amountleft = $data *-1; - - # begin transaction - my $nextaccntno = getnextacctno($borrowernumber); - # get lines with outstanding amounts to offset - my $sth = $dbh->prepare("SELECT * FROM accountlines +sub refund { + + #here we update both the accountoffsets and the account lines + my ( $borrowernumber, $data ) = @_; + my $dbh = C4::Context->dbh; + my $newamtos = 0; + my $accdata = ""; + my $amountleft = $data * -1; + + # begin transaction + my $nextaccntno = getnextacctno($borrowernumber); + + # get lines with outstanding amounts to offset + my $sth = $dbh->prepare( + "SELECT * FROM accountlines WHERE (borrowernumber = ?) AND (amountoutstanding<0) - ORDER BY date"); - $sth->execute($borrowernumber); -# print $amountleft; - # offset transactions - while (($accdata=$sth->fetchrow_hashref) and ($amountleft<0)){ - if ($accdata->{'amountoutstanding'} > $amountleft) { - $newamtos = 0; - $amountleft -= $accdata->{'amountoutstanding'}; - } else { - $newamtos = $accdata->{'amountoutstanding'} - $amountleft; - $amountleft = 0; - } -# print $amountleft; - my $thisacct = $accdata->{accountno}; - my $usth = $dbh->prepare("UPDATE accountlines SET amountoutstanding= ? - WHERE (borrowernumber = ?) AND (accountno=?)"); - $usth->execute($newamtos,$borrowernumber,$thisacct); - $usth->finish; - $usth = $dbh->prepare("INSERT INTO accountoffsets + ORDER BY date" + ); + $sth->execute($borrowernumber); + + # print $amountleft; + # offset transactions + while ( ( $accdata = $sth->fetchrow_hashref ) and ( $amountleft < 0 ) ) { + if ( $accdata->{'amountoutstanding'} > $amountleft ) { + $newamtos = 0; + $amountleft -= $accdata->{'amountoutstanding'}; + } + else { + $newamtos = $accdata->{'amountoutstanding'} - $amountleft; + $amountleft = 0; + } + + # print $amountleft; + my $thisacct = $accdata->{accountno}; + my $usth = $dbh->prepare( + "UPDATE accountlines SET amountoutstanding= ? + WHERE (borrowernumber = ?) AND (accountno=?)" + ); + $usth->execute( $newamtos, $borrowernumber, $thisacct ); + $usth->finish; + $usth = $dbh->prepare( + "INSERT INTO accountoffsets (borrowernumber, accountno, offsetaccount, offsetamount) - VALUES (?,?,?,?)"); - $usth->execute($borrowernumber,$accdata->{'accountno'},$nextaccntno,$newamtos); - $usth->finish; - } - $sth->finish; - return($amountleft); + VALUES (?,?,?,?)" + ); + $usth->execute( $borrowernumber, $accdata->{'accountno'}, + $nextaccntno, $newamtos ); + $usth->finish; + } + $sth->finish; + return ($amountleft); } +sub getcharges { + my ( $borrowerno, $timestamp, $accountno ) = @_; + my $dbh = C4::Context->dbh; + my $timestamp2 = $timestamp - 1; + my $query = ""; + my $sth = $dbh->prepare( + "SELECT * FROM accountlines WHERE borrowernumber=? AND accountno = ?" + ); + $sth->execute( $borrowerno, $accountno ); + + my @results; + while ( my $data = $sth->fetchrow_hashref ) { + push @results,$data; + } + return (@results); +} -END { } # module clean-up code here (global destructor) + +sub getcredits { + my ( $date, $date2 ) = @_; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare( + "SELECT * FROM accountlines,borrowers + WHERE amount < 0 AND accounttype <> 'Pay' AND accountlines.borrowernumber = borrowers.borrowernumber + AND timestamp >=TIMESTAMP(?) AND timestamp < TIMESTAMP(?)" + ); + + $sth->execute( $date, $date2 ); + my @results; + while ( my $data = $sth->fetchrow_hashref ) { + $data->{'date'} = $data->{'timestamp'}; + push @results,$data; + } + return (@results); +} + + +sub getrefunds { + my ( $date, $date2 ) = @_; + my $dbh = C4::Context->dbh; + + my $sth = $dbh->prepare( + "SELECT *,timestamp AS datetime + FROM accountlines,borrowers + WHERE (accounttype = 'REF' + AND accountlines.borrowernumber = borrowers.borrowernumber + AND date >=? AND date execute( $date, $date2 ); + + my @results; + while ( my $data = $sth->fetchrow_hashref ) { + push @results,$data; + + } + return (@results); +} +END { } # module clean-up code here (global destructor) 1; __END__ - =head1 SEE ALSO DBI(3)