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