se/SE/se-lib/SqlQueryWhereBuilder.php

<?php

class SqlQueryWhereBuilder {

	private $_log = array();
	private $_usedFields = array();
	private $_raw = "";

	public function __construct() {
	}

	public function openBlock($blockType) {
		if (!$this->isAllowedBlockType($blockType)) throw new Exception("unsupported block type");
		$this->_log[] = "open_block_{$blockType}";
	}

	public function closeBlock($blockType) {
		if (!$this->isAllowedBlockType($blockType)) throw new Exception("unsupported block type");
		$this->_log[] = "close_block_{$blockType}";
	}

	public function addComparisonFieldToValue($fieldName, $comparisonSign, $value) {
		$sqlCompSign = "";
		switch ($comparisonSign) {
			case '=': $sqlCompSign = '='; break;
			case 'like': $sqlCompSign = 'like'; break;
			case 'GreaterThen': $sqlCompSign = '>'; break;
			case 'LessThen': $sqlCompSign = '<'; break;
			case 'GreaterThenOrEqualTo': $sqlCompSign = '>='; break;
			case 'LessThenOrEqualTo': $sqlCompSign = '<='; break;
			default: throw new Exception("Unsupported comparison sign");
		}
		$this->_log[] = array('comparisonFieldToValue', $fieldName, $sqlCompSign, $value);
	}

	public function addComparisonFieldFunToValue($fieldFun, $fieldName, $comparisonSign, $value) {
		$sqlCompSign = "";
		switch ($comparisonSign) {
			case '=': $sqlCompSign = '='; break;
			case 'like': $sqlCompSign = 'like'; break;
			case 'GreaterThen': $sqlCompSign = '>'; break;
			case 'LessThen': $sqlCompSign = '<'; break;
			case 'GreaterThenOrEqualTo': $sqlCompSign = '>='; break;
			case 'LessThenOrEqualTo': $sqlCompSign = '<='; break;
			default: throw new Exception("Unsupported comparison sign");
		}
		$this->_log[] = array('comparisonFieldFunToValue', $fieldFun, $fieldName, $sqlCompSign, $value);
	}

	public function sql_filter_comparisonFieldIsNull($fieldName) {
		$this->_log[] = array('comparisonFieldIsNull', $fieldName);
	}

	public function getQueryWhere($tablePrefix = '') {
		$sqlWhereRaw = $this->parseQueryWhere();
		$sqlTablePrefix = ($tablePrefix)? "`{$tablePrefix}`." : '';
		return str_replace('{tablePrefix}', $sqlTablePrefix, $sqlWhereRaw);
	}

