3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2016 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
10 | CiviCRM is free software; you can copy, modify, and distribute it |
11 | under the terms of the GNU Affero General Public License |
12 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
14 | CiviCRM is distributed in the hope that it will be useful, but |
15 | WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
17 | See the GNU Affero General Public License for more details. |
19 | You should have received a copy of the GNU Affero General Public |
20 | License and the CiviCRM Licensing Exception along |
21 | with this program; if not, contact CiviCRM LLC |
22 | at info[AT]civicrm[DOT]org. If you have questions about the |
23 | GNU Affero General Public License or the licensing of CiviCRM, |
24 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
25 +--------------------------------------------------------------------+
28 namespace Civi\ActionSchedule
;
31 * Class RecipientBuilder
32 * @package Civi\ActionSchedule
34 * The RecipientBuilder prepares a list of recipients based on an action-schedule.
36 * This is a four-step process, with different steps depending on:
38 * (a) How the recipient is identified. Sometimes recipients are identified based
39 * on their relations (e.g. selecting the assignees of an activity or the
40 * participants of an event), and sometimes they are manually added using
41 * a flat contact list (e.g. with a contact ID or group ID).
42 * (b) Whether this is the first reminder or a follow-up/repeated reminder.
44 * The permutations of these (a)+(b) produce four phases -- RELATION_FIRST,
45 * RELATION_REPEAT, ADDITION_FIRST, ADDITION_REPEAT.
47 * Each phase requires running a complex query. As a general rule,
48 * MappingInterface::createQuery() produces a base query, and the RecipientBuilder
49 * appends extra bits (JOINs/WHEREs/GROUP BYs) depending on which step is running.
51 * For example, suppose we want to send reminders to anyone who registers for
52 * a "Conference" or "Exhibition" event with the 'pay later' option, and we want
53 * to fire the reminders X days after the registration date. The
54 * MappingInterface::createQuery() could return a query like:
57 * CRM_Utils_SQL_Select::from('civicrm_participant e')
58 * ->join('event', 'INNER JOIN civicrm_event event ON e.event_id = event.id')
59 * ->where('e.is_pay_later = 1')
60 * ->where('event.event_type_id IN (#myEventTypes)')
61 * ->param('myEventTypes', array(2, 5))
62 * ->param('casDateField', 'e.register_date')
63 * ->param($defaultParams)
67 * In the RELATION_FIRST phase, RecipientBuilder adds a LEFT-JOIN+WHERE to find
68 * participants who have *not* yet received any reminder, and filters those
69 * participants based on whether X days have passed since "e.register_date".
71 * Notice that the query may define several SQL elements directly (eg
72 * via `from()`, `where()`, `join()`, `groupBy()`). Additionally, it
73 * must define some parameters (eg `casDateField`). These parameters will be
74 * read by RecipientBuilder and used in other parts of the query.
76 * At time of writing, these parameters are required:
77 * - casAddlCheckFrom: string, SQL FROM expression
78 * - casContactIdField: string, SQL column expression
79 * - casDateField: string, SQL column expression
80 * - casEntityIdField: string, SQL column expression
82 * Some parameters are optional:
83 * - casContactTableAlias: string, SQL table alias
84 * - casAnniversaryMode: bool
85 * - casUseReferenceDate: bool
87 * Additionally, some parameters are automatically predefined:
89 * - casMappingEntity: string, SQL table name
91 * - casActionScheduleId: int
93 * Note: Any parameters defined by the core Civi\ActionSchedule subsystem
94 * use the prefix `cas`. If you define new parameters (like `myEventTypes`
95 * above), then use a different name (to avoid conflicts).
97 class RecipientBuilder
{
102 * Generate action_log's for new, first-time alerts to related contacts.
104 * @see buildRelFirstPass
106 const PHASE_RELATION_FIRST
= 'rel-first';
109 * Generate action_log's for new, first-time alerts to additional contacts.
111 * @see buildAddlFirstPass
113 const PHASE_ADDITION_FIRST
= 'addl-first';
116 * Generate action_log's for repeated, follow-up alerts to related contacts.
118 * @see buildRelRepeatPass
120 const PHASE_RELATION_REPEAT
= 'rel-repeat';
123 * Generate action_log's for repeated, follow-up alerts to additional contacts.
125 * @see buildAddlRepeatPass
127 const PHASE_ADDITION_REPEAT
= 'addl-repeat';
130 * @var \CRM_Core_DAO_ActionSchedule
132 private $actionSchedule;
135 * @var MappingInterface
141 * @param \CRM_Core_DAO_ActionSchedule $actionSchedule
142 * @param MappingInterface $mapping
144 public function __construct($now, $actionSchedule, $mapping) {
146 $this->actionSchedule
= $actionSchedule;
147 $this->mapping
= $mapping;
151 * Fill the civicrm_action_log with any new/missing TODOs.
153 * @throws \CRM_Core_Exception
155 public function build() {
156 $this->buildRelFirstPass();
158 if ($this->prepareAddlFilter('c.id')) {
159 $this->buildAddlFirstPass();
162 if ($this->actionSchedule
->is_repeat
) {
163 $this->buildRelRepeatPass();
166 if ($this->actionSchedule
->is_repeat
&& $this->prepareAddlFilter('c.id')) {
167 $this->buildAddlRepeatPass();
172 * Generate action_log's for new, first-time alerts to related contacts.
176 protected function buildRelFirstPass() {
177 $query = $this->prepareQuery(self
::PHASE_RELATION_FIRST
);
179 $startDateClauses = $this->prepareStartDateClauses();
181 // In some cases reference_date got outdated due to many reason e.g. In Membership renewal end_date got extended
182 // which means reference date mismatches with the end_date where end_date may be used as the start_action_date
183 // criteria for some schedule reminder so in order to send new reminder we INSERT new reminder with new reference_date
184 // value via UNION operation
185 $referenceReminderIDs = array();
186 $referenceDate = NULL;
187 if (!empty($query['casUseReferenceDate'])) {
188 // First retrieve all the action log's ids which are outdated or in other words reference_date now don't match with entity date.
189 // And the retrieve the updated entity date which will later used below to update all other outdated action log records
190 $sql = $query->copy()
191 ->select('reminder.id as id')
192 ->select($query['casDateField'] . ' as reference_date')
193 ->merge($this->joinReminder('INNER JOIN', 'rel', $query))
194 ->where("reminder.id IS NOT NULL AND reminder.reference_date IS NOT NULL AND reminder.reference_date <> !casDateField")
195 ->where($startDateClauses)
196 ->orderBy("reminder.id desc")
199 $dao = \CRM_Core_DAO
::executeQuery($sql);
201 while ($dao->fetch()) {
202 $referenceReminderIDs[] = $dao->id
;
203 $referenceDate = $dao->reference_date
;
207 if (empty($referenceReminderIDs)) {
208 $firstQuery = $query->copy()
209 ->merge($this->selectIntoActionLog(self
::PHASE_RELATION_FIRST
, $query))
210 ->merge($this->joinReminder('LEFT JOIN', 'rel', $query))
211 ->where("reminder.id IS NULL")
212 ->where($startDateClauses)
215 \CRM_Core_DAO
::executeQuery($firstQuery);
218 // INSERT new log to send reminder as desired entity date got updated
219 $referenceQuery = $query->copy()
220 ->merge($this->selectIntoActionLog(self
::PHASE_RELATION_FIRST
, $query))
221 ->merge($this->joinReminder('LEFT JOIN', 'rel', $query))
222 ->where("reminder.id = !reminderID")
223 ->where($startDateClauses)
224 ->param('reminderID', $referenceReminderIDs[0])
227 \CRM_Core_DAO
::executeQuery($referenceQuery);
229 // Update all the previous outdated reference date valued, action_log rows to the latest changed entity date
230 $updateQuery = "UPDATE civicrm_action_log SET reference_date = '" . $referenceDate . "' WHERE id IN (" . implode(', ', $referenceReminderIDs) . ")";
231 \CRM_Core_DAO
::executeQuery($updateQuery);
236 * Generate action_log's for new, first-time alerts to additional contacts.
240 protected function buildAddlFirstPass() {
241 $query = $this->prepareQuery(self
::PHASE_ADDITION_FIRST
);
243 $insertAdditionalSql = \CRM_Utils_SQL_Select
::from("civicrm_contact c")
244 ->merge($query, array('params'))
245 ->merge($this->selectIntoActionLog(self
::PHASE_ADDITION_FIRST
, $query))
246 ->merge($this->joinReminder('LEFT JOIN', 'addl', $query))
247 ->where('reminder.id IS NULL')
248 ->where("c.is_deleted = 0 AND c.is_deceased = 0")
249 ->merge($this->prepareAddlFilter('c.id'))
250 ->where("c.id NOT IN (
251 SELECT rem.contact_id
252 FROM civicrm_action_log rem INNER JOIN {$this->mapping->getEntity()} e ON rem.entity_id = e.id
253 WHERE rem.action_schedule_id = {$this->actionSchedule->id}
254 AND rem.entity_table = '{$this->mapping->getEntity()}'
256 // Where does e.id come from here? ^^^
260 \CRM_Core_DAO
::executeQuery($insertAdditionalSql);
264 * Generate action_log's for repeated, follow-up alerts to related contacts.
266 * @throws \CRM_Core_Exception
269 protected function buildRelRepeatPass() {
270 $query = $this->prepareQuery(self
::PHASE_RELATION_REPEAT
);
271 $startDateClauses = $this->prepareStartDateClauses();
273 // CRM-15376 - do not send our reminders if original criteria no longer applies
274 // the first part of the startDateClause array is the earliest the reminder can be sent. If the
275 // event (e.g membership_end_date) has changed then the reminder may no longer apply
276 // @todo - this only handles events that get moved later. Potentially they might get moved earlier
277 $repeatInsert = $query
278 ->merge($this->joinReminder('INNER JOIN', 'rel', $query))
279 ->merge($this->selectActionLogFields(self
::PHASE_RELATION_REPEAT
, $query))
280 ->select("MAX(reminder.action_date_time) as latest_log_time")
281 ->merge($this->prepareRepetitionEndFilter($query['casDateField']))
282 ->where($this->actionSchedule
->start_action_date ?
$startDateClauses[0] : array())
283 ->groupBy("reminder.contact_id, reminder.entity_id, reminder.entity_table")
284 // @todo replace use of timestampdiff with a direct comparison as TIMESTAMPDIFF cannot use an index.
285 ->having("TIMESTAMPDIFF(HOUR, latest_log_time, CAST(!casNow AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL !casRepetitionInterval))")
287 'casRepetitionInterval' => $this->parseRepetitionInterval(),
292 // For unknown reasons, we manually insert each row. Why not change
293 // selectActionLogFields() to selectIntoActionLog() above?
295 $arrValues = \CRM_Core_DAO
::executeQuery($repeatInsert)->fetchAll();
297 \CRM_Core_DAO
::executeQuery(
298 \CRM_Utils_SQL_Insert
::into('civicrm_action_log')
299 ->columns(array('contact_id', 'entity_id', 'entity_table', 'action_schedule_id'))
307 * Generate action_log's for repeated, follow-up alerts to additional contacts.
309 * @throws \CRM_Core_Exception
312 protected function buildAddlRepeatPass() {
313 $query = $this->prepareQuery(self
::PHASE_ADDITION_REPEAT
);
315 $addlCheck = \CRM_Utils_SQL_Select
::from($query['casAddlCheckFrom'])
317 ->merge($query, array('wheres'))// why only where? why not the joins?
318 ->merge($this->prepareRepetitionEndFilter($query['casDateField']))
323 $daoCheck = \CRM_Core_DAO
::executeQuery($addlCheck);
324 if ($daoCheck->fetch()) {
325 $repeatInsertAddl = \CRM_Utils_SQL_Select
::from('civicrm_contact c')
326 ->merge($this->selectActionLogFields(self
::PHASE_ADDITION_REPEAT
, $query))
327 ->merge($this->joinReminder('INNER JOIN', 'addl', $query))
328 ->select("MAX(reminder.action_date_time) as latest_log_time")
329 ->merge($this->prepareAddlFilter('c.id'))
330 ->where("c.is_deleted = 0 AND c.is_deceased = 0")
331 ->groupBy("reminder.contact_id")
332 // @todo replace use of timestampdiff with a direct comparison as TIMESTAMPDIFF cannot use an index.
333 ->having("TIMESTAMPDIFF(HOUR, latest_log_time, CAST(!casNow AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL !casRepetitionInterval)")
335 'casRepetitionInterval' => $this->parseRepetitionInterval(),
340 // For unknown reasons, we manually insert each row. Why not change
341 // selectActionLogFields() to selectIntoActionLog() above?
343 $addValues = \CRM_Core_DAO
::executeQuery($repeatInsertAddl)->fetchAll();
345 \CRM_Core_DAO
::executeQuery(
346 \CRM_Utils_SQL_Insert
::into('civicrm_action_log')
347 ->columns(array('contact_id', 'entity_id', 'entity_table', 'action_schedule_id'))
356 * @param string $phase
357 * @return \CRM_Utils_SQL_Select
358 * @throws \CRM_Core_Exception
360 protected function prepareQuery($phase) {
361 $defaultParams = array(
362 'casActionScheduleId' => $this->actionSchedule
->id
,
363 'casMappingId' => $this->mapping
->getId(),
364 'casMappingEntity' => $this->mapping
->getEntity(),
365 'casNow' => $this->now
,
368 /** @var \CRM_Utils_SQL_Select $query */
369 $query = $this->mapping
->createQuery($this->actionSchedule
, $phase, $defaultParams);
371 if ($this->actionSchedule
->limit_to
/*1*/) {
372 $query->merge($this->prepareContactFilter($query['casContactIdField']));
375 if (empty($query['casContactTableAlias'])) {
376 $query['casContactTableAlias'] = 'c';
377 $query->join('c', "INNER JOIN civicrm_contact c ON c.id = !casContactIdField AND c.is_deleted = 0 AND c.is_deceased = 0 ");
379 $multilingual = \CRM_Core_I18n
::isMultilingual();
380 if ($multilingual && !empty($this->actionSchedule
->filter_contact_language
)) {
381 $query->where($this->prepareLanguageFilter($query['casContactTableAlias']));
388 * Parse repetition interval.
392 protected function parseRepetitionInterval() {
393 $actionSchedule = $this->actionSchedule
;
394 if ($actionSchedule->repetition_frequency_unit
== 'day') {
395 $interval = "{$actionSchedule->repetition_frequency_interval} DAY";
397 elseif ($actionSchedule->repetition_frequency_unit
== 'week') {
398 $interval = "{$actionSchedule->repetition_frequency_interval} WEEK";
400 elseif ($actionSchedule->repetition_frequency_unit
== 'month') {
401 $interval = "{$actionSchedule->repetition_frequency_interval} MONTH";
403 elseif ($actionSchedule->repetition_frequency_unit
== 'year') {
404 $interval = "{$actionSchedule->repetition_frequency_interval} YEAR";
407 $interval = "{$actionSchedule->repetition_frequency_interval} HOUR";
413 * Prepare filter options for limiting by contact ID or group ID.
415 * @param string $contactIdField
416 * @return \CRM_Utils_SQL_Select
418 protected function prepareContactFilter($contactIdField) {
419 $actionSchedule = $this->actionSchedule
;
421 if ($actionSchedule->group_id
) {
422 if ($this->isSmartGroup($actionSchedule->group_id
)) {
423 // Check that the group is in place in the cache and up to date
424 \CRM_Contact_BAO_GroupContactCache
::check($actionSchedule->group_id
);
425 return \CRM_Utils_SQL_Select
::fragment()
426 ->join('grp', "INNER JOIN civicrm_group_contact_cache grp ON {$contactIdField} = grp.contact_id")
427 ->where(" grp.group_id IN ({$actionSchedule->group_id})");
430 return \CRM_Utils_SQL_Select
::fragment()
431 ->join('grp', " INNER JOIN civicrm_group_contact grp ON {$contactIdField} = grp.contact_id AND grp.status = 'Added'")
432 ->where(" grp.group_id IN ({$actionSchedule->group_id})");
435 elseif (!empty($actionSchedule->recipient_manual
)) {
436 $rList = \CRM_Utils_Type
::escape($actionSchedule->recipient_manual
, 'String');
437 return \CRM_Utils_SQL_Select
::fragment()
438 ->where("{$contactIdField} IN ({$rList})");
444 * Prepare language filter.
446 * @param string $contactTableAlias
449 protected function prepareLanguageFilter($contactTableAlias) {
450 $actionSchedule = $this->actionSchedule
;
452 // get language filter for the schedule
453 $filter_contact_language = explode(\CRM_Core_DAO
::VALUE_SEPARATOR
, $actionSchedule->filter_contact_language
);
455 if (($key = array_search(\CRM_Core_I18n
::NONE
, $filter_contact_language)) !== FALSE) {
456 $w .= "{$contactTableAlias}.preferred_language IS NULL OR {$contactTableAlias}.preferred_language = '' OR ";
457 unset($filter_contact_language[$key]);
459 if (count($filter_contact_language) > 0) {
460 $w .= "{$contactTableAlias}.preferred_language IN ('" . implode("','", $filter_contact_language) . "')";
469 protected function prepareStartDateClauses() {
470 $actionSchedule = $this->actionSchedule
;
471 $startDateClauses = array();
472 if ($actionSchedule->start_action_date
) {
473 $op = ($actionSchedule->start_action_condition
== 'before' ?
'<=' : '>=');
474 $operator = ($actionSchedule->start_action_condition
== 'before' ?
'DATE_SUB' : 'DATE_ADD');
475 $date = $operator . "(!casDateField, INTERVAL {$actionSchedule->start_action_offset} {$actionSchedule->start_action_unit})";
476 $startDateClauses[] = "'!casNow' >= {$date}";
477 // This is weird. Waddupwidat?
478 if ($this->mapping
->getEntity() == 'civicrm_participant') {
479 $startDateClauses[] = $operator . "(!casNow, INTERVAL 1 DAY ) {$op} " . '!casDateField';
482 $startDateClauses[] = "DATE_SUB(!casNow, INTERVAL 1 DAY ) <= {$date}";
485 elseif ($actionSchedule->absolute_date
) {
486 $startDateClauses[] = "DATEDIFF(DATE('!casNow'),'{$actionSchedule->absolute_date}') = 0";
488 return $startDateClauses;
492 * @param int $groupId
495 protected function isSmartGroup($groupId) {
496 // Then decide which table to join onto the query
497 $group = \CRM_Contact_DAO_Group
::getTableName();
499 // Get the group information
501 SELECT $group.id, $group.cache_date, $group.saved_search_id, $group.children
503 WHERE $group.id = {$groupId}
506 $groupDAO = \CRM_Core_DAO
::executeQuery($sql);
508 $groupDAO->fetch() &&
509 !empty($groupDAO->saved_search_id
)
517 * @param string $dateField
518 * @return \CRM_Utils_SQL_Select
520 protected function prepareRepetitionEndFilter($dateField) {
521 $repeatEventDateExpr = ($this->actionSchedule
->end_action
== 'before' ?
'DATE_SUB' : 'DATE_ADD')
522 . "({$dateField}, INTERVAL {$this->actionSchedule->end_frequency_interval} {$this->actionSchedule->end_frequency_unit})";
524 return \CRM_Utils_SQL_Select
::fragment()
525 ->where("@casNow <= !repetitionEndDate")
527 '!repetitionEndDate' => $repeatEventDateExpr,
532 * @param string $contactIdField
533 * @return \CRM_Utils_SQL_Select|null
535 protected function prepareAddlFilter($contactIdField) {
536 $contactAddlFilter = NULL;
537 if ($this->actionSchedule
->limit_to
!== NULL && !$this->actionSchedule
->limit_to
/*0*/) {
538 $contactAddlFilter = $this->prepareContactFilter($contactIdField);
540 return $contactAddlFilter;
544 * Generate a query fragment like for populating
547 * "SELECT contact_id, entity_id, entity_table, action schedule_id"
549 * @param string $phase
550 * @param \CRM_Utils_SQL_Select $query
551 * @return \CRM_Utils_SQL_Select
552 * @throws \CRM_Core_Exception
554 protected function selectActionLogFields($phase, $query) {
556 case self
::PHASE_RELATION_FIRST
:
557 case self
::PHASE_RELATION_REPEAT
:
558 $fragment = \CRM_Utils_SQL_Select
::fragment();
559 // CRM-15376: We are not tracking the reference date for 'repeated' schedule reminders.
560 if (!empty($query['casUseReferenceDate'])) {
561 $fragment->select($query['casDateField']);
565 "!casContactIdField as contact_id",
566 "!casEntityIdField as entity_id",
567 "@casMappingEntity as entity_table",
568 "#casActionScheduleId as action_schedule_id",
573 case self
::PHASE_ADDITION_FIRST
:
574 case self
::PHASE_ADDITION_REPEAT
:
575 $fragment = \CRM_Utils_SQL_Select
::fragment();
578 "c.id as contact_id",
580 "'civicrm_contact' as entity_table",
581 "#casActionScheduleId as action_schedule_id",
587 throw new \
CRM_Core_Exception("Unrecognized phase: $phase");
593 * Generate a query fragment like for populating
596 * "INSERT INTO civicrm_action_log (...) SELECT (...)"
598 * @param string $phase
599 * @param \CRM_Utils_SQL_Select $query
600 * @return \CRM_Utils_SQL_Select
601 * @throws \CRM_Core_Exception
603 protected function selectIntoActionLog($phase, $query) {
604 $actionLogColumns = array(
608 "action_schedule_id",
610 if ($phase === self
::PHASE_RELATION_FIRST ||
$phase === self
::PHASE_RELATION_REPEAT
) {
611 if (!empty($query['casUseReferenceDate'])) {
612 array_unshift($actionLogColumns, 'reference_date');
616 return $this->selectActionLogFields($phase, $query)
617 ->insertInto('civicrm_action_log', $actionLogColumns);
621 * Add a JOIN clause like "INNER JOIN civicrm_action_log reminder ON...".
623 * @param string $joinType
624 * Join type (eg INNER JOIN, LEFT JOIN).
627 * @param \CRM_Utils_SQL_Select $query
628 * @return \CRM_Utils_SQL_Select
629 * @throws \CRM_Core_Exception
631 protected function joinReminder($joinType, $for, $query) {
634 $contactIdField = $query['casContactIdField'];
635 $entityName = $this->mapping
->getEntity();
636 $entityIdField = $query['casEntityIdField'];
640 $contactIdField = 'c.id';
641 $entityName = 'civicrm_contact';
642 $entityIdField = 'c.id';
646 throw new \
CRM_Core_Exception("Unrecognized 'for': $for");
649 $joinClause = "civicrm_action_log reminder ON reminder.contact_id = {$contactIdField} AND
650 reminder.entity_id = {$entityIdField} AND
651 reminder.entity_table = '{$entityName}' AND
652 reminder.action_schedule_id = {$this->actionSchedule->id}";
654 // Why do we only include anniversary clause for 'rel' queries?
655 if ($for === 'rel' && !empty($query['casAnniversaryMode'])) {
656 // only consider reminders less than 11 months ago
657 $joinClause .= " AND reminder.action_date_time > DATE_SUB(!casNow, INTERVAL 11 MONTH)";
660 return \CRM_Utils_SQL_Select
::fragment()->join("reminder", "$joinType $joinClause");