Civi/ActionSchedule/RecipientBuilder.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   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  +--------------------------------------------------------------------+
  10  */
  11
  12 namespace Civi\ActionSchedule;
  13
  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  *
  40  * @code
  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)
  48  *   ...etc...
  49  * @endcode
  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
  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  */
  80 class RecipientBuilder {
  81
  82   private $now;
  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';
 111
 112   /**
 113    * @var \CRM_Core_DAO_ActionSchedule
 114    */
 115   private $actionSchedule;
 116
 117   /**
 118    * @var MappingInterface
 119    */
 120   private $mapping;
 121
 122   /**
 123    * @param $now
 124    * @param \CRM_Core_DAO_ActionSchedule $actionSchedule
 125    * @param MappingInterface $mapping
 126    */
 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() {
 139     $this->buildRelFirstPass();
 140
 141     if ($this->prepareAddlFilter('c.id') && $this->notTemplate()) {
 142       $this->buildAddlFirstPass();
 143     }
 144
 145     if ($this->actionSchedule->is_repeat) {
 146       $this->buildRelRepeatPass();
 147     }
 148
 149     if ($this->actionSchedule->is_repeat && $this->prepareAddlFilter('c.id')) {
 150       $this->buildAddlRepeatPass();
 151     }
 152   }
 153
 154   /**
 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.
 158    *
 159    * @throws \Exception
 160    */
 161   protected function buildRelFirstPass() {
 162     $query = $this->prepareQuery(self::PHASE_RELATION_FIRST);
 163
 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)
 171       ->strict()
 172       ->toSQL();
 173     \CRM_Core_DAO::executeQuery($firstInstanceQuery);
 174   }
 175
 176   /**
 177    * Generate action_log's for new, first-time alerts to additional contacts.
 178    *
 179    * @throws \Exception
 180    */
 181   protected function buildAddlFirstPass() {
 182     $query = $this->prepareQuery(self::PHASE_ADDITION_FIRST);
 183
 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()}'
 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   /**
 205    * Generate action_log's for repeated, follow-up alerts to related contacts.
 206    *
 207    * @throws \CRM_Core_Exception
 208    * @throws \Exception
 209    */
 210   protected function buildRelRepeatPass() {
 211     $query = $this->prepareQuery(self::PHASE_RELATION_REPEAT);
 212     $startDateClauses = $this->prepareStartDateClauses();
 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))
 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))")
 225       ->param([
 226         'casRepetitionInterval' => $this->parseRepetitionInterval(),
 227       ])
 228       ->strict()
 229       ->toSQL();
 230
 231     \CRM_Core_DAO::executeQuery($repeatInsert);
 232   }
 233
 234   /**
 235    * Generate action_log's for repeated, follow-up alerts to additional contacts.
 236    *
 237    * @throws \CRM_Core_Exception
 238    * @throws \Exception
 239    */
 240   protected function buildAddlRepeatPass() {
 241     $query = $this->prepareQuery(self::PHASE_ADDITION_REPEAT);
 242
 243     $addlCheck = \CRM_Utils_SQL_Select::from($query['casAddlCheckFrom'])
 244       ->select('*')
 245       ->merge($query, ['params', 'wheres', 'joins'])
 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')
 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))")
 260         ->param([
 261           'casRepetitionInterval' => $this->parseRepetitionInterval(),
 262         ])
 263         ->strict()
 264         ->toSQL();
 265
 266       \CRM_Core_DAO::executeQuery($repeatInsertAddl);
 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) {
 276     $defaultParams = [
 277       'casActionScheduleId' => $this->actionSchedule->id,
 278       'casMappingId' => $this->mapping->getId(),
 279       'casMappingEntity' => $this->mapping->getEntity(),
 280       'casNow' => $this->now,
 281     ];
 282
 283     /** @var \CRM_Utils_SQL_Select $query */
 284     $query = $this->mapping->createQuery($this->actionSchedule, $phase, $defaultParams);
 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   /**
 303    * Parse repetition interval.
 304    *
 305    * @return int|string
 306    */
 307   protected function parseRepetitionInterval() {
 308     $actionSchedule = $this->actionSchedule;
 309     if ($actionSchedule->repetition_frequency_unit == 'day') {
 310       $interval = "{$actionSchedule->repetition_frequency_interval} DAY";
 311     }
 312     elseif ($actionSchedule->repetition_frequency_unit == 'week') {
 313       $interval = "{$actionSchedule->repetition_frequency_interval} WEEK";
 314     }
 315     elseif ($actionSchedule->repetition_frequency_unit == 'month') {
 316       $interval = "{$actionSchedule->repetition_frequency_interval} MONTH";
 317     }
 318     elseif ($actionSchedule->repetition_frequency_unit == 'year') {
 319       $interval = "{$actionSchedule->repetition_frequency_interval} YEAR";
 320     }
 321     else {
 322       $interval = "{$actionSchedule->repetition_frequency_interval} HOUR";
 323     }
 324     return $interval;
 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) {
 337       $regularGroupIDs = $smartGroupIDs = $groupWhereCLause = [];
 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);
 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;
 347         }
 348         else {
 349           $regularGroupIDs[] = $groupID;
 350         }
 351       }
 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) . " ) ";
 360       }
 361       return $query->where(implode(" OR ", $groupWhereCLause));
 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   /**
 372    * Prepare language filter.
 373    *
 374    * @param string $contactTableAlias
 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   /**
 395    * @return array
 396    */
 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';
 408       }
 409       else {
 410         $startDateClauses[] = "DATE_SUB(!casNow, INTERVAL 1 DAY ) <= {$date}";
 411       }
 412     }
 413     elseif ($actionSchedule->absolute_date) {
 414       $startDateClauses[] = "DATEDIFF(DATE('!casNow'),'{$actionSchedule->absolute_date}') = 0";
 415     }
 416     return $startDateClauses;
 417   }
 418
 419   /**
 420    * @param int $groupId
 421    * @return bool
 422    */
 423   protected function isSmartGroup($groupId) {
 424     // Then decide which table to join onto the query
 425     $group = \CRM_Contact_DAO_Group::getTableName();
 426
 427     // Get the group information
 428     $sql = "
 429 SELECT     $group.id, $group.cache_date, $group.saved_search_id, $group.children
 430 FROM       $group
 431 WHERE      $group.id = {$groupId}
 432 ";
 433
 434     $groupDAO = \CRM_Core_DAO::executeQuery($sql);
 435     if (
 436       $groupDAO->fetch() &&
 437       !empty($groupDAO->saved_search_id)
 438     ) {
 439       return TRUE;
 440     }
 441     return FALSE;
 442   }
 443
 444   /**
 445    * @param string $dateField
 446    * @return \CRM_Utils_SQL_Select
 447    */
 448   protected function prepareRepetitionEndFilter($dateField) {
 449     $repeatEventDateExpr = ($this->actionSchedule->end_action == 'before' ? 'DATE_SUB' : 'DATE_ADD')
 450       . "({$dateField}, INTERVAL {$this->actionSchedule->end_frequency_interval} {$this->actionSchedule->end_frequency_unit})";
 451
 452     return \CRM_Utils_SQL_Select::fragment()
 453       ->where("@casNow <= !repetitionEndDate")
 454       ->param([
 455         '!repetitionEndDate' => $repeatEventDateExpr,
 456       ]);
 457   }
 458
 459   /**
 460    * @param string $contactIdField
 461    * @return \CRM_Utils_SQL_Select|null
 462    */
 463   protected function prepareAddlFilter($contactIdField) {
 464     $contactAddlFilter = NULL;
 465     if ($this->actionSchedule->limit_to !== NULL && !$this->actionSchedule->limit_to /*0*/) {
 466       $contactAddlFilter = $this->prepareContactFilter($contactIdField);
 467     }
 468     return $contactAddlFilter;
 469   }
 470
 471   /**
 472    * Generate a query fragment like for populating
 473    * action logs, e.g.
 474    *
 475    * "SELECT contact_id, entity_id, entity_table, action schedule_id"
 476    *
 477    * @param string $phase
 478    * @param \CRM_Utils_SQL_Select $query
 479    * @return \CRM_Utils_SQL_Select
 480    * @throws \CRM_Core_Exception
 481    */
 482   protected function selectActionLogFields($phase, $query) {
 483     $selectArray = [];
 484     switch ($phase) {
 485       case self::PHASE_RELATION_FIRST:
 486       case self::PHASE_RELATION_REPEAT:
 487         $fragment = \CRM_Utils_SQL_Select::fragment();
 488         $selectArray = [
 489           "!casContactIdField as contact_id",
 490           "!casEntityIdField as entity_id",
 491           "@casMappingEntity as entity_table",
 492           "#casActionScheduleId as action_schedule_id",
 493         ];
 494         if ($this->resetOnTriggerDateChange()) {
 495           $selectArray[] = "!casDateField as reference_date";
 496         }
 497         break;
 498
 499       case self::PHASE_ADDITION_FIRST:
 500       case self::PHASE_ADDITION_REPEAT:
 501         //CRM-19017: Load default params for fragment query object.
 502         $params = [
 503           'casActionScheduleId' => $this->actionSchedule->id,
 504           'casNow' => $this->now,
 505         ];
 506         $fragment = \CRM_Utils_SQL_Select::fragment()->param($params);
 507         $selectArray = [
 508           "c.id as contact_id",
 509           "c.id as entity_id",
 510           "'civicrm_contact' as entity_table",
 511           "#casActionScheduleId as action_schedule_id",
 512         ];
 513         break;
 514
 515       default:
 516         throw new \CRM_Core_Exception("Unrecognized phase: $phase");
 517     }
 518     $fragment->select($selectArray);
 519     return $fragment;
 520   }
 521
 522   /**
 523    * Generate a query fragment like for populating
 524    * action logs, e.g.
 525    *
 526    * "INSERT INTO civicrm_action_log (...) SELECT (...)"
 527    *
 528    * @param string $phase
 529    * @param \CRM_Utils_SQL_Select $query
 530    * @return \CRM_Utils_SQL_Select
 531    * @throws \CRM_Core_Exception
 532    */
 533   protected function selectIntoActionLog($phase, $query) {
 534     $actionLogColumns = [
 535       "contact_id",
 536       "entity_id",
 537       "entity_table",
 538       "action_schedule_id",
 539     ];
 540
 541     if ($this->resetOnTriggerDateChange() && ($phase == self::PHASE_RELATION_FIRST || $phase == self::PHASE_RELATION_REPEAT)) {
 542       $actionLogColumns[] = "reference_date";
 543     }
 544
 545     return $this->selectActionLogFields($phase, $query)
 546       ->insertInto('civicrm_action_log', $actionLogColumns);
 547   }
 548
 549   /**
 550    * Add a JOIN clause like "INNER JOIN civicrm_action_log reminder ON...".
 551    *
 552    * @param string $joinType
 553    *   Join type (eg INNER JOIN, LEFT JOIN).
 554    * @param string $for
 555    *    Ex: 'rel', 'addl'.
 556    * @param \CRM_Utils_SQL_Select $query
 557    * @return \CRM_Utils_SQL_Select
 558    * @throws \CRM_Core_Exception
 559    */
 560   protected function joinReminder($joinType, $for, $query) {
 561     switch ($for) {
 562       case 'rel':
 563         $contactIdField = $query['casContactIdField'];
 564         $entityName = $this->mapping->getEntity();
 565         $entityIdField = $query['casEntityIdField'];
 566         break;
 567
 568       case 'addl':
 569         $contactIdField = 'c.id';
 570         $entityName = 'civicrm_contact';
 571         $entityIdField = 'c.id';
 572         break;
 573
 574       default:
 575         throw new \CRM_Core_Exception("Unrecognized 'for': $for");
 576     }
 577
 578     $joinClause = "civicrm_action_log reminder ON reminder.contact_id = {$contactIdField} AND
 579 reminder.entity_id          = {$entityIdField} AND
 580 reminder.entity_table       = '{$entityName}' AND
 581 reminder.action_schedule_id = {$this->actionSchedule->id}";
 582
 583     if ($for == 'rel' && $this->resetOnTriggerDateChange()) {
 584       $joinClause .= " AND\nreminder.reference_date = !casDateField";
 585     }
 586
 587     // Why do we only include anniversary clause for 'rel' queries?
 588     if ($for === 'rel' && !empty($query['casAnniversaryMode'])) {
 589       // only consider reminders less than 11 months ago
 590       $joinClause .= " AND reminder.action_date_time > DATE_SUB(!casNow, INTERVAL 11 MONTH)";
 591     }
 592
 593     return \CRM_Utils_SQL_Select::fragment()->join("reminder", "$joinType $joinClause");
 594   }
 595
 596   /**
 597    * Should we use the reference date when checking to see if we already
 598    * sent reminders.
 599    *
 600    * @return bool
 601    */
 602   protected function resetOnTriggerDateChange() {
 603     return $this->mapping->resetOnTriggerDateChange($this->actionSchedule);
 604   }
 605
 606   /**
 607    * Confirm this object isn't attached to a template.
 608    * Returns TRUE if this action schedule isn't attached to a template.
 609    * Templates are (currently) unique to events, so we only evaluate those.
 610    *
 611    * @return bool;
 612    */
 613   private function notTemplate() {
 614     if ($this->mapping->getEntity() === 'civicrm_participant') {
 615       $entityId = $this->actionSchedule->entity_value;
 616       $query = new \CRM_Utils_SQL_Select('civicrm_event e');
 617       $sql = $query
 618         ->select('is_template')
 619         ->where("e.id = {$entityId}")
 620         ->toSQL();
 621       $dao = \CRM_Core_DAO::executeQuery($sql);
 622       return !(bool) $dao->fetchValue();
 623     }
 624     return TRUE;
 625   }
 626
 627 }