/** Function to get column fields for a given module * @param $module -- module:: Type string * @returns $column_fld -- column field :: Type array * */ function getColumnFields($module) { global $log; $log->debug("Entering getColumnFields(" . $module . ") method ..."); $log->debug("in getColumnFields " . $module); // Lookup in cache for information $cachedModuleFields = VTCacheUtils::lookupFieldInfo_Module($module); if ($cachedModuleFields === false) { global $adb; $tabid = getTabid($module); if ($module == 'Calendar') { $tabid = array('9', '16'); } // To overcome invalid module names. if (empty($tabid)) { return array(); } // Let us pick up all the fields first so that we can cache information $sql = "SELECT tabid, fieldname, fieldid, fieldlabel, columnname, tablename, uitype, typeofdata, presence\n\t\tFROM vtiger_field WHERE tabid in (" . generateQuestionMarks($tabid) . ")"; $result = $adb->pquery($sql, array($tabid)); $noofrows = $adb->num_rows($result); if ($noofrows) { while ($resultrow = $adb->fetch_array($result)) { // Update information to cache for re-use VTCacheUtils::updateFieldInfo($resultrow['tabid'], $resultrow['fieldname'], $resultrow['fieldid'], $resultrow['fieldlabel'], $resultrow['columnname'], $resultrow['tablename'], $resultrow['uitype'], $resultrow['typeofdata'], $resultrow['presence']); } } // For consistency get information from cache $cachedModuleFields = VTCacheUtils::lookupFieldInfo_Module($module); } if ($module == 'Calendar') { $cachedEventsFields = VTCacheUtils::lookupFieldInfo_Module('Events'); if ($cachedEventsFields) { if (empty($cachedModuleFields)) { $cachedModuleFields = $cachedEventsFields; } else { $cachedModuleFields = array_merge($cachedModuleFields, $cachedEventsFields); } } } $column_fld = array(); if ($cachedModuleFields) { foreach ($cachedModuleFields as $fieldinfo) { $column_fld[$fieldinfo['fieldname']] = ''; } } $log->debug("Exiting getColumnFields method ..."); return $column_fld; }
/** * Retrieve record information of the module * @param <Integer> $record - crmid of record * @param <String> $module - module name */ function retrieve_entity_info($record, $module) { global $adb, $log, $app_strings; // INNER JOIN is desirable if all dependent table has entries for the record. // LEFT JOIN is desired if the dependent tables does not have entry. $join_type = 'LEFT JOIN'; // Tables which has multiple rows for the same record // will be skipped in record retrieve - need to be taken care separately. $multirow_tables = NULL; if (isset($this->multirow_tables)) { $multirow_tables = $this->multirow_tables; } else { $multirow_tables = array('vtiger_campaignrelstatus', 'vtiger_attachments', 'vtiger_email_track'); } // Lookup module field cache //SalesPlatform.ru begin fix new bug of calendar mass save. This bug added in 6.1 Repair is - code from 6.0 version $cachedModuleFields = VTCacheUtils::lookupFieldInfo_Module($module); // if($module == 'Calendar' || $module == 'Events') { // getColumnFields('Calendar'); // $cachedEventsFields = VTCacheUtils::lookupFieldInfo_Module('Events'); // $cachedCalendarFields = VTCacheUtils::lookupFieldInfo_Module('Calendar'); // $cachedModuleFields = array_merge($cachedEventsFields, $cachedCalendarFields); // } else { // $cachedModuleFields = VTCacheUtils::lookupFieldInfo_Module($module); // } //SalesPaltform.ru end if ($cachedModuleFields === false) { // Pull fields and cache for further use $tabid = getTabid($module); $sql0 = "SELECT fieldname, fieldid, fieldlabel, columnname, tablename, uitype, typeofdata,presence FROM vtiger_field WHERE tabid=?"; // NOTE: Need to skip in-active fields which we will be done later. $result0 = $adb->pquery($sql0, array($tabid)); if ($adb->num_rows($result0)) { while ($resultrow = $adb->fetch_array($result0)) { // Update cache VTCacheUtils::updateFieldInfo($tabid, $resultrow['fieldname'], $resultrow['fieldid'], $resultrow['fieldlabel'], $resultrow['columnname'], $resultrow['tablename'], $resultrow['uitype'], $resultrow['typeofdata'], $resultrow['presence']); } // Get only active field information $cachedModuleFields = VTCacheUtils::lookupFieldInfo_Module($module); } } if ($cachedModuleFields) { $column_clause = ''; $from_clause = ''; $where_clause = ''; $limit_clause = ' LIMIT 1'; // to eliminate multi-records due to table joins. $params = array(); $required_tables = $this->tab_name_index; // copies-on-write foreach ($cachedModuleFields as $fieldinfo) { if (in_array($fieldinfo['tablename'], $multirow_tables)) { continue; } // Added to avoid picking shipping tax fields for Inventory modules, the shipping tax detail are stored in vtiger_inventoryshippingrel // table, but in vtiger_field table we have set tablename as vtiger_inventoryproductrel. // SalesPlatform.ru begin Added Acts and Consignments if (($module == 'Invoice' || $module == 'Quotes' || $module == 'SalesOrder' || $module == 'PurchaseOrder' || $module == 'Act' || $module == 'Consignment') && stripos($fieldinfo['columnname'], 'shtax') !== false) { continue; } // Alias prefixed with tablename+fieldname to avoid duplicate column name across tables // fieldname are always assumed to be unique for a module $column_clause .= $fieldinfo['tablename'] . '.' . $fieldinfo['columnname'] . ' AS ' . $this->createColumnAliasForField($fieldinfo) . ','; } $column_clause .= 'vtiger_crmentity.deleted'; if (isset($required_tables['vtiger_crmentity'])) { $from_clause = ' vtiger_crmentity'; unset($required_tables['vtiger_crmentity']); foreach ($required_tables as $tablename => $tableindex) { if (in_array($tablename, $multirow_tables)) { // Avoid multirow table joins. continue; } $from_clause .= sprintf(' %s %s ON %s.%s=%s.%s', $join_type, $tablename, $tablename, $tableindex, 'vtiger_crmentity', 'crmid'); } } $where_clause .= ' vtiger_crmentity.crmid=?'; $params[] = $record; $sql = sprintf('SELECT %s FROM %s WHERE %s %s', $column_clause, $from_clause, $where_clause, $limit_clause); $result = $adb->pquery($sql, $params); if (!$result || $adb->num_rows($result) < 1) { throw new Exception($app_strings['LBL_RECORD_NOT_FOUND'], -1); } else { $resultrow = $adb->query_result_rowdata($result); if (!empty($resultrow['deleted'])) { throw new Exception($app_strings['LBL_RECORD_DELETE'], 1); } foreach ($cachedModuleFields as $fieldinfo) { $fieldvalue = ''; $fieldkey = $this->createColumnAliasForField($fieldinfo); //Note : value is retrieved with a tablename+fieldname as we are using alias while building query if (isset($resultrow[$fieldkey])) { $fieldvalue = $resultrow[$fieldkey]; } $this->column_fields[$fieldinfo['fieldname']] = $fieldvalue; } } } $this->column_fields['record_id'] = $record; $this->column_fields['record_module'] = $module; }
/** * Function to get the fieldid * * @param Integer $tabid * @param Boolean $onlyactive */ function getFieldid($tabid, $fieldname, $onlyactive = true) { global $adb; // Look up information at cache first $fieldinfo = VTCacheUtils::lookupFieldInfo($tabid, $fieldname); if ($fieldinfo === false) { $query = "SELECT fieldid, fieldlabel, columnname, tablename, uitype, typeofdata, presence \n\t\t\tFROM vtiger_field WHERE tabid=? AND fieldname=?"; $result = $adb->pquery($query, array($tabid, $fieldname)); if ($adb->num_rows($result)) { $resultrow = $adb->fetch_array($result); // Update information to cache for re-use VTCacheUtils::updateFieldInfo($tabid, $fieldname, $resultrow['fieldid'], $resultrow['fieldlabel'], $resultrow['columnname'], $resultrow['tablename'], $resultrow['uitype'], $resultrow['typeofdata'], $resultrow['presence']); $fieldinfo = VTCacheUtils::lookupFieldInfo($tabid, $fieldname); } } // Get the field id based on required criteria $fieldid = false; if ($fieldinfo) { $fieldid = $fieldinfo['fieldid']; if ($onlyactive && !in_array($fieldinfo['presence'], array('0', '2'))) { $fieldid = false; } } return $fieldid; }
/** Function to retrive the information of the given recordid ,module * @param $record -- Id:: Type Integer * @param $module -- module:: Type varchar * This function retrives the information from the database and sets the value in the class columnfields array */ function retrieve_entity_info($record, $module) { global $adb, $log, $app_strings; $result = array(); foreach ($this->tab_name_index as $table_name => $index) { $result[$table_name] = $adb->pquery("select * from {$table_name} where {$index}=?", array($record)); $isRecordDeleted = $adb->query_result($result["vtiger_crmentity"], 0, "deleted"); if ($isRecordDeleted !== 0 && $isRecordDeleted !== '0') { die("<br><br><center>" . $app_strings['LBL_RECORD_DELETE'] . " <a href='javascript:window.history.back()'>" . $app_strings['LBL_GO_BACK'] . ".</a></center>"); } } /* Prasad: Fix for ticket #4595 */ if (isset($this->table_name)) { $mod_index_col = $this->tab_name_index[$this->table_name]; if ($adb->query_result($result[$this->table_name], 0, $mod_index_col) == '') { die("<br><br><center>" . $app_strings['LBL_RECORD_NOT_FOUND'] . ". <a href='javascript:window.history.back()'>" . $app_strings['LBL_GO_BACK'] . ".</a></center>"); } } // Lookup in cache for information $cachedModuleFields = VTCacheUtils::lookupFieldInfo_Module($module); if ($cachedModuleFields === false) { $tabid = getTabid($module); // Let us pick up all the fields first so that we can cache information $sql1 = "SELECT fieldname, fieldid, fieldlabel, columnname, tablename, uitype, typeofdata, presence\n \tFROM vtiger_field WHERE tabid=?"; // NOTE: Need to skip in-active fields which we will be done later. $result1 = $adb->pquery($sql1, array($tabid)); $noofrows = $adb->num_rows($result1); if ($noofrows) { while ($resultrow = $adb->fetch_array($result1)) { // Update information to cache for re-use VTCacheUtils::updateFieldInfo($tabid, $resultrow['fieldname'], $resultrow['fieldid'], $resultrow['fieldlabel'], $resultrow['columnname'], $resultrow['tablename'], $resultrow['uitype'], $resultrow['typeofdata'], $resultrow['presence']); } } // Get only active field information $cachedModuleFields = VTCacheUtils::lookupFieldInfo_Module($module); } if ($cachedModuleFields) { foreach ($cachedModuleFields as $fieldname => $fieldinfo) { $fieldcolname = $fieldinfo['columnname']; $tablename = $fieldinfo['tablename']; $fieldname = $fieldinfo['fieldname']; // To avoid ADODB execption pick the entries that are in $tablename // (ex. when we don't have attachment for troubletickets, $result[vtiger_attachments] // will not be set so here we should not retrieve) if (isset($result[$tablename])) { $fld_value = $adb->query_result($result[$tablename], 0, $fieldcolname); } else { $adb->println("There is no entry for this entity {$record} ({$module}) in the table {$tablename}"); $fld_value = ""; } $this->column_fields[$fieldname] = $fld_value; } } if ($module == 'Users') { for ($i = 0; $i < $noofrows; $i++) { $fieldcolname = $adb->query_result($result1, $i, "columnname"); $tablename = $adb->query_result($result1, $i, "tablename"); $fieldname = $adb->query_result($result1, $i, "fieldname"); $fld_value = $adb->query_result($result[$tablename], 0, $fieldcolname); $this->{$fieldname} = $fld_value; } } $this->column_fields["record_id"] = $record; $this->column_fields["record_module"] = $module; }
/** * Retrieve record information of the module * @param <Integer> $record - crmid of record * @param <String> $module - module name */ function retrieve_entity_info($record, $module) { $adb = PearDatabase::getInstance(); $log = vglobal('log'); $app_strings = vglobal('app_strings'); if (!isset($record)) { throw new NoPermittedToRecordException('LBL_RECORD_NOT_FOUND'); } // INNER JOIN is desirable if all dependent table has entries for the record. // LEFT JOIN is desired if the dependent tables does not have entry. $join_type = 'LEFT JOIN'; // Tables which has multiple rows for the same record // will be skipped in record retrieve - need to be taken care separately. $multirow_tables = NULL; if (isset($this->multirow_tables)) { $multirow_tables = $this->multirow_tables; } else { $multirow_tables = array('vtiger_campaignrelstatus', 'vtiger_attachments', 'vtiger_email_track'); } // Lookup module field cache if ($module == 'Calendar' || $module == 'Events') { getColumnFields('Calendar'); if (VTCacheUtils::lookupFieldInfo_Module('Events')) { $cachedEventsFields = VTCacheUtils::lookupFieldInfo_Module('Events'); } else { $cachedEventsFields = array(); } $cachedCalendarFields = VTCacheUtils::lookupFieldInfo_Module('Calendar'); $cachedModuleFields = array_merge($cachedEventsFields, $cachedCalendarFields); $module = 'Calendar'; } else { $cachedModuleFields = VTCacheUtils::lookupFieldInfo_Module($module); } if ($cachedModuleFields === false) { // Pull fields and cache for further use $tabid = getTabid($module); $sql0 = "SELECT fieldname, fieldid, fieldlabel, columnname, tablename, uitype, typeofdata,presence FROM vtiger_field WHERE tabid=?"; // NOTE: Need to skip in-active fields which we will be done later. $result0 = $adb->pquery($sql0, array($tabid)); if ($adb->num_rows($result0)) { while ($resultrow = $adb->fetch_array($result0)) { // Update cache VTCacheUtils::updateFieldInfo($tabid, $resultrow['fieldname'], $resultrow['fieldid'], $resultrow['fieldlabel'], $resultrow['columnname'], $resultrow['tablename'], $resultrow['uitype'], $resultrow['typeofdata'], $resultrow['presence']); } // Get only active field information $cachedModuleFields = VTCacheUtils::lookupFieldInfo_Module($module); } } if ($cachedModuleFields) { $column_clause = ''; $from_clause = ''; $where_clause = ''; $limit_clause = ' LIMIT 1'; // to eliminate multi-records due to table joins. $params = array(); $required_tables = $this->tab_name_index; // copies-on-write foreach ($cachedModuleFields as $fieldinfo) { if (in_array($fieldinfo['tablename'], $multirow_tables)) { continue; } // Alias prefixed with tablename+fieldname to avoid duplicate column name across tables // fieldname are always assumed to be unique for a module $column_clause .= $fieldinfo['tablename'] . '.' . $fieldinfo['columnname'] . ' AS ' . $this->createColumnAliasForField($fieldinfo) . ','; } $column_clause .= 'vtiger_crmentity.deleted'; if (isset($required_tables['vtiger_crmentity'])) { $from_clause = ' vtiger_crmentity'; unset($required_tables['vtiger_crmentity']); foreach ($required_tables as $tablename => $tableindex) { if (in_array($tablename, $multirow_tables)) { // Avoid multirow table joins. continue; } $from_clause .= sprintf(' %s %s ON %s.%s=%s.%s', $join_type, $tablename, $tablename, $tableindex, 'vtiger_crmentity', 'crmid'); } } $where_clause .= ' vtiger_crmentity.crmid = ? '; $params[] = $record; if ($module != '') { $where_clause .= ' AND vtiger_crmentity.setype = ?'; $params[] = $module; } $sql = sprintf('SELECT %s FROM %s WHERE %s %s', $column_clause, $from_clause, $where_clause, $limit_clause); $result = $adb->pquery($sql, $params); if (!$result || $adb->num_rows($result) < 1) { throw new NoPermittedToRecordException('LBL_RECORD_NOT_FOUND'); } else { $resultrow = $adb->query_result_rowdata($result); if (!empty($resultrow['deleted'])) { throw new NoPermittedToRecordException('LBL_RECORD_DELETE'); } foreach ($cachedModuleFields as $fieldinfo) { $fieldvalue = ''; $fieldkey = $this->createColumnAliasForField($fieldinfo); //Note : value is retrieved with a tablename+fieldname as we are using alias while building query if (isset($resultrow[$fieldkey])) { $fieldvalue = $resultrow[$fieldkey]; } if (in_array($fieldinfo['uitype'], array('10', '51', '73'))) { $this->column_fields[$fieldinfo['fieldname'] . '_label'] = Vtiger_Functions::getCRMRecordLabel($fieldvalue); } $this->column_fields[$fieldinfo['fieldname']] = $fieldvalue; } } } $this->column_fields['record_id'] = $record; $this->column_fields['record_module'] = $module; }