public static function getSqlJoinQuery(&$fieldValues, &$filterValues, $resultsLimit, $reportModule, $reportTable, $auditedReport, $alternativeDb, &$fieldsByRef, $aliasSuffix = '', &$domainField = null)
{
global $current_user, $sugar_config;
$bean = BeanFactory::newBean($reportModule);
$joinVariables = array();
$processedJoinsFromSqlKeys = array();
$isNonCrmDatabase = $alternativeDb !== false ? true : false;
$tableModulesAssociation = asol_ReportsUtils::getTableModulesAssociation($current_user->id);
//****************************************//
//***Get Indexes From Report Main Table***//
//****************************************//
$dbKey = $alternativeDb === false ? 'crm' : 'ext' . $alternativeDb;
if (!isset($_SESSION['asolReportsTableIndexes'][$dbKey][$reportTable])) {
foreach (asol_Report::getSelectionResults("SHOW INDEX FROM " . $reportTable, null, false, $alternativeDb) as $tableCurrentIndex) {
$_SESSION['asolReportsTableIndexes'][$dbKey][$reportTable][$tableCurrentIndex['Column_name']] = true;
}
}
//****************************************//
//***Get Indexes From Report Main Table***//
//****************************************//
foreach ($fieldValues['tables'][0]['data'] as $index => &$currentValues) {
//****************************************//
//*****Variable Definition Iteration******//
//****************************************//
$fieldExploded = explode('.', $currentValues['field']);
$keyExploded = explode(';', $currentValues['key']);
$tableExploded = explode('::', $fieldExploded[0]);
$mainKey = null;
$mainFieldKey = null;
//**************************************//
//*****Control Variable Definition******//
//**************************************//
$fieldIsVisible = in_array(strtolower($currentValues['visible']), array("yes", "html")) ? true : false;
$fieldOrderedWithLimit = self::fieldOrderedWithLimit($currentValues, $resultsLimit);
$fieldHasFilter = self::fieldHasFilter($filterValues, $currentValues);
$fieldSqlVarsInFilters = self::fieldSqlVarsInFilters($fieldValues, $filterValues, $currentValues);
$fieldSqlRefsInFilters = self::fieldSqlRefsInFilters($fieldValues, $filterValues, $currentValues);
$fieldHasDetailGrouped = $currentValues['grouping'] != "0" ? true : false;
$fieldHasFunction = !in_array($currentValues['function'], array("0", "undefined")) ? true : false;
$domainUsage = self::isDomainUsage($domainField, $currentValues);
$auditRequiredJoin = self::isAuditRequiredJoin($auditedReport, $reportTable, $currentValues);
$reportTableHasCustom = empty($currentValues['key']) && substr($fieldExploded[0], -strlen('_cstm')) === '_cstm';
$reportTableCustomJoin = $reportTableCustomJoin || $reportTableHasCustom;
$reportTableAlreadyCustomJoined = false;
$commonDoJoin = $fieldHasFilter || $fieldSqlRefsInFilters || $fieldHasDetailGrouped || $domainUsage || $auditRequiredJoin;
$currentDoJoin = $commonDoJoin || ($fieldIsVisible || $fieldSqlVarsInFilters);
$currentDoCountJoin = $commonDoJoin || ($fieldIsVisible && $fieldHasFunction || $fieldOrderedWithLimit);
//*****************************//
//*****Init Key Iteration******//
//*****************************//
$lastTableName = null;
foreach ($keyExploded as $keyDepth => $currentLink) {
if (!$currentValues['isRelated']) {
break;
}
if ($isNonCrmDatabase) {
$parentLinkExploded = explode(' ', $keyExploded[$keyDepth - 1]);
$linkExploded = explode(' ', $currentLink);
$mainLinkKey = $linkExploded[0];
$relatedLinkKey = $linkExploded[1];
$mainFieldKey = empty($mainFieldKey) ? $mainLinkKey : $mainFieldKey;
$mainTable = $keyDepth >= 1 ? $tableExploded[$keyDepth - 1] : $reportTable;
$mainKey = $mainLinkKey;
$relatedTable = $tableExploded[$keyDepth];
$relatedKey = $relatedLinkKey;
$parentKey = $keyDepth >= 1 ? $parentLinkExploded[0] : null;
$joinTable = null;
$isMainCustom = false;
$isRelatedCustom = false;
$storedKey = $mainKey . '.' . $relatedTable;
} else {
$parentLinkExploded = explode(' ', $keyExploded[$keyDepth - 1]);
$linkExploded = explode(' ', $currentLink);
$currentLinkKey = $linkExploded[0];
$tableUnderScoreExploded = explode("_", $tableExploded[$keyDepth]);
$isCustomTable = end($tableUnderScoreExploded) == 'cstm' ? true : false;
$tableName = $isCustomTable ? substr($tableExploded[$keyDepth], 0, -5) : $tableExploded[$keyDepth];
if ($currentLinkKey === 'id') {
//Relationship
$mainTableUnderScoreExploded = explode("_", $tableExploded[0]);
$isMainCustomTable = end($mainTableUnderScoreExploded) == 'cstm' ? true : false;
$mainTableName = $isMainCustomTable ? substr($tableExploded[0], 0, -5) : $tableExploded[0];
$mainKeyExploded = explode(' ', $keyExploded[0]);
$mainFieldKey = $mainKeyExploded[0];
$relationshipName = $linkExploded[1];
$mainTableName = $keyDepth >= 1 ? $tableExploded[$keyDepth - 1] : $reportTable;
$relatedTableName = substr($tableExploded[$keyDepth], -strlen('_cstm')) === '_cstm' ? substr($tableExploded[$keyDepth], 0, -strlen('_cstm')) : $tableExploded[$keyDepth];
$availableRelationShips = self::getAvailableRelationShips($mainTableName, $relatedTableName, $currentLinkKey);
if (!empty($availableRelationShips)) {
//**********************************//
//******Both Sides Relationship*****//
//**********************************//
$currentRelationShip = self::getCurrentRelationShip($availableRelationShips, $relationshipName);
$relationShipDirection = $currentRelationShip['asolRelationShipDirection'];
$mainTable = $relationShipDirection == 'L2R' ? $currentRelationShip['lhs_table'] : $currentRelationShip['rhs_table'];
$mainKey = $relationShipDirection == 'L2R' ? $currentRelationShip['lhs_key'] : $currentRelationShip['rhs_key'];
$relatedTable = $relationShipDirection == 'L2R' ? $currentRelationShip['rhs_table'] : $currentRelationShip['lhs_table'];
$relatedKey = $relationShipDirection == 'L2R' ? $currentRelationShip['rhs_key'] : $currentRelationShip['lhs_key'];
$parentKey = $keyDepth >= 1 ? $parentLinkExploded[0] : null;
$joinTable = array('table' => $currentRelationShip['join_table'], 'lhsKey' => $relationShipDirection == 'L2R' ? $currentRelationShip['join_key_lhs'] : $currentRelationShip['join_key_rhs'], 'rhsKey' => $relationShipDirection == 'L2R' ? $currentRelationShip['join_key_rhs'] : $currentRelationShip['join_key_lhs']);
$isMainCustom = false;
$isRelatedCustom = $isCustomTable;
$storedKey = $mainKey . '.' . $currentRelationShip['relationship_name'];
} else {
//*************************************//
//******Fields MetaData Searching******//
//*************************************//
$availableFieldsMetadata = self::getAvailableFieldsMetadata($reportModule);
$currentRelationShip = self::getCurrentRelationShip($availableFieldsMetadata, $relationshipName);
$mainTable = $keyDepth >= 1 ? $tableExploded[$keyDepth - 1] : $reportTable;
$mainKey = $currentRelationShip['ext3'];
$relatedTable = strtolower($currentRelationShip['ext2']);
$relatedKey = 'id';
$parentKey = $keyDepth >= 1 ? $parentLinkExploded[0] : null;
$joinTable = null;
$isMainCustom = true;
$isRelatedCustom = $isCustomTable;
$storedKey = $mainKey . '.' . $relatedTable;
}
} else {
//Relate Field
//***********************//
//***AlineaSol Premium***//
//***********************//
$extraParams = array('database' => '-1', 'restricted' => false);
$definedAvailableRelates = asol_ReportsUtils::managePremiumFeature("reportRelatesManagement", "reportFunctions.php", "getReportDefinedAvailableRelates", $extraParams);
$theModule = $keyDepth >= 1 ? $tableModulesAssociation[$lastTableName] : $reportModule;
$isVirtualLink = $definedAvailableRelates !== false ? isset($definedAvailableRelates[$theModule][$currentLink]) ? true : false : false;
//***********************//
//***AlineaSol Premium***//
//***********************//
if ($isVirtualLink) {
$relatedInfo = null;
$relatedModule = $definedAvailableRelates[$theModule][$currentLink]['relationModule'];
$relatedTable = $keyDepth >= 1 ? $tableName : strtolower($relatedModule);
$relatedKey = $definedAvailableRelates[$theModule][$currentLink]['relationField'];
} else {
$relatedInfo = asol_Report::getReportsRelatedFields($bean, $currentLink);
$relatedModule = $relatedInfo['module'];
$relatedTable = $keyDepth >= 1 ? $tableName : strtolower($relatedModule);
$relatedKey = 'id';
}
$mainKeyExploded = explode(' ', $keyExploded[0]);
$mainFieldKey = $mainKeyExploded[0];
$mainTable = $keyDepth >= 1 ? $tableExploded[$keyDepth - 1] : ($auditedReport ? $reportTable . '_audit' : $reportTable);
$mainKey = $currentLink;
$parentKey = $keyDepth >= 1 ? $parentLinkExploded[0] : null;
$joinTable = null;
$isMainCustom = isset($relatedInfo['custom_module']);
$isRelatedCustom = $isCustomTable;
$storedKey = $mainKey . '.' . $relatedTable;
}
}
$hasRelatedCustom = $joinVariables[$mainFieldKey][$keyDepth][$storedKey]['isRelatedCustom'];
$didCountJoin = $joinVariables[$mainFieldKey][$keyDepth][$storedKey]['doCountJoin'];
if (isset($didCountJoin) && !$didCountJoin && $currentDoCountJoin) {
$joinVariables[$mainFieldKey][$keyDepth][$storedKey]['doCountJoin'] = true;
}
if (!empty($mainKey) && $hasRelatedCustom !== true) {
$currentDoJoin = $currentDoJoin || $joinVariables[$mainFieldKey][$keyDepth][$storedKey]['doJoin'];
$currentDoCountJoin = $currentDoCountJoin || $joinVariables[$mainFieldKey][$keyDepth][$storedKey]['doCountJoin'];
$joinVariables[$mainFieldKey][$keyDepth][$storedKey] = array('mainTable' => $mainTable, 'mainKey' => $mainKey, 'relatedTable' => $relatedTable, 'relatedKey' => $relatedKey, 'parentKey' => $parentKey, 'joinTable' => $joinTable['table'] == null && $joinTable['lhsKey'] == null && $joinTable['rhsKey'] == null ? null : $joinTable, 'isMainCustom' => $isMainCustom, 'isRelatedCustom' => $isRelatedCustom, 'avoidAlias' => $keyDepth >= 1 ? false : ($auditedReport ? true : false), 'doJoin' => $currentDoJoin, 'doCountJoin' => $currentDoCountJoin);
}
$lastTableName = $tableName;
//********************************************//
//***Get Indexes From Report Related Tables***//
//********************************************//
if (!isset($_SESSION['asolReportsTableIndexes'][$dbKey][$relatedTable])) {
foreach (asol_Report::getSelectionResults("SHOW INDEX FROM " . $relatedTable, null, false, $alternativeDb) as $tableCurrentIndex) {
$_SESSION['asolReportsTableIndexes'][$dbKey][$relatedTable][$tableCurrentIndex['Column_name']] = true;
}
}
//********************************************//
//***Get Indexes From Report Related Tables***//
//********************************************//
}
//****************************************//
//******Generate SQL Variable Joins*******//
//****************************************//
if (!empty($currentValues['sql'])) {
self::getJoinsFromSqlVariables($currentValues['sql'], $currentDoJoin, $currentDoCountJoin, $alternativeDb, $reportModule, $reportTable, $joinVariables, $reportTableCustomJoin, $processedJoinsFromSqlKeys);
}
}
$sqlJoin = '';
$sqlCountJoin = '';
$aliasIndexTable = array();
$varJoinIndex = 0;
foreach ($joinVariables as $initKey => $mainJoinFields) {
foreach ($mainJoinFields as $mainJoinIndex => $secondJoinFields) {
$secondJoinIndex = 0;
foreach ($secondJoinFields as $joinKey => $joinVar) {
//***************************//
//****Variable Definition****//
//***************************//
$parentKey = $joinVar['parentKey'];
$mainTable = $joinVar['mainTable'];
$mainKey = $joinVar['mainKey'];
$relatedTable = $joinVar['relatedTable'];
$relatedKey = $joinVar['relatedKey'];
$joinTable = $joinVar['joinTable'];
$isMainCustom = $joinVar['isMainCustom'];
$isRelatedCustom = $joinVar['isRelatedCustom'];
$avoidAlias = $joinVar['avoidAlias'];
$doJoin = $joinVar['doJoin'];
$doCountJoin = $joinVar['doCountJoin'];
//***********************//
//***AlineaSol Premium***//
//***********************//
$joinType = asol_ReportsUtils::managePremiumFeature("multiJoinReport", "reportFunctions.php", "getJoinType", array('joinsTable' => $fieldValues['tables'][0]['joins'], 'relatedTable' => $relatedTable));
$joinType = $joinType !== false ? $joinType : 'LEFT';
//***********************//
//***AlineaSol Premium***//
//***********************//
//************************//
//****Index Generation****//
//************************//
$parentAliasIndex = $mainJoinIndex > 0 ? $aliasIndexTable[$initKey][$mainJoinIndex - 1][$parentKey][$mainTable] . $aliasSuffix : $aliasSuffix;
$currentAliasIndex = $avoidAlias ? $aliasSuffix : $varJoinIndex . $mainJoinIndex . $mainJoinIndex . $secondJoinIndex . $aliasSuffix;
//**********************//
//****SQL Generation****//
//**********************//
$sqlCurrentJoinQuery = '';
if ($isMainCustom && ($keyDepth >= 1 || !$reportTableAlreadyCustomJoined)) {
$sqlCurrentJoinQuery .= ' LEFT JOIN ' . $mainTable . '_cstm ' . $mainTable . '_cstm' . ($keyDepth >= 1 ? $currentAliasIndex : '') . ' ON (' . $mainTable . $parentAliasIndex . '.id=' . $mainTable . '_cstm' . ($keyDepth >= 1 ? $currentAliasIndex : '') . '.id_c)';
$reportTableAlreadyCustomJoined = $reportTableAlreadyCustomJoined || $keyDepth === 0;
}
if ($joinTable === null) {
$sqlCurrentJoinQuery .= ' ' . $joinType . ' JOIN ' . $relatedTable . ' ' . $relatedTable . $currentAliasIndex . ' ON (' . $mainTable . ($isMainCustom ? '_cstm' . ($keyDepth >= 1 ? $currentAliasIndex : '') : $parentAliasIndex) . '.' . $mainKey . '=' . $relatedTable . $currentAliasIndex . '.' . $relatedKey . ($isNonCrmDatabase ? '' : ' AND ' . $relatedTable . $currentAliasIndex . '.deleted=0') . ')';
} else {
$sqlCurrentJoinQuery .= ' ' . $joinType . ' JOIN ' . $joinTable['table'] . ' ' . $joinTable['table'] . $currentAliasIndex . ' ON (' . $mainTable . ($isMainCustom ? '_cstm' . ($keyDepth >= 1 ? $currentAliasIndex : '') : $parentAliasIndex) . '.' . $mainKey . '=' . $joinTable['table'] . $currentAliasIndex . '.' . $joinTable['lhsKey'] . ' AND ' . $joinTable['table'] . $currentAliasIndex . '.deleted=0)';
$sqlCurrentJoinQuery .= ' ' . $joinType . ' JOIN ' . $relatedTable . ' ' . $relatedTable . $currentAliasIndex . ' ON (' . $joinTable['table'] . $currentAliasIndex . '.' . $joinTable['rhsKey'] . '=' . $relatedTable . $currentAliasIndex . '.' . $relatedKey . ($isNonCrmDatabase ? '' : ' AND ' . $relatedTable . $currentAliasIndex . '.deleted=0') . ')';
}
if ($isRelatedCustom) {
$sqlCurrentJoinQuery .= ' LEFT JOIN ' . $relatedTable . '_cstm ' . $relatedTable . '_cstm' . $currentAliasIndex . ' ON (' . $relatedTable . $currentAliasIndex . '.id=' . $relatedTable . '_cstm' . $currentAliasIndex . '.id_c)';
}
//*************************************//
//****Apply Query to SqlJoin String****//
//*************************************//
if ($doJoin) {
$sqlJoin .= $sqlCurrentJoinQuery;
}
if ($doCountJoin) {
$sqlCountJoin .= $sqlCurrentJoinQuery;
}
$aliasIndexTable[$initKey][$mainJoinIndex][$mainKey][$relatedTable] = $currentAliasIndex;
$secondJoinIndex++;
}
}
$varJoinIndex++;
}
//*******************************************************//
//*****Generate Report Table Custom Join when Needed*****//
//*******************************************************//
if ($reportTableCustomJoin && !$reportTableAlreadyCustomJoined) {
$sqlCurrentJoinQuery = ' LEFT JOIN ' . $reportTable . '_cstm ON (' . $reportTable . '.id=' . $reportTable . '_cstm.id_c)';
$sqlJoin .= $sqlCurrentJoinQuery;
$sqlCountJoin .= $sqlCurrentJoinQuery;
}
//******************************************************//
//******Override Fields & Filters with Table Alias******//
//******************************************************//
self::resetValuesWithJoinAlias($fieldValues['tables'][0]['data'], $aliasIndexTable, $reportTable, $auditedReport, $fieldsByRef, $aliasSuffix);
self::resetValuesWithJoinAlias($filterValues, $aliasIndexTable, $reportTable, $auditedReport, $fieldsByRef, $aliasSuffix, false);
//*******************************//
//***Relation Domain Left Join***//
//*******************************//
if (asol_ReportsUtils::isDomainsInstalled()) {
if ($domainField !== null) {
if ($domainField != "") {
if (isset($domainField["domainRelation"])) {
$domainRelation = $domainField["domainRelation"];
$initKey = $domainRelation["linkField"];
$mainJoinIndex = 0;
$mainKey = $initKey;
$relatedTable = $domainRelation["relatedTable"];
$processedRelation = isset($aliasIndexTable[$initKey][$mainJoinIndex][$mainKey][$relatedTable]);
$domainIndex = $aliasIndexTable[$initKey][$mainJoinIndex][$mainKey][$relatedTable];
if (!$processedRelation) {
$domainIndex = count($fieldValues['tables'][0]['data']);
$domainRelationTableAlias = $domainRelation["relatedTable"] . $domainIndex;
$extendedJoin = " LEFT JOIN " . $domainRelation["relatedTable"] . " " . $domainRelationTableAlias . " ON (" . $reportTable . "." . $domainRelation["linkField"] . "=" . $domainRelationTableAlias . "." . $domainRelation["relatedKey"] . ") ";
$sqlJoin .= $extendedJoin;
$sqlCountJoin .= $extendedJoin;
} else {
$domainRelationTableAlias = $domainRelation["relatedTable"] . $domainIndex;
}
$domainField["domainRelation"]["relatedTable"] = $domainRelationTableAlias;
}
}
}
}
//********************************//
//***Relation Audited Left Join***//
//********************************//
if ($auditedReport) {
$domainRelation = $domainField["domainRelation"];
$initKey = 'parent_id';
$mainJoinIndex = 0;
$mainKey = $initKey;
$relatedTable = $reportTable;
$processedRelation = isset($aliasIndexTable[$initKey][$mainJoinIndex][$mainKey][$relatedTable]);
$domainIndex = $aliasIndexTable[$initKey][$mainJoinIndex][$mainKey][$relatedTable];
if (!$processedRelation) {
$domainIndex = count($fieldValues['tables'][0]['data']);
$domainRelationTableAlias = $domainRelation["relatedTable"] . $domainIndex;
$extendedJoin = " LEFT JOIN " . $relatedTable . " " . $relatedTable . " ON (" . $relatedTable . "_audit." . $mainKey . "=" . $relatedTable . ".id) ";
$sqlJoin .= $extendedJoin;
$sqlCountJoin .= $extendedJoin;
}
}
//**************************************//
//****Define Returned Function Array****//
//**************************************//
$returnedJoinArray = array("moduleCustomJoined" => false, "moduleCountCustomJoined" => false, "aliasIndexTable" => $aliasIndexTable, "querys" => array("Join" => $sqlJoin, "CountJoin" => $sqlCountJoin));
return $returnedJoinArray;
}
}
}
} else {
if ($htmlTarget == 'reportRelatedTableFields') {
$selectedDb = $_REQUEST['selectedDb'];
$selectedModule = $_REQUEST['selectedModule'];
$mainRelatedKey = !empty($_REQUEST['mainRelatedKey']) ? $_REQUEST['mainRelatedKey'] : null;
$mainRelatedModule = !empty($_REQUEST['mainRelatedModule']) ? $_REQUEST['mainRelatedModule'] : null;
$auditedReport = isset($_REQUEST['isAudited']) && $_REQUEST['isAudited'] === '1' ? 1 : 0;
$treeMode = isset($_REQUEST['treeMode']) && $_REQUEST['treeMode'] == 'true' ? !$breadCrumbNavigation && true : false;
$subQueryMode = isset($_REQUEST['subQueryMode']) ? $_REQUEST['subQueryMode'] == 'true' ? true : false : false;
$rhsKeyExploded = explode('.', $_REQUEST['selectedRhsKey']);
$rhsModExploded = explode('::', $rhsKeyExploded[0]);
if (count($rhsKeyExploded) > 1) {
$fixedTable = substr(end($rhsModExploded), -strlen('_cstm')) === '_cstm' ? substr(end($rhsModExploded), 0, -strlen('_cstm')) : end($rhsModExploded);
$tableModules = $selectedDb === '-1' ? asol_ReportsUtils::getTableModulesAssociation($current_user->id) : null;
$lastModule = $selectedDb === '-1' ? isset($tableModules[$fixedTable]) ? $tableModules[$fixedTable] : null : $fixedTable;
$selectedModule = $lastModule;
$rhsKey = $rhsKeyExploded[1];
} else {
$rhsKey = $rhsKeyExploded[0];
}
//***********************//
//***AlineaSol Premium***//
//***********************//
$extraParams = array('mainRelatedKey' => $mainRelatedKey, 'rhsModExploded' => $rhsModExploded);
$relationLinkAvailable = asol_ReportsUtils::managePremiumFeature("multipleLinks", "reportFunctions.php", "isRelationLinkAvailable", $extraParams);
$getRelateLinks = $relationLinkAvailable !== false ? $relationLinkAvailable['getRelateLinks'] : false;
$jumpedLinks = $relationLinkAvailable !== false ? $relationLinkAvailable['jumpedLinks'] : 0;
$linkColor = $getRelateLinks ? $relationLinkAvailable['linkColor'] : null;
//***********************//
