From d259ef90732fdcb5bfc1b20122df911b82d96443 Mon Sep 17 00:00:00 2001 From: Nahuel ANGELINETTI Date: Mon, 22 Jun 2009 14:47:07 +0200 Subject: [PATCH] (bug #3348) fix funds and budget table This fix how the funds and budget table is calculated. Some old values are included when they shouldn't. Then it disallow the user to receive non-closed baskets. And fix a the spent/commited budget page, to show (and calculate) only closed baskets. --- C4/Acquisition.pm | 5 ++++- C4/Bookfund.pm | 45 +++++++++++++++++++++++++++------------------ acqui/bookfund.pl | 3 ++- acqui/parcel.pl | 2 +- acqui/spent.pl | 4 ++-- 5 files changed, 36 insertions(+), 23 deletions(-) diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index d8a6c95e6e..35d525364a 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -196,7 +196,7 @@ Results are ordered from most to least recent. =cut sub GetPendingOrders { - my ($supplierid,$grouped) = @_; + my ($supplierid,$grouped, $closed) = @_; my $dbh = C4::Context->dbh; my $strsth = " SELECT ".($grouped?"count(*),":"")."aqbasket.basketno, @@ -210,6 +210,9 @@ sub GetPendingOrders { AND datecancellationprinted IS NULL AND (to_days(now())-to_days(closedate) < 180 OR closedate IS NULL) "; + if($closed){ + $strsth .= " AND closedate IS NOT NULL "; + } ## FIXME Why 180 days ??? my @query_params = ( $supplierid ); if ( C4::Context->preference("IndependantBranches") ) { diff --git a/C4/Bookfund.pm b/C4/Bookfund.pm index 8e9c95f25a..6a7c9ee68c 100644 --- a/C4/Bookfund.pm +++ b/C4/Bookfund.pm @@ -223,25 +223,30 @@ sub GetBookFundBreakdown { # do a query for spent totals. my $query = " SELECT quantity,datereceived,freight,unitprice,listprice,ecost, - quantityreceived,subscription + quantityreceived,subscription, closedate FROM aqorders + LEFT JOIN aqbasket USING (basketno) LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber LEFT JOIN aqbookfund ON (aqorderbreakdown.bookfundid=aqbookfund.bookfundid and aqorderbreakdown.branchcode=aqbookfund.branchcode) LEFT JOIN aqbudget ON (aqbudget.bookfundid=aqbookfund.bookfundid and aqbudget.branchcode=aqbookfund.branchcode) WHERE aqorderbreakdown.bookfundid=? AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') AND ((budgetdate >= ? and budgetdate < ?) OR (startdate>=? and enddate<=?)) + AND (closedate >= ? AND closedate <= ?) "; my $sth = $dbh->prepare($query); - $sth->execute( $id, $start, $end, $start, $end ); + $sth->execute( $id, $start, $end, $start, $end, $start, $end ); my ($spent) = 0; while ( my $data = $sth->fetchrow_hashref ) { - if ( $data->{'subscription'} == 1 ) { - $spent += $data->{'quantity'} * $data->{'unitprice'}; - } - else { - $spent += ( $data->{'unitprice'} ) * ($data->{'quantityreceived'}?$data->{'quantityreceived'}:0); + if($data->{datereceived}){ + if ( $data->{'subscription'} == 1 ) { + $spent += $data->{'quantity'} * $data->{'unitprice'}; + } + else { + $spent += ( $data->{'unitprice'} ) * ($data->{'quantityreceived'}?$data->{'quantityreceived'}:0); + + } } } @@ -254,32 +259,36 @@ sub GetBookFundBreakdown { listprice,ecost,quantityreceived AS qrev, subscription,title,itemtype,aqorders.biblionumber, aqorders.booksellerinvoicenumber, - quantity-quantityreceived AS tleft, + 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 <= ?) "; $sth = $dbh->prepare($query); # warn "$start $end"; - $sth->execute( $id, $start, $end ); + $sth->execute( $id, $start, $end , $start, $end); my $comtd=0; while ( my $data = $sth->fetchrow_hashref ) { - 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; + 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); } diff --git a/acqui/bookfund.pl b/acqui/bookfund.pl index 4769b9b323..bac0bf9aa6 100755 --- a/acqui/bookfund.pl +++ b/acqui/bookfund.pl @@ -71,9 +71,10 @@ SELECT quantity, AND budgetdate < ? AND (datecancellationprinted IS NULL OR datecancellationprinted = \'0000-00-00\') + AND (closedate >= ? and closedate < ?) '; my $sth = $dbh->prepare($query); -$sth->execute( $bookfund, $start, $end ); +$sth->execute( $bookfund, $start, $end, $start, $end); my @commited_loop; my $total = 0; diff --git a/acqui/parcel.pl b/acqui/parcel.pl index 63ce429104..3a5cd075fc 100755 --- a/acqui/parcel.pl +++ b/acqui/parcel.pl @@ -146,7 +146,7 @@ for (my $i = 0 ; $i < $countlines ; $i++) { $tototal += $total; } -my $pendingorders = GetPendingOrders($supplierid); +my $pendingorders = GetPendingOrders($supplierid, 0, 1); my $countpendings = scalar @$pendingorders; # pending orders totals diff --git a/acqui/spent.pl b/acqui/spent.pl index b62f16d985..2dd4ddb3db 100755 --- a/acqui/spent.pl +++ b/acqui/spent.pl @@ -44,11 +44,11 @@ my $query = and (datereceived >= ? and datereceived < ?) and (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') - + and (closedate >= ? and closedate < ?) "; my $sth = $dbh->prepare($query); -$sth->execute( $bookfund, $start, $end ); +$sth->execute( $bookfund, $start, $end, $start, $end); my $total = 0; my $toggle; -- 2.39.5