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

<?php

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

/**
 * @require CRM_PROCES_idx_URL_TO_PROCES_VIEW from FixCrmProcesInitIdx
 * @require CRM_PROCES_idx_GROUP_to_PROCES from FixCrmProcesInitIdx
 * @require idUser (ref from parent object - User)
 */
class Schema_UserUrlActionStorageAcl extends Core_AclSimpleSchemaBase {

	public $_simpleSchema = [
		'root' => [
			'@namespace' => 'default_objects/UserUrlAction',
			'@primaryKey' => 'ID_URL',
			'ID_URL' => [ '@type' => 'xsd:integer' ], // `ID_URL` 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,
			'link' => [ '@type' => 'p5:www_link' ], // `link` varchar(255) DEFAULT '',
			'label' => [ '@type' => 'xsd:string' ], // `label` varchar(255) DEFAULT '',
			'opis' => [ '@type' => 'xsd:string' ], // `opis` text,
			'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_URL_ACTION';
	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() {
		$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";
		DB::getPDO()->execSql("
			create table if not exists `{$this->_rootTableName}` (
				`ID_USER` int(11) NOT NULL,
				`ID_URL` int(11) NOT NULL,
				`ID_PROCES` int(11) DEFAULT NULL,
				`link` varchar(255) DEFAULT '',
				`label` varchar(255) DEFAULT '',
				`opis` text,
				`A_LAST_SYNC` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
				UNIQUE KEY `uniq` (ID_USER, ID_URL, ID_PROCES)
			) ENGINE=MyISAM	DEFAULT CHARSET=latin2
		");
		DB::getPDO()->execSql(" delete from `{$this->_rootTableName}` where ID_USER = {$this->idUser} {$sqlNoPrefixWhereAndIdProces} ");
			$sqlIdProcesListSql = $this->getUsedUserProcesIdsSql();
		if ($sqlIdProcesListSql) {
			DB::getPDO()->execSql("
					insert into `{$this->_rootTableName}` (ID_USER, A_LAST_SYNC, ID_PROCES, ID_URL, link, label, opis)
					select {$this->idUser}
						, NOW()
						, {$sqlIdProces}
						, upvg.`ID_URL`
						, upvg.`URL_LINK` as `link`
						, upvg.`URL_LABEL` as `label`
						, upvg.`URL_DESC` as `opis`
					from `CRM_PROCES_idx_URL_TO_PROCES_VIEW` as upvg
					where upvg.`ID_PROCES` in({$sqlIdProcesListSql})
					group by upvg.`ID_URL`
			");
		}
	}
	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.
		if (!empty($params)) DBG::log($params, 'array', "\$params");
		if (!empty($params['f_link'])) {
			if (is_string($params['f_link'])) {
				if ('=' === substr($params['f_link'], 0, 1)) {
					$sqlWhereAnd[] = "t.link = " . DB::getPDO()->quote(substr($params['f_link'], 1));
				} else {
					$sqlWhereAnd[] = "t.link like " . DB::getPDO()->quote("%{$params['f_link']}%");
				}
			}
		}
		if (!empty($params)) DBG::log($sqlWhereAnd, 'array', "\$sqlWhereAnd");
		return (!empty($sqlWhereAnd)) ? "where " . implode(" and ", $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 upvg.`ID_URL`
		// 		, upvg.`URL_LINK` as `link`
		// 		, upvg.`URL_LABEL` as `label`
		// 		, upvg.`URL_DESC` as `opis`
		// 	from `CRM_PROCES_idx_URL_TO_PROCES_VIEW` as upvg
		// 	where upvg.`ID_PROCES` in({$sqlIdProcesListSql})
		// 	group by upvg.`ID_URL`
		// ";
		// $userAccessForUrls = DB::getPDO()->fetchAllByKey($sql, 'ID_URL');
		// $userAccessForUrls = DB::getPDO()->fetchAllByKey("
		// 	select c.ID_URL
		// 			, c.link
		// 			, c.label
		// 			, c.opis
		// 	from `{$this->_rootTableName}` as c
		// 	where c.ID_USER = {$idUser}
		// ", 'ID_URL');
		$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_URL
					, t.ID_USER
					, t.ID_PROCES
					, t.link
					, t.label
					, t.opis
			from (
				select c.ID_URL
						, c.ID_USER
						, c.ID_PROCES
						, c.link
						, c.label
						, c.opis
				from `{$this->_rootTableName}` as c
				where c.ID_USER = {$this->idUser}
					{$sqlWhereAndIdProces}
			) as t
			{$sqlWhereAnd}
			{$sqlOrderBy}
			{$sqlLimitOffset}
		", 'ID_URL');
		// 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;
	}

}