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

<?php

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

class Schema_UserMsgsStorageAcl extends Core_AclSimpleSchemaBase {

	// select m.*
	//   `ID` int(11) NOT NULL AUTO_INCREMENT,
	//   `idReplyTo` int(11) NOT NULL DEFAULT '0',
	//   `idThread` int(11) NOT NULL DEFAULT '0',
	//   `app_className` varchar(255) DEFAULT NULL,
	//   `msg` varchar(1000) NOT NULL,
	//   `msgType` enum('info','danger','warning','success') NOT NULL DEFAULT 'info',
	//   `uiTargetType` enum('default_db_table','default_db_table_record','after_login','everywhere') NOT NULL,
	//   `uiTargetName` varchar(255) NOT NULL DEFAULT '',
	//   `userTargetType` enum('none','everyone','admin','user','group') NOT NULL DEFAULT 'none',
	//   `userTargetName` varchar(255) NOT NULL DEFAULT '',
	//   `actionExecutedTime` datetime DEFAULT NULL,
	//   `actionNotes` varchar(255) NOT NULL DEFAULT '',
	//   `A_STATUS` enum('WAITING','NORMAL','OFF_HARD','DELETED') NOT NULL DEFAULT 'WAITING',
	//   `A_RECORD_CREATE_DATE` datetime DEFAULT NULL,
	//   `A_RECORD_CREATE_AUTHOR` varchar(40) NOT NULL DEFAULT '',
	//   `A_RECORD_UPDATE_DATE` datetime DEFAULT NULL,
	//   `A_RECORD_UPDATE_AUTHOR` varchar(40) NOT NULL DEFAULT '',
	//   `A_RECORD_DELETE_DATE` datetime DEFAULT NULL,
	//   `A_RECORD_DELETE_AUTHOR` varchar(40) NOT NULL DEFAULT '',
	//   PRIMARY KEY (`ID`),
	//   KEY `app_className` (`app_className`),
	//   KEY `A_STATUS` (`A_STATUS`),
	//   KEY `A_RECORD_UPDATE_DATE` (`A_RECORD_UPDATE_DATE`)
	// 			from `CRM_UI_MSGS` m
	// 			where m.`uiTargetType`='default_db_table_record'
	// 		--		and m.`uiTargetName`='.'
	// 					and (
	// 			m.`userTargetType` in('everyone')
	// 			or (m.`userTargetType`='user' and m.`userTargetName`='plabudda')
	// 			or (m.`userTargetType`='group' and m.`userTargetName` in(94,4495,1767,2948,2975,13069,21168,12887,22017,20046,11875,20906,79,22720,26118,26522,26498,26519,26623,26532,26119,26529,26497,27324,289,288,270,13,20540))
	// 		)
	// 					and m.`A_STATUS` in('WAITING', 'NORMAL')
	// 			order by m.`ID` DESC
	// 			limit 21

	public $_simpleSchema = [
		'root' => [
			'@namespace' => 'default_objects/UserMsgs',
			'ID' => [ '@type' => 'xsd:integer' ],
			'idReplyTo' => [ '@type' => 'xsd:integer' ],
			'idThread' => [ '@type' => 'xsd:integer' ],
			'app_className' => [ '@type' => 'xsd:string' ],
			'msg' => [ '@type' => 'xsd:string' ],
			'msgType' => [ '@type' => 'xsd:string' ], // enum('info','danger','warning','success') NOT NULL DEFAULT 'info',
			'uiTargetType' => [ '@type' => 'xsd:string' ], // enum('default_db_table','default_db_table_record','after_login','everywhere') NOT NULL,
			'uiTargetName' => [ '@type' => 'xsd:string' ],
			'userTargetType' => [ '@type' => 'xsd:string' ], // enum('none','everyone','admin','user','group') NOT NULL DEFAULT 'none',
			'userTargetName' => [ '@type' => 'xsd:string' ],
			'actionExecutedTime' => [ '@type' => 'xsd:date' ],
			'actionNotes' => [ '@type' => 'xsd:string' ],
			'A_STATUS' => [ '@type' => 'xsd:string' ], // enum('WAITING','NORMAL','OFF_HARD','DELETED') NOT NULL DEFAULT 'WAITING',
			'actionNotes' => [ '@type' => 'xsd:string' ],
			'A_RECORD_CREATE_AUTHOR' => [ '@type' => 'xsd:string' ], // label: "autor"
			'A_RECORD_CREATE_DATE' => [ '@type' => 'xsd:date' ], // label: "utworzono"
			'A_RECORD_UPDATE_AUTHOR' => [ '@type' => 'xsd:string' ], // label: "zaktualizował"
			'A_RECORD_UPDATE_DATE' => [ '@type' => 'xsd:date' ], // label: "zaktualizowano"
			'A_RECORD_DELETE_AUTHOR' => [ '@type' => 'xsd:string' ], // label: "usunął"
			'A_RECORD_DELETE_DATE' => [ '@type' => 'xsd:date' ], // label: "usunięto"
			// 'custom_field_name' => [ '@type' => 'p5:www_link' ],
		]
	];
	public $_rootTableName = 'CRM_UI_MSGS';
	public $idUser = null;
	public $login = null;

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

