projects / civicrm-core.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | blame (incremental) | history | HEAD
minor fix
[civicrm-core.git] / CRM / Core / BAO / ActionSchedule.php
CommitLineData
6a488035
TO		 1<?php
2/*
3 +--------------------------------------------------------------------+
06b69b18 4 | CiviCRM version 4.5 |
6a488035
TO		 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
06b69b18 32 * @copyright CiviCRM LLC (c) 2004-2014
6a488035
TO		 33 * $Id$
34 *
35 */
36
37/**
38 * This class contains functions for managing Scheduled Reminders
39 */
40class CRM_Core_BAO_ActionSchedule extends CRM_Core_DAO_ActionSchedule {
41
b5c2afd0
EM		 42 /**
43 * @param null $id
44 *
45 * @return array
46 */
6a488035
TO		 47 static function getMapping($id = NULL) {
48 static $_action_mapping;
49
50 if ($id && !is_null($_action_mapping) && isset($_action_mapping[$id])) {
51 return $_action_mapping[$id];
52 }
53
54 $dao = new CRM_Core_DAO_ActionMapping();
55 if ($id) {
56 $dao->id = $id;
57 }
58 $dao->find();
59
60 $mapping = array();
61 while ($dao->fetch()) {
62 $defaults = array();
63 CRM_Core_DAO::storeValues($dao, $defaults);
64 $mapping[$dao->id] = $defaults;
65 }
66 $_action_mapping = $mapping;
67
68 return $mapping;
69 }
70
8d657dde
AH		 71 /**
72 * Get all fields of the type Date
73 */
74
75 static function getDateFields() {
76 $allFields = CRM_Core_BAO_CustomField::getFields('');
77 $dateFields = array('birth_date' => ts('Birth Date'));
78 foreach ($allFields as $fieldID => $field) {
79 if ($field['data_type'] == 'Date') {
80 $dateFields["custom_$fieldID"] = $field['label'];
81 }
82 }
83 return $dateFields;
84 }
85
6a488035
TO		 86 /**
87 * Retrieve list of selections/drop downs for Scheduled Reminder form
88 *
89 * @param bool $id mapping id
90 *
91 * @return array associated array of all the drop downs in the form
92 * @static
93 * @access public
94 */
95 static function getSelection($id = NULL) {
96 $mapping = self::getMapping($id);
97 $activityStatus = CRM_Core_PseudoConstant::activityStatus();
d161de06 98 $activityType = CRM_Core_PseudoConstant::activityType(TRUE, TRUE);
6a488035
TO		 99
100 $participantStatus = CRM_Event_PseudoConstant::participantStatus(NULL, NULL, 'label');
101 $event = CRM_Event_PseudoConstant::event(NULL, FALSE, "( is_template IS NULL OR is_template != 1 )");
102 $eventType = CRM_Event_PseudoConstant::eventType();
103 $eventTemplate = CRM_Event_PseudoConstant::eventTemplates();
e7e657f0 104 $autoRenew = CRM_Core_OptionGroup::values('auto_renew_options');
6a488035 105 $membershipType = CRM_Member_PseudoConstant::membershipType();
8d657dde
AH		 106 $dateFieldParams = array('data_type' => 'Date');
107 $dateFields = self::getDateFields();
108 $contactOptions = CRM_Core_OptionGroup::values('contact_date_reminder_options');
6a488035
TO		 109
110 asort($activityType);
111
112 $sel1 = $sel2 = $sel3 = $sel4 = $sel5 = array();
3e315abc 113 $options = array(
114 'manual' => ts('Choose Recipient(s)'),
115 'group' => ts('Select a Group'),
6a488035
TO		 116 );
117
118 $entityMapping = array();
119 $recipientMapping = array_combine(array_keys($options), array_keys($options));
120
121 if (!$id) {
122 $id = 1;
123 }
124
125 foreach ($mapping as $value) {
3e315abc 126 $entityValue = CRM_Utils_Array::value('entity_value', $value);
127 $entityStatus = CRM_Utils_Array::value('entity_status', $value);
128 $entityRecipient = CRM_Utils_Array::value('entity_recipient', $value);
129 $valueLabel = array('- ' . strtolower(CRM_Utils_Array::value('entity_value_label', $value)) . ' -');
130 $key = CRM_Utils_Array::value('id', $value);
6a488035
TO		 131 $entityMapping[$key] = CRM_Utils_Array::value('entity', $value);
132
3e315abc 133 $sel1Val = NULL;
6a488035
TO		 134 switch ($entityValue) {
135 case 'activity_type':
136 if ($value['entity'] == 'civicrm_activity') {
137 $sel1Val = ts('Activity');
138 }
139 $sel2[$key] = $valueLabel + $activityType;
140 break;
141
142 case 'event_type':
143 if ($value['entity'] == 'civicrm_participant') {
144 $sel1Val = ts('Event Type');
145 }
146 $sel2[$key] = $valueLabel + $eventType;
147 break;
148
149 case 'event_template':
150 if ($value['entity'] == 'civicrm_participant') {
151 $sel1Val = ts('Event Template');
152 }
153 $sel2[$key] = $valueLabel + $eventTemplate;
154 break;
155
156 case 'civicrm_event':
157 if ($value['entity'] == 'civicrm_participant') {
158 $sel1Val = ts('Event Name');
159 }
160 $sel2[$key] = $valueLabel + $event;
161 break;
162
163 case 'civicrm_membership_type':
164 if ($value['entity'] == 'civicrm_membership') {
165 $sel1Val = ts('Membership');
166 }
167 $sel2[$key] = $valueLabel + $membershipType;
168 break;
628801bf 169
8d657dde 170 case 'civicrm_contact':
628801bf
AH		 171 if ($value['entity'] == 'civicrm_contact') {
172 $sel1Val = ts('Contact');
173 }
8d657dde 174 $sel2[$key] = $dateFields;
628801bf 175 break;
6a488035
TO		 176 }
177 $sel1[$key] = $sel1Val;
178
179 if ($key == $id) {
180 if ($startDate = CRM_Utils_Array::value('entity_date_start', $value)) {
181 $sel4[$startDate] = ucwords(str_replace('_', ' ', $startDate));
182 }
183 if ($endDate = CRM_Utils_Array::value('entity_date_end', $value)) {
184 $sel4[$endDate] = ucwords(str_replace('_', ' ', $endDate));
185 }
186
187 switch ($entityRecipient) {
188 case 'activity_contacts':
e7e657f0 189 $activityContacts = CRM_Core_OptionGroup::values('activity_contacts');
6a488035 190 $sel5[$entityRecipient] = $activityContacts + $options;
e7e657f0 191 $recipientMapping += CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
6a488035
TO		 192 break;
193
194 case 'event_contacts':
84a3e359 195 $eventContacts = CRM_Core_OptionGroup::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'label', TRUE, FALSE, 'name');
6a488035 196 $sel5[$entityRecipient] = $eventContacts + $options;
84a3e359 197 $recipientMapping += CRM_Core_OptionGroup::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'name', TRUE, FALSE, 'name');
6a488035
TO		 198 break;
199
200 case NULL:
201 $sel5[$entityRecipient] = $options;
202 break;
203 }
204 }
205 }
206 $sel3 = $sel2;
207
208 foreach ($mapping as $value) {
209 $entityStatus = CRM_Utils_Array::value('entity_status', $value);
3e315abc 210 $statusLabel = array('- ' . strtolower(CRM_Utils_Array::value('entity_status_label', $value)) . ' -');
211 $id = CRM_Utils_Array::value('id', $value);
6a488035
TO		 212
213 switch ($entityStatus) {
214 case 'activity_status':
215 foreach ($sel3[$id] as $kkey => & $vval) {
216 $vval = $statusLabel + $activityStatus;
217 }
218 break;
219
220 case 'civicrm_participant_status_type':
221 foreach ($sel3[$id] as $kkey => & $vval) {
222 $vval = $statusLabel + $participantStatus;
223 }
224 break;
225
226 case 'auto_renew_options':
227 foreach ($sel3[$id] as $kkey => & $vval) {
228 $auto = 0;
229 if ($kkey) {
230 $auto = CRM_Core_DAO::getFieldValue('CRM_Member_DAO_MembershipType', $kkey, 'auto_renew');
231 }
232 if ( $auto ) {
233 $vval = $statusLabel + $autoRenew;
234 }
235 else {
236 $vval = $statusLabel;
237 }
238 }
239 break;
240
8d657dde 241 case 'contact_date_reminder_options':
628801bf 242 foreach ($sel3[$id] as $kkey => & $vval) {
8d657dde 243 $vval = $contactOptions;
628801bf
AH		 244 }
245 break;
246
6a488035
TO		 247 case '':
248 $sel3[$id] = '';
249 break;
250
251 }
252 }
6a488035
TO		 253 return array(
254 'sel1' => $sel1,
255 'sel2' => $sel2,
256 'sel3' => $sel3,
257 'sel4' => $sel4,
258 'sel5' => $sel5,
259 'entityMapping' => $entityMapping,
260 'recipientMapping' => $recipientMapping,
261 );
262 }
263
b5c2afd0
EM		 264 /**
265 * @param null $id
266 *
267 * @return array
268 */
6a488035
TO		 269 static function getSelection1($id = NULL) {
270 $mapping = self::getMapping($id);
3e315abc 271 $sel4 = $sel5 = array();
272 $options = array(
273 'manual' => ts('Choose Recipient(s)'),
274 'group' => ts('Select a Group'),
6a488035
TO		 275 );
276
277 $recipientMapping = array_combine(array_keys($options), array_keys($options));
278
279 foreach ($mapping as $value) {
280 $entityRecipient = CRM_Utils_Array::value('entity_recipient', $value);
281 $key = CRM_Utils_Array::value('id', $value);
282
283 if ($startDate = CRM_Utils_Array::value('entity_date_start', $value)) {
284 $sel4[$startDate] = ucwords(str_replace('_', ' ', $startDate));
285 }
286 if ($endDate = CRM_Utils_Array::value('entity_date_end', $value)) {
287 $sel4[$endDate] = ucwords(str_replace('_', ' ', $endDate));
288 }
289
290 switch ($entityRecipient) {
291 case 'activity_contacts':
e7e657f0 292 $activityContacts = CRM_Core_OptionGroup::values('activity_contacts');
6a488035 293 $sel5[$id] = $activityContacts + $options;
e7e657f0 294 $recipientMapping += CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
6a488035
TO		 295 break;
296
297 case 'event_contacts':
84a3e359 298 $eventContacts = CRM_Core_OptionGroup::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'label', TRUE, FALSE, 'name');
6a488035 299 $sel5[$id] = $eventContacts + $options;
84a3e359 300 $recipientMapping += CRM_Core_OptionGroup::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'name', TRUE, FALSE, 'name');
6a488035
TO		 301 break;
302
303 case NULL:
304 $sel5[$id] = $options;
305 break;
306 }
307 }
308
309 return array(
310 'sel4' => $sel4,
311 'sel5' => $sel5[$id],
312 'recipientMapping' => $recipientMapping,
313 );
314 }
315
316 /**
317 * Retrieve list of Scheduled Reminders
318 *
da6b46f4
EM		 319 * @param bool $namesOnly return simple list of names
320 *
321 * @param null $entityValue
322 * @param null $id
6a488035
TO		 323 *
324 * @return array (reference) reminder list
325 * @static
326 * @access public
327 */
328 static function &getList($namesOnly = FALSE, $entityValue = NULL, $id = NULL) {
7b007e61 329 $activity_type = CRM_Core_PseudoConstant::activityType(TRUE, TRUE);
6a488035
TO		 330 $activity_status = CRM_Core_PseudoConstant::activityStatus();
331
332 $event_type = CRM_Event_PseudoConstant::eventType();
333 $civicrm_event = CRM_Event_PseudoConstant::event(NULL, FALSE, "( is_template IS NULL OR is_template != 1 )");
334 $civicrm_participant_status_type = CRM_Event_PseudoConstant::participantStatus(NULL, NULL, 'label');
335 $event_template = CRM_Event_PseudoConstant::eventTemplates();
8d657dde 336 $civicrm_contact = self::getDateFields();
6a488035 337
e7e657f0 338 $auto_renew_options = CRM_Core_OptionGroup::values('auto_renew_options');
8d657dde 339 $contact_date_reminder_options = CRM_Core_OptionGroup::values('contact_date_reminder_options');
6a488035
TO		 340 $civicrm_membership_type = CRM_Member_PseudoConstant::membershipType();
341
6a488035
TO		 342 $entity = array(
343 'civicrm_activity' => 'Activity',
344 'civicrm_participant' => 'Event',
345 'civicrm_membership' => 'Member',
8d657dde 346 'civicrm_contact' => 'Contact',
6a488035
TO		 347 );
348
349 $query = "
350SELECT
351 title,
352 cam.entity,
353 cas.id as id,
354 cam.entity_value as entityValue,
355 cas.entity_value as entityValueIds,
356 cam.entity_status as entityStatus,
357 cas.entity_status as entityStatusIds,
358 cas.start_action_date as entityDate,
359 cas.start_action_offset,
360 cas.start_action_unit,
361 cas.start_action_condition,
362 cas.absolute_date,
363 is_repeat,
364 is_active
365
366FROM civicrm_action_schedule cas
367LEFT JOIN civicrm_action_mapping cam ON (cam.id = cas.mapping_id)
368";
369 $params = CRM_Core_DAO::$_nullArray;
62933949 370 $where = " WHERE 1 ";
6a488035 371 if ($entityValue and $id) {
62933949 372 $where .= "
373AND cas.entity_value = $id AND
6a488035
TO		 374 cam.entity_value = '$entityValue'";
375
3e315abc 376 $params = array(
377 1 => array($id, 'Integer'),
378 2 => array($entityValue, 'String'),
6a488035
TO		 379 );
380 }
62933949 381 $where .= " AND cas.used_for IS NULL";
382 $query .= $where;
6a488035
TO		 383 $dao = CRM_Core_DAO::executeQuery($query);
384 while ($dao->fetch()) {
385 $list[$dao->id]['id'] = $dao->id;
386 $list[$dao->id]['title'] = $dao->title;
387 $list[$dao->id]['start_action_offset'] = $dao->start_action_offset;
388 $list[$dao->id]['start_action_unit'] = $dao->start_action_unit;
389 $list[$dao->id]['start_action_condition'] = $dao->start_action_condition;
390 $list[$dao->id]['entityDate'] = ucwords(str_replace('_', ' ', $dao->entityDate));
391 $list[$dao->id]['absolute_date'] = $dao->absolute_date;
392
393 $status = $dao->entityStatus;
394 $statusArray = explode(CRM_Core_DAO::VALUE_SEPARATOR, $dao->entityStatusIds);
395 foreach ($statusArray as & $s) {
396 $s = CRM_Utils_Array::value($s, $$status);
397 }
398 $statusIds = implode(', ', $statusArray);
399
400 $value = $dao->entityValue;
401 $valueArray = explode(CRM_Core_DAO::VALUE_SEPARATOR, $dao->entityValueIds);
402 foreach ($valueArray as & $v) {
403 $v = CRM_Utils_Array::value($v, $$value);
404 }
405 $valueIds = implode(', ', $valueArray);
406 $list[$dao->id]['entity'] = $entity[$dao->entity];
407 $list[$dao->id]['value'] = $valueIds;
408 $list[$dao->id]['status'] = $statusIds;
409 $list[$dao->id]['is_repeat'] = $dao->is_repeat;
410 $list[$dao->id]['is_active'] = $dao->is_active;
411 }
412
413 return $list;
414 }
415
b5c2afd0
EM		 416 /**
417 * @param $contactId
418 * @param $to
419 * @param $scheduleID
420 * @param $from
421 * @param $tokenParams
422 *
423 * @return bool|null
424 * @throws CRM_Core_Exception
425 */
a3e3eea1 426 static function sendReminder($contactId, $to, $scheduleID, $from, $tokenParams) {
427 $email = $to['email'];
428 $phoneNumber = $to['phone'];
6a488035
TO		 429 $schedule = new CRM_Core_DAO_ActionSchedule();
430 $schedule->id = $scheduleID;
431
432 $domain = CRM_Core_BAO_Domain::getDomain();
433 $result = NULL;
434 $hookTokens = array();
435
436 if ($schedule->find(TRUE)) {
437 $body_text = $schedule->body_text;
438 $body_html = $schedule->body_html;
1e035d58 439 $sms_body_text = $schedule->sms_body_text;
6a488035
TO		 440 $body_subject = $schedule->subject;
441 if (!$body_text) {
442 $body_text = CRM_Utils_String::htmlToText($body_html);
443 }
444
445 $params = array(array('contact_id', '=', $contactId, 0, 0));
446 list($contact, $_) = CRM_Contact_BAO_Query::apiQuery($params);
447
448 //CRM-4524
449 $contact = reset($contact);
450
451 if (!$contact || is_a($contact, 'CRM_Core_Error')) {
452 return NULL;
453 }
454
455 // merge activity tokens with contact array
456 $contact = array_merge($contact, $tokenParams);
457
458 //CRM-5734
459 CRM_Utils_Hook::tokenValues($contact, $contactId);
460
461 CRM_Utils_Hook::tokens($hookTokens);
462 $categories = array_keys($hookTokens);
463
1e035d58 464 $type = array('body_html' => 'html', 'body_text' => 'text', 'sms_body_text' => 'text');
6a488035 465
1e035d58 466 foreach ($type as $bodyType => $value) {
6a488035 467 $dummy_mail = new CRM_Mailing_BAO_Mailing();
1e035d58 468 if ($bodyType == 'sms_body_text') {
469 $dummy_mail->body_text = $$bodyType;
470 }
471 else {
b3fc8c7a 472 $dummy_mail->$bodyType = $$bodyType;
1e035d58 473 }
6a488035
TO		 474 $tokens = $dummy_mail->getTokens();
475
476 if ($$bodyType) {
477 CRM_Utils_Token::replaceGreetingTokens($$bodyType, NULL, $contact['contact_id']);
478 $$bodyType = CRM_Utils_Token::replaceDomainTokens($$bodyType, $domain, TRUE, $tokens[$value], TRUE);
479 $$bodyType = CRM_Utils_Token::replaceContactTokens($$bodyType, $contact, FALSE, $tokens[$value], FALSE, TRUE);
480 $$bodyType = CRM_Utils_Token::replaceComponentTokens($$bodyType, $contact, $tokens[$value], TRUE, FALSE);
481 $$bodyType = CRM_Utils_Token::replaceHookTokens($$bodyType, $contact, $categories, TRUE);
482 }
483 }
484 $html = $body_html;
485 $text = $body_text;
1e035d58 486 $sms_text = $sms_body_text;
6a488035 487
4ee279f4 488 $smarty = CRM_Core_Smarty::singleton();
489 foreach (array(
1e035d58 490 'text', 'html', 'sms_text') as $elem) {
4ee279f4 491 $$elem = $smarty->fetch("string:{$$elem}");
492 }
493
6a488035
TO		 494 $matches = array();
495 preg_match_all('/(?<!\{|\\\\)\{(\w+\.\w+)\}(?!\})/',
496 $body_subject,
497 $matches,
498 PREG_PATTERN_ORDER
499 );
500
501 $subjectToken = NULL;
502 if ($matches[1]) {
503 foreach ($matches[1] as $token) {
504 list($type, $name) = preg_split('/\./', $token, 2);
505 if ($name) {
748a7b58
BS		 506 if (!isset($subjectToken[$type])) {
507 $subjectToken[$type] = array();
6a488035 508 }
748a7b58 509 $subjectToken[$type][] = $name;
6a488035
TO		 510 }
511 }
512 }
513
514 $messageSubject = CRM_Utils_Token::replaceContactTokens($body_subject, $contact, FALSE, $subjectToken);
748a7b58
BS		 515 $messageSubject = CRM_Utils_Token::replaceDomainTokens($messageSubject, $domain, TRUE, $subjectToken);
516 $messageSubject = CRM_Utils_Token::replaceComponentTokens($messageSubject, $contact, $subjectToken, TRUE);
6a488035
TO		 517 $messageSubject = CRM_Utils_Token::replaceHookTokens($messageSubject, $contact, $categories, TRUE);
518
4ee279f4 519 $messageSubject = $smarty->fetch("string:{$messageSubject}");
520
a3e3eea1 521 if ($schedule->mode == 'SMS' or $schedule->mode == 'User_Preference') {
522 $session = CRM_Core_Session::singleton();
523 $userID = $session->get('userID') ? $session->get('userID') : $contactId;
524 $smsParams = array('To' => $phoneNumber, 'provider_id' => $schedule->sms_provider_id, 'activity_subject' => $messageSubject);
525 $activityTypeID = CRM_Core_OptionGroup::getValue('activity_type',
526 'SMS',
527 'name'
528 );
a3e3eea1 529 $activityParams = array(
530 'source_contact_id' => $userID,
531 'activity_type_id' => $activityTypeID,
532 'activity_date_time' => date('YmdHis'),
533 'subject' => $messageSubject,
1e035d58 534 'details' => $sms_text,
a3e3eea1 535 'status_id' => CRM_Core_OptionGroup::getValue('activity_status', 'Completed', 'name'),
536 );
537
538 $activity = CRM_Activity_BAO_Activity::create($activityParams);
539
540 CRM_Activity_BAO_Activity::sendSMSMessage($contactId,
1e035d58 541 $sms_text,
a3e3eea1 542 $html,
543 $smsParams,
544 $activity->id,
545 $userID
546 );
6a488035 547 }
a3e3eea1 548
549 if ($schedule->mode == 'Email' or $schedule->mode == 'User_Preference') {
550 // set up the parameters for CRM_Utils_Mail::send
551 $mailParams = array(
552 'groupName' => 'Scheduled Reminder Sender',
553 'from' => $from,
554 'toName' => $contact['display_name'],
555 'toEmail' => $email,
556 'subject' => $messageSubject,
557 'entity' => 'action_schedule',
558 'entity_id' => $scheduleID,
559 );
560
561 if (!$html || $contact['preferred_mail_format'] == 'Text' ||
6a488035 562 $contact['preferred_mail_format'] == 'Both'
a3e3eea1 563 ) {
564 // render the &amp; entities in text mode, so that the links work
565 $mailParams['text'] = str_replace('&amp;', '&', $text);
566 }
567 if ($html && ($contact['preferred_mail_format'] == 'HTML' ||
568 $contact['preferred_mail_format'] == 'Both'
569 )
570 ) {
571 $mailParams['html'] = $html;
572 }
573 $result = CRM_Utils_Mail::send($mailParams);
6a488035 574 }
6a488035
TO		 575 }
576 $schedule->free();
577
578 return $result;
579 }
580
581 /**
582 * Function to add the schedules reminders in the db
583 *
584 * @param array $params (reference ) an assoc array of name/value pairs
585 * @param array $ids the array that holds all the db ids
586 *
587 * @return object CRM_Core_DAO_ActionSchedule
588 * @access public
589 * @static
590 *
591 */
5284dd80 592 static function add(&$params, $ids = array()) {
6a488035
TO		 593 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
594 $actionSchedule->copyValues($params);
595
596 return $actionSchedule->save();
597 }
598
599 /**
600 * Takes a bunch of params that are needed to match certain criteria and
601 * retrieves the relevant objects. It also stores all the retrieved
602 * values in the default array
603 *
604 * @param array $params (reference ) an assoc array of name/value pairs
605 * @param array $values (reference ) an assoc array to hold the flattened values
606 *
607 * @return object CRM_Core_DAO_ActionSchedule object on success, null otherwise
608 * @access public
609 * @static
610 */
611 static function retrieve(&$params, &$values) {
612 if (empty($params)) {
613 return NULL;
614 }
615 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
616
617 $actionSchedule->copyValues($params);
618
619 if ($actionSchedule->find(TRUE)) {
620 $ids['actionSchedule'] = $actionSchedule->id;
621
622 CRM_Core_DAO::storeValues($actionSchedule, $values);
623
624 return $actionSchedule;
625 }
626 return NULL;
627 }
628
629 /**
630 * Function to delete a Reminder
631 *
632 * @param int $id ID of the Reminder to be deleted.
633 *
634 * @access public
635 * @static
636 */
637 static function del($id) {
638 if ($id) {
639 $dao = new CRM_Core_DAO_ActionSchedule();
640 $dao->id = $id;
641 if ($dao->find(TRUE)) {
642 $dao->delete();
643 return;
644 }
645 }
646 CRM_Core_Error::fatal(ts('Invalid value passed to delete function.'));
647 }
648
649 /**
650 * update the is_active flag in the db
651 *
652 * @param int $id id of the database record
653 * @param boolean $is_active value we want to set the is_active field
654 *
655 * @return Object DAO object on success, null otherwise
656 * @static
657 */
658 static function setIsActive($id, $is_active) {
659 return CRM_Core_DAO::setFieldValue('CRM_Core_DAO_ActionSchedule', $id, 'is_active', $is_active);
660 }
661
b5c2afd0
EM		 662 /**
663 * @param $mappingID
664 * @param $now
665 *
666 * @throws CRM_Core_Exception
667 */
6a488035
TO		 668 static function sendMailings($mappingID, $now) {
669 $domainValues = CRM_Core_BAO_Domain::getNameAndEmail();
670 $fromEmailAddress = "$domainValues[0] <$domainValues[1]>";
671
672 $mapping = new CRM_Core_DAO_ActionMapping();
673 $mapping->id = $mappingID;
674 $mapping->find(TRUE);
675
676 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
677 $actionSchedule->mapping_id = $mappingID;
678 $actionSchedule->is_active = 1;
679 $actionSchedule->find(FALSE);
680
681 $tokenFields = array();
682 $session = CRM_Core_Session::singleton();
683
684 while ($actionSchedule->fetch()) {
bb56ad3d 685 $extraSelect = $extraJoin = $extraWhere = $extraOn = '';
6a488035 686
1991bba1
DG		 687 if ($actionSchedule->from_email)
688 $fromEmailAddress = "$actionSchedule->from_name <$actionSchedule->from_email>";
689
690
6a488035
TO		 691 if ($actionSchedule->record_activity) {
692 if ($mapping->entity == 'civicrm_membership') {
693 $activityTypeID =
694 CRM_Core_OptionGroup::getValue('activity_type', 'Membership Renewal Reminder', 'name');
695 }
696 else {
697 $activityTypeID =
698 CRM_Core_OptionGroup::getValue('activity_type', 'Reminder Sent', 'name');
699 }
700
701 $activityStatusID =
702 CRM_Core_OptionGroup::getValue('activity_status', 'Completed', 'name');
703 }
704
705 if ($mapping->entity == 'civicrm_activity') {
706 $tokenEntity = 'activity';
707 $tokenFields = array('activity_id', 'activity_type', 'subject', 'details', 'activity_date_time');
708 $extraSelect = ', ov.label as activity_type, e.id as activity_id';
a4b156a7
PJ		 709 $extraJoin = "
710INNER JOIN civicrm_option_group og ON og.name = 'activity_type'
6a488035 711INNER JOIN civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id";
7188a78d 712 $extraOn = ' AND e.is_current_revision = 1 AND e.is_deleted = 0 ';
a4b156a7
PJ		 713 if ($actionSchedule->limit_to == 0) {
714 $extraJoin = "
715LEFT JOIN civicrm_option_group og ON og.name = 'activity_type'
716LEFT JOIN civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id";
a4b156a7 717 }
6a488035
TO		 718 }
719
720 if ($mapping->entity == 'civicrm_participant') {
721 $tokenEntity = 'event';
13837844 722 $tokenFields = array('event_type', 'title', 'event_id', 'start_date', 'end_date', 'summary', 'description', 'location', 'info_url', 'registration_url', 'fee_amount', 'contact_email', 'contact_phone', 'balance');
6a488035
TO		 723 $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 ';
724
725 $extraJoin = "
726INNER JOIN civicrm_event ev ON e.event_id = ev.id
727INNER JOIN civicrm_option_group og ON og.name = 'event_type'
728INNER JOIN civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id
729LEFT JOIN civicrm_loc_block lb ON lb.id = ev.loc_block_id
730LEFT JOIN civicrm_address address ON address.id = lb.address_id
731LEFT JOIN civicrm_email email ON email.id = lb.email_id
732LEFT JOIN civicrm_phone phone ON phone.id = lb.phone_id
733";
a4b156a7
PJ		 734 if ($actionSchedule->limit_to == 0) {
735 $extraJoin = "
736LEFT JOIN civicrm_event ev ON e.event_id = ev.id
737LEFT JOIN civicrm_option_group og ON og.name = 'event_type'
738LEFT JOIN civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id
739LEFT JOIN civicrm_loc_block lb ON lb.id = ev.loc_block_id
740LEFT JOIN civicrm_address address ON address.id = lb.address_id
741LEFT JOIN civicrm_email email ON email.id = lb.email_id
742LEFT JOIN civicrm_phone phone ON phone.id = lb.phone_id
743";
744 }
6a488035
TO		 745 }
746
747 if ($mapping->entity == 'civicrm_membership') {
748 $tokenEntity = 'membership';
749 $tokenFields = array('fee', 'id', 'join_date', 'start_date', 'end_date', 'status', 'type');
750 $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';
751 $extraJoin = '
752 INNER JOIN civicrm_membership_type mt ON e.membership_type_id = mt.id
753 INNER JOIN civicrm_membership_status ms ON e.status_id = ms.id';
a4b156a7
PJ		 754
755 if ($actionSchedule->limit_to == 0) {
756 $extraJoin = '
757 LEFT JOIN civicrm_membership_type mt ON e.membership_type_id = mt.id
758 LEFT JOIN civicrm_membership_status ms ON e.status_id = ms.id';
759 }
760 }
761
628801bf
AH		 762 if ($mapping->entity == 'civicrm_contact') {
763 $tokenEntity = 'contact';
764 //TODO: get full list somewhere!
765 $tokenFields = array('birth_date', 'last_name');
766 //TODO: is there anything to add here?
767 }
768
a4b156a7
PJ		 769 $entityJoinClause = "INNER JOIN {$mapping->entity} e ON e.id = reminder.entity_id";
770 if ($actionSchedule->limit_to == 0) {
771 $entityJoinClause = "LEFT JOIN {$mapping->entity} e ON e.id = reminder.entity_id";
772 $extraWhere .= " AND (e.id = reminder.entity_id OR reminder.entity_table = 'civicrm_contact')";
6a488035 773 }
bb56ad3d 774 $entityJoinClause .= $extraOn;
6a488035
TO		 775
776 $query = "
65aa2420 777SELECT reminder.id as reminderID, reminder.contact_id as contactID, reminder.entity_table as entityTable, reminder.*, e.id as entityID, e.* {$extraSelect}
6a488035 778FROM civicrm_action_log reminder
a4b156a7 779{$entityJoinClause}
6a488035
TO		 780{$extraJoin}
781WHERE reminder.action_schedule_id = %1 AND reminder.action_date_time IS NULL
782{$extraWhere}";
783
784 $dao = CRM_Core_DAO::executeQuery($query,
3e315abc 785 array(1 => array($actionSchedule->id, 'Integer'))
6a488035
TO		 786 );
787
788 while ($dao->fetch()) {
789 $entityTokenParams = array();
790 foreach ($tokenFields as $field) {
791 if ($field == 'location') {
792 $loc = array();
793 $stateProvince = CRM_Core_PseudoConstant::stateProvince();
794 $loc['street_address'] = $dao->street_address;
795 $loc['city'] = $dao->city;
a124ebd4 796 $loc['state_province'] = CRM_Utils_Array::value($dao->state_province_id, $stateProvince);
6a488035
TO		 797 $loc['postal_code'] = $dao->postal_code;
798 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_Address::format($loc);
799 }
800 elseif ($field == 'info_url') {
801 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_System::url('civicrm/event/info', 'reset=1&id=' . $dao->event_id, TRUE, NULL, FALSE);
802 }
803 elseif ($field == 'registration_url') {
804 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_System::url('civicrm/event/register', 'reset=1&id=' . $dao->event_id, TRUE, NULL, FALSE);
805 }
806 elseif (in_array($field, array('start_date','end_date','join_date','activity_date_time'))) {
807 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_Date::customFormat($dao->$field);
808 }
13837844 809 elseif ($field == 'balance') {
65aa2420 810 if ($dao->entityTable == 'civicrm_contact') {
811 $balancePay = 'N/A';
812 }
813 elseif (!empty($dao->entityID)) {
814 $info = CRM_Contribute_BAO_Contribution::getPaymentInfo($dao->entityID, 'event');
815 $balancePay = CRM_Utils_Array::value('balance', $info);
816 $balancePay = CRM_Utils_Money::format($balancePay);
817 }
9ad04fb4 818 $entityTokenParams["{$tokenEntity}." . $field] = $balancePay;
13837844 819 }
372ad74d
PJ		 820 elseif ($field == 'fee_amount') {
821 $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_Money::format($dao->$field);
822 }
6a488035
TO		 823 else {
824 $entityTokenParams["{$tokenEntity}." . $field] = $dao->$field;
825 }
826 }
827
828 $isError = 0;
a3e3eea1 829 $errorMsg = $toEmail = $toPhoneNumber = '';
830
831 if ($actionSchedule->mode == 'SMS' or $actionSchedule->mode == 'User_Preference') {
832 $filters = array('is_deceased' => 0, 'is_deleted' => 0, 'do_not_sms' => 0);
833 $toPhoneNumbers = CRM_Core_BAO_Phone::allPhones($dao->contactID, FALSE, 'Mobile', $filters);
834 //to get primary mobile ph,if not get a first mobile phONE
835 if (!empty($toPhoneNumbers)) {
836 $toPhoneNumberDetails = reset($toPhoneNumbers);
837 $toPhoneNumber = CRM_Utils_Array::value('phone', $toPhoneNumberDetails);
838 //contact allows to send sms
839 $toDoNotSms = 0;
840 }
841 }
842 if ($actionSchedule->mode == 'Email' or $actionSchedule->mode == 'User_Preference') {
843 $toEmail = CRM_Contact_BAO_Contact::getPrimaryEmail($dao->contactID);
844 }
845 if ($toEmail || !(empty($toPhoneNumber) or $toDoNotSms)) {
846 $to['email'] = $toEmail;
847 $to['phone'] = $toPhoneNumber;
6a488035
TO		 848 $result =
849 CRM_Core_BAO_ActionSchedule::sendReminder(
a4b156a7 850 $dao->contactID,
a3e3eea1 851 $to,
6a488035
TO		 852 $actionSchedule->id,
853 $fromEmailAddress,
854 $entityTokenParams
855 );
856
857 if (!$result || is_a($result, 'PEAR_Error')) {
858 // we could not send an email, for now we ignore, CRM-3406
859 $isError = 1;
860 }
861 }
862 else {
863 $isError = 1;
864 $errorMsg = "Couldn\'t find recipient\'s email address.";
865 }
866
867 // update action log record
868 $logParams = array(
869 'id' => $dao->reminderID,
870 'is_error' => $isError,
871 'message' => $errorMsg ? $errorMsg : "null",
872 'action_date_time' => $now,
873 );
874 CRM_Core_BAO_ActionLog::create($logParams);
875
876 // insert activity log record if needed
877 if ($actionSchedule->record_activity) {
878 $activityParams = array(
879 'subject' => $actionSchedule->title,
880 'details' => $actionSchedule->body_html,
b319d00a 881 'source_contact_id' =>
a4b156a7
PJ		 882 $session->get('userID') ? $session->get('userID') : $dao->contactID,
883 'target_contact_id' => $dao->contactID,
6a488035
TO		 884 'activity_date_time' => date('YmdHis'),
885 'status_id' => $activityStatusID,
886 'activity_type_id' => $activityTypeID,
887 'source_record_id' => $dao->entityID,
888 );
889 $activity = CRM_Activity_BAO_Activity::create($activityParams);
890 }
891 }
892
893 $dao->free();
894 }
895 }
896
b5c2afd0
EM		 897 /**
898 * @param $mappingID
899 * @param $now
900 * @param array $params
901 *
902 * @throws API_Exception
903 */
73f3e293 904 static function buildRecipientContacts($mappingID, $now, $params = array()) {
6a488035
TO		 905 $actionSchedule = new CRM_Core_DAO_ActionSchedule();
906 $actionSchedule->mapping_id = $mappingID;
907 $actionSchedule->is_active = 1;
73f3e293
E		 908 if(!empty($params)) {
909 _civicrm_api3_dao_set_filter($actionSchedule, $params, FALSE, 'ActionSchedule');
910 }
6a488035
TO		 911 $actionSchedule->find();
912
913 while ($actionSchedule->fetch()) {
914 $mapping = new CRM_Core_DAO_ActionMapping();
915 $mapping->id = $mappingID;
916 $mapping->find(TRUE);
917
6d98dbc7
PJ		 918 // note: $where - this filtering applies for both
919 // 'limit to' and 'addition to' options
920 // $limitWhere - this filtering applies only for
921 // 'limit to' option
922 $select = $join = $where = $limitWhere = array();
3e315abc 923 $limitTo = $actionSchedule->limit_to;
6a488035 924 $value = explode(CRM_Core_DAO::VALUE_SEPARATOR,
3e315abc 925 trim($actionSchedule->entity_value, CRM_Core_DAO::VALUE_SEPARATOR)
6a488035
TO		 926 );
927 $value = implode(',', $value);
928
929 $status = explode(CRM_Core_DAO::VALUE_SEPARATOR,
3e315abc 930 trim($actionSchedule->entity_status, CRM_Core_DAO::VALUE_SEPARATOR)
6a488035
TO		 931 );
932 $status = implode(',', $status);
933
8d657dde
AH		 934 $anniversary = false;
935
6a488035 936 if (!CRM_Utils_System::isNull($mapping->entity_recipient)) {
40f2fee3 937 $recipientOptions = CRM_Core_OptionGroup::values($mapping->entity_recipient, FALSE, FALSE, FALSE, NULL, 'name');
6a488035
TO		 938 }
939 $from = "{$mapping->entity} e";
940
941 if ($mapping->entity == 'civicrm_activity') {
00b1b9f1 942 $contactField = 'r.contact_id';
6cb6ba4c 943 $table = 'civicrm_activity e';
e7e657f0 944 $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
9e74e3ce 945 $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
946 $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
947 $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
b319d00a 948
3ca9aaf7 949 if (!is_null($limitTo)) {
84a3e359 950 if ($limitTo == 0) {
951 // including the activity target contacts if 'in addition' is defined
952 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$targetID}";
953 }
954 else {
955 switch (CRM_Utils_Array::value($actionSchedule->recipient, $recipientOptions)) {
956 case 'Activity Assignees':
957 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$assigneeID}";
958 break;
959
960 case 'Activity Source':
961 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$sourceID}";
962 break;
963
964 default:
965 case 'Activity Targets':
966 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$targetID}";
967 break;
968 }
bb56ad3d 969 }
6a488035
TO		 970 }
971 // build where clause
972 if (!empty($value)) {
973 $where[] = "e.activity_type_id IN ({$value})";
974 }
975 else {
976 $where[] = "e.activity_type_id IS NULL";
977 }
978 if (!empty($status)) {
979 $where[] = "e.status_id IN ({$status})";
980 }
981 $where[] = ' e.is_current_revision = 1 ';
982 $where[] = ' e.is_deleted = 0 ';
983
984 $dateField = 'e.activity_date_time';
985 }
986
987 if ($mapping->entity == 'civicrm_participant') {
6cb6ba4c 988 $table = 'civicrm_event r';
6a488035
TO		 989 $contactField = 'e.contact_id';
990 $join[] = 'INNER JOIN civicrm_event r ON e.event_id = r.id';
bb56ad3d 991 if ($actionSchedule->recipient_listing && $limitTo) {
6a488035 992 $rList = explode(CRM_Core_DAO::VALUE_SEPARATOR,
3e315abc 993 trim($actionSchedule->recipient_listing, CRM_Core_DAO::VALUE_SEPARATOR)
6a488035
TO		 994 );
995 $rList = implode(',', $rList);
996
997 switch ($recipientOptions[$actionSchedule->recipient]) {
40f2fee3 998 case 'participant_role':
6a488035
TO		 999 $where[] = "e.role_id IN ({$rList})";
1000 break;
1001
1002 default:
1003 break;
1004 }
1005 }
1006
1007 // build where clause
1008 if (!empty($value)) {
1009 $where[] = ($mapping->entity_value == 'event_type') ? "r.event_type_id IN ({$value})" : "r.id IN ({$value})";
1010 }
1011 else {
1012 $where[] = ($mapping->entity_value == 'event_type') ? "r.event_type_id IS NULL" : "r.id IS NULL";
1013 }
1014
6d98dbc7
PJ		 1015 // participant status criteria not to be implemented
1016 // for additional recipients
6a488035 1017 if (!empty($status)) {
6d98dbc7 1018 $limitWhere[] = "e.status_id IN ({$status})";
6a488035
TO		 1019 }
1020
1021 $where[] = 'r.is_active = 1';
1022 $where[] = 'r.is_template = 0';
1023 $dateField = str_replace('event_', 'r.', $actionSchedule->start_action_date);
1024 }
1025
1026 $notINClause = '';
1027 if ($mapping->entity == 'civicrm_membership') {
1028 $contactField = 'e.contact_id';
6cb6ba4c 1029 $table = 'civicrm_membership e';
6a488035
TO		 1030 // build where clause
1031 if ( $status == 2 ) {
1032 //auto-renew memberships
1033 $where[] = "e.contribution_recur_id IS NOT NULL ";
1034 }
1035 elseif ( $status == 1 ) {
1036 $where[] = "e.contribution_recur_id IS NULL ";
1037 }
1038
1039 // build where clause
1040 if (!empty($value)) {
1041 $where[] = "e.membership_type_id IN ({$value})";
1042 }
1043 else {
1044 $where[] = "e.membership_type_id IS NULL";
1045 }
1046
eec002d2 1047 $where[] = "( e.is_override IS NULL OR e.is_override = 0 )";
6a488035
TO		 1048 $dateField = str_replace('membership_', 'e.', $actionSchedule->start_action_date);
1049 $notINClause = self::permissionedRelationships($contactField);
b319d00a 1050
7e9f2e18 1051 $membershipStatus = CRM_Member_PseudoConstant::membershipStatus(NULL, "(is_current_member = 1 OR name = 'Expired')", 'id');
b1998d9c 1052 $mStatus = implode (',', $membershipStatus);
ed920eb1 1053 $where[] = "e.status_id IN ({$mStatus})";
6a488035 1054 }
6d98dbc7 1055
628801bf 1056 if ($mapping->entity == 'civicrm_contact') {
8d657dde
AH		 1057 if ($value == 'birth_date') {
1058 $dateDBField = 'birth_date';
1059 $table = 'civicrm_contact e';
1060 $contactField = 'e.id';
1061 $where[] = 'e.is_deleted = 0';
1062 $where[] = 'e.is_deceased = 0';
1063 }
1064 else {
1065 //custom field
1066 $customFieldParams = array('id' => substr($value, 7));
1067 $customGroup = $customField = array();
1068 CRM_Core_BAO_CustomField::retrieve($customFieldParams, $customField);
1069 $dateDBField = $customField['column_name'];
1070 $customGroupParams = array('id' => $customField['custom_group_id'], $customGroup);
1071 CRM_Core_BAO_CustomGroup::retrieve($customGroupParams, $customGroup);
1072 $from = $table = "{$customGroup['table_name']} e";
1073 $contactField = 'e.entity_id';
1074 $where[] = '1'; // possible to have no "where" in this case
1075 }
1076
1077 $status_ = explode(',', $status);
1078 if (in_array(2, $status_)) {
1079 // anniversary mode:
43030baf 1080 $dateField = 'DATE_ADD(e.' . $dateDBField . ', INTERVAL ROUND(DATEDIFF(DATE(' . $now . '), e.' . $dateDBField . ') / 365) YEAR)';
8d657dde
AH		 1081 $anniversary = true;
1082 }
1083 else {
1084 // regular mode:
1085 $dateField = 'e.' . $dateDBField;
1086 }
628801bf 1087 // TODO get this working
8d657dde
AH		 1088
1089 // TODO: Make sure everything's provided for repetition, etc.
628801bf
AH		 1090 }
1091
f10c69fa
DL		 1092 // CRM-13577 Introduce Smart Groups Handling
1093 if ($actionSchedule->group_id) {
1094
1095 // Need to check if its a smart group or not
1096 // Then decide which table to join onto the query
2dd1b730 1097 $group = CRM_Contact_DAO_Group::getTableName();
f10c69fa
DL		 1098
1099 // Get the group information
1100 $sql = "
1101SELECT $group.id, $group.cache_date, $group.saved_search_id, $group.children
1102FROM $group
1103WHERE $group.id = {$actionSchedule->group_id}
1104";
1105
1106 $groupDAO = CRM_Core_DAO::executeQuery($sql);
1107 $isSmartGroup = FALSE;
1108 if (
1109 $groupDAO->fetch() &&
1110 !empty($groupDAO->saved_search_id)
1111 ) {
1112 // Check that the group is in place in the cache and up to date
1113 CRM_Contact_BAO_GroupContactCache::check($actionSchedule->group_id);
1114 // Set smart group flag
1115 $isSmartGroup = TRUE;
1116 }
1117 }
1118 // CRM-13577 End Introduce Smart Groups Handling
1119
6cb6ba4c 1120 if ($limitTo) {
1121 if ($actionSchedule->group_id) {
f10c69fa
DL		 1122 // CRM-13577 If smart group then use Cache table
1123 if ($isSmartGroup) {
1124 $join[] = "INNER JOIN civicrm_group_contact_cache grp ON {$contactField} = grp.contact_id";
1125 $where[] = "grp.group_id IN ({$actionSchedule->group_id})";
1126 } else {
1127 $join[] = "INNER JOIN civicrm_group_contact grp ON {$contactField} = grp.contact_id AND grp.status = 'Added'";
1128 $where[] = "grp.group_id IN ({$actionSchedule->group_id})";
1129 }
6cb6ba4c 1130 }
1131 elseif (!empty($actionSchedule->recipient_manual)) {
1132 $rList = CRM_Utils_Type::escape($actionSchedule->recipient_manual, 'String');
1133 $where[] = "{$contactField} IN ({$rList})";
1134 }
6a488035 1135 }
3ca9aaf7 1136 elseif (!is_null($limitTo)) {
a4b156a7 1137 $addGroup = $addWhere = '';
6cb6ba4c 1138 if ($actionSchedule->group_id) {
f10c69fa
DL		 1139 // CRM-13577 If smart group then use Cache table
1140 if ($isSmartGroup) {
1141 $addGroup = " INNER JOIN civicrm_group_contact_cache grp ON c.id = grp.contact_id";
1142 $addWhere = " grp.group_id IN ({$actionSchedule->group_id})";
1143 } else {
1144 $addGroup = " INNER JOIN civicrm_group_contact grp ON c.id = grp.contact_id AND grp.status = 'Added'";
1145 $addWhere = " grp.group_id IN ({$actionSchedule->group_id})";
1146 }
6cb6ba4c 1147 }
1148 if (!empty($actionSchedule->recipient_manual)) {
1149 $rList = CRM_Utils_Type::escape($actionSchedule->recipient_manual, 'String');
1150 $addWhere = "c.id IN ({$rList})";
1151 }
6a488035 1152 }
6d98dbc7 1153
3e315abc 1154 $select[] = "{$contactField} as contact_id";
1155 $select[] = 'e.id as entity_id';
1156 $select[] = "'{$mapping->entity}' as entity_table";
1157 $select[] = "{$actionSchedule->id} as action_schedule_id";
6a488035
TO		 1158 $reminderJoinClause = "civicrm_action_log reminder ON reminder.contact_id = {$contactField} AND
1159reminder.entity_id = e.id AND
1160reminder.entity_table = '{$mapping->entity}' AND
1161reminder.action_schedule_id = %1";
1162
8d657dde
AH		 1163 if ($anniversary) {
1164 // only consider reminders less than 11 months ago
1165 $reminderJoinClause .= " AND reminder.action_date_time > DATE_SUB({$now}, INTERVAL 11 MONTH)";
1166 }
1167
1168 if ($table != 'civicrm_contact e') {
1169 $join[] = "INNER JOIN civicrm_contact c ON c.id = {$contactField} AND c.is_deleted = 0 AND c.is_deceased = 0 ";
1170 }
6a488035
TO		 1171
1172 if ($actionSchedule->start_action_date) {
3e315abc 1173 $startDateClause = array();
1174 $op = ($actionSchedule->start_action_condition == 'before' ? '<=' : '>=');
1175 $operator = ($actionSchedule->start_action_condition == 'before' ? 'DATE_SUB' : 'DATE_ADD');
1176 $date = $operator . "({$dateField}, INTERVAL {$actionSchedule->start_action_offset} {$actionSchedule->start_action_unit})";
6a488035
TO		 1177 $startDateClause[] = "'{$now}' >= {$date}";
1178 if ($mapping->entity == 'civicrm_participant') {
1179 $startDateClause[] = $operator. "({$now}, INTERVAL 1 DAY ) {$op} " . $dateField;
1180 }
1181 else {
1182 $startDateClause[] = "DATE_SUB({$now}, INTERVAL 1 DAY ) <= {$date}";
1183 }
1184
1185 $startDate = implode(' AND ', $startDateClause);
1186 }
1187 elseif ($actionSchedule->absolute_date) {
1188 $startDate = "DATEDIFF(DATE('{$now}'),'{$actionSchedule->absolute_date}') = 0";
1189 }
1190
1191 // ( now >= date_built_from_start_time ) OR ( now = absolute_date )
1192 $dateClause = "reminder.id IS NULL AND {$startDate}";
1193
1194 // start composing query
1195 $selectClause = 'SELECT ' . implode(', ', $select);
3e315abc 1196 $fromClause = "FROM $from";
1197 $joinClause = !empty($join) ? implode(' ', $join) : '';
1198 $whereClause = 'WHERE ' . implode(' AND ', $where);
7188a78d
PJ		 1199 $limitWhereClause = '';
1200 if (!empty($limitWhere)) {
1201 $limitWhereClause = ' AND ' . implode(' AND ', $limitWhere);
1202 }
6cb6ba4c 1203
6a488035
TO		 1204 $query = "
1205INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
1206{$selectClause}
1207{$fromClause}
1208{$joinClause}
1209LEFT JOIN {$reminderJoinClause}
7188a78d 1210{$whereClause} {$limitWhereClause} AND {$dateClause} {$notINClause}
bb56ad3d 1211";
6a488035
TO		 1212 CRM_Core_DAO::executeQuery($query, array(1 => array($actionSchedule->id, 'Integer')));
1213
3ca9aaf7 1214 if (!is_null($limitTo) && $limitTo == 0 && (!empty($addGroup) || !empty($addWhere))) {
82bf20c7
PJ		 1215 $additionWhere = ' WHERE ';
1216 if ($actionSchedule->start_action_date) {
60d5bcb0 1217 $additionWhere = $whereClause . ' AND ';
82bf20c7 1218 }
3874157e
PJ		 1219 $contactTable = "civicrm_contact c";
1220 $addSelect = "SELECT c.id as contact_id, c.id as entity_id, 'civicrm_contact' as entity_table, {$actionSchedule->id} as action_schedule_id";
1221 $additionReminderClause = "civicrm_action_log reminder ON reminder.contact_id = c.id AND
1222 reminder.entity_id = c.id AND
1223 reminder.entity_table = 'civicrm_contact' AND
1224 reminder.action_schedule_id = {$actionSchedule->id}";
8010925d 1225 $addWhereClause = '';
1226 if ($addWhere) {
1227 $addWhereClause = "AND {$addWhere}";
1228 }
bb56ad3d
PJ		 1229 $insertAdditionalSql ="
1230INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
3874157e 1231{$addSelect}
fe9bc2c4 1232FROM ({$contactTable})
3874157e 1233LEFT JOIN {$additionReminderClause}
bb56ad3d 1234{$addGroup}
fe9bc2c4 1235WHERE c.is_deleted = 0 AND c.is_deceased = 0
8010925d 1236{$addWhereClause}
fe9bc2c4 1237
82bf20c7 1238AND c.id NOT IN (
bb56ad3d
PJ		 1239 SELECT rem.contact_id
1240 FROM civicrm_action_log rem INNER JOIN {$mapping->entity} e ON rem.entity_id = e.id
1241 WHERE rem.action_schedule_id = {$actionSchedule->id}
1242 AND rem.entity_table = '{$mapping->entity}'
1243 )
7188a78d 1244GROUP BY c.id
bb56ad3d
PJ		 1245";
1246 CRM_Core_DAO::executeQuery($insertAdditionalSql);
1247 }
6a488035
TO		 1248 // if repeat is turned ON:
1249 if ($actionSchedule->is_repeat) {
1250 $repeatEvent = ($actionSchedule->end_action == 'before' ? 'DATE_SUB' : 'DATE_ADD') . "({$dateField}, INTERVAL {$actionSchedule->end_frequency_interval} {$actionSchedule->end_frequency_unit})";
1251
1252 if ($actionSchedule->repetition_frequency_unit == 'day') {
1253 $hrs = 24 * $actionSchedule->repetition_frequency_interval;
1254 }
1255 elseif ($actionSchedule->repetition_frequency_unit == 'week') {
1256 $hrs = 24 * $actionSchedule->repetition_frequency_interval * 7;
1257 }
9aec4088 1258 elseif ($actionSchedule->repetition_frequency_unit == 'month') {
1259 $hrs = "24*(DATEDIFF(DATE_ADD(latest_log_time, INTERVAL 1 MONTH ), latest_log_time))";
1260 }
1261 elseif ($actionSchedule->repetition_frequency_unit == 'year') {
1262 $hrs = "24*(DATEDIFF(DATE_ADD(latest_log_time, INTERVAL 1 YEAR ), latest_log_time))";
1263 }
6a488035
TO		 1264 else {
1265 $hrs = $actionSchedule->repetition_frequency_interval;
1266 }
1267
1268 // (now <= repeat_end_time )
1269 $repeatEventClause = "'{$now}' <= {$repeatEvent}";
1270 // diff(now && logged_date_time) >= repeat_interval
1271 $havingClause = "HAVING TIMEDIFF({$now}, latest_log_time) >= TIME('{$hrs}:00:00')";
1272 $groupByClause = 'GROUP BY reminder.contact_id, reminder.entity_id, reminder.entity_table';
1273 $selectClause .= ', MAX(reminder.action_date_time) as latest_log_time';
861d11c4
DG		 1274 //CRM-15376 - do not send our reminders if original criteria no longer applies
1275 // the first part of the startDateClause array is the earliest the reminder can be sent. If the
1276 // event (e.g membership_end_date) has changed then the reminder may no longer apply
1277 // @todo - this only handles events that get moved later. Potentially they might get moved earlier
1278 $originalEventStartDateClause = empty($startDateClause) ? '' : 'AND' . $startDateClause[0];
6a488035
TO		 1279 $sqlInsertValues = "{$selectClause}
1280{$fromClause}
1281{$joinClause}
1282INNER JOIN {$reminderJoinClause}
861d11c4 1283{$whereClause} {$limitWhereClause} AND {$repeatEventClause} {$originalEventStartDateClause} {$notINClause}
6a488035
TO		 1284{$groupByClause}
1285{$havingClause}";
1286
1287 $valsqlInsertValues = CRM_Core_DAO::executeQuery($sqlInsertValues, array(1 => array($actionSchedule->id, 'Integer')));
1288
1289 $arrValues = array();
1290 while ($valsqlInsertValues->fetch()) {
1291 $arrValues[] = "( {$valsqlInsertValues->contact_id}, {$valsqlInsertValues->entity_id}, '{$valsqlInsertValues->entity_table}',{$valsqlInsertValues->action_schedule_id} )";
1292 }
1293
1294 $valString = implode(',', $arrValues);
1295
1296 if ($valString) {
1297 $query = '
1298 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id) VALUES ' . $valString;
1299 CRM_Core_DAO::executeQuery($query, array(1 => array($actionSchedule->id, 'Integer')));
1300 }
3874157e 1301
84a3e359 1302 if (!empty($limitTo) && $limitTo == 0) {
3874157e
PJ		 1303 $addSelect .= ', MAX(reminder.action_date_time) as latest_log_time';
1304 $sqlEndEventCheck = "
1305SELECT * FROM {$table}
1306{$whereClause} AND {$repeatEventClause} LIMIT 1";
1307
1308 $daoCheck = CRM_Core_DAO::executeQuery($sqlEndEventCheck);
1309 if ($daoCheck->fetch()) {
1310 $valSqlAdditionInsert = "
1311{$addSelect}
1312FROM {$contactTable}
1313{$addGroup}
1314INNER JOIN {$additionReminderClause}
1315WHERE {$addWhere} AND c.is_deleted = 0 AND c.is_deceased = 0
1316GROUP BY reminder.contact_id
1317{$havingClause}
1318";
1319 $daoForVals = CRM_Core_DAO::executeQuery($valSqlAdditionInsert);
1320 $addValues = array();
1321 while ($daoForVals->fetch()) {
1322 $addValues[] = "( {$daoForVals->contact_id}, {$daoForVals->entity_id}, '{$daoForVals->entity_table}',{$daoForVals->action_schedule_id} )";
1323 }
1324 $valString = implode(',', $addValues);
1325
1326 if ($valString) {
1327 $query = '
1328 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id) VALUES ' . $valString;
1329 CRM_Core_DAO::executeQuery($query);
1330 }
1331 }
1332 }
6a488035
TO		 1333 }
1334 }
1335 }
1336
b5c2afd0
EM		 1337 /**
1338 * @param $field
1339 *
1340 * @return null|string
1341 */
6a488035
TO		 1342 static function permissionedRelationships($field) {
1343 $query = '
1344SELECT 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
1345FROM civicrm_membership m
1346LEFT JOIN civicrm_membership cm ON cm.id = m.owner_membership_id
1347LEFT JOIN civicrm_membership_type cmt ON cmt.id = m.membership_type_id
1348LEFT 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 )
1349 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 ) )
1350WHERE m.owner_membership_id IS NOT NULL AND
1351 ( rel.is_permission_a_b = 0 OR rel.is_permission_b_a = 0)
1352
1353';
1354 $excludeIds = array();
1355 $dao = CRM_Core_DAO::executeQuery($query, array());
1356 while ($dao->fetch()) {
94254643 1357 if ($dao->slave_contact == $dao->contact_id_a && $dao->is_permission_a_b == 0) {
6a488035
TO		 1358 $excludeIds[] = $dao->slave_contact;
1359 }
94254643 1360 elseif ($dao->slave_contact == $dao->contact_id_b && $dao->is_permission_b_a == 0) {
6a488035
TO		 1361 $excludeIds[] = $dao->slave_contact;
1362 }
1363 }
1364
1365 if (!empty($excludeIds)) {
1366 $clause = "AND {$field} NOT IN ( " .implode(', ', $excludeIds) . ' ) ';
1367 return $clause;
1368 }
1369 return NULL;
1370 }
1371
b5c2afd0
EM		 1372 /**
1373 * @param null $now
1374 * @param array $params
1375 *
1376 * @return array
1377 */
73f3e293 1378 static function processQueue($now = NULL, $params = array()) {
6a488035
TO		 1379 $now = $now ? CRM_Utils_Time::setTime($now) : CRM_Utils_Time::getTime();
1380
1381 $mappings = self::getMapping();
1382 foreach ($mappings as $mappingID => $mapping) {
73f3e293 1383 self::buildRecipientContacts($mappingID, $now, $params);
6a488035
TO		 1384 self::sendMailings($mappingID, $now);
1385 }
1386
1387 $result = array(
1388 'is_error' => 0,
1389 'messages' => ts('Sent all scheduled reminders successfully'),
1390 );
1391 return $result;
1392 }
1393
9a4df24c
EM		 1394 /**
1395 * @param $id
1396 * @param $mappingID
1397 *
1398 * @return null|string
1399 */
6a488035
TO		 1400 static function isConfigured($id, $mappingID) {
1401 $queryString = "SELECT count(id) FROM civicrm_action_schedule
1402 WHERE mapping_id = %1 AND
1403 entity_value = %2";
1404
3e315abc 1405 $params = array(
1406 1 => array($mappingID, 'Integer'),
1407 2 => array($id, 'Integer'),
6a488035
TO		 1408 );
1409 return CRM_Core_DAO::singleValueQuery($queryString, $params);
1410 }
1411
b5c2afd0
EM		 1412 /**
1413 * @param $mappingID
1414 * @param $recipientType
1415 *
1416 * @return array
1417 */
6a488035
TO		 1418 static function getRecipientListing($mappingID, $recipientType) {
1419 $options = array();
1420 if (!$mappingID || !$recipientType) {
1421 return $options;
1422 }
1423
1424 $mapping = self::getMapping($mappingID);
1425
1426 switch ($mapping['entity']) {
1427 case 'civicrm_participant':
e7e657f0 1428 $eventContacts = CRM_Core_OptionGroup::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'name');
a7488080 1429 if (empty($eventContacts[$recipientType])) {
6a488035
TO		 1430 return $options;
1431 }
40f2fee3 1432 if ($eventContacts[$recipientType] == 'participant_role') {
6a488035
TO		 1433 $options = CRM_Event_PseudoConstant::participantRole();
1434 }
1435 break;
1436 }
1437
1438 return $options;
1439 }
1440}