<?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();
$main		=	array();
$main2		=	array();
$main3		=	array();
$main4		=	array();

$monthFrom		= isset($_GET['month_from']) ? strip_tags($_GET['month_from']) : "";
$monthTo		= isset($_GET['month_to']) ? strip_tags($_GET['month_to']) : "";
$filterSearch = isset($_GET['filter']) ? str_replace("'"," ",$_GET['filter']) : "";
//$filterSearch	= isset($_GET['filter']) ? strip_tags($_GET['filter']) : "";
$isfilterdate = isset($_GET['isfilterdate']) ? strip_tags($_GET['isfilterdate']) : "yes";
$sql = "SELECT r.Id as Id, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as Fullname, r.ReservationDate as ReservationDate, bsm.BuyerOrientationDate as BuyerOrientationDate,bsm.Conducted as Conducted,bsm.PagibigTakeOutDate as PagibigTakeOutDate, bsm.HomeownerOrientation as HomeownerOrientation, bsm.Inspection as Inspection, bsm.ReInspection as ReInspection, bsm.LetterSent as LetterSent, bsm.HouseAcceptance as HouseAcceptance, bsm.PermitToOccupy as PermitToOccupy, bsm.PermitToOccupyNumber as PermitToOccupyNumber, bsm.PTOStatus as PTOStatus, r.Model, r.Project, bpm.Financing as Financing FROM reservation r LEFT JOIN buyer_status_monitoring bsm ON bsm.ReservationId=r.Id LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id WHERE r.Status = 'active' "; 

