29 lines
809 B
SQL
29 lines
809 B
SQL
DROP PROCEDURE IF EXISTS floranet.deliveryDate_get;
|
|
|
|
DELIMITER $$
|
|
$$
|
|
CREATE DEFINER=`root`@`localhost` PROCEDURE `floranet`.`deliveryDate_get`(vPostalCode VARCHAR(15))
|
|
READS SQL DATA
|
|
BEGIN
|
|
/**
|
|
* Returns available dates for this postalCode, in the next seven days.
|
|
*
|
|
* @param vPostalCode Delivery address postal code
|
|
*/
|
|
DECLARE vCurrentDayOfWeek INT;
|
|
|
|
SET vCurrentDayOfWeek = DAYOFWEEK(NOW());
|
|
|
|
SELECT DISTINCT nextDay
|
|
FROM (
|
|
SELECT CURDATE() + INTERVAL IF(
|
|
apc.dayOfWeek >= vCurrentDayOfWeek,
|
|
apc.dayOfWeek - vCurrentDayOfWeek,
|
|
7 - apc.dayOfWeek
|
|
) DAY nextDay,
|
|
NOW() + INTERVAL apc.hoursInAdvance - 12 HOUR minDeliveryTime
|
|
FROM addressPostCode apc
|
|
WHERE apc.postCode = vPostalCode
|
|
HAVING nextDay > minDeliveryTime) sub;
|
|
END$$
|
|
DELIMITER ; |