- if ( $dbdriver eq "mysql" ) {
- $strsth = "
- SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
- FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
- LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
- WHERE (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY)
- AND (datereceived = '' OR datereceived IS NULL))
- ";
+ $query = "
+ SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
+ FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
+ LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
+ WHERE
+ ( datereceived = ''
+ OR datereceived IS NULL
+ OR aqorders.quantityreceived < aqorders.quantity
+ )
+ AND aqorders.rrp <> 0
+ AND aqorders.ecost <> 0
+ AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0
+ AND aqbasket.closedate IS NOT NULL
+ ";
+ if ( defined $delay && $delay >= 0 ) {
+ $query .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? + COALESCE(aqbooksellers.deliverytime,0) DAY)) ";
+ push @query_params, $delay;
+ } elsif ( $delay && $delay < 0 ){
+ warn 'WARNING: GetBooksellerWithLateOrders is called with a negative value';
+ return;
+ }
+ if ( defined $estimateddeliverydatefrom ) {
+ $query .= '
+ AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime,0) DAY) >= ?';
+ push @query_params, $estimateddeliverydatefrom;
+ if ( defined $estimateddeliverydateto ) {
+ $query .= ' AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime, 0) DAY) <= ?';
+ push @query_params, $estimateddeliverydateto;
+ } else {
+ $query .= ' AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime, 0) DAY) <= CAST(now() AS date)';
+ }