CRM/Core/BAO/ActionSchedule.php

   1 <?php
   2 /*
   3   +--------------------------------------------------------------------+
   4   | CiviCRM version 4.5                                                |
   5   +--------------------------------------------------------------------+
   6   | Copyright (C) 2011 Marty Wright                                    |
   7   | Licensed to CiviCRM under the Academic Free License version 3.0.   |
   8   +--------------------------------------------------------------------+
   9   | This file is a part of CiviCRM.                                    |
  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-2014
  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   /**
  43    * @param null $id
  44    *
  45    * @return array
  46    */
  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
  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
  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();
  97     $activityStatus = CRM_Core_PseudoConstant::activityStatus();
  98     $activityType   = CRM_Core_PseudoConstant::activityType(TRUE, TRUE);
  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();
 104     $autoRenew = CRM_Core_OptionGroup::values('auto_renew_options');
 105     $membershipType = CRM_Member_PseudoConstant::membershipType();
 106     $dateFieldParams = array('data_type' => 'Date');
 107     $dateFields = self::getDateFields();
 108     $contactOptions = CRM_Core_OptionGroup::values('contact_date_reminder_options');
 109
 110     asort($activityType);
 111
 112     $sel1 = $sel2 = $sel3 = $sel4 = $sel5 = array();
 113     $options = array(
 114       'manual' => ts('Choose Recipient(s)'),
 115       'group' => ts('Select Group'),
 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) {
 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);
 131       $entityMapping[$key] = CRM_Utils_Array::value('entity', $value);
 132
 133       $sel1Val = NULL;
 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;
 169
 170         case 'civicrm_contact':
 171           if ($value['entity'] == 'civicrm_contact') {
 172             $sel1Val = ts('Contact');
 173           }
 174           $sel2[$key] = $dateFields;
 175           break;
 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':
 189             $activityContacts = CRM_Core_OptionGroup::values('activity_contacts');
 190             $sel5[$entityRecipient] = $activityContacts + $options;
 191             $recipientMapping += CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
 192             break;
 193
 194           case 'event_contacts':
 195             $eventContacts = CRM_Core_OptionGroup::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'label', TRUE, FALSE, 'name');
 196             $sel5[$entityRecipient] = $eventContacts + $options;
 197             $recipientMapping += CRM_Core_OptionGroup::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'name', TRUE, FALSE, 'name');
 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);
 210       $statusLabel = array('- ' . strtolower(CRM_Utils_Array::value('entity_status_label', $value)) . ' -');
 211       $id = CRM_Utils_Array::value('id', $value);
 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
 241         case 'contact_date_reminder_options':
 242           foreach ($sel3[$id] as $kkey => & $vval) {
 243             $vval = $contactOptions;
 244           }
 245           break;
 246
 247         case '':
 248           $sel3[$id] = '';
 249           break;
 250
 251       }
 252     }
 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
 264   /**
 265    * @param null $id
 266    *
 267    * @return array
 268    */
 269   static function getSelection1($id = NULL) {
 270     $mapping = self::getMapping($id);
 271     $sel4 = $sel5 = array();
 272     $options = array(
 273       'manual' => ts('Choose Recipient(s)'),
 274       'group' => ts('Select Group'),
 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':
 292           $activityContacts = CRM_Core_OptionGroup::values('activity_contacts');
 293           $sel5[$id] = $activityContacts + $options;
 294           $recipientMapping += CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
 295           break;
 296
 297         case 'event_contacts':
 298           $eventContacts = CRM_Core_OptionGroup::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'label', TRUE, FALSE, 'name');
 299           $sel5[$id] = $eventContacts + $options;
 300           $recipientMapping += CRM_Core_OptionGroup::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'name', TRUE, FALSE, 'name');
 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    *
 319    * @param bool $namesOnly return simple list of names
 320    *
 321    * @param null $entityValue
 322    * @param null $id
 323    *
 324    * @return array  (reference)   reminder list
 325    * @static
 326    * @access public
 327    */
 328   static function &getList($namesOnly = FALSE, $entityValue = NULL, $id = NULL) {
 329     $activity_type = CRM_Core_PseudoConstant::activityType(TRUE, TRUE);
 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();
 336     $civicrm_contact = self::getDateFields();
 337
 338     $auto_renew_options = CRM_Core_OptionGroup::values('auto_renew_options');
 339     $contact_date_reminder_options = CRM_Core_OptionGroup::values('contact_date_reminder_options');
 340     $civicrm_membership_type = CRM_Member_PseudoConstant::membershipType();
 341
 342     $entity = array(
 343       'civicrm_activity' => 'Activity',
 344       'civicrm_participant' => 'Event',
 345       'civicrm_membership' => 'Member',
 346       'civicrm_contact' => 'Contact',
 347     );
 348
 349     $query = "
 350 SELECT
 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
 366 FROM civicrm_action_schedule cas
 367 LEFT JOIN civicrm_action_mapping cam ON (cam.id = cas.mapping_id)
 368 ";
 369     $params = CRM_Core_DAO::$_nullArray;
 370     $where = " WHERE 1 ";
 371     if ($entityValue and $id) {
 372       $where .= "
 373 AND   cas.entity_value = $id AND
 374         cam.entity_value = '$entityValue'";
 375
 376       $params = array(
 377         1 => array($id, 'Integer'),
 378         2 => array($entityValue, 'String'),
 379       );
 380     }
 381     $where .= " AND cas.used_for IS NULL";
 382     $query .= $where;
 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
 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    */
 426   static function sendReminder($contactId, $to, $scheduleID, $from, $tokenParams) {
 427     $email = $to['email'];
 428     $phoneNumber = $to['phone'];
 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;
 439       $sms_body_text = $schedule->sms_body_text;
 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
 464       $type = array('body_html' => 'html', 'body_text' => 'text', 'sms_body_text' => 'text');
 465
 466       foreach ($type as $bodyType => $value) {
 467         $dummy_mail = new CRM_Mailing_BAO_Mailing();
 468         if ($bodyType == 'sms_body_text') {
 469           $dummy_mail->body_text = $$bodyType;
 470         }
 471         else {
 472           $dummy_mail->$bodyType = $$bodyType;
 473         }
 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;
 486       $sms_text = $sms_body_text;
 487
 488       $smarty = CRM_Core_Smarty::singleton();
 489       foreach (array(
 490           'text', 'html', 'sms_text') as $elem) {
 491         $$elem = $smarty->fetch("string:{$$elem}");
 492       }
 493
 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) {
 506             if (!isset($subjectToken[$type])) {
 507               $subjectToken[$type] = array();
 508             }
 509             $subjectToken[$type][] = $name;
 510           }
 511         }
 512       }
 513
 514       $messageSubject = CRM_Utils_Token::replaceContactTokens($body_subject, $contact, FALSE, $subjectToken);
 515       $messageSubject = CRM_Utils_Token::replaceDomainTokens($messageSubject, $domain, TRUE, $subjectToken);
 516       $messageSubject = CRM_Utils_Token::replaceComponentTokens($messageSubject, $contact, $subjectToken, TRUE);
 517       $messageSubject = CRM_Utils_Token::replaceHookTokens($messageSubject, $contact, $categories, TRUE);
 518
 519       $messageSubject = $smarty->fetch("string:{$messageSubject}");
 520
 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         );
 529         $activityParams = array(
 530           'source_contact_id' => $userID,
 531           'activity_type_id' => $activityTypeID,
 532           'activity_date_time' => date('YmdHis'),
 533           'subject' => $messageSubject,
 534           'details' => $sms_text,
 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,
 541           $sms_text,
 542           $html,
 543           $smsParams,
 544           $activity->id,
 545           $userID
 546         );
 547       }
 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' ||
 562           $contact['preferred_mail_format'] == 'Both'
 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);
 574       }
 575     }
 576     $schedule->free();
 577
 578     return $result;
 579   }
 580
 581   /**
 582    * 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 CRM_Core_DAO_ActionSchedule
 588    * @access public
 589    * @static
 590    *
 591    */
 592   static function add(&$params, $ids = array()) {
 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 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    * 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
 662   /**
 663    * @param $mappingID
 664    * @param $now
 665    *
 666    * @throws CRM_Core_Exception
 667    */
 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()) {
 685       $extraSelect = $extraJoin = $extraWhere = $extraOn = '';
 686
 687     if ($actionSchedule->from_email)
 688             $fromEmailAddress = "$actionSchedule->from_name <$actionSchedule->from_email>";
 689
 690
 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';
 709         $extraJoin   = "
 710 INNER JOIN civicrm_option_group og ON og.name = 'activity_type'
 711 INNER JOIN civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id";
 712         $extraOn = ' AND e.is_current_revision = 1 AND e.is_deleted = 0 ';
 713         if ($actionSchedule->limit_to == 0) {
 714           $extraJoin   = "
 715 LEFT JOIN civicrm_option_group og ON og.name = 'activity_type'
 716 LEFT JOIN civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id";
 717         }
 718       }
 719
 720       if ($mapping->entity == 'civicrm_participant') {
 721         $tokenEntity = 'event';
 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');
 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   = "
 726 INNER JOIN civicrm_event ev ON e.event_id = ev.id
 727 INNER JOIN civicrm_option_group og ON og.name = 'event_type'
 728 INNER JOIN civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id
 729 LEFT  JOIN civicrm_loc_block lb ON lb.id = ev.loc_block_id
 730 LEFT  JOIN civicrm_address address ON address.id = lb.address_id
 731 LEFT  JOIN civicrm_email email ON email.id = lb.email_id
 732 LEFT  JOIN civicrm_phone phone ON phone.id = lb.phone_id
 733 ";
 734         if ($actionSchedule->limit_to == 0) {
 735           $extraJoin   = "
 736 LEFT JOIN civicrm_event ev ON e.event_id = ev.id
 737 LEFT JOIN civicrm_option_group og ON og.name = 'event_type'
 738 LEFT JOIN civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id
 739 LEFT JOIN civicrm_loc_block lb ON lb.id = ev.loc_block_id
 740 LEFT JOIN civicrm_address address ON address.id = lb.address_id
 741 LEFT JOIN civicrm_email email ON email.id = lb.email_id
 742 LEFT JOIN civicrm_phone phone ON phone.id = lb.phone_id
 743 ";
 744         }
 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';
 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
 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
 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')";
 773       }
 774       $entityJoinClause .= $extraOn;
 775
 776       $query = "
 777 SELECT reminder.id as reminderID, reminder.contact_id as contactID, reminder.entity_table as entityTable, reminder.*, e.id as entityID, e.* {$extraSelect}
 778 FROM  civicrm_action_log reminder
 779 {$entityJoinClause}
 780 {$extraJoin}
 781 WHERE reminder.action_schedule_id = %1 AND reminder.action_date_time IS NULL
 782 {$extraWhere}";
 783
 784       $dao = CRM_Core_DAO::executeQuery($query,
 785         array(1 => array($actionSchedule->id, 'Integer'))
 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;
 796             $loc['state_province'] = CRM_Utils_Array::value($dao->state_province_id, $stateProvince);
 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           }
 809           elseif ($field == 'balance') {
 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             }
 818             $entityTokenParams["{$tokenEntity}." . $field] = $balancePay;
 819           }
 820           elseif ($field == 'fee_amount') {
 821             $entityTokenParams["{$tokenEntity}." . $field] = CRM_Utils_Money::format($dao->$field);
 822           }
 823           else {
 824             $entityTokenParams["{$tokenEntity}." . $field] = $dao->$field;
 825           }
 826         }
 827
 828         $isError  = 0;
 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;
 848           $result =
 849             CRM_Core_BAO_ActionSchedule::sendReminder(
 850               $dao->contactID,
 851               $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,
 881             'source_contact_id' =>
 882             $session->get('userID') ? $session->get('userID') : $dao->contactID,
 883             'target_contact_id' => $dao->contactID,
 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
 897   /**
 898    * @param $mappingID
 899    * @param $now
 900    * @param array $params
 901    *
 902    * @throws API_Exception
 903    */
 904   static function buildRecipientContacts($mappingID, $now, $params = array()) {
 905     $actionSchedule = new CRM_Core_DAO_ActionSchedule();
 906     $actionSchedule->mapping_id = $mappingID;
 907     $actionSchedule->is_active = 1;
 908     if(!empty($params)) {
 909       _civicrm_api3_dao_set_filter($actionSchedule, $params, FALSE, 'ActionSchedule');
 910     }
 911     $actionSchedule->find();
 912
 913     while ($actionSchedule->fetch()) {
 914       $mapping = new CRM_Core_DAO_ActionMapping();
 915       $mapping->id = $mappingID;
 916       $mapping->find(TRUE);
 917
 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();
 923       $limitTo = $actionSchedule->limit_to;
 924       $value = explode(CRM_Core_DAO::VALUE_SEPARATOR,
 925         trim($actionSchedule->entity_value, CRM_Core_DAO::VALUE_SEPARATOR)
 926       );
 927       $value = implode(',', $value);
 928
 929       $status = explode(CRM_Core_DAO::VALUE_SEPARATOR,
 930         trim($actionSchedule->entity_status, CRM_Core_DAO::VALUE_SEPARATOR)
 931       );
 932       $status = implode(',', $status);
 933
 934       $anniversary = false;
 935
 936       if (!CRM_Utils_System::isNull($mapping->entity_recipient)) {
 937         if ($mapping->entity_recipient == 'event_contacts') {
 938           $recipientOptions = CRM_Core_OptionGroup::values($mapping->entity_recipient, FALSE, FALSE, FALSE, NULL, 'name', TRUE, FALSE, 'name');
 939         }
 940         else {
 941           $recipientOptions = CRM_Core_OptionGroup::values($mapping->entity_recipient, FALSE, FALSE, FALSE, NULL, 'name');
 942         }
 943       }
 944       $from = "{$mapping->entity} e";
 945
 946       if ($mapping->entity == 'civicrm_activity') {
 947         $contactField = 'r.contact_id';
 948         $table = 'civicrm_activity e';
 949         $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
 950         $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
 951         $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
 952         $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 953
 954         if (!is_null($limitTo)) {
 955           if ($limitTo == 0) {
 956             // including the activity target contacts if 'in addition' is defined
 957             $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$targetID}";
 958           }
 959           else {
 960             switch (CRM_Utils_Array::value($actionSchedule->recipient, $recipientOptions)) {
 961               case 'Activity Assignees':
 962                 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$assigneeID}";
 963                 break;
 964
 965               case 'Activity Source':
 966                 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$sourceID}";
 967                 break;
 968
 969               default:
 970               case 'Activity Targets':
 971                 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$targetID}";
 972                 break;
 973             }
 974           }
 975         }
 976         // build where clause
 977         if (!empty($value)) {
 978           $where[] = "e.activity_type_id IN ({$value})";
 979         }
 980         else {
 981           $where[] = "e.activity_type_id IS NULL";
 982         }
 983         if (!empty($status)) {
 984           $where[] = "e.status_id IN ({$status})";
 985         }
 986         $where[] = ' e.is_current_revision = 1 ';
 987         $where[] = ' e.is_deleted = 0 ';
 988
 989         $dateField = 'e.activity_date_time';
 990       }
 991
 992       if ($mapping->entity == 'civicrm_participant') {
 993         $table = 'civicrm_event r';
 994         $contactField = 'e.contact_id';
 995         $join[] = 'INNER JOIN civicrm_event r ON e.event_id = r.id';
 996         if ($actionSchedule->recipient_listing && $limitTo) {
 997           $rList = explode(CRM_Core_DAO::VALUE_SEPARATOR,
 998             trim($actionSchedule->recipient_listing, CRM_Core_DAO::VALUE_SEPARATOR)
 999           );
1000           $rList = implode(',', $rList);
1001
1002           switch ($recipientOptions[$actionSchedule->recipient]) {
1003             case 'participant_role':
1004               $where[] = "e.role_id IN ({$rList})";
1005               break;
1006
1007             default:
1008               break;
1009           }
1010         }
1011
1012         // build where clause
1013         if (!empty($value)) {
1014           $where[] = ($mapping->entity_value == 'event_type') ? "r.event_type_id IN ({$value})" : "r.id IN ({$value})";
1015         }
1016         else {
1017           $where[] = ($mapping->entity_value == 'event_type') ? "r.event_type_id IS NULL" : "r.id IS NULL";
1018         }
1019
1020         // participant status criteria not to be implemented
1021         // for additional recipients
1022         if (!empty($status)) {
1023           $limitWhere[] = "e.status_id IN ({$status})";
1024         }
1025
1026         $where[] = 'r.is_active = 1';
1027         $where[] = 'r.is_template = 0';
1028         $dateField = str_replace('event_', 'r.', $actionSchedule->start_action_date);
1029       }
1030
1031       $notINClause = '';
1032       if ($mapping->entity == 'civicrm_membership') {
1033         $contactField = 'e.contact_id';
1034         $table = 'civicrm_membership e';
1035         // build where clause
1036         if ( $status == 2 ) {
1037           //auto-renew memberships
1038           $where[] = "e.contribution_recur_id IS NOT NULL ";
1039         }
1040         elseif ( $status == 1 ) {
1041           $where[] = "e.contribution_recur_id IS NULL ";
1042         }
1043
1044         // build where clause
1045         if (!empty($value)) {
1046           $where[] = "e.membership_type_id IN ({$value})";
1047         }
1048         else {
1049           $where[] = "e.membership_type_id IS NULL";
1050         }
1051
1052         $where[] = "( e.is_override IS NULL OR e.is_override = 0 )";
1053         $dateField = str_replace('membership_', 'e.', $actionSchedule->start_action_date);
1054         $notINClause = self::permissionedRelationships($contactField);
1055
1056         $membershipStatus = CRM_Member_PseudoConstant::membershipStatus(NULL, "(is_current_member = 1 OR name = 'Expired')", 'id');
1057         $mStatus = implode (',', $membershipStatus);
1058         $where[] = "e.status_id IN ({$mStatus})";
1059       }
1060
1061       if ($mapping->entity == 'civicrm_contact') {
1062         if ($value == 'birth_date') {
1063           $dateDBField = 'birth_date';
1064           $table = 'civicrm_contact e';
1065           $contactField = 'e.id';
1066           $where[] = 'e.is_deleted = 0';
1067           $where[] = 'e.is_deceased = 0';
1068         }
1069         else {
1070           //custom field
1071           $customFieldParams = array('id' => substr($value, 7));
1072           $customGroup = $customField = array();
1073           CRM_Core_BAO_CustomField::retrieve($customFieldParams, $customField);
1074           $dateDBField = $customField['column_name'];
1075           $customGroupParams = array('id' => $customField['custom_group_id'], $customGroup);
1076           CRM_Core_BAO_CustomGroup::retrieve($customGroupParams, $customGroup);
1077           $from = $table = "{$customGroup['table_name']} e";
1078           $contactField = 'e.entity_id';
1079           $where[] = '1'; // possible to have no "where" in this case
1080         }
1081
1082         $status_ = explode(',', $status);
1083         if (in_array(2, $status_)) {
1084           // anniversary mode:
1085           $dateField = 'DATE_ADD(e.' . $dateDBField . ', INTERVAL ROUND(DATEDIFF(DATE(' . $now . '), e.' . $dateDBField . ') / 365) YEAR)';
1086           $anniversary = true;
1087         }
1088         else {
1089           // regular mode:
1090           $dateField = 'e.' . $dateDBField;
1091         }
1092         // TODO get this working
1093
1094         // TODO: Make sure everything's provided for repetition, etc.
1095       }
1096
1097       // CRM-13577 Introduce Smart Groups Handling
1098       if ($actionSchedule->group_id) {
1099
1100         // Need to check if its a smart group or not
1101         // Then decide which table to join onto the query
1102         $group = CRM_Contact_DAO_Group::getTableName();
1103
1104         // Get the group information
1105         $sql = "
1106 SELECT     $group.id, $group.cache_date, $group.saved_search_id, $group.children
1107 FROM       $group
1108 WHERE      $group.id = {$actionSchedule->group_id}
1109 ";
1110
1111         $groupDAO = CRM_Core_DAO::executeQuery($sql);
1112         $isSmartGroup = FALSE;
1113         if (
1114           $groupDAO->fetch() &&
1115           !empty($groupDAO->saved_search_id)
1116         ) {
1117           // Check that the group is in place in the cache and up to date
1118           CRM_Contact_BAO_GroupContactCache::check($actionSchedule->group_id);
1119           // Set smart group flag
1120           $isSmartGroup = TRUE;
1121         }
1122       }
1123       // CRM-13577 End Introduce Smart Groups Handling
1124
1125       if ($limitTo) {
1126         if ($actionSchedule->group_id) {
1127           // CRM-13577 If smart group then use Cache table
1128           if ($isSmartGroup) {
1129             $join[] = "INNER JOIN civicrm_group_contact_cache grp ON {$contactField} = grp.contact_id";
1130             $where[] = "grp.group_id IN ({$actionSchedule->group_id})";
1131           } else {
1132             $join[] = "INNER JOIN civicrm_group_contact grp ON {$contactField} = grp.contact_id AND grp.status = 'Added'";
1133             $where[] = "grp.group_id IN ({$actionSchedule->group_id})";
1134           }
1135         }
1136         elseif (!empty($actionSchedule->recipient_manual)) {
1137           $rList = CRM_Utils_Type::escape($actionSchedule->recipient_manual, 'String');
1138           $where[] = "{$contactField} IN ({$rList})";
1139         }
1140       }
1141       elseif (!is_null($limitTo)) {
1142         $addGroup = $addWhere = '';
1143         if ($actionSchedule->group_id) {
1144           // CRM-13577 If smart group then use Cache table
1145           if ($isSmartGroup) {
1146             $addGroup = " INNER JOIN civicrm_group_contact_cache grp ON c.id = grp.contact_id";
1147             $addWhere = " grp.group_id IN ({$actionSchedule->group_id})";
1148           } else {
1149             $addGroup = " INNER JOIN civicrm_group_contact grp ON c.id = grp.contact_id AND grp.status = 'Added'";
1150             $addWhere = " grp.group_id IN ({$actionSchedule->group_id})";
1151           }
1152         }
1153         if (!empty($actionSchedule->recipient_manual)) {
1154           $rList = CRM_Utils_Type::escape($actionSchedule->recipient_manual, 'String');
1155           $addWhere = "c.id IN ({$rList})";
1156         }
1157       }
1158
1159       $select[] = "{$contactField} as contact_id";
1160       $select[] = 'e.id as entity_id';
1161       $select[] = "'{$mapping->entity}' as entity_table";
1162       $select[] = "{$actionSchedule->id} as action_schedule_id";
1163       $reminderJoinClause = "civicrm_action_log reminder ON reminder.contact_id = {$contactField} AND
1164 reminder.entity_id          = e.id AND
1165 reminder.entity_table       = '{$mapping->entity}' AND
1166 reminder.action_schedule_id = %1";
1167
1168       if ($anniversary) {
1169         // only consider reminders less than 11 months ago
1170         $reminderJoinClause .= " AND reminder.action_date_time > DATE_SUB({$now}, INTERVAL 11 MONTH)";
1171       }
1172
1173       if ($table != 'civicrm_contact e') {
1174         $join[] = "INNER JOIN civicrm_contact c ON c.id = {$contactField} AND c.is_deleted = 0 AND c.is_deceased = 0 ";
1175       }
1176
1177       if ($actionSchedule->start_action_date) {
1178         $startDateClause = array();
1179         $op = ($actionSchedule->start_action_condition == 'before' ? '<=' : '>=');
1180         $operator = ($actionSchedule->start_action_condition == 'before' ? 'DATE_SUB' : 'DATE_ADD');
1181         $date = $operator . "({$dateField}, INTERVAL {$actionSchedule->start_action_offset} {$actionSchedule->start_action_unit})";
1182         $startDateClause[] = "'{$now}' >= {$date}";
1183         if ($mapping->entity == 'civicrm_participant') {
1184           $startDateClause[] = $operator. "({$now}, INTERVAL 1 DAY ) {$op} " . $dateField;
1185         }
1186         else {
1187           $startDateClause[] = "DATE_SUB({$now}, INTERVAL 1 DAY ) <= {$date}";
1188         }
1189
1190         $startDate = implode(' AND ', $startDateClause);
1191       }
1192       elseif ($actionSchedule->absolute_date) {
1193         $startDate = "DATEDIFF(DATE('{$now}'),'{$actionSchedule->absolute_date}') = 0";
1194       }
1195
1196       // ( now >= date_built_from_start_time ) OR ( now = absolute_date )
1197       $dateClause = "reminder.id IS NULL AND {$startDate}";
1198
1199       // start composing query
1200       $selectClause = 'SELECT ' . implode(', ', $select);
1201       $fromClause = "FROM $from";
1202       $joinClause = !empty($join) ? implode(' ', $join) : '';
1203       $whereClause = 'WHERE ' . implode(' AND ', $where);
1204       $limitWhereClause = '';
1205       if (!empty($limitWhere)) {
1206         $limitWhereClause = ' AND ' . implode(' AND ', $limitWhere);
1207       }
1208
1209       $query = "
1210 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
1211 {$selectClause}
1212 {$fromClause}
1213 {$joinClause}
1214 LEFT JOIN {$reminderJoinClause}
1215 {$whereClause} {$limitWhereClause} AND {$dateClause} {$notINClause}
1216 ";
1217       CRM_Core_DAO::executeQuery($query, array(1 => array($actionSchedule->id, 'Integer')));
1218
1219       if (!is_null($limitTo) && $limitTo == 0 && (!empty($addGroup) || !empty($addWhere))) {
1220         $additionWhere = ' WHERE ';
1221         if ($actionSchedule->start_action_date) {
1222           $additionWhere = $whereClause . ' AND ';
1223         }
1224         $contactTable = "civicrm_contact c";
1225         $addSelect  = "SELECT c.id as contact_id, c.id as entity_id, 'civicrm_contact' as entity_table, {$actionSchedule->id} as action_schedule_id";
1226         $additionReminderClause = "civicrm_action_log reminder ON reminder.contact_id = c.id AND
1227           reminder.entity_id          = c.id AND
1228           reminder.entity_table       = 'civicrm_contact' AND
1229           reminder.action_schedule_id = {$actionSchedule->id}";
1230         $addWhereClause = '';
1231         if ($addWhere) {
1232           $addWhereClause = "AND {$addWhere}";
1233         }
1234         $insertAdditionalSql ="
1235 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
1236 {$addSelect}
1237 FROM ({$contactTable})
1238 LEFT JOIN {$additionReminderClause}
1239 {$addGroup}
1240 WHERE c.is_deleted = 0 AND c.is_deceased = 0
1241 {$addWhereClause}
1242
1243 AND c.id NOT IN (
1244      SELECT rem.contact_id
1245      FROM civicrm_action_log rem INNER JOIN {$mapping->entity} e ON rem.entity_id = e.id
1246      WHERE rem.action_schedule_id = {$actionSchedule->id}
1247       AND rem.entity_table = '{$mapping->entity}'
1248     )
1249 GROUP BY c.id
1250 ";
1251         CRM_Core_DAO::executeQuery($insertAdditionalSql);
1252       }
1253       // if repeat is turned ON:
1254       if ($actionSchedule->is_repeat) {
1255         $repeatEvent = ($actionSchedule->end_action == 'before' ? 'DATE_SUB' : 'DATE_ADD') . "({$dateField}, INTERVAL {$actionSchedule->end_frequency_interval} {$actionSchedule->end_frequency_unit})";
1256
1257         if ($actionSchedule->repetition_frequency_unit == 'day') {
1258           $hrs = 24 * $actionSchedule->repetition_frequency_interval;
1259         }
1260         elseif ($actionSchedule->repetition_frequency_unit == 'week') {
1261           $hrs = 24 * $actionSchedule->repetition_frequency_interval * 7;
1262         }
1263         elseif ($actionSchedule->repetition_frequency_unit == 'month') {
1264           $hrs = "24*(DATEDIFF(DATE_ADD(latest_log_time, INTERVAL 1 MONTH ), latest_log_time))";
1265         }
1266         elseif ($actionSchedule->repetition_frequency_unit == 'year') {
1267           $hrs = "24*(DATEDIFF(DATE_ADD(latest_log_time, INTERVAL 1 YEAR ), latest_log_time))";
1268         }
1269         else {
1270           $hrs = $actionSchedule->repetition_frequency_interval;
1271         }
1272
1273         // (now <= repeat_end_time )
1274         $repeatEventClause = "'{$now}' <= {$repeatEvent}";
1275         // diff(now && logged_date_time) >= repeat_interval
1276         $havingClause = "HAVING TIMEDIFF({$now}, latest_log_time) >= TIME('{$hrs}:00:00')";
1277         $groupByClause = 'GROUP BY reminder.contact_id, reminder.entity_id, reminder.entity_table';
1278         $selectClause .= ', MAX(reminder.action_date_time) as latest_log_time';
1279         //CRM-15376 - do not send our reminders if original criteria no longer applies
1280         // the first part of the startDateClause array is the earliest the reminder can be sent. If the
1281         // event (e.g membership_end_date) has changed then the reminder may no longer apply
1282         // @todo - this only handles events that get moved later. Potentially they might get moved earlier
1283         $originalEventStartDateClause = empty($startDateClause) ? '' : 'AND' . $startDateClause[0];
1284         $sqlInsertValues = "{$selectClause}
1285 {$fromClause}
1286 {$joinClause}
1287 INNER JOIN {$reminderJoinClause}
1288 {$whereClause} {$limitWhereClause} AND {$repeatEventClause} {$originalEventStartDateClause} {$notINClause}
1289 {$groupByClause}
1290 {$havingClause}";
1291
1292         $valsqlInsertValues = CRM_Core_DAO::executeQuery($sqlInsertValues, array(1 => array($actionSchedule->id, 'Integer')));
1293
1294         $arrValues = array();
1295         while ($valsqlInsertValues->fetch()) {
1296           $arrValues[] = "( {$valsqlInsertValues->contact_id}, {$valsqlInsertValues->entity_id}, '{$valsqlInsertValues->entity_table}',{$valsqlInsertValues->action_schedule_id} )";
1297         }
1298
1299         $valString = implode(',', $arrValues);
1300
1301         if ($valString) {
1302           $query = '
1303               INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id) VALUES ' . $valString;
1304           CRM_Core_DAO::executeQuery($query, array(1 => array($actionSchedule->id, 'Integer')));
1305         }
1306
1307         if (!is_null($limitTo) && $limitTo == 0) {
1308           $addSelect .= ', MAX(reminder.action_date_time) as latest_log_time';
1309           $sqlEndEventCheck = "
1310 SELECT * FROM {$table}
1311 {$whereClause} AND {$repeatEventClause} LIMIT 1";
1312
1313           $daoCheck = CRM_Core_DAO::executeQuery($sqlEndEventCheck);
1314           if ($daoCheck->fetch()) {
1315             $valSqlAdditionInsert = "
1316 {$addSelect}
1317 FROM  {$contactTable}
1318 {$addGroup}
1319 INNER JOIN {$additionReminderClause}
1320 WHERE {$addWhere} AND c.is_deleted = 0 AND c.is_deceased = 0
1321 GROUP BY reminder.contact_id
1322 {$havingClause}
1323 ";
1324             $daoForVals = CRM_Core_DAO::executeQuery($valSqlAdditionInsert);
1325             $addValues = array();
1326             while ($daoForVals->fetch()) {
1327               $addValues[] = "( {$daoForVals->contact_id}, {$daoForVals->entity_id}, '{$daoForVals->entity_table}',{$daoForVals->action_schedule_id} )";
1328             }
1329             $valString = implode(',', $addValues);
1330
1331             if ($valString) {
1332               $query = '
1333                 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id) VALUES ' . $valString;
1334               CRM_Core_DAO::executeQuery($query);
1335             }
1336           }
1337         }
1338       }
1339     }
1340   }
1341
1342   /**
1343    * @param $field
1344    *
1345    * @return null|string
1346    */
1347   static function permissionedRelationships($field) {
1348     $query = '
1349 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
1350 FROM      civicrm_membership m
1351 LEFT JOIN civicrm_membership cm ON cm.id = m.owner_membership_id
1352 LEFT JOIN civicrm_membership_type cmt ON cmt.id = m.membership_type_id
1353 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 )
1354                                         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 ) )
1355 WHERE     m.owner_membership_id IS NOT NULL AND
1356           ( rel.is_permission_a_b = 0 OR rel.is_permission_b_a = 0)
1357
1358 ';
1359     $excludeIds = array();
1360     $dao = CRM_Core_DAO::executeQuery($query, array());
1361     while ($dao->fetch()) {
1362       if ($dao->slave_contact == $dao->contact_id_a && $dao->is_permission_a_b == 0) {
1363         $excludeIds[] = $dao->slave_contact;
1364       }
1365       elseif ($dao->slave_contact == $dao->contact_id_b && $dao->is_permission_b_a == 0) {
1366         $excludeIds[] = $dao->slave_contact;
1367       }
1368     }
1369
1370     if (!empty($excludeIds)) {
1371       $clause = "AND {$field} NOT IN ( " .implode(', ', $excludeIds) . ' ) ';
1372       return  $clause;
1373     }
1374     return NULL;
1375   }
1376
1377   /**
1378    * @param null $now
1379    * @param array $params
1380    *
1381    * @return array
1382    */
1383   static function processQueue($now = NULL, $params = array()) {
1384     $now = $now ? CRM_Utils_Time::setTime($now) : CRM_Utils_Time::getTime();
1385
1386     $mappings = self::getMapping();
1387     foreach ($mappings as $mappingID => $mapping) {
1388       self::buildRecipientContacts($mappingID, $now, $params);
1389       self::sendMailings($mappingID, $now);
1390     }
1391
1392     $result = array(
1393       'is_error' => 0,
1394       'messages' => ts('Sent all scheduled reminders successfully'),
1395     );
1396     return $result;
1397   }
1398
1399   /**
1400    * @param $id
1401    * @param $mappingID
1402    *
1403    * @return null|string
1404    */
1405   static function isConfigured($id, $mappingID) {
1406     $queryString = "SELECT count(id) FROM civicrm_action_schedule
1407                         WHERE  mapping_id = %1 AND
1408                                entity_value = %2";
1409
1410     $params = array(
1411       1 => array($mappingID, 'Integer'),
1412       2 => array($id, 'Integer'),
1413     );
1414     return CRM_Core_DAO::singleValueQuery($queryString, $params);
1415   }
1416
1417   /**
1418    * @param $mappingID
1419    * @param $recipientType
1420    *
1421    * @return array
1422    */
1423   static function getRecipientListing($mappingID, $recipientType) {
1424     $options = array();
1425     if (!$mappingID || !$recipientType) {
1426       return $options;
1427     }
1428
1429     $mapping = self::getMapping($mappingID);
1430
1431     switch ($mapping['entity']) {
1432       case 'civicrm_participant':
1433         $eventContacts = CRM_Core_OptionGroup::values('event_contacts', FALSE, FALSE, FALSE, NULL, 'name', TRUE, FALSE, 'name');
1434         if (empty($eventContacts[$recipientType])) {
1435           return $options;
1436         }
1437         if ($eventContacts[$recipientType] == 'participant_role') {
1438           $options = CRM_Event_PseudoConstant::participantRole();
1439         }
1440         break;
1441     }
1442
1443     return $options;
1444   }
1445 }