Civi/ActionSchedule/RecipientBuilder.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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     $firstQuery = $query->copy()
 182       ->merge($this->selectIntoActionLog(self::PHASE_RELATION_FIRST, $query))
 183       ->merge($this->joinReminder('LEFT JOIN', 'rel', $query))
 184       ->where("reminder.id IS NULL")
 185       ->where($startDateClauses)
 186       ->strict()
 187       ->toSQL();
 188     \CRM_Core_DAO::executeQuery($firstQuery);
 189
 190     // In some cases reference_date got outdated due to many reason e.g. In Membership renewal end_date got extended
 191     // which means reference date mismatches with the end_date where end_date may be used as the start_action_date
 192     // criteria  for some schedule reminder so in order to send new reminder we INSERT new reminder with new reference_date
 193     // value via UNION operation
 194     if (!empty($query['casUseReferenceDate'])) {
 195       $referenceQuery = $query->copy()
 196         ->merge($this->selectIntoActionLog(self::PHASE_RELATION_FIRST, $query))
 197         ->merge($this->joinReminder('LEFT JOIN', 'rel', $query))
 198         ->where("reminder.id IS NOT NULL")
 199         ->where($startDateClauses)
 200         ->where("reminder.action_date_time IS NOT NULL AND reminder.reference_date IS NOT NULL")
 201         ->groupBy("reminder.id, reminder.reference_date")
 202         ->having("reminder.id = MAX(reminder.id) AND reminder.reference_date <> !casDateField")
 203         ->strict()
 204         ->toSQL();
 205       \CRM_Core_DAO::executeQuery($referenceQuery);
 206     }
 207   }
 208
 209   /**
 210    * Generate action_log's for new, first-time alerts to additional contacts.
 211    *
 212    * @throws \Exception
 213    */
 214   protected function buildAddlFirstPass() {
 215     $query = $this->prepareQuery(self::PHASE_ADDITION_FIRST);
 216
 217     $insertAdditionalSql = \CRM_Utils_SQL_Select::from("civicrm_contact c")
 218       ->merge($query, array('params'))
 219       ->merge($this->selectIntoActionLog(self::PHASE_ADDITION_FIRST, $query))
 220       ->merge($this->joinReminder('LEFT JOIN', 'addl', $query))
 221       ->where("c.is_deleted = 0 AND c.is_deceased = 0")
 222       ->merge($this->prepareAddlFilter('c.id'))
 223       ->where("c.id NOT IN (
 224              SELECT rem.contact_id
 225              FROM civicrm_action_log rem INNER JOIN {$this->mapping->getEntity()} e ON rem.entity_id = e.id
 226              WHERE rem.action_schedule_id = {$this->actionSchedule->id}
 227              AND rem.entity_table = '{$this->mapping->getEntity()}'
 228              )")
 229       // Where does e.id come from here? ^^^
 230       ->groupBy("c.id")
 231       ->strict()
 232       ->toSQL();
 233     \CRM_Core_DAO::executeQuery($insertAdditionalSql);
 234   }
 235
 236   /**
 237    * Generate action_log's for repeated, follow-up alerts to related contacts.
 238    *
 239    * @throws \CRM_Core_Exception
 240    * @throws \Exception
 241    */
 242   protected function buildRelRepeatPass() {
 243     $query = $this->prepareQuery(self::PHASE_RELATION_REPEAT);
 244     $startDateClauses = $this->prepareStartDateClauses();
 245
 246     // CRM-15376 - do not send our reminders if original criteria no longer applies
 247     // the first part of the startDateClause array is the earliest the reminder can be sent. If the
 248     // event (e.g membership_end_date) has changed then the reminder may no longer apply
 249     // @todo - this only handles events that get moved later. Potentially they might get moved earlier
 250     $repeatInsert = $query
 251       ->merge($this->joinReminder('INNER JOIN', 'rel', $query))
 252       ->merge($this->selectActionLogFields(self::PHASE_RELATION_REPEAT, $query))
 253       ->select("MAX(reminder.action_date_time) as latest_log_time")
 254       ->merge($this->prepareRepetitionEndFilter($query['casDateField']))
 255       ->where($this->actionSchedule->start_action_date ? $startDateClauses[0] : array())
 256       ->groupBy("reminder.contact_id, reminder.entity_id, reminder.entity_table")
 257       ->having("TIMESTAMPDIFF(HOUR, latest_log_time, CAST(!casNow AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL !casRepetitionInterval))")
 258       ->param(array(
 259         'casRepetitionInterval' => $this->parseRepetitionInterval(),
 260       ))
 261       ->strict()
 262       ->toSQL();
 263
 264     // For unknown reasons, we manually insert each row. Why not change
 265     // selectActionLogFields() to selectIntoActionLog() above?
 266
 267     $arrValues = \CRM_Core_DAO::executeQuery($repeatInsert)->fetchAll();
 268     if ($arrValues) {
 269       \CRM_Core_DAO::executeQuery(
 270         \CRM_Utils_SQL_Insert::into('civicrm_action_log')
 271           ->columns(array('contact_id', 'entity_id', 'entity_table', 'action_schedule_id'))
 272           ->rows($arrValues)
 273           ->toSQL()
 274       );
 275     }
 276   }
 277
 278   /**
 279    * Generate action_log's for repeated, follow-up alerts to additional contacts.
 280    *
 281    * @throws \CRM_Core_Exception
 282    * @throws \Exception
 283    */
 284   protected function buildAddlRepeatPass() {
 285     $query = $this->prepareQuery(self::PHASE_ADDITION_REPEAT);
 286
 287     $addlCheck = \CRM_Utils_SQL_Select::from($query['casAddlCheckFrom'])
 288       ->select('*')
 289       ->merge($query, array('wheres'))// why only where? why not the joins?
 290       ->merge($this->prepareRepetitionEndFilter($query['casDateField']))
 291       ->limit(1)
 292       ->strict()
 293       ->toSQL();
 294
 295     $daoCheck = \CRM_Core_DAO::executeQuery($addlCheck);
 296     if ($daoCheck->fetch()) {
 297       $repeatInsertAddl = \CRM_Utils_SQL_Select::from('civicrm_contact c')
 298         ->merge($this->selectActionLogFields(self::PHASE_ADDITION_REPEAT, $query))
 299         ->merge($this->joinReminder('INNER JOIN', 'addl', $query))
 300         ->select("MAX(reminder.action_date_time) as latest_log_time")
 301         ->merge($this->prepareAddlFilter('c.id'))
 302         ->where("c.is_deleted = 0 AND c.is_deceased = 0")
 303         ->groupBy("reminder.contact_id")
 304         ->having("TIMESTAMPDIFF(HOUR, latest_log_time, CAST(!casNow AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL !casRepetitionInterval)")
 305         ->param(array(
 306           'casRepetitionInterval' => $this->parseRepetitionInterval(),
 307         ))
 308         ->strict()
 309         ->toSQL();
 310
 311       // For unknown reasons, we manually insert each row. Why not change
 312       // selectActionLogFields() to selectIntoActionLog() above?
 313
 314       $addValues = \CRM_Core_DAO::executeQuery($repeatInsertAddl)->fetchAll();
 315       if ($addValues) {
 316         \CRM_Core_DAO::executeQuery(
 317           \CRM_Utils_SQL_Insert::into('civicrm_action_log')
 318             ->columns(array('contact_id', 'entity_id', 'entity_table', 'action_schedule_id'))
 319             ->rows($addValues)
 320             ->toSQL()
 321         );
 322       }
 323     }
 324   }
 325
 326   /**
 327    * @param string $phase
 328    * @return \CRM_Utils_SQL_Select
 329    * @throws \CRM_Core_Exception
 330    */
 331   protected function prepareQuery($phase) {
 332     $defaultParams = array(
 333       'casActionScheduleId' => $this->actionSchedule->id,
 334       'casMappingId' => $this->mapping->getId(),
 335       'casMappingEntity' => $this->mapping->getEntity(),
 336       'casNow' => $this->now,
 337     );
 338
 339     /** @var \CRM_Utils_SQL_Select $query */
 340     $query = $this->mapping->createQuery($this->actionSchedule, $phase, $defaultParams);
 341
 342     if ($this->actionSchedule->limit_to /*1*/) {
 343       $query->merge($this->prepareContactFilter($query['casContactIdField']));
 344     }
 345
 346     if (empty($query['casContactTableAlias'])) {
 347       $query['casContactTableAlias'] = 'c';
 348       $query->join('c', "INNER JOIN civicrm_contact c ON c.id = !casContactIdField AND c.is_deleted = 0 AND c.is_deceased = 0 ");
 349     }
 350     $multilingual = \CRM_Core_I18n::isMultilingual();
 351     if ($multilingual && !empty($this->actionSchedule->filter_contact_language)) {
 352       $query->where($this->prepareLanguageFilter($query['casContactTableAlias']));
 353     }
 354
 355     return $query;
 356   }
 357
 358   /**
 359    * Parse repetition interval.
 360    *
 361    * @return int|string
 362    */
 363   protected function parseRepetitionInterval() {
 364     $actionSchedule = $this->actionSchedule;
 365     if ($actionSchedule->repetition_frequency_unit == 'day') {
 366       $interval = "{$actionSchedule->repetition_frequency_interval} DAY";
 367     }
 368     elseif ($actionSchedule->repetition_frequency_unit == 'week') {
 369       $interval = "{$actionSchedule->repetition_frequency_interval} WEEK";
 370     }
 371     elseif ($actionSchedule->repetition_frequency_unit == 'month') {
 372       $interval = "{$actionSchedule->repetition_frequency_interval} MONTH";
 373     }
 374     elseif ($actionSchedule->repetition_frequency_unit == 'year') {
 375       $interval = "{$actionSchedule->repetition_frequency_interval} YEAR";
 376     }
 377     else {
 378       $interval = "{$actionSchedule->repetition_frequency_interval} HOUR";
 379     }
 380     return $interval;
 381   }
 382
 383   /**
 384    * Prepare filter options for limiting by contact ID or group ID.
 385    *
 386    * @param string $contactIdField
 387    * @return \CRM_Utils_SQL_Select
 388    */
 389   protected function prepareContactFilter($contactIdField) {
 390     $actionSchedule = $this->actionSchedule;
 391
 392     if ($actionSchedule->group_id) {
 393       if ($this->isSmartGroup($actionSchedule->group_id)) {
 394         // Check that the group is in place in the cache and up to date
 395         \CRM_Contact_BAO_GroupContactCache::check($actionSchedule->group_id);
 396         return \CRM_Utils_SQL_Select::fragment()
 397           ->join('grp', "INNER JOIN civicrm_group_contact_cache grp ON {$contactIdField} = grp.contact_id")
 398           ->where(" grp.group_id IN ({$actionSchedule->group_id})");
 399       }
 400       else {
 401         return \CRM_Utils_SQL_Select::fragment()
 402           ->join('grp', " INNER JOIN civicrm_group_contact grp ON {$contactIdField} = grp.contact_id AND grp.status = 'Added'")
 403           ->where(" grp.group_id IN ({$actionSchedule->group_id})");
 404       }
 405     }
 406     elseif (!empty($actionSchedule->recipient_manual)) {
 407       $rList = \CRM_Utils_Type::escape($actionSchedule->recipient_manual, 'String');
 408       return \CRM_Utils_SQL_Select::fragment()
 409         ->where("{$contactIdField} IN ({$rList})");
 410     }
 411     return NULL;
 412   }
 413
 414   /**
 415    * Prepare language filter.
 416    *
 417    * @param string $contactTableAlias
 418    * @return string
 419    */
 420   protected function prepareLanguageFilter($contactTableAlias) {
 421     $actionSchedule = $this->actionSchedule;
 422
 423     // get language filter for the schedule
 424     $filter_contact_language = explode(\CRM_Core_DAO::VALUE_SEPARATOR, $actionSchedule->filter_contact_language);
 425     $w = '';
 426     if (($key = array_search(\CRM_Core_I18n::NONE, $filter_contact_language)) !== FALSE) {
 427       $w .= "{$contactTableAlias}.preferred_language IS NULL OR {$contactTableAlias}.preferred_language = '' OR ";
 428       unset($filter_contact_language[$key]);
 429     }
 430     if (count($filter_contact_language) > 0) {
 431       $w .= "{$contactTableAlias}.preferred_language IN ('" . implode("','", $filter_contact_language) . "')";
 432     }
 433     $w = "($w)";
 434     return $w;
 435   }
 436
 437   /**
 438    * @return array
 439    */
 440   protected function prepareStartDateClauses() {
 441     $actionSchedule = $this->actionSchedule;
 442     $startDateClauses = array();
 443     if ($actionSchedule->start_action_date) {
 444       $op = ($actionSchedule->start_action_condition == 'before' ? '<=' : '>=');
 445       $operator = ($actionSchedule->start_action_condition == 'before' ? 'DATE_SUB' : 'DATE_ADD');
 446       $date = $operator . "(!casDateField, INTERVAL {$actionSchedule->start_action_offset} {$actionSchedule->start_action_unit})";
 447       $startDateClauses[] = "'!casNow' >= {$date}";
 448       // This is weird. Waddupwidat?
 449       if ($this->mapping->getEntity() == 'civicrm_participant') {
 450         $startDateClauses[] = $operator . "(!casNow, INTERVAL 1 DAY ) {$op} " . '!casDateField';
 451       }
 452       else {
 453         $startDateClauses[] = "DATE_SUB(!casNow, INTERVAL 1 DAY ) <= {$date}";
 454       }
 455     }
 456     elseif ($actionSchedule->absolute_date) {
 457       $startDateClauses[] = "DATEDIFF(DATE('!casNow'),'{$actionSchedule->absolute_date}') = 0";
 458     }
 459     return $startDateClauses;
 460   }
 461
 462   /**
 463    * @param int $groupId
 464    * @return bool
 465    */
 466   protected function isSmartGroup($groupId) {
 467     // Then decide which table to join onto the query
 468     $group = \CRM_Contact_DAO_Group::getTableName();
 469
 470     // Get the group information
 471     $sql = "
 472 SELECT     $group.id, $group.cache_date, $group.saved_search_id, $group.children
 473 FROM       $group
 474 WHERE      $group.id = {$groupId}
 475 ";
 476
 477     $groupDAO = \CRM_Core_DAO::executeQuery($sql);
 478     if (
 479       $groupDAO->fetch() &&
 480       !empty($groupDAO->saved_search_id)
 481     ) {
 482       return TRUE;
 483     }
 484     return FALSE;
 485   }
 486
 487   /**
 488    * @param string $dateField
 489    * @return \CRM_Utils_SQL_Select
 490    */
 491   protected function prepareRepetitionEndFilter($dateField) {
 492     $repeatEventDateExpr = ($this->actionSchedule->end_action == 'before' ? 'DATE_SUB' : 'DATE_ADD')
 493       . "({$dateField}, INTERVAL {$this->actionSchedule->end_frequency_interval} {$this->actionSchedule->end_frequency_unit})";
 494
 495     return \CRM_Utils_SQL_Select::fragment()
 496       ->where("@casNow <= !repetitionEndDate")
 497       ->param(array(
 498         '!repetitionEndDate' => $repeatEventDateExpr,
 499       ));
 500   }
 501
 502   /**
 503    * @param string $contactIdField
 504    * @return \CRM_Utils_SQL_Select|null
 505    */
 506   protected function prepareAddlFilter($contactIdField) {
 507     $contactAddlFilter = NULL;
 508     if ($this->actionSchedule->limit_to !== NULL && !$this->actionSchedule->limit_to /*0*/) {
 509       $contactAddlFilter = $this->prepareContactFilter($contactIdField);
 510     }
 511     return $contactAddlFilter;
 512   }
 513
 514   /**
 515    * Generate a query fragment like for populating
 516    * action logs, e.g.
 517    *
 518    * "SELECT contact_id, entity_id, entity_table, action schedule_id"
 519    *
 520    * @param string $phase
 521    * @param \CRM_Utils_SQL_Select $query
 522    * @return \CRM_Utils_SQL_Select
 523    * @throws \CRM_Core_Exception
 524    */
 525   protected function selectActionLogFields($phase, $query) {
 526     switch ($phase) {
 527       case self::PHASE_RELATION_FIRST:
 528       case self::PHASE_RELATION_REPEAT:
 529         $fragment = \CRM_Utils_SQL_Select::fragment();
 530         // CRM-15376: We are not tracking the reference date for 'repeated' schedule reminders.
 531         if (!empty($query['casUseReferenceDate'])) {
 532           $fragment->select($query['casDateField']);
 533         }
 534         $fragment->select(
 535           array(
 536             "!casContactIdField as contact_id",
 537             "!casEntityIdField as entity_id",
 538             "@casMappingEntity as entity_table",
 539             "#casActionScheduleId as action_schedule_id",
 540           )
 541         );
 542         break;
 543
 544       case self::PHASE_ADDITION_FIRST:
 545       case self::PHASE_ADDITION_REPEAT:
 546         $fragment = \CRM_Utils_SQL_Select::fragment();
 547         $fragment->select(
 548           array(
 549             "c.id as contact_id",
 550             "c.id as entity_id",
 551             "'civicrm_contact' as entity_table",
 552             "#casActionScheduleId as action_schedule_id",
 553           )
 554         );
 555         break;
 556
 557       default:
 558         throw new \CRM_Core_Exception("Unrecognized phase: $phase");
 559     }
 560     return $fragment;
 561   }
 562
 563   /**
 564    * Generate a query fragment like for populating
 565    * action logs, e.g.
 566    *
 567    * "INSERT INTO civicrm_action_log (...) SELECT (...)"
 568    *
 569    * @param string $phase
 570    * @param \CRM_Utils_SQL_Select $query
 571    * @return \CRM_Utils_SQL_Select
 572    * @throws \CRM_Core_Exception
 573    */
 574   protected function selectIntoActionLog($phase, $query) {
 575     $actionLogColumns = array(
 576       "contact_id",
 577       "entity_id",
 578       "entity_table",
 579       "action_schedule_id",
 580     );
 581     if ($phase === self::PHASE_RELATION_FIRST || $phase === self::PHASE_RELATION_REPEAT) {
 582       if (!empty($query['casUseReferenceDate'])) {
 583         array_unshift($actionLogColumns, 'reference_date');
 584       }
 585     }
 586
 587     return $this->selectActionLogFields($phase, $query)
 588       ->insertInto('civicrm_action_log', $actionLogColumns);
 589   }
 590
 591   /**
 592    * Add a JOIN clause like "INNER JOIN civicrm_action_log reminder ON...".
 593    *
 594    * @param string $joinType
 595    *   Join type (eg INNER JOIN, LEFT JOIN).
 596    * @param string $for
 597    *    Ex: 'rel', 'addl'.
 598    * @param \CRM_Utils_SQL_Select $query
 599    * @return \CRM_Utils_SQL_Select
 600    * @throws \CRM_Core_Exception
 601    */
 602   protected function joinReminder($joinType, $for, $query) {
 603     switch ($for) {
 604       case 'rel':
 605         $contactIdField = $query['casContactIdField'];
 606         $entityName = $this->mapping->getEntity();
 607         $entityIdField = $query['casEntityIdField'];
 608         break;
 609
 610       case 'addl':
 611         $contactIdField = 'c.id';
 612         $entityName = 'civicrm_contact';
 613         $entityIdField = 'c.id';
 614         break;
 615
 616       default:
 617         throw new \CRM_Core_Exception("Unrecognized 'for': $for");
 618     }
 619
 620     $joinClause = "civicrm_action_log reminder ON reminder.contact_id = {$contactIdField} AND
 621 reminder.entity_id          = {$entityIdField} AND
 622 reminder.entity_table       = '{$entityName}' AND
 623 reminder.action_schedule_id = {$this->actionSchedule->id}";
 624
 625     // Why do we only include anniversary clause for 'rel' queries?
 626     if ($for === 'rel' && !empty($query['casAnniversaryMode'])) {
 627       // only consider reminders less than 11 months ago
 628       $joinClause .= " AND reminder.action_date_time > DATE_SUB(!casNow, INTERVAL 11 MONTH)";
 629     }
 630
 631     return \CRM_Utils_SQL_Select::fragment()->join("reminder", "$joinType $joinClause");
 632   }
 633
 634 }