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