Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
__swift/library/KQL/class.SWIFT_KQLParser.php
<?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
You can’t perform that action at this time.