se/SE/se-lib/Typespecial.php

<?php

Lib::loadClass('TypespecialBase');

/**
 * Zasoby:
 * DATABASE
 *   TABLE
 *     KOMORKA
 *   TYPESPECIAL
 *     PARAM_IN
 *     PARAM_OUT
 *       DANE ALIAS do komórki z innej tabeli
 *     PARAM_WHERE
 *
 * example:
 *   TYPESPECIAL 'id_project'
 *     PARAM_IN    ALIAS_ID -> 1260 -> IN7_DZIENNIK_KORESP.ID_PROJECT
 *     PARAM_OUT
 *       DANE ALIAS do komórki z innej tabeli
 *
 *** TODO: new struct, TODO: remove above
 *   TYPESPECIAL
 *     PARAM_OUT format: dla PARAM_IN = vcol pokzać listę, a dla PARAM_IN = col i perm W pokazać select/search
 *       PARAM_IN alias do komórki dla której ma się pokazać PARAM_OUT
 *       DANE alias do komórki/zmiennej z danymi do podstawienia
 *     PARAM_WHERE sql params - join tables
 *
 * $_SESSION['Typespecial_Cache'] = array(
 *   map = array(
 *     fieldID => typesiecialID
 *   ),
 *   typesiecialID => Typespecial Data
 * )
 *
 * Typespecial types:
 *   - typespecial function for form fields in edit, create
 *   - typespecial view function for virtual fields
 *
 * $_SESSION['USERS2_MARKETING_COLUMN']['RELATIVEDB']['SRC_COLUMN']['INTERNET_STATUS']="temp_USERS2_INTERNET_STATUS";
 * $_SESSION['USERS2_MARKETING_COLUMN']['RELATIVEDB']['TRG_COLUMN']['INTERNET_STATUS']="temp_USERS2_INTERNET_STATUS.USERS2_INTERNET_STATUS";
 * $_SESSION['USERS2_MARKETING_COLUMN']['RELATIVEDB']['TRG_COLUMN_NAME']['INTERNET_STATUS']="temp_USERS2_INTERNET_STATUS.USERS2_INTERNET_STATUS";
 * $_SESSION['USERS2_MARKETING_COLUMN']['RELATIVEDB']['JOINQUERY']['temp_USERS2_INTERNET_STATUS']="LEFT JOIN temp_USERS2_INTERNET_STATUS as temp_USERS2_INTERNET_STATUS on temp_USERS2_INTERNET_STATUS.S_ADDRESS_STREET=USERS2_MARKETING.S_ADDRESS_STREET ";
 *
 * used `CRM_LISTA_ZASOBOW`.`TYPE`:
			'TYPESPECIAL'
			'PARAM_IN'
			'PARAM_OUT'
			'PARAM_WHERE'
			'SQL_LIKE'					=> "`fld` LIKE '{param}'"
			'SQL_LIKE_PR'				=> "`fld` LIKE '%{param}%'"
			'SQL_NOT_LIKE'			=> "`fld` NOT LIKE '{param}'"
			'SQL_EQUAL'					=> "`fld`='{param}'"
			'SQL_NOT_EQUAL'			=> "`fld`!='{param}'"
			'SQL_IS_EMPTY'			=> "`fld`=''"
			'SQL_IS_NOT_EMPTY'	=> "`fld`!=''"
			'SQL_IS_NULL'				=> "`fld` IS NULL"
			'SQL_IS_NOT_NULL'		=> "`fld` IS NOT NULL"
			'SQL_IN'						=> "`fld` IN({params})"
			'SQL_NOT_IN'				=> "`fld` NOT IN({params})"
			'SQL_BETWEEN'				=> "`fld` BETWEEN {params1} AND {params2}"
			'SQL_NOT_BETWEEN'		=> "`fld` NOT BETWEEN {params1} AND {params2}"
 *
 *
'TYPESPECIAL','PARAM_IN','PARAM_OUT','PARAM_WHERE','SQL_LIKE','SQL_LIKE_PR','SQL_NOT_LIKE','SQL_EQUAL','SQL_NOT_EQUAL','SQL_IS_EMPTY','SQL_IS_NOT_EMPTY','SQL_IS_NULL','SQL_IS_NOT_NULL','SQL_IN','SQL_NOT_IN','SQL_BETWEEN','SQL_NOT_BETWEEN'

'KONTO-KSIEGOWE-OLD','KONTO-KSIEGOWE-ZESPOL','KONTO-KSIEGOWE','KONTO-KSIEGOWE-GRUPA','DANE','WYNIK_ZAPYTANIA','NARZEDZIE','PLIK','ZASOB_PLIKOW','TABELA','KOMORKA','BAZA_DANYCH','DATABASE_MYSQL','TRIGGER','SERWER','SERWER_PORT','DOKUMENTY','MAGAZYN','SEGREGATOR','POMIESZCZENIE','MATERIAL_REKLAMOWY','DZIAL','STANOWISKO','INNE','CZYNNOSC','PODMIOT','ALARM-SATEL','DOSTEP-ZAMEK','DOSTEP-KLUCZ','NOTATKA','URZADZENIE','TELBOX','SZAFA','REJESTRATOR','KAMERA','PIECZATKA','URL','URL_REQUEST_RESPOND','TYPESPECIAL','PARAM_IN','PARAM_OUT','PARAM_WHERE','SQL_LIKE','SQL_LIKE_PR','SQL_NOT_LIKE','SQL_EQUAL','SQL_NOT_EQUAL','SQL_IS_EMPTY','SQL_IS_NOT_EMPTY','SQL_IS_NULL','SQL_IS_NOT_NULL','SQL_IN','SQL_NOT_IN','SQL_BETWEEN','SQL_NOT_BETWEEN',''

 */
class Typespecial extends TypespecialBase {

	public $fldID;
	public $param_out;
	public $filters;
	public $fldAliasList;
	public $tblAliasList;
	public $sqlQuery;
	private $_fldName;// @use getFieldName()

