salix/db/routines/vn/functions/getAlert3State.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` FUNCTION `vn`.`getAlert3State`(vTicket INT)
	RETURNS varchar(45) CHARSET utf8mb3 COLLATE utf8mb3_unicode_ci
	NOT DETERMINISTIC
	READS SQL DATA
BEGIN
	DECLARE vDeliveryType INTEGER DEFAULT 0;
	DECLARE isWaitingForPickUp BOOLEAN DEFAULT FALSE;
	DECLARE vCode VARCHAR(45);
		
	SELECT am.deliveryMethodFk
	INTO vDeliveryType 
		FROM ticket t
			JOIN agencyMode am ON am.id = t.agencyModeFk
	WHERE t.id = vTicket;
		
		CASE vDeliveryType
			WHEN 1 THEN -- AGENCIAS
				SET vCode = 'DELIVERED';
					   
			WHEN 2 THEN -- REPARTO
				SET vCode = 'ON_DELIVERY';
					
			ELSE -- MERCADO, OTROS
				SELECT MIN(t.warehouseFk <> w.id) INTO isWaitingForPickUp
					FROM ticket t 
						LEFT JOIN warehouse w
							ON w.pickUpagencyModeFk = t.agencyModeFk
					WHERE t.id = vTicket;
						
			IF isWaitingForPickUp THEN
				SET vCode = 'WAITING_FOR_PICKUP';
					ELSE
				SET vCode = 'DELIVERED';
			END IF;
		END CASE;
	RETURN vCode;
END$$
DELIMITER ;