<?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 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.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/MonthlyUsageReport.xls');

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


	$rownum = 3;


	$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 += 3;


	$count_all_job = 0;
	$selected_job_ids = array();
	foreach ($num_companies as $key => $companies) {

		//get contract data and compare call num
		$sql3 = "SELECT * FROM sup_contract WHERE contract_from <= ? and ? <= contract_to and customer_id = ?";

		$parameters3 = array($end_date, $start_date, $companies["customer_id"]);
		if (!($sth = $dbh->prepare($sql3))) {
			throw new Exception("sql prepare statement failure: $sql3");
		}
		$sth->setFetchMode(PDO::FETCH_ASSOC);
		if (!$sth->execute($parameters3)) {
			throw new Exception("sql execute statement failure: $sql3");
		}



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

			if($contract){
				$num_schedule_call = 0;
				$num_services_call = 0;
				$num_urgent_call   = 0;
				$num_remote_call   = 0;
				$num_project       = 0;
				$total_job         = 0;


				//for counting the project num
				/*$sql4 = "
	SELECT job.*, job.id as job_id
	FROM sup_job job
		where (COALESCE(LENGTH(?), 0) = 0 OR job.`date` >= ?)
		AND (COALESCE(LENGTH(?), 0) = 0 OR job.`date` <= ?)
		AND job.type IS NULL
		AND job.project_id IS NOT NULL
	ORDER BY job.id ASC
	";

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


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

				$num_project = $sth4->rowCount();
*/

				//for counting the job type num
				$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 ($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"];
				}


				$parameters2 = array(
					//$customer_id, $customer_id,
					$companies["customer_id"], $companies["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();

				if($sth2->rowCount() <= 0 ){
					//header("Location: MonthlyUsageReport.php?message=No Data of this report!");
					//exit;
					break;
				}

				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{}
				}

				$count_all_job+=$total_job;

				$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->getCell('A' . $rownum)->setValue($company['cust_id']);

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

				$objWorksheet->getCell('E' . $rownum)->setValue($contract['maintenance_cost']);

				$objWorksheet->getCell('F' . $rownum)->setValue($contract['contract_id']);
				if ($contract['contract_type'] == 1) {
					$objWorksheet->getCell('G' . $rownum)->setValue("Monthly");
				} else if ($contract['contract_type'] == 2) {
					$objWorksheet->getCell('G' . $rownum)->setValue("Package");
				}
				$objWorksheet->getCell('H' . $rownum)->setValue($contract["contract_from"]." to ".$contract["contract_to"]);
				$objWorksheet->getStyle('H' . $rownum)->getAlignment()->setWrapText(true);


				$objWorksheet->getCell('I' . $rownum)->setValue($contract_schedule_call);
				$objWorksheet->getCell('J' . $rownum)->setValue($num_schedule_call);
				$objWorksheet->getCell('K' . $rownum)->setValue($contract_service_call);
				$objWorksheet->getCell('L' . $rownum)->setValue($num_services_call);
				$objWorksheet->getCell('M' . $rownum)->setValue($contract_urgent_call);
				$objWorksheet->getCell('N' . $rownum)->setValue($num_urgent_call);
				$objWorksheet->getCell('O' . $rownum)->setValue($contract_remote_call);
				$objWorksheet->getCell('P' . $rownum)->setValue($num_remote_call);
				$objWorksheet->getCell('Q' . $rownum)->setValue($num_project);

				//set right border for cell
				foreach (range('A', 'Q') as $letter) {

					$objWorksheet->getStyle($letter . $rownum)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
					$objWorksheet->getStyle($letter . $rownum)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
					$objWorksheet->getStyle($letter . $rownum)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

				}


				//set color for alert item
				if ($num_schedule_call > $contract_schedule_call) {
					foreach (range('I', 'J') as $letter) {
						$objWorksheet->getStyle($letter . $rownum)->getFill()->applyFromArray(
							array(
								'type'  => PHPExcel_Style_Fill::FILL_SOLID,
								'color' => array('rgb' => 'FF0000'),
							)
						);
					}
				}

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

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

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

				if ($num_project > 0) {
					$objWorksheet->getStyle('Q' . $rownum)->getFill()->applyFromArray(
						array(
							'type'  => PHPExcel_Style_Fill::FILL_SOLID,
							'color' => array('rgb' => 'FFFF00'),
						)
					);
				}

				//align center
				foreach (range('C', 'Q') as $letter) {
					$objWorksheet->getStyle($letter . $rownum)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				}

				$rownum++;


			}
		}
	}

	//set bottom border for cell
	$rownum--;
	$last_company_row = $rownum;
	foreach (range('A', 'Q') as $letter) {
		$objWorksheet->getStyle($letter . $rownum)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	}


	//this part is for unknown contract 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
		";*/

	$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` <= ?)
		";

	$parameters3 = 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 != ?";
		$parameters3[] = $selected_job_id;
	}

	//var_dump($parameters3);


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

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

	$unknown_contract_jobs = $sth3->fetchAll();

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

		$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($count_all_job);
		//$objWorksheet->getCell('A' . $rownum)->setValue($unknown_contract_job['cust_id']);

		//$objWorksheet->getCell('B' . $rownum)->setValue($unknown_contract_job['company_name']);

		$objWorksheet->getCell('F' . $rownum)->setValue("Not Specific For A Contract");

		$objWorksheet->mergeCells('F'.$rownum.":".'H'.$rownum);

		$objWorksheet->getCell('J' . $rownum)->setValue($unknown_num_schedule_call);

		$objWorksheet->getCell('L' . $rownum)->setValue($unknown_num_services_call);

		$objWorksheet->getCell('N' . $rownum)->setValue($unknown_num_urgent_call);

		$objWorksheet->getCell('P' . $rownum)->setValue($unknown_num_remote_call);

		$objWorksheet->getCell('Q' . $rownum)->setValue("0");

		//align center
		foreach (range('C', 'Q') as $letter) {
			$objWorksheet->getStyle($letter . $rownum)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		}

		//set right border for cell
		foreach (range('A', 'Q') as $letter) {

			$objWorksheet->getStyle($letter . $rownum)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
			$objWorksheet->getStyle($letter . $rownum)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
			$objWorksheet->getStyle($letter . $rownum)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

		}

	}


	$rownum++;

	//totoal and sum


	if($total_job > 0){
		$objWorksheet->getCell('B' . $rownum)->setValue("Total:");
		if($has_unknow_contract_job == 1)
			$last_company_row += 1;
		foreach (range('I', 'Q') as $letter) {
			$objWorksheet->getCell($letter . $rownum)->setValue("=SUM(".$letter."7:".$letter.$last_company_row.")");
			$objWorksheet->getStyle($letter . $rownum)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		}
	}




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




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

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

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

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

	flush();

?>