<?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();

$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.Project as Project, r.Model as Model, r.ReservationDate as ReservationDate, r.Phase as Phase, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName, CONCAT(MID(r.MiddleName, 1, 1),'.') as MiddleInitial, r.Broker as Broker, r.Agent as Agent, r.Spouse as Spouse, r.HomeTel as HomeTel, r.CellphoneNo as CellphoneNo, r.SpouseContactNo as SpouseContactNo, r.Address1 as Address1, r.Address2 as Address2, r.EmployerName as EmployerName, r.EmployeeType as EmployeeType, r.GrossIncome as GrossIncome, r.DiscountPromo as DiscountPromo, r.Position as Position, r.BuyerProfile as BuyerProfile, r.SpouseIncome as SpouseIncome, r.TinNo as TinNo, r.OfficeTel as OfficeTel, r.AggregateIncome as AggregateIncome, r.NoCoBorrower as NoCoBorrower, r.ReservationStatus as ReservationStatus, r.Status as rStatus, r.EmployeeType as EmployeeType, bsf.TakeOut as TakeOut, con.ReleasedPagibig as ReleasedPagibig, con.ProcessedDate as ProcessedDate, con.ReleasedDate as ReleasedDate, DATE_ADD(bsf.TakeOut, INTERVAL 18 MONTH) as conversions, bpm.ApprovedLoanable as ApprovedLoanable, ubl.TDLandNo as TDLandNo, ubl.TDBldgNo as TDBldgNo, cd.NotarialDate as NotarialDate, cd.TCT as TCT, cd.LMVTD as LMVTD, cd.ZonalValue as ZonalValue, cd.BMVTD as BMVTD, r.LastName as LastName, r.FirstName as FirstName, r.PricelistDate as PricelistDate FROM  reservation r LEFT JOIN buyer_status_folder_pagibig bsf ON bsf.ReservationId=r.Id LEFT JOIN conversion con ON con.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 conversion_details cd ON cd.ReservationId=r.Id  WHERE r.Status = 'active' AND r.ReservationStatus='active'  AND (bsf.TakeOut!='1970-01-01' OR bsf.TakeOut!=NULL)"; 
  
if(!empty($filterSearch)) { 
	$arr = explode(" ",$filterSearch); 
	foreach($arr as $ind){
		$ind = str_replace(',','',$ind);		 
		$sql.=" AND (r.Block LIKE '".$ind."%' ";
		$sql.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(con.ReleasedPagibig,'%m/%d/%Y')) LIKE '".$ind."%' ";   
		$sql.=" OR r.TinNo LIKE '".$ind."%' "; 
		$sql.=" OR r.LastName LIKE '".$ind."%' ";
		$sql.=" OR r.FirstName LIKE '".$ind."%' ";
		$sql.=" OR CONCAT(MID(r.MiddleName, 1, 1),'.') LIKE '".$ind."%' ";  
		$sql.=" OR UPPER(DATE_FORMAT(bsf.TakeOut,'%m/%d/%Y')) LIKE '".$ind."%' "; 
		$sql.=" OR r.Lot LIKE '".$ind."%' )"; 
	}
}

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

$sql .= 'ORDER BY Id desc ';
$mecon->query($sql);  
$main = $mecon->resultset(); 
$counter = count($main); 
$arr1 = array();
$arr2 = array();  
$arr3 = array();  

