Stored procedure for sending emails at specified times after license expiration

Question

I need to send email notifications for expired licenses:

One Day Before/After Expiration
Two Days Before/After Expiration
Three Days Before/After Expiration
One Week Before/After Expiration
Two Weeks Before/After Expiration
One Month Before/After Expiration
Two Months Before/After Expiration
Three Months Before/After Expiration
At the time of Expiration

This is the Stored procedure to fetch expired licenses.

DELIMITER $$

DROP PROCEDURE IF EXISTS `licensetrack`$$

CREATE  PROCEDURE `licensetrack`()

BEGIN

               DECLARE varOneDay   INT;
               DECLARE varTwoDay   INT;
               DECLARE varThreeDay   INT;
               DECLARE varOneWeek  INT;
               DECLARE varTwoWeek  INT;
               DECLARE varOneMonth   INT;
               DECLARE varTwoMonth   INT;
               DECLARE varThreeMonth  INT;
               DECLARE varAtTheTime   INT;

               DROP TEMPORARY TABLE IF EXISTS templicense;

               CREATE TEMPORARY TABLE IF NOT EXISTS templicense(
               TYPE VARCHAR(50) NOT NULL,
               entityID BIGINT NOT NULL,
               expirationDate DATE,
               beforeOneDay DATE,
               afterOneDay DATE,
               beforeTwoDay DATE,
               afterTwoDay DATE,
               beforeThreeDay DATE,
               afterThreeDay DATE,
               beforeOneWeek DATE,
               afterOneWeek DATE,
               beforeTwoWeek DATE,
               afterTwoWeek DATE,
               beforeOneMonth DATE,
               afterOneMonth DATE,
               beforeTwoMonth DATE,
               afterTwoMonth DATE,
               beforeThreeMonth DATE,
               afterThreeMonth DATE,
               atTheTime DATE

               ) ;


               INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
               afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
               SELECT 'Organ' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
               DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
               DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
               DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
               DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
               DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
               DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
               DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
               DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
                DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
               FROM organizationlicenses orglic;


               INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
               afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
               SELECT 'Location' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
               DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
               DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
               DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
               DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
               DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
               DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
               DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
               DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
                DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
               FROM locationlicenses loclic;


                 INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
               afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
               SELECT 'EmpLicense' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
               DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
               DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
               DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
               DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
               DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
               DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
               DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
               DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
                DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
               FROM employeelicenses emplic;


                 INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
               afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
               SELECT 'EmpDrivingLicense' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
               DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
               DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
               DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
               DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
               DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
               DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
               DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
               DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
                DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
               FROM employeedriving drilic;

               INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
               afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
               SELECT 'EmpImgLicense' AS TYPE, ID AS entityID, expiredDate AS expirationDate,
               DATE_SUB(expiredDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expiredDate, INTERVAL -1 DAY) afterOneDay,
               DATE_SUB(expiredDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expiredDate, INTERVAL -2 DAY) afterTwoDay,
               DATE_SUB(expiredDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expiredDate, INTERVAL -3 DAY) afterThreeDay,
               DATE_SUB(expiredDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expiredDate, INTERVAL -1 WEEK) afterOneWeek,
               DATE_SUB(expiredDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expiredDate, INTERVAL -2 WEEK) afterTwoWeek,
               DATE_SUB(expiredDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expiredDate, INTERVAL -1 MONTH) afterOneMonth,
               DATE_SUB(expiredDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expiredDate, INTERVAL -2 MONTH) afterTwoMonth,
               DATE_SUB(expiredDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expiredDate, INTERVAL -3 MONTH) afterThreeMonth,
                DATE_SUB(expiredDate, INTERVAL 0 DAY) atTheTime 
               FROM employeeimages empimglic;



               select    oneDay,twoDay,threeDay,oneWeek,
               twoWeek,oneMonth , twoMonth,threeMonth ,atTheTime 

               into varOneDay,varTwoDay,varThreeDay,varOneWeek,varTwoWeek,varOneMonth,varTwoMonth,
               varThreeMonth,varAtTheTime from licensetrackinterval;

               if varOneDay = 0 then
                       update templicense set beforeOneDay= null ,afterOneDay= NULL;
               end if;

               IF varTwoDay = 0 THEN
                       UPDATE templicense SET beforeTwoDay= NULL,afterTwoDay= NULL;
               END IF;

               IF varThreeDay = 0 THEN
                       UPDATE templicense SET beforeThreeDay= NULL, afterThreeDay= NULL;
               END IF;

               IF varOneWeek = 0 THEN
                       UPDATE templicense SET beforeOneWeek= NULL, afterOneWeek= NULL;
               END IF;

               IF varTwoWeek= 0 THEN
                       UPDATE templicense SET beforeTwoWeek= NULL,afterTwoWeek= NULL;
               END IF;

               IF varOneMonth = 0 THEN
                       UPDATE templicense SET beforeOneMonth= NULL,afterOneMonth= NULL;
               END IF;


               IF varTwoMonth = 0 THEN
                        UPDATE templicense SET beforeTwoMonth= NULL,afterTwoMonth= NULL;
               END IF;

               IF varThreeMonth = 0 THEN
                       UPDATE templicense SET beforeThreeMonth= NULL,afterThreeMonth= NULL;
               END IF;

               IF varAtTheTime = 0 THEN
                        UPDATE templicense SET atTheTime= NULL;
               END IF;

               select * from   templicense  ;


               END$$

DELIMITER ;

JERRY · Accepted Answer · 2018-08-10 04:05:13Z

You can use below reference for your requirement. I did for positive date difference only. you can write for negative values as well in CASE WHEN.

  SELECT expirationDate, 
            CASE WHEN DATEDIFF(expirationDate, NOW()) = 1 THEN 1
                 WHEN DATEDIFF(expirationDate, NOW()) = 2 THEN 2
                 WHEN DATEDIFF(expirationDate, NOW()) = 3 THEN 3
                 WHEN DATEDIFF(expirationDate, NOW()) BETWEEN 4 AND 7 THEN 7
                 WHEN DATEDIFF(expirationDate, NOW()) BETWEEN 8 AND 14 THEN 14
                 WHEN DATEDIFF(expirationDate, NOW()) BETWEEN 15 AND 30 THEN 30
                 WHEN DATEDIFF(expirationDate, NOW()) BETWEEN 31 AND 60 THEN 60
                 WHEN DATEDIFF(expirationDate, NOW()) BETWEEN 61 AND 90 THEN 90
                 END AS number_of_days
    FROM organizationlicenses 
    where expirationDate BETWEEN DATE_ADD(NOW(), INTERVAL -3 MONTH) AND DATE_ADD(NOW(), INTERVAL +3 MONTH);

Stack Exchange Network

Stored procedure for sending emails at specified times after license expiration

1 Answer 1

You must log in to answer this question.

Hot Network Questions

Stored procedure for sending emails at specified times after license expiration

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions