<?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--------------------------------------*/


	$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 j.id, c.company_name, j.call_date, j.call_num,  
		CASE
		WHEN j.type = 1 THEN concat("Schedule Visit"      , " ( ", j.call_num, " )")
		WHEN j.type = 2 THEN concat("Service Call"        , " ( ", j.call_num, " )")
		WHEN j.type = 3 THEN concat("Urgent Call"          , " ( ", j.call_num, " )")
		WHEN j.type = 4 THEN concat("Remote Service Call" , " ( ", j.call_num, " )")
		ELSE ""
		END as serviceType,   
		j.date, j.actual_start_time, j.actual_end_time,d.remarks, 
		CASE
		WHEN j.status = 1 THEN "Open"
		WHEN j.status = 2 THEN "Completed"
		WHEN j.status = 3 THEN "Follow up"
		WHEN j.status = 4 THEN "Cancel"
		ELSE ""
		END as taskStatus, j.status, j.type, u.userName as user_name, 
		SUBTIME(actual_end_time, actual_start_time) as manTime
from sup_job j,  v_cm_customer_support c, sup_job_detail d, sys_login u
 where j.customer_id = c.cust_id
  and j.id = d.job_id
  and j.deleted = 0 and d.deleted = 0 
  and j.staff_id = u.id
  and (COALESCE(LENGTH(?), 0) = 0 OR j.staff_id = ?)
	AND (COALESCE(LENGTH(?), 0) = 0 OR `date` >= ?)
	AND (COALESCE(LENGTH(?), 0) = 0 OR `date` <= ?)
	order by userName, j.id, date
';
	$parameters = array(
		$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");
	}

	$result = $sth->fetchAll();



$sql = "select sum(call_num) as callnum FROM sup_job
  where type = ?
    and (COALESCE(LENGTH(?), 0) = 0 OR staff_id = ?)
	AND (COALESCE(LENGTH(?), 0) = 0 OR `date` >= ?)
	AND (COALESCE(LENGTH(?), 0) = 0 OR `date` <= ?)
";
	$parameters = array(
		1,
		$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");
	}

	$type1 = $sth->fetchAll();

	$parameters[0] = 2;

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

	$type2 = $sth->fetchAll();


	$sql = "select SEC_TO_TIME(sum(d)) as manHour from (
		SELECT 	TIME_TO_SEC(actual_end_time) - TIME_TO_SEC(actual_start_time) as d
		FROM sup_job 
	  where type = ?
		and (COALESCE(LENGTH(?), 0) = 0 OR staff_id = ?)
		AND (COALESCE(LENGTH(?), 0) = 0 OR `date` >= ?)
		AND (COALESCE(LENGTH(?), 0) = 0 OR `date` <= ?)
		) q  	
	";

	$parameters[0] = 1;
	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");
	}
	$manHour1 = $sth->fetchAll();

	$parameters[0] = 2;
	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");
	}
	$manHour2 = $sth->fetchAll();
	
	
	$parameters[0] = 3;
	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");
	}
	$manHour3 = $sth->fetchAll();

	$parameters[0] = 4;
	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");
	}
	$manhour4 = $sth->fetchAll();

	//var_dump($abc[0]["manhour"]);
	//exit;



	$staffName = "All";
	
	if ( !empty($staff_id) ) {
		$staffName = $result[0]["user_name"];
	} 


	$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/JobListByStaff.xlsx');
	$objWorksheet = $objPHPexcel->getSheetByName('JobListByStaff');

	$lastColumn = "";
	if ( empty($staff_id) ) {
		$objWorksheet->removeRow(8, 1);
		$lastColumn = "I";
	} else {
		$objWorksheet->removeRow(9, 1);
		$lastColumn = "H";
	}
	

	$rownum = 3;

	$objWorksheet->getCell('B' . $rownum)->setValue( $start_date  . " To " . $end_date);
	
	$rownum += 2;
	$objWorksheet->getCell('B' . $rownum)->setValue($staffName);
	
	$rownum += 4;
	$objWorksheet->getCell('A' . $rownum)->setValue("Start");


	$count_all_job = 0;
	$selected_job_ids = array();
	
	//$scheduleCall = 0;
	//$serviceCall = 0;
	//$urgentCall = 0;
	//$remoteCall = 0;	
	//$projectCall = 0;	
	
	
	$serviceType = "";	
	$taskStatus = "";	
	
	$count = 0;	
	$saveId = 0;