for($a=0; $a<$counter; $a++){  
	  
	$notarialDate = date('m/d/Y', strtotime($main[$a]['NotarialDate']));
				$notarialDate = ($notarialDate=='01/01/1970') ? "" : $notarialDate;
				
				$arr1[0]  = $notarialDate;
				$arr1[1]  = (($main[$a]['TakeOut']=='1970-01-01') || ($main[$a]['TakeOut']=='')) ? "" : date('m/d/Y', strtotime($main[$a]['TakeOut']));
				$arr1[2]  = $a + 1; 
 				$arr1[3]  = strtoupper(trim(html_entity_decode(stripslashes($main[$a]['LastName']))));
				$arr1[4]  = strtoupper(trim(html_entity_decode(stripslashes($main[$a]['FirstName']))));
				$arr1[5]  = strtoupper(trim(html_entity_decode(stripslashes($main[$a]['MiddleInitial']))));
 				$arr1[6]  = trim($main[$a]['Block']);
				$arr1[7]  = trim($main[$a]['Lot']); 
				 
			//get lot size data	
			$pricedate	= (($main[$a]['PricelistDate']=='1970-01-01') || ($main[$a]['PricelistDate']=='') || ($main[$a]['PricelistDate']==NULL)) ? "" : date('Y-m-d', strtotime($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'])); 
			if($pricedate!='1970-01-01' || $pricedate!='' || $pricedate!=NULL){
				$other = $formClass->getOtherDetails($pricedate,$models,$project,$phase,$block,$lot);		
				$arr1[8]  = $other['lot_size']; 
			}else{
				$arr1[8]  = ''; //LOT SIZE  
			}


				  
 				$tct		  = trim($main[$a]['TCT']); 
				$arr1[9]  	  = $tct;

				$tdLandNo 	  = (!empty(trim($main[$a]['TDLandNo']))) ? 'L - '.substr(trim($main[$a]['TDLandNo']), -5) : "";
				$tdBldgNo	  = (!empty(trim($main[$a]['TDBldgNo']))) ? 'B - '.substr(trim($main[$a]['TDBldgNo']), -5) : "";
 				$arr1[10] 	  =  $tdLandNo;
				$arr1[11] 	  =  $tdBldgNo;

 				$lmvtd		  = (!empty(trim($main[$a]['LMVTD']))) ? number_format(trim($main[$a]['LMVTD']),2) : ""; 
				$arr1[12] 	  = $lmvtd;
  
				$zonalvalue	  = (!empty(trim($main[$a]['ZonalValue']))) ? number_format(trim($main[$a]['ZonalValue']),2) : "";

				$arr1[13] 	  = $zonalvalue; 
 
				$bmvtd	  	  = (!empty(trim($main[$a]['BMVTD']))) ? number_format(trim($main[$a]['BMVTD']),2) : "";
				$arr1[14]  	  = $bmvtd; 
 				
 				if(!empty($tdLandNo) && !empty($tdBldgNo)){
 					$combineTD = $tdLandNo.', '.$tdBldgNo;
 				}else if(!empty($tdLandNo) && empty($tdBldgNo)){
 					$combineTD = $tdLandNo;
 				}else if(empty($tdLandNo) && !empty($tdBldgNo)){
 					$combineTD = $tdBldgNo;
 				}else{
 					$combineTD = "";
 				}

 				$arr1[15]  	  = $combineTD; //auto combine

 				$arr1[16]  	  = (!empty(trim($main[$a]['ApprovedLoanable']))) ? number_format(trim($main[$a]['ApprovedLoanable']),2) : ""; 
 				$arr1[17]  	  = trim($main[$a]['TinNo']);   

 	$arr2[] 	= $arr1;
}

$arr3['data'] = $arr2;
$fileName    = "conversion_details.xls";
$objPHPExcel = $objReader->load("templates/conversion_details.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][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])
											  ->setCellValue('P'.$row, $arr3['data'][$a][16])
											  ->setCellValue('Q'.$row, $arr3['data'][$a][17]);
											  
			$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');
			$objPHPExcel->getActiveSheet()->getStyle('B'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');
			$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
			$objPHPExcel->getActiveSheet()->getStyle('B'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
			$objPHPExcel->getActiveSheet()->getStyle('C'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); 
			$objPHPExcel->getActiveSheet()->getStyle('D'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); 
			$objPHPExcel->getActiveSheet()->getStyle('E'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); 
			$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); 
			$objPHPExcel->getActiveSheet()->getStyle('I'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
			$objPHPExcel->getActiveSheet()->getStyle('J'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); 
			$objPHPExcel->getActiveSheet()->getStyle('K'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); 
			
			$objPHPExcel->getActiveSheet()->getStyle('L'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
			$objPHPExcel->getActiveSheet()->getStyle('M'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
			$objPHPExcel->getActiveSheet()->getStyle('N'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			$objPHPExcel->getActiveSheet()->getStyle('O'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); 
			$objPHPExcel->getActiveSheet()->getStyle('P'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			$objPHPExcel->getActiveSheet()->getStyle('Q'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
 }	

 			
 			
 			//CAPTION  
 			$row = 2;
			$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, 'Notarial Date')
											  ->setCellValue('B'.$row, 'Takeout Date') 
											  ->setCellValue('C'.$row, 'Last Name')
											  ->setCellValue('D'.$row, 'First Name')
											  ->setCellValue('E'.$row, 'M.I.')
											  ->setCellValue('F'.$row, 'Block')
											  ->setCellValue('G'.$row, 'Lot')
											  ->setCellValue('H'.$row, 'Lot Size')
											  ->setCellValue('I'.$row, 'TCT')
											  ->setCellValue('J'.$row, 'Land - TD No.')
											  ->setCellValue('K'.$row, 'Bldg - TD No.')
											  ->setCellValue('L'.$row, 'LMV - TD')
											  ->setCellValue('M'.$row, 'Zonal Value')
											  ->setCellValue('N'.$row, 'BMV - TD')
											  ->setCellValue('O'.$row, 'Tax-Dec, L&B')
											  ->setCellValue('P'.$row, 'Selling Price / Loanable Amount')
											  ->setCellValue('Q'.$row, 'TIN'); 

			$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
			$objPHPExcel->getActiveSheet()->getStyle('B'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
			$objPHPExcel->getActiveSheet()->getStyle('C'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
			$objPHPExcel->getActiveSheet()->getStyle('D'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
			$objPHPExcel->getActiveSheet()->getStyle('E'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
			$objPHPExcel->getActiveSheet()->getStyle('F'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
			$objPHPExcel->getActiveSheet()->getStyle('G'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
			$objPHPExcel->getActiveSheet()->getStyle('H'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
			$objPHPExcel->getActiveSheet()->getStyle('I'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
			$objPHPExcel->getActiveSheet()->getStyle('J'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
			$objPHPExcel->getActiveSheet()->getStyle('K'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
			$objPHPExcel->getActiveSheet()->getStyle('L'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
			$objPHPExcel->getActiveSheet()->getStyle('M'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
			$objPHPExcel->getActiveSheet()->getStyle('N'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$objPHPExcel->getActiveSheet()->getStyle('O'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
			$objPHPExcel->getActiveSheet()->getStyle('P'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$objPHPExcel->getActiveSheet()->getStyle('Q'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$styleArray = array('font' => array('bold' => true));
$objPHPExcel->getActiveSheet()->getStyle('A2:Q2')->applyFromArray($styleArray);
unset($styleArray);   

$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
$objWriter->save('php://output'); 