X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FAccounts2.pm;h=2a5dc4e7e8e671ba7dfefc8b5344d8425dd22050;hb=3096d756771d6f6c99cd75a16bf38bb1d7dc7cf0;hp=446b3118447c3cd47e254ffdca8061527868f954;hpb=e57535b581e1b238b114dcaa573e2a2f10527752;p=koha.git

diff --git a/C4/Accounts2.pm b/C4/Accounts2.pm
index 446b311844..2a5dc4e7e8 100755
--- a/C4/Accounts2.pm
+++ b/C4/Accounts2.pm
@@ -23,13 +23,13 @@ require Exporter;
 use DBI;
 use C4::Context;
 use C4::Stats;
-use C4::Search;
+use C4::Members;
 use C4::Circulation::Circ2;
 use vars qw($VERSION @ISA @EXPORT);
 
 # set the version for version checking
-$VERSION = 0.01;	# FIXME - Should probably be different from
-			# the version for C4::Accounts
+$VERSION = 0.01;        # FIXME - Should probably be different from
+                        # the version for C4::Accounts
 
 =head1 NAME
 
@@ -52,12 +52,51 @@ patron.
 =cut
 
 @ISA = qw(Exporter);
-@EXPORT = qw(&recordpayment &fixaccounts &makepayment &manualinvoice
-&getnextacctno);
+@EXPORT = qw(&checkaccount &recordpayment &fixaccounts &makepayment &manualinvoice
+&getnextacctno &reconcileaccount);
 
