se/SE/se-lib/ParseOgcFilter.php

<?php

Lib::loadClass('SqlQueryWhereBuilder');
Lib::loadClass('Api_WfsException');

/**
 * @see wfs 1.0 wfs:GetFeature schema: http://schemas.opengis.net/wfs/1.0.0/WFS-basic.xsd
 * @see docs wfs 1.1 http://docs.opengeospatial.org/is/04-094r1/04-094r1.html
 *   wfs:GetFeature
 *     wfs:Query [ maxOccurs => "unbounded", typeName [ use => "required" ] ]
 *       ogc:PropertyName [ maxOccurs => "unbounded" ]
 *       ogc:Filter [ maxOccurs => "1" ]
 *       ogc:SortBy [ maxOccurs => "1" ]
 */

/* TODO: fetch with recurse by global wfs:GetFeature attributes
 * @see "Example 11" in http://docs.opengeospatial.org/is/04-094r1/04-094r1.html
		<wfs:GetFeature traverseXlinkDepth="1" traverseXlinkExpiry="1"> ...
 */
/* TODO: fetch with recurse by local wfs:XlinkPropertyName
 * @see "Example 12" in http://docs.opengeospatial.org/is/04-094r1/04-094r1.html
		<wfs:GetFeature>
			<wfs:Query typeName="Town">
				<wfs:PropertyName>gml:name</wfs:PropertyName>
				<wfs:XlinkPropertyName traverseXlinkDepth="2" traverseXlinkExpiry="2">gml:directedNode</wfs:XlinkPropertyName>
 */

/* TODO: parse xlink query:
	<ogc:PropertyIsEqualTo>
		<ogc:PropertyName>File/@xlink:href</ogc:PropertyName>
		<ogc:Literal>https://biuro.biall-net.pl/wfs/objects#File.45</ogc:Literal>
	</ogc:PropertyIsEqualTo>
 * TODO: parse bbox query:
	<ogc:Filter xmlns:app="http://www.deegree.org/app" xmlns:gml="http://www.opengis.net/gml" xmlns:ogc="http://www.opengis.net/ogc">
		<ogc:BBOX>
			<ogc:PropertyName>app:geometry</ogc:PropertyName>
			<gml:Envelope>
				<gml:coord>
					<gml:X>343720</gml:X>
					<gml:Y>4374533</gml:Y>
				</gml:coord>
				<gml:coord>
					<gml:X>444136</gml:X>
					<gml:Y>4433308</gml:Y>
				</gml:coord>
			</gml:Envelope>
		</ogc:BBOX>
	</ogc:Filter>
 * TODO: in SqlQueryWhereBuilder split query for main database and join-ed db (right join in ref's)
 *
 * @usage:
		$parser = new ParseOgcFilter();
		$parser->loadOgcFilter($ogcFilter);
		$queryWhereBuilder = $parser->convertToSqlQueryWhereBuilder();
		$query = $queryWhereBuilder->getQueryWhere('t');
 *
 * TODO: recurse query ProcesTree only to ProcesInit - skip childrens under ProcesInit
 *       test xpath: //*[ not( __backRef/ns = 'PROCES_INIT' ) ]
	       <ogc:Filter>
	         <ogc:And>
	           <ogc:Not>
	             <ogc:PropertyName>//__backRef/namespace</ogc:PropertyName>
	             <ogc:Literal>default_db/CRM_PROCES/ProcesInit</ogc:Literal>
	           </ogc:Not>
	           <ogc:PropertyIsNull>
	             <ogc:PropertyName>PARENT_ID</ogc:PropertyName>
	           </ogc:PropertyIsNull>
	         </ogc:And>
	       </ogc:Filter>
 *
 */
class ParseOgcFilter {

	public $_ogcFilter = '';

	public function __construct() {
	}

	public function loadOgcFilter($ogcFilter) {
		// validate?
		$this->_ogcFilter = $ogcFilter;
	}

