<?php
//-----------------------------------------------------------------------------
// Define
//-----------------------------------------------------------------------------
	define('PROJECT_FOLDER', 'onesupport');
define('PROJECT_PATH', '/onesupport/');
define('PROJECT_ROOT', $_SERVER['DOCUMENT_ROOT'] . PROJECT_PATH);

//-----------------------------------------------------------------------------
// Error reporting level
//-----------------------------------------------------------------------------
	error_reporting(E_ALL ^ E_NOTICE);
	//error_reporting(E_ALL);
	ini_set('display_errors', 1);
//-----------------------------------------------------------------------------
// Timezone
//-----------------------------------------------------------------------------
	date_default_timezone_set('Asia/Hong_Kong');
//date_default_timezone_set('HKT/8.0/no DST');

//-----------------------------------------------------------------------------
// Session
//-----------------------------------------------------------------------------
	if ($without_session_start !== true) {
		session_start();
	}

//-----------------------------------------------------------------------------
// Send default header
//-----------------------------------------------------------------------------
	if ($without_send_header !== true) {
		// IE use last version
		if ($without_use_last_ie_version !== true) {
			header('X-UA-Compatible: IE=edge,chrome=1');
		}
		// Powered by
		if ($without_x_powered_by !== true) {
			header('X-Powered-By: OneSolution');
		}
		// No cache
		if ($without_no_cache_header !== true) {
			// Expires in the past
			header('Expires: Mon, 26 Jul 1990 05:00:00 GMT');
			// Always modified
			header('Last-Modified: ' . gmdate("D, d M Y H:i:s") . ' GMT');
			// HTTP/1.1
			header('Cache-Control: no-store, no-cache, must-revalidate');
			header('Cache-control: private', false); // IE 6 FIX
			header('Cache-Control: post-check=0, pre-check=0', false);
			// HTTP/1.0
			header('Pragma: no-cache');
		}
	}

//-----------------------------------------------------------------------------
// Connections
//-----------------------------------------------------------------------------
	if ($without_connection !== true) {
		$PDO_OPTIONS = array(
			PDO::ATTR_CASE              => PDO::CASE_LOWER,
			PDO::ATTR_ERRMODE           => PDO::ERRMODE_EXCEPTION,
			PDO::ATTR_ORACLE_NULLS      => PDO::NULL_NATURAL,
			PDO::ATTR_STRINGIFY_FETCHES => false,
		);

		//-----------------------------------------------------------------------------
		// MySQL
		//-----------------------------------------------------------------------------

		// production
		$dbh = new PDO("mysql:host=192.168.171.21;dbname=onesupport2013db", 'onesolutionsa', 'wir39S!s', $PDO_OPTIONS);

		$sql        = "SET NAMES ?";
		$parameters = array('utf8');
		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");
		}

		//-----------------------------------------------------------------------------
		// SQL Server
		//-----------------------------------------------------------------------------
//	$sqlsrv_dbh = new PDO("sqlsrv:Server=192.168.155.12;Database=oneDB", 'onesupportsa', 'Supp0rt@2013', $PDO_OPTIONS);
	}

