se/SE/se-lib/Data_Source.php

<?php

Lib::loadClass('SqlQueryWhereBuilder');
Lib::loadClass('ParseOgcFilter');
Lib::loadClass('DBG');

/**
 * API:
 *   get($field_name) - source field value
 *   get_item($id) - get item by ID
 */
class Data_Source {

	var $_db;
	var $_tbl;// sql table name, TODO: or zasob ID
	var $_default_sql_limit;
	var $_fields_type;// array of fields ( name => type )
	var $_fields_perm;// array of field perm ( name => perm )
	var $_cols = array();
	var $_vCols = array();// setVirtualCols - TODO: use Typespecial
	var $_col_types = array();// TODO: array( col_name => TYPE )
	var $_sql_where;// sql where
	var $_sql_limit;
	var $_sql_offset;
	var $_sql_left_join;// TODO: left join in table
	var $_isAccessFltrAllowed = null;
	private $_fieldOwner = null;
	private $_fieldGroupWrite = null;
	private $_fieldGroupRead = null;
	private $_showMsgsSpecialFilter = false;
	var $_primaryKey = null; // TODO: set after getFieldTypes to field name - HOW TO handle tables with non unique keys (group by in fetch data in TableAjax)

	function __construct($db = null) {
		$this->_idDatabase = ($db) ? $db : null;
		// if ($db) { // TODO: RM use DB::getPDO($this->_idDatabase)
		// 	$this->_db = DB::getDB($db); // TODO: RM
		// } else {
		// 	$this->_db = DB::getDB(); // TODO: RM
		// }
		$this->_default_sql_limit = 10;
		$this->_showMsgsSpecialFilter = true;// TODO: allow by acl, procesy?
	}

	function getDB() {
		return ($this->_idDatabase) ? DB::getDB($this->_idDatabase) : DB::getDB();
	}

	function set_table($tbl) {// TODO: RMME
		$this->setTable($tbl);
	}

	public function setTable($tbl) {
		$this->_tbl = $tbl;
		$this->_fields_type = array();
		$this->_fields_perm = array();
	}

	function get_cols() {
		if (empty($this->_cols)) {
			if (!$this->_tbl) return null;
			foreach (DB::getPDO($this->_idDatabase)->fetchAll(" show fields from `{$this->_tbl}` ") as $row) {
				$fieldName = $row['Field'];
				$this->_cols[ $fieldName ] = $fieldName;
				$this->_col_types[ $fieldName ] = "{$row['Type']};{$row['Default']}";
			}
		}
		return $this->_cols;
	}

	public function getFieldTypes() {
		$fieldTypes = array();

		$storage = DB::getStorage($this->_idDatabase);
		$tableStruct = $storage->getTableStruct($this->_tbl);
		DBG::log($tableStruct, 'array', "DBG:TEST:\$tableStruct");
		$fieldTypes = array_map(function ($field) {
			return [
				'type' => $field['raw_storage_type'],
				'null' => $field['is_nullable'] ? true : false,
				'default' => $field['default'],
			];
		}, $tableStruct);

		// TODO: find unique/primary keys in $tableStruct

		// $struct = DB::getPDO($this->_idDatabase)->fetchAll(" show fields from `{$this->_tbl}` ");
		// DBG::log($struct, 'array', "DBG:\$struct by `show fields from`");
		// foreach ($struct as $row) {
		// 	$fieldName = $row['Field'];
		// 	$fieldType = $row['Type'];
		// 	$fieldTypes[ $fieldName ] = [
		// 		'type' => $fieldType,
		// 		'null' => ('YES' == $row['Null']),
		// 		'default' => $row['Default']
		// 	];
		// }
		DBG::log($fieldTypes, 'array', "types({$this->_tbl})");
		return $fieldTypes;
	}

