Bug 33756 - Wrong calculation of total amount spent?
Summary: Wrong calculation of total amount spent?
Status: Needs Signoff
Alias: None
Product: Koha
Classification: Unclassified
Component: Acquisitions (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low minor
Assignee: Emmi Takkinen
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-05-17 17:18 UTC by Katrin Fischer
Modified: 2025-06-10 08:09 UTC (History)
2 users (show)

See Also:
GIT URL:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:


Attachments
Bug 33756: Use aqorders.quantityreceived to calculate total amount spent (2.52 KB, patch)
2025-06-10 08:09 UTC, Emmi Takkinen
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Katrin Fischer 2023-05-17 17:18:34 UTC
I am trying to work out something in acquisitions and stumbled on this bit:

https://git.koha-community.org/Koha-community/Koha/src/branch/master/C4/Budgets.pm#L553
        # Get all the budgets totals in as few queries as possible
        my $hr_budget_spent = $dbh->selectall_hashref(q|
            SELECT aqorders.budget_id, aqbudgets.budget_parent_id,
                SUM( | . C4::Acquisition::get_rounding_sql(qq|COALESCE(unitprice_tax_included, ecost_tax_included)|) . q| * quantity ) AS budget_spent
            FROM aqorders JOIN aqbudgets USING (budget_id)
            WHERE quantityreceived > 0 AND datecancellationprinted IS NULL
            GROUP BY budget_id, budget_parent_id
            |, 'budget_id');


If the amount is spent, I feel multiplying by quantity is incorrect. It should be quantityreceived.

I am not sure yet where this is used and how it manifests.
Comment 1 Emmi Takkinen 2025-06-06 10:57:55 UTC
It's used at least in page "Funds for budget..." (/cgi-bin/koha/admin/aqbudgets.pl?budget_period_id=[budget_period_id]). Sums in columns "Base-level spent" and "Total spent" are currently counted based on how many items have been ordered, not how many items are already received and how much this has costed. So the sums are too high.
Comment 2 Emmi Takkinen 2025-06-10 08:09:28 UTC
Created attachment 183111 [details] [review]
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_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 (/cgi-bin/koha/admin/aqbudgets.pl).
4. Compare funds tables "Base-level ordered" and "Total spend 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