<?php
require_once(__DIR__ . '/../checkuser.php');

if (!Util::isAdmin()) {
	header("Location: ../job/index.php");
	exit;
}


global $dbh, $sqlsrv_dbh;

$get_keys = array('contract_id', 'customer_id', 'contract_date', 'sort_by');
$get = array();
foreach ($get_keys as $get_key) {
	$$get_key      = isset($_GET[$get_key]) ? $_GET[$get_key] : null;
	$get[$get_key] = &$$get_key;
}

// Default search
$tmp_now = new DateTime;
if (!isset($_GET['contract_from']) && empty($contract_from)) {
	$contract_from = Util::date_to_string($tmp_now);
}
if (!isset($_GET['contract_to']) && empty($contract_to)) {
	$contract_to = Util::date_to_string($tmp_now);
}
if (!isset($_GET['staff_id']) && empty($staff_id)) {
	if (!Util::isAdmin()) {
		$staff_id = $_SESSION['webadmin']['id'];
	}
}
/*if (!isset($_GET['status']) && empty($status)) {
	$status = 1; // Open
}*/

// Show all
if ($_GET['show_all'] == 1) {
	foreach ($get_keys as $get_key) {
		if (Util::isGuest() && $get_key == 'staff_id') {
			continue;
		}
		$$get_key = null;
	}
}

	//sorting
	/*
	1.Customer Name (A-Z)
	2.Customer Name (Z-A)
	3.Period (Date From - Latest)
	4.Period (Date To - Latest)
	5.Contract Type (Monthly)
	6.Contract Type (Package)
	7.Status (Open)
	8.Status (Close)
	9.Status (Void)
	10.Status (Expired)
	*/

	$sort_by_array = array(1 => "Customer Name (A-Z)", 2 => "Customer Name (Z-A)", 3 => "Period (Date From - Latest)", 4 => "Period (Date To - Latest)", 5 => "Contract Type (Monthly)", 6 => "Contract Type (Package)", 7 => "Status (Open)", 8 => "Status (Close)", 9 => "Status (Void)", 10 => "Status (Expired)");

	$sort_by = $_GET["sort_by"];
	$sql_sort_by = "";

	if($sort_by == 1){
		$sql_sort_by = "ORDER BY customer.company_name ASC";
	}else if($sort_by == 2){
		$sql_sort_by = "ORDER BY customer.company_name DESC";
	}else if($sort_by == 3){
		$sql_sort_by = "ORDER BY contract.contract_from DESC";
	}else if($sort_by == 4){
		$sql_sort_by = "ORDER BY contract.contract_to DESC";
	}else if($sort_by == 5){
		$sql_sort_by = "ORDER BY contract.contract_type ASC, contract_id DESC";
	}else if($sort_by == 6){
		$sql_sort_by = "ORDER BY contract.contract_type DESC, contract_id DESC";
	}else if($sort_by == 7){
		$sql_sort_by = "AND contract.status = 1 ORDER BY contract_id DESC";
	}else if($sort_by == 8){
		$sql_sort_by = "AND contract.status = 2 ORDER BY contract_id DESC";
	}else if($sort_by == 9){
		$sql_sort_by = "AND contract.status = 3 ORDER BY contract_id DESC";
	}else if($sort_by == 10){
		$sql_sort_by = "AND contract.status = 4 ORDER BY contract_id DESC";
	}else{
		$sql_sort_by = "ORDER BY contract_id DESC";
	}

$sql = "
SELECT contract.*,
	(
		SELECT COUNT(*)
		FROM sup_contract contract
	) AS contract_count
FROM sup_contract contract
INNER JOIN v_cm_customer_support customer ON contract.customer_id = customer.cust_id
WHERE (COALESCE(LENGTH(?), 0) = 0 OR contract_id = ?)
	AND (COALESCE(LENGTH(?), 0) = 0 OR customer_id = ?)
	AND (COALESCE(LENGTH(?), 0) = 0 OR contract_from <= ?)
	AND (COALESCE(LENGTH(?), 0) = 0 OR contract_to >= ?)
".$sql_sort_by;
$parameters = array(
	$contract_id, $contract_id,
	$customer_id, $customer_id,
	$contract_date, $contract_date,
	$contract_date, $contract_date,
);
$pagination = new MySqlPagination($sql, $parameters);
$sql .= $pagination->getSqlLimitAndOffset();
if (!($sth = $dbh->prepare($sql))) {
	throw new Exception("sql prepare statement failure: $sql");
}
$sth->setFetchMode(PDO::FETCH_ASSOC);
if (!$sth->execute($parameters)) {
	throw new Exception("sql execute statement failure: $sql");
}
$contracts = $sth->fetchAll();

$sql = "SELECT * FROM v_cm_customer_support V_CM_CUSTOMER_SUPPORT ORDER BY company_name";
if (!($sth = $dbh->prepare($sql))) {
	throw new Exception("sql prepare statement failure: $sql");
}
$sth->setFetchMode(PDO::FETCH_ASSOC);
if (!$sth->execute()) {
	throw new Exception("sql execute statement failure: $sql");
}
$customers = $sth->fetchAll();

