· 6 years ago · Nov 07, 2019, 09:52 PM
1DELIMITER $$
2CREATE DEFINER=`cstroup`@`localhost` PROCEDURE `sp_Reporting_KareNBlood_ExpectedMessagesAPTELIG`()
3BEGIN
4
5SET @orgID = 104;
6-- SET @currentDate = CONVERT_TZ(NOW(), @@session.time_zone, 'UTC'); -- NOW()
7SET @currentDate = CONVERT_TZ(NOW(), @@session.time_zone, 'UTC'); -- NOW()
8SET @productType = "Whole Blood";
9
10
11-- LIST OF MOBILE VALID PARTICIPANTS
12DROP TEMPORARY TABLE IF EXISTS tmpMobileValid;
13CREATE TEMPORARY TABLE tmpMobileValid
14(INDEX idx_participantID (participantID))
15
16SELECT
17 PPN.participantID,
18 PNC.smsCapable,
19 PPN.lastValidatedDateTime
20FROM karen.participantPhoneNumber as PPN
21JOIN karen.participant as P on PPN.participantID = P.participantID
22LEFT JOIN karen.phoneNumberCarrier as PNC on PPN.phoneNumberCarrierID = PNC.phoneNumberCarrierID
23WHERE P.organizationID = @orgID
24AND P.participantID IN (SELECT participantID FROM karen.participant WHERE organizationID = @orgID AND (optOutStatusID = 100 OR optOutStatusID IS NULL))
25AND (PPN.optOutStatusID IN (100) OR PPN.optOutStatusID IS NULL) -- PHONE NUMBER IS NOT OPTED OUT
26AND PNC.phoneNumberCarrierID != 0 -- EXCLUDE UNKNOWN
27HAVING smsCapable = 1
28;
29
30
31
32-- POST APPOINTMENT
33-- PULL ALL APTS IN THE PAST DAYS
34DROP TEMPORARY TABLE IF EXISTS tmpPostApt;
35CREATE TEMPORARY TABLE tmpPostApt
36(INDEX idx_participantID (participantID))
37
38SELECT
39 "Post-Apt Program" as programStatus,
40 A.appointmentID,
41 P.firstName,
42 A.participantID,
43 ES.eventStatus,
44 A.beginDateTime as beginDateTimeUTC,
45 CONVERT_TZ(A.beginDateTime, 'UTC', 'US/Mountain') as beginDateTimeMountain,
46 CONVERT_TZ(A.beginDateTime, 'UTC', TZ.timeZone) as beginDateTimeLocal,
47 L.locationName,
48 LT.locationType,
49 CONVERT_TZ(@currentDate, 'UTC', TZ.timeZone) as nowLocal,
50 DATE_SUB(CONVERT_TZ(@currentDate, 'UTC', TZ.timeZone), INTERVAL 172800 SECOND) as twoDayIntervalStart,
51 DATE_SUB(CONVERT_TZ(@currentDate, 'UTC', TZ.timeZone), INTERVAL 169200 SECOND) as twoDayIntervalEnd
52FROM `appointment` as A
53LEFT JOIN `participant` as P on A.participantID = P.participantID
54LEFT JOIN `eventStatus` as ES on A.eventStatusID = ES.eventStatusID
55LEFT JOIN `location` as L on A.locationID = L.locationID
56LEFT JOIN `locationType` as LT on L.locationTypeID = LT.locationTypeID
57LEFT JOIN `organization` as O on P.organizationID = O.organizationID
58LEFT JOIN `timeZone` as TZ on O.timeZoneID = TZ.timeZoneID
59WHERE P.organizationID = @orgID
60AND (P.optOutStatusID = 100 OR P.optOutStatusID IS NULL)
61AND A.eventStatusID NOT IN (SELECT eventStatusID FROM `eventStatus` WHERE eventStatus IN ('Cancelled', 'Rescheduled'))
62AND A.productTypeID IN (SELECT productTypeID FROM `productType` WHERE productType = @productType)
63AND L.locationTypeID IN (SELECT locationTypeID FROM `locationType` WHERE locationType = 'Fixed')
64AND A.beginDateTime > DATE_SUB(@currentDate, INTERVAL 5 DAY)
65AND A.beginDateTime <= @currentDate
66AND A.participantID IN (SELECT participantID FROM tmpMobileValid WHERE smsCapable = 1) -- INCLUDE ONLY MOBILE VALID PHONE NUMBERS
67;
68
69
70-- PRE APPOINTMENT
71-- PULL ALL FUTURE APTS OF WHOLE BLOOD AT FIXED LOCATIONS
72DROP TEMPORARY TABLE IF EXISTS tmpPreApt;
73CREATE TEMPORARY TABLE tmpPreApt
74(INDEX idx_participantID (participantID))
75
76SELECT
77 "Pre-Apt Program" as programStatus,
78 A.appointmentID,
79 P.firstName,
80 A.participantID,
81 ES.eventStatus,
82 A.beginDateTime as beginDateTimeUTC,
83 CONVERT_TZ(A.beginDateTime, 'UTC', 'US/Mountain') as beginDateTimeMountain,
84 CONVERT_TZ(A.beginDateTime, 'UTC', TZ.timeZone) as beginDateTimeLocal,
85 L.locationName,
86 LT.locationType,
87 CONVERT_TZ(@currentDate, 'UTC', TZ.timeZone) as nowLocal,
88 DATE_ADD(CONVERT_TZ(@currentDate, 'UTC', TZ.timeZone), INTERVAL 86400 SECOND) as oneDayIntervalStart,
89 DATE_ADD(CONVERT_TZ(@currentDate, 'UTC', TZ.timeZone), INTERVAL 90000 SECOND) as oneDayIntervalEnd,
90 DATE_ADD(CONVERT_TZ(@currentDate, 'UTC', TZ.timeZone), INTERVAL 172800 SECOND) as twoDayIntervalStart,
91 DATE_ADD(CONVERT_TZ(@currentDate, 'UTC', TZ.timeZone), INTERVAL 176400 SECOND) as twoDayIntervalEnd
92FROM `appointment` as A
93LEFT JOIN `participant` as P on A.participantID = P.participantID
94LEFT JOIN `eventStatus` as ES on A.eventStatusID = ES.eventStatusID
95LEFT JOIN `location` as L on A.locationID = L.locationID
96LEFT JOIN `locationType` as LT on L.locationTypeID = LT.locationTypeID
97LEFT JOIN `organization` as O on P.organizationID = O.organizationID
98LEFT JOIN `timeZone` as TZ on O.timeZoneID = TZ.timeZoneID
99WHERE P.organizationID = @orgID
100AND (P.optOutStatusID = 100 OR P.optOutStatusID IS NULL)
101AND A.eventStatusID NOT IN (SELECT eventStatusID FROM `eventStatus` WHERE eventStatus IN ('Cancelled', 'Rescheduled'))
102AND A.productTypeID IN (SELECT productTypeID FROM `productType` WHERE productType = @productType)
103AND L.locationTypeID IN (SELECT locationTypeID FROM `locationType` WHERE locationType = 'Fixed')
104AND A.beginDateTime >= @currentDate
105AND A.participantID IN (SELECT participantID FROM tmpMobileValid WHERE smsCapable = 1) -- INCLUDE ONLY MOBILE VALID PHONE NUMBERS
106AND A.participantID NOT IN (SELECT participantID FROM tmpPostApt) -- NOT IN POST APT MESSAGING
107;
108
109
110
111-- RECRUITMENT PROGRAM
112DROP TEMPORARY TABLE IF EXISTS tmpRecruitment;
113CREATE TEMPORARY TABLE tmpRecruitment
114(INDEX idx_participantID (participantID))
115
116SELECT
117 "Enrolled in Recruitment" as programStatus,
118 E.eligibilityID,
119 P.firstName,
120 P.participantID,
121 ES.eventStatus,
122 E.beginDateTime,
123 CONVERT_TZ(E.beginDateTime, 'UTC', "US/Mountain") as beginDateTimeMountain,
124 CONVERT_TZ(E.beginDateTime, 'UTC', TZ.timeZone) as beginDateTimeLocal,
125 NULL as locationName,
126 NULL as locationType,
127 CONVERT_TZ(@currentDate, 'UTC', TZ.timeZone) as nowLocal,
128 DATE_ADD(CONVERT_TZ(@currentDate, 'UTC', TZ.timeZone), INTERVAL 259200 SECOND) as threeDayPreStart,
129 DATE_ADD(CONVERT_TZ(@currentDate, 'UTC', TZ.timeZone), INTERVAL 262800 SECOND) as threeDayPreEnd,
130 DATE_SUB(CONVERT_TZ(@currentDate, 'UTC', TZ.timeZone), INTERVAL 1296000 SECOND) as fifteenDayPostStart,
131 DATE_SUB(CONVERT_TZ(@currentDate, 'UTC', TZ.timeZone), INTERVAL 1299600 SECOND) as fifteenDayPostEnd,
132 ((TIMESTAMPDIFF(SECOND, E.beginDateTime, @currentDate) / 86400)) as timeDiffSeconds,
133 MOD(CEILING((TIMESTAMPDIFF(SECOND, E.beginDateTime, @currentDate) / 86400)), 30) as modCeiling
134FROM `eligibility` as E
135LEFT JOIN `participant` as P on E.participantID = P.participantID
136LEFT JOIN `productType` as PT on E.productTypeID = PT.productTypeID
137LEFT JOIN `eventStatus` as ES on E.eventStatusID = ES.eventStatusID
138LEFT JOIN `organization` as O on P.organizationID = O.organizationID
139LEFT JOIN `timeZone` as TZ on O.timeZoneID = TZ.timeZoneID
140WHERE E.productTypeID = 116
141AND E.participantID IN (SELECT participantID FROM karen.participant WHERE organizationID = @orgID AND (optOutStatusID = 100 OR optOutStatusID IS NULL))
142AND E.eventStatusID = 102 /* ACTIVE ELIG */
143AND E.participantID IN (SELECT participantID FROM tmpMobileValid WHERE smsCapable = 1) -- EXCLUDE DONORS WITH A FUTURE APT
144AND E.participantID NOT IN (SELECT participantID FROM tmpPostApt) -- NOT IN POST APT
145AND E.participantID NOT IN (SELECT participantID FROM tmpPreApt) -- NOT IN PRE APT
146AND
147 (CASE
148 WHEN P.organizationID = 104
149 THEN P.tapestryGroupFlagID IN (SELECT tapestryGroupFlagID FROM tapestryGroupFlag WHERE tapestryGroupFlag = "C")
150 WHEN P.organizationID = 101
151 THEN P.tapestryGroupFlagID IN (SELECT tapestryGroupFlagID FROM tapestryGroupFlag WHERE tapestryGroupFlag = "RECRUIT")
152 ELSE (SELECT tapestryGroupFlagID FROM tapestryGroupFlag WHERE tapestryGroupFlag = "Unknown") OR tapestryGroupFlagID IS NULL
153 END
154 )
155;
156
157
158SELECT
159 programStatus,
160 appointmentID,
161 firstName,
162 participantID,
163 eventStatus,
164 beginDateTimeUTC,
165 beginDateTimeMountain,
166 beginDateTimeLocal,
167 locationName,
168 locationType,
169 nowLocal,
170 CASE
171 WHEN beginDateTimeLocal > twoDayIntervalStart AND beginDateTimeLocal <= twoDayIntervalEnd
172 THEN CONCAT(eventStatus, " POST APT (+2 DAY)")
173 ELSE NULL
174 END as messageExpected,
175 DATE_FORMAT(beginDateTimeLocal, '%h:%i %p') as timeSendLocal
176FROM tmpPostApt
177HAVING messageExpected IS NOT NULL
178
179UNION
180
181SELECT
182 programStatus,
183 appointmentID,
184 firstName,
185 participantID,
186 eventStatus,
187 beginDateTimeUTC,
188 beginDateTimeMountain,
189 beginDateTimeLocal,
190 locationName,
191 locationType,
192 nowLocal,
193 CASE
194 WHEN beginDateTimeLocal > twoDayIntervalStart AND beginDateTimeLocal <= twoDayIntervalEnd
195 THEN "PRE APT (-2 DAYS)"
196 WHEN beginDateTimeLocal > oneDayIntervalStart AND beginDateTimeLocal <= oneDayIntervalEnd
197 THEN "PRE APT (-1 DAY)"
198 ELSE NULL
199 END as messageExpected,
200 DATE_FORMAT(beginDateTimeLocal, '%h:%i %p') as timeSendLocal
201FROM tmpPreApt
202HAVING messageExpected IS NOT NULL
203
204UNION
205
206SELECT
207 programStatus,
208 eligibilityID,
209 firstName,
210 participantID,
211 eventStatus,
212 beginDateTime,
213 beginDateTimeMountain,
214 beginDateTimeLocal,
215 NULL as locationName,
216 NULL as locationType,
217 nowLocal,
218 CASE
219 WHEN beginDateTimeLocal > threeDayPreStart AND beginDateTimeLocal <= threeDayPreEnd
220 THEN "RECRUIT (-3 DAY)"
221 WHEN beginDateTimeLocal > fifteenDayPostStart AND beginDateTimeLocal <= fifteenDayPostEnd
222 THEN "RECRUIT (+15 Day)"
223 WHEN timeDiffSeconds > 0 AND modCeiling = 0 AND DATE_FORMAT(beginDateTimeLocal, '%T') > DATE_FORMAT(nowLocal, '%T') AND DATE_FORMAT(beginDateTimeLocal, '%T') <= DATE_FORMAT(DATE_ADD(nowLocal, INTERVAL 1 HOUR), '%T')
224 THEN "RECRUIT (Every 30+ Day)"
225 ELSE NULL
226 END as messageExpected,
227 DATE_FORMAT(beginDateTimeLocal, '%h:%i %p') as timeSendLocal
228FROM tmpRecruitment
229WHERE beginDateTime > "0000-00-00 00:00:00"
230HAVING messageExpected IS NOT NULL
231;
232
233END$$
234DELIMITER ;