From 2b33623d5e628847c37eecf0539cc40340b0c188 Mon Sep 17 00:00:00 2001 From: Nahuel ANGELINETTI Date: Mon, 19 Oct 2009 14:45:45 +0200 Subject: [PATCH] [followup](bug #3348) fixing acq module This fix sql queries and the spent calculation. --- C4/Bookfund.pm | 82 +++++++++++++++----------------------------------- acqui/spent.pl | 23 +++++++------- 2 files changed, 35 insertions(+), 70 deletions(-) diff --git a/C4/Bookfund.pm b/C4/Bookfund.pm index 5f754d71f1..1743a38a5d 100644 --- 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,76 +223,41 @@ sub GetBookFundBreakdown { # do a query for spent totals. my $query = " - Select 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 (datereceived >= ? and datereceived < ?) - and (datecancellationprinted is NULL or - datecancellationprinted='0000-00-00') - and (closedate >= ? and closedate < ?) + 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 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); + $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}){ - my $recv = $data->{'qrev'}; if ( $recv > 0 ) { $spent += $recv * $data->{'unitprice'}; } - } - } - - # then do a seperate query for commited totals, (pervious single query was - # returning incorrect comitted results. + $left = $data->{quantity} if(not $recv); - $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 <= ?) - "; + $comtd += $left * $ecost; - $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; diff --git a/acqui/spent.pl b/acqui/spent.pl index ec7977d430..89a71e50dc 100755 --- a/acqui/spent.pl +++ b/acqui/spent.pl @@ -31,24 +31,23 @@ my ( $template, $loggedinuser, $cookie ) = get_template_and_user( # correctly due to missing joins between tables my $query = -"Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived - as qrev,subscription,title,itype as 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,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 (datereceived >= ? and datereceived < ?) - and (datecancellationprinted is NULL or - datecancellationprinted='0000-00-00') - and (closedate >= ? and closedate < ?) + 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, $start, $end); +$sth->execute( $bookfund); my $total = 0; my $toggle; -- 2.39.5