From 9beffec5aac0e42247633f5f4f793f40f11b4216 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Fri, 28 Oct 2016 14:23:24 +0000 Subject: [PATCH] Bug 13321 - DBRev 16.06.00.042 Signed-off-by: Kyle M Hall --- Koha.pm | 2 +- .../data/mysql/atomicupdate/bug_13321.perl | 108 ------------------ installer/data/mysql/updatedatabase.pl | 107 +++++++++++++++++ 3 files changed, 108 insertions(+), 109 deletions(-) delete mode 100644 installer/data/mysql/atomicupdate/bug_13321.perl diff --git a/Koha.pm b/Koha.pm index 0ef29fc885..e84c37ea04 100644 --- a/Koha.pm +++ b/Koha.pm @@ -29,7 +29,7 @@ use vars qw{ $VERSION }; # - #4 : the developer version. The 4th number is the database subversion. # used by developers when the database changes. updatedatabase take care of the changes itself # and is automatically called by Auth.pm when needed. -$VERSION = "16.06.00.041"; +$VERSION = "16.06.00.042"; sub version { return $VERSION; diff --git a/installer/data/mysql/atomicupdate/bug_13321.perl b/installer/data/mysql/atomicupdate/bug_13321.perl deleted file mode 100644 index 54bea794d3..0000000000 --- a/installer/data/mysql/atomicupdate/bug_13321.perl +++ /dev/null @@ -1,108 +0,0 @@ -#!/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/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 7249b2a113..308f33dd6e 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -13419,6 +13419,113 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } +$DBversion = '16.06.00.042'; +if ( CheckVersion($DBversion) ) { + $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}, + ); + } + + print "Upgrade to $DBversion done (Bug 13321 - Tax and prices calculation need to be fixed)\n"; + SetVersion($DBversion); +} + # DEVELOPER PROCESS, search for anything to execute in the db_update directory # SEE bug 13068 # if there is anything in the atomicupdate, read and execute it. -- 2.39.2