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'<div style="border:1px solid red; background:red; color:#fff;">';
			echo "Error #" . $e->getCode() .  "|" . $e->getLine() .  ": " . $e->getMessage();
			echo'</div>';
		}
		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 testReinstallTable() {
		$pdo = DB::getPDO();
		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->showCreateStruct('_test_table', $expectedStruct, array('char_encoding'=>'latin2'));
			DBG::_(true, true, "fixedEpectedStruct", $sqlCreate, __CLASS__, __FUNCTION__, __LINE__);
			$pdo->assertTableStruct('_test_table', $expectedStruct, array('char_encoding'=>'latin2'));
			{// force - drop/create
				$pdo->exec("DROP TABLE IF EXISTS _test_table");
				$pdo->exec($sqlCreate);
			}
		}
		if(0){// 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);
		}
	}

	public function reinstall() {
		$sqlList = array();
		$pdo = DB::getPDO();
		$sqlCreate = <<<EOF_SQL_CREATE
			CREATE TABLE IF NOT EXISTS CRM_NOTIFY (
				`who` varchar(20) NOT NULL
				, `when` varchar(255) NOT NULL
				, `what` varchar(255) NOT NULL
				, `last_exec_time` datetime DEFAULT NULL
				, `_created` datetime NOT NULL
				, UNIQUE KEY `uniq_key_1` (`who`,`when`,`what`)
				, KEY `key_who` (`who`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2
EOF_SQL_CREATE;
		/*
			`CRM_NOTIFY`:
				- who - user login
				- when - shedule (once a day, once on houd)
				- what - action type
				- last_exec_time
		*/
		if(1){
			$pdo->exec("DROP TABLE IF EXISTS CRM_NOTIFY");
			$pdo->exec("
					CREATE TABLE IF NOT EXISTS CRM_NOTIFY (
						`who` varchar(20) NOT NULL
						, `when` varchar(255) NOT NULL
						, `what` varchar(255) NOT NULL DEFAULT ''
						, `last_exec_time` datetime
						, KEY `who` (`who`)
					) ENGINE=MyISAM DEFAULT CHARSET=latin2
			");
		}
		{// assertTableStruct CRM_NOTIFY
			$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`)

			DBG::_(true, true, "sqlCreate - raw", $sqlCreate, __CLASS__, __FUNCTION__, __LINE__);
			$sqlCreate = $pdo->showCreateStruct('CRM_NOTIFY', $expectedStruct, array('char_encoding'=>'latin2'));
			DBG::_(true, true, "sqlCreate - generated", $sqlCreate, __CLASS__, __FUNCTION__, __LINE__);
			$pdo->assertTableStruct('CRM_NOTIFY', $expectedStruct, array('char_encoding'=>'latin2'));
			if(0){// force - drop/create
				$pdo->exec("DROP TABLE IF EXISTS CRM_NOTIFY");
				$pdo->exec($sqlCreate);
			}
		}
		{// assertTableStruct CRM_NOTIFY_EXEC_LOG
			// TODO: create table CRM_NOTIFY_EXEC_LOG with action log
		}
		//$this->reinstallFunctions();
	}

}