	public function __construct($fldID, $fldName = '', $cacheData = array()) {
		$this->fldID = $fldID;
		$this->param_out = V::get('param_out', null, $cacheData);
		$this->sqlQuery = V::get('sqlQuery', null, $cacheData);
		if (empty($this->sqlQuery->from) && !empty($this->sqlQuery->form)) {
			$this->sqlQuery->from = $this->sqlQuery->form;
			unset($this->sqlQuery->form);
		}
		$this->tblAliasList = V::get('tblAliasList', null, $cacheData);
		if(V::get('DBG_TS', 0, $_GET) > 3){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">Typespecial cacheData (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($cacheData);echo'</pre>';}

		$this->_fldName = $fldName;
	}

	public function validate() {
		// TODO: validate cache data
		// TODO: check if has parse errors
		// TODO: try to run sql and check if has error
		return true;
	}

	/**
	 * @returns virtual fields id list
	 */
	public static function initFields($fieldIds) {
		if (empty($fieldIds)) {
			return;
		}

		$db = DB::getDB();
		$typeSpecials = array();
		$sql = "select z.`ID`
				, z.`ALIAS_ID` as fieldID
				, zpp.`ID` as TYPE_ID
			from `CRM_LISTA_ZASOBOW` as z
				join `CRM_LISTA_ZASOBOW` as zp on(zp.`ID`=z.`PARENT_ID`)
				join `CRM_LISTA_ZASOBOW` as zpp on(zpp.`ID`=zp.`PARENT_ID`)
			where zpp.`TYPE`='TYPESPECIAL'
				and zpp.`A_STATUS` in('WAITING','NORMAL')
				and zp.`TYPE`='PARAM_OUT'
				and z.`TYPE`='PARAM_IN'
				and z.`ALIAS_ID` in(" . implode(",", $fieldIds) . ")
		";
		if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$r->params_out = array();
			$r->filters = array();
			$typeSpecials[$r->TYPE_ID] = $r;
			$typeSpecials_2[$r->fieldID] = $r;
			$_SESSION['Typespecial_Cache']['map'][$r->fieldID] = $r->TYPE_ID;
			$_SESSION['Typespecial_Cache']['map_param_out'][$r->fieldID] = $r->TYPE_ID;
		}
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">typeSpecials (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($typeSpecials);echo'</pre>';}
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid blue;text-align:left;">typeSpecials_2 (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($typeSpecials_2);echo'</pre>';}

		if (empty($typeSpecials)) {
			return;
		}

		$sql = "select z.`ID`
				, z.`ALIAS_ID`
				, z.`TYPE`
				, z.`DESC`
				, za.`DESC` as ALIAS_DESC
				, za.`PARENT_ID` as ALIAS_PARENT_ID
				, zp.`ID` as PARAM_ID
				, zp.`ALIAS_ID` as ALIAS_ID_RETURN
				, zp.`DESC` as PARAM_DESC
				, zp.`PARENT_ID` as TYPE_ID
				, zp.`TYPE` as TYPE_TYPE
				, za.`TYPE` as ALIAS_TYPE
			from `CRM_LISTA_ZASOBOW` as z
				join `CRM_LISTA_ZASOBOW` as zp on(zp.`ID`=z.`PARENT_ID`)
				left join `CRM_LISTA_ZASOBOW` as za on(za.`ID`=z.`ALIAS_ID`)
			where zp.`TYPE` in('PARAM_OUT', 'PARAM_WHERE')
				and z.`TYPE` not in('PARAM_IN')
				and zp.`PARENT_ID` in(" . implode(",", array_keys($typeSpecials)) . ")
		";
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			if ($r->TYPE_TYPE == 'PARAM_OUT') {
				$typeSpecials[$r->TYPE_ID]->params_out[$r->PARAM_ID][] = $r;
			}
			else {
				if (!array_key_exists($r->PARAM_ID, $typeSpecials[$r->TYPE_ID]->filters)) {
					/*
					(
							[ID] => 4526
							[ALIAS_ID] => 1123
							[TYPE] => DANE
							[DESC] => IN
							[ALIAS_DESC] => A_STATUS
							[PARAM_ID] => 4531
							[ALIAS_ID_RETURN] => 0
							[PARAM_DESC] => AND
							[TYPE_ID] => 4519
							[TYPE_TYPE] => PARAM_WHERE
					)
					 */
					$typeSpecials[$r->TYPE_ID]->filters[$r->PARAM_ID] = new stdClass();
					$typeSpecials[$r->TYPE_ID]->filters[$r->PARAM_ID]->filtersByID = array();
					$typeSpecials[$r->TYPE_ID]->filters[$r->PARAM_ID]->treeFlat = array();
					$typeSpecials[$r->TYPE_ID]->filters[$r->PARAM_ID]->ID = $r->PARAM_ID;
					$typeSpecials[$r->TYPE_ID]->filters[$r->PARAM_ID]->ALIAS_ID = $r->ALIAS_ID_RETURN;
					$typeSpecials[$r->TYPE_ID]->filters[$r->PARAM_ID]->PARAM_DESC = $r->PARAM_DESC;
					$typeSpecials[$r->TYPE_ID]->filters[$r->PARAM_ID]->FLTR_ID = $r->ID;
				}

				$param = new stdClass();
				$param->ID = $r->ID;
				$param->PARENT_ID = 0;
				$param->ALIAS_ID = $r->ALIAS_ID;
				$param->ALIAS_PARENT_ID = $r->ALIAS_PARENT_ID;
				$param->ALIAS_TYPE = $r->ALIAS_TYPE;
				$param->TYPE = $r->TYPE;
				$param->DESC = $r->DESC;
				$param->ALIAS_DESC = $r->ALIAS_DESC;
				$typeSpecials[$r->TYPE_ID]->filters[$r->PARAM_ID]->filtersByID[$r->ID] = $param;
			}
		}
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">typeSpecials (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($typeSpecials);echo'</pre>';}

		$typeSpecialsByField = array();// table, db info
		$aliasesMap = array();
		$filtersByField = array();// filters info
		// create sql by params, filters
		/*
			[ID] => 4527
			[ALIAS_ID] => 0
			[TYPE] => DANE
			[DESC] => NORMAL
			[ALIAS_DESC] =>
			[PARAM_ID] => 4526
			[PARAM_DESC] => IN
			[TYPE_ID] => 4519
			[TYPE_TYPE] => DANE
		 */
		foreach ($typeSpecials as $kTypeID => $vType) {
			$typeSpecialsByField[$vType->fieldID] = new Typespecial($vType->fieldID);
			$params = $vType->params_out;
			foreach ($params as $kParamID => $vParams) {
				$vParam = reset($vParams);
				$typeSpecialsByField[$vType->fieldID]->param_out = new stdClass();
				$typeSpecialsByField[$vType->fieldID]->param_out->format = $vParam->PARAM_DESC;
				$typeSpecialsByField[$vType->fieldID]->param_out->retID = $vParam->ALIAS_ID_RETURN;
				$typeSpecialsByField[$vType->fieldID]->param_out->values = array();
				$typeSpecialsByField[$vType->fieldID]->param_out->alias = array();
				$typeSpecialsByField[$vType->fieldID]->param_out->exports = array();
			}
			foreach ($params as $kParamID => $vParams) {
				foreach ($vParams as $vParam) {
					if ($vParam->TYPE == 'PARAM_EXPORT') {
						$typeSpecialsByField[$vType->fieldID]->param_out->exports[$vParam->ALIAS_ID] = $vParam->DESC;
					} else {
						$typeSpecialsByField[$vType->fieldID]->param_out->values[$vParam->DESC] = $vParam->ALIAS_ID;
						$typeSpecialsByField[$vType->fieldID]->param_out->alias[$vParam->ALIAS_ID] = $vParam->ALIAS_DESC;
					}
				}
			}
			foreach ($typeSpecialsByField[$vType->fieldID]->param_out->alias as $kParam => $vParam) {
				$aliasesMap[$kParam] = $vParam;
			}

			$filters = $vType->filters;
			foreach ($filters as $kFilterID => $vFilters) {
				$typeSpecialsByField[$vType->fieldID]->filters[$kFilterID] = $vFilters;
			}
		}

		{// fetch filters recursive
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">typeSpecialsByField update by filtersTodoIds (F.' . __FUNCTION__ . ':' . __LINE__ . '): '."\n";}
			$filtersTodoIds = array();
			foreach ($typeSpecialsByField as $kFldID => $vType) {
				if (!empty($vType->filters)) {
					foreach ($vType->filters as $kFltrID => $vFltr) {
						foreach ($vFltr->filtersByID as $kId => $v) {
							$filtersTodoIds[$kId] = array('type' => $kFldID, 'filter' => $kFltrID);
						}
					}
				}
			}
if(V::get('DBG_TS', 0, $_GET) > 0){echo "filtersTodoIds: [" . implode(",", array_keys($filtersTodoIds)) . "]\n";}

			$deepLimit = 3;
			for ($i = 0; $i < $deepLimit; $i++) {
if(V::get('DBG_TS', 0, $_GET) > 0){echo "loop i({$i}) filtersTodoIds[" . implode(",", array_keys($filtersTodoIds)) . "] \n";}
				if (empty($filtersTodoIds)) {
					break;
				}

				$newFiltersTodoIds = array();
				$sql = "select z.`ID`
						, z.`ALIAS_ID`
						, z.`TYPE`
						, z.`DESC`
						, z.`PARENT_ID`
						, za.`DESC` as ALIAS_DESC
						, za.`TYPE` as ALIAS_TYPE
						, za.`PARENT_ID` as ALIAS_PARENT_ID
					from `CRM_LISTA_ZASOBOW` as z
						left join `CRM_LISTA_ZASOBOW` as za on(za.`ID`=z.`ALIAS_ID`)
					where
						z.`PARENT_ID` in(" . implode(",", array_keys($filtersTodoIds)) . ")
				";
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;display:none">sql (F.' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
				$res = $db->query($sql);
				while ($r = $db->fetch($res)) {
					$fltr = $filtersTodoIds[$r->PARENT_ID];
if(V::get('DBG_TS', 0, $_GET) > 0){echo "\tr: {" . json_encode($r) . "}\n";}
if(V::get('DBG_TS', 0, $_GET) > 0){echo "\t\tfltr: {" . json_encode($fltr) . "}\n";}
					$typeSpecialsByField[$fltr['type']]->filters[$fltr['filter']]->filtersByID[$r->ID] = $r;
					$newFiltersTodoIds[$r->ID] = array('type' => $fltr['type'], 'filter' => $fltr['filter']);

					if ($r->ALIAS_ID > 0) {
						$aliasesMap[$r->ALIAS_ID] = $r->ALIAS_DESC;
					}
				}
				$filtersTodoIds = $newFiltersTodoIds;
			}
if(V::get('DBG_TS', 0, $_GET) > 0){echo '</pre>';}
		}

