| 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 | * ``` |
| 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 | * ``` |
| 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->mapping->sendToAdditional($this->actionSchedule->entity_value)) { |
| 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') && $this->mapping->sendToAdditional($this->actionSchedule->entity_value)) { |
| 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 | } |