[Summary view] [Print] [Text view]

   1  <?php
   2  
   3  /* +***********************************************************************************

   4   * The contents of this file are subject to the vtiger CRM Public License Version 1.0

   5   * ("License"); You may not use this file except in compliance with the License

   6   * The Original Code is:  vtiger CRM Open Source

   7   * The Initial Developer of the Original Code is vtiger.

   8   * Portions created by vtiger are Copyright (C) vtiger.

   9   * All Rights Reserved.

  10   * *********************************************************************************** */
  11  
  12  class Reports_ScheduleReports_Model extends Vtiger_Base_Model {
  13  
  14      var $scheduledFormat = 'CSV';
  15  
  16      static $SCHEDULED_DAILY = 1;
  17      static $SCHEDULED_WEEKLY = 2;
  18      static $SCHEDULED_MONTHLY_BY_DATE = 3;
  19      static $SCHEDULED_ANNUALLY = 4;
  20      static $SCHEDULED_ON_SPECIFIC_DATE = 5;
  21  
  22  	public static function getInstance(){
  23          return new self();
  24      }
  25  
  26      /**

  27       * Function returns the Scheduled Reports Model instance

  28       * @param <Number> $recordId

  29       * @return <Reports_ScehduleReports_Model>

  30       */
  31  	public static function getInstanceById($recordId) {
  32          $db = PearDatabase::getInstance();
  33          $scheduledReportModel = new self();
  34  
  35          if (!empty($recordId)) {
  36              $scheduledReportResult = $db->pquery('SELECT * FROM vtiger_schedulereports WHERE reportid = ?', array($recordId));
  37              if ($db->num_rows($scheduledReportResult) > 0) {
  38                  $reportScheduleInfo = $db->query_result_rowdata($scheduledReportResult, 0);
  39                  $reportScheduleInfo['schdate'] = decode_html($reportScheduleInfo['schdate']);
  40                  $reportScheduleInfo['schdayoftheweek'] = decode_html($reportScheduleInfo['schdayoftheweek']);
  41                  $reportScheduleInfo['schdayofthemonth'] = decode_html($reportScheduleInfo['schdayofthemonth']);
  42                  $reportScheduleInfo['schannualdates'] = decode_html($reportScheduleInfo['schannualdates']);
  43                  $reportScheduleInfo['recipients'] = decode_html($reportScheduleInfo['recipients']);
  44                  $reportScheduleInfo['specificemails'] = decode_html($reportScheduleInfo['specificemails']);
  45                  $scheduledReportModel->setData($reportScheduleInfo);
  46              }
  47          }
  48          return $scheduledReportModel;
  49      }
  50  
  51      /**

  52       * Function to save the  Scheduled Reports data

  53       */
  54  	public function saveScheduleReport() {
  55          $adb = PearDatabase::getInstance();
  56  
  57          $reportid = $this->get('reportid');
  58          $scheduleid = $this->get('scheduleid');
  59          $schtime = $this->get('schtime');
  60          if(!preg_match('/^[0-2]\d(:[0-5]\d){1,2}$/', $schtime) or substr($schtime,0,2)>23) {  // invalid time format
  61              $schtime='00:00';
  62          }
  63          $schtime .=':00';
  64  
  65          $schdate = null; $schdayoftheweek = null; $schdayofthemonth = null; $schannualdates = null;
  66          if ($scheduleid == self::$SCHEDULED_ON_SPECIFIC_DATE) {
  67              $date = $this->get('schdate');
  68              $dateDBFormat = DateTimeField::convertToDBFormat($date);
  69              $nextTriggerTime = $dateDBFormat.' '.$schtime;
  70              $currentTime = Vtiger_Util_Helper::getActiveAdminCurrentDateTime();
  71              if($nextTriggerTime > $currentTime) {
  72                  $this->set('next_trigger_time', $nextTriggerTime);
  73              } else {
  74                  $this->set('next_trigger_time', date('Y-m-d H:i:s', strtotime('+10 year')));
  75              }
  76              $schdate = Zend_Json::encode(array($dateDBFormat));
  77          } else if ($scheduleid == self::$SCHEDULED_WEEKLY) {
  78              $schdayoftheweek = Zend_Json::encode($this->get('schdayoftheweek'));
  79              $this->set('schdayoftheweek', $schdayoftheweek);
  80          } else if ($scheduleid == self::$SCHEDULED_MONTHLY_BY_DATE) {
  81              $schdayofthemonth = Zend_Json::encode($this->get('schdayofthemonth'));
  82              $this->set('schdayofthemonth', $schdayofthemonth);
  83          } else if ($scheduleid == self::$SCHEDULED_ANNUALLY) {
  84              $schannualdates = Zend_Json::encode($this->get('schannualdates'));
  85              $this->set('schannualdates', $schannualdates);
  86          }
  87  
  88          $recipients = Zend_Json::encode($this->get('recipients'));
  89          $specificemails = Zend_Json::encode($this->get('specificemails'));
  90          $isReportScheduled = $this->get('isReportScheduled');
  91  
  92          if($scheduleid != self::$SCHEDULED_ON_SPECIFIC_DATE) {
  93              $nextTriggerTime = $this->getNextTriggerTime();
  94          }
  95          if ($isReportScheduled == '0' || $isReportScheduled == '' || $isReportScheduled == false) {
  96              $deleteScheduledReportSql = "DELETE FROM vtiger_schedulereports WHERE reportid=?";
  97              $adb->pquery($deleteScheduledReportSql, array($reportid));
  98          } else {
  99              $checkScheduledResult = $adb->pquery('SELECT 1 FROM vtiger_schedulereports WHERE reportid=?', array($reportid));
 100              if ($adb->num_rows($checkScheduledResult) > 0) {
 101                  $scheduledReportSql = 'UPDATE vtiger_schedulereports SET scheduleid=?, recipients=?, schdate=?, schtime=?, schdayoftheweek=?, schdayofthemonth=?, schannualdates=?, specificemails=?, next_trigger_time=? WHERE reportid=?';
 102                  $adb->pquery($scheduledReportSql, array($scheduleid, $recipients, $schdate, $schtime, $schdayoftheweek, $schdayofthemonth, $schannualdates, $specificemails, $nextTriggerTime, $reportid));
 103              } else {
 104                  $scheduleReportSql = 'INSERT INTO vtiger_schedulereports (reportid,scheduleid,recipients,schdate,schtime,schdayoftheweek,schdayofthemonth,schannualdates,next_trigger_time,specificemails) VALUES (?,?,?,?,?,?,?,?,?,?)';
 105                  $adb->pquery($scheduleReportSql, array($reportid, $scheduleid, $recipients, $schdate, $schtime, $schdayoftheweek, $schdayofthemonth, $schannualdates, $nextTriggerTime, $specificemails));
 106              }
 107          }
 108      }
 109  
 110  	public function getRecipientEmails() {
 111          $recipientsInfo = $this->get('recipients');
 112  
 113          if (!empty($recipientsInfo)) {
 114              $recipients = array();
 115              $recipientsInfo = Zend_Json::decode($recipientsInfo);
 116              foreach ($recipientsInfo as $key => $recipient) {
 117                  if (strpos($recipient,'USER') !== false) {
 118                      $id = explode('::', $recipient);
 119                      $recipients['Users'][] = $id[1];
 120                  }else if (strpos($recipient,'GROUP') !== false) {
 121                      $id = explode('::', $recipient);
 122                      $recipients['Groups'][] = $id[1];
 123                  }else if (strpos($recipient,'ROLE') !== false) {
 124                      $id = explode('::', $recipient);
 125                      $recipients['Roles'][] = $id[1];
 126                  }
 127              }
 128          }
 129          $recipientsList = array();
 130          if (!empty($recipients)) {
 131              if (!empty($recipients['Users'])) {
 132                  $recipientsList = array_merge($recipientsList, $recipients['Users']);
 133              }
 134  
 135              if (!empty($recipients['Roles'])) {
 136                  foreach ($recipients['Roles'] as $roleId) {
 137                      $roleUsers = getRoleUsers($roleId);
 138                      foreach ($roleUsers as $userId => $userName) {
 139                          array_push($recipientsList, $userId);
 140                      }
 141                  }
 142              }
 143  
 144              if (!empty($recipients['Groups'])) {
 145                  require_once  'include/utils/GetGroupUsers.php';
 146                  foreach ($recipients['Groups'] as $groupId) {
 147                      $userGroups = new GetGroupUsers();
 148                      $userGroups->getAllUsersInGroup($groupId);
 149                      $recipientsList = array_merge($recipientsList, $userGroups->group_users);
 150                  }
 151              }
 152          }
 153          $recipientsList = array_unique($recipientsList);
 154          $recipientsEmails = array();
 155          if (!empty($recipientsList) && count($recipientsList) > 0) {
 156              foreach ($recipientsList as $userId) {
 157                  if(!Vtiger_Util_Helper::isUserDeleted($userId)) {
 158                  $userName = getUserFullName($userId);
 159                  $userEmail = getUserEmail($userId);
 160                  if (!in_array($userEmail, $recipientsEmails)) {
 161                      $recipientsEmails[$userName] = $userEmail;
 162                  }
 163              }
 164          }
 165          }
 166          //Added for specific email address.

 167          $specificemails = explode(',', Zend_Json::decode($this->get('specificemails')));
 168          if (!empty($specificemails)) {
 169              $recipientsEmails = array_merge($recipientsEmails, $specificemails);
 170          }
 171  
 172          return $recipientsEmails;
 173      }
 174  
 175  	public function sendEmail() {
 176          require_once  'vtlib/Vtiger/Mailer.php';
 177  
 178          $vtigerMailer = new Vtiger_Mailer();
 179  
 180          $recipientEmails = $this->getRecipientEmails();
 181          Vtiger_Utils::ModuleLog('ScheduleReprots', $recipientEmails);
 182          foreach ($recipientEmails as $name => $email) {
 183              $vtigerMailer->AddAddress($email, $name);
 184          }
 185          vimport('~modules/Report/models/Record.php');
 186          $reportRecordModel = Reports_Record_Model::getInstanceById($this->get('reportid'));
 187          $currentTime = date('Y-m-d H:i:s');
 188          Vtiger_Utils::ModuleLog('ScheduleReprots Send Mail Start ::', $currentTime);
 189          $reportname = decode_html($reportRecordModel->getName());
 190          $subject = $reportname;
 191          Vtiger_Utils::ModuleLog('ScheduleReprot Name ::', $reportname);
 192          $vtigerMailer->Subject = $subject;
 193          $vtigerMailer->Body = $this->getEmailContent($reportRecordModel);
 194          $vtigerMailer->IsHTML();
 195  
 196          $baseFileName = $reportname . '_' . $currentTime;
 197  
 198          $oReportRun = ReportRun::getInstance($this->get('reportid'));
 199          $reportFormat = $this->scheduledFormat;
 200          $attachments = array();
 201  
 202          if ($reportFormat == 'CSV') {
 203              $fileName = $baseFileName . '.csv';
 204              $filePath = 'storage/' . $fileName;
 205              $attachments[$fileName] = $filePath;
 206              $oReportRun->writeReportToCSVFile($filePath);
 207          }
 208  
 209          foreach ($attachments as $attachmentName => $path) {
 210              $vtigerMailer->AddAttachment($path, decode_html($attachmentName));
 211          }
 212          //Added cc to account owner

 213          $accountOwnerId = Users::getActiveAdminId();
 214          $vtigerMailer->AddCC(getUserEmail($accountOwnerId), getUserFullName($accountOwnerId));
 215          $status = $vtigerMailer->Send(true);
 216  
 217          foreach ($attachments as $attachmentName => $path) {
 218              unlink($path);
 219          }
 220          return $status;
 221      }
 222  
 223      /**

 224       * Function gets the next trigger for the workflows

 225       * @global <String> $default_timezone

 226       * @return type

 227       */
 228  	function getNextTriggerTime() {
 229          require_once  'modules/com_vtiger_workflow/VTWorkflowManager.inc';
 230          $default_timezone = vglobal('default_timezine');
 231          $admin = Users::getActiveAdminUser();
 232          $adminTimeZone = $admin->time_zone;
 233          @date_default_timezone_set($adminTimeZone);
 234  
 235          $scheduleType = $this->get('scheduleid');
 236          $nextTime = null;
 237  
 238          $workflow = new Workflow();
 239          if ($scheduleType == self::$SCHEDULED_DAILY) {
 240              $nextTime = $workflow->getNextTriggerTimeForDaily($this->get('schtime'));
 241          }
 242          if ($scheduleType == self::$SCHEDULED_WEEKLY) {
 243              $nextTime = $workflow->getNextTriggerTimeForWeekly($this->get('schdayoftheweek'), $this->get('schtime'));
 244          }
 245  
 246          if ($scheduleType == self::$SCHEDULED_ON_SPECIFIC_DATE) {
 247              $nextTime = date('Y-m-d H:i:s', strtotime('+10 year'));
 248          }
 249  
 250          if ($scheduleType == self::$SCHEDULED_MONTHLY_BY_DATE) {
 251              $nextTime = $workflow->getNextTriggerTimeForMonthlyByDate($this->get('schdayofthemonth'), $this->get('schtime'));
 252          }
 253  
 254          if ($scheduleType == self::$SCHEDULED_ANNUALLY) {
 255              $nextTime = $workflow->getNextTriggerTimeForAnnualDates($this->get('schannualdates'), $this->get('schtime'));
 256          }
 257          @date_default_timezone_set($default_timezone);
 258          return $nextTime;
 259      }
 260  
 261  	public function updateNextTriggerTime() {
 262          $adb = PearDatabase::getInstance();
 263          $nextTriggerTime = $this->getNextTriggerTime();
 264          Vtiger_Utils::ModuleLog('ScheduleReprot Next Trigger Time >> ', $nextTriggerTime);
 265          $adb->pquery('UPDATE vtiger_schedulereports SET next_trigger_time=? WHERE reportid=?', array($nextTriggerTime, $this->get('reportid')));
 266          Vtiger_Utils::ModuleLog('ScheduleReprot', 'Next Trigger Time updated');
 267      }
 268  
 269  	public static function getScheduledReports() {
 270          $adb = PearDatabase::getInstance();
 271          $default_timezone = vglobal('default_timezone');
 272  
 273          // set the time zone to the admin's time zone, this is needed so that the scheduled reprots will be triggered

 274          // at admin's time zone rather than the systems time zone. This is specially needed for Hourly and Daily scheduled reports

 275          $admin = Users::getActiveAdminUser();
 276          $adminTimeZone = $admin->time_zone;
 277          @date_default_timezone_set($adminTimeZone);
 278          $currentTimestamp  = date("Y-m-d H:i:s");
 279          @date_default_timezone_set($default_timezone);
 280          $result = $adb->pquery("SELECT reportid FROM vtiger_schedulereports WHERE next_trigger_time = '' || next_trigger_time <= ?", array($currentTimestamp));
 281  
 282          $scheduledReports = array();
 283          $noOfScheduledReports = $adb->num_rows($result);
 284          for ($i = 0; $i < $noOfScheduledReports; ++$i) {
 285              $recordId = $adb->query_result($result, $i, 'reportid');
 286              $scheduledReports[] = self::getInstanceById($recordId);
 287          }
 288          return $scheduledReports;
 289      }
 290  
 291  	public static function runScheduledReports() {
 292          vimport('~~modules/com_vtiger_workflow/VTWorkflowUtils.php');
 293          $util = new VTWorkflowUtils();
 294          $util->adminUser();
 295  
 296          global $currentModule, $current_language;
 297          if(empty($currentModule)) $currentModule = 'Reports';
 298          if(empty($current_language)) $current_language = 'en_us';
 299  
 300          $scheduledReports = self::getScheduledReports();
 301          foreach ($scheduledReports as $scheduledReport) {
 302              $status = $scheduledReport->sendEmail();
 303              Vtiger_Utils::ModuleLog('ScheduleReprot Send Mail Status ', $status);
 304              if($status)
 305                  $scheduledReport->updateNextTriggerTime();
 306          }
 307          $util->revertUser();
 308          return $status;
 309      }
 310  
 311  	function getEmailContent($reportRecordModel){
 312          $site_URL = vglobal('site_URL');
 313          $currentModule = vglobal('currentModule');
 314          $companydetails = getCompanyDetails();
 315          $logo = $site_URL.'/test/logo/'.$companydetails['logoname'];
 316  
 317          $body = '<table width="700" cellspacing="0" cellpadding="0" border="0" align="center" style="font-family: Arial,Helvetica,sans-serif; font-size: 12px; font-weight: normal; text-decoration: none; ">
 318              <tr>
 319                  <td> </td>
 320              </tr>
 321              <tr>
 322                  <td>
 323                  <table width="100%" cellspacing="0" cellpadding="0" border="0">
 324                          <tr>
 325                              <td>
 326                              <table width="100%" cellspacing="0" cellpadding="0" border="0">
 327                                      <tr>
 328                                          <td rowspan="4" ><img height="30" src='.$logo.'></td>
 329                                      </tr>
 330                              </table>
 331                              </td>
 332                          </tr>
 333                          <tr>
 334                              <td>
 335                              <table width="100%" cellspacing="0" cellpadding="0" border="0" style="font-family: Arial,Helvetica,sans-serif; font-size: 12px; font-weight: normal; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255);">
 336                                      <tr>
 337                                          <td valign="top">
 338                                          <table width="100%" cellspacing="0" cellpadding="5" border="0">
 339                                                  <tr>
 340                                                      <td align="right" style="font-family: Arial,Helvetica,sans-serif; font-size: 12px; font-weight: bolder; text-decoration: none; color: rgb(66, 66, 253);"> </td>
 341                                                  </tr>
 342                                                  <tr>
 343                                                      <td> </td>
 344                                                  </tr>
 345                                                  <tr>
 346                                                      <td style="font-family: Arial,Helvetica,sans-serif; font-size: 12px; color: rgb(0, 0, 0); font-weight: normal; text-align: justify; line-height: 20px;"> '.  vtranslate('LBL_AUTO_GENERATED_REPORT_EMAIL', $currentModule).'</td>
 347                                                  </tr>
 348                                                  <tr>
 349                                                      <td align="center">
 350                                                      <table width="75%" cellspacing="0" cellpadding="10" border="0" style="border: 2px solid rgb(180, 180, 179); background-color: rgb(226, 226, 225); font-family: Arial,Helvetica,sans-serif; font-size: 12px; color: rgb(0, 0, 0); font-weight: normal;">
 351                                                              <tr>
 352                                                                  <td><b>' . vtranslate('LBL_REPORT_NAME', $currentModule) . ' </b> : <font color="#990000"><strong> <a href=' .$site_URL.'/'. $reportRecordModel->getDetailViewUrl() . '>' . $reportRecordModel->getName() . '</a></strong></font> </td>
 353                                                              </tr>
 354                                                              <tr>
 355                                                                  <td><b>' . vtranslate('LBL_DESCRIPTION', $currentModule) . ' :</b> <font color="#990000"><strong>' . $reportRecordModel->get('description') . '</strong></font> </td>
 356                                                              </tr>
 357                                                      </table>
 358                                                      </td>
 359                                                  </tr>
 360                                          </table>
 361                                          </td>
 362                                          <td width="1%" valign="top"> </td>
 363                                      </tr>
 364                              </table>
 365                              </td>
 366                          </tr>
 367                  </table>
 368                  </td>
 369              </tr>
 370              <tr>
 371                  <td> </td>
 372              </tr>
 373              <tr>
 374                  <td> </td>
 375              </tr>
 376              <tr>
 377                  <td> </td>
 378              </tr>
 379      </table>';
 380  
 381      return $body;
 382      }
 383  }
 384