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

<?php
// @requires $_SERVER['SERVER_NAME']

Lib::loadClass('RouteBase');
Lib::loadClass('Przypomnij');
Lib::loadClass('Request');
Lib::loadClass('TypespecialVariable');
Lib::loadClass('Route_UserMsgs');

/*
# 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

TODO: add after_action for Msgs to user/group
TODO: add after_action for Tasks with special params:
  - Owner - notify after Worker change satus (100% done)
	- Worker - notify after Owner set him as a Worker to task

*/
class Route_Notify extends RouteBase {

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

	public function defaultAction() {
		SE_Layout::gora();
		SE_Layout::menu();
		try {
			$usrLogin = User::getLogin();
			$this->formUserTableReminder($usrLogin);
		} catch (Exception $e) {
			SE_Layout::alert('danger', "Error #" . $e->getCode() .  "|" . $e->getLine() .  ": " . $e->getMessage());
		}
		SE_Layout::dol();
	}

	public function remindersPanelAction() {
		$usrLogin = User::getLogin();
		SE_Layout::gora();
		//SE_Layout::menu();

		{// init args
			$usrLogin = User::getLogin();
			$usrLogin = (isset($_REQUEST['usrLogin']))? V::get('usrLogin', '', $_REQUEST, 'login') : $usrLogin;
			if (!$usrLogin) die("Wrong user login!");

			$notifyTypeList = V::get('reminder', array(), $_REQUEST, 'array');
			if (!$notifyTypeList) {
				$userNotifyList = $this->getUserNotifyList($usrLogin);
				if (empty($userNotifyList)) die("Brak zdefiniowanych powiadomień");
				foreach ($userNotifyList as $notify) { $notifyTypeList[] = $notify['what']; }
			}
		}

		if ('1' == V::get('send', 0, $_POST, 'int')) {
			$this->send($usrLogin, $notifyTypeList);
			$this->markAsSent($usrLogin, $notifyTypeList);
		}

		$urlParts = array();// index.php?_route=Notify&_task=generateUserReminders&usrLogin=magdalena.cichosz&reminder[]=msgs
		$urlParts[] = Request::getPathUri();
		$urlParts[] = 'index.php?_route=Notify&_task=generateUserReminders';
		$urlParts[] = "&usrLogin={$usrLogin}";
		foreach ($notifyTypeList as $reminderType) {
			$urlParts[] = "&reminder[]={$reminderType}";
		}
		$previewLink = implode('', $urlParts);

		$lastSentDate = 'brak';// TODO: getLastSendData($usrLogin);
		$typeSpecialUserLogin = TypespecialVariable::getInstance(-1, '__USER_LOGIN');
		?>
<div class="jumbotron">
	<div class="container">
		<form id="reminderPanelForm" class="form-horizontal" method="POST">
			<div class="form-group">
				<label class="col-sm-2 control-label">User</label>
				<div class="col-sm-10">
				<?php if ($typeSpecialUserLogin) : ?>
					<?php
						$fldName = 'usrLogin';
						$fldParams = array();
						$fldParams['allowCreate'] = false;
						$fldParams['ajaxDataUrlBase'] = "index.php?_route=Notify&_task=typeSpecialUserLogin";
						$fldParams['placeholder'] = 'Szukaj...';
						//$fldParams['ajaxDataUrlBase'] .= "&DBG_TS=3";
						echo $typeSpecialUserLogin->showFormItem($tblID = -1, $fldName, $usrLogin, $fldParams);
					?>
				<?php else : ?>
						<input class="form-control" type="text" name="usrLogin" value="<?php echo $usrLogin; ?>">
				<?php endif; ?>
				</div>
			</div>

			<?php foreach ($this->getFieldValueLabels('what') as $reminderType => $reminderLabel) : ?>
				<?php if ('l_app_all_przypomnij' == $reminderType) continue; ?>
				<div class="col-sm-3">
					<div class="checkbox">
						<label>
							<input type="checkbox"
										 name="reminder[]"
										 <?php if (in_array($reminderType, $notifyTypeList)) : ?>
											 checked="checked"
										 <?php endif; ?>
										 value="<?php echo $reminderType; ?>"> <?php echo $reminderLabel; ?>
						</label>
					</div>
				</div>
			<?php endforeach; ?>

			<div class="col-sm-offset-2 col-sm-10" style="margin-top:10px;padding-top:10px;border-top:1px solid silver;">
				<button class="btn btn-default" onclick="refreshPreview(); return false;">odśwież podgląd</button>
				<button class="btn btn-primary" name="send" value="1">wyślij maila</button>
				data wysłania ostatniej wiadomości do wybranego użytkownika: <?php echo $lastSentDate; ?>
			</div>
		</form>
	</div>
</div>
<script>
function refreshPreview() {
//		$urlParts[] = Request::getPathUri();
//		$urlParts[] = 'index.php?_route=Notify&_task=generateUserReminders';
//		$urlParts[] = "&usrLogin={$usrLogin}";
//		foreach ($notifyTypeList as $reminderType) {
//			$urlParts[] = "&reminder[]={$reminderType}";
//		}
	var frm = document.getElementById('reminderPanelForm'),
			baseUrl = '<?php echo Request::getPathUri(); ?>',
			previewLink = '';

	previewLink = baseUrl + 'index.php?_route=Notify&_task=generateUserReminders';
	previewLink += '&usrLogin=' + frm['usrLogin'].value;
	for (var i in frm['reminder[]']) {
		console.log('frm[reminder[]]['+i+']', frm['reminder[]'][i].value, ' checked:', frm['reminder[]'][i].checked);
		if (frm['reminder[]'][i].checked) {
			previewLink += '&reminder[]=' + frm['reminder[]'][i].value;
		}
	}
	console.log('frm[reminder[]].', frm['reminder[]']);
	console.log('frm[reminder[]].value', frm['reminder[]'].value);
	console.log('previewLink:', previewLink);
	document.getElementById('reminderPreview').src = previewLink;
}
</script>
<div>
	<iframe id="reminderPreview" src="<?php echo $previewLink; ?>" style="width:100%;height:500px;max-height:500px;overflow:scroll;border:2px solid silver"></iframe>
</div>
<?php
		SE_Layout::dol();
	}

