CRM/Core/BAO/ActionSchedule.php

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