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

<?php

Lib::loadClass('RouteBase');

/*
 * @uses MySQL Function `A_STATUS_L2_SQL_DATE`

SET @p0 = '26199';
SET @p1 = '2015-09-02';
SELECT A_STATUS_L2_SQL_DATE ( @p0 , @p1 ) AS A_STATUS_L2_SQL_DATE;

*/
class Route_ActiveServices extends RouteBase {

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

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

		SE_Layout::gora();
		SE_Layout::menu();
		$this->_menu($selectedMonth);
		SE_Layout::dol();
	}

	private function _menu($selectedMonth) {
		$selectedMonth = ($selectedMonth)? $selectedMonth : date("Y-m");
		?>
<div class="jumbotron">
  <div class="container">
		<form class="form-inline" method="POST">
			<input type="hidden" name="_task" value="updateMonth" />
			<label for="zest_month">Aktualizacja zestawienia aktywnych usług na otwarcie/zamknięcie miesiąca:</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">
				Aktualizuj dane
			</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
	}

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

		SE_Layout::gora();
		SE_Layout::menu();
		$this->_menu($selectedMonth);
		if (strlen($selectedMonth) != 7) {
			echo '<div class="alert alert-gander">Wrong month</div>';
		} else {
			try {
			$this->_updateMonth($selectedMonth);
			} catch (Exception $e) {
				echo '<div class="alert alert-danger">' . $e->getMessage(). '</div>';
				SE_Layout::dol();
				exit;
			}
			echo '<div class="alert alert-success">' . "Gotowe" . '</div>';
		}
		// TODO: TableAjax for `STATS_ACTIVE_SERVICES_STATUS`
		SE_Layout::dol();
	}

	public function _updateMonth($selectedMonth) {
		//echo '<p>TODO: update month '.$selectedMonth.'</p>';
		$parts = explode('-', $selectedMonth);
		if (count($parts) != 2 || strlen($parts[0]) != 4 || strlen($parts[1]) != 2) {
			throw new Exception("Wrong month format");
		}
		$sqlYear = $parts[0];
		$sqlMonth = $parts[1];
		$sqlMonthFirstDay = "{$sqlYear}-{$sqlMonth}-01";
		$sqlUpdateMonth = <<<DB_SQL
select
	c.`ID` as `ID_KLIENT`
	, srv.`ID` as `ID_SERVICES`
	, c.`A_ADM_COMPANY`
	, '{$sqlYear}' as YEAR
	, '{$sqlMonth}' as MONTH
	, IF(DATE_SUB('{$sqlMonthFirstDay}', INTERVAL 2 DAY) > CURDATE()
		, '???'
		, A_STATUS_L2_SQL_DATE(srv.`ID`, DATE_SUB('{$sqlMonthFirstDay}', INTERVAL 2 DAY))
		) as OPEN_DAY_MINUS_2
	, IF(DATE_SUB('{$sqlMonthFirstDay}', INTERVAL 1 DAY) > CURDATE()
		, '???'
		, A_STATUS_L2_SQL_DATE(srv.`ID`, DATE_SUB('{$sqlMonthFirstDay}', INTERVAL 1 DAY))
		) as OPEN_DAY_MINUS_1
	, IF('{$sqlMonthFirstDay}' > CURDATE()
		, '???'
		, A_STATUS_L2_SQL_DATE(srv.`ID`, '{$sqlMonthFirstDay}')
		) as OPEN_DAY
	, IF(DATE_ADD('{$sqlMonthFirstDay}', INTERVAL 1 DAY) > CURDATE()
		, '???'
		, A_STATUS_L2_SQL_DATE(srv.`ID`, DATE_ADD('{$sqlMonthFirstDay}', INTERVAL 1 DAY))
		) as OPEN_DAY_PLUS_1
	, IF(DATE_SUB(LAST_DAY('{$sqlMonthFirstDay}'), INTERVAL 1 DAY) > CURDATE()
		, '???'
		, A_STATUS_L2_SQL_DATE(srv.`ID`, DATE_SUB(LAST_DAY('{$sqlMonthFirstDay}'), INTERVAL 1 DAY))
		) as CLOSE_DAY_MINUS_1
	, IF(LAST_DAY('{$sqlMonthFirstDay}') > CURDATE()
		, '???'
		, A_STATUS_L2_SQL_DATE(srv.`ID`, LAST_DAY('{$sqlMonthFirstDay}'))
		) as CLOSE_DAY
	, IF(DATE_ADD(LAST_DAY('{$sqlMonthFirstDay}'), INTERVAL 1 DAY) > CURDATE()
		, '???'
		, A_STATUS_L2_SQL_DATE(srv.`ID`, DATE_ADD(LAST_DAY('{$sqlMonthFirstDay}'), INTERVAL 1 DAY))
		) as CLOSE_DAY_PLUS_1
	, IF(DATE_ADD(LAST_DAY('{$sqlMonthFirstDay}'), INTERVAL 2 DAY) > CURDATE()
		, '???'
		, A_STATUS_L2_SQL_DATE(srv.`ID`, DATE_ADD(LAST_DAY('{$sqlMonthFirstDay}'), INTERVAL 2 DAY))
		) as CLOSE_DAY_PLUS_2
	, 0 as `_TO_REMOVE`
from `COMPANIES` c
	join `SERVICES` srv on(srv.`ID_BILLING_USERS`=c.`ID`)
-- where c.`A_ADM_COMPANY`='19994_PODMIOT_Vectra_wlasciciel'

DB_SQL;
		//echo '<pre>';echo "{$sqlUpdateMonth}";echo'</pre>';
		$sqlUpdateMonth = <<<SQL

	insert into `STATS_ACTIVE_SERVICES_STATUS` (
		`ID_KLIENT`
		, `ID_SERVICES`
		, `A_ADM_COMPANY`
		, `YEAR`
		, `MONTH`
		, `OPEN_DAY_MINUS_2`
		, `OPEN_DAY_MINUS_1`
		, `OPEN_DAY`
		, `OPEN_DAY_PLUS_1`
		, `CLOSE_DAY_MINUS_1`
		, `CLOSE_DAY`
		, `CLOSE_DAY_PLUS_1`
		, `CLOSE_DAY_PLUS_2`
		, `_TO_REMOVE`
	)
		{$sqlUpdateMonth}

SQL;
		//echo '<pre>';echo "{$sqlUpdateMonth}";echo'</pre>';
		$sqlList = array();
		$sqlList['drop month data'] = "delete from `STATS_ACTIVE_SERVICES_STATUS` where `YEAR`='{$sqlYear}' and `MONTH`='{$sqlMonth}' ";
		//$sqlList['set _TO_REMOVE=1'] = "update `STATS_ACTIVE_SERVICES_STATUS` set `_TO_REMOVE`=1 where `YEAR`='{$sqlYear}' and `MONTH`='{$sqlMonth}' ";
		$sqlList['update month data'] = $sqlUpdateMonth;
		//$sqlList['delete _TO_REMOVE=1'] = "delete from `STATS_ACTIVE_SERVICES_STATUS` where `_TO_REMOVE`=1 ";
		$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 reinstallAction() {
		SE_Layout::gora();
		$this->reinstall();
		echo 'OK';
		SE_Layout::dol();
	}

	public function reinstall() {
		$sqlList = array();
		$sqlList['RemoveTable_STATS_ACTIVE_SERVICES_STATUS'] = "DROP TABLE IF EXISTS `STATS_ACTIVE_SERVICES_STATUS`";
		$sqlList['InstallTable_STATS_ACTIVE_SERVICES_STATUS'] = <<<SQL
			CREATE TABLE IF NOT EXISTS `STATS_ACTIVE_SERVICES_STATUS` (
				`ID` int(11) NOT NULL AUTO_INCREMENT
				, `ID_KLIENT` int(11) NOT NULL
				, `ID_SERVICES` int(11) NOT NULL
				, `A_ADM_COMPANY` varchar(100) NOT NULL DEFAULT ''
				, `YEAR` int(11) NOT NULL
				, `MONTH` int(11) NOT NULL
				, `OPEN_DAY_MINUS_2` varchar(16) NOT NULL DEFAULT ''
				, `OPEN_DAY_MINUS_1` varchar(16) NOT NULL DEFAULT ''
				, `OPEN_DAY` varchar(16) NOT NULL DEFAULT ''
				, `OPEN_DAY_PLUS_1` varchar(16) NOT NULL DEFAULT ''
				, `CLOSE_DAY_MINUS_1` varchar(16) NOT NULL DEFAULT ''
				, `CLOSE_DAY` varchar(16) NOT NULL DEFAULT ''
				, `CLOSE_DAY_PLUS_1` varchar(16) NOT NULL DEFAULT ''
				, `CLOSE_DAY_PLUS_2` varchar(16) NOT NULL DEFAULT ''
				, `_TO_REMOVE` tinyint(1) NOT NULL DEFAULT '0'
				, PRIMARY KEY (`ID`)
				, KEY `ID_KLIENT` (`ID_KLIENT`)
				, UNIQUE KEY `uniq__srv_month` (`ID_KLIENT`,`ID_SERVICES`,`YEAR`,`MONTH`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2;
SQL;
		$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()));
			}
		}
	}

}