	public function typeSpecialUserLoginAction() {
		header("Content-type: application/json");
		$typeSpecialUserId = TypespecialVariable::getInstance(-1, '__USER_LOGIN');
		if (!$typeSpecialUserId) {
			$jsonData = new stdClass();
			$jsonData->message = "TypeSpecial '__USER_LOGIN' not exists";
			echo json_encode($jsonData);
			exit;
		}

		$query = V::get('q', '', $_REQUEST);
		$rawRows = null;
		$jsonData = array();
		$queryParams = array();
		$rows = $typeSpecialUserId->getValuesWithExports($query, $queryParams);
		foreach ($rows as $kID => $vItem) {
			$itemJson = new stdClass();
			$itemJson->id = $vItem->id;
			$itemJson->name = $vItem->param_out;
			if (!empty($vItem->exports)) {
				$itemJson->exports = $vItem->exports;
			}
			$jsonData[] = $itemJson;
		}
		echo json_encode($jsonData);
	}

	public function send($usrLogin, $notifyTypeList, $forceMail = null) {
		DBG::_('DBG_NTF', '>1', "usrLogin", $usrLogin, __CLASS__, __FUNCTION__, __LINE__);
		DBG::_('DBG_NTF', '>1', "reminders", $notifyTypeList, __CLASS__, __FUNCTION__, __LINE__);
		$mainMail = $this->getUserMainMail($usrLogin);

		$msgNote = '';
		if (!empty($forceMail)) {
			$mainMail = $forceMail;
			if (empty($user['aliasy'])) $msgNote .= 'ERROR: Brak lokalnego adresu email' . "\n\n";
		}

		$pattern = "/^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$/i";
		if (!preg_match($pattern, $mainMail)) throw new Exception("Adres email ma błędny format");

		$usrReminders = $this->getUserReminders($usrLogin, $notifyTypeList);
		if (empty($usrReminders)) {
			DBG::_('DBG_NTF', '>0', "Skip usr({$usrLogin}) - no remindres", $usrReminders, __CLASS__, __FUNCTION__, __LINE__);
			return;
		}

		$to = $mainMail;
		$headers = "From: " . "noreply@{$_SERVER['SERVER_NAME']}" . "\r\n";
		$headers .= "MIME-Version: 1.0\r\n";
		$headers .= "Content-Type: text/html; charset=UTF-8\r\n";
		$subject = "Powiadomienia - {$_SERVER['SERVER_NAME']} - " . date("Y-m-d") . " - {$usrLogin}";
		ob_start();
		$this->viewUserReminders($usrReminders, $usrLogin);
		echo "\n\n<br><br>\nWiadomość została wygenerowana autoamtycznie." . "\n\n";
		{
			$baseUrl = "https://{$_SERVER['SERVER_NAME']}/SE";
			if ('biuro.biall-net.pl' == $_SERVER['SERVER_NAME']) $baseUrl = "https://{$_SERVER['SERVER_NAME']}/SE/version-git";
			$notifyLink = "{$baseUrl}/index.php?_route=Notify";
			echo "<br><br>\nPowiadomienia można ustawić na: <a href=\"{$notifyLink}\">{$notifyLink}</a>." . "\n\n";
		}
		$message = ob_get_clean();

		if (!mail($to, $subject, $message, $headers)) throw new Exception("Nie udało się wysłać powiadomienia");
	}

