<?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.xlsx');

	$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 = ? and status != ? and deleted = ?";

	$parameters3 = array($end_date, $start_date, $customer_id, "3", "0");

	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 += 3;
	} else {
		$rownum += 4;
	}


//}

	//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 job.job_type='JOB'
		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
	";

	$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) {


		if (empty($this_job["call_num"])) {
			$this_call_numm = 1;
		} else {
			$this_call_numm = $this_job["call_num"];
		}

		$objWorksheet->getCell('A' . $rownum)->setValue($this_job["call_date"]);
		$objWorksheet->getCell('B' . $rownum)->setValue($this_job["call_time"]);
		if (empty($this_job["project_id"])) {
			$objWorksheet->getCell('C' . $rownum)->setValue(Job::typeOptions2($this_job["type"]) . " (" . $this_call_numm . ")");
		}

		$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['actual_end_time'])) {
			//change the end time from 00 to 59
			$time_explode = explode(':', $this_job['actual_end_time']);
			$hours        = $time_explode[0];
			$minutes      = $time_explode[1];

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

				$this_job['actual_end_time'] = $hours . ":" . $minutes . ":00";
			}
		}

		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'),
				)
			);
		}

		//job type
		/*if (!empty($this_job["project_id"])) {
			$job_type = "Project #" . $this_job["project_id"] . " (" . $this_call_numm . ")";

			$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);*/
		$staff_list = $this_job["username"];

		if(!empty($this_job["other_staff_id"])){
			$other_staff_id_list = explode(",", $this_job["other_staff_id"]);

			$other_staff_name_list = "";
			foreach($other_staff_id_list as $staff_id){
				$sql3 = "SELECT * from sys_login where id = ?";

				$parameters3 = array($staff_id);

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

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

				$staff_info = $sth3->fetch();

				$other_staff_name_list .= ", ".$staff_info["username"];
			}
		}

		$objWorksheet->getCell('I' . $rownum)->setValue($staff_list.$other_staff_name_list);

		foreach (range('A', 'I') as $letter) {

			$objWorksheet->getStyle($letter . $rownum)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
		}

		$rownum++;

	}

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


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

	$parameters4 = array($end_date, $start_date, $customer_id, "3", "0");

	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 job.job_type='JOB'
		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 ($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"];

			if (empty($company["call_num"])) {
				$this_call_numm = 1;
			} else {
				$this_call_numm = $company["call_num"];
			}

			$total_flag = 1;
			$total_job++;
			if ($company["type"] == 1)
				$num_schedule_call += $this_call_numm;
//				$num_schedule_call++;
			else if ($company["type"] == 2)
				$num_services_call += $this_call_numm;
//				$num_services_call++;
			else if ($company["type"] == 3)
				$num_urgent_call += $this_call_numm;
//				$num_urgent_call++;
			else if ($company["type"] == 4)
				$num_remote_call += $this_call_numm;
//				$num_remote_call++;
			else if (empty($company["type"])) {
				$num_project += $this_call_numm;
//				$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*2;
			} 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']*2;
			} 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'),
				)
			);
		}


		foreach (range('A', 'H') as $letter) {

			$objWorksheet->getStyle($letter . $rownum)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
		}

		$rownum++;
		$objWorksheet->getCell('A' . $rownum)->setValue("Project Call");
		$objWorksheet->getCell('B' . $rownum)->setValue($num_project);
		$objWorksheet->getStyle('B' . $rownum)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
	}

	//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 job.job_type='JOB'
		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
		";

	$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, 2);
		$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;
		$unknown_num_project_call  = 0;

		foreach ($unknown_contract_jobs as $unknown_contract_job) {
			if ($unknown_contract_job["type"] == 1)
				$unknown_num_schedule_call += $unknown_contract_job["call_num"];
//				$unknown_num_schedule_call++;
			else if ($unknown_contract_job["type"] == 2)
				$unknown_num_services_call += $unknown_contract_job["call_num"];
//				$unknown_num_services_call++;
			else if ($unknown_contract_job["type"] == 3)
				$unknown_num_urgent_call += $unknown_contract_job["call_num"];
//				$unknown_num_urgent_call++;
			else if ($unknown_contract_job["type"] == 4)
				$unknown_num_remote_call += $unknown_contract_job["call_num"];
//				$unknown_num_remote_call++;
			else if (empty($unknown_contract_job["type"])) {
				$unknown_num_project_call += $unknown_contract_job["call_num"];
//				$num_project++;
			} 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)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
		}

		$rownum++;

		$objWorksheet->getCell('A' . $rownum)->setValue("Project Call");
		$objWorksheet->getStyle('A' . $rownum)->getFont()->setBold(false);
		$objWorksheet->getCell('B' . $rownum)->setValue($unknown_num_project_call);
		$objWorksheet->getStyle('B' . $rownum)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

		/*$rownum += 3;*/
	}

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

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

	//$rownum-=1;
	//foreach($job_id_array as $key => $job_id ){
	foreach ($all_jobs as $this_job) {

		$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('A', 'I') 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);
			}


			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"]);
			$JobDetail_task_statusOption = JobDetail::statusOptions();
			$objWorksheet->getCell('I' . $rownum)->setValue($JobDetail_task_statusOption[$job_detail['status']]);

			$rownum++;
		}

		foreach (range('A', 'I') 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->removeRow($rownum, 1);


	//if this customer has contract, show the period task

	$sql6 = "select task_category.*, ptask.* from sup_task_category task_category, sup_period_task ptask where ptask.task_category_id = task_category.task_category_id and ptask.customer_id = ? and ptask.start_date <= ? and ? <= ptask.end_date and ptask.status != ? and ptask.deleted = ?";

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

	$sth6->setFetchMode(PDO::FETCH_ASSOC);

	if (!$sth6->execute(array($customer_id, $end_date, $start_date, "3", "0"))) {
		throw new Exception("sql execute statement failure: $sql6");
	}

	$all_ptasks = $sth6->fetchAll();

	if ($sth4->rowCount() > 0 && $contract_type == "Monthly" && !empty($all_ptasks)) {
		$rownum ++;

		$objWorksheet->getCell('A' . $rownum)->setValue("Task Type");
		$objWorksheet->getCell('B' . $rownum)->setValue("Task Period");
		$objWorksheet->getCell('C' . $rownum)->setValue("Competed Date");
		$objWorksheet->getCell('D' . $rownum)->setValue("Task Category");
		$objWorksheet->getCell('E' . $rownum)->setValue("Task Title");
		$objWorksheet->getCell('F' . $rownum)->setValue("Task Remark");
		$objWorksheet->getCell('G' . $rownum)->setValue("Task Status");
		foreach (range('A', 'G') 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()->setBold(true);
			$objWorksheet->getStyle($letter . $rownum)->getFill()->applyFromArray(
				array(
					'type'  => PHPExcel_Style_Fill::FILL_SOLID,
					'color' => array('rgb' => 'C0C0C0'),
				)
			);

		}


		if (!empty($all_ptasks)) {
			foreach ($all_ptasks as $ptask) {
				$rownum++;

				$objWorksheet->getCell('A' . $rownum)->setValue("Monthly Task");
				$objWorksheet->getCell('B' . $rownum)->setValue($ptask["start_date"] . " - " . $ptask["end_date"]);

				if (!empty($ptask["completed_date"]) && $ptask["completed_date"] != "0000-00-00") {
					$completed_date = $ptask["completed_date"];
				} else {
					$completed_date = "";
				}

				$objWorksheet->getCell('C' . $rownum)->setValue($completed_date);
				$objWorksheet->getCell('D' . $rownum)->setValue($ptask["category_name"]);
				$objWorksheet->getCell('E' . $rownum)->setValue($ptask["task_title"]);
				$objWorksheet->getCell('F' . $rownum)->setValue($ptask["task_remarks"]);

				$period_task_statusOption = period_task_statusOption::statusOptions();
				$objWorksheet->getCell('G' . $rownum)->setValue($period_task_statusOption[$ptask['status']]);


				foreach (range('A', 'G') as $letter) {
					//$objWorksheet->getStyle($letter . $rownum)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

					$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);
				}
			}
		} else {
			$rownum++;
		}


	}


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

	}


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

	header('Content-Type: application/vnd.ms-excel');

	if (date("m", strtotime($start_date)) == date("m", strtotime($end_date)))
		header('Content-Disposition: attachment;filename="ClientServiceReport' . date("M", strtotime($start_date)) . '.xlsx"');
	else
		header('Content-Disposition: attachment;filename="ClientServiceReport' . date("M", strtotime($start_date)) . '_to_' . date("M", strtotime($end_date)) . '.xlsx"');

	header('Cache-Control: max-age=0');

	$objPHPexcel->setActiveSheetIndex(0);
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel2007');
	$objWriter->save('php://output');


	flush();

?>