|
Line 0
Link Here
|
|
|
1 |
use Date::Calc qw( Add_Delta_Days ); |
| 2 |
$DBversion = 'XXX'; |
| 3 |
if( CheckVersion( $DBversion ) ) { |
| 4 |
unless( column_exists( 'aqorders', 'estimated_delivery_date' ) ) { |
| 5 |
$dbh->do(q{ ALTER TABLE aqorders ADD estimated_delivery_date date default null AFTER suppliers_report }); |
| 6 |
my $orders = $dbh->selectall_arrayref("SELECT ordernumber, orderstatus, basketno FROM aqorders", { Slice => {} }); |
| 7 |
foreach my $order ( @$orders ) { |
| 8 |
my $bookseller = $dbh->selectrow_hashref("SELECT deliverytime FROM aqbooksellers WHERE id = |
| 9 |
(SELECT booksellerid FROM aqbasket WHERE basketno = |
| 10 |
(SELECT basketno FROM aqorders WHERE ordernumber = $order->{ordernumber}) |
| 11 |
) |
| 12 |
"); |
| 13 |
my $deliverytime = $bookseller->{deliverytime} || 0; |
| 14 |
if ( $order->{orderstatus} eq 'ordered' or $order->{orderstatus} eq 'complete' ) { |
| 15 |
# the basket has been closed and will have a closedate |
| 16 |
my $basket = $dbh->selectrow_hashref("SELECT closedate FROM aqbasket WHERE basketno = $order->{basketno}"); |
| 17 |
my ($year, $month, $day) = ( $basket->{closedate} =~ /(\d+)-(\d+)-(\d+)/ ); |
| 18 |
($year, $month, $day) = Add_Delta_Days($year, $month, $day, $deliverytime); |
| 19 |
my $estimated_delivery_date = sprintf( "%04d-%02d-%02d", $year, $month, $day ); |
| 20 |
$dbh->do(qq{ UPDATE aqorders SET estimated_delivery_date = $estimated_delivery_date WHERE ordernumber = $order->{ordernumber} }); |
| 21 |
} else { |
| 22 |
# we have no closedate to use in calculation, so use today's date |
| 23 |
$dbh->do(qq{ UPDATE aqorders SET estimated_delivery_date = DATE_ADD(NOW(), INTERVAL $deliverytime DAYS) WHERE ordernumber = $order->{ordernumber} }); |
| 24 |
} |
| 25 |
} |
| 26 |
|
| 27 |
NewVersion( $DBversion, 15348, "Add new column aqorders.estimated_delivery_date and pre-fill with calculated dates" ); |
| 28 |
} |
| 29 |
} |