Attachment 156064 Details for Bug 31631 – Bug 31631: Optionally use tax-exclusive values for calculating fund values

[patch] Bug 31631: Optionally use tax-exclusive values for calculating fund values

Bug-31631-Optionally-use-tax-exclusive-values-for-.patch (text/plain), 11.25 KB, created by Kyle M Hall (khall) on 2023-09-22 10:57:56 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Kyle M Hall (khall)

Created: 2023-09-22 10:57:56 UTC

Size: 11.25 KB

patch

obsolete

>From db4eff762514096290f72438323a04b5e0dfdd0f Mon Sep 17 00:00:00 2001
>From: Alex Buckley <alexbuckley@catalyst.net.nz>
>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 <instance>
>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 <david@davidnind.com>
>
>Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
>---
> 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 = <<EOQ;
> SELECT
>     aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber,
>     quantity-quantityreceived AS tleft,
>-    ecost_tax_included, budgetdate, entrydate,
>+    $ecost_field, budgetdate, entrydate,
>     aqbasket.booksellerid,
>     aqbooksellers.name as vendorname,
>     GROUP_CONCAT(DISTINCT itype SEPARATOR '|') AS itypes,
>@@ -74,7 +77,7 @@ WHERE
>     (quantity > 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 = <<EOQ;
> SELECT
>     aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber,
>@@ -62,7 +65,7 @@ SELECT
>     aqorders.invoiceid,
>     aqinvoices.invoicenumber,
>     quantityreceived,
>-    unitprice_tax_included,
>+    $unitprice_field,
>     datereceived,
>     aqbooksellers.name as vendorname
> FROM (aqorders, aqbasket)
>@@ -89,7 +92,7 @@ WHERE
>              aqorders.invoiceid,
>              aqinvoices.invoicenumber,
>              quantityreceived,
>-             unitprice_tax_included,
>+             $unitprice_field,
>              datereceived,
>              aqbooksellers.name
> 
>@@ -105,9 +108,9 @@ while ( my $data = $sth->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 %]
>                         </td>
>                         <td class="data cell">
>-                            [% order.ecost_tax_included | $Price %]
>+                            [% IF Koha.Preference('CalculateFundValuesIncludingTax') %]
>+                                [% order.ecost_tax_included | $Price %]
>+                            [% ELSE %]
>+                                [% order.ecost_tax_excluded | $Price %]
>+                            [% END %]
>                         </td>
>                         <td class="cell" data-order="[% order.entrydate | html %]">
>                             [% 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 %]
>                 </td>
>                 <td class="cell">
>-                    [% order.unitprice_tax_included | $Price %]
>+                    [% IF Koha.Preference('CalculateFundValuesIncludingTax') %]
>+                        [% order.unitprice_tax_included | $Price %]
>+                    [% ELSE %]
>+                        [% order.unitprice_tax_excluded | $Price %]
>+                    [% END %]
>                 </td>
>                 <td class="cell" data-order="[% order.entrydate | html %]">
>                     [% order.entrydate | $KohaDates %]
>-- 
>2.30.2

Actions: View | Diff | Splinter Review

Attachments on bug 31631: 141017 | 141018 | 141019 | 142597 | 142661 | 143156 | 143157 | 143158 | 143159 | 147011 | 147012 | 147013 | 147014 | 147015 | 147098 | 147099 | 147100 | 147101 | 147102 | 147103 | 147104 | 147105 | 150146 | 156062 | 156063 | 156064 | 156065 | 156066 | 156067 | 156068 | 156069