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