3
\$\begingroup\$

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 ;
\$\endgroup\$
1
\$\begingroup\$

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);
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.