[Koha-patches] [PATCH] (bug #3348) fix funds and budget table

[Nahuel ANGELINETTI]

This fix how the funds and budget table is calculated. Some old values are included when they shouldn't.
Then it disallow the user to receive non-closed baskets.
And fix a the spent/commited budget page, to show (and calculate) only closed baskets.
---
 C4/Acquisition.pm |    5 ++++-
 C4/Bookfund.pm    |   46 +++++++++++++++++++++++++++-------------------
 acqui/bookfund.pl |    3 ++-
 acqui/parcel.pl   |    2 +-
 acqui/spent.pl    |    4 +++-
 5 files changed, 37 insertions(+), 23 deletions(-)

diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm
index 5fc05ce..4d8af94 100644
--- a/C4/Acquisition.pm
+++ b/C4/Acquisition.pm
@@ -196,7 +196,7 @@ Results are ordered from most to least recent.
 =cut
 
 sub GetPendingOrders {
-    my ($supplierid,$grouped) = @_;
+    my ($supplierid,$grouped, $closed) = @_;
     my $dbh = C4::Context->dbh;
     my $strsth = "
         SELECT    ".($grouped?"count(*),":"")."aqbasket.basketno,
@@ -210,6 +210,9 @@ sub GetPendingOrders {
             AND datecancellationprinted IS NULL
             AND (to_days(now())-to_days(closedate) < 180 OR closedate IS NULL)
     ";
+    if($closed){
+        $strsth .= " AND closedate IS NOT NULL ";
+    }
     ## FIXME  Why 180 days ???
     my @query_params = ( $supplierid );
     if ( C4::Context->preference("IndependantBranches") ) {
diff --git a/C4/Bookfund.pm b/C4/Bookfund.pm
index 8e9c95f..9846913 100644
--- a/C4/Bookfund.pm
+++ b/C4/Bookfund.pm
@@ -223,26 +223,30 @@ sub GetBookFundBreakdown {
     # do a query for spent totals.
     my $query = "
         SELECT quantity,datereceived,freight,unitprice,listprice,ecost,
-               quantityreceived,subscription
+               quantityreceived,subscription, closedate
         FROM   aqorders
+        LEFT JOIN aqbasket USING (basketno)
         LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
         LEFT JOIN aqbookfund ON (aqorderbreakdown.bookfundid=aqbookfund.bookfundid and aqorderbreakdown.branchcode=aqbookfund.branchcode)
         LEFT JOIN aqbudget ON (aqbudget.bookfundid=aqbookfund.bookfundid and aqbudget.branchcode=aqbookfund.branchcode)
         WHERE  aqorderbreakdown.bookfundid=?
             AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
             AND ((budgetdate >= ? and budgetdate < ?) OR (startdate>=? and enddate<=?))
+            AND (closedate >= ? AND closedate <= ?)
     ";
     my $sth = $dbh->prepare($query);
-    $sth->execute( $id, $start, $end, $start, $end );
+    $sth->execute( $id, $start, $end, $start, $end, $start, $end );
 
     my ($spent) = 0;
     while ( my $data = $sth->fetchrow_hashref ) {
-        if ( $data->{'subscription'} == 1 ) {
-            $spent += $data->{'quantity'} * $data->{'unitprice'};
-        }
-        else {
-            $spent += ( $data->{'unitprice'} ) * ($data->{'quantityreceived'}?$data->{'quantityreceived'}:0);
-
+        if($data->{datereceived}){
+            if ( $data->{'subscription'} == 1 ) {
+                $spent += $data->{'quantity'} * $data->{'unitprice'};
+            }
+            else {
+                $spent += ( $data->{'unitprice'} ) * ($data->{'quantityreceived'}?$data->{'quantityreceived'}:0);
+    
+            }
         }
     }
 
@@ -254,32 +258,36 @@ sub GetBookFundBreakdown {
                 listprice,ecost,quantityreceived AS qrev,
                 subscription,title,itemtype,aqorders.biblionumber,
                 aqorders.booksellerinvoicenumber,
-                quantity-quantityreceived AS tleft,
+                quantity-quantityreceived AS tleft,quantityreceived,
                 aqorders.ordernumber AS ordnum,entrydate,budgetdate
         FROM    aqorders
+        LEFT JOIN aqbasket USING (basketno)
         LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=aqorders.biblioitemnumber
         LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
         WHERE   bookfundid=?
             AND (budgetdate >= ? AND budgetdate < ?)
             AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
+            AND (closedate >= ? AND closedate <= ?)
     ";
 
     $sth = $dbh->prepare($query);
 #      warn "$start $end";     
-    $sth->execute( $id, $start, $end );
+    $sth->execute( $id, $start, $end, $start, $end);
 
     my $comtd=0;
 
     while ( my $data = $sth->fetchrow_hashref ) {
-        my $left = $data->{'tleft'};
-        if ( !$left || $left eq '' ) {
-            $left = $data->{'quantity'};
-        }
-        if ( $left && $left > 0 ) {
-            my $subtotal = $left * $data->{'ecost'};
-            $data->{subtotal} = $subtotal;
-            $data->{'left'} = $left;
-            $comtd += $subtotal;
+        if(not $data->{datereceived}){
+            my $left = $data->{'tleft'};
+            if ( !$left || $left eq '' ) {
+                $left = $data->{'quantity'};
+            }
+            if ( $left && $left > 0 ) {
+                my $subtotal = $left * $data->{'ecost'};
+                $data->{subtotal} = $subtotal;
+                $data->{'left'} = $left;
+                $comtd += $subtotal;
+            }
         }
 #         use Data::Dumper; warn Dumper($data);    
     }
diff --git a/acqui/bookfund.pl b/acqui/bookfund.pl
index 4769b9b..bac0bf9 100755
--- a/acqui/bookfund.pl
+++ b/acqui/bookfund.pl
@@ -71,9 +71,10 @@ SELECT quantity,
     AND budgetdate < ?
     AND (datecancellationprinted IS NULL
          OR datecancellationprinted = \'0000-00-00\')
+    AND (closedate >= ? and closedate < ?)
 ';
 my $sth = $dbh->prepare($query);
-$sth->execute( $bookfund, $start, $end );
+$sth->execute( $bookfund, $start, $end, $start, $end);
 my @commited_loop;
 
 my $total = 0;
diff --git a/acqui/parcel.pl b/acqui/parcel.pl
index 62c77e4..118547e 100755
--- a/acqui/parcel.pl
+++ b/acqui/parcel.pl
@@ -137,7 +137,7 @@ for (my $i = 0 ; $i < $countlines ; $i++) {
     $tototal       += $total;
 }
 
-my $pendingorders = GetPendingOrders($supplierid);
+my $pendingorders = GetPendingOrders($supplierid, 0, 1);
 my $countpendings = scalar @$pendingorders;
 
 # pending orders totals
diff --git a/acqui/spent.pl b/acqui/spent.pl
index af6f1ea..578ed9b 100755
--- a/acqui/spent.pl
+++ b/acqui/spent.pl
@@ -35,6 +35,7 @@ my $query =
     as ordnum,entrydate,budgetdate,booksellerid,aqbasket.basketno
     from aqorderbreakdown,aqbasket,aqorders
     left join biblioitems on  biblioitems.biblioitemnumber=aqorders.biblioitemnumber 
+    LEFT JOINaqbasket USING (basketno)
     where bookfundid=? and
     aqorders.ordernumber=aqorderbreakdown.ordernumber and
     aqorders.basketno=aqbasket.basketno
@@ -42,11 +43,12 @@ my $query =
 	(datereceived >= ? and datereceived < ?))
     and (datecancellationprinted is NULL or
 	   datecancellationprinted='0000-00-00')
+	and (closedate >= ? and closedate < ?)
 
 
   ";
 my $sth = $dbh->prepare($query);
-$sth->execute( $bookfund, $start, $end );
+$sth->execute( $bookfund, $start, $end, $start, $end );
 
 my $total = 0;
 my $toggle;
-- 
1.6.0.4