/** 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;
}
