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