From 3ca1ab63ac65104a16f5a0bcfd87f080b8a869fc 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 Signed-off-by: Tomas Cohen Arazi --- C4/Budgets.pm | 60 +++++++++++++++---- acqui/ordered.pl | 9 ++- acqui/spent.pl | 13 ++-- .../prog/en/modules/acqui/ordered.tt | 6 +- .../prog/en/modules/acqui/spent.tt | 6 +- 5 files changed, 71 insertions(+), 23 deletions(-) diff --git a/C4/Budgets.pm b/C4/Budgets.pm index aa75c88f3c..26e4c49143 100644 --- a/C4/Budgets.pm +++ b/C4/Budgets.pm @@ -79,6 +79,7 @@ BEGIN { CloneBudgetPeriod CloneBudgetHierarchy MoveOrders + FieldsForCalculatingFundValues ); } @@ -341,15 +342,17 @@ 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 |); - $sth->execute($budget_id); + $sth->execute($budget_id); my $sum = ( $sth->fetchrow_array || 0 ) + 0; $sth = $dbh->prepare(qq| @@ -367,20 +370,24 @@ sub GetBudgetSpent { $sum += $adj->adjustment; } - return $sum; + return $sum; } # ------------------------------------------------------------------- sub GetBudgetOrdered { - my ($budget_id) = @_; - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare(qq| - SELECT SUM(| . C4::Acquisition::get_rounding_sql(qq|ecost_tax_included|) . qq| * quantity) AS sum FROM aqorders + 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_field|) . qq| * quantity) AS sum FROM aqorders WHERE budget_id = ? AND quantityreceived = 0 AND datecancellationprinted IS NULL |); - $sth->execute($budget_id); + $sth->execute($budget_id); my $sum = ( $sth->fetchrow_array || 0 ) + 0; my $adjustments = Koha::Acquisition::Invoice::Adjustments->search({budget_id => $budget_id, encumber_open => 1, closedate => undef},{ join => 'invoiceid' }); @@ -388,7 +395,7 @@ sub GetBudgetOrdered { $sum += $adj->adjustment; } - return $sum; + return $sum; } =head2 GetBudgetName @@ -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 calculating 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..dfa3a041ae 100755 --- a/acqui/spent.pl +++ b/acqui/spent.pl @@ -21,7 +21,7 @@ =head1 NAME - spent.pl + acqui/spent.pl =head1 DESCRIPTION @@ -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 8651691965..abb49b93dd 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 abb5b941dd..2fe76e0b2d 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.39.5