se/SE/se-lib/Schema/UserObjectStorageAcl.php

<?php

Lib::loadClass('Core_AclSimpleSchemaBase');
Lib::loadClass('ParseOgcFilter');
Lib::loadClass('UsersHelper');

/**
 * @require CRM_PROCES_idx_TABLE_TO_PROCES_GROUPED_VIEW from FixCrmProcesInitIdx
 * @require idUser (ref from parent object - User)
 */
class Schema_UserObjectStorageAcl extends Core_AclSimpleSchemaBase {

	public $_simpleSchema = [
		'root' => [
			'@namespace' => 'default_objects/UserObject',
			'@primaryKey' => 'ID_TABLE',
			// 'ID' => [ '@type' => 'xsd:string', '@aliasSqlConcat' => ['{table}.ID_TABLE', '-', '{table}.ID_USER', '-', '{table}.ID_PROCES'] ],
			'ID_TABLE' => [ '@type' => 'xsd:integer' ], // `ID_TABLE` int(11) NOT NULL,
			'ID_USER' => [ '@type' => 'xsd:integer' ], // `ID_USER` int(11) NOT NULL,
			'ID_PROCES' => [ '@type' => 'xsd:integer' ], // `ID_PROCES` int(11) DEFAULT NULL,
			'db' => [ '@type' => 'xsd:integer' ], // `db` int(11) NOT NULL,
			'name' => [ '@type' => 'xsd:string' ], // `name` varchar(255) DEFAULT '',
			'label' => [ '@type' => 'xsd:string' ], // `label` varchar(255) DEFAULT '',
			'opis' => [ '@type' => 'xsd:string' ], // `opis` text,
			'_rootTableName' => [ '@type' => 'xsd:string' ], // `ROOT_TABLE_NAME` varchar(255) DEFAULT '',
			'A_LAST_SYNC' => [ '@type' => 'xsd:dateTime' ], // `A_LAST_SYNC` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			// TODO: UNIQUE KEY `uniq` (ID_USER, ID_URL, ID_PROCES)
		]
	];
	public $_rootTableName = 'CRM_AUTH_#CACHE_ACL_OBJECT';
	public $idUser = null;
	public $idProcesFilter = null;

	public function __construct($simpleSchema = null) {
		parent::__construct($simpleSchema);
		$this->idUser = User::getID();// default - current user
	}

