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