	public function getUniqueKeys() {
		$sqlKeys = array();
		//$dbID = $this->getDB();
		$db = $this->getDB();
		$tblName = $this->_tbl;//(TableAcl) $this->getName();
		if (!$db) {
			throw new Exception('DataSource is not defined');
		}
		$sql = "SHOW KEYS FROM  `{$tblName}`";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			if ($r->Non_unique == '0') {
				$sqlKeys[$r->Column_name] = true;
			}
		}
		$sqlKeys = array_keys($sqlKeys);
		return $sqlKeys;
	}

	function set_cols($cols) {// TODO: RMME
		$this->setCols($cols);
	}

	function setCols($cols) {
		foreach ($cols as $v_field_name) {
			$this->_cols[$v_field_name] = $v_field_name;
		}
	}

	public function setColTypes($types) {
		$this->_col_types = $types;
		// TableAcl->getTypes():  $this->_types[$fieldName] = array('type'=>$h[1], 'null'=>('YES' == $h[2]), 'default'=>$h[4]);
	}

	public function getColDefault($fieldName) {
		$fldType = V::get($fieldName, '', $this->_col_types);
		if (!$fldType) return '';
		return V::get('default', '', $fldType);
	}

	function setVirtualCols($cols) {
		foreach ($cols as $v_field_name) {
			$this->_vCols[$v_field_name] = $v_field_name;
		}
	}

	public function setFieldGroupWrite($fieldName, $fieldExists = false) {
		if ($fieldExists) {
			$this->_fieldGroupWrite = $fieldName;
			$this->_cols[$fieldName] = $fieldName;
		}
	}

	public function setFieldGroupRead($fieldName, $fieldExists = false) {
		if ($fieldExists) {
			$this->_fieldGroupRead = $fieldName;
			$this->_cols[$fieldName] = $fieldName;
		}
	}

	public function setFieldOwner($fieldName, $fieldExists = false) {
		if ($fieldExists) {
			$this->_fieldOwner = $fieldName;
			$this->_cols[$fieldName] = $fieldName;
		}
	}

	public function getFieldGroupWrite() {
		return $this->_fieldGroupWrite;
	}

	public function getFieldGroupRead() {
		return $this->_fieldGroupRead;
	}

	public function getFieldOwner() {
		return $this->_fieldOwner;
	}

	function _getSqlCols($colsList = null) {
		$sqlCols = "t.*";
		if (!empty($this->_cols)) {
			$sqlColsList = array();
			if (null === $colsList || empty($colsList)) {
				$colsList = array_keys($this->_cols);
			}
			foreach ($colsList as $fieldName) {
				if (!array_key_exists($fieldName, $this->_cols)) throw new Exception("Field not exists '{$fieldName}'!");

				if ($this->isGeomField($fieldName)) {
					$sqlFld = "AsWKT(t.`{$fieldName}`) as {$fieldName}";
				} else {
					$sqlFld = "t.`{$fieldName}`";
				}
				$sqlColsList[] = $sqlFld;
			}
			$sqlCols = implode(", ", $sqlColsList);
		}
		return $sqlCols;
	}

	public function setAccessFltrAllowed($isAccessFltrAllowed) {
		$this->_isAccessFltrAllowed = $isAccessFltrAllowed;
	}

	public function isAccessFltrAllowed() {
		if (false === $this->_isAccessFltrAllowed) {
			return false;
		}
		else if ($this->hasAclGroupFields()) {
			return true;
		}
		return false;
	}

	public function hasAclGroupFields() {
		if ( !empty($this->_fieldGroupWrite)
			&& !empty($this->_fieldGroupRead)
			&& array_key_exists('A_ADM_COMPANY', $this->_cols)
			&& array_key_exists('A_CLASSIFIED', $this->_cols)
		) {
			return true;
		}
		return false;
	}

	function getSpecialFilters() {
		$fltrs = array();

		if (array_key_exists('A_STATUS', $this->_cols)) {
			$fltrs['Trash'] = (object)[
				'icon' => 'glyphicon glyphicon-trash',
				'btns' => [
					'Kosz' => (object)[ 'value' => 'TRASH' ],
					'Ukryj' => (object)[ 'value' => 'HIDE' ],
				],
			];
		}
		if ($this->_showMsgsSpecialFilter) {
			$fltrs['Msgs'] = (object)[
				'icon' => 'glyphicon glyphicon-envelope',
				'label' => 'Wiadomości',
				'btns' => [
					'Wiadomości' => (object)[ 'value' => 'HAS_MSGS' ],
					'Nowe' => (object)[ 'value' => 'NEW_MSGS' ],
					'Brak' => (object)[ 'value' => 'NO_MSGS' ],
				],
			];
		}
		if (array_key_exists('A_PROBLEM', $this->_cols)) {
			$fltrs['Problemy'] = (object)[
				'icon' => 'glyphicon glyphicon-warning-sign',
				'btns' => [
					'Problemy' => (object)[ 'value' => 'PROBLEM' ],
					'Ostrzeżenia' => (object)[ 'value' => 'WARNING' ],
					'Brak' => (object)[ 'value' => 'NORMAL' ],
				],
			];
		}
		if (array_key_exists('A_STATUS', $this->_cols)) {
			$fltrs['Status'] = (object)[
				'icon' => 'glyphicon glyphicon-question-sign',
				'btns' => [
					'Oczekujący' => (object)[ 'value' => 'WAITING' ],
					'Aktywni' => (object)[ 'value' => 'AKTYWNI' ],
				],
			];
		}
		if (array_key_exists('L_APPOITMENT_DATE', $this->_cols)) {
			$fltrs['Przypomnienia'] = (object)[
				'icon' => 'glyphicon glyphicon-calendar',
				'btns' => [
					'Stare' => (object)[ 'value' => 'OLD' ],
					'Dzisiaj' => (object)[ 'value' => 'TODAY' ],
					'Zaraz' => (object)[ 'value' => 'NOW' ],
					'Brak' => (object)[ 'value' => 'BRAK' ],
				],
			];
		}
		if ($this->isAccessFltrAllowed()) {
			$fltrs['Access'] = (object)[
				'icon' => 'glyphicon glyphicon-lock',
				'btns' => [
					'Pokaż' => (object)[ 'value' => 'SHOW' ],
				],
			];
		}
		return $fltrs;
	}

	public function _parseOgcFilter($ogcFilter) {
		$parser = new ParseOgcFilter();
		$parser->loadOgcFilter($ogcFilter);
		$queryWhereBuilder = $parser->convertToSqlQueryWhereBuilder();
		return $queryWhereBuilder->getQueryWhere('t');
	}

	function _parseSpecialFilter($fltr, $value) {
		$sqlFltr = "";

		switch ($fltr) {
			case 'Msgs':
				if ($this->_showMsgsSpecialFilter) {
					$sqHasFltrMsgs = "
						select 1
						from `CRM_UI_MSGS` m
						where m.`uiTargetName`=CONCAT('{$this->_tbl}.', t.`ID`)
							and m.`uiTargetType`='default_db_table_record'
							and m.`A_STATUS` not in('DELETED')
						limit 1
					";
					switch ($value) {
						case 'NEW_MSGS':
							$sqNewFltrMsgs = "
								select 1
								from `CRM_UI_MSGS` m
								where m.`uiTargetName`=CONCAT('{$this->_tbl}.', t.`ID`)
									and m.`uiTargetType`='default_db_table_record'
									and m.`A_STATUS` in('WAITING')
								limit 1
							";
							$sqlFltr = " ({$sqNewFltrMsgs})=1 ";
							break;
						case 'HAS_MSGS':
							$sqlFltr = " ({$sqHasFltrMsgs})=1 ";
							break;
						case 'NO_MSGS':
							$sqlFltr = " ({$sqHasFltrMsgs}) is null ";
							break;
					}
				}
				break;
			case 'Problemy':
				if (array_key_exists('A_PROBLEM', $this->_cols)) {
					switch ($value) {
						case 'PROBLEM':
							$sqlFltr = " t.`A_PROBLEM`!='' ";
							break;
						case 'WARNING':
							$sqlFltr = " t.`A_PROBLEM`='WARNING' ";
							break;
						case 'NORMAL':
							$sqlFltr = " t.`A_PROBLEM`='' ";
							break;
					}
				}
				break;
			case 'Status':
				if (array_key_exists('A_STATUS', $this->_cols)) {
					switch ($value) {
						case 'WAITING':
							$sqlFltr = " t.`A_STATUS`='WAITING' ";
							break;
						case 'AKTYWNI':
							$sqlFltr = " t.`A_STATUS` in('NORMAL', 'WARNING') ";
			// TODO: $_SESSION['USERS_FILTER_STATUS_SQL']="and (( $thiss->DETECT_TABLE_NAME.A_STATUS='NORMAL' or $thiss->DETECT_TABLE_NAME.A_STATUS='WARNING'  ) or ( $thiss->DETECT_TABLE_NAME.A_STATUS='OFF_SOFT' and $thiss->DETECT_TABLE_NAME.A_PROBLEM_DESC not like '%odla%fizy%' and $thiss->DETECT_TABLE_NAME.A_PROBLEM!='' ) or ( $thiss->DETECT_TABLE_NAME.A_STATUS='OFF_SOFT' and $thiss->DETECT_TABLE_NAME.A_PROBLEM='' )) ";
			// if ($thiss->DETECT_TABLE_NAME == 'KSIEG_DOKUMENTY') $_SESSION['USERS_FILTER_STATUS_SQL']="and ( $thiss->DETECT_TABLE_NAME.A_STATUS='NORMAL' or $thiss->DETECT_TABLE_NAME.A_STATUS='WARNING'  )  ";
							break;
					}
				}
				break;
			case 'Trash':
				if (array_key_exists('A_STATUS', $this->_cols)) {
					switch ($value) {
						case 'TRASH':
							$sqlFltr = " t.`A_STATUS` = 'DELETED' ";
							break;
						case 'HIDE':
							$sqlFltr = " t.`A_STATUS` != 'DELETED' ";
							break;
					}
				}
				break;
			case 'Przypomnienia':
				if (array_key_exists('L_APPOITMENT_DATE', $this->_cols)) {
					switch ($value) {
						case 'OLD':
							$sqlFltr = "
								COALESCE(UNIX_TIMESTAMP(t.`L_APPOITMENT_DATE`), 0) < UNIX_TIMESTAMP(now())
								and t.`L_APPOITMENT_DATE` != ''
								and t.`L_APPOITMENT_DATE` != '0000-00-00 00:00:00'
							";
							break;
						case 'NOW':
							$sqlFltr = "
								COALESCE(UNIX_TIMESTAMP(t.`L_APPOITMENT_DATE`), 0) < UNIX_TIMESTAMP(now())+3600
								and COALESCE(UNIX_TIMESTAMP(t.`L_APPOITMENT_DATE`), 0) > UNIX_TIMESTAMP(now())-3600
							";
							break;
						case 'TODAY':
							$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}'
							";
							break;
						case 'TOMORROW':
							$start = mktime(0,0,0, date("m"), date("d") + 1, date("Y"));
							$end = mktime(0,0,0, date("m"), date("d") + 2, date("Y"));
							$sqlFltr = "
								COALESCE(UNIX_TIMESTAMP(t.`L_APPOITMENT_DATE`), 0) > '{$start}'
								and COALESCE(UNIX_TIMESTAMP(t.`L_APPOITMENT_DATE`), 0) < '{$end}'
							";
							break;
						case 'YESTERDAY':
							$start = mktime(0,0,0, date("m"), date("d") - 1, date("Y"));
							$end = mktime(0,0,0, date("m"), date("d") - 2, date("Y"));
							$sqlFltr = "
								COALESCE(UNIX_TIMESTAMP(t.`L_APPOITMENT_DATE`), 0) > '{$start}'
								and COALESCE(UNIX_TIMESTAMP(t.`L_APPOITMENT_DATE`), 0) < '{$end}'
							";
							break;
						case 'BRAK':
							$start = mktime(0,0,0, date("m"), date("d") - 1, date("Y"));
							$end = mktime(0,0,0, date("m"), date("d") - 2, date("Y"));
							$sqlFltr = "
								(t.`L_APPOITMENT_DATE` = ''
								or t.`L_APPOITMENT_DATE` = '0000-00-00 00:00:00')
							";
							break;
					}
				}
				break;
			case 'Access':
				if ('SHOW' != $value && $this->isAccessFltrAllowed()) {
					$userLogin = User::getLogin();
					$usrAclGroups = User::getLdapGroupsNames();
					$usrAclGroups[] = '';// TODO: allow empty for everyone?
					$sqlUsrAclGroups = "'" . implode("','", $usrAclGroups) . "'";
					$sqlFltr = "
						(	t.`{$this->_fieldGroupWrite}` in({$sqlUsrAclGroups})
							or t.`{$this->_fieldGroupRead}` in({$sqlUsrAclGroups})
						)
					";
					if (array_key_exists('L_APPOITMENT_USER', $this->_cols)) {
						$sqlFltr = "
							(
								({$sqlFltr})
								or t.`L_APPOITMENT_USER`='{$userLogin}'
							)
						";
					}
				}
				break;
			default:
		}