-# FIXME - Never used
-sub displayaccounts{
-  my ($env)=@_;
+=item checkaccount
+
+  $owed = &checkaccount($env, $borrowernumber, $dbh, $date);
+
+Looks up the total amount of money owed by a borrower (fines, etc.).
+
+C<$borrowernumber> specifies the borrower to look up.
+
+C<$dbh> is a DBI::db handle for the Koha database.
+
+C<$env> is ignored.
+
+=cut
+#'
+sub checkaccount  {
+  #take borrower number
+  #check accounts and list amounts owing
+	my ($env,$bornumber,$dbh,$date)=@_;
+	my $select="SELECT SUM(amountoutstanding) AS total
+			FROM accountlines
+		WHERE borrowernumber = ?
+			AND amountoutstanding<>0";
+	my @bind = ($bornumber);
+	if ($date && $date ne ''){
+	$select.=" AND date < ?";
+	push(@bind,$date);
+	}
+	#  print $select;
+	my $sth=$dbh->prepare($select);
+	$sth->execute(@bind);
+	my $data=$sth->fetchrow_hashref;
+	my $total = $data->{'total'} || 0;
+	$sth->finish;
+	# output(1,2,"borrower owes $total");
+	#if ($total > 0){
+	#  # output(1,2,"borrower owes $total");
+	#  if ($total > 5){
+	#    reconcileaccount($env,$dbh,$bornumber,$total);
+	#  }
+	#}
+	#  pause();
+	return($total);
 }
 
 =item recordpayment
@@ -80,49 +119,45 @@ will be credited to the next one.
 sub recordpayment{
   #here we update both the accountoffsets and the account lines
   my ($env,$bornumber,$data)=@_;
+    warn "in accounts2.pm";
   my $dbh = C4::Context->dbh;
-  my $updquery = "";
   my $newamtos = 0;
   my $accdata = "";
   my $branch=$env->{'branchcode'};
+    warn $branch;
   my $amountleft = $data;
   # begin transaction
   my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
   # get lines with outstanding amounts to offset
-  my $query = "select * from accountlines
-  where (borrowernumber = '$bornumber') and (amountoutstanding<>0)
-  order by date";
-  my $sth = $dbh->prepare($query);
-  $sth->execute;
+  my $sth = $dbh->prepare("select * from accountlines
+  where (borrowernumber = ?) and (amountoutstanding<>0)
+  order by date");
+  $sth->execute($bornumber);
   # offset transactions
   while (($accdata=$sth->fetchrow_hashref) and ($amountleft>0)){
      if ($accdata->{'amountoutstanding'} < $amountleft) {
         $newamtos = 0;
-	$amountleft -= $accdata->{'amountoutstanding'};
+        $amountleft -= $accdata->{'amountoutstanding'};
      }  else {
         $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
-	$amountleft = 0;
+        $amountleft = 0;
      }
      my $thisacct = $accdata->{accountno};
-     $updquery = "update accountlines set amountoutstanding= '$newamtos'
-     where (borrowernumber = '$bornumber') and (accountno='$thisacct')";
-     my $usth = $dbh->prepare($updquery);
-     $usth->execute;
+     my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
+     where (borrowernumber = ?) and (accountno=?)");
+     $usth->execute($newamtos,$bornumber,$thisacct);
      $usth->finish;
-     $updquery = "insert into accountoffsets
+     $usth = $dbh->prepare("insert into accountoffsets
      (borrowernumber, accountno, offsetaccount,  offsetamount)
-     values ($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos)";
-     $usth = $dbh->prepare($updquery);
-     $usth->execute;
+     values (?,?,?,?)");
+     $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
      $usth->finish;
   }
   # create new line
-  $updquery = "insert into accountlines
+  my $usth = $dbh->prepare("insert into accountlines
   (borrowernumber, accountno,date,amount,description,accounttype,amountoutstanding)
-  values ($bornumber,$nextaccntno,now(),0-$data,'Payment,thanks',
-  'Pay',0-$amountleft)";
-  my $usth = $dbh->prepare($updquery);
-  $usth->execute;
+  values (?,?,now(),?,'Payment,thanks','Pay',?)");
+  $usth->execute($bornumber,$nextaccntno,0-$data,0-$amountleft);
   $usth->finish;
   UpdateStats($env,$branch,'payment',$data,'','','',$bornumber);
   $sth->finish;
@@ -149,47 +184,47 @@ 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 ($bornumber,$accountno,$amount,$user)=@_;
-  my $env;
+  my ($bornumber,$accountno,$amount,$user,$branch)=@_;
+  my %env;
+  $env{'branchcode'}=$branch;
   my $dbh = C4::Context->dbh;
   # begin transaction
-  my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
+  my $nextaccntno = getnextacctno(\%env,$bornumber,$dbh);
   my $newamtos=0;
-  my $sel="Select * from accountlines where  borrowernumber=$bornumber and
-  accountno=$accountno";
-  my $sth=$dbh->prepare($sel);
-  $sth->execute;
+  my $sth=$dbh->prepare("Select * from accountlines where  borrowernumber=? and accountno=?");
+  $sth->execute($bornumber,$accountno);
   my $data=$sth->fetchrow_hashref;
   $sth->finish;
 
   $dbh->do(<<EOT);
-	UPDATE	accountlines
-	SET	amountoutstanding = 0
-	WHERE	borrowernumber = $bornumber
-	  AND	accountno = $accountno
+        UPDATE  accountlines
+        SET     amountoutstanding = 0
+        WHERE   borrowernumber = $bornumber
+          AND   accountno = $accountno
 EOT
 
 #  print $updquery;
   $dbh->do(<<EOT);
-	INSERT INTO	accountoffsets
-			(borrowernumber, accountno, offsetaccount,
-			 offsetamount)
-	VALUES		($bornumber, $accountno, $nextaccntno, $newamtos)
+        INSERT INTO     accountoffsets
+                        (borrowernumber, accountno, offsetaccount,
+                         offsetamount)
+        VALUES          ($bornumber, $accountno, $nextaccntno, $newamtos)
 EOT
 
   # create new line
   my $payment=0-$amount;
   $dbh->do(<<EOT);
-	INSERT INTO	accountlines
-			(borrowernumber, accountno, date, amount,
-			 description, accounttype, amountoutstanding)
-	VALUES		($bornumber, $nextaccntno, now(), $payment,
-			'Payment,thanks - $user', 'Pay', 0)
+        INSERT INTO     accountlines
+                        (borrowernumber, accountno, date, amount,
+                         description, accounttype, amountoutstanding)
+        VALUES          ($bornumber, $nextaccntno, now(), $payment,
+                        'Payment,thanks - $user', 'Pay', 0)
 EOT
 
   # FIXME - The second argument to &UpdateStats is supposed to be the
   # branch code.
-  UpdateStats($env,$user,'payment',$amount,'','','',$bornumber);
+  # UpdateStats is now being passed $accountno too. MTJ
+  UpdateStats(\%env,$user,'payment',$amount,'','','',$bornumber,$accountno);
   $sth->finish;
   #check to see what accounttype
   if ($data->{'accounttype'} eq 'Rep' || $data->{'accounttype'} eq 'L'){
@@ -214,11 +249,10 @@ C<$env> is ignored.
 sub getnextacctno {
   my ($env,$bornumber,$dbh)=@_;
   my $nextaccntno = 1;
-  my $query = "select * from accountlines
-  where (borrowernumber = '$bornumber')
-  order by accountno desc";
-  my $sth = $dbh->prepare($query);
-  $sth->execute;
+  my $sth = $dbh->prepare("select * from accountlines
+  where (borrowernumber = ?)
+  order by accountno desc");
+  $sth->execute($bornumber);
   if (my $accdata=$sth->fetchrow_hashref){
     $nextaccntno = $accdata->{'accountno'} + 1;
   }
@@ -236,22 +270,21 @@ sub getnextacctno {
 sub fixaccounts {
   my ($borrowernumber,$accountno,$amount)=@_;
   my $dbh = C4::Context->dbh;
-  my $query="Select * from accountlines where borrowernumber=$borrowernumber
-     and accountno=$accountno";
-  my $sth=$dbh->prepare($query);
-  $sth->execute;
+  my $sth=$dbh->prepare("Select * from accountlines where borrowernumber=?
+     and accountno=?");
+  $sth->execute($borrowernumber,$accountno);
   my $data=$sth->fetchrow_hashref;
-	# FIXME - Error-checking
+        # FIXME - Error-checking
   my $diff=$amount-$data->{'amount'};
   my $outstanding=$data->{'amountoutstanding'}+$diff;
   $sth->finish;
 
   $dbh->do(<<EOT);
-	UPDATE	accountlines
-	SET	amount = '$amount',
-		amountoutstanding = '$outstanding'
-	WHERE	borrowernumber = $borrowernumber
-	  AND	accountno = $accountno
+        UPDATE  accountlines
+        SET     amount = '$amount',
+                amountoutstanding = '$outstanding'
+        WHERE   borrowernumber = $borrowernumber
+          AND   accountno = $accountno
 EOT
  }
 
@@ -259,19 +292,16 @@ EOT
 sub returnlost{
   my ($borrnum,$itemnum)=@_;
   my $dbh = C4::Context->dbh;
-  my $borrower=borrdata('',$borrnum); #from C4::Search;
-  my $upiss="Update issues set returndate=now() where
-  borrowernumber='$borrnum' and itemnumber='$itemnum' and returndate is null";
-  my $sth=$dbh->prepare($upiss);
-  $sth->execute;
+  my $borrower=borrdata('',$borrnum);
+  my $sth=$dbh->prepare("Update issues set returndate=now() where
+  borrowernumber=? and itemnumber=? and returndate is null");
+  $sth->execute($borrnum,$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'}";
-  # FIXME - Use $dbh->do();
-  my $upitem="Update items set paidfor='Paid for by $bor $date' where itemnumber='$itemnum'";
-  $sth=$dbh->prepare($upitem);
-  $sth->execute;
+  $sth=$dbh->prepare("Update items set paidfor=? where itemnumber=?");
+  $sth->execute("Paid for by $bor $date",$itemnum);
   $sth->finish;
 }
 
@@ -301,7 +331,7 @@ sub manualinvoice{
 
   if ($type eq 'CS' || $type eq 'CB' || $type eq 'CW'
   || $type eq 'CF' || $type eq 'CL'){
-    my $amount2=$amount*-1;	# FIXME - $amount2 = -$amount
+    my $amount2=$amount*-1;     # FIXME - $amount2 = -$amount
     $amountleft=fixcredit(\%env,$bornum,$amount2,$itemnum,$type,$user);
   }
   if ($type eq 'N'){
@@ -314,21 +344,22 @@ sub manualinvoice{
     $amountleft=refund('',$bornum,$amount);
   }
   if ($itemnum ne ''){
+#FIXME to use ? before uncommenting
 #     my $sth=$dbh->prepare("Select * from items where barcode='$itemnum'");
 #     $sth->execute;
 #     my $data=$sth->fetchrow_hashref;
 #     $sth->finish;
     $desc.=" ".$itemnum;
-    my $sth=$dbh->prepare("INSERT INTO	accountlines
-			(borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding, itemnumber)
-	VALUES (?, ?, now(), ?,?, ?,?,?)");
+    my $sth=$dbh->prepare("INSERT INTO  accountlines
+                        (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding, itemnumber)
+        VALUES (?, ?, now(), ?,?, ?,?,?)");
 #     $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft, $data->{'itemnumber'});
      $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft, $itemnum);
   } else {
     $desc=$dbh->quote($desc);
-    my $sth=$dbh->prepare("INSERT INTO	accountlines
-			(borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding)
-			VALUES (?, ?, now(), ?, ?, ?, ?)");
+    my $sth=$dbh->prepare("INSERT INTO  accountlines
+                        (borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding)
+                        VALUES (?, ?, now(), ?, ?, ?, ?)");
     $sth->execute($bornum, $accountno, $amount, $desc, $type, $amountleft);
   }
 }
@@ -342,15 +373,14 @@ sub fixcredit{
   #here we update both the accountoffsets and the account lines
   my ($env,$bornumber,$data,$barcode,$type,$user)=@_;
   my $dbh = C4::Context->dbh;
-  my $updquery = "";
   my $newamtos = 0;
   my $accdata = "";
   my $amountleft = $data;
   if ($barcode ne ''){
     my $item=getiteminformation($env,'',$barcode);
     my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
-    my $query="Select * from accountlines where (borrowernumber='$bornumber'
-    and itemnumber='$item->{'itemnumber'}' and amountoutstanding > 0)";
+    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'){
@@ -361,58 +391,53 @@ sub fixcredit{
     }
 #    print $query;
     my $sth=$dbh->prepare($query);
-    $sth->execute;
+    $sth->execute($bornumber,$item->{'itemnumber'});
     $accdata=$sth->fetchrow_hashref;
     $sth->finish;
     if ($accdata->{'amountoutstanding'} < $amountleft) {
         $newamtos = 0;
-	$amountleft -= $accdata->{'amountoutstanding'};
+        $amountleft -= $accdata->{'amountoutstanding'};
      }  else {
         $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
-	$amountleft = 0;
+        $amountleft = 0;
      }
           my $thisacct = $accdata->{accountno};
-     my $updquery = "update accountlines set amountoutstanding= '$newamtos'
-     where (borrowernumber = '$bornumber') and (accountno='$thisacct')";
-     my $usth = $dbh->prepare($updquery);
-     $usth->execute;
+     my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
+     where (borrowernumber = ?) and (accountno=?)");
+     $usth->execute($newamtos,$bornumber,$thisacct);
      $usth->finish;
-     $updquery = "insert into accountoffsets
+     $usth = $dbh->prepare("insert into accountoffsets
      (borrowernumber, accountno, offsetaccount,  offsetamount)
-     values ($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos)";
-     $usth = $dbh->prepare($updquery);
-     $usth->execute;
+     values (?,?,?,?)");
+     $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
      $usth->finish;
   }
   # begin transaction
   my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
   # get lines with outstanding amounts to offset
-  my $query = "select * from accountlines
-  where (borrowernumber = '$bornumber') and (amountoutstanding >0)
-  order by date";
-  my $sth = $dbh->prepare($query);
-  $sth->execute;
+  my $sth = $dbh->prepare("select * from accountlines
+  where (borrowernumber = ?) and (amountoutstanding >0)
+  order by date");
+  $sth->execute($bornumber);
 #  print $query;
   # offset transactions
   while (($accdata=$sth->fetchrow_hashref) and ($amountleft>0)){
      if ($accdata->{'amountoutstanding'} < $amountleft) {
         $newamtos = 0;
-	$amountleft -= $accdata->{'amountoutstanding'};
+        $amountleft -= $accdata->{'amountoutstanding'};
      }  else {
         $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
-	$amountleft = 0;
+        $amountleft = 0;
      }
      my $thisacct = $accdata->{accountno};
-     $updquery = "update accountlines set amountoutstanding= '$newamtos'
-     where (borrowernumber = '$bornumber') and (accountno='$thisacct')";
-     my $usth = $dbh->prepare($updquery);
-     $usth->execute;
+     my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
+     where (borrowernumber = ?) and (accountno=?)");
+     $usth->execute($newamtos,$bornumber,$thisacct);
      $usth->finish;
-     $updquery = "insert into accountoffsets
+     $usth = $dbh->prepare("insert into accountoffsets
      (borrowernumber, accountno, offsetaccount,  offsetamount)
-     values ($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos)";
-     $usth = $dbh->prepare($updquery);
-     $usth->execute;
+     values (?,?,?,?)");
+     $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
      $usth->finish;
   }
   $sth->finish;
@@ -429,7 +454,6 @@ sub refund{
   #here we update both the accountoffsets and the account lines
   my ($env,$bornumber,$data)=@_;
   my $dbh = C4::Context->dbh;
-  my $updquery = "";
   my $newamtos = 0;
   my $accdata = "";
 #  my $branch=$env->{'branchcode'};
@@ -438,40 +462,37 @@ sub refund{
   # begin transaction
   my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
   # get lines with outstanding amounts to offset
-  my $query = "select * from accountlines
-  where (borrowernumber = '$bornumber') and (amountoutstanding<0)
-  order by date";
-  my $sth = $dbh->prepare($query);
-  $sth->execute;
-#  print $query;
+  my $sth = $dbh->prepare("select * from accountlines
+  where (borrowernumber = ?) and (amountoutstanding<0)
+  order by date");
+  $sth->execute($bornumber);
 #  print $amountleft;
   # offset transactions
   while (($accdata=$sth->fetchrow_hashref) and ($amountleft<0)){
      if ($accdata->{'amountoutstanding'} > $amountleft) {
         $newamtos = 0;
-	$amountleft -= $accdata->{'amountoutstanding'};
+        $amountleft -= $accdata->{'amountoutstanding'};
      }  else {
         $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
-	$amountleft = 0;
+        $amountleft = 0;
      }
 #     print $amountleft;
      my $thisacct = $accdata->{accountno};
-     $updquery = "update accountlines set amountoutstanding= '$newamtos'
-     where (borrowernumber = '$bornumber') and (accountno='$thisacct')";
-     my $usth = $dbh->prepare($updquery);
-     $usth->execute;
+     my $usth = $dbh->prepare("update accountlines set amountoutstanding= ?
+     where (borrowernumber = ?) and (accountno=?)");
+     $usth->execute($newamtos,$bornumber,$thisacct);
      $usth->finish;
-     $updquery = "insert into accountoffsets
+     $usth = $dbh->prepare("insert into accountoffsets
      (borrowernumber, accountno, offsetaccount,  offsetamount)
-     values ($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos)";
-     $usth = $dbh->prepare($updquery);
-     $usth->execute;
+     values (?,?,?,?)");
+     $usth->execute($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos);
      $usth->finish;
   }
   $sth->finish;
   return($amountleft);
 }
 
+
 END { }       # module clean-up code here (global destructor)
 
 1;
@@ -484,3 +505,4 @@ __END__
 DBI(3)
 
 =cut
+