3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.3 |
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-2013
38 * This class contains functions for managing Scheduled Reminders
40 class CRM_Core_BAO_ActionSchedule
extends CRM_Core_DAO_ActionSchedule
{
42 static function getMapping($id = NULL) {
43 static $_action_mapping;
45 if ($id && !is_null($_action_mapping) && isset($_action_mapping[$id])) {
46 return $_action_mapping[$id];
49 $dao = new CRM_Core_DAO_ActionMapping();
56 while ($dao->fetch()) {
58 CRM_Core_DAO
::storeValues($dao, $defaults);
59 $mapping[$dao->id
] = $defaults;
61 $_action_mapping = $mapping;
67 * Retrieve list of selections/drop downs for Scheduled Reminder form
69 * @param bool $id mapping id
71 * @return array associated array of all the drop downs in the form
75 static function getSelection($id = NULL) {
76 $mapping = self
::getMapping($id);
77 $activityStatus = CRM_Core_PseudoConstant
::activityStatus();
78 $activityType = CRM_Core_PseudoConstant
::activityType(FALSE) + CRM_Core_PseudoConstant
::activityType(FALSE, TRUE);
80 $participantStatus = CRM_Event_PseudoConstant
::participantStatus(NULL, NULL, 'label');
81 $event = CRM_Event_PseudoConstant
::event(NULL, FALSE, "( is_template IS NULL OR is_template != 1 )");
82 $eventType = CRM_Event_PseudoConstant
::eventType();
83 $eventTemplate = CRM_Event_PseudoConstant
::eventTemplates();
84 $autoRenew = CRM_Core_OptionGroup
::values('auto_renew_options');
85 $membershipType = CRM_Member_PseudoConstant
::membershipType();
89 $sel1 = $sel2 = $sel3 = $sel4 = $sel5 = array();
91 'manual' => ts('Choose Recipient(s)'),
92 'group' => ts('Select a Group'),
95 $entityMapping = array();
96 $recipientMapping = array_combine(array_keys($options), array_keys($options));
102 foreach ($mapping as $value) {
103 $entityValue = CRM_Utils_Array
::value('entity_value', $value);
104 $entityStatus = CRM_Utils_Array
::value('entity_status', $value);
105 $entityRecipient = CRM_Utils_Array
::value('entity_recipient', $value);
106 $valueLabel = array('- ' . strtolower(CRM_Utils_Array
::value('entity_value_label', $value)) . ' -');
107 $key = CRM_Utils_Array
::value('id', $value);
108 $entityMapping[$key] = CRM_Utils_Array
::value('entity', $value);
111 switch ($entityValue) {
112 case 'activity_type':
113 if ($value['entity'] == 'civicrm_activity') {
114 $sel1Val = ts('Activity');
116 $sel2[$key] = $valueLabel +
$activityType;
120 if ($value['entity'] == 'civicrm_participant') {
121 $sel1Val = ts('Event Type');
123 $sel2[$key] = $valueLabel +
$eventType;
126 case 'event_template':
127 if ($value['entity'] == 'civicrm_participant') {
128 $sel1Val = ts('Event Template');
130 $sel2[$key] = $valueLabel +
$eventTemplate;
133 case 'civicrm_event':
134 if ($value['entity'] == 'civicrm_participant') {
135 $sel1Val = ts('Event Name');
137 $sel2[$key] = $valueLabel +
$event;
140 case 'civicrm_membership_type':
141 if ($value['entity'] == 'civicrm_membership') {
142 $sel1Val = ts('Membership');
144 $sel2[$key] = $valueLabel +
$membershipType;
147 $sel1[$key] = $sel1Val;
150 if ($startDate = CRM_Utils_Array
::value('entity_date_start', $value)) {
151 $sel4[$startDate] = ucwords(str_replace('_', ' ', $startDate));
153 if ($endDate = CRM_Utils_Array
::value('entity_date_end', $value)) {
154 $sel4[$endDate] = ucwords(str_replace('_', ' ', $endDate));
157 switch ($entityRecipient) {
158 case 'activity_contacts':
159 $activityContacts = CRM_Core_OptionGroup
::values('activity_contacts');
160 $sel5[$entityRecipient] = $activityContacts +
$options;
161 $recipientMapping +
= CRM_Core_OptionGroup
::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
164 case 'event_contacts':
165 $eventContacts = CRM_Core_OptionGroup
::values('event_contacts');
166 $sel5[$entityRecipient] = $eventContacts +
$options;
167 $recipientMapping +
= CRM_Core_OptionGroup
::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'name');
171 $sel5[$entityRecipient] = $options;
178 foreach ($mapping as $value) {
179 $entityStatus = CRM_Utils_Array
::value('entity_status', $value);
180 $statusLabel = array('- ' . strtolower(CRM_Utils_Array
::value('entity_status_label', $value)) . ' -');
181 $id = CRM_Utils_Array
::value('id', $value);
183 switch ($entityStatus) {
184 case 'activity_status':
185 foreach ($sel3[$id] as $kkey => & $vval) {
186 $vval = $statusLabel +
$activityStatus;
190 case 'civicrm_participant_status_type':
191 foreach ($sel3[$id] as $kkey => & $vval) {
192 $vval = $statusLabel +
$participantStatus;
196 case 'auto_renew_options':
197 foreach ($sel3[$id] as $kkey => & $vval) {
200 $auto = CRM_Core_DAO
::getFieldValue('CRM_Member_DAO_MembershipType', $kkey, 'auto_renew');
203 $vval = $statusLabel +
$autoRenew;
206 $vval = $statusLabel;
224 'entityMapping' => $entityMapping,
225 'recipientMapping' => $recipientMapping,
229 static function getSelection1($id = NULL) {
230 $mapping = self
::getMapping($id);
231 $sel4 = $sel5 = array();
233 'manual' => ts('Choose Recipient(s)'),
234 'group' => ts('Select a Group'),
237 $recipientMapping = array_combine(array_keys($options), array_keys($options));
239 foreach ($mapping as $value) {
240 $entityRecipient = CRM_Utils_Array
::value('entity_recipient', $value);
241 $key = CRM_Utils_Array
::value('id', $value);
243 if ($startDate = CRM_Utils_Array
::value('entity_date_start', $value)) {
244 $sel4[$startDate] = ucwords(str_replace('_', ' ', $startDate));
246 if ($endDate = CRM_Utils_Array
::value('entity_date_end', $value)) {
247 $sel4[$endDate] = ucwords(str_replace('_', ' ', $endDate));
250 switch ($entityRecipient) {
251 case 'activity_contacts':
252 $activityContacts = CRM_Core_OptionGroup
::values('activity_contacts');
253 $sel5[$id] = $activityContacts +
$options;
254 $recipientMapping +
= CRM_Core_OptionGroup
::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
257 case 'event_contacts':
258 $eventContacts = CRM_Core_OptionGroup
::values('event_contacts');
259 $sel5[$id] = $eventContacts +
$options;
260 $recipientMapping +
= CRM_Core_OptionGroup
::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'name');
264 $sel5[$id] = $options;
271 'sel5' => $sel5[$id],
272 'recipientMapping' => $recipientMapping,
277 * Retrieve list of Scheduled Reminders
279 * @param bool $namesOnly return simple list of names
281 * @return array (reference) reminder list
285 static function &getList($namesOnly = FALSE, $entityValue = NULL, $id = NULL) {
286 $activity_type = CRM_Core_PseudoConstant
::activityType(FALSE) + CRM_Core_PseudoConstant
::activityType(FALSE, TRUE);
287 $activity_status = CRM_Core_PseudoConstant
::activityStatus();
289 $event_type = CRM_Event_PseudoConstant
::eventType();
290 $civicrm_event = CRM_Event_PseudoConstant
::event(NULL, FALSE, "( is_template IS NULL OR is_template != 1 )");
291 $civicrm_participant_status_type = CRM_Event_PseudoConstant
::participantStatus(NULL, NULL, 'label');
292 $event_template = CRM_Event_PseudoConstant
::eventTemplates();
294 $auto_renew_options = CRM_Core_OptionGroup
::values('auto_renew_options');
295 $civicrm_membership_type = CRM_Member_PseudoConstant
::membershipType();
297 asort($activity_type);
299 'civicrm_activity' => 'Activity',
300 'civicrm_participant' => 'Event',
301 'civicrm_membership' => 'Member',
309 cam.entity_value as entityValue,
310 cas.entity_value as entityValueIds,
311 cam.entity_status as entityStatus,
312 cas.entity_status as entityStatusIds,
313 cas.start_action_date as entityDate,
314 cas.start_action_offset,
315 cas.start_action_unit,
316 cas.start_action_condition,
321 FROM civicrm_action_schedule cas
322 LEFT JOIN civicrm_action_mapping cam ON (cam.id = cas.mapping_id)
324 $params = CRM_Core_DAO
::$_nullArray;
326 if ($entityValue and $id) {
328 WHERE cas.entity_value = $id AND
329 cam.entity_value = '$entityValue'";
334 1 => array($id, 'Integer'),
335 2 => array($entityValue, 'String'),
339 $dao = CRM_Core_DAO
::executeQuery($query);
340 while ($dao->fetch()) {
341 $list[$dao->id
]['id'] = $dao->id
;
342 $list[$dao->id
]['title'] = $dao->title
;
343 $list[$dao->id
]['start_action_offset'] = $dao->start_action_offset
;
344 $list[$dao->id
]['start_action_unit'] = $dao->start_action_unit
;
345 $list[$dao->id
]['start_action_condition'] = $dao->start_action_condition
;
346 $list[$dao->id
]['entityDate'] = ucwords(str_replace('_', ' ', $dao->entityDate
));
347 $list[$dao->id
]['absolute_date'] = $dao->absolute_date
;
349 $status = $dao->entityStatus
;
350 $statusArray = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $dao->entityStatusIds
);
351 foreach ($statusArray as & $s) {
352 $s = CRM_Utils_Array
::value($s, $
$status);
354 $statusIds = implode(', ', $statusArray);
356 $value = $dao->entityValue
;
357 $valueArray = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $dao->entityValueIds
);
358 foreach ($valueArray as & $v) {
359 $v = CRM_Utils_Array
::value($v, $
$value);
361 $valueIds = implode(', ', $valueArray);
362 $list[$dao->id
]['entity'] = $entity[$dao->entity
];
363 $list[$dao->id
]['value'] = $valueIds;
364 $list[$dao->id
]['status'] = $statusIds;
365 $list[$dao->id
]['is_repeat'] = $dao->is_repeat
;
366 $list[$dao->id
]['is_active'] = $dao->is_active
;
372 static function sendReminder($contactId, $email, $scheduleID, $from, $tokenParams) {
374 $schedule = new CRM_Core_DAO_ActionSchedule();
375 $schedule->id
= $scheduleID;
377 $domain = CRM_Core_BAO_Domain
::getDomain();
379 $hookTokens = array();
381 if ($schedule->find(TRUE)) {
382 $body_text = $schedule->body_text
;
383 $body_html = $schedule->body_html
;
384 $body_subject = $schedule->subject
;
386 $body_text = CRM_Utils_String
::htmlToText($body_html);
389 $params = array(array('contact_id', '=', $contactId, 0, 0));
390 list($contact, $_) = CRM_Contact_BAO_Query
::apiQuery($params);
393 $contact = reset($contact);
395 if (!$contact ||
is_a($contact, 'CRM_Core_Error')) {
399 // merge activity tokens with contact array
400 $contact = array_merge($contact, $tokenParams);
403 CRM_Utils_Hook
::tokenValues($contact, $contactId);
405 CRM_Utils_Hook
::tokens($hookTokens);
406 $categories = array_keys($hookTokens);
408 $type = array('html', 'text');
410 foreach ($type as $key => $value) {
411 $dummy_mail = new CRM_Mailing_BAO_Mailing();
412 $bodyType = "body_{$value}";
413 $dummy_mail->$bodyType = $
$bodyType;
414 $tokens = $dummy_mail->getTokens();
417 CRM_Utils_Token
::replaceGreetingTokens($
$bodyType, NULL, $contact['contact_id']);
418 $
$bodyType = CRM_Utils_Token
::replaceDomainTokens($
$bodyType, $domain, TRUE, $tokens[$value], TRUE);
419 $
$bodyType = CRM_Utils_Token
::replaceContactTokens($
$bodyType, $contact, FALSE, $tokens[$value], FALSE, TRUE);
420 $
$bodyType = CRM_Utils_Token
::replaceComponentTokens($
$bodyType, $contact, $tokens[$value], TRUE, FALSE);
421 $
$bodyType = CRM_Utils_Token
::replaceHookTokens($
$bodyType, $contact, $categories, TRUE);
427 $smarty = CRM_Core_Smarty
::singleton();
429 'text', 'html') as $elem) {
430 $
$elem = $smarty->fetch("string:{$$elem}");
434 preg_match_all('/(?<!\{|\\\\)\{(\w+\.\w+)\}(?!\})/',
440 $subjectToken = NULL;
442 foreach ($matches[1] as $token) {
443 list($type, $name) = preg_split('/\./', $token, 2);
445 if (!isset($subjectToken['contact'])) {
446 $subjectToken['contact'] = array();
448 $subjectToken['contact'][] = $name;
453 $messageSubject = CRM_Utils_Token
::replaceContactTokens($body_subject, $contact, FALSE, $subjectToken);
454 $messageSubject = CRM_Utils_Token
::replaceDomainTokens($messageSubject, $domain, TRUE, $tokens[$value]);
455 $messageSubject = CRM_Utils_Token
::replaceComponentTokens($messageSubject, $contact, $tokens[$value], TRUE);
456 $messageSubject = CRM_Utils_Token
::replaceHookTokens($messageSubject, $contact, $categories, TRUE);
458 $messageSubject = $smarty->fetch("string:{$messageSubject}");
460 // set up the parameters for CRM_Utils_Mail::send
462 'groupName' => 'Scheduled Reminder Sender',
464 'toName' => $contact['display_name'],
466 'subject' => $messageSubject,
469 if (!$html ||
$contact['preferred_mail_format'] == 'Text' ||
470 $contact['preferred_mail_format'] == 'Both'
472 // render the & entities in text mode, so that the links work
473 $mailParams['text'] = str_replace('&', '&', $text);
475 if ($html && ($contact['preferred_mail_format'] == 'HTML' ||
476 $contact['preferred_mail_format'] == 'Both'
478 $mailParams['html'] = $html;
481 $result = CRM_Utils_Mail
::send($mailParams);
489 * Function to add the schedules reminders in the db
491 * @param array $params (reference ) an assoc array of name/value pairs
492 * @param array $ids the array that holds all the db ids
494 * @return object CRM_Core_DAO_ActionSchedule
499 static function add(&$params, &$ids) {
500 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
501 $actionSchedule->copyValues($params);
503 return $actionSchedule->save();
507 * Takes a bunch of params that are needed to match certain criteria and
508 * retrieves the relevant objects. It also stores all the retrieved
509 * values in the default array
511 * @param array $params (reference ) an assoc array of name/value pairs
512 * @param array $values (reference ) an assoc array to hold the flattened values
514 * @return object CRM_Core_DAO_ActionSchedule object on success, null otherwise
518 static function retrieve(&$params, &$values) {
519 if (empty($params)) {
522 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
524 $actionSchedule->copyValues($params);
526 if ($actionSchedule->find(TRUE)) {
527 $ids['actionSchedule'] = $actionSchedule->id
;
529 CRM_Core_DAO
::storeValues($actionSchedule, $values);
531 return $actionSchedule;
537 * Function to delete a Reminder
539 * @param int $id ID of the Reminder to be deleted.
544 static function del($id) {
546 $dao = new CRM_Core_DAO_ActionSchedule();
548 if ($dao->find(TRUE)) {
553 CRM_Core_Error
::fatal(ts('Invalid value passed to delete function.'));
557 * update the is_active flag in the db
559 * @param int $id id of the database record
560 * @param boolean $is_active value we want to set the is_active field
562 * @return Object DAO object on success, null otherwise
565 static function setIsActive($id, $is_active) {
566 return CRM_Core_DAO
::setFieldValue('CRM_Core_DAO_ActionSchedule', $id, 'is_active', $is_active);
569 static function sendMailings($mappingID, $now) {
570 $domainValues = CRM_Core_BAO_Domain
::getNameAndEmail();
571 $fromEmailAddress = "$domainValues[0] <$domainValues[1]>";
573 $mapping = new CRM_Core_DAO_ActionMapping();
574 $mapping->id
= $mappingID;
575 $mapping->find(TRUE);
577 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
578 $actionSchedule->mapping_id
= $mappingID;
579 $actionSchedule->is_active
= 1;
580 $actionSchedule->find(FALSE);
582 $tokenFields = array();
583 $session = CRM_Core_Session
::singleton();
585 while ($actionSchedule->fetch()) {
586 $extraSelect = $extraJoin = $extraWhere = '';
588 if ($actionSchedule->record_activity
) {
589 if ($mapping->entity
== 'civicrm_membership') {
591 CRM_Core_OptionGroup
::getValue('activity_type', 'Membership Renewal Reminder', 'name');
595 CRM_Core_OptionGroup
::getValue('activity_type', 'Reminder Sent', 'name');
599 CRM_Core_OptionGroup
::getValue('activity_status', 'Completed', 'name');
602 if ($mapping->entity
== 'civicrm_activity') {
603 $tokenEntity = 'activity';
604 $tokenFields = array('activity_id', 'activity_type', 'subject', 'details', 'activity_date_time');
605 $extraSelect = ', ov.label as activity_type, e.id as activity_id';
606 $extraJoin = "INNER JOIN civicrm_option_group og ON og.name = 'activity_type'
607 INNER JOIN civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id";
608 $extraWhere = 'AND e.is_current_revision = 1 AND e.is_deleted = 0';
611 if ($mapping->entity
== 'civicrm_participant') {
612 $tokenEntity = 'event';
613 $tokenFields = array('event_type', 'title', 'event_id', 'start_date', 'end_date', 'summary', 'description', 'location', 'info_url', 'registration_url', 'fee_amount', 'contact_email', 'contact_phone');
614 $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 ';
617 INNER JOIN civicrm_event ev ON e.event_id = ev.id
618 INNER JOIN civicrm_option_group og ON og.name = 'event_type'
619 INNER JOIN civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id
620 LEFT JOIN civicrm_loc_block lb ON lb.id = ev.loc_block_id
621 LEFT JOIN civicrm_address address ON address.id = lb.address_id
622 LEFT JOIN civicrm_email email ON email.id = lb.email_id
623 LEFT JOIN civicrm_phone phone ON phone.id = lb.phone_id
627 if ($mapping->entity
== 'civicrm_membership') {
628 $tokenEntity = 'membership';
629 $tokenFields = array('fee', 'id', 'join_date', 'start_date', 'end_date', 'status', 'type');
630 $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';
632 INNER JOIN civicrm_membership_type mt ON e.membership_type_id = mt.id
633 INNER JOIN civicrm_membership_status ms ON e.status_id = ms.id';
637 SELECT reminder.id as reminderID, reminder.*, e.id as entityID, e.* {$extraSelect}
638 FROM civicrm_action_log reminder
639 INNER JOIN {$mapping->entity} e ON e.id = reminder.entity_id
641 WHERE reminder.action_schedule_id = %1 AND reminder.action_date_time IS NULL
644 $dao = CRM_Core_DAO
::executeQuery($query,
645 array(1 => array($actionSchedule->id
, 'Integer'))
648 while ($dao->fetch()) {
649 $entityTokenParams = array();
650 foreach ($tokenFields as $field) {
651 if ($field == 'location') {
653 $stateProvince = CRM_Core_PseudoConstant
::stateProvince();
654 $loc['street_address'] = $dao->street_address
;
655 $loc['city'] = $dao->city
;
656 $loc['state_province'] = CRM_Utils_array
::value($dao->state_province_id
, $stateProvince);
657 $loc['postal_code'] = $dao->postal_code
;
658 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_Address
::format($loc);
660 elseif ($field == 'info_url') {
661 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_System
::url('civicrm/event/info', 'reset=1&id=' . $dao->event_id
, TRUE, NULL, FALSE);
663 elseif ($field == 'registration_url') {
664 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_System
::url('civicrm/event/register', 'reset=1&id=' . $dao->event_id
, TRUE, NULL, FALSE);
666 elseif (in_array($field, array('start_date','end_date','join_date','activity_date_time'))) {
667 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_Date
::customFormat($dao->$field);
670 $entityTokenParams["{$tokenEntity}." . $field] = $dao->$field;
676 $toEmail = CRM_Contact_BAO_Contact
::getPrimaryEmail($dao->contact_id
);
679 CRM_Core_BAO_ActionSchedule
::sendReminder(
687 if (!$result ||
is_a($result, 'PEAR_Error')) {
688 // we could not send an email, for now we ignore, CRM-3406
694 $errorMsg = "Couldn\'t find recipient\'s email address.";
697 // update action log record
699 'id' => $dao->reminderID
,
700 'is_error' => $isError,
701 'message' => $errorMsg ?
$errorMsg : "null",
702 'action_date_time' => $now,
704 CRM_Core_BAO_ActionLog
::create($logParams);
706 // insert activity log record if needed
707 if ($actionSchedule->record_activity
) {
708 $activityParams = array(
709 'subject' => $actionSchedule->title
,
710 'details' => $actionSchedule->body_html
,
711 'source_contact_id' =>
712 $session->get('userID') ?
$session->get('userID') : $dao->contact_id
,
713 'target_contact_id' => $dao->contact_id
,
714 'activity_date_time' => date('YmdHis'),
715 'status_id' => $activityStatusID,
716 'activity_type_id' => $activityTypeID,
717 'source_record_id' => $dao->entityID
,
719 $activity = CRM_Activity_BAO_Activity
::create($activityParams);
727 static function buildRecipientContacts($mappingID, $now) {
728 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
729 $actionSchedule->mapping_id
= $mappingID;
730 $actionSchedule->is_active
= 1;
731 $actionSchedule->find();
733 while ($actionSchedule->fetch()) {
734 $mapping = new CRM_Core_DAO_ActionMapping();
735 $mapping->id
= $mappingID;
736 $mapping->find(TRUE);
738 $select = $join = $where = array();
739 $limitTo = $actionSchedule->limit_to
;
740 $value = explode(CRM_Core_DAO
::VALUE_SEPARATOR
,
741 trim($actionSchedule->entity_value
, CRM_Core_DAO
::VALUE_SEPARATOR
)
743 $value = implode(',', $value);
745 $status = explode(CRM_Core_DAO
::VALUE_SEPARATOR
,
746 trim($actionSchedule->entity_status
, CRM_Core_DAO
::VALUE_SEPARATOR
)
748 $status = implode(',', $status);
750 if (!CRM_Utils_System
::isNull($mapping->entity_recipient
)) {
751 $recipientOptions = CRM_Core_OptionGroup
::values($mapping->entity_recipient
);
753 $from = "{$mapping->entity} e";
755 if ($mapping->entity
== 'civicrm_activity') {
756 $contactField = 'r.contact_id';
757 $activityContacts = CRM_Core_OptionGroup
::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
758 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
759 $assigneeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
760 $targetID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
762 switch (CRM_Utils_Array
::value($actionSchedule->recipient
, $recipientOptions)) {
763 case 'Activity Assignees':
764 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$assigneeID}";
767 case 'Activity Source':
768 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$sourceID}";
772 case 'Activity Targets':
773 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$targetID}";
777 // build where clause
778 if (!empty($value)) {
779 $where[] = "e.activity_type_id IN ({$value})";
782 $where[] = "e.activity_type_id IS NULL";
784 if (!empty($status)) {
785 $where[] = "e.status_id IN ({$status})";
787 $where[] = ' e.is_current_revision = 1 ';
788 $where[] = ' e.is_deleted = 0 ';
790 $dateField = 'e.activity_date_time';
793 if ($mapping->entity
== 'civicrm_participant') {
794 $contactField = 'e.contact_id';
795 $join[] = 'INNER JOIN civicrm_event r ON e.event_id = r.id';
796 if ($actionSchedule->recipient_listing
) {
797 $rList = explode(CRM_Core_DAO
::VALUE_SEPARATOR
,
798 trim($actionSchedule->recipient_listing
, CRM_Core_DAO
::VALUE_SEPARATOR
)
800 $rList = implode(',', $rList);
802 switch ($recipientOptions[$actionSchedule->recipient
]) {
803 case 'Participant Role':
804 $where[] = "e.role_id IN ({$rList})";
812 // build where clause
813 if (!empty($value)) {
814 $where[] = ($mapping->entity_value
== 'event_type') ?
"r.event_type_id IN ({$value})" : "r.id IN ({$value})";
817 $where[] = ($mapping->entity_value
== 'event_type') ?
"r.event_type_id IS NULL" : "r.id IS NULL";
820 if (!empty($status)) {
821 $where[] = "e.status_id IN ({$status})";
824 $where[] = 'r.is_active = 1';
825 $where[] = 'r.is_template = 0';
826 $dateField = str_replace('event_', 'r.', $actionSchedule->start_action_date
);
830 if ($mapping->entity
== 'civicrm_membership') {
831 $contactField = 'e.contact_id';
833 // build where clause
834 if ( $status == 2 ) {
835 //auto-renew memberships
836 $where[] = "e.contribution_recur_id IS NOT NULL ";
838 elseif ( $status == 1 ) {
839 $where[] = "e.contribution_recur_id IS NULL ";
842 // build where clause
843 if (!empty($value)) {
844 $where[] = "e.membership_type_id IN ({$value})";
847 $where[] = "e.membership_type_id IS NULL";
850 $where[] = "( e.is_override IS NULL OR e.is_override = 0 )";
851 $dateField = str_replace('membership_', 'e.', $actionSchedule->start_action_date
);
852 $notINClause = self
::permissionedRelationships($contactField);
854 $membershipStatus = CRM_Member_PseudoConstant
::membershipStatus(NULL, "(is_current_member = 1 OR name = 'Expired')", 'id');
855 $mStatus = implode (',', $membershipStatus);
856 $where[] = "e.status_id IN ({$mStatus})";
860 if ($actionSchedule->group_id
) {
861 $join[] = "INNER JOIN civicrm_group_contact grp ON {$contactField} = grp.contact_id AND grp.status = 'Added'";
862 $where[] = "grp.group_id IN ({$actionSchedule->group_id})";
864 elseif (!empty($actionSchedule->recipient_manual
)) {
865 $rList = CRM_Utils_Type
::escape($actionSchedule->recipient_manual
, 'String');
866 $where[] = "{$contactField} IN ({$rList})";
869 $select[] = "{$contactField} as contact_id";
870 $select[] = 'e.id as entity_id';
871 $select[] = "'{$mapping->entity}' as entity_table";
872 $select[] = "{$actionSchedule->id} as action_schedule_id";
873 $reminderJoinClause = "civicrm_action_log reminder ON reminder.contact_id = {$contactField} AND
874 reminder.entity_id = e.id AND
875 reminder.entity_table = '{$mapping->entity}' AND
876 reminder.action_schedule_id = %1";
878 $join[] = "INNER JOIN civicrm_contact c ON c.id = {$contactField} AND c.is_deleted = 0 AND c.is_deceased = 0 ";
880 if ($actionSchedule->start_action_date
) {
881 $startDateClause = array();
882 $op = ($actionSchedule->start_action_condition
== 'before' ?
'<=' : '>=');
883 $operator = ($actionSchedule->start_action_condition
== 'before' ?
'DATE_SUB' : 'DATE_ADD');
884 $date = $operator . "({$dateField}, INTERVAL {$actionSchedule->start_action_offset} {$actionSchedule->start_action_unit})";
885 $startDateClause[] = "'{$now}' >= {$date}";
886 if ($mapping->entity
== 'civicrm_participant') {
887 $startDateClause[] = $operator. "({$now}, INTERVAL 1 DAY ) {$op} " . $dateField;
890 $startDateClause[] = "DATE_SUB({$now}, INTERVAL 1 DAY ) <= {$date}";
893 $startDate = implode(' AND ', $startDateClause);
895 elseif ($actionSchedule->absolute_date
) {
896 $startDate = "DATEDIFF(DATE('{$now}'),'{$actionSchedule->absolute_date}') = 0";
899 // ( now >= date_built_from_start_time ) OR ( now = absolute_date )
900 $dateClause = "reminder.id IS NULL AND {$startDate}";
902 // start composing query
903 $selectClause = 'SELECT ' . implode(', ', $select);
904 $fromClause = "FROM $from";
905 $joinClause = !empty($join) ?
implode(' ', $join) : '';
906 $whereClause = 'WHERE ' . implode(' AND ', $where);
909 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
913 LEFT JOIN {$reminderJoinClause}
914 {$whereClause} AND {$dateClause} {$notINClause}";
916 CRM_Core_DAO
::executeQuery($query, array(1 => array($actionSchedule->id
, 'Integer')));
918 // if repeat is turned ON:
919 if ($actionSchedule->is_repeat
) {
920 $repeatEvent = ($actionSchedule->end_action
== 'before' ?
'DATE_SUB' : 'DATE_ADD') . "({$dateField}, INTERVAL {$actionSchedule->end_frequency_interval} {$actionSchedule->end_frequency_unit})";
922 if ($actionSchedule->repetition_frequency_unit
== 'day') {
923 $hrs = 24 * $actionSchedule->repetition_frequency_interval
;
925 elseif ($actionSchedule->repetition_frequency_unit
== 'week') {
926 $hrs = 24 * $actionSchedule->repetition_frequency_interval
* 7;
929 $hrs = $actionSchedule->repetition_frequency_interval
;
932 // (now <= repeat_end_time )
933 $repeatEventClause = "'{$now}' <= {$repeatEvent}";
934 // diff(now && logged_date_time) >= repeat_interval
935 $havingClause = "HAVING TIMEDIFF({$now}, latest_log_time) >= TIME('{$hrs}:00:00')";
936 $groupByClause = 'GROUP BY reminder.contact_id, reminder.entity_id, reminder.entity_table';
937 $selectClause .= ', MAX(reminder.action_date_time) as latest_log_time';
939 $sqlInsertValues = "{$selectClause}
942 INNER JOIN {$reminderJoinClause}
943 {$whereClause} AND {$repeatEventClause}
947 $valsqlInsertValues = CRM_Core_DAO
::executeQuery($sqlInsertValues, array(1 => array($actionSchedule->id
, 'Integer')));
949 $arrValues = array();
950 while ($valsqlInsertValues->fetch()) {
951 $arrValues[] = "( {$valsqlInsertValues->contact_id}, {$valsqlInsertValues->entity_id}, '{$valsqlInsertValues->entity_table}',{$valsqlInsertValues->action_schedule_id} )";
954 $valString = implode(',', $arrValues);
958 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id) VALUES ' . $valString;
959 CRM_Core_DAO
::executeQuery($query, array(1 => array($actionSchedule->id
, 'Integer')));
965 static function permissionedRelationships($field) {
967 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
968 FROM civicrm_membership m
969 LEFT JOIN civicrm_membership cm ON cm.id = m.owner_membership_id
970 LEFT JOIN civicrm_membership_type cmt ON cmt.id = m.membership_type_id
971 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 )
972 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 ) )
973 WHERE m.owner_membership_id IS NOT NULL AND
974 ( rel.is_permission_a_b = 0 OR rel.is_permission_b_a = 0)
977 $excludeIds = array();
978 $dao = CRM_Core_DAO
::executeQuery($query, array());
979 while ($dao->fetch()) {
980 if ($dao->slave_contact
== $dao->contact_id_a
&& $dao->is_permission_b_a
== 0) {
981 $excludeIds[] = $dao->slave_contact
;
983 elseif ($dao->slave_contact
== $dao->contact_id_b
&& $dao->is_permission_a_b
== 0) {
984 $excludeIds[] = $dao->slave_contact
;
988 if (!empty($excludeIds)) {
989 $clause = "AND {$field} NOT IN ( " .implode(', ', $excludeIds) . ' ) ';
995 static function processQueue($now = NULL) {
996 $now = $now ? CRM_Utils_Time
::setTime($now) : CRM_Utils_Time
::getTime();
998 $mappings = self
::getMapping();
999 foreach ($mappings as $mappingID => $mapping) {
1000 self
::buildRecipientContacts($mappingID, $now);
1001 self
::sendMailings($mappingID, $now);
1006 'messages' => ts('Sent all scheduled reminders successfully'),
1011 static function isConfigured($id, $mappingID) {
1012 $queryString = "SELECT count(id) FROM civicrm_action_schedule
1013 WHERE mapping_id = %1 AND
1017 1 => array($mappingID, 'Integer'),
1018 2 => array($id, 'Integer'),
1020 return CRM_Core_DAO
::singleValueQuery($queryString, $params);
1023 static function getRecipientListing($mappingID, $recipientType) {
1025 if (!$mappingID ||
!$recipientType) {
1029 $mapping = self
::getMapping($mappingID);
1031 switch ($mapping['entity']) {
1032 case 'civicrm_participant':
1033 $eventContacts = CRM_Core_OptionGroup
::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'name');
1034 if (!CRM_Utils_Array
::value($recipientType, $eventContacts)) {
1037 if ($eventContacts[$recipientType] == 'Participant Role') {
1038 $options = CRM_Event_PseudoConstant
::participantRole();