//-----------------------------------------------------------------------------
// Global functions
//-----------------------------------------------------------------------------
	require_once(__DIR__ . '/class.phpmailer.php');
	function createPHPMailer()
	{
		$mailer = new PHPMailer();
		$mailer->IsSMTP();
		$mailer->Host     = "localhost";
		$mailer->CharSet  = "UTF-8";
		$mailer->WordWrap = 50;
		$mailer->IsHTML(true);
		return $mailer;
	}

	function redirectAndExit($url)
	{
		header('Location: ' . $url);
		exit;
	}

	function isPost()
	{
		return strtoupper($_SERVER['REQUEST_METHOD']) == 'POST';
	}

	function isGet()
	{
		return strtoupper($_SERVER['REQUEST_METHOD']) == 'GET';
	}

	/**
	 * Wrapper of htmlspecialchars()
	 *
	 * @param string $text
	 * @param string $charset
	 *
	 * @return string
	 */
	function h($text, $charset = null)
	{
//	return htmlspecialchars($text, ENT_QUOTES | ENT_HTML401, isset($charset) ? $charset : 'UTF-8');
		return htmlspecialchars($text, ENT_QUOTES, isset($charset) ? $charset : 'UTF-8');
	}

	/**
	 * Helper class - Util
	 */
	class Util
	{

		public static function isAdmin()
		{
			return $_SESSION['webadmin']['role'] == 1;
		}

		public static function isGuest()
		{
			return $_SESSION['webadmin']['role'] == 4;
		}

		public static function date_to_string(DateTime $datetime, $format = 'Y-m-d')
		{
			return $datetime->format($format);
		}

		public static function time_to_string(DateTime $datetime, $format = 'H:i:s')
		{
			return $datetime->format($format);
		}

		public static function value_to_date_string($value, $format = 'Y-m-d')
		{
			$output = '';
			if (!empty($value)) {
				$date   = DateTime::createFromFormat('Y-m-d', $value);
				$output = $date->format($format);
			}
			return $output;
		}

		public static function value_to_time_string($value, $format = 'H:i')
		{
			$output = '';
			if (!empty($value)) {
				$date   = DateTime::createFromFormat('H:i:s', $value);
				$output = $date->format($format);
			}
			return $output;
		}

		/**
		 * This will give
		 *
		 * $a="/home/a.php";
		 * $b="/home/root/b/b.php";
		 * echo getRelativePath($a,$b), PHP_EOL;  // ./root/b/b.php
		 * and
		 *
		 * $a="/home/apache/a/a.php";
		 * $b="/home/root/b/b.php";
		 * echo getRelativePath($a,$b), PHP_EOL; // ../../root/b/b.php
		 * and
		 *
		 * $a="/home/root/a/a.php";
		 * $b="/home/apache/htdocs/b/en/b.php";
		 * echo getRelativePath($a,$b), PHP_EOL; // ../../apache/htdocs/b/en/b.php
		 * and
		 *
		 * $a="/home/apache/htdocs/b/en/b.php";
		 * $b="/home/root/a/a.php";
		 * echo getRelativePath($a,$b), PHP_EOL; // ../../../../root/a/a.php
		 *
		 * @param type $from
		 * @param type $to
		 *
		 * @return type
		 */
		public static function getRelativePath($from, $to)
		{
			$from    = explode('/', $from);
			$to      = explode('/', $to);
			$relPath = $to;

			foreach ($from as $depth => $dir) {
				// find first non-matching dir
				if ($dir === $to[$depth]) {
					// ignore this directory
					array_shift($relPath);
				} else {
					// get number of remaining dirs to $from
					$remaining = count($from) - $depth;
					if ($remaining > 1) {
						// add traversals up to first matching dir
						$padLength = (count($relPath) + $remaining - 1) * -1;
						$relPath   = array_pad($relPath, $padLength, '..');
						break;
					} else {
						$relPath[0] = './' . $relPath[0];
					}
				}
			}
			return implode('/', $relPath);
		}

		public static function link($filepath)
		{
			$from = $_SERVER['SCRIPT_FILENAME'];
			$to   = realpath($filepath);

			$from = str_replace('\\', '/', $from);
			$to   = str_replace('\\', '/', $to);

			$link = $from == $to ? '' : Util::getRelativePath($from, $to);
			if (empty($link) && $from == $to) {
				$parts = explode('/', $from);
				$count = count($parts);
				$link  = $parts[$count - 1];
			}

			return $link;
		}

	}

