<?php
	include '../inc/configure.php';
	date_default_timezone_set('Asia/Hong_Kong');

	error_reporting(E_ALL ^ E_NOTICE ^ E_WARNING);
	ini_set('display_errors', 1);

// Check if the user is logged in

	// Check permission
	if (!Util::isAdmin()) {
		redirectAndExit(Util::link(__DIR__ . '/../main.php'));
	}

	require_once "phpexcel/PHPExcel.php";
	require_once 'func_tcpdf.php';

	/*--------------------------------END OF FUNCTIONS--------------------------------------*/


	$customer_id = isset($_POST["_customer_id"]) ? $_POST["_customer_id"] : null;
	$staff_id    = isset($_POST["_staff_id"]) ? $_POST["_staff_id"] : null;

	$start_date  = isset($_POST["_start_date"]) ? $_POST["_start_date"] : null;
	$end_date    = isset($_POST["_end_date"]) ? $_POST["_end_date"] : null;

	$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();

	foreach ($staffs as $key => $staff) {
		$staffs_list[$staff["id"]] = $staff["username"];
	}

	//echo $customer_id."=".$staff_id."=".$start_date."=".$end_date;
	//exit;
	/*$sql = "
SELECT job.*,customer.*,staff.*, job.id as job_id
FROM sup_job job, v_cm_customer_support customer, sys_login staff
WHERE job.customer_id = customer.cust_id AND job.staff_id = staff.id
	AND (COALESCE(LENGTH(?), 0) = 0 OR job.customer_id = ?)
	AND (COALESCE(LENGTH(?), 0) = 0 OR job.staff_id = ?)
	AND (COALESCE(LENGTH(?), 0) = 0 OR job.`date` >= ?)
	AND (COALESCE(LENGTH(?), 0) = 0 OR job.`date` <= ?)
GROUP BY job.customer_id
ORDER BY customer.company_name ASC,job.id ASC
";
	$parameters = array(
		$customer_id, $customer_id,
		$staff_id, $staff_id,
		$start_date, $start_date,
		$end_date, $end_date,
	);


	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");
	}

	$num_companies = $sth->fetchAll();
*/


	$defaultFontface = 'Times New Roman';

	@set_time_limit(0);

	ini_set('post_max_size', '10M');
	ini_set('memory_limit', '1024M');
	$showimg = true;

	$objPHPexcel = PHPExcel_IOFactory::load('phpexcel/ClientServiceReport.xls');

	$objWorksheet = $objPHPexcel->getSheetByName('ClientServiceReport');

	$rownum = 2;

	//foreach ($num_companies as $key => $companies){

	//set contract detail to excel
	$sql3 = "SELECT customer.* FROM v_cm_customer_support customer WHERE customer.cust_id = ?";

	$parameters3 = array($customer_id);

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

	if (!$sth->execute($parameters3)) {
		throw new Exception("sql execute statement failure: $sql3");
	}
	//$contract = $sth->fetch(PDO::FETCH_ASSOC);

	$client_info = $sth->fetch(PDO::FETCH_ASSOC);

	if($client_info){
		$objWorksheet->getCell('B' . $rownum)->setValue(date("Y", strtotime($start_date)));
		$rownum++;

		if (date("m", strtotime($start_date)) == date("m", strtotime($end_date)))
			$objWorksheet->getCell('B' . $rownum)->setValue(date("F", strtotime($start_date)));
		else
			$objWorksheet->getCell('B' . $rownum)->setValue(date("F", strtotime($start_date)) . " To " . date("F", strtotime($end_date)));
		$rownum++;

		$objWorksheet->getCell('B' . $rownum)->setValue($client_info["cust_id"]);
		$rownum++;

		$objWorksheet->getCell('B' . $rownum)->setValue($client_info["company_name"]);
	}


	//get contract data

	$sql3 = "SELECT * FROM sup_contract WHERE contract_from <= ? and ? <= contract_to and customer_id = ?";

	$parameters3 = array($end_date, $start_date, $customer_id);

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

	if (!$sth->execute($parameters3)) {
		throw new Exception("sql execute statement failure: $sql3");
	}
	//$contract = $sth->fetch(PDO::FETCH_ASSOC);
	$rownum++;
	$contract_num = 0;
	$contract_ids = array();
	$unknow_contract_job_ids = array();
	$selected_job_ids = array();

	while ($contract = $sth->fetch(PDO::FETCH_ASSOC)) {

		//set contract detail
		$contract_ids[] = $contract["contract_id"];
		$contract_num++;
		$objWorksheet->insertNewRowBefore($rownum,3);


		//$objWorksheet->getCell('A' . $rownum)->setValue("Contract ".$contract_num);
		$objWorksheet->getCell('A' . $rownum)->setValue("Contract #".$contract["contract_id"]);
		$objWorksheet->getCell('B' . $rownum)->setValue("Type:");
		$objWorksheet->getStyle('B' . $rownum)->getFont()->setBold(true);

		if($contract["contract_type"] == 1)
			$contract_type = "Monthly";
		else if($contract["contract_type"] == 2)
			$contract_type = "Package";
		else{}

		$objWorksheet->getCell('C' . $rownum)->setValue($contract_type);
		$objWorksheet->getCell('D' . $rownum)->setValue("Period:");
		$objWorksheet->getStyle('D' . $rownum)->getFont()->setBold(true);
		$objWorksheet->getCell('E' . $rownum)->setValue($contract["contract_from"]." To ".$contract["contract_to"]);
		$rownum++;

		$objWorksheet->getCell('A' . $rownum)->setValue("Admin In Charge");
		$objWorksheet->getStyle('A' . $rownum)->getFont()->setBold(true);
		$objWorksheet->getCell('D' . $rownum)->setValue("e-mail:");
		$objWorksheet->getCell('E' . $rownum)->setValue($contract["email"]);
		$objWorksheet->getCell('F' . $rownum)->setValue("support e-mail:");
		$objWorksheet->getCell('H' . $rownum)->setValue($contract["email_support"]);
		$rownum++;

		$objWorksheet->getCell('A' . $rownum)->setValue("Engineer In Charge");
		$objWorksheet->getStyle('A' . $rownum)->getFont()->setBold(true);
		$objWorksheet->getCell('B' . $rownum)->setValue($staffs_list[$contract["network_engineer"]]);
		$objWorksheet->getCell('D' . $rownum)->setValue("Technician");
		$technician_list = $staffs_list[$contract["technician1"]];
		if (!empty($contract["technician2"]))
			$technician_list .= ", " . $staffs_list[$contract["technician2"]];

		if (!empty($contract["technician3"]))
			$technician_list .= ", " . $staffs_list[$contract["technician3"]];
		$objWorksheet->getCell('E' . $rownum)->setValue($technician_list);
		$rownum+=2;

	}
	$objWorksheet->removeRow($rownum,1);

	if($sth->rowCount() > 1){
		$rownum += 2;
	}else{

		$rownum += 3;
	}


