Merge remote-tracking branch 'upstream/4.3' into 4.3-master-2013-08-05-11-07-08
[civicrm-core.git] / CRM / Core / BAO / ActionSchedule.php
1 <?php
2 /*
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. |
10 | |
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. |
14 | |
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. |
19 | |
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 +--------------------------------------------------------------------+
27 */
28
29 /**
30 *
31 * @package CRM
32 * @copyright CiviCRM LLC (c) 2004-2013
33 * $Id$
34 *
35 */
36
37 /**
38 * This class contains functions for managing Scheduled Reminders
39 */
40 class CRM_Core_BAO_ActionSchedule extends CRM_Core_DAO_ActionSchedule {
41
42 static function getMapping($id = NULL) {
43 static $_action_mapping;
44
45 if ($id && !is_null($_action_mapping) && isset($_action_mapping[$id])) {
46 return $_action_mapping[$id];
47 }
48
49 $dao = new CRM_Core_DAO_ActionMapping();
50 if ($id) {
51 $dao->id = $id;
52 }
53 $dao->find();
54
55 $mapping = array();
56 while ($dao->fetch()) {
57 $defaults = array();
58 CRM_Core_DAO::storeValues($dao, $defaults);
59 $mapping[$dao->id] = $defaults;
60 }
61 $_action_mapping = $mapping;
62
63 return $mapping;
64 }
65
66 /**
67 * Retrieve list of selections/drop downs for Scheduled Reminder form
68 *
69 * @param bool $id mapping id
70 *
71 * @return array associated array of all the drop downs in the form
72 * @static
73 * @access public
74 */
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);
79
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();
86
87 asort($activityType);
88
89 $sel1 = $sel2 = $sel3 = $sel4 = $sel5 = array();
90 $options = array(
91 'manual' => ts('Choose Recipient(s)'),
92 'group' => ts('Select a Group'),
93 );
94
95 $entityMapping = array();
96 $recipientMapping = array_combine(array_keys($options), array_keys($options));
97
98 if (!$id) {
99 $id = 1;
100 }
101
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);
109
110 $sel1Val = NULL;
111 switch ($entityValue) {
112 case 'activity_type':
113 if ($value['entity'] == 'civicrm_activity') {
114 $sel1Val = ts('Activity');
115 }
116 $sel2[$key] = $valueLabel + $activityType;
117 break;
118
119 case 'event_type':
120 if ($value['entity'] == 'civicrm_participant') {
121 $sel1Val = ts('Event Type');
122 }
123 $sel2[$key] = $valueLabel + $eventType;
124 break;
125
126 case 'event_template':
127 if ($value['entity'] == 'civicrm_participant') {
128 $sel1Val = ts('Event Template');
129 }
130 $sel2[$key] = $valueLabel + $eventTemplate;
131 break;
132
133 case 'civicrm_event':
134 if ($value['entity'] == 'civicrm_participant') {
135 $sel1Val = ts('Event Name');
136 }
137 $sel2[$key] = $valueLabel + $event;
138 break;
139
140 case 'civicrm_membership_type':
141 if ($value['entity'] == 'civicrm_membership') {
142 $sel1Val = ts('Membership');
143 }
144 $sel2[$key] = $valueLabel + $membershipType;
145 break;
146 }
147 $sel1[$key] = $sel1Val;
148
149 if ($key == $id) {
150 if ($startDate = CRM_Utils_Array::value('entity_date_start', $value)) {
151 $sel4[$startDate] = ucwords(str_replace('_', ' ', $startDate));
152 }
153 if ($endDate = CRM_Utils_Array::value('entity_date_end', $value)) {
154 $sel4[$endDate] = ucwords(str_replace('_', ' ', $endDate));
155 }
156
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');
162 break;
163
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');
168 break;
169
170 case NULL:
171 $sel5[$entityRecipient] = $options;
172 break;
173 }
174 }
175 }
176 $sel3 = $sel2;
177
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);
182
183 switch ($entityStatus) {
184 case 'activity_status':
185 foreach ($sel3[$id] as $kkey => & $vval) {
186 $vval = $statusLabel + $activityStatus;
187 }
188 break;
189
190 case 'civicrm_participant_status_type':
191 foreach ($sel3[$id] as $kkey => & $vval) {
192 $vval = $statusLabel + $participantStatus;
193 }
194 break;
195
196 case 'auto_renew_options':
197 foreach ($sel3[$id] as $kkey => & $vval) {
198 $auto = 0;
199 if ($kkey) {
200 $auto = CRM_Core_DAO::getFieldValue('CRM_Member_DAO_MembershipType', $kkey, 'auto_renew');
201 }
202 if ( $auto ) {
203 $vval = $statusLabel + $autoRenew;
204 }
205 else {
206 $vval = $statusLabel;
207 }
208 }
209 break;
210
211 case '':
212 $sel3[$id] = '';
213 break;
214
215 }
216 }
217
218 return array(
219 'sel1' => $sel1,
220 'sel2' => $sel2,
221 'sel3' => $sel3,
222 'sel4' => $sel4,
223 'sel5' => $sel5,
224 'entityMapping' => $entityMapping,
225 'recipientMapping' => $recipientMapping,
226 );
227 }
228
229 static function getSelection1($id = NULL) {
230 $mapping = self::getMapping($id);
231 $sel4 = $sel5 = array();
232 $options = array(
233 'manual' => ts('Choose Recipient(s)'),
234 'group' => ts('Select a Group'),
235 );
236
237 $recipientMapping = array_combine(array_keys($options), array_keys($options));
238
239 foreach ($mapping as $value) {
240 $entityRecipient = CRM_Utils_Array::value('entity_recipient', $value);
241 $key = CRM_Utils_Array::value('id', $value);
242
243 if ($startDate = CRM_Utils_Array::value('entity_date_start', $value)) {
244 $sel4[$startDate] = ucwords(str_replace('_', ' ', $startDate));
245 }
246 if ($endDate = CRM_Utils_Array::value('entity_date_end', $value)) {
247 $sel4[$endDate] = ucwords(str_replace('_', ' ', $endDate));
248 }
249
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');
255 break;
256
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');
261 break;
262
263 case NULL:
264 $sel5[$id] = $options;
265 break;
266 }
267 }
268
269 return array(
270 'sel4' => $sel4,
271 'sel5' => $sel5[$id],
272 'recipientMapping' => $recipientMapping,
273 );
274 }
275
276 /**
277 * Retrieve list of Scheduled Reminders
278 *
279 * @param bool $namesOnly return simple list of names
280 *
281 * @return array (reference) reminder list
282 * @static
283 * @access public
284 */
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();
288
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();
293
294 $auto_renew_options = CRM_Core_OptionGroup::values('auto_renew_options');
295 $civicrm_membership_type = CRM_Member_PseudoConstant::membershipType();
296
297 asort($activity_type);
298 $entity = array(
299 'civicrm_activity' => 'Activity',
300 'civicrm_participant' => 'Event',
301 'civicrm_membership' => 'Member',
302 );
303
304 $query = "
305 SELECT
306 title,
307 cam.entity,
308 cas.id as id,
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,
317 cas.absolute_date,
318 is_repeat,
319 is_active
320
321 FROM civicrm_action_schedule cas
322 LEFT JOIN civicrm_action_mapping cam ON (cam.id = cas.mapping_id)
323 ";
324 $params = CRM_Core_DAO::$_nullArray;
325
326 if ($entityValue and $id) {
327 $where = "
328 WHERE cas.entity_value = $id AND
329 cam.entity_value = '$entityValue'";
330
331 $query .= $where;
332
333 $params = array(
334 1 => array($id, 'Integer'),
335 2 => array($entityValue, 'String'),
336 );
337 }
338
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;
348
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);
353 }
354 $statusIds = implode(', ', $statusArray);
355
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);
360 }
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;
367 }
368
369 return $list;
370 }
371
372 static function sendReminder($contactId, $email, $scheduleID, $from, $tokenParams) {
373
374 $schedule = new CRM_Core_DAO_ActionSchedule();
375 $schedule->id = $scheduleID;
376
377 $domain = CRM_Core_BAO_Domain::getDomain();
378 $result = NULL;
379 $hookTokens = array();
380
381 if ($schedule->find(TRUE)) {
382 $body_text = $schedule->body_text;
383 $body_html = $schedule->body_html;
384 $body_subject = $schedule->subject;
385 if (!$body_text) {
386 $body_text = CRM_Utils_String::htmlToText($body_html);
387 }
388
389 $params = array(array('contact_id', '=', $contactId, 0, 0));
390 list($contact, $_) = CRM_Contact_BAO_Query::apiQuery($params);
391
392 //CRM-4524
393 $contact = reset($contact);
394
395 if (!$contact || is_a($contact, 'CRM_Core_Error')) {
396 return NULL;
397 }
398
399 // merge activity tokens with contact array
400 $contact = array_merge($contact, $tokenParams);
401
402 //CRM-5734
403 CRM_Utils_Hook::tokenValues($contact, $contactId);
404
405 CRM_Utils_Hook::tokens($hookTokens);
406 $categories = array_keys($hookTokens);
407
408 $type = array('html', 'text');
409
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();
415
416 if ($$bodyType) {
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);
422 }
423 }
424 $html = $body_html;
425 $text = $body_text;
426
427 $smarty = CRM_Core_Smarty::singleton();
428 foreach (array(
429 'text', 'html') as $elem) {
430 $$elem = $smarty->fetch("string:{$$elem}");
431 }
432
433 $matches = array();
434 preg_match_all('/(?<!\{|\\\\)\{(\w+\.\w+)\}(?!\})/',
435 $body_subject,
436 $matches,
437 PREG_PATTERN_ORDER
438 );
439
440 $subjectToken = NULL;
441 if ($matches[1]) {
442 foreach ($matches[1] as $token) {
443 list($type, $name) = preg_split('/\./', $token, 2);
444 if ($name) {
445 if (!isset($subjectToken['contact'])) {
446 $subjectToken['contact'] = array();
447 }
448 $subjectToken['contact'][] = $name;
449 }
450 }
451 }
452
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);
457
458 $messageSubject = $smarty->fetch("string:{$messageSubject}");
459
460 // set up the parameters for CRM_Utils_Mail::send
461 $mailParams = array(
462 'groupName' => 'Scheduled Reminder Sender',
463 'from' => $from,
464 'toName' => $contact['display_name'],
465 'toEmail' => $email,
466 'subject' => $messageSubject,
467 );
468
469 if (!$html || $contact['preferred_mail_format'] == 'Text' ||
470 $contact['preferred_mail_format'] == 'Both'
471 ) {
472 // render the &amp; entities in text mode, so that the links work
473 $mailParams['text'] = str_replace('&amp;', '&', $text);
474 }
475 if ($html && ($contact['preferred_mail_format'] == 'HTML' ||
476 $contact['preferred_mail_format'] == 'Both'
477 )) {
478 $mailParams['html'] = $html;
479 }
480
481 $result = CRM_Utils_Mail::send($mailParams);
482 }
483 $schedule->free();
484
485 return $result;
486 }
487
488 /**
489 * Function to add the schedules reminders in the db
490 *
491 * @param array $params (reference ) an assoc array of name/value pairs
492 * @param array $ids the array that holds all the db ids
493 *
494 * @return object CRM_Core_DAO_ActionSchedule
495 * @access public
496 * @static
497 *
498 */
499 static function add(&$params, &$ids) {
500 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
501 $actionSchedule->copyValues($params);
502
503 return $actionSchedule->save();
504 }
505
506 /**
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
510 *
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
513 *
514 * @return object CRM_Core_DAO_ActionSchedule object on success, null otherwise
515 * @access public
516 * @static
517 */
518 static function retrieve(&$params, &$values) {
519 if (empty($params)) {
520 return NULL;
521 }
522 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
523
524 $actionSchedule->copyValues($params);
525
526 if ($actionSchedule->find(TRUE)) {
527 $ids['actionSchedule'] = $actionSchedule->id;
528
529 CRM_Core_DAO::storeValues($actionSchedule, $values);
530
531 return $actionSchedule;
532 }
533 return NULL;
534 }
535
536 /**
537 * Function to delete a Reminder
538 *
539 * @param int $id ID of the Reminder to be deleted.
540 *
541 * @access public
542 * @static
543 */
544 static function del($id) {
545 if ($id) {
546 $dao = new CRM_Core_DAO_ActionSchedule();
547 $dao->id = $id;
548 if ($dao->find(TRUE)) {
549 $dao->delete();
550 return;
551 }
552 }
553 CRM_Core_Error::fatal(ts('Invalid value passed to delete function.'));
554 }
555
556 /**
557 * update the is_active flag in the db
558 *
559 * @param int $id id of the database record
560 * @param boolean $is_active value we want to set the is_active field
561 *
562 * @return Object DAO object on success, null otherwise
563 * @static
564 */
565 static function setIsActive($id, $is_active) {
566 return CRM_Core_DAO::setFieldValue('CRM_Core_DAO_ActionSchedule', $id, 'is_active', $is_active);
567 }
568
569 static function sendMailings($mappingID, $now) {
570 $domainValues = CRM_Core_BAO_Domain::getNameAndEmail();
571 $fromEmailAddress = "$domainValues[0] <$domainValues[1]>";
572
573 $mapping = new CRM_Core_DAO_ActionMapping();
574 $mapping->id = $mappingID;
575 $mapping->find(TRUE);
576
577 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
578 $actionSchedule->mapping_id = $mappingID;
579 $actionSchedule->is_active = 1;
580 $actionSchedule->find(FALSE);
581
582 $tokenFields = array();
583 $session = CRM_Core_Session::singleton();
584
585 while ($actionSchedule->fetch()) {
586 $extraSelect = $extraJoin = $extraWhere = '';
587
588 if ($actionSchedule->record_activity) {
589 if ($mapping->entity == 'civicrm_membership') {
590 $activityTypeID =
591 CRM_Core_OptionGroup::getValue('activity_type', 'Membership Renewal Reminder', 'name');
592 }
593 else {
594 $activityTypeID =
595 CRM_Core_OptionGroup::getValue('activity_type', 'Reminder Sent', 'name');
596 }
597
598 $activityStatusID =
599 CRM_Core_OptionGroup::getValue('activity_status', 'Completed', 'name');
600 }
601
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 = "
607 INNER JOIN civicrm_option_group og ON og.name = 'activity_type'
608 INNER JOIN civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id";
609 $extraWhere = 'AND e.is_current_revision = 1 AND e.is_deleted = 0';
610 if ($actionSchedule->limit_to == 0) {
611 $extraJoin = "
612 LEFT JOIN civicrm_option_group og ON og.name = 'activity_type'
613 LEFT JOIN civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id";
614 $extraWhere = '';
615 }
616 }
617
618 if ($mapping->entity == 'civicrm_participant') {
619 $tokenEntity = 'event';
620 $tokenFields = array('event_type', 'title', 'event_id', 'start_date', 'end_date', 'summary', 'description', 'location', 'info_url', 'registration_url', 'fee_amount', 'contact_email', 'contact_phone');
621 $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 ';
622
623 $extraJoin = "
624 INNER JOIN civicrm_event ev ON e.event_id = ev.id
625 INNER JOIN civicrm_option_group og ON og.name = 'event_type'
626 INNER JOIN civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id
627 LEFT JOIN civicrm_loc_block lb ON lb.id = ev.loc_block_id
628 LEFT JOIN civicrm_address address ON address.id = lb.address_id
629 LEFT JOIN civicrm_email email ON email.id = lb.email_id
630 LEFT JOIN civicrm_phone phone ON phone.id = lb.phone_id
631 ";
632 if ($actionSchedule->limit_to == 0) {
633 $extraJoin = "
634 LEFT JOIN civicrm_event ev ON e.event_id = ev.id
635 LEFT JOIN civicrm_option_group og ON og.name = 'event_type'
636 LEFT JOIN civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id
637 LEFT JOIN civicrm_loc_block lb ON lb.id = ev.loc_block_id
638 LEFT JOIN civicrm_address address ON address.id = lb.address_id
639 LEFT JOIN civicrm_email email ON email.id = lb.email_id
640 LEFT JOIN civicrm_phone phone ON phone.id = lb.phone_id
641 ";
642 }
643 }
644
645 if ($mapping->entity == 'civicrm_membership') {
646 $tokenEntity = 'membership';
647 $tokenFields = array('fee', 'id', 'join_date', 'start_date', 'end_date', 'status', 'type');
648 $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';
649 $extraJoin = '
650 INNER JOIN civicrm_membership_type mt ON e.membership_type_id = mt.id
651 INNER JOIN civicrm_membership_status ms ON e.status_id = ms.id';
652
653 if ($actionSchedule->limit_to == 0) {
654 $extraJoin = '
655 LEFT JOIN civicrm_membership_type mt ON e.membership_type_id = mt.id
656 LEFT JOIN civicrm_membership_status ms ON e.status_id = ms.id';
657 }
658 }
659
660 $entityJoinClause = "INNER JOIN {$mapping->entity} e ON e.id = reminder.entity_id";
661 if ($actionSchedule->limit_to == 0) {
662 $entityJoinClause = "LEFT JOIN {$mapping->entity} e ON e.id = reminder.entity_id";
663 $extraWhere .= " AND (e.id = reminder.entity_id OR reminder.entity_table = 'civicrm_contact')";
664 }
665
666 $query = "
667 SELECT reminder.id as reminderID, reminder.contact_id as contactID, reminder.*, e.id as entityID, e.* {$extraSelect}
668 FROM civicrm_action_log reminder
669 {$entityJoinClause}
670 {$extraJoin}
671 WHERE reminder.action_schedule_id = %1 AND reminder.action_date_time IS NULL
672 {$extraWhere}";
673
674 $dao = CRM_Core_DAO::executeQuery($query,
675 array(1 => array($actionSchedule->id, 'Integer'))
676 );
677
678 while ($dao->fetch()) {
679 $entityTokenParams = array();
680 foreach ($tokenFields as $field) {
681 if ($field == 'location') {
682 $loc = array();
683 $stateProvince = CRM_Core_PseudoConstant::stateProvince();
684 $loc['street_address'] = $dao->street_address;
685 $loc['city'] = $dao->city;
686 $loc['state_province'] = CRM_Utils_array::value($dao->state_province_id, $stateProvince);
687 $loc['postal_code'] = $dao->postal_code;
688 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_Address::format($loc);
689 }
690 elseif ($field == 'info_url') {
691 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_System::url('civicrm/event/info', 'reset=1&id=' . $dao->event_id, TRUE, NULL, FALSE);
692 }
693 elseif ($field == 'registration_url') {
694 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_System::url('civicrm/event/register', 'reset=1&id=' . $dao->event_id, TRUE, NULL, FALSE);
695 }
696 elseif (in_array($field, array('start_date','end_date','join_date','activity_date_time'))) {
697 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_Date::customFormat($dao->$field);
698 }
699 else {
700 $entityTokenParams["{$tokenEntity}." . $field] = $dao->$field;
701 }
702 }
703
704 $isError = 0;
705 $errorMsg = '';
706 $toEmail = CRM_Contact_BAO_Contact::getPrimaryEmail($dao->contactID);
707 if ($toEmail) {
708 $result =
709 CRM_Core_BAO_ActionSchedule::sendReminder(
710 $dao->contactID,
711 $toEmail,
712 $actionSchedule->id,
713 $fromEmailAddress,
714 $entityTokenParams
715 );
716
717 if (!$result || is_a($result, 'PEAR_Error')) {
718 // we could not send an email, for now we ignore, CRM-3406
719 $isError = 1;
720 }
721 }
722 else {
723 $isError = 1;
724 $errorMsg = "Couldn\'t find recipient\'s email address.";
725 }
726
727 // update action log record
728 $logParams = array(
729 'id' => $dao->reminderID,
730 'is_error' => $isError,
731 'message' => $errorMsg ? $errorMsg : "null",
732 'action_date_time' => $now,
733 );
734 CRM_Core_BAO_ActionLog::create($logParams);
735
736 // insert activity log record if needed
737 if ($actionSchedule->record_activity) {
738 $activityParams = array(
739 'subject' => $actionSchedule->title,
740 'details' => $actionSchedule->body_html,
741 'source_contact_id' =>
742 $session->get('userID') ? $session->get('userID') : $dao->contactID,
743 'target_contact_id' => $dao->contactID,
744 'activity_date_time' => date('YmdHis'),
745 'status_id' => $activityStatusID,
746 'activity_type_id' => $activityTypeID,
747 'source_record_id' => $dao->entityID,
748 );
749 $activity = CRM_Activity_BAO_Activity::create($activityParams);
750 }
751 }
752
753 $dao->free();
754 }
755 }
756
757 static function buildRecipientContacts($mappingID, $now) {
758 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
759 $actionSchedule->mapping_id = $mappingID;
760 $actionSchedule->is_active = 1;
761 $actionSchedule->find();
762
763 while ($actionSchedule->fetch()) {
764 $mapping = new CRM_Core_DAO_ActionMapping();
765 $mapping->id = $mappingID;
766 $mapping->find(TRUE);
767
768 $select = $join = $where = array();
769 $limitTo = $actionSchedule->limit_to;
770 $value = explode(CRM_Core_DAO::VALUE_SEPARATOR,
771 trim($actionSchedule->entity_value, CRM_Core_DAO::VALUE_SEPARATOR)
772 );
773 $value = implode(',', $value);
774
775 $status = explode(CRM_Core_DAO::VALUE_SEPARATOR,
776 trim($actionSchedule->entity_status, CRM_Core_DAO::VALUE_SEPARATOR)
777 );
778 $status = implode(',', $status);
779
780 if (!CRM_Utils_System::isNull($mapping->entity_recipient)) {
781 $recipientOptions = CRM_Core_OptionGroup::values($mapping->entity_recipient);
782 }
783 $from = "{$mapping->entity} e";
784
785 if ($mapping->entity == 'civicrm_activity') {
786 $contactField = 'r.contact_id';
787 $table = 'civicrm_activity e';
788 $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
789 $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
790 $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
791 $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
792
793 switch (CRM_Utils_Array::value($actionSchedule->recipient, $recipientOptions)) {
794 case 'Activity Assignees':
795 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$assigneeID}";
796 break;
797
798 case 'Activity Source':
799 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$sourceID}";
800 break;
801
802 default:
803 case 'Activity Targets':
804 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$targetID}";
805 break;
806
807 }
808 // build where clause
809 if (!empty($value)) {
810 $where[] = "e.activity_type_id IN ({$value})";
811 }
812 else {
813 $where[] = "e.activity_type_id IS NULL";
814 }
815 if (!empty($status)) {
816 $where[] = "e.status_id IN ({$status})";
817 }
818 $where[] = ' e.is_current_revision = 1 ';
819 $where[] = ' e.is_deleted = 0 ';
820
821 $dateField = 'e.activity_date_time';
822 }
823
824 if ($mapping->entity == 'civicrm_participant') {
825 $table = 'civicrm_event r';
826 $contactField = 'e.contact_id';
827 $join[] = 'INNER JOIN civicrm_event r ON e.event_id = r.id';
828 if ($actionSchedule->recipient_listing) {
829 $rList = explode(CRM_Core_DAO::VALUE_SEPARATOR,
830 trim($actionSchedule->recipient_listing, CRM_Core_DAO::VALUE_SEPARATOR)
831 );
832 $rList = implode(',', $rList);
833
834 switch ($recipientOptions[$actionSchedule->recipient]) {
835 case 'Participant Role':
836 $where[] = "e.role_id IN ({$rList})";
837 break;
838
839 default:
840 break;
841 }
842 }
843
844 // build where clause
845 if (!empty($value)) {
846 $where[] = ($mapping->entity_value == 'event_type') ? "r.event_type_id IN ({$value})" : "r.id IN ({$value})";
847 }
848 else {
849 $where[] = ($mapping->entity_value == 'event_type') ? "r.event_type_id IS NULL" : "r.id IS NULL";
850 }
851
852 if (!empty($status)) {
853 $where[] = "e.status_id IN ({$status})";
854 }
855
856 $where[] = 'r.is_active = 1';
857 $where[] = 'r.is_template = 0';
858 $dateField = str_replace('event_', 'r.', $actionSchedule->start_action_date);
859 }
860
861 $notINClause = '';
862 if ($mapping->entity == 'civicrm_membership') {
863 $contactField = 'e.contact_id';
864 $table = 'civicrm_membership e';
865 // build where clause
866 if ( $status == 2 ) {
867 //auto-renew memberships
868 $where[] = "e.contribution_recur_id IS NOT NULL ";
869 }
870 elseif ( $status == 1 ) {
871 $where[] = "e.contribution_recur_id IS NULL ";
872 }
873
874 // build where clause
875 if (!empty($value)) {
876 $where[] = "e.membership_type_id IN ({$value})";
877 }
878 else {
879 $where[] = "e.membership_type_id IS NULL";
880 }
881
882 $where[] = "( e.is_override IS NULL OR e.is_override = 0 )";
883 $dateField = str_replace('membership_', 'e.', $actionSchedule->start_action_date);
884 $notINClause = self::permissionedRelationships($contactField);
885
886 $membershipStatus = CRM_Member_PseudoConstant::membershipStatus(NULL, "(is_current_member = 1 OR name = 'Expired')", 'id');
887 $mStatus = implode (',', $membershipStatus);
888 $where[] = "e.status_id IN ({$mStatus})";
889
890 }
891 if ($limitTo) {
892 if ($actionSchedule->group_id) {
893 $join[] = "INNER JOIN civicrm_group_contact grp ON {$contactField} = grp.contact_id AND grp.status = 'Added'";
894 $where[] = "grp.group_id IN ({$actionSchedule->group_id})";
895 }
896 elseif (!empty($actionSchedule->recipient_manual)) {
897 $rList = CRM_Utils_Type::escape($actionSchedule->recipient_manual, 'String');
898 $where[] = "{$contactField} IN ({$rList})";
899 }
900 }
901 else {
902 $addGroup = $addWhere = '';
903 if ($actionSchedule->group_id) {
904 $addGroup = " INNER JOIN civicrm_group_contact grp ON c.id = grp.contact_id AND grp.status = 'Added'";
905 $addWhere = " grp.group_id IN ({$actionSchedule->group_id})";
906 }
907 if (!empty($actionSchedule->recipient_manual)) {
908 $rList = CRM_Utils_Type::escape($actionSchedule->recipient_manual, 'String');
909 $addWhere = "c.id IN ({$rList})";
910 }
911 }
912 $select[] = "{$contactField} as contact_id";
913 $select[] = 'e.id as entity_id';
914 $select[] = "'{$mapping->entity}' as entity_table";
915 $select[] = "{$actionSchedule->id} as action_schedule_id";
916 $reminderJoinClause = "civicrm_action_log reminder ON reminder.contact_id = {$contactField} AND
917 reminder.entity_id = e.id AND
918 reminder.entity_table = '{$mapping->entity}' AND
919 reminder.action_schedule_id = %1";
920
921 $join[] = "INNER JOIN civicrm_contact c ON c.id = {$contactField} AND c.is_deleted = 0 AND c.is_deceased = 0 ";
922
923 if ($actionSchedule->start_action_date) {
924 $startDateClause = array();
925 $op = ($actionSchedule->start_action_condition == 'before' ? '<=' : '>=');
926 $operator = ($actionSchedule->start_action_condition == 'before' ? 'DATE_SUB' : 'DATE_ADD');
927 $date = $operator . "({$dateField}, INTERVAL {$actionSchedule->start_action_offset} {$actionSchedule->start_action_unit})";
928 $startDateClause[] = "'{$now}' >= {$date}";
929 if ($mapping->entity == 'civicrm_participant') {
930 $startDateClause[] = $operator. "({$now}, INTERVAL 1 DAY ) {$op} " . $dateField;
931 }
932 else {
933 $startDateClause[] = "DATE_SUB({$now}, INTERVAL 1 DAY ) <= {$date}";
934 }
935
936 $startDate = implode(' AND ', $startDateClause);
937 }
938 elseif ($actionSchedule->absolute_date) {
939 $startDate = "DATEDIFF(DATE('{$now}'),'{$actionSchedule->absolute_date}') = 0";
940 }
941
942 // ( now >= date_built_from_start_time ) OR ( now = absolute_date )
943 $dateClause = "reminder.id IS NULL AND {$startDate}";
944
945 // start composing query
946 $selectClause = 'SELECT ' . implode(', ', $select);
947 $fromClause = "FROM $from";
948 $joinClause = !empty($join) ? implode(' ', $join) : '';
949 $whereClause = 'WHERE ' . implode(' AND ', $where);
950 $union = '';
951
952 if ($limitTo == 0) {
953 $union ="
954 UNION
955 SELECT c.id as contact_id, c.id as entity_id, 'civicrm_contact' as entity_table, {$actionSchedule->id} as action_schedule_id
956 FROM (civicrm_contact c, {$table})
957 LEFT JOIN civicrm_action_log reminder ON reminder.contact_id = c.id AND
958 reminder.entity_id = c.id AND
959 reminder.entity_table = 'civicrm_contact' AND
960 reminder.action_schedule_id = {$actionSchedule->id}
961 {$addGroup}
962 WHERE (reminder.id IS NULL AND c.is_deleted = 0 AND c.is_deceased = 0 AND {$addWhere}) AND
963 {$dateClause}";
964 }
965 $query = "
966 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
967 {$selectClause}
968 {$fromClause}
969 {$joinClause}
970 LEFT JOIN {$reminderJoinClause}
971 {$whereClause} AND {$dateClause} {$notINClause}
972 {$union}";
973
974 CRM_Core_DAO::executeQuery($query, array(1 => array($actionSchedule->id, 'Integer')));
975
976 // if repeat is turned ON:
977 if ($actionSchedule->is_repeat) {
978 $repeatEvent = ($actionSchedule->end_action == 'before' ? 'DATE_SUB' : 'DATE_ADD') . "({$dateField}, INTERVAL {$actionSchedule->end_frequency_interval} {$actionSchedule->end_frequency_unit})";
979
980 if ($actionSchedule->repetition_frequency_unit == 'day') {
981 $hrs = 24 * $actionSchedule->repetition_frequency_interval;
982 }
983 elseif ($actionSchedule->repetition_frequency_unit == 'week') {
984 $hrs = 24 * $actionSchedule->repetition_frequency_interval * 7;
985 }
986 else {
987 $hrs = $actionSchedule->repetition_frequency_interval;
988 }
989
990 // (now <= repeat_end_time )
991 $repeatEventClause = "'{$now}' <= {$repeatEvent}";
992 // diff(now && logged_date_time) >= repeat_interval
993 $havingClause = "HAVING TIMEDIFF({$now}, latest_log_time) >= TIME('{$hrs}:00:00')";
994 $groupByClause = 'GROUP BY reminder.contact_id, reminder.entity_id, reminder.entity_table';
995 $selectClause .= ', MAX(reminder.action_date_time) as latest_log_time';
996
997 $sqlInsertValues = "{$selectClause}
998 {$fromClause}
999 {$joinClause}
1000 INNER JOIN {$reminderJoinClause}
1001 {$whereClause} AND {$repeatEventClause}
1002 {$groupByClause}
1003 {$havingClause}";
1004
1005 $valsqlInsertValues = CRM_Core_DAO::executeQuery($sqlInsertValues, array(1 => array($actionSchedule->id, 'Integer')));
1006
1007 $arrValues = array();
1008 while ($valsqlInsertValues->fetch()) {
1009 $arrValues[] = "( {$valsqlInsertValues->contact_id}, {$valsqlInsertValues->entity_id}, '{$valsqlInsertValues->entity_table}',{$valsqlInsertValues->action_schedule_id} )";
1010 }
1011
1012 $valString = implode(',', $arrValues);
1013
1014 if ($valString) {
1015 $query = '
1016 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id) VALUES ' . $valString;
1017 CRM_Core_DAO::executeQuery($query, array(1 => array($actionSchedule->id, 'Integer')));
1018 }
1019 }
1020 }
1021 }
1022
1023 static function permissionedRelationships($field) {
1024 $query = '
1025 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
1026 FROM civicrm_membership m
1027 LEFT JOIN civicrm_membership cm ON cm.id = m.owner_membership_id
1028 LEFT JOIN civicrm_membership_type cmt ON cmt.id = m.membership_type_id
1029 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 )
1030 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 ) )
1031 WHERE m.owner_membership_id IS NOT NULL AND
1032 ( rel.is_permission_a_b = 0 OR rel.is_permission_b_a = 0)
1033
1034 ';
1035 $excludeIds = array();
1036 $dao = CRM_Core_DAO::executeQuery($query, array());
1037 while ($dao->fetch()) {
1038 if ($dao->slave_contact == $dao->contact_id_a && $dao->is_permission_b_a == 0) {
1039 $excludeIds[] = $dao->slave_contact;
1040 }
1041 elseif ($dao->slave_contact == $dao->contact_id_b && $dao->is_permission_a_b == 0) {
1042 $excludeIds[] = $dao->slave_contact;
1043 }
1044 }
1045
1046 if (!empty($excludeIds)) {
1047 $clause = "AND {$field} NOT IN ( " .implode(', ', $excludeIds) . ' ) ';
1048 return $clause;
1049 }
1050 return NULL;
1051 }
1052
1053 static function processQueue($now = NULL) {
1054 $now = $now ? CRM_Utils_Time::setTime($now) : CRM_Utils_Time::getTime();
1055
1056 $mappings = self::getMapping();
1057 foreach ($mappings as $mappingID => $mapping) {
1058 self::buildRecipientContacts($mappingID, $now);
1059 self::sendMailings($mappingID, $now);
1060 }
1061
1062 $result = array(
1063 'is_error' => 0,
1064 'messages' => ts('Sent all scheduled reminders successfully'),
1065 );
1066 return $result;
1067 }
1068
1069 static function isConfigured($id, $mappingID) {
1070 $queryString = "SELECT count(id) FROM civicrm_action_schedule
1071 WHERE mapping_id = %1 AND
1072 entity_value = %2";
1073
1074 $params = array(
1075 1 => array($mappingID, 'Integer'),
1076 2 => array($id, 'Integer'),
1077 );
1078 return CRM_Core_DAO::singleValueQuery($queryString, $params);
1079 }
1080
1081 static function getRecipientListing($mappingID, $recipientType) {
1082 $options = array();
1083 if (!$mappingID || !$recipientType) {
1084 return $options;
1085 }
1086
1087 $mapping = self::getMapping($mappingID);
1088
1089 switch ($mapping['entity']) {
1090 case 'civicrm_participant':
1091 $eventContacts = CRM_Core_OptionGroup::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'name');
1092 if (!CRM_Utils_Array::value($recipientType, $eventContacts)) {
1093 return $options;
1094 }
1095 if ($eventContacts[$recipientType] == 'Participant Role') {
1096 $options = CRM_Event_PseudoConstant::participantRole();
1097 }
1098 break;
1099 }
1100
1101 return $options;
1102 }
1103 }
1104