3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.5 |
5 +--------------------------------------------------------------------+
6 | Copyright (C) 2011 Marty Wright |
7 | Licensed to CiviCRM under the Academic Free License version 3.0. |
8 +--------------------------------------------------------------------+
9 | This file is a part of CiviCRM. |
11 | CiviCRM is free software; you can copy, modify, and distribute it |
12 | under the terms of the GNU Affero General Public License |
13 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
15 | CiviCRM is distributed in the hope that it will be useful, but |
16 | WITHOUT ANY WARRANTY; without even the implied warranty of |
17 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
18 | See the GNU Affero General Public License for more details. |
20 | You should have received a copy of the GNU Affero General Public |
21 | License and the CiviCRM Licensing Exception along |
22 | with this program; if not, contact CiviCRM LLC |
23 | at info[AT]civicrm[DOT]org. If you have questions about the |
24 | GNU Affero General Public License or the licensing of CiviCRM, |
25 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
26 +--------------------------------------------------------------------+
32 * @copyright CiviCRM LLC (c) 2004-2014
38 * This class contains functions for managing Scheduled Reminders
40 class CRM_Core_BAO_ActionSchedule
extends CRM_Core_DAO_ActionSchedule
{
47 static function getMapping($id = NULL) {
48 static $_action_mapping;
50 if ($id && !is_null($_action_mapping) && isset($_action_mapping[$id])) {
51 return $_action_mapping[$id];
54 $dao = new CRM_Core_DAO_ActionMapping();
61 while ($dao->fetch()) {
63 CRM_Core_DAO
::storeValues($dao, $defaults);
64 $mapping[$dao->id
] = $defaults;
66 $_action_mapping = $mapping;
72 * Retrieve list of selections/drop downs for Scheduled Reminder form
74 * @param bool $id mapping id
76 * @return array associated array of all the drop downs in the form
80 static function getSelection($id = NULL) {
81 $mapping = self
::getMapping($id);
82 $activityStatus = CRM_Core_PseudoConstant
::activityStatus();
83 $activityType = CRM_Core_PseudoConstant
::activityType(TRUE, TRUE);
85 $participantStatus = CRM_Event_PseudoConstant
::participantStatus(NULL, NULL, 'label');
86 $event = CRM_Event_PseudoConstant
::event(NULL, FALSE, "( is_template IS NULL OR is_template != 1 )");
87 $eventType = CRM_Event_PseudoConstant
::eventType();
88 $eventTemplate = CRM_Event_PseudoConstant
::eventTemplates();
89 $autoRenew = CRM_Core_OptionGroup
::values('auto_renew_options');
90 $membershipType = CRM_Member_PseudoConstant
::membershipType();
94 $sel1 = $sel2 = $sel3 = $sel4 = $sel5 = array();
96 'manual' => ts('Choose Recipient(s)'),
97 'group' => ts('Select a Group'),
100 $entityMapping = array();
101 $recipientMapping = array_combine(array_keys($options), array_keys($options));
107 foreach ($mapping as $value) {
108 $entityValue = CRM_Utils_Array
::value('entity_value', $value);
109 $entityStatus = CRM_Utils_Array
::value('entity_status', $value);
110 $entityRecipient = CRM_Utils_Array
::value('entity_recipient', $value);
111 $valueLabel = array('- ' . strtolower(CRM_Utils_Array
::value('entity_value_label', $value)) . ' -');
112 $key = CRM_Utils_Array
::value('id', $value);
113 $entityMapping[$key] = CRM_Utils_Array
::value('entity', $value);
116 switch ($entityValue) {
117 case 'activity_type':
118 if ($value['entity'] == 'civicrm_activity') {
119 $sel1Val = ts('Activity');
121 $sel2[$key] = $valueLabel +
$activityType;
125 if ($value['entity'] == 'civicrm_participant') {
126 $sel1Val = ts('Event Type');
128 $sel2[$key] = $valueLabel +
$eventType;
131 case 'event_template':
132 if ($value['entity'] == 'civicrm_participant') {
133 $sel1Val = ts('Event Template');
135 $sel2[$key] = $valueLabel +
$eventTemplate;
138 case 'civicrm_event':
139 if ($value['entity'] == 'civicrm_participant') {
140 $sel1Val = ts('Event Name');
142 $sel2[$key] = $valueLabel +
$event;
145 case 'civicrm_membership_type':
146 if ($value['entity'] == 'civicrm_membership') {
147 $sel1Val = ts('Membership');
149 $sel2[$key] = $valueLabel +
$membershipType;
152 $sel1[$key] = $sel1Val;
155 if ($startDate = CRM_Utils_Array
::value('entity_date_start', $value)) {
156 $sel4[$startDate] = ucwords(str_replace('_', ' ', $startDate));
158 if ($endDate = CRM_Utils_Array
::value('entity_date_end', $value)) {
159 $sel4[$endDate] = ucwords(str_replace('_', ' ', $endDate));
162 switch ($entityRecipient) {
163 case 'activity_contacts':
164 $activityContacts = CRM_Core_OptionGroup
::values('activity_contacts');
165 $sel5[$entityRecipient] = $activityContacts +
$options;
166 $recipientMapping +
= CRM_Core_OptionGroup
::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
169 case 'event_contacts':
170 $eventContacts = CRM_Core_OptionGroup
::values('event_contacts');
171 $sel5[$entityRecipient] = $eventContacts +
$options;
172 $recipientMapping +
= CRM_Core_OptionGroup
::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'name');
176 $sel5[$entityRecipient] = $options;
183 foreach ($mapping as $value) {
184 $entityStatus = CRM_Utils_Array
::value('entity_status', $value);
185 $statusLabel = array('- ' . strtolower(CRM_Utils_Array
::value('entity_status_label', $value)) . ' -');
186 $id = CRM_Utils_Array
::value('id', $value);
188 switch ($entityStatus) {
189 case 'activity_status':
190 foreach ($sel3[$id] as $kkey => & $vval) {
191 $vval = $statusLabel +
$activityStatus;
195 case 'civicrm_participant_status_type':
196 foreach ($sel3[$id] as $kkey => & $vval) {
197 $vval = $statusLabel +
$participantStatus;
201 case 'auto_renew_options':
202 foreach ($sel3[$id] as $kkey => & $vval) {
205 $auto = CRM_Core_DAO
::getFieldValue('CRM_Member_DAO_MembershipType', $kkey, 'auto_renew');
208 $vval = $statusLabel +
$autoRenew;
211 $vval = $statusLabel;
228 'entityMapping' => $entityMapping,
229 'recipientMapping' => $recipientMapping,
238 static function getSelection1($id = NULL) {
239 $mapping = self
::getMapping($id);
240 $sel4 = $sel5 = array();
242 'manual' => ts('Choose Recipient(s)'),
243 'group' => ts('Select a Group'),
246 $recipientMapping = array_combine(array_keys($options), array_keys($options));
248 foreach ($mapping as $value) {
249 $entityRecipient = CRM_Utils_Array
::value('entity_recipient', $value);
250 $key = CRM_Utils_Array
::value('id', $value);
252 if ($startDate = CRM_Utils_Array
::value('entity_date_start', $value)) {
253 $sel4[$startDate] = ucwords(str_replace('_', ' ', $startDate));
255 if ($endDate = CRM_Utils_Array
::value('entity_date_end', $value)) {
256 $sel4[$endDate] = ucwords(str_replace('_', ' ', $endDate));
259 switch ($entityRecipient) {
260 case 'activity_contacts':
261 $activityContacts = CRM_Core_OptionGroup
::values('activity_contacts');
262 $sel5[$id] = $activityContacts +
$options;
263 $recipientMapping +
= CRM_Core_OptionGroup
::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
266 case 'event_contacts':
267 $eventContacts = CRM_Core_OptionGroup
::values('event_contacts');
268 $sel5[$id] = $eventContacts +
$options;
269 $recipientMapping +
= CRM_Core_OptionGroup
::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'name');
273 $sel5[$id] = $options;
280 'sel5' => $sel5[$id],
281 'recipientMapping' => $recipientMapping,
286 * Retrieve list of Scheduled Reminders
288 * @param bool $namesOnly return simple list of names
290 * @param null $entityValue
293 * @return array (reference) reminder list
297 static function &getList($namesOnly = FALSE, $entityValue = NULL, $id = NULL) {
298 $activity_type = CRM_Core_PseudoConstant
::activityType(TRUE, TRUE);
299 $activity_status = CRM_Core_PseudoConstant
::activityStatus();
301 $event_type = CRM_Event_PseudoConstant
::eventType();
302 $civicrm_event = CRM_Event_PseudoConstant
::event(NULL, FALSE, "( is_template IS NULL OR is_template != 1 )");
303 $civicrm_participant_status_type = CRM_Event_PseudoConstant
::participantStatus(NULL, NULL, 'label');
304 $event_template = CRM_Event_PseudoConstant
::eventTemplates();
306 $auto_renew_options = CRM_Core_OptionGroup
::values('auto_renew_options');
307 $civicrm_membership_type = CRM_Member_PseudoConstant
::membershipType();
310 'civicrm_activity' => 'Activity',
311 'civicrm_participant' => 'Event',
312 'civicrm_membership' => 'Member',
320 cam.entity_value as entityValue,
321 cas.entity_value as entityValueIds,
322 cam.entity_status as entityStatus,
323 cas.entity_status as entityStatusIds,
324 cas.start_action_date as entityDate,
325 cas.start_action_offset,
326 cas.start_action_unit,
327 cas.start_action_condition,
332 FROM civicrm_action_schedule cas
333 LEFT JOIN civicrm_action_mapping cam ON (cam.id = cas.mapping_id)
335 $params = CRM_Core_DAO
::$_nullArray;
337 if ($entityValue and $id) {
339 WHERE cas.entity_value = $id AND
340 cam.entity_value = '$entityValue'";
345 1 => array($id, 'Integer'),
346 2 => array($entityValue, 'String'),
350 $dao = CRM_Core_DAO
::executeQuery($query);
351 while ($dao->fetch()) {
352 $list[$dao->id
]['id'] = $dao->id
;
353 $list[$dao->id
]['title'] = $dao->title
;
354 $list[$dao->id
]['start_action_offset'] = $dao->start_action_offset
;
355 $list[$dao->id
]['start_action_unit'] = $dao->start_action_unit
;
356 $list[$dao->id
]['start_action_condition'] = $dao->start_action_condition
;
357 $list[$dao->id
]['entityDate'] = ucwords(str_replace('_', ' ', $dao->entityDate
));
358 $list[$dao->id
]['absolute_date'] = $dao->absolute_date
;
360 $status = $dao->entityStatus
;
361 $statusArray = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $dao->entityStatusIds
);
362 foreach ($statusArray as & $s) {
363 $s = CRM_Utils_Array
::value($s, $
$status);
365 $statusIds = implode(', ', $statusArray);
367 $value = $dao->entityValue
;
368 $valueArray = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $dao->entityValueIds
);
369 foreach ($valueArray as & $v) {
370 $v = CRM_Utils_Array
::value($v, $
$value);
372 $valueIds = implode(', ', $valueArray);
373 $list[$dao->id
]['entity'] = $entity[$dao->entity
];
374 $list[$dao->id
]['value'] = $valueIds;
375 $list[$dao->id
]['status'] = $statusIds;
376 $list[$dao->id
]['is_repeat'] = $dao->is_repeat
;
377 $list[$dao->id
]['is_active'] = $dao->is_active
;
388 * @param $tokenParams
391 * @throws CRM_Core_Exception
393 static function sendReminder($contactId, $to, $scheduleID, $from, $tokenParams) {
394 $email = $to['email'];
395 $phoneNumber = $to['phone'];
396 $schedule = new CRM_Core_DAO_ActionSchedule();
397 $schedule->id
= $scheduleID;
399 $domain = CRM_Core_BAO_Domain
::getDomain();
401 $hookTokens = array();
403 if ($schedule->find(TRUE)) {
404 $body_text = $schedule->body_text
;
405 $body_html = $schedule->body_html
;
406 $body_subject = $schedule->subject
;
408 $body_text = CRM_Utils_String
::htmlToText($body_html);
411 $params = array(array('contact_id', '=', $contactId, 0, 0));
412 list($contact, $_) = CRM_Contact_BAO_Query
::apiQuery($params);
415 $contact = reset($contact);
417 if (!$contact ||
is_a($contact, 'CRM_Core_Error')) {
421 // merge activity tokens with contact array
422 $contact = array_merge($contact, $tokenParams);
425 CRM_Utils_Hook
::tokenValues($contact, $contactId);
427 CRM_Utils_Hook
::tokens($hookTokens);
428 $categories = array_keys($hookTokens);
430 $type = array('html', 'text');
432 foreach ($type as $key => $value) {
433 $dummy_mail = new CRM_Mailing_BAO_Mailing();
434 $bodyType = "body_{$value}";
435 $dummy_mail->$bodyType = $
$bodyType;
436 $tokens = $dummy_mail->getTokens();
439 CRM_Utils_Token
::replaceGreetingTokens($
$bodyType, NULL, $contact['contact_id']);
440 $
$bodyType = CRM_Utils_Token
::replaceDomainTokens($
$bodyType, $domain, TRUE, $tokens[$value], TRUE);
441 $
$bodyType = CRM_Utils_Token
::replaceContactTokens($
$bodyType, $contact, FALSE, $tokens[$value], FALSE, TRUE);
442 $
$bodyType = CRM_Utils_Token
::replaceComponentTokens($
$bodyType, $contact, $tokens[$value], TRUE, FALSE);
443 $
$bodyType = CRM_Utils_Token
::replaceHookTokens($
$bodyType, $contact, $categories, TRUE);
449 $smarty = CRM_Core_Smarty
::singleton();
451 'text', 'html') as $elem) {
452 $
$elem = $smarty->fetch("string:{$$elem}");
456 preg_match_all('/(?<!\{|\\\\)\{(\w+\.\w+)\}(?!\})/',
462 $subjectToken = NULL;
464 foreach ($matches[1] as $token) {
465 list($type, $name) = preg_split('/\./', $token, 2);
467 if (!isset($subjectToken[$type])) {
468 $subjectToken[$type] = array();
470 $subjectToken[$type][] = $name;
475 $messageSubject = CRM_Utils_Token
::replaceContactTokens($body_subject, $contact, FALSE, $subjectToken);
476 $messageSubject = CRM_Utils_Token
::replaceDomainTokens($messageSubject, $domain, TRUE, $subjectToken);
477 $messageSubject = CRM_Utils_Token
::replaceComponentTokens($messageSubject, $contact, $subjectToken, TRUE);
478 $messageSubject = CRM_Utils_Token
::replaceHookTokens($messageSubject, $contact, $categories, TRUE);
480 $messageSubject = $smarty->fetch("string:{$messageSubject}");
482 if ($schedule->mode
== 'SMS' or $schedule->mode
== 'User_Preference') {
483 $session = CRM_Core_Session
::singleton();
484 $userID = $session->get('userID') ?
$session->get('userID') : $contactId;
485 $smsParams = array('To' => $phoneNumber, 'provider_id' => $schedule->sms_provider_id
, 'activity_subject' => $messageSubject);
486 $activityTypeID = CRM_Core_OptionGroup
::getValue('activity_type',
490 $details = $html ?
$html : $text;
491 $activityParams = array(
492 'source_contact_id' => $userID,
493 'activity_type_id' => $activityTypeID,
494 'activity_date_time' => date('YmdHis'),
495 'subject' => $messageSubject,
496 'details' => $details,
497 'status_id' => CRM_Core_OptionGroup
::getValue('activity_status', 'Completed', 'name'),
500 $activity = CRM_Activity_BAO_Activity
::create($activityParams);
502 CRM_Activity_BAO_Activity
::sendSMSMessage($contactId,
511 if ($schedule->mode
== 'Email' or $schedule->mode
== 'User_Preference') {
512 // set up the parameters for CRM_Utils_Mail::send
514 'groupName' => 'Scheduled Reminder Sender',
516 'toName' => $contact['display_name'],
518 'subject' => $messageSubject,
519 'entity' => 'action_schedule',
520 'entity_id' => $scheduleID,
523 if (!$html ||
$contact['preferred_mail_format'] == 'Text' ||
524 $contact['preferred_mail_format'] == 'Both'
526 // render the & entities in text mode, so that the links work
527 $mailParams['text'] = str_replace('&', '&', $text);
529 if ($html && ($contact['preferred_mail_format'] == 'HTML' ||
530 $contact['preferred_mail_format'] == 'Both'
533 $mailParams['html'] = $html;
535 $result = CRM_Utils_Mail
::send($mailParams);
544 * Function to add the schedules reminders in the db
546 * @param array $params (reference ) an assoc array of name/value pairs
547 * @param array $ids the array that holds all the db ids
549 * @return object CRM_Core_DAO_ActionSchedule
554 static function add(&$params, $ids = array()) {
555 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
556 $actionSchedule->copyValues($params);
558 return $actionSchedule->save();
562 * Takes a bunch of params that are needed to match certain criteria and
563 * retrieves the relevant objects. It also stores all the retrieved
564 * values in the default array
566 * @param array $params (reference ) an assoc array of name/value pairs
567 * @param array $values (reference ) an assoc array to hold the flattened values
569 * @return object CRM_Core_DAO_ActionSchedule object on success, null otherwise
573 static function retrieve(&$params, &$values) {
574 if (empty($params)) {
577 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
579 $actionSchedule->copyValues($params);
581 if ($actionSchedule->find(TRUE)) {
582 $ids['actionSchedule'] = $actionSchedule->id
;
584 CRM_Core_DAO
::storeValues($actionSchedule, $values);
586 return $actionSchedule;
592 * Function to delete a Reminder
594 * @param int $id ID of the Reminder to be deleted.
599 static function del($id) {
601 $dao = new CRM_Core_DAO_ActionSchedule();
603 if ($dao->find(TRUE)) {
608 CRM_Core_Error
::fatal(ts('Invalid value passed to delete function.'));
612 * update the is_active flag in the db
614 * @param int $id id of the database record
615 * @param boolean $is_active value we want to set the is_active field
617 * @return Object DAO object on success, null otherwise
620 static function setIsActive($id, $is_active) {
621 return CRM_Core_DAO
::setFieldValue('CRM_Core_DAO_ActionSchedule', $id, 'is_active', $is_active);
628 * @throws CRM_Core_Exception
630 static function sendMailings($mappingID, $now) {
631 $domainValues = CRM_Core_BAO_Domain
::getNameAndEmail();
632 $fromEmailAddress = "$domainValues[0] <$domainValues[1]>";
634 $mapping = new CRM_Core_DAO_ActionMapping();
635 $mapping->id
= $mappingID;
636 $mapping->find(TRUE);
638 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
639 $actionSchedule->mapping_id
= $mappingID;
640 $actionSchedule->is_active
= 1;
641 $actionSchedule->find(FALSE);
643 $tokenFields = array();
644 $session = CRM_Core_Session
::singleton();
646 while ($actionSchedule->fetch()) {
647 $extraSelect = $extraJoin = $extraWhere = $extraOn = '';
649 if ($actionSchedule->record_activity
) {
650 if ($mapping->entity
== 'civicrm_membership') {
652 CRM_Core_OptionGroup
::getValue('activity_type', 'Membership Renewal Reminder', 'name');
656 CRM_Core_OptionGroup
::getValue('activity_type', 'Reminder Sent', 'name');
660 CRM_Core_OptionGroup
::getValue('activity_status', 'Completed', 'name');
663 if ($mapping->entity
== 'civicrm_activity') {
664 $tokenEntity = 'activity';
665 $tokenFields = array('activity_id', 'activity_type', 'subject', 'details', 'activity_date_time');
666 $extraSelect = ', ov.label as activity_type, e.id as activity_id';
668 INNER JOIN civicrm_option_group og ON og.name = 'activity_type'
669 INNER JOIN civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id";
670 $extraOn = ' AND e.is_current_revision = 1 AND e.is_deleted = 0 ';
671 if ($actionSchedule->limit_to
== 0) {
673 LEFT JOIN civicrm_option_group og ON og.name = 'activity_type'
674 LEFT JOIN civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id";
678 if ($mapping->entity
== 'civicrm_participant') {
679 $tokenEntity = 'event';
680 $tokenFields = array('event_type', 'title', 'event_id', 'start_date', 'end_date', 'summary', 'description', 'location', 'info_url', 'registration_url', 'fee_amount', 'contact_email', 'contact_phone', 'balance');
681 $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 ';
684 INNER JOIN civicrm_event ev ON e.event_id = ev.id
685 INNER JOIN civicrm_option_group og ON og.name = 'event_type'
686 INNER JOIN civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id
687 LEFT JOIN civicrm_loc_block lb ON lb.id = ev.loc_block_id
688 LEFT JOIN civicrm_address address ON address.id = lb.address_id
689 LEFT JOIN civicrm_email email ON email.id = lb.email_id
690 LEFT JOIN civicrm_phone phone ON phone.id = lb.phone_id
692 if ($actionSchedule->limit_to
== 0) {
694 LEFT JOIN civicrm_event ev ON e.event_id = ev.id
695 LEFT JOIN civicrm_option_group og ON og.name = 'event_type'
696 LEFT JOIN civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id
697 LEFT JOIN civicrm_loc_block lb ON lb.id = ev.loc_block_id
698 LEFT JOIN civicrm_address address ON address.id = lb.address_id
699 LEFT JOIN civicrm_email email ON email.id = lb.email_id
700 LEFT JOIN civicrm_phone phone ON phone.id = lb.phone_id
705 if ($mapping->entity
== 'civicrm_membership') {
706 $tokenEntity = 'membership';
707 $tokenFields = array('fee', 'id', 'join_date', 'start_date', 'end_date', 'status', 'type');
708 $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';
710 INNER JOIN civicrm_membership_type mt ON e.membership_type_id = mt.id
711 INNER JOIN civicrm_membership_status ms ON e.status_id = ms.id';
713 if ($actionSchedule->limit_to
== 0) {
715 LEFT JOIN civicrm_membership_type mt ON e.membership_type_id = mt.id
716 LEFT JOIN civicrm_membership_status ms ON e.status_id = ms.id';
720 $entityJoinClause = "INNER JOIN {$mapping->entity} e ON e.id = reminder.entity_id";
721 if ($actionSchedule->limit_to
== 0) {
722 $entityJoinClause = "LEFT JOIN {$mapping->entity} e ON e.id = reminder.entity_id";
723 $extraWhere .= " AND (e.id = reminder.entity_id OR reminder.entity_table = 'civicrm_contact')";
725 $entityJoinClause .= $extraOn;
728 SELECT reminder.id as reminderID, reminder.contact_id as contactID, reminder.*, e.id as entityID, e.* {$extraSelect}
729 FROM civicrm_action_log reminder
732 WHERE reminder.action_schedule_id = %1 AND reminder.action_date_time IS NULL
735 $dao = CRM_Core_DAO
::executeQuery($query,
736 array(1 => array($actionSchedule->id
, 'Integer'))
739 while ($dao->fetch()) {
740 $entityTokenParams = array();
741 foreach ($tokenFields as $field) {
742 if ($field == 'location') {
744 $stateProvince = CRM_Core_PseudoConstant
::stateProvince();
745 $loc['street_address'] = $dao->street_address
;
746 $loc['city'] = $dao->city
;
747 $loc['state_province'] = CRM_Utils_Array
::value($dao->state_province_id
, $stateProvince);
748 $loc['postal_code'] = $dao->postal_code
;
749 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_Address
::format($loc);
751 elseif ($field == 'info_url') {
752 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_System
::url('civicrm/event/info', 'reset=1&id=' . $dao->event_id
, TRUE, NULL, FALSE);
754 elseif ($field == 'registration_url') {
755 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_System
::url('civicrm/event/register', 'reset=1&id=' . $dao->event_id
, TRUE, NULL, FALSE);
757 elseif (in_array($field, array('start_date','end_date','join_date','activity_date_time'))) {
758 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_Date
::customFormat($dao->$field);
760 elseif ($field == 'balance') {
761 $info = CRM_Contribute_BAO_Contribution
::getPaymentInfo($dao->entityID
, 'event');
762 $balancePay = CRM_Utils_Array
::value('balance', $info);
763 $balancePay = CRM_Utils_Money
::format($balancePay);
764 $entityTokenParams["{$tokenEntity}." . $field] = $balancePay;
766 elseif ($field == 'fee_amount') {
767 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_Money
::format($dao->$field);
770 $entityTokenParams["{$tokenEntity}." . $field] = $dao->$field;
775 $errorMsg = $toEmail = $toPhoneNumber = '';
777 if ($actionSchedule->mode
== 'SMS' or $actionSchedule->mode
== 'User_Preference') {
778 $filters = array('is_deceased' => 0, 'is_deleted' => 0, 'do_not_sms' => 0);
779 $toPhoneNumbers = CRM_Core_BAO_Phone
::allPhones($dao->contactID
, FALSE, 'Mobile', $filters);
780 //to get primary mobile ph,if not get a first mobile phONE
781 if (!empty($toPhoneNumbers)) {
782 $toPhoneNumberDetails = reset($toPhoneNumbers);
783 $toPhoneNumber = CRM_Utils_Array
::value('phone', $toPhoneNumberDetails);
784 //contact allows to send sms
788 if ($actionSchedule->mode
== 'Email' or $actionSchedule->mode
== 'User_Preference') {
789 $toEmail = CRM_Contact_BAO_Contact
::getPrimaryEmail($dao->contactID
);
791 if ($toEmail ||
!(empty($toPhoneNumber) or $toDoNotSms)) {
792 $to['email'] = $toEmail;
793 $to['phone'] = $toPhoneNumber;
795 CRM_Core_BAO_ActionSchedule
::sendReminder(
803 if (!$result ||
is_a($result, 'PEAR_Error')) {
804 // we could not send an email, for now we ignore, CRM-3406
810 $errorMsg = "Couldn\'t find recipient\'s email address.";
813 // update action log record
815 'id' => $dao->reminderID
,
816 'is_error' => $isError,
817 'message' => $errorMsg ?
$errorMsg : "null",
818 'action_date_time' => $now,
820 CRM_Core_BAO_ActionLog
::create($logParams);
822 // insert activity log record if needed
823 if ($actionSchedule->record_activity
) {
824 $activityParams = array(
825 'subject' => $actionSchedule->title
,
826 'details' => $actionSchedule->body_html
,
827 'source_contact_id' =>
828 $session->get('userID') ?
$session->get('userID') : $dao->contactID
,
829 'target_contact_id' => $dao->contactID
,
830 'activity_date_time' => date('YmdHis'),
831 'status_id' => $activityStatusID,
832 'activity_type_id' => $activityTypeID,
833 'source_record_id' => $dao->entityID
,
835 $activity = CRM_Activity_BAO_Activity
::create($activityParams);
846 * @param array $params
848 * @throws API_Exception
850 static function buildRecipientContacts($mappingID, $now, $params = array()) {
851 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
852 $actionSchedule->mapping_id
= $mappingID;
853 $actionSchedule->is_active
= 1;
854 if(!empty($params)) {
855 _civicrm_api3_dao_set_filter($actionSchedule, $params, FALSE, 'ActionSchedule');
857 $actionSchedule->find();
859 while ($actionSchedule->fetch()) {
860 $mapping = new CRM_Core_DAO_ActionMapping();
861 $mapping->id
= $mappingID;
862 $mapping->find(TRUE);
864 // note: $where - this filtering applies for both
865 // 'limit to' and 'addition to' options
866 // $limitWhere - this filtering applies only for
868 $select = $join = $where = $limitWhere = array();
869 $limitTo = $actionSchedule->limit_to
;
870 $value = explode(CRM_Core_DAO
::VALUE_SEPARATOR
,
871 trim($actionSchedule->entity_value
, CRM_Core_DAO
::VALUE_SEPARATOR
)
873 $value = implode(',', $value);
875 $status = explode(CRM_Core_DAO
::VALUE_SEPARATOR
,
876 trim($actionSchedule->entity_status
, CRM_Core_DAO
::VALUE_SEPARATOR
)
878 $status = implode(',', $status);
880 if (!CRM_Utils_System
::isNull($mapping->entity_recipient
)) {
881 $recipientOptions = CRM_Core_OptionGroup
::values($mapping->entity_recipient
, FALSE, FALSE, FALSE, NULL, 'name');
883 $from = "{$mapping->entity} e";
885 if ($mapping->entity
== 'civicrm_activity') {
886 $contactField = 'r.contact_id';
887 $table = 'civicrm_activity e';
888 $activityContacts = CRM_Core_OptionGroup
::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
889 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
890 $assigneeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
891 $targetID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
894 // including the activity target contacts if 'in addition' is defined
895 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$targetID}";
898 switch (CRM_Utils_Array
::value($actionSchedule->recipient
, $recipientOptions)) {
899 case 'Activity Assignees':
900 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$assigneeID}";
903 case 'Activity Source':
904 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$sourceID}";
908 case 'Activity Targets':
909 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$targetID}";
913 // build where clause
914 if (!empty($value)) {
915 $where[] = "e.activity_type_id IN ({$value})";
918 $where[] = "e.activity_type_id IS NULL";
920 if (!empty($status)) {
921 $where[] = "e.status_id IN ({$status})";
923 $where[] = ' e.is_current_revision = 1 ';
924 $where[] = ' e.is_deleted = 0 ';
926 $dateField = 'e.activity_date_time';
929 if ($mapping->entity
== 'civicrm_participant') {
930 $table = 'civicrm_event r';
931 $contactField = 'e.contact_id';
932 $join[] = 'INNER JOIN civicrm_event r ON e.event_id = r.id';
933 if ($actionSchedule->recipient_listing
&& $limitTo) {
934 $rList = explode(CRM_Core_DAO
::VALUE_SEPARATOR
,
935 trim($actionSchedule->recipient_listing
, CRM_Core_DAO
::VALUE_SEPARATOR
)
937 $rList = implode(',', $rList);
939 switch ($recipientOptions[$actionSchedule->recipient
]) {
940 case 'participant_role':
941 $where[] = "e.role_id IN ({$rList})";
949 // build where clause
950 if (!empty($value)) {
951 $where[] = ($mapping->entity_value
== 'event_type') ?
"r.event_type_id IN ({$value})" : "r.id IN ({$value})";
954 $where[] = ($mapping->entity_value
== 'event_type') ?
"r.event_type_id IS NULL" : "r.id IS NULL";
957 // participant status criteria not to be implemented
958 // for additional recipients
959 if (!empty($status)) {
960 $limitWhere[] = "e.status_id IN ({$status})";
963 $where[] = 'r.is_active = 1';
964 $where[] = 'r.is_template = 0';
965 $dateField = str_replace('event_', 'r.', $actionSchedule->start_action_date
);
969 if ($mapping->entity
== 'civicrm_membership') {
970 $contactField = 'e.contact_id';
971 $table = 'civicrm_membership e';
972 // build where clause
973 if ( $status == 2 ) {
974 //auto-renew memberships
975 $where[] = "e.contribution_recur_id IS NOT NULL ";
977 elseif ( $status == 1 ) {
978 $where[] = "e.contribution_recur_id IS NULL ";
981 // build where clause
982 if (!empty($value)) {
983 $where[] = "e.membership_type_id IN ({$value})";
986 $where[] = "e.membership_type_id IS NULL";
989 $where[] = "( e.is_override IS NULL OR e.is_override = 0 )";
990 $dateField = str_replace('membership_', 'e.', $actionSchedule->start_action_date
);
991 $notINClause = self
::permissionedRelationships($contactField);
993 $membershipStatus = CRM_Member_PseudoConstant
::membershipStatus(NULL, "(is_current_member = 1 OR name = 'Expired')", 'id');
994 $mStatus = implode (',', $membershipStatus);
995 $where[] = "e.status_id IN ({$mStatus})";
998 // CRM-13577 Introduce Smart Groups Handling
999 if ($actionSchedule->group_id
) {
1001 // Need to check if its a smart group or not
1002 // Then decide which table to join onto the query
1003 $group = CRM_Contact_DAO_Group
::getTableName();
1005 // Get the group information
1007 SELECT $group.id, $group.cache_date, $group.saved_search_id, $group.children
1009 WHERE $group.id = {$actionSchedule->group_id}
1012 $groupDAO = CRM_Core_DAO
::executeQuery($sql);
1013 $isSmartGroup = FALSE;
1015 $groupDAO->fetch() &&
1016 !empty($groupDAO->saved_search_id
)
1018 // Check that the group is in place in the cache and up to date
1019 CRM_Contact_BAO_GroupContactCache
::check($actionSchedule->group_id
);
1020 // Set smart group flag
1021 $isSmartGroup = TRUE;
1024 // CRM-13577 End Introduce Smart Groups Handling
1027 if ($actionSchedule->group_id
) {
1028 // CRM-13577 If smart group then use Cache table
1029 if ($isSmartGroup) {
1030 $join[] = "INNER JOIN civicrm_group_contact_cache grp ON {$contactField} = grp.contact_id";
1031 $where[] = "grp.group_id IN ({$actionSchedule->group_id})";
1033 $join[] = "INNER JOIN civicrm_group_contact grp ON {$contactField} = grp.contact_id AND grp.status = 'Added'";
1034 $where[] = "grp.group_id IN ({$actionSchedule->group_id})";
1037 elseif (!empty($actionSchedule->recipient_manual
)) {
1038 $rList = CRM_Utils_Type
::escape($actionSchedule->recipient_manual
, 'String');
1039 $where[] = "{$contactField} IN ({$rList})";
1043 $addGroup = $addWhere = '';
1044 if ($actionSchedule->group_id
) {
1045 // CRM-13577 If smart group then use Cache table
1046 if ($isSmartGroup) {
1047 $addGroup = " INNER JOIN civicrm_group_contact_cache grp ON c.id = grp.contact_id";
1048 $addWhere = " grp.group_id IN ({$actionSchedule->group_id})";
1050 $addGroup = " INNER JOIN civicrm_group_contact grp ON c.id = grp.contact_id AND grp.status = 'Added'";
1051 $addWhere = " grp.group_id IN ({$actionSchedule->group_id})";
1054 if (!empty($actionSchedule->recipient_manual
)) {
1055 $rList = CRM_Utils_Type
::escape($actionSchedule->recipient_manual
, 'String');
1056 $addWhere = "c.id IN ({$rList})";
1060 $select[] = "{$contactField} as contact_id";
1061 $select[] = 'e.id as entity_id';
1062 $select[] = "'{$mapping->entity}' as entity_table";
1063 $select[] = "{$actionSchedule->id} as action_schedule_id";
1064 $reminderJoinClause = "civicrm_action_log reminder ON reminder.contact_id = {$contactField} AND
1065 reminder.entity_id = e.id AND
1066 reminder.entity_table = '{$mapping->entity}' AND
1067 reminder.action_schedule_id = %1";
1069 $join[] = "INNER JOIN civicrm_contact c ON c.id = {$contactField} AND c.is_deleted = 0 AND c.is_deceased = 0 ";
1071 if ($actionSchedule->start_action_date
) {
1072 $startDateClause = array();
1073 $op = ($actionSchedule->start_action_condition
== 'before' ?
'<=' : '>=');
1074 $operator = ($actionSchedule->start_action_condition
== 'before' ?
'DATE_SUB' : 'DATE_ADD');
1075 $date = $operator . "({$dateField}, INTERVAL {$actionSchedule->start_action_offset} {$actionSchedule->start_action_unit})";
1076 $startDateClause[] = "'{$now}' >= {$date}";
1077 if ($mapping->entity
== 'civicrm_participant') {
1078 $startDateClause[] = $operator. "({$now}, INTERVAL 1 DAY ) {$op} " . $dateField;
1081 $startDateClause[] = "DATE_SUB({$now}, INTERVAL 1 DAY ) <= {$date}";
1084 $startDate = implode(' AND ', $startDateClause);
1086 elseif ($actionSchedule->absolute_date
) {
1087 $startDate = "DATEDIFF(DATE('{$now}'),'{$actionSchedule->absolute_date}') = 0";
1090 // ( now >= date_built_from_start_time ) OR ( now = absolute_date )
1091 $dateClause = "reminder.id IS NULL AND {$startDate}";
1093 // start composing query
1094 $selectClause = 'SELECT ' . implode(', ', $select);
1095 $fromClause = "FROM $from";
1096 $joinClause = !empty($join) ?
implode(' ', $join) : '';
1097 $whereClause = 'WHERE ' . implode(' AND ', $where);
1098 $limitWhereClause = '';
1099 if (!empty($limitWhere)) {
1100 $limitWhereClause = ' AND ' . implode(' AND ', $limitWhere);
1104 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
1108 LEFT JOIN {$reminderJoinClause}
1109 {$whereClause} {$limitWhereClause} AND {$dateClause} {$notINClause}
1111 CRM_Core_DAO
::executeQuery($query, array(1 => array($actionSchedule->id
, 'Integer')));
1113 if ($limitTo == 0) {
1114 $additionWhere = ' WHERE ';
1115 if ($actionSchedule->start_action_date
) {
1116 $additionWhere = $whereClause . ' AND ';
1118 $contactTable = "civicrm_contact c";
1119 $addSelect = "SELECT c.id as contact_id, c.id as entity_id, 'civicrm_contact' as entity_table, {$actionSchedule->id} as action_schedule_id";
1120 $additionReminderClause = "civicrm_action_log reminder ON reminder.contact_id = c.id AND
1121 reminder.entity_id = c.id AND
1122 reminder.entity_table = 'civicrm_contact' AND
1123 reminder.action_schedule_id = {$actionSchedule->id}";
1124 $addWhereClause = '';
1126 $addWhereClause = "AND {$addWhere}";
1128 $insertAdditionalSql ="
1129 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
1131 FROM ({$contactTable})
1132 LEFT JOIN {$additionReminderClause}
1134 WHERE c.is_deleted = 0 AND c.is_deceased = 0
1138 SELECT rem.contact_id
1139 FROM civicrm_action_log rem INNER JOIN {$mapping->entity} e ON rem.entity_id = e.id
1140 WHERE rem.action_schedule_id = {$actionSchedule->id}
1141 AND rem.entity_table = '{$mapping->entity}'
1145 CRM_Core_DAO
::executeQuery($insertAdditionalSql);
1147 // if repeat is turned ON:
1148 if ($actionSchedule->is_repeat
) {
1149 $repeatEvent = ($actionSchedule->end_action
== 'before' ?
'DATE_SUB' : 'DATE_ADD') . "({$dateField}, INTERVAL {$actionSchedule->end_frequency_interval} {$actionSchedule->end_frequency_unit})";
1151 if ($actionSchedule->repetition_frequency_unit
== 'day') {
1152 $hrs = 24 * $actionSchedule->repetition_frequency_interval
;
1154 elseif ($actionSchedule->repetition_frequency_unit
== 'week') {
1155 $hrs = 24 * $actionSchedule->repetition_frequency_interval
* 7;
1157 elseif ($actionSchedule->repetition_frequency_unit
== 'month') {
1158 $hrs = "24*(DATEDIFF(DATE_ADD(latest_log_time, INTERVAL 1 MONTH ), latest_log_time))";
1160 elseif ($actionSchedule->repetition_frequency_unit
== 'year') {
1161 $hrs = "24*(DATEDIFF(DATE_ADD(latest_log_time, INTERVAL 1 YEAR ), latest_log_time))";
1164 $hrs = $actionSchedule->repetition_frequency_interval
;
1167 // (now <= repeat_end_time )
1168 $repeatEventClause = "'{$now}' <= {$repeatEvent}";
1169 // diff(now && logged_date_time) >= repeat_interval
1170 $havingClause = "HAVING TIMEDIFF({$now}, latest_log_time) >= TIME('{$hrs}:00:00')";
1171 $groupByClause = 'GROUP BY reminder.contact_id, reminder.entity_id, reminder.entity_table';
1172 $selectClause .= ', MAX(reminder.action_date_time) as latest_log_time';
1173 $sqlInsertValues = "{$selectClause}
1176 INNER JOIN {$reminderJoinClause}
1177 {$whereClause} {$limitWhereClause} AND {$repeatEventClause}
1181 $valsqlInsertValues = CRM_Core_DAO
::executeQuery($sqlInsertValues, array(1 => array($actionSchedule->id
, 'Integer')));
1183 $arrValues = array();
1184 while ($valsqlInsertValues->fetch()) {
1185 $arrValues[] = "( {$valsqlInsertValues->contact_id}, {$valsqlInsertValues->entity_id}, '{$valsqlInsertValues->entity_table}',{$valsqlInsertValues->action_schedule_id} )";
1188 $valString = implode(',', $arrValues);
1192 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id) VALUES ' . $valString;
1193 CRM_Core_DAO
::executeQuery($query, array(1 => array($actionSchedule->id
, 'Integer')));
1196 if ($limitTo == 0) {
1197 $addSelect .= ', MAX(reminder.action_date_time) as latest_log_time';
1198 $sqlEndEventCheck = "
1199 SELECT * FROM {$table}
1200 {$whereClause} AND {$repeatEventClause} LIMIT 1";
1202 $daoCheck = CRM_Core_DAO
::executeQuery($sqlEndEventCheck);
1203 if ($daoCheck->fetch()) {
1204 $valSqlAdditionInsert = "
1206 FROM {$contactTable}
1208 INNER JOIN {$additionReminderClause}
1209 WHERE {$addWhere} AND c.is_deleted = 0 AND c.is_deceased = 0
1210 GROUP BY reminder.contact_id
1213 $daoForVals = CRM_Core_DAO
::executeQuery($valSqlAdditionInsert);
1214 $addValues = array();
1215 while ($daoForVals->fetch()) {
1216 $addValues[] = "( {$daoForVals->contact_id}, {$daoForVals->entity_id}, '{$daoForVals->entity_table}',{$daoForVals->action_schedule_id} )";
1218 $valString = implode(',', $addValues);
1222 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id) VALUES ' . $valString;
1223 CRM_Core_DAO
::executeQuery($query);
1234 * @return null|string
1236 static function permissionedRelationships($field) {
1238 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
1239 FROM civicrm_membership m
1240 LEFT JOIN civicrm_membership cm ON cm.id = m.owner_membership_id
1241 LEFT JOIN civicrm_membership_type cmt ON cmt.id = m.membership_type_id
1242 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 )
1243 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 ) )
1244 WHERE m.owner_membership_id IS NOT NULL AND
1245 ( rel.is_permission_a_b = 0 OR rel.is_permission_b_a = 0)
1248 $excludeIds = array();
1249 $dao = CRM_Core_DAO
::executeQuery($query, array());
1250 while ($dao->fetch()) {
1251 if ($dao->slave_contact
== $dao->contact_id_a
&& $dao->is_permission_b_a
== 0) {
1252 $excludeIds[] = $dao->slave_contact
;
1254 elseif ($dao->slave_contact
== $dao->contact_id_b
&& $dao->is_permission_a_b
== 0) {
1255 $excludeIds[] = $dao->slave_contact
;
1259 if (!empty($excludeIds)) {
1260 $clause = "AND {$field} NOT IN ( " .implode(', ', $excludeIds) . ' ) ';
1268 * @param array $params
1272 static function processQueue($now = NULL, $params = array()) {
1273 $now = $now ? CRM_Utils_Time
::setTime($now) : CRM_Utils_Time
::getTime();
1275 $mappings = self
::getMapping();
1276 foreach ($mappings as $mappingID => $mapping) {
1277 self
::buildRecipientContacts($mappingID, $now, $params);
1278 self
::sendMailings($mappingID, $now);
1283 'messages' => ts('Sent all scheduled reminders successfully'),
1292 * @return null|string
1294 static function isConfigured($id, $mappingID) {
1295 $queryString = "SELECT count(id) FROM civicrm_action_schedule
1296 WHERE mapping_id = %1 AND
1300 1 => array($mappingID, 'Integer'),
1301 2 => array($id, 'Integer'),
1303 return CRM_Core_DAO
::singleValueQuery($queryString, $params);
1308 * @param $recipientType
1312 static function getRecipientListing($mappingID, $recipientType) {
1314 if (!$mappingID ||
!$recipientType) {
1318 $mapping = self
::getMapping($mappingID);
1320 switch ($mapping['entity']) {
1321 case 'civicrm_participant':
1322 $eventContacts = CRM_Core_OptionGroup
::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'name');
1323 if (empty($eventContacts[$recipientType])) {
1326 if ($eventContacts[$recipientType] == 'participant_role') {
1327 $options = CRM_Event_PseudoConstant
::participantRole();