$sql = "SELECT * FROM sys_login WHERE deleted = ? ORDER BY username";
$parameters = array(0);
if (!($sth = $dbh->prepare($sql))) {
	throw new Exception("sql prepare statement failure: $sql");
}
$sth->setFetchMode(PDO::FETCH_ASSOC);
if (!$sth->execute($parameters)) {
	throw new Exception("sql execute statement failure: $sql");
}
$staffs = $sth->fetchAll();

$tmp_customer_map = array();
foreach ($customers as &$tmp_customer) {
	$tmp_customer_id                    = $tmp_customer['cust_id'];
	$tmp_customer_map[$tmp_customer_id] = $tmp_customer;
}

$tmp_staff_map = array();
foreach ($staffs as &$tmp_staff) {
	$tmp_staff_id                 = $tmp_staff['id'];
	$tmp_staff_map[$tmp_staff_id] = $tmp_staff;
}

foreach ($contracts as $tmp_key => &$tmp_job) {
	$tmp_customer_id     = $tmp_job['customer_id'];
	$tmp_job['customer'] = $tmp_customer_map[$tmp_customer_id];

	$tmp_staff_id     = $tmp_job['staff_id'];
	$tmp_job['staff'] = $tmp_staff_map[$tmp_staff_id];
}

$message = $_GET['message'];
$pagination = $pagination->toString();


	//some updating of call num when it is null
	$sql2 = "update `sup_contract` set schedule_call_3hrs = 0 where schedule_call_3hrs is NULL;
update `sup_contract` set schedule_call_half_day = 0 where schedule_call_half_day is NULL;
update `sup_contract` set schedule_call_full_day = 0 where schedule_call_full_day is NULL;
update `sup_contract` set service_call = 0 where service_call is NULL;
update `sup_contract` set urgent_call = 0 where urgent_call is NULL;
update `sup_contract` set remote_service_call = 0 where remote_service_call is NULL;";
	$parameters2 = array();
	if (!($sth2 = $dbh->prepare($sql2))) {
		throw new Exception("sql prepare statement failure: $sql2");
	}
	$sth2->setFetchMode(PDO::FETCH_ASSOC);
	if (!$sth2->execute($parameters2)) {
		throw new Exception("sql execute statement failure: $sql2");
	}


?>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>

	<?php require(__DIR__ . '/../inc/_head_meta.php'); ?>

	<?php require(__DIR__ . '/../inc/_head_css.php'); ?>

	<style type="text/css">
		body {
			padding-top: 60px; /* 60px to make the container go all the way to the bottom of the topbar */
			padding-bottom: 40px;
		}
	</style>

	<?php require(__DIR__ . '/../inc/_head_script.php'); ?>

</head>
<body>
<?php require(__DIR__ . '/../inc/_navbar.php'); ?>

