Civi/ActionSchedule/RecipientBuilder.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2016                                |
   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  *  - casUseReferenceDate: bool
  86  *
  87  * Additionally, some parameters are automatically predefined:
  88  *  - casNow
  89  *  - casMappingEntity: string, SQL table name
  90  *  - casMappingId: int
  91  *  - casActionScheduleId: int
  92  *
  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).
  96  */
  97 class RecipientBuilder {
  98
  99   private $now;
 100
 101   /**
 102    * Generate action_log's for new, first-time alerts to related contacts.
 103    *
 104    * @see buildRelFirstPass
 105    */
 106   const PHASE_RELATION_FIRST = 'rel-first';
 107
 108   /**
 109    * Generate action_log's for new, first-time alerts to additional contacts.
 110    *
 111    * @see buildAddlFirstPass
 112    */
 113   const PHASE_ADDITION_FIRST = 'addl-first';
 114
 115   /**
 116    * Generate action_log's for repeated, follow-up alerts to related contacts.
 117    *
 118    * @see buildRelRepeatPass
 119    */
 120   const PHASE_RELATION_REPEAT = 'rel-repeat';
 121
 122   /**
 123    * Generate action_log's for repeated, follow-up alerts to additional contacts.
 124    *
 125    * @see buildAddlRepeatPass
 126    */
 127   const PHASE_ADDITION_REPEAT = 'addl-repeat';
 128
 129   /**
 130    * @var \CRM_Core_DAO_ActionSchedule
 131    */
 132   private $actionSchedule;
 133
 134   /**
 135    * @var MappingInterface
 136    */
 137   private $mapping;
 138
 139   /**
 140    * @param $now
 141    * @param \CRM_Core_DAO_ActionSchedule $actionSchedule
 142    * @param MappingInterface $mapping
 143    */
 144   public function __construct($now, $actionSchedule, $mapping) {
 145     $this->now = $now;
 146     $this->actionSchedule = $actionSchedule;
 147     $this->mapping = $mapping;
 148   }
 149
 150   /**
 151    * Fill the civicrm_action_log with any new/missing TODOs.
 152    *
 153    * @throws \CRM_Core_Exception
 154    */
 155   public function build() {
 156     $this->buildRelFirstPass();
 157
 158     if ($this->prepareAddlFilter('c.id')) {
 159       $this->buildAddlFirstPass();
 160     }
 161
 162     if ($this->actionSchedule->is_repeat) {
 163       $this->buildRelRepeatPass();
 164     }
 165
 166     if ($this->actionSchedule->is_repeat && $this->prepareAddlFilter('c.id')) {
 167       $this->buildAddlRepeatPass();
 168     }
 169   }
 170
 171   /**
 172    * Generate action_log's for new, first-time alerts to related contacts.
 173    *
 174    * @throws \Exception
 175    */
 176   protected function buildRelFirstPass() {
 177     $query = $this->prepareQuery(self::PHASE_RELATION_FIRST);
 178
 179     $startDateClauses = $this->prepareStartDateClauses();
 180
 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")
 197         ->strict()
 198         ->toSQL();
 199       $dao = \CRM_Core_DAO::executeQuery($sql);
 200
 201       while ($dao->fetch()) {
 202         $referenceReminderIDs[] = $dao->id;
 203         $referenceDate = $dao->reference_date;
 204       }
 205     }
 206
 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)
 213         ->strict()
 214         ->toSQL();
 215       \CRM_Core_DAO::executeQuery($firstQuery);
 216     }
 217     else {
 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])
 225         ->strict()
 226         ->toSQL();
 227       \CRM_Core_DAO::executeQuery($referenceQuery);
 228
 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);
 232     }
 233   }
 234
 235   /**
 236    * Generate action_log's for new, first-time alerts to additional contacts.
 237    *
 238    * @throws \Exception
 239    */
 240   protected function buildAddlFirstPass() {
 241     $query = $this->prepareQuery(self::PHASE_ADDITION_FIRST);
 242
 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()}'
 255              )")
 256       // Where does e.id come from here? ^^^
 257       ->groupBy("c.id")
 258       ->strict()
 259       ->toSQL();
 260     \CRM_Core_DAO::executeQuery($insertAdditionalSql);
 261   }
 262
 263   /**
 264    * Generate action_log's for repeated, follow-up alerts to related contacts.
 265    *
 266    * @throws \CRM_Core_Exception
 267    * @throws \Exception
 268    */
 269   protected function buildRelRepeatPass() {
 270     $query = $this->prepareQuery(self::PHASE_RELATION_REPEAT);
 271     $startDateClauses = $this->prepareStartDateClauses();
 272
 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       ->having("TIMESTAMPDIFF(HOUR, latest_log_time, CAST(!casNow AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL !casRepetitionInterval))")
 285       ->param(array(
 286         'casRepetitionInterval' => $this->parseRepetitionInterval(),
 287       ))
 288       ->strict()
 289       ->toSQL();
 290
 291     // For unknown reasons, we manually insert each row. Why not change
 292     // selectActionLogFields() to selectIntoActionLog() above?
 293
 294     $arrValues = \CRM_Core_DAO::executeQuery($repeatInsert)->fetchAll();
 295     if ($arrValues) {
 296       \CRM_Core_DAO::executeQuery(
 297         \CRM_Utils_SQL_Insert::into('civicrm_action_log')
 298           ->columns(array('contact_id', 'entity_id', 'entity_table', 'action_schedule_id'))
 299           ->rows($arrValues)
 300           ->toSQL()
 301       );
 302     }
 303   }
 304
 305   /**
 306    * Generate action_log's for repeated, follow-up alerts to additional contacts.
 307    *
 308    * @throws \CRM_Core_Exception
 309    * @throws \Exception
 310    */
 311   protected function buildAddlRepeatPass() {
 312     $query = $this->prepareQuery(self::PHASE_ADDITION_REPEAT);
 313
 314     $addlCheck = \CRM_Utils_SQL_Select::from($query['casAddlCheckFrom'])
 315       ->select('*')
 316       ->merge($query, array('wheres'))// why only where? why not the joins?
 317       ->merge($this->prepareRepetitionEndFilter($query['casDateField']))
 318       ->limit(1)
 319       ->strict()
 320       ->toSQL();
 321
 322     $daoCheck = \CRM_Core_DAO::executeQuery($addlCheck);
 323     if ($daoCheck->fetch()) {
 324       $repeatInsertAddl = \CRM_Utils_SQL_Select::from('civicrm_contact c')
 325         ->merge($this->selectActionLogFields(self::PHASE_ADDITION_REPEAT, $query))
 326         ->merge($this->joinReminder('INNER JOIN', 'addl', $query))
 327         ->select("MAX(reminder.action_date_time) as latest_log_time")
 328         ->merge($this->prepareAddlFilter('c.id'))
 329         ->where("c.is_deleted = 0 AND c.is_deceased = 0")
 330         ->groupBy("reminder.contact_id")
 331         ->having("TIMESTAMPDIFF(HOUR, latest_log_time, CAST(!casNow AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL !casRepetitionInterval)")
 332         ->param(array(
 333           'casRepetitionInterval' => $this->parseRepetitionInterval(),
 334         ))
 335         ->strict()
 336         ->toSQL();
 337
 338       // For unknown reasons, we manually insert each row. Why not change
 339       // selectActionLogFields() to selectIntoActionLog() above?
 340
 341       $addValues = \CRM_Core_DAO::executeQuery($repeatInsertAddl)->fetchAll();
 342       if ($addValues) {
 343         \CRM_Core_DAO::executeQuery(
 344           \CRM_Utils_SQL_Insert::into('civicrm_action_log')
 345             ->columns(array('contact_id', 'entity_id', 'entity_table', 'action_schedule_id'))
 346             ->rows($addValues)
 347             ->toSQL()
 348         );
 349       }
 350     }
 351   }
 352
 353   /**
 354    * @param string $phase
 355    * @return \CRM_Utils_SQL_Select
 356    * @throws \CRM_Core_Exception
 357    */
 358   protected function prepareQuery($phase) {
 359     $defaultParams = array(
 360       'casActionScheduleId' => $this->actionSchedule->id,
 361       'casMappingId' => $this->mapping->getId(),
 362       'casMappingEntity' => $this->mapping->getEntity(),
 363       'casNow' => $this->now,
 364     );
 365
 366     /** @var \CRM_Utils_SQL_Select $query */
 367     $query = $this->mapping->createQuery($this->actionSchedule, $phase, $defaultParams);
 368
 369     if ($this->actionSchedule->limit_to /*1*/) {
 370       $query->merge($this->prepareContactFilter($query['casContactIdField']));
 371     }
 372
 373     if (empty($query['casContactTableAlias'])) {
 374       $query['casContactTableAlias'] = 'c';
 375       $query->join('c', "INNER JOIN civicrm_contact c ON c.id = !casContactIdField AND c.is_deleted = 0 AND c.is_deceased = 0 ");
 376     }
 377     $multilingual = \CRM_Core_I18n::isMultilingual();
 378     if ($multilingual && !empty($this->actionSchedule->filter_contact_language)) {
 379       $query->where($this->prepareLanguageFilter($query['casContactTableAlias']));
 380     }
 381
 382     return $query;
 383   }
 384
 385   /**
 386    * Parse repetition interval.
 387    *
 388    * @return int|string
 389    */
 390   protected function parseRepetitionInterval() {
 391     $actionSchedule = $this->actionSchedule;
 392     if ($actionSchedule->repetition_frequency_unit == 'day') {
 393       $interval = "{$actionSchedule->repetition_frequency_interval} DAY";
 394     }
 395     elseif ($actionSchedule->repetition_frequency_unit == 'week') {
 396       $interval = "{$actionSchedule->repetition_frequency_interval} WEEK";
 397     }
 398     elseif ($actionSchedule->repetition_frequency_unit == 'month') {
 399       $interval = "{$actionSchedule->repetition_frequency_interval} MONTH";
 400     }
 401     elseif ($actionSchedule->repetition_frequency_unit == 'year') {
 402       $interval = "{$actionSchedule->repetition_frequency_interval} YEAR";
 403     }
 404     else {
 405       $interval = "{$actionSchedule->repetition_frequency_interval} HOUR";
 406     }
 407     return $interval;
 408   }
 409
 410   /**
 411    * Prepare filter options for limiting by contact ID or group ID.
 412    *
 413    * @param string $contactIdField
 414    * @return \CRM_Utils_SQL_Select
 415    */
 416   protected function prepareContactFilter($contactIdField) {
 417     $actionSchedule = $this->actionSchedule;
 418
 419     if ($actionSchedule->group_id) {
 420       if ($this->isSmartGroup($actionSchedule->group_id)) {
 421         // Check that the group is in place in the cache and up to date
 422         \CRM_Contact_BAO_GroupContactCache::check($actionSchedule->group_id);
 423         return \CRM_Utils_SQL_Select::fragment()
 424           ->join('grp', "INNER JOIN civicrm_group_contact_cache grp ON {$contactIdField} = grp.contact_id")
 425           ->where(" grp.group_id IN ({$actionSchedule->group_id})");
 426       }
 427       else {
 428         return \CRM_Utils_SQL_Select::fragment()
 429           ->join('grp', " INNER JOIN civicrm_group_contact grp ON {$contactIdField} = grp.contact_id AND grp.status = 'Added'")
 430           ->where(" grp.group_id IN ({$actionSchedule->group_id})");
 431       }
 432     }
 433     elseif (!empty($actionSchedule->recipient_manual)) {
 434       $rList = \CRM_Utils_Type::escape($actionSchedule->recipient_manual, 'String');
 435       return \CRM_Utils_SQL_Select::fragment()
 436         ->where("{$contactIdField} IN ({$rList})");
 437     }
 438     return NULL;
 439   }
 440
 441   /**
 442    * Prepare language filter.
 443    *
 444    * @param string $contactTableAlias
 445    * @return string
 446    */
 447   protected function prepareLanguageFilter($contactTableAlias) {
 448     $actionSchedule = $this->actionSchedule;
 449
 450     // get language filter for the schedule
 451     $filter_contact_language = explode(\CRM_Core_DAO::VALUE_SEPARATOR, $actionSchedule->filter_contact_language);
 452     $w = '';
 453     if (($key = array_search(\CRM_Core_I18n::NONE, $filter_contact_language)) !== FALSE) {
 454       $w .= "{$contactTableAlias}.preferred_language IS NULL OR {$contactTableAlias}.preferred_language = '' OR ";
 455       unset($filter_contact_language[$key]);
 456     }
 457     if (count($filter_contact_language) > 0) {
 458       $w .= "{$contactTableAlias}.preferred_language IN ('" . implode("','", $filter_contact_language) . "')";
 459     }
 460     $w = "($w)";
 461     return $w;
 462   }
 463
 464   /**
 465    * @return array
 466    */
 467   protected function prepareStartDateClauses() {
 468     $actionSchedule = $this->actionSchedule;
 469     $startDateClauses = array();
 470     if ($actionSchedule->start_action_date) {
 471       $op = ($actionSchedule->start_action_condition == 'before' ? '<=' : '>=');
 472       $operator = ($actionSchedule->start_action_condition == 'before' ? 'DATE_SUB' : 'DATE_ADD');
 473       $date = $operator . "(!casDateField, INTERVAL {$actionSchedule->start_action_offset} {$actionSchedule->start_action_unit})";
 474       $startDateClauses[] = "'!casNow' >= {$date}";
 475       // This is weird. Waddupwidat?
 476       if ($this->mapping->getEntity() == 'civicrm_participant') {
 477         $startDateClauses[] = $operator . "(!casNow, INTERVAL 1 DAY ) {$op} " . '!casDateField';
 478       }
 479       else {
 480         $startDateClauses[] = "DATE_SUB(!casNow, INTERVAL 1 DAY ) <= {$date}";
 481       }
 482     }
 483     elseif ($actionSchedule->absolute_date) {
 484       $startDateClauses[] = "DATEDIFF(DATE('!casNow'),'{$actionSchedule->absolute_date}') = 0";
 485     }
 486     return $startDateClauses;
 487   }
 488
 489   /**
 490    * @param int $groupId
 491    * @return bool
 492    */
 493   protected function isSmartGroup($groupId) {
 494     // Then decide which table to join onto the query
 495     $group = \CRM_Contact_DAO_Group::getTableName();
 496
 497     // Get the group information
 498     $sql = "
 499 SELECT     $group.id, $group.cache_date, $group.saved_search_id, $group.children
 500 FROM       $group
 501 WHERE      $group.id = {$groupId}
 502 ";
 503
 504     $groupDAO = \CRM_Core_DAO::executeQuery($sql);
 505     if (
 506       $groupDAO->fetch() &&
 507       !empty($groupDAO->saved_search_id)
 508     ) {
 509       return TRUE;
 510     }
 511     return FALSE;
 512   }
 513
 514   /**
 515    * @param string $dateField
 516    * @return \CRM_Utils_SQL_Select
 517    */
 518   protected function prepareRepetitionEndFilter($dateField) {
 519     $repeatEventDateExpr = ($this->actionSchedule->end_action == 'before' ? 'DATE_SUB' : 'DATE_ADD')
 520       . "({$dateField}, INTERVAL {$this->actionSchedule->end_frequency_interval} {$this->actionSchedule->end_frequency_unit})";
 521
 522     return \CRM_Utils_SQL_Select::fragment()
 523       ->where("@casNow <= !repetitionEndDate")
 524       ->param(array(
 525         '!repetitionEndDate' => $repeatEventDateExpr,
 526       ));
 527   }
 528
 529   /**
 530    * @param string $contactIdField
 531    * @return \CRM_Utils_SQL_Select|null
 532    */
 533   protected function prepareAddlFilter($contactIdField) {
 534     $contactAddlFilter = NULL;
 535     if ($this->actionSchedule->limit_to !== NULL && !$this->actionSchedule->limit_to /*0*/) {
 536       $contactAddlFilter = $this->prepareContactFilter($contactIdField);
 537     }
 538     return $contactAddlFilter;
 539   }
 540
 541   /**
 542    * Generate a query fragment like for populating
 543    * action logs, e.g.
 544    *
 545    * "SELECT contact_id, entity_id, entity_table, action schedule_id"
 546    *
 547    * @param string $phase
 548    * @param \CRM_Utils_SQL_Select $query
 549    * @return \CRM_Utils_SQL_Select
 550    * @throws \CRM_Core_Exception
 551    */
 552   protected function selectActionLogFields($phase, $query) {
 553     switch ($phase) {
 554       case self::PHASE_RELATION_FIRST:
 555       case self::PHASE_RELATION_REPEAT:
 556         $fragment = \CRM_Utils_SQL_Select::fragment();
 557         // CRM-15376: We are not tracking the reference date for 'repeated' schedule reminders.
 558         if (!empty($query['casUseReferenceDate'])) {
 559           $fragment->select($query['casDateField']);
 560         }
 561         $fragment->select(
 562           array(
 563             "!casContactIdField as contact_id",
 564             "!casEntityIdField as entity_id",
 565             "@casMappingEntity as entity_table",
 566             "#casActionScheduleId as action_schedule_id",
 567           )
 568         );
 569         break;
 570
 571       case self::PHASE_ADDITION_FIRST:
 572       case self::PHASE_ADDITION_REPEAT:
 573         $fragment = \CRM_Utils_SQL_Select::fragment();
 574         $fragment->select(
 575           array(
 576             "c.id as contact_id",
 577             "c.id as entity_id",
 578             "'civicrm_contact' as entity_table",
 579             "#casActionScheduleId as action_schedule_id",
 580           )
 581         );
 582         break;
 583
 584       default:
 585         throw new \CRM_Core_Exception("Unrecognized phase: $phase");
 586     }
 587     return $fragment;
 588   }
 589
 590   /**
 591    * Generate a query fragment like for populating
 592    * action logs, e.g.
 593    *
 594    * "INSERT INTO civicrm_action_log (...) SELECT (...)"
 595    *
 596    * @param string $phase
 597    * @param \CRM_Utils_SQL_Select $query
 598    * @return \CRM_Utils_SQL_Select
 599    * @throws \CRM_Core_Exception
 600    */
 601   protected function selectIntoActionLog($phase, $query) {
 602     $actionLogColumns = array(
 603       "contact_id",
 604       "entity_id",
 605       "entity_table",
 606       "action_schedule_id",
 607     );
 608     if ($phase === self::PHASE_RELATION_FIRST || $phase === self::PHASE_RELATION_REPEAT) {
 609       if (!empty($query['casUseReferenceDate'])) {
 610         array_unshift($actionLogColumns, 'reference_date');
 611       }
 612     }
 613
 614     return $this->selectActionLogFields($phase, $query)
 615       ->insertInto('civicrm_action_log', $actionLogColumns);
 616   }
 617
 618   /**
 619    * Add a JOIN clause like "INNER JOIN civicrm_action_log reminder ON...".
 620    *
 621    * @param string $joinType
 622    *   Join type (eg INNER JOIN, LEFT JOIN).
 623    * @param string $for
 624    *    Ex: 'rel', 'addl'.
 625    * @param \CRM_Utils_SQL_Select $query
 626    * @return \CRM_Utils_SQL_Select
 627    * @throws \CRM_Core_Exception
 628    */
 629   protected function joinReminder($joinType, $for, $query) {
 630     switch ($for) {
 631       case 'rel':
 632         $contactIdField = $query['casContactIdField'];
 633         $entityName = $this->mapping->getEntity();
 634         $entityIdField = $query['casEntityIdField'];
 635         break;
 636
 637       case 'addl':
 638         $contactIdField = 'c.id';
 639         $entityName = 'civicrm_contact';
 640         $entityIdField = 'c.id';
 641         break;
 642
 643       default:
 644         throw new \CRM_Core_Exception("Unrecognized 'for': $for");
 645     }
 646
 647     $joinClause = "civicrm_action_log reminder ON reminder.contact_id = {$contactIdField} AND
 648 reminder.entity_id          = {$entityIdField} AND
 649 reminder.entity_table       = '{$entityName}' AND
 650 reminder.action_schedule_id = {$this->actionSchedule->id}";
 651
 652     // Why do we only include anniversary clause for 'rel' queries?
 653     if ($for === 'rel' && !empty($query['casAnniversaryMode'])) {
 654       // only consider reminders less than 11 months ago
 655       $joinClause .= " AND reminder.action_date_time > DATE_SUB(!casNow, INTERVAL 11 MONTH)";
 656     }
 657
 658     return \CRM_Utils_SQL_Select::fragment()->join("reminder", "$joinType $joinClause");
 659   }
 660
 661 }