3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
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 |
9 +--------------------------------------------------------------------+
12 namespace Civi\ActionSchedule
;
15 * Class RecipientBuilder
16 * @package Civi\ActionSchedule
18 * The RecipientBuilder prepares a list of recipients based on an action-schedule.
20 * This is a four-step process, with different steps depending on:
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.
28 * The permutations of these (a)+(b) produce four phases -- RELATION_FIRST,
29 * RELATION_REPEAT, ADDITION_FIRST, ADDITION_REPEAT.
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.
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:
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')
47 * ->param($defaultParams)
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".
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.
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
66 * Some parameters are optional:
67 * - casContactTableAlias: string, SQL table alias
68 * - casAnniversaryMode: bool
70 * Additionally, some parameters are automatically predefined:
72 * - casMappingEntity: string, SQL table name
74 * - casActionScheduleId: int
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).
80 class RecipientBuilder
{
85 * Generate action_log's for new, first-time alerts to related contacts.
87 * @see buildRelFirstPass
89 const PHASE_RELATION_FIRST
= 'rel-first';
92 * Generate action_log's for new, first-time alerts to additional contacts.
94 * @see buildAddlFirstPass
96 const PHASE_ADDITION_FIRST
= 'addl-first';
99 * Generate action_log's for repeated, follow-up alerts to related contacts.
101 * @see buildRelRepeatPass
103 const PHASE_RELATION_REPEAT
= 'rel-repeat';
106 * Generate action_log's for repeated, follow-up alerts to additional contacts.
108 * @see buildAddlRepeatPass
110 const PHASE_ADDITION_REPEAT
= 'addl-repeat';
113 * @var \CRM_Core_DAO_ActionSchedule
115 private $actionSchedule;
118 * @var MappingInterface
124 * @param \CRM_Core_DAO_ActionSchedule $actionSchedule
125 * @param MappingInterface $mapping
127 public function __construct($now, $actionSchedule, $mapping) {
129 $this->actionSchedule
= $actionSchedule;
130 $this->mapping
= $mapping;
134 * Fill the civicrm_action_log with any new/missing TODOs.
136 * @throws \CRM_Core_Exception
138 public function build() {
139 $this->buildRelFirstPass();
141 if ($this->prepareAddlFilter('c.id') && $this->mapping
->sendToAdditional($this->actionSchedule
->entity_value
)) {
142 $this->buildAddlFirstPass();
145 if ($this->actionSchedule
->is_repeat
) {
146 $this->buildRelRepeatPass();
149 if ($this->actionSchedule
->is_repeat
&& $this->prepareAddlFilter('c.id') && $this->mapping
->sendToAdditional($this->actionSchedule
->entity_value
)) {
150 $this->buildAddlRepeatPass();
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
161 protected function buildRelFirstPass() {
162 $query = $this->prepareQuery(self
::PHASE_RELATION_FIRST
);
164 $startDateClauses = $this->prepareStartDateClauses();
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)
173 \CRM_Core_DAO
::executeQuery($firstInstanceQuery);
177 * Generate action_log's for new, first-time alerts to additional contacts.
181 protected function buildAddlFirstPass() {
182 $query = $this->prepareQuery(self
::PHASE_ADDITION_FIRST
);
184 $insertAdditionalSql = \CRM_Utils_SQL_Select
::from("civicrm_contact c")
185 ->merge($query, ['params'])
186 ->merge($this->selectIntoActionLog(self
::PHASE_ADDITION_FIRST
, $query))
187 ->merge($this->joinReminder('LEFT JOIN', 'addl', $query))
188 ->where('reminder.id IS NULL')
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
193 FROM civicrm_action_log rem INNER JOIN {$this->mapping->getEntity()} e ON rem.entity_id = e.id
194 WHERE rem.action_schedule_id = {$this->actionSchedule->id}
195 AND rem.entity_table = '{$this->mapping->getEntity()}'
197 // Where does e.id come from here? ^^^
201 \CRM_Core_DAO
::executeQuery($insertAdditionalSql);
205 * Generate action_log's for repeated, follow-up alerts to related contacts.
207 * @throws \CRM_Core_Exception
210 protected function buildRelRepeatPass() {
211 $query = $this->prepareQuery(self
::PHASE_RELATION_REPEAT
);
212 $startDateClauses = $this->prepareStartDateClauses();
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))
220 ->merge($this->selectIntoActionLog(self
::PHASE_RELATION_REPEAT
, $query))
221 ->merge($this->prepareRepetitionEndFilter($query['casDateField']))
222 ->where($this->actionSchedule
->start_action_date ?
$startDateClauses[0] : [])
223 ->groupBy("reminder.contact_id, reminder.entity_id, reminder.entity_table")
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))")
226 'casRepetitionInterval' => $this->parseRepetitionInterval(),
231 \CRM_Core_DAO
::executeQuery($repeatInsert);
235 * Generate action_log's for repeated, follow-up alerts to additional contacts.
237 * @throws \CRM_Core_Exception
240 protected function buildAddlRepeatPass() {
241 $query = $this->prepareQuery(self
::PHASE_ADDITION_REPEAT
);
243 $addlCheck = \CRM_Utils_SQL_Select
::from($query['casAddlCheckFrom'])
245 ->merge($query, ['params', 'wheres', 'joins'])
246 ->merge($this->prepareRepetitionEndFilter($query['casDateField']))
251 $daoCheck = \CRM_Core_DAO
::executeQuery($addlCheck);
252 if ($daoCheck->fetch()) {
253 $repeatInsertAddl = \CRM_Utils_SQL_Select
::from('civicrm_contact c')
254 ->merge($this->selectIntoActionLog(self
::PHASE_ADDITION_REPEAT
, $query))
255 ->merge($this->joinReminder('INNER JOIN', 'addl', $query))
256 ->merge($this->prepareAddlFilter('c.id'), ['params'])
257 ->where("c.is_deleted = 0 AND c.is_deceased = 0")
258 ->groupBy("reminder.contact_id")
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))")
261 'casRepetitionInterval' => $this->parseRepetitionInterval(),
266 \CRM_Core_DAO
::executeQuery($repeatInsertAddl);
271 * @param string $phase
272 * @return \CRM_Utils_SQL_Select
273 * @throws \CRM_Core_Exception
275 protected function prepareQuery($phase) {
277 'casActionScheduleId' => $this->actionSchedule
->id
,
278 'casMappingId' => $this->mapping
->getId(),
279 'casMappingEntity' => $this->mapping
->getEntity(),
280 'casNow' => $this->now
,
283 /** @var \CRM_Utils_SQL_Select $query */
284 $query = $this->mapping
->createQuery($this->actionSchedule
, $phase, $defaultParams);
286 if ($this->actionSchedule
->limit_to
/*1*/) {
287 $query->merge($this->prepareContactFilter($query['casContactIdField']));
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 ");
294 $multilingual = \CRM_Core_I18n
::isMultilingual();
295 if ($multilingual && !empty($this->actionSchedule
->filter_contact_language
)) {
296 $query->where($this->prepareLanguageFilter($query['casContactTableAlias']));
303 * Parse repetition interval.
307 protected function parseRepetitionInterval() {
308 $actionSchedule = $this->actionSchedule
;
309 if ($actionSchedule->repetition_frequency_unit
== 'day') {
310 $interval = "{$actionSchedule->repetition_frequency_interval} DAY";
312 elseif ($actionSchedule->repetition_frequency_unit
== 'week') {
313 $interval = "{$actionSchedule->repetition_frequency_interval} WEEK";
315 elseif ($actionSchedule->repetition_frequency_unit
== 'month') {
316 $interval = "{$actionSchedule->repetition_frequency_interval} MONTH";
318 elseif ($actionSchedule->repetition_frequency_unit
== 'year') {
319 $interval = "{$actionSchedule->repetition_frequency_interval} YEAR";
322 $interval = "{$actionSchedule->repetition_frequency_interval} HOUR";
328 * Prepare filter options for limiting by contact ID or group ID.
330 * @param string $contactIdField
331 * @return \CRM_Utils_SQL_Select
333 protected function prepareContactFilter($contactIdField) {
334 $actionSchedule = $this->actionSchedule
;
336 if ($actionSchedule->group_id
) {
337 $regularGroupIDs = $smartGroupIDs = $groupWhereCLause = [];
338 $query = \CRM_Utils_SQL_Select
::fragment();
340 // get child group IDs if any
341 $childGroupIDs = \CRM_Contact_BAO_Group
::getChildGroupIds($actionSchedule->group_id
);
342 foreach (array_merge([$actionSchedule->group_id
], $childGroupIDs) as $groupID) {
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;
349 $regularGroupIDs[] = $groupID;
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) . " ) ";
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) . " ) ";
361 return $query->where(implode(" OR ", $groupWhereCLause));
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})");
372 * Prepare language filter.
374 * @param string $contactTableAlias
377 protected function prepareLanguageFilter($contactTableAlias) {
378 $actionSchedule = $this->actionSchedule
;
380 // get language filter for the schedule
381 $filter_contact_language = explode(\CRM_Core_DAO
::VALUE_SEPARATOR
, $actionSchedule->filter_contact_language
);
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]);
387 if (count($filter_contact_language) > 0) {
388 $w .= "{$contactTableAlias}.preferred_language IN ('" . implode("','", $filter_contact_language) . "')";
397 protected function prepareStartDateClauses() {
398 $actionSchedule = $this->actionSchedule
;
399 $startDateClauses = [];
400 if ($actionSchedule->start_action_date
) {
401 $op = ($actionSchedule->start_action_condition
== 'before' ?
'<=' : '>=');
402 $operator = ($actionSchedule->start_action_condition
== 'before' ?
'DATE_SUB' : 'DATE_ADD');
403 $date = $operator . "(!casDateField, INTERVAL {$actionSchedule->start_action_offset} {$actionSchedule->start_action_unit})";
404 $startDateClauses[] = "'!casNow' >= {$date}";
405 // This is weird. Waddupwidat?
406 if ($this->mapping
->getEntity() == 'civicrm_participant') {
407 $startDateClauses[] = $operator . "(!casNow, INTERVAL 1 DAY ) {$op} " . '!casDateField';
410 $startDateClauses[] = "DATE_SUB(!casNow, INTERVAL 1 DAY ) <= {$date}";
412 if (!empty($actionSchedule->effective_start_date
)) {
413 $startDateClauses[] = "'{$actionSchedule->effective_start_date}' <= {$date}";
415 if (!empty($actionSchedule->effective_end_date
)) {
416 $startDateClauses[] = "'{$actionSchedule->effective_end_date}' > {$date}";
419 elseif ($actionSchedule->absolute_date
) {
420 $startDateClauses[] = "DATEDIFF(DATE('!casNow'),'{$actionSchedule->absolute_date}') = 0";
422 return $startDateClauses;
426 * @param int $groupId
429 protected function isSmartGroup($groupId) {
430 // Then decide which table to join onto the query
431 $group = \CRM_Contact_DAO_Group
::getTableName();
433 // Get the group information
435 SELECT $group.id, $group.cache_date, $group.saved_search_id, $group.children
437 WHERE $group.id = {$groupId}
440 $groupDAO = \CRM_Core_DAO
::executeQuery($sql);
442 $groupDAO->fetch() &&
443 !empty($groupDAO->saved_search_id
)
451 * @param string $dateField
452 * @return \CRM_Utils_SQL_Select
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})";
458 return \CRM_Utils_SQL_Select
::fragment()
459 ->where("@casNow <= !repetitionEndDate")
461 '!repetitionEndDate' => $repeatEventDateExpr,
466 * @param string $contactIdField
467 * @return \CRM_Utils_SQL_Select|null
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);
474 return $contactAddlFilter;
478 * Generate a query fragment like for populating
481 * "SELECT contact_id, entity_id, entity_table, action schedule_id"
483 * @param string $phase
484 * @param \CRM_Utils_SQL_Select $query
485 * @return \CRM_Utils_SQL_Select
486 * @throws \CRM_Core_Exception
488 protected function selectActionLogFields($phase, $query) {
491 case self
::PHASE_RELATION_FIRST
:
492 case self
::PHASE_RELATION_REPEAT
:
493 $fragment = \CRM_Utils_SQL_Select
::fragment();
495 "!casContactIdField as contact_id",
496 "!casEntityIdField as entity_id",
497 "@casMappingEntity as entity_table",
498 "#casActionScheduleId as action_schedule_id",
500 if ($this->resetOnTriggerDateChange()) {
501 $selectArray[] = "!casDateField as reference_date";
505 case self
::PHASE_ADDITION_FIRST
:
506 case self
::PHASE_ADDITION_REPEAT
:
507 //CRM-19017: Load default params for fragment query object.
509 'casActionScheduleId' => $this->actionSchedule
->id
,
510 'casNow' => $this->now
,
512 $fragment = \CRM_Utils_SQL_Select
::fragment()->param($params);
514 "c.id as contact_id",
516 "'civicrm_contact' as entity_table",
517 "#casActionScheduleId as action_schedule_id",
522 throw new \
CRM_Core_Exception("Unrecognized phase: $phase");
524 $fragment->select($selectArray);
529 * Generate a query fragment like for populating
532 * "INSERT INTO civicrm_action_log (...) SELECT (...)"
534 * @param string $phase
535 * @param \CRM_Utils_SQL_Select $query
536 * @return \CRM_Utils_SQL_Select
537 * @throws \CRM_Core_Exception
539 protected function selectIntoActionLog($phase, $query) {
540 $actionLogColumns = [
544 "action_schedule_id",
547 if ($this->resetOnTriggerDateChange() && ($phase == self
::PHASE_RELATION_FIRST ||
$phase == self
::PHASE_RELATION_REPEAT
)) {
548 $actionLogColumns[] = "reference_date";
551 return $this->selectActionLogFields($phase, $query)
552 ->insertInto('civicrm_action_log', $actionLogColumns);
556 * Add a JOIN clause like "INNER JOIN civicrm_action_log reminder ON...".
558 * @param string $joinType
559 * Join type (eg INNER JOIN, LEFT JOIN).
562 * @param \CRM_Utils_SQL_Select $query
563 * @return \CRM_Utils_SQL_Select
564 * @throws \CRM_Core_Exception
566 protected function joinReminder($joinType, $for, $query) {
569 $contactIdField = $query['casContactIdField'];
570 $entityName = $this->mapping
->getEntity();
571 $entityIdField = $query['casEntityIdField'];
575 $contactIdField = 'c.id';
576 $entityName = 'civicrm_contact';
577 $entityIdField = 'c.id';
581 throw new \
CRM_Core_Exception("Unrecognized 'for': $for");
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}";
589 if ($for == 'rel' && $this->resetOnTriggerDateChange()) {
590 $joinClause .= " AND\nreminder.reference_date = !casDateField";
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
596 $joinClause .= " AND reminder.action_date_time > DATE_SUB(!casNow, INTERVAL 11 MONTH)";
599 return \CRM_Utils_SQL_Select
::fragment()->join("reminder", "$joinType $joinClause");
603 * Should we use the reference date when checking to see if we already
608 protected function resetOnTriggerDateChange() {
609 return $this->mapping
->resetOnTriggerDateChange($this->actionSchedule
);