-#James Winter 3/4/2009: Original query does not select spent rows
-# correctly due to missing joins between tables
-
-my $query =
-"Select distinct quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived
- as qrev,subscription,title,itype as itemtype,aqorders.biblionumber,aqorders.booksellerinvoicenumber,
- quantity-quantityreceived as tleft,
- aqorders.ordernumber
- as ordnum,entrydate,budgetdate,aqbasket.booksellerid,aqbasket.basketno
- from aqorders
- inner join aqorderbreakdown on aqorderbreakdown.ordernumber = aqorders.ordernumber
- inner join aqbasket on aqbasket.basketno = aqorders.basketno
- left join items on items.biblionumber=aqorders.biblionumber
- 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')
-
-
- ";
-my $sth = $dbh->prepare($query);
-$sth->execute( $bookfund, $start, $end );
+my $query = <<EOQ;
+SELECT
+ aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber,
+ quantity-quantityreceived AS tleft,
+ budgetdate, entrydate,
+ aqbasket.booksellerid,
+ GROUP_CONCAT(DISTINCT itype SEPARATOR '|') as itypes,
+ title,
+ aqorders.invoiceid,
+ aqinvoices.invoicenumber,
+ quantityreceived,
+ unitprice_tax_included,
+ datereceived,
+ aqbooksellers.name as vendorname
+FROM (aqorders, aqbasket)
+LEFT JOIN biblio ON
+ biblio.biblionumber=aqorders.biblionumber
+LEFT JOIN aqorders_items ON
+ aqorders.ordernumber = aqorders_items.ordernumber
+LEFT JOIN items ON
+ aqorders_items.itemnumber = items.itemnumber
+LEFT JOIN aqinvoices ON
+ aqorders.invoiceid = aqinvoices.invoiceid
+LEFT JOIN aqbooksellers ON
+ aqbasket.booksellerid = aqbooksellers.id
+WHERE
+ aqorders.basketno=aqbasket.basketno AND
+ budget_id=? AND
+ (datecancellationprinted IS NULL OR
+ datecancellationprinted='0000-00-00') AND
+ datereceived IS NOT NULL
+ GROUP BY aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber,
+ tleft,
+ budgetdate, entrydate,
+ aqbasket.booksellerid,
+ title,
+ aqorders.invoiceid,
+ aqinvoices.invoicenumber,
+ quantityreceived,
+ unitprice_tax_included,
+ datereceived,
+ aqbooksellers.name