(bug #3348) fix funds and budget table

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.

diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm
index d8a6c95..35d5253 100644 (file)
--- 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..6a7c9ee 100644 (file)
--- a/C4/Bookfund.pm
+++ b/C4/Bookfund.pm
@@ -223,25 +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 +259,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 (executable)
--- 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 63ce429..3a5cd07 100755 (executable)
--- a/acqui/parcel.pl
+++ b/acqui/parcel.pl
@@ -146,7 +146,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 b62f16d..2dd4ddb 100755 (executable)
--- a/acqui/spent.pl
+++ b/acqui/spent.pl
@@ -44,11 +44,11 @@ my $query =
    and (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;