/** Function to get vtiger_fields for the given module and block * This function gets the vtiger_fields for the given module * It accepts the module and the block as arguments and * returns the array column lists * Array module_columnlist[ vtiger_fieldtablename:fieldcolname:module_fieldlabel1:fieldname:fieldtypeofdata]=fieldlabel */ function getColumnsListbyBlock($module, $block) { global $adb, $log, $current_user; if (is_string($block)) { $block = explode(",", $block); } $skipTalbes = array('vtiger_emaildetails', 'vtiger_attachments'); $tabid = getTabid($module); if ($module == 'Calendar') { $tabid = array('9', '16'); } $params = array($tabid, $block); require 'user_privileges/user_privileges_' . $current_user->id . '.php'; //Security Check if ($is_admin == true || $profileGlobalPermission[1] == 0 || $profileGlobalPermission[2] == 0) { $sql = "select * from vtiger_field where vtiger_field.tabid in (" . generateQuestionMarks($tabid) . ") and vtiger_field.block in (" . generateQuestionMarks($block) . ") and vtiger_field.displaytype in (1,2,3) and vtiger_field.presence in (0,2) AND tablename NOT IN (" . generateQuestionMarks($skipTalbes) . ") "; //fix for Ticket #4016 if ($module == "Calendar") { $sql .= " group by vtiger_field.fieldlabel order by sequence"; } else { $sql .= " order by sequence"; } } else { $profileList = getCurrentUserProfileList(); $sql = "select * from vtiger_field inner join vtiger_profile2field on vtiger_profile2field.fieldid=vtiger_field.fieldid inner join vtiger_def_org_field on vtiger_def_org_field.fieldid=vtiger_field.fieldid where vtiger_field.tabid in (" . generateQuestionMarks($tabid) . ") and vtiger_field.block in (" . generateQuestionMarks($block) . ") and vtiger_field.displaytype in (1,2,3) and vtiger_profile2field.visible=0 and vtiger_def_org_field.visible=0 and vtiger_field.presence in (0,2)"; if (count($profileList) > 0) { $sql .= " and vtiger_profile2field.profileid in (" . generateQuestionMarks($profileList) . ")"; array_push($params, $profileList); } $sql .= ' and tablename NOT IN (' . generateQuestionMarks($skipTalbes) . ') '; //fix for Ticket #4016 if ($module == "Calendar") { $sql .= " group by vtiger_field.fieldlabel order by sequence"; } else { $sql .= " group by vtiger_field.fieldid order by sequence"; } } array_push($params, $skipTalbes); $module_columnlist = array(); $result = $adb->pquery($sql, $params); $noofrows = $adb->num_rows($result); for ($i = 0; $i < $noofrows; $i++) { $fieldtablename = $adb->query_result($result, $i, "tablename"); $fieldcolname = $adb->query_result($result, $i, "columnname"); $fieldname = $adb->query_result($result, $i, "fieldname"); $fieldtype = $adb->query_result($result, $i, "typeofdata"); $uitype = $adb->query_result($result, $i, "uitype"); $fieldtype = explode("~", $fieldtype); $fieldtypeofdata = $fieldtype[0]; $blockid = $adb->query_result($result, $i, "block"); //Here we Changing the displaytype of the field. So that its criteria will be displayed correctly in Reports Advance Filter. $fieldtypeofdata = ChangeTypeOfData_Filter($fieldtablename, $fieldcolname, $fieldtypeofdata); if ($uitype == 68 || $uitype == 59) { $fieldtypeofdata = 'V'; } if ($fieldtablename == "vtiger_crmentity") { $fieldtablename = $fieldtablename . $module; } if ($fieldname == "assigned_user_id") { $fieldtablename = "vtiger_users" . $module; $fieldcolname = "user_name"; } if ($fieldname == "assigned_user_id1") { $fieldtablename = "vtiger_usersRel1"; $fieldcolname = "user_name"; } $fieldlabel = $adb->query_result($result, $i, "fieldlabel"); if ($module == 'Emails' and $fieldlabel == 'Date & Time Sent') { $fieldlabel = 'Date Sent'; $fieldtypeofdata = 'D'; } $fieldlabel1 = str_replace(' ', '_', $fieldlabel); $fieldlabel1 = ReportRun::replaceSpecialChar($fieldlabel1); $optionvalue = $fieldtablename . ":" . $fieldcolname . ":" . $module . "_" . $fieldlabel1 . ":" . $fieldname . ":" . $fieldtypeofdata; $this->adv_rel_fields[$fieldtypeofdata][] = '$' . $module . '#' . $fieldname . '$' . "::" . getTranslatedString($module, $module) . " " . getTranslatedString($fieldlabel, $module); $module_columnlist[$optionvalue] = $fieldlabel; } $blockname = getBlockName($block); if ($blockname == 'LBL_RELATED_PRODUCTS' && in_array($module, getInventoryModules())) { $fieldtablename = 'vtiger_inventoryproductrel'; $fields = array('productid' => getTranslatedString('Product Name', $module), 'serviceid' => getTranslatedString('Service Name', $module), 'listprice' => getTranslatedString('List Price', $module), 'discount' => getTranslatedString('Discount', $module), 'quantity' => getTranslatedString('Quantity', $module), 'comment' => getTranslatedString('Comments', $module)); $fields_datatype = array('productid' => 'V', 'serviceid' => 'V', 'listprice' => 'N', 'discount' => 'N', 'quantity' => 'N', 'comment' => 'V'); foreach ($fields as $fieldcolname => $label) { $fieldtypeofdata = $fields_datatype[$fieldcolname]; if ($fieldcolname != 'productid' || $fieldcolname != 'serviceid') { $optionvalue = $fieldtablename . $module . ":" . $fieldcolname . ":" . $module . "_" . $label . ":" . $fieldcolname . ":" . $fieldtypeofdata; } else { $optionvalue = $fieldtablename . ":" . $fieldcolname . ":" . $module . "_" . $label . ":" . $fieldcolname . ":" . $fieldtypeofdata; } $module_columnlist[$optionvalue] = $label; } } $log->info("Reports :: FieldColumns->Successfully returned ColumnslistbyBlock" . $module . $block); return $module_columnlist; }
public static function generateChartDataFromReports($queryResult, $groupbyField, $fieldDetails = '', $reportid = '') { require_once 'modules/Reports/CustomReportUtils.php'; require_once 'include/Webservices/Utils.php'; require_once 'include/Webservices/Query.php'; global $adb, $current_user, $theme, $default_charset; $inventorymodules = array('Quotes', 'SalesOrder', 'PurchaseOrder', 'Invoice', 'Products', 'PriceBooks', 'Vendors', 'Services'); $rows = $adb->num_rows($queryResult); $condition = "is"; $current_theme = $theme; $groupByFields = array(); $yaxisArray = array(); $ChartDataArray = array(); $target_val = array(); $report = new ReportRun($reportid); $restrictedModules = array(); if ($report->secondarymodule != '') { $reportModules = explode(":", $report->secondarymodule); } else { $reportModules = array(); } array_push($reportModules, $report->primarymodule); $restrictedModules = false; foreach ($reportModules as $mod) { if (isPermitted($mod, 'index') != "yes" || vtlib_isModuleActive($mod) == false) { if (!is_array($restrictedModules)) { $restrictedModules = array(); } $restrictedModules[] = $mod; } } if (is_array($restrictedModules) && count($restrictedModules) > 0) { $ChartDataArray['error'] = "<h4>" . getTranslatedString('LBL_NO_ACCESS', 'Reports') . ' - ' . implode(',', $restrictedModules) . "</h4>"; return $ChartDataArray; } if ($fieldDetails != '') { list($tablename, $colname, $module_field, $fieldname, $single) = explode(":", $fieldDetails); list($module, $field) = split("_", $module_field); $dateField = false; if ($single == 'D') { $dateField = true; $query = "SELECT * FROM vtiger_reportgroupbycolumn WHERE reportid=? ORDER BY sortid"; $result = $adb->pquery($query, array($reportid)); $criteria = $adb->query_result($result, 0, 'dategroupbycriteria'); } } preg_match('/&/', $groupbyField, $matches); if (!empty($matches)) { $groupfield = str_replace('&', '&', $groupbyField); $groupbyField = $report->replaceSpecialChar($groupfield); } $handler = vtws_getModuleHandlerFromName($module, $current_user); $meta = $handler->getMeta(); $meta->retrieveMeta(); $referenceFields = $meta->getReferenceFieldDetails(); if ($rows > 0) { $resultRow = $adb->query_result_rowdata($queryResult, 0); if (!array_key_exists($groupbyField, $resultRow)) { $ChartDataArray['error'] = "<h4>" . getTranslatedString('LBL_NO_PERMISSION_FIELD', 'Dashboard') . "</h4>"; return $ChartDataArray; } } for ($i = 0; $i < $rows; $i++) { $groupFieldValue = $adb->query_result($queryResult, $i, strtolower($groupbyField)); $decodedGroupFieldValue = html_entity_decode($groupFieldValue, ENT_QUOTES, $default_charset); if (!empty($groupFieldValue)) { if (in_array($module_field, $report->append_currency_symbol_to_value)) { $valueComp = explode('::', $groupFieldValue); $groupFieldValue = $valueComp[1]; } if ($dateField) { if (!empty($groupFieldValue)) { $groupByFields[] = CustomReportUtils::getXAxisDateFieldValue($groupFieldValue, $criteria); } else { $groupByFields[] = "Null"; } } else { if (in_array($fieldname, array_keys($referenceFields))) { if (count($referenceFields[$fieldname]) > 1) { $refenceModule = CustomReportUtils::getEntityTypeFromName($decodedGroupFieldValue, $referenceFields[$fieldname]); } else { $refenceModule = $referenceFields[$fieldname][0]; } $groupByFields[] = $groupFieldValue; if ($fieldname == 'currency_id' && in_array($module, $inventorymodules)) { $tablename = 'vtiger_currency_info'; } elseif ($refenceModule == 'DocumentFolders' && $fieldname == 'folderid') { $tablename = 'vtiger_attachmentsfolder'; $colname = 'foldername'; } else { require_once "modules/{$refenceModule}/{$refenceModule}.php"; $focus = new $refenceModule(); $tablename = $focus->table_name; $colname = $focus->list_link_field; $condition = "c"; } } else { $groupByFields[] = $groupFieldValue; } } $yaxisArray[] = $adb->query_result($queryResult, $i, 'groupby_count'); if ($fieldDetails != '') { if ($dateField) { $advanceSearchCondition = CustomReportUtils::getAdvanceSearchCondition($fieldDetails, $criteria, $groupFieldValue); if ($module == 'Calendar') { $link_val = "index.php?module=" . $module . "&query=true&action=ListView&" . $advanceSearchCondition; } else { $link_val = "index.php?module=" . $module . "&query=true&action=index&" . $advanceSearchCondition; } } else { $cvid = getCvIdOfAll($module); $esc_search_str = urlencode($decodedGroupFieldValue); if ($single == 'DT') { $esc_search_str = urlencode($groupFieldValue); if (strtolower($fieldname) == 'modifiedtime' || strtolower($fieldname) == 'createdtime') { $tablename = 'vtiger_crmentity'; $colname = $fieldname; } } if ($fieldname == 'assigned_user_id') { $tablename = 'vtiger_crmentity'; $colname = 'smownerid'; } if ($fieldname == 'serviceid' && in_array($module, getInventoryModules())) { $fieldname = 'productid'; } if ($module == 'Calendar') { $link_val = "index.php?module=" . $module . "&action=ListView&search_text=" . $esc_search_str . "&search_field=" . $fieldname . "&searchtype=BasicSearch&query=true&operator=e&viewname=" . $cvid; } else { $link_val = "index.php?module=" . $module . "&action=index&search_text=" . $esc_search_str . "&search_field=" . $fieldname . "&searchtype=BasicSearch&query=true&operator=e&viewname=" . $cvid; } } $target_val[] = $link_val; } } } if (count($groupByFields) == 0) { $ChartDataArray['error'] = "<div class='componentName'>" . getTranslatedString('LBL_NO_DATA', 'Reports') . "</div"; } $ChartDataArray['xaxisData'] = $groupByFields; $ChartDataArray['yaxisData'] = $yaxisArray; $ChartDataArray['targetLink'] = $target_val; $theme = $current_theme; return $ChartDataArray; }