//$objWorksheet->getCell("E3")->setValue("manhour");
//$objWorksheet->getCell("F3")->setValue($abc[0]["manhour"]);
	
	$startRow = $firstRow = $rownum;
	foreach ($result as $row) {
		$count = (int)$row["call_num"];
		$curId = (int)$row["id"];
		switch ($row["type"]) {
		  case 1:
			$serviceType = "Schedule Call" . " ( " . $row["call_num"] . " )";
			//$scheduleCall += $id <> $curId ? $count : 0;
			break;
		  case 2:
			$serviceType = "Service Call" . " ( " . $row["call_num"] . " )";
			//$serviceCall += $id <> $curId ? $count : 0;
			break;
		  case 3:
			$serviceType = "Urgent Call" . " ( " . $row["call_num"] . " )";
			//$urgentCall += $id <> $curId ? $count : 0;
			break;
		  case 4:
			$serviceType = "Remote Call" . " ( " . $row["call_num"] . " )";
			//$remoteCall += $id <> $curId ? $count : 0;
			break;
		  default:
			//nothing in this case
		};		
		switch ($row["type"]) {
		  case 1:
			$taskStatus = "Open";
			break;
		  case 2:
			$taskStatus = "Completed";
			break;
		  case 3:
			$taskStatus = "Follow up";
			break;
		  case 4:
			$taskStatus = "Cancel";
			break;
		  default:
			//nothing in this case
		};				
		
		if ( !empty($staff_id) ) { 
			$objWorksheet->getCell("A" . $rownum)->setValue($row["company_name"]);
			$objWorksheet->getCell("B" . $rownum)->setValue($row["call_date"]);
			$objWorksheet->getCell("C" . $rownum)->setValue($serviceType);
			$objWorksheet->getCell("D" . $rownum)->setValue($row["date"]);
			$objWorksheet->getCell("E" . $rownum)->setValue($row["actual_start_time"]);
			$objWorksheet->getCell("F" . $rownum)->setValue($row["actual_end_time"]);
			$objWorksheet->getCell("G" . $rownum)->setValue($row["remarks"]);
			$objWorksheet->getCell("H" . $rownum)->setValue($taskStatus);
		} else {
			$objWorksheet->getCell("A" . $rownum)->setValue($row["user_name"]);
			$objWorksheet->getCell("B" . $rownum)->setValue($row["company_name"]);
			$objWorksheet->getCell("C" . $rownum)->setValue($row["call_date"]);
			$objWorksheet->getCell("D" . $rownum)->setValue($serviceType);
			$objWorksheet->getCell("E" . $rownum)->setValue($row["date"]);
			$objWorksheet->getCell("F" . $rownum)->setValue($row["actual_start_time"]);
			$objWorksheet->getCell("G" . $rownum)->setValue($row["actual_end_time"]);
			$objWorksheet->getCell("H" . $rownum)->setValue($row["remarks"]);
			$objWorksheet->getCell("I" . $rownum)->setValue($taskStatus);
		}
		
		$rownum++;
		$id = $curId;
	}
	$endRow = $rownum - 1;
	
	$objWorksheet->getStyle('A' . $startRow . ':'. $lastColumn . $endRow)->applyFromArray(
		array(
			'borders' => array(
				'allborders' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN,
					'color' => array('argb' => '766f6e')
					)
				)
			)
		);	
	// $objWorksheet->getCell("j" . 1)->setValue('A' . $startRow . ':'. $lastColumn . $endRow);		
	// $font = new PHPExcel_Style_Font();		
	// $font->setSize(12);	
	// $font->setName('Times New Roman');
	// $objWorksheet->getStyle('A' . $startRow . ':'. $lastColumn . $endRow)->getFont()->applyFromArray(
	// 	array(
	// 		'font' => $font
	// 		)
	// 	);		
	
