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 array $filters
  43    *   Filter by property (e.g. 'id', 'entity_value').
  44    * @return array
  45    *   Array(scalar $id => Mapping $mapping).
  46    */
  47   public static function getMappings($filters = NULL) {
  48     static $_action_mapping;
  49
  50     if ($_action_mapping === NULL) {
  51       $event = \Civi\Core\Container::singleton()->get('dispatcher')
  52         ->dispatch(\Civi\ActionSchedule\Events::MAPPINGS,
  53           new \Civi\ActionSchedule\Event\MappingRegisterEvent());
  54       $_action_mapping = $event->getMappings();
  55     }
  56
  57     if ($filters === NULL) {
  58       return $_action_mapping;
  59     }
  60
  61     $result = array();
  62     foreach ($_action_mapping as $mappingId => $mapping) {
  63       $match = TRUE;
  64       foreach ($filters as $filterField => $filterValue) {
  65         if ($mapping->{$filterField} != $filterValue) {
  66           $match = FALSE;
  67         }
  68       }
  69       if ($match) {
  70         $result[$mappingId] = $mapping;
  71       }
  72     }
  73     return $result;
  74   }
  75
  76   /**
  77    * Retrieve list of selections/drop downs for Scheduled Reminder form
  78    *
  79    * @param bool $id
  80    *   Mapping id.
  81    *
  82    * @return array
  83    *   associated array of all the drop downs in the form
  84    */
  85   public static function getSelection($id = NULL) {
  86     $mappings = CRM_Core_BAO_ActionSchedule::getMappings();
  87
  88     $entityValueLabels = $entityStatusLabels = $dateFieldLabels = array();
  89     $entityRecipientLabels = $entityRecipientNames = array();
  90
  91     if (!$id) {
  92       $id = 1;
  93     }
  94
  95     foreach ($mappings as $mapping) {
  96       /** @var \Civi\ActionSchedule\Mapping $mapping */
  97
  98       $entityValueLabels[$mapping->id] = $mapping->getValueLabels();
  99       // Not sure why: everything *except* contact-dates have a $valueLabel.
 100       if ($mapping->id !== CRM_Core_ActionScheduleTmp::CONTACT_MAPPING_ID) {
 101         $valueLabel = array('- ' . strtolower($mapping->entity_value_label) . ' -');
 102         $entityValueLabels[$mapping->id] = $valueLabel + $entityValueLabels[$mapping->id];
 103       }
 104
 105       if ($mapping->id == $id) {
 106         $dateFieldLabels = $mapping->getDateFields();
 107         $entityRecipientLabels = array($mapping->entity_recipient => $mapping->getRecipientTypes());
 108         $entityRecipientNames = array_combine(array_keys($entityRecipientLabels[$mapping->entity_recipient]), array_keys($entityRecipientLabels[$mapping->entity_recipient]));
 109       }
 110
 111       $statusLabel = array('- ' . strtolower($mapping->entity_status_label) . ' -');
 112       $entityStatusLabels[$mapping->id] = $entityValueLabels[$mapping->id];
 113       foreach ($entityStatusLabels[$mapping->id] as $kkey => & $vval) {
 114         $vval = $statusLabel + $mapping->getStatusLabels($kkey);
 115       }
 116     }
 117
 118     return array(
 119       'sel1' => CRM_Utils_Array::collect('entity_label', $mappings),
 120       'sel2' => $entityValueLabels,
 121       'sel3' => $entityStatusLabels,
 122       'sel4' => $dateFieldLabels,
 123       'sel5' => $entityRecipientLabels,
 124       'entityMapping' => CRM_Utils_Array::collect('entity', $mappings),
 125       'recipientMapping' => $entityRecipientNames,
 126     );
 127   }
 128
 129   /**
 130    * @param int $mappingId
 131    * @param int $isLimit
 132    *
 133    * @return array
 134    */
 135   public static function getSelection1($mappingId = NULL, $isLimit = NULL) {
 136     $mappings = CRM_Core_BAO_ActionSchedule::getMappings(array(
 137       'id' => $mappingId,
 138     ));
 139     $dateFieldLabels = $entityRecipientLabels = array();
 140
 141     foreach ($mappings as $mapping) {
 142       /** @var \Civi\ActionSchedule\Mapping $mapping */
 143       $dateFieldLabels = $mapping->getDateFields();
 144       $entityRecipientLabels = $mapping->getRecipientTypes(!$isLimit);
 145     }
 146
 147     return array(
 148       'sel4' => $dateFieldLabels,
 149       'sel5' => $entityRecipientLabels,
 150       'recipientMapping' => array_combine(array_keys($entityRecipientLabels), array_keys($entityRecipientLabels)),
 151     );
 152   }
 153
 154   /**
 155    * Retrieve list of Scheduled Reminders.
 156    *
 157    * @param bool $namesOnly
 158    *   Return simple list of names.
 159    *
 160    * @param \Civi\ActionSchedule\Mapping|NULL $filterMapping
 161    *   Filter by the schedule's mapping type.
 162    * @param int $filterValue
 163    *   Filter by the schedule's entity_value.
 164    *
 165    * @return array
 166    *   (reference)   reminder list
 167    */
 168   public static function &getList($namesOnly = FALSE, $filterMapping = NULL, $filterValue = NULL) {
 169     $query = "
 170 SELECT
 171        title,
 172        cas.id as id,
 173        cas.mapping_id,
 174        cas.entity_value as entityValueIds,
 175        cas.entity_status as entityStatusIds,
 176        cas.start_action_date as entityDate,
 177        cas.start_action_offset,
 178        cas.start_action_unit,
 179        cas.start_action_condition,
 180        cas.absolute_date,
 181        is_repeat,
 182        is_active
 183
 184 FROM civicrm_action_schedule cas
 185 ";
 186     $queryParams = array();
 187     $where = " WHERE 1 ";
 188     if ($filterMapping and $filterValue) {
 189       $where .= " AND cas.entity_value = %1 AND cas.mapping_id = %2";
 190       $queryParams[1] = array($filterValue, 'Integer');
 191       $queryParams[2] = array($filterMapping->id, 'Integer');
 192     }
 193     $where .= " AND cas.used_for IS NULL";
 194     $query .= $where;
 195     $dao = CRM_Core_DAO::executeQuery($query, $queryParams);
 196     while ($dao->fetch()) {
 197       /** @var Civi\ActionSchedule\Mapping $filterMapping */
 198       $filterMapping = CRM_Utils_Array::first(self::getMappings(array(
 199         'id' => $dao->mapping_id,
 200       )));
 201       $list[$dao->id]['id'] = $dao->id;
 202       $list[$dao->id]['title'] = $dao->title;
 203       $list[$dao->id]['start_action_offset'] = $dao->start_action_offset;
 204       $list[$dao->id]['start_action_unit'] = $dao->start_action_unit;
 205       $list[$dao->id]['start_action_condition'] = $dao->start_action_condition;
 206       $list[$dao->id]['entityDate'] = ucwords(str_replace('_', ' ', $dao->entityDate));
 207       $list[$dao->id]['absolute_date'] = $dao->absolute_date;
 208       $list[$dao->id]['entity'] = $filterMapping->entity_label;
 209       $list[$dao->id]['value'] = implode(', ', CRM_Utils_Array::subset(
 210         $filterMapping->getValueLabels(),
 211         explode(CRM_Core_DAO::VALUE_SEPARATOR, $dao->entityValueIds)
 212       ));
 213       $list[$dao->id]['status'] = implode(', ', CRM_Utils_Array::subset(
 214         $filterMapping->getStatusLabels($dao->entityValueIds),
 215         explode(CRM_Core_DAO::VALUE_SEPARATOR, $dao->entityStatusIds)
 216       ));
 217       $list[$dao->id]['is_repeat'] = $dao->is_repeat;
 218       $list[$dao->id]['is_active'] = $dao->is_active;
 219     }
 220
 221     return $list;
 222   }
 223
 224   /**
 225    * Add the schedules reminders in the db.
 226    *
 227    * @param array $params
 228    *   (reference ) an assoc array of name/value pairs.
 229    * @param array $ids
 230    *   Unused variable.
 231    *
 232    * @return CRM_Core_DAO_ActionSchedule
 233    */
 234   public static function add(&$params, $ids = array()) {
 235     $actionSchedule = new CRM_Core_DAO_ActionSchedule();
 236     $actionSchedule->copyValues($params);
 237
 238     return $actionSchedule->save();
 239   }
 240
 241   /**
 242    * Retrieve DB object based on input parameters.
 243    *
 244    * It also stores all the retrieved values in the default array.
 245    *
 246    * @param array $params
 247    *   (reference ) an assoc array of name/value pairs.
 248    * @param array $values
 249    *   (reference ) an assoc array to hold the flattened values.
 250    *
 251    * @return CRM_Core_DAO_ActionSchedule|null
 252    *   object on success, null otherwise
 253    */
 254   public static function retrieve(&$params, &$values) {
 255     if (empty($params)) {
 256       return NULL;
 257     }
 258     $actionSchedule = new CRM_Core_DAO_ActionSchedule();
 259
 260     $actionSchedule->copyValues($params);
 261
 262     if ($actionSchedule->find(TRUE)) {
 263       $ids['actionSchedule'] = $actionSchedule->id;
 264
 265       CRM_Core_DAO::storeValues($actionSchedule, $values);
 266
 267       return $actionSchedule;
 268     }
 269     return NULL;
 270   }
 271
 272   /**
 273    * Delete a Reminder.
 274    *
 275    * @param int $id
 276    *   ID of the Reminder to be deleted.
 277    *
 278    */
 279   public static function del($id) {
 280     if ($id) {
 281       $dao = new CRM_Core_DAO_ActionSchedule();
 282       $dao->id = $id;
 283       if ($dao->find(TRUE)) {
 284         $dao->delete();
 285         return;
 286       }
 287     }
 288     CRM_Core_Error::fatal(ts('Invalid value passed to delete function.'));
 289   }
 290
 291   /**
 292    * Update the is_active flag in the db.
 293    *
 294    * @param int $id
 295    *   Id of the database record.
 296    * @param bool $is_active
 297    *   Value we want to set the is_active field.
 298    *
 299    * @return Object
 300    *   DAO object on success, null otherwise
 301    */
 302   public static function setIsActive($id, $is_active) {
 303     return CRM_Core_DAO::setFieldValue('CRM_Core_DAO_ActionSchedule', $id, 'is_active', $is_active);
 304   }
 305
 306   /**
 307    * @param int $mappingID
 308    * @param $now
 309    *
 310    * @throws CRM_Core_Exception
 311    */
 312   public static function sendMailings($mappingID, $now) {
 313     $mapping = CRM_Utils_Array::first(self::getMappings(array(
 314       'id' => $mappingID,
 315     )));
 316
 317     $actionSchedule = new CRM_Core_DAO_ActionSchedule();
 318     $actionSchedule->mapping_id = $mappingID;
 319     $actionSchedule->is_active = 1;
 320     $actionSchedule->find(FALSE);
 321
 322     while ($actionSchedule->fetch()) {
 323       $query = CRM_Core_BAO_ActionSchedule::prepareMailingQuery($mapping, $actionSchedule);
 324       $dao = CRM_Core_DAO::executeQuery($query,
 325         array(1 => array($actionSchedule->id, 'Integer'))
 326       );
 327
 328       $multilingual = CRM_Core_I18n::isMultilingual();
 329       while ($dao->fetch()) {
 330         // switch language if necessary
 331         if ($multilingual) {
 332           $preferred_language = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $dao->contactID, 'preferred_language');
 333           CRM_Core_BAO_ActionSchedule::setCommunicationLanguage($actionSchedule->communication_language, $preferred_language);
 334         }
 335
 336         $errors = array();
 337         try {
 338           $tokenProcessor = self::createTokenProcessor($actionSchedule, $mapping);
 339           $tokenProcessor->addRow()
 340             ->context('contactId', $dao->contactID)
 341             ->context('actionSearchResult', (object) $dao->toArray());
 342           foreach ($tokenProcessor->evaluate()->getRows() as $tokenRow) {
 343             if ($actionSchedule->mode == 'SMS' or $actionSchedule->mode == 'User_Preference') {
 344               CRM_Utils_Array::extend($errors, self::sendReminderSms($tokenRow, $actionSchedule, $dao->contactID));
 345             }
 346
 347             if ($actionSchedule->mode == 'Email' or $actionSchedule->mode == 'User_Preference') {
 348               CRM_Utils_Array::extend($errors, self::sendReminderEmail($tokenRow, $actionSchedule, $dao->contactID));
 349             }
 350           }
 351         }
 352         catch (\Civi\Token\TokenException $e) {
 353           $errors['token_exception'] = $e->getMessage();
 354         }
 355
 356         // update action log record
 357         $logParams = array(
 358           'id' => $dao->reminderID,
 359           'is_error' => !empty($errors),
 360           'message' => empty($errors) ? "null" : implode(' ', $errors),
 361           'action_date_time' => $now,
 362         );
 363         CRM_Core_BAO_ActionLog::create($logParams);
 364
 365         // insert activity log record if needed
 366         if ($actionSchedule->record_activity && empty($errors)) {
 367           $caseID = empty($dao->case_id) ? NULL : $dao->case_id;
 368           CRM_Core_BAO_ActionSchedule::createMailingActivity($actionSchedule, $mapping, $dao->contactID, $dao->entityID, $caseID);
 369         }
 370       }
 371
 372       $dao->free();
 373     }
 374   }
 375
 376   /**
 377    * @param int $mappingID
 378    * @param $now
 379    * @param array $params
 380    *
 381    * @throws API_Exception
 382    */
 383   public static function buildRecipientContacts($mappingID, $now, $params = array()) {
 384     $actionSchedule = new CRM_Core_DAO_ActionSchedule();
 385     $actionSchedule->mapping_id = $mappingID;
 386     $actionSchedule->is_active = 1;
 387     if (!empty($params)) {
 388       _civicrm_api3_dao_set_filter($actionSchedule, $params, FALSE);
 389     }
 390     $actionSchedule->find();
 391
 392     while ($actionSchedule->fetch()) {
 393       /** @var \Civi\ActionSchedule\Mapping $mapping */
 394       $mapping = CRM_Utils_Array::first(self::getMappings(array(
 395         'id' => $mappingID,
 396       )));
 397
 398       // note: $where - this filtering applies for both
 399       // 'limit to' and 'addition to' options
 400       // $limitWhere - this filtering applies only for
 401       // 'limit to' option
 402       $select = $join = $where = $limitWhere = array();
 403       $selectColumns = "contact_id, entity_id, entity_table, action_schedule_id";
 404       $limitTo = $actionSchedule->limit_to;
 405       $value = explode(CRM_Core_DAO::VALUE_SEPARATOR,
 406         trim($actionSchedule->entity_value, CRM_Core_DAO::VALUE_SEPARATOR)
 407       );
 408       $value = implode(',', $value);
 409
 410       $status = explode(CRM_Core_DAO::VALUE_SEPARATOR,
 411         trim($actionSchedule->entity_status, CRM_Core_DAO::VALUE_SEPARATOR)
 412       );
 413       $status = implode(',', $status);
 414
 415       $anniversary = FALSE;
 416
 417       $from = "{$mapping->entity} e";
 418
 419       if ($mapping->entity == 'civicrm_activity') {
 420         $contactField = 'r.contact_id';
 421         $table = 'civicrm_activity e';
 422         $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
 423         $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
 424         $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
 425         $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 426
 427         if (!is_null($limitTo)) {
 428           if ($limitTo == 0) {
 429             // including the activity target contacts if 'in addition' is defined
 430             $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$targetID}";
 431           }
 432           else {
 433             switch (CRM_Utils_Array::value($actionSchedule->recipient, $mapping->getRecipientOptions())) {
 434               case 'Activity Assignees':
 435                 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$assigneeID}";
 436                 break;
 437
 438               case 'Activity Source':
 439                 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$sourceID}";
 440                 break;
 441
 442               default:
 443               case 'Activity Targets':
 444                 $join[] = "INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = {$targetID}";
 445                 break;
 446             }
 447           }
 448         }
 449         // build where clause
 450         if (!empty($value)) {
 451           $where[] = "e.activity_type_id IN ({$value})";
 452         }
 453         else {
 454           $where[] = "e.activity_type_id IS NULL";
 455         }
 456         if (!empty($status)) {
 457           $where[] = "e.status_id IN ({$status})";
 458         }
 459         $where[] = ' e.is_current_revision = 1 ';
 460         $where[] = ' e.is_deleted = 0 ';
 461
 462         $dateField = 'e.activity_date_time';
 463       }
 464
 465       if ($mapping->entity == 'civicrm_participant') {
 466         $table = 'civicrm_event r';
 467         $contactField = 'e.contact_id';
 468         $join[] = 'INNER JOIN civicrm_event r ON e.event_id = r.id';
 469         if ($actionSchedule->recipient_listing && $limitTo) {
 470           $rList = explode(CRM_Core_DAO::VALUE_SEPARATOR,
 471             trim($actionSchedule->recipient_listing, CRM_Core_DAO::VALUE_SEPARATOR)
 472           );
 473           $rList = implode(',', $rList);
 474
 475           switch (CRM_Utils_Array::value($actionSchedule->recipient, $mapping->getRecipientOptions())) {
 476             case 'participant_role':
 477               $where[] = "e.role_id IN ({$rList})";
 478               break;
 479
 480             default:
 481               break;
 482           }
 483         }
 484
 485         // build where clause
 486         if (!empty($value)) {
 487           $where[] = ($mapping->id == CRM_Core_ActionScheduleTmp::EVENT_TYPE_MAPPING_ID) ? "r.event_type_id IN ({$value})" : "r.id IN ({$value})";
 488         }
 489         else {
 490           $where[] = ($mapping->id == CRM_Core_ActionScheduleTmp::EVENT_TYPE_MAPPING_ID) ? "r.event_type_id IS NULL" : "r.id IS NULL";
 491         }
 492
 493         // participant status criteria not to be implemented
 494         // for additional recipients
 495         if (!empty($status)) {
 496           $limitWhere[] = "e.status_id IN ({$status})";
 497         }
 498
 499         $where[] = 'r.is_active = 1';
 500         $where[] = 'r.is_template = 0';
 501         $dateField = str_replace('event_', 'r.', $actionSchedule->start_action_date);
 502       }
 503
 504       $notINClause = '';
 505       if ($mapping->entity == 'civicrm_membership') {
 506         $contactField = 'e.contact_id';
 507         $table = 'civicrm_membership e';
 508         // build where clause
 509         if ($status == 2) {
 510           //auto-renew memberships
 511           $where[] = "e.contribution_recur_id IS NOT NULL ";
 512         }
 513         elseif ($status == 1) {
 514           $where[] = "e.contribution_recur_id IS NULL ";
 515         }
 516
 517         // build where clause
 518         if (!empty($value)) {
 519           $where[] = "e.membership_type_id IN ({$value})";
 520         }
 521         else {
 522           $where[] = "e.membership_type_id IS NULL";
 523         }
 524
 525         $where[] = "( e.is_override IS NULL OR e.is_override = 0 )";
 526         $dateField = str_replace('membership_', 'e.', $actionSchedule->start_action_date);
 527         $notINClause = CRM_Core_BAO_ActionSchedule::permissionedRelationships($contactField);
 528
 529         $membershipStatus = CRM_Member_PseudoConstant::membershipStatus(NULL, "(is_current_member = 1 OR name = 'Expired')", 'id');
 530         $mStatus = implode(',', $membershipStatus);
 531         $where[] = "e.status_id IN ({$mStatus})";
 532
 533         // We are not tracking the reference date for 'repeated' schedule reminders,
 534         // for further details please check CRM-15376
 535         if ($actionSchedule->start_action_date && $actionSchedule->is_repeat == FALSE) {
 536           $select[] = $dateField;
 537           $selectColumns = "reference_date, " . $selectColumns;
 538         }
 539       }
 540
 541       if ($mapping->entity == 'civicrm_contact') {
 542         $contactFields = array(
 543           'birth_date',
 544           'created_date',
 545           'modified_date',
 546         );
 547         if (in_array($value, $contactFields)) {
 548           $dateDBField = $value;
 549           $table = 'civicrm_contact e';
 550           $contactField = 'e.id';
 551           $where[] = 'e.is_deleted = 0';
 552           $where[] = 'e.is_deceased = 0';
 553         }
 554         else {
 555           //custom field
 556           $customFieldParams = array('id' => substr($value, 7));
 557           $customGroup = $customField = array();
 558           CRM_Core_BAO_CustomField::retrieve($customFieldParams, $customField);
 559           $dateDBField = $customField['column_name'];
 560           $customGroupParams = array('id' => $customField['custom_group_id'], $customGroup);
 561           CRM_Core_BAO_CustomGroup::retrieve($customGroupParams, $customGroup);
 562           $from = $table = "{$customGroup['table_name']} e";
 563           $contactField = 'e.entity_id';
 564           $where[] = '1'; // possible to have no "where" in this case
 565         }
 566
 567         $status_ = explode(',', $status);
 568         if (in_array(2, $status_)) {
 569           // anniversary mode:
 570           $dateField = 'DATE_ADD(e.' . $dateDBField . ', INTERVAL ROUND(DATEDIFF(DATE(' . $now . '), e.' . $dateDBField . ') / 365) YEAR)';
 571           $anniversary = TRUE;
 572         }
 573         else {
 574           // regular mode:
 575           $dateField = 'e.' . $dateDBField;
 576         }
 577       }
 578
 579       // CRM-13577 Introduce Smart Groups Handling
 580       if ($actionSchedule->group_id) {
 581
 582         // Need to check if its a smart group or not
 583         // Then decide which table to join onto the query
 584         $group = CRM_Contact_DAO_Group::getTableName();
 585
 586         // Get the group information
 587         $sql = "
 588 SELECT     $group.id, $group.cache_date, $group.saved_search_id, $group.children
 589 FROM       $group
 590 WHERE      $group.id = {$actionSchedule->group_id}
 591 ";
 592
 593         $groupDAO = CRM_Core_DAO::executeQuery($sql);
 594         $isSmartGroup = FALSE;
 595         if (
 596           $groupDAO->fetch() &&
 597           !empty($groupDAO->saved_search_id)
 598         ) {
 599           // Check that the group is in place in the cache and up to date
 600           CRM_Contact_BAO_GroupContactCache::check($actionSchedule->group_id);
 601           // Set smart group flag
 602           $isSmartGroup = TRUE;
 603         }
 604       }
 605       // CRM-13577 End Introduce Smart Groups Handling
 606
 607       if ($limitTo) {
 608         if ($actionSchedule->group_id) {
 609           // CRM-13577 If smart group then use Cache table
 610           if ($isSmartGroup) {
 611             $join[] = "INNER JOIN civicrm_group_contact_cache grp ON {$contactField} = grp.contact_id";
 612             $where[] = "grp.group_id IN ({$actionSchedule->group_id})";
 613           }
 614           else {
 615             $join[] = "INNER JOIN civicrm_group_contact grp ON {$contactField} = grp.contact_id AND grp.status = 'Added'";
 616             $where[] = "grp.group_id IN ({$actionSchedule->group_id})";
 617           }
 618         }
 619         elseif (!empty($actionSchedule->recipient_manual)) {
 620           $rList = CRM_Utils_Type::escape($actionSchedule->recipient_manual, 'String');
 621           $where[] = "{$contactField} IN ({$rList})";
 622         }
 623       }
 624       elseif (!is_null($limitTo)) {
 625         $addGroup = $addWhere = '';
 626         if ($actionSchedule->group_id) {
 627           // CRM-13577 If smart group then use Cache table
 628           if ($isSmartGroup) {
 629             $addGroup = " INNER JOIN civicrm_group_contact_cache grp ON c.id = grp.contact_id";
 630             $addWhere = " grp.group_id IN ({$actionSchedule->group_id})";
 631           }
 632           else {
 633             $addGroup = " INNER JOIN civicrm_group_contact grp ON c.id = grp.contact_id AND grp.status = 'Added'";
 634             $addWhere = " grp.group_id IN ({$actionSchedule->group_id})";
 635           }
 636         }
 637         if (!empty($actionSchedule->recipient_manual)) {
 638           $rList = CRM_Utils_Type::escape($actionSchedule->recipient_manual, 'String');
 639           $addWhere = "c.id IN ({$rList})";
 640         }
 641       }
 642
 643       $select[] = "{$contactField} as contact_id";
 644       $select[] = 'e.id as entity_id';
 645       $select[] = "'{$mapping->entity}' as entity_table";
 646       $select[] = "{$actionSchedule->id} as action_schedule_id";
 647       $reminderJoinClause = "civicrm_action_log reminder ON reminder.contact_id = {$contactField} AND
 648 reminder.entity_id          = e.id AND
 649 reminder.entity_table       = '{$mapping->entity}' AND
 650 reminder.action_schedule_id = %1";
 651
 652       if ($anniversary) {
 653         // only consider reminders less than 11 months ago
 654         $reminderJoinClause .= " AND reminder.action_date_time > DATE_SUB({$now}, INTERVAL 11 MONTH)";
 655       }
 656
 657       if ($table != 'civicrm_contact e') {
 658         $join[] = "INNER JOIN civicrm_contact c ON c.id = {$contactField} AND c.is_deleted = 0 AND c.is_deceased = 0 ";
 659       }
 660
 661       $multilingual = CRM_Core_I18n::isMultilingual();
 662       if ($multilingual && !empty($actionSchedule->filter_contact_language)) {
 663         $tableAlias = ($table != 'civicrm_contact e') ? 'c' : 'e';
 664
 665         // get language filter for the schedule
 666         $filter_contact_language = explode(CRM_Core_DAO::VALUE_SEPARATOR, $actionSchedule->filter_contact_language);
 667         $w = '';
 668         if (($key = array_search(CRM_Core_I18n::NONE, $filter_contact_language)) !== FALSE) {
 669           $w .= "{$tableAlias}.preferred_language IS NULL OR {$tableAlias}.preferred_language = '' OR ";
 670           unset($filter_contact_language[$key]);
 671         }
 672         if (count($filter_contact_language) > 0) {
 673           $w .= "{$tableAlias}.preferred_language IN ('" . implode("','", $filter_contact_language) . "')";
 674         }
 675         $where[] = "($w)";
 676       }
 677
 678       if ($actionSchedule->start_action_date) {
 679         $startDateClause = array();
 680         $op = ($actionSchedule->start_action_condition == 'before' ? '<=' : '>=');
 681         $operator = ($actionSchedule->start_action_condition == 'before' ? 'DATE_SUB' : 'DATE_ADD');
 682         $date = $operator . "({$dateField}, INTERVAL {$actionSchedule->start_action_offset} {$actionSchedule->start_action_unit})";
 683         $startDateClause[] = "'{$now}' >= {$date}";
 684         if ($mapping->entity == 'civicrm_participant') {
 685           $startDateClause[] = $operator . "({$now}, INTERVAL 1 DAY ) {$op} " . $dateField;
 686         }
 687         else {
 688           $startDateClause[] = "DATE_SUB({$now}, INTERVAL 1 DAY ) <= {$date}";
 689         }
 690
 691         $startDate = implode(' AND ', $startDateClause);
 692       }
 693       elseif ($actionSchedule->absolute_date) {
 694         $startDate = "DATEDIFF(DATE('{$now}'),'{$actionSchedule->absolute_date}') = 0";
 695       }
 696
 697       // ( now >= date_built_from_start_time ) OR ( now = absolute_date )
 698       $dateClause = "reminder.id IS NULL AND {$startDate}";
 699
 700       // start composing query
 701       $selectClause = 'SELECT ' . implode(', ', $select);
 702       $fromClause = "FROM $from";
 703       $joinClause = !empty($join) ? implode(' ', $join) : '';
 704       $whereClause = 'WHERE ' . implode(' AND ', $where);
 705       $limitWhereClause = '';
 706       if (!empty($limitWhere)) {
 707         $limitWhereClause = ' AND ' . implode(' AND ', $limitWhere);
 708       }
 709
 710       $query = "
 711 INSERT INTO civicrm_action_log ({$selectColumns})
 712 {$selectClause}
 713 {$fromClause}
 714 {$joinClause}
 715 LEFT JOIN {$reminderJoinClause}
 716 {$whereClause} {$limitWhereClause} AND {$dateClause} {$notINClause}
 717 ";
 718
 719       // In some cases reference_date got outdated due to many reason e.g. In Membership renewal end_date got extended
 720       // which means reference date mismatches with the end_date where end_date may be used as the start_action_date
 721       // criteria  for some schedule reminder so in order to send new reminder we INSERT new reminder with new reference_date
 722       // value via UNION operation
 723       if (strpos($selectColumns, 'reference_date') !== FALSE) {
 724         $dateClause = str_replace('reminder.id IS NULL', 'reminder.id IS NOT NULL', $dateClause);
 725         $referenceQuery = "
 726 INSERT INTO civicrm_action_log ({$selectColumns})
 727 {$selectClause}
 728 {$fromClause}
 729 {$joinClause}
 730  LEFT JOIN {$reminderJoinClause}
 731 {$whereClause} {$limitWhereClause} {$notINClause} AND {$dateClause} AND
 732  reminder.action_date_time IS NOT NULL AND
 733  reminder.reference_date IS NOT NULL
 734 GROUP BY reminder.id, reminder.reference_date
 735 HAVING reminder.id = MAX(reminder.id) AND reminder.reference_date <> {$dateField}
 736 ";
 737       }
 738
 739       CRM_Core_DAO::executeQuery($query, array(1 => array($actionSchedule->id, 'Integer')));
 740
 741       if (!empty($referenceQuery)) {
 742         CRM_Core_DAO::executeQuery($referenceQuery, array(1 => array($actionSchedule->id, 'Integer')));
 743       }
 744
 745       $isSendToAdditionalContacts = (!is_null($limitTo) && $limitTo == 0 && (!empty($addGroup) || !empty($addWhere))) ? TRUE : FALSE;
 746       if ($isSendToAdditionalContacts) {
 747         $contactTable = "civicrm_contact c";
 748         $addSelect = "SELECT c.id as contact_id, c.id as entity_id, 'civicrm_contact' as entity_table, {$actionSchedule->id} as action_schedule_id";
 749         $additionReminderClause = "civicrm_action_log reminder ON reminder.contact_id = c.id AND
 750           reminder.entity_id          = c.id AND
 751           reminder.entity_table       = 'civicrm_contact' AND
 752           reminder.action_schedule_id = {$actionSchedule->id}";
 753         $addWhereClause = '';
 754         if ($addWhere) {
 755           $addWhereClause = "AND {$addWhere}";
 756         }
 757         $insertAdditionalSql = "
 758 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
 759 {$addSelect}
 760 FROM ({$contactTable})
 761 LEFT JOIN {$additionReminderClause}
 762 {$addGroup}
 763 WHERE c.is_deleted = 0 AND c.is_deceased = 0
 764 {$addWhereClause}
 765
 766 AND reminder.id IS NULL
 767 AND c.id NOT IN (
 768      SELECT rem.contact_id
 769      FROM civicrm_action_log rem INNER JOIN {$mapping->entity} e ON rem.entity_id = e.id
 770      WHERE rem.action_schedule_id = {$actionSchedule->id}
 771       AND rem.entity_table = '{$mapping->entity}'
 772     )
 773 GROUP BY c.id
 774 ";
 775         CRM_Core_DAO::executeQuery($insertAdditionalSql);
 776       }
 777       // if repeat is turned ON:
 778       if ($actionSchedule->is_repeat) {
 779         $repeatEvent = ($actionSchedule->end_action == 'before' ? 'DATE_SUB' : 'DATE_ADD') . "({$dateField}, INTERVAL {$actionSchedule->end_frequency_interval} {$actionSchedule->end_frequency_unit})";
 780
 781         if ($actionSchedule->repetition_frequency_unit == 'day') {
 782           $interval = "{$actionSchedule->repetition_frequency_interval} DAY";
 783         }
 784         elseif ($actionSchedule->repetition_frequency_unit == 'week') {
 785           $interval = "{$actionSchedule->repetition_frequency_interval} WEEK";
 786         }
 787         elseif ($actionSchedule->repetition_frequency_unit == 'month') {
 788           $interval = "{$actionSchedule->repetition_frequency_interval} MONTH";
 789         }
 790         elseif ($actionSchedule->repetition_frequency_unit == 'year') {
 791           $interval = "{$actionSchedule->repetition_frequency_interval} YEAR";
 792         }
 793         else {
 794           $interval = "{$actionSchedule->repetition_frequency_interval} HOUR";
 795         }
 796
 797         // (now <= repeat_end_time )
 798         $repeatEventClause = "'{$now}' <= {$repeatEvent}";
 799         // diff(now && logged_date_time) >= repeat_interval
 800         $havingClause = "HAVING TIMESTAMPDIFF(HOUR, latest_log_time, CAST({$now} AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL $interval))";
 801         $groupByClause = 'GROUP BY reminder.contact_id, reminder.entity_id, reminder.entity_table';
 802         $selectClause .= ', MAX(reminder.action_date_time) as latest_log_time';
 803         //CRM-15376 - do not send our reminders if original criteria no longer applies
 804         // the first part of the startDateClause array is the earliest the reminder can be sent. If the
 805         // event (e.g membership_end_date) has changed then the reminder may no longer apply
 806         // @todo - this only handles events that get moved later. Potentially they might get moved earlier
 807         $originalEventStartDateClause = empty($startDateClause) ? '' : 'AND' . $startDateClause[0];
 808         $sqlInsertValues = "{$selectClause}
 809 {$fromClause}
 810 {$joinClause}
 811 INNER JOIN {$reminderJoinClause}
 812 {$whereClause} {$limitWhereClause} AND {$repeatEventClause} {$originalEventStartDateClause} {$notINClause}
 813 {$groupByClause}
 814 {$havingClause}";
 815
 816         $valsqlInsertValues = CRM_Core_DAO::executeQuery($sqlInsertValues, array(
 817             1 => array(
 818               $actionSchedule->id,
 819               'Integer',
 820             ),
 821           )
 822         );
 823
 824         $arrValues = array();
 825         while ($valsqlInsertValues->fetch()) {
 826           $arrValues[] = "( {$valsqlInsertValues->contact_id}, {$valsqlInsertValues->entity_id}, '{$valsqlInsertValues->entity_table}',{$valsqlInsertValues->action_schedule_id} )";
 827         }
 828
 829         $valString = implode(',', $arrValues);
 830
 831         if ($valString) {
 832           $query = '
 833               INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id) VALUES ' . $valString;
 834           CRM_Core_DAO::executeQuery($query, array(1 => array($actionSchedule->id, 'Integer')));
 835         }
 836
 837         if ($isSendToAdditionalContacts) {
 838           $addSelect .= ', MAX(reminder.action_date_time) as latest_log_time';
 839           $sqlEndEventCheck = "
 840 SELECT * FROM {$table}
 841 {$whereClause} AND {$repeatEventClause} LIMIT 1";
 842
 843           $daoCheck = CRM_Core_DAO::executeQuery($sqlEndEventCheck);
 844           if ($daoCheck->fetch()) {
 845             $valSqlAdditionInsert = "
 846 {$addSelect}
 847 FROM  {$contactTable}
 848 {$addGroup}
 849 INNER JOIN {$additionReminderClause}
 850 WHERE {$addWhere} AND c.is_deleted = 0 AND c.is_deceased = 0
 851 GROUP BY reminder.contact_id
 852 {$havingClause}
 853 ";
 854             $daoForVals = CRM_Core_DAO::executeQuery($valSqlAdditionInsert);
 855             $addValues = array();
 856             while ($daoForVals->fetch()) {
 857               $addValues[] = "( {$daoForVals->contact_id}, {$daoForVals->entity_id}, '{$daoForVals->entity_table}',{$daoForVals->action_schedule_id} )";
 858             }
 859             $valString = implode(',', $addValues);
 860
 861             if ($valString) {
 862               $query = '
 863                 INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id) VALUES ' . $valString;
 864               CRM_Core_DAO::executeQuery($query);
 865             }
 866           }
 867         }
 868       }
 869     }
 870   }
 871
 872   /**
 873    * @param $field
 874    *
 875    * @return null|string
 876    */
 877   public static function permissionedRelationships($field) {
 878     $query = '
 879 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
 880 FROM      civicrm_membership m
 881 LEFT JOIN civicrm_membership cm ON cm.id = m.owner_membership_id
 882 LEFT JOIN civicrm_membership_type cmt ON cmt.id = m.membership_type_id
 883 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 )
 884                                         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 ) )
 885 WHERE     m.owner_membership_id IS NOT NULL AND
 886           ( rel.is_permission_a_b = 0 OR rel.is_permission_b_a = 0)
 887
 888 ';
 889     $excludeIds = array();
 890     $dao = CRM_Core_DAO::executeQuery($query, array());
 891     while ($dao->fetch()) {
 892       if ($dao->slave_contact == $dao->contact_id_a && $dao->is_permission_a_b == 0) {
 893         $excludeIds[] = $dao->slave_contact;
 894       }
 895       elseif ($dao->slave_contact == $dao->contact_id_b && $dao->is_permission_b_a == 0) {
 896         $excludeIds[] = $dao->slave_contact;
 897       }
 898     }
 899
 900     if (!empty($excludeIds)) {
 901       $clause = "AND {$field} NOT IN ( " . implode(', ', $excludeIds) . ' ) ';
 902       return $clause;
 903     }
 904     return NULL;
 905   }
 906
 907   /**
 908    * @param null $now
 909    * @param array $params
 910    *
 911    * @return array
 912    */
 913   public static function processQueue($now = NULL, $params = array()) {
 914     $now = $now ? CRM_Utils_Time::setTime($now) : CRM_Utils_Time::getTime();
 915
 916     $mappings = CRM_Core_BAO_ActionSchedule::getMappings();
 917     foreach ($mappings as $mappingID => $mapping) {
 918       CRM_Core_BAO_ActionSchedule::buildRecipientContacts($mappingID, $now, $params);
 919       CRM_Core_BAO_ActionSchedule::sendMailings($mappingID, $now);
 920     }
 921
 922     $result = array(
 923       'is_error' => 0,
 924       'messages' => ts('Sent all scheduled reminders successfully'),
 925     );
 926     return $result;
 927   }
 928
 929   /**
 930    * @param int $id
 931    * @param int $mappingID
 932    *
 933    * @return null|string
 934    */
 935   public static function isConfigured($id, $mappingID) {
 936     $queryString = "SELECT count(id) FROM civicrm_action_schedule
 937                         WHERE  mapping_id = %1 AND
 938                                entity_value = %2";
 939
 940     $params = array(
 941       1 => array($mappingID, 'Integer'),
 942       2 => array($id, 'Integer'),
 943     );
 944     return CRM_Core_DAO::singleValueQuery($queryString, $params);
 945   }
 946
 947   /**
 948    * @param int $mappingID
 949    * @param $recipientType
 950    *
 951    * @return array
 952    */
 953   public static function getRecipientListing($mappingID, $recipientType) {
 954     if (!$mappingID) {
 955       return array();
 956     }
 957
 958     /** @var \Civi\ActionSchedule\Mapping $mapping */
 959     $mapping = CRM_Utils_Array::first(CRM_Core_BAO_ActionSchedule::getMappings(array(
 960       'id' => $mappingID,
 961     )));
 962     return $mapping->getRecipientListing($recipientType);
 963   }
 964
 965   /**
 966    * @param $communication_language
 967    * @param $preferred_language
 968    */
 969   public static function setCommunicationLanguage($communication_language, $preferred_language) {
 970     $config = CRM_Core_Config::singleton();
 971     $language = $config->lcMessages;
 972
 973     // prepare the language for the email
 974     if ($communication_language == CRM_Core_I18n::AUTO) {
 975       if (!empty($preferred_language)) {
 976         $language = $preferred_language;
 977       }
 978     }
 979     else {
 980       $language = $communication_language;
 981     }
 982
 983     // language not in the existing language, use default
 984     $languages = CRM_Core_I18n::languages(TRUE);
 985     if (!in_array($language, $languages)) {
 986       $language = $config->lcMessages;
 987     }
 988
 989     // change the language
 990     $i18n = CRM_Core_I18n::singleton();
 991     $i18n->setLanguage($language);
 992   }
 993
 994   /**
 995    * Save a record about the delivery of a reminder email.
 996    *
 997    * WISHLIST: Instead of saving $actionSchedule->body_html, call this immediately after
 998    * sending the message and pass in the fully rendered text of the message.
 999    *
1000    * @param CRM_Core_DAO_ActionSchedule $actionSchedule
1001    * @param Civi\ActionSchedule\Mapping $mapping
1002    * @param int $contactID
1003    * @param int $entityID
1004    * @param int|NULL $caseID
1005    * @throws CRM_Core_Exception
1006    */
1007   protected static function createMailingActivity($actionSchedule, $mapping, $contactID, $entityID, $caseID) {
1008     $session = CRM_Core_Session::singleton();
1009
1010     if ($mapping->entity == 'civicrm_membership') {
1011       $activityTypeID
1012         = CRM_Core_OptionGroup::getValue('activity_type', 'Membership Renewal Reminder', 'name');
1013     }
1014     else {
1015       $activityTypeID
1016         = CRM_Core_OptionGroup::getValue('activity_type', 'Reminder Sent', 'name');
1017     }
1018
1019     $activityParams = array(
1020       'subject' => $actionSchedule->title,
1021       'details' => $actionSchedule->body_html,
1022       'source_contact_id' => $session->get('userID') ? $session->get('userID') : $contactID,
1023       'target_contact_id' => $contactID,
1024       'activity_date_time' => CRM_Utils_Time::getTime('YmdHis'),
1025       'status_id' => CRM_Core_OptionGroup::getValue('activity_status', 'Completed', 'name'),
1026       'activity_type_id' => $activityTypeID,
1027       'source_record_id' => $entityID,
1028     );
1029     $activity = CRM_Activity_BAO_Activity::create($activityParams);
1030
1031     //file reminder on case if source activity is a case activity
1032     if (!empty($caseID)) {
1033       $caseActivityParams = array();
1034       $caseActivityParams['case_id'] = $caseID;
1035       $caseActivityParams['activity_id'] = $activity->id;
1036       CRM_Case_BAO_Case::processCaseActivity($caseActivityParams);
1037     }
1038   }
1039
1040   /**
1041    * @param $mapping
1042    * @param $actionSchedule
1043    * @return string
1044    */
1045   protected static function prepareMailingQuery($mapping, $actionSchedule) {
1046     $select = CRM_Utils_SQL_Select::from('civicrm_action_log reminder', array('mode' => 'out'))
1047       ->select("reminder.id as reminderID, reminder.contact_id as contactID, reminder.entity_table as entityTable, reminder.*, e.id as entityID, e.*")
1048       ->where("reminder.action_schedule_id = #casScheduleId")
1049       ->param('casScheduleId', $actionSchedule->id)
1050       ->where("reminder.action_date_time IS NULL");
1051
1052     if ($actionSchedule->limit_to == 0) {
1053       $entityJoinClause = "LEFT JOIN {$mapping->entity} e ON e.id = reminder.entity_id";
1054       $select->where("e.id = reminder.entity_id OR reminder.entity_table = 'civicrm_contact'");
1055     }
1056     else {
1057       $entityJoinClause = "INNER JOIN {$mapping->entity} e ON e.id = reminder.entity_id";
1058     }
1059     if ($mapping->entity == 'civicrm_activity') {
1060       $entityJoinClause .= ' AND e.is_current_revision = 1 AND e.is_deleted = 0 ';
1061     }
1062     $select->join('a', $entityJoinClause);
1063
1064     if ($mapping->entity == 'civicrm_activity') {
1065       $compInfo = CRM_Core_Component::getEnabledComponents();
1066       $select->select('ov.label as activity_type, e.id as activity_id');
1067
1068       $JOIN_TYPE = ($actionSchedule->limit_to == 0) ? 'LEFT JOIN' : 'INNER JOIN';
1069       $select->join("og", "$JOIN_TYPE civicrm_option_group og ON og.name = 'activity_type'");
1070       $select->join("ov", "$JOIN_TYPE civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id");
1071
1072       // if CiviCase component is enabled, join for caseId.
1073       if (array_key_exists('CiviCase', $compInfo)) {
1074         $select->select("civicrm_case_activity.case_id as case_id");
1075         $select->join('civicrm_case_activity', "LEFT JOIN `civicrm_case_activity` ON `e`.`id` = `civicrm_case_activity`.`activity_id`");
1076       }
1077     }
1078
1079     if ($mapping->entity == 'civicrm_participant') {
1080       $select->select('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');
1081
1082       $JOIN_TYPE = ($actionSchedule->limit_to == 0) ? 'LEFT JOIN' : 'INNER JOIN';
1083       $select->join('participant_stuff', "
1084 $JOIN_TYPE civicrm_event ev ON e.event_id = ev.id
1085 $JOIN_TYPE civicrm_option_group og ON og.name = 'event_type'
1086 $JOIN_TYPE civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id
1087 LEFT JOIN civicrm_loc_block lb ON lb.id = ev.loc_block_id
1088 LEFT JOIN civicrm_address address ON address.id = lb.address_id
1089 LEFT JOIN civicrm_email email ON email.id = lb.email_id
1090 LEFT JOIN civicrm_phone phone ON phone.id = lb.phone_id
1091 ");
1092     }
1093
1094     if ($mapping->entity == 'civicrm_membership') {
1095       $select->select('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');
1096
1097       $JOIN_TYPE = ($actionSchedule->limit_to == 0) ? 'LEFT JOIN' : 'INNER JOIN';
1098       $select->join('mt', "$JOIN_TYPE civicrm_membership_type mt ON e.membership_type_id = mt.id");
1099       $select->join('ms', "$JOIN_TYPE civicrm_membership_status ms ON e.status_id = ms.id");
1100     }
1101
1102     return $select->toSQL();
1103   }
1104
1105   /**
1106    * @param TokenRow $tokenRow
1107    * @param CRM_Core_DAO_ActionSchedule $schedule
1108    * @param int $toContactID
1109    * @throws CRM_Core_Exception
1110    * @return array
1111    *   List of error messages.
1112    */
1113   protected static function sendReminderSms($tokenRow, $schedule, $toContactID) {
1114     $toPhoneNumber = self::pickSmsPhoneNumber($toContactID);
1115     if (!$toPhoneNumber) {
1116       return array("sms_phone_missing" => "Couldn't find recipient's phone number.");
1117     }
1118
1119     $messageSubject = $tokenRow->render('subject');
1120     $sms_body_text = $tokenRow->render('sms_body_text');
1121
1122     $session = CRM_Core_Session::singleton();
1123     $userID = $session->get('userID') ? $session->get('userID') : $tokenRow->context['contactId'];
1124     $smsParams = array(
1125       'To' => $toPhoneNumber,
1126       'provider_id' => $schedule->sms_provider_id,
1127       'activity_subject' => $messageSubject,
1128     );
1129     $activityTypeID = CRM_Core_OptionGroup::getValue('activity_type',
1130       'SMS',
1131       'name'
1132     );
1133     $activityParams = array(
1134       'source_contact_id' => $userID,
1135       'activity_type_id' => $activityTypeID,
1136       'activity_date_time' => date('YmdHis'),
1137       'subject' => $messageSubject,
1138       'details' => $sms_body_text,
1139       'status_id' => CRM_Core_OptionGroup::getValue('activity_status', 'Completed', 'name'),
1140     );
1141
1142     $activity = CRM_Activity_BAO_Activity::create($activityParams);
1143
1144     CRM_Activity_BAO_Activity::sendSMSMessage($tokenRow->context['contactId'],
1145       $sms_body_text,
1146       $smsParams,
1147       $activity->id,
1148       $userID
1149     );
1150
1151     return array();
1152   }
1153
1154   /**
1155    * @param CRM_Core_DAO_ActionSchedule $actionSchedule
1156    * @return string
1157    *   Ex: "Alice <alice@example.org>".
1158    */
1159   protected static function pickFromEmail($actionSchedule) {
1160     $domainValues = CRM_Core_BAO_Domain::getNameAndEmail();
1161     $fromEmailAddress = "$domainValues[0] <$domainValues[1]>";
1162     if ($actionSchedule->from_email) {
1163       $fromEmailAddress = "$actionSchedule->from_name <$actionSchedule->from_email>";
1164       return $fromEmailAddress;
1165     }
1166     return $fromEmailAddress;
1167   }
1168
1169   /**
1170    * @param TokenRow $tokenRow
1171    * @param CRM_Core_DAO_ActionSchedule $schedule
1172    * @param int $toContactID
1173    * @return array
1174    *   List of error messages.
1175    */
1176   protected static function sendReminderEmail($tokenRow, $schedule, $toContactID) {
1177     $toEmail = CRM_Contact_BAO_Contact::getPrimaryEmail($toContactID);
1178     if (!$toEmail) {
1179       return array("email_missing" => "Couldn't find recipient's email address.");
1180     }
1181
1182     $body_text = $tokenRow->render('body_text');
1183     $body_html = $tokenRow->render('body_html');
1184     if (!$schedule->body_text) {
1185       $body_text = CRM_Utils_String::htmlToText($body_html);
1186     }
1187
1188     // set up the parameters for CRM_Utils_Mail::send
1189     $mailParams = array(
1190       'groupName' => 'Scheduled Reminder Sender',
1191       'from' => self::pickFromEmail($schedule),
1192       'toName' => $tokenRow->context['contact']['display_name'],
1193       'toEmail' => $toEmail,
1194       'subject' => $tokenRow->render('subject'),
1195       'entity' => 'action_schedule',
1196       'entity_id' => $schedule->id,
1197     );
1198
1199     if (!$body_html || $tokenRow->context['contact']['preferred_mail_format'] == 'Text' ||
1200       $tokenRow->context['contact']['preferred_mail_format'] == 'Both'
1201     ) {
1202       // render the &amp; entities in text mode, so that the links work
1203       $mailParams['text'] = str_replace('&amp;', '&', $body_text);
1204     }
1205     if ($body_html && ($tokenRow->context['contact']['preferred_mail_format'] == 'HTML' ||
1206         $tokenRow->context['contact']['preferred_mail_format'] == 'Both'
1207       )
1208     ) {
1209       $mailParams['html'] = $body_html;
1210     }
1211     $result = CRM_Utils_Mail::send($mailParams);
1212     if (!$result || is_a($result, 'PEAR_Error')) {
1213       return array('email_fail' => 'Failed to send message');
1214     }
1215
1216     return array();
1217   }
1218
1219   /**
1220    * @param CRM_Core_DAO_ActionSchedule $schedule
1221    * @param \Civi\ActionSchedule\Mapping $mapping
1222    * @return \Civi\Token\TokenProcessor
1223    */
1224   protected static function createTokenProcessor($schedule, $mapping) {
1225     $tp = new \Civi\Token\TokenProcessor(\Civi\Core\Container::singleton()->get('dispatcher'), array(
1226       'controller' => __CLASS__,
1227       'actionSchedule' => $schedule,
1228       'actionMapping' => $mapping,
1229       'smarty' => TRUE,
1230     ));
1231     $tp->addMessage('body_text', $schedule->body_text, 'text/plain');
1232     $tp->addMessage('body_html', $schedule->body_html, 'text/html');
1233     $tp->addMessage('sms_body_text', $schedule->sms_body_text, 'text/plain');
1234     $tp->addMessage('subject', $schedule->subject, 'text/plain');
1235     return $tp;
1236   }
1237
1238   /**
1239    * @param $dao
1240    * @return string|NULL
1241    */
1242   protected static function pickSmsPhoneNumber($smsToContactId) {
1243     $toPhoneNumbers = CRM_Core_BAO_Phone::allPhones($smsToContactId, FALSE, 'Mobile', array(
1244       'is_deceased' => 0,
1245       'is_deleted' => 0,
1246       'do_not_sms' => 0,
1247     ));
1248     //to get primary mobile ph,if not get a first mobile phONE
1249     if (!empty($toPhoneNumbers)) {
1250       $toPhoneNumberDetails = reset($toPhoneNumbers);
1251       $toPhoneNumber = CRM_Utils_Array::value('phone', $toPhoneNumberDetails);
1252       return $toPhoneNumber;
1253     }
1254     return NULL;
1255   }
1256
1257 }