//}

	//list call detail (all type)
	$sql2 = "
	SELECT job.*,customer.*,staff.*, job.id as job_id
	FROM sup_job job, v_cm_customer_support customer, sys_login staff
	WHERE job.customer_id = customer.cust_id AND job.staff_id = staff.id
		AND (COALESCE(LENGTH(?), 0) = 0 OR job.customer_id = ?)
		AND (COALESCE(LENGTH(?), 0) = 0 OR job.`date` >= ?)
		AND (COALESCE(LENGTH(?), 0) = 0 OR job.`date` <= ?)
	ORDER BY job.date ASC
	";
	/*if ($start_date < $contract["contract_from"])
		$search_job_start_date = $contract["contract_from"];
	else
		$search_job_start_date = $start_date;

	if ($end_date < $contract["contract_to"])
		$search_job_end_date = $end_date;
	else
		$search_job_end_date = $contract["contract_to"];

	*/

	$parameters2 = array(
		$customer_id, $customer_id,
		$start_date, $start_date,
		$end_date, $end_date
	);


	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");
	}

	$all_jobs = $sth2->fetchAll();

	$fetch_row_num = $sth2->rowCount();

	if($fetch_row_num <= 0 ){
		header("Location: ClientServiceReport.php?message=No Data of this report!");
		exit;
	}

	$objWorksheet->insertNewRowBefore($rownum,$fetch_row_num);

	$rownum--;

	foreach ($all_jobs as $this_job) {


		$objWorksheet->getCell('A' . $rownum)->setValue($this_job["call_date"]);
		$objWorksheet->getCell('B' . $rownum)->setValue($this_job["call_time"]);
		$objWorksheet->getCell('C' . $rownum)->setValue(Job::typeOptions2($this_job["type"]));
		$objWorksheet->getCell('D' . $rownum)->setValue($this_job["date"]);
		$objWorksheet->getCell('E' . $rownum)->setValue($this_job["start_time"]);
		$objWorksheet->getCell('F' . $rownum)->setValue($this_job["end_time"]);
		$objWorksheet->getCell('G' . $rownum)->setValue($this_job["actual_start_time"]);
		if(!empty($this_job["project_id"])){
			$job_type = "Project #".$this_job["project_id"];

			$objWorksheet->getStyle('I' . $rownum)->getFill()->applyFromArray(
				array(
					'type'  => PHPExcel_Style_Fill::FILL_SOLID,
					'color' => array('rgb' => 'FFFF85'),
				)
			);

		}else{
			if(!empty($this_job["contract_id"])){
				$job_type = "Contract #".$this_job["contract_id"];

				$objWorksheet->getStyle('I' . $rownum)->getFill()->applyFromArray(
					array(
						'type'  => PHPExcel_Style_Fill::FILL_SOLID,
						'color' => array('rgb' => '75D1FF'),
					)
				);

			}else{
				$job_type = "";
			}
		}
		$objWorksheet->getCell('I' . $rownum)->setValue($job_type);

		if (!empty($this_job['actual_start_time']) && $this_job['actual_start_time'] > $this_job['start_time']) {
			$objWorksheet->getStyle('G' . $rownum)->getFill()->applyFromArray(
				array(
					'type'  => PHPExcel_Style_Fill::FILL_SOLID,
					'color' => array('rgb' => 'FF0000'),
				)
			);
		}

		$objWorksheet->getCell('H' . $rownum)->setValue($this_job["actual_end_time"]);

		if (!empty($this_job['actual_end_time']) && $this_job['actual_end_time'] > $this_job['end_time']) {
			$objWorksheet->getStyle('H' . $rownum)->getFill()->applyFromArray(
				array(
					'type'  => PHPExcel_Style_Fill::FILL_SOLID,
					'color' => array('rgb' => 'FF0000'),
				)
			);
		}

		$rownum++;


	}

	$objWorksheet->removeRow($rownum,1);
	$rownum+=3;



	$sql4 = "SELECT * FROM sup_contract WHERE contract_from <= ? and ? <= contract_to and customer_id = ?";

	$parameters4 = array($end_date, $start_date, $customer_id);

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

	if (!$sth4->execute($parameters4)) {
		throw new Exception("sql execute statement failure: $sql4");
	}
	while ($contract = $sth4->fetch(PDO::FETCH_ASSOC)) {
		//foreach($contract_ids as $contract_id){
		//calculate call type

		$num_schedule_call = 0;
		$num_services_call = 0;
		$num_urgent_call   = 0;
		$num_remote_call   = 0;
		$num_project       = 0;
		$total_job         = 0;

		$sql2 = "
	SELECT job.*,customer.*,staff.*, job.id as job_id
	FROM sup_job job, v_cm_customer_support customer, sys_login staff
	WHERE job.customer_id = customer.cust_id AND job.staff_id = staff.id
		AND (COALESCE(LENGTH(?), 0) = 0 OR job.customer_id = ?)
		AND (COALESCE(LENGTH(?), 0) = 0 OR job.`date` >= ?)
		AND (COALESCE(LENGTH(?), 0) = 0 OR job.`date` <= ?)
	ORDER BY customer.company_name ASC,job.id ASC
	";

		/*if ($contract["contract_type"] == 1) {
			if ($start_date < $contract["contract_from"])
				$search_job_start_date = $contract["contract_from"];
			else
				$search_job_start_date = $start_date;

			if ($end_date < $contract["contract_to"])
				$search_job_end_date = $end_date;
			else
				$search_job_end_date = $contract["contract_to"];
		} else {
			$search_job_start_date = $contract["contract_from"];
			$search_job_end_date   = $contract["contract_to"];
		}*/

		if ($start_date < $contract["contract_from"])
			$search_job_start_date = $contract["contract_from"];
		else
			$search_job_start_date = $start_date;

		if ($end_date < $contract["contract_to"])
			$search_job_end_date = $end_date;
		else
			$search_job_end_date = $contract["contract_to"];


		$parameters2 = array(
			$customer_id, $customer_id,
			$search_job_start_date, $search_job_start_date,
			$search_job_end_date, $search_job_end_date
		);


		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");
		}

		$this_company = $sth2->fetchAll();


		foreach ($this_company as $company) {

			$selected_job_ids[] = $company["job_id"];

			$total_flag = 1;
			$total_job++;
			if ($company["type"] == 1)
				$num_schedule_call++;
			else if ($company["type"] == 2)
				$num_services_call++;
			else if ($company["type"] == 3)
				$num_urgent_call++;
			else if ($company["type"] == 4)
				$num_remote_call++;
			else if (empty($company["type"])){
				$num_project++;
			}else{}
		}


		$search_date_range   = dateRange($start_date, $end_date, '+1 month');
		$contract_date_range = dateRange($contract["contract_from"], $contract["contract_to"], '+1 month');

		$overlap_month = array();

		foreach ($search_date_range as $search_date) {
			foreach ($contract_date_range as $contract_date) {
				if ($search_date == $contract_date) { //overlap month
					$overlap_month[] = $search_date;
				}
			}
		}

		if ($contract["contract_type"] == 1) { //monthly
			$size_of_overlap_month = sizeof($overlap_month);

			if($size_of_overlap_month == 0)
				$size_of_overlap_month = 1;
			if (!empty($contract['schedule_call_3hrs'])) {
				$contract_schedule_call = $contract['schedule_call_3hrs'] * $size_of_overlap_month;
			} else if (!empty($contract['schedule_call_half_day'])) {
				$contract_schedule_call = $contract['schedule_call_half_day'] * $size_of_overlap_month;
			} else if (!empty($contract['schedule_call_full_day'])) {
				$contract_schedule_call = $contract['schedule_call_full_day'] * $size_of_overlap_month;
			} else {
				$contract_schedule_call = 0;
			}


			if ($contract['service_call'] == 0)
				$contract_service_call = 0;
			else
				$contract_service_call = $contract['service_call'] * $size_of_overlap_month;

			if ($contract['urgent_call'] == 0)
				$contract_urgent_call = 0;
			else
				$contract_urgent_call = $contract['urgent_call'] * $size_of_overlap_month;

			if ($contract['remote_service_call'] == 0)
				$contract_remote_call = 0;
			else
				$contract_remote_call = $contract['remote_service_call'] * $size_of_overlap_month;


			$total_call = $contract_schedule_call + $contract_service_call + $contract_urgent_call + $contract_remote_call;
		} else { //package


			if (!empty($contract['schedule_call_3hrs'])) {
				$contract_schedule_call = $contract['schedule_call_3hrs'];
			} else if (!empty($contract['schedule_call_half_day'])) {
				$contract_schedule_call = $contract['schedule_call_half_day'];
			} else if (!empty($contract['schedule_call_full_day'])) {
				$contract_schedule_call = $contract['schedule_call_full_day'];
			} else {
				$contract_schedule_call = 0;
			}

			if ($contract['service_call'] == 0)
				$contract_service_call = 0;
			else
				$contract_service_call = $contract['service_call'];

			if ($contract['urgent_call'] == 0)
				$contract_urgent_call = 0;
			else
				$contract_urgent_call = $contract['urgent_call'];

			if ($contract['remote_service_call'] == 0)
				$contract_remote_call = 0;
			else
				$contract_remote_call = $contract['remote_service_call'];


			$total_call = $contract_schedule_call + $contract_service_call + $contract_urgent_call + $contract_remote_call;
		}





		$objWorksheet->insertNewRowBefore($rownum,2);

		$objWorksheet->getCell('A' . $rownum)->setValue("Contract #".$contract["contract_id"]);
		$objWorksheet->getStyle('A' . $rownum)->getFont()->setBold(false);

		$rownum++;

		$objWorksheet->getCell('A' . $rownum)->setValue("Schedule Call");
		$objWorksheet->getStyle('A' . $rownum)->getFont()->setBold(false);
		$objWorksheet->getCell('B' . $rownum)->setValue($num_schedule_call." / ".$contract_schedule_call);

		$objWorksheet->getCell('C' . $rownum)->setValue("Service Call");
		$objWorksheet->getCell('D' . $rownum)->setValue($num_services_call." / ".$contract_service_call);

		$objWorksheet->getCell('E' . $rownum)->setValue("Urgent Call");
		$objWorksheet->getCell('F' . $rownum)->setValue($num_urgent_call." / ".$contract_urgent_call);

		$objWorksheet->getCell('G' . $rownum)->setValue("Remote Call");
		$objWorksheet->getCell('H' . $rownum)->setValue($num_remote_call." / ".$contract_remote_call);


		//set color for alert item
		if ($num_schedule_call > $contract_schedule_call) {

			$objWorksheet->getStyle('B' . $rownum)->getFill()->applyFromArray(
				array(
					'type'  => PHPExcel_Style_Fill::FILL_SOLID,
					'color' => array('rgb' => 'FF0000'),
				)
			);

		}

		if ($num_services_call > $contract_service_call) {
			$objWorksheet->getStyle('D' . $rownum)->getFill()->applyFromArray(
				array(
					'type'  => PHPExcel_Style_Fill::FILL_SOLID,
					'color' => array('rgb' => 'FF0000'),
				)
			);
		}

		if ($num_urgent_call > $contract_urgent_call) {
			$objWorksheet->getStyle('F' . $rownum)->getFill()->applyFromArray(
				array(
					'type'  => PHPExcel_Style_Fill::FILL_SOLID,
					'color' => array('rgb' => 'FF0000'),
				)
			);
		}

		if ($num_remote_call > $contract_remote_call) {
			$objWorksheet->getStyle('H' . $rownum)->getFill()->applyFromArray(
				array(
					'type'  => PHPExcel_Style_Fill::FILL_SOLID,
					'color' => array('rgb' => 'FF0000'),
				)
			);
		}


		$rownum+=2;

	}

	//summary of unknow contact job

	$sql3 = "
	SELECT job.*,customer.*,staff.*, job.id as job_id
	FROM sup_job job, v_cm_customer_support customer, sys_login staff
	WHERE job.customer_id = customer.cust_id AND job.staff_id = staff.id
		AND (COALESCE(LENGTH(?), 0) = 0 OR job.customer_id = ?)
		AND (COALESCE(LENGTH(?), 0) = 0 OR job.`date` >= ?)
		AND (COALESCE(LENGTH(?), 0) = 0 OR job.`date` <= ?)
		AND job.contract_id IS NULL AND job.type IS NOT NULL
		";

	$parameters6 = array($customer_id, $customer_id, $start_date,$start_date, $end_date, $end_date);

	foreach($selected_job_ids as $selected_job_id){
		$sql33 .= " AND job.id != ?";
		$parameters6[] = $selected_job_id;
	}

	//var_dump($parameters3);


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

	if (!$sth3->execute($parameters6)) {
		throw new Exception("sql execute statement failure: $sql3");
	}

	$unknown_contract_jobs = $sth3->fetchAll();

	if($sth3->rowCount() > 0){

		$objWorksheet->insertNewRowBefore($rownum,3);
		$has_unknow_contract_job = 1;
		//$rownum++;

		//calculate the type of these jobs
		$unknown_num_schedule_call = 0;
		$unknown_num_services_call = 0;
		$unknown_num_urgent_call = 0;
		$unknown_num_remote_call = 0;

		foreach ($unknown_contract_jobs as $unknown_contract_job) {
			if ($unknown_contract_job["type"] == 1)
				$unknown_num_schedule_call++;
			else if ($unknown_contract_job["type"] == 2)
				$unknown_num_services_call++;
			else if ($unknown_contract_job["type"] == 3)
				$unknown_num_urgent_call++;
			else if ($unknown_contract_job["type"] == 4)
				$unknown_num_remote_call++;
			else {
			}
		}

		$objWorksheet->getCell('A' . $rownum)->setValue("Not Specific For A Contract");
		$objWorksheet->getStyle('A' . $rownum)->getFont()->setBold(false);

		$rownum++;

		$objWorksheet->getCell('A' . $rownum)->setValue("Schedule Call");
		$objWorksheet->getStyle('A' . $rownum)->getFont()->setBold(false);
		$objWorksheet->getCell('B' . $rownum)->setValue($unknown_num_schedule_call);

		$objWorksheet->getCell('C' . $rownum)->setValue("Service Call");
		$objWorksheet->getCell('D' . $rownum)->setValue($unknown_num_services_call);

		$objWorksheet->getCell('E' . $rownum)->setValue("Urgent Call");
		$objWorksheet->getCell('F' . $rownum)->setValue($unknown_num_urgent_call);

		$objWorksheet->getCell('G' . $rownum)->setValue("Remote Call");
		$objWorksheet->getCell('H' . $rownum)->setValue($unknown_num_remote_call);

		foreach (range('A', 'H') as $letter) {
			/*$objWorksheet->getStyle($letter . $rownum)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
			$objWorksheet->getStyle($letter . $rownum)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
			$objWorksheet->getStyle($letter . $rownum)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
			$objWorksheet->getStyle($letter . $rownum)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);*/
			$objWorksheet->getStyle($letter . $rownum)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
		}

		$rownum+=2;
	}


	$objWorksheet->removeRow($rownum,1);

	//set job detail data
	if($sth4->rowCount()>1)
		$rownum++;
	else
		$rownum+=2;

	//foreach($job_id_array as $key => $job_id ){
	foreach ($all_jobs as $this_job) {
		/*
		$sql4 = "select * from sup_job where `id` = ? ";

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

		$sth4->setFetchMode(PDO::FETCH_ASSOC);
		if (!$sth4->execute(array($this_job["job_id"]))) {
			throw new Exception("sql execute statement failure: $sql4");
		}

		$this_job = $sth4->fetch(PDO::FETCH_ASSOC);
		*/

		$objWorksheet->getCell('A' . $rownum)->setValue(Job::typeOptions2($this_job["type"]));
		$objWorksheet->getCell('B' . $rownum)->setValue($this_job["date"]);
		$objWorksheet->getCell('C' . $rownum)->setValue($this_job["actual_start_time"]);
		$objWorksheet->getCell('D' . $rownum)->setValue($this_job["actual_end_time"]);

		if(!empty($this_job["project_id"])){
			$job_type = "Project #".$this_job["project_id"];

			$objWorksheet->getStyle('E' . $rownum)->getFill()->applyFromArray(
				array(
					'type'  => PHPExcel_Style_Fill::FILL_SOLID,
					'color' => array('rgb' => 'FFFF85'),
				)
			);

		}else{
			if(!empty($this_job["contract_id"])){
				$job_type = "Contract #".$this_job["contract_id"];

				$objWorksheet->getStyle('E' . $rownum)->getFill()->applyFromArray(
					array(
						'type'  => PHPExcel_Style_Fill::FILL_SOLID,
						'color' => array('rgb' => '75D1FF'),
					)
				);

			}else{
				$job_type = "";
			}
		}
		$objWorksheet->getCell('E' . $rownum)->setValue($job_type);

		foreach (range('B', 'D') as $letter) {
			$objWorksheet->getStyle($letter . $rownum)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		}

		    //normal job monthly task
			$sql5 = "select task_category.*, job_detail.* from sup_task_category task_category, sup_job_detail job_detail where job_detail.task_category_id = task_category.task_category_id and job_detail.job_id = ?";

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

			$sth5->setFetchMode(PDO::FETCH_ASSOC);
			if (!$sth5->execute(array($this_job["job_id"]))) {
				throw new Exception("sql execute statement failure: $sql5");
			}

			$job_details = $sth5->fetchAll();

			//$objWorksheet->getCell('E' . $rownum)->setValue("N/A");

			foreach($job_details as $key => $job_detail){   //looping normal job task detail
				$key+=1;

				foreach (range('F', 'H') as $letter) {
					$objWorksheet->getStyle($letter . $rownum)->getAlignment()->setWrapText(true);
					$objWorksheet->getStyle($letter . $rownum)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
				}


				$objWorksheet->getCell('F' . $rownum)->setValue($job_detail["category_name"]);
				$objWorksheet->getCell('G' . $rownum)->setValue($key.". ".$job_detail["title"]);
				$objWorksheet->getCell('H' . $rownum)->setValue($job_detail["remarks"]);

				foreach (range('A', 'H') as $letter) {
					$objWorksheet->getStyle($letter . $rownum)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
					$objWorksheet->getStyle($letter . $rownum)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
					$objWorksheet->getStyle($letter . $rownum)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
					$objWorksheet->getStyle($letter . $rownum)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
					$objWorksheet->getStyle($letter . $rownum)->getFont()->setName('Times New Roman')->setSize(12);
				}

				$rownum++;
			}

		foreach (range('A', 'H') as $letter) {
			$objWorksheet->getStyle($letter . $rownum)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
			$objWorksheet->getStyle($letter . $rownum)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
			$objWorksheet->getStyle($letter . $rownum)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
			$objWorksheet->getStyle($letter . $rownum)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
		}
		$rownum++;
	}



	foreach ($objWorksheet->getRowDimensions() as $rd) {
		$rd->setRowHeight(-1);
	}


	$objWorksheet->getHeaderFooter()->setOddHeader('ClientServiceReport' . $start_date . ' To ' . $end_date);

	$objPHPexcel->setActiveSheetIndex(0);
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel5');
	$start_date = date("Y-m-d");
	$objWriter->save("output_report/ClientServiceReport" . $start_date . ".xls");


	flush();

?>