Civi/ActionSchedule/RecipientBuilder.php

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