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();
		try {
			$this->formUserTableReminder();
		} catch (Exception $e) {
			SE_Layout::alert('danger', "Error #" . $e->getCode() .  "|" . $e->getLine() .  ": " . $e->getMessage());
		}
		SE_Layout::dol();
	}

	public function removeUserTableReminder($args) {
		$usrLogin = User::getLogin();
		$who  = V::validate('who',  $args, array('type'=>'word', 'equal'=>$usrLogin, 'error_msg_equal'=>"Brak uprawnień do wprowadzania zmian w powiadomieniach innych użytwkoników"));
		$what = V::validate('what', $args, array('type'=>'word', 'not_empty'=>true, 'max_length'=>'255', 'values'=>$this->getFieldValues('what')));
		$when = V::validate('when', $args, array('type'=>'word', 'not_empty'=>true, 'max_length'=>'255', 'values'=>$this->getFieldValues('when')));

		if ($usrLogin != $who) throw new Exception("Brak uprawnień do wprowadzania zmian w powiadomieniach innych użytwkoników");
		$pdo = DB::getPDO();
		$sth = $pdo->prepare("
			delete from `CRM_NOTIFY`
			where `who` = :who
				and `what` = :what
				and `when` = :when
		");
		$sth->bindValue(':who', $usrLogin, PDO::PARAM_STR);
		$sth->bindValue(':what', $what, PDO::PARAM_STR);
		$sth->bindValue(':when', $when, PDO::PARAM_STR);
		$sth->execute();
	}

	public function formUserTableReminder() {
		$usrLogin = User::getLogin();
		echo '<div class="container">';
		$subTask = V::get('_subTask', '', $_POST);
		if ('_add_table_reminder' == $subTask) {
			try {
				$this->addUserTableReminder($usrLogin, $_POST);
				SE_Layout::alert('info', "Dodano powiadomienie");
			} catch (PDOException $e) {
				if (!empty($e->errorInfo) && !empty($e->errorInfo[1]) && '1062' == $e->errorInfo[1]) {
					SE_Layout::alert('warning', "Powiadomienie tego typu zostało już wcześniej dodane");
				} else {
					SE_Layout::alert('danger', "Error #" . $e->getCode() .  "|" . $e->getLine() .  ": " . $e->getMessage() . '<br>' . "Info: " . json_encode($e->errorInfo));
				}
			} catch (Exception $e) {
				SE_Layout::alert('danger', "Error #" . $e->getCode() .  "|" . $e->getLine() .  ": " . $e->getMessage());
			}
		}
		else if ('_rm_table_reminder' == $subTask) {
			try {
				$this->removeUserTableReminder($_POST);
				SE_Layout::alert('info', "Usunięto powiadomienie");
			} catch (Exception $e) {
				SE_Layout::alert('danger', "Error #" . $e->getCode() .  "|" . $e->getLine() .  ": " . $e->getMessage());
			}
		}
		$userReminders = $this->getUserReminders($usrLogin);
		?>
	<h1>Powiadomienia dla <code><?php echo $usrLogin; ?></code></h1>
	<?php if (empty($userReminders)) : ?>
		<?php SE_Layout::alert('warning', "Brak zdefiniowanych powiadomień"); ?>
	<?php else : ?>
		<table class="table table-hovered">
			<thead>
				<tr>
					<th>rodzaj</th>
					<th>jak często</th>
					<th>utworzony</th>
					<th>ostatnio uruchomiony</th>
					<th></th>
				</tr>
			</thead>
			<tbody>
				<?php foreach ($userReminders as $reminder) : ?>
					<tr>
						<td><?php echo $this->printValue('what', $reminder['what']); ?></td>
						<td><?php echo $this->printValue('when', $reminder['when']); ?></td>
						<td><?php echo $reminder['_created']; ?></td>
						<td><?php echo ($reminder['last_exec_time'])? $reminder['last_exec_time'] : '<i>brak danych</i>'; ?></td>
						<td>
							<form method="post" class="form-inline">
								<input type="hidden" name="_subTask" value="_rm_table_reminder">
								<input type="hidden" name="who" value="<?php echo $usrLogin; ?>">
								<input type="hidden" name="what" value="<?php echo $reminder['what']; ?>">
								<input type="hidden" name="when" value="<?php echo $reminder['when']; ?>">
								<button class="btn btn-link btn-xs" onclick="return confirm('Czy usunąć powiadomienie?')" type="submit"><i class="glyphicon glyphicon-remove" style="color:red;opacity:0.4;"></i></button>
							</form>
						</td>
					</tr>
				<?php endforeach; ?>
			</tbody>
		</table>
	<?php endif; ?>

	<hr>
	<h4>Dodaj powiadomienia:</h4>
	<form method="post" class="form-inline">
		<input type="hidden" name="_subTask" value="_add_table_reminder">
		<label>rodzaj:</label>
		<select type="select" name="what" class="form-control">
			<option value="">[ Wybierz ]</option>
			<?php foreach ($this->getFieldValueLabels('what') as $value => $label) : ?>
				<option value="<?php echo $value; ?>"><?php echo $label; ?></option>
			<?php endforeach; ?>
		</select>
		<label>jak często:</label>
		<select type="select" name="when" class="form-control">
			<option value="">[ Wybierz ]</option>
			<option value="once_a_day">Raz dziennie</option>
		</select>
		<input type="submit" value="Dodaj" class="btn btn-primary">
	</form>
		<?php
		echo '</div>';// .container
	}

	public function printValue($fldName, $argValue) {
		if ('what' == $fldName || 'when' == $fldName) {
			$valueLabels = $this->getFieldValueLabels($fldName);
			return V::get($argValue, $argValue, $valueLabels);
		}
		return $argValue;
	}

	public function getFieldValueLabels($fldName) {
		$valueLabels = array();
		if ('what' == $fldName) {
			$valueLabels['msgs'] = "wiadomości";
			$valueLabels['l_app_projekty'] = "projekty";
			$valueLabels['l_app_koresp'] = "korespondencja";
			$valueLabels['l_app_zadania'] = "zadania";
			$valueLabels['l_app_procesy'] = "procesy";
			$valueLabels['l_app_zasoby'] = "zasoby";
			$valueLabels['l_app_all_przypomnij'] = "*wszystkie";
		} else if ('when' == $fldName) {
			$valueLabels['once_a_day'] = "Raz dziennie";
		}
		return $valueLabels;
	}

	public function getFieldValues($fldName) {
		$valueLabels = $this->getFieldValueLabels($fldName);
		return array_keys($valueLabels);
	}

	public function addUserTableReminder($usrLogin, $args) {
		$what_values = $this->getFieldValues('what');
		$what = V::validate('what', $args, array('type'=>'word', 'not_empty'=>true, 'max_length'=>'255', 'values'=>$what_values));
		$when_values = $this->getFieldValues('when');
		$when = V::validate('when', $args, array('type'=>'word', 'not_empty'=>true, 'max_length'=>'255', 'values'=>$when_values));
		$pdo = DB::getPDO();
		$sth = $pdo->prepare("
			insert into `CRM_NOTIFY` (
				`who`,
				`what`,
				`when`,
				`_created`
			) values (
				:who,
				:what,
				:when,
				NOW()
			)
		");
		$bindValues = array();
		$bindValues['who'] = array($usrLogin, PDO::PARAM_STR);
		$bindValues['what'] = array($what, PDO::PARAM_STR);
		$bindValues['when'] = array($when, PDO::PARAM_STR);
		$pdo->bindValues($sth, $bindValues);
		//DBG::_(true, true, "sql", $pdo->getRawSql($sth), __CLASS__, __FUNCTION__, __LINE__);
//		try {
			$sth->execute();
//		} catch (PDOException $e) {
//			throw new Exception("Błąd bazy danych: " . json_encode($e->errorInfo));
//		} catch (Exception $e) {
//			throw $e;
//		}
	}

	public function getUserReminders($usrLogin) {
		$pdo = DB::getPDO();
		$sth = $pdo->prepare("
			select n.*
			from `CRM_NOTIFY` n
			where n.`who` = '{$usrLogin}'
		");
		$sth->execute();
		$reminders = $sth->fetchAll();
		return $reminders;
	}

	public function reinstallAction() {
		try {
			$force = ('true' == V::get('_force', '', $_GET));
			$this->reinstall($force);
		} 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($force = false) {
		$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($force){
			$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 `key_who` (`who`)
						, UNIQUE KEY `uniq_key_1` (`who`,`when`,`what`)
					) 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();
	}

}