if(!empty($filterSearch)) { 
	$arr = explode(" ",$filterSearch);  
	foreach($arr as $ind){
		$ind = str_replace(',','',$ind);
		$sql.=" AND ( r.Block LIKE '%".$ind."%' ";
		$sql.=" OR r.Lot LIKE '%".$ind."%' ";
		$sql.=" OR r.Project LIKE '%".$ind."%' ";
		$sql.=" OR r.Model LIKE '%".$ind."%' ";
		$sql.=" OR bsm.Conducted LIKE '%".$ind."%' ";
		$sql.=" OR bpm.Financing LIKE '%".$ind."%' ";
		$sql.=" OR bsm.PTOStatus LIKE '%".$ind."%' ";
		$sql.=" OR bsm.PermitToOccupyNumber LIKE '%".$ind."%' ";
		$sql.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(bsm.PermitToOccupy,'%m/%d/%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsm.HouseAcceptance,'%m/%d/%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsm.LetterSent,'%m/%d/%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsm.HomeownerOrientation,'%m/%d/%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsm.ReInspection,'%m/%d/%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsm.Inspection,'%m/%d/%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsm.PagibigTakeOutDate,'%m/%d/%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsm.BuyerOrientationDate,'%m/%d/%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%m/%d/%Y')) LIKE '%".$ind."%' ) "; 
	}
}

if($isfilterdate=="yes"){
		if( !empty($monthFrom) && !empty($monthTo) ){
			$sql .= " AND r.ReservationDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
		}else if( !empty($monthFrom)){
			$sql .= " AND r.ReservationDate > ('".date('Y-m-d', strtotime($monthFrom))."') ";
		}else if( !empty($monthTo)){
			$sql .= " AND r.ReservationDate > ('".date('Y-m-d', strtotime($monthTo))."') ";
		} 
} 


$sql .= " ORDER BY r.ReservationDate desc , cast(r.Block as unsigned) asc, cast(r.Lot as unsigned) asc ";

$mecon->query($sql); 
//$mecon->query("SELECT r.Id, r.Block, r.Lot, CONCAT(r.LastName,', ',r.FirstName, r.MiddleName) as Fullname, r.ReservationDate, bsm.BuyerOrientationDate,bsm.Conducted,bsm.PagibigTakeOutDate, bsm.HomeownerOrientation, bsm.Inspection, bsm.ReInspection, bsm.LetterSent, bsm.HouseAcceptance, bsm.PermitToOccupy FROM reservation r LEFT JOIN buyer_status_monitoring bsm ON bsm.ReservationId=r.Id WHERE r.Status = :status");
//$mecon->bind(':status', 'active');
$main = $mecon->resultset(); 
$counter = count($main); 
$arr1 = array();
$arr2 = array();  
$arr3 = array();  

for($a=0; $a<$counter; $a++){  
	$arr1[0]	= $a+1;
	$arr1[1]	= $main[$a]['Block'];
	$arr1[2]	= $main[$a]['Lot'];
	$arr1[3]	= trim(html_entity_decode(stripslashes($main[$a]['Fullname']))); 
	$arr1[4]	= (!empty($main[$a]['ReservationDate']) && ($main[$a]['ReservationDate']!='1970-01-01')) ? $main[$a]['ReservationDate'] : "";
	$arr1[5]	= (!empty($main[$a]['BuyerOrientationDate']) && ($main[$a]['BuyerOrientationDate']!='1970-01-01')) ? $main[$a]['BuyerOrientationDate']  : "";
	$arr1[6]	= $main[$a]['Conducted']; 
	$arr1[7]	= $main[$a]['Financing']; 
	$arr1[8]	= (!empty($main[$a]['PagibigTakeOutDate']) && ($main[$a]['PagibigTakeOutDate']!='1970-01-01')) ? $main[$a]['PagibigTakeOutDate'] : "";
	$arr1[9]	= (!empty($main[$a]['HomeownerOrientation']) && ($main[$a]['HomeownerOrientation']!='1970-01-01')) ? $main[$a]['HomeownerOrientation'] : "";
	$arr1[10]	= (!empty($main[$a]['Inspection']) && ($main[$a]['Inspection']!='1970-01-01')) ? $main[$a]['Inspection'] : "";
	//$arr1[11]	= (!empty($main[$a]['ReInspection']) && ($main[$a]['ReInspection']!='1970-01-01')) ? $main[$a]['ReInspection'] : "";
	$arr1[11]	= (!empty($main[$a]['LetterSent']) && ($main[$a]['LetterSent']!='1970-01-01')) ? $main[$a]['LetterSent'] : "";
	$arr1[12]	= (!empty($main[$a]['HouseAcceptance']) && ($main[$a]['HouseAcceptance']!='1970-01-01')) ? $main[$a]['HouseAcceptance'] : "";
	$arr1[13]	= (!empty($main[$a]['PermitToOccupy']) && ($main[$a]['PermitToOccupy']!='1970-01-01')) ? $main[$a]['PermitToOccupy'] : "";
	$arr1[14]	= strtoupper($main[$a]['PermitToOccupyNumber']); 
	$arr1[15]	= strtoupper($main[$a]['PTOStatus']); 
	$arr2[] 	= $arr1;
}

$arr3['data'] = $arr2;
$fileName    = "buyer_status_monitoring.xls";
$objPHPExcel = $objReader->load("templates/report_buyer_status_monitoring.xls"); 
$baseRow = 2; 
for($a=0; $a<count($arr3['data']); $a++){ 
	$row = $baseRow + $a + 1;
				$objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1); 
				$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $arr3['data'][$a][1])
											  ->setCellValue('B'.$row, $arr3['data'][$a][2])
											  ->setCellValue('C'.$row, $arr3['data'][$a][3])
											  ->setCellValue('D'.$row, $arr3['data'][$a][4])
											  ->setCellValue('E'.$row, $arr3['data'][$a][5])
											  ->setCellValue('F'.$row, $arr3['data'][$a][6])
											  ->setCellValue('G'.$row, $arr3['data'][$a][7])
											  ->setCellValue('H'.$row, $arr3['data'][$a][8])
											  ->setCellValue('I'.$row, $arr3['data'][$a][9])
											  ->setCellValue('J'.$row, $arr3['data'][$a][10])
											  ->setCellValue('K'.$row, $arr3['data'][$a][11])
											  ->setCellValue('L'.$row, $arr3['data'][$a][12])
											  ->setCellValue('M'.$row, $arr3['data'][$a][13])
											  ->setCellValue('N'.$row, $arr3['data'][$a][14])
											  ->setCellValue('O'.$row, $arr3['data'][$a][15]);
											  
			$objPHPExcel->getActiveSheet()->getStyle('D'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');											  
			$objPHPExcel->getActiveSheet()->getStyle('E'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');											  
			$objPHPExcel->getActiveSheet()->getStyle('G'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');											  
			$objPHPExcel->getActiveSheet()->getStyle('H'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');											  
			$objPHPExcel->getActiveSheet()->getStyle('I'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');											  
			$objPHPExcel->getActiveSheet()->getStyle('J'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');											  
			$objPHPExcel->getActiveSheet()->getStyle('K'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');											  
			$objPHPExcel->getActiveSheet()->getStyle('L'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');											  
			$objPHPExcel->getActiveSheet()->getStyle('M'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');											  
 }  
$objPHPExcel->getActiveSheet()->removeRow($baseRow-1,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'); 