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