se/SE/se-lib/Route/Msgs.php

<?php

Lib::loadClass('RouteBase');

/*
	## Flash message system:

	System for automatic and custom messages for users.

	- `app_className` - for automatic msgs to search for msg text
	- `msg` - msg to show or to parse by `app_className`
	- `uiTargetType` - where to show this msg eg. 'default_db_table', 'after_login', 'everywhere'
	- `uiTargetName` - eg. database table name (if from default_db)
	- `userTargetType` - type of users allowed to see this msg eg. 'everyone', 'admin' (ADMIN_LEVEL=0?), 'user', 'group'
	- `userTargetName` - login, group name
	- `actionExecuted` - execution time if msg require user to run specific action (send msg.id in request)
	- `actionNotes` - notes/msgs/dbg for user actions executed from this msg

	Messages created by db triggers must define `app_className` that should parse `msg`. For example to use in FixProjectPath and FixZasobPath to keep correct paths.

	## `A_STATUS`
	- 'WAITING'  - msg sent by `A_RECORD_CREATE_AUTHOR` at `A_RECORD_CREATE_DATE`
	- 'NORMAL'   - msg read by `A_RECORD_UPDATE_AUTHOR` at `A_RECORD_UPDATE_DATE`
	- 'OFF_HARD' - msg deleted by `A_RECORD_DELETE_AUTHOR` if != `A_RECORD_CREATE_AUTHOR` at `A_RECORD_DELETE_DATE`
	- 'DELETED'  - msg deleted by `A_RECORD_CREATE_AUTHOR` at `A_RECORD_DELETE_DATE`
*/
class Route_Msgs extends RouteBase {

	public function handleAuth() {
		if (!User::logged()) {
			throw new HttpException('Unauthorized', 401);
		}
	}

	public function defaultAction() {
		SE_Layout::gora();
		?>
<div class="container">
	<h1>Messages system</h1>
	...
</div>
		<?php
		SE_Layout::dol();
	}

	public function reinstallAction() {
		$this->reinstall();
		die('OK');
	}

	public function reinstallFunctionsAction() {
		$this->reinstallFunctions();
		die('OK');
	}

	public function runAction() {
		$msgId = V::get('_msgId', 0, $_REQUEST, 'int');
		if ($msgId > 0) {
			$this->runByMessageId($msgId);
		}
		$jsonData = new stdClass();
		$jsonData->type = 'success';
		$jsonData->msg = 'Gotowe';
		echo json_encode($jsonData);
		exit;
	}

	public function addTestMsgAction() {
		$sql = "INSERT INTO `CRM_UI_MSGS` (`ID`, `app_className`, `msg`, `msgType`, `uiTargetType`, `uiTargetName`, `userTargetType`, `userTargetName`, `actionExecutedTime`, `actionNotes`, `A_RECORD_CREATE_DATE`, `A_RECORD_CREATE_AUTHOR`, `A_RECORD_UPDATE_DATE`, `A_RECORD_UPDATE_AUTHOR`)
			VALUES (NULL, 'FixZasobPath', 'Update all paths', 'danger', 'default_db_table', 'CRM_LISTA_ZASOBOW', 'user', 'plabudda', NULL, '', NULL, 'plabudda', NULL, '')";
		$db = DB::getDB();
		$db->query($sql);
		die('OK');
	}

