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