public function process(Vtiger_Request $request)
{
$mode = $request->getMode();
$viewer = $this->getViewer($request);
$moduleName = $request->getModule();
$record = $request->get('record');
$reportModel = Reports_Record_Model::getInstanceById($record);
$reportModel->setModule('Reports');
$reportModel->set('advancedFilter', $request->get('advanced_filter'));
$page = $request->get('page');
$pagingModel = new Vtiger_Paging_Model();
$pagingModel->set('page', $page);
$pagingModel->set('limit', Reports_Detail_View::REPORT_LIMIT);
if ($mode === 'save') {
$reportModel->saveAdvancedFilters();
$reportData = $reportModel->getReportData($pagingModel);
$data = $reportData['data'];
} else {
if ($mode === 'generate') {
$reportData = $reportModel->generateData($pagingModel);
$data = $reportData['data'];
}
}
$calculation = $reportModel->generateCalculationData();
$viewer->assign('PRIMARY_MODULE', $reportModel->getPrimaryModule());
$viewer->assign('CALCULATION_FIELDS', $calculation);
$viewer->assign('DATA', $data);
$viewer->assign('RECORD_ID', $record);
$viewer->assign('PAGING_MODEL', $pagingModel);
$viewer->assign('MODULE', $moduleName);
$viewer->assign('NEW_COUNT', $reportData['count']);
$viewer->assign('REPORT_RUN_INSTANCE', ReportRun::getInstance($record));
$viewer->view('ReportContents.tpl', $moduleName);
}
public static function getReportChart($reportid, $chartType)
{
global $log, $adb;
$oReportRun = ReportRun::getInstance($reportid);
$groupBy = $oReportRun->getGroupingList($reportid);
foreach ($groupBy as $key => $value) {
// $groupByConditon = explode(" ",$value);
//$groupByNew = explode("'",$groupByConditon[0]);
list($tablename, $colname, $module_field, $fieldname, $single) = split(":", $key);
list($module, $field) = split("_", $module_field);
$fieldDetails = $key;
break;
}
$queryReports = self::getCustomReportsQuery($reportid);
$queryResult = $adb->pquery($queryReports, array());
//ChartUtils::generateChartDataFromReports($queryResult, strtolower($groupByNew[1]));
if ($chartType == 'horizontalbarchart') {
$Chart = ChartUtils::getReportBarChart($queryResult, strtolower($module_field), $fieldDetails, $reportid);
} else {
if ($chartType == 'verticalbarchart') {
$Chart = ChartUtils::getReportBarChart($queryResult, strtolower($module_field), $fieldDetails, $reportid, 'vertical');
} else {
if ($chartType == 'piechart') {
$Chart = ChartUtils::getReportPieChart($queryResult, strtolower($module_field), $fieldDetails, $reportid);
}
}
}
return $Chart;
}
public function process(Vtiger_Request $request)
{
$mode = $request->getMode();
$viewer = $this->getViewer($request);
$moduleName = $request->getModule();
$record = $request->get('record');
$reportModel = Reports_Record_Model::getInstanceById($record);
$reportModel->setModule('Reports');
$reportModel->set('advancedFilter', $request->get('advanced_filter'));
$page = $request->get('page');
$pagingModel = new Vtiger_Paging_Model();
$pagingModel->set('page', $page);
$pagingModel->set('limit', Reports_Detail_View::REPORT_LIMIT);
if ($mode === 'save') {
$reportModel->saveAdvancedFilters();
$reportData = $reportModel->getReportData($pagingModel);
$data = $reportData['data'];
} else {
if ($mode === 'generate') {
$reportData = $reportModel->generateData($pagingModel);
$data = $reportData['data'];
}
}
$calculation = $reportModel->generateCalculationData();
/*убираем ненужные столбцы*/
$columns = array('SUM' => false, 'AVG' => false, 'MIN' => false, 'MAX' => false);
foreach ($calculation as $metriks) {
foreach ($metriks as $index => $metrik) {
$postfix = substr($index, -3, 3);
if ($columns[$postfix]) {
continue;
}
if (!empty($metrik)) {
$columns[$postfix] = true;
}
}
}
$viewer->assign('COLUMNS', $columns);
/*end*/
$viewer->assign('PRIMARY_MODULE', $reportModel->getPrimaryModule());
$viewer->assign('CALCULATION_FIELDS', $calculation);
$viewer->assign('DATA', $data);
$viewer->assign('RECORD_ID', $record);
$viewer->assign('PAGING_MODEL', $pagingModel);
$viewer->assign('MODULE', $moduleName);
$viewer->assign('NEW_COUNT', $reportData['count']);
$viewer->assign('REPORT_RUN_INSTANCE', ReportRun::getInstance($record));
$viewer->view('ReportContents.tpl', $moduleName);
}
public function sendEmail()
{
require_once 'vtlib/Vtiger/Mailer.php';
$vtigerMailer = new Vtiger_Mailer();
$recipientEmails = $this->getRecipientEmails();
Vtiger_Utils::ModuleLog('ScheduleReprots', $recipientEmails);
foreach ($recipientEmails as $name => $email) {
$vtigerMailer->AddAddress($email, $name);
}
vimport('~modules/Report/models/Record.php');
$reportRecordModel = Reports_Record_Model::getInstanceById($this->get('reportid'));
$currentTime = date('Y-m-d.H.i.s');
Vtiger_Utils::ModuleLog('ScheduleReprots Send Mail Start ::', $currentTime);
$reportname = decode_html($reportRecordModel->getName());
$subject = $reportname;
Vtiger_Utils::ModuleLog('ScheduleReprot Name ::', $reportname);
$vtigerMailer->Subject = $subject;
$vtigerMailer->Body = $this->getEmailContent($reportRecordModel);
$vtigerMailer->IsHTML();
$baseFileName = $reportname . '__' . $currentTime;
$oReportRun = ReportRun::getInstance($this->get('reportid'));
$reportFormat = $this->scheduledFormat;
$attachments = array();
if ($reportFormat == 'CSV') {
$fileName = $baseFileName . '.csv';
$filePath = 'storage/' . $fileName;
$attachments[$fileName] = $filePath;
$oReportRun->writeReportToCSVFile($filePath);
}
foreach ($attachments as $attachmentName => $path) {
$vtigerMailer->AddAttachment($path, decode_html($attachmentName));
}
//Added cc to account owner
$accountOwnerId = Users::getActiveAdminId();
$vtigerMailer->AddCC(getUserEmail($accountOwnerId), getUserFullName($accountOwnerId));
$status = $vtigerMailer->Send(true);
foreach ($attachments as $attachmentName => $path) {
unlink($path);
}
return $status;
}
function getQuery() {
$chartModel = $this->getParent();
$reportModel = $chartModel->getParent();
$this->reportRun = ReportRun::getInstance($reportModel->getId());
$advFilterSql = $reportModel->getAdvancedFilterSQL();
$queryColumnsByFieldModel = $this->getQueryColumnsByFieldModel();
if(is_array($queryColumnsByFieldModel)) {
foreach($queryColumnsByFieldModel as $field) {
$this->reportRun->queryPlanner->addTable($field->get('table'));
$columns[] = $field->get('reportcolumn');
}
}
$groupByColumnsByFieldModel = $this->getGroupbyColumnsByFieldModel();
if(is_array($groupByColumnsByFieldModel)) {
foreach($groupByColumnsByFieldModel as $groupField) {
$this->reportRun->queryPlanner->addTable($groupField->get('table'));
$groupByColumns[] = $groupField->get('reportlabel');
$columns[] = $groupField->get('reportcolumn');
}
}
$sql = split(' from ', $this->reportRun->sGetSQLforReport($reportModel->getId(), $advFilterSql, 'PDF'));
$columnLabels = array();
$chartSQL = "SELECT ";
if($this->isRecordCount()) {
$chartSQL .= " count(*) AS RECORD_COUNT,";
}
// Add other columns
if($columns && is_array($columns)) {
$columnLabels = array_merge($columnLabels, $groupByColumns);
$chartSQL .= implode(',', $columns);
}
$chartSQL .= " FROM $sql[1] ";
if($groupByColumns && is_array($groupByColumns)) {
$chartSQL .= " GROUP BY " . implode(',', $groupByColumns);
}
return $chartSQL;
}
/**
* Function returns the Advanced filter SQL
* @return <String>
*/
function getAdvancedFilterSQL()
{
$advancedFilter = $this->get('advancedFilter');
$advancedFilterCriteria = array();
$advancedFilterCriteriaGroup = array();
if (is_array($advancedFilter)) {
foreach ($advancedFilter as $groupIndex => $groupInfo) {
$groupColumns = $groupInfo['columns'];
$groupCondition = $groupInfo['condition'];
if (empty($groupColumns)) {
unset($advancedFilter[1]['condition']);
} else {
if (!empty($groupCondition)) {
$advancedFilterCriteriaGroup[$groupIndex] = array('groupcondition' => $groupCondition);
}
}
foreach ($groupColumns as $groupColumn) {
$groupColumn['groupid'] = $groupIndex;
$groupColumn['columncondition'] = $groupColumn['column_condition'];
unset($groupColumn['column_condition']);
$advancedFilterCriteria[] = $groupColumn;
}
}
}
$this->reportRun = ReportRun::getInstance($this->getId());
$filterQuery = $this->reportRun->RunTimeAdvFilter($advancedFilterCriteria, $advancedFilterCriteriaGroup);
return $filterQuery;
}
public function sendEmail()
{
$currentModule = vglobal('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 = ReportRun::getInstance($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);
}
}
$rep_modules = array();
}
array_push($rep_modules, $primarymodule);
$modules_permitted = true;
$modules_export_permitted = true;
foreach ($rep_modules as $mod) {
if (isPermitted($mod, 'index') != "yes" || vtlib_isModuleActive($mod) == false) {
$modules_permitted = false;
$restrictedmodules[] = $mod;
}
if (isPermitted("{$mod}", 'Export', '') != 'yes') {
$modules_export_permitted = false;
}
}
if (isPermitted($primarymodule, 'index') == "yes" && $modules_permitted == true) {
$oReportRun = ReportRun::getInstance($reportid);
require_once 'include/Zend/Json.php';
$json = new Zend_Json();
$advft_criteria = $_REQUEST['advft_criteria'];
if (!empty($advft_criteria)) {
$advft_criteria = $json->decode($advft_criteria);
}
$advft_criteria_groups = $_REQUEST['advft_criteria_groups'];
if (!empty($advft_criteria_groups)) {
$advft_criteria_groups = $json->decode($advft_criteria_groups);
}
if ($_REQUEST['submode'] == 'saveCriteria') {
updateAdvancedCriteria($reportid, $advft_criteria, $advft_criteria_groups);
}
$filtersql = $oReportRun->RunTimeAdvFilter($advft_criteria, $advft_criteria_groups);
$showCharts = $reporttype == 'summary' && $oReportRun->hasGroupingList() ? true : false;
function getReport(Vtiger_Request $request)
{
$viewer = $this->getViewer($request);
$moduleName = $request->getModule();
$record = $request->get('record');
$page = $request->get('page');
$data = $this->reportData;
$calculation = $this->calculationFields;
if (empty($data)) {
$reportModel = Reports_Record_Model::getInstanceById($record);
$reportModel->setModule('Reports');
$reportType = $reportModel->get('reporttype');
$pagingModel = new Vtiger_Paging_Model();
$pagingModel->set('page', $page);
$pagingModel->set('limit', self::REPORT_LIMIT + 1);
$reportData = $reportModel->getReportData($pagingModel);
$data = $reportData['data'];
$calculation = $reportModel->getReportCalulationData();
$advFilterSql = $reportModel->getAdvancedFilterSQL();
$query = $reportModel->getReportSQL($advFilterSql, 'PDF');
$countQuery = $reportModel->generateCountQuery($query);
$this->count = $reportModel->getReportsCount($countQuery);
}
$viewer->assign('CALCULATION_FIELDS', $calculation);
$viewer->assign('DATA', $data);
$viewer->assign('RECORD_ID', $record);
$viewer->assign('PAGING_MODEL', $pagingModel);
$viewer->assign('COUNT', $this->count);
$viewer->assign('MODULE', $moduleName);
$viewer->assign('REPORT_RUN_INSTANCE', ReportRun::getInstance($record));
if (count($data) > self::REPORT_LIMIT) {
$viewer->assign('LIMIT_EXCEEDED', true);
}
$viewer->view('ReportContents.tpl', $moduleName);
}
function getReport(Vtiger_Request $request)
{
$viewer = $this->getViewer($request);
$moduleName = $request->getModule();
$record = $request->get('record');
$page = $request->get('page');
//$data = $this->reportData; //TODO
$calculation = $this->calculationFields;
//if(empty($data)){
$reportModel = Reports_Record_Model::getInstanceById($record);
$reportModel->setModule('Reports');
$reportType = $reportModel->get('reporttype');
$pagingModel = new Vtiger_Paging_Model();
$pagingModel->set('page', $page);
$pagingModel->set('limit', self::REPORT_LIMIT + 1);
$reportData = $reportModel->getReportData($pagingModel);
$data = $reportData['data'];
$dataRaw = $reportData['data_raw'];
$calculation = $reportModel->getReportCalulationData();
$advFilterSql = $reportModel->getAdvancedFilterSQL();
$query = $reportModel->getReportSQL($advFilterSql, 'PDF');
$countQuery = $reportModel->generateCountQuery($query);
$this->count = $reportModel->getReportsCount($countQuery);
//}
/*готовим данные в нужный нам формат для новой таблицы*/
/*получим поле для группировки*/
global $adb;
$reportRun = ReportRun::getInstance($record);
$fieldRawArr = $reportRun->getGroupingList();
foreach ($fieldRawArr as $value) {
$fieldRaw = explode('_', $value, 2);
break;
}
$groupModule = str_replace('`', '', $fieldRaw[0]);
$groupLabel = explode(' ', $fieldRaw[1], 2);
$groupLabel = str_replace('`', '', $groupLabel[0]);
//это системное имя, нужен перевод
$fieldInfo = getFieldByReportLabel($groupModule, $groupLabel);
$fieldType = null;
if (!empty($fieldInfo)) {
$field = WebserviceField::fromArray($adb, $fieldInfo);
$fieldType = $field->getFieldDataType();
}
if (!empty($fieldInfo)) {
$translatedLabel = getTranslatedString($field->getFieldLabelKey(), $groupModule);
} else {
$translatedLabel = getTranslatedString($groupLabel, $groupModule);
$translatedLabel = str_replace("_", " ", $translatedLabel);
}
$groupLabel = $translatedLabel;
/*STRING TRANSLATION starts TODO*/
/*$moduleLabel ='';
if(in_array($module,$modules_selected))
$moduleLabel = getTranslatedString($module,$module);
if(empty($translatedLabel)) {
$translatedLabel = getTranslatedString(str_replace('_', " ",
$fld->name), $module);
}*/
/*if(!empty($this->secondarymodule)) {
if($moduleLabel != '') {
$headerLabel = $moduleLabel." ". $translatedLabel;
}
}*/
/*формируем нужный массив*/
$dataGrouped = array();
foreach ($data as $index => $row) {
$groupIndex = $row[$groupLabel];
if (!isset($dataGrouped[$groupIndex])) {
$dataGrouped[$groupIndex] = array('rows' => array(), 'summary' => array());
}
$dataGrouped[$groupIndex]['rows'][] = $row;
foreach ($row as $reportField => $value) {
$dataRawVal = explode('::', $dataRaw[$index][$reportField]);
$dataRawType = empty($dataRawVal[1]) ? 'undefined' : $dataRawVal[1];
$dataRawVal = $dataRawVal[0];
if (!isset($dataGrouped[$groupIndex]['summary'][$reportField])) {
if ($dataRawType == 'currency') {
$dataRawVal = $dataRawVal + 0;
}
if (empty($dataRawType)) {
$dataRawVal = (string) $dataRawVal;
}
$dataGrouped[$groupIndex]['summary'][$reportField] = $dataRawVal;
} elseif ($dataRawType == 'currency') {
/*можно позже добавить другие типы данных*/
$summed = $dataGrouped[$groupIndex]['summary'][$reportField] + $dataRawVal;
$dataGrouped[$groupIndex]['summary'][$reportField] = $summed;
/*todo переделать в пользовательский формат*/
}
}
}
/*end*/
/*убираем ненужные столбцы*/
$columns = array('SUM' => false, 'AVG' => false, 'MIN' => false, 'MAX' => false);
foreach ($calculation as $metriks) {
foreach ($metriks as $index => $metrik) {
$postfix = substr($index, -3, 3);
if ($columns[$postfix]) {
continue;
}
if (!empty($metrik)) {
$columns[$postfix] = true;
}
}
}
$viewer->assign('COLUMNS', $columns);
/*end*/
$viewer->assign('CALCULATION_FIELDS', $calculation);
$viewer->assign('DATA', $data);
$viewer->assign('DATAGROUPED', $dataGrouped);
$viewer->assign('RECORD_ID', $record);
$viewer->assign('PAGING_MODEL', $pagingModel);
$viewer->assign('COUNT', $this->count);
$viewer->assign('MODULE', $moduleName);
$viewer->assign('REPORT_RUN_INSTANCE', ReportRun::getInstance($record));
if (count($data) > self::REPORT_LIMIT) {
$viewer->assign('LIMIT_EXCEEDED', true);
}
$viewer->view('ReportContents.tpl', $moduleName);
}