/*
			//USERS_FILTER_PROBLEM
			// 	PROBLEMY			https://biuro.biall-net.pl/SE/se-dev/index.php?FUNCTION_INIT=USERS_FILTER_PROBLEM&USERS_FILTER_PROBLEM=PROBLEM
			//	OSTRZEZENIA		https://biuro.biall-net.pl/SE/se-dev/index.php?FUNCTION_INIT=USERS_FILTER_PROBLEM&USERS_FILTER_PROBLEM=WARNING
			//	BEZ_PROBLEM.	https://biuro.biall-net.pl/SE/se-dev/index.php?FUNCTION_INIT=USERS_FILTER_PROBLEM&USERS_FILTER_PROBLEM=NORMAL
			//	KASUJ-FILTR		https://biuro.biall-net.pl/SE/se-dev/index.php?FUNCTION_INIT=USERS_FILTER_PROBLEM&USERS_FILTER_PROBLEM=

			//USERS_FILTER_STATUS
			//	OCZEKUJACY	https://biuro.biall-net.pl/SE/se-dev/index.php?FUNCTION_INIT=USERS_FILTER_STATUS&USERS_FILTER_STATUS=WAITING
			//	AKTYWNI			https://biuro.biall-net.pl/SE/se-dev/index.php?FUNCTION_INIT=USERS_FILTER_STATUS&USERS_FILTER_STATUS=AKTYWNI
			//	KASUJ-FILTR	https://biuro.biall-net.pl/SE/se-dev/index.php?FUNCTION_INIT=USERS_FILTER_STATUS&USERS_FILTER_STATUS=

			//USERS_FILTER_APPOINTMENT
			//	A_STARE		https://biuro.biall-net.pl/SE/se-dev/index.php?FUNCTION_INIT=USERS_FILTER_APPOINTMENT&USERS_FILTER_APPOINTMENT=OLD
			//	A_ZARAZ		https://biuro.biall-net.pl/SE/se-dev/index.php?FUNCTION_INIT=USERS_FILTER_APPOINTMENT&USERS_FILTER_APPOINTMENT=NOW
			//	A_DZISIAJ	https://biuro.biall-net.pl/SE/se-dev/index.php?FUNCTION_INIT=USERS_FILTER_APPOINTMENT&USERS_FILTER_APPOINTMENT=TODAY
			//	APP_ALL		https://biuro.biall-net.pl/SE/se-dev/index.php?FUNCTION_INIT=USERS_FILTER_APPOINTMENT&USERS_FILTER_APPOINTMENT=

			// btn class: active, disabled
			{// USERS_FILTER_PROBLEM
				var groupHtml = $('<div class="btn-group"></div>');
				$('<button class="btn btn-xs" title="Problemy"><i class="glyphicon glyphicon-warning-sign"></i></button>').appendTo(groupHtml);
				$('<button class="btn btn-xs">PROBLEMY</button>').appendTo(groupHtml);
				$('<button class="btn btn-xs">OSTRZEZENIA</button>').appendTo(groupHtml);
				$('<button class="btn btn-xs active">BEZ_PROBLEM.</button>').appendTo(groupHtml);
				$('<button class="btn btn-xs disabled" title="Kasuj filtr"><i class="glyphicon glyphicon-remove"></i></button>').appendTo(groupHtml);
				groupHtml.appendTo(_headSpecialFilter);
			}
			{// USERS_FILTER_STATUS
				var groupHtml = $('<div class="btn-group"></div>');
				$('<button class="btn btn-xs" title="Status"><i class="glyphicon glyphicon-question-sign"></i></button>').appendTo(groupHtml);
				$('<button class="btn btn-xs">OCZEKUJACY</button>').appendTo(groupHtml);
				$('<button class="btn btn-xs">AKTYWNI</button>').appendTo(groupHtml);
				$('<button class="btn btn-xs disabled" title="Kasuj filtr"><i class="glyphicon glyphicon-remove"></i></button>').appendTo(groupHtml);
				groupHtml.appendTo(_headSpecialFilter);
			}
			{// USERS_FILTER_APPOINTMENT
				var groupHtml = $('<div class="btn-group"></div>');
				$('<button class="btn btn-xs" title="Spotkania"><i class="glyphicon glyphicon-calendar"></i></button>').appendTo(groupHtml);
				$('<button class="btn btn-xs">A_STARE</button>').appendTo(groupHtml);
				$('<button class="btn btn-xs">A_ZARAZ</button>').appendTo(groupHtml);
				$('<button class="btn btn-xs active">A_DZISIAJ</button>').appendTo(groupHtml);
				$('<button class="btn btn-xs disabled" title="Kasuj filtr"><i class="glyphicon glyphicon-remove"></i></button>').appendTo(groupHtml);// To samo co KASUJ-FILTR
				groupHtml.appendTo(_headSpecialFilter);
			}
 */

		return $sqlFltr;
	}

	private function isColTypeNumber($colName) {
		if ($colName == 'ID') {
			return true;
		}
		$type = V::get($colName, null, $this->_col_types);
		if(V::get('DBG_DS', 0, $_GET) > 0){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">type['.$colName.'] (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r(array('type'=>$type, '!empty'=>(!empty($type['type']['type']))));echo'</pre>'."\n\n";}
		if (!empty($type['type'])) {
			$sqlType = $type['type'];
			if (substr($sqlType, 0, 3) == 'int'
					|| substr($sqlType, 0, 7) == 'tinyint'
					|| substr($sqlType, 0, 8) == 'smallint'
			) {
				return true;
			}
		}
		return false;
	}

	public function _parseSqlWhere($params = array()) {

		if (empty($params['sf_Access'])) $params['sf_Access'] = 'HIDE'; // default filter value

		$sql_where = '';
		// ... parse filters
		$sql_where_and = array();
		foreach ($params as $k => $v) {
			if (strlen($k) > 3 && substr($k, 0, 2) == 'f_') {
				//$v = trim($v, '% ');
				//$sql_where_and[] = "t.`" . substr($k, 2) . "` like '%" . DB::_($v) . "%'";
				$fldName = substr($k, 2);
				if ($this->isGeomField($fldName)) {
					$sqlFilter = $this->_sqlValueForGeomField($fldName, $v, 't');
					if ($sqlFilter) $sql_where_and[] = $sqlFilter;
					continue;
				}
				if ($this->isCsvNumericField($fldName)) {
					$sqlFilter = $this->_sqlValueForCsvNumericField($fldName, $v, 't');
					if ($sqlFilter) $sql_where_and[] = $sqlFilter;
					continue;
				}

				if (substr($v, 0, 1) == '=') {
					$v = $this->getDB()->_(substr($v, 1));
					if (strlen($v)) $sql_where_and[] = "t.`{$fldName}`='{$v}'";
				}
				else if ($v == '!NULL' || $v == 'IS NOT NULL') {
					$sql_where_and[] = "t.`{$fldName}` is not null";
				}
				else if (substr($v, 0, 1) == '!') {
					$v = $this->getDB()->_(substr($v, 1));
					if (strlen($v)) $sql_where_and[] = "t.`{$fldName}` not like '{$v}'";
				}
				else if (substr($v, 0, 2) == '<=') {
					$v = $this->getDB()->_(substr($v, 2));
					if (strlen($v)) $sql_where_and[] = "t.`{$fldName}`<='{$v}'";
				}
				else if (substr($v, 0, 2) == '>=') {
					$v = $this->getDB()->_(substr($v, 2));
					if (strlen($v)) $sql_where_and[] = "t.`{$fldName}`>='{$v}'";
				}
				else if (substr($v, 0, 1) == '<') {
					$v = $this->getDB()->_(substr($v, 1));
					if (strlen($v)) $sql_where_and[] = "t.`{$fldName}`<'{$v}'";
				}
				else if (substr($v, 0, 1) == '>') {
					$v = $this->getDB()->_(substr($v, 1));
					if (strlen($v)) $sql_where_and[] = "t.`{$fldName}`>'{$v}'";
				}
				else if (false !== strpos($v, '%')) {
					$sql_where_and[] = "t.`{$fldName}` like '{$v}'";
				}
				else if ($this->isColTypeNumber($fldName)) {
					$v = $this->getDB()->_($v);
					$sql_where_and[] = "t.`{$fldName}`='{$v}'";
				}
				else {
					$queryWhereBuilder = new SqlQueryWhereBuilder();
					$searchWords = $queryWhereBuilder->splitQueryToWords($v);
					$sqlWhereWords = array();
					if (!empty($searchWords)) {
						foreach ($searchWords as $word) {
							$sqlWord = $this->getDB()->_($word);
							$sqlWhereWords[] = "t.`{$fldName}` like '%{$sqlWord}%'";
						}
					}
					if (!empty($searchWords)) {
						$sql_where_and[] = "(" . implode(" and ", $sqlWhereWords) . ")";
					}
				}
			}
			else if (strlen($k) > 4 && substr($k, 0, 3) == 'sf_') {
				$sqlFltr = $this->_parseSpecialFilter(substr($k, 3), $v);
				if (!empty($sqlFltr)) {
					$sql_where_and[] = $sqlFltr;
				}
			}
			else if ('ogc:Filter' == $k) {
				$sqlFltr = $this->_parseOgcFilter($v);
				if (!empty($sqlFltr)) {
					$sql_where_and[] = $sqlFltr;
				}
			}
			else if ('primaryKey' == $k) {
				if (!empty($v)) {
					$primaryKeyField = $this->getPrimaryKeyField();
					$sql_where_and[] = "t.`{$primaryKeyField}` = '" . $this->getDB()->_($v) . "'";
				}
			}
		}
		if (!empty($sql_where_and)) {
			$sql_where = implode(" and ", $sql_where_and);
		}

		if (!$sql_where) $sql_where = "1=1";
		if(V::get('DBG_DS', 0, $_GET) > 1){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql_where (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql_where);echo"\n".'params: ';print_r($params);echo'</pre>'."\n\n";}
		return $sql_where;
	}

	private function _sqlValueForGeomField($fldName, $fltrValue, $tblPrefix = 't') {
		$sqlFilter = false;
		// example: BBOX:54.40993961633866,18.583889010112824,54.337945760687454,18.397121431987586
		if ('BBOX:' == substr($fltrValue, 0, 5)) {
			$val = substr($fltrValue, 5);
			$valParts = explode(',', $val);
			if (count($valParts) == 4) {
				$isAllNumeric = true;
				foreach ($valParts as $v) {
					if (!is_numeric($v)) $isAllNumeric = false;
				}
				if ($isAllNumeric) {
					$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
					$sqlFilter = "Intersects(GeomFromText('{$bounds}'), {$tblPrefix}.`{$fldName}`)=1";
				}
			}
		}
		else if ('GeometryType=' == substr($fltrValue, 0, 13)) {
			$sqlFilter = "GeometryType({$tblPrefix}.`{$fldName}`)='" . substr($fltrValue, 13) . "'";
		}
		return $sqlFilter;
	}

	private 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;
	}

	function get_item($id) {// TODO: RMME
		$this->getItem($id);
	}

	/**
	 * @returns object
	 */
	public function getItem($primaryKey, $params = []) {
		$primaryKeyField = $this->getPrimaryKeyField();
		$sql_cols = $this->_getSqlCols();
		$item = DB::getPDO()->fetchFirst("
			select {$sql_cols}
			from `{$this->_tbl}` as t
			where t.`{$primaryKeyField}` = :pk
		", [
			':pk' => $primaryKey,
		]);
		return ($item) ? (object)$item : null;
	}

	function get_items($params = array()) {// TODO: RMME
		$this->getItems($params);
	}

	public function getItems($params = array()) {
		$primaryKeyField = $this->getPrimaryKeyField();
		DBG::log("Data_Source::getItems - \$primaryKeyField({$primaryKeyField})");
		$items = array();
		$sql = new stdClass();
		$sql->_cols = V::get('cols', null, $params);
		if (!is_array($sql->_cols) || empty($sql->_cols)) {
			$sql->_cols = null;
		}
		$sql->limit = V::get('limit', $this->_default_sql_limit, $params, 'int');
		$sql->offset = V::get('limitstart', 0, $params, 'int');
		$sql->orderBy = '';
		$sql->_orderBy = V::get('order_by', '', $params);
		$sql->_sortBy = V::get('sortBy', '', $params);
		if ($sql->_sortBy) {// ID A,COL_X D,COL_Y A,...
			$sql->_sortByList = array();
			$sortByEx = explode(',', $sql->_sortBy);
			foreach ($sortByEx as $sortPart) {
				$sortPart = trim($sortPart);
				if (empty($sortPart)) continue;
				$sortPartEx = explode(' ', $sortPart);
				if (count($sortPartEx) > 2) throw new Exception("SortBy parse error #" . __LINE__);
				$sortColName = trim($sortPartEx[0]);
				if (!array_key_exists($sortColName, $this->_cols)) throw new Exception("SortBy parse error - no column name '{$sortColName}' #" . __LINE__);
				$colSortDir = 'ASC';
				if (count($sortPartEx) == 2) {
					if ('A' == $sortPartEx[1] || 'ASC' == $sortPartEx[1]) {
					} else if ('D' == $sortPartEx[1] || 'DESC' == $sortPartEx[1]) {
						$colSortDir = 'DESC';
					} else throw new Exception("SortBy parse error - unknown sort order '{$sortPartEx[1]}' #" . __LINE__);
				}
				$sql->_sortByList[] = "t.`{$sortColName}` {$colSortDir}";
			}
			if (!empty($sql->_sortByList)) {
				$sql->orderBy = "order by " . implode(", ", $sql->_sortByList);
			}
		} else {
			if ($sql->_orderBy) {
				$sql->_orderDir = V::get('order_dir', '', $params);

				// prevent from sorting by special columns
				if (!array_key_exists($sql->_orderBy, $this->_cols)) {
					$sql->_orderBy = null;
					$sql->_orderDir = null;
				}
			}
			if ($sql->_orderBy) {
				$sql->orderBy = "order by t.`{$sql->_orderBy}`";
				if ($sql->_orderDir) {
					$sql->orderBy = "{$sql->orderBy} {$sql->_orderDir}";
				}
			}
		}
		$sql->where = $this->_parseSqlWhere($params);
		$sql->cols = $this->_getSqlCols($sql->_cols);
		$sql->query = "select {$sql->cols}
			from `{$this->_tbl}` t
			where {$sql->where}
			{$sql->orderBy}
			limit {$sql->limit} offset {$sql->offset}
		";
		DBG::log([ 'msg'=>"Data_Source::getItems - \$sql", '$sql'=>$sql ]);
		$res = $this->getDB()->query($sql->query);
		while ($r = $this->getDB()->fetch($res)) {
			$items[$r->{$primaryKeyField}] = $r;
		}
		return $items;
	}

	function get_hist_items($id) {// TODO: RMME
		$this->getHistItems($id);
	}

	public function getHistItems($id, $params = array()) {
		$ret = array();
		DBG::log($params, 'array', "getHistItems({$id}, \$params)");
		$sql_tbl = $this->_tbl . "_HIST";
		$sql_cols = $this->_getSqlCols();
		$sql_where = "t.`ID_USERS2`='{$id}'";

		$idHist = V::get('ID', 0, $params, 'int');
		if ($idHist > 0) {
			$sql_where .= "\n and t.`ID`='{$idHist}'";
		}
		$paramNotEmptyFlds = V::get('notEmptyFlds', '', $params);
		if (!empty($paramNotEmptyFlds) && is_array($paramNotEmptyFlds)) {
			$sqlWhereOr = array();
			foreach ($paramNotEmptyFlds as $fldName) {
				if (array_key_exists($fldName, $this->_cols)) {
					$sqlWhereOr[] = "t.`{$fldName}`!='N/S;'";
				}
			}
			if (!empty($sqlWhereOr)) $sql_where .= "\n and (" . implode(" or ", $sqlWhereOr) . ")";
		}

		$sql = "select {$sql_cols}
			from {$sql_tbl} as t
			where {$sql_where}
			order by ID DESC
		";
		return array_map(function ($histRow) {
			$histItem = (object)$histRow;
			$histItem->_author = $histItem->A_RECORD_UPDATE_AUTHOR;
			$histItem->_created = $histItem->A_RECORD_UPDATE_DATE;

			if (!$histItem->_author || $histItem->_author == 'N/S;') {
				$histItem->_author = $histItem->A_RECORD_CREATE_AUTHOR;
			}
			if (!$histItem->_created || $histItem->_created == 'N/S;') {
				$histItem->_created = $histItem->A_RECORD_CREATE_DATE;
			}
			return $histItem;
		}, DB::getPDO()->fetchAllByKey($sql, $key = 'ID'));
	}

	function get_total($params = array()) {// TODO: RMME
		$this->getTotal($params);
	}

	public function getTotal($params = array()) {
		$ret = 0;
		$sql_where = $this->_parseSqlWhere($params);
		$sql = "select count(1) as cnt
			from {$this->_tbl} as t
			where {$sql_where}
		";
		$res = $this->getDB()->query($sql);
		if ($r = $this->getDB()->fetch($res)) {
			$ret = $r->cnt;
		}
		return $ret;
	}

	function set_sql_where($sql_where) {
		$this->_sql_where = $sql_where;
	}

	function set_field_sql_type($field, $sql_type) {
		$this->_fields_type[$field] = $sql_type;
	}

	function get_field_sql_type($field) {
		if (array_key_exists($field, $this->_fields_type)) {
			return $this->_fields_type[$field];
		}
		return 'varchar(255)';
	}

	function set_field_perm($field, $perm) {
		$this->_fields_perm[$field] = $perm;
	}

	function get_field_perm($field) {
		if (array_key_exists($field, $this->_fields_perm)) {
			return $this->_fields_perm[$field];
		}
		return '';
	}

	function field_allow_write($field_name) {
		return (strpos($this->get_field_perm($field_name), 'W') !== false)? true : false;
	}

	function field_allow_read($field_name) {
		return (strpos($this->get_field_perm($field_name), 'R') !== false)? true : false;
	}

	function field_allow_create($field_name) {
		return (strpos($this->get_field_perm($field_name), 'C') !== false)? true : false;
	}

	public function add_col($col_name, $label = '', $type = 'string') {
		if (!$label) $label = $col_name;
		$this->_cols[$col_name] = $label;
		$this->_col_types[$col_name] = $type;
	}

	public function addCol($col_name) {
		$this->_cols[$col_name] = $col_name;
	}

	function count() {
		$ret = 0;
		$sql_where = ($this->_sql_where)? $this->_sql_where : "1=1";
		$sql = "select count(1) as cnt
			from `{$this->_tbl}`
			where {$sql_where}
		";
		$res = $this->getDB()->query($sql);
		if ($r = $this->getDB()->fetch($res)) {
			$ret = $r->cnt;
		}
		return $ret;
	}

	function fetch_list($limit = 10, $offset = 0) {
		$primaryKeyField = $this->getPrimaryKeyField();
		$ret = array();
		$this->_sql_limit = $limit;
		$this->_sql_offset = $offset;
		$sql_cols = (!empty($this->_cols))? implode(',', array_keys($this->_cols)) : "*";
		$sql_where = ($this->_sql_where)? $this->_sql_where : "1=1";
		$sql = "
			select {$sql_cols}
			from {$this->_tbl}
			where {$sql_where}
			limit {$this->_sql_limit} offset {$this->_sql_offset}
		";
		$res = $this->getDB()->query($sql);
		while ($r = $this->getDB()->fetch($res)) {
			$ret[$r->{$primaryKeyField}] = $r;
		}
		return $ret;
	}

	function field_check_value($field_name, $val) {
		if (!$this->field_allow_write($field_name)) {
			return false;
		}
		// post verify
		// get type, and check if value is correct
		// TODO: if typespecial use it
		return true;
	}

	function save_item(&$item, $values, $prefix) {
		if (!$item->ID) {
			return null;
		}
		$sql_obj = new stdClass();
		$sql_obj->ID = $item->ID;
		foreach ($values as $k_field_with_prefix => $v_field) {
			if (substr($k_field_with_prefix, 0, strlen($prefix)) != $prefix) {
				continue;
			}
			$k_field = substr($k_field_with_prefix, strlen($prefix));
			if ($this->field_allow_write($k_field)) {
				if ($this->field_check_value($k_field, $v_field)) {
					$sql_obj->$k_field = $v_field;
				}
			}
		}
		$affected = $this->getDB()->PDATE_OBJ($this->_tbl, $sql_obj);
		return $affected;
	}

	function add_item($values, $prefix) {
		$sql_obj = new stdClass();
		foreach ($values as $k_field_with_prefix => $v_field) {
			if (substr($k_field_with_prefix, 0, strlen($prefix)) != $prefix) {
				continue;
			}
			$k_field = substr($k_field_with_prefix, strlen($prefix));
			if ($this->field_allow_create($k_field)) {
				if ($this->field_check_value($k_field, $v_field)) {
					$sql_obj->$k_field = $v_field;
				}
			}
		}
		$insert_id = $this->getDB()->ADD_NEW_OBJ($this->_tbl, $sql_obj);
		return $insert_id;
	}

	public function isGeomField($fldName) {
		return ('the_geom' == $fldName);
	}

	public function isCsvNumericField($fldName) {
		return ('_CSV_NUM' == substr($fldName, -8));
	}

	public function updateItem($itemPatch) {
		// TODO: $item as array and copy from $db->UPDATE_OBJ using PDO
		if (is_object($itemPatch)) {
			$itemPatch = (array)$itemPatch;
		} else if (!is_array($itemPatch)) {
			throw new HttpException('Item patch is not array', 400);
		}
		if (empty($itemPatch)) {
			//throw new Exception('Item patch is empty');
			return 0;// nothing to change
		}

		$primaryKeyField = $this->getPrimaryKeyField();
		if (empty($itemPatch[$primaryKeyField])) {
			throw new HttpException("Item Primary Key not set!", 400);
		}

		// the geom fix
		foreach ($itemPatch as $fld => $val) {
			if ($this->isGeomField($fld)) {
				if (!empty($val)
						&& 'NULL' !== $val
						&& 'GeomFromText' != substr($val, 0, strlen('GeomFromText'))
					 ) {
					$itemPatch[$fld] = "GeomFromText('{$val}')";
				}
			}
		}

		$itemPatch = (object)$itemPatch;
		$affected = $this->getDB()->UPDATE_OBJ($this->_tbl, $itemPatch);
		if ($affected < 0) {
			$dsErrors = $this->getDbErrors();
			//$dsErrors = "Wystąpiły błędy!\n" . implode("\n", $dsErrors);
			if (!empty($dsErrors)) {
				throw new StorageException($dsErrors);
			}
		}
		return $affected;
	}

	public function addItem($item) {
		// TODO: $item as array and copy from $db->ADD_NEW_OBJ using PDO
		if (is_object($item)) {
			$item = (array)$item;
		} else if (!is_array($item)) {
			throw new HttpException('Item is not array', 400);
		}
		// add empty record is allowed - empty($item)

		// the geom fix
		foreach ($item as $fld => $val) {
			if ($this->isGeomField($fld)) {
				if (!empty($val)
						&& 'NULL' !== $val
						&& 'GeomFromText' != substr($val, 0, strlen('GeomFromText'))
					 ) {
					$item[$fld] = "GeomFromText('{$val}')";
				}
			}
		}

		$primaryKey = $this->getDB()->ADD_NEW_OBJ($this->_tbl, (object)$item);
		if ($primaryKey <= 0) {
			$dsErrors = $this->getDbErrors();
			$dsErrors = "Wystąpiły błędy!\n" . implode("\n", $dsErrors);
			throw new Exception($dsErrors);
		}
		return $primaryKey;
	}

	public function getDbErrors() {
		$errors = array();
		if ($this->getDB()->has_errors()) {
			$errorsSql = $this->getDB()->get_errors();
			foreach ($errorsSql as $vErr) {
				if ('SQL QUERY FAILED: ' == substr($vErr, 0, 18)) {
					$vErr = substr($vErr, 18);
				}
				//$errors[] = StorageException::parseMessage($vErr);
				$errors[] = $vErr;
			}
		}
		return $errors;
	}

	public function getPrimaryKeyField() {
		return $this->_primaryKey ? $this->_primaryKey : 'ID';// TODO: read from struct - jest funkcja w Mysql.php my Psql.php
	}

}