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

(-)a/installer/data/mysql/kohastructure.sql (-6 / +13 lines)
Lines 3030-3037 CREATE TABLE `aqorders` ( -- information related to the basket line items Link Here
3030
  `totalamount` decimal(28,6) default NULL, -- not used? always NULL
3030
  `totalamount` decimal(28,6) default NULL, -- not used? always NULL
3031
  `datereceived` date default NULL, -- the date this order was received
3031
  `datereceived` date default NULL, -- the date this order was received
3032
  invoiceid int(11) default NULL, -- id of invoice
3032
  invoiceid int(11) default NULL, -- id of invoice
3033
  `freight` decimal(28,6) default NULL, -- shipping costs (not used)
3033
  `freight` decimal(28,6) DEFAULT NULL, -- shipping costs (not used)
3034
  `unitprice` decimal(28,6) default NULL, -- the actual cost entered when receiving this line item
3034
  `unitprice` decimal(28,6) DEFAULT NULL, -- the actual cost entered when receiving this line item
3035
  `unitprice_tax_excluded` decimal(28,6) default NULL, -- the unit price excluding tax (on receiving)
3036
  `unitprice_tax_included` decimal(28,6) default NULL, -- the unit price including tax (on receiving)
3035
  `quantityreceived` smallint(6) NOT NULL default 0, -- the quantity that have been received so far
3037
  `quantityreceived` smallint(6) NOT NULL default 0, -- the quantity that have been received so far
3036
  `datecancellationprinted` date default NULL, -- the date the line item was deleted
3038
  `datecancellationprinted` date default NULL, -- the date the line item was deleted
3037
  `cancellationreason` text default NULL, -- reason of cancellation
3039
  `cancellationreason` text default NULL, -- reason of cancellation
Lines 3041-3050 CREATE TABLE `aqorders` ( -- information related to the basket line items Link Here
3041
  `purchaseordernumber` mediumtext, -- not used? always NULL
3043
  `purchaseordernumber` mediumtext, -- not used? always NULL
3042
  `basketno` int(11) default NULL, -- links this order line to a specific basket (aqbasket.basketno)
3044
  `basketno` int(11) default NULL, -- links this order line to a specific basket (aqbasket.basketno)
3043
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order line was last modified
3045
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order line was last modified
3044
  `rrp` decimal(13,2) default NULL, -- the replacement cost for this line item
3046
  `rrp` decimal(13,2) DEFAULT NULL, -- the replacement cost for this line item
3045
  `ecost` decimal(13,2) default NULL, -- the estimated cost for this line item
3047
  `rrp_tax_excluded` decimal(28,6) default NULL, -- the replacement cost excluding tax
3046
  `gstrate` decimal(6,4) default NULL, -- the tax rate for this line item
3048
  `rrp_tax_included` decimal(28,6) default NULL, -- the replacement cost including tax
3047
  `discount` float(6,4) default NULL, -- the discount for this line item
3049
  `ecost` decimal(13,2) DEFAULT NULL, -- the replacement cost for this line item
3050
  `ecost_tax_excluded` decimal(28,6) default NULL, -- the estimated cost excluding tax
3051
  `ecost_tax_included` decimal(28,6) default NULL, -- the estimated cost including tax
3052
  `tax_rate` decimal(6,4) DEFAULT NULL, -- the tax rate for this line item (%)
3053
  `tax_value` decimal(28,6) default NULL, -- the tax value for this line item
3054
  `discount` float(6,4) default NULL, -- the discount for this line item (%)
3048
  `budget_id` int(11) NOT NULL, -- the fund this order goes against (aqbudgets.budget_id)
3055
  `budget_id` int(11) NOT NULL, -- the fund this order goes against (aqbudgets.budget_id)
3049
  `budgetgroup_id` int(11) NOT NULL, -- not used? always zero
3056
  `budgetgroup_id` int(11) NOT NULL, -- not used? always zero
3050
  `budgetdate` date default NULL, -- not used? always NULL
3057
  `budgetdate` date default NULL, -- not used? always NULL
(-)a/installer/data/mysql/updatedatabase.pl (-1 / +110 lines)
Lines 9480-9485 if (CheckVersion($DBversion)) { Link Here
9480
    SetVersion($DBversion);
9480
    SetVersion($DBversion);
9481
}
9481
}
9482
9482
9483
$DBversion = '3.17.00.XXX';
9484
if ( CheckVersion($DBversion) ) {
9485
9486
    # Add the new columns
9487
    $dbh->do(q|
9488
        ALTER TABLE aqorders
9489
            ADD COLUMN unitprice_tax_excluded decimal(28,6) default NULL AFTER unitprice,
9490
            ADD COLUMN unitprice_tax_included decimal(28,6) default NULL AFTER unitprice_tax_excluded,
9491
            ADD COLUMN rrp_tax_excluded decimal(28,6) default NULL AFTER rrp,
9492
            ADD COLUMN rrp_tax_included decimal(28,6) default NULL AFTER rrp_tax_excluded,
9493
            ADD COLUMN ecost_tax_excluded decimal(28,6) default NULL AFTER ecost,
9494
            ADD COLUMN ecost_tax_included decimal(28,6) default NULL AFTER ecost_tax_excluded,
9495
            ADD COLUMN tax_value decimal(6,4) default NULL AFTER gstrate,
9496
            CHANGE COLUMN gstrate tax_rate decimal(6,4) default NULL
9497
    |);
9498
9499
    # rename gstrate with tax_rate
9500
    $dbh->do(q|ALTER TABLE aqorders CHANGE COLUMN gstrate tax_rate decimal(6,4) DEFAULT NULL|);
9501
    $dbh->do(q|ALTER TABLE aqbooksellers CHANGE COLUMN gstrate tax_rate decimal(6,4) DEFAULT NULL|);
9502
9503
    # Fill the new columns
9504
    my $orders = $dbh->selectall_arrayref(q|
9505
        SELECT * FROM aqorders
9506
    |, { Slice => {} } );
9507
9508
    my $sth_update_order = $dbh->prepare(q|
9509
        UPDATE aqorders
9510
        SET unitprice_tax_excluded = ?,
9511
            unitprice_tax_included = ?,
9512
            rrp_tax_excluded = ?,
9513
            rrp_tax_included = ?,
9514
            ecost_tax_excluded = ?,
9515
            ecost_tax_included = ?,
9516
            tax_value = ?
9517
        WHERE ordernumber = ?
9518
    |);
9519
9520
    my $sth_get_bookseller = $dbh->prepare(q|
9521
        SELECT aqbooksellers.*
9522
        FROM aqbooksellers
9523
        LEFT JOIN aqbasket ON aqbasket.booksellerid = aqbooksellers.id
9524
        LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
9525
        WHERE ordernumber = ?
9526
    |);
9527
9528
    require Koha::Number::Price;
9529
    for my $order ( @$orders ) {
9530
        $sth_get_bookseller->execute( $order->{ordernumber} );
9531
        my ( $bookseller ) = $sth_get_bookseller->fetchrow_hashref;
9532
        $order->{rrp}   = Koha::Number::Price->new( $order->{rrp} )->round;
9533
        $order->{ecost} = Koha::Number::Price->new( $order->{ecost} )->round;
9534
        $order->{tax_rate} ||= 0 ; # tax_rate can be NULL in DB
9535
        # Ordering
9536
        if ( $bookseller->{listincgst} ) {
9537
            $order->{rrp_tax_included} = $order->{rrp};
9538
            $order->{rrp_tax_excluded} = Koha::Number::Price->new(
9539
                $order->{rrp_tax_included} / ( 1 + $order->{tax_rate} ) )->round;
9540
            $order->{ecost_tax_included} = $order->{ecost};
9541
            $order->{ecost_tax_excluded} = Koha::Number::Price->new(
9542
                $order->{ecost} / ( 1 + $order->{tax_rate} ) )->round;
9543
        }
9544
        else {
9545
            $order->{rrp_tax_excluded} = $order->{rrp};
9546
            $order->{rrp_tax_included} = Koha::Number::Price->new(
9547
                $order->{rrp} * ( 1 + $order->{tax_rate} ) )->round;
9548
            $order->{ecost_tax_excluded} = $order->{ecost};
9549
            $order->{ecost_tax_included} = Koha::Number::Price->new(
9550
                $order->{ecost} * ( 1 + $order->{tax_rate} ) )->round;
9551
        }
9552
9553
        #receiving
9554
        if ( $bookseller->{listincgst} ) {
9555
            $order->{unitprice_tax_included} = Koha::Number::Price->new( $order->{unitprice} )->round;
9556
            $order->{unitprice_tax_excluded} = Koha::Number::Price->new(
9557
              $order->{unitprice_tax_included} / ( 1 + $order->{tax_rate} ) )->round;
9558
        }
9559
        else {
9560
            $order->{unitprice_tax_excluded} = Koha::Number::Price->new( $order->{unitprice} )->round;
9561
            $order->{unitprice_tax_included} = Koha::Number::Price->new(
9562
              $order->{unitprice_tax_excluded} * ( 1 + $order->{tax_rate} ) )->round;
9563
        }
9564
9565
        # If the order is received, the tax is calculated from the unit price
9566
        if ( $order->{status} eq 'complete' ) {
9567
            $order->{tax_value} = Koha::Number::Price->new(
9568
              ( $order->{unitprice_tax_included} - $order->{unitprice_tax_excluded} )
9569
              * $order->{quantity} )->round;
9570
        } else {
9571
            # otherwise the ecost is used
9572
            $order->{tax_value} = Koha::Number::Price->new(
9573
                ( $order->{ecost_tax_included} - $order->{ecost_tax_excluded} ) *
9574
                  $order->{quantity} )->round;
9575
        }
9576
9577
        $sth_update_order->execute(
9578
            $order->{unitprice_tax_excluded},
9579
            $order->{unitprice_tax_included},
9580
            $order->{rrp_tax_excluded},
9581
            $order->{rrp_tax_included},
9582
            $order->{ecost_tax_excluded},
9583
            $order->{ecost_tax_included},
9584
            $order->{tax_value},
9585
            $order->{ordernumber},
9586
        );
9587
    }
9588
9589
    print "Upgrade to $DBversion done (Bug 13321 - Tax and prices calculation: add new columns to aqorders)\n";
9590
    SetVersion($DBversion);
9591
}
9592
9483
=head1 FUNCTIONS
9593
=head1 FUNCTIONS
9484
9594
9485
=head2 TableExists($table)
9595
=head2 TableExists($table)
9486
- 

Return to bug 13321