//-----------------------------------------------------------------------------
// Pagination
//-----------------------------------------------------------------------------
	abstract class Pagination
	{

		private $number_per_page = 15;
		private $show_near_pages = 6;

		public function setNumberPerPage($number_per_page)
		{
			$this->number_per_page = $number_per_page;
		}

		public function getNumberPerPage()
		{
			return $this->number_per_page;
		}

		abstract protected function count();

		public function getOffset()
		{
			return ($this->getCurrentPage() - 1) * $this->number_per_page;
		}

		public function getLimit()
		{
			return $this->number_per_page;
		}

		public function getCurrentPage()
		{
			return max(intval($_GET['page']), 1);
		}

		private function getQueryString($cleanPage = true)
		{
			$get = $_GET;
			unset($get['page']);
			return $get;
		}

		protected function getPrevLink()
		{
			$page = $this->getCurrentPage() - 1;
			return $this->getPageLink($page);
		}

		protected function getNextLink()
		{
			$page = $this->getCurrentPage() + 1;
			return $this->getPageLink($page);
		}

		protected function getPageLink($page)
		{
			$get         = $this->getQueryString();
			$get['page'] = max($page, 1);
			return '?' . http_build_query($get);
		}

		public function pageCount()
		{
			$count = $this->count();
			$page  = $count / floatval($this->number_per_page);
			if (floor($page) < $page) {
				$page = floor($page) + 1;
			}
			return $page;
		}

		private $_cache_to_string;

		public function toString()
		{
			if (!empty($this->_cache_to_string)) {
				return $this->_cache_to_string;
			}

			$pageCount   = $this->pageCount();
			$currentPage = $this->getCurrentPage();
			ob_start();
			?>
			<div class="pagination">
				<ul>
					<li<?= $currentPage == 1 ? ' class="disabled"' : '' ?>>
						<a href="<?= $this->getPrevLink() ?>">&laquo;</a></li>
					<?php
						$is_dot = false;
						for ($i = 1; $i <= max($pageCount, 1); $i++):
							$is_show = $i == 1 || $i == $pageCount || abs($currentPage - $i) < $this->show_near_pages;
							if ($is_show):
								$is_dot = true;
								?>
								<li<?= $currentPage == $i ? ' class="active"' : '' ?>>
									<a href="<?= $this->getPageLink($i) ?>"><?= $i ?></a></li>
							<?php
							elseif ($is_dot):
								$is_dot = false;
								?>
								<li class="disabled"><a href="javascript:void(0)">...</a></li>
							<?php
							endif;
						endfor;
					?>
					<li<?= $currentPage >= $pageCount ? ' class="disabled"' : '' ?>>
						<a href="<?= $this->getNextLink() ?>">&raquo;</a></li>
				</ul>
			</div>
			<?php
			$this->_cache_to_string = ob_get_contents();
			ob_end_clean();

			return $this->_cache_to_string;
		}

	}

	class MySqlPagination extends Pagination
	{
		private $sql;
		private $parameters;
		private $dbh;

		function __construct($sql, $parameters)
		{
			global $dbh;

			$this->sql        = $sql;
			$this->parameters = $parameters;
			$this->dbh        = $dbh;
		}

		protected function count()
		{
			$sql = $this->sql;
			$sql = "SELECT COUNT(*) AS count FROM ( $sql ) t";
			if (!($sth = $this->dbh->prepare($sql))) {
				throw new Exception("sql prepare statement failure: $sql");
			}
			$sth->setFetchMode(PDO::FETCH_ASSOC);
			if (!$sth->execute($this->parameters)) {
				throw new Exception("sql execute statement failure: $sql");
			}
			$record = $sth->fetch(PDO::FETCH_ASSOC);

			return $record['count'];
		}

		public function getSqlLimitAndOffset()
		{
			$limit  = $this->getLimit();
			$offset = $this->getOffset();
			return " LIMIT $limit OFFSET $offset ";
		}
	}

	class SqlsrvPagination extends Pagination
	{
		private $sql;
		private $parameters;
		private $dbh;

		function __construct($sql, $parameters)
		{
			global $sqlsrv_dbh;

			$this->sql        = $sql;
			$this->parameters = $parameters;
			$this->dbh        = $sqlsrv_dbh;
		}

		protected function count()
		{
			$sql = $this->sql;
			$pos = strpos($sql, "ORDER BY");
			if ($pos !== false) {
				$sql = substr($sql, 0, $pos);
			}
			$sql = "SELECT COUNT(*) AS count FROM ( $sql ) t";
			if (!($sth = $this->dbh->prepare($sql))) {
				throw new Exception("sql prepare statement failure: $sql");
			}
			$sth->setFetchMode(PDO::FETCH_ASSOC);
			if (!$sth->execute($this->parameters)) {
				throw new Exception("sql execute statement failure: $sql");
			}
			$record = $sth->fetch(PDO::FETCH_ASSOC);

			return $record['count'];
		}

		public function getSql()
		{
			$sql    = $this->sql;
			$limit  = $this->getLimit();
			$offset = $this->getOffset();

			$limit  = intval($limit);
			$offset = intval($offset);

			if ($offset == 0)
				return preg_replace('/^SELECT\s/i', 'SELECT TOP ' . $limit . ' ', $sql);

			$orderby = stristr($sql, 'ORDER BY');
			$over    = $orderby ? preg_replace('/\"[^,]*\".\"([^,]*)\"/i', '"inner_tbl"."$1"', $orderby) : 'ORDER BY (SELECT 0)';

			// Remove ORDER BY clause from $sql
			$sql = preg_replace('/\s+ORDER BY(.*)/', '', $sql);

			// Add ORDER BY clause as an argument for ROW_NUMBER()
			$sql = "SELECT ROW_NUMBER() OVER ($over) AS \"AR_ROWNUM\", * FROM ($sql) AS inner_tbl";

			$start = $offset + 1;
			$end   = $offset + $limit;
			return "WITH outer_tbl AS ($sql) SELECT * FROM outer_tbl WHERE \"AR_ROWNUM\" BETWEEN $start AND $end";
		}
	}

