Commit | Line | Data |
---|---|---|
c09bacfd | 1 | <?php |
546a1ecc TO |
2 | /* |
3 | +--------------------------------------------------------------------+ | |
41498ac5 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
546a1ecc | 5 | | | |
41498ac5 TO |
6 | | This work is published under the GNU AGPLv3 license with some | |
7 | | permitted exceptions and without any warranty. For full license | | |
8 | | and copyright information, see https://civicrm.org/licensing | | |
546a1ecc TO |
9 | +--------------------------------------------------------------------+ |
10 | */ | |
11 | ||
c09bacfd TO |
12 | namespace Civi\ActionSchedule; |
13 | ||
546a1ecc TO |
14 | /** |
15 | * Class RecipientBuilder | |
16 | * @package Civi\ActionSchedule | |
17 | * | |
18 | * The RecipientBuilder prepares a list of recipients based on an action-schedule. | |
19 | * | |
20 | * This is a four-step process, with different steps depending on: | |
21 | * | |
22 | * (a) How the recipient is identified. Sometimes recipients are identified based | |
23 | * on their relations (e.g. selecting the assignees of an activity or the | |
24 | * participants of an event), and sometimes they are manually added using | |
25 | * a flat contact list (e.g. with a contact ID or group ID). | |
26 | * (b) Whether this is the first reminder or a follow-up/repeated reminder. | |
27 | * | |
28 | * The permutations of these (a)+(b) produce four phases -- RELATION_FIRST, | |
29 | * RELATION_REPEAT, ADDITION_FIRST, ADDITION_REPEAT. | |
30 | * | |
31 | * Each phase requires running a complex query. As a general rule, | |
32 | * MappingInterface::createQuery() produces a base query, and the RecipientBuilder | |
33 | * appends extra bits (JOINs/WHEREs/GROUP BYs) depending on which step is running. | |
34 | * | |
35 | * For example, suppose we want to send reminders to anyone who registers for | |
36 | * a "Conference" or "Exhibition" event with the 'pay later' option, and we want | |
37 | * to fire the reminders X days after the registration date. The | |
38 | * MappingInterface::createQuery() could return a query like: | |
39 | * | |
0b882a86 | 40 | * ``` |
546a1ecc TO |
41 | * CRM_Utils_SQL_Select::from('civicrm_participant e') |
42 | * ->join('event', 'INNER JOIN civicrm_event event ON e.event_id = event.id') | |
43 | * ->where('e.is_pay_later = 1') | |
44 | * ->where('event.event_type_id IN (#myEventTypes)') | |
45 | * ->param('myEventTypes', array(2, 5)) | |
46 | * ->param('casDateField', 'e.register_date') | |
efc40454 | 47 | * ->param($defaultParams) |
546a1ecc | 48 | * ...etc... |
0b882a86 | 49 | * ``` |
546a1ecc TO |
50 | * |
51 | * In the RELATION_FIRST phase, RecipientBuilder adds a LEFT-JOIN+WHERE to find | |
52 | * participants who have *not* yet received any reminder, and filters those | |
53 | * participants based on whether X days have passed since "e.register_date". | |
54 | * | |
55 | * Notice that the query may define several SQL elements directly (eg | |
56 | * via `from()`, `where()`, `join()`, `groupBy()`). Additionally, it | |
57 | * must define some parameters (eg `casDateField`). These parameters will be | |
58 | * read by RecipientBuilder and used in other parts of the query. | |
59 | * | |
60 | * At time of writing, these parameters are required: | |
61 | * - casAddlCheckFrom: string, SQL FROM expression | |
62 | * - casContactIdField: string, SQL column expression | |
63 | * - casDateField: string, SQL column expression | |
64 | * - casEntityIdField: string, SQL column expression | |
65 | * | |
66 | * Some parameters are optional: | |
67 | * - casContactTableAlias: string, SQL table alias | |
68 | * - casAnniversaryMode: bool | |
546a1ecc TO |
69 | * |
70 | * Additionally, some parameters are automatically predefined: | |
71 | * - casNow | |
72 | * - casMappingEntity: string, SQL table name | |
73 | * - casMappingId: int | |
74 | * - casActionScheduleId: int | |
75 | * | |
76 | * Note: Any parameters defined by the core Civi\ActionSchedule subsystem | |
77 | * use the prefix `cas`. If you define new parameters (like `myEventTypes` | |
78 | * above), then use a different name (to avoid conflicts). | |
79 | */ | |
c09bacfd TO |
80 | class RecipientBuilder { |
81 | ||
82 | private $now; | |
546a1ecc TO |
83 | |
84 | /** | |
85 | * Generate action_log's for new, first-time alerts to related contacts. | |
86 | * | |
87 | * @see buildRelFirstPass | |
88 | */ | |
89 | const PHASE_RELATION_FIRST = 'rel-first'; | |
90 | ||
91 | /** | |
92 | * Generate action_log's for new, first-time alerts to additional contacts. | |
93 | * | |
94 | * @see buildAddlFirstPass | |
95 | */ | |
96 | const PHASE_ADDITION_FIRST = 'addl-first'; | |
97 | ||
98 | /** | |
99 | * Generate action_log's for repeated, follow-up alerts to related contacts. | |
100 | * | |
101 | * @see buildRelRepeatPass | |
102 | */ | |
103 | const PHASE_RELATION_REPEAT = 'rel-repeat'; | |
104 | ||
105 | /** | |
106 | * Generate action_log's for repeated, follow-up alerts to additional contacts. | |
107 | * | |
108 | * @see buildAddlRepeatPass | |
109 | */ | |
110 | const PHASE_ADDITION_REPEAT = 'addl-repeat'; | |
c09bacfd TO |
111 | |
112 | /** | |
113 | * @var \CRM_Core_DAO_ActionSchedule | |
114 | */ | |
115 | private $actionSchedule; | |
116 | ||
117 | /** | |
546a1ecc | 118 | * @var MappingInterface |
c09bacfd TO |
119 | */ |
120 | private $mapping; | |
121 | ||
546a1ecc TO |
122 | /** |
123 | * @param $now | |
124 | * @param \CRM_Core_DAO_ActionSchedule $actionSchedule | |
125 | * @param MappingInterface $mapping | |
126 | */ | |
c09bacfd TO |
127 | public function __construct($now, $actionSchedule, $mapping) { |
128 | $this->now = $now; | |
129 | $this->actionSchedule = $actionSchedule; | |
130 | $this->mapping = $mapping; | |
131 | } | |
132 | ||
133 | /** | |
134 | * Fill the civicrm_action_log with any new/missing TODOs. | |
135 | * | |
136 | * @throws \CRM_Core_Exception | |
137 | */ | |
138 | public function build() { | |
c09bacfd TO |
139 | $this->buildRelFirstPass(); |
140 | ||
3741f351 | 141 | if ($this->prepareAddlFilter('c.id') && $this->mapping->sendToAdditional($this->actionSchedule->entity_value)) { |
c09bacfd TO |
142 | $this->buildAddlFirstPass(); |
143 | } | |
144 | ||
c09bacfd TO |
145 | if ($this->actionSchedule->is_repeat) { |
146 | $this->buildRelRepeatPass(); | |
147 | } | |
148 | ||
3741f351 | 149 | if ($this->actionSchedule->is_repeat && $this->prepareAddlFilter('c.id') && $this->mapping->sendToAdditional($this->actionSchedule->entity_value)) { |
c09bacfd TO |
150 | $this->buildAddlRepeatPass(); |
151 | } | |
152 | } | |
153 | ||
154 | /** | |
e08fae02 PH |
155 | * Generate action_log's for new, first-time alerts to related contacts, |
156 | * and contacts who are again eligible to receive the alert e.g. membership | |
157 | * renewal reminders. | |
546a1ecc | 158 | * |
c09bacfd TO |
159 | * @throws \Exception |
160 | */ | |
161 | protected function buildRelFirstPass() { | |
546a1ecc | 162 | $query = $this->prepareQuery(self::PHASE_RELATION_FIRST); |
c09bacfd | 163 | |
efc40454 | 164 | $startDateClauses = $this->prepareStartDateClauses(); |
e08fae02 PH |
165 | // Send reminder to all contacts who have never received this scheduled reminder |
166 | $firstInstanceQuery = $query->copy() | |
167 | ->merge($this->selectIntoActionLog(self::PHASE_RELATION_FIRST, $query)) | |
168 | ->merge($this->joinReminder('LEFT JOIN', 'rel', $query)) | |
169 | ->where("reminder.id IS NULL") | |
170 | ->where($startDateClauses) | |
171 | ->strict() | |
172 | ->toSQL(); | |
173 | \CRM_Core_DAO::executeQuery($firstInstanceQuery); | |
c09bacfd TO |
174 | } |
175 | ||
176 | /** | |
546a1ecc TO |
177 | * Generate action_log's for new, first-time alerts to additional contacts. |
178 | * | |
c09bacfd TO |
179 | * @throws \Exception |
180 | */ | |
181 | protected function buildAddlFirstPass() { | |
546a1ecc | 182 | $query = $this->prepareQuery(self::PHASE_ADDITION_FIRST); |
c09bacfd TO |
183 | |
184 | $insertAdditionalSql = \CRM_Utils_SQL_Select::from("civicrm_contact c") | |
c64f69d9 | 185 | ->merge($query, ['params']) |
546a1ecc | 186 | ->merge($this->selectIntoActionLog(self::PHASE_ADDITION_FIRST, $query)) |
c09bacfd | 187 | ->merge($this->joinReminder('LEFT JOIN', 'addl', $query)) |
a56f6b25 | 188 | ->where('reminder.id IS NULL') |
c09bacfd TO |
189 | ->where("c.is_deleted = 0 AND c.is_deceased = 0") |
190 | ->merge($this->prepareAddlFilter('c.id')) | |
191 | ->where("c.id NOT IN ( | |
192 | SELECT rem.contact_id | |
9e1bf145 | 193 | FROM civicrm_action_log rem INNER JOIN {$this->mapping->getEntity()} e ON rem.entity_id = e.id |
c09bacfd | 194 | WHERE rem.action_schedule_id = {$this->actionSchedule->id} |
9e1bf145 | 195 | AND rem.entity_table = '{$this->mapping->getEntity()}' |
c09bacfd TO |
196 | )") |
197 | // Where does e.id come from here? ^^^ | |
198 | ->groupBy("c.id") | |
199 | ->strict() | |
200 | ->toSQL(); | |
201 | \CRM_Core_DAO::executeQuery($insertAdditionalSql); | |
202 | } | |
203 | ||
204 | /** | |
546a1ecc TO |
205 | * Generate action_log's for repeated, follow-up alerts to related contacts. |
206 | * | |
c09bacfd TO |
207 | * @throws \CRM_Core_Exception |
208 | * @throws \Exception | |
209 | */ | |
210 | protected function buildRelRepeatPass() { | |
546a1ecc | 211 | $query = $this->prepareQuery(self::PHASE_RELATION_REPEAT); |
efc40454 | 212 | $startDateClauses = $this->prepareStartDateClauses(); |
c09bacfd TO |
213 | |
214 | // CRM-15376 - do not send our reminders if original criteria no longer applies | |
215 | // the first part of the startDateClause array is the earliest the reminder can be sent. If the | |
216 | // event (e.g membership_end_date) has changed then the reminder may no longer apply | |
217 | // @todo - this only handles events that get moved later. Potentially they might get moved earlier | |
218 | $repeatInsert = $query | |
219 | ->merge($this->joinReminder('INNER JOIN', 'rel', $query)) | |
e08fae02 | 220 | ->merge($this->selectIntoActionLog(self::PHASE_RELATION_REPEAT, $query)) |
c09bacfd | 221 | ->merge($this->prepareRepetitionEndFilter($query['casDateField'])) |
c64f69d9 | 222 | ->where($this->actionSchedule->start_action_date ? $startDateClauses[0] : []) |
c09bacfd | 223 | ->groupBy("reminder.contact_id, reminder.entity_id, reminder.entity_table") |
e08fae02 | 224 | ->having("TIMESTAMPDIFF(HOUR, MAX(reminder.action_date_time), CAST(!casNow AS datetime)) >= TIMESTAMPDIFF(HOUR, MAX(reminder.action_date_time), DATE_ADD(MAX(reminder.action_date_time), INTERVAL !casRepetitionInterval))") |
c64f69d9 | 225 | ->param([ |
281905e6 | 226 | 'casRepetitionInterval' => $this->parseRepetitionInterval(), |
c64f69d9 | 227 | ]) |
c09bacfd TO |
228 | ->strict() |
229 | ->toSQL(); | |
230 | ||
e08fae02 | 231 | \CRM_Core_DAO::executeQuery($repeatInsert); |
c09bacfd | 232 | } |
546a1ecc | 233 | |
c09bacfd | 234 | /** |
546a1ecc TO |
235 | * Generate action_log's for repeated, follow-up alerts to additional contacts. |
236 | * | |
c09bacfd TO |
237 | * @throws \CRM_Core_Exception |
238 | * @throws \Exception | |
239 | */ | |
240 | protected function buildAddlRepeatPass() { | |
546a1ecc | 241 | $query = $this->prepareQuery(self::PHASE_ADDITION_REPEAT); |
c09bacfd TO |
242 | |
243 | $addlCheck = \CRM_Utils_SQL_Select::from($query['casAddlCheckFrom']) | |
244 | ->select('*') | |
c64f69d9 | 245 | ->merge($query, ['params', 'wheres', 'joins']) |
c09bacfd TO |
246 | ->merge($this->prepareRepetitionEndFilter($query['casDateField'])) |
247 | ->limit(1) | |
248 | ->strict() | |
249 | ->toSQL(); | |
250 | ||
251 | $daoCheck = \CRM_Core_DAO::executeQuery($addlCheck); | |
252 | if ($daoCheck->fetch()) { | |
253 | $repeatInsertAddl = \CRM_Utils_SQL_Select::from('civicrm_contact c') | |
e08fae02 | 254 | ->merge($this->selectIntoActionLog(self::PHASE_ADDITION_REPEAT, $query)) |
c09bacfd | 255 | ->merge($this->joinReminder('INNER JOIN', 'addl', $query)) |
c64f69d9 | 256 | ->merge($this->prepareAddlFilter('c.id'), ['params']) |
c09bacfd TO |
257 | ->where("c.is_deleted = 0 AND c.is_deceased = 0") |
258 | ->groupBy("reminder.contact_id") | |
e08fae02 | 259 | ->having("TIMESTAMPDIFF(HOUR, MAX(reminder.action_date_time), CAST(!casNow AS datetime)) >= TIMESTAMPDIFF(HOUR, MAX(reminder.action_date_time), DATE_ADD(MAX(reminder.action_date_time), INTERVAL !casRepetitionInterval))") |
c64f69d9 | 260 | ->param([ |
281905e6 | 261 | 'casRepetitionInterval' => $this->parseRepetitionInterval(), |
c64f69d9 | 262 | ]) |
c09bacfd TO |
263 | ->strict() |
264 | ->toSQL(); | |
265 | ||
e08fae02 | 266 | \CRM_Core_DAO::executeQuery($repeatInsertAddl); |
c09bacfd TO |
267 | } |
268 | } | |
269 | ||
270 | /** | |
271 | * @param string $phase | |
272 | * @return \CRM_Utils_SQL_Select | |
273 | * @throws \CRM_Core_Exception | |
274 | */ | |
275 | protected function prepareQuery($phase) { | |
c64f69d9 | 276 | $defaultParams = [ |
c09bacfd | 277 | 'casActionScheduleId' => $this->actionSchedule->id, |
9e1bf145 TO |
278 | 'casMappingId' => $this->mapping->getId(), |
279 | 'casMappingEntity' => $this->mapping->getEntity(), | |
546a1ecc | 280 | 'casNow' => $this->now, |
c64f69d9 | 281 | ]; |
efc40454 TO |
282 | |
283 | /** @var \CRM_Utils_SQL_Select $query */ | |
284 | $query = $this->mapping->createQuery($this->actionSchedule, $phase, $defaultParams); | |
c09bacfd TO |
285 | |
286 | if ($this->actionSchedule->limit_to /*1*/) { | |
287 | $query->merge($this->prepareContactFilter($query['casContactIdField'])); | |
288 | } | |
289 | ||
290 | if (empty($query['casContactTableAlias'])) { | |
291 | $query['casContactTableAlias'] = 'c'; | |
292 | $query->join('c', "INNER JOIN civicrm_contact c ON c.id = !casContactIdField AND c.is_deleted = 0 AND c.is_deceased = 0 "); | |
293 | } | |
294 | $multilingual = \CRM_Core_I18n::isMultilingual(); | |
295 | if ($multilingual && !empty($this->actionSchedule->filter_contact_language)) { | |
296 | $query->where($this->prepareLanguageFilter($query['casContactTableAlias'])); | |
297 | } | |
298 | ||
299 | return $query; | |
300 | } | |
301 | ||
302 | /** | |
54957108 | 303 | * Parse repetition interval. |
304 | * | |
c09bacfd TO |
305 | * @return int|string |
306 | */ | |
281905e6 | 307 | protected function parseRepetitionInterval() { |
c09bacfd TO |
308 | $actionSchedule = $this->actionSchedule; |
309 | if ($actionSchedule->repetition_frequency_unit == 'day') { | |
281905e6 | 310 | $interval = "{$actionSchedule->repetition_frequency_interval} DAY"; |
c09bacfd TO |
311 | } |
312 | elseif ($actionSchedule->repetition_frequency_unit == 'week') { | |
281905e6 | 313 | $interval = "{$actionSchedule->repetition_frequency_interval} WEEK"; |
c09bacfd TO |
314 | } |
315 | elseif ($actionSchedule->repetition_frequency_unit == 'month') { | |
281905e6 | 316 | $interval = "{$actionSchedule->repetition_frequency_interval} MONTH"; |
c09bacfd TO |
317 | } |
318 | elseif ($actionSchedule->repetition_frequency_unit == 'year') { | |
281905e6 | 319 | $interval = "{$actionSchedule->repetition_frequency_interval} YEAR"; |
c09bacfd TO |
320 | } |
321 | else { | |
281905e6 | 322 | $interval = "{$actionSchedule->repetition_frequency_interval} HOUR"; |
c09bacfd | 323 | } |
281905e6 | 324 | return $interval; |
c09bacfd TO |
325 | } |
326 | ||
327 | /** | |
328 | * Prepare filter options for limiting by contact ID or group ID. | |
329 | * | |
330 | * @param string $contactIdField | |
331 | * @return \CRM_Utils_SQL_Select | |
332 | */ | |
333 | protected function prepareContactFilter($contactIdField) { | |
334 | $actionSchedule = $this->actionSchedule; | |
335 | ||
336 | if ($actionSchedule->group_id) { | |
c64f69d9 | 337 | $regularGroupIDs = $smartGroupIDs = $groupWhereCLause = []; |
cc949606 | 338 | $query = \CRM_Utils_SQL_Select::fragment(); |
339 | ||
340 | // get child group IDs if any | |
341 | $childGroupIDs = \CRM_Contact_BAO_Group::getChildGroupIds($actionSchedule->group_id); | |
c64f69d9 | 342 | foreach (array_merge([$actionSchedule->group_id], $childGroupIDs) as $groupID) { |
cc949606 | 343 | if ($this->isSmartGroup($groupID)) { |
344 | // Check that the group is in place in the cache and up to date | |
345 | \CRM_Contact_BAO_GroupContactCache::check($groupID); | |
346 | $smartGroupIDs[] = $groupID; | |
347 | } | |
348 | else { | |
349 | $regularGroupIDs[] = $groupID; | |
350 | } | |
c09bacfd | 351 | } |
cc949606 | 352 | |
353 | if (!empty($smartGroupIDs)) { | |
354 | $query->join('sg', "LEFT JOIN civicrm_group_contact_cache sg ON {$contactIdField} = sg.contact_id"); | |
355 | $groupWhereCLause[] = " sg.group_id IN ( " . implode(', ', $smartGroupIDs) . " ) "; | |
356 | } | |
357 | if (!empty($regularGroupIDs)) { | |
358 | $query->join('rg', " LEFT JOIN civicrm_group_contact rg ON {$contactIdField} = rg.contact_id AND rg.status = 'Added'"); | |
359 | $groupWhereCLause[] = " rg.group_id IN ( " . implode(', ', $regularGroupIDs) . " ) "; | |
c09bacfd | 360 | } |
cc949606 | 361 | return $query->where(implode(" OR ", $groupWhereCLause)); |
c09bacfd TO |
362 | } |
363 | elseif (!empty($actionSchedule->recipient_manual)) { | |
364 | $rList = \CRM_Utils_Type::escape($actionSchedule->recipient_manual, 'String'); | |
365 | return \CRM_Utils_SQL_Select::fragment() | |
366 | ->where("{$contactIdField} IN ({$rList})"); | |
367 | } | |
368 | return NULL; | |
369 | } | |
370 | ||
371 | /** | |
54957108 | 372 | * Prepare language filter. |
373 | * | |
374 | * @param string $contactTableAlias | |
c09bacfd TO |
375 | * @return string |
376 | */ | |
377 | protected function prepareLanguageFilter($contactTableAlias) { | |
378 | $actionSchedule = $this->actionSchedule; | |
379 | ||
380 | // get language filter for the schedule | |
381 | $filter_contact_language = explode(\CRM_Core_DAO::VALUE_SEPARATOR, $actionSchedule->filter_contact_language); | |
382 | $w = ''; | |
383 | if (($key = array_search(\CRM_Core_I18n::NONE, $filter_contact_language)) !== FALSE) { | |
384 | $w .= "{$contactTableAlias}.preferred_language IS NULL OR {$contactTableAlias}.preferred_language = '' OR "; | |
385 | unset($filter_contact_language[$key]); | |
386 | } | |
387 | if (count($filter_contact_language) > 0) { | |
388 | $w .= "{$contactTableAlias}.preferred_language IN ('" . implode("','", $filter_contact_language) . "')"; | |
389 | } | |
390 | $w = "($w)"; | |
391 | return $w; | |
392 | } | |
393 | ||
394 | /** | |
c09bacfd TO |
395 | * @return array |
396 | */ | |
efc40454 | 397 | protected function prepareStartDateClauses() { |
c09bacfd | 398 | $actionSchedule = $this->actionSchedule; |
c64f69d9 | 399 | $startDateClauses = []; |
c09bacfd TO |
400 | if ($actionSchedule->start_action_date) { |
401 | $op = ($actionSchedule->start_action_condition == 'before' ? '<=' : '>='); | |
402 | $operator = ($actionSchedule->start_action_condition == 'before' ? 'DATE_SUB' : 'DATE_ADD'); | |
efc40454 TO |
403 | $date = $operator . "(!casDateField, INTERVAL {$actionSchedule->start_action_offset} {$actionSchedule->start_action_unit})"; |
404 | $startDateClauses[] = "'!casNow' >= {$date}"; | |
c09bacfd | 405 | // This is weird. Waddupwidat? |
efc40454 TO |
406 | if ($this->mapping->getEntity() == 'civicrm_participant') { |
407 | $startDateClauses[] = $operator . "(!casNow, INTERVAL 1 DAY ) {$op} " . '!casDateField'; | |
c09bacfd TO |
408 | } |
409 | else { | |
efc40454 | 410 | $startDateClauses[] = "DATE_SUB(!casNow, INTERVAL 1 DAY ) <= {$date}"; |
c09bacfd | 411 | } |
71a9c04c | 412 | if (!empty($actionSchedule->effective_start_date) && $actionSchedule->effective_start_date !== '0000-00-00 00:00:00') { |
0b1910ac MD |
413 | $startDateClauses[] = "'{$actionSchedule->effective_start_date}' <= {$date}"; |
414 | } | |
71a9c04c | 415 | if (!empty($actionSchedule->effective_end_date) && $actionSchedule->effective_end_date !== '0000-00-00 00:00:00') { |
0b1910ac MD |
416 | $startDateClauses[] = "'{$actionSchedule->effective_end_date}' > {$date}"; |
417 | } | |
c09bacfd TO |
418 | } |
419 | elseif ($actionSchedule->absolute_date) { | |
efc40454 | 420 | $startDateClauses[] = "DATEDIFF(DATE('!casNow'),'{$actionSchedule->absolute_date}') = 0"; |
c09bacfd TO |
421 | } |
422 | return $startDateClauses; | |
423 | } | |
424 | ||
425 | /** | |
426 | * @param int $groupId | |
427 | * @return bool | |
428 | */ | |
429 | protected function isSmartGroup($groupId) { | |
430 | // Then decide which table to join onto the query | |
431 | $group = \CRM_Contact_DAO_Group::getTableName(); | |
432 | ||
433 | // Get the group information | |
434 | $sql = " | |
435 | SELECT $group.id, $group.cache_date, $group.saved_search_id, $group.children | |
436 | FROM $group | |
437 | WHERE $group.id = {$groupId} | |
438 | "; | |
439 | ||
440 | $groupDAO = \CRM_Core_DAO::executeQuery($sql); | |
441 | if ( | |
442 | $groupDAO->fetch() && | |
443 | !empty($groupDAO->saved_search_id) | |
444 | ) { | |
445 | return TRUE; | |
446 | } | |
447 | return FALSE; | |
448 | } | |
449 | ||
450 | /** | |
451 | * @param string $dateField | |
452 | * @return \CRM_Utils_SQL_Select | |
453 | */ | |
454 | protected function prepareRepetitionEndFilter($dateField) { | |
455 | $repeatEventDateExpr = ($this->actionSchedule->end_action == 'before' ? 'DATE_SUB' : 'DATE_ADD') | |
456 | . "({$dateField}, INTERVAL {$this->actionSchedule->end_frequency_interval} {$this->actionSchedule->end_frequency_unit})"; | |
457 | ||
458 | return \CRM_Utils_SQL_Select::fragment() | |
546a1ecc | 459 | ->where("@casNow <= !repetitionEndDate") |
c64f69d9 | 460 | ->param([ |
c09bacfd | 461 | '!repetitionEndDate' => $repeatEventDateExpr, |
c64f69d9 | 462 | ]); |
c09bacfd TO |
463 | } |
464 | ||
c09bacfd TO |
465 | /** |
466 | * @param string $contactIdField | |
467 | * @return \CRM_Utils_SQL_Select|null | |
468 | */ | |
469 | protected function prepareAddlFilter($contactIdField) { | |
470 | $contactAddlFilter = NULL; | |
471 | if ($this->actionSchedule->limit_to !== NULL && !$this->actionSchedule->limit_to /*0*/) { | |
472 | $contactAddlFilter = $this->prepareContactFilter($contactIdField); | |
473 | } | |
474 | return $contactAddlFilter; | |
475 | } | |
476 | ||
c09bacfd TO |
477 | /** |
478 | * Generate a query fragment like for populating | |
479 | * action logs, e.g. | |
480 | * | |
481 | * "SELECT contact_id, entity_id, entity_table, action schedule_id" | |
482 | * | |
483 | * @param string $phase | |
484 | * @param \CRM_Utils_SQL_Select $query | |
485 | * @return \CRM_Utils_SQL_Select | |
486 | * @throws \CRM_Core_Exception | |
487 | */ | |
488 | protected function selectActionLogFields($phase, $query) { | |
e08fae02 | 489 | $selectArray = []; |
c09bacfd | 490 | switch ($phase) { |
546a1ecc TO |
491 | case self::PHASE_RELATION_FIRST: |
492 | case self::PHASE_RELATION_REPEAT: | |
c09bacfd | 493 | $fragment = \CRM_Utils_SQL_Select::fragment(); |
e08fae02 PH |
494 | $selectArray = [ |
495 | "!casContactIdField as contact_id", | |
496 | "!casEntityIdField as entity_id", | |
497 | "@casMappingEntity as entity_table", | |
498 | "#casActionScheduleId as action_schedule_id", | |
499 | ]; | |
500 | if ($this->resetOnTriggerDateChange()) { | |
501 | $selectArray[] = "!casDateField as reference_date"; | |
c09bacfd | 502 | } |
c09bacfd TO |
503 | break; |
504 | ||
546a1ecc TO |
505 | case self::PHASE_ADDITION_FIRST: |
506 | case self::PHASE_ADDITION_REPEAT: | |
9d180e38 | 507 | //CRM-19017: Load default params for fragment query object. |
c64f69d9 | 508 | $params = [ |
9d180e38 JP |
509 | 'casActionScheduleId' => $this->actionSchedule->id, |
510 | 'casNow' => $this->now, | |
c64f69d9 | 511 | ]; |
9d180e38 | 512 | $fragment = \CRM_Utils_SQL_Select::fragment()->param($params); |
e08fae02 PH |
513 | $selectArray = [ |
514 | "c.id as contact_id", | |
515 | "c.id as entity_id", | |
516 | "'civicrm_contact' as entity_table", | |
517 | "#casActionScheduleId as action_schedule_id", | |
518 | ]; | |
c09bacfd TO |
519 | break; |
520 | ||
521 | default: | |
522 | throw new \CRM_Core_Exception("Unrecognized phase: $phase"); | |
523 | } | |
e08fae02 | 524 | $fragment->select($selectArray); |
c09bacfd TO |
525 | return $fragment; |
526 | } | |
527 | ||
528 | /** | |
529 | * Generate a query fragment like for populating | |
530 | * action logs, e.g. | |
531 | * | |
532 | * "INSERT INTO civicrm_action_log (...) SELECT (...)" | |
533 | * | |
534 | * @param string $phase | |
535 | * @param \CRM_Utils_SQL_Select $query | |
536 | * @return \CRM_Utils_SQL_Select | |
537 | * @throws \CRM_Core_Exception | |
538 | */ | |
539 | protected function selectIntoActionLog($phase, $query) { | |
c64f69d9 | 540 | $actionLogColumns = [ |
c09bacfd TO |
541 | "contact_id", |
542 | "entity_id", | |
543 | "entity_table", | |
544 | "action_schedule_id", | |
c64f69d9 | 545 | ]; |
e08fae02 PH |
546 | |
547 | if ($this->resetOnTriggerDateChange() && ($phase == self::PHASE_RELATION_FIRST || $phase == self::PHASE_RELATION_REPEAT)) { | |
548 | $actionLogColumns[] = "reference_date"; | |
c09bacfd TO |
549 | } |
550 | ||
551 | return $this->selectActionLogFields($phase, $query) | |
552 | ->insertInto('civicrm_action_log', $actionLogColumns); | |
553 | } | |
554 | ||
555 | /** | |
556 | * Add a JOIN clause like "INNER JOIN civicrm_action_log reminder ON...". | |
557 | * | |
558 | * @param string $joinType | |
559 | * Join type (eg INNER JOIN, LEFT JOIN). | |
560 | * @param string $for | |
561 | * Ex: 'rel', 'addl'. | |
562 | * @param \CRM_Utils_SQL_Select $query | |
563 | * @return \CRM_Utils_SQL_Select | |
564 | * @throws \CRM_Core_Exception | |
565 | */ | |
566 | protected function joinReminder($joinType, $for, $query) { | |
567 | switch ($for) { | |
568 | case 'rel': | |
569 | $contactIdField = $query['casContactIdField']; | |
9e1bf145 | 570 | $entityName = $this->mapping->getEntity(); |
c09bacfd TO |
571 | $entityIdField = $query['casEntityIdField']; |
572 | break; | |
573 | ||
574 | case 'addl': | |
575 | $contactIdField = 'c.id'; | |
576 | $entityName = 'civicrm_contact'; | |
577 | $entityIdField = 'c.id'; | |
578 | break; | |
579 | ||
580 | default: | |
581 | throw new \CRM_Core_Exception("Unrecognized 'for': $for"); | |
582 | } | |
583 | ||
584 | $joinClause = "civicrm_action_log reminder ON reminder.contact_id = {$contactIdField} AND | |
585 | reminder.entity_id = {$entityIdField} AND | |
586 | reminder.entity_table = '{$entityName}' AND | |
587 | reminder.action_schedule_id = {$this->actionSchedule->id}"; | |
588 | ||
e08fae02 PH |
589 | if ($for == 'rel' && $this->resetOnTriggerDateChange()) { |
590 | $joinClause .= " AND\nreminder.reference_date = !casDateField"; | |
591 | } | |
592 | ||
c09bacfd TO |
593 | // Why do we only include anniversary clause for 'rel' queries? |
594 | if ($for === 'rel' && !empty($query['casAnniversaryMode'])) { | |
595 | // only consider reminders less than 11 months ago | |
546a1ecc | 596 | $joinClause .= " AND reminder.action_date_time > DATE_SUB(!casNow, INTERVAL 11 MONTH)"; |
c09bacfd TO |
597 | } |
598 | ||
599 | return \CRM_Utils_SQL_Select::fragment()->join("reminder", "$joinType $joinClause"); | |
600 | } | |
601 | ||
e08fae02 PH |
602 | /** |
603 | * Should we use the reference date when checking to see if we already | |
604 | * sent reminders. | |
605 | * | |
606 | * @return bool | |
607 | */ | |
608 | protected function resetOnTriggerDateChange() { | |
609 | return $this->mapping->resetOnTriggerDateChange($this->actionSchedule); | |
610 | } | |
611 | ||
c09bacfd | 612 | } |