380 lines
14 KiB
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();
|
|
}
|
|
|
|
}
|
|
|
|
?>
|