Commit | Line | Data |
---|---|---|
c09bacfd | 1 | <?php |
546a1ecc TO |
2 | /* |
3 | +--------------------------------------------------------------------+ | |
3435af9a | 4 | | CiviCRM version 4.7 | |
546a1ecc TO |
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 | ||
c09bacfd TO |
28 | namespace Civi\ActionSchedule; |
29 | ||
546a1ecc TO |
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') | |
efc40454 | 63 | * ->param($defaultParams) |
546a1ecc TO |
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 | */ | |
c09bacfd TO |
97 | class RecipientBuilder { |
98 | ||
99 | private $now; | |
546a1ecc TO |
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'; | |
c09bacfd TO |
128 | |
129 | /** | |
130 | * @var \CRM_Core_DAO_ActionSchedule | |
131 | */ | |
132 | private $actionSchedule; | |
133 | ||
134 | /** | |
546a1ecc | 135 | * @var MappingInterface |
c09bacfd TO |
136 | */ |
137 | private $mapping; | |
138 | ||
546a1ecc TO |
139 | /** |
140 | * @param $now | |
141 | * @param \CRM_Core_DAO_ActionSchedule $actionSchedule | |
142 | * @param MappingInterface $mapping | |
143 | */ | |
c09bacfd TO |
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() { | |
c09bacfd TO |
156 | $this->buildRelFirstPass(); |
157 | ||
c09bacfd TO |
158 | if ($this->prepareAddlFilter('c.id')) { |
159 | $this->buildAddlFirstPass(); | |
160 | } | |
161 | ||
c09bacfd TO |
162 | if ($this->actionSchedule->is_repeat) { |
163 | $this->buildRelRepeatPass(); | |
164 | } | |
165 | ||
c09bacfd TO |
166 | if ($this->actionSchedule->is_repeat && $this->prepareAddlFilter('c.id')) { |
167 | $this->buildAddlRepeatPass(); | |
168 | } | |
169 | } | |
170 | ||
171 | /** | |
546a1ecc TO |
172 | * Generate action_log's for new, first-time alerts to related contacts. |
173 | * | |
c09bacfd TO |
174 | * @throws \Exception |
175 | */ | |
176 | protected function buildRelFirstPass() { | |
546a1ecc | 177 | $query = $this->prepareQuery(self::PHASE_RELATION_FIRST); |
c09bacfd | 178 | |
efc40454 | 179 | $startDateClauses = $this->prepareStartDateClauses(); |
c09bacfd TO |
180 | |
181 | $firstQuery = $query->copy() | |
546a1ecc | 182 | ->merge($this->selectIntoActionLog(self::PHASE_RELATION_FIRST, $query)) |
c09bacfd TO |
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() | |
546a1ecc | 196 | ->merge($this->selectIntoActionLog(self::PHASE_RELATION_FIRST, $query)) |
c09bacfd TO |
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 | /** | |
546a1ecc TO |
210 | * Generate action_log's for new, first-time alerts to additional contacts. |
211 | * | |
c09bacfd TO |
212 | * @throws \Exception |
213 | */ | |
214 | protected function buildAddlFirstPass() { | |
546a1ecc | 215 | $query = $this->prepareQuery(self::PHASE_ADDITION_FIRST); |
c09bacfd TO |
216 | |
217 | $insertAdditionalSql = \CRM_Utils_SQL_Select::from("civicrm_contact c") | |
efc40454 | 218 | ->merge($query, array('params')) |
546a1ecc | 219 | ->merge($this->selectIntoActionLog(self::PHASE_ADDITION_FIRST, $query)) |
c09bacfd TO |
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 | |
9e1bf145 | 225 | FROM civicrm_action_log rem INNER JOIN {$this->mapping->getEntity()} e ON rem.entity_id = e.id |
c09bacfd | 226 | WHERE rem.action_schedule_id = {$this->actionSchedule->id} |
9e1bf145 | 227 | AND rem.entity_table = '{$this->mapping->getEntity()}' |
c09bacfd TO |
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 | /** | |
546a1ecc TO |
237 | * Generate action_log's for repeated, follow-up alerts to related contacts. |
238 | * | |
c09bacfd TO |
239 | * @throws \CRM_Core_Exception |
240 | * @throws \Exception | |
241 | */ | |
242 | protected function buildRelRepeatPass() { | |
546a1ecc | 243 | $query = $this->prepareQuery(self::PHASE_RELATION_REPEAT); |
efc40454 | 244 | $startDateClauses = $this->prepareStartDateClauses(); |
c09bacfd TO |
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)) | |
546a1ecc | 252 | ->merge($this->selectActionLogFields(self::PHASE_RELATION_REPEAT, $query)) |
c09bacfd TO |
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") | |
281905e6 | 257 | ->having("TIMESTAMPDIFF(HOUR, latest_log_time, CAST(!casNow AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL !casRepetitionInterval))") |
c09bacfd | 258 | ->param(array( |
281905e6 | 259 | 'casRepetitionInterval' => $this->parseRepetitionInterval(), |
c09bacfd TO |
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 | } | |
546a1ecc | 277 | |
c09bacfd | 278 | /** |
546a1ecc TO |
279 | * Generate action_log's for repeated, follow-up alerts to additional contacts. |
280 | * | |
c09bacfd TO |
281 | * @throws \CRM_Core_Exception |
282 | * @throws \Exception | |
283 | */ | |
284 | protected function buildAddlRepeatPass() { | |
546a1ecc | 285 | $query = $this->prepareQuery(self::PHASE_ADDITION_REPEAT); |
c09bacfd TO |
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') | |
546a1ecc | 298 | ->merge($this->selectActionLogFields(self::PHASE_ADDITION_REPEAT, $query)) |
c09bacfd TO |
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") | |
281905e6 | 304 | ->having("TIMESTAMPDIFF(HOUR, latest_log_time, CAST(!casNow AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL !casRepetitionInterval)") |
c09bacfd | 305 | ->param(array( |
281905e6 | 306 | 'casRepetitionInterval' => $this->parseRepetitionInterval(), |
c09bacfd TO |
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) { | |
efc40454 | 332 | $defaultParams = array( |
c09bacfd | 333 | 'casActionScheduleId' => $this->actionSchedule->id, |
9e1bf145 TO |
334 | 'casMappingId' => $this->mapping->getId(), |
335 | 'casMappingEntity' => $this->mapping->getEntity(), | |
546a1ecc | 336 | 'casNow' => $this->now, |
efc40454 TO |
337 | ); |
338 | ||
339 | /** @var \CRM_Utils_SQL_Select $query */ | |
340 | $query = $this->mapping->createQuery($this->actionSchedule, $phase, $defaultParams); | |
c09bacfd TO |
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 | /** | |
54957108 | 359 | * Parse repetition interval. |
360 | * | |
c09bacfd TO |
361 | * @return int|string |
362 | */ | |
281905e6 | 363 | protected function parseRepetitionInterval() { |
c09bacfd TO |
364 | $actionSchedule = $this->actionSchedule; |
365 | if ($actionSchedule->repetition_frequency_unit == 'day') { | |
281905e6 | 366 | $interval = "{$actionSchedule->repetition_frequency_interval} DAY"; |
c09bacfd TO |
367 | } |
368 | elseif ($actionSchedule->repetition_frequency_unit == 'week') { | |
281905e6 | 369 | $interval = "{$actionSchedule->repetition_frequency_interval} WEEK"; |
c09bacfd TO |
370 | } |
371 | elseif ($actionSchedule->repetition_frequency_unit == 'month') { | |
281905e6 | 372 | $interval = "{$actionSchedule->repetition_frequency_interval} MONTH"; |
c09bacfd TO |
373 | } |
374 | elseif ($actionSchedule->repetition_frequency_unit == 'year') { | |
281905e6 | 375 | $interval = "{$actionSchedule->repetition_frequency_interval} YEAR"; |
c09bacfd TO |
376 | } |
377 | else { | |
281905e6 | 378 | $interval = "{$actionSchedule->repetition_frequency_interval} HOUR"; |
c09bacfd | 379 | } |
281905e6 | 380 | return $interval; |
c09bacfd TO |
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 | /** | |
54957108 | 415 | * Prepare language filter. |
416 | * | |
417 | * @param string $contactTableAlias | |
c09bacfd TO |
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 | /** | |
c09bacfd TO |
438 | * @return array |
439 | */ | |
efc40454 | 440 | protected function prepareStartDateClauses() { |
c09bacfd | 441 | $actionSchedule = $this->actionSchedule; |
c09bacfd TO |
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'); | |
efc40454 TO |
446 | $date = $operator . "(!casDateField, INTERVAL {$actionSchedule->start_action_offset} {$actionSchedule->start_action_unit})"; |
447 | $startDateClauses[] = "'!casNow' >= {$date}"; | |
c09bacfd | 448 | // This is weird. Waddupwidat? |
efc40454 TO |
449 | if ($this->mapping->getEntity() == 'civicrm_participant') { |
450 | $startDateClauses[] = $operator . "(!casNow, INTERVAL 1 DAY ) {$op} " . '!casDateField'; | |
c09bacfd TO |
451 | } |
452 | else { | |
efc40454 | 453 | $startDateClauses[] = "DATE_SUB(!casNow, INTERVAL 1 DAY ) <= {$date}"; |
c09bacfd TO |
454 | } |
455 | } | |
456 | elseif ($actionSchedule->absolute_date) { | |
efc40454 | 457 | $startDateClauses[] = "DATEDIFF(DATE('!casNow'),'{$actionSchedule->absolute_date}') = 0"; |
c09bacfd TO |
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() | |
546a1ecc | 496 | ->where("@casNow <= !repetitionEndDate") |
c09bacfd | 497 | ->param(array( |
c09bacfd TO |
498 | '!repetitionEndDate' => $repeatEventDateExpr, |
499 | )); | |
500 | } | |
501 | ||
c09bacfd TO |
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 | ||
c09bacfd TO |
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) { | |
546a1ecc TO |
527 | case self::PHASE_RELATION_FIRST: |
528 | case self::PHASE_RELATION_REPEAT: | |
c09bacfd TO |
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 | ||
546a1ecc TO |
544 | case self::PHASE_ADDITION_FIRST: |
545 | case self::PHASE_ADDITION_REPEAT: | |
c09bacfd TO |
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 | ); | |
546a1ecc | 581 | if ($phase === self::PHASE_RELATION_FIRST || $phase === self::PHASE_RELATION_REPEAT) { |
c09bacfd TO |
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']; | |
9e1bf145 | 606 | $entityName = $this->mapping->getEntity(); |
c09bacfd TO |
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 | |
546a1ecc | 628 | $joinClause .= " AND reminder.action_date_time > DATE_SUB(!casNow, INTERVAL 11 MONTH)"; |
c09bacfd TO |
629 | } |
630 | ||
631 | return \CRM_Utils_SQL_Select::fragment()->join("reminder", "$joinType $joinClause"); | |
632 | } | |
633 | ||
634 | } |