-
-
Save mahesh-salaria/5a2699660dda345c2953 to your computer and use it in GitHub Desktop.
__swift/library/KQL/class.SWIFT_KQLParser.php
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| /** | |
| * ======================================= | |
| * ################################### | |
| * SWIFT Framework | |
| * | |
| * @package SWIFT | |
| * @author Kayako Infotech Ltd. | |
| * @copyright Copyright (c) 2001-2009, Kayako Infotech Ltd. | |
| * @license http://www.kayako.com/license | |
| * @link http://www.kayako.com | |
| * @filesource | |
| * ################################### | |
| * ======================================= | |
| */ | |
| SWIFT_Loader::LoadLibrary('KQL:KQL'); | |
| /** | |
| * The KQL Parser Class | |
| * | |
| * This class processes a raw KQL statement into one (or more) SQL statements. | |
| * | |
| * @author Varun Shoor | |
| */ | |
| class SWIFT_KQLParser extends SWIFT_KQL | |
| { | |
| /** | |
| * This variable contains a list of all table names that are specified by user (does not include auto joins) | |
| */ | |
| protected $_sqlTableList = array(); | |
| /** | |
| * This variable contains a list of tables that are supposed to be automatically joined to the query | |
| */ | |
| protected $_autoJoinTableList = array(); | |
| protected $_autoJoinTableExtendedList = array(); | |
| /** | |
| * This array contains the list of tables that have been joined | |
| */ | |
| protected $_joinedTableList = array(); | |
| /** | |
| * This variable contains a list of SQL Expressions to be automatically added to the query (LEFT JOIN Tablename ON (moo = cow)) | |
| */ | |
| protected $_autoJoinExpressionList = array(); | |
| /** | |
| * This variable contains a list of SQL Expression fields to be automatically added to the query | |
| */ | |
| protected $_autoSQLExpressionList = array(); | |
| /** | |
| * This variable contains a list of Distinct SQL Expression fields to be automatically added to the query | |
| */ | |
| protected $_autoDistinctSQLExpressionList = array(); | |
| /** | |
| * The Primary Table Name | |
| */ | |
| protected $_primaryTableName = ''; | |
| /** | |
| * The Return Fields | |
| */ | |
| protected $_returnGroupByFields = array(); | |
| /** | |
| * The Return Fields: X Axis | |
| */ | |
| protected $_returnGroupByXFields = array(); | |
| /** | |
| * The Return Fields: For Grouped Tabular Reports | |
| */ | |
| protected $_returnMultiGroupByFields = array(); | |
| /** | |
| * The Return Fields: Multi Group SQL Statements | |
| */ | |
| protected $_returnMultiGroupBySQLStatements = array(); | |
| /** | |
| * The original SQL expressions specified by user | |
| */ | |
| protected $_returnSQLExpressions = array(); | |
| protected $_distinctValueContainer = array(); | |
| /** | |
| * Constructor | |
| * | |
| * @author Varun Shoor | |
| * @return bool "true" on Success, "false" otherwise | |
| */ | |
| public function __construct() | |
| { | |
| parent::__construct(); | |
| $this->Load->Library('KQL:KQLParserResult', false, false); | |
| return true; | |
| } | |
| /** | |
| * Destructor | |
| * | |
| * @author Varun Shoor | |
| * @return bool "true" on Success, "false" otherwise | |
| */ | |
| public function __destruct() | |
| { | |
| parent::__destruct(); | |
| return true; | |
| } | |
| /** | |
| * Parse the KQL to Chunks | |
| * | |
| * @author Varun Shoor | |
| * @param string $_kqlStatement | |
| * @return SWIFT_KQLParserResult The KQL Parser Result Object | |
| * @throws SWIFT_Exception If the Class is not Loaded or If Invalid Data is Provided | |
| */ | |
| public function ParseStatement($_kqlStatement) | |
| { | |
| if (!$this->GetIsClassLoaded()) { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } else if (empty($_kqlStatement)) { | |
| throw new SWIFT_Exception(SWIFT_INVALIDDATA); | |
| } | |
| $_chunksContainer = $this->SQLParser->parse($_kqlStatement); | |
| $_sqlStatementList = array(); | |
| if (_is_array($_chunksContainer)) { | |
| foreach ($_chunksContainer as $_key => $_chunkBase) { | |
| if (!is_array($_chunkBase) && trim($_chunkBase) == ';') { | |
| continue; | |
| } | |
| $_statementResult = $this->ParseSQLChunks($_chunkBase); | |
| if (is_array($_statementResult)) { | |
| $_sqlStatementList = array_merge($_sqlStatementList, $_statementResult); | |
| } else if (is_string($_statementResult)) { | |
| $_sqlStatementList[] = $_statementResult; | |
| } | |
| } | |
| } | |
| // No Group By Used | |
| if (!count($this->_returnGroupByFields) && !count($this->_returnGroupByXFields) && !count($this->_returnMultiGroupByFields)) { | |
| return SWIFT_KQLParserResult::LoadTabular($_sqlStatementList); | |
| // Uses Y Group Type | |
| } else if (count($this->_returnGroupByFields) && !count($this->_returnGroupByXFields) && !count($this->_returnMultiGroupByFields)) { | |
| return SWIFT_KQLParserResult::LoadSummary($_sqlStatementList, $this->_returnGroupByFields); | |
| // Uses X Group Type | |
| } else if (count($this->_returnGroupByXFields)) { | |
| return SWIFT_KQLParserResult::LoadMatrix($_sqlStatementList, $this->_returnGroupByFields, $this->_returnGroupByXFields, $this->_distinctValueContainer); | |
| // Uses MultiGroup Type | |
| } else if (count($this->_returnMultiGroupByFields)) { | |
| return SWIFT_KQLParserResult::LoadGroupedTabular($_sqlStatementList, $this->_returnMultiGroupByFields); | |
| } | |
| return SWIFT_KQLParserResult::LoadTabular($_sqlStatementList); | |
| } | |
| /** | |
| * Parse the SQL Chunks | |
| * | |
| * @author Varun Shoor | |
| * @param array $_chunksContainer | |
| * @return bool "true" on Success, "false" otherwise | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| protected function ParseSQLChunks($_chunksContainer) | |
| { | |
| if (!$this->GetIsClassLoaded()) { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } | |
| if (!isset($_chunksContainer['command']) || $_chunksContainer['command'] != 'select') { | |
| throw new SWIFT_Exception('Not a valid SELECT Statement'); | |
| } | |
| if (!isset($_chunksContainer['from'])) { | |
| throw new SWIFT_Exception('No Source Table Specified'); | |
| } | |
| $_sqlExpressions = $_sqlTableList = $_sqlJoinList = $_sqlWhereClauses = $_sqlWhereClausesExtended = array(); | |
| $_sqlJoinTableNameList = array(); | |
| $_sqlPrimaryTable = ''; | |
| /** | |
| * --------------------------------------------- | |
| * Tables | |
| * --------------------------------------------- | |
| */ | |
| foreach ($_chunksContainer['from']['table_references']['table_factors'] as $_tableList) { | |
| // We have three probable table names: | |
| // 1) Actual one supplied | |
| // 2) Language Key | |
| // 3) Alias | |
| $_tableName = mb_strtolower($_tableList['table']); | |
| $_tableAlias = mb_strtolower($_tableList['alias']); | |
| $_tableLabelResult = $this->GetTableNameOnLabel($_tableName); | |
| $_finalTableName = false; | |
| // Do we have a table for this? | |
| if (isset($this->_schemaContainer[$_tableName])) { | |
| $_finalTableName = $this->Database->Escape(Clean($_tableName)); | |
| } else if (!empty($_tableLabelResult)) { | |
| $_finalTableName = $this->Database->Escape(Clean($_tableLabelResult)); | |
| } else if (!empty($_tableAlias) && isset($this->_schemaContainer[$_tableAlias])) { | |
| $_finalTableName = $this->Database->Escape(Clean($_tableAlias)); | |
| } else { | |
| throw new SWIFT_Exception('Table not found: ' . $_tableName); | |
| } | |
| $_sqlTableList[] = $_finalTableName; | |
| if (isset($this->_schemaContainer[$_finalTableName][SWIFT_KQLSchema::SCHEMA_AUTOJOIN])) { | |
| foreach ($this->_schemaContainer[$_finalTableName][SWIFT_KQLSchema::SCHEMA_AUTOJOIN] as $_autoJoinTableName) { | |
| if (!in_array($_autoJoinTableName, $this->_autoJoinTableList)) { | |
| $this->_autoJoinTableList[] = $_autoJoinTableName; | |
| $this->_autoJoinTableExtendedList[$_autoJoinTableName] = $_finalTableName; | |
| } | |
| } | |
| } | |
| } | |
| $this->_sqlTableList = $_sqlTableList; | |
| // Update Primary Table | |
| $_sqlPrimaryTable = $_sqlTableList[0]; | |
| $this->_primaryTableName = $_sqlPrimaryTable; | |
| /** | |
| * --------------------------------------------- | |
| * Prepare LEFT JOIN's | |
| * --------------------------------------------- | |
| */ | |
| foreach ($_sqlTableList as $_key => $_tableName) { | |
| // Move on if table is primary | |
| if ($_key == 0) { | |
| continue; | |
| } | |
| // Make sure the tables are related | |
| if (!isset($this->_schemaContainer[$_sqlPrimaryTable][SWIFT_KQLSchema::SCHEMA_RELATEDTABLES][$_tableName])) { | |
| throw new SWIFT_Exception($_sqlPrimaryTable . ' is not related to ' . $_tableName); | |
| } | |
| $this->_joinedTableList[] = $_tableName; | |
| $_joinStatement = ''; | |
| // We have an extended where processing for related tables in place | |
| if (_is_array($this->_schemaContainer[$_sqlPrimaryTable][SWIFT_KQLSchema::SCHEMA_RELATEDTABLES][$_tableName])) { | |
| $_joinStatement = $this->_schemaContainer[$_sqlPrimaryTable][SWIFT_KQLSchema::SCHEMA_RELATEDTABLES][$_tableName][0]; | |
| $_sqlWhereClausesExtended[] = $this->_schemaContainer[$_sqlPrimaryTable][SWIFT_KQLSchema::SCHEMA_RELATEDTABLES][$_tableName][1]; | |
| } else { | |
| $_joinStatement = $this->_schemaContainer[$_sqlPrimaryTable][SWIFT_KQLSchema::SCHEMA_RELATEDTABLES][$_tableName]; | |
| } | |
| $_sqlJoinList[] = TABLE_PREFIX . $_tableName . ' AS ' . $_tableName . ' ON (' . $_joinStatement . ')'; | |
| } | |
| /** | |
| * --------------------------------------------- | |
| * SQL Expressions | |
| * --------------------------------------------- | |
| */ | |
| if (isset($_chunksContainer['select_expressions']) && _is_array($_chunksContainer['select_expressions'])) { | |
| foreach ($_chunksContainer['select_expressions'] as $_argsContainer) { | |
| if (!isset($_argsContainer['args']) || !_is_array($_argsContainer['args'])) { | |
| continue; | |
| } | |
| $_argumentDepth = 0; | |
| $_sqlExpressions = array_merge($_sqlExpressions, $this->ProcessParentArguments($_argsContainer, $_sqlPrimaryTable, $_sqlTableList, $_argumentDepth)); | |
| } | |
| } | |
| /** | |
| * --------------------------------------------- | |
| * Where Clause | |
| * --------------------------------------------- | |
| */ | |
| if (isset($_chunksContainer['where_clause']) && _is_array($_chunksContainer['where_clause'])) { | |
| if (isset($_chunksContainer['where_clause']['args'])) { | |
| $_argumentDepth = 0; | |
| $_sqlWhereClauses = array_merge($_sqlWhereClauses, $this->ProcessWhereArguments($_chunksContainer['where_clause'], $_sqlPrimaryTable, $_sqlTableList, $_argumentDepth)); | |
| } | |
| foreach ($_chunksContainer['where_clause'] as $_argsContainer) { | |
| if (!isset($_argsContainer['args']) || !_is_array($_argsContainer['args'])) { | |
| continue; | |
| } | |
| $_argumentDepth = 0; | |
| $_sqlWhereClauses = array_merge($_sqlWhereClauses, $this->ProcessWhereArguments($_argsContainer, $_sqlPrimaryTable, $_sqlTableList, $_argumentDepth)); | |
| } | |
| } | |
| /** | |
| * --------------------------------------------- | |
| * Group | |
| * --------------------------------------------- | |
| */ | |
| $_sqlGroupByExpressions = array(); | |
| if (isset($_chunksContainer['group_by']) && _is_array($_chunksContainer['group_by'])) { | |
| foreach ($_chunksContainer['group_by'] as $_groupByContainer) { | |
| $_baseGroupByExpression = $this->ProcessGroupBy($_sqlPrimaryTable, $_sqlTableList, $_groupByContainer[0], $_groupByContainer[1]); | |
| if (!empty($_baseGroupByExpression)) { | |
| $_sqlGroupByExpressions[] = $_baseGroupByExpression; | |
| } | |
| } | |
| } | |
| /** | |
| * --------------------------------------------- | |
| * MultiGroup | |
| * --------------------------------------------- | |
| */ | |
| if (isset($_chunksContainer['multigroup_by']) && _is_array($_chunksContainer['multigroup_by'])) { | |
| foreach ($_chunksContainer['multigroup_by'] as $_multiGroupByContainer) { | |
| $_baseMultiGroupByExpression = $this->ProcessMultiGroupBy($_sqlPrimaryTable, $_sqlTableList, $_multiGroupByContainer[0], $_multiGroupByContainer[1]); | |
| if (!empty($_baseMultiGroupByExpression)) { | |
| $_sqlGroupByExpressions[] = $_baseMultiGroupByExpression; | |
| } | |
| } | |
| } | |
| /** | |
| * --------------------------------------------- | |
| * Order | |
| * --------------------------------------------- | |
| */ | |
| $_sqlSortOrderExpressions = array(); | |
| if (isset($_chunksContainer['sort_order']) && _is_array($_chunksContainer['sort_order'])) { | |
| foreach ($_chunksContainer['sort_order'] as $_tableCol => $_order) { | |
| $_sqlSortOrderExpressions[] = $this->ProcessSortOrder($_sqlPrimaryTable, $_sqlTableList, $_tableCol, $_order); | |
| } | |
| } | |
| /** | |
| * --------------------------------------------- | |
| * Prepare *automatic* LEFT JOIN's | |
| * --------------------------------------------- | |
| */ | |
| foreach ($this->_autoJoinTableList as $_tableName) { | |
| // If the table is already joined up, then move on | |
| if (in_array($_tableName, $_sqlTableList) || in_array($_tableName, $this->_joinedTableList)) { | |
| continue; | |
| } | |
| $_joinParentTableName = $_sqlPrimaryTable; | |
| // If we are supposed to join on a different table, then check | |
| if (isset($this->_autoJoinTableExtendedList[$_tableName])) { | |
| $_joinParentTableName = $this->_autoJoinTableExtendedList[$_tableName]; | |
| } | |
| // Make sure the tables are related | |
| if (!isset($this->_schemaContainer[$_joinParentTableName][SWIFT_KQLSchema::SCHEMA_RELATEDTABLES][$_tableName])) { | |
| throw new SWIFT_Exception($_joinParentTableName . ' is not related to ' . $_tableName); | |
| } | |
| $this->_joinedTableList[] = $_tableName; | |
| $_joinStatement = ''; | |
| // We have an extended where processing for related tables in place | |
| if (_is_array($this->_schemaContainer[$_joinParentTableName][SWIFT_KQLSchema::SCHEMA_RELATEDTABLES][$_tableName])) { | |
| $_joinStatement = $this->_schemaContainer[$_joinParentTableName][SWIFT_KQLSchema::SCHEMA_RELATEDTABLES][$_tableName][0]; | |
| $_sqlWhereClausesExtended[] = $this->_schemaContainer[$_joinParentTableName][SWIFT_KQLSchema::SCHEMA_RELATEDTABLES][$_tableName][1]; | |
| } else { | |
| $_joinStatement = $this->_schemaContainer[$_joinParentTableName][SWIFT_KQLSchema::SCHEMA_RELATEDTABLES][$_tableName]; | |
| } | |
| $_sqlJoinList[] = TABLE_PREFIX . $_tableName . ' AS ' . $_tableName . ' ON (' . $_joinStatement . ')'; | |
| } | |
| $_sqlJoinList = array_merge($_sqlJoinList, $this->_autoJoinExpressionList); | |
| /** | |
| * --------------------------------------------- | |
| * Do final processing on where clauses | |
| * --------------------------------------------- | |
| */ | |
| if (count($_sqlWhereClausesExtended)) { | |
| foreach ($_sqlWhereClausesExtended as $_whereClauseText) { | |
| $_opsPrefix = ''; | |
| if (count($_sqlWhereClauses)) { | |
| $_opsPrefix = ' AND '; | |
| } | |
| $_sqlWhereClauses[] = $_opsPrefix . $_whereClauseText; | |
| } | |
| } | |
| /** | |
| * --------------------------------------------- | |
| * Construct Final SQL Statements | |
| * --------------------------------------------- | |
| */ | |
| // Load up the distinct values? | |
| if (count($this->_autoDistinctSQLExpressionList)) { | |
| $_distinctValueMap = array(); | |
| $_statementSuffixes = ''; | |
| if (count($_sqlJoinList)) { | |
| $_statementSuffixes .= ' LEFT JOIN ' . implode(' LEFT JOIN ', $_sqlJoinList); | |
| } | |
| if (count($_sqlWhereClauses)) { | |
| $_statementSuffixes .= ' WHERE ' . implode('', $_sqlWhereClauses); | |
| } | |
| foreach ($this->_autoDistinctSQLExpressionList as $_fieldNameReference => $_distinctField) { | |
| $_sqlStatement = 'SELECT ' . 'DISTINCT ' . $_distinctField . ' FROM ' . TABLE_PREFIX . $_sqlPrimaryTable . ' AS ' . $_sqlPrimaryTable . $_statementSuffixes; | |
| $this->Database->Query($_sqlStatement); | |
| while ($this->Database->NextRecord()) { | |
| if (!isset($_distinctValueMap[$_fieldNameReference])) { | |
| $_distinctValueMap[$_fieldNameReference] = array(); | |
| } | |
| $_distinctValueMap[$_fieldNameReference][] = $this->Database->Record[$_fieldNameReference]; | |
| } | |
| if (isset($_distinctValueMap[$_fieldNameReference]) && _is_array($_distinctValueMap[$_fieldNameReference])) { | |
| sort($_distinctValueMap[$_fieldNameReference]); | |
| } | |
| } | |
| $this->_distinctValueContainer = $_distinctValueMap; | |
| } | |
| /** | |
| * --------------------------------------------- | |
| * MULTIGROUP STATEMENT | |
| * --------------------------------------------- | |
| */ | |
| if (_is_array($this->_returnMultiGroupByFields)) { | |
| $_combinedWhereClauseList = $_fieldWhereClauseList = $_finalMultiWhereClauseList = $_activeMultiWhereClauseList = array(); | |
| $_isFirst = true; | |
| $_totalMultiFields = count($this->_returnMultiGroupByFields); | |
| foreach ($this->_returnMultiGroupByFields as $_index => $_multiGroupByField) { | |
| if (!isset($this->_distinctValueContainer[$_multiGroupByField[1]])) { | |
| continue; | |
| } | |
| foreach ($this->_distinctValueContainer[$_multiGroupByField[1]] as $_distinctValue) { | |
| $_multiWhereComparison = $_multiGroupByField[2] . " = '" . $this->Database->Escape($_distinctValue) . "'"; | |
| if ($_distinctValue === NULL) { | |
| $_multiWhereComparison = $_multiGroupByField[2] . " IS NULL"; | |
| } | |
| $_distinctValue = $this->GetParsedDistinctValue($_multiGroupByField[1], $_distinctValue); | |
| if ($_isFirst) { | |
| $_fieldWhereClauseList[$_distinctValue] = $_multiWhereComparison; | |
| } else { | |
| foreach ($_activeMultiWhereClauseList[$_index-1] as $_key => $_val) { | |
| $_fieldWhereClauseList[$_key . '_' . $_distinctValue] = $_val . " AND " . $_multiWhereComparison; | |
| } | |
| } | |
| } | |
| $_activeMultiWhereClauseList[$_index] = $_fieldWhereClauseList; | |
| $_combinedWhereClauseList = array_merge($_combinedWhereClauseList, $_fieldWhereClauseList); | |
| $_fieldWhereClauseList = array(); | |
| $_isFirst = false; | |
| } | |
| if (isset($_activeMultiWhereClauseList[$_totalMultiFields-1])) { | |
| $_finalMultiWhereClauseList = $_activeMultiWhereClauseList[$_totalMultiFields-1]; | |
| } | |
| ksort($_finalMultiWhereClauseList, SORT_STRING); | |
| $_finalSQLStatementList = array(); | |
| $_sqlStatement = ''; | |
| $_sqlExpressions = array_merge($_sqlExpressions, $this->_autoSQLExpressionList, $this->_autoDistinctSQLExpressionList); | |
| $_sqlStatement = 'SELECT ' . implode(', ', $_sqlExpressions) . ' FROM ' . TABLE_PREFIX . $_sqlPrimaryTable . ' AS ' . $_sqlPrimaryTable; | |
| if (count($_sqlJoinList)) { | |
| $_sqlStatement .= ' LEFT JOIN ' . implode(' LEFT JOIN ', $_sqlJoinList); | |
| } | |
| if (count($_sqlWhereClauses)) { | |
| $_sqlStatement .= ' WHERE ' . implode('', $_sqlWhereClauses) . ' AND %extendedmultiwhereclause%'; | |
| // Always add WHERE clause | |
| } else { | |
| $_sqlStatement .= ' WHERE %extendedmultiwhereclause%'; | |
| } | |
| if (count($_sqlSortOrderExpressions)) { | |
| $_sqlStatement .= ' ORDER BY ' . implode(', ', $_sqlSortOrderExpressions); | |
| } | |
| if (isset($_chunksContainer['limit_clause']) && _is_array($_chunksContainer['limit_clause'])) { | |
| $_sqlStatement .= ' LIMIT ' . intval($_chunksContainer['limit_clause']['start']) . ', ' . intval($_chunksContainer['limit_clause']['length']); | |
| } | |
| foreach ($_finalMultiWhereClauseList as $_statementTitle => $_extendedMultiWhereClause) { | |
| $_finalSQLStatementList[$_statementTitle] = str_replace('%extendedmultiwhereclause%', $_extendedMultiWhereClause, $_sqlStatement); | |
| } | |
| return $_finalSQLStatementList; | |
| /** | |
| * --------------------------------------------- | |
| * MATRIX STATEMENT | |
| * --------------------------------------------- | |
| */ | |
| } else if (_is_array($this->_returnGroupByXFields)) { | |
| $_combinedWhereClauseList = $_fieldWhereClauseList = $_finalMatrixWhereClauseList = $_activeMatrixWhereClauseList = array(); | |
| $_isFirst = true; | |
| $_totalMatrixFields = count($this->_returnGroupByXFields); | |
| foreach ($this->_returnGroupByXFields as $_index => $_groupByXField) { | |
| if (!isset($this->_distinctValueContainer[$_groupByXField[1]])) { | |
| continue; | |
| } | |
| foreach ($this->_distinctValueContainer[$_groupByXField[1]] as $_distinctValue) { | |
| $_matrixWhereComparison = $_groupByXField[2] . " = '" . $this->Database->Escape($_distinctValue) . "'"; | |
| if ($_distinctValue === NULL) { | |
| $_matrixWhereComparison = $_groupByXField[2] . " IS NULL"; | |
| } | |
| if ($_isFirst) { | |
| $_fieldWhereClauseList[$_groupByXField[1] . ':' . $_distinctValue] = $_matrixWhereComparison; | |
| } else { | |
| foreach ($_activeMatrixWhereClauseList[$_index-1] as $_key => $_val) { | |
| $_fieldWhereClauseList[$_key . '_' . $_groupByXField[1] . ':' . $_distinctValue] = $_val . " AND " . $_matrixWhereComparison; | |
| } | |
| } | |
| } | |
| $_activeMatrixWhereClauseList[$_index] = $_fieldWhereClauseList; | |
| $_combinedWhereClauseList = array_merge($_combinedWhereClauseList, $_fieldWhereClauseList); | |
| $_fieldWhereClauseList = array(); | |
| $_isFirst = false; | |
| } | |
| if (isset($_activeMatrixWhereClauseList[$_totalMatrixFields-1])) { | |
| $_finalMatrixWhereClauseList = $_activeMatrixWhereClauseList[$_totalMatrixFields-1]; | |
| } | |
| ksort($_finalMatrixWhereClauseList, SORT_STRING); | |
| $_finalSQLStatementList = array(); | |
| $_sqlStatement = ''; | |
| $_sqlExpressions = array_merge($_sqlExpressions, $this->_autoSQLExpressionList, $this->_autoDistinctSQLExpressionList); | |
| $_sqlStatement = 'SELECT ' . implode(', ', $_sqlExpressions) . ' FROM ' . TABLE_PREFIX . $_sqlPrimaryTable . ' AS ' . $_sqlPrimaryTable; | |
| if (count($_sqlJoinList)) { | |
| $_sqlStatement .= ' LEFT JOIN ' . implode(' LEFT JOIN ', $_sqlJoinList); | |
| } | |
| if (count($_sqlWhereClauses)) { | |
| $_sqlStatement .= ' WHERE ' . implode('', $_sqlWhereClauses) ; | |
| if (isset($_finalMatrixWhereClauseList) && _is_array($_finalMatrixWhereClauseList)) { | |
| $_sqlStatement .= ' AND %extendedmatrixwhereclause%'; | |
| } | |
| // Always add WHERE clause | |
| } else { | |
| if (isset($_finalMatrixWhereClauseList) && _is_array($_finalMatrixWhereClauseList)) { | |
| $_sqlStatement .= ' WHERE %extendedmatrixwhereclause%'; | |
| } | |
| } | |
| if (count($_sqlGroupByExpressions)) { | |
| $_sqlStatement .= ' GROUP BY ' . implode(', ', $_sqlGroupByExpressions); | |
| } | |
| if (count($_sqlSortOrderExpressions)) { | |
| $_sqlStatement .= ' ORDER BY ' . implode(', ', $_sqlSortOrderExpressions); | |
| } | |
| if (isset($_chunksContainer['limit_clause']) && _is_array($_chunksContainer['limit_clause'])) { | |
| $_sqlStatement .= ' LIMIT ' . intval($_chunksContainer['limit_clause']['start']) . ', ' . intval($_chunksContainer['limit_clause']['length']); | |
| } | |
| if (isset($_finalMatrixWhereClauseList) && _is_array($_finalMatrixWhereClauseList)) { | |
| foreach ($_finalMatrixWhereClauseList as $_statementTitle => $_extendedMatrixWhereClause) { | |
| $_finalSQLStatementList[$_statementTitle] = str_replace('%extendedmatrixwhereclause%', $_extendedMatrixWhereClause, $_sqlStatement); | |
| } | |
| return $_finalSQLStatementList; | |
| } else { | |
| return $_sqlStatement; | |
| } | |
| } | |
| /** | |
| * --------------------------------------------- | |
| * BASIC STATEMENT | |
| * --------------------------------------------- | |
| */ | |
| $_sqlStatement = ''; | |
| $_sqlExpressions = array_merge($_sqlExpressions, $this->_autoSQLExpressionList); | |
| $_sqlStatement = 'SELECT ' . implode(', ', $_sqlExpressions) . ' FROM ' . TABLE_PREFIX . $_sqlPrimaryTable . ' AS ' . $_sqlPrimaryTable; | |
| if (count($_sqlJoinList)) { | |
| $_sqlStatement .= ' LEFT JOIN ' . implode(' LEFT JOIN ', $_sqlJoinList); | |
| } | |
| if (count($_sqlWhereClauses)) { | |
| $_sqlStatement .= ' WHERE ' . implode('', $_sqlWhereClauses); | |
| } | |
| if (count($_sqlGroupByExpressions)) { | |
| $_sqlStatement .= ' GROUP BY ' . implode(', ', $_sqlGroupByExpressions); | |
| } | |
| if (count($_sqlSortOrderExpressions)) { | |
| $_sqlStatement .= ' ORDER BY ' . implode(', ', $_sqlSortOrderExpressions); | |
| } | |
| if (isset($_chunksContainer['limit_clause']) && _is_array($_chunksContainer['limit_clause'])) { | |
| $_sqlStatement .= ' LIMIT ' . intval($_chunksContainer['limit_clause']['start']) . ', ' . intval($_chunksContainer['limit_clause']['length']); | |
| } | |
| return $_sqlStatement; | |
| } | |
| /** | |
| * Process Parent Arguments | |
| * | |
| * @author Varun Shoor | |
| * @param array $_argsContainer | |
| * @param string $_sqlPrimaryTable | |
| * @param array $_sqlTableList | |
| * @return array SQL Expression Container | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| protected function ProcessParentArguments($_argsContainer, $_sqlPrimaryTable, $_sqlTableList, $_argumentDepth) | |
| { | |
| if (!$this->GetIsClassLoaded()) { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } | |
| $_sqlExpressions = array(); | |
| foreach ($_argsContainer['args'] as $_key => $_argument) { | |
| $_aliasName = ''; | |
| if (isset($_argsContainer['alias']) && $_argumentDepth == 0) { | |
| $_aliasName = $this->Database->Escape(Clean($_argsContainer['alias'])); | |
| } | |
| $_opsPrefix = ''; | |
| if (isset($_argsContainer['ops'])) { | |
| $_opsHandle = $_key-1; | |
| if (isset($_argsContainer['ops'][$_opsHandle])) { | |
| $_opsPrefix = ' ' . $_argsContainer['ops'][$_opsHandle] . ' '; | |
| } | |
| } | |
| // Whenever the select expression is enclosed in quotes | |
| // 'Tickets.Ticket ID' OR 'tickets.ticketid' | |
| if (isset($_argument['value']) && isset($_argument['type']) && $_argument['type'] == 'text_val') { | |
| if (!strpos($_argument['value'], '.')) { | |
| $_sqlExpressions[] = $_opsPrefix . $this->GetExpressionFromTableNameAndColumn($_sqlPrimaryTable, $_argument['value'], $_sqlTableList, $_aliasName, false); | |
| } else { | |
| $_sqlExpressions[] = $_opsPrefix . $this->GetExpressionFromTableNameAndColumn(substr($_argument['value'], 0, strpos($_argument['value'], '.')), substr($_argument['value'], strpos($_argument['value'], '.')+1), $_sqlTableList, $_aliasName, false); | |
| } | |
| } else if (isset($_argument['value']) && isset($_argument['type']) && $_argument['type'] == 'int_val') { | |
| $_sqlExpressions[] = $_opsPrefix . floatval($_argument['value']); | |
| // Complete Fetch | |
| // SELECT * | |
| } else if (isset($_argument['value']) && isset($_argument['type']) && $_argument['type'] == '*') { | |
| throw new SWIFT_Exception('Cannot retrieve via *, please specify an exact field name.'); | |
| $_sqlExpressions[] = $_opsPrefix . $_sqlPrimaryTable . '.*'; | |
| // Basic Column Expression | |
| // ticketid OR tickets.ticketid, tickets.* | |
| // Contains table, column & alias | |
| } else if (isset($_argument['column']) && !in_array($_argument['column'], self::$_disallowedColumns)) { | |
| if (!empty($_argument['table'])) { | |
| $_sqlExpressions[] = $_opsPrefix . $this->GetExpressionFromTableNameAndColumn($_argument['table'], $_argument['column'], $_sqlTableList, $_aliasName); | |
| } else { | |
| $_sqlExpressions[] = $_opsPrefix . $_argument['column']; | |
| // Commented to support work on aliases like Open+InProgress+Closed | |
| // $_sqlExpressions[] = $_opsPrefix . $this->GetExpressionFromTableNameAndColumn($_sqlPrimaryTable, $_argument['column'], $_sqlTableList, $_aliasName); | |
| } | |
| // Select Expression with Function Call | |
| // COUNT(tickets.ticketid) or COUNT(ticketid) | |
| // Can contain alias for expressions like: COUNT(tickets.ticketid) AS ticketid | |
| } else if (isset($_argument['name']) && isset($_argument['arg'])) { | |
| $_nestedArguments = array(); | |
| if (isset($_argument['args'])) { | |
| $_nestedArguments = $_argument['args']; | |
| } | |
| $_sqlExpressions[] = $_opsPrefix . $this->GetExpressionFromFunction($_argument['name'], $_argument['arg'], $_nestedArguments, $_sqlTableList, $_sqlPrimaryTable, $_aliasName, false); | |
| } else if (isset($_argument['args'])) { | |
| $_expressionSuffix = ''; | |
| if (isset($_argument['alias']) && !empty($_argument['alias'])) { | |
| $_expressionSuffix = ' AS ' . $this->Database->Escape(Clean($_argument['alias'])); | |
| } | |
| $_ops = ''; | |
| if (!isset($_argument['ops'])) { | |
| $_ops = ', '; | |
| } | |
| $_sqlExpressions[] = '(' . implode($_ops, $this->ProcessParentArguments($_argument, $_sqlPrimaryTable, $_sqlTableList, ($_argumentDepth+1))) . ')' . $_expressionSuffix; | |
| } | |
| } | |
| return $_sqlExpressions; | |
| } | |
| /** | |
| * Process Arguments for Where Clause | |
| * | |
| * @author Varun Shoor | |
| * @param array $_argsContainer | |
| * @param string $_sqlPrimaryTable | |
| * @param array $_sqlTableList | |
| * @return array SQL Expression Container | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| protected function ProcessWhereArguments($_argsContainer, $_sqlPrimaryTable, $_sqlTableList, $_argumentDepth) | |
| { | |
| if (!$this->GetIsClassLoaded()) { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } | |
| $_whereExpressions = array(); | |
| foreach ($_argsContainer['args'] as $_key => $_argument) { | |
| $_opsPrefix = $_operator = ''; | |
| $_activeTableName = $_activeColumnName = ''; | |
| if (isset($_argsContainer['ops'])) { | |
| $_opsHandle = $_key-1; | |
| if (isset($_argsContainer['ops'][$_opsHandle])) { | |
| $_opsPrefix = ' ' . trim(mb_strtoupper($_argsContainer['ops'][$_opsHandle])) . ' '; | |
| $_operator = trim(mb_strtolower($_argsContainer['ops'][$_opsHandle])); | |
| } | |
| } | |
| // Whenever the where expression is enclosed in quotes | |
| // 'Tickets.Ticket ID' OR 'tickets.ticketid' | |
| if (isset($_argument['value']) && isset($_argument['type']) && $_argument['type'] == 'text_val') { | |
| // Is it a function? | |
| $_functionMatches = array(); | |
| if (preg_match('/^(.*)\((.*)?\)$/i', $_argument['value'], $_functionMatches)) { | |
| $_whereExpressionResult = $this->GetWhereExpressionFromFunction(mb_strtolower($_functionMatches[1]), self::ParseArgumentIntoArray($_functionMatches[2]), $_sqlTableList, $_sqlPrimaryTable, $_opsPrefix); | |
| if (_is_array($_whereExpressionResult)) { | |
| $_whereExpressions[] = ' ' . $_whereExpressionResult[0] . ' ' . $_whereExpressionResult[1]; | |
| } else { | |
| $_whereExpressions[] = $_opsPrefix . $_whereExpressionResult; | |
| } | |
| } else { | |
| // Is it a valid operator? | |
| if (in_array($_operator, self::$_allowedBasicOperators) || in_array($_operator, self::$_allowedTextOperators)) { | |
| if (isset($_argsContainer['args'][$_key-1]['value']) && $_argsContainer['args'][$_key-1]['type'] == 'text_val') { | |
| $_activeTableContainer = $this->GetTableAndFieldNameOnText($_argsContainer['args'][$_key-1]['value']); | |
| if (isset($_activeTableContainer[0]) && !empty($_activeTableContainer[0])) { | |
| $_activeTableName = $_activeTableContainer[0]; | |
| $_activeColumnName = $_activeTableContainer[1]; | |
| } | |
| $_whereExpressions[] = $_opsPrefix . $this->GetWhereValue($_argument['value'], $_activeTableName, $_activeColumnName, $_sqlTableList, $_operator); | |
| } | |
| // This is a column then | |
| } else { | |
| if (!strpos($_argument['value'], '.')) { | |
| $_whereExpressions[] = $_opsPrefix . $this->GetWhereExpressionFromTableNameAndColumn($_sqlPrimaryTable, $_argument['value'], $_sqlTableList, false, false); | |
| } else { | |
| $_whereExpressions[] = $_opsPrefix . $this->GetWhereExpressionFromTableNameAndColumn(substr($_argument['value'], 0, strpos($_argument['value'], '.')), substr($_argument['value'], strpos($_argument['value'], '.')+1), $_sqlTableList, false); | |
| } | |
| } | |
| } | |
| // Integer Value | |
| } else if (isset($_argument['value']) && isset($_argument['type']) && $_argument['type'] == 'int_val') { | |
| $_whereExpressions[] = $_opsPrefix . floatval($_argument['value']); | |
| // Multiple Values: IN, NOT IN | |
| } else if (isset($_argument['values']) && isset($_argument['types'])) { | |
| if (isset($_argsContainer['args'][$_key-1]['value']) && $_argsContainer['args'][$_key-1]['type'] == 'text_val') { | |
| $_activeTableContainer = $this->GetTableAndFieldNameOnText($_argsContainer['args'][$_key-1]['value']); | |
| if (isset($_activeTableContainer[0]) && !empty($_activeTableContainer[0])) { | |
| $_activeTableName = $_activeTableContainer[0]; | |
| $_activeColumnName = $_activeTableContainer[1]; | |
| } | |
| } | |
| $_whereExpressions[] = $this->GetWhereExpressionForMultipleValues($_argument, $_opsPrefix, $_activeTableName, $_activeColumnName, $_operator); | |
| // Basic Column Expression | |
| // ticketid OR tickets.ticketid, tickets.* | |
| // Contains table, column & alias | |
| } else if (isset($_argument['column'])) { | |
| if (!empty($_argument['table'])) { | |
| $_whereExpressions[] = $_opsPrefix . $this->GetWhereExpressionFromTableNameAndColumn($_argument['table'], $_argument['column'], $_sqlTableList, false); | |
| } else { | |
| $_whereExpressions[] = $_opsPrefix . $this->GetWhereExpressionFromTableNameAndColumn($_sqlPrimaryTable, $_argument['column'], $_sqlTableList, false); | |
| } | |
| // Select Expression with Function Call | |
| // COUNT(tickets.ticketid) or COUNT(ticketid) | |
| // Can contain alias for expressions like: COUNT(tickets.ticketid) AS ticketid | |
| } else if (isset($_argument['name']) && isset($_argument['arg'])) { | |
| $_whereExpressionResult = $this->GetWhereExpressionFromFunction($_argument['name'], $_argument['arg'], $_sqlTableList, $_sqlPrimaryTable, $_opsPrefix); | |
| if (_is_array($_whereExpressionResult)) { | |
| $_whereExpressions[] = ' ' . $_whereExpressionResult[0] . ' ' . $_whereExpressionResult[1]; | |
| } else { | |
| $_whereExpressions[] = $_opsPrefix . $_whereExpressionResult; | |
| } | |
| } else if (isset($_argument['args'])) { | |
| $_ops = ''; | |
| if (!isset($_argument['ops'])) { | |
| $_ops = ' AND '; | |
| } | |
| $_whereExpressions[] = $_opsPrefix . '(' . implode($_ops, $this->ProcessWhereArguments($_argument, $_sqlPrimaryTable, $_sqlTableList, ($_argumentDepth+1))) . ')'; | |
| } | |
| } | |
| return $_whereExpressions; | |
| } | |
| /** | |
| * Get the WHERE value | |
| * | |
| * @author Varun Shoor | |
| * @param string $_value | |
| * @param string $_tableName | |
| * @param string $_columnName | |
| * @param array $_tableList | |
| * @param string $_operator | |
| * @return bool "true" on Success, "false" otherwise | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| protected function GetWhereValue($_value, $_tableName, $_columnName, $_tableList, $_operator) | |
| { | |
| if (!$this->GetIsClassLoaded()) { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } | |
| $_tableName = $this->Database->Escape(Clean($this->GetTableNameOnLabel(mb_strtolower($_tableName)))); | |
| $_columnName = $this->Database->Escape(mb_strtolower($_columnName)); | |
| $_returnValue = false; | |
| if (empty($_tableName)) { | |
| $_returnValue = true; | |
| } else if (!empty($_tableName) && empty($_columnName)) { | |
| $_returnValue = true; | |
| } else if (!isset($this->_schemaContainer[$_tableName])) { | |
| $_returnValue = true; | |
| } else if (!in_array($_tableName, $_tableList) && !in_array($_tableName, $this->_autoJoinTableList)) { | |
| $_returnValue = true; | |
| } | |
| if ($_returnValue) { | |
| return "'" . $_value . "'"; | |
| } | |
| // Check to see that the field exists under table | |
| if (isset($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName])) { | |
| if ($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_TYPE] == SWIFT_KQLSchema::FIELDTYPE_LINKED) { | |
| return "'" . $this->GetLinkedFieldValue($_tableName, $_columnName, $this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_LINKEDTO], $_value, $_operator) . "'"; | |
| } else if ($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_TYPE] == SWIFT_KQLSchema::FIELDTYPE_CUSTOM) { | |
| return "'" . $this->GetCustomFieldValue($_tableName, $_columnName, $_value) . "'"; | |
| } | |
| } | |
| // Now if the column doesnt exist, it either is being used as a label or its an invalid column, attempt to look it up using label | |
| foreach ($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS] as $_columnNameLoop => $_columnContainer) { | |
| $_columnLabel = mb_strtolower(SWIFT_KQLSchema::GetLabel($_tableName . '_' . $_columnNameLoop)); | |
| // Attempt on just column name | |
| if (empty($_columnLabel)) { | |
| $_columnLabel = mb_strtolower(SWIFT_KQLSchema::GetLabel($_columnNameLoop)); | |
| } | |
| if (!empty($_columnLabel) && $_columnLabel == mb_strtolower($_columnName)) { | |
| if ($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnNameLoop][SWIFT_KQLSchema::FIELD_TYPE] == SWIFT_KQLSchema::FIELDTYPE_LINKED) { | |
| return "'" . $this->GetLinkedFieldValue($_tableName, $_columnNameLoop, $this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnNameLoop][SWIFT_KQLSchema::FIELD_LINKEDTO], $_value, $_operator) . "'"; | |
| } else { | |
| return "'" . $this->GetCustomFieldValue($_tableName, $_columnNameLoop, $_value) . "'"; | |
| } | |
| break; | |
| } | |
| } | |
| return "'" . $_value . "'"; | |
| } | |
| /** | |
| * Get the Custom Field Value | |
| * | |
| * @author Varun Shoor | |
| * @param string $_tableName | |
| * @param string $_columnName | |
| * @param string $_fieldValue | |
| * @return bool "true" on Success, "false" otherwise | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| private function GetCustomFieldValue($_tableName, $_columnName, $_fieldValue) | |
| { | |
| if (!$this->GetIsClassLoaded()) { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } | |
| $_fieldValue = trim($this->Database->Escape(stripslashes($_fieldValue))); // Sanitize the string | |
| /** | |
| * BUG FIX: Parminder Singh | |
| * | |
| * SWIFT-1860: [Notice]: Undefined offset: 5 (KQL/class.SWIFT_KQLParser.php:995) | |
| * | |
| * Comments: Added isset check | |
| */ | |
| if (isset($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_CUSTOMVALUES])) { | |
| foreach ($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_CUSTOMVALUES] as $_actualValue => $_localeString) { | |
| $_localeValue = SWIFT_KQLSchema::GetLabel($_localeString); | |
| // Do the final values match? | |
| if (mb_strtolower($_localeValue) == mb_strtolower($_fieldValue)) { | |
| return $_actualValue; | |
| } | |
| } | |
| } | |
| return $_fieldValue; | |
| } | |
| /** | |
| * Get the Linked Field Value | |
| * | |
| * @author Varun Shoor | |
| * @param string $_tableName | |
| * @param string $_columnName | |
| * @param array $_linkedFieldContainer The KQL Schema Linked Field Container: array(linked field, field value fetcher, extended where clause) | |
| * @param string $_fieldValue | |
| * @param string $_operator | |
| * @return bool "true" on Success, "false" otherwise | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| private function GetLinkedFieldValue($_tableName, $_columnName, $_linkedFieldContainer, $_fieldValue, $_operator) | |
| { | |
| if (!$this->GetIsClassLoaded()) { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } else if (!isset($_linkedFieldContainer[0]) || !isset($_linkedFieldContainer[1])) { | |
| throw new SWIFT_Exception('Invalid Linked Field Container'); | |
| } | |
| $_fieldValue = $this->Database->Escape(stripslashes($_fieldValue)); // Sanitize the string | |
| // If this is numeric, we return as is | |
| if (is_numeric($_fieldValue)) { | |
| return $_fieldValue; | |
| } | |
| $_columnValueContainer = $this->GetTableAndFieldNameOnText($_linkedFieldContainer[1]); | |
| if (!isset($_columnValueContainer[0]) || empty($_columnValueContainer[0])) { | |
| throw new SWIFT_Exception('Invalid Linked Value Column'); | |
| } | |
| $_extendedWhere = ''; | |
| if (isset($_linkedFieldContainer[2])) { | |
| $_extendedWhere = " AND " . $_linkedFieldContainer[2]; | |
| } | |
| $_resultValue = $this->Database->QueryFetch("SELECT " . $_linkedFieldContainer[0] . " AS resultvalue FROM " . TABLE_PREFIX . $_columnValueContainer[0] . " AS " . $_columnValueContainer[0] | |
| . " WHERE " . $_linkedFieldContainer[1] . " " . 'LIKE' . " '" . $_fieldValue . "'" . $_extendedWhere); | |
| if (isset($_resultValue['resultvalue'])) { | |
| return $_resultValue['resultvalue']; | |
| } | |
| return $_fieldValue; | |
| } | |
| /** | |
| * Retrieve the Where Expression for Multiple Values | |
| * | |
| * @author Varun Shoor | |
| * @param array $_argumentContainer | |
| * @param string $_opsPrefix | |
| * @param string $_tableName | |
| * @param string $_columnName | |
| * @param string $_operator | |
| * @return bool "true" on Success, "false" otherwise | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| protected function GetWhereExpressionForMultipleValues($_argumentContainer, $_opsPrefix, $_tableName, $_columnName, $_operator) | |
| { | |
| if (!$this->GetIsClassLoaded()) { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } | |
| $_opsPrefix = trim($_opsPrefix); | |
| $_prefix = $_suffix = ''; | |
| if ($_opsPrefix == 'IN' || $_opsPrefix == 'NOT IN') { | |
| $_prefix = $_opsPrefix . '('; | |
| $_suffix = ')'; | |
| } | |
| $_tableName = $this->Database->Escape(Clean($this->GetTableNameOnLabel(mb_strtolower($_tableName)))); | |
| $_columnName = $this->Database->Escape(mb_strtolower($_columnName)); | |
| $_returnValue = false; | |
| if (empty($_tableName)) { | |
| $_returnValue = true; | |
| } else if (!empty($_tableName) && empty($_columnName)) { | |
| $_returnValue = true; | |
| } else if (!isset($this->_schemaContainer[$_tableName])) { | |
| $_returnValue = true; | |
| } | |
| $_linkedFieldContainer = $_customFieldContainer = false; | |
| // Check to see that the field exists under table | |
| if (!$_returnValue && isset($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName])) { | |
| if ($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_TYPE] == SWIFT_KQLSchema::FIELDTYPE_LINKED) { | |
| $_linkedFieldContainer = $this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_LINKEDTO]; | |
| } else if ($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_TYPE] == SWIFT_KQLSchema::FIELDTYPE_CUSTOM) { | |
| $_customFieldContainer = array($_tableName, $_columnName); | |
| } | |
| } | |
| // Now if the column doesnt exist, it either is being used as a label or its an invalid column, attempt to look it up using label | |
| if (!$_returnValue) { | |
| foreach ($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS] as $_columnNameLoop => $_columnContainer) { | |
| $_columnLabel = mb_strtolower(SWIFT_KQLSchema::GetLabel($_tableName . '_' . $_columnNameLoop)); | |
| // Attempt on just column name | |
| if (empty($_columnLabel)) { | |
| $_columnLabel = mb_strtolower(SWIFT_KQLSchema::GetLabel($_columnNameLoop)); | |
| } | |
| if (!empty($_columnLabel) && $_columnLabel == mb_strtolower($_columnName)) { | |
| if ($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnNameLoop][SWIFT_KQLSchema::FIELD_TYPE] == SWIFT_KQLSchema::FIELDTYPE_LINKED) { | |
| $_linkedFieldContainer = $this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnNameLoop][SWIFT_KQLSchema::FIELD_LINKEDTO]; | |
| } else if ($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnNameLoop][SWIFT_KQLSchema::FIELD_TYPE] == SWIFT_KQLSchema::FIELDTYPE_CUSTOM) { | |
| $_customFieldContainer = array($_tableName, $_columnNameLoop); | |
| } | |
| break; | |
| } | |
| } | |
| } | |
| $_argumentList = array(); | |
| foreach ($_argumentContainer['values'] as $_key => $_val) { | |
| if (_is_array($_customFieldContainer)) { | |
| $_argumentList[] = "'" . $this->GetCustomFieldValue($_customFieldContainer[0], $_customFieldContainer[1], $_val) . "'"; | |
| continue; | |
| } | |
| $_dispatchValue = ''; | |
| if (isset($_argumentContainer['types'][$_key]) && $_argumentContainer['types'][$_key] == 'int_val') { | |
| $_argumentList[] = $this->Database->Escape($_val); | |
| } else if (isset($_argumentContainer['types'][$_key]) && $_argumentContainer['types'][$_key] == 'text_val') { | |
| $_dispatchValue = "'" . $this->Database->Escape($_val) . "'"; | |
| } else { | |
| $_dispatchValue = "'" . $this->Database->Escape($_val) . "'"; | |
| } | |
| if (!empty($_dispatchValue) && !$_returnValue && _is_array($_linkedFieldContainer)) { | |
| $_argumentList[] = "'" . $this->GetLinkedFieldValue($_tableName, $_columnName, $_linkedFieldContainer, $_val, $_operator) . "'"; | |
| } else if (!empty($_dispatchValue)) { | |
| $_argumentList[] = $_dispatchValue; | |
| } | |
| } | |
| $_finalValue = ' ' . $_prefix . implode(', ', $_argumentList) . $_suffix; | |
| return $_finalValue; | |
| } | |
| /** | |
| * Get Expression From Function Call | |
| * | |
| * @author Varun Shoor | |
| * @param string $_functionName | |
| * @param array $_arguments | |
| * @param array $_nestedArguments | |
| * @param array $_tableList | |
| * @param string $_sqlPrimaryTable | |
| * @param string $_alias (OPTIONAL) | |
| * @return bool "true" on Success, "false" otherwise | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| protected function GetExpressionFromFunction($_functionName, $_arguments, $_nestedArguments, $_tableList, $_sqlPrimaryTable, $_alias = '') | |
| { | |
| if (!$this->GetIsClassLoaded()) { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } | |
| if (_is_array($_nestedArguments)) { | |
| foreach ($_nestedArguments as $_key => $_argument) { | |
| $_dispatchNestedArguments = array(); | |
| if (isset($_argument['args'])) { | |
| $_dispatchNestedArguments = $_argument['args']; | |
| } | |
| return mb_strtoupper($_functionName) . '(' . $this->GetExpressionFromFunction($_argument['name'], $_argument['arg'], $_dispatchNestedArguments, $_tableList, $_sqlPrimaryTable, '', false) . ')' . IIF(!empty($_alias), ' AS ' . $_alias); | |
| } | |
| } | |
| $_functionName = $this->Database->Escape(Clean(mb_strtolower($_functionName))); | |
| // Commented because a check is needed for base functions like IF, COUNT, SUM too! | |
| // if (!in_array($_functionName, self::$_allowedFunctionList) && !in_array($_functionName, self::$_allowedExtendedFunctionList)) { | |
| // throw new SWIFT_Exception('Invalid Function: ' . $_functionName); | |
| // } | |
| if (in_array($_functionName, self::$_allowedExtendedFunctionList)) { | |
| return $this->ParseExtendedFunction($_functionName, $_arguments, $_tableList, $_sqlPrimaryTable); | |
| } | |
| $_alias = $this->Database->Escape(Clean($_alias)); | |
| $_finalArgumentList = array(); | |
| foreach ($_arguments as $_argument) { | |
| $_finalArgumentList[] = $_argument; | |
| } | |
| return mb_strtoupper($_functionName) . '(' . implode(', ' , $_finalArgumentList) . ')' . IIF(!empty($_alias), ' AS ' . $_alias); | |
| } | |
| /** | |
| * Get Expression From Function Call | |
| * | |
| * @author Varun Shoor | |
| * @param string $_functionName | |
| * @param array $_arguments | |
| * @param array $_tableList | |
| * @param string $_sqlPrimaryTable | |
| * @param string $_opsPrefix | |
| * @return bool "true" on Success, "false" otherwise | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| protected function GetWhereExpressionFromFunction($_functionName, $_arguments, $_tableList, $_sqlPrimaryTable, $_opsPrefix) | |
| { | |
| if (!$this->GetIsClassLoaded()) { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } | |
| $_operator = trim($_opsPrefix); | |
| $_functionName = $this->Database->Escape(Clean(mb_strtolower($_functionName))); | |
| if (!in_array($_functionName, self::$_allowedFunctionList) && !in_array($_functionName, self::$_allowedExtendedFunctionList)) { | |
| throw new SWIFT_Exception('Invalid Function: ' . $_functionName); | |
| } | |
| if (in_array($_functionName, self::$_allowedExtendedFunctionList)) { | |
| return $this->ParseExtendedFunction($_functionName, $_arguments, $_tableList, $_sqlPrimaryTable, $_operator); | |
| } | |
| $_alias = $this->Database->Escape(Clean($_alias)); | |
| $_finalArgumentList = array(); | |
| foreach ($_arguments as $_argument) { | |
| if (!strpos($_argument, '.')) { | |
| $_finalArgumentList[] = $this->GetWhereExpressionFromTableNameAndColumn($_sqlPrimaryTable, $_argument, $_tableList, false); | |
| } else { | |
| $_finalArgumentList[] = $this->GetWhereExpressionFromTableNameAndColumn(substr($_argument, 0, strpos($_argument, '.')), substr($_argument, strpos($_argument, '.')+1), $_tableList, false); | |
| } | |
| } | |
| return mb_strtoupper($_functionName) . '(' . implode(', ' , $_finalArgumentList) . ')'; | |
| } | |
| /** | |
| * Parse Extended Function | |
| * | |
| * @author Varun Shoor | |
| * @param string $_functionName | |
| * @param array $_arguments | |
| * @param array $_tableList | |
| * @param string $_sqlPrimaryTable | |
| * @param string $_operator | |
| * @return bool "true" on Success, "false" otherwise | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| public function ParseExtendedFunction($_functionName, $_arguments, $_tableList, $_sqlPrimaryTable, $_operator) | |
| { | |
| if (!$this->GetIsClassLoaded()) { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } | |
| $_SWIFT = SWIFT::GetInstance(); | |
| /** | |
| * --------------------------------------------- | |
| * Process the primary functions first | |
| * --------------------------------------------- | |
| */ | |
| switch ($_functionName) { | |
| case 'customfield': | |
| return '0'; | |
| break; | |
| case 'mktime': | |
| return call_user_func_array('mktime', $_arguments); | |
| case 'datenow': | |
| return DATENOW; | |
| } | |
| /** | |
| * --------------------------------------------- | |
| * If we are using equal as an operator | |
| * --------------------------------------------- | |
| */ | |
| if ($_operator == '=' || $_operator == '!=') { | |
| $_returnOperator = 'BETWEEN'; | |
| if ($_operator == '!=') { | |
| $_returnOperator = 'NOT BETWEEN'; | |
| } | |
| switch ($_functionName) { | |
| case 'today': | |
| return array($_returnOperator, "'" . SWIFT_Date::FloorDate(strtotime('today')) . "' AND '" . SWIFT_Date::CeilDate(strtotime('today')) . "'"); | |
| case 'yesterday': | |
| return array($_returnOperator, "'" . SWIFT_Date::FloorDate(strtotime('yesterday')) . "' AND '" . SWIFT_Date::CeilDate(strtotime('yesterday')) . "'"); | |
| case 'tomorrow': | |
| return array($_returnOperator, "'" . SWIFT_Date::FloorDate(strtotime('tomorrow')) . "' AND '" . SWIFT_Date::CeilDate(strtotime('tomorrow')) . "'"); | |
| case 'last7days': | |
| return array($_returnOperator, "'" . SWIFT_Date::FloorDate(strtotime('-7 days')) . "' AND '" . SWIFT_Date::CeilDate(strtotime('today')) . "'"); | |
| case 'lastweek': | |
| return array($_returnOperator, "'" . SWIFT_Date::FloorDate(strtotime('-1 week')) . "' AND '" . SWIFT_Date::CeilDate(strtotime('today')) . "'"); | |
| case 'thisweek': | |
| return array($_returnOperator, "'" . SWIFT_Date::FloorDate(strtotime('this week', DATENOW)) . "' AND '" . SWIFT_Date::CeilDate(strtotime('today')) . "'"); | |
| case 'endofweek': | |
| return array($_returnOperator, "'" . SWIFT_Date::FloorDate(strtotime('monday', strtotime('this week', DATENOW))) . "' AND '" . SWIFT_Date::CeilDate(strtotime('sunday', strtotime('this week', DATENOW))) . "'"); | |
| case 'nextweek': | |
| return array($_returnOperator, "'" . SWIFT_Date::FloorDate(strtotime('next week', DATENOW)) . "' AND '" . SWIFT_Date::CeilDate(strtotime('+6 days', strtotime('next week', DATENOW))) . "'"); | |
| case 'lastmonth': | |
| return array($_returnOperator, "'" . SWIFT_Date::FloorDate(strtotime('first day of last month', DATENOW)) . "' AND '" . SWIFT_Date::CeilDate(strtotime('last day of last month', DATENOW)) . "'"); | |
| case 'thismonth': | |
| // Check Month Name is Specified in Arguments, e.g. January 2010, August etc. | |
| if (isset($_arguments[0])) { | |
| $_monthName = $this->Database->Escape($_arguments[0]); | |
| return array($_returnOperator, "'" . SWIFT_Date::FloorDate(strtotime('first day of ' . $_monthName)) . "' AND '" . SWIFT_Date::CeilDate(strtotime('last day of ' . $_monthName)) . "'"); | |
| } else { | |
| return array($_returnOperator, "'" . SWIFT_Date::FloorDate(strtotime('first day of this month', DATENOW)) . "' AND '" . SWIFT_Date::CeilDate(strtotime('last day of this month', DATENOW)) . "'"); | |
| } | |
| case 'nextmonth': | |
| return array($_returnOperator, "'" . SWIFT_Date::FloorDate(strtotime('first day of next month', DATENOW)) . "' AND '" . SWIFT_Date::CeilDate(strtotime('last day of next month', DATENOW)) . "'"); | |
| } | |
| } | |
| /** | |
| * --------------------------------------------- | |
| * Fallback | |
| * --------------------------------------------- | |
| */ | |
| switch ($_functionName) { | |
| case 'today': | |
| return "'" . strtotime('today') . "'"; | |
| case 'yesterday': | |
| return "'" . strtotime('yesterday') . "'"; | |
| case 'tomorrow': | |
| return "'" . strtotime('tomorrow') . "'"; | |
| case 'last7days': | |
| return "'" . SWIFT_Date::FloorDate(strtotime('-7 days')) . "'"; | |
| case 'lastweek': | |
| return "'" . SWIFT_Date::FloorDate(strtotime('-1 week')) . "'"; | |
| case 'thisweek': | |
| return "'" . SWIFT_Date::FloorDate(strtotime('this week', DATENOW)) . "'"; | |
| case 'endofweek': | |
| return "'" . SWIFT_Date::CeilDate(strtotime('sunday', strtotime('this week', DATENOW))) . "'"; | |
| case 'nextweek': | |
| return "'" . SWIFT_Date::FloorDate(strtotime('next week', DATENOW)) . "'"; | |
| case 'lastmonth': | |
| return "'" . SWIFT_Date::FloorDate(strtotime('first day of last month', DATENOW)) . "'"; | |
| case 'thismonth': | |
| return "'" . SWIFT_Date::FloorDate(strtotime('first day of this month', DATENOW)) . "'"; | |
| case 'nextmonth': | |
| return "'" . SWIFT_Date::FloorDate(strtotime('first day of next month', DATENOW)) . "'"; | |
| default: | |
| break; | |
| } | |
| return '0'; | |
| } | |
| /** | |
| * Retrieve the SQL Expression from Table Name and Column | |
| * | |
| * @author Varun Shoor | |
| * @param string $_tableName | |
| * @param string $_column | |
| * @param array $_tableList | |
| * @param string $_aliasName (OPTIONAL) | |
| * @param bool $_prefixTable (OPTIONAL) | |
| * @param bool $_isFunctionCall (OPTIONAL) | |
| * @return bool "true" on Success, "false" otherwise | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| protected function GetExpressionFromTableNameAndColumn($_tableName, $_column, $_tableList, $_aliasName = '', $_prefixTable = true, $_isFunctionCall = false) | |
| { | |
| $_originalColumn = $_column; | |
| $_tableName = $this->Database->Escape(Clean($this->GetTableNameOnLabel(mb_strtolower($_tableName)))); | |
| $_column = $this->Database->Escape(mb_strtolower($_column)); | |
| $_aliasName = $this->Database->Escape(Clean($_aliasName)); | |
| if ($_originalColumn == '*') { | |
| $_column = '*'; | |
| } | |
| if (!$this->GetIsClassLoaded()) { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } else if (empty($_tableName)) { | |
| throw new SWIFT_Exception('No Table Name Specified for SQL Expression'); | |
| } else if (!empty($_tableName) && empty($_column)) { | |
| throw new SWIFT_Exception('No Column specified for table: ' . $_tableName); | |
| } else if (!isset($this->_schemaContainer[$_tableName])) { | |
| throw new SWIFT_Exception('Table is not available in Schema: ' . $_tableName); | |
| } else if (!in_array($_tableName, $_tableList) && !in_array($_tableName, $this->_autoJoinTableList)) { | |
| throw new SWIFT_Exception('Table is not part of available table list: ' . $_tableName); | |
| } | |
| $_tablePrefix = ''; | |
| if ($_prefixTable) { | |
| $_tablePrefix = TABLE_PREFIX; | |
| } | |
| if (!in_array($_tableName, $this->_autoJoinTableList)) { | |
| $this->_autoJoinTableList[] = $_tableName; | |
| } | |
| // By now we have the table, check to see if its a blanket fetch | |
| if ($_column == '*') { | |
| throw new SWIFT_Exception('Cannot retrieve via *, please specify an exact field name.'); | |
| return $_tablePrefix . $_tableName . '.*'; | |
| } | |
| // Check to see that the field exists under table | |
| if (isset($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_column])) { | |
| if (!$_isFunctionCall && empty($_aliasName)) { | |
| $_aliasName = $_tableName . '_' . $_column; | |
| } | |
| $_linkedResult = $this->GetLinkedToTableAndColumnInfo($_tableName, $_column, $_aliasName, $_tablePrefix); | |
| if (!empty($_linkedResult)) { | |
| return $_linkedResult; | |
| } | |
| return $_tablePrefix . $_tableName . '.' . $_column . IIF(!empty($_aliasName), ' AS ' . $_aliasName); | |
| } | |
| // Now if the column doesnt exist, it either is being used as a label or its an invalid column, attempt to look it up using label | |
| foreach ($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS] as $_columnName => $_columnContainer) { | |
| $_columnLabel = mb_strtolower(SWIFT_KQLSchema::GetLabel($_tableName . '_' . $_columnName)); | |
| // Attempt on just column name | |
| if (empty($_columnLabel)) { | |
| $_columnLabel = mb_strtolower(SWIFT_KQLSchema::GetLabel($_columnName)); | |
| } | |
| if (!empty($_columnLabel) && $_columnLabel == mb_strtolower($_column)) { | |
| if (!$_isFunctionCall && empty($_aliasName)) { | |
| $_aliasName = $_tableName . '_' . $_columnName; | |
| } | |
| $_linkedResult = $this->GetLinkedToTableAndColumnInfo($_tableName, $_columnName, $_aliasName, $_tablePrefix); | |
| if (!empty($_linkedResult)) { | |
| return $_linkedResult; | |
| } | |
| return $_tablePrefix . $_tableName . '.' . $_columnName . IIF(!empty($_aliasName), ' AS ' . $_aliasName); | |
| } | |
| } | |
| // No go? | |
| throw new SWIFT_Exception('Invalid Column, column not found: ' . $_column); | |
| return true; | |
| } | |
| /** | |
| * Retrieve the Linked To Table Info and add to Auto Join | |
| * | |
| * @author Varun Shoor | |
| * @param string $_tableName | |
| * @param string $_columnName | |
| * @param string $_aliasName | |
| * @param string $_tablePrefix | |
| * @return bool "true" on Success, "false" otherwise | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| public function GetLinkedToTableAndColumnInfo($_tableName, $_columnName, $_aliasName, $_tablePrefix) | |
| { | |
| if (!$this->GetIsClassLoaded()) { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } | |
| $_tableName = $this->GetTableNameOnLabel($_tableName); | |
| if (!isset($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_LINKEDTO])) { | |
| return false; | |
| } | |
| // We have a linked field! | |
| $_joinField = $this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_LINKEDTO][0]; | |
| $_replacementField = $this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_LINKEDTO][1]; | |
| $_tableAndFieldContainer = $this->GetTableAndFieldNameOnText($_joinField); | |
| if (!isset($_tableAndFieldContainer[0]) || empty($_tableAndFieldContainer[0])) { | |
| throw new SWIFT_Exception('Invalid Join Table Name'); | |
| } | |
| $_joinTableName = $_tableAndFieldContainer[0]; | |
| $_joinFieldName = $_tableAndFieldContainer[1]; | |
| if (!in_array($_joinTableName, $this->_sqlTableList) && !in_array($_joinTableName, $this->_autoJoinTableList)) { | |
| $this->_autoJoinExpressionList[] = TABLE_PREFIX . $_joinTableName . ' AS ' . $_joinTableName . ' ON (' . $_tableName . '.' . $_columnName . ' = ' . $_joinField . ')'; | |
| $this->_sqlTableList[] = $_joinTableName; | |
| } | |
| return $_tablePrefix . $_replacementField . IIF(!empty($_aliasName), ' AS ' . $_aliasName); | |
| } | |
| /** | |
| * Retrieve the Linked To Table Info and add to Auto Join (ONLY FOR SORTING!) | |
| * | |
| * @author Varun Shoor | |
| * @param string $_tableName | |
| * @param string $_columnName | |
| * @param string $_aliasName | |
| * @param string $_tablePrefix | |
| * @return bool "true" on Success, "false" otherwise | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| public function GetSortLinkedToTableAndColumnInfo($_tableName, $_columnName, $_aliasName, $_tablePrefix) | |
| { | |
| if (!$this->GetIsClassLoaded()) { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } | |
| $_tableName = $this->GetTableNameOnLabel($_tableName); | |
| if (!isset($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_LINKEDTO])) { | |
| return false; | |
| } | |
| // We have a linked field! | |
| $_joinField = $this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_LINKEDTO][0]; | |
| $_replacementField = $this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_LINKEDTO][1]; | |
| $_tableAndFieldContainer = $this->GetTableAndFieldNameOnText($_joinField); | |
| if (!isset($_tableAndFieldContainer[0]) || empty($_tableAndFieldContainer[0])) { | |
| throw new SWIFT_Exception('Invalid Join Table Name'); | |
| } | |
| $_joinTableName = $_tableAndFieldContainer[0]; | |
| $_joinFieldName = $_tableAndFieldContainer[1]; | |
| if (!in_array($_joinTableName, $this->_sqlTableList) && !in_array($_joinTableName, $this->_autoJoinTableList)) { | |
| $this->_autoJoinExpressionList[] = TABLE_PREFIX . $_joinTableName . ' AS ' . $_joinTableName . ' ON (' . $_tableName . '.' . $_columnName . ' = ' . $_joinField . ')'; | |
| $this->_sqlTableList[] = $_joinTableName; | |
| } | |
| return $_tablePrefix . $_replacementField . IIF(!empty($_aliasName), ' AS ' . $_aliasName); | |
| } | |
| /** | |
| * Retrieve the Linked To Table Info and add to Auto Join | |
| * | |
| * @author Varun Shoor | |
| * @param string $_tableName | |
| * @param string $_columnName | |
| * @param string $_aliasName | |
| * @param string $_tablePrefix | |
| * @return bool "true" on Success, "false" otherwise | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| protected function GetWhereLinkedToTableAndColumnInfo($_tableName, $_columnName, $_aliasName, $_tablePrefix, $_extendedFunction) | |
| { | |
| if (!$this->GetIsClassLoaded()) { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } | |
| $_tableName = $this->GetTableNameOnLabel($_tableName); | |
| if (!isset($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_LINKEDTO])) { | |
| return false; | |
| } | |
| // We have a linked field! | |
| $_joinField = $this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_LINKEDTO][0]; | |
| $_replacementField = $this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_LINKEDTO][1]; | |
| $_tableAndFieldContainer = $this->GetTableAndFieldNameOnText($_joinField); | |
| if (!isset($_tableAndFieldContainer[0]) || empty($_tableAndFieldContainer[0])) { | |
| return false; | |
| } | |
| $_joinTableName = $_tableAndFieldContainer[0]; | |
| $_joinFieldName = $_tableAndFieldContainer[1]; | |
| $_replacementTableAndFieldContainer = $this->GetTableAndFieldNameOnText($_replacementField); | |
| if (!isset($_replacementTableAndFieldContainer[0]) || empty($_replacementTableAndFieldContainer[0])) { | |
| return false; | |
| } | |
| $_replacementTableName = $_replacementTableAndFieldContainer[0]; | |
| $_replacementFieldName = $_replacementTableAndFieldContainer[1]; | |
| if (!in_array($_joinTableName, $this->_sqlTableList) && !in_array($_joinTableName, $this->_autoJoinTableList)) { | |
| $this->_autoJoinExpressionList[] = TABLE_PREFIX . $_joinTableName . ' AS ' . $_joinTableName . ' ON (' . $_tableName . '.' . $_columnName . ' = ' . $_joinField . ')'; | |
| $this->_sqlTableList[] = $_joinTableName; | |
| } | |
| return array($_tablePrefix . $_replacementField . IIF(!empty($_aliasName), ' AS ' . $_aliasName), $_replacementTableName, $_replacementFieldName); | |
| } | |
| /** | |
| * Retrieve the SQL WHERE Expression from Table Name and Column | |
| * | |
| * @author Varun Shoor | |
| * @param string $_tableName | |
| * @param string $_column | |
| * @param array $_tableList | |
| * @param bool $_prefixTable (OPTIONAL) | |
| * @return bool "true" on Success, "false" otherwise | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| protected function GetWhereExpressionFromTableNameAndColumn($_tableName, $_column, $_tableList, $_prefixTable = true, $_hasTableByDefault = true) | |
| { | |
| $_originalColumn = $_column; | |
| $_tableName = $this->Database->Escape(Clean($this->GetTableNameOnLabel(mb_strtolower($_tableName)))); | |
| $_column = $this->Database->Escape(mb_strtolower($_column)); | |
| if ($_originalColumn == '*') { | |
| $_column = '*'; | |
| } | |
| if (!$this->GetIsClassLoaded()) { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } else if (empty($_tableName)) { | |
| throw new SWIFT_Exception('No Table Name Specified for SQL Expression'); | |
| } else if (!empty($_tableName) && empty($_column)) { | |
| throw new SWIFT_Exception('No Column specified for table: ' . $_tableName); | |
| } else if (!isset($this->_schemaContainer[$_tableName])) { | |
| throw new SWIFT_Exception('Table is not available in Schema: ' . $_tableName); | |
| } else if (!in_array($_tableName, $_tableList) && !in_array($_tableName, $this->_autoJoinTableList)) { | |
| throw new SWIFT_Exception('Table is not part of available table list: ' . $_tableName); | |
| } | |
| $_tablePrefix = ''; | |
| if ($_prefixTable) { | |
| $_tablePrefix = TABLE_PREFIX; | |
| } | |
| // Check to see that the field exists under table | |
| if (isset($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_column])) { | |
| return $_tablePrefix . $_tableName . '.' . $_column; | |
| } | |
| // Now if the column doesnt exist, it either is being used as a label or its an invalid column, attempt to look it up using label | |
| foreach ($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS] as $_columnName => $_columnContainer) { | |
| $_columnLabel = mb_strtolower(SWIFT_KQLSchema::GetLabel($_tableName . '_' . $_columnName)); | |
| // Attempt on just column name | |
| if (empty($_columnLabel)) { | |
| $_columnLabel = mb_strtolower(SWIFT_KQLSchema::GetLabel($_columnName)); | |
| } | |
| if (!empty($_columnLabel) && $_columnLabel == mb_strtolower($_column)) { | |
| return $_tablePrefix . $_tableName . '.' . $_columnName; | |
| } | |
| } | |
| // No column found? Did we have a provided table? if not, return value as is | |
| if (!$_hasTableByDefault) { | |
| return "'" . $_originalColumn . "'"; | |
| } | |
| // No go? | |
| throw new SWIFT_Exception('Invalid Column, column not found: ' . $_column); | |
| return true; | |
| } | |
| /** | |
| * Process the Sort Order | |
| * | |
| * @author Varun Shoor | |
| * @param string $_sqlPrimaryTable | |
| * @param array $_tableList | |
| * @param string $_tableCol | |
| * @param string $_order | |
| * @param bool $_prefixTable (OPTIONAL) | |
| * @return bool "true" on Success, "false" otherwise | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| public function ProcessSortOrder($_sqlPrimaryTable, $_tableList, $_tableCol, $_order, $_prefixTable = false) | |
| { | |
| if (!$this->GetIsClassLoaded()) | |
| { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } | |
| $_order = $this->Database->Escape(Clean(mb_strtoupper($_order))); | |
| if ($_order != 'ASC' && $_order != 'DESC') { | |
| throw new SWIFT_Exception('Invalid Order Specified: ' . $_order); | |
| } | |
| $_tableName = $_sqlPrimaryTable; | |
| $_column = ''; | |
| if (!strpos($_tableCol, '.')) { | |
| $_column = $_tableCol; | |
| } else { | |
| $_tableName = substr($_tableCol, 0, strpos($_tableCol, '.')); | |
| $_column = substr($_tableCol, strpos($_tableCol, '.')+1); | |
| } | |
| $_tableName = $this->Database->Escape(Clean($this->GetTableNameOnLabel(mb_strtolower($_tableName)))); | |
| $_column = $this->Database->Escape(mb_strtolower($_column)); | |
| if (empty($_tableName)) { | |
| throw new SWIFT_Exception('No Table Name Specified for SQL Expression'); | |
| } else if (!empty($_tableName) && empty($_column)) { | |
| throw new SWIFT_Exception('No Column specified for table: ' . $_tableName); | |
| } else if (!isset($this->_schemaContainer[$_tableName])) { | |
| throw new SWIFT_Exception('Table is not available in Schema: ' . $_tableName); | |
| } else if (!in_array($_tableName, $_tableList) && !in_array($_tableName, $this->_autoJoinTableList)) { | |
| throw new SWIFT_Exception('Table is not part of available table list: ' . $_tableName); | |
| } | |
| $_tablePrefix = ''; | |
| if ($_prefixTable) { | |
| $_tablePrefix = TABLE_PREFIX; | |
| } | |
| // Check to see that the field exists under table | |
| if (isset($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_column])) { | |
| $_linkedResult = $this->GetSortLinkedToTableAndColumnInfo($_tableName, $_column, '', $_tablePrefix); | |
| if (!empty($_linkedResult)) { | |
| return $_linkedResult . ' ' . $_order; | |
| } | |
| return $_tablePrefix . $_tableName . '.' . $_column . ' ' . $_order; | |
| } | |
| // Now if the column doesnt exist, it either is being used as a label or its an invalid column, attempt to look it up using label | |
| foreach ($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS] as $_columnName => $_columnContainer) { | |
| $_columnLabel = mb_strtolower(SWIFT_KQLSchema::GetLabel($_tableName . '_' . $_columnName)); | |
| // Attempt on just column name | |
| if (empty($_columnLabel)) { | |
| $_columnLabel = mb_strtolower(SWIFT_KQLSchema::GetLabel($_columnName)); | |
| } | |
| if (!empty($_columnLabel) && $_columnLabel == mb_strtolower($_column)) { | |
| $_linkedResult = $this->GetLinkedToTableAndColumnInfo($_tableName, $_columnName, '', $_tablePrefix); | |
| if (!empty($_linkedResult)) { | |
| return $_linkedResult . ' ' . $_order; | |
| } | |
| return $_tablePrefix . $_tableName . '.' . $_columnName . ' ' . $_order; | |
| } | |
| } | |
| // No go? | |
| throw new SWIFT_Exception('Invalid Column, column not found: ' . $_column); | |
| return true; | |
| } | |
| /** | |
| * Process the Group By | |
| * | |
| * @author Varun Shoor | |
| * @param string $_sqlPrimaryTable | |
| * @param array $_tableList | |
| * @param string $_tableCol | |
| * @param array $_options | |
| * @param bool $_prefixTable (OPTIONAL) | |
| * @return bool "true" on Success, "false" otherwise | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| public function ProcessGroupBy($_sqlPrimaryTable, $_tableList, $_tableCol, $_options, $_prefixTable = false) | |
| { | |
| if (!$this->GetIsClassLoaded()) | |
| { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } | |
| $_tableName = $_sqlPrimaryTable; | |
| $_column = ''; | |
| if (!strpos($_tableCol, '.')) { | |
| $_column = $_tableCol; | |
| } else { | |
| $_tableName = substr($_tableCol, 0, strpos($_tableCol, '.')); | |
| $_column = substr($_tableCol, strpos($_tableCol, '.')+1); | |
| } | |
| $_tableName = $this->Database->Escape(Clean($this->GetTableNameOnLabel(mb_strtolower($_tableName)))); | |
| $_column = $this->Database->Escape(mb_strtolower($_column)); | |
| if (empty($_tableName)) { | |
| throw new SWIFT_Exception('No Table Name Specified for SQL Expression'); | |
| } else if (!empty($_tableName) && empty($_column)) { | |
| throw new SWIFT_Exception('No Column specified for table: ' . $_tableName); | |
| } else if (!isset($this->_schemaContainer[$_tableName])) { | |
| throw new SWIFT_Exception('Table is not available in Schema: ' . $_tableName); | |
| } else if (!in_array($_tableName, $_tableList) && !in_array($_tableName, $this->_autoJoinTableList)) { | |
| print_r($this->_autoJoinTableList); | |
| throw new SWIFT_Exception('Table is not part of available table list: ' . $_tableName); | |
| } | |
| $_tablePrefix = ''; | |
| if ($_prefixTable) { | |
| $_tablePrefix = TABLE_PREFIX; | |
| } | |
| $_hasExtendedArgument = false; | |
| $_fieldPrefix = $_fieldSuffix = $_extendedFunction = ''; | |
| if (isset($_options['extended'])) { | |
| $_hasExtendedArgument = true; | |
| $_extendedFunction = mb_strtolower($_options['extended']); | |
| } | |
| $_groupFunction = ''; | |
| if (isset($_options['function'])) { | |
| $_groupFunction = mb_strtoupper($_options['function']); | |
| } | |
| // Check to see that the field exists under table | |
| if (isset($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_column])) { | |
| $_linkedResultContainer = $this->GetWhereLinkedToTableAndColumnInfo($_tableName, $_column, '', $_tablePrefix, $_extendedFunction); | |
| if (_is_array($_linkedResultContainer)) { | |
| $_baseFieldName = $_linkedResultContainer[1] . '_' . $_linkedResultContainer[2]; | |
| $_fetchFieldName = $_fieldPrefix . $_linkedResultContainer[0] . $_fieldSuffix; | |
| if ($_hasExtendedArgument) { | |
| extract($this->GetFieldPrefixSuffix($_extendedFunction, $_linkedResultContainer[1], $_linkedResultContainer[2])); | |
| $_baseFieldName = $_extendedFunction . '_' . $_linkedResultContainer[1] . '_' . $_linkedResultContainer[2]; | |
| $_fetchFieldName = $_fieldPrefix . $_linkedResultContainer[0] . $_fieldSuffix; | |
| // Skip the grouping for X axis | |
| if ($_groupFunction == 'X') { | |
| $this->_autoDistinctSQLExpressionList[$_baseFieldName] = $_fetchFieldName . ' AS ' . $_baseFieldName; | |
| $this->_returnGroupByXFields[] = array($_linkedResultContainer[0], $_baseFieldName, $_fetchFieldName); | |
| return ''; | |
| } | |
| $this->_autoSQLExpressionList[] = $_fetchFieldName . ' AS ' . $_baseFieldName; | |
| // Add to Group By array | |
| $this->_returnGroupByFields[] = array($_linkedResultContainer[0], $_baseFieldName); | |
| return $_baseFieldName; | |
| } | |
| // Skip the grouping for X axis | |
| if ($_groupFunction == 'X') { | |
| $this->_autoDistinctSQLExpressionList[$_baseFieldName] = $_fetchFieldName . ' AS ' . $_baseFieldName; | |
| $this->_returnGroupByXFields[] = array($_linkedResultContainer[0], $_baseFieldName, $_linkedResultContainer[0]); | |
| return ''; | |
| } | |
| $this->_autoSQLExpressionList[] = $_linkedResultContainer[0] . ' AS ' . $_baseFieldName; | |
| // Add to Group By array | |
| $this->_returnGroupByFields[] = array($_linkedResultContainer[0], $_baseFieldName); | |
| return $_fieldPrefix . $_linkedResultContainer[0] . $_fieldSuffix; | |
| } | |
| extract($this->GetFieldPrefixSuffix($_extendedFunction, $_tableName, $_column)); | |
| $_returnField = $_fieldPrefix . $_tablePrefix . $_tableName . '.' . $_column . $_fieldSuffix; | |
| $_baseFieldName = $_tableName . '_' . $_column; | |
| if ($_hasExtendedArgument) { | |
| $_baseFieldName = $_extendedFunction . '_' . $_tableName . '_' . $_column; | |
| // Skip the grouping for X axis | |
| if ($_groupFunction == 'X') { | |
| $this->_autoDistinctSQLExpressionList[$_baseFieldName] = $_fieldPrefix . $_tablePrefix . $_tableName . '.' . $_column . $_fieldSuffix . ' AS ' . $_baseFieldName; | |
| $this->_returnGroupByXFields[] = array($_tableName . '.' . $_column, $_baseFieldName, $_returnField); | |
| return ''; | |
| } | |
| $this->_autoSQLExpressionList[] = $_returnField . ' AS ' . $_baseFieldName; | |
| $_returnField = $_baseFieldName; | |
| // Dont add to auto load if its supposed to be part of the matrix report | |
| } else if ($_groupFunction != 'X') { | |
| $this->_autoSQLExpressionList[] = $_tableName . '.' . $_column . ' AS ' . $_baseFieldName; | |
| } | |
| // Skip the grouping for X axis | |
| if ($_groupFunction == 'X') { | |
| $this->_autoDistinctSQLExpressionList[$_baseFieldName] = $_fieldPrefix . $_tablePrefix . $_tableName . '.' . $_column . $_fieldSuffix . ' AS ' . $_baseFieldName; | |
| $this->_returnGroupByXFields[] = array($_tableName . '.' . $_column, $_baseFieldName, $_tableName . '.' . $_column); | |
| return ''; | |
| } | |
| // Add to Group By array | |
| $this->_returnGroupByFields[] = array($_tableName . '.' . $_column, $_baseFieldName); | |
| return $_returnField; | |
| } | |
| // Now if the column doesnt exist, it either is being used as a label or its an invalid column, attempt to look it up using label | |
| foreach ($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS] as $_columnName => $_columnContainer) { | |
| $_columnLabel = mb_strtolower(SWIFT_KQLSchema::GetLabel($_tableName . '_' . $_columnName)); | |
| // Attempt on just column name | |
| if (empty($_columnLabel)) { | |
| $_columnLabel = mb_strtolower(SWIFT_KQLSchema::GetLabel($_columnName)); | |
| } | |
| if (!empty($_columnLabel) && $_columnLabel == mb_strtolower($_column)) { | |
| $_linkedResultContainer = $this->GetWhereLinkedToTableAndColumnInfo($_tableName, $_columnName, '', $_tablePrefix, $_extendedFunction); | |
| if (_is_array($_linkedResultContainer)) { | |
| $_fetchFieldName = $_fieldPrefix . $_linkedResultContainer[0] . $_fieldSuffix; | |
| $_baseFieldName = $_linkedResultContainer[1] . '_' . $_linkedResultContainer[2]; | |
| if ($_hasExtendedArgument) { | |
| extract($this->GetFieldPrefixSuffix($_extendedFunction, $_linkedResultContainer[1], $_linkedResultContainer[2])); | |
| $_fetchFieldName = $_fieldPrefix . $_linkedResultContainer[0] . $_fieldSuffix; | |
| $_baseFieldName = $_extendedFunction . '_' . $_linkedResultContainer[1] . '_' . $_linkedResultContainer[2]; | |
| // Skip the grouping for X axis | |
| if ($_groupFunction == 'X') { | |
| $this->_autoDistinctSQLExpressionList[$_baseFieldName] = $_fieldPrefix . $_fetchFieldName . $_fieldSuffix . ' AS ' . $_baseFieldName; | |
| $this->_returnGroupByXFields[] = array($_linkedResultContainer[0], $_baseFieldName, $_fetchFieldName); | |
| return ''; | |
| } | |
| $this->_autoSQLExpressionList[] = $_fetchFieldName . ' AS ' . $_baseFieldName; | |
| // Add to Group By array | |
| $this->_returnGroupByFields[] = array($_linkedResultContainer[0], $_baseFieldName); | |
| return $_baseFieldName; | |
| } | |
| // Skip the grouping for X axis | |
| if ($_groupFunction == 'X') { | |
| $this->_autoDistinctSQLExpressionList[$_baseFieldName] = $_fieldPrefix . $_fetchFieldName . $_fieldSuffix . ' AS ' . $_baseFieldName; | |
| $this->_returnGroupByXFields[] = array($_linkedResultContainer[0], $_baseFieldName, $_linkedResultContainer[0]); | |
| return ''; | |
| } | |
| $this->_autoSQLExpressionList[] = $_linkedResultContainer[0] . ' AS ' . $_baseFieldName; | |
| // Add to Group By array | |
| $this->_returnGroupByFields[] = array($_linkedResultContainer[0], $_baseFieldName); | |
| return $_fieldPrefix . $_linkedResultContainer[0] . $_fieldSuffix; | |
| } | |
| extract($this->GetFieldPrefixSuffix($_extendedFunction, $_tableName, $_columnName)); | |
| $_returnField = $_fieldPrefix . $_tablePrefix . $_tableName . '.' . $_columnName . $_fieldSuffix; | |
| $_baseFieldName = $_tableName . '_' . $_columnName; | |
| if ($_hasExtendedArgument) { | |
| $_baseFieldName = $_extendedFunction . '_' . $_tableName . '_' . $_columnName; | |
| // Skip the grouping for X axis | |
| if ($_groupFunction == 'X') { | |
| $this->_autoDistinctSQLExpressionList[$_baseFieldName] = $_fieldPrefix . $_tablePrefix . $_tableName . '.' . $_columnName . $_fieldSuffix . ' AS ' . $_baseFieldName; | |
| $this->_returnGroupByXFields[] = array($_tableName . '.' . $_columnName, $_baseFieldName, $_returnField); | |
| return ''; | |
| } | |
| $this->_autoSQLExpressionList[] = $_returnField . ' AS ' . $_baseFieldName; | |
| $_returnField = $_baseFieldName; | |
| // Dont add to auto load if its supposed to be part of the matrix report | |
| } else if ($_groupFunction != 'X') { | |
| $this->_autoSQLExpressionList[] = $_tableName . '.' . $_columnName . ' AS ' . $_baseFieldName; | |
| } | |
| if ($_groupFunction == 'X') { | |
| $this->_autoDistinctSQLExpressionList[$_baseFieldName] = $_fieldPrefix . $_tablePrefix . $_tableName . '.' . $_columnName . $_fieldSuffix . ' AS ' . $_baseFieldName; | |
| $this->_returnGroupByXFields[] = array($_tableName . '.' . $_columnName, $_baseFieldName, $_tableName . '.' . $_columnName); | |
| return ''; | |
| } | |
| // Add to Group By array | |
| $this->_returnGroupByFields[] = array($_tableName . '.' . $_columnName, $_baseFieldName); | |
| return $_returnField; | |
| } | |
| } | |
| // No go? | |
| throw new SWIFT_Exception('Invalid Column, column not found: ' . $_column); | |
| return true; | |
| } | |
| /** | |
| * Process the MultiGroup By Clauses | |
| * | |
| * @author Varun Shoor | |
| * @param string $_sqlPrimaryTable | |
| * @param array $_tableList | |
| * @param string $_tableCol | |
| * @param array $_options | |
| * @param bool $_prefixTable (OPTIONAL) | |
| * @return bool "true" on Success, "false" otherwise | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| public function ProcessMultiGroupBy($_sqlPrimaryTable, $_tableList, $_tableCol, $_options, $_prefixTable = false) | |
| { | |
| if (!$this->GetIsClassLoaded()) | |
| { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } | |
| $_tableName = $_sqlPrimaryTable; | |
| $_column = ''; | |
| if (!strpos($_tableCol, '.')) { | |
| $_column = $_tableCol; | |
| } else { | |
| $_tableName = substr($_tableCol, 0, strpos($_tableCol, '.')); | |
| $_column = substr($_tableCol, strpos($_tableCol, '.')+1); | |
| } | |
| $_tableName = $this->Database->Escape(Clean($this->GetTableNameOnLabel(mb_strtolower($_tableName)))); | |
| $_column = $this->Database->Escape(mb_strtolower($_column)); | |
| if (empty($_tableName)) { | |
| throw new SWIFT_Exception('No Table Name Specified for SQL Expression'); | |
| } else if (!empty($_tableName) && empty($_column)) { | |
| throw new SWIFT_Exception('No Column specified for table: ' . $_tableName); | |
| } else if (!isset($this->_schemaContainer[$_tableName])) { | |
| throw new SWIFT_Exception('Table is not available in Schema: ' . $_tableName); | |
| } else if (!in_array($_tableName, $_tableList) && !in_array($_tableName, $this->_autoJoinTableList)) { | |
| print_r($this->_autoJoinTableList); | |
| throw new SWIFT_Exception('Table is not part of available table list: ' . $_tableName); | |
| } | |
| $_tablePrefix = ''; | |
| if ($_prefixTable) { | |
| $_tablePrefix = TABLE_PREFIX; | |
| } | |
| $_hasExtendedArgument = false; | |
| $_fieldPrefix = $_fieldSuffix = $_extendedFunction = ''; | |
| if (isset($_options['extended'])) { | |
| $_hasExtendedArgument = true; | |
| $_extendedFunction = mb_strtolower($_options['extended']); | |
| } | |
| $_groupFunction = ''; | |
| if (isset($_options['function'])) { | |
| $_groupFunction = mb_strtoupper($_options['function']); | |
| } | |
| // Check to see that the field exists under table | |
| if (isset($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_column])) { | |
| $_linkedResultContainer = $this->GetWhereLinkedToTableAndColumnInfo($_tableName, $_column, '', $_tablePrefix, $_extendedFunction); | |
| if (_is_array($_linkedResultContainer)) { | |
| $_baseFieldName = $_linkedResultContainer[1] . '_' . $_linkedResultContainer[2]; | |
| $_fetchFieldName = $_fieldPrefix . $_linkedResultContainer[0] . $_fieldSuffix; | |
| if ($_hasExtendedArgument) { | |
| extract($this->GetFieldPrefixSuffix($_extendedFunction, $_linkedResultContainer[1], $_linkedResultContainer[2])); | |
| $_baseFieldName = $_extendedFunction . '_' . $_linkedResultContainer[1] . '_' . $_linkedResultContainer[2]; | |
| $_fetchFieldName = $_fieldPrefix . $_linkedResultContainer[0] . $_fieldSuffix; | |
| $this->_autoDistinctSQLExpressionList[$_baseFieldName] = $_fetchFieldName . ' AS ' . $_baseFieldName; | |
| // Add to Multi Group By array | |
| $this->_returnMultiGroupByFields[] = array($_linkedResultContainer[0], $_baseFieldName, $_fetchFieldName); | |
| return false; | |
| } | |
| $this->_autoDistinctSQLExpressionList[$_baseFieldName] = $_linkedResultContainer[0] . ' AS ' . $_baseFieldName; | |
| // Add to Multi Group By array | |
| $this->_returnMultiGroupByFields[] = array($_linkedResultContainer[0], $_baseFieldName, $_linkedResultContainer[0]); | |
| return false; | |
| } | |
| extract($this->GetFieldPrefixSuffix($_extendedFunction, $_tableName, $_column)); | |
| $_returnField = $_fieldPrefix . $_tablePrefix . $_tableName . '.' . $_column . $_fieldSuffix; | |
| $_baseFieldName = $_tableName . '_' . $_column; | |
| if ($_hasExtendedArgument) { | |
| $_baseFieldName = $_extendedFunction . '_' . $_tableName . '_' . $_column; | |
| $this->_autoDistinctSQLExpressionList[$_baseFieldName] = $_returnField . ' AS ' . $_baseFieldName; | |
| // Add to Multi Group By array | |
| $this->_returnMultiGroupByFields[] = array($_tableName . '.' . $_column, $_baseFieldName, $_returnField); | |
| } else { | |
| $this->_autoDistinctSQLExpressionList[$_baseFieldName] = $_tableName . '.' . $_column . ' AS ' . $_baseFieldName; | |
| // Add to Multi Group By array | |
| $this->_returnMultiGroupByFields[] = array($_tableName . '.' . $_column, $_baseFieldName, $_tableName . '.' . $_column); | |
| } | |
| return false; | |
| } | |
| // Now if the column doesnt exist, it either is being used as a label or its an invalid column, attempt to look it up using label | |
| foreach ($this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS] as $_columnName => $_columnContainer) { | |
| $_columnLabel = mb_strtolower(SWIFT_KQLSchema::GetLabel($_tableName . '_' . $_columnName)); | |
| // Attempt on just column name | |
| if (empty($_columnLabel)) { | |
| $_columnLabel = mb_strtolower(SWIFT_KQLSchema::GetLabel($_columnName)); | |
| } | |
| if (!empty($_columnLabel) && $_columnLabel == mb_strtolower($_column)) { | |
| $_linkedResultContainer = $this->GetWhereLinkedToTableAndColumnInfo($_tableName, $_columnName, '', $_tablePrefix, $_extendedFunction); | |
| if (_is_array($_linkedResultContainer)) { | |
| $_fetchFieldName = $_fieldPrefix . $_linkedResultContainer[0] . $_fieldSuffix; | |
| $_baseFieldName = $_linkedResultContainer[1] . '_' . $_linkedResultContainer[2]; | |
| if ($_hasExtendedArgument) { | |
| extract($this->GetFieldPrefixSuffix($_extendedFunction, $_linkedResultContainer[1], $_linkedResultContainer[2])); | |
| $_fetchFieldName = $_fieldPrefix . $_linkedResultContainer[0] . $_fieldSuffix; | |
| $_baseFieldName = $_extendedFunction . '_' . $_linkedResultContainer[1] . '_' . $_linkedResultContainer[2]; | |
| $this->_autoDistinctSQLExpressionList[$_baseFieldName] = $_fetchFieldName . ' AS ' . $_baseFieldName; | |
| // Add to Multi Group By array | |
| $this->_returnMultiGroupByFields[] = array($_linkedResultContainer[0], $_baseFieldName, $_fetchFieldName); | |
| return false; | |
| } | |
| $this->_autoDistinctSQLExpressionList[$_baseFieldName] = $_linkedResultContainer[0] . ' AS ' . $_baseFieldName; | |
| // Add to Multi Group By array | |
| $this->_returnMultiGroupByFields[] = array($_linkedResultContainer[0], $_baseFieldName, $_linkedResultContainer[0]); | |
| return false; | |
| } | |
| extract($this->GetFieldPrefixSuffix($_extendedFunction, $_tableName, $_columnName)); | |
| $_returnField = $_fieldPrefix . $_tablePrefix . $_tableName . '.' . $_columnName . $_fieldSuffix; | |
| $_baseFieldName = $_tableName . '_' . $_columnName; | |
| if ($_hasExtendedArgument) { | |
| $_baseFieldName = $_extendedFunction . '_' . $_tableName . '_' . $_columnName; | |
| $this->_autoDistinctSQLExpressionList[$_baseFieldName] = $_returnField . ' AS ' . $_baseFieldName; | |
| // Add to Multi Group By array | |
| $this->_returnMultiGroupByFields[] = array($_tableName . '.' . $_columnName, $_baseFieldName, $_returnField); | |
| } else { | |
| $this->_autoDistinctSQLExpressionList[$_baseFieldName] = $_tableName . '.' . $_columnName . ' AS ' . $_baseFieldName; | |
| // Add to Multi Group By array | |
| $this->_returnMultiGroupByFields[] = array($_tableName . '.' . $_columnName, $_baseFieldName, $_tableName . '.' . $_columnName); | |
| } | |
| return false; | |
| } | |
| } | |
| // No go? | |
| throw new SWIFT_Exception('Invalid Column, column not found: ' . $_column); | |
| return true; | |
| } | |
| /** | |
| * Retrieve the field prefix and suffix | |
| * | |
| * @author Varun Shoor | |
| * @param string $_extendedFunction | |
| * @param string $_tableName | |
| * @param string $_columnName | |
| * @return bool "true" on Success, "false" otherwise | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| protected function GetFieldPrefixSuffix($_extendedFunction, $_tableName, $_columnName) | |
| { | |
| if (!$this->GetIsClassLoaded()) { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } | |
| $_tableName = $this->GetTableNameOnLabel($_tableName); | |
| if (empty($_extendedFunction) || !isset(self::$_extendedClauses[$this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_TYPE]])) { | |
| return array(); | |
| } | |
| // Check for validity of function | |
| $_fieldType = $this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_TYPE]; | |
| $_functionList = self::$_extendedClauses[$this->_schemaContainer[$_tableName][SWIFT_KQLSchema::SCHEMA_FIELDS][$_columnName][SWIFT_KQLSchema::FIELD_TYPE]]; | |
| $_fieldPrefix = $_fieldSuffix = ''; | |
| $_foundMatch = false; | |
| foreach ($_functionList as $_function) { | |
| if ($_extendedFunction == mb_strtolower($_function)) { | |
| // Now that we have the match, we decide on the prefix/suffix | |
| if ($_fieldType == SWIFT_KQLSchema::FIELDTYPE_SECONDS) { | |
| $_foundMatch = true; | |
| if ($_extendedFunction == 'minute') { | |
| $_fieldPrefix = '('; | |
| $_fieldSuffix = '/60)'; | |
| } else if ($_extendedFunction == 'hour') { | |
| $_fieldPrefix = '('; | |
| $_fieldSuffix = '/3600)'; | |
| } else if ($_extendedFunction == 'day') { | |
| $_fieldPrefix = '('; | |
| $_fieldSuffix = '/86400)'; | |
| } | |
| } else if ($_fieldType == SWIFT_KQLSchema::FIELDTYPE_UNIXTIME) { | |
| $_foundMatch = true; | |
| $_fieldPrefix = strtoupper($_extendedFunction) . '(FROM_UNIXTIME('; | |
| $_fieldSuffix = '))'; | |
| } | |
| } | |
| } | |
| if (!$_foundMatch) { | |
| return array(); | |
| } | |
| return array('_fieldPrefix' => $_fieldPrefix, '_fieldSuffix' => $_fieldSuffix); | |
| } | |
| /** | |
| * Return the Primary Table Name | |
| * | |
| * @author Varun Shoor | |
| * @return string The Primary Table Name | |
| * @throws SWIFT_Exception If the Class is not Loaded | |
| */ | |
| protected function GetPrimaryTableName() | |
| { | |
| if (!$this->GetIsClassLoaded()) { | |
| throw new SWIFT_Exception(SWIFT_CLASSNOTLOADED); | |
| return false; | |
| } | |
| return $this->_primaryTableName; | |
| } | |
| /** | |
| * Parse the argument into an array | |
| * | |
| * @author Varun Shoor | |
| * @param string $_argumentText | |
| * @return array The Result Array | |
| * @throws SWIFT_Exception If Invalid Data is Provided | |
| */ | |
| static public function ParseArgumentIntoArray($_argumentText) | |
| { | |
| $_SWIFT = SWIFT::GetInstance(); | |
| $_argumentText = trim($_argumentText); | |
| $_resultArray = array(); | |
| if (empty($_argumentText)) { | |
| return $_resultArray; | |
| } | |
| $_chunks = explode(',', $_argumentText); | |
| if (!_is_array($_chunks)) { | |
| $_chunks = array($_argumentText); | |
| } | |
| foreach ($_chunks as $_chunkValue) { | |
| $_resultArray[] = preg_replace(array('/^(\'|")/i', '/(\'|")$/i'), array('', ''), trim($_chunkValue)); | |
| } | |
| return $_resultArray; | |
| } | |
| } | |
| ?> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment