se/SE/se-lib/AclQueryFeatures.php

<?php

Lib::loadClass('ACL');
Lib::loadClass('SqlQueryWhereBuilder');
Lib::loadClass('ParseOgcFilter');
Lib::loadClass('TableAcl');

// usage: (Acl class)::buildQuery($params): return new AclQueryFeatures($this, $params);
//				(view): $queryFeatures = $acl->buildQuery($params);
//				(view): $total = $queryFeatures->getTotal();
//				(view): $items = $queryFeatures->getItems();
// example: @see TableAcl, TableAjax

// Special Filter Access - btns visible only if user don't have super access perms. If has, then will always see all rows.

class AclQueryFeatures {

	public $_params;
	public $_acl;
	public $_query;
	public $_total;
	public $_legacyMode;
	public $_selectLocalFields; // TODO: leave here or move to AclQueryBuilder? use join for perf?
	public $_selectRemote; // TODO: ...
	public $_dbgExecTime;
	public $_instanceID;
	public $_foundFeatures;

	public function __construct($acl, $params, $legacyMode = false) {
		$this->_acl = $acl;
		$this->_params = $params;
		$this->_query = null;
		$this->_total = null;
		$this->_legacyMode = $legacyMode;
		// TODO: _legacyMode = ($from instanceof simple schema or another programmed objects)
		$this->_selectLocalFields = null;
		$this->_selectRemote = null;
		$this->_foundFeatures = [];
		// 'skipFeaturesAsXlink' => $this->_foundFeatures
		if (array_key_exists('skipFeaturesAsXlink', $this->_params)) {
			$this->_foundFeatures = V::get('skipFeaturesAsXlink', [], $this->_params, 'array');
			unset($this->_params['skipFeaturesAsXlink']);
		}


		$DBG_LOG_TO_FILE = false; // TODO: read from ENV or REQUEST
		if ($DBG_LOG_TO_FILE) {
			Lib::loadClass('DebugExecutionTime');
			$this->_dbgExecTime = new DebugExecutionTime();
			$this->_dbgExecTime->setLogFile('/tmp/se-dbg-exec-time--AclQueryFeatures.csv', $format = 'csv'); // $format: 'csv', 'json'
			$this->_logToFile("from: '" . $this->_acl->getNamespace() . "'");
		}
	}

	public function _logToFile($msg) {
		if ($this->_dbgExecTime) {
			$idInstance = $this->_getInstanceID();
			$this->_dbgExecTime->logToFile("#{$idInstance}: {$msg}");
		}
	}
	public function _getInstanceID() {
		static $_INSTANCE_ID = 0;
		if (!$this->_instanceID) {
			$_INSTANCE_ID += 1;
			$this->_instanceID = $_INSTANCE_ID;
		}
		return $this->_instanceID;
	}