//-----------------------------------------------------------------------------
// Sign in
//-----------------------------------------------------------------------------
	class SigninController
	{

		public function isLogin()
		{
			return isset($_SESSION['webadmin']['id']) && strlen($_SESSION['webadmin']['id']);
		}

		protected function redirectMain()
		{
			if ($_SESSION['webadmin']["role"] == 1) { //admin
				redirectAndExit(Util::link(__DIR__ . '/../contract/contract_alert.php'));
			} else {
				redirectAndExit(Util::link(__DIR__ . '/../main.php') . '?' . http_build_query($_GET));
			}

		}

		protected function redirectLogin()
		{
			redirectAndExit(Util::link(__DIR__ . '/../login.php') . '?' . http_build_query($_GET));
		}

		public function checkLogin()
		{
			if (!$this->isLogin()) {
				$this->redirectLogin();
			}
		}

		public function login()
		{
			global $dbh;

			if ($this->isLogin()) {
				$this->redirectMain();
			}

			$message = null;
			if (!empty($_GET['message'])) {
				$message = $_GET['message'];
			}
			$message_heading = 'Error!';
			if (!empty($_GET['message_heading'])) {
				$message_heading = $_GET['message_heading'];
			}
			$message_css_class = 'alert-error';
			if (!empty($_GET['message_css_class'])) {
				$message_css_class = $_GET['message_css_class'];
			}

			if (isPost()) {
				$name     = $_POST['name'];
				$password = $_POST['password'];
				$sql      = "SELECT * FROM sys_login WHERE loginname = ? AND loginpw = ? AND actived = ? AND deleted = ?";
				if (!($sth = $dbh->prepare($sql))) {
					throw new Exception("sql prepare statement failure: $sql");
				}
				$sth->setFetchMode(PDO::FETCH_ASSOC);
				if (!$sth->execute(array($name, md5($password), 1, 0))) {
					throw new Exception("sql execute statement failure: $sql");
				}
				$staff = $sth->fetch(PDO::FETCH_ASSOC);
				if (!empty($staff)) {
					// Assign webadmin session
					$_SESSION['webadmin'] = $staff;

					// Redirect to job
					$this->redirectMain();
				}
				$message           = 'User name or password not correct.';
				$message_heading   = 'Error!';
				$message_css_class = 'alert-error';
			}

			return array(
				'message'           => $message,
				'message_heading'   => $message_heading,
				'message_css_class' => $message_css_class,
			);
		}

		public function logout()
		{
			// Clear webadmin session
			unset($_SESSION['webadmin']);

			// Redirect to login
			$this->redirectLogin();
		}

	}

