[followup](bug #3348) fixing acq module

[koha.git] / C4 / Bookfund.pm

diff --git a/C4/Bookfund.pm b/C4/Bookfund.pm
index 6a7c9ee..1743a38 100644 (file)
--- a/C4/Bookfund.pm
+++ b/C4/Bookfund.pm
@@ -214,6 +214,7 @@ sub GetBookFundBreakdown {
     my ( $id, $start, $end ) = @_;
     my $dbh = C4::Context->dbh;
 
+    
     # if no start/end dates given defaut to everything
     if ( !$start ) {
         $start = '0000-00-00';
@@ -222,75 +223,41 @@ sub GetBookFundBreakdown {
 
     # do a query for spent totals.
     my $query = "
-        SELECT quantity,datereceived,freight,unitprice,listprice,ecost,
-               quantityreceived,subscription, closedate
-        FROM   aqorders
+    SELECT quantity,datereceived,freight,unitprice,listprice,ecost,
+        quantityreceived AS qrev,subscription,title,aqorders.biblionumber,
+        aqorders.booksellerinvoicenumber,quantity-quantityreceived as tleft,
+        aqorders.ordernumber as ordnum,entrydate,budgetdate,aqbasket.booksellerid,
+        aqbasket.basketno
+    FROM aqorders
+        LEFT JOIN aqorderbreakdown USING (ordernumber)
         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 <= ?)
+        LEFT JOIN aqbudget USING (bookfundid)
+    WHERE bookfundid=?
+        AND (datecancellationprinted IS NULL OR datecancellationprinted = '0000-00-00')
+        AND closedate BETWEEN startdate AND enddate 
+        AND creationdate > startdate
+    ORDER BY datereceived
     ";
     my $sth = $dbh->prepare($query);
-    $sth->execute( $id, $start, $end, $start, $end, $start, $end );
+    $sth->execute( $id);
 
-    my ($spent) = 0;
+    my ($spent, $comtd) = (0, 0);
     while ( my $data = $sth->fetchrow_hashref ) {
+        
+        my $recv  = $data->{'qrev'};
+        my $left = $data->{'tleft'};
+        my $ecost = $data->{'ecost'};
+        
+        
         if($data->{datereceived}){
-            if ( $data->{'subscription'} == 1 ) {
-                $spent += $data->{'quantity'} * $data->{'unitprice'};
-            }
-            else {
-                $spent += ( $data->{'unitprice'} ) * ($data->{'quantityreceived'}?$data->{'quantityreceived'}:0);
-    
+            if ( $recv > 0 ) {
+                $spent += $recv * $data->{'unitprice'};
             }
-
         }
-    }
+        $left = $data->{quantity} if(not $recv);
 
-    # then do a seperate query for commited totals, (pervious single query was
-    # returning incorrect comitted results.
+        $comtd += $left * $ecost;
 
-    $query = "
-        SELECT  quantity,datereceived,freight,unitprice,
-                listprice,ecost,quantityreceived AS qrev,
-                subscription,title,itemtype,aqorders.biblionumber,
-                aqorders.booksellerinvoicenumber,
-                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 , $start, $end);
-
-    my $comtd=0;
-
-    while ( my $data = $sth->fetchrow_hashref ) {
-        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);    
     }
 
     $sth->finish;