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

<?php

Lib::loadClass('RouteBase');

class Route_WindykacjaZestawienia extends RouteBase {

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

	public function defaultAction() {
		SE_Layout::gora();
		SE_Layout::menu();
		echo'TODO: Main menu...';
		SE_Layout::dol();
	}

	public function sendMassAction() {
		$selectedMonth = V::get('zest_month', date("Y-m"), $_REQUEST);
		$f_type = V::get('f_type', '', $_REQUEST);
		$f_day = V::get('f_day', '', $_REQUEST);
		$f_status = V::get('f_status', '', $_REQUEST);

		SE_Layout::gora();
		SE_Layout::menu();
		$this->_sendMassMenu($selectedMonth);
		$this->_showSendMass($selectedMonth);
		if (!empty($f_day) && !empty($f_status)) {
			if ('MAIL' == $f_type) {
				$this->_showSendMassMailDetails($f_day, $f_status);
			} else if ('SMS' == $f_type) {
				$this->_showSendMassSmsDetails($f_day, $f_status);
			}
		}
		SE_Layout::dol();
	}

	private function _showSendMass($selectedMonth) {
		$zest = array();
		if (strlen($selectedMonth) != 7) return;
		$db = DB::getDB();
		$sql = <<<SQL
(
	select statsMail.`TYPE`
		, statsMail.`A_RECORD_UPDATE_AUTHOR`
		, statsMail.`_wind_save`
		, statsMail.`STATUS`
		, count(*) as cnt
	from (
		select 'MAIL' as `TYPE`
			, h.`A_RECORD_UPDATE_AUTHOR`
			, substr(h.`A_RECORD_UPDATE_DATE`, 1, 10) as `_wind_save`
		--	, hc.`STATUS_MAIL` as `STATUS`
			, IF(hc.`REQUEST_STATUS_MAIL`='CONFIRM_SENT_MAIL' and hc.`STATUS_MAIL`='NONE'
					, 'WAITING'
					, IF(hc.`REQUEST_STATUS_MAIL`='CONFIRM_SENT_MAIL' and hc.`STATUS_MAIL`='SENT_MAIL'
						, 'SENT'
						, IF(hc.`REQUEST_STATUS_MAIL`='NONE' and hc.`STATUS_MAIL`='NONE'
							, 'UNDELIVERED'
							, 'UNKNOWN'
						)
					)
				) as `STATUS`
		from `SES_USERS2`.`USERS2_WINDYKACJA_STATUS_HIST` h
			join `SES_USERS2`.`USERS2_WINDYKACJA_STATUS` w on(w.`ID`=h.`ID_USERS2`)
			join `billing2013`.`HIST_CONTACTS` hc on(hc.`ID`=h.`LAST_MAIL_MSG_ID`)
		where h.`LAST_MAIL_MSG_ID`>0
			and h.`A_RECORD_UPDATE_DATE` like '{$selectedMonth}-%'
	) as statsMail
	group by statsMail.`A_RECORD_UPDATE_AUTHOR`, statsMail.`_wind_save`, statsMail.`STATUS`
)
union
(
	select statsSms.`TYPE`
		, statsSms.`A_RECORD_UPDATE_AUTHOR`
		, statsSms.`_wind_save`
		, statsSms.`STATUS`
		, count(*) as cnt
	from (
		select 'SMS' as `TYPE`
			, h.`A_RECORD_UPDATE_AUTHOR`
			, substr(h.`A_RECORD_UPDATE_DATE`, 1, 10) as `_wind_save`
			, IF(hc.`REQUEST_STATUS_SMS`='SENT_SMS' and hc.`STATUS_SMS`='NONE'
					, 'WAITING'
					, IF(hc.`REQUEST_STATUS_SMS`='CONFIRM_SENT_SMS' and hc.`STATUS_SMS`='SENT_SMS'
						, 'MONITOR'
						, IF(hc.`REQUEST_STATUS_SMS`='NONE' and hc.`STATUS_SMS`='CONFIRM_SENT_SMS'
							, 'SENT'
							, IF(hc.`REQUEST_STATUS_SMS`='NONE' and hc.`STATUS_SMS` in('UNDELIVERED','NONE')
								, 'UNDELIVERED'
								, IF(hc.`REQUEST_STATUS_SMS`='NONE' and hc.`STATUS_SMS`='UNKNOWN'
									, 'OFF_SOFT'
									, 'UNKNOWN'
								)
							)
						)
					)
				) as `STATUS`
		from `SES_USERS2`.`USERS2_WINDYKACJA_STATUS_HIST` h
			join `SES_USERS2`.`USERS2_WINDYKACJA_STATUS` w on(w.`ID`=h.`ID_USERS2`)
			join `billing2013`.`HIST_CONTACTS` hc on(hc.`ID`=h.`LAST_SMS_MSG_ID`)
		where h.`LAST_SMS_MSG_ID` > 0
			and h.`A_RECORD_UPDATE_DATE` like '{$selectedMonth}-%'
	) as statsSms
	group by statsSms.`A_RECORD_UPDATE_AUTHOR`, statsSms.`_wind_save`, statsSms.`STATUS`
)
order by `_wind_save` ASC, `TYPE`
;
SQL;
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
		/*
WAITING: REQUEST_STATUS_MAIL='CONFIRM_SENT_MAIL’ && STATUS_MAIL='NONE’ - mail czeka na wysłanie
SENT: REQUEST_STATUS_MAIL='CONFIRM_SENT_MAIL’ && STATUS_MAIL=’SENT_MAIL’ - mail został wysłany
UNDELIVERED: REQUEST_STATUS_MAIL=’NONE’ && STATUS_MAIL='NONE’ - mail nie został i nie zostanie wysłany (np. brak/błędny adres mailowy)

WAITING: REQUEST_STATUS_SMS=’SENT_SMS’ && STATUS_SMS=’NONE' - sms do wysłania
MONITOR: REQUEST_STATUS_SMS='CONFIRM_SENT_SMS’ && STATUS_SMS=’SENT_SMS’ - sms wysłany, oczekuje na potwierdzenie dostarczenia
SENT: REQUEST_STATUS_SMS=’NONE’ && STATUS_SMS='CONFIRM_SENT_SMS' - sms dostarczony
UNDELIVERED: REQUEST_STATUS_SMS=’NONE’ && STATUS_SMS='UNDELIVERED' - sms niedostarczony (musiał wystąpić jakiś błąd)
UNDELIVERED: REQUEST_STATUS_SMS=’NONE’ && STATUS_SMS=‚NONE’ (obecnie nieużywana już kombinacja) - sms niedostarczony (musiał wystąpić jakiś błąd)
OFF_SOFT: REQUEST_STATUS_SMS=’NONE’ && STATUS_SMS='UNKNOWN' - sms wysłany, ale nie wiadomo czy dostarczony (nie uzyskano odpowiedzi ze strony platformy na temat dostarczenia sms)

*/
			$r->STATUS_INFO = '';
			if ('MAIL' == $r->TYPE) {
				if ('WAITING' == $r->STATUS) {
					$r->STATUS_INFO = 'do wysłania';
				} else if ('SENT' == $r->STATUS) {
					$r->STATUS_INFO = 'wysłano';
				} else if ('UNDELIVERED' == $r->STATUS) {
					$r->STATUS_INFO = 'niewysłano (np. brak/błędny adres mailowy)';
				}
			} else if ('SMS' == $r->TYPE) {
				if ('WAITING' == $r->STATUS) {
					$r->STATUS_INFO = 'do wysłania';
				} else if ('MONITOR' == $r->STATUS) {
					$r->STATUS_INFO = 'oczekiwanie na potwierdzenie odbioru';
				} else if ('SENT' == $r->STATUS) {
					$r->STATUS_INFO = 'wysłano';
				} else if ('UNDELIVERED' == $r->STATUS) {
					$r->STATUS_INFO = 'niedostarczono';
				}
			}
			$zest[] = $r;
		}
		$this->_showCss();
?>
<div class="container">
	<table class="table table-bordered">
		<thead>
			<th>Typ</th>
			<th>User</th>
			<th>Data</th>
			<th>Status</th>
			<th>Ilość</th>
			<th>Szczegóły</th>
		</thead>
		<tbody>
			<?php foreach ($zest as $r) : ?>
				<?php
		$link = "index.php?_route=WindykacjaZestawienia&_task=sendMass&zest_month={$selectedMonth}&f_type={$r->TYPE}&f_day={$r->_wind_save}&f_status={$r->STATUS}";
				?>
				<tr>
					<td><?php echo $r->TYPE; ?></td>
					<td><?php echo $r->A_RECORD_UPDATE_AUTHOR; ?></td>
					<td><?php echo $r->_wind_save; ?></td>
					<td class="status_cell-<?php echo $r->STATUS; ?>" title="<?php echo $r->STATUS_INFO; ?>"><?php echo $r->STATUS; ?> <i class="glyphicon glyphicon-info-sign"></i></td>
					<td><?php echo $r->cnt; ?></td>
					<td><a href="<?php echo $link; ?>">szczegóły</a></td>
				</tr>
			<?php endforeach; ?>
		</tbody>
</table>
</div>
<?php
	}

	private function _showSendMassMailDetails($f_day, $f_status) {
		if (empty($f_day) || strlen($f_day) != 10
				|| empty($f_status) || strlen($f_status) > 20) {
			echo 'Wrong params';
			return;
		}
		$zest = array();
		$db = DB::getDB();
		$sql = <<<SQL
	select statsMail.`TYPE`
		, statsMail.`A_RECORD_UPDATE_AUTHOR`
		, statsMail.`_wind_save`
		, statsMail.`STATUS`
		, statsMail.`ID_BILLING_USERS`
		, '' as `STATUS_INFO`
	from (
		select 'MAIL' as `TYPE`
			, h.`A_RECORD_UPDATE_AUTHOR`
			, substr(h.`A_RECORD_UPDATE_DATE`, 1, 10) as `_wind_save`
			, IF(hc.`REQUEST_STATUS_MAIL`='CONFIRM_SENT_MAIL' and hc.`STATUS_MAIL`='NONE'
					, 'WAITING'
					, IF(hc.`REQUEST_STATUS_MAIL`='CONFIRM_SENT_MAIL' and hc.`STATUS_MAIL`='SENT_MAIL'
						, 'SENT'
						, IF(hc.`REQUEST_STATUS_MAIL`='NONE' and hc.`STATUS_MAIL`='NONE'
							, 'UNDELIVERED'
							, 'UNKNOWN'
						)
					)
				) as `STATUS`
			, hc.`ID_BILLING_USERS`
		from `SES_USERS2`.`USERS2_WINDYKACJA_STATUS_HIST` h
			join `SES_USERS2`.`USERS2_WINDYKACJA_STATUS` w on(w.`ID`=h.`ID_USERS2`)
			join `billing2013`.`HIST_CONTACTS` hc on(hc.`ID`=h.`LAST_MAIL_MSG_ID`)
		where h.`LAST_MAIL_MSG_ID`>0
			and h.`A_RECORD_UPDATE_DATE` like '{$f_day}%'
	) as statsMail
	where statsMail.`STATUS`='{$f_status}'

SQL;
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			if ('UNDELIVERED' == $r->STATUS) {
				$r->STATUS_INFO = 'mail nie został i nie zostanie wysłany (np. brak/błędny adres mailowy)';
			} else if ('SENT' == $r->STATUS) {
				$r->STATUS_INFO = 'mail został wysłany';
			}
			$zest[] = $r;
		}
		$this->_showCss();
		?>
