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

<?php

Lib::loadClass('RouteBase');

/*
# Nofitication system:

Sends mail by settings `CRM_NOTIFY`:
- who - user login
- when - shedule (once a day, once on houd)
- what - action type
- last_exec_time

*/
class Route_Notify extends RouteBase {

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

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

	public function reinstallAction() {
		try {
			$this->reinstall();
		} catch (Exception $e) {
			echo "Error #" . $e->getCode() .  "|" . $e->getLine() .  ": " . $e->getMessage();
		}
		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 reinstallFunctions() {
		$sqlList = array();
		$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();
		$pdo = DB::getPDO();
		/*
			`CRM_NOTIFY`:
				- who - user login
				- when - shedule (once a day, once on houd)
				- what - action type
				- last_exec_time
		*/
		if(1){// TEST
			$expectedStruct = array();
			$expectedStruct['who'] = array('type'=>'varchar', 'max_length'=>20, 'is_nullable'=>false);// `who` varchar(20) -- TODO:  NOT NULL
			$expectedStruct['when'] = array('type'=>'varchar', 'max_length'=>255, 'is_nullable'=>false);// `when` varchar(255) NOT NULL
			$expectedStruct['what'] = array('type'=>'varchar', 'max_length'=>255, 'is_nullable'=>false);// `what` varchar(255) NOT NULL
			$expectedStruct['last_exec_time'] = array('type'=>'datetime', 'is_nullable'=>true);// `last_exec_time` datetime
			$expectedStruct['_created'] = array('type'=>'datetime', 'is_nullable'=>false);// `_created` datetime
			$expectedStruct['uniq_key_1'] = array('type'=>'UNIQUE KEY', 'key_fields'=>array('who','when','what'));// UNIQUE KEY `uniq_key_1` (`who`,`when`,`what`)
			$expectedStruct['key_who'] = array('type'=>'KEY', 'key_fields'=>array('who'));// KEY `key_who` (`who`)
//			$expectedStruct['t1'] = array('type'=>'varchar', 'max_length'=>300, 'default_value'=>null);
//			$expectedStruct['t2'] = array('type'=>'int');
//			$expectedStruct['t3'] = array('type'=>'int', 'num_precision'=>11, 'default_value'=>null);
//			$expectedStruct['t_dec_11_x'] = array('type'=>'decimal', 'num_precision'=>11);
//			$expectedStruct['t_dec_11_2'] = array('type'=>'decimal', 'num_precision'=>11, 'num_scale'=>2);
			$expectedStruct['t_int'] = array('type'=>'int');
			$expectedStruct['t_tinyint'] = array('type'=>'tinyint');
			$expectedStruct['t_smallint'] = array('type'=>'smallint');
			$expectedStruct['t_mediumint'] = array('type'=>'mediumint');
			$expectedStruct['t_bigint'] = array('type'=>'bigint');
			$expectedStruct['t_decimal'] = array('type'=>'decimal');
			$expectedStruct['t_float'] = array('type'=>'float');
			$expectedStruct['t_double'] = array('type'=>'double');
			$expectedStruct['t_real'] = array('type'=>'real');
			$expectedStruct['t_date'] = array('type'=>'date');
			$expectedStruct['t_datetime'] = array('type'=>'datetime');
			$expectedStruct['t_timestamp'] = array('type'=>'timestamp');
			$expectedStruct['t_time'] = array('type'=>'time');
			$expectedStruct['t_year'] = array('type'=>'year');
			$expectedStruct['t_char'] = array('type'=>'char');
			$expectedStruct['t_varchar'] = array('type'=>'varchar');
			$expectedStruct['t_text'] = array('type'=>'text');
			$expectedStruct['t_tinytext'] = array('type'=>'tinytext');
			$expectedStruct['t_mediumtext'] = array('type'=>'mediumtext');
			$expectedStruct['t_longtext'] = array('type'=>'longtext');
			$expectedStruct['t_enum'] = array('type'=>'enum', 'values'=>array('v1'));
			$expectedStruct['t_set'] = array('type'=>'set', 'values'=>array('v1'));
			$expectedStruct['t_bit'] = array('type'=>'bit', 'max_length'=>2);
			$expectedStruct['t_boolean'] = array('type'=>'boolean');
			$expectedStruct['t_serial'] = array('type'=>'serial');
			$expectedStruct['t_binary'] = array('type'=>'binary');
			$expectedStruct['t_varbinary'] = array('type'=>'varbinary');
			$expectedStruct['t_blob'] = array('type'=>'blob');
			$expectedStruct['t_tinyblob'] = array('type'=>'tinyblob');
			$expectedStruct['t_mediumblob'] = array('type'=>'mediumblob');
			$expectedStruct['t_longblob'] = array('type'=>'longblob');
			$expectedStruct['t_geometry'] = array('type'=>'geometry');
			$expectedStruct['t_point'] = array('type'=>'point');
			$expectedStruct['t_linestring'] = array('type'=>'linestring');
			$expectedStruct['t_polygon'] = array('type'=>'polygon');
			$expectedStruct['t_multipoint'] = array('type'=>'multipoint');
			$expectedStruct['t_multilinestring'] = array('type'=>'multilinestring');
			$expectedStruct['t_multipolygon'] = array('type'=>'multipolygon');
			$expectedStruct['t_geometrycollection'] = array('type'=>'geometrycollection');

			$sqlCreate = $pdo->showCreateStructMysql('CRM_NOTIFY', $expectedStruct, array('char_encoding'=>'latin2'));
			DBG::_(true, true, "fixedEpectedStruct", $sqlCreate, __CLASS__, __FUNCTION__, __LINE__);
			$pdo->assertTableStruct('CRM_NOTIFY', $expectedStruct, array('char_encoding'=>'latin2'));
			{// force - drop/create
				$pdo->exec("DROP TABLE IF EXISTS CRM_NOTIFY");
				$pdo->exec($sqlCreate);
			}
			return;
		}

		$sth = $pdo->prepare("
				CREATE TABLE IF NOT EXISTS CRM_NOTIFY (
					`who` varchar(20) -- TODO:  NOT NULL
					, `when` varchar(255) NOT NULL
					, `what` varchar(255) NOT NULL
					, `last_exec_time` datetime -- TODO: DEFAULT NULL
					, `_created` datetime
					, UNIQUE KEY `uniq` (`who`,`when`,`what`)
					, KEY `who` (`who`)
				) ENGINE=MyISAM DEFAULT CHARSET=latin2
		");
		$sth->execute();
		if(1){// TEST nulls
			$sth = $pdo->prepare("DROP TABLE IF EXISTS CRM_NOTIFY");
			$sth->execute();
			$sth = $pdo->prepare("
					CREATE TABLE IF NOT EXISTS CRM_NOTIFY (
						`who` varchar(20)
						, `when` varchar(255) NOT NULL
						, `what` varchar(255) DEFAULT ''
						, `t1` varchar(255) NOT NULL -- same as with DEFAULT ''
						, `t2` varchar(255) NOT NULL DEFAULT ''
						, `t2z` varchar(255) NOT NULL DEFAULT '0'
					--	, `t3` varchar(255) NOT NULL DEFAULT NULL -- SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value
						, `t4` varchar(255) NULL -- same as with DEFAULT NULL
						, `t5` varchar(255) NULL DEFAULT ''
						, `t6` varchar(255) NULL DEFAULT NULL
						, `i1` int(11) NOT NULL -- same as with DEFAULT 0
						, `i2` int(11) NOT NULL DEFAULT 0
					--	, `i3` int(11) NOT NULL DEFAULT NULL -- SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value
						, `i4` int(11) NULL -- same as with DEFAULT NULL
						, `i5` int(11) NULL DEFAULT 0
						, `i6` int(11) NULL DEFAULT NULL
						, `last_exec_time` datetime DEFAULT NULL
						, KEY `who` (`who`)
					) ENGINE=MyISAM DEFAULT CHARSET=latin2
			");
			$sth->execute();
			$sth = $pdo->prepare("insert into CRM_NOTIFY (`last_exec_time`) values (NULL)");
			$sth->execute();
			// INSERT INTO CRM_NOTIFY (t1, t2, t2z, t4,   t5, t6,   i1, i2, i4,   i5, i6,   last_exec_time)
			//                 VALUES ('', '', '0', NULL, '', NULL, 0,  0,  NULL, 0,  NULL, NULL);
			$struct = $pdo->getTableStruct('CRM_NOTIFY');
			DBG::_(true, true, "struct", $struct, __CLASS__, __FUNCTION__, __LINE__);
			DBG::_(true, true, "`t2`: is_null(struct[4]['Default'])=(".is_null($struct[4]['Default']).")", null, __CLASS__, __FUNCTION__, __LINE__);
			DBG::_(true, true, "`t4`: is_null(struct[6]['Default'])=(".is_null($struct[6]['Default']).")", null, __CLASS__, __FUNCTION__, __LINE__);
			DBG::_(true, true, "`t5`: is_null(struct[7]['Default'])=(".is_null($struct[7]['Default']).")", null, __CLASS__, __FUNCTION__, __LINE__);
			DBG::_(true, true, "`t6`: is_null(struct[8]['Default'])=(".is_null($struct[8]['Default']).")", null, __CLASS__, __FUNCTION__, __LINE__);
			$expectedStruct = array();
			$expectedStruct['t1'] = array('type'=>'varchar', 'max_length'=>300, 'default_value'=>null);
			//$expectedStruct['t2'] = array('type'=>'int', 'num_precision'=>11, 'default_value'=>null);
			$pdo->assertTableStruct('CRM_NOTIFY', $expectedStruct);
			return;
		}

		$struct = $pdo->getTableStruct('CRM_NOTIFY');
		DBG::_(true, true, "struct", $struct, __CLASS__, __FUNCTION__, __LINE__);

		$expectedStruct = array();
		$expectedStruct['who'] = array('type'=>'varchar', 'max_length'=>20);
		$expectedStruct['when'] = array('type'=>'varchar', 'max_length'=>255);
		$expectedStruct['what'] = array('type'=>'varchar', 'max_length'=>255);
		$expectedStruct['last_exec_time'] = array('type'=>'datetime');
		$expectedStruct['_created'] = array('type'=>'datetime');
		$expectedStruct['tX'] = array('type'=>'varchar', 'max_length'=>255, 'default_value'=>null, 'is_nullable'=>true);

		DBG::_(true, true, "is_null('')=(".is_null('').") is_null()=(".is_null().") is_null(null)=(".is_null(null).")", null, __CLASS__, __FUNCTION__, __LINE__);

		if(0){// keys
			$sth = $pdo->prepare("show keys from CRM_NOTIFY");
			$sth->execute();
			$keys = $sth->fetchAll();
			DBG::_(true, true, "keys", $keys, __CLASS__, __FUNCTION__, __LINE__);
		}

		//$this->reinstallFunctions();
	}

	public function getActiveMessagesForTable($tblName) {
		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'
				and m.`A_STATUS`='WAITING'
				and m.`uiTargetName`='{$tblName}'
				and (m.`userTargetType` in('everyone')
					or (m.`userTargetType`='user' and m.`userTargetName`='{$usrLogin}')
					-- TODO: use 'admin', 'group'
				)
		";
		$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 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()));
	}

}