<?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();
$formClass  = 	new formClass();
$main		=	array();
$main2		=	array();
$main3		=	array();
$main4		=	array();
//$mecon->query("SELECT r.Id as Id, rd.LoanableAmount as LoanableAmount, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname, rd.LotSize as LotSize, UPPER(DATE_FORMAT(r.ReservationDate,'%d-%b-%Y')) as ReservationDate, m.MonitoringFolderDetails as MonitoringFolderDetails FROM reservation r LEFT JOIN monitoring_folders m ON m.ReservationId=r.Id LEFT JOIN reservation_details rd ON rd.Id=m.ReservationId  WHERE r.Status = :status ");

$isfilterdate = isset($_GET['isfilterdate']) ? strip_tags($_GET['isfilterdate']) : "yes";
$monthFrom		= isset($_GET['month_from']) ? strip_tags($_GET['month_from']) : "";
$monthTo		= isset($_GET['month_to']) ? strip_tags($_GET['month_to']) : "";
//$filterSearch	= isset($_GET['filter']) ? strip_tags($_GET['filter']) : "";
$filterSearch = isset($_GET['filter']) ? str_replace("'"," ",$_GET['filter']) : "";
/*$sql = "SELECT r.Block, r.Lot, r.Id as Id, bpm.LoanableAmount as LoanableAmount, r.Phase as Phase, r.PricelistDate as PricelistDate, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname,  r.ReservationDate, m.BuyerStandardReqDetails as BuyerStandardReqDetails, r.Model, r.Project, r.ReservationStatus as ReservationStatus, pal.TaxDecLandProcessed as TaxDecLandProcessed, pal.TaxDecLandReleased as TaxDecLandReleased, pal.TaxDecBuildingProcessed as TaxDecBuildingProcessed, pal.TaxDecBuildingReleased as TaxDecBuildingReleased, pal.TaxReceiptLandProcessed as TaxReceiptLandProcessed, pal.TaxReceiptLandReleased as TaxReceiptLandReleased, pal.TaxReceiptBuildingProcessed as TaxReceiptBuildingProcessed, pal.TaxReceiptBuildingReleased as TaxReceiptBuildingReleased, pal.BuildingPermitProcessed as BuildingPermitProcessed, pal.BuildingPermitReleased as BuildingPermitReleased, pal.ElectricalPermitProcessed as ElectricalPermitProcessed, pal.ElectricalPermitReleased as ElectricalPermitReleased, pal.SanitaryPermitProcessed as SanitaryPermitProcessed, pal.SanitaryPermitReleased as SanitaryPermitReleased, pal.OccupancyPermitProcessed as OccupancyPermitProcessed, pal.OccupancyPermitReleased as OccupancyPermitReleased, cau.Road as Road, cau.SideWalkGutter as SideWalkGutter, cau.Meralco as Meralco, cau.Water as Water, cau.HouseConstruction as HouseConstruction, cau.HouseConstructionPercentage as HouseConstructionPercentage, ubl.TDLandNo, ubl.TDBldgNo FROM reservation r LEFT JOIN checklist m ON m.ReservationId=r.Id LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id
LEFT JOIN units_block_lot ubl ON ubl.Block=r.Block AND ubl.Lot=r.Lot
LEFT JOIN units u ON u.Id=ubl.UnitId
LEFT JOIN construction_and_utilities cau ON cau.UnitId=u.Id
LEFT JOIN permits_and_licenses pal ON pal.UnitId=u.Id WHERE r.Status = 'active' ";
*/

$resultQuery = "SELECT r.Block, r.Lot, r.Id as Id, bpm.LoanableAmount as LoanableAmount, r.Phase as Phase, r.PricelistDate as PricelistDate, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname,  r.ReservationDate, m.BuyerStandardReqDetails as BuyerStandardReqDetails, r.Model, r.Project, r.ReservationStatus as ReservationStatus, pal.TaxDecLandProcessed as TaxDecLandProcessed, pal.TaxDecLandReleased as TaxDecLandReleased, pal.TaxDecBuildingProcessed as TaxDecBuildingProcessed, pal.TaxDecBuildingReleased as TaxDecBuildingReleased, pal.TaxReceiptLandProcessed as TaxReceiptLandProcessed, pal.TaxReceiptLandReleased as TaxReceiptLandReleased, pal.TaxReceiptBuildingProcessed as TaxReceiptBuildingProcessed, pal.TaxReceiptBuildingReleased as TaxReceiptBuildingReleased, pal.BuildingPermitProcessed as BuildingPermitProcessed, pal.BuildingPermitReleased as BuildingPermitReleased, pal.ElectricalPermitProcessed as ElectricalPermitProcessed, pal.ElectricalPermitReleased as ElectricalPermitReleased, pal.SanitaryPermitProcessed as SanitaryPermitProcessed, pal.SanitaryPermitReleased as SanitaryPermitReleased, pal.OccupancyPermitProcessed as OccupancyPermitProcessed, pal.OccupancyPermitReleased as OccupancyPermitReleased, cau.Road as Road, cau.SideWalkGutter as SideWalkGutter, cau.Meralco as Meralco, cau.Water as Water, cau.HouseConstruction as HouseConstruction, cau.HouseConstructionPercentage as HouseConstructionPercentage, ubl.TDLandNo, ubl.TDBldgNo FROM reservation r LEFT JOIN checklist m ON m.ReservationId=r.Id LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id
LEFT JOIN units_block_lot ubl ON ubl.Block=r.Block AND ubl.Lot=r.Lot
LEFT JOIN units u ON u.Id=ubl.UnitId
LEFT JOIN construction_and_utilities cau ON cau.UnitId=u.Id
LEFT JOIN permits_and_licenses pal ON pal.UnitId=u.Id";

