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();
		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();
		$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;
  }

}