3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2015 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2015
37 * This class contains functions for managing Scheduled Reminders
39 class CRM_Core_BAO_ActionSchedule
extends CRM_Core_DAO_ActionSchedule
{
42 * @param array $filters
43 * Filter by property (e.g. 'id', 'entity_value').
45 * Array(scalar $id => Mapping $mapping).
47 public static function getMappings($filters = NULL) {
48 static $_action_mapping;
50 if ($_action_mapping === NULL) {
51 $event = \Civi\Core\Container
::singleton()->get('dispatcher')
52 ->dispatch(\Civi\ActionSchedule\Events
::MAPPINGS
,
53 new \Civi\ActionSchedule\Event\
MappingRegisterEvent());
54 $_action_mapping = $event->getMappings();
57 if ($filters === NULL) {
58 return $_action_mapping;
62 foreach ($_action_mapping as $mappingId => $mapping) {
64 foreach ($filters as $filterField => $filterValue) {
65 if ($mapping->{$filterField} != $filterValue) {
70 $result[$mappingId] = $mapping;
77 * Retrieve list of selections/drop downs for Scheduled Reminder form
83 * associated array of all the drop downs in the form
85 public static function getSelection($id = NULL) {
86 $mappings = CRM_Core_BAO_ActionSchedule
::getMappings();
88 $entityValueLabels = $entityStatusLabels = $dateFieldLabels = array();
89 $entityRecipientLabels = $entityRecipientNames = array();
95 foreach ($mappings as $mapping) {
96 /** @var \Civi\ActionSchedule\Mapping $mapping */
98 $entityValueLabels[$mapping->id
] = $mapping->getValueLabels();
99 // Not sure why: everything *except* contact-dates have a $valueLabel.
100 if ($mapping->id
!== CRM_Core_ActionScheduleTmp
::CONTACT_MAPPING_ID
) {
101 $valueLabel = array('- ' . strtolower($mapping->entity_value_label
) . ' -');
102 $entityValueLabels[$mapping->id
] = $valueLabel +
$entityValueLabels[$mapping->id
];
105 if ($mapping->id
== $id) {
106 $dateFieldLabels = $mapping->getDateFields();
107 $entityRecipientLabels = array($mapping->entity_recipient
=> $mapping->getRecipientTypes());
108 $entityRecipientNames = array_combine(array_keys($entityRecipientLabels[$mapping->entity_recipient
]), array_keys($entityRecipientLabels[$mapping->entity_recipient
]));
111 $statusLabel = array('- ' . strtolower($mapping->entity_status_label
) . ' -');
112 $entityStatusLabels[$mapping->id
] = $entityValueLabels[$mapping->id
];
113 foreach ($entityStatusLabels[$mapping->id
] as $kkey => & $vval) {
114 $vval = $statusLabel +
$mapping->getStatusLabels($kkey);
119 'sel1' => CRM_Utils_Array
::collect('entity_label', $mappings),
120 'sel2' => $entityValueLabels,
121 'sel3' => $entityStatusLabels,
122 'sel4' => $dateFieldLabels,
123 'sel5' => $entityRecipientLabels,
124 'entityMapping' => CRM_Utils_Array
::collect('entity', $mappings),
125 'recipientMapping' => $entityRecipientNames,
130 * @param int $mappingId
131 * @param int $isLimit
135 public static function getSelection1($mappingId = NULL, $isLimit = NULL) {
136 $mappings = CRM_Core_BAO_ActionSchedule
::getMappings(array(
139 $dateFieldLabels = $entityRecipientLabels = array();
141 foreach ($mappings as $mapping) {
142 /** @var \Civi\ActionSchedule\Mapping $mapping */
143 $dateFieldLabels = $mapping->getDateFields();
144 $entityRecipientLabels = $mapping->getRecipientTypes(!$isLimit);
148 'sel4' => $dateFieldLabels,
149 'sel5' => $entityRecipientLabels,
150 'recipientMapping' => array_combine(array_keys($entityRecipientLabels), array_keys($entityRecipientLabels)),
155 * Retrieve list of Scheduled Reminders.
157 * @param bool $namesOnly
158 * Return simple list of names.
160 * @param \Civi\ActionSchedule\Mapping|NULL $filterMapping
161 * Filter by the schedule's mapping type.
162 * @param int $filterValue
163 * Filter by the schedule's entity_value.
166 * (reference) reminder list
168 public static function &getList($namesOnly = FALSE, $filterMapping = NULL, $filterValue = NULL) {
174 cas.entity_value as entityValueIds,
175 cas.entity_status as entityStatusIds,
176 cas.start_action_date as entityDate,
177 cas.start_action_offset,
178 cas.start_action_unit,
179 cas.start_action_condition,
184 FROM civicrm_action_schedule cas
186 $queryParams = array();
187 $where = " WHERE 1 ";
188 if ($filterMapping and $filterValue) {
189 $where .= " AND cas.entity_value = %1 AND cas.mapping_id = %2";
190 $queryParams[1] = array($filterValue, 'Integer');
191 $queryParams[2] = array($filterMapping->id
, 'Integer');
193 $where .= " AND cas.used_for IS NULL";
195 $dao = CRM_Core_DAO
::executeQuery($query, $queryParams);
196 while ($dao->fetch()) {
197 /** @var Civi\ActionSchedule\Mapping $filterMapping */
198 $filterMapping = CRM_Utils_Array
::first(self
::getMappings(array(
199 'id' => $dao->mapping_id
,
201 $list[$dao->id
]['id'] = $dao->id
;
202 $list[$dao->id
]['title'] = $dao->title
;
203 $list[$dao->id
]['start_action_offset'] = $dao->start_action_offset
;
204 $list[$dao->id
]['start_action_unit'] = $dao->start_action_unit
;
205 $list[$dao->id
]['start_action_condition'] = $dao->start_action_condition
;
206 $list[$dao->id
]['entityDate'] = ucwords(str_replace('_', ' ', $dao->entityDate
));
207 $list[$dao->id
]['absolute_date'] = $dao->absolute_date
;
208 $list[$dao->id
]['entity'] = $filterMapping->entity_label
;
209 $list[$dao->id
]['value'] = implode(', ', CRM_Utils_Array
::subset(
210 $filterMapping->getValueLabels(),
211 explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $dao->entityValueIds
)
213 $list[$dao->id
]['status'] = implode(', ', CRM_Utils_Array
::subset(
214 $filterMapping->getStatusLabels($dao->entityValueIds
),
215 explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $dao->entityStatusIds
)
217 $list[$dao->id
]['is_repeat'] = $dao->is_repeat
;
218 $list[$dao->id
]['is_active'] = $dao->is_active
;
225 * Add the schedules reminders in the db.
227 * @param array $params
228 * (reference ) an assoc array of name/value pairs.
232 * @return CRM_Core_DAO_ActionSchedule
234 public static function add(&$params, $ids = array()) {
235 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
236 $actionSchedule->copyValues($params);
238 return $actionSchedule->save();
242 * Retrieve DB object based on input parameters.
244 * It also stores all the retrieved values in the default array.
246 * @param array $params
247 * (reference ) an assoc array of name/value pairs.
248 * @param array $values
249 * (reference ) an assoc array to hold the flattened values.
251 * @return CRM_Core_DAO_ActionSchedule|null
252 * object on success, null otherwise
254 public static function retrieve(&$params, &$values) {
255 if (empty($params)) {
258 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
260 $actionSchedule->copyValues($params);
262 if ($actionSchedule->find(TRUE)) {
263 $ids['actionSchedule'] = $actionSchedule->id
;
265 CRM_Core_DAO
::storeValues($actionSchedule, $values);
267 return $actionSchedule;
276 * ID of the Reminder to be deleted.
279 public static function del($id) {
281 $dao = new CRM_Core_DAO_ActionSchedule();
283 if ($dao->find(TRUE)) {
288 CRM_Core_Error
::fatal(ts('Invalid value passed to delete function.'));
292 * Update the is_active flag in the db.
295 * Id of the database record.
296 * @param bool $is_active
297 * Value we want to set the is_active field.
300 * DAO object on success, null otherwise
302 public static function setIsActive($id, $is_active) {
303 return CRM_Core_DAO
::setFieldValue('CRM_Core_DAO_ActionSchedule', $id, 'is_active', $is_active);
307 * @param int $mappingID
310 * @throws CRM_Core_Exception
312 public static function sendMailings($mappingID, $now) {
313 $mapping = CRM_Utils_Array
::first(self
::getMappings(array(
317 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
318 $actionSchedule->mapping_id
= $mappingID;
319 $actionSchedule->is_active
= 1;
320 $actionSchedule->find(FALSE);
322 while ($actionSchedule->fetch()) {
323 $query = CRM_Core_BAO_ActionSchedule
::prepareMailingQuery($mapping, $actionSchedule);
324 $dao = CRM_Core_DAO
::executeQuery($query,
325 array(1 => array($actionSchedule->id
, 'Integer'))
328 $multilingual = CRM_Core_I18n
::isMultilingual();
329 while ($dao->fetch()) {
330 // switch language if necessary
332 $preferred_language = CRM_Core_DAO
::getFieldValue('CRM_Contact_DAO_Contact', $dao->contactID
, 'preferred_language');
333 CRM_Core_BAO_ActionSchedule
::setCommunicationLanguage($actionSchedule->communication_language
, $preferred_language);
338 $tokenProcessor = self
::createTokenProcessor($actionSchedule, $mapping);
339 $tokenProcessor->addRow()
340 ->context('contactId', $dao->contactID
)
341 ->context('actionSearchResult', (object) $dao->toArray());
342 foreach ($tokenProcessor->evaluate()->getRows() as $tokenRow) {
343 if ($actionSchedule->mode
== 'SMS' or $actionSchedule->mode
== 'User_Preference') {
344 CRM_Utils_Array
::extend($errors, self
::sendReminderSms($tokenRow, $actionSchedule, $dao->contactID
));
347 if ($actionSchedule->mode
== 'Email' or $actionSchedule->mode
== 'User_Preference') {
348 CRM_Utils_Array
::extend($errors, self
::sendReminderEmail($tokenRow, $actionSchedule, $dao->contactID
));
352 catch (\Civi\Token\TokenException
$e) {
353 $errors['token_exception'] = $e->getMessage();
356 // update action log record
358 'id' => $dao->reminderID
,
359 'is_error' => !empty($errors),
360 'message' => empty($errors) ?
"null" : implode(' ', $errors),
361 'action_date_time' => $now,
363 CRM_Core_BAO_ActionLog
::create($logParams);
365 // insert activity log record if needed
366 if ($actionSchedule->record_activity
&& empty($errors)) {
367 $caseID = empty($dao->case_id
) ?
NULL : $dao->case_id
;
368 CRM_Core_BAO_ActionSchedule
::createMailingActivity($actionSchedule, $mapping, $dao->contactID
, $dao->entityID
, $caseID);
377 * @param int $mappingID
379 * @param array $params
381 * @throws API_Exception
383 public static function buildRecipientContacts($mappingID, $now, $params = array()) {
384 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
385 $actionSchedule->mapping_id
= $mappingID;
386 $actionSchedule->is_active
= 1;
387 if (!empty($params)) {
388 _civicrm_api3_dao_set_filter($actionSchedule, $params, FALSE);
390 $actionSchedule->find();
392 while ($actionSchedule->fetch()) {
393 /** @var \Civi\ActionSchedule\Mapping $mapping */
394 $mapping = CRM_Utils_Array
::first(self
::getMappings(array(
398 // note: $where - this filtering applies for both
399 // 'limit to' and 'addition to' options
400 // $limitWhere - this filtering applies only for
402 $select = $join = $where = $limitWhere = array();
403 $selectColumns = "contact_id, entity_id, entity_table, action_schedule_id";
404 $limitTo = $actionSchedule->limit_to
;
405 $value = explode(CRM_Core_DAO
::VALUE_SEPARATOR
,
406 trim($actionSchedule->entity_value
, CRM_Core_DAO
::VALUE_SEPARATOR
)
408 $value = implode(',', $value);
410 $status = explode(CRM_Core_DAO
::VALUE_SEPARATOR
,
411 trim($actionSchedule->entity_status
, CRM_Core_DAO
::VALUE_SEPARATOR
)
413 $status = implode(',', $status);
415 $anniversary = FALSE;
417 $from = "{$mapping->entity} e";
419 if ($mapping->entity
== 'civicrm_activity') {
420 $contactField = 'r.contact_id';
421 $table = 'civicrm_activity e';
422 $activityContacts = CRM_Core_OptionGroup
::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
423 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
424 $assigneeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
425 $targetID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
427 if (!is_null($limitTo)) {
429 // including the activity target contacts if 'in addition' is defined
430 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$targetID}";
433 switch (CRM_Utils_Array
::value($actionSchedule->recipient
, $mapping->getRecipientOptions())) {
434 case 'Activity Assignees':
435 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$assigneeID}";
438 case 'Activity Source':
439 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$sourceID}";
443 case 'Activity Targets':
444 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$targetID}";
449 // build where clause
450 if (!empty($value)) {
451 $where[] = "e.activity_type_id IN ({$value})";
454 $where[] = "e.activity_type_id IS NULL";
456 if (!empty($status)) {
457 $where[] = "e.status_id IN ({$status})";
459 $where[] = ' e.is_current_revision = 1 ';
460 $where[] = ' e.is_deleted = 0 ';
462 $dateField = 'e.activity_date_time';
465 if ($mapping->entity
== 'civicrm_participant') {
466 $table = 'civicrm_event r';
467 $contactField = 'e.contact_id';
468 $join[] = 'INNER JOIN civicrm_event r ON e.event_id = r.id';
469 if ($actionSchedule->recipient_listing
&& $limitTo) {
470 $rList = explode(CRM_Core_DAO
::VALUE_SEPARATOR
,
471 trim($actionSchedule->recipient_listing
, CRM_Core_DAO
::VALUE_SEPARATOR
)
473 $rList = implode(',', $rList);
475 switch (CRM_Utils_Array
::value($actionSchedule->recipient
, $mapping->getRecipientOptions())) {
476 case 'participant_role':
477 $where[] = "e.role_id IN ({$rList})";
485 // build where clause
486 if (!empty($value)) {
487 $where[] = ($mapping->id
== CRM_Core_ActionScheduleTmp
::EVENT_TYPE_MAPPING_ID
) ?
"r.event_type_id IN ({$value})" : "r.id IN ({$value})";
490 $where[] = ($mapping->id
== CRM_Core_ActionScheduleTmp
::EVENT_TYPE_MAPPING_ID
) ?
"r.event_type_id IS NULL" : "r.id IS NULL";
493 // participant status criteria not to be implemented
494 // for additional recipients
495 if (!empty($status)) {
496 $limitWhere[] = "e.status_id IN ({$status})";
499 $where[] = 'r.is_active = 1';
500 $where[] = 'r.is_template = 0';
501 $dateField = str_replace('event_', 'r.', $actionSchedule->start_action_date
);
505 if ($mapping->entity
== 'civicrm_membership') {
506 $contactField = 'e.contact_id';
507 $table = 'civicrm_membership e';
508 // build where clause
510 //auto-renew memberships
511 $where[] = "e.contribution_recur_id IS NOT NULL ";
513 elseif ($status == 1) {
514 $where[] = "e.contribution_recur_id IS NULL ";
517 // build where clause
518 if (!empty($value)) {
519 $where[] = "e.membership_type_id IN ({$value})";
522 $where[] = "e.membership_type_id IS NULL";
525 $where[] = "( e.is_override IS NULL OR e.is_override = 0 )";
526 $dateField = str_replace('membership_', 'e.', $actionSchedule->start_action_date
);
527 $notINClause = CRM_Core_BAO_ActionSchedule
::permissionedRelationships($contactField);
529 $membershipStatus = CRM_Member_PseudoConstant
::membershipStatus(NULL, "(is_current_member = 1 OR name = 'Expired')", 'id');
530 $mStatus = implode(',', $membershipStatus);
531 $where[] = "e.status_id IN ({$mStatus})";
533 // We are not tracking the reference date for 'repeated' schedule reminders,
534 // for further details please check CRM-15376
535 if ($actionSchedule->start_action_date
&& $actionSchedule->is_repeat
== FALSE) {
536 $select[] = $dateField;
537 $selectColumns = "reference_date, " . $selectColumns;
541 if ($mapping->entity
== 'civicrm_contact') {
542 $contactFields = array(
547 if (in_array($value, $contactFields)) {
548 $dateDBField = $value;
549 $table = 'civicrm_contact e';
550 $contactField = 'e.id';
551 $where[] = 'e.is_deleted = 0';
552 $where[] = 'e.is_deceased = 0';
556 $customFieldParams = array('id' => substr($value, 7));
557 $customGroup = $customField = array();
558 CRM_Core_BAO_CustomField
::retrieve($customFieldParams, $customField);
559 $dateDBField = $customField['column_name'];
560 $customGroupParams = array('id' => $customField['custom_group_id'], $customGroup);
561 CRM_Core_BAO_CustomGroup
::retrieve($customGroupParams, $customGroup);
562 $from = $table = "{$customGroup['table_name']} e";
563 $contactField = 'e.entity_id';
564 $where[] = '1'; // possible to have no "where" in this case
567 $status_ = explode(',', $status);
568 if (in_array(2, $status_)) {
570 $dateField = 'DATE_ADD(e.' . $dateDBField . ', INTERVAL ROUND(DATEDIFF(DATE(' . $now . '), e.' . $dateDBField . ') / 365) YEAR)';
575 $dateField = 'e.' . $dateDBField;
579 // CRM-13577 Introduce Smart Groups Handling
580 if ($actionSchedule->group_id
) {
582 // Need to check if its a smart group or not
583 // Then decide which table to join onto the query
584 $group = CRM_Contact_DAO_Group
::getTableName();
586 // Get the group information
588 SELECT $group.id, $group.cache_date, $group.saved_search_id, $group.children
590 WHERE $group.id = {$actionSchedule->group_id}
593 $groupDAO = CRM_Core_DAO
::executeQuery($sql);
594 $isSmartGroup = FALSE;
596 $groupDAO->fetch() &&
597 !empty($groupDAO->saved_search_id
)
599 // Check that the group is in place in the cache and up to date
600 CRM_Contact_BAO_GroupContactCache
::check($actionSchedule->group_id
);
601 // Set smart group flag
602 $isSmartGroup = TRUE;
605 // CRM-13577 End Introduce Smart Groups Handling
608 if ($actionSchedule->group_id
) {
609 // CRM-13577 If smart group then use Cache table
611 $join[] = "INNER JOIN civicrm_group_contact_cache grp ON {$contactField} = grp.contact_id";
612 $where[] = "grp.group_id IN ({$actionSchedule->group_id})";
615 $join[] = "INNER JOIN civicrm_group_contact grp ON {$contactField} = grp.contact_id AND grp.status = 'Added'";
616 $where[] = "grp.group_id IN ({$actionSchedule->group_id})";
619 elseif (!empty($actionSchedule->recipient_manual
)) {
620 $rList = CRM_Utils_Type
::escape($actionSchedule->recipient_manual
, 'String');
621 $where[] = "{$contactField} IN ({$rList})";
624 elseif (!is_null($limitTo)) {
625 $addGroup = $addWhere = '';
626 if ($actionSchedule->group_id
) {
627 // CRM-13577 If smart group then use Cache table
629 $addGroup = " INNER JOIN civicrm_group_contact_cache grp ON c.id = grp.contact_id";
630 $addWhere = " grp.group_id IN ({$actionSchedule->group_id})";
633 $addGroup = " INNER JOIN civicrm_group_contact grp ON c.id = grp.contact_id AND grp.status = 'Added'";
634 $addWhere = " grp.group_id IN ({$actionSchedule->group_id})";
637 if (!empty($actionSchedule->recipient_manual
)) {
638 $rList = CRM_Utils_Type
::escape($actionSchedule->recipient_manual
, 'String');
639 $addWhere = "c.id IN ({$rList})";
643 $select[] = "{$contactField} as contact_id";
644 $select[] = 'e.id as entity_id';
645 $select[] = "'{$mapping->entity}' as entity_table";
646 $select[] = "{$actionSchedule->id} as action_schedule_id";
647 $reminderJoinClause = "civicrm_action_log reminder ON reminder.contact_id = {$contactField} AND
648 reminder.entity_id = e.id AND
649 reminder.entity_table = '{$mapping->entity}' AND
650 reminder.action_schedule_id = %1";
653 // only consider reminders less than 11 months ago
654 $reminderJoinClause .= " AND reminder.action_date_time > DATE_SUB({$now}, INTERVAL 11 MONTH)";
657 if ($table != 'civicrm_contact e') {
658 $join[] = "INNER JOIN civicrm_contact c ON c.id = {$contactField} AND c.is_deleted = 0 AND c.is_deceased = 0 ";
661 $multilingual = CRM_Core_I18n
::isMultilingual();
662 if ($multilingual && !empty($actionSchedule->filter_contact_language
)) {
663 $tableAlias = ($table != 'civicrm_contact e') ?
'c' : 'e';
665 // get language filter for the schedule
666 $filter_contact_language = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $actionSchedule->filter_contact_language
);
668 if (($key = array_search(CRM_Core_I18n
::NONE
, $filter_contact_language)) !== FALSE) {
669 $w .= "{$tableAlias}.preferred_language IS NULL OR {$tableAlias}.preferred_language = '' OR ";
670 unset($filter_contact_language[$key]);
672 if (count($filter_contact_language) > 0) {
673 $w .= "{$tableAlias}.preferred_language IN ('" . implode("','", $filter_contact_language) . "')";
678 if ($actionSchedule->start_action_date
) {
679 $startDateClause = array();
680 $op = ($actionSchedule->start_action_condition
== 'before' ?
'<=' : '>=');
681 $operator = ($actionSchedule->start_action_condition
== 'before' ?
'DATE_SUB' : 'DATE_ADD');
682 $date = $operator . "({$dateField}, INTERVAL {$actionSchedule->start_action_offset} {$actionSchedule->start_action_unit})";
683 $startDateClause[] = "'{$now}' >= {$date}";
684 if ($mapping->entity
== 'civicrm_participant') {
685 $startDateClause[] = $operator . "({$now}, INTERVAL 1 DAY ) {$op} " . $dateField;
688 $startDateClause[] = "DATE_SUB({$now}, INTERVAL 1 DAY ) <= {$date}";
691 $startDate = implode(' AND ', $startDateClause);
693 elseif ($actionSchedule->absolute_date
) {
694 $startDate = "DATEDIFF(DATE('{$now}'),'{$actionSchedule->absolute_date}') = 0";
697 // ( now >= date_built_from_start_time ) OR ( now = absolute_date )
698 $dateClause = "reminder.id IS NULL AND {$startDate}";
700 // start composing query
701 $selectClause = 'SELECT ' . implode(', ', $select);
702 $fromClause = "FROM $from";
703 $joinClause = !empty($join) ?
implode(' ', $join) : '';
704 $whereClause = 'WHERE ' . implode(' AND ', $where);
705 $limitWhereClause = '';
706 if (!empty($limitWhere)) {
707 $limitWhereClause = ' AND ' . implode(' AND ', $limitWhere);
711 INSERT INTO civicrm_action_log ({$selectColumns})
715 LEFT JOIN {$reminderJoinClause}
716 {$whereClause} {$limitWhereClause} AND {$dateClause} {$notINClause}
719 // In some cases reference_date got outdated due to many reason e.g. In Membership renewal end_date got extended
720 // which means reference date mismatches with the end_date where end_date may be used as the start_action_date
721 // criteria for some schedule reminder so in order to send new reminder we INSERT new reminder with new reference_date
722 // value via UNION operation
723 if (strpos($selectColumns, 'reference_date') !== FALSE) {
724 $dateClause = str_replace('reminder.id IS NULL', 'reminder.id IS NOT NULL', $dateClause);
726 INSERT INTO civicrm_action_log ({$selectColumns})
730 LEFT JOIN {$reminderJoinClause}
731 {$whereClause} {$limitWhereClause} {$notINClause} AND {$dateClause} AND
732 reminder.action_date_time IS NOT NULL AND
733 reminder.reference_date IS NOT NULL
734 GROUP BY reminder.id, reminder.reference_date
735 HAVING reminder.id = MAX(reminder.id) AND reminder.reference_date <> {$dateField}
739 CRM_Core_DAO
::executeQuery($query, array(1 => array($actionSchedule->id
, 'Integer')));
741 if (!empty($referenceQuery)) {
742 CRM_Core_DAO
::executeQuery($referenceQuery, array(1 => array($actionSchedule->id
, 'Integer')));
745 $isSendToAdditionalContacts = (!is_null($limitTo) && $limitTo == 0 && (!empty($addGroup) ||
!empty($addWhere))) ?
TRUE : FALSE;
746 if ($isSendToAdditionalContacts) {
747 $contactTable = "civicrm_contact c";
748 $addSelect = "SELECT c.id as contact_id, c.id as entity_id, 'civicrm_contact' as entity_table, {$actionSchedule->id} as action_schedule_id";
749 $additionReminderClause = "civicrm_action_log reminder ON reminder.contact_id = c.id AND
750 reminder.entity_id = c.id AND
751 reminder.entity_table = 'civicrm_contact' AND
752 reminder.action_schedule_id = {$actionSchedule->id}";
753 $addWhereClause = '';
755 $addWhereClause = "AND {$addWhere}";
757 $insertAdditionalSql = "
758 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
760 FROM ({$contactTable})
761 LEFT JOIN {$additionReminderClause}
763 WHERE c.is_deleted = 0 AND c.is_deceased = 0
766 AND reminder.id IS NULL
768 SELECT rem.contact_id
769 FROM civicrm_action_log rem INNER JOIN {$mapping->entity} e ON rem.entity_id = e.id
770 WHERE rem.action_schedule_id = {$actionSchedule->id}
771 AND rem.entity_table = '{$mapping->entity}'
775 CRM_Core_DAO
::executeQuery($insertAdditionalSql);
777 // if repeat is turned ON:
778 if ($actionSchedule->is_repeat
) {
779 $repeatEvent = ($actionSchedule->end_action
== 'before' ?
'DATE_SUB' : 'DATE_ADD') . "({$dateField}, INTERVAL {$actionSchedule->end_frequency_interval} {$actionSchedule->end_frequency_unit})";
781 if ($actionSchedule->repetition_frequency_unit
== 'day') {
782 $interval = "{$actionSchedule->repetition_frequency_interval} DAY";
784 elseif ($actionSchedule->repetition_frequency_unit
== 'week') {
785 $interval = "{$actionSchedule->repetition_frequency_interval} WEEK";
787 elseif ($actionSchedule->repetition_frequency_unit
== 'month') {
788 $interval = "{$actionSchedule->repetition_frequency_interval} MONTH";
790 elseif ($actionSchedule->repetition_frequency_unit
== 'year') {
791 $interval = "{$actionSchedule->repetition_frequency_interval} YEAR";
794 $interval = "{$actionSchedule->repetition_frequency_interval} HOUR";
797 // (now <= repeat_end_time )
798 $repeatEventClause = "'{$now}' <= {$repeatEvent}";
799 // diff(now && logged_date_time) >= repeat_interval
800 $havingClause = "HAVING TIMESTAMPDIFF(HOUR, latest_log_time, CAST({$now} AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL $interval))";
801 $groupByClause = 'GROUP BY reminder.contact_id, reminder.entity_id, reminder.entity_table';
802 $selectClause .= ', MAX(reminder.action_date_time) as latest_log_time';
803 //CRM-15376 - do not send our reminders if original criteria no longer applies
804 // the first part of the startDateClause array is the earliest the reminder can be sent. If the
805 // event (e.g membership_end_date) has changed then the reminder may no longer apply
806 // @todo - this only handles events that get moved later. Potentially they might get moved earlier
807 $originalEventStartDateClause = empty($startDateClause) ?
'' : 'AND' . $startDateClause[0];
808 $sqlInsertValues = "{$selectClause}
811 INNER JOIN {$reminderJoinClause}
812 {$whereClause} {$limitWhereClause} AND {$repeatEventClause} {$originalEventStartDateClause} {$notINClause}
816 $valsqlInsertValues = CRM_Core_DAO
::executeQuery($sqlInsertValues, array(
824 $arrValues = array();
825 while ($valsqlInsertValues->fetch()) {
826 $arrValues[] = "( {$valsqlInsertValues->contact_id}, {$valsqlInsertValues->entity_id}, '{$valsqlInsertValues->entity_table}',{$valsqlInsertValues->action_schedule_id} )";
829 $valString = implode(',', $arrValues);
833 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id) VALUES ' . $valString;
834 CRM_Core_DAO
::executeQuery($query, array(1 => array($actionSchedule->id
, 'Integer')));
837 if ($isSendToAdditionalContacts) {
838 $addSelect .= ', MAX(reminder.action_date_time) as latest_log_time';
839 $sqlEndEventCheck = "
840 SELECT * FROM {$table}
841 {$whereClause} AND {$repeatEventClause} LIMIT 1";
843 $daoCheck = CRM_Core_DAO
::executeQuery($sqlEndEventCheck);
844 if ($daoCheck->fetch()) {
845 $valSqlAdditionInsert = "
849 INNER JOIN {$additionReminderClause}
850 WHERE {$addWhere} AND c.is_deleted = 0 AND c.is_deceased = 0
851 GROUP BY reminder.contact_id
854 $daoForVals = CRM_Core_DAO
::executeQuery($valSqlAdditionInsert);
855 $addValues = array();
856 while ($daoForVals->fetch()) {
857 $addValues[] = "( {$daoForVals->contact_id}, {$daoForVals->entity_id}, '{$daoForVals->entity_table}',{$daoForVals->action_schedule_id} )";
859 $valString = implode(',', $addValues);
863 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id) VALUES ' . $valString;
864 CRM_Core_DAO
::executeQuery($query);
875 * @return null|string
877 public static function permissionedRelationships($field) {
879 SELECT cm.id AS owner_id, cm.contact_id AS owner_contact, m.id AS slave_id, m.contact_id AS slave_contact, cmt.relationship_type_id AS relation_type, rel.contact_id_a, rel.contact_id_b, rel.is_permission_a_b, rel.is_permission_b_a
880 FROM civicrm_membership m
881 LEFT JOIN civicrm_membership cm ON cm.id = m.owner_membership_id
882 LEFT JOIN civicrm_membership_type cmt ON cmt.id = m.membership_type_id
883 LEFT JOIN civicrm_relationship rel ON ( ( rel.contact_id_a = m.contact_id AND rel.contact_id_b = cm.contact_id AND rel.relationship_type_id = cmt.relationship_type_id )
884 OR ( rel.contact_id_a = cm.contact_id AND rel.contact_id_b = m.contact_id AND rel.relationship_type_id = cmt.relationship_type_id ) )
885 WHERE m.owner_membership_id IS NOT NULL AND
886 ( rel.is_permission_a_b = 0 OR rel.is_permission_b_a = 0)
889 $excludeIds = array();
890 $dao = CRM_Core_DAO
::executeQuery($query, array());
891 while ($dao->fetch()) {
892 if ($dao->slave_contact
== $dao->contact_id_a
&& $dao->is_permission_a_b
== 0) {
893 $excludeIds[] = $dao->slave_contact
;
895 elseif ($dao->slave_contact
== $dao->contact_id_b
&& $dao->is_permission_b_a
== 0) {
896 $excludeIds[] = $dao->slave_contact
;
900 if (!empty($excludeIds)) {
901 $clause = "AND {$field} NOT IN ( " . implode(', ', $excludeIds) . ' ) ';
909 * @param array $params
913 public static function processQueue($now = NULL, $params = array()) {
914 $now = $now ? CRM_Utils_Time
::setTime($now) : CRM_Utils_Time
::getTime();
916 $mappings = CRM_Core_BAO_ActionSchedule
::getMappings();
917 foreach ($mappings as $mappingID => $mapping) {
918 CRM_Core_BAO_ActionSchedule
::buildRecipientContacts($mappingID, $now, $params);
919 CRM_Core_BAO_ActionSchedule
::sendMailings($mappingID, $now);
924 'messages' => ts('Sent all scheduled reminders successfully'),
931 * @param int $mappingID
933 * @return null|string
935 public static function isConfigured($id, $mappingID) {
936 $queryString = "SELECT count(id) FROM civicrm_action_schedule
937 WHERE mapping_id = %1 AND
941 1 => array($mappingID, 'Integer'),
942 2 => array($id, 'Integer'),
944 return CRM_Core_DAO
::singleValueQuery($queryString, $params);
948 * @param int $mappingID
949 * @param $recipientType
953 public static function getRecipientListing($mappingID, $recipientType) {
958 /** @var \Civi\ActionSchedule\Mapping $mapping */
959 $mapping = CRM_Utils_Array
::first(CRM_Core_BAO_ActionSchedule
::getMappings(array(
962 return $mapping->getRecipientListing($recipientType);
966 * @param $communication_language
967 * @param $preferred_language
969 public static function setCommunicationLanguage($communication_language, $preferred_language) {
970 $config = CRM_Core_Config
::singleton();
971 $language = $config->lcMessages
;
973 // prepare the language for the email
974 if ($communication_language == CRM_Core_I18n
::AUTO
) {
975 if (!empty($preferred_language)) {
976 $language = $preferred_language;
980 $language = $communication_language;
983 // language not in the existing language, use default
984 $languages = CRM_Core_I18n
::languages(TRUE);
985 if (!in_array($language, $languages)) {
986 $language = $config->lcMessages
;
989 // change the language
990 $i18n = CRM_Core_I18n
::singleton();
991 $i18n->setLanguage($language);
995 * Save a record about the delivery of a reminder email.
997 * WISHLIST: Instead of saving $actionSchedule->body_html, call this immediately after
998 * sending the message and pass in the fully rendered text of the message.
1000 * @param CRM_Core_DAO_ActionSchedule $actionSchedule
1001 * @param Civi\ActionSchedule\Mapping $mapping
1002 * @param int $contactID
1003 * @param int $entityID
1004 * @param int|NULL $caseID
1005 * @throws CRM_Core_Exception
1007 protected static function createMailingActivity($actionSchedule, $mapping, $contactID, $entityID, $caseID) {
1008 $session = CRM_Core_Session
::singleton();
1010 if ($mapping->entity
== 'civicrm_membership') {
1012 = CRM_Core_OptionGroup
::getValue('activity_type', 'Membership Renewal Reminder', 'name');
1016 = CRM_Core_OptionGroup
::getValue('activity_type', 'Reminder Sent', 'name');
1019 $activityParams = array(
1020 'subject' => $actionSchedule->title
,
1021 'details' => $actionSchedule->body_html
,
1022 'source_contact_id' => $session->get('userID') ?
$session->get('userID') : $contactID,
1023 'target_contact_id' => $contactID,
1024 'activity_date_time' => CRM_Utils_Time
::getTime('YmdHis'),
1025 'status_id' => CRM_Core_OptionGroup
::getValue('activity_status', 'Completed', 'name'),
1026 'activity_type_id' => $activityTypeID,
1027 'source_record_id' => $entityID,
1029 $activity = CRM_Activity_BAO_Activity
::create($activityParams);
1031 //file reminder on case if source activity is a case activity
1032 if (!empty($caseID)) {
1033 $caseActivityParams = array();
1034 $caseActivityParams['case_id'] = $caseID;
1035 $caseActivityParams['activity_id'] = $activity->id
;
1036 CRM_Case_BAO_Case
::processCaseActivity($caseActivityParams);
1042 * @param $actionSchedule
1045 protected static function prepareMailingQuery($mapping, $actionSchedule) {
1046 $extraSelect = $extraJoin = $extraWhere = $extraOn = '';
1048 if ($mapping->entity
== 'civicrm_activity') {
1049 $compInfo = CRM_Core_Component
::getEnabledComponents();
1050 $extraSelect = ', ov.label as activity_type, e.id as activity_id';
1052 INNER JOIN civicrm_option_group og ON og.name = 'activity_type'
1053 INNER JOIN civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id";
1054 $extraOn = ' AND e.is_current_revision = 1 AND e.is_deleted = 0 ';
1055 if ($actionSchedule->limit_to
== 0) {
1057 LEFT JOIN civicrm_option_group og ON og.name = 'activity_type'
1058 LEFT JOIN civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id";
1062 // if CiviCase component is enabled
1063 if (array_key_exists('CiviCase', $compInfo)) {
1064 $extraSelect .= ", civicrm_case_activity.case_id as case_id";
1066 LEFT JOIN `civicrm_case_activity` ON `e`.`id` = `civicrm_case_activity`.`activity_id`";
1070 if ($mapping->entity
== 'civicrm_participant') {
1071 $extraSelect = ', ov.label as event_type, ev.title, ev.id as event_id, ev.start_date, ev.end_date, ev.summary, ev.description, address.street_address, address.city, address.state_province_id, address.postal_code, email.email as contact_email, phone.phone as contact_phone ';
1074 INNER JOIN civicrm_event ev ON e.event_id = ev.id
1075 INNER JOIN civicrm_option_group og ON og.name = 'event_type'
1076 INNER JOIN civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id
1077 LEFT JOIN civicrm_loc_block lb ON lb.id = ev.loc_block_id
1078 LEFT JOIN civicrm_address address ON address.id = lb.address_id
1079 LEFT JOIN civicrm_email email ON email.id = lb.email_id
1080 LEFT JOIN civicrm_phone phone ON phone.id = lb.phone_id
1082 if ($actionSchedule->limit_to
== 0) {
1084 LEFT JOIN civicrm_event ev ON e.event_id = ev.id
1085 LEFT JOIN civicrm_option_group og ON og.name = 'event_type'
1086 LEFT JOIN civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id
1087 LEFT JOIN civicrm_loc_block lb ON lb.id = ev.loc_block_id
1088 LEFT JOIN civicrm_address address ON address.id = lb.address_id
1089 LEFT JOIN civicrm_email email ON email.id = lb.email_id
1090 LEFT JOIN civicrm_phone phone ON phone.id = lb.phone_id
1095 if ($mapping->entity
== 'civicrm_membership') {
1096 $extraSelect = ', mt.minimum_fee as fee, e.id as id , e.join_date, e.start_date, e.end_date, ms.name as status, mt.name as type';
1098 INNER JOIN civicrm_membership_type mt ON e.membership_type_id = mt.id
1099 INNER JOIN civicrm_membership_status ms ON e.status_id = ms.id';
1101 if ($actionSchedule->limit_to
== 0) {
1103 LEFT JOIN civicrm_membership_type mt ON e.membership_type_id = mt.id
1104 LEFT JOIN civicrm_membership_status ms ON e.status_id = ms.id';
1108 $entityJoinClause = "INNER JOIN {$mapping->entity} e ON e.id = reminder.entity_id";
1109 if ($actionSchedule->limit_to
== 0) {
1110 $entityJoinClause = "LEFT JOIN {$mapping->entity} e ON e.id = reminder.entity_id";
1111 $extraWhere .= " AND (e.id = reminder.entity_id OR reminder.entity_table = 'civicrm_contact')";
1113 $entityJoinClause .= $extraOn;
1116 SELECT reminder.id as reminderID, reminder.contact_id as contactID, reminder.entity_table as entityTable, reminder.*, e.id as entityID, e.* {$extraSelect}
1117 FROM civicrm_action_log reminder
1120 WHERE reminder.action_schedule_id = %1 AND reminder.action_date_time IS NULL
1126 * @param TokenRow $tokenRow
1127 * @param CRM_Core_DAO_ActionSchedule $schedule
1128 * @param int $toContactID
1129 * @throws CRM_Core_Exception
1131 * List of error messages.
1133 protected static function sendReminderSms($tokenRow, $schedule, $toContactID) {
1134 $toPhoneNumber = self
::pickSmsPhoneNumber($toContactID);
1135 if (!$toPhoneNumber) {
1136 return array("sms_phone_missing" => "Couldn't find recipient's phone number.");
1139 $messageSubject = $tokenRow->render('subject');
1140 $sms_body_text = $tokenRow->render('sms_body_text');
1142 $session = CRM_Core_Session
::singleton();
1143 $userID = $session->get('userID') ?
$session->get('userID') : $tokenRow->context
['contactId'];
1145 'To' => $toPhoneNumber,
1146 'provider_id' => $schedule->sms_provider_id
,
1147 'activity_subject' => $messageSubject,
1149 $activityTypeID = CRM_Core_OptionGroup
::getValue('activity_type',
1153 $activityParams = array(
1154 'source_contact_id' => $userID,
1155 'activity_type_id' => $activityTypeID,
1156 'activity_date_time' => date('YmdHis'),
1157 'subject' => $messageSubject,
1158 'details' => $sms_body_text,
1159 'status_id' => CRM_Core_OptionGroup
::getValue('activity_status', 'Completed', 'name'),
1162 $activity = CRM_Activity_BAO_Activity
::create($activityParams);
1164 CRM_Activity_BAO_Activity
::sendSMSMessage($tokenRow->context
['contactId'],
1175 * @param CRM_Core_DAO_ActionSchedule $actionSchedule
1177 * Ex: "Alice <alice@example.org>".
1179 protected static function pickFromEmail($actionSchedule) {
1180 $domainValues = CRM_Core_BAO_Domain
::getNameAndEmail();
1181 $fromEmailAddress = "$domainValues[0] <$domainValues[1]>";
1182 if ($actionSchedule->from_email
) {
1183 $fromEmailAddress = "$actionSchedule->from_name <$actionSchedule->from_email>";
1184 return $fromEmailAddress;
1186 return $fromEmailAddress;
1190 * @param TokenRow $tokenRow
1191 * @param CRM_Core_DAO_ActionSchedule $schedule
1192 * @param int $toContactID
1194 * List of error messages.
1196 protected static function sendReminderEmail($tokenRow, $schedule, $toContactID) {
1197 $toEmail = CRM_Contact_BAO_Contact
::getPrimaryEmail($toContactID);
1199 return array("email_missing" => "Couldn't find recipient's email address.");
1202 $body_text = $tokenRow->render('body_text');
1203 $body_html = $tokenRow->render('body_html');
1204 if (!$schedule->body_text
) {
1205 $body_text = CRM_Utils_String
::htmlToText($body_html);
1208 // set up the parameters for CRM_Utils_Mail::send
1209 $mailParams = array(
1210 'groupName' => 'Scheduled Reminder Sender',
1211 'from' => self
::pickFromEmail($schedule),
1212 'toName' => $tokenRow->context
['contact']['display_name'],
1213 'toEmail' => $toEmail,
1214 'subject' => $tokenRow->render('subject'),
1215 'entity' => 'action_schedule',
1216 'entity_id' => $schedule->id
,
1219 if (!$body_html ||
$tokenRow->context
['contact']['preferred_mail_format'] == 'Text' ||
1220 $tokenRow->context
['contact']['preferred_mail_format'] == 'Both'
1222 // render the & entities in text mode, so that the links work
1223 $mailParams['text'] = str_replace('&', '&', $body_text);
1225 if ($body_html && ($tokenRow->context
['contact']['preferred_mail_format'] == 'HTML' ||
1226 $tokenRow->context
['contact']['preferred_mail_format'] == 'Both'
1229 $mailParams['html'] = $body_html;
1231 $result = CRM_Utils_Mail
::send($mailParams);
1232 if (!$result ||
is_a($result, 'PEAR_Error')) {
1233 return array('email_fail' => 'Failed to send message');
1240 * @param CRM_Core_DAO_ActionSchedule $schedule
1241 * @param \Civi\ActionSchedule\Mapping $mapping
1242 * @return \Civi\Token\TokenProcessor
1244 protected static function createTokenProcessor($schedule, $mapping) {
1245 $tp = new \Civi\Token\
TokenProcessor(\Civi\Core\Container
::singleton()->get('dispatcher'), array(
1246 'controller' => __CLASS__
,
1247 'actionSchedule' => $schedule,
1248 'actionMapping' => $mapping,
1251 $tp->addMessage('body_text', $schedule->body_text
, 'text/plain');
1252 $tp->addMessage('body_html', $schedule->body_html
, 'text/html');
1253 $tp->addMessage('sms_body_text', $schedule->sms_body_text
, 'text/plain');
1254 $tp->addMessage('subject', $schedule->subject
, 'text/plain');
1260 * @return string|NULL
1262 protected static function pickSmsPhoneNumber($smsToContactId) {
1263 $toPhoneNumbers = CRM_Core_BAO_Phone
::allPhones($smsToContactId, FALSE, 'Mobile', array(
1268 //to get primary mobile ph,if not get a first mobile phONE
1269 if (!empty($toPhoneNumbers)) {
1270 $toPhoneNumberDetails = reset($toPhoneNumbers);
1271 $toPhoneNumber = CRM_Utils_Array
::value('phone', $toPhoneNumberDetails);
1272 return $toPhoneNumber;