$sql			=	$resultQuery."  WHERE r.Status = 'active' ";

if(!empty($filterSearch)) { 
	$arr = explode(" ",$filterSearch);  
	foreach($arr as $ind){
		/*
		$ind = str_replace(',','',$ind);
		$sql.=" AND ( r.Model LIKE '%".$ind."%' ";
		$sql.=" OR r.Project LIKE '%".$ind."%' ";
		$sql.=" OR r.Block LIKE '%".$ind."%' ";
		$sql.=" OR r.Lot LIKE '%".$ind."%' ";
		$sql.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' "; 
		$sql.=" OR bpm.LoanableAmount LIKE '%".str_replace(',','',$ind)."%' ";
		$sql.=" OR bpm.LoanableAmount LIKE '%".$ind."%' "; 
		$sql.=" OR cau.HouseConstruction LIKE '%".$ind."%' ";   
		$sql.=" OR m.BuyerStandardReqDetails LIKE '%".$ind."%' ";   
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%m/%d/%Y')) LIKE '%".$ind."%' ) ";   
		*/

		$ind = str_replace(',','',$ind);		
		$sql.=" AND (r.Block LIKE '%".$ind."%' ";
		$sql.=" OR r.Lot LIKE '%".$ind."%' ";
		$sql.=" OR m.BuyerStandardReqDetails LIKE '%".$ind."%' "; 
		$sql.=" OR cau.HouseConstruction LIKE '%".$ind."%' "; 
		$sql.=" OR r.ReservationStatus LIKE '%".$ind."%' ";
		$sql.=" OR r.Project LIKE '%".$ind."%' ";
		$sql.=" OR r.Model LIKE '%".$ind."%' ";
		$sql.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%d-%b-%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);
$main = $mecon->resultset(); 
$counter = count($main); 
$arr1 = array();
$arr2 = array();  
$arr3 = array();  

for($a=0; $a<$counter; $a++){  
	$arr1[0]	= $a+1;
	$arr1[1]	= html_entity_decode(stripslashes($main[$a]['Fullname']));
	$arr1[23]	= (strtolower($main[$a]['HouseConstruction'])=='yes') ? 'OK' : '';   
	$arr1[2]	= $main[$a]['Block'];
	$arr1[3]	= $main[$a]['Lot'];
	//$arr1[4]	= $main[$a]['LotSize'];
	
	
	$pricedate	= html_entity_decode(stripslashes($main[$a]['PricelistDate'])); 
	$phase		= html_entity_decode(stripslashes($main[$a]['Phase'])); 
	$block		= html_entity_decode(stripslashes($main[$a]['Block'])); 
	$lot		= html_entity_decode(stripslashes($main[$a]['Lot'])); 
	$project	= html_entity_decode(stripslashes($main[$a]['Project'])); 
	$models		= html_entity_decode(stripslashes($main[$a]['Model'])); 
				
	$other = $formClass->getOtherDetails($pricedate,$models,$project,$phase,$block,$lot);		
	$arr1[4] = $other['lot_size'];
	$arr1[22] = $other['floor_area'];
			 
	$arr1[5]	= (($main[$a]['ReservationDate']=='1970-01-01') || ($main[$a]['ReservationDate']=='')) ? "" : date('m/d/Y', strtotime($main[$a]['ReservationDate'])); 
	
	$arr1[6]	= ($main[$a]['LoanableAmount']!="") ? number_format($main[$a]['LoanableAmount'],2) : $main[$a]['LoanableAmount']; 
	//$detArr = json_decode($main[$a]['MonitoringFolderDetails'],true);
	$detArr = json_decode(str_replace('""','',$main[$a]['BuyerStandardReqDetails']),true);
			
			$req_completed 		= "";	
				$signed_loan_docs 	= "";
				$sworn_cert 		= "";
				$tax_dec_land 		= "";
				$tax_dec_building 	= ""; 
				$tax_land_receipt 	= ""; 
				$tax_building_receipt 	= ""; 
				$building_permit 	= ""; 
				$electrical_permit 	= ""; 
				$sanitary_permit 	= ""; 
				$occupancy_permit 	= ""; 
				$road 				= ""; 
				$sidewalk_and_gutter 	= ""; 
				$meralco 				= ""; 
				$water 					= "";  
				 
				

			/*if(count($detArr)!=0){ 				
					foreach($detArr as $detInd){ 
						if($detInd=="completed_documentary_requirement"){	$req_completed = "OK";	} 
						if($detInd=="signed_loan_documents"){	$signed_loan_docs = "OK";	 }  
					}
			}	*/


			if(count($detArr)!=0){ 

				if(in_array("completed_documentary_requirement", $detArr)){
					$req_completed 		= "OK";
				}else{
					$req_completed 		= "";
				}

				if(in_array("signed_loan_documents", $detArr)){
					$signed_loan_docs 		= "OK";
				}else{
					$signed_loan_docs 		= "";
				} 
			}else{
				$req_completed 			= "";
				$signed_loan_docs 		= "";
			}


						$arr1[7] 	= $req_completed; 
						$arr1[8] 	= $signed_loan_docs; 
						//$arr1[9] 	= $sworn_cert; 
						$arr1[10] 	= ($main[$a]['TaxDecLandProcessed']!='1970-01-01' && $main[$a]['TaxDecLandReleased']!='1970-01-01' && $main[$a]['TaxDecLandProcessed']!=NULL && $main[$a]['TaxDecLandReleased']!=NULL) ? 'OK' : '';	
						$arr1[11] 	= ($main[$a]['TaxDecBuildingProcessed']!='1970-01-01' && $main[$a]['TaxDecBuildingReleased']!='1970-01-01' && $main[$a]['TaxDecBuildingProcessed']!=NULL && $main[$a]['TaxDecBuildingReleased']!=NULL) ? 'OK' : ''; 
						$arr1[12] 	= ($main[$a]['TaxReceiptLandProcessed']!='1970-01-01' && $main[$a]['TaxReceiptLandReleased']!='1970-01-01' && $main[$a]['TaxReceiptLandProcessed']!=NULL && $main[$a]['TaxReceiptLandReleased']!=NULL) ? 'OK' : ''; 
						$arr1[13] 	= ($main[$a]['TaxReceiptBuildingProcessed']!='1970-01-01' && $main[$a]['TaxReceiptBuildingReleased']!='1970-01-01' && $main[$a]['TaxReceiptBuildingProcessed']!=NULL  && $main[$a]['TaxReceiptBuildingReleased']!=NULL) ? 'OK' : ''; 
						$arr1[14] 	= ($main[$a]['BuildingPermitProcessed']!='1970-01-01' && $main[$a]['BuildingPermitReleased']!='1970-01-01' && $main[$a]['BuildingPermitProcessed']!=NULL && $main[$a]['BuildingPermitReleased']!=NULL) ? 'OK' : ''; 
						$arr1[15] 	= ($main[$a]['ElectricalPermitProcessed']!='1970-01-01' && $main[$a]['ElectricalPermitReleased']!='1970-01-01' && $main[$a]['ElectricalPermitProcessed']!=NULL && $main[$a]['ElectricalPermitReleased']!=NULL) ? 'OK' : ''; 
						$arr1[16] 	= ($main[$a]['SanitaryPermitProcessed']!='1970-01-01' && $main[$a]['SanitaryPermitReleased']!='1970-01-01' && $main[$a]['SanitaryPermitProcessed']!=NULL && $main[$a]['SanitaryPermitReleased']!=NULL) ? 'OK' : ''; 
						$arr1[17] 	 = ($main[$a]['OccupancyPermitProcessed']!='1970-01-01' && $main[$a]['OccupancyPermitReleased']!='1970-01-01' && $main[$a]['OccupancyPermitProcessed']!=NULL && $main[$a]['OccupancyPermitReleased']!=NULL) ? 'OK' : ''; 


						$arr1[18] 	= (strtolower($main[$a]['Road'])=='yes') ? 'OK' : ''; 
						$arr1[19] 	= (strtolower($main[$a]['SideWalkGutter'])=='yes') ? 'OK' : ''; 
						$arr1[20] 	= (strtolower($main[$a]['Meralco'])=='yes') ? 'OK' : '';  
						$arr1[21] 	= (strtolower($main[$a]['Water'])=='yes') ? 'OK' : '';  

 
	
	$arr2[] 	= $arr1;
}
$arr3['data'] = $arr2;
 
 
$fileName    = "monitoring_folders.xls";
$objPHPExcel = $objReader->load("templates/monitoring_folders.xls"); 



$showMonthFrom = date('F', strtotime($monthFrom));
$showYearFrom = date('Y', strtotime($monthFrom));
$showMonthTo = date('F', strtotime($monthTo));
$showYearTo = date('Y', strtotime($monthTo));
if($isfilterdate=="yes"){
	if($showYearFrom!=$showYearTo){
		$caption = "For the Month of $showMonthFrom $showYearFrom - $showMonthTo $showYearTo";	 
	}else{ 
		$caption = "For the Month of $showMonthFrom - $showMonthTo $showYearTo";
	}
}else{
$caption = "";
}
if($monthFrom!="" && $monthTo!=""){
		$objPHPExcel->getActiveSheet()->setCellValue('A2', $caption);
}		
$baseRow = 6;
  
 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][0])
											  ->setCellValue('B'.$row, $arr3['data'][$a][1])
											  ->setCellValue('C'.$row, $arr3['data'][$a][2])
											  ->setCellValue('D'.$row, $arr3['data'][$a][3])
											  ->setCellValue('E'.$row, $arr3['data'][$a][4])
											  ->setCellValue('F'.$row, $arr3['data'][$a][22])
											  ->setCellValue('G'.$row, $arr3['data'][$a][5])
											  ->setCellValue('H'.$row, $arr3['data'][$a][6])
											  ->setCellValue('I'.$row, $arr3['data'][$a][7])
											  ->setCellValue('J'.$row, $arr3['data'][$a][8])
											  ->setCellValue('K'.$row, $arr3['data'][$a][10])
											  ->setCellValue('L'.$row, $arr3['data'][$a][11])
											  ->setCellValue('M'.$row, $arr3['data'][$a][12])
											  ->setCellValue('N'.$row, $arr3['data'][$a][13])
											  ->setCellValue('O'.$row, $arr3['data'][$a][14])
											  ->setCellValue('P'.$row, $arr3['data'][$a][15])
											  ->setCellValue('Q'.$row, $arr3['data'][$a][16])
											  ->setCellValue('R'.$row, $arr3['data'][$a][17])
											  ->setCellValue('S'.$row, $arr3['data'][$a][18])
											  ->setCellValue('T'.$row, $arr3['data'][$a][19])
											  ->setCellValue('U'.$row, $arr3['data'][$a][20])
											  ->setCellValue('V'.$row, $arr3['data'][$a][21])
											  ->setCellValue('W'.$row, $arr3['data'][$a][23]); 
											  
			$objPHPExcel->getActiveSheet()->getStyle('G'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');

			$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
			$objPHPExcel->getActiveSheet()->getStyle('C'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
			$objPHPExcel->getActiveSheet()->getStyle('D'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
			$objPHPExcel->getActiveSheet()->getStyle('E'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
			$objPHPExcel->getActiveSheet()->getStyle('F'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
			$objPHPExcel->getActiveSheet()->getStyle('G'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
			$objPHPExcel->getActiveSheet()->getStyle('H'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
 }



 //labels
 	$objPHPExcel->getActiveSheet()->setCellValue('A4', '')
											  ->setCellValue('B4', 'NAME') 
											  ->setCellValue('C4', 'BLOCK')
											  ->setCellValue('D4', 'LOT')
											  ->setCellValue('E4', 'LOT SIZE')
											  ->setCellValue('F4', 'FLOOR AREA')
											  ->setCellValue('G4', 'RESERVED DATE')
											  ->setCellValue('H4', 'LOAN AMOUNT')
											  ->setCellValue('I4', 'REQ. COMPLETED')
											  ->setCellValue('J4', 'SIGNED LOAN DOCS')
											  ->setCellValue('K4', 'TAX DEC. LAND')
											  ->setCellValue('L4', 'TAX DEC BUILDING')
											  ->setCellValue('M4', 'TAX RECEIPT LAND')
											  ->setCellValue('N4', 'TAX RECEIPT BUILDING')
											  ->setCellValue('O4', 'BUILDING PERMIT')
											  ->setCellValue('P4', 'ELECTRICAL PERMIT')
											  ->setCellValue('Q4', 'SANITARY PERMIT')
											  ->setCellValue('R4', 'OCCUPANCY PERMIT')
											  ->setCellValue('S4', 'ROAD')
											  ->setCellValue('T4', 'SIDEWALK AND GUTTER')
											  ->setCellValue('U4', 'MERALCO')
											  ->setCellValue('V4', 'WATER')
											  ->setCellValue('W4', 'HOUSE CONSTRUCTION')
											  ->setCellValue('X4', ''); 

$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'); 