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 = []) {
		// TODO: $sqlWhereAnd = $this->_parseSqlWhere($params);
		$sqlWhereAndIdProces = ($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 t.ID_USER = {$this->idUser}
				{$sqlWhereAndIdProces}
		");
	}

	public function _parseSqlWhere($params = []) {
		$sqlWhereAnd = "";
		// TODO: parse where/ogc, etc.
		return $sqlWhereAnd;
	}

	public function getItems($params = []) {
		$sqlOrderBy = "";
		$sqlLimitOffset = "";
		$sqlWhereAnd = $this->_parseSqlWhere($params);

		$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');
		$sqlIdProces = ($this->idProcesFilter > 0) ? $this->idProcesFilter : 'NULL';
		$sqlWhereAndIdProces = ($this->idProcesFilter > 0) ? "and c.ID_PROCES = {$this->idProcesFilter}" : "and c.ID_PROCES is NULL";
		$items = DB::getPDO()->fetchAllByKey("
			select t.ID_TABLE
					, {$this->idUser} as ID_USER
					, {$sqlIdProces} as ID_PROCES
					, t.db
					, t.name
					, t.label
					, t.opis
					, t._rootTableName
					, (select o.namespace from `CRM_#CACHE_ACL_OBJECT` o where o.idZasob = t.ID_TABLE) as namespace
			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 c.ID_USER = {$this->idUser}
					{$sqlWhereAndIdProces}
			) as t
			where 1=1
				{$sqlWhereAnd}
			{$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;
	}

}