	public function parseQueryWhere() {
		$this->_raw = "";
		$this->_usedFields = array();

		$sqlBlocksStack = array();
		$sqlValuesStack = array();
		$arr = array(); array_push($sqlValuesStack, $arr);// empty array to start
		foreach ($this->_log as $log) {
			switch ($log) {
				case 'open_block_and':
				case 'open_block_or':
				case 'open_block_not': {
					$blockType = substr($log, 11);
					array_push($sqlBlocksStack, $blockType);
					$arr = array(); array_push($sqlValuesStack, $arr);
//echo "L.".__LINE__.":sqlBlocksStack:" . json_encode($sqlBlocksStack) . "\n";
//echo "L.".__LINE__.":sqlValuesStack:" . json_encode($sqlValuesStack) . "\n\n";
				}
					break;
				case 'close_block_and':
				case 'close_block_or': {
					$blockType = substr($log, 12);
					if (empty($sqlBlocksStack)) throw new Exception("parse sql query failed - blocks stack is empty");
					$stackBlockType = array_pop($sqlBlocksStack);
					if ($blockType != $stackBlockType) throw new Exception("parse sql query failed - expected stop '{$blockType}', given '{$stackBlockType}'");

					// parse to string and add to parent value stack
					$stackValue = array_pop($sqlValuesStack);
					if (!is_array($stackValue)) throw new Exception("parse sql query failed - stack value is not array");
					if (empty($stackValue)) throw new Exception("parse sql query failed - stack value is empty");
					$sqlFromStack = " ( " . implode(" {$blockType} ", $stackValue) . " ) ";
					$parentStackValue = array_pop($sqlValuesStack);
					if (!is_array($parentStackValue)) throw new Exception("parse sql query failed - parent stack value is not array");
					array_push($parentStackValue, $sqlFromStack);
					array_push($sqlValuesStack, $parentStackValue);
				}
					break;
				case 'close_block_not': {
					$blockType = substr($log, 12);
					if (empty($sqlBlocksStack)) throw new Exception("parse sql query failed - blocks stack is empty");
					$stackBlockType = array_pop($sqlBlocksStack);
					if ($blockType != $stackBlockType) throw new Exception("parse sql query failed - expected stop '{$blockType}', given '{$stackBlockType}'");

					// parse to string and add to parent value stack
					$stackValue = array_pop($sqlValuesStack);
					if (!is_array($stackValue)) throw new Exception("parse sql query failed - stack value is not array");
					if (empty($stackValue)) throw new Exception("parse sql query failed - stack value is empty");
					if (1 != count($stackValue)) throw new Exception("parse sql query failed - stack value count is not equal to 1");
					$stackValue = reset($stackValue);
					$sqlFromStack = " ! ({$stackValue}) ";
					$parentStackValue = array_pop($sqlValuesStack);
					if (!is_array($parentStackValue)) throw new Exception("parse sql query failed - parent stack value is not array");
					array_push($parentStackValue, $sqlFromStack);
					array_push($sqlValuesStack, $parentStackValue);
				}
					break;
				default: {
					if (is_array($log) && 4 == count($log) && 'comparisonFieldToValue' == $log[0]) {
						$sqlFieldName = $log[1];
						$this->_usedFields[$sqlFieldName] = true;
						$sqlFromStack = "{tablePrefix}`{$sqlFieldName}` {$log[2]} '{$log[3]}'";
						$stackValue = array_pop($sqlValuesStack);
						if (!is_array($stackValue)) throw new Exception("parse sql query failed - stack value is not array");
						array_push($stackValue, $sqlFromStack);
						array_push($sqlValuesStack, $stackValue);
//echo "L.".__LINE__.":sqlBlocksStack:" . json_encode($sqlBlocksStack) . "\n";
//echo "L.".__LINE__.":sqlValuesStack:" . json_encode($sqlValuesStack) . "\n\n";
					} else if (is_array($log) && 5 == count($log) && 'comparisonFieldFunToValue' == $log[0]) {
						$sqlFieldFunName = $log[1];
						if (strtolower($sqlFieldFunName) != 'geometrytype') throw new Exception("Unsupported db function {$sqlFieldName}");
						$sqlFieldName = $log[2];
						$this->_usedFields[$sqlFieldName] = true;
						$sqlFromStack = "{$sqlFieldFunName}({tablePrefix}`{$sqlFieldName}`) {$log[3]} '{$log[4]}'";
						$stackValue = array_pop($sqlValuesStack);
						if (!is_array($stackValue)) throw new Exception("parse sql query failed - stack value is not array");
						array_push($stackValue, $sqlFromStack);
						array_push($sqlValuesStack, $stackValue);
					} else if (is_array($log) && 2 == count($log) && 'comparisonFieldIsNull' == $log[0]) {
						$sqlFieldName = $log[1];
						$sqlFromStack = "{tablePrefix}`{$sqlFieldName}` is null";
						$stackValue = array_pop($sqlValuesStack);
						if (!is_array($stackValue)) throw new Exception("parse sql query failed - stack value is not array");
						array_push($stackValue, $sqlFromStack);
						array_push($sqlValuesStack, $stackValue);
					} else {
						throw new Exception("parse sql query failed - unknown '" . json_encode($log) . "'");
					}
				}
			}
		}
		// TODO: parse log
//echo "L.".__LINE__.":sqlBlocksStack:" . json_encode($sqlBlocksStack) . "\n";
//echo "L.".__LINE__.":sqlValuesStack:" . json_encode($sqlValuesStack) . "\n--------\n";
		if (!empty($sqlBlocksStack)) throw new Exception("parse sql query failed - blocks stack is not empty");
		if (1 !== count($sqlValuesStack)) throw new Exception("parse sql query failed - values stack is empty");
		$this->_raw = implode("\n", $sqlValuesStack[0]);
		return $this->_raw;
	}

	public function getUsedFields() {
		return array_keys($this->_usedFields);
	}

	public function isAllowedBlockType($blockType) {
		if ('and' == $blockType || 'or' == $blockType) {
			return true;
		} else if ('not' == $blockType) {
			return true;
		}
		return false;
	}

	public function splitQueryToWords($q) {
		$searchWords = explode(' ', $q);
		$sqlSearchWords = array();
		if (!empty($searchWords)) {
			foreach ($searchWords as $word) {
				if (!empty($word)) {
					$sqlSearchWords[] = $word;
				}
			}
		}
		return $sqlSearchWords;
	}

}