<?php error_reporting(0);
set_time_limit(0);
if (substr_count($_SERVER['HTTP_ACCEPT_ENCODING'], 'gzip')) ob_start("ob_gzhandler"); else ob_start();
session_start();
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Asia/Manila');
if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser'); 
require_once dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php'; 
$objReader = PHPExcel_IOFactory::createReader('Excel5');
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );
include_once('../config/minho.php');
include_once('../config/autoloadClass.php');

$autoload	=	new autoloadClass();
$mecon		=	new condbClass();
$mecon2		=	new condbClass();
$mecon3		=	new condbClass();
$mecon4		=	new condbClass();
$mecon5		=	new condbClass();
$mecon6		=	new condbClass();
$mecon7		=	new condbClass();
$mecon8		=	new condbClass();
$main		=	array();
$main2		=	array();
$main3		=	array();
$main4		=	array(); 
$main5		=	array(); 
$main6		=	array(); 
$main7		=	array(); 
$main8		=	array(); 

$monthFrom		= isset($_GET['month_from']) ? strip_tags($_GET['month_from']) : "";
$monthTo		= isset($_GET['month_to']) ? strip_tags($_GET['month_to']) : "";
$isfilterdate   = isset($_GET['isfilterdate']) ? strip_tags($_GET['isfilterdate']) : "yes";
 
$cond3 = "";
$cond2 = "";
if($isfilterdate=="yes"){
	if( !empty($monthFrom) && !empty($monthTo) ){ 
		$cond2 .= " AND r.ReservationDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
		$cond3 .= " AND rr.ReservationDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
		 
	}else if( !empty($monthFrom)){ 
		$cond2 .= " AND r.ReservationDate >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
		$cond3 .= " AND rr.ReservationDate >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
		 
	}else if( !empty($monthTo)){ 
		$cond2 .= " AND r.ReservationDate <= ('".date('Y-m-d', strtotime($monthTo))."') ";
		$cond3 .= " AND rr.ReservationDate <= ('".date('Y-m-d', strtotime($monthTo))."') ";
		 
	}
}	 
//OVERSEAS / OFW
$resultQuery = "SELECT r.ReservationDate as ReservationDate, r.EmployeeType as EmployeeType, COUNT(r.EmployeeType) as EmployeeTypeCount, 
ROUND(COUNT(r.EmployeeType) * 100 / t.s) AS total 
FROM reservation r 
CROSS JOIN (SELECT COUNT(rr.EmployeeType) AS s FROM reservation rr WHERE rr.EmployeeType!='-' AND rr.Status='active' $cond3 ) t  
WHERE r.EmployeeType!='-' AND r.Status='active' AND r.EmployeeType='OFW'";

$sql = $resultQuery;
$sql .= $cond2;   
$sql .= " GROUP BY r.EmployeeType";
 
$mecon->query($sql); 
$main			= $mecon->resultset();  
$counterOverseas = count($main); 
$arr3 = array();
if($counterOverseas!=0){
$arr1['overseas_employeetype']	= $main[0]['EmployeeType'];
$arr1['overseas_employeecount']	= $main[0]['EmployeeTypeCount'];
$arr1['overseas_employeetotal']	= $main[0]['total']; 
}  

//PRIVATE / LOCALLY EMPLOYED
$resultQuery2 = "SELECT r.ReservationDate as ReservationDate, r.EmployeeType as EmployeeType, COUNT(r.EmployeeType) as EmployeeTypeCount, 
ROUND(COUNT(r.EmployeeType) * 100 / t.s) AS total 
FROM reservation r 
CROSS JOIN (SELECT COUNT(rr.EmployeeType) AS s FROM reservation rr WHERE rr.EmployeeType!='-' AND rr.Status='active' $cond3 ) t  
WHERE r.EmployeeType!='-' AND r.Status='active' AND r.EmployeeType='LOCALLY EMPLOYED' ";
$sql2 = $resultQuery2;
$sql2 .= $cond2;   
$sql2 .= " GROUP BY r.EmployeeType";
$mecon2->query($sql2); 
$main2			= $mecon2->resultset();  
$counterPrivate = count($main2); 
if($counterPrivate!=0){
$arr1['private_employeetype']	= $main2[0]['EmployeeType'];
$arr1['private_employeecount']	= $main2[0]['EmployeeTypeCount'];
$arr1['private_employeetotal']	= $main2[0]['total']; 
}  