//-----------------------------------------------------------------------------
// Static values
//-----------------------------------------------------------------------------
	class Staff
	{

		public static function roleOptions()
		{
			return array(
				1 => 'Admin',
				2 => 'Engine',
				3 => 'Support',
				4 => 'Guest',
			);
		}

		public static function staff_name($staff_id)
		{
			global $dbh;

			$sql = "SELECT * from sys_login where id = ?";

			if (!($sth = $dbh->prepare($sql))) {
				throw new Exception("sql prepare statement failure: $sql");
			}
			$sth->setFetchMode(PDO::FETCH_ASSOC);
			$parameters = array((int)$staff_id);
			if (!$sth->execute($parameters)) {
				throw new Exception("sql execute statement failure: $sql");
			}
			$staff_detail = $sth->fetchAll();

			return $staff_detail;
		}

	}

	class JobDetail
	{
		public static function statusOptions()
		{
			return array(
				1 => 'Open',
				2 => 'Completed',
				3 => 'Follow up',
				4 => 'Cancel',
			);
		}

		public static function statusOptions2($statusnum)
		{
			if ($statusnum == 1)
				return 'Open';
			if ($statusnum == 2)
				return 'Completed';
			if ($statusnum == 3)
				return 'Follow up';
			if ($statusnum == 4)
				return 'Cancel';
		}
	}

	class JobPending
	{
		public static function convert_to_job_detail($job_pending, array $job_detail_attributes)
		{
			$attributes = array_merge($job_detail_attributes, array(
				'from_job_pending_id' => $job_pending['id'],
			));
			// copy fields
			$copy_fields = array('title', 'remarks');
			foreach ($copy_fields as $field) {
				$attributes[$field] = $job_pending[$field];
			}
			return $attributes;
		}
	}

	class Job
	{

		public static function week()
		{
			return array(
				1 => 'Mon',
				2 => 'Tue',
				3 => 'Wed',
				4 => 'Thu',
				5 => 'Fri',
				6 => 'Sat',
				7 => 'Sun',
			);
		}

		public static function statusOptions()
		{
			return array(
				1 => 'Open',
				2 => 'Close',
				3 => 'Void',
			);
		}

		public static function activeperiod()
		{
			return array(
				1 => 'Daily',
				2 => 'Weekly',
				3 => 'Monthly',
			);
		}

		public static function whichweek()
		{
			return array(
				1 => 'The First Week of Month (1-7)',
				2 => 'The Second Week of Month (8-14)',
				3 => 'The Third Week of Month (15-21)',
				4 => 'The Fourth Week of Month (22-28)',
				5 => 'The Fifth Week of Month (29-31)',
			);
		}

		public static function monthly_task_option($customer_id)
		{
			global $dbh;

			$sql        = "SELECT * from sup_monthly_task where customer_id = '" . (int)$customer_id . "'";
			$parameters = array();
			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");
			}
			$monthly_tasks = $sth->fetchAll();

			return $monthly_tasks;
		}

		public static function typeOptions()
		{
			return array(
				1 => 'Schedule Visit',
				2 => 'Service Call',
				3 => 'Urgent Call',
				4 => 'Remote Service Call',
			);
		}

		public static function typeOptions2($typenum)
		{
			if ($typenum == 1)
				return 'Schedule Visit';
			if ($typenum == 2)
				return 'Service Call';
			if ($typenum == 3)
				return 'Urgent Call';
			if ($typenum == 4)
				return 'Remote Service Call';

		}

		public static function createDetails($keyValues)
		{
			global $dbh;

			if (empty($keyValues)) {
				$keyValues = array();
			}

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

			$job_pending_ids    = array();
			$job_pending_values = array();
			//var_dump($keyValues);
			foreach ($keyValues as $id => $attributes) {
				if (isset($_SESSION["all_job_ids"]) && !empty($_SESSION["all_job_ids"])) {
					foreach ($_SESSION["all_job_ids"] as $job_id) {
						$job_detail = array(
							'job_id' => $job_id,
						);

						foreach ($job_detail_columns as $column) {
							$column = $column['column_name'];
							if (isset($attributes[$column])) {
								$job_detail[$column] = $attributes[$column];
							}
						}

						// Append record time
						$now        = date("Y-m-d H:i:s");
						$job_detail = array_merge($job_detail, array(
							'createdate' => $now,
							'createby'   => $_SESSION['webadmin']['id'],
							'lastupdate' => $now,
							'lastupby'   => $_SESSION['webadmin']['id'],
							'actived'    => 1,
							'deleted'    => 0,
							'status'     => 1, // Open
						));

						$columns    = array();
						$values     = array();
						$parameters = array();
						foreach ($job_detail as $column => $value) {
							$columns[]    = $column;
							$parameters[] = !strlen($value) ? null : $value;
							$values[]     = '?';
						}
						$sql = "INSERT sup_job_detail (" . 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($parameters)) {
							throw new Exception("sql execute statement failure: $sql");
						}

						if (!empty($job_detail['from_job_pending_id'])) {
							$job_pending_ids[]    = $job_detail['from_job_pending_id'];
							$job_pending_values[] = '?';
						}
					}
				} else {
					/*foreach($_SESSION["all_job_ids"] as $job_id){
					$job_detail = array(
						'job_id' => $job_id,
						'actived' => 1,
						'deleted' => 0,
						'status' => 1, // Open
					);*/
					foreach ($job_detail_columns as $column) {
						$column = $column['column_name'];
						if (isset($attributes[$column])) {
							$job_detail[$column] = $attributes[$column];
						}
					}

					// Append record time
					$now        = date("Y-m-d H:i:s");
					$job_detail = array_merge($job_detail, array(
						'createdate' => $now,
						'createby'   => $_SESSION['webadmin']['id'],
						'lastupdate' => $now,
						'lastupby'   => $_SESSION['webadmin']['id'],
						'actived'    => 1,
						'deleted'    => 0,
						'status'     => 1, // Open
					));

					$columns    = array();
					$values     = array();
					$parameters = array();
					foreach ($job_detail as $column => $value) {
						$columns[]    = $column;
						$parameters[] = !strlen($value) ? null : $value;
						$values[]     = '?';
					}
					$sql = "INSERT sup_job_detail (" . 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($parameters)) {
						throw new Exception("sql execute statement failure: $sql");
					}

					if (!empty($job_detail['from_job_pending_id'])) {
						$job_pending_ids[]    = $job_detail['from_job_pending_id'];
						$job_pending_values[] = '?';
					}
				}


			}
			/*if (!empty($job_pending_ids)) {
				$job_pending = array(
					'actived' => 0,
				);

				// Append record time
				$now = date("Y-m-d H:i:s");
				$job_pending = array_merge($job_pending, array(
					'lastupdate' => $now,
					'lastupby' => $_SESSION['webadmin']['id'],
				));

				// Update job detail
				$values = array();
				$parameters = array();
				foreach ($job_pending as $column => $value) {
					if ($column != 'id') {
						$parameters[] = !strlen($value) ? null : $value;
						$values[] = "`$column` = ?";
					}
				}

				$sql = "UPDATE sup_job_pending SET " . implode(', ', $values) . " WHERE id IN (" . implode(', ', $job_pending_values) . ")";
				$parameters = array_merge($parameters, $job_pending_ids);
				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");
				}
			}*/
		}

		public static function updateDetails($keyValues, array $job_details)
		{
			global $dbh;

			if (empty($keyValues)) {
				$keyValues = array();
			}

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

			$job_detail_map = array();
			foreach ($job_details as $job_detail) {
				$job_detail_id                  = $job_detail['id'];
				$job_detail_map[$job_detail_id] = $job_detail;
			}

			foreach ($keyValues as $id => $attributes) {
				$job_detail = $job_detail_map[$id];
				if (empty($job_detail)) {
					throw new Exception('Job detail not found!');
				}

				foreach ($job_detail_columns as $column) {
					$column = $column['column_name'];
					if (isset($attributes[$column])) {
						$job_detail[$column] = $attributes[$column];
					}
				}

				// Append record time
				$now        = date("Y-m-d H:i:s");
				$job_detail = array_merge($job_detail, array(
					'lastupdate' => $now,
					'lastupby'   => $_SESSION['webadmin']['id'],
				));

				$values     = array();
				$parameters = array();
				foreach ($job_detail as $column => $value) {
					if ($column != 'id') {
						$parameters[] = !strlen($value) ? null : $value;
						$values[]     = "`$column` = ?";
					}
				}
				$sql          = "UPDATE sup_job_detail SET " . implode(', ', $values) . " WHERE id = ?";
				$parameters[] = $job_detail['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");
				}
			}
		}

		public static function allow_change_status_to_confirm($id)
		{
			global $dbh;

			$is_allow = true;

			$sql        = "SELECT * FROM sup_job_detail WHERE job_id = ?";
			$parameters = array($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");
			}
			$job_details = $sth->fetchAll();

			// check any detail status is open
			foreach ($job_details as $job_detail) {
				if ($job_detail['status'] == 1) {
					$is_allow = false;
					break;
				}
			}

			return $is_allow;
		}

	}