//$objWorksheet->getStyle('A' . $startRow . ':'. $lastColumn . $endRow)->getFont()->setName('Times New Roman');	
	
	$rownum += 2;
	$startRow = $rownum;
	$objWorksheet->mergeCells('A' . $rownum . ':B' . $rownum);
	$objWorksheet->getStyle('A' . $rownum . ':B' . $rownum)->getFont()->setBold( true );
	
	$objWorksheet->getCell('A' . $rownum)->setValue("Service Call Count :");
	$rownum++;
	$objWorksheet->getCell('A' . $rownum)->setValue("Schedule Call :");
	$objWorksheet->getCell('B' . $rownum)->setValue($type1[0]["callnum"]);
	$rownum++;
	$objWorksheet->getCell('A' . $rownum)->setValue("Service Call :");
	$objWorksheet->getCell('B' . $rownum)->setValue($type2[0]["callnum"]);
	$rownum++;
	$objWorksheet->mergeCells('A' . $rownum . ':B' . $rownum);
	
	$objWorksheet->getCell('A' . $rownum)->setValue("Man Hours :");
	$rownum++;
	$objWorksheet->getCell('A' . $rownum)->setValue("Schedule Call :");
	$objWorksheet->getCell('B' . $rownum)->setValue(empty($manHour1[0]["manhour"]) ? "00:00:00" : $manHour1[0]["manhour"]);
	$rownum++;
	$objWorksheet->getCell('A' . $rownum)->setValue("Service Call :");
	$objWorksheet->getCell('B' . $rownum)->setValue(empty($manHour2[0]["manhour"]) ? "00:00:00" : $manHour2[0]["manhour"]);
	$rownum++;
	$objWorksheet->getCell('A' . $rownum)->setValue("Remote Call :");
	$objWorksheet->getCell('B' . $rownum)->setValue(empty($manHour3[0]["manhour"]) ? "00:00:00" : $manHour3[0]["manhour"]);
	$rownum++;
	$objWorksheet->getCell('A' . $rownum)->setValue("Urgent Call :");
	$objWorksheet->getCell('B' . $rownum)->setValue(empty($manHour4[0]["manhour"]) ? "00:00:00" : $manHour4[0]["manhour"]);
	$rownum++;
	$objWorksheet->getCell('A' . $rownum)->setValue("Project Call :");
	$objWorksheet->getCell('B' . $rownum)->setValue("00:00:00");
	$endRow = $rownum;

	$objWorksheet->getStyle('B' . ( $rownum - 5 ) . ':B' . $rownum)->applyFromArray(
		array(
			'alignment' => array(
			'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT )
		)
	);

	$startRow ++;
	
	
	$objWorksheet->getStyle('A' . $startRow . ':B' . $endRow)->applyFromArray(
		array(
			'borders' => array(
				'allborders' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN,
					'color' => array('argb' => '766f6e')
					)
				)
			)
		);
		
			
		
	foreach (range('A', $lastColumn ) as $letra) {            
		$objWorksheet->getColumnDimension($letra)->setAutoSize(true);
	}

	foreach (range(1, $endRow ) as $letra) {            
		$objWorksheet->getRowDimension($letra)->setRowHeight(22);
	}
	$objWorksheet->getRowDimension(1)->setRowHeight(35);
	$objWorksheet->getRowDimension(8)->setRowHeight(32);
	
	header('Content-Type: application/vnd.ms-excel');

	if (date("m", strtotime($start_date)) == date("m", strtotime($end_date)))
		header('Content-Disposition: attachment;filename="JobListByStaff_' . date("M", strtotime($start_date)) . '.xlsx"');
	else
		header('Content-Disposition: attachment;filename="JobListByStaff_' . 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();

?>