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); }