<?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, r.Block, r.Lot, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname, r.ReservationDate, r.ReservationStatus, r.Project, r.Model, bm.PermitToConstructNo as PermitToConstructNo, bm.ConstructionBondPaid as ConstructionBondPaid, bm.RefundDate as RefundDate, bm.FullRefundOrWithViolation as FullRefundOrWithViolation, bm.Penalty as Penalty, bm.Remarks as Remarks, bm.Amount as Amount FROM reservation r LEFT JOIN buyer_house_renovation_monitoring bm ON bm.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 bm.PermitToConstructNo LIKE '%".$ind."%' ";  
		$sql.=" OR bm.FullRefundOrWithViolation LIKE '%".$ind."%' "; 
		$sql.=" OR bm.Penalty LIKE '%".$ind."%' "; 
		$sql.=" OR bm.Remarks LIKE '%".$ind."%' "; 
		$sql.=" OR bm.Amount LIKE '%".$ind."%' "; 
		$sql.=" OR UPPER(DATE_FORMAT(bm.ConstructionBondPaid,'%m/%d/%Y')) LIKE '%".$ind."%' "; 
		$sql.=" OR UPPER(DATE_FORMAT(bm.RefundDate,'%m/%d/%Y')) LIKE '%".$ind."%' "; 
		$sql.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($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 cast(r.Block as unsigned) asc, cast(r.Lot as unsigned) asc";

$mecon->query($sql);  
$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]	= $main[$a]['PermitToConstructNo']; 
	$arr1[6]	= (!empty($main[$a]['ConstructionBondPaid']) && ($main[$a]['ConstructionBondPaid']!='1970-01-01')) ? $main[$a]['ConstructionBondPaid'] : "";
	$arr1[7]	= (!empty($main[$a]['RefundDate']) && ($main[$a]['RefundDate']!='1970-01-01')) ? $main[$a]['RefundDate'] : "";
	$arr1[8]	= $main[$a]['FullRefundOrWithViolation']; 
	$arr1[9]	= !empty($main[$a]['Penalty']) ? number_format($main[$a]['Penalty'],2) : "";
	$arr1[10]	= $main[$a]['Amount']; 
	$arr1[11]	= $main[$a]['Remarks'];  
	$arr2[] 	= $arr1;
}

$arr3['data'] = $arr2;
$fileName    = "buyer_house_renovation_monitoring.xls";
$objPHPExcel = $objReader->load("templates/report_buyer_house_renovation_monitoring.xls"); 
$baseRow = 3; 
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][5])
											  ->setCellValue('D'.$row, $arr3['data'][$a][6])
											  ->setCellValue('E'.$row, $arr3['data'][$a][7])
											  ->setCellValue('F'.$row, $arr3['data'][$a][8])
											  ->setCellValue('G'.$row, $arr3['data'][$a][9])
											  ->setCellValue('H'.$row, $arr3['data'][$a][11]);
											  
			$objPHPExcel->getActiveSheet()->getStyle('D'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');											  
			$objPHPExcel->getActiveSheet()->getStyle('E'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');											     
 }  
$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'); 