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