	public function parseQueryValue($fieldName, $searchQuery, $fieldType = 'xsd:string') {
		if ('!NULL' === $searchQuery) return ['is not null', null];
		if ('IS NOT NULL' === $searchQuery) return ['is not null', null];
		if ('NULL' === $searchQuery) return ['is null', null];
		if ('IS NULL' === $searchQuery) return ['is null', null];
		switch ($fieldType) {
			case 'p5Type:polygon':
			case 'p5Type:lineString':
			case 'p5Type:point':
			case 'gml:PolygonPropertyType':
			case 'gml:PointPropertyType':
			case 'gml:LineStringPropertyType':
			case 'gml:GeometryPropertyType': return $this->_parseGeomQuery($searchQuery);
			//	 $sqlFilter = $this->_sqlValueForGeomField($fldName, $v, 't');
			// if ('_CSV_NUM' == substr($fldName, -8)) { // if ($this->isCsvNumericField($fldName)) { // TODO: xsd type - p5:csv_num
			//	 $sqlFilter = $this->_sqlValueForCsvNumericField($fldName, $v, 't');
			//	 if ($sqlFilter) $sql_where_and[] = $sqlFilter;
			//	 continue;
			// }
		}

		switch (substr($searchQuery, 0, 1)) {
			case '=': return ['=', substr($searchQuery, 1)];
			case '>':
				switch (substr($searchQuery, 1, 1)) {
					case '=': return ['>=', substr($searchQuery, 2)];
					default:	return ['>', substr($searchQuery, 1)];
				}
			case '<':
				switch (substr($searchQuery, 1, 1)) {
					case '=': return ['<=', substr($searchQuery, 2)];
					case '>': return ['!=', substr($searchQuery, 2)];
					default:	return ['<', substr($searchQuery, 1)];
				}
			case '!':
				switch (substr($searchQuery, 1, 1)) {
					case '=': return ['!=', substr($searchQuery, 2)];
					default:	return ['not like', substr($searchQuery, 1)];
				}
			default: {
				switch ($fieldType) {
					case 'xsd:long':
					case 'xsd:number':
					case 'xsd:int':
					case 'xsd:integer': {
						if (false !== strpos($searchQuery, '%')) return ['like', $searchQuery];
						return ['=', $searchQuery];
					}
					default: {
						if (false !== strpos($searchQuery, '%')) return ['like', $searchQuery];
						$queryWhereBuilder = new SqlQueryWhereBuilder();
						return ['and'
							, array_map(function ($word) use ($fieldName) {
									return [$fieldName, 'like', "%{$word}%"];
								}, $queryWhereBuilder->splitQueryToWords($searchQuery)
							)
						];
					}
				}
				return ['=', $searchQuery];
			}
		}
	}
	public function _parseGeomQuery($searchQuery) { // _sqlValueForGeomField($fldName, $fltrValue, $tblPrefix = 't')
		// example: BBOX:54.40993961633866,18.583889010112824,54.337945760687454,18.397121431987586
		DBG::log($searchQuery, 'string', "\$searchQuery");
		if ('BBOX:' == substr($searchQuery, 0, 5)) {
			$valParts = explode(',', substr($searchQuery, 5));
			if (4 !== count($valParts)) throw new Exception("Wrong BBOX query");
			$valParts = array_filter($valParts, 'is_numeric');
			if (4 !== count($valParts)) throw new Exception("Wrong BBOX query - expected 4 numeric values");
			$bounds = "POLYGON((
				{$valParts[3]} {$valParts[2]},
				{$valParts[3]} {$valParts[0]},
				{$valParts[1]} {$valParts[0]},
				{$valParts[1]} {$valParts[2]},
				{$valParts[3]} {$valParts[2]}
			))";
			// for mysql 5.6 use ST_Contains() @see http://dev.mysql.com/doc/refman/5.6/en/spatial-relation-functions.html
			return [ 'Intersects', $bounds ];
		}
		else if ('GeometryType=' == substr($fltrValue, 0, 13)) {
			return [ 'GeometryType', substr($fltrValue, 13) ];
		}
		throw new Exception("Not implemented geometry query string"); // TODO:? return null;
	}

	public function _sqlValueForCsvNumericField($fldName, $fltrValue, $tblPrefix = 't') {
		$sqlFilter = false;
		if (is_numeric($fltrValue)) {
			$sqlFilter = "FIND_IN_SET('{$fltrValue}', `{$fldName}`)>0";
		} else if (false !== strpos($fltrValue, ' ')) {
			$sqlGlue = " or ";
			$fltrValues = $fltrValue;
			if ('&' == substr($fltrValues, 0, 1)) {
				$fltrValues = substr($fltrValues, 1);
				$sqlGlue = " and ";
			}
			$fltrValues = explode(' ', $fltrValues);
			$sqlNumericValues = array();
			foreach ($fltrValues as $fltrVal) {
				if (is_numeric($fltrVal)) {
					$sqlNumericValues[] = "FIND_IN_SET('{$fltrVal}', `{$fldName}`)>0";
				}
			}
			if (!empty($sqlNumericValues)) {
				$sqlFilter = "(" . implode($sqlGlue, $sqlNumericValues) . ")";
			}
		}
		return $sqlFilter;
	}

	public function parseSpecialFilterMsgs($type) {
		$rootTableName = $this->_acl->getRootTableName();
		DBG::log($rootTableName, 'string', "parse SpecialFilter Msgs({$type}), \$rootTableName");
		$sqlHasFltrMsgs = "
			select 1
			from `CRM_UI_MSGS` m
			where m.`uiTargetName`=CONCAT('{$rootTableName}.', t.`ID`)
				and m.`uiTargetType`='default_db_table_record'
				and m.`A_STATUS` not in('DELETED')
			limit 1
		";
		switch ($type) {
			case 'HAS_MSGS': return " ({$sqlHasFltrMsgs})=1 ";
			case 'NO_MSGS':	return " ({$sqlHasFltrMsgs}) is null ";
			case 'NEW_MSGS': {
				$sqlNewFltrMsgs = "
					select 1
					from `CRM_UI_MSGS` m
					where m.`uiTargetName`=CONCAT('{$rootTableName}.', t.`ID`)
						and m.`uiTargetType`='default_db_table_record'
						and m.`A_STATUS` in('WAITING')
					limit 1
				";
				return " ({$sqlNewFltrMsgs})=1 ";
			}
		}
		return null;
	}

	public function parseSpecialFilterProblemy($type) {
		DBG::log($type, 'string', "parse SpecialFilter Problemy");
		switch ($type) {
			case 'PROBLEM': return ['A_PROBLEM', '!=', ''];
			case 'WARNING': return ['A_PROBLEM', '=', 'WARNING'];
			case 'NORMAL':	return ['A_PROBLEM', '=', 'NORMAL'];
		}
		return null;
	}

	public function parseSpecialFilterStatus($type) {
		DBG::log($type, 'string', "parse SpecialFilter Status");
		switch ($type) {
			case 'WAITING': return ['A_STATUS', '=', 'WAITING'];
			case 'AKTYWNI': return ['A_STATUS', 'or', [ // `A_STATUS` in('NORMAL', 'WARNING') ";
				['A_STATUS', '=', 'NORMAL'],
				['A_STATUS', '=', 'WARNING'],
			] ];
		}
		return null;
	}

	public function parseSpecialFilterSpotkania($type) {
		DBG::log($type, 'string', "parse SpecialFilter Spotkania");
		switch ($type) {
			case 'OLD': return ['L_APPOITMENT_DATE', 'and', [
				['L_APPOITMENT_DATE', 'UNIX_TIMESTAMP_LESS_THAN_NOW'],
			] ];
			//					 COALESCE(UNIX_TIMESTAMP(t.`L_APPOITMENT_DATE`), 0) < UNIX_TIMESTAMP()
			//					 and t.`L_APPOITMENT_DATE` != ''
			//					 and t.`L_APPOITMENT_DATE` != '0000-00-00 00:00:00'
			case 'NOW': return ['L_APPOITMENT_DATE', 'and', [
				[ 'L_APPOITMENT_DATE', 'UNIX_TIMESTAMP_NOW_3600'],
			] ];
			//					 COALESCE(UNIX_TIMESTAMP(t.`L_APPOITMENT_DATE`), 0) < UNIX_TIMESTAMP()+3600
			//					 and COALESCE(UNIX_TIMESTAMP(t.`L_APPOITMENT_DATE`), 0) > UNIX_TIMESTAMP()-3600
			case 'TODAY': return ['L_APPOITMENT_DATE', 'and', [
				['L_APPOITMENT_DATE', 'UNIX_TIMESTAMP_GREATER_THAN', mktime(0,0,0, date("m"), date("d"),		 date("Y"))],
				['L_APPOITMENT_DATE', 'UNIX_TIMESTAMP_LESS_THAN',		mktime(0,0,0, date("m"), date("d") + 1, date("Y"))],
			] ];
			//				 $start = mktime(0,0,0, date("m"), date("d"), date("Y"));
			//				 $end = mktime(0,0,0, date("m"), date("d") + 1, date("Y"));
			//				 $sqlFltr = "
			//					 COALESCE(UNIX_TIMESTAMP(t.`L_APPOITMENT_DATE`), 0) > '{$start}'
			//					 and COALESCE(UNIX_TIMESTAMP(t.`L_APPOITMENT_DATE`), 0) < '{$end}'
			//				 ";
			case 'TOMORROW': return ['L_APPOITMENT_DATE', 'and', [
				['L_APPOITMENT_DATE', 'UNIX_TIMESTAMP_GREATER_THAN', mktime(0,0,0, date("m"), date("d") + 1, date("Y"))],
				['L_APPOITMENT_DATE', 'UNIX_TIMESTAMP_LESS_THAN',		mktime(0,0,0, date("m"), date("d") + 2, date("Y"))],
			] ];
			case 'YESTERDAY': return ['L_APPOITMENT_DATE', 'and', [
				['L_APPOITMENT_DATE', 'UNIX_TIMESTAMP_GREATER_THAN', mktime(0,0,0, date("m"), date("d") - 2, date("Y"))],
				['L_APPOITMENT_DATE', 'UNIX_TIMESTAMP_LESS_THAN',		mktime(0,0,0, date("m"), date("d") - 1, date("Y"))],
			] ];
			case 'BRAK': return ['L_APPOITMENT_DATE', 'or', [
				['L_APPOITMENT_DATE', '=', ''],
				['L_APPOITMENT_DATE', '=', '0000-00-00 00:00:00'],
			] ];
		}
		return null;
	}

	public function parseSpecialFilterAccess() {
		$userLogin = User::getLogin();
		$usrAclGroups = User::getLdapGroupsNames();
		DBG::log(['ns'=>$this->_acl->getNamespace(), 'login'=>$userLogin, 'hasWriteField'=>$this->_acl->hasWriteGroupField(), 'hasReadField'=>$this->_acl->hasReadGroupField(), 'hasOwnerField'=>$this->_acl->hasOwnerField(), 'groups'=>$usrAclGroups], 'array', "parse SpecialFilter Access");
		$orWhere = [];
		if ($this->_acl->hasWriteGroupField()) {
			$orWhere[] = ['A_ADM_COMPANY', '=', ''];// TODO: allow empty for everyone?
			foreach ($usrAclGroups as $group) $orWhere[] = ['A_ADM_COMPANY', '=', $group];
		}
		if ($this->_acl->hasReadGroupField()) {
			$orWhere[] = ['A_CLASSIFIED', '=', ''];// TODO: allow empty for everyone?
			foreach ($usrAclGroups as $group) $orWhere[] = ['A_CLASSIFIED', '=', $group];
		}
		if (!empty($orWhere) && $this->_acl->hasOwnerField()) {
			$orWhere[] = ['L_APPOITMENT_USER', '=', $userLogin];
		}
		return (!empty($orWhere)) ? [null, 'or', $orWhere] : null;
	}

	public function parseOgcFilter($ogcFilter) {
		$parser = new ParseOgcFilter();
		$parser->loadOgcFilter($ogcFilter);
		$queryWhereBuilder = $parser->convertToSqlQueryWhereBuilder();
		return $queryWhereBuilder->getQueryWhere('t'); // TODO: $this->_fromPrefix
	}

	public function getQuery() {
		if ($this->_query) return clone($this->_query);
		// $ds = $this->_acl->getDataSource(); // TODO: only for TableAcl // TODO: move _parseSqlWhere to this class
		$filtrIsInstance = []; // $filtrIsInstance = [ $this->_acl->getNamespace() ];
		$filtrIsNotInstance = [];
		if (!empty($this->_params['f_is_instance'])) $filtrIsInstance = $this->_params['f_is_instance'];
		if (!empty($this->_params['f_is_not_instance'])) $filtrIsNotInstance = $this->_params['f_is_not_instance'];

		$this->_query = ACL::query($this->_acl)
			->isInstance($filtrIsInstance)
			->isNotInstance($filtrIsNotInstance);
		// ->join($instanceTable, 'i', [ 'rawJoin' => "i.pk = t.{$sqlPrimaryKey} and i.idInstance = {$idInstance}" ])
		// $this->_query->where($ds->_parseSqlWhere($params))
		DBG::log($this->_params, 'array', "AclQueryFeatures::getQuery \$this->_params");
		foreach ($this->_params as $k => $v) {
			// DBG::log(['v'=>$v, 'is_numeric' => is_numeric($k), 'is_int' => is_int($k), 'is_array' => is_array($v)], 'array', "AclQueryFeatures::getQuery \$this->_params[{$k}]");
			if (is_int($k) && is_array($v)) {
				$this->_query->where($v); // TODO: check format [$fieldName, $comparisonSign, $value]
			} else if (is_int($k) && null === $v) { // skip NULL
			} else if ('f_is_instance' === $k) { // parsed before
			} else if ('f_is_not_instance' === $k) { // parsed before
			} else if ('@instances' === $k) { // skip - select
			} else if ('cols' === $k) { // skip - select
			} else if ('f_@selected' === $k) { // skip - f_@selected
				Lib::loadClass('FeatureAttrSelected');
				$userSelectedTableName = FeatureAttrSelected::getAttributeTableName($typeName = $this->_acl->getNamespace(), $idUser = User::getID());
				$pkFieldName = $this->_acl->getPrimaryKeyField();
				if ('YES' === $v) {
					$this->_query->join($userSelectedTableName, 't_selected', [ 'rawJoin' => "t.{$pkFieldName} = t_selected.primaryKey" ]);
				} else if ('NO' === $v) {
					$this->_query->leftJoin($userSelectedTableName, 't_selected', [ 'rawJoin' => "t.{$pkFieldName} = t_selected.primaryKey" ]);
					$this->_query->where(['t_selected.primaryKey', 'is null']);
				} else {
					throw new Exception("Not implemented @selected filter value '{$v}'");
				}
			} else if ('f_' === substr($k, 0, 2) && is_string($v) && strlen($k) > 3) {
				$fieldName = substr($k, 2);
				$fieldType = $this->_acl->getXsdFieldType($fieldName);
				list($comparisonSign, $value) = $this->parseQueryValue($fieldName, $v, $fieldType);
				DBG::log([ $fieldName, $comparisonSign, $value, $fieldType ], 'array', "parseQueryValue");
				$this->_query->where([$fieldName, $comparisonSign, $value]);
			} else if ('sf_' === substr($k, 0, 3) && is_string($v) && strlen($k) > 4) {
				switch (substr($k, 3)) {
					case 'Msgs': $this->_query->where($this->parseSpecialFilterMsgs($v)); break;
					case 'Problemy': $this->_query->where($this->parseSpecialFilterProblemy($v)); break;
					case 'Status': $this->_query->where($this->parseSpecialFilterStatus($v)); break;
					case 'Spotkania': $this->_query->where($this->parseSpecialFilterSpotkania($v)); break;
					case 'Access': break; // SKIP - used below
					default: {
						if (method_exists($this->_acl, 'parseSpecialFilter')) {
							$this->_query->where(
								$this->_acl->parseSpecialFilter(substr($k, 3), $v)
							);
						} else {
							throw new Exception("Not Implemented special filter '".substr($k, 3)."'");
						}
					}
				}
			} else if ('ogc:Filter' === $k) {
				$this->_query->where($this->parseOgcFilter($v));
			} else if ('primaryKey' === $k) {
				$fieldName = $this->_acl->getPrimaryKeyField();
				$fieldType = $this->_acl->getXsdFieldType($fieldName);
				if (is_array($v)) {
					DBG::log([ $fieldName, $v, $fieldType ], 'array', "parseQueryValue :: primaryKey, value is array");
					$this->_query->where([ $fieldName, 'or', array_map(function ($pk) use ($fieldName) {
						return [ $fieldName, '=', $pk ];
					}, $v) ]);
				} else {
					list($comparisonSign, $value) = $this->parseQueryValue($fieldName, $v, $fieldType);
					DBG::log([ $fieldName, $comparisonSign, $value, $fieldType ], 'array', "parseQueryValue");
					$this->_query->where([$fieldName, $comparisonSign, $value]);
				}
			} else if ('__backRef' === $k) { // skip - parse below
			} else if ('__childRef' === $k) { // skip - parse below
			} else if ('limit' === $k) {
			} else if ('limitstart' === $k) {
			} else if ('order_by' === $k) {
			} else if ('order_dir' === $k) {
			} else if ('sortBy' === $k) {
			} else {
				throw new Exception("Not Implemented param '{$k}' = '{$v}'");
			}
		}

		// sf_Access: if 'SHOW' then show all rows, but data with ***
		if ('SHOW' !== V::get('sf_Access', '', $this->_params)) $this->_query->where($this->parseSpecialFilterAccess());

		if (array_key_exists('__backRef', $this->_params)) {
			$backRef = $this->_params['__backRef'];
			if (!is_array($backRef)) throw new Exception("Wrong back ref structure - expected array");
			if (empty($backRef['namespace'])) throw new Exception("Wrong back ref structure - missing namespace");
			if (empty($backRef['primaryKey'])) throw new Exception("Wrong back ref structure - missing primaryKey");
			if (empty($backRef['fieldName'])) throw new Exception("Wrong back ref structure - missing fieldName");
			// TODO: $this->_query->where([ '__backRef' ]); or	$this->_query->join([ '__backRef' ]);
			$refAcl = ACL::getAclByNamespace($backRef['namespace']);
			if ($refAcl->getSourceName() !== $this->_acl->getSourceName()) throw new Exception("Not implemented join with different source");
			$refTable = ACL::getRefTable($refAcl->getNamespace(), $backRef['fieldName']);
			// TODO: 'in' operator? // $this->_query->where($pkField, 'in', "");
			$sqlPk = $this->getAclSqlPrimaryKeyField();
			$sqlBackRefPk = DB::getPDO()->quote($backRef['primaryKey']);
			$limit = V::get('limit', 10, $this->_params, 'int');
			$offset = V::get('limitstart', 0, $this->_params, 'int');
			DBG::log(['limit' => $limit, 'offset' => $offset], 'array', "DBG: limit with __backRef \$this->_params");
			$sqlLimit = "limit {$limit} offset {$offset}";
			// $this->_query->where("
			// 	t.{$sqlPk} in (
			// 		select refTable.REMOTE_PRIMARY_KEY
			// 		from `{$refTable}` refTable
			// 		where refTable.PRIMARY_KEY = {$sqlBackRefPk}
			// 		order by refTable.REMOTE_PRIMARY_KEY DESC -- TODO refTable.SORT_PRIO
			// 		-- {$sqlLimit} -- BUG MariaDB not support limit in subquery
			// 	)
			// ");
			// TODO: convert `where t.ID in ( ... )` into `join` and `order by`
			// join `CRM__#REF_TABLE__120_VIEW` refTable on (refTable.REMOTE_PRIMARY_KEY = t.ID and refTable.PRIMARY_KEY = '37' )
			DBG::log([
				'ACL::getRefTable(...)' => [$refAcl->getNamespace(), $backRef['fieldName']],
				'$backRef' => $backRef,
				'$refTable' => $refTable
			], 'array', "DBG: join \$join class(".get_class($join).")");

			$this->_query->join($refTable, 'refTable', [ 'rawJoin' => "t.{$sqlPk} = refTable.REMOTE_PRIMARY_KEY and refTable.PRIMARY_KEY = {$sqlBackRefPk}" ]);
			// moved to getItems: $this->_query->orderBy("refTable.REMOTE_PRIMARY_KEY DESC"); // TODO: order by refTable.SORT_PRIO
		}

		if (array_key_exists('__childRef', $this->_params)) {
			$childRef = $this->_params['__childRef'];
			if (!is_array($childRef)) throw new Exception("Wrong child ref structure - expected array");
			if (empty($childRef['namespace'])) throw new Exception("Wrong child ref structure - missing namespace");
			if (empty($childRef['primaryKey'])) throw new Exception("Wrong child ref structure - missing primaryKey");
			$refAcl = ACL::getAclByNamespace($childRef['namespace']);
			if ($refAcl->getSourceName() !== $this->_acl->getSourceName()) throw new Exception("Not implemented join with different source");
			$refTypeName = Api_WfsNs::typeName($childRef['namespace']);
			DBG::log("\$refTypeName({$refTypeName})");
			$refTable = ACL::getRefTable($this->_acl->getNamespace(), $refTypeName);
			// TODO: 'in' operator? // $this->_query->where($pkField, 'in', "");
			$sqlPk = $this->getAclSqlPrimaryKeyField();
			$sqlChildRefPk = DB::getPDO()->quote($childRef['primaryKey']);
			$this->_query->where("
				t.{$sqlPk} in (
					select refTable.PRIMARY_KEY
					from `{$refTable}` refTable
					where refTable.REMOTE_PRIMARY_KEY = {$sqlChildRefPk}
				)
			");
		}

		DBG::log($this->_query, 'array', "TODO: optimize \$this->_query");
		$this->_USE_TEMPORARY_TABLE = false; // TODO: ...
		if ($this->_USE_TEMPORARY_TABLE) {
			$sortBy = ($this->hasParam('sortBy')) ? $this->getParam('sortBy') : null;
			if (!$sortBy) {
				$sortBy = $this->hasParam('order_by')
					? ( $this->hasParam('order_dir')
							? $this->getParam('order_by') . " " . $this->getParam('order_dir')
							: $this->getParam('order_by')
						)
					: '';
			}

			$TMP_TABLE_NAME = $this->_acl->getRootTableName() . "__#TEMPORARY";
			DBG::log("\$TMP_TABLE_NAME='{$TMP_TABLE_NAME}'");
			Lib::loadClass('AntAclBase');
			if ($this->_acl instanceof AntAclBase) {
				$baseSql = $this->_query->generateSql();
				DB::getPDO()->execSql("
					create temporary table `{$TMP_TABLE_NAME}`
					{$baseSql}
				");
				DBG::log(DB::getPDO()->fetchValue("select count(*) as cnt from `{$TMP_TABLE_NAME}`"), 'array', "select count(*) cnt from '{$TMP_TABLE_NAME}'");

				$tmpAclFrom = clone($this->_acl);
				$tmpAclFrom->_rootTableName = $TMP_TABLE_NAME;
				// $tmpAclFrom = AntAclBase::buildInstance($this->_acl->getID(), [
				// 	'name' => $this->_acl->getName(),
				// 	'_rootTableName' => $TMP_TABLE_NAME,
				// 	'idDatabase' => $this->_acl->getDatabaseID(),
				// 	'namespace' => $this->_acl->getNamespace(),
				// 	'primaryKey' => $this->_acl->getPrimaryKeyField(),
				// 	'field' => $this->_acl->getFields(),
				// ]);
				Lib::loadClass('AclQueryBuilder');
				$query = new AclQueryBuilder();
				$query->from($tmpAclFrom, $prefix = 't');
				DBG::log($query, 'array', "TODO: \$query by temporary table");
				// $this->_query = $query;
				// return $this->_query;
			}

		}
		return clone($this->_query);
	}

	public function getTotal() {
		$this->_beforeFetchData();
		if ($this->_legacyMode) return $this->_acl->getTotal($this->_params);
		if (null !== $this->_total) return $this->_total;
		$this->_total = $this->getQuery()->fetchTotal();
		return $this->_total;
	}

	public function hasParam($key) { return !empty($this->_params[$key]); }
	public function getParam($key) { return V::get($key, '', $this->_params); }

	public function getItems() {
		$this->_logToFile('getItems() ...');
		$this->_beforeFetchData();
		if ($this->_legacyMode) return $this->_acl->getItems($this->_params);// TODO: array_map( $r => (array)$r )
		// 'limit' => 10,
		//	'limitstart' => 0,
		//	'order_by' => 'ID',
		//	'order_dir' => 'desc',
		// TODO: sortBy from wfs query
		$sortBy = ($this->hasParam('sortBy')) ? $this->getParam('sortBy') : null;
		if (!$sortBy && array_key_exists('__backRef', $this->_params)) {
			$sortBy = "refTable.REMOTE_PRIMARY_KEY DESC"; // TODO: order by refTable.SORT_PRIO
		}
		if (!$sortBy) {
			$sortBy = $this->hasParam('order_by')
				? ( $this->hasParam('order_dir')
						? $this->getParam('order_by') . " " . $this->getParam('order_dir')
						: $this->getParam('order_by')
					)
				: '';
		}
		$limit = V::get('limit', 10, $this->_params, 'int');
		$offset = V::get('limitstart', 0, $this->_params, 'int');

		DBG::log(['params' => $this->_params, 'sortBy' => $sortBy, 'limit' => $limit, 'offset' => $offset], 'array', '$this->_params');

		$select = $this->prepareSelect();
		DBG::log($select, 'array', "\$select is(TableAcl)=(".($this->_acl instanceof TableAcl).")");
		// DBG::log($this->getQuery(), 'array', "\$select is(TableAcl)=(".($this->_acl instanceof TableAcl).") \$this->getQuery()");

		$items = $this->fetchRowsRefs(
			$this->getQuery()
				->select(array_merge(['@primaryKey'], $select))
				->limit($limit)
				->offset($offset)
				->orderBy($sortBy)
				->fetchAll()
		);
		$this->_logToFile('getItems() fetched.');
		return $items;
	}

	public function getRawQueryPrimaryKeys() {
		if ($this->_legacyMode) throw new Exception("getRawQueryPrimaryKeys not supported in legacyMode");

		$sortBy = ($this->hasParam('sortBy')) ? $this->getParam('sortBy') : null;
		if (!$sortBy && array_key_exists('__backRef', $this->_params)) {
			$sortBy = "refTable.REMOTE_PRIMARY_KEY DESC"; // TODO: order by refTable.SORT_PRIO
		}
		if (!$sortBy) {
			$sortBy = $this->hasParam('order_by')
				? ( $this->hasParam('order_dir')
						? $this->getParam('order_by') . " " . $this->getParam('order_dir')
						: $this->getParam('order_by')
					)
				: '';
		}
		$limit = V::get('limit', 10, $this->_params, 'int');
		$offset = V::get('limitstart', 0, $this->_params, 'int');
		return $this->getQuery()
			->select(['@primaryKey'])
			->limit($limit)
			->offset($offset)
			->orderBy($sortBy)
			->generateSql();
	}

	function _beforeFetchData() {
		if (method_exists($this->_acl, 'onBeforeFetchData')) $this->_acl->onBeforeFetchData();
	}

	public function getItem($primaryKey) { // TODO: throw exception if not found?
		$this->_beforeFetchData();
		if ($this->_legacyMode) return (array)$this->_acl->getItem($primaryKey, $this->_params);
		$select = $this->prepareSelect();
		$pkField = $this->_acl->getPrimaryKeyField();
		return $this->fetchRowRefs(
			$this->getQuery()
				->select($select)
				->where([$pkField, '=', $primaryKey])
				->fetchFirst()
		);
	}

	public function prepareSelect() { // TODO: replace with getSelectLocal
		// TODO: select from params: 'cols' => [ fieldName, ... ]
		// TODO: select from params: '@instances' => 1
		// TODO: if no fields set, then '*'
		// TODO: select must contain primaryKey
		return $this->getSelectLocal();
	}
	public function getSelectLocal() { // @returns [ $fieldName, ... ]
		// TODO: rawSelect!
		if (null !== $this->_selectLocalFields) return $this->_selectLocalFields;
		$this->_selectLocalFields = [];
		$todoFetchAllCols = false; // select t.*
		if (!empty($this->_params['cols'])) {
			if (in_array('*', $this->_params['cols'])) $todoFetchAllCols = true;
			$acl = $this->_acl;
			$this->_selectLocalFields = array_filter($this->_params['cols'], function ($fieldQuery) use ($acl) {
				if ('*' === $fieldQuery) return false;
				list($fieldName, $subFieldQuery) = explode('/', $fieldQuery, 2);
				if (!empty($subFieldQuery)) return false;
				return $acl->isLocalField($fieldName);
			});
		}
		if (!empty($this->_params['@instances'])) $this->_selectLocalFields[] = '@instances';
		if (empty($this->_selectLocalFields)) $todoFetchAllCols = true;
		if (1 === count($this->_selectLocalFields) && in_array('@instances', $this->_selectLocalFields)) $todoFetchAllCols = true;
		// if ($this->_acl instanceof TableAcl) {
		//	 $rawSelect = $this->_acl->getDataSource()->_getSqlCols();
		//	 DBG::log($rawSelect, 'string', "DBG raw select");
		//	 if ('*' !== $rawSelect && 't.*' !== $rawSelect) {
		//		 $this->_selectLocalFields['rawSelect'] = $rawSelect;
		//	 }
		// }
		if (!empty($this->_params['cols'])) DBG::log($this->_params['cols'], 'array', "\$this->_params[cols] (" . ($this->_acl ? $this->_acl->getNamespace() : 'unknown') . ")");

		if ($todoFetchAllCols) {
			// $this->_selectLocalFields[] = '*'; // TODO: select all $this->from local fields
			DBG::log($this->_acl->getLocalFieldList(), 'array', "\$this->_acl->getLocalFieldList()");
			foreach ($this->_acl->getLocalFieldList() as $localFieldName) {
				$this->_selectLocalFields[] = $localFieldName;
			}
		}

		$primaryKeyField = $this->getAclSqlPrimaryKeyField();
		if (!in_array($primaryKeyField, $this->_selectLocalFields)) {
			$this->_selectLocalFields[] = $primaryKeyField;
		}

		// TODO: always add A_ADM_COMPANY, A_CLASSIFIED, L_APPOITMENT_USER ?

		DBG::log($this->_selectLocalFields, 'array', '$this->_selectLocalFields');
		return $this->_selectLocalFields;
	}
	public function getSelectRemote() { // @returns [ $fieldName => [ $fieldName, ... ] ]
		if (null !== $this->_selectRemote) return $this->_selectRemote;
		$this->_selectRemote = [];
		if (!empty($this->_params['cols'])) {
			$cols = $this->_params['cols'];
			if (in_array('*', $cols)) {
				$this->_selectLocalFields[] = '*';
				$cols = array_filter($cols, function ($fieldQuery) { return '*' !== $fieldQuery; });
			}
			$acl = $this->_acl;
			$cols = array_filter($cols, function ($fieldQuery) use ($acl) {
				list($fieldName, $subFieldQuery) = explode('/', $fieldQuery, 2);
				// if (empty($subFieldQuery)) return false;
				return !$acl->isLocalField($fieldName);
			});
			foreach ($cols as $fieldQuery) { // group by fieldName
				list($fieldName, $subFieldQuery) = explode('/', $fieldQuery, 2);
				if (!array_key_exists($fieldName, $this->_selectRemote)) $this->_selectRemote[$fieldName] = [];
				if (!empty($subFieldQuery)) $this->_selectRemote[$fieldName][] = $subFieldQuery;
			}
		}
		return $this->_selectRemote;
	}

	public function fetchRowsRefs($rows) {
		$pkField = $this->_acl->getPrimaryKeyField();
		$namespace = $this->_acl->getNamespace();
		$this->_foundFeatures = array_merge($this->_foundFeatures, array_map(function ($row) use ($namespace, $pkField) {
			return "{$namespace}." . V::get($pkField, '', $row);
		}, $rows));
		DBG::log($this->_foundFeatures, 'array', "_foundFeatures");
		return array_map([ $this, 'fetchRowRefs' ], $rows);
	}

	public function fetchRowRefs($row) {
		try {
			return $this->_fetchRowRefs($row);
		} catch (Exception $e) {
			DBG::log($e);
		}
		return $row;
	}
	public function _fetchRowRefs($row) {
		if (!$row) return $row;
		$sqlPk = $this->getAclSqlPrimaryKeyField();
		$primaryKey = $row[$sqlPk];
		DBG::log($row, 'array', "DBG primaryKey '{$primaryKey}'");
		if (!$primaryKey) throw new Exception("Missing primaryKey");
		$defaultRefLimit = 10; // TODO: get from $this->_params and pass to nested buildQuery
		$refLimitPlus1 = $defaultRefLimit + 1;
		foreach ($this->getSelectRemote() as $fieldName => $cols) {
			DBG::log($cols, 'array', "add select remote '{$fieldName}' \$cols");
			$xsdType = $this->_acl->getXsdFieldType($fieldName);
			if ('ref:' === substr($xsdType, 0, 4) && empty($cols)) {
				DBG::log("add remote xlink's '{$fieldName}' \$items[{$primaryKey}] ...");
				$refTable = ACL::getRefTable($this->_acl->getNamespace(), $fieldName);
				if (!$refTable) DBG::log("BUG: Missing refTable in add remote xlink's '{$fieldName}' \$items[{$primaryKey}]");
				if ($refTable) {
					$xlinks = DB::getPDO()->fetchAll("
						select r.REMOTE_PRIMARY_KEY
						from `{$refTable}` r
						where r.PRIMARY_KEY = '{$primaryKey}'
						order by r.REMOTE_PRIMARY_KEY DESC  -- TODO r.SORT_PRIO
						limit {$refLimitPlus1}
					");
					DBG::log($xlinks, 'array', "add remote xlink's for '{$fieldName}' \$items[{$primaryKey}]");
					$row[$fieldName] = array_map(function ($refInfo) use ($fieldName) {
						$ns = Core_AclHelper::parseTypeName($fieldName);
						return [
							// '_ns' => $ns,
							'xlink' => "{$ns['url']}#{$ns['name']}.{$refInfo['REMOTE_PRIMARY_KEY']}",
						];
					}, $xlinks);
					if (count($xlinks) > $defaultRefLimit) DBG::log('TODO: xlink FETCH MORE DATA...');
					if (count($xlinks) > $defaultRefLimit) $row[$fieldName][] = [ 'p5:links' => [
						'p5:next' => [
							'@typeName' => $fieldName,
							'@backRefNS' => $this->_acl->getNamespace(),
							'@backRefPK' => $primaryKey,
							'@startIndex' => $defaultRefLimit,
							'@maxFeatures' => $defaultRefLimit,
							'value' => Request::getScriptUri() . "?SERVICE=WFS&VERSION=1.0.0&TYPENAME={$fieldName}&REQUEST=GetFeature&backRefNS=".$this->_acl->getNamespace()."&backRefPK={$primaryKey}&backRefField={$fieldName}&maxFeatures={$defaultRefLimit}&startIndex={$defaultRefLimit}",
						],
					] ];
					DBG::log($row[$fieldName], 'array', "remote xlinks for \$items[{$primaryKey}][{$fieldName}]");
				}
			} else if ('ref:' === substr($xsdType, 0, 4) && !empty($cols)) {
				$refAcl = ACL::getAclByTypeName($fieldName);
				$refQuery = $refAcl->buildQuery([
					'cols' => $cols,
					'__backRef' => [
						'namespace' => $this->_acl->getNamespace(),
						'primaryKey' => $primaryKey,
						'fieldName' => $fieldName,
					],
					'limit' => $refLimitPlus1,
					'skipFeaturesAsXlink' => $this->_foundFeatures
					// TODO: add $defaultRefLimit + 1
				]);
				$totalRefs = $refQuery->getTotal();
				$items = $refQuery->getItems([ 'limit' => $defaultRefLimit ]);
				// TODO: if (count($items) > $defaultRefLimit) // TODO: add item for GUI - has more data + wfs link to fetch more (offset)
				DBG::log($items, 'array', "add remote items '{$fieldName}' \$items[{$primaryKey}][{$fieldName}] total({$totalRefs})");
				if ($totalRefs > $defaultRefLimit) {
					DBG::log('TODO: resolve recurse fetch more data link');
					array_pop($items); // remove last item
					$items[] = [ 'p5:links' => [
						'p5:next' => [
							'@typeName' => $fieldName,
							'@backRefNS' => $this->_acl->getNamespace(),
							'@backRefPK' => $primaryKey,
							'@startIndex' => $defaultRefLimit,
							'@maxFeatures' => $defaultRefLimit,
							'value' => Request::getScriptUri() . "?SERVICE=WFS&VERSION=1.0.0&TYPENAME={$fieldName}&REQUEST=GetFeature&backRefNS=".$this->_acl->getNamespace()."&backRefPK={$primaryKey}&backRefField={$fieldName}&maxFeatures={$defaultRefLimit}&startIndex={$defaultRefLimit}",
						],
					] ];
				}

				$refNs = $refAcl->getNamespace();
				$refPk = $refAcl->getPrimaryKeyField();
				$this__hasFeatureId = [ $this, 'hasFeatureId' ];
				$this__addFeatureId = [ $this, 'addFeatureId' ];
				$row[$fieldName] = array_map(function ($item) use ($fieldName, $refNs, $refPk, $this__hasFeatureId, $this__addFeatureId) {
					if (1 === count($item) && !empty($item['p5:links'])) return $item;
					$pk = V::get($refPk, '', $item);
					$featureId = "{$refNs}.{$pk}";
					if (!$this__hasFeatureId($featureId)) {
						$this__addFeatureId($featureId);
						return $item;
					} else {
						$ns = Core_AclHelper::parseTypeName($fieldName);
						return [
							'xlink' => "{$ns['url']}#{$ns['name']}.{$pk}",
						];
					}
				}, $items);
			} else { // TODO: field is not ref
				DBG::log($items, 'array', "NotImplemented - add remote items for non ref field \$items[{$primaryKey}][{$fieldName}]?");
			}
		}
		return $row;
	}

	public function hasFeatureId($featureId) {
		return in_array($featureId, $this->_foundFeatures);
	}
	public function addFeatureId($featureId) {
		$this->_foundFeatures[] = $featureId;
		DBG::log($this->_foundFeatures, 'array', "addFeatureId({$featureId})");
	}

	public function getAclSqlPrimaryKeyField() {
		return ($this->_acl instanceof Core_AclBase)
			? $this->_acl->getSqlPrimaryKeyField()
			: 'ID';
	}

}