salix/db/routines/vn/events/envialiaThreHoldChecker.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` EVENT `vn`.`envialiaThreHoldChecker`
	ON SCHEDULE EVERY 1 DAY
		STARTS '2022-01-28 09:52:46.000'
	ON COMPLETION NOT PRESERVE
	ENABLE
DO BEGIN
	DECLARE vActualNumber BIGINT;
	DECLARE vEndRange BIGINT;
    DECLARE vIsAlreadyNotified BOOLEAN;
    DECLARE vNotificationThrehold INT;

	SELECT actualNumber, endRange, isAlreadyNotified, notificationThrehold
			INTO vActualNumber, vEndRange, vIsAlreadyNotified, vNotificationThrehold
		FROM envialiaConfig;

	IF (vActualNumber+vNotificationThrehold) >= vEndRange AND
        vIsAlreadyNotified = FALSE
	THEN
		CALL mail_insert(
		 	'cau@verdnatura.es',
		 	NULL,
			'Hablar con envialia, acercandonos al numero final del rango permitido para las etiquetas',
            CONCAT('Numero actual: ', vActualNumber, ' , numero final: ',vEndRange)
		);
		UPDATE envialiaConfig SET isAlreadyNotified = TRUE;
	END IF;

	IF (vActualNumber+vNotificationThrehold) < vEndRange AND
        vIsAlreadyNotified = TRUE
	THEN
		UPDATE envialiaConfig SET isAlreadyNotified = FALSE;
	END IF;

END$$
DELIMITER ;