3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.6 |
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 public 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 * Get all fields of the type Date
74 public static function getDateFields() {
75 $allFields = CRM_Core_BAO_CustomField
::getFields('');
76 $dateFields = array('birth_date' => ts('Birth Date'));
77 foreach ($allFields as $fieldID => $field) {
78 if ($field['data_type'] == 'Date') {
79 $dateFields["custom_$fieldID"] = $field['label'];
86 * Retrieve list of selections/drop downs for Scheduled Reminder form
92 * associated array of all the drop downs in the form
94 public static function getSelection($id = NULL) {
95 $mapping = self
::getMapping();
96 $activityStatus = CRM_Core_PseudoConstant
::activityStatus();
97 $activityType = CRM_Core_PseudoConstant
::activityType(TRUE, TRUE);
99 $participantStatus = CRM_Event_PseudoConstant
::participantStatus(NULL, NULL, 'label');
100 $event = CRM_Event_PseudoConstant
::event(NULL, FALSE, "( is_template IS NULL OR is_template != 1 )");
101 $eventType = CRM_Event_PseudoConstant
::eventType();
102 $eventTemplate = CRM_Event_PseudoConstant
::eventTemplates();
103 $autoRenew = CRM_Core_OptionGroup
::values('auto_renew_options');
104 $membershipType = CRM_Member_PseudoConstant
::membershipType();
105 $dateFieldParams = array('data_type' => 'Date');
106 $dateFields = self
::getDateFields();
107 $contactOptions = CRM_Core_OptionGroup
::values('contact_date_reminder_options');
109 asort($activityType);
111 $sel1 = $sel2 = $sel3 = $sel4 = $sel5 = array();
113 'manual' => ts('Choose Recipient(s)'),
114 'group' => ts('Select Group'),
117 $entityMapping = array();
118 $recipientMapping = array_combine(array_keys($options), array_keys($options));
124 foreach ($mapping as $value) {
125 $entityValue = CRM_Utils_Array
::value('entity_value', $value);
126 $entityStatus = CRM_Utils_Array
::value('entity_status', $value);
127 $entityRecipient = CRM_Utils_Array
::value('entity_recipient', $value);
128 $valueLabel = array('- ' . strtolower(CRM_Utils_Array
::value('entity_value_label', $value)) . ' -');
129 $key = CRM_Utils_Array
::value('id', $value);
130 $entityMapping[$key] = CRM_Utils_Array
::value('entity', $value);
133 switch ($entityValue) {
134 case 'activity_type':
135 if ($value['entity'] == 'civicrm_activity') {
136 $sel1Val = ts('Activity');
138 $sel2[$key] = $valueLabel +
$activityType;
142 if ($value['entity'] == 'civicrm_participant') {
143 $sel1Val = ts('Event Type');
145 $sel2[$key] = $valueLabel +
$eventType;
148 case 'event_template':
149 if ($value['entity'] == 'civicrm_participant') {
150 $sel1Val = ts('Event Template');
152 $sel2[$key] = $valueLabel +
$eventTemplate;
155 case 'civicrm_event':
156 if ($value['entity'] == 'civicrm_participant') {
157 $sel1Val = ts('Event Name');
159 $sel2[$key] = $valueLabel +
$event;
162 case 'civicrm_membership_type':
163 if ($value['entity'] == 'civicrm_membership') {
164 $sel1Val = ts('Membership');
166 $sel2[$key] = $valueLabel +
$membershipType;
169 case 'civicrm_contact':
170 if ($value['entity'] == 'civicrm_contact') {
171 $sel1Val = ts('Contact');
173 $sel2[$key] = $dateFields;
176 $sel1[$key] = $sel1Val;
179 if ($startDate = CRM_Utils_Array
::value('entity_date_start', $value)) {
180 $sel4[$startDate] = ucwords(str_replace('_', ' ', $startDate));
182 if ($endDate = CRM_Utils_Array
::value('entity_date_end', $value)) {
183 $sel4[$endDate] = ucwords(str_replace('_', ' ', $endDate));
186 switch ($entityRecipient) {
187 case 'activity_contacts':
188 $activityContacts = CRM_Core_OptionGroup
::values('activity_contacts');
189 $sel5[$entityRecipient] = $activityContacts +
$options;
190 $recipientMapping +
= CRM_Core_OptionGroup
::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
193 case 'event_contacts':
194 $eventContacts = CRM_Core_OptionGroup
::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'label', TRUE, FALSE, 'name');
195 $sel5[$entityRecipient] = $eventContacts +
$options;
196 $recipientMapping +
= CRM_Core_OptionGroup
::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'name', TRUE, FALSE, 'name');
200 $sel5[$entityRecipient] = $options;
207 foreach ($mapping as $value) {
208 $entityStatus = CRM_Utils_Array
::value('entity_status', $value);
209 $statusLabel = array('- ' . strtolower(CRM_Utils_Array
::value('entity_status_label', $value)) . ' -');
210 $id = CRM_Utils_Array
::value('id', $value);
212 switch ($entityStatus) {
213 case 'activity_status':
214 foreach ($sel3[$id] as $kkey => & $vval) {
215 $vval = $statusLabel +
$activityStatus;
219 case 'civicrm_participant_status_type':
220 foreach ($sel3[$id] as $kkey => & $vval) {
221 $vval = $statusLabel +
$participantStatus;
225 case 'auto_renew_options':
226 foreach ($sel3[$id] as $kkey => & $vval) {
229 $auto = CRM_Core_DAO
::getFieldValue('CRM_Member_DAO_MembershipType', $kkey, 'auto_renew');
232 $vval = $statusLabel +
$autoRenew;
235 $vval = $statusLabel;
240 case 'contact_date_reminder_options':
241 foreach ($sel3[$id] as $kkey => & $vval) {
242 $vval = $contactOptions;
258 'entityMapping' => $entityMapping,
259 'recipientMapping' => $recipientMapping,
268 public static function getSelection1($id = NULL) {
269 $mapping = self
::getMapping($id);
270 $sel4 = $sel5 = array();
272 'manual' => ts('Choose Recipient(s)'),
273 'group' => ts('Select Group'),
276 $recipientMapping = array_combine(array_keys($options), array_keys($options));
278 foreach ($mapping as $value) {
279 $entityRecipient = CRM_Utils_Array
::value('entity_recipient', $value);
280 $key = CRM_Utils_Array
::value('id', $value);
282 if ($startDate = CRM_Utils_Array
::value('entity_date_start', $value)) {
283 $sel4[$startDate] = ucwords(str_replace('_', ' ', $startDate));
285 if ($endDate = CRM_Utils_Array
::value('entity_date_end', $value)) {
286 $sel4[$endDate] = ucwords(str_replace('_', ' ', $endDate));
289 switch ($entityRecipient) {
290 case 'activity_contacts':
291 $activityContacts = CRM_Core_OptionGroup
::values('activity_contacts');
292 $sel5[$id] = $activityContacts +
$options;
293 $recipientMapping +
= CRM_Core_OptionGroup
::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
296 case 'event_contacts':
297 $eventContacts = CRM_Core_OptionGroup
::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'label', TRUE, FALSE, 'name');
298 $sel5[$id] = $eventContacts +
$options;
299 $recipientMapping +
= CRM_Core_OptionGroup
::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'name', TRUE, FALSE, 'name');
303 $sel5[$id] = $options;
310 'sel5' => $sel5[$id],
311 'recipientMapping' => $recipientMapping,
316 * Retrieve list of Scheduled Reminders
318 * @param bool $namesOnly
319 * Return simple list of names.
321 * @param null $entityValue
325 * (reference) reminder list
327 public static function &getList($namesOnly = FALSE, $entityValue = NULL, $id = NULL) {
328 $activity_type = CRM_Core_PseudoConstant
::activityType(TRUE, TRUE);
329 $activity_status = CRM_Core_PseudoConstant
::activityStatus();
331 $event_type = CRM_Event_PseudoConstant
::eventType();
332 $civicrm_event = CRM_Event_PseudoConstant
::event(NULL, FALSE, "( is_template IS NULL OR is_template != 1 )");
333 $civicrm_participant_status_type = CRM_Event_PseudoConstant
::participantStatus(NULL, NULL, 'label');
334 $event_template = CRM_Event_PseudoConstant
::eventTemplates();
335 $civicrm_contact = self
::getDateFields();
337 $auto_renew_options = CRM_Core_OptionGroup
::values('auto_renew_options');
338 $contact_date_reminder_options = CRM_Core_OptionGroup
::values('contact_date_reminder_options');
339 $civicrm_membership_type = CRM_Member_PseudoConstant
::membershipType();
342 'civicrm_activity' => 'Activity',
343 'civicrm_participant' => 'Event',
344 'civicrm_membership' => 'Member',
345 'civicrm_contact' => 'Contact',
353 cam.entity_value as entityValue,
354 cas.entity_value as entityValueIds,
355 cam.entity_status as entityStatus,
356 cas.entity_status as entityStatusIds,
357 cas.start_action_date as entityDate,
358 cas.start_action_offset,
359 cas.start_action_unit,
360 cas.start_action_condition,
365 FROM civicrm_action_schedule cas
366 LEFT JOIN civicrm_action_mapping cam ON (cam.id = cas.mapping_id)
368 $params = CRM_Core_DAO
::$_nullArray;
369 $where = " WHERE 1 ";
370 if ($entityValue and $id) {
372 AND cas.entity_value = $id AND
373 cam.entity_value = '$entityValue'";
376 1 => array($id, 'Integer'),
377 2 => array($entityValue, 'String'),
380 $where .= " AND cas.used_for IS NULL";
382 $dao = CRM_Core_DAO
::executeQuery($query);
383 while ($dao->fetch()) {
384 $list[$dao->id
]['id'] = $dao->id
;
385 $list[$dao->id
]['title'] = $dao->title
;
386 $list[$dao->id
]['start_action_offset'] = $dao->start_action_offset
;
387 $list[$dao->id
]['start_action_unit'] = $dao->start_action_unit
;
388 $list[$dao->id
]['start_action_condition'] = $dao->start_action_condition
;
389 $list[$dao->id
]['entityDate'] = ucwords(str_replace('_', ' ', $dao->entityDate
));
390 $list[$dao->id
]['absolute_date'] = $dao->absolute_date
;
392 $status = $dao->entityStatus
;
393 $statusArray = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $dao->entityStatusIds
);
394 foreach ($statusArray as & $s) {
395 $s = CRM_Utils_Array
::value($s, $
$status);
397 $statusIds = implode(', ', $statusArray);
399 $value = $dao->entityValue
;
400 $valueArray = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $dao->entityValueIds
);
401 foreach ($valueArray as & $v) {
402 $v = CRM_Utils_Array
::value($v, $
$value);
404 $valueIds = implode(', ', $valueArray);
405 $list[$dao->id
]['entity'] = $entity[$dao->entity
];
406 $list[$dao->id
]['value'] = $valueIds;
407 $list[$dao->id
]['status'] = $statusIds;
408 $list[$dao->id
]['is_repeat'] = $dao->is_repeat
;
409 $list[$dao->id
]['is_active'] = $dao->is_active
;
416 * @param int $contactId
418 * @param int $scheduleID
420 * @param array $tokenParams
423 * @throws CRM_Core_Exception
425 public static function sendReminder($contactId, $to, $scheduleID, $from, $tokenParams) {
426 $email = $to['email'];
427 $phoneNumber = $to['phone'];
428 $schedule = new CRM_Core_DAO_ActionSchedule();
429 $schedule->id
= $scheduleID;
431 $domain = CRM_Core_BAO_Domain
::getDomain();
433 $hookTokens = array();
435 if ($schedule->find(TRUE)) {
436 $body_text = $schedule->body_text
;
437 $body_html = $schedule->body_html
;
438 $sms_body_text = $schedule->sms_body_text
;
439 $body_subject = $schedule->subject
;
441 $body_text = CRM_Utils_String
::htmlToText($body_html);
444 $params = array(array('contact_id', '=', $contactId, 0, 0));
445 list($contact, $_) = CRM_Contact_BAO_Query
::apiQuery($params);
448 $contact = reset($contact);
450 if (!$contact ||
is_a($contact, 'CRM_Core_Error')) {
454 // merge activity tokens with contact array
455 $contact = array_merge($contact, $tokenParams);
458 CRM_Utils_Hook
::tokenValues($contact, $contactId);
460 CRM_Utils_Hook
::tokens($hookTokens);
461 $categories = array_keys($hookTokens);
463 $type = array('body_html' => 'html', 'body_text' => 'text', 'sms_body_text' => 'text');
465 foreach ($type as $bodyType => $value) {
466 $dummy_mail = new CRM_Mailing_BAO_Mailing();
467 if ($bodyType == 'sms_body_text') {
468 $dummy_mail->body_text
= $
$bodyType;
471 $dummy_mail->$bodyType = $
$bodyType;
473 $tokens = $dummy_mail->getTokens();
476 CRM_Utils_Token
::replaceGreetingTokens($
$bodyType, NULL, $contact['contact_id']);
477 $
$bodyType = CRM_Utils_Token
::replaceDomainTokens($
$bodyType, $domain, TRUE, $tokens[$value], TRUE);
478 $
$bodyType = CRM_Utils_Token
::replaceContactTokens($
$bodyType, $contact, FALSE, $tokens[$value], FALSE, TRUE);
479 $
$bodyType = CRM_Utils_Token
::replaceComponentTokens($
$bodyType, $contact, $tokens[$value], TRUE, FALSE);
480 $
$bodyType = CRM_Utils_Token
::replaceHookTokens($
$bodyType, $contact, $categories, TRUE);
485 $sms_text = $sms_body_text;
487 $smarty = CRM_Core_Smarty
::singleton();
493 $
$elem = $smarty->fetch("string:{$$elem}");
496 //@todo - this next section is a duplicate of function CRM_Utils_Token::getTokens
498 preg_match_all('/(?<!\{|\\\\)\{(\w+\.\w+)\}(?!\})/',
504 $subjectToken = NULL;
506 foreach ($matches[1] as $token) {
507 list($type, $name) = preg_split('/\./', $token, 2);
509 if (!isset($subjectToken[$type])) {
510 $subjectToken[$type] = array();
512 $subjectToken[$type][] = $name;
517 // @todo this (along with the copy-&-paste chunk above is a commonly repeated chunk of code & should be in a re-usable function
518 $messageSubject = CRM_Utils_Token
::replaceContactTokens($body_subject, $contact, FALSE, $subjectToken);
519 $messageSubject = CRM_Utils_Token
::replaceDomainTokens($messageSubject, $domain, TRUE, $subjectToken);
520 $messageSubject = CRM_Utils_Token
::replaceComponentTokens($messageSubject, $contact, $subjectToken, TRUE);
521 $messageSubject = CRM_Utils_Token
::replaceHookTokens($messageSubject, $contact, $categories, TRUE);
523 $messageSubject = $smarty->fetch("string:{$messageSubject}");
525 if ($schedule->mode
== 'SMS' or $schedule->mode
== 'User_Preference') {
526 $session = CRM_Core_Session
::singleton();
527 $userID = $session->get('userID') ?
$session->get('userID') : $contactId;
529 'To' => $phoneNumber,
530 'provider_id' => $schedule->sms_provider_id
,
531 'activity_subject' => $messageSubject,
533 $activityTypeID = CRM_Core_OptionGroup
::getValue('activity_type',
537 $activityParams = array(
538 'source_contact_id' => $userID,
539 'activity_type_id' => $activityTypeID,
540 'activity_date_time' => date('YmdHis'),
541 'subject' => $messageSubject,
542 'details' => $sms_text,
543 'status_id' => CRM_Core_OptionGroup
::getValue('activity_status', 'Completed', 'name'),
546 $activity = CRM_Activity_BAO_Activity
::create($activityParams);
548 CRM_Activity_BAO_Activity
::sendSMSMessage($contactId,
556 if ($schedule->mode
== 'Email' or $schedule->mode
== 'User_Preference') {
557 // set up the parameters for CRM_Utils_Mail::send
559 'groupName' => 'Scheduled Reminder Sender',
561 'toName' => $contact['display_name'],
563 'subject' => $messageSubject,
564 'entity' => 'action_schedule',
565 'entity_id' => $scheduleID,
568 if (!$html ||
$contact['preferred_mail_format'] == 'Text' ||
569 $contact['preferred_mail_format'] == 'Both'
571 // render the & entities in text mode, so that the links work
572 $mailParams['text'] = str_replace('&', '&', $text);
574 if ($html && ($contact['preferred_mail_format'] == 'HTML' ||
575 $contact['preferred_mail_format'] == 'Both'
578 $mailParams['html'] = $html;
580 $result = CRM_Utils_Mail
::send($mailParams);
589 * Add the schedules reminders in the db
591 * @param array $params
592 * (reference ) an assoc array of name/value pairs.
594 * The array that holds all the db ids.
596 * @return CRM_Core_DAO_ActionSchedule
598 public static function add(&$params, $ids = array()) {
599 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
600 $actionSchedule->copyValues($params);
602 return $actionSchedule->save();
606 * Takes a bunch of params that are needed to match certain criteria and
607 * retrieves the relevant objects. It also stores all the retrieved
608 * values in the default array
610 * @param array $params
611 * (reference ) an assoc array of name/value pairs.
612 * @param array $values
613 * (reference ) an assoc array to hold the flattened values.
615 * @return CRM_Core_DAO_ActionSchedule|null
616 * object on success, null otherwise
618 public static function retrieve(&$params, &$values) {
619 if (empty($params)) {
622 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
624 $actionSchedule->copyValues($params);
626 if ($actionSchedule->find(TRUE)) {
627 $ids['actionSchedule'] = $actionSchedule->id
;
629 CRM_Core_DAO
::storeValues($actionSchedule, $values);
631 return $actionSchedule;
640 * ID of the Reminder to be deleted.
643 public static function del($id) {
645 $dao = new CRM_Core_DAO_ActionSchedule();
647 if ($dao->find(TRUE)) {
652 CRM_Core_Error
::fatal(ts('Invalid value passed to delete function.'));
656 * Update the is_active flag in the db
659 * Id of the database record.
660 * @param bool $is_active
661 * Value we want to set the is_active field.
664 * DAO object on success, null otherwise
666 public static function setIsActive($id, $is_active) {
667 return CRM_Core_DAO
::setFieldValue('CRM_Core_DAO_ActionSchedule', $id, 'is_active', $is_active);
671 * @param int $mappingID
674 * @throws CRM_Core_Exception
676 public static function sendMailings($mappingID, $now) {
677 $domainValues = CRM_Core_BAO_Domain
::getNameAndEmail();
678 $fromEmailAddress = "$domainValues[0] <$domainValues[1]>";
680 $mapping = new CRM_Core_DAO_ActionMapping();
681 $mapping->id
= $mappingID;
682 $mapping->find(TRUE);
684 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
685 $actionSchedule->mapping_id
= $mappingID;
686 $actionSchedule->is_active
= 1;
687 $actionSchedule->find(FALSE);
689 $tokenFields = array();
690 $session = CRM_Core_Session
::singleton();
692 while ($actionSchedule->fetch()) {
693 $extraSelect = $extraJoin = $extraWhere = $extraOn = '';
695 if ($actionSchedule->from_email
) {
696 $fromEmailAddress = "$actionSchedule->from_name <$actionSchedule->from_email>";
699 $activityTypeID = FALSE;
700 $activityStatusID = FALSE;
701 if ($actionSchedule->record_activity
) {
702 if ($mapping->entity
== 'civicrm_membership') {
704 = CRM_Core_OptionGroup
::getValue('activity_type', 'Membership Renewal Reminder', 'name');
708 = CRM_Core_OptionGroup
::getValue('activity_type', 'Reminder Sent', 'name');
712 = CRM_Core_OptionGroup
::getValue('activity_status', 'Completed', 'name');
715 if ($mapping->entity
== 'civicrm_activity') {
716 $compInfo = CRM_Core_Component
::getEnabledComponents();
717 $tokenEntity = 'activity';
718 $tokenFields = array('activity_id', 'activity_type', 'subject', 'details', 'activity_date_time');
719 $extraSelect = ', ov.label as activity_type, e.id as activity_id';
721 INNER JOIN civicrm_option_group og ON og.name = 'activity_type'
722 INNER JOIN civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id";
723 $extraOn = ' AND e.is_current_revision = 1 AND e.is_deleted = 0 ';
724 if ($actionSchedule->limit_to
== 0) {
726 LEFT JOIN civicrm_option_group og ON og.name = 'activity_type'
727 LEFT JOIN civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id";
731 // if CiviCase component is enabled
732 if (array_key_exists('CiviCase', $compInfo)) {
733 $extraSelect .= ", civicrm_case_activity.case_id as case_id";
735 LEFT JOIN `civicrm_case_activity` ON `e`.`id` = `civicrm_case_activity`.`activity_id`";
739 if ($mapping->entity
== 'civicrm_participant') {
740 $tokenEntity = 'event';
741 $tokenFields = array(
757 $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 ';
760 INNER JOIN civicrm_event ev ON e.event_id = ev.id
761 INNER JOIN civicrm_option_group og ON og.name = 'event_type'
762 INNER JOIN civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id
763 LEFT JOIN civicrm_loc_block lb ON lb.id = ev.loc_block_id
764 LEFT JOIN civicrm_address address ON address.id = lb.address_id
765 LEFT JOIN civicrm_email email ON email.id = lb.email_id
766 LEFT JOIN civicrm_phone phone ON phone.id = lb.phone_id
768 if ($actionSchedule->limit_to
== 0) {
770 LEFT JOIN civicrm_event ev ON e.event_id = ev.id
771 LEFT JOIN civicrm_option_group og ON og.name = 'event_type'
772 LEFT JOIN civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id
773 LEFT JOIN civicrm_loc_block lb ON lb.id = ev.loc_block_id
774 LEFT JOIN civicrm_address address ON address.id = lb.address_id
775 LEFT JOIN civicrm_email email ON email.id = lb.email_id
776 LEFT JOIN civicrm_phone phone ON phone.id = lb.phone_id
781 if ($mapping->entity
== 'civicrm_membership') {
782 $tokenEntity = 'membership';
783 $tokenFields = array('fee', 'id', 'join_date', 'start_date', 'end_date', 'status', 'type');
784 $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';
786 INNER JOIN civicrm_membership_type mt ON e.membership_type_id = mt.id
787 INNER JOIN civicrm_membership_status ms ON e.status_id = ms.id';
789 if ($actionSchedule->limit_to
== 0) {
791 LEFT JOIN civicrm_membership_type mt ON e.membership_type_id = mt.id
792 LEFT JOIN civicrm_membership_status ms ON e.status_id = ms.id';
796 if ($mapping->entity
== 'civicrm_contact') {
797 $tokenEntity = 'contact';
798 //TODO: get full list somewhere!
799 $tokenFields = array('birth_date', 'last_name');
800 //TODO: is there anything to add here?
803 $entityJoinClause = "INNER JOIN {$mapping->entity} e ON e.id = reminder.entity_id";
804 if ($actionSchedule->limit_to
== 0) {
805 $entityJoinClause = "LEFT JOIN {$mapping->entity} e ON e.id = reminder.entity_id";
806 $extraWhere .= " AND (e.id = reminder.entity_id OR reminder.entity_table = 'civicrm_contact')";
808 $entityJoinClause .= $extraOn;
811 SELECT reminder.id as reminderID, reminder.contact_id as contactID, reminder.entity_table as entityTable, reminder.*, e.id as entityID, e.* {$extraSelect}
812 FROM civicrm_action_log reminder
815 WHERE reminder.action_schedule_id = %1 AND reminder.action_date_time IS NULL
818 $dao = CRM_Core_DAO
::executeQuery($query,
819 array(1 => array($actionSchedule->id
, 'Integer'))
822 while ($dao->fetch()) {
823 $entityTokenParams = array();
824 foreach ($tokenFields as $field) {
825 if ($field == 'location') {
827 $stateProvince = CRM_Core_PseudoConstant
::stateProvince();
828 $loc['street_address'] = $dao->street_address
;
829 $loc['city'] = $dao->city
;
830 $loc['state_province'] = CRM_Utils_Array
::value($dao->state_province_id
, $stateProvince);
831 $loc['postal_code'] = $dao->postal_code
;
832 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_Address
::format($loc);
834 elseif ($field == 'info_url') {
835 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_System
::url('civicrm/event/info', 'reset=1&id=' . $dao->event_id
, TRUE, NULL, FALSE);
837 elseif ($field == 'registration_url') {
838 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_System
::url('civicrm/event/register', 'reset=1&id=' . $dao->event_id
, TRUE, NULL, FALSE);
840 elseif (in_array($field, array('start_date', 'end_date', 'join_date', 'activity_date_time'))) {
841 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_Date
::customFormat($dao->$field);
843 elseif ($field == 'balance') {
844 if ($dao->entityTable
== 'civicrm_contact') {
847 elseif (!empty($dao->entityID
)) {
848 $info = CRM_Contribute_BAO_Contribution
::getPaymentInfo($dao->entityID
, 'event');
849 $balancePay = CRM_Utils_Array
::value('balance', $info);
850 $balancePay = CRM_Utils_Money
::format($balancePay);
852 $entityTokenParams["{$tokenEntity}." . $field] = $balancePay;
854 elseif ($field == 'fee_amount') {
855 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_Money
::format($dao->$field);
858 $entityTokenParams["{$tokenEntity}." . $field] = $dao->$field;
863 $errorMsg = $toEmail = $toPhoneNumber = '';
865 if ($actionSchedule->mode
== 'SMS' or $actionSchedule->mode
== 'User_Preference') {
866 $filters = array('is_deceased' => 0, 'is_deleted' => 0, 'do_not_sms' => 0);
867 $toPhoneNumbers = CRM_Core_BAO_Phone
::allPhones($dao->contactID
, FALSE, 'Mobile', $filters);
868 //to get primary mobile ph,if not get a first mobile phONE
869 if (!empty($toPhoneNumbers)) {
870 $toPhoneNumberDetails = reset($toPhoneNumbers);
871 $toPhoneNumber = CRM_Utils_Array
::value('phone', $toPhoneNumberDetails);
872 //contact allows to send sms
876 if ($actionSchedule->mode
== 'Email' or $actionSchedule->mode
== 'User_Preference') {
877 $toEmail = CRM_Contact_BAO_Contact
::getPrimaryEmail($dao->contactID
);
879 if ($toEmail ||
!(empty($toPhoneNumber) or $toDoNotSms)) {
880 $to['email'] = $toEmail;
881 $to['phone'] = $toPhoneNumber;
883 = CRM_Core_BAO_ActionSchedule
::sendReminder(
891 if (!$result ||
is_a($result, 'PEAR_Error')) {
892 // we could not send an email, for now we ignore, CRM-3406
898 $errorMsg = "Couldn\'t find recipient\'s email address.";
901 // update action log record
903 'id' => $dao->reminderID
,
904 'is_error' => $isError,
905 'message' => $errorMsg ?
$errorMsg : "null",
906 'action_date_time' => $now,
908 CRM_Core_BAO_ActionLog
::create($logParams);
910 // insert activity log record if needed
911 if ($actionSchedule->record_activity
&& !$isError) {
912 $activityParams = array(
913 'subject' => $actionSchedule->title
,
914 'details' => $actionSchedule->body_html
,
915 'source_contact_id' => $session->get('userID') ?
$session->get('userID') : $dao->contactID
,
916 'target_contact_id' => $dao->contactID
,
917 'activity_date_time' => date('YmdHis'),
918 'status_id' => $activityStatusID,
919 'activity_type_id' => $activityTypeID,
920 'source_record_id' => $dao->entityID
,
922 $activity = CRM_Activity_BAO_Activity
::create($activityParams);
924 //file reminder on case if source activity is a case activity
925 if (!empty($dao->case_id
)) {
926 $caseActivityParams = array();
927 $caseActivityParams['case_id'] = $dao->case_id
;
928 $caseActivityParams['activity_id'] = $activity->id
;
929 CRM_Case_BAO_Case
::processCaseActivity($caseActivityParams);
939 * @param int $mappingID
941 * @param array $params
943 * @throws API_Exception
945 public static function buildRecipientContacts($mappingID, $now, $params = array()) {
946 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
947 $actionSchedule->mapping_id
= $mappingID;
948 $actionSchedule->is_active
= 1;
949 if (!empty($params)) {
950 _civicrm_api3_dao_set_filter($actionSchedule, $params, FALSE, 'ActionSchedule');
952 $actionSchedule->find();
954 while ($actionSchedule->fetch()) {
955 $mapping = new CRM_Core_DAO_ActionMapping();
956 $mapping->id
= $mappingID;
957 $mapping->find(TRUE);
959 // note: $where - this filtering applies for both
960 // 'limit to' and 'addition to' options
961 // $limitWhere - this filtering applies only for
963 $select = $join = $where = $limitWhere = array();
964 $limitTo = $actionSchedule->limit_to
;
965 $value = explode(CRM_Core_DAO
::VALUE_SEPARATOR
,
966 trim($actionSchedule->entity_value
, CRM_Core_DAO
::VALUE_SEPARATOR
)
968 $value = implode(',', $value);
970 $status = explode(CRM_Core_DAO
::VALUE_SEPARATOR
,
971 trim($actionSchedule->entity_status
, CRM_Core_DAO
::VALUE_SEPARATOR
)
973 $status = implode(',', $status);
975 $anniversary = FALSE;
977 if (!CRM_Utils_System
::isNull($mapping->entity_recipient
)) {
978 if ($mapping->entity_recipient
== 'event_contacts') {
979 $recipientOptions = CRM_Core_OptionGroup
::values($mapping->entity_recipient
, FALSE, FALSE, FALSE, NULL, 'name', TRUE, FALSE, 'name');
982 $recipientOptions = CRM_Core_OptionGroup
::values($mapping->entity_recipient
, FALSE, FALSE, FALSE, NULL, 'name');
985 $from = "{$mapping->entity} e";
987 if ($mapping->entity
== 'civicrm_activity') {
988 $contactField = 'r.contact_id';
989 $table = 'civicrm_activity e';
990 $activityContacts = CRM_Core_OptionGroup
::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
991 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
992 $assigneeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
993 $targetID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
995 if (!is_null($limitTo)) {
997 // including the activity target contacts if 'in addition' is defined
998 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$targetID}";
1001 switch (CRM_Utils_Array
::value($actionSchedule->recipient
, $recipientOptions)) {
1002 case 'Activity Assignees':
1003 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$assigneeID}";
1006 case 'Activity Source':
1007 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$sourceID}";
1011 case 'Activity Targets':
1012 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$targetID}";
1017 // build where clause
1018 if (!empty($value)) {
1019 $where[] = "e.activity_type_id IN ({$value})";
1022 $where[] = "e.activity_type_id IS NULL";
1024 if (!empty($status)) {
1025 $where[] = "e.status_id IN ({$status})";
1027 $where[] = ' e.is_current_revision = 1 ';
1028 $where[] = ' e.is_deleted = 0 ';
1030 $dateField = 'e.activity_date_time';
1033 if ($mapping->entity
== 'civicrm_participant') {
1034 $table = 'civicrm_event r';
1035 $contactField = 'e.contact_id';
1036 $join[] = 'INNER JOIN civicrm_event r ON e.event_id = r.id';
1037 if ($actionSchedule->recipient_listing
&& $limitTo) {
1038 $rList = explode(CRM_Core_DAO
::VALUE_SEPARATOR
,
1039 trim($actionSchedule->recipient_listing
, CRM_Core_DAO
::VALUE_SEPARATOR
)
1041 $rList = implode(',', $rList);
1043 switch ($recipientOptions[$actionSchedule->recipient
]) {
1044 case 'participant_role':
1045 $where[] = "e.role_id IN ({$rList})";
1053 // build where clause
1054 if (!empty($value)) {
1055 $where[] = ($mapping->entity_value
== 'event_type') ?
"r.event_type_id IN ({$value})" : "r.id IN ({$value})";
1058 $where[] = ($mapping->entity_value
== 'event_type') ?
"r.event_type_id IS NULL" : "r.id IS NULL";
1061 // participant status criteria not to be implemented
1062 // for additional recipients
1063 if (!empty($status)) {
1064 $limitWhere[] = "e.status_id IN ({$status})";
1067 $where[] = 'r.is_active = 1';
1068 $where[] = 'r.is_template = 0';
1069 $dateField = str_replace('event_', 'r.', $actionSchedule->start_action_date
);
1073 if ($mapping->entity
== 'civicrm_membership') {
1074 $contactField = 'e.contact_id';
1075 $table = 'civicrm_membership e';
1076 // build where clause
1078 //auto-renew memberships
1079 $where[] = "e.contribution_recur_id IS NOT NULL ";
1081 elseif ($status == 1) {
1082 $where[] = "e.contribution_recur_id IS NULL ";
1085 // build where clause
1086 if (!empty($value)) {
1087 $where[] = "e.membership_type_id IN ({$value})";
1090 $where[] = "e.membership_type_id IS NULL";
1093 $where[] = "( e.is_override IS NULL OR e.is_override = 0 )";
1094 $dateField = str_replace('membership_', 'e.', $actionSchedule->start_action_date
);
1095 $notINClause = self
::permissionedRelationships($contactField);
1097 $membershipStatus = CRM_Member_PseudoConstant
::membershipStatus(NULL, "(is_current_member = 1 OR name = 'Expired')", 'id');
1098 $mStatus = implode(',', $membershipStatus);
1099 $where[] = "e.status_id IN ({$mStatus})";
1102 if ($mapping->entity
== 'civicrm_contact') {
1103 if ($value == 'birth_date') {
1104 $dateDBField = 'birth_date';
1105 $table = 'civicrm_contact e';
1106 $contactField = 'e.id';
1107 $where[] = 'e.is_deleted = 0';
1108 $where[] = 'e.is_deceased = 0';
1112 $customFieldParams = array('id' => substr($value, 7));
1113 $customGroup = $customField = array();
1114 CRM_Core_BAO_CustomField
::retrieve($customFieldParams, $customField);
1115 $dateDBField = $customField['column_name'];
1116 $customGroupParams = array('id' => $customField['custom_group_id'], $customGroup);
1117 CRM_Core_BAO_CustomGroup
::retrieve($customGroupParams, $customGroup);
1118 $from = $table = "{$customGroup['table_name']} e";
1119 $contactField = 'e.entity_id';
1120 $where[] = '1'; // possible to have no "where" in this case
1123 $status_ = explode(',', $status);
1124 if (in_array(2, $status_)) {
1125 // anniversary mode:
1126 $dateField = 'DATE_ADD(e.' . $dateDBField . ', INTERVAL ROUND(DATEDIFF(DATE(' . $now . '), e.' . $dateDBField . ') / 365) YEAR)';
1127 $anniversary = TRUE;
1131 $dateField = 'e.' . $dateDBField;
1133 // TODO get this working
1135 // TODO: Make sure everything's provided for repetition, etc.
1138 // CRM-13577 Introduce Smart Groups Handling
1139 if ($actionSchedule->group_id
) {
1141 // Need to check if its a smart group or not
1142 // Then decide which table to join onto the query
1143 $group = CRM_Contact_DAO_Group
::getTableName();
1145 // Get the group information
1147 SELECT $group.id, $group.cache_date, $group.saved_search_id, $group.children
1149 WHERE $group.id = {$actionSchedule->group_id}
1152 $groupDAO = CRM_Core_DAO
::executeQuery($sql);
1153 $isSmartGroup = FALSE;
1155 $groupDAO->fetch() &&
1156 !empty($groupDAO->saved_search_id
)
1158 // Check that the group is in place in the cache and up to date
1159 CRM_Contact_BAO_GroupContactCache
::check($actionSchedule->group_id
);
1160 // Set smart group flag
1161 $isSmartGroup = TRUE;
1164 // CRM-13577 End Introduce Smart Groups Handling
1167 if ($actionSchedule->group_id
) {
1168 // CRM-13577 If smart group then use Cache table
1169 if ($isSmartGroup) {
1170 $join[] = "INNER JOIN civicrm_group_contact_cache grp ON {$contactField} = grp.contact_id";
1171 $where[] = "grp.group_id IN ({$actionSchedule->group_id})";
1174 $join[] = "INNER JOIN civicrm_group_contact grp ON {$contactField} = grp.contact_id AND grp.status = 'Added'";
1175 $where[] = "grp.group_id IN ({$actionSchedule->group_id})";
1178 elseif (!empty($actionSchedule->recipient_manual
)) {
1179 $rList = CRM_Utils_Type
::escape($actionSchedule->recipient_manual
, 'String');
1180 $where[] = "{$contactField} IN ({$rList})";
1183 elseif (!is_null($limitTo)) {
1184 $addGroup = $addWhere = '';
1185 if ($actionSchedule->group_id
) {
1186 // CRM-13577 If smart group then use Cache table
1187 if ($isSmartGroup) {
1188 $addGroup = " INNER JOIN civicrm_group_contact_cache grp ON c.id = grp.contact_id";
1189 $addWhere = " grp.group_id IN ({$actionSchedule->group_id})";
1192 $addGroup = " INNER JOIN civicrm_group_contact grp ON c.id = grp.contact_id AND grp.status = 'Added'";
1193 $addWhere = " grp.group_id IN ({$actionSchedule->group_id})";
1196 if (!empty($actionSchedule->recipient_manual
)) {
1197 $rList = CRM_Utils_Type
::escape($actionSchedule->recipient_manual
, 'String');
1198 $addWhere = "c.id IN ({$rList})";
1202 $select[] = "{$contactField} as contact_id";
1203 $select[] = 'e.id as entity_id';
1204 $select[] = "'{$mapping->entity}' as entity_table";
1205 $select[] = "{$actionSchedule->id} as action_schedule_id";
1206 $reminderJoinClause = "civicrm_action_log reminder ON reminder.contact_id = {$contactField} AND
1207 reminder.entity_id = e.id AND
1208 reminder.entity_table = '{$mapping->entity}' AND
1209 reminder.action_schedule_id = %1";
1212 // only consider reminders less than 11 months ago
1213 $reminderJoinClause .= " AND reminder.action_date_time > DATE_SUB({$now}, INTERVAL 11 MONTH)";
1216 if ($table != 'civicrm_contact e') {
1217 $join[] = "INNER JOIN civicrm_contact c ON c.id = {$contactField} AND c.is_deleted = 0 AND c.is_deceased = 0 ";
1220 if ($actionSchedule->start_action_date
) {
1221 $startDateClause = array();
1222 $op = ($actionSchedule->start_action_condition
== 'before' ?
'<=' : '>=');
1223 $operator = ($actionSchedule->start_action_condition
== 'before' ?
'DATE_SUB' : 'DATE_ADD');
1224 $date = $operator . "({$dateField}, INTERVAL {$actionSchedule->start_action_offset} {$actionSchedule->start_action_unit})";
1225 $startDateClause[] = "'{$now}' >= {$date}";
1226 if ($mapping->entity
== 'civicrm_participant') {
1227 $startDateClause[] = $operator . "({$now}, INTERVAL 1 DAY ) {$op} " . $dateField;
1230 $startDateClause[] = "DATE_SUB({$now}, INTERVAL 1 DAY ) <= {$date}";
1233 $startDate = implode(' AND ', $startDateClause);
1235 elseif ($actionSchedule->absolute_date
) {
1236 $startDate = "DATEDIFF(DATE('{$now}'),'{$actionSchedule->absolute_date}') = 0";
1239 // ( now >= date_built_from_start_time ) OR ( now = absolute_date )
1240 $dateClause = "reminder.id IS NULL AND {$startDate}";
1242 // start composing query
1243 $selectClause = 'SELECT ' . implode(', ', $select);
1244 $fromClause = "FROM $from";
1245 $joinClause = !empty($join) ?
implode(' ', $join) : '';
1246 $whereClause = 'WHERE ' . implode(' AND ', $where);
1247 $limitWhereClause = '';
1248 if (!empty($limitWhere)) {
1249 $limitWhereClause = ' AND ' . implode(' AND ', $limitWhere);
1253 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
1257 LEFT JOIN {$reminderJoinClause}
1258 {$whereClause} {$limitWhereClause} AND {$dateClause} {$notINClause}
1260 CRM_Core_DAO
::executeQuery($query, array(1 => array($actionSchedule->id
, 'Integer')));
1261 $isSendToAdditionalContacts = (!is_null($limitTo) && $limitTo == 0 && (!empty($addGroup) ||
!empty($addWhere))) ?
TRUE : FALSE;
1262 if ($isSendToAdditionalContacts) {
1263 $contactTable = "civicrm_contact c";
1264 $addSelect = "SELECT c.id as contact_id, c.id as entity_id, 'civicrm_contact' as entity_table, {$actionSchedule->id} as action_schedule_id";
1265 $additionReminderClause = "civicrm_action_log reminder ON reminder.contact_id = c.id AND
1266 reminder.entity_id = c.id AND
1267 reminder.entity_table = 'civicrm_contact' AND
1268 reminder.action_schedule_id = {$actionSchedule->id}";
1269 $addWhereClause = '';
1271 $addWhereClause = "AND {$addWhere}";
1273 $insertAdditionalSql = "
1274 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
1276 FROM ({$contactTable})
1277 LEFT JOIN {$additionReminderClause}
1279 WHERE c.is_deleted = 0 AND c.is_deceased = 0
1283 SELECT rem.contact_id
1284 FROM civicrm_action_log rem INNER JOIN {$mapping->entity} e ON rem.entity_id = e.id
1285 WHERE rem.action_schedule_id = {$actionSchedule->id}
1286 AND rem.entity_table = '{$mapping->entity}'
1290 CRM_Core_DAO
::executeQuery($insertAdditionalSql);
1292 // if repeat is turned ON:
1293 if ($actionSchedule->is_repeat
) {
1294 $repeatEvent = ($actionSchedule->end_action
== 'before' ?
'DATE_SUB' : 'DATE_ADD') . "({$dateField}, INTERVAL {$actionSchedule->end_frequency_interval} {$actionSchedule->end_frequency_unit})";
1296 if ($actionSchedule->repetition_frequency_unit
== 'day') {
1297 $hrs = 24 * $actionSchedule->repetition_frequency_interval
;
1299 elseif ($actionSchedule->repetition_frequency_unit
== 'week') {
1300 $hrs = 24 * $actionSchedule->repetition_frequency_interval
* 7;
1302 elseif ($actionSchedule->repetition_frequency_unit
== 'month') {
1303 $hrs = "24*(DATEDIFF(DATE_ADD(latest_log_time, INTERVAL 1 MONTH ), latest_log_time))";
1305 elseif ($actionSchedule->repetition_frequency_unit
== 'year') {
1306 $hrs = "24*(DATEDIFF(DATE_ADD(latest_log_time, INTERVAL 1 YEAR ), latest_log_time))";
1309 $hrs = $actionSchedule->repetition_frequency_interval
;
1312 // (now <= repeat_end_time )
1313 $repeatEventClause = "'{$now}' <= {$repeatEvent}";
1314 // diff(now && logged_date_time) >= repeat_interval
1315 $havingClause = "HAVING TIMEDIFF({$now}, latest_log_time) >= TIME('{$hrs}:00:00')";
1316 $groupByClause = 'GROUP BY reminder.contact_id, reminder.entity_id, reminder.entity_table';
1317 $selectClause .= ', MAX(reminder.action_date_time) as latest_log_time';
1318 //CRM-15376 - do not send our reminders if original criteria no longer applies
1319 // the first part of the startDateClause array is the earliest the reminder can be sent. If the
1320 // event (e.g membership_end_date) has changed then the reminder may no longer apply
1321 // @todo - this only handles events that get moved later. Potentially they might get moved earlier
1322 $originalEventStartDateClause = empty($startDateClause) ?
'' : 'AND' . $startDateClause[0];
1323 $sqlInsertValues = "{$selectClause}
1326 INNER JOIN {$reminderJoinClause}
1327 {$whereClause} {$limitWhereClause} AND {$repeatEventClause} {$originalEventStartDateClause} {$notINClause}
1331 $valsqlInsertValues = CRM_Core_DAO
::executeQuery($sqlInsertValues, array(
1333 $actionSchedule->id
,
1339 $arrValues = array();
1340 while ($valsqlInsertValues->fetch()) {
1341 $arrValues[] = "( {$valsqlInsertValues->contact_id}, {$valsqlInsertValues->entity_id}, '{$valsqlInsertValues->entity_table}',{$valsqlInsertValues->action_schedule_id} )";
1344 $valString = implode(',', $arrValues);
1348 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id) VALUES ' . $valString;
1349 CRM_Core_DAO
::executeQuery($query, array(1 => array($actionSchedule->id
, 'Integer')));
1352 if ($isSendToAdditionalContacts) {
1353 $addSelect .= ', MAX(reminder.action_date_time) as latest_log_time';
1354 $sqlEndEventCheck = "
1355 SELECT * FROM {$table}
1356 {$whereClause} AND {$repeatEventClause} LIMIT 1";
1358 $daoCheck = CRM_Core_DAO
::executeQuery($sqlEndEventCheck);
1359 if ($daoCheck->fetch()) {
1360 $valSqlAdditionInsert = "
1362 FROM {$contactTable}
1364 INNER JOIN {$additionReminderClause}
1365 WHERE {$addWhere} AND c.is_deleted = 0 AND c.is_deceased = 0
1366 GROUP BY reminder.contact_id
1369 $daoForVals = CRM_Core_DAO
::executeQuery($valSqlAdditionInsert);
1370 $addValues = array();
1371 while ($daoForVals->fetch()) {
1372 $addValues[] = "( {$daoForVals->contact_id}, {$daoForVals->entity_id}, '{$daoForVals->entity_table}',{$daoForVals->action_schedule_id} )";
1374 $valString = implode(',', $addValues);
1378 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id) VALUES ' . $valString;
1379 CRM_Core_DAO
::executeQuery($query);
1390 * @return null|string
1392 public static function permissionedRelationships($field) {
1394 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
1395 FROM civicrm_membership m
1396 LEFT JOIN civicrm_membership cm ON cm.id = m.owner_membership_id
1397 LEFT JOIN civicrm_membership_type cmt ON cmt.id = m.membership_type_id
1398 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 )
1399 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 ) )
1400 WHERE m.owner_membership_id IS NOT NULL AND
1401 ( rel.is_permission_a_b = 0 OR rel.is_permission_b_a = 0)
1404 $excludeIds = array();
1405 $dao = CRM_Core_DAO
::executeQuery($query, array());
1406 while ($dao->fetch()) {
1407 if ($dao->slave_contact
== $dao->contact_id_a
&& $dao->is_permission_a_b
== 0) {
1408 $excludeIds[] = $dao->slave_contact
;
1410 elseif ($dao->slave_contact
== $dao->contact_id_b
&& $dao->is_permission_b_a
== 0) {
1411 $excludeIds[] = $dao->slave_contact
;
1415 if (!empty($excludeIds)) {
1416 $clause = "AND {$field} NOT IN ( " . implode(', ', $excludeIds) . ' ) ';
1424 * @param array $params
1428 public static function processQueue($now = NULL, $params = array()) {
1429 $now = $now ? CRM_Utils_Time
::setTime($now) : CRM_Utils_Time
::getTime();
1431 $mappings = self
::getMapping();
1432 foreach ($mappings as $mappingID => $mapping) {
1433 self
::buildRecipientContacts($mappingID, $now, $params);
1434 self
::sendMailings($mappingID, $now);
1439 'messages' => ts('Sent all scheduled reminders successfully'),
1446 * @param int $mappingID
1448 * @return null|string
1450 public static function isConfigured($id, $mappingID) {
1451 $queryString = "SELECT count(id) FROM civicrm_action_schedule
1452 WHERE mapping_id = %1 AND
1456 1 => array($mappingID, 'Integer'),
1457 2 => array($id, 'Integer'),
1459 return CRM_Core_DAO
::singleValueQuery($queryString, $params);
1463 * @param int $mappingID
1464 * @param $recipientType
1468 public static function getRecipientListing($mappingID, $recipientType) {
1470 if (!$mappingID ||
!$recipientType) {
1474 $mapping = self
::getMapping($mappingID);
1476 switch ($mapping['entity']) {
1477 case 'civicrm_participant':
1478 $eventContacts = CRM_Core_OptionGroup
::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'name', TRUE, FALSE, 'name');
1479 if (empty($eventContacts[$recipientType])) {
1482 if ($eventContacts[$recipientType] == 'participant_role') {
1483 $options = CRM_Event_PseudoConstant
::participantRole();