[Koha-patches] [PATCH] [followup](bug #3348) fixing acq module
Nahuel ANGELINETTI
nahuel.angelinetti at biblibre.com
Mon Oct 19 14:47:12 CEST 2009
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 5f754d7..1743a38 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 ec7977d..89a71e5 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;
--
1.6.0.4
More information about the Koha-patches
mailing list