	function setIdUser($idUser) { $this->idUser = intval($idUser); }
	function getIdUser() { return $this->idUser; }

	function getTotal($params = []) {
		$sqlWhere = $this->_parseSqlWhere($params);

		return DB::getPDO()->fetchValue("
			select count(1) as total
			from `CRM_UI_MSGS` m
			where {$sqlWhere}
		");
	}

	function _parseSqlWhere($params = []) {
		$sqlWhereAnd = [];

		$sqlWhereAnd[] = " m.`uiTargetType` = 'default_db_table_record' ";

		$sqlUserLogin = DB::getPDO()->quote($this->login);
		$idGroupList = $this->_getUserIdGroupList();
		if (empty($idGroupList)) throw new Exception("Brak przypisanych grup do użytkownika");
		$sqlIdGroupsCsv = implode(",", $idGroupList);
		$sqlWhereAnd[] = "
			(
				m.`userTargetType` in('everyone')
				or ( m.`userTargetType`='user' and m.`userTargetName` = {$sqlUserLogin} )
				or ( m.`userTargetType`='group' and m.`userTargetName` in( {$sqlIdGroupsCsv} ) )
			)
		";

		$sqlWhereAnd[] = " m.`A_STATUS` in('WAITING', 'NORMAL') ";

		// TODO: parse where/ogc, etc.

		return implode(" and ", $sqlWhereAnd);
	}

	function getItems($params = []) {
		$sqlOrderBy = "";
		$sqlLimitOffset = "";
		$sqlWhere = $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 m.`{$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}";

		$items = DB::getPDO()->fetchAllByKey("
			select m.ID
				, m.idReplyTo
				, m.idThread
				, m.app_className
				, m.msg
				, m.msgType
				, m.uiTargetType
				, m.uiTargetName
				, m.userTargetType
				, m.userTargetName
				, m.actionExecutedTime
				, m.actionNotes
				, m.A_STATUS
				, m.actionNotes
				, m.A_RECORD_CREATE_AUTHOR
				, m.A_RECORD_CREATE_DATE
				, m.A_RECORD_UPDATE_AUTHOR
				, m.A_RECORD_UPDATE_DATE
				, m.A_RECORD_DELETE_AUTHOR
				, m.A_RECORD_DELETE_DATE
			from `CRM_UI_MSGS` m
			where {$sqlWhere}
			{$sqlOrderBy}
			{$sqlLimitOffset}
		", 'ID');
		// 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;
	}

	function _getUserIdGroupList() {
		return DB::getPDO()->fetchValuesList("
			select z.ID
			from `CRM_AUTH_PROFILE` as up
				left join `CRM_LISTA_ZASOBOW` as z on(z.`ID`=up.`ID_ZASOB`)
			where
				up.`REMOTE_ID` = :id_user
				and up.`A_STATUS` in('WAITING', 'NORMAL')
				and up.`REMOTE_TABLE`='ADMIN_USERS'
				and z.`ID` is not null
				and z.`TYPE` in('STANOWISKO','PODMIOT','DZIAL')
		", [
			':id_user' => $this->idUser,
		]);
	}

}