<?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.Id as Id, bpm.LoanableAmount as LoanableAmount, r.Block as Block, r.Phase as Phase, r.PricelistDate as PricelistDate, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname, r.ReservationDate, m.HouseConstruction as HouseConstruction, m.MonitoringFolderDetails as MonitoringFolderDetails, r.Model, r.Project FROM reservation r LEFT JOIN monitoring_folders m ON m.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.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 m.HouseConstruction LIKE '%".$ind."%' ";   
		$sql.=" OR m.MonitoringFolderDetails 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))."') ";
	} 
}
	
$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]	= html_entity_decode(stripslashes($main[$a]['HouseConstruction']));
	$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]['MonitoringFolderDetails']),true);
			 if(count($detArr)!=0){ 
				$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				 	= "";  
				
					foreach($detArr as $detInd){ 
						if($detInd=="req_completed"){	$req_completed = "OK";	} 
						if($detInd=="signed_loan_docs"){	$signed_loan_docs = "OK";	 } 
						if($detInd=="sworn_cert"){	 $sworn_cert = "OK";	 } 
						if($detInd=="tax_dec_land"){ $tax_dec_land = "OK";	 } 
						if($detInd=="tax_dec_building"){ $tax_dec_building = "OK";	 } 
						if($detInd=="tax_land_receipt"){ $tax_land_receipt = "OK";	 } 
						if($detInd=="tax_building_receipt"){ $tax_building_receipt = "OK";	 } 

						if($detInd=="building_permit"){	$building_permit = "OK"; } 
						if($detInd=="electrical_permit"){	$electrical_permit = "OK";	 } 
						if($detInd=="sanitary_permit"){	$sanitary_permit = "OK"; }  
						if($detInd=="occupancy_permit"){	$occupancy_permit = "OK";	 } 

						if($detInd=="road"){	$road = "OK";	 } 
						if($detInd=="sidewalk_and_gutter"){	$sidewalk_and_gutter = "OK";	 } 
						if($detInd=="meralco"){	$meralco = "OK";	 } 
						if($detInd=="water"){	$water = "OK";	 }
					}
					
						$arr1[7] 	= $req_completed; 
						$arr1[8] 	= $signed_loan_docs; 
						$arr1[9] 	= $sworn_cert; 
						$arr1[10] 	= $tax_dec_land; 
						$arr1[11] 	= $tax_dec_building; 
						$arr1[12] 	= $tax_land_receipt; 
						$arr1[13] 	= $tax_building_receipt; 
						$arr1[14] 	= $building_permit; 
						$arr1[15] 	= $electrical_permit; 
						$arr1[16] 	= $sanitary_permit; 
						$arr1[17] 	= $occupancy_permit;  
						$arr1[18] 	= $road; 
						$arr1[19] 	= $sidewalk_and_gutter; 
						$arr1[20] 	= $meralco; 
						$arr1[21] 	= $water; 
			 }else{
				$arr1[7]	= "";
				$arr1[8]	= "";
				$arr1[9]	= "";
				$arr1[10]	= "";
				$arr1[11]	= "";
				$arr1[12]	= "";
				$arr1[13]	= "";
				$arr1[14]	= "";
				$arr1[15]	= "";
				$arr1[16]	= "";
				$arr1[17]	= "";
				$arr1[18]	= "";
				$arr1[19]	= "";
				$arr1[20]	= "";
				$arr1[21]	= "";
			 } 
	
	$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][9])
											  ->setCellValue('L'.$row, $arr3['data'][$a][10])
											  ->setCellValue('M'.$row, $arr3['data'][$a][11])
											  ->setCellValue('N'.$row, $arr3['data'][$a][12])
											  ->setCellValue('O'.$row, $arr3['data'][$a][13])
											  ->setCellValue('P'.$row, $arr3['data'][$a][14])
											  ->setCellValue('Q'.$row, $arr3['data'][$a][15])
											  ->setCellValue('R'.$row, $arr3['data'][$a][16])
											  ->setCellValue('S'.$row, $arr3['data'][$a][17])
											  ->setCellValue('T'.$row, $arr3['data'][$a][18])
											  ->setCellValue('U'.$row, $arr3['data'][$a][19])
											  ->setCellValue('V'.$row, $arr3['data'][$a][20])
											  ->setCellValue('W'.$row, $arr3['data'][$a][21])
											  ->setCellValue('X'.$row, $arr3['data'][$a][23]); 
											  
			$objPHPExcel->getActiveSheet()->getStyle('G'.$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
$objWriter->save('php://output'); 