From 198a15ed164d926d95bb92864d1351e34c439acc Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Wed, 19 Nov 2014 16:59:39 +0100 Subject: [PATCH] Bug 13323: Tax rate can change on receiving - DB changes This patch adds 4 new DB fields to the aqorders table: * tax_rate_on_ordering * tax_rate_on_receiving * tax_value_on_ordering * tax_value_on_receiving --- installer/data/mysql/atomicupdate/bug_13323.perl | 51 ++++++++++++++++++++++++ installer/data/mysql/kohastructure.sql | 8 +++- 2 files changed, 57 insertions(+), 2 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_13323.perl diff --git a/installer/data/mysql/atomicupdate/bug_13323.perl b/installer/data/mysql/atomicupdate/bug_13323.perl new file mode 100644 index 0000000..e74c2f6 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_13323.perl @@ -0,0 +1,51 @@ +#!/usr/bin/env perl + +use Modern::Perl; +use C4::Context; + +my $dbh = C4::Context->dbh; + +# Add the new columns +$dbh->do(q| + ALTER TABLE aqorders + ADD COLUMN tax_rate_on_ordering decimal(6,4) default NULL AFTER tax_rate, + ADD COLUMN tax_rate_on_receiving decimal(6,4) default NULL AFTER tax_rate_on_ordering, + ADD COLUMN tax_value_on_ordering decimal(28,6) default NULL AFTER tax_value, + ADD COLUMN tax_value_on_receiving decimal(28,6) default NULL AFTER tax_value_on_ordering +|); + +my $orders = $dbh->selectall_arrayref(q| + SELECT * FROM aqorders +|, { Slice => {} } ); + +my $sth_update_order = $dbh->prepare(q| + UPDATE aqorders + SET tax_rate_on_ordering = tax_rate, + tax_rate_on_receiving = tax_rate, + tax_value_on_ordering = ?, + tax_value_on_receiving = ? + WHERE ordernumber = ? +|); + +require Koha::Number::Price; +for my $order (@$orders) { + my $tax_value_on_ordering = + $order->{quantity} * + $order->{ecost_tax_excluded} * + $order->{tax_rate}; + + my $tax_value_on_receiving = + ( defined $order->{unitprice_tax_excluded} ) + ? $order->{quantity} * $order->{unitprice_tax_excluded} * $order->{tax_rate} + : undef; + + $sth_update_order->execute( $tax_value_on_ordering, + $tax_value_on_receiving, $order->{ordernumber} ); +} + +# Remove the old columns +$dbh->do(q| + ALTER TABLE aqorders + CHANGE COLUMN tax_value tax_value_bak decimal(28,6) default NULL, + CHANGE COLUMN tax_rate tax_rate_bak decimal(6,4) default NULL +|); diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 0940eca..50d46fb 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3080,8 +3080,12 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items `ecost` decimal(13,2) DEFAULT NULL, -- the replacement cost for this line item `ecost_tax_excluded` decimal(28,6) default NULL, -- the estimated cost excluding tax `ecost_tax_included` decimal(28,6) default NULL, -- the estimated cost including tax - `tax_rate` decimal(6,4) DEFAULT NULL, -- the tax rate for this line item (%) - `tax_value` decimal(28,6) default NULL, -- the tax value for this line item + `tax_rate_bak` decimal(6,4) DEFAULT NULL, -- the tax rate for this line item (%) + `tax_rate_on_ordering` decimal(6,4) DEFAULT NULL, -- the tax rate on ordering for this line item (%) + `tax_rate_on_receiving` decimal(6,4) DEFAULT NULL, -- the tax rate on receiving for this line item (%) + `tax_value_bak` decimal(28,6) default NULL, -- the tax value for this line item + `tax_value_on_ordering` decimal(28,6) DEFAULT NULL, -- the tax value on ordering for this line item + `tax_value_on_receiving` decimal(28,6) DEFAULT NULL, -- the tax value on receiving for this line item `discount` float(6,4) default NULL, -- the discount for this line item (%) `budget_id` int(11) NOT NULL, -- the fund this order goes against (aqbudgets.budget_id) `budgetgroup_id` int(11) NOT NULL, -- not used? always zero -- 1.9.1