From 044da8c6fb9fe2b12baa8c2d2d5701c79340b03e Mon Sep 17 00:00:00 2001 From: James Winter Date: Wed, 4 Mar 2009 15:57:01 -0500 Subject: [PATCH] bug 2703: Changed spent.pl select statment to include joins between tables. Signed-off-by: Galen Charlton --- acqui/spent.pl | 15 ++++++++++----- 1 file changed, 10 insertions(+), 5 deletions(-) diff --git a/acqui/spent.pl b/acqui/spent.pl index af6f1eabbe..c9c13ea3a6 100755 --- a/acqui/spent.pl +++ b/acqui/spent.pl @@ -27,14 +27,19 @@ 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 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,booksellerid,aqbasket.basketno - from aqorderbreakdown,aqbasket,aqorders - left join biblioitems on biblioitems.biblioitemnumber=aqorders.biblioitemnumber + 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 -- 2.20.1