	public function getUserMainMail($usrLogin) {
		$user = null;
		$sth = DB::getPDO()->prepare("
		select u.ID, u.ADM_ACCOUNT as login, u.EMAIL_LOCAL_ACCOUNT_ADDRESS as aliasy
		from ADMIN_USERS u
		where u.ADM_ACCOUNT = :usr_login
		");
		$sth->bindValue(':usr_login', $usrLogin, PDO::PARAM_STR);
		$sth->execute();
		$user = $sth->fetch();
		if (!$user) throw new Exception("User not found");

		if (empty($user['aliasy'])) throw new Exception("Brak lokalnego adresu email");
		$aliasy = explode(' ', trim($user['aliasy']));
		return reset($aliasy);
	}

	public function markAsSent($usrLogin, $notifyTypeList) {
		$sqlReminderTypes = "'" . implode("','", $notifyTypeList) . "'";
		$sth = DB::getPDO()->prepare("
			update CRM_NOTIFY
			set `last_exec_time` = NOW()
			where `who` = :login
				and `what` in({$sqlReminderTypes})
		");
		$sth->bindValue(':login', $usrLogin, PDO::PARAM_STR);
		$sth->execute();
	}

	public function generateUserRemindersAction() {
		$usrLogin = User::getLogin();
		$usrLogin = (isset($_GET['usrLogin']))? V::get('usrLogin', '', $_GET, 'login') : $usrLogin;
		if (!$usrLogin) die("Wrong user login!");

		$notifyTypeList = V::get('reminder', array(), $_GET, 'array');
		if (!$notifyTypeList) {
			$userNotifyList = $this->getUserNotifyList($usrLogin);
			if (empty($userNotifyList)) die("Brak zdefiniowanych powiadomień");
			foreach ($userNotifyList as $notify) { $notifyTypeList[] = $notify['what']; }
		}

		header('Content-Type: text/html; charset="UTF-8"');
		$usrReminders = $this->getUserReminders($usrLogin, $notifyTypeList);
		$this->viewUserReminders($usrReminders, $usrLogin);
	}

	public function getUserReminders($usrLogin, $notifyTypeList) {
		$usrReminders = array();
		foreach ($notifyTypeList as $notifyType) {
			$listTodo = $this->getTodoForUserReminder($usrLogin, $notifyType);
			if (!empty($listTodo)) $usrReminders[$notifyType] = $listTodo;
		}
		return $usrReminders;
	}

	public function viewUserReminders($usrReminders, $usrLogin) {
		echo '<html>';
		echo '<body>';
		echo '<div>';
		?>
<h1 style="<?php echo $this->inlineCss('h1'); ?>">Powiadomienia dla <code><?php echo $usrLogin; ?></code></h1>
<?php foreach ($usrReminders as $reminderType => $listTodo) : ?>
	<div style="<?php echo $this->inlineCss('header'); ?>"><strong><?php echo $this->printValue('what', $reminderType); ?></strong></div>
	<table rules="all" cellspacing="0" style="border-color:#bbb;" cellpadding="8">
	<?php $lp = 0; foreach ($listTodo as $todo) : ?>
		<tr>
			<td style="<?php echo $this->inlineCss('td.lp'); ?>"><?php echo ++$lp; ?></td>
			<td style="<?php echo $this->inlineCss('td.' . $todo['_l_app_class']); ?>">
				<span style="<?php echo $this->inlineCss('l_app_date.' . $todo['_l_app_class']); ?>"><?php echo $todo['_l_app_date']; ?></span>
				<br><i style="<?php echo $this->inlineCss('l_app.target'); ?>"><?php echo $todo['_l_app']; ?></i>
				<?php if (V::get('ID', '', $todo) > 0) : ?>
					<br><a href="<?php echo $this->editLink($todo); ?>" target="_blank" style="<?php echo $this->inlineCss('a'); ?>">Edytuj rekord <?php echo $todo['ID']; ?></a>
				<?php elseif (V::get('idMsg', '', $todo) > 0) : ?>
					<br><a href="<?php echo $this->readMsgLink($usrLogin, $todo); ?>" target="_blank" style="<?php echo $this->inlineCss('a'); ?>">Przeczytaj</a>
				<?php endif; ?>
			</td>
			<td style="<?php echo $this->inlineCss('td.info'); ?>"><?php echo $todo['L_APPOITMENT_INFO']; ?></td>
			<td style="<?php echo $this->inlineCss('td.record-info'); ?>">
				<?php echo $todo['_title']; ?>
			</td>
<?php if (V::get('DBG', '', $_GET)) : ?>
			<td style="<?php echo $this->inlineCss('td.record-desc'); ?>"><pre style="border:1px solid red;max-height:80px;overflow:scroll"><?php print_r($todo); ?></pre></td>
<?php endif; ?>
		</tr>
	<?php endforeach; ?>
	</table>
<?php endforeach; ?>
<?php
		echo '</div>';// .container
		echo '</body>';
		echo '</html>';
	}

	public function editLink($todo) {
		$idTblZasob = 0;
		$idRecord = 0;
		if ('zasob' == $todo['_task_type']
				|| 'proces' == $todo['_task_type']
				|| 'projekt' == $todo['_task_type']
				|| 'koresp' == $todo['_task_type']
				|| 'task' == $todo['_task_type']
			 ) {
			$idTblZasob = $todo['_idZasobTable'];
			$idRecord = $todo['ID'];
		}
		if (!$idTblZasob || !$idRecord) return '#';

		$urlParts = array();
		$urlParts[] = (!empty($_SERVER['HTTP_X_FORWARDED_PROTO']))? $_SERVER['HTTP_X_FORWARDED_PROTO'] : V::get('REQUEST_SCHEME', 'http', $_SERVER);
		$urlParts[] = '://';
		$urlParts[] = $_SERVER['SERVER_NAME'];
		$urlParts[] = $_SERVER['SCRIPT_URL'];
		// https://biuro.biall-net.pl/dev-pl/se-master/index.php?MENU_INIT=VIEWTABLE_AJAX&ZASOB_ID=1466#EDIT/3954
		$urlParts[] = "?MENU_INIT=VIEWTABLE_AJAX&ZASOB_ID={$idTblZasob}#EDIT/{$idRecord}";
		return implode("", $urlParts);
	}

	public function readMsgLink($usrLogin, $todo) {
		$idMsg = 0;
		if ('msg' == $todo['_task_type']) {
			$idMsg = $todo['idMsg'];
		}
		if (!$idMsg) return '#' . json_encode($todo);
		if (!$idMsg) return '#';

		$urlParts = array();
		$urlParts[] = Request::getPathUri();
		$urlParts[] = 'index.php';
		$urlParts[] = "?_route=UserMsgs";
		$urlParts[] = "&_task=read";
		$urlParts[] = "&usrLogin={$usrLogin}";
		$urlParts[] = "&id={$idMsg}";
		return implode("", $urlParts);
	}

	public function inlineCss($cssSelector) {
		$baseCss = array();
		$baseCss['font-family'] = 'Arial, Helvetica, sans-serif';
		$baseCss['font-size'] = 'small';
		$baseCss['color'] = '#333';

		$customCss = array(
			'h1' => array(),
			'header' => array('background' => '#eee', 'padding' => '4px 10px', 'margin-top' => '6px'),
			'l_app.target' => array('color' => '#999', 'white-space' => 'nowrap'),
			'td.date-base' => array('white-space' => 'nowrap'),
			'td.lp' => array('color' => 'silver'),
			'l_app_date-base' => array('white-space' => 'nowrap'),
			'a' => array('color' => '#337ab7', 'text-decoration' => 'none')
		);

		$css = $baseCss;
		if (array_key_exists($cssSelector, $customCss)) $css = V::extend($css, $customCss[$cssSelector]);
		if ('td.date-' == substr($cssSelector, 0, 8)) {
			$css = V::extend($css, $customCss['td.date-base']);
			$css = V::extend($css, array('color' => $this->getTodoDateCssColor(substr($cssSelector, 3))));
		}
		if ('l_app_date.date-' == substr($cssSelector, 0, 16)) {
			$css = V::extend($css, $customCss['l_app_date-base']);
			$css = V::extend($css, array('color' => $this->getTodoDateCssColor(substr($cssSelector, 11))));
		}

		$inlineCss = array();
		foreach ($css as $cssPropName => $cssValue) {
			if (true === $cssValue) {
				if (!array_key_exists($cssPropName, $baseCss)) continue;
				$cssValue = $baseCss[$cssPropName];
			}
			$inlineCss[] = "{$cssPropName}:{$cssValue}";
		}
		return implode(";", $inlineCss);
	}

	public function getTodoDateCssColor($lAppClass) {
		$color = '#000';
		switch ($lAppClass) {
			case 'date-PO_TERMINIE': $color = '#FD4242'; break;
			case 'date-DZISIAJ': $color = '#34B934'; break;
			case 'date-W_CIAGU_7_DNI': $color = '#C58B1F'; break;
			case 'date-PO_7_DNIACH': $color = '#87847D'; break;
		}
		return $color;
	}

	public function getTodoForUserReminder($usrLogin, $reminderWhat) {
		$listTodo = array();
		if ('msgs' == $reminderWhat) {
			$routeMsgs = Router::getRoute('UserMsgs');
			$routeMsgs->_listLimit = 50;
			$msgs = $routeMsgs->getMsgs('unread', $usrLogin);
			foreach ($msgs as $idMsg => $msg) {
				$todo = array();
				$todo['_task_type'] = 'msg';
				// [userTargetType] => group
				// [userTargetName] => 2948
				$todo['_l_app'] = '';
				if ('group' == $msg['_raw']->userTargetType) {
					$todo['_l_app'] = "do grupy {$msg['_raw']->userTargetName}";
				} else if ('user' == $msg['_raw']->userTargetType) {
					$todo['_l_app'] = "do {$msg['_raw']->userTargetName}";
				}
				$todo['idMsg'] = $idMsg;
				$todo['_l_app_date'] = substr($msg['_raw']->A_RECORD_CREATE_DATE, 0, 10);
				$todo['_l_app_class'] = 'date-PO_TERMINIE';
				$todo['L_APPOITMENT_INFO'] = $msg['message'];
				// $msg['_raw']->A_STATUS == 'WAITING'  => Unread
				// $msg['_raw']->A_STATUS == 'NORMAL'   => Read
				// $msg['_raw']->A_STATUS == 'OFF_SOFT' => Trash
				$msgInfo = '<i style="color:silver">' . "Wysłane {$msg['_raw']->A_RECORD_CREATE_DATE} przez {$msg['_raw']->A_RECORD_CREATE_AUTHOR}" . '</i>';
				if ('NORMAL' == $msg['_raw']->A_STATUS) $msgInfo .= '<br><i style="color:silver">' . "Przeczytane {$msg['_raw']->A_RECORD_UPDATE_DATE} przez {$msg['_raw']->A_RECORD_UPDATE_AUTHOR}" . '</i>';
				$todo['_title'] = $msgInfo;
				$listTodo[] = $todo;
			}
		}
		else if ('l_app_projekty' == $reminderWhat) {
			$tasks = $this->getPrzypomnijTasks($usrLogin);
			foreach ($tasks as $task) {
				if (empty($task['_l_app_date']) || '0000-00-00' == $task['_l_app_date']) continue;
				if ('projekt' != $task['_task_type']) continue;
				//$task['DESC'] = $task['A_PROBLEM_DESC'];
				$listTodo[] = $task;
			}
		}
		else if ('l_app_koresp' == $reminderWhat) {
			$tasks = $this->getPrzypomnijTasks($usrLogin);
			foreach ($tasks as $task) {
				if (empty($task['_l_app_date']) || '0000-00-00' == $task['_l_app_date']) continue;
				if ('koresp' != $task['_task_type']) continue;
				//$task['DESC'] = $task['A_PROBLEM_DESC'];
				$listTodo[] = $task;
			}
		}
		else if ('l_app_zadania' == $reminderWhat) {
			$tasks = $this->getPrzypomnijTasks($usrLogin);
			foreach ($tasks as $task) {
				if (empty($task['_l_app_date']) || '0000-00-00' == $task['_l_app_date']) continue;
				if ('task' != $task['_task_type']) continue;
				//$task['DESC'] = $task['A_PROBLEM_DESC'];
				$listTodo[] = $task;
			}
		}
		else if ('l_app_procesy' == $reminderWhat) {
			$tasks = $this->getPrzypomnijTasks($usrLogin);
			foreach ($tasks as $task) {
				if (empty($task['_l_app_date']) || '0000-00-00' == $task['_l_app_date']) continue;
				if ('proces' != $task['_task_type']) continue;
				$listTodo[] = $task;
			}
		}
		else if ('l_app_zasoby' == $reminderWhat) {
			$tasks = $this->getPrzypomnijTasks($usrLogin);
			foreach ($tasks as $task) {
				if (empty($task['_l_app_date']) || '0000-00-00' == $task['_l_app_date']) continue;
				if ('zasob' != $task['_task_type']) continue;
				$listTodo[] = $task;
			}
		}
		return $listTodo;
	}

	public function getPrzypomnijTasks($usrLogin) {
		static $_tasks = array();
		if (empty($_tasks)) {
			$przypomnij = new Przypomnij();
			$przypomnij->setRecurseLimit(3);
			$przypomnij->fetchData($usrLogin);
			$przypomnij->setFltrUser($usrLogin);

			$tasks = $przypomnij->getTasksByDate();
			{
				$usrGroupNames = array();
				//$usrGroupNames = User::getLdapGroupsNames();
				Lib::loadClass('UsersLdapHelper');
				$ldapGroups = UsersLdapHelper::getUserGroups($usrLogin, 3);
				foreach ($ldapGroups as $kID => $vLDAPGroup) {
					$usrGroupNames[$kID] = $vLDAPGroup->cn;
				}
			}
			foreach ($tasks as $task) {
				if (!$task->_show) continue;
				if (
					$task->A_CLASSIFIED != ''
					and !(in_array($task->A_CLASSIFIED, $usrGroupNames))
					and $task->A_ADM_COMPANY != ''
					and !(in_array($task->A_ADM_COMPANY, $usrGroupNames))
					// !($allowedUsers[$task->_l_app]) - to jest bez sensu - wystarczy widocznosc sprawy?
				) {
					continue;
				}
				$task->_l_app_class = $przypomnij->getTaskDateFltrType($task->_l_app_date);
				$task->_idZasobTable = $przypomnij->getZasobIdByType($task->_task_type);

				$_tasks[] = (array)$task;
			}
		}
		return $_tasks;
	}

	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) {
		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());
			}
		}
		$userNotifyList = $this->getUserNotifyList($usrLogin);
		$userMail = $this->getUserMainMail($usrLogin);
		?>
	<h1>Powiadomienia dla <code><?php echo $usrLogin; ?></code></h1>
	<p><i>Będą wysyłane na adres: <?php echo $userMail; ?></i></p>
	<?php if (empty($userNotifyList)) : ?>
		<?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 ($userNotifyList as $notify) : ?>
					<tr>
						<td><?php echo $this->printValue('what', $notify['what']); ?></td>
						<td><?php echo $this->printValue('when', $notify['when']); ?></td>
						<td><?php echo $notify['_created']; ?></td>
						<td><?php echo ($notify['last_exec_time'])? $notify['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 $notify['what']; ?>">
								<input type="hidden" name="when" value="<?php echo $notify['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 getAllPrzypomnijTypes() {
		$allPrzypomnijTypes = array();
		$allPrzypomnijTypes[] = 'msgs';
		$allPrzypomnijTypes[] = 'l_app_projekty';
		$allPrzypomnijTypes[] = 'l_app_koresp';
		$allPrzypomnijTypes[] = 'l_app_zadania';
		$allPrzypomnijTypes[] = 'l_app_procesy';
		$allPrzypomnijTypes[] = 'l_app_zasoby';
		return $allPrzypomnijTypes;
	}

	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();
		if ('l_app_all_przypomnij' == $what) {
			$types = $this->getAllPrzypomnijTypes();
			$sqlValues = array();
			$sqlWho = $pdo->quote($usrLogin, PDO::PARAM_STR);
			$sqlWhen = $pdo->quote($when, PDO::PARAM_STR);
			foreach ($types as $type) {
				$sqlWhat = $pdo->quote($type, PDO::PARAM_STR);
				$sqlValues[] = "( {$sqlWho}, {$sqlWhat}, {$sqlWhen}, NOW() )";
			}
			$sqlValues = implode(", ", $sqlValues);
			$pdo->exec("
				insert ignore into `CRM_NOTIFY` (
					`who`,
					`what`,
					`when`,
					`_created`
				) values {$sqlValues}
			");
		} else {
			$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);
//		try {
				$sth->execute();
//		} catch (PDOException $e) {
//			throw new Exception("Błąd bazy danych: " . json_encode($e->errorInfo));
//		} catch (Exception $e) {
//			throw $e;
//		}
		}
	}

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

	public function getTodoList($limit = 10) {
		// $sth = DB::getPDO()->prepare("
		// 	select n.`who`, n.`when`, min(n.last_exec_time) as min_last_exec_time
		// 	from `CRM_NOTIFY` n
		// 	group by n.`who`, n.`when`
		// 	having min_last_exec_time is null or min_last_exec_time < NOW()
		// 	order by min_last_exec_time
		// 	limit :limit
		// ");
		// $sth->bindValue(':limit', $limit, PDO::PARAM_INT);
		$pdo = DB::getPDO();
		$sqlWhenFltr = "
			(
				n.`when` = 'once_a_day' and (
						n.last_exec_time is null
						or DATE(n.last_exec_time) < CURDATE()
					)
				-- TODO add `or` with another `when` values
			)
		";
		$sth = $pdo->prepare("
			select u.ADM_ACCOUNT
			from ADMIN_USERS u
				join CRM_NOTIFY n on(n.`who` = u.ADM_ACCOUNT and {$sqlWhenFltr})
			where u.A_STATUS = 'NORMAL'
				and u.EMPLOYEE_TYPE = 'Pracownik'
				and n.`who` is not null
			group by u.ADM_ACCOUNT
			limit :limit
		");
		$sth->bindValue(':limit', $limit, PDO::PARAM_INT);
		$sth->execute();
		$rawUsers = $sth->fetchAll();
		if (empty($rawUsers)) return array();
		$sqlUsers = array(); foreach ($rawUsers as $row) { $sqlUsers[] = $pdo->quote($row['ADM_ACCOUNT'], PDO::PARAM_STR); }
		$sqlUsers = implode(", ", $sqlUsers);
		$sth = $pdo->prepare("
			select n.`who`, n.`when`, n.`what`, UNIX_TIMESTAMP(n.last_exec_time) as last_exec_time
			from CRM_NOTIFY n
			where n.`who` in({$sqlUsers})
				and {$sqlWhenFltr}
		");
		$sth->execute();
		$rawReminders = $sth->fetchAll();
		$reminders = array();
		$timeToday = mktime(0, 0, 0, date('n'), date('j'), date('Y'));
		//$timeTomorrow = mktime(0, 0, 0, date('n'), date('j') + 1, date('Y'));
		DBG::_('DBG_NTF', '>1', "rawReminders (today:{$timeToday})", $rawReminders, __CLASS__, __FUNCTION__, __LINE__);
		foreach ($rawReminders as $row) {
			$user = $row['who'];
			$when = $row['when'];
			$what = $row['what'];
			$last_exec_time = $row['last_exec_time'];
			if ('once_a_day' == $when) {
				if ($last_exec_time >= $timeToday) {
					DBG::_('DBG_NTF', '>1', "skip {$last_exec_time} for {$what} at {$when}", $row, __CLASS__, __FUNCTION__, __LINE__);
					continue;
				} else {
					//DBG::_(true, true, "TODO: else {$last_exec_time} >= {$timeToday}", $row, __CLASS__, __FUNCTION__, __LINE__);
				}
			} else {
				DBG::_('DBG_NTF', '>0', "TODO: unimplemented when = '{$when}'", $row, __CLASS__, __FUNCTION__, __LINE__);
				continue;
			}
			$last_date = ($last_exec_time)? date("Y-m-d H:i:s", $last_exec_time) : null;
			if (!isset($reminders[$user])) $reminders[$user] = array();
			if (!isset($reminders[$user][$when])) $reminders[$user][$when] = array();
			$reminders[$user][$when][$what] = $last_date;
		}
		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();
	}

}