//SELF-EMPLOYED / OWNED BUSINESS
$resultQuery3 = "SELECT r.ReservationDate as ReservationDate, r.EmployeeType as EmployeeType, COUNT(r.EmployeeType) as EmployeeTypeCount, 
ROUND(COUNT(r.EmployeeType) * 100 / t.s) AS total 
FROM reservation r 
CROSS JOIN (SELECT COUNT(rr.EmployeeType) AS s FROM reservation rr WHERE rr.EmployeeType!='-' AND rr.Status='active' $cond3 ) t  
WHERE r.EmployeeType!='-' AND r.Status='active' AND r.EmployeeType='SELF-EMPLOYED / OWNED BUSINESS' ";
$sql3 = $resultQuery3;
$sql3 .= $cond2;   
$sql3 .= " GROUP BY r.EmployeeType";
$mecon3->query($sql3); 
$main3			= $mecon3->resultset();  
$counterSelf = count($main3); 
if($counterSelf!=0){
$arr1['self_employeetype']	= $main3[0]['EmployeeType'];
$arr1['self_employeecount']	= $main3[0]['EmployeeTypeCount'];
$arr1['self_employeetotal']	= $main3[0]['total']; 
}



//ONLINE EMPLOYMENT
$resultQuery7 = "SELECT r.ReservationDate as ReservationDate, r.EmployeeType as EmployeeType, COUNT(r.EmployeeType) as EmployeeTypeCount, 
ROUND(COUNT(r.EmployeeType) * 100 / t.s) AS total 
FROM reservation r 
CROSS JOIN (SELECT COUNT(rr.EmployeeType) AS s FROM reservation rr WHERE rr.EmployeeType!='-' AND rr.Status='active' $cond3 ) t  
WHERE r.EmployeeType!='-' AND r.Status='active' AND r.EmployeeType='ONLINE EMPLOYMENT' ";
$sql7 = $resultQuery7;
$sql7 .= $cond2;   
$sql7 .= " GROUP BY r.EmployeeType";
$mecon7->query($sql7); 
$main7			= $mecon7->resultset();  
$counterOnline = count($main7); 
if($counterOnline!=0){
$arr1['online_employeetype']	= $main7[0]['EmployeeType'];
$arr1['online_employeecount']	= $main7[0]['EmployeeTypeCount'];
$arr1['online_employeetotal']	= $main7[0]['total']; 
}


//IMMIGRANT
$resultQuery8 = "SELECT r.ReservationDate as ReservationDate, r.EmployeeType as EmployeeType, COUNT(r.EmployeeType) as EmployeeTypeCount, 
ROUND(COUNT(r.EmployeeType) * 100 / t.s) AS total 
FROM reservation r 
CROSS JOIN (SELECT COUNT(rr.EmployeeType) AS s FROM reservation rr WHERE rr.EmployeeType!='-' AND rr.Status='active' $cond3 ) t  
WHERE r.EmployeeType!='-' AND r.Status='active' AND (r.EmployeeType='IMMIGRANT' OR r.EmployeeType='IMMIGRANTS') ";
$sql8 = $resultQuery8;
$sql8 .= $cond2;   
$sql8 .= " GROUP BY r.EmployeeType";
$mecon8->query($sql8); 
$main8			= $mecon8->resultset();  
$counterImmigrant = count($main8); 
if($counterImmigrant!=0){
$arr1['immigrant_employeetype']		= $main8[0]['EmployeeType'];
$arr1['immigrant_employeecount']	= $main8[0]['EmployeeTypeCount'];
$arr1['immigrant_employeetotal']	= $main8[0]['total']; 
}



//SPOT CASH
$resultQuery4 = "SELECT r.ReservationDate as ReservationDate, r.EmployeeType as EmployeeType, COUNT(bpm.Financing) as EmployeeTypeCount, 
ROUND(COUNT(r.EmployeeType) * 100 / t.s) AS total 
FROM reservation r LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id 
CROSS JOIN (SELECT COUNT(rr.Id) AS s FROM reservation rr WHERE rr.EmployeeType!='-' AND rr.Status='active' $cond3 ) t  
WHERE r.EmployeeType!='-' AND r.Status='active' AND bpm.Financing='cash' ";
$sql4 = $resultQuery4;
$sql4 .= $cond2;    
$mecon4->query($sql4); 
$main4			= $mecon4->resultset();  
$counterCash = count($main4); 
if($counterCash!=0){
$arr1['cash_employeetype']	= $main4[0]['EmployeeType'];
$arr1['cash_employeecount']	= $main4[0]['EmployeeTypeCount'];
$arr1['cash_employeetotal']	= $main4[0]['total']; 
}


