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 |
- |
|
|