		foreach ($typeSpecialsByField as $kFieldID => $vType) {
			$vType->filters = Typespecial::convertFilters($vType->filters);
		}
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">typeSpecialsByField (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($typeSpecialsByField);echo'</pre>';}
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">aliasesMap (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($aliasesMap);echo'</pre>';}
		$sqlTables = array();
		if (!isset($_SESSION['Typespecial_Cache']['sqlTablesInfo'])) $_SESSION['Typespecial_Cache']['sqlTablesInfo'] = array();
		$sql = "select z.`ID`
				, z.`PARENT_ID`
				, z.`ALIAS_ID`
				, z.`TYPE`
				, z.`DESC`
				, zp.`ID` as TBL_ID
				, zp.`PARENT_ID` as DB_ID
				, zp.`DESC` as TBL_NAME
		--		, zpp.`ID` as DB_ID
		--		, zpp.`DESC` as DB_DESC
			from `CRM_LISTA_ZASOBOW` as z
				join `CRM_LISTA_ZASOBOW` as zp on(zp.`ID`=z.`PARENT_ID`)
		--		join `CRM_LISTA_ZASOBOW` as zpp on(zpp.`ID`=zp.`PARENT_ID`)
			where zp.`TYPE`='TABELA'
				and z.`TYPE`='KOMORKA'
		--		and zpp.`TYPE` in('DATABASE', 'DATABASE_MYSQL')
				and z.`ID` in(" . implode(",", array_keys($aliasesMap)) . ")
		";
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">sql (C.' . __CLASS__ . ':C.' . __CLASS__ . ':F.' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
			$res = $db->query($sql);
			while ($r = $db->fetch($res)) {
				$_SESSION['Typespecial_Cache']['sqlTablesInfo'][$r->ID] = (object)array('tbl_id'=>$r->TBL_ID, 'db_id'=>$r->DB_ID, 'tbl_name'=>$r->TBL_NAME);
			}
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">sqlTablesInfo (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($_SESSION['Typespecial_Cache']['sqlTablesInfo']);echo'</pre>';}
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left">typeSpecialsByField update by sqlTablesInfo (F.' . __FUNCTION__ . ':' . __LINE__ . '): '."\n";}
		foreach ($typeSpecialsByField as $kFieldID => $vType) {
if(V::get('DBG_TS', 0, $_GET) > 0){			echo "loop: {$kFieldID}\n";}
			$vType->fldAliasList = array();// [tblID][fldID] = fldName
			$vType->tblAliasList = array();
			foreach ($vType->param_out->values as $kName => $vZasobID) {
if(V::get('DBG_TS', 0, $_GET) > 0){				echo "\t value: {$kName}=>{$vZasobID} (" . (array_key_exists($vZasobID, $_SESSION['Typespecial_Cache']['sqlTablesInfo'])) . ") \n";}
				if (array_key_exists($vZasobID, $_SESSION['Typespecial_Cache']['sqlTablesInfo'])) {
					$vTbl = $_SESSION['Typespecial_Cache']['sqlTablesInfo'][$vZasobID];
if(V::get('DBG_TS', 0, $_GET) > 0){					echo "\t\t tbl({$vTbl->tbl_id}:{$vTbl->tbl_name}), db({$vTbl->db_id}) (info: " . '<b style="display:none">' . json_encode($vTbl) . '</b>' . ") \n";}
					$vType->fldAliasList[$vTbl->tbl_id][$vZasobID] = $aliasesMap[$vZasobID];
					$vType->tblAliasList[$vTbl->tbl_id] = $vTbl->tbl_name;
				}
			}
			// TODO: add tblAliasList, fldAliasList by filters (PARAM_WHERE)
			if (!empty($vType->filters)) foreach ($vType->filters as $kFltr => $vFltrs) {
				foreach ($vFltrs->filtersByID as $kFltrId => $vFltr) {
					if ($vFltr->ALIAS_ID > 0) {
						$vZasobID = $vFltr->ALIAS_ID;
						if (array_key_exists($vZasobID, $_SESSION['Typespecial_Cache']['sqlTablesInfo'])) {
							$vTbl = $_SESSION['Typespecial_Cache']['sqlTablesInfo'][$vZasobID];
if(V::get('DBG_TS', 0, $_GET) > 0){					echo "\t\t filters tbl({$vTbl->tbl_id}:{$vTbl->tbl_name}), db({$vTbl->db_id}) (info: " . '<b style="display:none">' . json_encode($vTbl) . '</b>' . ") \n";}
							if (!empty($aliasesMap[$vZasobID])) {
								$vType->fldAliasList[$vTbl->tbl_id][$vZasobID] = $aliasesMap[$vZasobID];
							}
							$vType->tblAliasList[$vTbl->tbl_id] = $vTbl->tbl_name;
						}
					}
				}
			}
		}
if(V::get('DBG_TS', 0, $_GET) > 0){echo'</pre>';}
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:300px;overflow:auto;border:1px solid orange;text-align:left;">typeSpecialsByField-2 (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($typeSpecialsByField);echo'</pre>';}

		foreach ($typeSpecialsByField as $kFieldID => $vType) {
			$vType->buildSqlQuery();
		}
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:300px;overflow:auto;border:1px solid orange;text-align:left;">typeSpecialsByField-3 (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($typeSpecialsByField);echo'</pre>';}

		foreach ($typeSpecialsByField as $kFieldID => $vType) {
			$vType->saveCache();
		}