//BANK
$resultQuery5 = "SELECT r.ReservationDate as ReservationDate, r.EmployeeType as EmployeeType, COUNT(bpm.Financing) as EmployeeTypeCount, 
ROUND(COUNT(r.EmployeeType) * 100 / t.s) AS total 
FROM reservation r LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id 
CROSS JOIN (SELECT COUNT(rr.Id) AS s FROM reservation rr WHERE rr.EmployeeType!='-' AND rr.Status='active' $cond3 ) t  
WHERE r.EmployeeType!='-' AND r.Status='active' AND bpm.Financing='bank' ";
$sql5 = $resultQuery5;
$sql5 .= $cond2;    
$mecon5->query($sql5); 
$main5			= $mecon5->resultset();  
$counterBank = count($main5); 
if($counterBank!=0){
$arr1['bank_employeetype']	= $main5[0]['EmployeeType'];
$arr1['bank_employeecount']	= $main5[0]['EmployeeTypeCount'];
$arr1['bank_employeetotal']	= $main5[0]['total']; 
}


//PAGIBIG
$resultQuery6 = "SELECT r.ReservationDate as ReservationDate, r.EmployeeType as EmployeeType, COUNT(bpm.Financing) as EmployeeTypeCount, 
ROUND(COUNT(r.EmployeeType) * 100 / t.s) AS total 
FROM reservation r LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id 
CROSS JOIN (SELECT COUNT(rr.Id) AS s FROM reservation rr WHERE rr.EmployeeType!='-' AND rr.Status='active' $cond3 ) t  
WHERE r.EmployeeType!='-' AND r.Status='active' AND bpm.Financing='pagibig' ";
$sql6 = $resultQuery6;
$sql6 .= $cond2;    
$mecon6->query($sql6); 
$main6			= $mecon6->resultset();  
$counterPagibig = count($main6); 
if($counterPagibig!=0){
$arr1['pagibig_employeetype']	= $main6[0]['EmployeeType'];
$arr1['pagibig_employeecount']	= $main6[0]['EmployeeTypeCount'];
$arr1['pagibig_employeetotal']	= $main6[0]['total']; 
}


if($counterOverseas!=0 || $counterPrivate!=0 || $counterSelf!=0 || $counterOnline!=0 || $counterImmigrant!=0){     
$arr3['data'] = $arr1;
}


$fileName    = "report_analysis_report.xls";
$objPHPExcel = $objReader->load("templates/report_analysis_report.xls"); 

$baseRow = 3;

