<?php
	require_once(__DIR__ . '/../checkuser.php');
	session_start();
//-----------------------------------------------------------------------------
// Check permission
//-----------------------------------------------------------------------------
//if (!Util::isAdmin()) {
//	redirectAndExit('index.php?message=No permission!');
//}

//-----------------------------------------------------------------------------
// Save if POST method
//-----------------------------------------------------------------------------
	if (isPost()) {

		if (!$dbh->beginTransaction()) {
			throw new Exception('mysql begin transaction failure.');
		}
		try {
			$all_job_ids = array();

			$sql        = "SELECT column_name FROM information_schema.columns WHERE table_schema = (SELECT DATABASE()) AND table_name = ?";
			$parameters = array('sup_job');
			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");
			}
			$columns = $sth->fetchAll();

			foreach ($columns as $column) {

				$column = $column['column_name'];

				//if($column != "start_time" || $column != "end_time"){   //do not need to insert time data, it will be update later
				if (isset($_POST[$column])) {
					$job[$column] = $_POST[$column];
				}
				//}

			}

			if (!Util::isAdmin()) {
				// Copy actual time
				if (empty($job['actual_start_time'])) {
					$job['actual_start_time'] = $job['start_time'];
				}
				if (empty($job['actual_end_time'])) {
					$job['actual_end_time'] = $job['end_time'];
				}
			}

			$sql        = "SELECT * FROM v_cm_customer_support V_CM_CUSTOMER_SUPPORT WHERE cust_id = ?";
			$parameters = array($job['customer_id']);
			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");
			}
			$customer = $sth->fetch(PDO::FETCH_ASSOC);

			$job['email1'] = $customer['email'];

			// Append record time
			$now = date("Y-m-d H:i:s");

			$job = array_merge($job, array(
				'createdate' => $now,
				'createby'   => $_SESSION['webadmin']['id'],
				'lastupdate' => $now,
				'lastupby'   => $_SESSION['webadmin']['id'],
			));

			// Create job
			$columns     = array();
			$values      = array();
			$parameters2 = array();

			foreach ($job as $column => $value) {
				$columns[]     = $column;
				if($column == "other_staff_id" && count($value) > 0){
					$parameters2[] = implode(",", $value);
					$values[]      = '?';
				}else{
					$parameters2[] = !strlen($value) ? null : $value;
					$values[]      = '?';
				}
			}

			$remark_job_id = array();

			if ($_POST["is_today"] == 1) {//for only today use

				$sql = "INSERT sup_job (" . implode(', ', $columns) . ") VALUES (" . implode(', ', $values) . ")";
				if (!($sth = $dbh->prepare($sql))) {
					throw new Exception("sql prepare statement failure: $sql");
				}
				$sth->setFetchMode(PDO::FETCH_ASSOC);
				if (!$sth->execute($parameters2)) {
					throw new Exception("sql execute statement failure: $sql");
				}

				$lastinsertid = $dbh->lastInsertId();
				$remark_job_id[] = $lastinsertid;

				//need to link the new contract job with the existing monthly task
				if (isset($_POST["contract_id"])  && $_POST["contract_type"] == 1) {

					$_date       = $_POST["today_selected_day"];
					$_date_year  = date("Y", strtotime($_date));
					$_date_month = date("n", strtotime($_date));

					//check the create job date is within the contract period or not
					$sql3 = "select * from sup_contract where contract_id = ?";
					if (!($sth3 = $dbh->prepare($sql3))) {
						throw new Exception("sql prepare statement failure: $sql3");
					}

					if (!$sth3->execute(array((int)$_POST["contract_id"]))) {
						throw new Exception("sql execute statement failure: $sql3");
					}

					$row3 = $sth3->fetch(PDO::FETCH_ASSOC);
					//created new contract job should not later than contract to
					if($row3["contract_to"] < $_date){
						redirectAndExit('index.php?message=Job Cannot Create. The contract job date should not before the date of contract to.');
					}

				}



				//record all job id
				$all_job_ids[] = $lastinsertid;

				$_staff_id = (int)$_POST["staff_id"];

				$_date       = $_POST["today_selected_day"];
				$_start_time = $_POST["today_start_time"];
				$_end_time   = $_POST["today_end_time"];

				//change the end time from 00 to 59
				$time_explode = explode(':', $_end_time);
				$minutes      = $time_explode[0];
				$seconds      = $time_explode[1];

				if ($seconds == "00" && $minutes != "00") {
					$minutes = $minutes - 1;
					$seconds = "59";

					$_end_time = $minutes . ":" . $seconds;
				}
				//end of change time


				if (!empty($_date) && !empty($_start_time) && !empty($_end_time)) {
					//checking whether the staff already has job on that time
					$sql = "SELECT job.*,customer.* FROM sup_job job,v_cm_customer_support customer WHERE job.customer_id = customer.cust_id and job.date = ? and job.start_time <= ? and ? <= job.end_time and job.staff_id = ? order by job.date ASC";

					//$parameters2 = array(0);
					if (!($sth = $dbh->prepare($sql))) {
						throw new Exception("sql prepare statement failure: $sql");
					}
					$sth->setFetchMode(PDO::FETCH_ASSOC);
					if (!$sth->execute(array($_date, $_end_time, $_start_time, $_staff_id))) {
						throw new Exception("sql execute g failure: $sql");
					}

					$check_job_time = $sth->fetchAll();

					if (!empty($check_job_time)) { //staff time clash
						foreach ($check_job_time as $check) {
							$clash_job_array[]    = $check["id"] . " (" . $check["company_name"] . ")";
							$clash_job_id_array[] = $check["id"];
						}
					}


				}


				if (empty($_POST["location"])) {
					if (!empty($customer["billaddress"])) {
						$location = $customer["billaddress"];
					} else {
						$location = $customer["shipaddress"];
					}
				} else {
					$location = $_POST["location"];
				}

				$sql = "UPDATE sup_job SET `date`=?,start_time=?, end_time=?, location=? WHERE id = ?";

				if (!($sth = $dbh->prepare($sql))) {
					throw new Exception("sql prepare statement failure: $sql");
				}
				$sth->setFetchMode(PDO::FETCH_ASSOC);
				if (!$sth->execute(array($_date, $_start_time, $_end_time, $location, $lastinsertid))) {
					throw new Exception("sql execute statement failure: $sql");
				}



			} else {
				$start_time   = $_POST["start_time1"];
				$end_time     = $_POST["end_time1"];
				$selected_day = $_POST["selected_day"];
				$arraynum     = $_POST["arraynum"];

				$_staff_id   = (int)$_POST["staff_id"];

				$clash_array = array();

				foreach ($arraynum as $key => $rownum) {
					$sql = "INSERT sup_job (" . implode(', ', $columns) . ") VALUES (" . implode(', ', $values) . ")";
					if (!($sth = $dbh->prepare($sql))) {
						throw new Exception("sql prepare statement failure: $sql");
					}
					//$sth->setFetchMode(PDO::FETCH_ASSOC);
					if (!$sth->execute($parameters2)) {
						throw new Exception("sql execute statement failure: $sql");
					}
					$lastinsertid = $dbh->lastInsertId();
					$remark_job_id[] = $lastinsertid;


					//need to link the new contract job with the existing monthly task
					if (isset($_POST["contract_id"]) && $_POST["contract_type"] == 1) {
						$_date       = $selected_day[$rownum];
						$_date_year  = date("Y", strtotime($_date));
						$_date_month = date("n", strtotime($_date));

						//check the create job date is within the contract period or not
						$sql3 = "select * from sup_contract where contract_id = ?";
						if (!($sth3 = $dbh->prepare($sql3))) {
							throw new Exception("sql prepare statement failure: $sql3");
						}

						if (!$sth3->execute(array((int)$_POST["contract_id"]))) {
							throw new Exception("sql execute statement failure: $sql3");
						}

						$row3 = $sth3->fetch(PDO::FETCH_ASSOC);
						//created new contract job should not later than contract to
						if($row3["contract_to"] < $_date){
							redirectAndExit('index.php?message=Job Cannot Create. The contract job date should not before the date of contract to.');
						}


					}


					//record all job id
					$all_job_ids[] = $lastinsertid;

					$_date       = $selected_day[$rownum];
					$_start_time = $start_time[$rownum];
					$_end_time   = $end_time[$rownum];

					//change the end time from 00 to 59
					$time_explode = explode(':', $_end_time);
					$minutes      = $time_explode[0];
					$seconds      = $time_explode[1];

					if ($seconds == "00" && $minutes != "00") {
						$minutes = $minutes - 1;
						$seconds = "59";

						$_end_time = $minutes . ":" . $seconds;
					}
					//end of change time


					if (!empty($_date) && !empty($_start_time) && !empty($_end_time)) {
						//checking whether the staff already has job on that time
						$sql = "SELECT job.*,customer.* FROM sup_job job,v_cm_customer_support customer WHERE job.customer_id = customer.cust_id and job.date = ? and job.start_time <= ? and ? <= job.end_time and job.staff_id = ? order by job.date ASC";

						//$parameters2 = array(0);
						if (!($sth = $dbh->prepare($sql))) {
							throw new Exception("sql prepare statement failure: $sql");
						}
						$sth->setFetchMode(PDO::FETCH_ASSOC);
						if (!$sth->execute(array($_date, $_end_time, $_start_time, $_staff_id))) {
							throw new Exception("sql execute statement failure: $sql");
						}

						$check_job_time = $sth->fetchAll();

						if (!empty($check_job_time)) { //staff time clash
							foreach ($check_job_time as $check) {
								$clash_job_array[]    = $check["id"] . " (" . $check["company_name"] . ")";
								$clash_job_id_array[] = $check["id"];
							}
						}


					}

					//update the previous record with correct time
					if (empty($_POST["location"])) {
						if (!empty($customer["billaddress"])) {
							$location = $customer["billaddress"];
						} else {
							$location = $customer["shipaddress"];
						}
					} else {
						$location = $_POST["location"];
					}

					$sql = "UPDATE sup_job SET `date`=?,start_time=?, end_time=?, location=? WHERE id = ?";

					if (!($sth = $dbh->prepare($sql))) {
						throw new Exception("sql prepare statement failure: $sql");
					}
					$sth->setFetchMode(PDO::FETCH_ASSOC);
					if (!$sth->execute(array($_date, $_start_time, $_end_time, $location, $lastinsertid))) {
						throw new Exception("sql execute statement failure: $sql");
					}

				}


			}


			if (!$dbh->commit()) {
				throw new Exception('mysql commit transaction failure.');
			}


			$_SESSION["clash_job_array"]    = $clash_job_array;
			$_SESSION["clash_job_id_array"] = $clash_job_id_array;
			$_SESSION["remark_job_id"] = $remark_job_id;

			$job['id'] = $lastinsertid;
			//$job['id'] = $firstinsertid;

			$data = array(
				'job_id' => $job['id'],
			);

			$_SESSION["all_job_ids"] = $all_job_ids;

			if ($job['project_id'] || $job["contract_id"]) { //no need to add task in the first time

//				$startdate = date("Y-m-d");
//				$enddate = Date('Y-m-d', strtotime($startdate ."+1 month"));
//				redirectAndExit('index.php?start_date='.$startdate.'&end_date='.$enddate.'&message=Job Saved.');
				redirectAndExit(Util::link(__DIR__ . '/../task/addform.php') . '?' . http_build_query($data));
			} else {
				redirectAndExit(Util::link(__DIR__ . '/../task/addform.php') . '?' . http_build_query($data));
				//redirectAndExit(Util::link(__DIR__ . '/../task/addform.php'));
			}

		} catch (Exception $exception) {
			if (!$dbh->rollBack()) {
				throw new Exception('mysql roll back transaction failure.');
			}
			throw $exception;
		}

	}