	public function convertToSqlQueryWhereBuilder() {
		$ogcFilterXmlString = '<?xml version="1.0" encoding="UTF-8"?>' . "\n";
		$ogcFilterXmlString .= <<<OGC_FILTER_XML_FILE
<filter xmlns="https://biuro.biall-net.pl/SE/version-git/schema/filter.xsd"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:ogc="http://www.opengis.net/ogc">
	{$this->_ogcFilter}
</filter>
OGC_FILTER_XML_FILE;
		$convertedGuiXml = $this->_convertOgcFilterToCmdList($ogcFilterXmlString);

		DBG::_('DBG_DS_OGC', '>1', "convertedGuiXml(".strlen($convertedGuiXml).")", $convertedGuiXml, __CLASS__, __FUNCTION__, __LINE__);

		$tags = array();
		$parserXml = xml_parser_create();
		xml_parser_set_option($parserXml, XML_OPTION_CASE_FOLDING, 0);
		xml_parser_set_option($parserXml, XML_OPTION_SKIP_WHITE, 1);
		if (0 == xml_parse_into_struct($parserXml, $convertedGuiXml, $tags)) {
			throw new Exception("Parse Request xml error #" . __LINE__ . ": parse converted transaction failed");
		}
		xml_parser_free($parserXml);
		if (empty($tags)) {
			throw new Exception("Parse Request xml error #" . __LINE__ . ": parse converted transaction returns empty structure");
		}

		$queryWhereBuilder = new SqlQueryWhereBuilder();
		foreach ($tags as $tag) {
			switch ($tag['tag']) {
				case 'filter': {// root tag
				}
					break;
				case 'sql_filter_comparisonFieldToValue': {
					$fieldName = V::get('fieldName', '', $tag['attributes']);
					$fieldFunction = V::get('fieldFunction', null, $tag['attributes']);
					$comparisonSign = V::get('comparisonSign', '', $tag['attributes']);
					$value = V::get('value', '', $tag['attributes']);
					DBG::_('DBG_DS_OGC', '>3', "sql_filter_comparisonFieldToValue comparisonSign", $comparisonSign, __CLASS__, __FUNCTION__, __LINE__);
					if ('like' == $comparisonSign) {
						$wildCard = V::get('wildCard', '', $tag['attributes']);
						$singleChar = V::get('singleChar', '', $tag['attributes']);
						$escapeChar = V::get('escapeChar', '', $tag['attributes']);
						$value = $this->_convertIsLikeToSql($value, $wildCard, $singleChar, $escapeChar);
					}
					DBG::_('DBG_DS_OGC', '>3', "sql_filter_comparisonFieldToValue value({$value})", null, __CLASS__, __FUNCTION__, __LINE__);
					if ($fieldFunction) {
						$queryWhereBuilder->addComparisonFieldFunToValue($fieldFunction, $fieldName, $comparisonSign, $value);
					} else {
						$queryWhereBuilder->addComparisonFieldToValue($fieldName, $comparisonSign, $value);
					}
				}
					break;
				case 'sql_filter_comparisonFieldIsNull': {
					$fieldName = V::get('fieldName', '', $tag['attributes']);
					$queryWhereBuilder->sql_filter_comparisonFieldIsNull($fieldName);
				}
					break;
				case 'sql_filter_openBlock': {
					$blockType = V::get('type', '', $tag['attributes']);
					DBG::_('DBG_DS_OGC', '>2', "sql_filter_openBlock block Type {$blockType} attrs", json_encode($tag), __CLASS__, __FUNCTION__, __LINE__);
					$queryWhereBuilder->openBlock($blockType);
				}
					break;
				case 'sql_filter_closeBlock': {
					$blockType = V::get('type', '', $tag['attributes']);
					$queryWhereBuilder->closeBlock($blockType);
				}
					break;
				case 'not_implemented_tag': {
					$tagName = V::get('name', '', $tag['attributes']);
					DBG::_('DBG_DS_OGC', '>1', "Not Implemented tag '{$tagName}'", $queryWhereBuilder, __CLASS__, __FUNCTION__, __LINE__);
					throw new Api_WfsException("Not Implemented tag '{$tagName}'", 501, null, 'NotImplementedOgcTag', 'request');
				}
					break;
				default: {
					DBG::_('DBG_DS_OGC', '>1', "TODO: tag {$tag['tag']}", $queryWhereBuilder, __CLASS__, __FUNCTION__, __LINE__);
					throw new HttpException("TODO: tag {$tag['tag']}", 501);
				}
			}
		}

		$queryWhereBuilder->parseQueryWhere();
		DBG::_('DBG_DS_OGC', '>1', "queryWhereBuilder", $queryWhereBuilder, __CLASS__, __FUNCTION__, __LINE__);
		return $queryWhereBuilder;
	}