	public function setIdUser($idUser) {
		$this->idUser = intval($idUser);
		if ($this->idUser <= 0) throw new Exception("Missing id user");
	}
	public function getIdUser() { return $this->idUser; }
	public function setIdProcesFilter($idProcesFilter) { $this->idProcesFilter = intval($idProcesFilter); }
	public function getIdProcesFilter() { return $this->idProcesFilter; }
	public function updateCacheFeatures() {
		$dbName = DB::getPDO()->getDatabaseName();
		$idDatabase = DB::getPDO()->getZasobId();
		$sqlIdProces = ($this->idProcesFilter > 0) ? $this->idProcesFilter : 'NULL';
		$sqlWhereAndIdProces = ($this->idProcesFilter > 0) ? "and c.ID_PROCES = {$this->idProcesFilter}" : "and c.ID_PROCES is NULL";
		$sqlNoPrefixWhereAndIdProces = ($this->idProcesFilter > 0) ? "and ID_PROCES = {$this->idProcesFilter}" : "and ID_PROCES is NULL";
		$sqlIdProcesListSql = $this->getUsedUserProcesIdsSql();
		DB::getPDO()->execSql("
			create table if not exists `{$this->_rootTableName}` (
				`ID_USER` int(11) NOT NULL,
				`ID_TABLE` int(11) NOT NULL,
				`ID_PROCES` int(11) DEFAULT NULL,
				`db` int(11) NOT NULL,
				`name` varchar(255) DEFAULT '',
				`label` varchar(255) DEFAULT '',
				`opis` text,
				`ROOT_TABLE_NAME` varchar(255) DEFAULT '',
				`A_LAST_SYNC` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
				UNIQUE KEY `uniq` (ID_USER, ID_TABLE, ID_PROCES)
			) ENGINE=MyISAM	DEFAULT CHARSET=latin2
		");
		DB::getPDO()->execSql(" delete from `{$this->_rootTableName}` where ID_USER = {$this->idUser} {$sqlNoPrefixWhereAndIdProces} ");
		if ($sqlIdProcesListSql) {
			DB::getPDO()->execSql("
				insert into `{$this->_rootTableName}` (ID_USER, A_LAST_SYNC, ID_PROCES, ID_TABLE, db, name, label, opis)
				select {$this->idUser}
					, NOW()
					, {$sqlIdProces}
					, tpvg.`ID_TABLE`
					, tpvg.`ID_DATABASE` as `db`
					, tpvg.`TABLE_NAME` as `name`
					, tpvg.`TABLE_LABEL` as `label`
					, tpvg.`TABLE_DESCRIPTION` as `opis`
				from `CRM_PROCES_idx_TABLE_TO_PROCES_GROUPED_VIEW` as tpvg
				where tpvg.`ID_PROCES` in({$sqlIdProcesListSql})
				group by tpvg.`ID_TABLE`
			");
		}
		DB::getPDO()->execSql("
			update `{$this->_rootTableName}` c
				join `information_schema`.`TABLES` t on(t.TABLE_NAME = c.name and t.TABLE_SCHEMA = '{$dbName}')
			set c.ROOT_TABLE_NAME = t.TABLE_NAME
			where c.ID_USER = {$this->idUser}
				and c.db = {$idDatabase}
				{$sqlWhereAndIdProces}
		");
	}
	public function getUsedUserProcesIdsSql() {
		if ($this->idProcesFilter > 0) {
			return "
				select i.`ID_PROCES`
					from `CRM_PROCES_idx` i
					where i.`idx_MAIN_PROCES_INIT_ID`='{$this->idProcesFilter}'
			";
		}
		$idUserGroupList = $this->getUsedUserGroupIds();
		if (empty($idUserGroupList)) return null;
		$sqlIdUserGroupList = implode(",", $idUserGroupList);
		return "
			select gi.`ID_PROCES`
				from `CRM_PROCES_idx_GROUP_to_PROCES` gi
				where gi.`ID_GROUP` in({$sqlIdUserGroupList})
		";
	}
	public function getUsedUserGroupIds() {
		// $idUserGroupList = User::getGroupsIds();
		// TODO: acl filtr by group ids
		return array_keys(UsersHelper::getGroupByUser($this->idUser));
	}

	public function getTotal($params = []) {
		$sqlParsedWhere = $this->_parseSqlWhere($params);
		$sqlWhere = " t.ID_USER = {$this->idUser} " .
			(($sqlParsedWhere) ? " and " . $sqlParsedWhere : "") .
			(($this->idProcesFilter > 0) ? " and t.ID_PROCES = {$this->idProcesFilter} " : " and t.ID_PROCES is NULL ")
		;
		return DB::getPDO()->fetchValue("
			select count(1) as total
			from `{$this->_rootTableName}` t
			where {$sqlWhere}
		");
	}

	function _parseSqlWhere($params = []) {
		$sqlWhereAnd = array();
		foreach ($params as $k => $v) {
			if (strlen($k) > 3 && substr($k, 0, 2) == 'f_') {
				DBG::log("TODO: Not implemented filter 'f_' for '{$k}' = '{$v}'");
				// //$v = trim($v, '% ');
				// //$sqlWhereAnd[] = "t.`" . substr($k, 2) . "` like '%" . DB::_($v) . "%'";
				// $fldName = substr($k, 2);
				// if ($this->isGeomField($fldName)) {
				// 	$sqlFilter = $this->_sqlValueForGeomField($fldName, $v, 't');
				// 	if ($sqlFilter) $sqlWhereAnd[] = $sqlFilter;
				// 	continue;
				// }
				// if ($this->isCsvNumericField($fldName)) {
				// 	$sqlFilter = $this->_sqlValueForCsvNumericField($fldName, $v, 't');
				// 	if ($sqlFilter) $sqlWhereAnd[] = $sqlFilter;
				// 	continue;
				// }

				// if (substr($v, 0, 1) == '=') {
				// 	$v = $this->getDB()->_(substr($v, 1));
				// 	if (strlen($v)) $sqlWhereAnd[] = "t.`{$fldName}`='{$v}'";
				// }
				// else if ($v == '!NULL' || $v == 'IS NOT NULL') {
				// 	$sqlWhereAnd[] = "t.`{$fldName}` is not null";
				// }
				// else if (substr($v, 0, 1) == '!') {
				// 	$v = $this->getDB()->_(substr($v, 1));
				// 	if (strlen($v)) $sqlWhereAnd[] = "t.`{$fldName}` not like '{$v}'";
				// }
				// else if (substr($v, 0, 2) == '<=') {
				// 	$v = $this->getDB()->_(substr($v, 2));
				// 	if (strlen($v)) $sqlWhereAnd[] = "t.`{$fldName}`<='{$v}'";
				// }
				// else if (substr($v, 0, 2) == '>=') {
				// 	$v = $this->getDB()->_(substr($v, 2));
				// 	if (strlen($v)) $sqlWhereAnd[] = "t.`{$fldName}`>='{$v}'";
				// }
				// else if (substr($v, 0, 1) == '<') {
				// 	$v = $this->getDB()->_(substr($v, 1));
				// 	if (strlen($v)) $sqlWhereAnd[] = "t.`{$fldName}`<'{$v}'";
				// }
				// else if (substr($v, 0, 1) == '>') {
				// 	$v = $this->getDB()->_(substr($v, 1));
				// 	if (strlen($v)) $sqlWhereAnd[] = "t.`{$fldName}`>'{$v}'";
				// }
				// else if (false !== strpos($v, '%')) {
				// 	$sqlWhereAnd[] = "t.`{$fldName}` like '{$v}'";
				// }
				// else if ($this->isColTypeNumber($fldName)) {
				// 	$v = $this->getDB()->_($v);
				// 	$sqlWhereAnd[] = "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)) {
				// 		$sqlWhereAnd[] = "(" . implode(" and ", $sqlWhereWords) . ")";
				// 	}
				// }
			}
			else if (strlen($k) > 4 && substr($k, 0, 3) == 'sf_') {
				DBG::log("TODO: Not implemented special filter 'sf_' for '{$k}' = '{$v}'");
				// $sqlFltr = $this->_parseSpecialFilter(substr($k, 3), $v);
				// if (!empty($sqlFltr)) {
				// 	$sqlWhereAnd[] = $sqlFltr;
				// }
			}
			else if ('ogc:Filter' == $k) {
				DBG::log($v, 'string', "TODO: Not implemented ogc:Filter filter");
				$sqlFltr = $this->_parseOgcFilter($v);
				if (!empty($sqlFltr)) {
					$sqlWhereAnd[] = $sqlFltr;
				}
			}
			else if ('primaryKey' == $k) {
				DBG::log("DBG: primaryKey filter '{$v}'");
				if (!empty($v)) {
					$primaryKeyField = $this->getPrimaryKeyField();
					$pdo = DB::getPDO();
					$sqlWhereAnd[] = "t." . $pdo->identifierQuote($primaryKeyField) . " = " . $pdo->quote($v);
				}
			}
		}
		return (!empty($sqlWhereAnd)) ? implode(" and ", $sqlWhereAnd) : "";
	}

	public function getItems($params = []) {
		$sqlIdProces = ($this->idProcesFilter > 0) ? $this->idProcesFilter : 'NULL';
		$cols = [];
		$cols[] = [ 'ID_TABLE', "t.ID_TABLE" ];
		$cols[] = [ 'ID_USER', "'{$this->idUser}'" ];
		$cols[] = [ 'ID_PROCES', "'{$sqlIdProces}'" ];
		$cols[] = [ 'db', "t.db" ];
		$cols[] = [ 'name', "t.name" ];
		$cols[] = [ 'label', "t.label" ];
		$cols[] = [ 'opis', "t.opis" ];
		$cols[] = [ '_rootTableName', "t._rootTableName" ];
		$cols[] = [ 'namespace', "(select o.namespace from `CRM_#CACHE_ACL_OBJECT` o where o.idZasob = t.ID_TABLE)" ];
		$colNameList = array_map(function ($x) { return $x[0]; }, $cols);
		$filterCols = V::get('cols', [], $params);
		$filterCols = array_filter($filterCols, function ($col) use ($colNameList) {
			return in_array($col, $colNameList);
		});
		DBG::log($filterCols, 'array', "DBG:\$filterCols");
		if (!empty($filterCols) && !in_array($this->getPrimaryKeyField(), $filterCols)) {
			$filterCols[] = $this->getPrimaryKeyField();
		}
		$sqlListCols = array_reduce($cols, function ($ret, $col) use ($filterCols) {
			if (!empty($filterCols) && !in_array($col[0], $filterCols)) return $ret;
			$ret[] = "{$col[1]} as `{$col[0]}`";
			return $ret;
		}, []);
		DBG::log($sqlListCols, 'array', "DBG:\$sqlListCols");
		$sqlCols = implode("\n, ", $sqlListCols);
		DBG::log($sqlCols, 'select', "DBG:\$sqlCols");

		$sqlOrderBy = "";
		$sqlLimitOffset = "";

		$currSortCol = V::get('order_by', 'ID', $params);
		$currSortFlip = strtolower(V::get('order_dir', 'desc', $params));
		// TODO: validate $currSortCol is in field list
		// TODO: validate $currSortFlip ('asc' or 'desc')

		$aliasMap = array();
		foreach ($this->_simpleSchema['root'] as $key => $field) {
			if ('@' === substr($key, 0, 1)) continue;
			$aliasMap[ $key ] = (!empty($field['@alias'])) ? $field['@alias'] : $key;
		}
		// TODO: if (!array_key_exists($currSortCol, $aliasMap)) throw new Exception("field name not allowed to sort");
		$currSortCol = (array_key_exists($currSortCol, $aliasMap)) ? $aliasMap[$currSortCol] : null;

		if (!empty($currSortCol) && ('asc' == $currSortFlip || 'desc' == $currSortFlip)) {
			$sqlOrderBy = "order by t.`{$currSortCol}` {$currSortFlip}";
		}

		$limit = V::get('limit', 0, $params);
		$limit = ($limit < 0) ? 0 : $limit;
		$offset = V::get('limitstart', 0, $params);
		$offset = ($offset < 0) ? 0 : $offset;
		if ($limit > 0) $sqlLimitOffset = "limit {$limit} offset {$offset}";

		// $sql = "
		// 	select tpvg.`ID_TABLE`
		// 		, tpvg.`ID_DATABASE` as `db`
		// 		, tpvg.`TABLE_NAME` as `name`
		// 		, tpvg.`TABLE_LABEL` as `label`
		// 		, tpvg.`TABLE_DESCRIPTION` as `opis`
		// 	from `CRM_PROCES_idx_TABLE_TO_PROCES_GROUPED_VIEW` as tpvg
		// 	where tpvg.`ID_PROCES` in({$sqlIdProcesListSql})
		// 	group by tpvg.`ID_TABLE`
		// ";
		// DBG::logAuth($sql, "_fetchPerms sql");
		// $sql = "
		// 	select c.ID_TABLE
		// 			, c.db
		// 			, c.name
		// 			, c.label
		// 			, c.opis
		// 			, c.ROOT_TABLE_NAME as _rootTableName
		// 	from `CRM_AUTH_#CACHE_ACL_OBJECT` as c
		// 	where c.ID_USER = {$idUser}
		// 		{$sqlWhereAndIdProces}
		// ";
		// $userAccessForTables = DB::getPDO()->fetchAllByKey($sql, 'ID_TABLE');
		$sqlParsedWhere = $this->_parseSqlWhere($params);
		$sqlWhere = ($sqlParsedWhere) ? $sqlParsedWhere : "1=1";
		$subSqlWhere = "c.ID_USER = {$this->idUser}" .
			(($this->idProcesFilter > 0) ? " and c.ID_PROCES = {$this->idProcesFilter} " : " and c.ID_PROCES is NULL ")
		;

		$items = DB::getPDO()->fetchAllByKey("
			select {$sqlCols}
			from (
				select c.ID_TABLE
						, c.db
						, c.name
						, c.label
						, c.opis
						, c.ROOT_TABLE_NAME as _rootTableName
				from `{$this->_rootTableName}` as c
				where {$subSqlWhere}
			) as t
			where {$sqlWhere}
			{$sqlOrderBy}
			{$sqlLimitOffset}
		", 'ID_TABLE');
		// array_walk($items, function (&$item, $key) {
		//	 $item['link_uruchom_filtr_procesu'] = Request::getPathUri() . "index.php?FUNCTION_INIT=MENU_SELECT_PROCES&_action=setPermsByProces&id_proces={$item['ID']}";
		// });
		return $items;
	}

}