<div class="container">
	<h3>Szczegóły (rodzaj: MAIL, dzień: <?php echo $f_day; ?>, status: <?php echo $f_status; ?>)</h3>
	<table class="table table-bordered">
		<thead>
			<th>Typ</th>
			<th>User</th>
			<th>Data</th>
			<th>Nr klienta</th>
			<th>Status</th>
			<th>Info</th>
		</thead>
		<tbody>
			<?php foreach ($zest as $r) : ?>
				<tr>
					<td><?php echo $r->TYPE; ?></td>
					<td><?php echo $r->A_RECORD_UPDATE_AUTHOR; ?></td>
					<td><?php echo $r->_wind_save; ?></td>
					<td><?php echo $r->ID_BILLING_USERS; ?></td>
					<td class="status_cell-<?php echo $r->STATUS; ?>"><?php echo $r->STATUS; ?></td>
					<td><?php echo $r->STATUS_INFO; ?></td>
				</tr>
			<?php endforeach; ?>
		</tbody>
</table>
</div>
<?php
	}

	private function _showSendMassSmsDetails($f_day, $f_status) {
		if (empty($f_day) || strlen($f_day) != 10
				|| empty($f_status) || strlen($f_status) > 20) {
			echo 'Wrong params';
			return;
		}
		$zest = array();
		$db = DB::getDB();
		$sql = <<<SQL
	select statsSms.`TYPE`
		, statsSms.`A_RECORD_UPDATE_AUTHOR`
		, statsSms.`_wind_save`
		, statsSms.`STATUS`
		, statsSms.`ID_BILLING_USERS`
		, statsSms.`STATUS_INFO`
	from (
		select 'SMS' as `TYPE`
			, h.`A_RECORD_UPDATE_AUTHOR`
			, substr(h.`A_RECORD_UPDATE_DATE`, 1, 10) as `_wind_save`
			, IF(hc.`REQUEST_STATUS_SMS`='SENT_SMS' and hc.`STATUS_SMS`='NONE'
					, 'WAITING'
					, IF(hc.`REQUEST_STATUS_SMS`='CONFIRM_SENT_SMS' and hc.`STATUS_SMS`='SENT_SMS'
						, 'MONITOR'
						, IF(hc.`REQUEST_STATUS_SMS`='NONE' and hc.`STATUS_SMS`='CONFIRM_SENT_SMS'
							, 'SENT'
							, IF(hc.`REQUEST_STATUS_SMS`='NONE' and hc.`STATUS_SMS` in('UNDELIVERED','NONE')
								, 'UNDELIVERED'
								, IF(hc.`REQUEST_STATUS_SMS`='NONE' and hc.`STATUS_SMS`='UNKNOWN'
									, 'OFF_SOFT'
									, 'UNKNOWN'
								)
							)
						)
					)
				) as `STATUS`
			, hc.`ID_BILLING_USERS`
			, hc.`STATUS_SMS_INFO` as `STATUS_INFO`
		from `SES_USERS2`.`USERS2_WINDYKACJA_STATUS_HIST` h
			join `SES_USERS2`.`USERS2_WINDYKACJA_STATUS` w on(w.`ID`=h.`ID_USERS2`)
			join `billing2013`.`HIST_CONTACTS` hc on(hc.`ID`=h.`LAST_SMS_MSG_ID`)
		where h.`LAST_SMS_MSG_ID` > 0
			and h.`A_RECORD_UPDATE_DATE` like '{$f_day}%'
	) as statsSms
	where statsSms.`STATUS`='{$f_status}'

SQL;
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			if ('WAITING' == $r->STATUS) {
				$r->STATUS_INFO = "do wysłania";
			} else if ('MONITOR' == $r->STATUS) {
				$r->STATUS_INFO = "oczekiwanie na potwierdzenie odbioru";
			}
			$zest[] = $r;
		}
		$this->_showCss();
		?>
