<?php
require_once(__DIR__ . '/../checkuser.php');
//auto close over time contract
/*$sql = "update sup_contract set status=2 where status=1 and deleted=0 and contract_to < '".date("Y-m-d")."'";
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");
}*/

function index() {
	global $dbh, $sqlsrv_dbh;
	
	$get_keys = array('job_id', 'customer_id', 'start_date', 'end_date', 'staff_id', 'status', 'has_quotation');
	$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['start_date']) && empty($start_date)) {
		$start_date = Util::date_to_string($tmp_now);
	}
	if (!isset($_GET['end_date']) && empty($end_date)) {
		$end_date = 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;
		}
	}

	$sql = "
SELECT job.*,
	(
		SELECT COUNT(*)
		FROM sup_job_detail detail
		WHERE detail.job_id = job.id
			AND detail.status = 1
			AND detail.actived = 1
			AND detail.deleted = 0
	) AS detail_open_count,
	(
		SELECT COUNT(*)
		FROM sup_job_detail detail
		WHERE detail.job_id = job.id
			AND detail.status = 2
			AND detail.actived = 1
			AND detail.deleted = 0
	) AS detail_completed_count,
	(
		SELECT COUNT(*)
		FROM sup_job_detail detail
		WHERE detail.job_id = job.id
			AND detail.status = 3
			AND detail.actived = 1
			AND detail.deleted = 0
	) AS detail_follow_up_count,
	(
		SELECT COUNT(*)
		FROM sup_job_detail detail
		WHERE detail.job_id = job.id
			AND detail.status = 4
			AND detail.actived = 1
			AND detail.deleted = 0
	) AS detail_cancel_count
FROM sup_job job
WHERE (COALESCE(LENGTH(?), 0) = 0 OR id = ?)
	AND (COALESCE(LENGTH(?), 0) = 0 OR customer_id = ?)
	AND (COALESCE(LENGTH(?), 0) = 0 OR staff_id = ?)
	AND (COALESCE(LENGTH(?), 0) = 0 OR status = ?)
	AND (COALESCE(LENGTH(?), 0) = 0 OR `createdate` >= ?)
	AND (COALESCE(LENGTH(?), 0) = 0 OR `createdate` <= ?)
	AND (COALESCE(LENGTH(?), 0) = 0 OR LENGTH(quotation) > 0)
	AND job.deleted =0 AND job.status !=3 AND job.job_type='CALL IN'
	GROUP BY job.id
