vtigerossez/modules/Reports/ScheduledReports.php

380 lines
14 KiB
PHP

<?php
/*+***********************************************************************************
* The contents of this file are subject to the vtiger CRM Public License Version 1.0
* ("License"); You may not use this file except in compliance with the License
* The Original Code is: vtiger CRM Open Source
* The Initial Developer of the Original Code is vtiger.
* Portions created by vtiger are Copyright (C) vtiger.
* All Rights Reserved.
*************************************************************************************/
require_once 'modules/Reports/Reports.php';
require_once 'modules/Reports/ReportRun.php';
require_once 'include/Zend/Json.php';
class VTScheduledReport extends Reports {
var $db;
var $user;
var $isScheduled = false;
var $scheduledInterval = null;
var $scheduledFormat = null;
var $scheduledRecipients = null;
static $SCHEDULED_HOURLY = 1;
static $SCHEDULED_DAILY = 2;
static $SCHEDULED_WEEKLY = 3;
static $SCHEDULED_BIWEEKLY = 4;
static $SCHEDULED_MONTHLY = 5;
static $SCHEDULED_ANNUALLY = 6;
public function __construct($adb, $user, $reportid="") {
$this->db = $adb;
$this->user = $user;
$this->id = $reportid;
parent::__construct($reportid);
}
public function getReportScheduleInfo() {
global $adb;
if(!empty($this->id)) {
$cachedInfo = VTCacheUtils::lookupReport_ScheduledInfo($this->user->id, $this->id);
if($cachedInfo == false) {
$result = $adb->pquery('SELECT * FROM vtiger_scheduled_reports WHERE reportid=?', array($this->id));
if($adb->num_rows($result) > 0) {
$reportScheduleInfo = $adb->raw_query_result_rowdata($result, 0);
$scheduledInterval = (!empty($reportScheduleInfo['schedule']))?Zend_Json::decode($reportScheduleInfo['schedule']):array();
$scheduledRecipients = (!empty($reportScheduleInfo['recipients']))?Zend_Json::decode($reportScheduleInfo['recipients']):array();
VTCacheUtils::updateReport_ScheduledInfo($this->user->id, $this->id, true, $reportScheduleInfo['format'],
$scheduledInterval, $scheduledRecipients, $reportScheduleInfo['next_trigger_time']);
$cachedInfo = VTCacheUtils::lookupReport_ScheduledInfo($this->user->id, $this->id);
}
}
if($cachedInfo) {
$this->isScheduled = $cachedInfo['isScheduled'];
$this->scheduledFormat = $cachedInfo['scheduledFormat'];
$this->scheduledInterval = $cachedInfo['scheduledInterval'];
$this->scheduledRecipients = $cachedInfo['scheduledRecipients'];
$this->scheduledTime = $cachedInfo['scheduledTime'];
return true;
}
}
return false;
}
public function getRecipientEmails() {
$recipientsInfo = $this->scheduledRecipients;
$recipientsList = array();
if(!empty($recipientsInfo)) {
if(!empty($recipientsInfo['users'])) {
$recipientsList = array_merge($recipientsList, $recipientsInfo['users']);
}
if(!empty($recipientsInfo['roles'])) {
foreach($recipientsInfo['roles'] as $roleId) {
$roleUsers = getRoleUsers($roleId);
foreach($roleUsers as $userId => $userName) {
array_push($recipientsList, $userId);
}
}
}
if(!empty($recipientsInfo['rs'])) {
foreach($recipientsInfo['rs'] as $roleId) {
$users = getRoleAndSubordinateUsers($roleId);
foreach($users as $userId => $userName) {
array_push($recipientsList, $userId);
}
}
}
if(!empty($recipientsInfo['groups'])) {
require_once 'include/utils/GetGroupUsers.php';
foreach($recipientsInfo['groups'] as $groupId) {
$userGroups = new GetGroupUsers();
$userGroups->getAllUsersInGroup($groupId);
$recipientsList = array_merge($recipientsList, $userGroups->group_users);
}
}
}
$recipientsEmails = array();
if(!empty($recipientsList) && count($recipientsList) > 0) {
foreach($recipientsList as $userId) {
$userName = getUserFullName($userId);
$userEmail = getUserEmail($userId);
if(!in_array($userEmail, $recipientsEmails)) {
$recipientsEmails[$userName] = $userEmail;
}
}
}
return $recipientsEmails;
}
public function sendEmail() {
global $currentModule;
require_once 'vtlib/Vtiger/Mailer.php';
$vtigerMailer = new Vtiger_Mailer();
$recipientEmails = $this->getRecipientEmails();
foreach($recipientEmails as $name => $email) {
$vtigerMailer->AddAddress($email, $name);
}
$currentTime = date('Y-m-d H:i:s');
$subject = $this->reportname .' - '. $currentTime .' ('. DateTimeField::getDBTimeZone() .')';
$contents = getTranslatedString('LBL_AUTO_GENERATED_REPORT_EMAIL', $currentModule) .'<br/><br/>';
$contents .= '<b>'.getTranslatedString('LBL_REPORT_NAME', $currentModule) .' :</b> '. $this->reportname .'<br/>';
$contents .= '<b>'.getTranslatedString('LBL_DESCRIPTION', $currentModule) .' :</b><br/>'. $this->reportdescription .'<br/><br/>';
$vtigerMailer->Subject = $subject;
$vtigerMailer->Body = $contents;
$vtigerMailer->ContentType = "text/html";
$baseFileName = preg_replace('/[^a-zA-Z0-9_-\s]/', '', $this->reportname).'_'. preg_replace('/[^a-zA-Z0-9_-\s]/', '', $currentTime);
$oReportRun = new ReportRun($this->id);
$reportFormat = $this->scheduledFormat;
$attachments = array();
if($reportFormat == 'pdf' || $reportFormat == 'both') {
$fileName = $baseFileName.'.pdf';
$filePath = 'storage/'.$fileName;
$attachments[$fileName] = $filePath;
$pdf = $oReportRun->getReportPDF();
$pdf->Output($filePath,'F');
}
if ($reportFormat == 'excel' || $reportFormat == 'both') {
$fileName = $baseFileName.'.xls';
$filePath = 'storage/'.$fileName;
$attachments[$fileName] = $filePath;
$oReportRun->writeReportToExcelFile($filePath);
}
foreach($attachments as $attachmentName => $path) {
$vtigerMailer->AddAttachment($path, $attachmentName);
}
$vtigerMailer->Send(true);
foreach($attachments as $attachmentName => $path) {
unlink($path);
}
}
public function getNextTriggerTime() {
$scheduleInfo = $this->scheduledInterval;
$scheduleType = $scheduleInfo['scheduletype'];
$scheduledMonth = $scheduleInfo['month'];
$scheduledDayOfMonth= $scheduleInfo['date'];
$scheduledDayOfWeek = $scheduleInfo['day'];
$scheduledTime = $scheduleInfo['time'];
if(empty($scheduledTime)) {
$scheduledTime = '10:00';
} elseif(stripos(':', $scheduledTime) === false) {
$scheduledTime = $scheduledTime .':00';
}
if($scheduleType == VTScheduledReport::$SCHEDULED_HOURLY) {
return date("Y-m-d H:i:s",strtotime("+1 hour"));
}
if($scheduleType == VTScheduledReport::$SCHEDULED_DAILY) {
return date("Y-m-d H:i:s",strtotime("+ 1 day ".$scheduledTime));
}
if($scheduleType == VTScheduledReport::$SCHEDULED_WEEKLY) {
$weekDays = array('0'=>'Sunday','1'=>'Monday','2'=>'Tuesday','3'=>'Wednesday','4'=>'Thursday','5'=>'Friday','6'=>'Saturday');
if(date('w',time()) == $scheduledDayOfWeek) {
return date("Y-m-d H:i:s",strtotime('+1 week '.$scheduledTime));
} else {
return date("Y-m-d H:i:s",strtotime($weekDays[$scheduledDayOfWeek].' '.$scheduledTime));
}
}
if($scheduleType == VTScheduledReport::$SCHEDULED_BIWEEKLY) {
$weekDays = array('0'=>'Sunday','1'=>'Monday','2'=>'Tuesday','3'=>'Wednesday','4'=>'Thursday','5'=>'Friday','6'=>'Saturday');
if(date('w',time()) == $scheduledDayOfWeek) {
return date("Y-m-d H:i:s",strtotime('+2 weeks '.$scheduledTime));
} else {
return date("Y-m-d H:i:s",strtotime($weekDays[$scheduledDayOfWeek].' '.$scheduledTime));
}
}
if($scheduleType == VTScheduledReport::$SCHEDULED_MONTHLY) {
$currentTime = time();
$currentDayOfMonth = date('j',$currentTime);
if($scheduledDayOfMonth == $currentDayOfMonth) {
return date("Y-m-d H:i:s",strtotime('+1 month '.$scheduledTime));
} else {
$monthInFullText = date('F',$currentTime);
$yearFullNumberic = date('Y',$currentTime);
if($scheduledDayOfMonth < $currentDayOfMonth) {
$nextMonth = date("Y-m-d H:i:s",strtotime('next month'));
$monthInFullText = date('F',strtotime($nextMonth));
}
return date("Y-m-d H:i:s",strtotime($scheduledDayOfMonth.' '.$monthInFullText.' '.$yearFullNumberic.' '.$scheduledTime));
}
}
if($scheduleType == VTScheduledReport::$SCHEDULED_ANNUALLY) {
$months = array(0=>'January',1=>'February',2=>'March',3=>'April',4=>'May',5=>'June',6=>'July',
7=>'August',8=>'September',9=>'October',10=>'November',11=>'December');
$currentTime = time();
$currentMonth = date('n',$currentTime);
if(($scheduledMonth+1) == $currentMonth) {
return date("Y-m-d H:i:s",strtotime('+1 year '.$scheduledTime));
} else {
$monthInFullText = $months[$scheduledMonth];
$yearFullNumberic = date('Y',$currentTime);
if(($scheduledMonth+1) < $currentMonth) {
$nextMonth = date("Y-m-d H:i:s",strtotime('next year'));
$yearFullNumberic = date('Y',strtotime($nextMonth));
}
return date("Y-m-d H:i:s",strtotime($scheduledDayOfMonth.' '.$monthInFullText.' '.$yearFullNumberic.' '.$scheduledTime));
}
}
}
public function updateNextTriggerTime() {
$adb = $this->db;
$currentTime = date('Y-m-d H:i:s');
$scheduledInterval = $this->scheduledInterval;
$nextTriggerTime = $this->getNextTriggerTime(); // Compute based on the frequency set
$adb->pquery('UPDATE vtiger_scheduled_reports SET next_trigger_time=? WHERE reportid=?', array($nextTriggerTime, $this->id));
}
public static function generateRecipientOption($type, $value, $name='') {
switch($type) {
case "users" : if(empty($name)) $name = getUserFullName($value);
$optionName = 'User::'.addslashes(decode_html($name));
$optionValue = 'users::'.$value;
break;
case "groups" : if(empty($name)) {
$groupInfo = getGroupName($value);
$name = $groupInfo[0];
}
$optionName = 'Group::'.addslashes(decode_html($name));
$optionValue = 'groups::'.$value;
break;
case "roles" : if(empty($name)) $name = getRoleName ($value);
$optionName = 'Roles::'.addslashes(decode_html($name));
$optionValue = 'roles::'.$value;
break;
case "rs" : if(empty($name)) $name = getRoleName ($value);
$optionName = 'RoleAndSubordinates::'.addslashes(decode_html($name));
$optionValue = 'rs::'.$value;
break;
}
return '<option value="'.$optionValue.'">'.$optionName.'</option>';
}
public function getSelectedRecipientsHTML() {
$selectedRecipientsHTML = '';
if(!empty($this->scheduledRecipients)) {
foreach($this->scheduledRecipients as $recipientType => $recipients) {
foreach($recipients as $recipientId) {
$selectedRecipientsHTML .= VTScheduledReport::generateRecipientOption($recipientType, $recipientId);
}
}
}
return $selectedRecipientsHTML;
}
public static function getAvailableUsersHTML() {
$userDetails = getAllUserName();
$usersHTML = '<select id="availableRecipients" name="availableRecipients" multiple size="10" class="small crmFormList">';
foreach($userDetails as $userId=>$userName) {
$usersHTML .= VTScheduledReport::generateRecipientOption('users', $userId, $userName);
}
$usersHTML .= '</select>';
return $usersHTML;
}
public static function getAvailableGroupsHTML() {
$grpDetails = getAllGroupName();
$groupsHTML = '<select id="availableRecipients" name="availableRecipients" multiple size="10" class="small crmFormList">';
foreach($grpDetails as $groupId=>$groupName) {
$groupsHTML .= VTScheduledReport::generateRecipientOption('groups', $groupId, $groupName);
}
$groupsHTML .= '</select>';
return $groupsHTML;
}
public static function getAvailableRolesHTML() {
$roleDetails = getAllRoleDetails();
$rolesHTML = '<select id="availableRecipients" name="availableRecipients" multiple size="10" class="small crmFormList">';
foreach($roleDetails as $roleId=>$roleInfo) {
$rolesHTML .= VTScheduledReport::generateRecipientOption('roles', $roleId, $roleInfo[0]);
}
$rolesHTML .= '</select>';
return $rolesHTML;
}
public static function getAvailableRolesAndSubordinatesHTML() {
$roleDetails = getAllRoleDetails();
$rolesAndSubHTML = '<select id="availableRecipients" name="availableRecipients" multiple size="10" class="small crmFormList">';
foreach($roleDetails as $roleId=>$roleInfo) {
$rolesAndSubHTML .= VTScheduledReport::generateRecipientOption('rs', $roleId, $roleInfo[0]);
}
$rolesAndSubHTML .= '</select>';
return $rolesAndSubHTML;
}
public static function getScheduledReports($adb, $user) {
$currentTime = date('Y-m-d H:i:s');
$result = $adb->pquery("SELECT * FROM vtiger_scheduled_reports
WHERE next_trigger_time = '' || next_trigger_time <= ?", array($currentTime));
$scheduledReports = array();
$noOfScheduledReports = $adb->num_rows($result);
for($i=0; $i<$noOfScheduledReports; ++$i) {
$reportScheduleInfo = $adb->raw_query_result_rowdata($result, $i);
$scheduledInterval = (!empty($reportScheduleInfo['schedule']))?Zend_Json::decode($reportScheduleInfo['schedule']):array();
$scheduledRecipients = (!empty($reportScheduleInfo['recipients']))?Zend_Json::decode($reportScheduleInfo['recipients']):array();
$vtScheduledReport = new VTScheduledReport($adb, $user, $reportScheduleInfo['reportid']);
$vtScheduledReport->isScheduled = true;
$vtScheduledReport->scheduledFormat = $reportScheduleInfo['format'];
$vtScheduledReport->scheduledInterval = $scheduledInterval;
$vtScheduledReport->scheduledRecipients = $scheduledRecipients;
$vtScheduledReport->scheduledTime = $reportScheduleInfo['next_trigger_time'];
$scheduledReports[] = $vtScheduledReport;
}
return $scheduledReports;
}
public static function runScheduledReports($adb) {
require_once 'modules/com_vtiger_workflow/VTWorkflowUtils.php';
$util = new VTWorkflowUtils();
$adminUser = $util->adminUser();
global $currentModule, $current_language;
if(empty($currentModule)) $currentModule = 'Reports';
if(empty($current_language)) $current_language = 'en_us';
$scheduledReports = self::getScheduledReports($adb, $adminUser);
foreach($scheduledReports as $scheduledReport) {
$scheduledReport->sendEmail();
$scheduledReport->updateNextTriggerTime();
}
$util->revertUser();
}
}
?>