	public function reinstallFunctions() {
		$sqlList = array();
		//$sqlList['RemoveTable_CRM_UI_MSGS__EXEC_LOG'] = "DROP TABLE IF EXISTS `CRM_UI_MSGS__EXEC_LOG`";
		$sqlList['CreateTable_CRM_UI_MSGS__EXEC_LOG'] = "
			CREATE TABLE IF NOT EXISTS `CRM_UI_MSGS__EXEC_LOG` (
				`ID` int(11) NOT NULL AUTO_INCREMENT
				, `_exec_time` datetime DEFAULT NULL
				, `author` VARCHAR(40) DEFAULT ''
				, `app_className` VARCHAR(255) DEFAULT ''
				, `msg` VARCHAR(255) DEFAULT ''
				, `uiTargetType` VARCHAR(255) DEFAULT ''
				, `uiTargetName` VARCHAR(255) DEFAULT ''
				, `userTargetType` VARCHAR(255) DEFAULT ''
				, `userTargetName` VARCHAR(255) DEFAULT ''
				, PRIMARY KEY (`ID`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2
		";
		$sqlList['RemoveProcedureMarkAsExecuted'] = "DROP PROCEDURE IF EXISTS `CRM_UI_MSGS__markAsExecuted`";
		$sqlList['InstallProcedureMarkAsExecuted'] = <<<SQL_QUERY
CREATE DEFINER=`root`@`localhost` PROCEDURE `CRM_UI_MSGS__markAsExecuted`(
	IN author VARCHAR(40),
	IN app_className VARCHAR(255),
	IN msg VARCHAR(255),
	IN uiTargetType VARCHAR(255),
	IN uiTargetName VARCHAR(255),
	IN userTargetType VARCHAR(255),
	IN userTargetName VARCHAR(255)
)
BEGIN
--	insert into `CRM_UI_MSGS__EXEC_LOG`
--		(`author`, `app_className`, `msg`, `uiTargetType`, `uiTargetName`, `userTargetType`, `userTargetName`, `_exec_time`)
--		values(author, app_className, msg, uiTargetType, uiTargetName, userTargetType, userTargetName, NOW());
	update `CRM_UI_MSGS` m
		set m.`A_STATUS`='OFF_HARD'
			, m.`actionExecutedTime`=NOW()
			, m.`A_RECORD_UPDATE_DATE`=NOW()
			, m.`A_RECORD_UPDATE_AUTHOR`=author
		where m.`A_STATUS`='WAITING' -- TODO: not in('DELETED', 'OFF_HARD')
			and m.`app_className`=app_className
			and m.`msg`=msg
			and m.`uiTargetType`=uiTargetType
			and m.`uiTargetName`=uiTargetName
			and m.`userTargetType`=userTargetType
			and m.`userTargetName`=userTargetName
		;
END
SQL_QUERY;
		$sqlList['RemoveProcedureMarkTableEveryoneAsExecuted'] = "DROP PROCEDURE IF EXISTS `CRM_UI_MSGS__markTableEveryoneAsExecuted`";
		$sqlList['InstallProcedureMarkTableEveryoneAsExecuted'] = <<<SQL_QUERY
CREATE DEFINER=`root`@`localhost` PROCEDURE `CRM_UI_MSGS__markTableEveryoneAsExecuted`(
	IN author VARCHAR(40),
	IN app_className VARCHAR(255),
	IN msg VARCHAR(255),
	IN uiTargetName VARCHAR(255)
)
BEGIN
	CALL CRM_UI_MSGS__markAsExecuted(author, app_className, msg, 'default_db_table', uiTargetName, 'everyone', '');
END
SQL_QUERY;

		$sqlList['RemoveProcedureAddUniqueMsg'] = "DROP PROCEDURE IF EXISTS `CRM_UI_MSGS__addUniqueMsg`";
		$sqlList['InstallProcedureAddUniqueMsg'] = <<<SQL_QUERY
CREATE DEFINER=`root`@`localhost` PROCEDURE `CRM_UI_MSGS__addUniqueMsg`(
	IN author VARCHAR(40),
	IN app_className VARCHAR(255),
	IN msgType VARCHAR(16),
	IN msg VARCHAR(255),
	IN uiTargetType VARCHAR(255),
	IN uiTargetName VARCHAR(255),
	IN userTargetType VARCHAR(255),
	IN userTargetName VARCHAR(255)
)
BEGIN
	IF (select count(1)
			from `CRM_UI_MSGS`
			where `app_className`=app_className
				and `msg`=msg
				and `uiTargetType`=uiTargetType
				and `uiTargetName`=uiTargetName
				and `userTargetType`=userTargetType
				and `userTargetName`=userTargetName
				and `A_STATUS`='WAITING'
			) = 0 THEN
		INSERT INTO `CRM_UI_MSGS` (`ID`
				, `app_className`, `msg`, `msgType`
				, `uiTargetType`, `uiTargetName`
				, `userTargetType`, `userTargetName`
				, `A_RECORD_CREATE_DATE`, `A_RECORD_CREATE_AUTHOR`)
			VALUES (NULL
				, app_className, msg, msgType
				, uiTargetType, uiTargetName
				, userTargetType, userTargetName
				, NOW(), author
			);
	END IF;
END
SQL_QUERY;
		$sqlList['RemoveProcedureAddTableEveryoneUniqueMsg'] = "DROP PROCEDURE IF EXISTS `CRM_UI_MSGS__addTableEveryoneUniqueMsg`";
		$sqlList['InstallProcedureAddTableEveryoneUniqueMsg'] = <<<SQL_QUERY
CREATE DEFINER=`root`@`localhost` PROCEDURE `CRM_UI_MSGS__addTableEveryoneUniqueMsg`(
	IN author VARCHAR(40),
	IN app_className VARCHAR(255),
	IN msgType VARCHAR(16),
	IN msg VARCHAR(255),
	IN uiTargetName VARCHAR(255)
)
BEGIN
	CALL CRM_UI_MSGS__addUniqueMsg(author, app_className, msgType, msg, 'default_db_table', uiTargetName, 'everyone', '');
END
SQL_QUERY;
		$formFixZasobPath = <<<SQL_QUERY
		CALL CRM_UI_MSGS__addTableEveryoneUniqueMsg(NEW.A_RECORD_UPDATE_AUTHOR, 'FixZasobPath', 'danger', 'Update all paths', 'CRM_LISTA_ZASOBOW');
		IF (select count(1) from `CRM_UI_MSGS` where `app_className`='FixZasobPath'
					and `msg`='Update all paths'
					and `uiTargetType`='default_db_table'
					and `uiTargetName`='CRM_LISTA_ZASOBOW'
					and `A_STATUS`='WAITING'
				) = 0 THEN
			INSERT INTO `CRM_UI_MSGS` (`ID`
					, `app_className`, `msg`, `msgType`
					, `uiTargetType`, `uiTargetName`
					, `userTargetType`
					, `A_RECORD_CREATE_DATE`, `A_RECORD_CREATE_AUTHOR`)
				VALUES (NULL
					, 'FixZasobPath', 'Update all paths', 'danger'
					, 'default_db_table', 'CRM_LISTA_ZASOBOW'
					, 'everyone'
					, NOW(), NEW.A_RECORD_UPDATE_AUTHOR
				);
		END IF;
SQL_QUERY;
		$formFixProjectPath = <<<SQL_QUERY
		CALL CRM_UI_MSGS__addTableEveryoneUniqueMsg(NEW.A_RECORD_UPDATE_AUTHOR, 'FixProjectPath', 'danger', 'Update all paths', 'IN7_MK_BAZA_DYSTRYBUCJI');
		IF (select count(1) from `CRM_UI_MSGS` where `app_className`='FixProjectPath'
					and `msg`='Update all paths'
					and `uiTargetType`='default_db_table'
					and `uiTargetName`='IN7_MK_BAZA_DYSTRYBUCJI'
					and `A_STATUS`='WAITING'
				) = 0 THEN
			INSERT INTO `CRM_UI_MSGS` (`ID`
					, `app_className`, `msg`, `msgType`
					, `uiTargetType`, `uiTargetName`
					, `userTargetType`
					, `A_RECORD_CREATE_DATE`, `A_RECORD_CREATE_AUTHOR`)
				VALUES (NULL
					, 'FixProjectPath', 'Update all paths', 'danger'
					, 'default_db_table', 'IN7_MK_BAZA_DYSTRYBUCJI'
					, 'everyone'
					, NOW(), NEW.A_RECORD_UPDATE_AUTHOR
				);
		END IF;

SQL_QUERY;
		$db = DB::getDB();
		if ($db->has_errors()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
		foreach ($sqlList as $sqlName => $sql) {
			$res = $db->query($sql);
			if ($db->has_errors()) {
				throw new Exception("DB Errors at sql '{$sqlName}': " . implode("\n<br>", $db->get_errors()));
			}
		}
	}

	public function reinstall() {
		$sqlList = array();
		//$sqlList['RemoveTable'] = "DROP TABLE IF EXISTS `CRM_UI_MSGS`";// TODO: update struct (add cells, rm old keys) - not drop/create
		$sqlList['InstallTable'] = "
			CREATE TABLE IF NOT EXISTS `CRM_UI_MSGS` (
				`ID` int(11) NOT NULL AUTO_INCREMENT
				, `idReplyTo` int(11) NOT NULL DEFAULT 0
				, `idThread` int(11) NOT NULL DEFAULT 0
				-- app_className - for automatic msgs to search for msg text
				, `app_className` varchar(255) DEFAULT NULL
				-- msg - msg to show or to parse by app_className
				, `msg` varchar(1000) NOT NULL
				, `msgType` enum('info','danger','warning','success') NOT NULL DEFAULT 'info'
				-- uiTargetType - where to show this msg eg. 'default_db_table', 'after_login', 'everywhere'
				, `uiTargetType` enum('default_db_table','default_db_table_record','after_login','everywhere') NOT NULL
				-- uiTargetName - eg. database table name (if from default_db)
				, `uiTargetName` varchar(255) NOT NULL DEFAULT ''
				-- userTargetType - type of users allowed to see this msg eg. 'everyone', 'admin' (ADMIN_LEVEL=0?), 'user', 'group'
				, `userTargetType` enum('none','everyone','admin','user','group') NOT NULL DEFAULT 'none'
				-- userTargetName - login, group name
				, `userTargetName` varchar(255) NOT NULL DEFAULT ''
				-- actionExecutedTime - execution time if msg require user to run specific action (send msg.id in request)
				, `actionExecutedTime` datetime DEFAULT NULL
				-- actionNotes - notes/msgs/dbg for user actions executed from this msg
				, `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`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2
		";
		$db = DB::getDB();
		if ($db->has_errors()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
		foreach ($sqlList as $sqlName => $sql) {
			$res = $db->query($sql);
			if ($db->has_errors()) {
				throw new Exception("DB Errors at sql '{$sqlName}': " . implode("\n<br>", $db->get_errors()));
			}
		}
		$this->reinstallFunctions();
	}

	public function getActiveMessagesForTable($tblName) {
		if (empty($tblName)) return;
		$sqlTableName = DB::getPDO()->quote($tblName);
		$usrLogin = User::getLogin();
		$msgs = [];
		$sql = "
			select m.*
			from `CRM_UI_MSGS` m
			where m.`uiTargetType`='default_db_table'
				and m.`A_STATUS`='WAITING'
				and m.`uiTargetName`={$sqlTableName}
				and (m.`userTargetType` in('everyone')
					or (m.`userTargetType`='user' and m.`userTargetName`='{$usrLogin}')
					-- TODO: use 'admin', 'group'
				)
		";
		foreach (DB::getPDO()->fetchAll($sql) as $row) {
			$r = (object)$row;
			if ($msg = $this->parseMessage($r)) {
				$msg['link'] = 'index.php?_route=Msgs&_task=run&_msgId=' . $r->ID;
				$msg['linkType'] = 'ajax';
				$msgs[$r->ID] = $msg;
			}
		}
		return $msgs;
	}

	public function getActiveMessagesForTableRecord($tblName, $id) {
		if (empty($tblName)) return;
		$db = DB::getDB();
		$tblName = $db->_($tblName);

		$usrLogin = User::getLogin();
		$msgs = null;
		$sql = "select m.*
			from `CRM_UI_MSGS` m
			where m.`uiTargetType`='default_db_table_record'
				and m.`uiTargetName`='{$tblName}.{$id}'
				and (m.`userTargetType` in('everyone')
					or (m.`userTargetType`='user' and m.`userTargetName`='{$usrLogin}')
					-- TODO: use group id
				)
				and m.`A_STATUS`='WAITING'
			order by m.`ID` DESC
		";
		$db = DB::getDB();
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			if ($msg = $this->parseMessage($r)) {
				$msg['link'] = 'index.php?_route=Msgs&_task=run&_msgId=' . $r->ID;
				$msg['linkType'] = 'ajax';
				$msgs[$r->ID] = $msg;
			}
		}
		return $msgs;
	}

	public function parseMessage($r) {
		$msg = null;// ['type'=>'info', 'message'=>'...']
		// $r->app_className - for automatic msgs to search for msg text
		// $r->msg - for automatic msgs to search for msg text
		// $r->msgType - 'info','danger','warning','success'
		if (!empty($r->app_className)) {
			$route = Router::getRoute($r->app_className);
			$msg = array();
			$msg['message'] = $route->parseMessageFromMsgsSystem($r->msg);
			$msg['type'] = $r->msgType;
			$msg['_raw'] = $r;
		} else {
			$msg = array();
			$msg['message'] = $this->parseMessageFromMsgsSystem($r->msg);
			$msg['type'] = $r->msgType;
			$msg['_raw'] = $r;
		}
		return $msg;
	}

	public function parseMessageFromMsgsSystem($msg) {
		return $msg;
	}

	public function runByMessageId($id) {
		$msgRow = $this->getActiveMessage($id);
		$execNotes = '';
		if (!empty($msgRow->app_className)) {
			$route = Router::getRoute($msgRow->app_className);
			$route->runByMessageFromMsgsSystem($msgRow->msg, $execNotes);
		}
		$this->forceFinishMessage($id, $execNotes);
	}

	public function getMessage($id) {
		if (empty($id)) return;
		$id = intval($id);
		if ($id <= 0) return;

		$msg = null;
		$sql = "select * from `CRM_UI_MSGS` where `ID`='{$id}' ";
		$db = DB::getDB();
		$res = $db->query($sql);
		if ($r = $db->fetch($res)) {
			$msg = $r;
		}
		return $msg;
	}

	public function getActiveMessage($id) {
		if (empty($id)) return;
		$id = intval($id);
		if ($id <= 0) return;

		$msg = null;
		$sql = "select m.*
			from `CRM_UI_MSGS` m
			where m.`ID`='{$id}'
				and m.`A_STATUS`='WAITING'
		";
		$db = DB::getDB();
		$res = $db->query($sql);
		if ($r = $db->fetch($res)) {
			$msg = $r;
		}
		if (!$msg) {
			throw new HttpException("Message not found", 404);
		}
		return $msg;
	}

	public function forceFinishMessage($id, $execNotes) {
		if (empty($id)) return;
		$id = intval($id);
		if ($id <= 0) return;

		$usrLogin = User::getLogin();
		$db = DB::getDB();
		$execNotes = $db->_($execNotes);
		$sql = "update `CRM_UI_MSGS`
			set `A_STATUS`='OFF_HARD'
				, `actionExecutedTime`=NOW()
				, `actionNotes`='{$execNotes}'
				, `A_RECORD_UPDATE_DATE`=NOW()
				, `A_RECORD_UPDATE_AUTHOR`='{$usrLogin}'
			where `ID`='{$id}'
		";
		$db->query($sql);
		return;
	}

	public function removeMessage($id) {
		if (empty($id)) return;
		$id = intval($id);
		if ($id <= 0) return;

		$sql = "update `CRM_UI_MSGS` set `A_STATUS`='DELETED' where `ID`='{$id}' ";
		$db = DB::getDB();
		$db->query($sql);
	}

	public function removeTableRecordMsg($idMsg) {
		// IDEA: do kosza - add trigger to insert into `CRM_UI_MSGS__TRASH` after DELETE on `CRM_UI_MSGS`
		$idMsg = intval($idMsg);
		if ($idMsg <= 0) throw new Exception("Brak wiadomości!");
		$usrLogin = User::getLogin();
		$db = DB::getDB();
		if (!$db) throw new Exception("Brak dazy danych!");
		if ($db->has_errors()) throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		$sqlTODO = "delete `CRM_UI_MSGS` where `ID`='{$idMsg}' ";
		$sql = "update `CRM_UI_MSGS`
			set `A_STATUS`=IF('{$usrLogin}'=`A_RECORD_CREATE_AUTHOR`, 'DELETED', 'OFF_SOFT')
				, `A_RECORD_DELETE_AUTHOR`='{$usrLogin}'
				, `A_RECORD_DELETE_DATE`=NOW()
			where `ID`='{$idMsg}'
		";
		DBG::_('DBG_MSGS', '>1', "sql", $sql, __CLASS__, __FUNCTION__, __LINE__);
		$res = $db->query($sql);
		if (!$res || $db->has_errors()) throw new Exception("Wystąpiły błędy podczas próby zapisu wiadomości: " . implode("\n<br>", $db->get_errors()));
	}

}