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