/** to get the getColumnsListbyBlock for the given module and Block
* @param $module :: Type String
* @param $block :: Type Integer
* @returns $columnlist Array in the format
* $columnlist = Array ($fieldlabel =>'$fieldtablename:$fieldcolname:$fieldname:$module_$fieldlabel1:$fieldtypeofdata',
$fieldlabel1 =>'$fieldtablename1:$fieldcolname1:$fieldname1:$module_$fieldlabel11:$fieldtypeofdata1',
|
$fieldlabeln =>'$fieldtablenamen:$fieldcolnamen:$fieldnamen:$module_$fieldlabel1n:$fieldtypeofdatan')
*/
function getColumnsListbyBlock($module, $block)
{
global $adb, $mod_strings, $app_strings;
$block_ids = explode(",", $block);
$tabid = getTabid($module);
global $current_user;
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
if (empty($this->meta) && $module != 'Calendar') {
$this->meta = $this->getMeta($module, $current_user);
}
if ($tabid == 9) {
$tabid = "9,16";
}
$display_type = " vtiger_field.displaytype in (1,2,3)";
if ($is_admin == true || $profileGlobalPermission[1] == 0 || $profileGlobalPermission[2] == 0) {
$tab_ids = explode(",", $tabid);
$sql = "select * from vtiger_field ";
$sql .= " where vtiger_field.tabid in (" . generateQuestionMarks($tab_ids) . ") and vtiger_field.block in (" . generateQuestionMarks($block_ids) . ") and vtiger_field.presence in (0,2) and";
$sql .= $display_type;
if ($tabid == 9 || $tabid == 16) {
$sql .= " and vtiger_field.fieldname not in('notime','duration_minutes','duration_hours')";
}
$sql .= " order by sequence";
$params = array($tab_ids, $block_ids);
} else {
$tab_ids = explode(",", $tabid);
$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 ";
$sql .= " where vtiger_field.tabid in (" . generateQuestionMarks($tab_ids) . ") and vtiger_field.block in (" . generateQuestionMarks($block_ids) . ") and";
$sql .= "{$display_type} and vtiger_profile2field.visible=0 and vtiger_def_org_field.visible=0 and vtiger_field.presence in (0,2)";
$params = array($tab_ids, $block_ids);
if (count($profileList) > 0) {
$sql .= " and vtiger_profile2field.profileid in (" . generateQuestionMarks($profileList) . ")";
array_push($params, $profileList);
}
if ($tabid == 9 || $tabid == 16) {
$sql .= " and vtiger_field.fieldname not in('notime','duration_minutes','duration_hours')";
}
$sql .= " group by columnname order by sequence";
}
if ($tabid == '9,16') {
$tabid = "9";
}
$result = $adb->pquery($sql, $params);
$noofrows = $adb->num_rows($result);
//Added on 14-10-2005 -- added ticket id in list
if ($module == 'HelpDesk' && $block == 25) {
$module_columnlist['vtiger_crmentity:crmid::HelpDesk_Ticket_ID:I'] = 'Ticket ID';
}
//Added to include vtiger_activity type in vtiger_activity vtiger_customview list
if ($module == 'Calendar' && $block == 19) {
$module_columnlist['vtiger_activity:activitytype:activitytype:Calendar_Activity_Type:V'] = 'Activity Type';
}
if ($module == 'SalesOrder' && $block == 63) {
$module_columnlist['vtiger_crmentity:crmid::SalesOrder_Order_No:I'] = $app_strings['Order No'];
}
if ($module == 'PurchaseOrder' && $block == 57) {
$module_columnlist['vtiger_crmentity:crmid::PurchaseOrder_Order_No:I'] = $app_strings['Order No'];
}
if ($module == 'Quotes' && $block == 51) {
$module_columnlist['vtiger_crmentity:crmid::Quotes_Quote_No:I'] = $app_strings['Quote No'];
}
if ($module != 'Calendar') {
$moduleFieldList = $this->meta->getModuleFields();
}
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");
$fieldtype = explode("~", $fieldtype);
$fieldtypeofdata = $fieldtype[0];
$fieldlabel = $adb->query_result($result, $i, "fieldlabel");
$field = $moduleFieldList[$fieldname];
if (!empty($field) && $field->getFieldDataType() == 'reference') {
$fieldtypeofdata = 'V';
} else {
//Here we Changing the displaytype of the field. So that its criteria will be
//displayed Correctly in Custom view Advance Filter.
$fieldtypeofdata = ChangeTypeOfData_Filter($fieldtablename, $fieldcolname, $fieldtypeofdata);
}
if ($fieldlabel == "Related To") {
$fieldlabel = "Related to";
}
if ($fieldlabel == "Start Date & Time") {
$fieldlabel = "Start Date";
if ($module == 'Calendar' && $block == 19) {
$module_columnlist['vtiger_activity:time_start::Calendar_Start_Time:I'] = 'Start Time';
}
}
$fieldlabel1 = str_replace(" ", "_", $fieldlabel);
$optionvalue = $fieldtablename . ":" . $fieldcolname . ":" . $fieldname . ":" . $module . "_" . $fieldlabel1 . ":" . $fieldtypeofdata;
//added to escape attachments fields in customview as we have multiple attachments
$fieldlabel = getTranslatedString($fieldlabel);
//added to support i18n issue
if ($module != 'HelpDesk' || $fieldname != 'filename') {
$module_columnlist[$optionvalue] = $fieldlabel;
}
if ($fieldtype[1] == "M") {
$this->mandatoryvalues[] = "'" . $optionvalue . "'";
$this->showvalues[] = $fieldlabel;
$this->data_type[$fieldlabel] = $fieldtype[1];
}
}
return $module_columnlist;
}
/**
* Function to get the Report column name transformation of the field
* @return <String> - tablename:columnname:module_fieldlabel:fieldname:fieldtype
*/
public function getReportFilterColumnName()
{
$moduleName = $this->getModuleName();
$tableName = $this->get('table');
$columnName = $this->get('column');
$fieldName = $this->get('name');
$fieldLabel = $this->get('label');
$typeOfData = $this->get('typeofdata');
$fieldTypeOfData = explode('~', $typeOfData);
$fieldType = $fieldTypeOfData[0];
if ($this->getFieldDataType() == 'reference') {
$fieldType = 'V';
} else {
$fieldType = ChangeTypeOfData_Filter($tableName, $columnName, $fieldType);
}
$escapedFieldLabel = str_replace(' ', '_', $fieldLabel);
$moduleFieldLabel = $moduleName . '_' . $escapedFieldLabel;
if ($tableName == 'vtiger_crmentity' && $columnName != 'smownerid') {
$tableName = 'vtiger_crmentity' . $moduleName;
} elseif ($columnName == 'smownerid') {
$tableName = 'vtiger_users' . $moduleName;
$columnName = 'user_name';
}
return $tableName . ':' . $columnName . ':' . $moduleFieldLabel . ':' . $fieldName . ':' . $fieldType;
}
/** Function to get field that is to be compared in query form for the given Comparator and field
* @ param $field : field
* returns the value for the comparator
*/
function getFilterComparedField($field)
{
global $adb, $ogReport;
if (!empty($this->secondarymodule)) {
$secModules = explode(':', $this->secondarymodule);
foreach ($secModules as $secModule) {
$secondary = CRMEntity::getInstance($secModule);
$this->queryPlanner->addTable($secondary->table_name);
}
}
$field = split('#', $field);
$module = $field[0];
$fieldname = trim($field[1]);
$tabid = getTabId($module);
$field_query = $adb->pquery("SELECT tablename,columnname,typeofdata,fieldname,uitype FROM vtiger_field WHERE tabid = ? AND fieldname= ?", array($tabid, $fieldname));
$fieldtablename = $adb->query_result($field_query, 0, 'tablename');
$fieldcolname = $adb->query_result($field_query, 0, 'columnname');
$typeofdata = $adb->query_result($field_query, 0, 'typeofdata');
$fieldtypeofdata = ChangeTypeOfData_Filter($fieldtablename, $fieldcolname, $typeofdata[0]);
$uitype = $adb->query_result($field_query, 0, 'uitype');
/*if($tr[0]==$ogReport->primodule)
$value = $adb->query_result($field_query,0,'tablename').".".$adb->query_result($field_query,0,'columnname');
else
$value = $adb->query_result($field_query,0,'tablename').$tr[0].".".$adb->query_result($field_query,0,'columnname');
*/
if ($uitype == 68 || $uitype == 59) {
$fieldtypeofdata = 'V';
}
if ($fieldtablename == "vtiger_crmentity" && $module != $this->primarymodule) {
$fieldtablename = $fieldtablename . $module;
}
if ($fieldname == "assigned_user_id") {
$fieldtablename = "vtiger_users" . $module;
$fieldcolname = "user_name";
}
if ($fieldtablename == "vtiger_crmentity" && $fieldname == "modifiedby") {
$fieldtablename = "vtiger_lastModifiedBy" . $module;
$fieldcolname = "user_name";
}
if ($fieldname == "assigned_user_id1") {
$fieldtablename = "vtiger_usersRel1";
$fieldcolname = "user_name";
}
$value = $fieldtablename . "." . $fieldcolname;
$this->queryPlanner->addTable($fieldtablename);
return $value;
}
/** 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, $group_res_by_block = false)
{
global $adb;
global $log;
global $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);
$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);
$optionvalue = $fieldtablename . ":" . $fieldcolname . ":" . $module . "__" . $fieldlabel1 . ":" . $fieldname . ":" . $fieldtypeofdata;
$adv_rel_field_tod_value = '$' . $module . '#' . $fieldname . '$' . "::" . getTranslatedString($module, $module) . " " . getTranslatedString($fieldlabel, $module);
if (!is_array($this->adv_rel_fields[$fieldtypeofdata]) || !in_array($adv_rel_field_tod_value, $this->adv_rel_fields[$fieldtypeofdata])) {
$this->adv_rel_fields[$fieldtypeofdata][] = $adv_rel_field_tod_value;
}
//added to escape attachments fields in Reports as we have multiple attachments
if ($module == 'HelpDesk' && $fieldname == 'filename') {
continue;
}
if (is_string($block) || $group_res_by_block == false) {
$module_columnlist[$optionvalue] = $fieldlabel;
} else {
$module_columnlist[$blockid][$optionvalue] = $fieldlabel;
}
}
if (is_string($block)) {
$this->fixGetColumnsListbyBlockForInventory($module, $block, $module_columnlist);
}
return $module_columnlist;
}
/** Function to get field that is to be compared in query form for the given Comparator and field
* @ param $field : field
* returns the value for the comparator
*/
function getFilterComparedField($field)
{
global $adb, $ogReport;
if (!empty($this->secondarymodule)) {
$secModules = explode(':', $this->secondarymodule);
foreach ($secModules as $secModule) {
$secondary = CRMEntity::getInstance($secModule);
$this->queryPlanner->addTable($secondary->table_name);
}
}
$field = split('#', $field);
$module = $field[0];
$fieldname = trim($field[1]);
$tabid = getTabId($module);
$field_query = $adb->pquery("SELECT tablename,columnname,typeofdata,fieldname,uitype FROM vtiger_field WHERE tabid = ? AND fieldname= ?", array($tabid, $fieldname));
$fieldtablename = $adb->query_result($field_query, 0, 'tablename');
$fieldcolname = $adb->query_result($field_query, 0, 'columnname');
$typeofdata = $adb->query_result($field_query, 0, 'typeofdata');
$fieldtypeofdata = ChangeTypeOfData_Filter($fieldtablename, $fieldcolname, $typeofdata[0]);
$uitype = $adb->query_result($field_query, 0, 'uitype');
/* if($tr[0]==$ogReport->primodule)
$value = $adb->query_result($field_query,0,'tablename').".".$adb->query_result($field_query,0,'columnname');
else
$value = $adb->query_result($field_query,0,'tablename').$tr[0].".".$adb->query_result($field_query,0,'columnname');
*/
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 == "account_id") {
$fieldtablename = "vtiger_account" . $module;
$fieldcolname = "accountname";
}
if ($fieldname == "contact_id") {
$fieldtablename = "vtiger_contactdetails" . $module;
$fieldcolname = "lastname";
}
if ($fieldname == "parent_id") {
$fieldtablename = "vtiger_crmentityRel" . $module;
$fieldcolname = "setype";
}
if ($fieldname == "vendor_id") {
$fieldtablename = "vtiger_vendorRel" . $module;
$fieldcolname = "vendorname";
}
if ($fieldname == "potential_id") {
$fieldtablename = "vtiger_potentialRel" . $module;
$fieldcolname = "potentialname";
}
if ($fieldname == "assigned_user_id1") {
$fieldtablename = "vtiger_usersRel1";
$fieldcolname = "user_name";
}
if ($fieldname == 'quote_id') {
$fieldtablename = "vtiger_quotes" . $module;
$fieldcolname = "subject";
}
if ($fieldname == 'product_id' && $fieldtablename == 'vtiger_troubletickets') {
$fieldtablename = "vtiger_productsRel";
$fieldcolname = "productname";
}
if ($fieldname == 'product_id' && $fieldtablename == 'vtiger_campaign') {
$fieldtablename = "vtiger_productsCampaigns";
$fieldcolname = "productname";
}
if ($fieldname == 'product_id' && $fieldtablename == 'vtiger_products') {
$fieldtablename = "vtiger_productsProducts";
$fieldcolname = "productname";
}
if ($fieldname == 'campaignid' && $module == 'Potentials') {
$fieldtablename = "vtiger_campaign" . $module;
$fieldcolname = "campaignname";
}
$value = $fieldtablename . "." . $fieldcolname;
$this->queryPlanner->addTable($fieldtablename);
return $value;
}
/** Function to get field that is to be compared in query form for the given Comparator and field
* @ param $field : field
* returns the value for the comparator
*/
function getFilterComparedField($field)
{
global $adb, $ogReport;
$field = explode('#', $field);
$module = $field[0];
$fieldname = trim($field[1]);
$tabid = getTabId($module);
$field_query = $adb->pquery("SELECT tablename,columnname,typeofdata,fieldname,uitype FROM vtiger_field WHERE tabid = ? AND fieldname= ?", array($tabid, $fieldname));
$fieldtablename = $adb->query_result($field_query, 0, 'tablename');
$fieldcolname = $adb->query_result($field_query, 0, 'columnname');
$typeofdata = $adb->query_result($field_query, 0, 'typeofdata');
$fieldtypeofdata = ChangeTypeOfData_Filter($fieldtablename, $fieldcolname, $typeofdata[0]);
$uitype = $adb->query_result($field_query, 0, 'uitype');
/*if($tr[0]==$ogReport->primodule)
$value = $adb->query_result($field_query,0,'tablename').".".$adb->query_result($field_query,0,'columnname');
else
$value = $adb->query_result($field_query,0,'tablename').$tr[0].".".$adb->query_result($field_query,0,'columnname');
*/
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 ($fieldtablename == "vtiger_crmentity" && $fieldname == "modifiedby") {
$fieldtablename = "vtiger_lastModifiedBy" . $module;
$fieldcolname = "user_name";
}
if ($fieldname == "assigned_user_id1") {
$fieldtablename = "vtiger_usersRel1";
$fieldcolname = "user_name";
}
$value = $fieldtablename . "." . $fieldcolname;
return $value;
}
/** 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;
}
/** 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;
global $log;
global $current_user;
if (is_string($block)) {
$block = explode(",", $block);
}
$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) ";
//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);
}
//fix for Ticket #4016
if ($module == "Calendar") {
$sql .= " group by vtiger_field.fieldid,vtiger_field.fieldlabel order by sequence";
} else {
$sql .= " group by vtiger_field.fieldid order by sequence";
}
}
$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];
//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 == "account_id") {
$fieldtablename = "vtiger_account" . $module;
$fieldcolname = "accountname";
}
if ($fieldname == "contact_id") {
$fieldtablename = "vtiger_contactdetails" . $module;
$fieldcolname = "lastname";
}
if ($fieldname == "parent_id") {
$fieldtablename = "vtiger_crmentityRel" . $module;
$fieldcolname = "setype";
}
if ($fieldname == "vendor_id") {
$fieldtablename = "vtiger_vendorRel" . $module;
$fieldcolname = "vendorname";
}
if ($fieldname == "potential_id") {
$fieldtablename = "vtiger_potentialRel" . $module;
$fieldcolname = "potentialname";
}
if ($fieldname == "assigned_user_id1") {
$fieldtablename = "vtiger_usersRel1";
$fieldcolname = "user_name";
}
if ($fieldname == 'quote_id') {
$fieldtablename = "vtiger_quotes" . $module;
$fieldcolname = "subject";
}
$product_id_tables = array("vtiger_troubletickets" => "vtiger_productsRel", "vtiger_campaign" => "vtiger_productsCampaigns", "vtiger_faq" => "vtiger_productsFaq");
if ($fieldname == 'product_id' && isset($product_id_tables[$fieldtablename])) {
$fieldtablename = $product_id_tables[$fieldtablename];
$fieldcolname = "productname";
}
if ($fieldname == 'campaignid' && $module == 'Potentials') {
$fieldtablename = "vtiger_campaign" . $module;
$fieldcolname = "campaignname";
}
if ($fieldname == 'currency_id' && $fieldtablename == 'vtiger_pricebook') {
$fieldtablename = "vtiger_currency_info" . $module;
$fieldcolname = "currency_name";
}
$fieldlabel = $adb->query_result($result, $i, "fieldlabel");
$fieldlabel1 = str_replace(" ", "_", $fieldlabel);
$optionvalue = $fieldtablename . ":" . $fieldcolname . ":" . $module . "_" . $fieldlabel1 . ":" . $fieldname . ":" . $fieldtypeofdata;
$this->adv_rel_fields[$fieldtypeofdata][] = '$' . $module . '#' . $fieldname . '$' . "::" . getTranslatedString($module, $module) . " " . $fieldlabel;
//added to escape attachments fields in Reports as we have multiple attachments
if ($module != 'HelpDesk' || $fieldname != 'filename') {
$module_columnlist[$optionvalue] = $fieldlabel;
}
}
$blockname = getBlockName($block);
if ($blockname == 'LBL_RELATED_PRODUCTS' && ($module == 'PurchaseOrder' || $module == 'SalesOrder' || $module == 'Quotes' || $module == 'Invoice')) {
$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' => 'I', 'discount' => 'I', 'quantity' => 'I', 'comment' => 'V');
foreach ($fields as $fieldcolname => $label) {
$fieldtypeofdata = $fields_datatype[$fieldcolname];
$optionvalue = $fieldtablename . ":" . $fieldcolname . ":" . $module . "_" . $label . ":" . $fieldcolname . ":" . $fieldtypeofdata;
$module_columnlist[$optionvalue] = $label;
}
}
$log->info("Reports :: FieldColumns->Successfully returned ColumnslistbyBlock" . $module . $block);
return $module_columnlist;
}
function getSMOwnerIDColumn($module, $relfieldid = "")
{
$adb = PearDatabase::getInstance();
$tabid = getTabid($module);
if ($module == 'Calendar') {
$tabid = array('9', '16');
}
$params = array($tabid);
$user_privileges_path = 'user_privileges/user_privileges_' . $this->current_user->id . '.php';
if (file_exists($user_privileges_path)) {
require $user_privileges_path;
}
$sql = "select * from vtiger_field where vtiger_field.tabid in (" . generateQuestionMarks($tabid) . ") and vtiger_field.displaytype in (1,2,3) and vtiger_field.presence in (0,2) AND columnname = 'smownerid'";
//fix for Ticket #4016
if ($module == "Calendar") {
$sql .= " group by vtiger_field.fieldlabel order by sequence";
} else {
$sql .= " order by sequence";
}
$result = $adb->pquery($sql, $params);
$noofrows = $adb->num_rows($result);
for ($i = 0; $i < $noofrows; $i++) {
$fieldid = $adb->query_result($result, $i, "fieldid");
// ITS4YOU-UP SlOl 1. 10. 2013 10:46:35
$fieldtablename = $adb->query_result($result, $i, "tablename") . $relfieldid;
$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];
//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';
}
$fieldlabel = $adb->query_result($result, $i, "fieldlabel");
$fieldlabel1 = $fieldlabel;
// this is defining module id for uitype 10
if ($relfieldid != "") {
$rel_field_row = $adb->fetchByAssoc($adb->pquery("SELECT uitype FROM vtiger_field WHERE fieldid = ? ", array($relfieldid)), 0);
$rel_field_uitype = $rel_field_row["uitype"];
if ($rel_field_uitype == 10) {
$relfieldid = getTabid($module) . ":" . $relfieldid;
}
}
$module_lbl = vtranslate($module, $module);
$optionvalue = $fieldtablename . ":" . $fieldcolname . ":" . $module . "_" . $fieldlabel1 . ":" . $fieldname . ":" . $fieldtypeofdata . ($relfieldid != "" ? ":" . $relfieldid : "");
// $optionvalue = $fieldtablename.":".$fieldcolname.":".$module."_".$fieldlabel1.":".$fieldname.":".$fieldtypeofdata;
$this->adv_rel_fields[$fieldtypeofdata][] = '$' . $module . '#' . $fieldname . '$' . "::" . $module_lbl . " " . $fieldlabel;
//added to escape attachments fields in Reports as we have multiple attachments
if ($module != 'HelpDesk' || $fieldname != 'filename') {
$module_columnlist[$optionvalue] = $fieldlabel;
}
// $module_lbl." ".
}
return $module_columnlist;
}
