/**
* 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());
// SalesPlatform.ru begin
if(in_array($this->reportRun->reporttype, getCustomReportsList())) {
$filterQuery = $this->reportRun->generateAdvFilterArray($advancedFilterCriteria);
} else {
$filterQuery = $this->reportRun->RunTimeAdvFilter($advancedFilterCriteria, $advancedFilterCriteriaGroup);
}
// SalesPlatform.ru end
return $filterQuery;
}
/** function to get query for the given reportid,filterlist,type
* @ param $reportid : Type integer
* @ param $filtersql : Type Array
* @ param $module : Type String
* this returns join query for the report
*/
function sGetSQLforReport($reportid, $filtersql, $type = '', $chartReport = false, $startLimit = false, $endLimit = false)
{
global $log;
// SalesPlatform.ru begin
if (in_array($this->reporttype, getCustomReportsList())) {
$filterArray = $filtersql;
return $this->sSPGetSQLforReport($filterArray);
}
// SalesPlatform.ru end
$columnlist = $this->getQueryColumnsList($reportid, $type);
$groupslist = $this->getGroupingList($reportid);
$groupTimeList = $this->getGroupByTimeList($reportid);
$stdfilterlist = $this->getStdFilterList($reportid);
$columnstotallist = $this->getColumnsTotal($reportid);
$advfiltersql = $this->getAdvFilterSql($reportid);
$this->totallist = $columnstotallist;
global $current_user;
//Fix for ticket #4915.
$selectlist = $columnlist;
//columns list
if (isset($selectlist)) {
$selectedcolumns = implode(", ", $selectlist);
if ($chartReport == true) {
$selectedcolumns .= ", count(*) AS 'groupby_count'";
}
}
//groups list
if (isset($groupslist)) {
$groupsquery = implode(", ", $groupslist);
}
if (isset($groupTimeList)) {
$groupTimeQuery = implode(", ", $groupTimeList);
}
//standard list
if (isset($stdfilterlist)) {
$stdfiltersql = implode(", ", $stdfilterlist);
}
//columns to total list
if (isset($columnstotallist)) {
$columnstotalsql = implode(", ", $columnstotallist);
}
if ($stdfiltersql != "") {
$wheresql = " and " . $stdfiltersql;
}
if (isset($filtersql) && $filtersql !== false && $filtersql != '') {
$advfiltersql = $filtersql;
}
if ($advfiltersql != "") {
$wheresql .= " and " . $advfiltersql;
}
$reportquery = $this->getReportsQuery($this->primarymodule, $type);
// If we don't have access to any columns, let us select one column and limit result to shown we have not results
// Fix for: http://trac.vtiger.com/cgi-bin/trac.cgi/ticket/4758 - Prasad
$allColumnsRestricted = false;
if ($type == 'COLUMNSTOTOTAL') {
if ($columnstotalsql != '') {
$reportquery = "select " . $columnstotalsql . " " . $reportquery . " " . $wheresql;
}
} else {
if ($selectedcolumns == '') {
// Fix for: http://trac.vtiger.com/cgi-bin/trac.cgi/ticket/4758 - Prasad
$selectedcolumns = "''";
// "''" to get blank column name
$allColumnsRestricted = true;
}
$reportquery = "select DISTINCT " . $selectedcolumns . " " . $reportquery . " " . $wheresql;
}
$reportquery = listQueryNonAdminChange($reportquery, $this->primarymodule);
if (trim($groupsquery) != "" && $type !== 'COLUMNSTOTOTAL') {
if ($chartReport == true) {
$reportquery .= "group by " . $this->GetFirstSortByField($reportid);
} else {
$reportquery .= " order by " . $groupsquery;
}
}
// Prasad: No columns selected so limit the number of rows directly.
if ($allColumnsRestricted) {
$reportquery .= " limit 0";
} else {
if ($startLimit !== false && $endLimit !== false) {
$reportquery .= " LIMIT {$startLimit}, {$endLimit}";
}
}
preg_match('/&/', $reportquery, $matches);
if (!empty($matches)) {
$report = str_replace('&', '&', $reportquery);
$reportquery = $this->replaceSpecialChar($report);
}
$log->info("ReportRun :: Successfully returned sGetSQLforReport" . $reportid);
$this->queryPlanner->initializeTempTables();
return $reportquery;
}
function preProcess(Vtiger_Request $request)
{
parent::preProcess($request);
$viewer = $this->getViewer($request);
$moduleName = $request->getModule();
$recordId = $request->get('record');
$page = $request->get('page');
$detailViewModel = Reports_DetailView_Model::getInstance($moduleName, $recordId);
$reportModel = $detailViewModel->getRecord();
$reportModel->setModule('Reports');
$pagingModel = new Vtiger_Paging_Model();
$pagingModel->set('page', $page);
$pagingModel->set('limit', self::REPORT_LIMIT);
$reportData = $reportModel->getReportData($pagingModel);
$this->reportData = $reportData['data'];
$this->calculationFields = $reportModel->getReportCalulationData();
$count = $reportData['count'];
if ($count < 1000) {
$this->count = $count;
} else {
$query = $reportModel->getReportSQL(false, 'PDF');
$countQuery = $reportModel->generateCountQuery($query);
$this->count = $reportModel->getReportsCount($countQuery);
}
$primaryModule = $reportModel->getPrimaryModule();
$secondaryModules = $reportModel->getSecondaryModules();
$primaryModuleModel = Vtiger_Module_Model::getInstance($primaryModule);
$currentUser = Users_Record_Model::getCurrentUserModel();
$userPrivilegesModel = Users_Privileges_Model::getInstanceById($currentUser->getId());
$permission = $userPrivilegesModel->hasModulePermission($primaryModuleModel->getId());
if (!$permission) {
$viewer->assign('MODULE', $primaryModule);
$viewer->assign('MESSAGE', 'LBL_PERMISSION_DENIED');
$viewer->view('OperationNotPermitted.tpl', $primaryModule);
exit;
}
$detailViewLinks = $detailViewModel->getDetailViewLinks();
// Advanced filter conditions
$viewer->assign('SELECTED_ADVANCED_FILTER_FIELDS', $reportModel->transformToNewAdvancedFilter());
$viewer->assign('PRIMARY_MODULE', $primaryModule);
$recordStructureInstance = Vtiger_RecordStructure_Model::getInstanceFromRecordModel($reportModel);
$primaryModuleRecordStructure = $recordStructureInstance->getPrimaryModuleRecordStructure();
$secondaryModuleRecordStructures = $recordStructureInstance->getSecondaryModuleRecordStructure();
//TODO : We need to remove "update_log" field from "HelpDesk" module in New Look
// after removing old look we need to remove this field from crm
if ($primaryModule == 'HelpDesk') {
foreach ($primaryModuleRecordStructure as $blockLabel => $blockFields) {
foreach ($blockFields as $field => $object) {
if ($field == 'update_log') {
unset($primaryModuleRecordStructure[$blockLabel][$field]);
}
}
}
}
if (!empty($secondaryModuleRecordStructures)) {
foreach ($secondaryModuleRecordStructures as $module => $structure) {
if ($module == 'HelpDesk') {
foreach ($structure as $blockLabel => $blockFields) {
foreach ($blockFields as $field => $object) {
if ($field == 'update_log') {
unset($secondaryModuleRecordStructures[$module][$blockLabel][$field]);
}
}
}
}
}
}
// End
$viewer->assign('PRIMARY_MODULE_RECORD_STRUCTURE', $primaryModuleRecordStructure);
$viewer->assign('SECONDARY_MODULE_RECORD_STRUCTURES', $secondaryModuleRecordStructures);
$secondaryModuleIsCalendar = strpos($secondaryModules, 'Calendar');
if ($primaryModule == 'Calendar' || $secondaryModuleIsCalendar !== FALSE) {
$advanceFilterOpsByFieldType = Calendar_Field_Model::getAdvancedFilterOpsByFieldType();
} else {
$advanceFilterOpsByFieldType = Vtiger_Field_Model::getAdvancedFilterOpsByFieldType();
}
$viewer->assign('ADVANCED_FILTER_OPTIONS', Vtiger_Field_Model::getAdvancedFilterOptions());
$viewer->assign('ADVANCED_FILTER_OPTIONS_BY_TYPE', $advanceFilterOpsByFieldType);
$dateFilters = Vtiger_Field_Model::getDateFilterTypes();
foreach ($dateFilters as $comparatorKey => $comparatorInfo) {
$comparatorInfo['startdate'] = DateTimeField::convertToUserFormat($comparatorInfo['startdate']);
$comparatorInfo['enddate'] = DateTimeField::convertToUserFormat($comparatorInfo['enddate']);
$comparatorInfo['label'] = vtranslate($comparatorInfo['label'], $module);
$dateFilters[$comparatorKey] = $comparatorInfo;
}
$viewer->assign('DATE_FILTERS', $dateFilters);
$viewer->assign('LINEITEM_FIELD_IN_CALCULATION', $reportModel->showLineItemFieldsInFilter(false));
$viewer->assign('DETAILVIEW_LINKS', $detailViewLinks);
$viewer->assign('REPORT_MODEL', $reportModel);
$viewer->assign('RECORD_ID', $recordId);
$viewer->assign('COUNT', $this->count);
$viewer->assign('MODULE', $moduleName);
// SalesPlatform.ru begin
// If it is a custom report
if (in_array($reportModel->getReportType(), getCustomReportsList())) {
// Only equals filter
$customAdvanceFilterOpsByFieldType = array('V' => array('e'), 'N' => array('e'), 'T' => array('e'), 'I' => array('e'), 'C' => array('e'), 'D' => array('e'), 'DT' => array('e'), 'I' => array('e'), 'NN' => array('e'), 'E' => array('e'));
$spPaymentsRecordStructure = $recordStructureInstance->getStructure('SPPayments');
foreach ($spPaymentsRecordStructure as $blockLabel => $blockFields) {
foreach ($blockFields as $field => $object) {
if ($field != 'pay_date' && $field != 'assigned_user_id' && $field != 'payer') {
unset($spPaymentsRecordStructure[$blockLabel][$field]);
}
}
}
$customBlockName = 'CUSTOM_BLOCK';
$customRecordStructure[$customBlockName] = $spPaymentsRecordStructure['LBL_PAYMENT_DETAILS'];
// Check date filter for custom report
if (!in_array($reportModel->getReportType(), getCustomReportsListWithDateFilter())) {
unset($customRecordStructure[$customBlockName]['pay_date']);
}
// Check owner filter for custom report
if (!in_array($reportModel->getReportType(), getCustomReportsListWithOwnerFilter())) {
unset($customRecordStructure[$customBlockName]['assigned_user_id']);
}
// Check account filter for custom report
if (!in_array($reportModel->getReportType(), getCustomReportsListWithAccountFilter())) {
unset($customRecordStructure[$customBlockName]['payer']);
}
$viewer->assign('PRIMARY_MODULE_RECORD_STRUCTURE', $customRecordStructure);
$viewer->assign('ADVANCED_FILTER_OPTIONS_BY_TYPE', $customAdvanceFilterOpsByFieldType);
$viewer->view('SPReportHeader.tpl', $moduleName);
} else {
$viewer->view('ReportHeader.tpl', $moduleName);
}
// SalesPlatform.ru end
}
