From db4eff762514096290f72438323a04b5e0dfdd0f 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 Signed-off-by: Kyle M Hall --- 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 aa75c88f3ca..d22e32245c4 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 84b49550c13..1d07220550b 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 b8269d08df3..25a5622a02b 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 86516919652..abb49b93dd0 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/ordered.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/ordered.tt @@ -79,7 +79,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 abb5b941ddc..2fe76e0b2d5 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/spent.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/spent.tt @@ -81,7 +81,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.30.2