if($counterOverseas!=0 || $counterPrivate!=0 || $counterSelf!=0 || $counterOnline!=0 || $counterImmigrant!=0){   
 			$row = $baseRow + count($arr3['data']) + 1;
				$objPHPExcel->getActiveSheet()->insertNewRowBefore(5,1); 


				$overseasEmployeeType 	= (isset($arr1['overseas_employeetype'])) ? $arr1['overseas_employeetype'] : "OFW";
				$overseasEmployeeCount	= (isset($arr1['overseas_employeecount'])) ? $arr1['overseas_employeecount'] : "";
				$overseasEmployeeTotal	= (isset($arr1['overseas_employeetotal'])) ? $arr1['overseas_employeetotal'] : "";

				$privateEmployeeType 	= (isset($arr1['private_employeetype'])) ? $arr1['private_employeetype'] : "Locally Employed";
				$privateEmployeeCount	= (isset($arr1['private_employeecount'])) ? $arr1['private_employeecount'] : "";
				$privateEmployeeTotal	= (isset($arr1['private_employeetotal'])) ? $arr1['private_employeetotal'] : "";

				$selfEmployeeType 		= (isset($arr1['self_employeetype'])) ? $arr1['self_employeetype'] : "Self Employed / Owned Business";
				$selfEmployeeCount		= (isset($arr1['self_employeecount'])) ? $arr1['self_employeecount'] : "";
				$selfEmployeeTotal		= (isset($arr1['self_employeetotal'])) ? $arr1['self_employeetotal'] : "";


				$onlineEmployeeType 	= (isset($arr1['online_employeetype'])) ? $arr1['online_employeetype'] : "Online Employment";
				$onlineEmployeeCount	= (isset($arr1['online_employeecount'])) ? $arr1['online_employeecount'] : "";
				$onlineEmployeeTotal	= (isset($arr1['online_employeetotal'])) ? $arr1['online_employeetotal'] : "";

				$immigrantEmployeeType 	= (isset($arr1['immigrant_employeetype'])) ? $arr1['immigrant_employeetype'] : "Immigrant";
				$immigrantEmployeeCount	= (isset($arr1['immigrant_employeecount'])) ? $arr1['immigrant_employeecount'] : "";
				$immigrantEmployeeTotal	= (isset($arr1['immigrant_employeetotal'])) ? $arr1['immigrant_employeetotal'] : "";

				$cashCount				= (isset($arr1['cash_employeecount'])) ? $arr1['cash_employeecount'] : "";
				$cashTotal				= (isset($arr1['cash_employeetotal'])) ? $arr1['cash_employeetotal'] : "";

				$bankCount				= (isset($arr1['bank_employeecount'])) ? $arr1['bank_employeecount'] : "";
				$bankTotal				= (isset($arr1['bank_employeetotal'])) ? $arr1['bank_employeetotal'] : "";

				$pagibigCount			= (isset($arr1['pagibig_employeecount'])) ? $arr1['pagibig_employeecount'] : "";
				$pagibigTotal			= (isset($arr1['pagibig_employeetotal'])) ? $arr1['pagibig_employeetotal'] : "";


				$objPHPExcel->getActiveSheet()->setCellValue('A4', $overseasEmployeeType)
											  ->setCellValue('B4', $overseasEmployeeCount) 
											  ->setCellValue('C4', $overseasEmployeeTotal)
											  ->setCellValue('A5', $privateEmployeeType)
											  ->setCellValue('B5', $privateEmployeeCount) 
											  ->setCellValue('C5', $privateEmployeeTotal)
											  ->setCellValue('A6', $selfEmployeeType)
											  ->setCellValue('B6', $selfEmployeeCount) 
											  ->setCellValue('C6', $selfEmployeeTotal)
											  ->setCellValue('A7', $onlineEmployeeType)
											  ->setCellValue('B7', $onlineEmployeeCount) 
											  ->setCellValue('C7', $onlineEmployeeTotal) 
											  ->setCellValue('A8', $immigrantEmployeeType)
											  ->setCellValue('B8', $immigrantEmployeeCount) 
											  ->setCellValue('C8', $immigrantEmployeeTotal) 
											  ->setCellValue('E4', 'SPOT CASH')
											  ->setCellValue('F4', $cashCount) 
											  ->setCellValue('G4', $cashTotal)
											  ->setCellValue('E5', 'BANK')
											  ->setCellValue('F5', $bankCount) 
											  ->setCellValue('G5', $bankTotal)
											  ->setCellValue('E6', 'PAG-IBIG')
											  ->setCellValue('F6', $pagibigCount) 
											  ->setCellValue('G6', $pagibigTotal); 
} 


$from = "A1"; // or any value
$to = "G10"; // or any value
$objPHPExcel->getActiveSheet()->getStyle("$from:$to")->getFont()->setBold(true);

$objPHPExcel->getActiveSheet()->getStyle("B1:C10")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle("F1:G10")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
 
$objPHPExcel->getActiveSheet()->getStyle("C3:C10")
    ->getNumberFormat()->applyFromArray( 
        array( 
            'code' => PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00
        )
    );

$objPHPExcel->getActiveSheet()->getStyle("G3:G10")
    ->getNumberFormat()->applyFromArray( 
        array( 
            'code' => PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00
        )
    );  

$objPHPExcel->getActiveSheet()->getStyle("C3:C10")->getNumberFormat()->setFormatCode('0'); 
$objPHPExcel->getActiveSheet()->getStyle("G3:G10")->getNumberFormat()->setFormatCode('0'); 


$objPHPExcel->getActiveSheet()->removeRow($baseRow,1);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$fileName.'"');
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
//header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
//header ('Pragma: public'); // HTTP/1.0

header("Cache-Control: no-cache, no-store, must-revalidate"); // HTTP 1.1.
header("Pragma: no-cache"); // HTTP 1.0.
header("Expires: 0"); // Proxies.


$objWriter->save('php://output'); 