		return array_keys($typeSpecialsByField);
	}

	public static function convertFilters($filters) {
		if (empty($filters)) {
			return $filters;
		}

		foreach ($filters as $kFltrID => $vFltrRoot) {
			foreach ($vFltrRoot->filtersByID as $vFltr) {
				$vFltrRoot->treeFlat[$vFltr->PARENT_ID][] = $vFltr->ID;
			}
		}
if(V::get('DBG_TS', 0, $_GET) > 0){		echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">filters (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($filters);echo'</pre>';}
		return $filters;
	}

	private function buildSqlWhere($fltrRoot, $fltr) {
		if(V::get('DBG_TS', 0, $_GET) > 0){echo "buildSqlWhere({$fltr->ID}) {" . json_encode($fltr) . "}\n";}
		if (in_array($fltr->TYPE, array('SQL_IN', 'SQL_NOT_IN'))) {
			$sqlValues = array();
			foreach ($fltrRoot->treeFlat[$fltr->ID] as $vFltrID) {
				$vFltr = $fltrRoot->filtersByID[$vFltrID];
				$sqlValues[] = "'{$vFltr->DESC}'";
			}
			if (!empty($sqlValues)) {
				$sqlSign = 'in';
				if ($fltr->TYPE == 'SQL_NOT_IN') {
					$sqlSign = 'not in';
				}
				return "`t_{$fltr->ALIAS_PARENT_ID}`.`{$fltr->ALIAS_DESC}` {$sqlSign}(" . implode(", ", $sqlValues) . ")";
			} else {
				return null;// TODO: add parse error - use in validate
			}
		}
		else if ($fltr->TYPE == 'SQL_EQUAL') {
if(V::get('DBG_TS', 0, $_GET) > 0){print_r($fltrRoot);echo "\n";}
			$sqlValues = array();

			if (count($fltrRoot->treeFlat[$fltr->ID]) == 1) {
if(V::get('DBG_TS', 0, $_GET) > 0){echo "\nTODO: SQL_EQUAL count(" . count($fltrRoot->treeFlat[$fltr->ID]) . ") < 2\n";}

				foreach ($fltrRoot->filtersByID as $vFltr) {
if(V::get('DBG_TS', 0, $_GET) > 0){echo "\nTODO: [" . reset($fltrRoot->treeFlat[0]) . "] vFltr=";print_r($vFltr);}
					if ($vFltr->ALIAS_ID > 0) {
						$sqlValues[] = "`t_{$vFltr->ALIAS_PARENT_ID}`.`{$vFltr->ALIAS_DESC}`";
					} else if (!empty($vFltr->DESC)) {
						$sqlValues[] = "'{$vFltr->DESC}'";
					}
				}
			}
			else {
				foreach ($fltrRoot->treeFlat[$fltr->ID] as $vFltrID) {
					$vFltr = $fltrRoot->filtersByID[$vFltrID];
					// TODO: $vFltr->TYPE == 'PARAM_IN' - use like {fltr_name} and add to special fields in sql to generate by given data
					$sqlValues[] = "`t_{$vFltr->ALIAS_PARENT_ID}`.`{$vFltr->ALIAS_DESC}`";
				}
			}

			if (count($sqlValues) == 2) {
				return "{$sqlValues[0]}={$sqlValues[1]}";
			} else if (count($sqlValues) > 2) {// TODO: multiple '='
if(V::get('DBG_TS', 0, $_GET) > 0){echo "\nTODO: SQL_EQUAL count(sqlValues) > 2\n";}
				return null;
			} else {
if(V::get('DBG_TS', 0, $_GET) > 0){echo "\nTODO: SQL_EQUAL count(sqlValues) < 2\n";}
				return null;// TODO: add parse error - use in validate
			}
		}
		else if ($fltr->TYPE == 'SQL_LIKE') {
			if(V::get('DBG_TS', 0, $_GET) > 0){echo "\nTODO: SQL_LIKE\n";print_r($fltrRoot);echo "\n";}
			$fltrs = array();
			$fltrs[] = $fltr;

			foreach ($fltrRoot->treeFlat[$fltr->ID] as $vFltrID) {
				$vFltr = $fltrRoot->filtersByID[$vFltrID];
				$fltrs[] = $vFltr;
			}
			if (count($fltrs) == 2) {
				if(V::get('DBG_TS', 0, $_GET) > 0){echo "\nTODO: SQL_LIKE count(fltrs) == 1; fltrs = ";print_r($fltrs);echo"\n";}
				$sqlValues = array();
				foreach ($fltrs as $vFltr) {
					if ($vFltr->ALIAS_TYPE == 'KOMORKA') {
						$sqlValues[] = "`t_{$vFltr->ALIAS_PARENT_ID}`.`{$vFltr->ALIAS_DESC}`";
					} else if ($vFltr->ALIAS_TYPE == 'VARIABLE') {
						switch ($vFltr->ALIAS_DESC) {
							case 'ADM_COMPANY':
								$sqlValues[] = "'{$_SESSION['ADM_COMPANY']}'";
								break;
							default:

						}
					} else {
						if(V::get('DBG_TS', 0, $_GET) > 0){echo "\nTODO: SQL_LIKE unknown ALIAS_DESC ($vFltr->ALIAS_DESC) \n";}
					}
				}
				if (count($sqlValues) == 2) {
					$sql = "{$sqlValues[0]} LIKE {$sqlValues[1]}";
					if(V::get('DBG_TS', 0, $_GET) > 0){echo "\nTODO: SQL_LIKE count(sqlValues) == 2; return {$sql}\n";}
					return $sql;
				} else {
					if(V::get('DBG_TS', 0, $_GET) > 0){echo "\nTODO: SQL_LIKE count(sqlValues) != 2\n";}
					return null;// TODO: add parse error - use in validate
				}
			} else {
				if(V::get('DBG_TS', 0, $_GET) > 0){echo "\nTODO: SQL_LIKE count(fltrs) != 2\n";}
				return null;// TODO: add parse error - use in validate
			}
		}
		return null;// TODO: add parse error - use in validate
	}

	/**
	 * Create sql query by given filters
	 *
	 * @requires before $this->convertFilters();
	 * @uses $this->filters
	 * @uses $this->fldAliasList
	 * @uses $this->buildSqlWhere($vFltrRoot, $fltr);
	 */
	public function buildSqlQuery() {
		$this->sqlQuery = new stdClass();
		$this->sqlQuery->select = array();
		$this->sqlQuery->from = array();
		$this->sqlQuery->where = '';
		$this->sqlQuery->orderBy = '';
		$this->sqlQuery->limit = '';
		foreach ($this->fldAliasList as $kTblID => $vFields) {
			$this->sqlQuery->from[] = "`{$this->tblAliasList[$kTblID]}` as t_{$kTblID}";
			foreach ($vFields as $kFldID => $vFldName) {
				$this->sqlQuery->select[] = "`t_{$kTblID}`.`{$vFldName}` as f_{$kFldID}";
			}
		}
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">buildSqlWhere by filters (F.' . __FUNCTION__ . ':' . __LINE__ . '): '."\n";}
		$sql_where_and = array();
		if (!empty($this->filters)) {
			foreach ($this->filters as $kFltrID => $vFltrRoot) {
				foreach ($vFltrRoot->treeFlat[0] as $fltrID) {
					$fltr = $vFltrRoot->filtersByID[$fltrID];//$vFltrRoot->FLTR_ID];
if(V::get('DBG_TS', 0, $_GET) > 0){echo "kFltrID: {$kFltrID} fltrID({$fltr->ID})\n";}
					$sqlPart = $this->buildSqlWhere($vFltrRoot, $fltr);
					if ($sqlPart) {
						$sql_where_and[] = $sqlPart;
					}
				}
			}
		}
if(V::get('DBG_TS', 0, $_GET) > 0){echo'</pre>';}
		$sql_where = (!empty($sql_where_and))? implode("\n and ", $sql_where_and) : "1=1";
		$this->sqlQuery->where = $sql_where;
	}

	public function getFieldName() {
		if (empty($this->_fldName)) {
			$db = DB::getDB();
			$sql = "select z.`DESC` from `CRM_LISTA_ZASOBOW` as z where z.`ID`='{$this->fldID}' ";
			$res = $db->query($sql);
			if ($r = $db->fetch($res)) {
				$this->_fldName = $r->DESC;
			}
		}
		return $this->_fldName;
	}

	/*
	 * TODO: RMME - use Typeahead plugin
	 */
	public function showFormItem__OLD($tblID, $fName, $selValue = '', $params = array(), $record = null) {
		$out = '';
		//if(V::get('DBG_TS', 0, $_GET) > 1){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">showFormItem (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r(array('tblID'=>$tblID,'fName'=>$fName,'selValue'=>$selValue));echo'</pre>';}

		// TODO: set value = $selValue to typespecial search field to load data from ajax?

		$tsValue = V::get('typespecialValue', '', $params);
		if (!empty($selValue) && !empty($tsValue)) {
			$tsValue = "{$selValue}: {$tsValue}";
		}

		$out = '<div class="input-prepend">
				<span class="add-on"><i class="glyphicon glyphicon-search"></i></span>
				<input class="span3" id="typeahead-' . $fName . '" type="search" placeholder="Search..." autocomplete="off" value="' . $tsValue . '">
			</div>
			<script>' . "

jQuery('#typeahead-{$fName}').typeahead({
	source: function (query, process) {
		return $.ajax({
			dataType: 'json',
			url: 'index-ajax.php?_cls=TableAjax&_zasobID={$tblID}&_task=TYPESPECIAL&fldID={$this->fldID}',
			data: 'q=' + query,
			type: 'POST',
			success: function (json) {
				process(json);
			}
		});
	},
	updater: function (item) {
		console.log('selected item: '+item);
		var itemID = item.split(':')[0];
		console.log('selected id: '+itemID);
		jQuery('#{$fName}').val(itemID);
		return item;
	},
	matcher: function(item) {
		var queryArr = [];
		var words = this.query.split(' ');
		$.each(words, function(ind, word) {
			word = $.trim(word);
			if (word != '') {
				queryArr.push(word);
			}
		});

		var found = false;
		$.each(queryArr, function(ind, word) {
			//console.log('item(' + item + ') word(' + word + ') found(' + (item.toLowerCase().indexOf(word.toLowerCase())) + ')');
			if (-1 !== item.toLowerCase().indexOf(word.toLowerCase())) {
				found = true;
			}
		});
		return found;
	},
	sorter: function (items) {
		var bestFound = []
			, oneFound = []
			, notFound = []
			, item;

		var queryArr = [];
		var words = this.query.split(' ');
		$.each(words, function(ind, word) {
			word = $.trim(word);
			if (word != '') {
				queryArr.push(word);
			}
		});

		var found = 0;
		while (item = items.shift()) {
			found = 0;
			$.each(queryArr, function(ind, word) {
				//console.log('item(' + item + ') word(' + word + ') found(' + (item.toLowerCase().indexOf(word.toLowerCase())) + ')');
				if (-1 !== item.toLowerCase().indexOf(word.toLowerCase())) {
					found++;
				}
			});

			if (found > 1) {
				bestFound.push(item);
			} else if (found == 1) {
				oneFound.push(item);
			} else {
				notFound.push(item);
			}
		}
		return bestFound.concat(oneFound, notFound)
	},
	items: 20 // The max number of items to display in the dropdown.
});
			" . '</script>
		';
		return $out;
	}

	public function showFormItem($tblID, $fName, $selValue = '', $params = array(), $record = null) {
		$out = '';

		$jsonAllowCreate = 'true';
		switch ($this->getFieldName()) {
			case 'L_APPOITMENT_USER':
			case 'T_TELBOX_NEIGHBOUR_IN_ID':
			case 'CRM_LISTA_ZASOBOW_ID':
			case 'ID_ZASOB':
			case 'ID_BILLING_USERS':
			case 'ID_PROJECT':
			case 'ID_DEVICE':
				$jsonAllowCreate = 'false';
				break;
			default:
				$jsonAllowCreate = 'true';
		}

		$tsValue = V::get('typespecialValue', '', $params);
		if (!empty($selValue) && !empty($tsValue)) {
			//$tsValue = "{$selValue}: {$tsValue}";
		}

		$optionsJson = 'null';
		$out .= '<div class="typepsecial">';
		$out .= '<select id="ts-' . $fName . '">';
			if (!empty($selValue)) {
				$tsSelectedItemLabel = (!empty($tsValue))? $tsValue : $selValue;
				//$out .= '<option value="' . $selValue . '" selected="selected" typespecial="'.$tsSelectedItemLabel.'">' . $tsSelectedItemLabel . '</option>';
				{// fix prefix
					$prefix = "{$selValue}";
					$prefixLen = strlen($prefix);
					if (strlen($tsSelectedItemLabel) > $prefixLen && $prefix != substr($tsSelectedItemLabel, 0, $prefixLen)) {
						if (' ' == substr($tsSelectedItemLabel, 0, 1)) {
							$tsSelectedItemLabel = "{$prefix}{$tsSelectedItemLabel}";
						} else {
							$tsSelectedItemLabel = "{$prefix}: {$tsSelectedItemLabel}";
						}
					}
				}
				$out .= '<option value="' . $selValue . '" selected="selected">' . $tsSelectedItemLabel . '</option>';
				$optionsJson = new stdClass();
				$optionsJson->id = $selValue;
				$optionsJson->name = $tsSelectedItemLabel;
				$optionsJson = array($optionsJson);
				$optionsJson = json_encode($optionsJson);
			}
		$out .= '</select>';
		$out .= '</div>';

		$ajaxDataUrlBase = "index-ajax.php?_cls=TableAjax&_zasobID={$tblID}&_task=TYPESPECIAL&fldID={$this->fldID}";
		$ajaxDataUrlBase = V::get('ajaxDataUrlBase', $ajaxDataUrlBase, $params);

		$out .= '<script>' . "
(function(){
	var fldNode=jQuery('#{$fName}'), tsNode=jQuery('#ts-{$fName}');
	if (!fldNode && !tsNode) {
		return;
	}

	fldNode.attr('name', '');
	fldNode.hide();
	if (fldNode.parent().hasClass('show-last-value')) {
		fldNode.parent().hide();
	}
	tsNode.attr('name', '{$fName}');
	tsNode.attr('tabindex', fldNode.attr('tabindex'));


	tsNode.selectize({
		theme: 'typespecial',
		valueField: 'id',
		labelField: 'id',
		searchField: 'name',
		// sortField: 'name',
		create: {$jsonAllowCreate},
		delimiter: ';',
		dataAttr: 'typespecial',
//		preload: true,
		options: {$optionsJson},
		preload: true,
		render: {
			item: function(item, escape) {
				//console.log('item', item);
				//var name = formatName(item);
				return '<div>' +
					'<span class=\"name\">' + escape(item.name || item.id) + '</span>' +
				'</div>';
			},
			option: function(item, escape) {
//console.log('--- render.option item(',item,') this:',this);
				return '<div>' +
					'<span class=\"title\">' +
						escape(item.name || item.id) +
					'</span>' +
				'</div>';
			}
		},
		onItemAdd: function(value, item) {// Invoked when an item is selected.
			var curSel = this.options[value] || null, fieldExport;
//console.log('--- onItemAdd val(',value,'/',item.data('name'),'/',item,') this:curSel:',curSel);
			if (curSel && curSel.exports) {
				for (var i in curSel.exports) {
					fieldExport = jQuery('#ts-f' + i);
					if (fieldExport.length) {
//console.log('--- onItemAdd fieldExport.selectize(',fieldExport.selectize,')', fieldExport);
						if (fieldExport.get(0).selectize) {
							fieldExport.get(0).selectize.addOption({id:curSel.exports[i], name:curSel.exports[i]});
							fieldExport.get(0).selectize.setValue(curSel.exports[i]);
						} else {
							//jQuery('#f' + i).val(curSel.exports[i]);
						}
					} else {
//console.log('--- onItemAdd jQuery(#f' + i + ').val(', curSel.exports[i], '): fieldExport.is(input:',fieldExport.is('input'),'/select:',fieldExport.is('select'),')', fieldExport);
						fieldExport = jQuery('#f' + i);
						if (fieldExport.is('input')) {
							jQuery('#f' + i).val(curSel.exports[i]);
						} else if (fieldExport.is('select')) {
							//TODO: add option and select //jQuery('#f' + i).val(curSel.exports[i]);
						}
					}
				}
			}
		},
		score: function(query) {
			// console.log('Typespecial({$fName})::score: q(', query, ')');
			var score = this.getScoreFunction(query);
			return function(item) {
				var retScore = 0, lName = ('' + item.name).toLowerCase(), lQuery = query.toLowerCase();
				if (query.search(' ') > -1) {
					retScore = score(item);
				}
				else {
					if (lQuery + ':' == lName.substr(0, lQuery.length + 1)) {
						retScore = 100;
					} else if (lName.search(lQuery) > -1) {
						retScore = 90 - lName.search(lQuery);
					} else {
						retScore = 0;
					}
				}
				// console.log('Typespecial({$fName})::score: q(', lQuery, ') , retScore(', retScore, '), score(', score(item), '), item.name(', item.name, ') item.id:', item.id);
				return retScore;
			};
		},
		load: function(query, callback) {
			//if (!query.length) return callback();
			$.ajax({
				url: '{$ajaxDataUrlBase}',
				data: 'q=' + encodeURIComponent(query),
				type: 'POST',
				error: function() {
					callback();
				},
				success: function(res) {
					var i, prefix, prefixLen;
					// res.sort(function(a, b) {// TODO: use \$order from reqeust
					// 	if (!a['\$order'])
					// })
					for (i in res) {
						prefix = '' + res[i].id;
						prefixLen = prefix.length;
						if (prefixLen > 0 && prefix !== res[i].name.substr(0, prefixLen)) {
							res[i].name = res[i].id + ': ' + res[i].name;
						}
						res[i]['\$order'] = 1 + (parseInt(i) || 0);// set order from request
					}
					// console.log('Typespecial({$fName})::res',res);
					callback(res);
				}
			});
		}
	});

})()
		" . '</script>';

		return $out;
	}

	public function _getValues($query) {
		$rows = array();
		$db = DB::getDB();// TODO: get from DB by zasoby ID
		DBG::log(['msg'=>"session", $_SESSION['Typespecial_Cache']['sqlTablesInfo']]);
		DBG::log(['msg'=>"this", $this]);
		$query = trim($query);
		$sqlWhereAdd = "";
		if (!empty($query)) {
			Lib::loadClass('SqlQueryWhereBuilder');
			$queryWhereBuilder = new SqlQueryWhereBuilder();
			$qArr = $queryWhereBuilder->splitQueryToWords($query);
			DBG::log(['msg'=>"qArr", $qArr]);
			if (!empty($qArr)) {
				$sqlWhereAnd = array();
				foreach ($qArr as $q) {
					$sqlSearchWord = DB::getPDO()->quote(
						(is_numeric($q)) ? "{$q}%" : "%{$q}%",
						PDO::PARAM_STR
					);
					$sqlWhereOr = array();
					foreach ($this->param_out->alias as $fldID => $fldName) {
						if (false === strpos($this->param_out->format, "{{$fldName}}")) continue;
						$tblInfo = V::get($fldID, null, $_SESSION['Typespecial_Cache']['sqlTablesInfo']);
						if ($tblInfo) {
							$sqlWhereOr[] = "`t_{$tblInfo->tbl_id}`.`{$fldName}` like {$sqlSearchWord}";
						}
					}
					if (!empty($sqlWhereOr)) {
						$sqlWhereAnd[] = "(" . implode(" or ", $sqlWhereOr) . ")";
					}
				}
				if (!empty($sqlWhereAnd)) {
					$sqlWhereAdd = " and (" . implode(" and ", $sqlWhereAnd) . ")";
				}
			}
		}
		DBG::log(['msg'=>"this", $this]);
		Lib::loadClass('ACL');
		Lib::loadClass('AclQueryFeatures');
		try {
			$andSql = array_map(
				function ($tableName, $idTable) {
					$tableAcl = User::getAcl()->getObjectAcl('default_db', $tableName);
					return ACL::query($tableAcl, "t_{$idTable}")
					->where( (new AclQueryFeatures($tableAcl, $params = []))->parseSpecialFilterAccess() )
					->generateWhereSql()
					;
				},
				$this->tblAliasList, array_keys($this->tblAliasList)
			);
			$andSql = array_filter($andSql, ['V', 'filterNotEmpty']);
			if (!empty($andSql)) $sqlWhereAdd .= "\n" . " and " . implode(" and ", $andSql);
		} catch (Exception $e) {
			DBG::log($e);
			return [];
		}
		$sqlLimit = V::get('limit', 0, $this->sqlQuery, 'int');
		$sqlLimit = ($sqlLimit > 0) ? $sqlLimit : 20;

		$histRows = array();
		{
			if (empty($query)) {
				if (V::get('TEST_SORT', '', $_GET)) echo '{' . "\n";
				if (V::get('TEST_SORT', '', $_GET)) echo '"this": ' . json_encode($this) . ",";
				if (!empty($this->fldID) && is_numeric($this->fldID)) {
					$fldName = $this->param_out->alias[ $this->param_out->retID ];
					$zasobCellInfo = DB::getPDO()->fetchFirst("
						select z.`DESC` as fldName, t.`DESC` as tblName
						from CRM_LISTA_ZASOBOW z
							join CRM_LISTA_ZASOBOW t on(t.ID = z.PARENT_ID)
						where z.ID = '{$this->fldID}'
							and z.`TYPE` = 'KOMORKA'
							and t.`TYPE` = 'TABELA'
					");
					if (!empty($zasobCellInfo)) {
						$fldName = $zasobCellInfo['fldName'];
						$tblName = $zasobCellInfo['tblName'];
						if (V::get('TEST_SORT', '', $_GET)) echo '"field_name": "'.$fldName.'",';
						if (V::get('TEST_SORT', '', $_GET)) echo '"table_name": "'.$tblName.'",';
						$userLogin = V::get('TEST_SORT_LOGIN', User::getLogin(), $_REQUEST);
						try {
							$histRows = DB::getPDO()->fetchAll("
								select h.`{$fldName}`
										, count(1) as cnt
								from `{$tblName}_HIST` h
								where h.A_RECORD_UPDATE_AUTHOR = '{$userLogin}'
									and h.`{$fldName}` != 'N/S;'
									and h.`{$fldName}` != ''
									and h.`{$fldName}` != '0'
									and h.`{$fldName}` IS NOT NULL
									and h.`{$fldName}` != 'NULL'
								group by h.`{$fldName}`
								order by cnt desc
								limit 10
							");
						} catch (Exception $e) {
							if (V::get('TEST_SORT', '', $_GET)) echo '"Exception": ' . json_encode($e) . ',';
						}
						if (V::get('TEST_SORT', '', $_GET)) echo '"hist_rows_stats": ' . json_encode($histRows) . ',';
					}
				}
			}
		}
		$rawRows = DB::getPDO()->fetchAll("
			select " . implode("\n\t\t,", $this->sqlQuery->select) . "
			from " . implode(", ", $this->sqlQuery->from) . "
			where {$this->sqlQuery->where}
				{$sqlWhereAdd}
			limit {$sqlLimit}
		");
		foreach ($rawRows as $r) {
			$retID = V::get("f_{$this->param_out->retID}", '', $r);
			$rows[$retID] = (object)$r;
		}
		if (!empty($histRows)) {
			$sordedRows = array();
			$orderIndex = 1000;
			foreach ($histRows as $histRow) {
				$retValue = $histRow[$fldName];
				if (array_key_exists($retValue, $rows)) {
					$sordedRows[$retValue] = $rows[$retValue];
					$sordedRows[$retValue]->{'$order'} = $orderIndex--;
					unset($rows[$retValue]);
				} else {
					try {
						$sordedRows[$retValue] = (object)DB::getPDO()->fetchFirst("
							select " . implode("\n\t\t,", $this->sqlQuery->select) . "
							from " . implode(", ", $this->sqlQuery->from) . "
							where {$this->sqlQuery->where}
								{$sqlWhereAdd}
							having `f_{$this->param_out->retID}` = '{$retValue}'
						");
						if (V::get('TEST_SORT', '', $_GET)) echo '"test_fetch_'.$retValue.'":' . json_encode($sordedRows[$retValue]) . ",";
						if (!empty($sordedRows[$retValue])) {
							$sordedRows[$retValue]->{'$order'} = $orderIndex--;
						}
					} catch (Exception $e) {
						DBG::log($e);
					}
				}
			}
			foreach ($rows as $key => $val) {
				$sordedRows[$key] = $val;
			}
			$rows = $sordedRows;
		}
		if (V::get('TEST_SORT', '', $_GET)) echo '"rows":' . json_encode($rows) . ",";
		if (V::get('TEST_SORT', '', $_GET)) die('"end":"end"' . "\n" . '}');
		DBG::log(['msg'=>"rows", $rows]);
		return $rows;
	}

	public function getValues($query) {
		$rows = $this->_getValues($query);
		DBG::log(['msg'=>"param_out", $this->param_out]);
		{// convert param_out
			$format = $this->param_out->format;
			foreach ($this->param_out->values as $kFldName => $vFldID) {
				$format = str_replace("{{$kFldName}}", "{f_{$vFldID}}", $format);
			}
			DBG::log(['msg'=>"format", $format]);
		}

		$rows_out = array();
		foreach ($rows as $ind => $r) {
			$row_out = $format;
			foreach ($r as $kFldName => $vVal) {
				$row_out = str_replace("{{$kFldName}}", "{$vVal}", $row_out);
			}
			$rows_out[$ind] = $row_out;
		}
		DBG::log(['msg'=>"rows_out", $rows_out]);
		return $rows_out;
	}

	public function getValuesWithExports($query) {
		$rows = $this->_getValues($query);

		DBG::log(['msg'=>"param_out", $this->param_out]);
		{// convert param_out
			$format = $this->param_out->format;
			foreach ($this->param_out->values as $kFldName => $vFldID) {
				$format = str_replace("{{$kFldName}}", "{f_{$vFldID}}", $format);
			}
			DBG::log(['msg'=>"format", $format]);
		}

		$retObjList = array();
		foreach ($rows as $ind => $r) {
			$vObj = new stdClass();
			$vObj->id = $ind;
			$vObj->param_out = $format;
			foreach ($r as $kFldName => $vVal) {
				$vObj->param_out = str_replace("{{$kFldName}}", "{$vVal}", $vObj->param_out);
			}

			if (!empty($this->param_out->exports)) {
				$vObj->exports = array();
				foreach ($this->param_out->exports as $kExportID => $vExportOut) {
					$vObj->exports[$kExportID] = new stdClass();
					foreach ($this->param_out->values as $kFldName => $vFldID) {
						$vExportOut = str_replace("{{$kFldName}}", "{f_{$vFldID}}", $vExportOut);
					}
					foreach ($r as $kFldName => $vVal) {
						$vExportOut = str_replace("{{$kFldName}}", "{$vVal}", $vExportOut);
					}
					$vObj->exports[$kExportID] = $vExportOut;
				}
			}
			if (!empty($r->{'$order'})) {
				$vObj->{'$order'} = $r->{'$order'};
			}
			$retObjList[$ind] = $vObj;
		}
		DBG::log(['msg'=>"retObjList", $retObjList]);
		return $retObjList;
	}

	public function getEditSelectedValuesByIds($tblId, $rowId, $fieldName, $fieldValue = '') {
		$rows = array();
		$db = DB::getDB();// TODO: get from DB by zasoby ID
if(V::get('DBG_TS', 0, $_GET) > 1){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">args (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r(array('tblId'=>$tblId, 'rowId'=>$rowId, 'fieldName'=>$fieldName, 'fieldValue'=>$fieldValue));echo'</pre>';}// TODO: RMME
if(V::get('DBG_TS', 0, $_GET) > 1){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">sqlTablesInfo (tblId:'.$tblId.') (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($_SESSION['Typespecial_Cache']['sqlTablesInfo']);echo'</pre>';}// TODO: RMME
if(V::get('DBG_TS', 0, $_GET) > 1){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">this->tblAliasList (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($this->tblAliasList);echo'</pre>';}// TODO: RMME
if(V::get('DBG_TS', 0, $_GET) > 1){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">this (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($this);echo'</pre>';}// TODO: RMME

		$retFldName = '';
		$sqlWhereAdd = array();
		$tblFound = null;
		foreach ($_SESSION['Typespecial_Cache']['sqlTablesInfo'] as $vTblInfo) {
			if ($vTblInfo->tbl_id == $tblId) {
				$tblFound = $vTblInfo;
				break;
			}
		}
		if (!$tblFound) {
			$db = DB::getDB();
			$sql = "select z.`ID` as tbl_id
					, z.`PARENT_ID` as db_id
					, z.`DESC` as tbl_name
				from `CRM_LISTA_ZASOBOW` as z
				where z.`ID`={$tblId}
			";
			$res = $db->query($sql);
			if ($r = $db->fetch($res)) {
				$tblFound = $r;
				if(V::get('DBG_TS', 0, $_GET) > 1){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">Found 2 tblFound ('.$tblId.') (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($tblFound);echo'</pre>';}// TODO: RMME
			}
		}
		if (!$tblFound) {
			if(V::get('DBG_TS', 0, $_GET) > 1){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">No tblFound! ('.$tblId.') (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($_SESSION['Typespecial_Cache']['sqlTablesInfo']);echo'</pre>';}// TODO: RMME
			return false;
		}
//echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">$vTblInfo (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($vTblInfo);echo'</pre>';

		$sqlFrom = $this->sqlQuery->from;
		$sqlSelect = $this->sqlQuery->select;
		if ($this->param_out->retID > 0) {
			$sqlWhereAdd[] = "`tr_{$tblId}`.`ID`={$rowId}";
			if (array_key_exists($this->param_out->retID, $_SESSION['Typespecial_Cache']['sqlTablesInfo'])) {
				$retTblId = $_SESSION['Typespecial_Cache']['sqlTablesInfo'][$this->param_out->retID]->tbl_id;
				$retName = $this->param_out->alias[$this->param_out->retID];
				$sqlWhereAdd[] = "`t_{$retTblId}`.`{$retName}`=`tr_{$tblId}`.`{$fieldName}`";
			} else {
				return false;
			}
			$sqlSelect[] = "`tr_{$tblId}`.`ID`";

			$sqlFrom[] = "`{$tblFound->tbl_name}` as tr_{$tblFound->tbl_id}";

			$retFldName = "f_{$this->param_out->retID}";
			$foundInSelect = false;
			foreach ($sqlSelect as $vSelectStr) {
				if (false !== strpos($vSelectStr, $retFldName)) {
					$foundInSelect = true;
				}
			}
			if (!$foundInSelect) $retFldName = '';
		}
		else {// if no retID then TYPESPECIAL is link
			$sqlWhereAdd[] = "`t_{$tblId}`.`ID`={$rowId}";
			$sqlSelect[] = "`t_{$tblId}`.`ID`";
		}
		$sqlWhereAdd = (!empty($sqlWhereAdd))? " and " . implode(" and ", $sqlWhereAdd) : "";

		$sqlLimit = V::get('limit', 0, $this->sqlQuery, 'int');
		if ($sqlLimit <= 0) {
			$sqlLimit = 20;
		}
		$sqlLimit = "limit {$sqlLimit}";
		$sqlFrom = implode(", ", $sqlFrom);
		$sql = "select " . implode("\n\t\t,", $sqlSelect) . "
			from {$sqlFrom}
			where {$this->sqlQuery->where}
				{$sqlWhereAdd}
			{$sqlLimit}
		";
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$rows[] = $r;
		}
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">rows (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($rows);echo'</pre>';}

if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">param_out (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($this->param_out);echo'</pre>';}
		{// convert param_out
			$format = $this->param_out->format;
			foreach ($this->param_out->values as $kFldName => $vFldID) {
				$format = str_replace("{{$kFldName}}", "{f_{$vFldID}}", $format);
			}
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">format (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($format);echo'</pre>';}
		}

if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">retFldName:'.$retFldName.' (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($rows);echo'</pre>';}
		$rowsOut = array();
		foreach ($rows as $r) {
			$rowOut = $format;
			foreach ($r as $kFldName => $vVal) {
				$rowOut = str_replace("{{$kFldName}}", "{$vVal}", $rowOut);
			}
			if ($retFldName) {
				$rowsOut[$r->{$retFldName}] = $rowOut;
			} else {
				$rowsOut[] = $rowOut;
			}
		}
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">rowsOut (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($rowsOut);echo'</pre>';}
		return $rowsOut;
	}

	public function getValuesByIds($tblId, $ids) {
		$rows = array();
		$db = DB::getDB();// TODO: get from DB by zasoby ID
if(V::get('DBG_TS', 0, $_GET) > 1){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">sqlTablesInfo (tblId:'.$tblId.') (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($_SESSION['Typespecial_Cache']['sqlTablesInfo']);echo'</pre>';}// TODO: RMME
if(V::get('DBG_TS', 0, $_GET) > 1){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">this->tblAliasList (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($this->tblAliasList);echo'</pre>';}// TODO: RMME
if(V::get('DBG_TS', 0, $_GET) > 1){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">this (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($this);echo'</pre>';}// TODO: RMME

		$sqlWhereAdd = array();
		$tblFound = null;
		foreach ($_SESSION['Typespecial_Cache']['sqlTablesInfo'] as $vTblInfo) {
			if ($vTblInfo->tbl_id == $tblId) {
				$tblFound = $vTblInfo;
				break;
			}
		}
		if (!$tblFound) {
			return false;
		}
		$sqlWhereAdd[] = "`t_{$tblId}`.`ID` in(" . implode(",", $ids) . ")";
		$sqlWhereAdd = (!empty($sqlWhereAdd))? " and " . implode(" and ", $sqlWhereAdd) : "";

		$sqlLimit = V::get('limit', 0, $this->sqlQuery, 'int');
		if ($sqlLimit <= 0) {
			$sqlLimit = 20;
		}
		$sqlLimit = "limit {$sqlLimit}";
		$sql = "select " . implode("\n\t\t,", $this->sqlQuery->select) . "
				, `t_{$tblId}`.`ID`
			from " . implode(", ", $this->sqlQuery->from) . "
			where {$this->sqlQuery->where}
				{$sqlWhereAdd}
			{$sqlLimit}
		";
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$rows[$r->ID][] = $r;
		}
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">rows (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($rows);echo'</pre>';}

if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">param_out (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($this->param_out);echo'</pre>';}
		{// convert param_out
			$format = $this->param_out->format;
			foreach ($this->param_out->values as $kFldName => $vFldID) {
				$format = str_replace("{{$kFldName}}", "{f_{$vFldID}}", $format);
			}
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">format (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($format);echo'</pre>';}
		}

		$rowsOut = array();
		foreach ($rows as $id => $vRows) {
			foreach ($vRows as $r) {
				$rowOut = $format;
				foreach ($r as $kFldName => $vVal) {
					$rowOut = str_replace("{{$kFldName}}", "{$vVal}", $rowOut);
				}
				$rowsOut[$id][] = $rowOut;
			}
		}
if(V::get('DBG_TS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">rowsOut (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($rowsOut);echo'</pre>';}
		return $rowsOut;
	}

	public function getTblAliasList() {
		return $this->tblAliasList;
	}

	public static function getCache() {
		if (!array_key_exists('Typespecial_Cache', $_SESSION)) {
			$_SESSION['Typespecial_Cache'] = array();
		}
		return $_SESSION['Typespecial_Cache'];
	}

	private function saveCache() {
		if(V::get('DBG_TS', 0, $_GET) > 3){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">Typespecial_Cache (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($_SESSION['Typespecial_Cache']);echo'</pre>';}
		$typeSpecialID = $_SESSION['Typespecial_Cache']['map'][$this->fldID];
		$_SESSION['Typespecial_Cache'][$typeSpecialID] = array('param_out'=>$this->param_out, 'sqlQuery'=>$this->sqlQuery, 'tblAliasList'=>$this->tblAliasList);
	}

	public static function getInstance($fieldID, $colName) {
		DBG::log("Typespecial::getInstance({$fieldID}, {$colName})");
		if (!empty($colName)) {
			Lib::loadClass('Config');
			$LIB_CLASS_NAME_PREFIX = Config::get('LIB_CLASS_NAME_PREFIX');
			if (!empty($LIB_CLASS_NAME_PREFIX)) {
				$clsName = "{$LIB_CLASS_NAME_PREFIX}_TypespecialVariable";
				if (Lib::tryLoadClass($clsName)) {
					//$typeSpecial = $clsName::getInstance($fieldID, $colName);
					$typeSpecial = call_user_func(array($clsName, 'getInstance'), $fieldID, $colName);
					if ($typeSpecial) {
						return $typeSpecial;
					}
				}
			}

			Lib::loadClass('TypespecialVariable');
			$typeSpecial = TypespecialVariable::getInstance($fieldID, $colName);
			DBG::log($typeSpecial, 'array', "TypespecialVariable::getInstance({$fieldID}, {$colName})");
			if ($typeSpecial) {
				return $typeSpecial;
			}
		}

		$cache = self::getCache();
		$map = V::get('map', '', $cache);
		if (!empty($map) && array_key_exists($fieldID, $map)) {
			$typespecialID = $map[$fieldID];
			if (array_key_exists($typespecialID, $cache)) {
				$typeSpecial = new Typespecial($fieldID, $colName, $cache[$typespecialID]);
				if ($typeSpecial->validate()) {
					return $typeSpecial;
				}
			}
		}
		return null;
	}

	public function getReturnId() {
		if (!isset($this->param_out)) return null;
		if (!isset($this->param_out->retID)) return null;
		return $this->param_out->retID;
	}

	public function getReturnData($tblId, $rowId, $fieldName) {
		if (!isset($this->param_out)) return null;
		if (!isset($this->param_out->retID)) return null;
		if ($this->param_out->retID <= 0) return null;

		$retData = new stdClass();
		$retData->tbl_id = 0;
		$retData->fld_name = null;
		$retData->tbl_name = null;
		$retData->tbl_label = null;

		$spValues = $this->getEditSelectedValuesByIds($tblId, $rowId, $fieldName, '');
		if(V::get('DBG_TS', 0, $_GET) > 1){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">getEditSelectedValuesByIds (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($spValues);echo'</pre>';}
		if (empty($spValues)) return null;
		$itemsJson = array();
		foreach ($spValues as $kID => $val) {
			$itemJson = new stdClass();
			$itemJson->id = $kID;
			$itemJson->label = $val;
			$itemsJson[] = $itemJson;
		}
		if (empty($itemsJson)) return null;
		$retData->items = $itemsJson;

		$db = DB::getDB();
		$sql = "select z.`ID`
				, z.`DESC` as fld_name
				, tbl.`ID` as tbl_id
				, tbl.`DESC` as tbl_name
				, tbl.`DESC_PL` as tbl_label
			from `CRM_LISTA_ZASOBOW` as z
				join `CRM_LISTA_ZASOBOW` tbl on (tbl.`ID`=z.`PARENT_ID`)
			where
				z.`ID`='{$this->param_out->retID}'
				and z.`TYPE`='KOMORKA'
				and tbl.`TYPE`='TABELA'
		";
		if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
		$res = $db->query($sql);
		if ($r = $db->fetch($res)) {
			$retData->fld_name = $r->fld_name;
			$retData->tbl_id = $r->tbl_id;
			$retData->tbl_name = $r->tbl_name;
			$retData->tbl_label = $r->tbl_label;
		}
		if(V::get('DBG_TS', 0, $_GET) > 1){echo'<pre style="max-height:200px;overflow:auto;border:1px solid orange;text-align:left;">retData (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($retData);echo'</pre>';}

		if ($retData->tbl_id > 0) {
			return $retData;
		}
		return null;
	}

}