CRM/Core/BAO/ActionSchedule.php

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