View | Details | Raw Unified | Return to bug 13321
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/bug_13321.perl (+108 lines)
Line 0 Link Here
1
#!/usr/bin/env perl
2
3
use Modern::Perl;
4
5
use C4::Context;
6
7
my $dbh = C4::Context->dbh;
8
9
$dbh->do(q|
10
    ALTER TABLE aqorders
11
        ADD COLUMN unitprice_tax_excluded decimal(28,6) default NULL AFTER unitprice,
12
        ADD COLUMN unitprice_tax_included decimal(28,6) default NULL AFTER unitprice_tax_excluded,
13
        ADD COLUMN rrp_tax_excluded decimal(28,6) default NULL AFTER rrp,
14
        ADD COLUMN rrp_tax_included decimal(28,6) default NULL AFTER rrp_tax_excluded,
15
        ADD COLUMN ecost_tax_excluded decimal(28,6) default NULL AFTER ecost,
16
        ADD COLUMN ecost_tax_included decimal(28,6) default NULL AFTER ecost_tax_excluded,
17
        ADD COLUMN tax_value decimal(6,4) default NULL AFTER gstrate
18
|);
19
20
# rename gstrate with tax_rate
21
$dbh->do(q|ALTER TABLE aqorders CHANGE COLUMN gstrate tax_rate decimal(6,4) DEFAULT NULL|);
22
$dbh->do(q|ALTER TABLE aqbooksellers CHANGE COLUMN gstrate tax_rate decimal(6,4) DEFAULT NULL|);
23
24
# Fill the new columns
25
my $orders = $dbh->selectall_arrayref(q|
26
    SELECT * FROM aqorders
27
|, { Slice => {} } );
28
29
my $sth_update_order = $dbh->prepare(q|
30
    UPDATE aqorders
31
    SET unitprice_tax_excluded = ?,
32
        unitprice_tax_included = ?,
33
        rrp_tax_excluded = ?,
34
        rrp_tax_included = ?,
35
        ecost_tax_excluded = ?,
36
        ecost_tax_included = ?,
37
        tax_value = ?
38
    WHERE ordernumber = ?
39
|);
40
41
my $sth_get_bookseller = $dbh->prepare(q|
42
    SELECT aqbooksellers.*
43
    FROM aqbooksellers
44
    LEFT JOIN aqbasket ON aqbasket.booksellerid = aqbooksellers.id
45
    LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
46
    WHERE ordernumber = ?
47
|);
48
49
require Koha::Number::Price;
50
for my $order ( @$orders ) {
51
    $sth_get_bookseller->execute( $order->{ordernumber} );
52
    my ( $bookseller ) = $sth_get_bookseller->fetchrow_hashref;
53
    $order->{rrp}   = Koha::Number::Price->new( $order->{rrp} )->round;
54
    $order->{ecost} = Koha::Number::Price->new( $order->{ecost} )->round;
55
    $order->{tax_rate} ||= 0 ; # tax_rate can be NULL in DB
56
    # Ordering
57
    if ( $bookseller->{listincgst} ) {
58
        $order->{rrp_tax_included} = $order->{rrp};
59
        $order->{rrp_tax_excluded} = Koha::Number::Price->new(
60
            $order->{rrp_tax_included} / ( 1 + $order->{tax_rate} ) )->round;
61
        $order->{ecost_tax_included} = $order->{ecost};
62
        $order->{ecost_tax_excluded} = Koha::Number::Price->new(
63
            $order->{ecost} / ( 1 + $order->{tax_rate} ) )->round;
64
    }
65
    else {
66
        $order->{rrp_tax_excluded} = $order->{rrp};
67
        $order->{rrp_tax_included} = Koha::Number::Price->new(
68
            $order->{rrp} * ( 1 + $order->{tax_rate} ) )->round;
69
        $order->{ecost_tax_excluded} = $order->{ecost};
70
        $order->{ecost_tax_included} = Koha::Number::Price->new(
71
            $order->{ecost} * ( 1 + $order->{tax_rate} ) )->round;
72
    }
73
74
    #receiving
75
    if ( $bookseller->{listincgst} ) {
76
        $order->{unitprice_tax_included} = Koha::Number::Price->new( $order->{unitprice} )->round;
77
        $order->{unitprice_tax_excluded} = Koha::Number::Price->new(
78
          $order->{unitprice_tax_included} / ( 1 + $order->{tax_rate} ) )->round;
79
    }
80
    else {
81
        $order->{unitprice_tax_excluded} = Koha::Number::Price->new( $order->{unitprice} )->round;
82
        $order->{unitprice_tax_included} = Koha::Number::Price->new(
83
          $order->{unitprice_tax_excluded} * ( 1 + $order->{tax_rate} ) )->round;
84
    }
85
86
    # If the order is received, the tax is calculated from the unit price
87
    if ( $order->{orderstatus} eq 'complete' ) {
88
        $order->{tax_value} = Koha::Number::Price->new(
89
          ( $order->{unitprice_tax_included} - $order->{unitprice_tax_excluded} )
90
          * $order->{quantity} )->round;
91
    } else {
92
        # otherwise the ecost is used
93
        $order->{tax_value} = Koha::Number::Price->new(
94
            ( $order->{ecost_tax_included} - $order->{ecost_tax_excluded} ) *
95
              $order->{quantity} )->round;
96
    }
97
98
    $sth_update_order->execute(
99
        $order->{unitprice_tax_excluded},
100
        $order->{unitprice_tax_included},
101
        $order->{rrp_tax_excluded},
102
        $order->{rrp_tax_included},
103
        $order->{ecost_tax_excluded},
104
        $order->{ecost_tax_included},
105
        $order->{tax_value},
106
        $order->{ordernumber},
107
    );
108
}
(-)a/installer/data/mysql/kohastructure.sql (-7 / +13 lines)
Lines 3062-3069 CREATE TABLE `aqorders` ( -- information related to the basket line items Link Here
3062
  `listprice` decimal(28,6) default NULL, -- the vendor price for this line item
3062
  `listprice` decimal(28,6) default NULL, -- the vendor price for this line item
3063
  `datereceived` date default NULL, -- the date this order was received
3063
  `datereceived` date default NULL, -- the date this order was received
3064
  invoiceid int(11) default NULL, -- id of invoice
3064
  invoiceid int(11) default NULL, -- id of invoice
3065
  `freight` decimal(28,6) default NULL, -- shipping costs (not used)
3065
  `freight` decimal(28,6) DEFAULT NULL, -- shipping costs (not used)
3066
  `unitprice` decimal(28,6) default NULL, -- the actual cost entered when receiving this line item
3066
  `unitprice` decimal(28,6) DEFAULT NULL, -- the actual cost entered when receiving this line item
3067
  `unitprice_tax_excluded` decimal(28,6) default NULL, -- the unit price excluding tax (on receiving)
3068
  `unitprice_tax_included` decimal(28,6) default NULL, -- the unit price including tax (on receiving)
3067
  `quantityreceived` smallint(6) NOT NULL default 0, -- the quantity that have been received so far
3069
  `quantityreceived` smallint(6) NOT NULL default 0, -- the quantity that have been received so far
3068
  `datecancellationprinted` date default NULL, -- the date the line item was deleted
3070
  `datecancellationprinted` date default NULL, -- the date the line item was deleted
3069
  `cancellationreason` text default NULL, -- reason of cancellation
3071
  `cancellationreason` text default NULL, -- reason of cancellation
Lines 3072-3081 CREATE TABLE `aqorders` ( -- information related to the basket line items Link Here
3072
  `purchaseordernumber` mediumtext, -- not used? always NULL
3074
  `purchaseordernumber` mediumtext, -- not used? always NULL
3073
  `basketno` int(11) default NULL, -- links this order line to a specific basket (aqbasket.basketno)
3075
  `basketno` int(11) default NULL, -- links this order line to a specific basket (aqbasket.basketno)
3074
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order line was last modified
3076
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order line was last modified
3075
  `rrp` decimal(13,2) default NULL, -- the replacement cost for this line item
3077
  `rrp` decimal(13,2) DEFAULT NULL, -- the replacement cost for this line item
3076
  `ecost` decimal(13,2) default NULL, -- the estimated cost for this line item
3078
  `rrp_tax_excluded` decimal(28,6) default NULL, -- the replacement cost excluding tax
3077
  `gstrate` decimal(6,4) default NULL, -- the tax rate for this line item
3079
  `rrp_tax_included` decimal(28,6) default NULL, -- the replacement cost including tax
3078
  `discount` float(6,4) default NULL, -- the discount for this line item
3080
  `ecost` decimal(13,2) DEFAULT NULL, -- the replacement cost for this line item
3081
  `ecost_tax_excluded` decimal(28,6) default NULL, -- the estimated cost excluding tax
3082
  `ecost_tax_included` decimal(28,6) default NULL, -- the estimated cost including tax
3083
  `tax_rate` decimal(6,4) DEFAULT NULL, -- the tax rate for this line item (%)
3084
  `tax_value` decimal(28,6) default NULL, -- the tax value for this line item
3085
  `discount` float(6,4) default NULL, -- the discount for this line item (%)
3079
  `budget_id` int(11) NOT NULL, -- the fund this order goes against (aqbudgets.budget_id)
3086
  `budget_id` int(11) NOT NULL, -- the fund this order goes against (aqbudgets.budget_id)
3080
  `budgetgroup_id` int(11) NOT NULL, -- not used? always zero
3087
  `budgetgroup_id` int(11) NOT NULL, -- not used? always zero
3081
  `budgetdate` date default NULL, -- not used? always NULL
3088
  `budgetdate` date default NULL, -- not used? always NULL
3082
- 

Return to bug 13321