<div class="container">
	<h3>Szczegóły (rodzaj: SMS, dzień: <?php echo $f_day; ?>, status: <?php echo $f_status; ?>)</h3>
	<table class="table table-bordered">
		<thead>
			<th>Typ</th>
			<th>User</th>
			<th>Data</th>
			<th>Nr klienta</th>
			<th>Status</th>
			<th>Info</th>
		</thead>
		<tbody>
			<?php foreach ($zest as $r) : ?>
				<tr>
					<td><?php echo $r->TYPE; ?></td>
					<td><?php echo $r->A_RECORD_UPDATE_AUTHOR; ?></td>
					<td><?php echo $r->_wind_save; ?></td>
					<td><?php echo $r->ID_BILLING_USERS; ?></td>
					<td class="status_cell-<?php echo $r->STATUS; ?>"><?php echo $r->STATUS; ?></td>
					<td><?php echo $r->STATUS_INFO; ?></td>
				</tr>
			<?php endforeach; ?>
		</tbody>
</table>
</div>
<?php
	}

	private function _sendMassMenu($selectedMonth) {
		$year = ($selectedMonth)? $selectedMonth : date("Y-m");
		?>
<div class="jumbotron">
  <div class="container">
		<form class="form-inline" method="POST">
			<input type="hidden" name="task" value="sendMass" />
			<label for="zest_month">Zestawienie masowej wysyłki sms/mail:</label>
			<div class="input-group date" id="fldZestMonth">
				<input type="text" name="zest_month" class="form-control" value="<?php echo $selectedMonth; ?>" />
				<span class="input-group-addon"><span class="glyphicon glyphicon-time"></span></span>
			</div>
			<button type="submit" id="fldZestMonthBtn" class="btn btn-primary" autocomplete="off">
				Generuj zestawienie
			</button>
		</form>
	</div>
</div>
<script type="text/javascript">
jQuery(document).ready(function () {
  jQuery('#fldZestMonthBtn').on('click', function () {
    jQuery(this).text(jQuery(this).text() + '...').attr('disabled', 'disabled');
		jQuery(this).parent().submit();
  })

	jQuery("#fldZestMonth").datetimepicker({
		format: "YYYY-MM",
		defaultDate: new Date(<?php echo date("Y"); ?>, <?php echo intval(date("m")) - 1; ?>, 1),
		minDate: new Date(2014, 11, 1),
		// maxDate: "<?php echo date("Y"); ?>"
	});
});
</script>
		<?php
	}

	private function _showCss() {
		static $_printed = false;
		if ($_printed) return;
		if (false == $_printed) $_printed = true;
		?>
<style type="text/css">
	.status_cell-SENT { background:#aeffae; color:#000; text-align:center; }
	.status_cell-WAITING { background:#ffd2ff; color:#000; text-align:center; }
	.status_cell-MONITOR { background:#cccaff; color:#000; text-align:center; }
	.status_cell-WARNING { background:#ffbaba; color:#000; text-align:center; }
	.status_cell-UNDELIVERED { background:#e0e0e0; color:#808080; text-align:center; }
	.status_cell-OFF_SOFT { background:#fce3b7; color:#808080; text-align:center; }
	.status_cell-OFF_HARD { background:#eee; color:#808080; text-align:center; }
</style>
<?php
	}

}