<div class="container">

	<?php if (isset($message) && !empty($message)): ?>
		<div class="alert alert-info">
			<button type="button" class="close" data-dismiss="alert">&times;</button>
			<h5 class="alert-heading">Note:</h5>

			<p><?= $message ?></p>
		</div>
	<?php endif; ?>

	<?php if (isset($_SESSION["exist_contract_id"]) && !empty($_SESSION["exist_contract_id"])): ?>
		<div class="alert alert-info">
			<button type="button" class="close" data-dismiss="alert">&times;</button>
			<h5 class="alert-heading">Opened Contract:</h5>

			<p>
				<?php
					echo "<a href='../contract/modifyform.php?id=" . $_SESSION["exist_contract_id"] . "' target='_blank'> Contract ID: " . $_SESSION["exist_contract_id"] . "</a><br>";
				?>
			</p>
		</div>
	<?php endif; ?>

	<?php if (isset($_SESSION["remark_contract_id"]) && !empty($_SESSION["remark_contract_id"])): ?>
		<div class="alert alert-info">
			<button type="button" class="close" data-dismiss="alert">&times;</button>
			<h5 class="alert-heading">Last Add/Modify Contract:</h5>

			<p>
				<?php
					echo "<a href='../contract/modifyform.php?id=" . $_SESSION["remark_contract_id"] . "' target='_blank'> Contract ID: " . $_SESSION["remark_contract_id"] . "</a><br>";
				?>
			</p>
		</div>
	<?php endif; ?>

	<a href="addform.php" class="btn btn-primary pull-right">New Contract</a>

	<h2>Contracts</h2>

	<div class="container-fluid">
		<div class="row-fluid">
			<div class="span12">
				<div class="hero-unit" style="padding:10px">
					<a href="#" id="search_btn" class="btn btn-link"><h4><i class="icon-list-alt"></i> Search Form</h4>
					</a>
					<script type="text/javascript">
						$(function () {
							$('#search_btn').click(function (event) {
								event.preventDefault();
								$('#search_form').toggle();
							});
							$('.date-picker').datetimepicker({pickTime: false});
							$('.time-picker').datetimepicker({pickDate: false, pickSeconds: false});
							$('.select2').select2();
							$('#search_form').validate();
						});
					</script>
					<form id="search_form" class="form-horizontal" method="get">

						<div class="control-group">
							<?php $attribute = 'contract_id';
								$label       = 'Contract no.'; ?>
							<label class="control-label" for="<?= $attribute ?>"><?= $label ?></label>

							<div class="controls">
								<input type="text" id="<?= $attribute ?>" name="<?= $attribute ?>" placeholder="<?= $label ?>" class="digits" value="<?= $$attribute ?>"/>
							</div>
						</div>

						<div class="control-group">
							<?php $attribute = 'customer_id';
								$label       = 'Customer'; ?>
							<label class="control-label" for="<?= $attribute ?>"><?= $label ?></label>

							<div class="controls">
								<select id="<?= $attribute ?>" name="<?= $attribute ?>" placeholder="Select a <?= $label ?>" class="select2">
									<option value=""></option>
									<?php foreach ($customers as $customer): ?>
										<option value="<?= $customer['cust_id'] ?>"<?= $customer['cust_id'] == $$attribute ? ' selected="selected"' : '' ?>><?= $customer['enable'] == 1 ? '' : '[Disabled] ' ?><?= h($customer['company_name']) ?></option>
									<?php endforeach; ?>
								</select>
							</div>
						</div>

						<div class="control-group">
							<?php $attribute = 'contract_date';
								$label       = 'Contract Date'; ?>
							<label class="control-label" for="<?= $attribute ?>"><?= $label ?></label>

							<div class="controls">
								<div class="input-append date-picker">
									<input type="text" id="<?= $attribute ?>" name="<?= $attribute ?>" class="dateISO" data-format="yyyy-MM-dd" value="<?= $$attribute ?>" placeholder="<?= $label ?>"/>
										<span class="add-on">
											<i data-time-icon="icon-time" data-date-icon="icon-calendar"></i>
										</span>
								</div>
							</div>
						</div>

						<div class="control-group">
							<?php $attribute = 'sort_by';
								$label       = 'Sort By'; ?>
							<label class="control-label" for="<?= $attribute ?>"><?= $label ?></label>

							<div class="controls">
								<select name="<?= $attribute ?>">
									<option value="">Default</option>
									<?php
										foreach($sort_by_array as $key => $sort_by){
											if($key == $_GET["sort_by"])
												$selected = "selected";
											else
												$selected = "";

											echo '<option value="'.$key.'" '.$selected.'>'.$sort_by.'</option>';
										}
									?>
								</select>
							</div>
						</div>

						<div class="control-group">
							<div class="controls">
								<button type="submit" class="btn btn-primary"><i class="icon-search icon-white"></i>
									Search
								</button>
								<a href="index.php?show_all=1" class="btn">Show All</a>
							</div>
						</div>
					</form>
				</div>
			</div>
			<!--/span-->
		</div>
		<!--/row-->
	</div>

	<?= $pagination ?>

	<table class="table table-striped">
		<caption class="text-left hide"><h2>Contract</h2></caption>
		<thead>
		<tr style="white-space: nowrap;">
			<th>&nbsp;</th>
			<th>Number</th>
			<th>Customer</th>
			<th style="width: 170px;">Period</th>
			<th>Schedule Call</th>
			<th>Service Call</th>
			<th>Urgent Call</th>
			<th>Remote Call</th>
			<th>Contract Type</th>
			<th>Status</th>
			<!--<th>Technician</th>
			<th>Network Engineer</th>-->
		</tr>
		</thead>
		<tbody>
		<?php foreach ($contracts as $contract): ?>
			<tr>
				<td><a href="modifyform.php?id=<?= $contract['contract_id'] ?>" class="btn"><i class="icon-pencil"></i></a>
				</td>
				<td><?= $contract['contract_id'] ?></td>
				<td><?= h($contract['customer']['company_name']) ?></td>
				<td><?= $contract['contract_from'] . " to " . $contract['contract_to'] ?></td>
				<td>
					<?= $contract['schedule_call_3hrs'] . " (3hrs),<br> ".$contract['schedule_call_half_day'] . " (Half Days),<br> ",$contract['schedule_call_full_day'] . " (Full Days)"; ?>
				</td>

				<td><?= $contract['service_call'] ?></td>
				<td><?= $contract['urgent_call'] ?></td>
				<td><?= $contract['remote_service_call'] ?></td>
				<td><?= contract_type($contract['contract_type']); ?></td>
				<td><?php $contract_statusOptions = Contract::statusOptions();
						echo $contract_statusOptions[$contract['status']] ?></td>
			</tr>
		<?php endforeach; ?>
		</tbody>
	</table>

	<?= $pagination ?>

	<div class="scroll_to_top">
		<a href="#" class="btn btn-link btn-mini scroll-to-top">Scroll to Top</a>
	</div>

	<script type="text/javascript">
		$(function () {
			$('.job-status, .job-time').tooltip({html: true});
			$('.scroll-to-top').click(function (event) {
				event.preventDefault();
				scrollToTop();
			});
		});
	</script>

	<?php require(__DIR__ . '/../inc/_footer.php'); ?>

</div>
<!-- /container -->
</body>
</html>
<?php
	unset($_SESSION["remark_contract_id"]);
	unset($_SESSION["exist_contract_id"]);
?>