· 8 years ago · Jul 18, 2018, 04:12 AM
1DELIMITER $$
2
3DROP PROCEDURE IF EXISTS `licensetrack`$$
4
5CREATE PROCEDURE `licensetrack`()
6
7BEGIN
8
9 DECLARE varOneDay INT;
10 DECLARE varTwoDay INT;
11 DECLARE varThreeDay INT;
12 DECLARE varOneWeek INT;
13 DECLARE varTwoWeek INT;
14 DECLARE varOneMonth INT;
15 DECLARE varTwoMonth INT;
16 DECLARE varThreeMonth INT;
17 DECLARE varAtTheTime INT;
18
19 DROP TEMPORARY TABLE IF EXISTS templicense;
20
21 CREATE TEMPORARY TABLE IF NOT EXISTS templicense(
22 TYPE VARCHAR(50) NOT NULL,
23 entityID BIGINT NOT NULL,
24 expirationDate DATE,
25 beforeOneDay DATE,
26 afterOneDay DATE,
27 beforeTwoDay DATE,
28 afterTwoDay DATE,
29 beforeThreeDay DATE,
30 afterThreeDay DATE,
31 beforeOneWeek DATE,
32 afterOneWeek DATE,
33 beforeTwoWeek DATE,
34 afterTwoWeek DATE,
35 beforeOneMonth DATE,
36 afterOneMonth DATE,
37 beforeTwoMonth DATE,
38 afterTwoMonth DATE,
39 beforeThreeMonth DATE,
40 afterThreeMonth DATE,
41 atTheTime DATE
42
43 ) ;
44
45
46 INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
47 afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
48 SELECT 'Organ' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
49 DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
50 DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
51 DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
52 DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
53 DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
54 DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
55 DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
56 DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
57 DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime
58 FROM organizationlicenses orglic;
59
60
61 INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
62 afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
63 SELECT 'Location' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
64 DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
65 DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
66 DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
67 DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
68 DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
69 DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
70 DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
71 DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
72 DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime
73 FROM locationlicenses loclic;
74
75
76 INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
77 afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
78 SELECT 'EmpLicense' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
79 DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
80 DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
81 DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
82 DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
83 DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
84 DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
85 DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
86 DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
87 DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime
88 FROM employeelicenses emplic;
89
90
91 INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
92 afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
93 SELECT 'EmpDrivingLicense' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
94 DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
95 DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
96 DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
97 DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
98 DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
99 DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
100 DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
101 DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
102 DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime
103 FROM employeedriving drilic;
104
105 INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
106 afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
107 SELECT 'EmpImgLicense' AS TYPE, ID AS entityID, expiredDate AS expirationDate,
108 DATE_SUB(expiredDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expiredDate, INTERVAL -1 DAY) afterOneDay,
109 DATE_SUB(expiredDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expiredDate, INTERVAL -2 DAY) afterTwoDay,
110 DATE_SUB(expiredDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expiredDate, INTERVAL -3 DAY) afterThreeDay,
111 DATE_SUB(expiredDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expiredDate, INTERVAL -1 WEEK) afterOneWeek,
112 DATE_SUB(expiredDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expiredDate, INTERVAL -2 WEEK) afterTwoWeek,
113 DATE_SUB(expiredDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expiredDate, INTERVAL -1 MONTH) afterOneMonth,
114 DATE_SUB(expiredDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expiredDate, INTERVAL -2 MONTH) afterTwoMonth,
115 DATE_SUB(expiredDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expiredDate, INTERVAL -3 MONTH) afterThreeMonth,
116 DATE_SUB(expiredDate, INTERVAL 0 DAY) atTheTime
117 FROM employeeimages empimglic;
118
119
120
121 select oneDay,twoDay,threeDay,oneWeek,
122 twoWeek,oneMonth , twoMonth,threeMonth ,atTheTime
123
124 into varOneDay,varTwoDay,varThreeDay,varOneWeek,varTwoWeek,varOneMonth,varTwoMonth,
125 varThreeMonth,varAtTheTime from licensetrackinterval;
126
127 if varOneDay = 0 then
128 update templicense set beforeOneDay= null ,afterOneDay= NULL;
129 end if;
130
131 IF varTwoDay = 0 THEN
132 UPDATE templicense SET beforeTwoDay= NULL,afterTwoDay= NULL;
133 END IF;
134
135 IF varThreeDay = 0 THEN
136 UPDATE templicense SET beforeThreeDay= NULL, afterThreeDay= NULL;
137 END IF;
138
139 IF varOneWeek = 0 THEN
140 UPDATE templicense SET beforeOneWeek= NULL, afterOneWeek= NULL;
141 END IF;
142
143 IF varTwoWeek= 0 THEN
144 UPDATE templicense SET beforeTwoWeek= NULL,afterTwoWeek= NULL;
145 END IF;
146
147 IF varOneMonth = 0 THEN
148 UPDATE templicense SET beforeOneMonth= NULL,afterOneMonth= NULL;
149 END IF;
150
151
152 IF varTwoMonth = 0 THEN
153 UPDATE templicense SET beforeTwoMonth= NULL,afterTwoMonth= NULL;
154 END IF;
155
156 IF varThreeMonth = 0 THEN
157 UPDATE templicense SET beforeThreeMonth= NULL,afterThreeMonth= NULL;
158 END IF;
159
160 IF varAtTheTime = 0 THEN
161 UPDATE templicense SET atTheTime= NULL;
162 END IF;
163
164 select * from templicense ;
165
166
167 END$$
168
169DELIMITER ;