public function getFromClause()
{
if (!empty($this->query) || !empty($this->fromClause)) {
return $this->fromClause;
}
$moduleFields = $this->meta->getModuleFields();
$tableList = array();
$tableJoinMapping = array();
$tableJoinCondition = array();
foreach ($this->fields as $fieldName) {
if ($fieldName == 'id') {
continue;
}
$field = $moduleFields[$fieldName];
$baseTable = $field->getTableName();
$tableIndexList = $this->meta->getEntityTableIndexList();
$baseTableIndex = $tableIndexList[$baseTable];
if ($field->getFieldDataType() == 'reference') {
$moduleList = $this->referenceFieldInfoList[$fieldName];
$tableJoinMapping[$field->getTableName()] = 'INNER JOIN';
foreach ($moduleList as $module) {
if ($module == 'Users') {
$tableJoinCondition[$fieldName]['vtiger_users'] = $field->getTableName() . "." . $field->getColumnName() . " = vtiger_users.id";
$tableJoinCondition[$fieldName]['vtiger_groups'] = $field->getTableName() . "." . $field->getColumnName() . " = vtiger_groups.groupid";
$tableJoinMapping['vtiger_users'] = 'LEFT JOIN';
$tableJoinMapping['vtiger_groups'] = 'LEFT JOIN';
}
}
} elseif ($field->getFieldDataType() == 'owner') {
$tableList['vtiger_users'] = 'vtiger_users';
$tableList['vtiger_groups'] = 'vtiger_groups';
$tableJoinMapping['vtiger_users'] = 'LEFT JOIN';
$tableJoinMapping['vtiger_groups'] = 'LEFT JOIN';
}
$tableList[$field->getTableName()] = $field->getTableName();
$tableJoinMapping[$field->getTableName()] = $this->meta->getJoinClause($field->getTableName());
}
$baseTable = $this->meta->getEntityBaseTable();
$moduleTableIndexList = $this->meta->getEntityTableIndexList();
$baseTableIndex = $moduleTableIndexList[$baseTable];
foreach ($this->whereFields as $fieldName) {
if (empty($fieldName)) {
continue;
}
$field = $moduleFields[$fieldName];
if (empty($field)) {
// not accessible field.
continue;
}
$baseTable = $field->getTableName();
// When a field is included in Where Clause, but not is Select Clause, and the field table is not base table,
// The table will not be present in tablesList and hence needs to be added to the list.
if (empty($tableList[$baseTable])) {
$tableList[$baseTable] = $field->getTableName();
$tableJoinMapping[$baseTable] = $this->meta->getJoinClause($field->getTableName());
}
if ($field->getFieldDataType() == 'reference') {
$moduleList = $this->referenceFieldInfoList[$fieldName];
$tableJoinMapping[$field->getTableName()] = 'INNER JOIN';
foreach ($moduleList as $module) {
$meta = $this->getMeta($module);
$nameFields = $this->moduleNameFields[$module];
$nameFieldList = explode(',', $nameFields);
foreach ($nameFieldList as $index => $column) {
// for non admin user users module is inaccessible.
// so need hard code the tablename.
if ($module == 'Users') {
$instance = CRMEntity::getInstance($module);
$referenceTable = $instance->table_name;
$tableIndexList = $instance->tab_name_index;
$referenceTableIndex = $tableIndexList[$referenceTable];
} else {
$referenceField = $meta->getFieldByColumnName($column);
$referenceTable = $referenceField->getTableName();
$tableIndexList = $meta->getEntityTableIndexList();
$referenceTableIndex = $tableIndexList[$referenceTable];
}
if (isset($moduleTableIndexList[$referenceTable])) {
$referenceTableName = "{$referenceTable} {$referenceTable}{$fieldName}";
$referenceTable = "{$referenceTable}{$fieldName}";
} else {
$referenceTableName = $referenceTable;
}
//should always be left join for cases where we are checking for null
//reference field values.
$tableJoinMapping[$referenceTableName] = 'LEFT JOIN';
$tableJoinCondition[$fieldName][$referenceTableName] = $baseTable . '.' . $field->getColumnName() . ' = ' . $referenceTable . '.' . $referenceTableIndex;
}
}
} elseif ($field->getFieldDataType() == 'owner') {
$tableList['vtiger_users'] = 'vtiger_users';
$tableList['vtiger_groups'] = 'vtiger_groups';
$tableJoinMapping['vtiger_users'] = 'LEFT JOIN';
$tableJoinMapping['vtiger_groups'] = 'LEFT JOIN';
} else {
$tableList[$field->getTableName()] = $field->getTableName();
$tableJoinMapping[$field->getTableName()] = $this->meta->getJoinClause($field->getTableName());
}
}
$defaultTableList = $this->meta->getEntityDefaultTableList();
foreach ($defaultTableList as $table) {
if (!in_array($table, $tableList)) {
$tableList[$table] = $table;
$tableJoinMapping[$table] = 'INNER JOIN';
}
}
$ownerFields = $this->meta->getOwnerFields();
if (count($ownerFields) > 0) {
$ownerField = $ownerFields[0];
}
$baseTable = $this->meta->getEntityBaseTable();
$sql = " FROM {$baseTable} ";
unset($tableList[$baseTable]);
foreach ($defaultTableList as $tableName) {
$sql .= " {$tableJoinMapping[$tableName]} {$tableName} ON {$baseTable}." . "{$baseTableIndex} = {$tableName}.{$moduleTableIndexList[$tableName]}";
unset($tableList[$tableName]);
}
foreach ($tableList as $tableName) {
if ($tableName == 'vtiger_users') {
$field = $moduleFields[$ownerField];
$sql .= " {$tableJoinMapping[$tableName]} {$tableName} ON " . $field->getTableName() . "." . $field->getColumnName() . " = {$tableName}.id";
} elseif ($tableName == 'vtiger_groups') {
$field = $moduleFields[$ownerField];
$sql .= " {$tableJoinMapping[$tableName]} {$tableName} ON " . $field->getTableName() . "." . $field->getColumnName() . " = {$tableName}.groupid";
} else {
$sql .= " {$tableJoinMapping[$tableName]} {$tableName} ON {$baseTable}." . "{$baseTableIndex} = {$tableName}.{$moduleTableIndexList[$tableName]}";
}
}
if ($this->meta->getTabName() == 'Documents') {
$tableJoinCondition['folderid'] = array('vtiger_attachmentsfolder' => "{$baseTable}.folderid = vtiger_attachmentsfolder.folderid");
$tableJoinMapping['vtiger_attachmentsfolder'] = 'INNER JOIN';
}
foreach ($tableJoinCondition as $fieldName => $conditionInfo) {
foreach ($conditionInfo as $tableName => $condition) {
if (!empty($tableList[$tableName])) {
$tableNameAlias = $tableName . '2';
$condition = str_replace($tableName, $tableNameAlias, $condition);
} else {
$tableNameAlias = '';
}
$sql .= " {$tableJoinMapping[$tableName]} {$tableName} {$tableNameAlias} ON {$condition}";
}
}
foreach ($this->manyToManyRelatedModuleConditions as $conditionInfo) {
$relatedModuleMeta = RelatedModuleMeta::getInstance($this->meta->getTabName(), $conditionInfo['relatedModule']);
$relationInfo = $relatedModuleMeta->getRelationMeta();
$relatedModule = $this->meta->getTabName();
$sql .= ' INNER JOIN ' . $relationInfo['relationTable'] . " ON " . $relationInfo['relationTable'] . ".{$relationInfo[$relatedModule]}=" . "{$baseTable}.{$baseTableIndex}";
}
$sql .= $this->meta->getEntityAccessControlQuery();
$this->fromClause = $sql;
return $sql;
}
public function getFromClause()
{
global $current_user;
if (!empty($this->query) || !empty($this->fromClause)) {
return $this->fromClause;
}
$baseModule = $this->getModule();
$moduleFields = $this->getModuleFields();
$tableList = array();
$tableJoinMapping = array();
$tableJoinCondition = array();
$i = 1;
$moduleTableIndexList = $this->meta->getEntityTableIndexList();
foreach ($this->fields as $fieldName) {
if ($fieldName == 'id') {
continue;
}
$field = $moduleFields[$fieldName];
$baseTable = $field->getTableName();
$tableIndexList = $this->meta->getEntityTableIndexList();
$baseTableIndex = $tableIndexList[$baseTable];
if ($field->getFieldDataType() == 'reference') {
$moduleList = $this->referenceFieldInfoList[$fieldName];
$tableJoinMapping[$field->getTableName()] = 'INNER JOIN';
foreach ($moduleList as $module) {
if ($module == 'Users' && $baseModule != 'Users') {
$tableJoinCondition[$fieldName]['vtiger_users' . $fieldName] = $field->getTableName() . "." . $field->getColumnName() . " = vtiger_users" . $fieldName . ".id";
$tableJoinCondition[$fieldName]['vtiger_groups' . $fieldName] = $field->getTableName() . "." . $field->getColumnName() . " = vtiger_groups" . $fieldName . ".groupid";
$tableJoinMapping['vtiger_users' . $fieldName] = 'LEFT JOIN vtiger_users AS';
$tableJoinMapping['vtiger_groups' . $fieldName] = 'LEFT JOIN vtiger_groups AS';
$i++;
}
}
} elseif ($field->getFieldDataType() == 'owner') {
$tableList['vtiger_users'] = 'vtiger_users';
$tableList['vtiger_groups'] = 'vtiger_groups';
$tableJoinMapping['vtiger_users'] = 'LEFT JOIN';
$tableJoinMapping['vtiger_groups'] = 'LEFT JOIN';
if ($fieldName == "created_user_id") {
$tableJoinCondition[$fieldName]['vtiger_users' . $fieldName] = $field->getTableName() . "." . $field->getColumnName() . " = vtiger_users" . $fieldName . ".id";
$tableJoinCondition[$fieldName]['vtiger_groups' . $fieldName] = $field->getTableName() . "." . $field->getColumnName() . " = vtiger_groups" . $fieldName . ".groupid";
$tableJoinMapping['vtiger_users' . $fieldName] = 'LEFT JOIN vtiger_users AS';
$tableJoinMapping['vtiger_groups' . $fieldName] = 'LEFT JOIN vtiger_groups AS';
}
}
$tableList[$field->getTableName()] = $field->getTableName();
$tableJoinMapping[$field->getTableName()] = $this->meta->getJoinClause($field->getTableName());
}
$baseTable = $this->meta->getEntityBaseTable();
$baseTableIndex = $moduleTableIndexList[$baseTable];
foreach ($this->whereFields as $fieldName) {
if (empty($fieldName)) {
continue;
}
$field = $moduleFields[$fieldName];
if (empty($field)) {
// not accessible field.
continue;
}
$baseTable = $field->getTableName();
// When a field is included in Where Clause, but not is Select Clause, and the field table is not base table,
// The table will not be present in tablesList and hence needs to be added to the list.
if (empty($tableList[$baseTable])) {
$tableList[$baseTable] = $field->getTableName();
$tableJoinMapping[$baseTable] = $this->meta->getJoinClause($field->getTableName());
}
if ($field->getFieldDataType() == 'reference') {
$moduleList = $this->referenceFieldInfoList[$fieldName];
// This is special condition as the data is not stored in the base table,
// If empty search is performed on this field then it fails to retrieve any information.
if ($fieldName == 'parent_id' && $field->getTableName() == 'vtiger_seactivityrel') {
$tableJoinMapping[$field->getTableName()] = 'LEFT JOIN';
} else {
if ($fieldName == 'contact_id' && $field->getTableName() == 'vtiger_cntactivityrel') {
$tableJoinMapping[$field->getTableName()] = "LEFT JOIN";
} else {
$tableJoinMapping[$field->getTableName()] = 'INNER JOIN';
}
}
foreach ($moduleList as $module) {
$meta = $this->getMeta($module);
$nameFields = $this->moduleNameFields[$module];
$nameFieldList = explode(',', $nameFields);
foreach ($nameFieldList as $index => $column) {
$referenceField = $meta->getFieldByColumnName($column);
$referenceTable = $referenceField->getTableName();
$tableIndexList = $meta->getEntityTableIndexList();
$referenceTableIndex = $tableIndexList[$referenceTable];
$referenceTableName = "{$referenceTable} {$referenceTable}{$fieldName}";
$referenceTable = "{$referenceTable}{$fieldName}";
//should always be left join for cases where we are checking for null
//reference field values.
if (!array_key_exists($referenceTable, $tableJoinMapping)) {
// table already added in from clause
$tableJoinMapping[$referenceTableName] = 'LEFT JOIN';
$tableJoinCondition[$fieldName][$referenceTableName] = $baseTable . '.' . $field->getColumnName() . ' = ' . $referenceTable . '.' . $referenceTableIndex;
}
}
}
} elseif ($field->getFieldDataType() == 'owner') {
$tableList['vtiger_users'] = 'vtiger_users';
$tableList['vtiger_groups'] = 'vtiger_groups';
$tableJoinMapping['vtiger_users'] = 'LEFT JOIN';
$tableJoinMapping['vtiger_groups'] = 'LEFT JOIN';
} else {
$tableList[$field->getTableName()] = $field->getTableName();
$tableJoinMapping[$field->getTableName()] = $this->meta->getJoinClause($field->getTableName());
}
}
$defaultTableList = $this->meta->getEntityDefaultTableList();
foreach ($defaultTableList as $table) {
if (!in_array($table, $tableList)) {
$tableList[$table] = $table;
$tableJoinMapping[$table] = 'INNER JOIN';
}
}
$ownerFields = $this->meta->getOwnerFields();
if (count($ownerFields) > 0) {
$ownerField = $ownerFields[0];
}
$baseTable = $this->meta->getEntityBaseTable();
$sql = " FROM {$baseTable} ";
unset($tableList[$baseTable]);
foreach ($defaultTableList as $tableName) {
$sql .= " {$tableJoinMapping[$tableName]} {$tableName} ON {$baseTable}." . "{$baseTableIndex} = {$tableName}.{$moduleTableIndexList[$tableName]}";
unset($tableList[$tableName]);
}
foreach ($tableList as $tableName) {
if ($tableName == 'vtiger_users') {
$field = $moduleFields[$ownerField];
$sql .= " {$tableJoinMapping[$tableName]} {$tableName} ON " . $field->getTableName() . "." . $field->getColumnName() . " = {$tableName}.id";
} elseif ($tableName == 'vtiger_groups') {
$field = $moduleFields[$ownerField];
$sql .= " {$tableJoinMapping[$tableName]} {$tableName} ON " . $field->getTableName() . "." . $field->getColumnName() . " = {$tableName}.groupid";
} else {
$sql .= " {$tableJoinMapping[$tableName]} {$tableName} ON {$baseTable}." . "{$baseTableIndex} = {$tableName}.{$moduleTableIndexList[$tableName]}";
}
}
if ($this->meta->getTabName() == 'Documents') {
$tableJoinCondition['folderid'] = array('vtiger_attachmentsfolderfolderid' => "{$baseTable}.folderid = vtiger_attachmentsfolderfolderid.folderid");
$tableJoinMapping['vtiger_attachmentsfolderfolderid'] = 'INNER JOIN vtiger_attachmentsfolder';
}
foreach ($tableJoinCondition as $fieldName => $conditionInfo) {
foreach ($conditionInfo as $tableName => $condition) {
if (!empty($tableList[$tableName])) {
$tableNameAlias = $tableName . '2';
$condition = str_replace($tableName, $tableNameAlias, $condition);
} else {
$tableNameAlias = '';
}
$sql .= " {$tableJoinMapping[$tableName]} {$tableName} {$tableNameAlias} ON {$condition}";
}
}
foreach ($this->manyToManyRelatedModuleConditions as $conditionInfo) {
$relatedModuleMeta = RelatedModuleMeta::getInstance($this->meta->getTabName(), $conditionInfo['relatedModule']);
$relationInfo = $relatedModuleMeta->getRelationMeta();
$relatedModule = $this->meta->getTabName();
$sql .= ' INNER JOIN ' . $relationInfo['relationTable'] . " ON " . $relationInfo['relationTable'] . ".{$relationInfo[$relatedModule]}=" . "{$baseTable}.{$baseTableIndex}";
}
// Adding support for conditions on reference module fields
if ($this->referenceModuleField) {
$referenceFieldTableList = array();
foreach ($this->referenceModuleField as $index => $conditionInfo) {
$handler = vtws_getModuleHandlerFromName($conditionInfo['relatedModule'], $current_user);
$meta = $handler->getMeta();
$tableList = $meta->getEntityTableIndexList();
$fieldName = $conditionInfo['fieldName'];
$referenceFieldObject = $moduleFields[$conditionInfo['referenceField']];
$fields = $meta->getModuleFields();
$fieldObject = $fields[$fieldName];
if (empty($fieldObject)) {
continue;
}
$tableName = $fieldObject->getTableName();
if (!in_array($tableName, $referenceFieldTableList)) {
if ($referenceFieldObject->getFieldName() == 'parent_id' && ($this->getModule() == 'Calendar' || $this->getModule() == 'Events')) {
$sql .= ' LEFT JOIN vtiger_seactivityrel ON vtiger_seactivityrel.activityid = vtiger_activity.activityid ';
}
//TODO : this will create duplicates, need to find a better way
if ($referenceFieldObject->getFieldName() == 'contact_id' && ($this->getModule() == 'Calendar' || $this->getModule() == 'Events')) {
$sql .= ' LEFT JOIN vtiger_cntactivityrel ON vtiger_cntactivityrel.activityid = vtiger_activity.activityid ';
}
$sql .= " LEFT JOIN " . $tableName . ' AS ' . $tableName . $conditionInfo['referenceField'] . ' ON
' . $tableName . $conditionInfo['referenceField'] . '.' . $tableList[$tableName] . '=' . $referenceFieldObject->getTableName() . '.' . $referenceFieldObject->getColumnName();
$referenceFieldTableList[] = $tableName;
}
}
}
$sql .= $this->meta->getEntityAccessControlQuery();
$this->fromClause = $sql;
return $sql;
}
public function getFromClause()
{
global $current_user;
if (!empty($this->query) || !empty($this->fromClause)) {
return $this->fromClause;
}
$baseModule = $this->getModule();
$moduleFields = $this->getModuleFields();
$tableList = array();
$tableJoinMapping = array();
$tableJoinCondition = array();
$moduleTableIndexList = $this->meta->getEntityTableIndexList();
foreach ($this->fields as $fieldName) {
if ($fieldName == 'id' or empty($moduleFields[$fieldName])) {
continue;
}
$field = $moduleFields[$fieldName];
$baseTable = $field->getTableName();
$tableIndexList = $this->meta->getEntityTableIndexList();
$baseTableIndex = $tableIndexList[$baseTable];
if ($field->getFieldDataType() == 'reference') {
$moduleList = $this->referenceFieldInfoList[$fieldName];
$tableJoinMapping[$field->getTableName()] = 'INNER JOIN';
foreach ($moduleList as $module) {
if ($module == 'Users' && $baseModule != 'Users') {
$tableJoinCondition[$fieldName]['vtiger_users' . $fieldName] = $field->getTableName() . "." . $field->getColumnName() . " = vtiger_users" . $fieldName . ".id";
$tableJoinCondition[$fieldName]['vtiger_groups' . $fieldName] = $field->getTableName() . "." . $field->getColumnName() . " = vtiger_groups" . $fieldName . ".groupid";
$tableJoinMapping['vtiger_users' . $fieldName] = 'LEFT JOIN vtiger_users AS';
$tableJoinMapping['vtiger_groups' . $fieldName] = 'LEFT JOIN vtiger_groups AS';
}
}
} elseif ($field->getFieldDataType() == 'owner') {
$tableList['vtiger_users'] = 'vtiger_users';
$tableList['vtiger_groups'] = 'vtiger_groups';
$tableJoinMapping['vtiger_users'] = 'LEFT JOIN';
$tableJoinMapping['vtiger_groups'] = 'LEFT JOIN';
}
$tableList[$field->getTableName()] = $field->getTableName();
$tableJoinMapping[$field->getTableName()] = $this->meta->getJoinClause($field->getTableName());
}
$baseTable = $this->meta->getEntityBaseTable();
$moduleTableIndexList = $this->meta->getEntityTableIndexList();
$baseTableIndex = $moduleTableIndexList[$baseTable];
foreach ($this->whereFields as $fieldName) {
if (empty($fieldName)) {
continue;
}
if (empty($moduleFields[$fieldName])) {
// not accessible field.
continue;
}
$field = $moduleFields[$fieldName];
$baseTable = $field->getTableName();
// When a field is included in Where Clause, but not in Select Clause, and the field table is not base table,
// The table will not be present in tablesList and hence needs to be added to the list.
if (empty($tableList[$baseTable])) {
$tableList[$baseTable] = $field->getTableName();
$tableJoinMapping[$baseTable] = $this->meta->getJoinClause($field->getTableName());
}
if ($field->getFieldDataType() == 'reference') {
$moduleList = $this->referenceFieldInfoList[$fieldName];
// This is special condition as the data is not stored in the base table,
// If empty search is performed on this field then it fails to retrieve any information.
if ($fieldName == 'parent_id' && $field->getTableName() == 'vtiger_seactivityrel') {
$tableJoinMapping[$field->getTableName()] = 'LEFT JOIN';
} else {
if ($fieldName == 'contact_id' && $field->getTableName() == 'vtiger_cntactivityrel') {
$tableJoinMapping[$field->getTableName()] = "LEFT JOIN";
} else {
$tableJoinMapping[$field->getTableName()] = 'INNER JOIN';
}
}
foreach ($moduleList as $module) {
$meta = $this->getMeta($module);
$nameFields = $this->moduleNameFields[$module];
$nameFieldList = explode(',', $nameFields);
foreach ($nameFieldList as $index => $column) {
$joinas = 'LEFT JOIN';
// for non admin user users module is inaccessible.
// so need hard code the tablename.
if ($module == 'Users' && $baseModule != 'Users') {
$referenceTable = 'vtiger_users' . $fieldName;
$referenceTableIndex = 'id';
$joinas = 'LEFT JOIN vtiger_users AS';
} else {
$referenceField = $meta->getFieldByColumnName($column);
if (!$referenceField) {
continue;
}
$referenceTable = $referenceField->getTableName();
$tableIndexList = $meta->getEntityTableIndexList();
$referenceTableIndex = $tableIndexList[$referenceTable];
}
if (isset($moduleTableIndexList[$referenceTable])) {
$referenceTableName = "{$referenceTable} {$referenceTable}{$fieldName}";
$referenceTable = "{$referenceTable}{$fieldName}";
} else {
$referenceTableName = $referenceTable;
}
//should always be left join for cases where we are checking for null
//reference field values.
if (!array_key_exists($referenceTable, $tableJoinMapping)) {
// table already added in from clause
$tableJoinMapping[$referenceTableName] = $joinas;
$tableJoinCondition[$fieldName][$referenceTableName] = $baseTable . '.' . $field->getColumnName() . ' = ' . $referenceTable . '.' . $referenceTableIndex;
}
}
}
} elseif ($field->getFieldDataType() == 'owner') {
$tableList['vtiger_users'] = 'vtiger_users';
$tableList['vtiger_groups'] = 'vtiger_groups';
$tableJoinMapping['vtiger_users'] = 'LEFT JOIN';
$tableJoinMapping['vtiger_groups'] = 'LEFT JOIN';
} else {
$tableList[$field->getTableName()] = $field->getTableName();
$tableJoinMapping[$field->getTableName()] = $this->meta->getJoinClause($field->getTableName());
}
}
$defaultTableList = $this->meta->getEntityDefaultTableList();
foreach ($defaultTableList as $table) {
if (!in_array($table, $tableList)) {
$tableList[$table] = $table;
$tableJoinMapping[$table] = 'INNER JOIN';
}
}
$ownerFields = $this->meta->getOwnerFields();
if (count($ownerFields) > 0) {
$ownerField = $ownerFields[0];
}
$baseTable = $this->meta->getEntityBaseTable();
$sql = " FROM {$baseTable} ";
unset($tableList[$baseTable]);
foreach ($defaultTableList as $tableName) {
$sql .= " {$tableJoinMapping[$tableName]} {$tableName} ON {$baseTable}." . "{$baseTableIndex} = {$tableName}.{$moduleTableIndexList[$tableName]}";
unset($tableList[$tableName]);
}
foreach ($tableList as $tableName) {
if ($tableName == 'vtiger_users') {
$field = $moduleFields[$ownerField];
$sql .= " {$tableJoinMapping[$tableName]} {$tableName} ON " . $field->getTableName() . "." . $field->getColumnName() . " = {$tableName}.id";
} elseif ($tableName == 'vtiger_groups') {
$field = $moduleFields[$ownerField];
$sql .= " {$tableJoinMapping[$tableName]} {$tableName} ON " . $field->getTableName() . "." . $field->getColumnName() . " = {$tableName}.groupid";
} else {
$sql .= " {$tableJoinMapping[$tableName]} {$tableName} ON {$baseTable}." . "{$baseTableIndex} = {$tableName}.{$moduleTableIndexList[$tableName]}";
}
}
if ($this->meta->getTabName() == 'Documents') {
$tableJoinCondition['folderid'] = array('vtiger_attachmentsfolder' => "{$baseTable}.folderid = vtiger_attachmentsfolder.folderid");
$tableJoinMapping['vtiger_attachmentsfolder'] = 'INNER JOIN';
}
$alias_count = 2;
foreach ($tableJoinCondition as $fieldName => $conditionInfo) {
foreach ($conditionInfo as $tableName => $condition) {
if (!empty($tableList[$tableName])) {
$tableNameAlias = $tableName . $alias_count;
$alias_count++;
$condition = str_replace($tableName, $tableNameAlias, $condition);
} else {
$tableNameAlias = '';
}
$sql .= " {$tableJoinMapping[$tableName]} {$tableName} {$tableNameAlias} ON {$condition}";
}
}
foreach ($this->manyToManyRelatedModuleConditions as $conditionInfo) {
$relatedModuleMeta = RelatedModuleMeta::getInstance($this->meta->getTabName(), $conditionInfo['relatedModule']);
$relationInfo = $relatedModuleMeta->getRelationMeta();
$relatedModule = $this->meta->getTabName();
$sql .= ' INNER JOIN ' . $relationInfo['relationTable'] . " ON " . $relationInfo['relationTable'] . ".{$relationInfo[$relatedModule]}={$baseTable}.{$baseTableIndex}";
}
// Adding support for conditions on reference module fields
if (count($this->referenceFieldInfoList) > 0) {
$alreadyinfrom = array_keys($tableJoinMapping);
$alreadyinfrom[] = $baseTable;
$referenceFieldTableList = array();
if (isset($this->referenceModuleField) and is_array($this->referenceModuleField)) {
foreach ($this->referenceModuleField as $index => $conditionInfo) {
if ($conditionInfo['relatedModule'] == 'Users' && $baseModule != 'Users' && !in_array('vtiger_users', $referenceFieldTableList) && !in_array('vtiger_users', $tableList)) {
$sql .= ' LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid ';
$referenceFieldTableList[] = 'vtiger_users';
$sql .= ' LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid ';
$referenceFieldTableList[] = 'vtiger_groups';
continue;
}
$handler = vtws_getModuleHandlerFromName($conditionInfo['relatedModule'], $current_user);
$meta = $handler->getMeta();
$reltableList = $meta->getEntityTableIndexList();
$fieldName = $conditionInfo['fieldName'];
$referenceFieldObject = $moduleFields[$conditionInfo['referenceField']];
$fields = $meta->getModuleFields();
if ($fieldName == 'id') {
$tableName = $meta->getEntityBaseTable();
} else {
if (empty($fields[$fieldName])) {
continue;
}
$fieldObject = $fields[$fieldName];
$tableName = $fieldObject->getTableName();
}
if (!in_array($tableName, $referenceFieldTableList)) {
if ($referenceFieldObject->getFieldName() == 'parent_id' && ($this->getModule() == 'Calendar' || $this->getModule() == 'Events')) {
$joinclause = 'LEFT JOIN vtiger_seactivityrel ON vtiger_seactivityrel.activityid = vtiger_activity.activityid';
$referenceFieldTableList[] = 'vtiger_seactivityrel';
if (strpos($sql, $joinclause) === false) {
$sql .= " {$joinclause} ";
}
}
if ($referenceFieldObject->getFieldName() == 'contact_id' && ($this->getModule() == 'Calendar' || $this->getModule() == 'Events')) {
$joinclause = 'LEFT JOIN vtiger_cntactivityrel ON vtiger_cntactivityrel.activityid = vtiger_activity.activityid';
$referenceFieldTableList[] = 'vtiger_cntactivityrel';
if (strpos($sql, $joinclause) === false) {
$sql .= " {$joinclause} ";
}
}
if ($this->getModule() == 'Emails') {
$joinclause = 'INNER JOIN vtiger_emaildetails ON vtiger_activity.activityid = vtiger_emaildetails.emailid';
$referenceFieldTableList[] = 'vtiger_emaildetails';
if (strpos($sql, $joinclause) === false) {
$sql .= " {$joinclause} ";
}
}
$sql .= " LEFT JOIN " . $tableName . ' AS ' . $tableName . $conditionInfo['referenceField'] . ' ON ' . $tableName . $conditionInfo['referenceField'] . '.' . $reltableList[$tableName] . '=' . $referenceFieldObject->getTableName() . '.' . $referenceFieldObject->getColumnName();
$referenceFieldTableList[] = $tableName;
}
}
}
foreach ($this->fields as $fieldName) {
if ($fieldName == 'id' or !empty($moduleFields[$fieldName])) {
continue;
}
if (strpos($fieldName, '.')) {
list($fldmod, $fldname) = explode('.', $fieldName);
} else {
$fldmod = '';
$fldname = $fieldName;
}
$field = '';
if ($fldmod == '') {
// not FQN > we have to look for it
foreach ($this->referenceFieldInfoList as $fld => $mods) {
if ($fld == 'modifiedby' or $fld == 'assigned_user_id') {
continue;
}
foreach ($mods as $mname) {
if (!empty($this->referenceFields[$fld][$mname][$fldname])) {
$handler = vtws_getModuleHandlerFromName($mname, $current_user);
$meta = $handler->getMeta();
$reltableList = $meta->getEntityTableIndexList();
$referenceFieldObject = $this->referenceFields[$fld][$mname][$fldname];
$tableName = $referenceFieldObject->getTableName();
if (!in_array($tableName, $referenceFieldTableList)) {
if (($referenceFieldObject->getFieldName() == 'parent_id' || $fld == 'parent_id') && ($this->getModule() == 'Calendar' || $this->getModule() == 'Events')) {
$joinclause = 'LEFT JOIN vtiger_seactivityrel ON vtiger_seactivityrel.activityid = vtiger_activity.activityid';
if (strpos($sql, $joinclause) === false) {
$sql .= " {$joinclause} ";
}
}
if (($referenceFieldObject->getFieldName() == 'contact_id' || $fld == 'contact_id') && ($this->getModule() == 'Calendar' || $this->getModule() == 'Events')) {
$joinclause = 'LEFT JOIN vtiger_cntactivityrel ON vtiger_cntactivityrel.activityid = vtiger_activity.activityid';
if (strpos($sql, $joinclause) === false) {
$sql .= " {$joinclause} ";
}
}
$sql .= " LEFT JOIN " . $tableName . ' AS ' . $tableName . $fld . ' ON ' . $tableName . $fld . '.' . $reltableList[$tableName] . '=' . $moduleFields[$fld]->getTableName() . '.' . $moduleFields[$fld]->getColumnName();
$referenceFieldTableList[] = $tableName;
}
break 2;
}
}
}
} else {
// FQN
foreach ($this->referenceFieldInfoList as $fld => $mods) {
if ($fld == 'modifiedby' or $fld == 'assigned_user_id') {
continue;
}
if (!empty($this->referenceFields[$fld][$fldmod][$fldname])) {
$handler = vtws_getModuleHandlerFromName($fldmod, $current_user);
$meta = $handler->getMeta();
$reltableList = $meta->getEntityTableIndexList();
$referenceFieldObject = $this->referenceFields[$fld][$fldmod][$fldname];
$tableName = $referenceFieldObject->getTableName();
if (!in_array($moduleFields[$fld]->getTableName(), array_merge($referenceFieldTableList, $alreadyinfrom))) {
$fldtname = $moduleFields[$fld]->getTableName();
$sql .= " LEFT JOIN {$fldtname} ON {$fldtname}" . '.' . $moduleTableIndexList[$fldtname] . '=' . $baseTable . '.' . $baseTableIndex;
$alreadyinfrom[] = $fldtname;
}
if (!in_array($tableName, $referenceFieldTableList)) {
if (($referenceFieldObject->getFieldName() == 'parent_id' || $fld == 'parent_id') && ($this->getModule() == 'Calendar' || $this->getModule() == 'Events')) {
$joinclause = 'LEFT JOIN vtiger_seactivityrel ON vtiger_seactivityrel.activityid = vtiger_activity.activityid';
if (strpos($sql, $joinclause) === false) {
$sql .= " {$joinclause} ";
}
}
if (($referenceFieldObject->getFieldName() == 'contact_id' || $fld == 'contact_id') && ($this->getModule() == 'Calendar' || $this->getModule() == 'Events')) {
$joinclause = 'LEFT JOIN vtiger_cntactivityrel ON vtiger_cntactivityrel.activityid = vtiger_activity.activityid';
if (strpos($sql, $joinclause) === false) {
$sql .= " {$joinclause} ";
}
}
$sql .= " LEFT JOIN " . $tableName . ' AS ' . $tableName . $fld . ' ON ' . $tableName . $fld . '.' . $reltableList[$tableName] . '=' . $moduleFields[$fld]->getTableName() . '.' . $moduleFields[$fld]->getColumnName();
$referenceFieldTableList[] = $tableName;
}
break;
}
}
}
}
}
$sql .= $this->meta->getEntityAccessControlQuery();
$this->fromClause = $sql;
return $sql;
}
