Commit | Line | Data |
---|---|---|
c09bacfd | 1 | <?php |
546a1ecc TO |
2 | /* |
3 | +--------------------------------------------------------------------+ | |
3435af9a | 4 | | CiviCRM version 4.7 | |
546a1ecc | 5 | +--------------------------------------------------------------------+ |
fa938177 | 6 | | Copyright CiviCRM LLC (c) 2004-2016 | |
546a1ecc TO |
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 | 180 | |
c09bacfd TO |
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 | |
f8da3b93 | 185 | $referenceReminderIDs = array(); |
186 | $referenceDate = NULL; | |
c09bacfd | 187 | if (!empty($query['casUseReferenceDate'])) { |
f8da3b93 | 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 | |
c09bacfd | 219 | $referenceQuery = $query->copy() |
546a1ecc | 220 | ->merge($this->selectIntoActionLog(self::PHASE_RELATION_FIRST, $query)) |
c09bacfd | 221 | ->merge($this->joinReminder('LEFT JOIN', 'rel', $query)) |
f8da3b93 | 222 | ->where("reminder.id = !reminderID") |
c09bacfd | 223 | ->where($startDateClauses) |
f8da3b93 | 224 | ->param('reminderID', $referenceReminderIDs[0]) |
c09bacfd TO |
225 | ->strict() |
226 | ->toSQL(); | |
227 | \CRM_Core_DAO::executeQuery($referenceQuery); | |
f8da3b93 | 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); | |
c09bacfd TO |
232 | } |
233 | } | |
234 | ||
235 | /** | |
546a1ecc TO |
236 | * Generate action_log's for new, first-time alerts to additional contacts. |
237 | * | |
c09bacfd TO |
238 | * @throws \Exception |
239 | */ | |
240 | protected function buildAddlFirstPass() { | |
546a1ecc | 241 | $query = $this->prepareQuery(self::PHASE_ADDITION_FIRST); |
c09bacfd TO |
242 | |
243 | $insertAdditionalSql = \CRM_Utils_SQL_Select::from("civicrm_contact c") | |
efc40454 | 244 | ->merge($query, array('params')) |
546a1ecc | 245 | ->merge($this->selectIntoActionLog(self::PHASE_ADDITION_FIRST, $query)) |
c09bacfd | 246 | ->merge($this->joinReminder('LEFT JOIN', 'addl', $query)) |
a56f6b25 | 247 | ->where('reminder.id IS NULL') |
c09bacfd TO |
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 | |
9e1bf145 | 252 | FROM civicrm_action_log rem INNER JOIN {$this->mapping->getEntity()} e ON rem.entity_id = e.id |
c09bacfd | 253 | WHERE rem.action_schedule_id = {$this->actionSchedule->id} |
9e1bf145 | 254 | AND rem.entity_table = '{$this->mapping->getEntity()}' |
c09bacfd TO |
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 | /** | |
546a1ecc TO |
264 | * Generate action_log's for repeated, follow-up alerts to related contacts. |
265 | * | |
c09bacfd TO |
266 | * @throws \CRM_Core_Exception |
267 | * @throws \Exception | |
268 | */ | |
269 | protected function buildRelRepeatPass() { | |
546a1ecc | 270 | $query = $this->prepareQuery(self::PHASE_RELATION_REPEAT); |
efc40454 | 271 | $startDateClauses = $this->prepareStartDateClauses(); |
c09bacfd TO |
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)) | |
546a1ecc | 279 | ->merge($this->selectActionLogFields(self::PHASE_RELATION_REPEAT, $query)) |
c09bacfd TO |
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") | |
4052239b | 284 | // @todo replace use of timestampdiff with a direct comparison as TIMESTAMPDIFF cannot use an index. |
281905e6 | 285 | ->having("TIMESTAMPDIFF(HOUR, latest_log_time, CAST(!casNow AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL !casRepetitionInterval))") |
c09bacfd | 286 | ->param(array( |
281905e6 | 287 | 'casRepetitionInterval' => $this->parseRepetitionInterval(), |
c09bacfd TO |
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 | } | |
546a1ecc | 305 | |
c09bacfd | 306 | /** |
546a1ecc TO |
307 | * Generate action_log's for repeated, follow-up alerts to additional contacts. |
308 | * | |
c09bacfd TO |
309 | * @throws \CRM_Core_Exception |
310 | * @throws \Exception | |
311 | */ | |
312 | protected function buildAddlRepeatPass() { | |
546a1ecc | 313 | $query = $this->prepareQuery(self::PHASE_ADDITION_REPEAT); |
c09bacfd TO |
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') | |
546a1ecc | 326 | ->merge($this->selectActionLogFields(self::PHASE_ADDITION_REPEAT, $query)) |
c09bacfd TO |
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") | |
4052239b | 332 | // @todo replace use of timestampdiff with a direct comparison as TIMESTAMPDIFF cannot use an index. |
281905e6 | 333 | ->having("TIMESTAMPDIFF(HOUR, latest_log_time, CAST(!casNow AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL !casRepetitionInterval)") |
c09bacfd | 334 | ->param(array( |
281905e6 | 335 | 'casRepetitionInterval' => $this->parseRepetitionInterval(), |
c09bacfd TO |
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) { | |
efc40454 | 361 | $defaultParams = array( |
c09bacfd | 362 | 'casActionScheduleId' => $this->actionSchedule->id, |
9e1bf145 TO |
363 | 'casMappingId' => $this->mapping->getId(), |
364 | 'casMappingEntity' => $this->mapping->getEntity(), | |
546a1ecc | 365 | 'casNow' => $this->now, |
efc40454 TO |
366 | ); |
367 | ||
368 | /** @var \CRM_Utils_SQL_Select $query */ | |
369 | $query = $this->mapping->createQuery($this->actionSchedule, $phase, $defaultParams); | |
c09bacfd TO |
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 | /** | |
54957108 | 388 | * Parse repetition interval. |
389 | * | |
c09bacfd TO |
390 | * @return int|string |
391 | */ | |
281905e6 | 392 | protected function parseRepetitionInterval() { |
c09bacfd TO |
393 | $actionSchedule = $this->actionSchedule; |
394 | if ($actionSchedule->repetition_frequency_unit == 'day') { | |
281905e6 | 395 | $interval = "{$actionSchedule->repetition_frequency_interval} DAY"; |
c09bacfd TO |
396 | } |
397 | elseif ($actionSchedule->repetition_frequency_unit == 'week') { | |
281905e6 | 398 | $interval = "{$actionSchedule->repetition_frequency_interval} WEEK"; |
c09bacfd TO |
399 | } |
400 | elseif ($actionSchedule->repetition_frequency_unit == 'month') { | |
281905e6 | 401 | $interval = "{$actionSchedule->repetition_frequency_interval} MONTH"; |
c09bacfd TO |
402 | } |
403 | elseif ($actionSchedule->repetition_frequency_unit == 'year') { | |
281905e6 | 404 | $interval = "{$actionSchedule->repetition_frequency_interval} YEAR"; |
c09bacfd TO |
405 | } |
406 | else { | |
281905e6 | 407 | $interval = "{$actionSchedule->repetition_frequency_interval} HOUR"; |
c09bacfd | 408 | } |
281905e6 | 409 | return $interval; |
c09bacfd TO |
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 | /** | |
54957108 | 444 | * Prepare language filter. |
445 | * | |
446 | * @param string $contactTableAlias | |
c09bacfd TO |
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 | /** | |
c09bacfd TO |
467 | * @return array |
468 | */ | |
efc40454 | 469 | protected function prepareStartDateClauses() { |
c09bacfd | 470 | $actionSchedule = $this->actionSchedule; |
c09bacfd TO |
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'); | |
efc40454 TO |
475 | $date = $operator . "(!casDateField, INTERVAL {$actionSchedule->start_action_offset} {$actionSchedule->start_action_unit})"; |
476 | $startDateClauses[] = "'!casNow' >= {$date}"; | |
c09bacfd | 477 | // This is weird. Waddupwidat? |
efc40454 TO |
478 | if ($this->mapping->getEntity() == 'civicrm_participant') { |
479 | $startDateClauses[] = $operator . "(!casNow, INTERVAL 1 DAY ) {$op} " . '!casDateField'; | |
c09bacfd TO |
480 | } |
481 | else { | |
efc40454 | 482 | $startDateClauses[] = "DATE_SUB(!casNow, INTERVAL 1 DAY ) <= {$date}"; |
c09bacfd TO |
483 | } |
484 | } | |
485 | elseif ($actionSchedule->absolute_date) { | |
efc40454 | 486 | $startDateClauses[] = "DATEDIFF(DATE('!casNow'),'{$actionSchedule->absolute_date}') = 0"; |
c09bacfd TO |
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() | |
546a1ecc | 525 | ->where("@casNow <= !repetitionEndDate") |
c09bacfd | 526 | ->param(array( |
c09bacfd TO |
527 | '!repetitionEndDate' => $repeatEventDateExpr, |
528 | )); | |
529 | } | |
530 | ||
c09bacfd TO |
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 | ||
c09bacfd TO |
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) { | |
546a1ecc TO |
556 | case self::PHASE_RELATION_FIRST: |
557 | case self::PHASE_RELATION_REPEAT: | |
c09bacfd TO |
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 | ||
546a1ecc TO |
573 | case self::PHASE_ADDITION_FIRST: |
574 | case self::PHASE_ADDITION_REPEAT: | |
c09bacfd TO |
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 | ); | |
546a1ecc | 610 | if ($phase === self::PHASE_RELATION_FIRST || $phase === self::PHASE_RELATION_REPEAT) { |
c09bacfd TO |
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']; | |
9e1bf145 | 635 | $entityName = $this->mapping->getEntity(); |
c09bacfd TO |
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 | |
546a1ecc | 657 | $joinClause .= " AND reminder.action_date_time > DATE_SUB(!casNow, INTERVAL 11 MONTH)"; |
c09bacfd TO |
658 | } |
659 | ||
660 | return \CRM_Utils_SQL_Select::fragment()->join("reminder", "$joinType $joinClause"); | |
661 | } | |
662 | ||
663 | } |