@@ -, +, @@ --- C4/Budgets.pm | 16 ++++++++-------- 1 files changed, 8 insertions(+), 8 deletions(-) --- a/C4/Budgets.pm +++ a/C4/Budgets.pm @@ -309,10 +309,10 @@ sub GetBudgetSpent { my ($budget_id) = @_; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare(qq| - SELECT SUM( COALESCE(unitprice, ecost) * quantity ) AS sum FROM aqorders - WHERE budget_id = ? AND - quantityreceived > 0 AND - datecancellationprinted IS NULL + SELECT SUM(( COALESCE(unitprice, ecost) +COALESCE(unitprice, ecost)*IF(invoiceincgst=0,COALESCE(aqorders.gstrate,0),0) )* quantityreceived ) AS sum FROM aqorders + LEFT JOIN aqbasket ON (aqorders.basketno = aqbasket.basketno) + LEFT JOIN aqbooksellers ON(aqbasket.booksellerid=aqbooksellers.id) + WHERE budget_id = ? AND quantityreceived > 0 AND datecancellationprinted IS NULL |); $sth->execute($budget_id); my $sum = $sth->fetchrow_array; @@ -335,10 +335,10 @@ sub GetBudgetOrdered { my ($budget_id) = @_; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare(qq| - SELECT SUM(ecost * quantity) AS sum FROM aqorders - WHERE budget_id = ? AND - quantityreceived = 0 AND - datecancellationprinted IS NULL + SELECT SUM((ecost+ecost*IF(listincgst=0,COALESCE(aqorders.gstrate,0),0) ) * (quantity-quantityreceived)) AS sum FROM aqorders + LEFT JOIN aqbasket ON (aqorders.basketno = aqbasket.basketno) + LEFT JOIN aqbooksellers ON(aqbasket.booksellerid=aqbooksellers.id) + WHERE budget_id = ? AND datecancellationprinted IS NULL |); $sth->execute($budget_id); my $sum = $sth->fetchrow_array; --