[Summary view] [Print] [Text view]

   1  <?php
   2  /*+***********************************************************************************
   3   * The contents of this file are subject to the vtiger CRM Public License Version 1.0
   4   * ("License"); You may not use this file except in compliance with the License
   5   * The Original Code is:  vtiger CRM Open Source
   6   * The Initial Developer of the Original Code is vtiger.
   7   * Portions created by vtiger are Copyright (C) vtiger.
   8   * All Rights Reserved.
   9   *************************************************************************************/
  10  
  11  require_once  'modules/Reports/Reports.php';
  12  require_once  'modules/Reports/ReportRun.php';
  13  require_once  'include/Zend/Json.php';
  14  
  15  class VTScheduledReport extends Reports {
  16  
  17      var $db;
  18      var $user;
  19  
  20      var $isScheduled = false;
  21      var $scheduledInterval = null;
  22      var $scheduledFormat = null;
  23      var $scheduledRecipients = null;
  24  
  25      static $SCHEDULED_HOURLY = 1;
  26      static $SCHEDULED_DAILY = 2;
  27      static $SCHEDULED_WEEKLY = 3;
  28      static $SCHEDULED_BIWEEKLY = 4;
  29      static $SCHEDULED_MONTHLY = 5;
  30      static $SCHEDULED_ANNUALLY = 6;
  31  
  32  	public function  __construct($adb, $user, $reportid="") {
  33          $this->db    = $adb;
  34          $this->user = $user;
  35          $this->id    = $reportid;
  36          parent::__construct($reportid);
  37      }
  38  
  39  	public function getReportScheduleInfo() {
  40          global $adb;
  41  
  42          if(!empty($this->id)) {
  43              $cachedInfo = VTCacheUtils::lookupReport_ScheduledInfo($this->user->id, $this->id);
  44  
  45              if($cachedInfo == false) {
  46                  $result = $adb->pquery('SELECT * FROM vtiger_scheduled_reports WHERE reportid=?', array($this->id));
  47  
  48                  if($adb->num_rows($result) > 0) {
  49                      $reportScheduleInfo = $adb->raw_query_result_rowdata($result, 0);
  50  
  51                      $scheduledInterval = (!empty($reportScheduleInfo['schedule']))?Zend_Json::decode($reportScheduleInfo['schedule']):array();
  52                      $scheduledRecipients = (!empty($reportScheduleInfo['recipients']))?Zend_Json::decode($reportScheduleInfo['recipients']):array();
  53  
  54                      VTCacheUtils::updateReport_ScheduledInfo($this->user->id, $this->id, true, $reportScheduleInfo['format'],
  55                                                          $scheduledInterval, $scheduledRecipients, $reportScheduleInfo['next_trigger_time']);
  56  
  57                      $cachedInfo = VTCacheUtils::lookupReport_ScheduledInfo($this->user->id, $this->id);
  58                  }
  59              }
  60              if($cachedInfo) {
  61                  $this->isScheduled            = $cachedInfo['isScheduled'];
  62                  $this->scheduledFormat        = $cachedInfo['scheduledFormat'];
  63                  $this->scheduledInterval    = $cachedInfo['scheduledInterval'];
  64                  $this->scheduledRecipients    = $cachedInfo['scheduledRecipients'];
  65                  $this->scheduledTime        = $cachedInfo['scheduledTime'];
  66                  return true;
  67              }
  68          }
  69          return false;
  70      }
  71  
  72  	public function getRecipientEmails() {
  73          $recipientsInfo = $this->scheduledRecipients;
  74  
  75          $recipientsList = array();
  76          if(!empty($recipientsInfo)) {
  77              if(!empty($recipientsInfo['users'])) {
  78                  $recipientsList = array_merge($recipientsList, $recipientsInfo['users']);
  79              }
  80  
  81              if(!empty($recipientsInfo['roles'])) {
  82                  foreach($recipientsInfo['roles'] as $roleId) {
  83                      $roleUsers = getRoleUsers($roleId);
  84                      foreach($roleUsers as $userId => $userName) {
  85                          array_push($recipientsList, $userId);
  86                      }
  87                  }
  88              }
  89  
  90              if(!empty($recipientsInfo['rs'])) {
  91                  foreach($recipientsInfo['rs'] as $roleId) {
  92                      $users = getRoleAndSubordinateUsers($roleId);
  93                      foreach($users as $userId => $userName) {
  94                          array_push($recipientsList, $userId);
  95                      }
  96                  }
  97              }
  98  
  99  
 100              if(!empty($recipientsInfo['groups'])) {
 101                  require_once  'include/utils/GetGroupUsers.php';
 102                  foreach($recipientsInfo['groups'] as $groupId) {
 103                      $userGroups = new GetGroupUsers();
 104                      $userGroups->getAllUsersInGroup($groupId);
 105                      $recipientsList = array_merge($recipientsList, $userGroups->group_users);
 106                  }
 107              }
 108          }
 109          $recipientsEmails = array();
 110          if(!empty($recipientsList) && count($recipientsList) > 0) {
 111              foreach($recipientsList as $userId) {
 112                  $userName = getUserFullName($userId);
 113                  $userEmail = getUserEmail($userId);
 114                  if(!in_array($userEmail, $recipientsEmails)) {
 115                      $recipientsEmails[$userName] = $userEmail;
 116                  }
 117              }
 118          }
 119          return $recipientsEmails;
 120      }
 121  
 122  	public function sendEmail() {
 123          global $currentModule;
 124          require_once  'vtlib/Vtiger/Mailer.php';
 125  
 126          $vtigerMailer = new Vtiger_Mailer();
 127  
 128          $recipientEmails = $this->getRecipientEmails();
 129          foreach($recipientEmails as $name => $email) {
 130              $vtigerMailer->AddAddress($email, $name);
 131          }
 132  
 133          $currentTime = date('Y-m-d H:i:s');
 134          $subject = $this->reportname .' - '. $currentTime .' ('. DateTimeField::getDBTimeZone() .')';
 135  
 136          $contents = getTranslatedString('LBL_AUTO_GENERATED_REPORT_EMAIL', $currentModule) .'<br/><br/>';
 137          $contents .= '<b>'.getTranslatedString('LBL_REPORT_NAME', $currentModule) .' :</b> '. $this->reportname .'<br/>';
 138          $contents .= '<b>'.getTranslatedString('LBL_DESCRIPTION', $currentModule) .' :</b><br/>'. $this->reportdescription .'<br/><br/>';
 139  
 140          $vtigerMailer->Subject = $subject;
 141          $vtigerMailer->Body    = $contents;
 142          $vtigerMailer->ContentType = "text/html";
 143  
 144          $baseFileName = preg_replace('/[^a-zA-Z0-9_-\s]/', '', $this->reportname).'_'. preg_replace('/[^a-zA-Z0-9_-\s]/', '', $currentTime);
 145  
 146          $oReportRun = ReportRun::getInstance($this->id);
 147          $reportFormat = $this->scheduledFormat;
 148          $attachments = array();
 149  
 150          if($reportFormat == 'pdf' || $reportFormat == 'both') {
 151              $fileName = $baseFileName.'.pdf';
 152              $filePath = 'storage/'.$fileName;
 153              $attachments[$fileName] = $filePath;
 154              $pdf = $oReportRun->getReportPDF();
 155              $pdf->Output($filePath,'F');
 156          }
 157          if ($reportFormat == 'excel' || $reportFormat == 'both') {
 158              $fileName = $baseFileName.'.xls';
 159              $filePath = 'storage/'.$fileName;
 160              $attachments[$fileName] = $filePath;
 161              $oReportRun->writeReportToExcelFile($filePath);
 162          }
 163  
 164          foreach($attachments as $attachmentName => $path) {
 165              $vtigerMailer->AddAttachment($path, $attachmentName);
 166          }
 167  
 168          $vtigerMailer->Send(true);
 169  
 170          foreach($attachments as $attachmentName => $path) {
 171              unlink($path);
 172          }
 173      }
 174  
 175  	public function getNextTriggerTime() {
 176          $scheduleInfo = $this->scheduledInterval;
 177  
 178          $scheduleType        = $scheduleInfo['scheduletype'];
 179          $scheduledMonth        = $scheduleInfo['month'];
 180          $scheduledDayOfMonth= $scheduleInfo['date'];
 181          $scheduledDayOfWeek = $scheduleInfo['day'];
 182          $scheduledTime        = $scheduleInfo['time'];
 183          if(empty($scheduledTime)) {
 184              $scheduledTime = '10:00';
 185          } elseif(stripos(':', $scheduledTime) === false) {
 186              $scheduledTime = $scheduledTime .':00';
 187          }
 188  
 189          if($scheduleType == VTScheduledReport::$SCHEDULED_HOURLY) {
 190              return date("Y-m-d H:i:s",strtotime("+1 hour"));
 191          }
 192          if($scheduleType == VTScheduledReport::$SCHEDULED_DAILY) {
 193              return date("Y-m-d H:i:s",strtotime("+ 1 day ".$scheduledTime));
 194          }
 195          if($scheduleType == VTScheduledReport::$SCHEDULED_WEEKLY) {
 196              $weekDays = array('0'=>'Sunday','1'=>'Monday','2'=>'Tuesday','3'=>'Wednesday','4'=>'Thursday','5'=>'Friday','6'=>'Saturday');
 197  
 198              if(date('w',time()) == $scheduledDayOfWeek) {
 199                  return date("Y-m-d H:i:s",strtotime('+1 week '.$scheduledTime));
 200              } else {
 201                  return date("Y-m-d H:i:s",strtotime($weekDays[$scheduledDayOfWeek].' '.$scheduledTime));
 202              }
 203          }
 204          if($scheduleType == VTScheduledReport::$SCHEDULED_BIWEEKLY) {
 205              $weekDays = array('0'=>'Sunday','1'=>'Monday','2'=>'Tuesday','3'=>'Wednesday','4'=>'Thursday','5'=>'Friday','6'=>'Saturday');
 206              if(date('w',time()) == $scheduledDayOfWeek) {
 207                  return date("Y-m-d H:i:s",strtotime('+2 weeks '.$scheduledTime));
 208              } else {
 209                  return date("Y-m-d H:i:s",strtotime($weekDays[$scheduledDayOfWeek].' '.$scheduledTime));
 210              }
 211          }
 212          if($scheduleType == VTScheduledReport::$SCHEDULED_MONTHLY) {
 213              $currentTime = time();
 214              $currentDayOfMonth = date('j',$currentTime);
 215  
 216              if($scheduledDayOfMonth == $currentDayOfMonth) {
 217                  return date("Y-m-d H:i:s",strtotime('+1 month '.$scheduledTime));
 218              } else {
 219                  $monthInFullText = date('F',$currentTime);
 220                  $yearFullNumberic = date('Y',$currentTime);
 221                  if($scheduledDayOfMonth < $currentDayOfMonth) {
 222                      $nextMonth = date("Y-m-d H:i:s",strtotime('next month'));
 223                      $monthInFullText = date('F',strtotime($nextMonth));
 224                  }
 225                  return date("Y-m-d H:i:s",strtotime($scheduledDayOfMonth.' '.$monthInFullText.' '.$yearFullNumberic.' '.$scheduledTime));
 226              }
 227          }
 228          if($scheduleType == VTScheduledReport::$SCHEDULED_ANNUALLY) {
 229              $months = array(0=>'January',1=>'February',2=>'March',3=>'April',4=>'May',5=>'June',6=>'July',
 230                                  7=>'August',8=>'September',9=>'October',10=>'November',11=>'December');
 231              $currentTime = time();
 232              $currentMonth = date('n',$currentTime);
 233              if(($scheduledMonth+1) == $currentMonth) {
 234                  return date("Y-m-d H:i:s",strtotime('+1 year '.$scheduledTime));
 235              } else {
 236                  $monthInFullText = $months[$scheduledMonth];
 237                  $yearFullNumberic = date('Y',$currentTime);
 238                  if(($scheduledMonth+1) < $currentMonth) {
 239                      $nextMonth = date("Y-m-d H:i:s",strtotime('next year'));
 240                      $yearFullNumberic = date('Y',strtotime($nextMonth));
 241                  }
 242                  return date("Y-m-d H:i:s",strtotime($scheduledDayOfMonth.' '.$monthInFullText.' '.$yearFullNumberic.' '.$scheduledTime));
 243              }
 244          }
 245      }
 246  
 247  	public function updateNextTriggerTime() {
 248          $adb = $this->db;
 249  
 250          $currentTime = date('Y-m-d H:i:s');
 251          $scheduledInterval = $this->scheduledInterval;
 252          $nextTriggerTime = $this->getNextTriggerTime(); // Compute based on the frequency set
 253  
 254          $adb->pquery('UPDATE vtiger_scheduled_reports SET next_trigger_time=? WHERE reportid=?', array($nextTriggerTime, $this->id));
 255      }
 256  
 257  	public static function generateRecipientOption($type, $value, $name='') {
 258          switch($type) {
 259              case "users"    :    if(empty($name)) $name = getUserFullName($value);
 260                                  $optionName = 'User::'.addslashes(decode_html($name));
 261                                  $optionValue = 'users::'.$value;
 262                                  break;
 263              case "groups"    :    if(empty($name)) {
 264                                      $groupInfo = getGroupName($value);
 265                                      $name = $groupInfo[0];
 266                                  }
 267                                  $optionName = 'Group::'.addslashes(decode_html($name));
 268                                  $optionValue = 'groups::'.$value;
 269                                  break;
 270              case "roles"    :    if(empty($name)) $name = getRoleName ($value);
 271                                  $optionName = 'Roles::'.addslashes(decode_html($name));
 272                                  $optionValue = 'roles::'.$value;
 273                                  break;
 274              case "rs"        :    if(empty($name)) $name = getRoleName ($value);
 275                                  $optionName = 'RoleAndSubordinates::'.addslashes(decode_html($name));
 276                                  $optionValue = 'rs::'.$value;
 277                                  break;
 278          }
 279          return '<option value="'.$optionValue.'">'.$optionName.'</option>';
 280      }
 281  
 282  	public function getSelectedRecipientsHTML() {
 283          $selectedRecipientsHTML = '';
 284          if(!empty($this->scheduledRecipients)) {
 285              foreach($this->scheduledRecipients as $recipientType => $recipients) {
 286                  foreach($recipients as $recipientId) {
 287                      $selectedRecipientsHTML .= VTScheduledReport::generateRecipientOption($recipientType, $recipientId);
 288                  }
 289              }
 290          }
 291          return $selectedRecipientsHTML;
 292      }
 293  
 294  	public static function getAvailableUsersHTML() {
 295          $userDetails = getAllUserName();
 296          $usersHTML = '<select id="availableRecipients" name="availableRecipients" multiple size="10" class="small crmFormList">';
 297          foreach($userDetails as $userId=>$userName) {
 298              $usersHTML .= VTScheduledReport::generateRecipientOption('users', $userId, $userName);
 299          }
 300          $usersHTML .= '</select>';
 301          return $usersHTML;
 302      }
 303  
 304  	public static function getAvailableGroupsHTML() {
 305          $grpDetails = getAllGroupName();
 306          $groupsHTML = '<select id="availableRecipients" name="availableRecipients" multiple size="10" class="small crmFormList">';
 307          foreach($grpDetails as $groupId=>$groupName) {
 308              $groupsHTML .= VTScheduledReport::generateRecipientOption('groups', $groupId, $groupName);
 309          }
 310          $groupsHTML .= '</select>';
 311          return $groupsHTML;
 312      }
 313  
 314  	public static function getAvailableRolesHTML() {
 315          $roleDetails = getAllRoleDetails();
 316          $rolesHTML = '<select id="availableRecipients" name="availableRecipients" multiple size="10" class="small crmFormList">';
 317          foreach($roleDetails as $roleId=>$roleInfo) {
 318              $rolesHTML .= VTScheduledReport::generateRecipientOption('roles', $roleId, $roleInfo[0]);
 319          }
 320          $rolesHTML .= '</select>';
 321          return $rolesHTML;
 322      }
 323  
 324  	public static function getAvailableRolesAndSubordinatesHTML() {
 325          $roleDetails = getAllRoleDetails();
 326          $rolesAndSubHTML = '<select id="availableRecipients" name="availableRecipients" multiple size="10" class="small crmFormList">';
 327          foreach($roleDetails as $roleId=>$roleInfo) {
 328              $rolesAndSubHTML .= VTScheduledReport::generateRecipientOption('rs', $roleId, $roleInfo[0]);
 329          }
 330          $rolesAndSubHTML .= '</select>';
 331          return $rolesAndSubHTML;
 332      }
 333  
 334  	public static function getScheduledReports($adb, $user) {
 335  
 336          $currentTime = date('Y-m-d H:i:s');
 337          $result = $adb->pquery("SELECT * FROM vtiger_scheduled_reports
 338                                      WHERE next_trigger_time = '' || next_trigger_time <= ?", array($currentTime));
 339  
 340          $scheduledReports = array();
 341          $noOfScheduledReports = $adb->num_rows($result);
 342          for($i=0; $i<$noOfScheduledReports; ++$i) {
 343              $reportScheduleInfo = $adb->raw_query_result_rowdata($result, $i);
 344  
 345              $scheduledInterval = (!empty($reportScheduleInfo['schedule']))?Zend_Json::decode($reportScheduleInfo['schedule']):array();
 346              $scheduledRecipients = (!empty($reportScheduleInfo['recipients']))?Zend_Json::decode($reportScheduleInfo['recipients']):array();
 347  
 348              $vtScheduledReport = new VTScheduledReport($adb, $user, $reportScheduleInfo['reportid']);
 349              $vtScheduledReport->isScheduled            = true;
 350              $vtScheduledReport->scheduledFormat        = $reportScheduleInfo['format'];
 351              $vtScheduledReport->scheduledInterval    = $scheduledInterval;
 352              $vtScheduledReport->scheduledRecipients = $scheduledRecipients;
 353              $vtScheduledReport->scheduledTime        = $reportScheduleInfo['next_trigger_time'];
 354  
 355              $scheduledReports[] = $vtScheduledReport;
 356          }
 357          return $scheduledReports;
 358      }
 359  
 360  	public static function runScheduledReports($adb) {
 361          require_once  'modules/com_vtiger_workflow/VTWorkflowUtils.php';
 362          $util = new VTWorkflowUtils();
 363          $adminUser = $util->adminUser();
 364  
 365          global $currentModule, $current_language;
 366          if(empty($currentModule)) $currentModule = 'Reports';
 367          if(empty($current_language)) $current_language = 'en_us';
 368  
 369          $scheduledReports = self::getScheduledReports($adb, $adminUser);
 370          foreach($scheduledReports as $scheduledReport) {
 371              $scheduledReport->sendEmail();
 372              $scheduledReport->updateNextTriggerTime();
 373          }
 374          $util->revertUser();
 375      }
 376  
 377  }
 378  
 379  ?>