ORDER BY job.date DESC
";
	$parameters = array(
		$job_id, $job_id,
		$customer_id, $customer_id,
		$staff_id, $staff_id,
		$status, $status,
		$start_date, $start_date,
		$end_date, $end_date,
		$has_quotation ? '1' : '',

	);
	$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");
	}
	$jobs = $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 ($jobs 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];
	}



	return array_merge($get, array(
		'jobs' => $jobs,
		//'projects' => $projects,
		'message' => $_GET['message'],
		'customers' => $customers,
		'staffs' => $staffs,
		'statusOptions' => Job::statusOptions(),
		'typeOptions' => Job::typeOptions(),
		'pagination' => $pagination->toString(),
	));
}
extract(index());
?><!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["remark_job_id"]) && !empty($_SESSION["remark_job_id"]) ): ?>
		<div class="alert alert-info">
			<button type="button" class="close" data-dismiss="alert">&times;</button>
			<h5 class="alert-heading">Last Add/Modify Job:</h5>
			<p>
				<?php
					if(!empty($_GET["id"])){

						echo "<a href='../job/modifyform.php?id=".$_GET["id"]."' target='_blank'> Job ID: ".$_GET["id"]."</a><br>";
					}else{
						foreach($_SESSION["remark_job_id"] as $key => $remark_job_id){
							echo "<a href='../job/modifyform.php?id=".$remark_job_id."' target='_blank'> Job ID: ".$remark_job_id."</a><br>";
						}
					}


				?>
			</p>
		</div>
		<?php endif; ?>


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

				<p>
					<?php
						foreach($_SESSION["clash_job_array"] as $key => $clash_job_array){
							echo "<a href='../job/modifyform.php?id=".$_SESSION["clash_job_id_array"][$key]."' target='_blank'>".$clash_job_array."</a><br>";
						}

					?>
					<br>CLASH this added/modified Job!!
				</p>
			</div>
		<?php endif; ?>


		<?php //if (Util::isAdmin() || $_SESSION['webadmin']['role'] == 2): ?>

			<a href="addform.php" class="btn btn-primary pull-right" >New Call In Job</a>
		<?php //endif; ?>

		<h2>Call In Jobs</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 = 'job_id'; $label = 'Job 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 = 'staff_id'; $label = 'Staff'; ?>
								<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 ($staffs as $staff): ?>
										<option value="<?=$staff['id']?>"<?=$staff['id'] == $$attribute ? ' selected="selected"' : ''?>><?=$staff['actived'] == 1 ? '' : '[Disabled] '?><?=h($staff['username'])?></option>
										<?php endforeach; ?>
									</select>
								</div>
							</div>
							<div class="control-group">
								<?php $attribute = 'start_date'; $label = 'Start 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 = 'end_date'; $label = 'End 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 = 'status'; $label = 'Status'; ?>
								<label class="control-label"><?=$label?></label>
								<div class="controls">
									<select id="<?=$attribute?>" name="<?=$attribute?>" placeholder="Select a <?=$label?>">
										<option value=""></option>
										<?php foreach ($statusOptions as $value => $label): ?>
										<option value="<?=$value?>"<?=$value == $$attribute ? ' selected="selected"' : ''?>><?=$label?></option>
										<?php endforeach; ?>
									</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>Jobs</h2></caption>
			<thead>
				<tr>
					<th>&nbsp;</th>
					<th>Number</th>
					<th>Customer</th>
					<th>Date</th>
					<th>Time</th>
					<th>Staff</th>
					<th>Type</th>
					<th>Status</th>
				</tr>
			</thead>
			<tbody>
			<?php

				foreach ($jobs as $job): ?>
				<tr>
					<td><a href="modifyform.php?id=<?=$job['id']?>" class="btn"><i class="icon-pencil"></i></a></td>
					<td><?=$job['id']?></td>
					<td><?=h($job['customer']['company_name'])?></td>
					<td><?=$job['createdate']?></td>
					<td>
						<?php
						ob_start();
						?>
						Time: <?=h(Util::value_to_time_string($job['start_time']))?> - <?=h(Util::value_to_time_string($job['end_time']))?>
						<br />
						Actual Time: <?=h(Util::value_to_time_string($job['actual_start_time']))?> - <?=h(Util::value_to_time_string($job['actual_end_time']))?>
						<?php
						$tmp_title = ob_get_contents();
						ob_end_clean();
						?>
						<a href="javascript:void(0)" class="job-time" data-toggle="tooltip" title="<?=  htmlentities($tmp_title)?>">
							<?=h(Util::value_to_time_string($job['start_time']))?> - <?=h(Util::value_to_time_string($job['end_time']))?>
						</a>
					</td>
					<td><?=h($job['staff']['username'])?></td>
					<td>
						<?php
							if(empty($job["project_id"]))
								echo h($typeOptions[$job['type']]);
							else {

								$sql2 = "select * from sup_project where id = ?";

								if (!($sth1 = $dbh->prepare($sql2))) {
									throw new Exception("sql prepare statement failure: $sql2");
								}

								if (!$sth1->execute(array($job["project_id"]))) {
									throw new Exception("sql execute statement failure: $sql2");
								}
								$project = $sth1->fetch(PDO::FETCH_ASSOC);

								echo "Project - ".$project{"project_name"};
							}
						?>
					</td>
					<td>
						<?php
						ob_start();
						?>
						Open: <?=$job['detail_open_count']?>
						<br />
						Completed: <?=$job['detail_completed_count']?>
						<br />
						Follow up: <?=$job['detail_follow_up_count']?>
						<br />
						Cancel: <?=$job['detail_cancel_count']?>
						<?php
						$tmp_title = ob_get_contents();
						ob_end_clean();
						?>
						<a href="javascript:void(0)" class="job-status" data-toggle="tooltip" title="<?=htmlentities($tmp_title)?>">
							<i class="<?=in_array($job['status'], array(1, 2)) ? 'icon-ok' : 'icon-remove'?>"></i>
							<?=h($statusOptions[$job['status']])?>
						</a>
					</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["clash_job_id_array"]);
	unset($_SESSION["clash_job_array"]);
	unset($_SESSION["remark_job_id"]);
?>