From fdbd6f9338ddfafc1411af4697db5dc5511fd5fe Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Wed, 19 Nov 2014 16:52:01 +0100 Subject: [PATCH] Bug 13321: Tax and prices calculation - DB Changes This patch adds 7 columns to the aqorders table: * unitprice_tax_excluded * unitprice_tax_included * rrp_tax_excluded * rrp_tax_included * ecost_tax_excluded * ecost_tax_included * tax_value It also renames: * aqorders.gstrate with aqorders.tax_rate * aqbooksellers.gstrate with aqbooksellers.tax_rate The new columns are filled with the previous calculation method. Signed-off-by: Laurence Rault Signed-off-by: Francois Charbonnier Signed-off-by: Sonia Bouis --- installer/data/mysql/atomicupdate/bug_13321.perl | 108 +++++++++++++++++++++++ installer/data/mysql/kohastructure.sql | 19 ++-- 2 files changed, 121 insertions(+), 6 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_13321.perl diff --git a/installer/data/mysql/atomicupdate/bug_13321.perl b/installer/data/mysql/atomicupdate/bug_13321.perl new file mode 100644 index 0000000..54bea79 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_13321.perl @@ -0,0 +1,108 @@ +#!/usr/bin/env perl + +use Modern::Perl; + +use C4::Context; + +my $dbh = C4::Context->dbh; + +$dbh->do(q| + ALTER TABLE aqorders + ADD COLUMN unitprice_tax_excluded decimal(28,6) default NULL AFTER unitprice, + ADD COLUMN unitprice_tax_included decimal(28,6) default NULL AFTER unitprice_tax_excluded, + ADD COLUMN rrp_tax_excluded decimal(28,6) default NULL AFTER rrp, + ADD COLUMN rrp_tax_included decimal(28,6) default NULL AFTER rrp_tax_excluded, + ADD COLUMN ecost_tax_excluded decimal(28,6) default NULL AFTER ecost, + ADD COLUMN ecost_tax_included decimal(28,6) default NULL AFTER ecost_tax_excluded, + ADD COLUMN tax_value decimal(6,4) default NULL AFTER gstrate +|); + +# rename gstrate with tax_rate +$dbh->do(q|ALTER TABLE aqorders CHANGE COLUMN gstrate tax_rate decimal(6,4) DEFAULT NULL|); +$dbh->do(q|ALTER TABLE aqbooksellers CHANGE COLUMN gstrate tax_rate decimal(6,4) DEFAULT NULL|); + +# Fill the new columns +my $orders = $dbh->selectall_arrayref(q| + SELECT * FROM aqorders +|, { Slice => {} } ); + +my $sth_update_order = $dbh->prepare(q| + UPDATE aqorders + SET unitprice_tax_excluded = ?, + unitprice_tax_included = ?, + rrp_tax_excluded = ?, + rrp_tax_included = ?, + ecost_tax_excluded = ?, + ecost_tax_included = ?, + tax_value = ? + WHERE ordernumber = ? +|); + +my $sth_get_bookseller = $dbh->prepare(q| + SELECT aqbooksellers.* + FROM aqbooksellers + LEFT JOIN aqbasket ON aqbasket.booksellerid = aqbooksellers.id + LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno + WHERE ordernumber = ? +|); + +require Koha::Number::Price; +for my $order ( @$orders ) { + $sth_get_bookseller->execute( $order->{ordernumber} ); + my ( $bookseller ) = $sth_get_bookseller->fetchrow_hashref; + $order->{rrp} = Koha::Number::Price->new( $order->{rrp} )->round; + $order->{ecost} = Koha::Number::Price->new( $order->{ecost} )->round; + $order->{tax_rate} ||= 0 ; # tax_rate can be NULL in DB + # Ordering + if ( $bookseller->{listincgst} ) { + $order->{rrp_tax_included} = $order->{rrp}; + $order->{rrp_tax_excluded} = Koha::Number::Price->new( + $order->{rrp_tax_included} / ( 1 + $order->{tax_rate} ) )->round; + $order->{ecost_tax_included} = $order->{ecost}; + $order->{ecost_tax_excluded} = Koha::Number::Price->new( + $order->{ecost} / ( 1 + $order->{tax_rate} ) )->round; + } + else { + $order->{rrp_tax_excluded} = $order->{rrp}; + $order->{rrp_tax_included} = Koha::Number::Price->new( + $order->{rrp} * ( 1 + $order->{tax_rate} ) )->round; + $order->{ecost_tax_excluded} = $order->{ecost}; + $order->{ecost_tax_included} = Koha::Number::Price->new( + $order->{ecost} * ( 1 + $order->{tax_rate} ) )->round; + } + + #receiving + if ( $bookseller->{listincgst} ) { + $order->{unitprice_tax_included} = Koha::Number::Price->new( $order->{unitprice} )->round; + $order->{unitprice_tax_excluded} = Koha::Number::Price->new( + $order->{unitprice_tax_included} / ( 1 + $order->{tax_rate} ) )->round; + } + else { + $order->{unitprice_tax_excluded} = Koha::Number::Price->new( $order->{unitprice} )->round; + $order->{unitprice_tax_included} = Koha::Number::Price->new( + $order->{unitprice_tax_excluded} * ( 1 + $order->{tax_rate} ) )->round; + } + + # If the order is received, the tax is calculated from the unit price + if ( $order->{orderstatus} eq 'complete' ) { + $order->{tax_value} = Koha::Number::Price->new( + ( $order->{unitprice_tax_included} - $order->{unitprice_tax_excluded} ) + * $order->{quantity} )->round; + } else { + # otherwise the ecost is used + $order->{tax_value} = Koha::Number::Price->new( + ( $order->{ecost_tax_included} - $order->{ecost_tax_excluded} ) * + $order->{quantity} )->round; + } + + $sth_update_order->execute( + $order->{unitprice_tax_excluded}, + $order->{unitprice_tax_included}, + $order->{rrp_tax_excluded}, + $order->{rrp_tax_included}, + $order->{ecost_tax_excluded}, + $order->{ecost_tax_included}, + $order->{tax_value}, + $order->{ordernumber}, + ); +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e6e3142..25f4430 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3085,8 +3085,10 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items `listprice` decimal(28,6) default NULL, -- the vendor price for this line item `datereceived` date default NULL, -- the date this order was received invoiceid int(11) default NULL, -- id of invoice - `freight` decimal(28,6) default NULL, -- shipping costs (not used) - `unitprice` decimal(28,6) default NULL, -- the actual cost entered when receiving this line item + `freight` decimal(28,6) DEFAULT NULL, -- shipping costs (not used) + `unitprice` decimal(28,6) DEFAULT NULL, -- the actual cost entered when receiving this line item + `unitprice_tax_excluded` decimal(28,6) default NULL, -- the unit price excluding tax (on receiving) + `unitprice_tax_included` decimal(28,6) default NULL, -- the unit price including tax (on receiving) `quantityreceived` smallint(6) NOT NULL default 0, -- the quantity that have been received so far `datecancellationprinted` date default NULL, -- the date the line item was deleted `cancellationreason` text default NULL, -- reason of cancellation @@ -3095,10 +3097,15 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items `purchaseordernumber` mediumtext, -- not used? always NULL `basketno` int(11) default NULL, -- links this order line to a specific basket (aqbasket.basketno) `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order line was last modified - `rrp` decimal(13,2) default NULL, -- the replacement cost for this line item - `ecost` decimal(13,2) default NULL, -- the estimated cost for this line item - `gstrate` decimal(6,4) default NULL, -- the tax rate for this line item - `discount` float(6,4) default NULL, -- the discount for this line item + `rrp` decimal(13,2) DEFAULT NULL, -- the replacement cost for this line item + `rrp_tax_excluded` decimal(28,6) default NULL, -- the replacement cost excluding tax + `rrp_tax_included` decimal(28,6) default NULL, -- the replacement cost including tax + `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 + `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 `budgetdate` date default NULL, -- not used? always NULL -- 2.1.4