//contract
	class Contract
	{
		public static function statusOptions($data = NULL)
		{
			if (empty($data)) {
				return array(
					1 => 'Open',
					2 => 'Close',
					3 => 'Void',
					4 => 'Expired'
				);
			} else {
				if ($data == 1)
					return 'Open';
				if ($data == 2)
					return 'Close';
				if ($data == 3)
					return 'Void';
				if ($data == 4)
					return 'Expired';
			}
		}

		public static function typeOptions($data = NULL)
		{
			if (empty($data)) {
				return array(
					1 => 'Schedule Visit',
					2 => 'Service Call',
					3 => 'Urgent Call',
					4 => 'Remote Service Call',
				);
			} else {
				if ($data == 1)
					return 'Schedule Visit';
				if ($data == 2)
					return 'Service Call';
				if ($data == 3)
					return 'Urgent Call';
				if ($data == 4)
					return 'Remote Service Call';
			}
		}
	}

//Project
	class Project
	{
		public static function statusOptions()
		{
			return array(
				1 => 'Open',
				2 => 'Close',
				//3 => 'Void',
			);
		}

		public static function statusOptions2($status)
		{
			if ($status == 1)
				return 'Open';
			if ($status == 2)
				return 'Close';
			//if($status == 3)
			//return 'Void';
		}


	}

