From c00752b2c1cb03b1d102fc9ce0a805e3bd753eb9 Mon Sep 17 00:00:00 2001 From: Alex Buckley Date: Tue, 27 Sep 2022 16:59:00 +1300 Subject: [PATCH] Bug 31631: Optionally use tax-exclusive values for calculating fund values Test plan: 1. Apply patches 2. sudo koha-upgrade-schema 3. Restart services 4. Set TaxRates syspref = 0.15 5. Create a new fund with amount = 1000 6. Add an order to a basket with the following values: - Fund = Choose the fund from step #5 - Quantity = 1 - Vendor price = 10 - Tax rate = 15% - Discount = 20% - Retail price = 10.00 - Replacement cost = 10.00 - Actual cost = 10.00 - Ensure the basket belongs to a vendor with ("List prices" = "Don't include tax" and "Invoice prices" = "Don't include tax") 7. Go to the Acquisitions home page 8. Confirm the 'Ordered' column for the step #5 fund contains 9.20 9. Click on the 9.20 link and confirm the ordered.pl page that loads also contains 9.20 as the 'Subtotal' 10. Change 'CalculateFundValuesIncludingTax' = 'Exclude' 11. On the Acquisitions home page confirm the 'Ordered' column now contains 8.00 12. Click on the 8.00 link and confirm the ordered.pl page also contains 8.00 as the 'Subtotal' 13. Close your basket and receive your order - change the 'Actual costs' = 8.00 (the $10 retail price less the 20% discount, tax exclusive) 14. Go to the Acquisitions home page and confirm the 'Spent' column now contains 8.00 15. Click on the 8.00 link and confirm the spent.pl page also contains 8.00 as the 'Subtotal' 16. Change 'CalculateFundValuesIncludingTax' = 'Include' 17. Confirm the 'Spent' column on the Acquisitions home page now shows 9.20 18. Click on the 9.20 link and confirm the spent.pl page also contains 9.20 as the 'Subtotal' Sponsored-by: Waikato Institute of Technology, New Zealand Signed-off-by: David Nind --- C4/Budgets.pm | 46 ++++++++++++++++--- acqui/ordered.pl | 9 ++-- acqui/spent.pl | 11 +++-- .../prog/en/modules/acqui/ordered.tt | 6 ++- .../prog/en/modules/acqui/spent.tt | 6 ++- 5 files changed, 63 insertions(+), 15 deletions(-) diff --git a/C4/Budgets.pm b/C4/Budgets.pm index aa75c88f3c..d22e32245c 100644 --- a/C4/Budgets.pm +++ b/C4/Budgets.pm @@ -79,6 +79,7 @@ BEGIN { CloneBudgetPeriod CloneBudgetHierarchy MoveOrders + FieldsForCalculatingFundValues ); } @@ -341,10 +342,12 @@ sub ModBudgetPlan { sub GetBudgetSpent { my ($budget_id) = @_; my $dbh = C4::Context->dbh; - # unitprice_tax_included should always been set here - # we should not need to retrieve ecost_tax_included + + # Choose correct unitprice and ecost fields + my ( $unitprice_field, $ecost_field ) = FieldsForCalculatingFundValues(); + my $sth = $dbh->prepare(qq| - SELECT SUM( | . C4::Acquisition::get_rounding_sql("COALESCE(unitprice_tax_included, ecost_tax_included)") . qq| * quantity ) AS sum FROM aqorders + SELECT SUM( | . C4::Acquisition::get_rounding_sql("COALESCE($unitprice_field, $ecost_field)") . qq| * quantity ) AS sum FROM aqorders WHERE budget_id = ? AND quantityreceived > 0 AND datecancellationprinted IS NULL @@ -374,8 +377,12 @@ sub GetBudgetSpent { sub GetBudgetOrdered { my ($budget_id) = @_; my $dbh = C4::Context->dbh; + + # Get correct unitprice and ecost prices as possible + my ( $unitprice_field, $ecost_field ) = FieldsForCalculatingFundValues(); + my $sth = $dbh->prepare(qq| - SELECT SUM(| . C4::Acquisition::get_rounding_sql(qq|ecost_tax_included|) . qq| * quantity) AS sum FROM aqorders + SELECT SUM(| . C4::Acquisition::get_rounding_sql(qq|$ecost_field|) . qq| * quantity) AS sum FROM aqorders WHERE budget_id = ? AND quantityreceived = 0 AND datecancellationprinted IS NULL @@ -550,18 +557,22 @@ sub GetBudgetHierarchy { foreach my $first_parent (@first_parents) { _add_budget_children(\@sort, $first_parent, 0); } + + # Get correct unitprice and ecost prices as possible + my ( $unitprice_field, $ecost_field ) = FieldsForCalculatingFundValues(); + if (!$skiptotals) { # 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 + SUM( | . C4::Acquisition::get_rounding_sql(qq|COALESCE($unitprice_field, $ecost_field)|) . 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'); my $hr_budget_ordered = $dbh->selectall_hashref(q| SELECT aqorders.budget_id, aqbudgets.budget_parent_id, - SUM( | . C4::Acquisition::get_rounding_sql(qq|ecost_tax_included|) . q| * quantity) AS budget_ordered + SUM( | . C4::Acquisition::get_rounding_sql(qq|$ecost_field|) . q| * quantity) AS budget_ordered FROM aqorders JOIN aqbudgets USING (budget_id) WHERE quantityreceived = 0 AND datecancellationprinted IS NULL GROUP BY budget_id, budget_parent_id @@ -1399,6 +1410,29 @@ sub MoveOrders { return \@report; } +=head2 FieldsForCalculatingFundValues + + my ( $unitprice_field, $ecost_field ) = FieldsForCalculatingFundValues(); + +Fetch the tax inclusive or tax exclusive database fields for caclulating fund values based +on the value of the CalculateFundValuesIncludingTax system preference. + +=cut + +sub FieldsForCalculatingFundValues { + + # Choose correct unitprice and ecost fields + my $unitprice_field = 'unitprice_tax_included'; + my $ecost_field = 'ecost_tax_included'; + + if ( !C4::Context->preference('CalculateFundValuesIncludingTax') ) { + $unitprice_field = 'unitprice_tax_excluded'; + $ecost_field = 'ecost_tax_excluded'; + } + + return ( $unitprice_field, $ecost_field); +} + END { } # module clean-up code here (global destructor) 1; diff --git a/acqui/ordered.pl b/acqui/ordered.pl index 84b49550c1..1d07220550 100755 --- a/acqui/ordered.pl +++ b/acqui/ordered.pl @@ -49,11 +49,14 @@ my ( $template, $loggedinuser, $cookie ) = get_template_and_user( } ); +# Choose correct ecost field +my ( $unitprice_field, $ecost_field ) = C4::Budgets->FieldsForCalculatingFundValues(); + my $query = < quantityreceived OR quantityreceived IS NULL) GROUP BY aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber, tleft, - ecost_tax_included, budgetdate, entrydate, + $ecost_field, budgetdate, entrydate, aqbasket.booksellerid, aqbooksellers.name, title @@ -96,7 +99,7 @@ while ( my $data = $sth->fetchrow_hashref ) { $left = $data->{'quantity'}; } if ( $left && $left > 0 ) { - my $subtotal = $left * get_rounded_price( $data->{'ecost_tax_included'} ); + my $subtotal = $left * get_rounded_price( $data->{$ecost_field} ); $data->{subtotal} = sprintf( "%.2f", $subtotal ); $data->{'left'} = $left; push @ordered, $data; diff --git a/acqui/spent.pl b/acqui/spent.pl index b8269d08df..25a5622a02 100755 --- a/acqui/spent.pl +++ b/acqui/spent.pl @@ -51,6 +51,9 @@ my ( $template, $loggedinuser, $cookie ) = get_template_and_user( } ); +# Get correct unitprice field +my ( $unitprice_field ) = C4::Budgets->FieldsForCalculatingFundValues(); + my $query = <fetchrow_hashref ) { my $recv = $data->{'quantityreceived'}; $data->{'itemtypes'} = [split('\|', $data->{itypes})]; if ( $recv > 0 ) { - my $rowtotal = $recv * get_rounded_price($data->{'unitprice_tax_included'}); + my $rowtotal = $recv * get_rounded_price($data->{$unitprice_field}); $data->{'rowtotal'} = sprintf( "%.2f", $rowtotal ); - $data->{'unitprice_tax_included'} = sprintf( "%.2f", $data->{'unitprice_tax_included'} ); + $data->{'unitprice_tax_included'} = sprintf( "%.2f", $data->{$unitprice_field} ); $subtotal += $rowtotal; push @spent, $data; } diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/ordered.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/ordered.tt index a324b42db7..1a6e2e4592 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/ordered.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/ordered.tt @@ -83,7 +83,11 @@ [% order.left | html %] - [% order.ecost_tax_included | $Price %] + [% IF Koha.Preference('CalculateFundValuesIncludingTax') %] + [% order.ecost_tax_included | $Price %] + [% ELSE %] + [% order.ecost_tax_excluded | $Price %] + [% END %] [% order.entrydate | $KohaDates %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/spent.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/spent.tt index 3e9b043603..6b96b57d4c 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/spent.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/spent.tt @@ -85,7 +85,11 @@ [% order.quantityreceived | html %] - [% order.unitprice_tax_included | $Price %] + [% IF Koha.Preference('CalculateFundValuesIncludingTax') %] + [% order.unitprice_tax_included | $Price %] + [% ELSE %] + [% order.unitprice_tax_excluded | $Price %] + [% END %] [% order.entrydate | $KohaDates %] -- 2.20.1