From 160b3c0ad3d70165d15edcad19b36943f50cdfe2 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. --- installer/data/mysql/kohastructure.sql | 19 ++++-- installer/data/mysql/updatedatabase.pl | 110 +++++++++++++++++++++++++++++++++ 2 files changed, 123 insertions(+), 6 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 8c777f7..f106048 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3030,8 +3030,10 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items `totalamount` decimal(28,6) default NULL, -- not used? always NULL `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 @@ -3041,10 +3043,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 diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 8682d2a..3273225 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -9480,6 +9480,116 @@ if (CheckVersion($DBversion)) { SetVersion($DBversion); } +$DBversion = '3.17.00.XXX'; +if ( CheckVersion($DBversion) ) { + + # Add the new columns + $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, + CHANGE COLUMN gstrate tax_rate decimal(6,4) default NULL + |); + + # 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->{status} 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}, + ); + } + + print "Upgrade to $DBversion done (Bug 13321 - Tax and prices calculation: add new columns to aqorders)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) -- 2.1.0