CRM/Core/BAO/ActionSchedule.php

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