Attachment 51646 Details for Bug 13321 – Bug 13321: Tax and prices calculation - DB Changes

[patch] Bug 13321: Tax and prices calculation - DB Changes

Bug-13321-Tax-and-prices-calculation---DB-Changes.patch (text/plain), 8.49 KB, created by Alex Arnaud on 2016-05-20 12:27:07 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Alex Arnaud

Created: 2016-05-20 12:27:07 UTC

Size: 8.49 KB

patch

obsolete

>From 3b4c73f3ca78ae28ffc61ec52f1f24e98aa2d5a1 Mon Sep 17 00:00:00 2001
>From: Jonathan Druart <jonathan.druart@biblibre.com>
>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 <laurence.rault@biblibre.com>
>
>Signed-off-by: Francois Charbonnier <francois.charbonnier@inlibro.com>
>
>Signed-off-by: Sonia Bouis <sonia.bouis@univ-lyon3.fr>
>---
> 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 781d26d..3e5e197 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -3139,8 +3139,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
>@@ -3149,10 +3151,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)
>   `budgetdate` date default NULL, -- not used? always NULL
>   `sort1` varchar(80) default NULL, -- statistical field
>-- 
>2.7.0

Actions: View | Diff | Splinter Review

Attachments on bug 13321: 33789 | 33790 | 33791 | 33792 | 33793 | 34432 | 35152 | 35165 | 47489 | 47490 | 47491 | 47492 | 47493 | 47494 | 48867 | 48868 | 48869 | 48870 | 48871 | 48872 | 51080 | 51081 | 51082 | 51083 | 51084 | 51085 | 51643 | 51644 | 51645 | 51646 | 51647 | 51648 | 51649 | 52046 | 55168 | 55415 | 55416 | 55417 | 55418 | 55419 | 55420 | 55421 | 55422 | 55423 | 55427 | 56408 | 56422 | 56423 | 56424 | 56425 | 56426 | 56427 | 56428 | 56429 | 56430 | 56431 | 56432 | 56898 | 56899 | 56900 | 56901 | 56902 | 56903 | 56904 | 56905 | 56906 | 56907 | 56908 | 56909 | 56930 | 56959