bug Fix 3712

[koha.git] / acqui / spent.pl

diff --git a/acqui/spent.pl b/acqui/spent.pl
index af6f1ea..89a71e5 100755 (executable)
--- a/acqui/spent.pl
+++ b/acqui/spent.pl
@@ -27,26 +27,27 @@ my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
     }
 );
 
+#James Winter 3/4/2009: Original query does not select spent rows
+#      correctly due to missing joins between tables
+
 my $query =
-"Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived
-    as qrev,subscription,title,itemtype,aqorders.biblionumber,aqorders.booksellerinvoicenumber,
+"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,booksellerid,aqbasket.basketno
-    from aqorderbreakdown,aqbasket,aqorders
-    left join biblioitems on  biblioitems.biblioitemnumber=aqorders.biblioitemnumber 
-    where bookfundid=? and
-    aqorders.ordernumber=aqorderbreakdown.ordernumber and
-    aqorders.basketno=aqbasket.basketno
-   and (
-       (datereceived >= ? and datereceived < ?))
-    and (datecancellationprinted is NULL or
-          datecancellationprinted='0000-00-00')
-
-
+    as ordnum,entrydate,budgetdate,aqbasket.booksellerid,aqbasket.basketno
+    FROM aqorders
+    LEFT JOIN aqorderbreakdown USING (ordernumber)
+    LEFT JOIN aqbasket USING (basketno)
+    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( $bookfund, $start, $end );
+$sth->execute( $bookfund);
 
 my $total = 0;
 my $toggle;