From 248669fc1d91df5650cce15f761988aaad47f459 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 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 keeping all the default values 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 --- 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 f115cbd76c..8b3852f06a 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 5eca17fe30..6f456f8f8c 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/spent.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/spent.tt @@ -80,7 +80,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