//Project
	class Customer
	{

		public static function company_name($customer_id)
		{
			global $dbh;

			$sql = "SELECT * from v_cm_customer_support where cust_id = ?";

			if (!($sth = $dbh->prepare($sql))) {
				throw new Exception("sql prepare statement failure: $sql");
			}
			$sth->setFetchMode(PDO::FETCH_ASSOC);
			$parameters = array($customer_id);
			if (!$sth->execute($parameters)) {
				throw new Exception("sql execute statement failure: $sql");
			}
			$customer_detail = $sth->fetchAll();

			return $customer_detail;
		}

	}


	class monthly_task_statusOption
	{
		public static function statusOptions()
		{
			return array(
				1 => 'Open',
				2 => 'Completed',
				3 => 'Cancel',
			);
		}
	}

	class period_task_statusOption
	{
		public static function statusOptions()
		{
			return array(
				1 => 'Open',
				2 => 'Completed',
				3 => 'Cancel',
			);
		}
	}


	if (!function_exists('contract_type')) {
		function contract_type($value)
		{
			if ($value == "1")
				return "Monthly";
			else if ($value == "2")
				return "Package";
			else
				return "Error";
		}
	}

	function get_month_name($month)
	{
		$months = array(
			1  => 'Jan',
			2  => 'Feb',
			3  => 'Mar',
			4  => 'Apr',
			5  => 'May',
			6  => 'Jun',
			7  => 'Jul',
			8  => 'Aug',
			9  => 'Sep',
			10 => 'Oct',
			11 => 'Nov',
			12 => 'Dec'
		);

		return $months[$month];
	}


	function dateRange($first, $last, $step = '+1 month', $format = 'Y-m')
	{
		$dates   = array();
		$current = strtotime($first);
		$last    = strtotime($last);

		while ($current <= $last) {
			$dates[] = date($format, $current);
			$current = strtotime($step, $current);
		}
		return $dates;
	}