From ff70734b2891784ab30a0b87c2d26e7c812bf564 Mon Sep 17 00:00:00 2001 From: Emmi Takkinen Date: Tue, 10 Jun 2025 11:06:44 +0300 Subject: [PATCH] Bug 33756: Use aqorders.quantityreceived to calculate total amount spent We currently use aqorders.quantity to calculate how much funds have been spent for orders. This leads to situation where sum doesn't match the amount which has been already spented. This patch changes this by using quantityreceived column instead of quantity column to calculate spented funds. This test plan assumes that you already have budgets and orders in your database. To test: 1. Find an existing fund where some of the ordered items have been received and look up their budget_id. Use this report if needed: SELECT budget_id, budget_period_id, budget_code FROM aqbudgets WHERE budget_id IN( SELECT budget_id FROM aqorders WHERE quantity != quantityreceived AND quantityreceived > 0) 2. Set syspref CalculateFundValuesIncludingTax as "Exclude". 3. Add following report to your report library: SELECT SUM(quantity), SUM(quantityreceived), SUM(unitprice_tax_excluded * quantity) AS "Budget spent by quantity", SUM(unitprice_tax_excluded * quantityreceived) AS "Budget spent by quantity received" FROM aqorders WHERE budget_id= [budget_id from step 1.] 3. Search your chosen budget from Funds page navigate to address /cgi-bin/koha/admin/aqbudgets.pl?budget_period_id=[budget_period_id from step 1.] and then filter table with budget_code from step 1. 4. Compare funds tables "Base-level spent" and "Total spent" columns with report results. => Note that both column values match value in reports "Budget spent by quantity" columns value. 5. Apply this patch. 6. Refresh funds table. => Note that now column values match value in reports "Budget spent by quantity received" columns value. Also prove that t/db_dependent/Budgets.t and t/db_dependent/Budgets/CloneBudgetHierarchy.t still pass. Sponsored-by: Koha-Suomi Oy --- C4/Budgets.pm | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/C4/Budgets.pm b/C4/Budgets.pm index 52d8b972c9..c0dc2c2205 100644 --- a/C4/Budgets.pm +++ b/C4/Budgets.pm @@ -735,7 +735,7 @@ sub GetBudgetHierarchy { SELECT aqorders.budget_id, aqbudgets.budget_parent_id, SUM( | . C4::Acquisition::get_rounding_sql(qq|COALESCE($unitprice_field, $ecost_field)|) - . q| * quantity ) AS budget_spent + . q| * quantityreceived ) AS budget_spent FROM aqorders JOIN aqbudgets USING (budget_id) WHERE quantityreceived > 0 AND datecancellationprinted IS NULL GROUP BY budget_id, budget_parent_id -- 2.34.1