use C4::Context;
use C4::Auth;
-use C4::Interface::CGI::Output;
+use C4::Output;
use strict;
use CGI;
my $dbh = C4::Context->dbh;
my $input = new CGI;
-my $bookfund = $input->param('bookfund');
+my $budgetid = $input->param('aqbudgetid');
my $start = $input->param('start');
my $end = $input->param('end');
}
);
+#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 aqorders,aqorderbreakdown,aqbasket
- 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 aqbudgetid=?
+ 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( $budgetid );
my $total = 0;
my $toggle;