	public function _convertIsLikeToSql($value, $wildCard, $singleChar, $escapeChar) {
		if (0 === strlen($wildCard)) throw new Exception("Missing wildCard in PropertyIsLike");
		if (0 === strlen($singleChar)) throw new Exception("Missing singleChar in PropertyIsLike");
		if (0 === strlen($escapeChar)) throw new Exception("Missing escapeChar in PropertyIsLike");
		// The <PropertyIsLike> element is intended to encode a character string comparison operator with pattern matching.
		// The pattern is defined by a combination of regular characters, the wildCard character, the singleChar character,
		// and the escapeChar character.
		// - wildCard   (sql: '%') - matches zero or more characters
		// - singleChar (sql: '_') - matches exactly one character
		// - escapeChar (sql: '\') - is used to escape the meaning of the wildCard, singleChar and escapeChar itself

		// wildCard="*" singleChar="#" escapeChar="!" => sql: % _ \
		// '*ORMA!*' => '%ORMA\*'
		// TODO: BUG: singleChar="%23"

		// TODO: first replace every escapeChar
		$valLength = strlen($value);

		$parts = [ $value ];
		DBG::log($parts, 'array', "_convertIsLikeToSql('{$value}', '{$wildCard}', '{$singleChar}', '{$escapeChar}') - init");
		$parts = array_reduce($parts, function ($ret, $cur) use ($escapeChar, $wildCard) {
			if (is_array($cur)) { $ret[] = $cur; return $ret; }// SKIP SQL_...
			foreach (explode("{$escapeChar}{$wildCard}", $cur) as $idx => $p) {
				if ($idx > 0) $ret[] = [ $wildCard ];
				$ret[] = $p;
			}
			return $ret;
		}, []);
		DBG::log($parts, 'array', "_convertIsLikeToSql('{$value}', '{$wildCard}', '{$singleChar}', '{$escapeChar}') - pass split escape \$wildCard");
		$parts = array_reduce($parts, function ($ret, $cur) use ($escapeChar, $singleChar) {
			if (is_array($cur)) { $ret[] = $cur; return $ret; }// SKIP SQL_...
			foreach (explode("{$escapeChar}{$singleChar}", $cur) as $idx => $p) {
				if ($idx > 0) $ret[] = [ $singleChar ];
				$ret[] = $p;
			}
			return $ret;
		}, []);
		DBG::log($parts, 'array', "_convertIsLikeToSql('{$value}', '{$wildCard}', '{$singleChar}', '{$escapeChar}') - pass split escape \$singleChar");
		$parts = array_reduce($parts, function ($ret, $cur) use ($wildCard) {
			if (is_array($cur)) { $ret[] = $cur; return $ret; }// SKIP SQL_...
			foreach (explode("{$wildCard}", $cur) as $idx => $p) {
				if ($idx > 0) $ret[] = [ '%' ];
				$ret[] = $p;
			}
			return $ret;
		}, []);
		DBG::log($parts, 'array', "_convertIsLikeToSql('{$value}', '{$wildCard}', '{$singleChar}', '{$escapeChar}') - pass split escape \$wildCard");
		$parts = array_reduce($parts, function ($ret, $cur) use ($singleChar) {
			if (is_array($cur)) { $ret[] = $cur; return $ret; }// SKIP SQL_...
			foreach (explode("{$singleChar}", $cur) as $idx => $p) {
				if ($idx > 0) $ret[] = [ '_' ];
				$ret[] = $p;
			}
			return $ret;
		}, []);
		DBG::log($parts, 'array', "_convertIsLikeToSql('{$value}', '{$wildCard}', '{$singleChar}', '{$escapeChar}') - pass split escape \$singleChar");
		foreach ($parts as $idx => $cur) {
			if (is_array($cur)) continue;
			$parts[$idx] = str_replace('\\', '\\\\', $cur);
		}
		DBG::log($parts, 'array', "_convertIsLikeToSql('{$value}', '{$wildCard}', '{$singleChar}', '{$escapeChar}') - pass escape sql escape");
		foreach ($parts as $idx => $cur) {
			if (is_array($cur)) continue;
			$parts[$idx] = str_replace('%', '\\%', $cur);
		}
		DBG::log($parts, 'array', "_convertIsLikeToSql('{$value}', '{$wildCard}', '{$singleChar}', '{$escapeChar}') - pass escape sql wildCard");
		foreach ($parts as $idx => $cur) {
			if (is_array($cur)) continue;
			$parts[$idx] = str_replace('_', '\\_', $cur);
		}
		DBG::log($parts, 'array', "_convertIsLikeToSql('{$value}', '{$wildCard}', '{$singleChar}', '{$escapeChar}') - pass escape sql singleChar");
		foreach ($parts as $idx => $cur) {
			if (is_array($cur)) $parts[$idx] = $cur[0];
		}
		DBG::log(implode('', $parts), 'array', "_convertIsLikeToSql('{$value}', '{$wildCard}', '{$singleChar}', '{$escapeChar}') - END");
		return implode('', $parts);

		$valCharsAllowReplace = array(); for ($i = 0; $i < $valLength; $i++) $valCharsAllowReplace[] = true;
		{// escapeChar
			$lastOffset = 0;
			while (false !== ($pos = strpos($value, $escapeChar, $lastOffset))) {
				DBG::_('DBG_DS_OGC', '>3', "sql_filter_comparisonFieldToValue like value({$value}) escapeChar({$escapeChar}) pos({$pos}) lastOffset({$lastOffset})", null, __CLASS__, __FUNCTION__, __LINE__);
				$value[$pos] = '\\';
				$valCharsAllowReplace[$pos] = false;
				if ($pos + 1 < $valLength) $valCharsAllowReplace[$pos + 1] = false;
				$lastOffset = $pos + 1;
			}
		}
		{// singleChar
			$lastOffset = 0;
			while (false !== ($pos = strpos($value, $singleChar, $lastOffset))) {
				DBG::_('DBG_DS_OGC', '>3', "sql_filter_comparisonFieldToValue like value({$value}) singleChar({$singleChar}) pos({$pos}) lastOffset({$lastOffset})", null, __CLASS__, __FUNCTION__, __LINE__);
				if ($valCharsAllowReplace[$pos]) {
					$value[$pos] = '_';
					$valCharsAllowReplace[$pos] = false;
				}
				$lastOffset = $pos + 1;
			}
		}
		{// wildCard
			$lastOffset = 0;
			while (false !== ($pos = strpos($value, $wildCard, $lastOffset))) {
				DBG::_('DBG_DS_OGC', '>3', "sql_filter_comparisonFieldToValue like value({$value}) wildCard({$wildCard}) pos({$pos}) lastOffset({$lastOffset})", null, __CLASS__, __FUNCTION__, __LINE__);
				if ($valCharsAllowReplace[$pos]) {
					$value[$pos] = '%';
					$valCharsAllowReplace[$pos] = false;
				}
				$lastOffset = $pos + 1;
			}
		}
		return $value;
	}

	public function _convertOgcFilterToCmdList($ogcFilterXmlString) {
		$convertOgcFilterXslString = @file_get_contents(APP_PATH_SCHEMA . DS . 'wfs' . DS . 'convertOgcFilterToXmlTaskList.xsl');
		if (false === $convertOgcFilterXslString) throw new HttpException("Cannot find file 'convertOgcFilter...'", 404);
		if (empty($convertOgcFilterXslString)) throw new HttpException("Empty file 'convertOgcFilter...'", 404);

		$requestXml = new DOMDocument();
		$isFileCorrect = @$requestXml->loadXml($ogcFilterXmlString);
		if (false === $isFileCorrect) throw new HttpException("Parse error for ogc filter", 400);
		$convertOgcFilterXsl = new DOMDocument();
		$isFileCorrect = @$convertOgcFilterXsl->loadXml($convertOgcFilterXslString);
		if (false === $isFileCorrect) throw new HttpException("Parse error for file 'convertOgcFilter...'", 500);
		$proc = new XSLTProcessor();
		$isImported = $proc->importStylesheet($convertOgcFilterXsl);
		if (false === $isImported) throw new HttpException("XSLT Parse error for import 'convertOgcFilter...'", 500);
		return $proc->transformToXML($requestXml);
	}

}