<?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']) ? strip_tags($_GET['filter']) : "";
$filterSearch = isset($_GET['filter']) ? str_replace("'"," ",$_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, r.ReservationStatus as ReservationStatus, bpm.DateRFDeposited as DateRFDeposited,bpm.StartOfEquity as StartOfEquity,bpm.TCP as TCP,bpm.LoanableAmount as LoanableAmount,bpm.NetEquity as NetEquity,bpm.Discount as Discount,bpm.PDCSubmitted as PDCSubmitted, bpm.LackingPDC as LackingPDC, bpm.TermsOfPayment as TermsOfPayment, bpm.PDCStatus as PDCStatus, bpm.Interest as Interest,bpm.MonthlyEquity as MonthlyEquity,bpm.RemarksOnEquity as RemarksOnEquity,bpm.PaymentsMade as PaymentsMade,bpm.TakeOutAmount as TakeOutAmount,bpm.MRI as MRI,bpm.DifferenceApprovedVSLoanable as DifferenceApprovedVSLoanable,bpm.Misc as Misc,bpm.Adjustment as Adjustment,bpm.AdjustmentDate as AdjustmentDate,bpm.PagibigLoanableAmount as PagibigLoanableAmount,bpm.MoveInMRI as MoveInMRI,bpm.MoveInOthers as MoveInOthers,bpm.RefillingFee as RefillingFee,bpm.PagIbigUpgrading as PagIbigUpgrading,bpm.PagIbigLoanUpdate as PagIbigLoanUpdate,bpm.CollectedDate as CollectedDate,bpm.DepositedDate as DepositedDate,bpm.MoveInAmount as MoveInAmount,bpm.TakeOutDate as TakeOutDate, r.Project, r.Model, bpm.PaymentMode FROM reservation r 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 Block LIKE '%".$ind."%' ";
		$sql.=" OR Lot LIKE '%".$ind."%' ";
		$sql.=" OR CONCAT(LastName,', ',FirstName,' ',MiddleName) LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(DateRFDeposited,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(StartOfEquity,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(TakeOutDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR TCP LIKE '%".$ind."%' ";
		$sql.=" OR Discount LIKE '%".$ind."%' ";
		$sql.=" OR LoanableAmount LIKE '%".$ind."%' ";
		$sql.=" OR NetEquity LIKE '%".$ind."%' ";
		$sql.=" OR Interest LIKE '%".$ind."%' ";
		$sql.=" OR TermsOfPayment LIKE '%".$ind."%' ";
		$sql.=" OR MonthlyEquity LIKE '%".$ind."%' ";
		$sql.=" OR LackingPDC LIKE '%".$ind."%' ";
		$sql.=" OR PDCStatus LIKE '%".strtolower(str_replace(" ","_",$ind))."%' ";
		$sql.=" OR RemarksOnEquity LIKE '%".$ind."%' ";
		$sql.=" OR TakeOutAmount LIKE '%".$ind."%' ";
		$sql.=" OR MRI LIKE '%".$ind."%' ";
		$sql.=" OR DifferenceApprovedVSLoanable LIKE '%".$ind."%' ";
		$sql.=" OR Adjustment LIKE '%".$ind."%' ";
		$sql.=" OR PagibigLoanableAmount LIKE '%".$ind."%' ";
		$sql.=" OR Misc LIKE '%".$ind."%' ";
		$sql.=" OR MoveInMRI LIKE '%".$ind."%' ";
		$sql.=" OR MoveInOthers LIKE '%".$ind."%' ";
		$sql.=" OR RefillingFee LIKE '%".$ind."%' ";
		$sql.=" OR PagIbigUpgrading LIKE '%".$ind."%' ";
		$sql.=" OR PagIbigLoanUpdate LIKE '%".$ind."%' "; 
		$sql.=" OR UPPER(DATE_FORMAT(CollectedDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(DepositedDate,'%m/%d/%Y')) LIKE '%".$ind."%' "; 
		$sql.=" OR MoveInAmount 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 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]	= stripslashes(html_entity_decode($main[$a]['Fullname']));
	 
	$arr1[4]	= (!empty($main[$a]['ReservationDate']) && ($main[$a]['ReservationDate']!='1970-01-01')) ? $main[$a]['ReservationDate'] : "";
	$arr1[5]	= (!empty($main[$a]['DateRFDeposited']) && ($main[$a]['DateRFDeposited']!='1970-01-01')) ? $main[$a]['DateRFDeposited'] : ""; 
	$arr1[6]	= (!empty($main[$a]['StartOfEquity']) && ($main[$a]['StartOfEquity']!='1970-01-01')) ? $main[$a]['StartOfEquity'] : "";
	
	$arr1[7]	= number_format($main[$a]['TCP'],2); 
	$arr1[8]	= number_format($main[$a]['Discount'],2); 
	$arr1[9]	= number_format($main[$a]['LoanableAmount'],2); 
	$arr1[10]	= number_format($main[$a]['NetEquity'],2); 
	//$arr1[11]	= number_format($main[$a]['Interest'],2); 
	//$arr1[12]	= $main[$a]['TermsOfPayment'];
	$arr1[11]	= ($main[$a]['PaymentMode']=="cash") ? "cash" : $main[$a]['TermsOfPayment']; 
	$arr1[12]	= number_format($main[$a]['MonthlyEquity'],2); 
	$arr1[13]	= $main[$a]['LackingPDC']; 
	$arr1[14]	= strtoupper(str_replace("_"," ",$main[$a]['PDCStatus']));    
	$arr1[15]	= $main[$a]['RemarksOnEquity'];  
	$arr1[16]	= (!empty($main[$a]['TakeOutDate']) && ($main[$a]['TakeOutDate']!='1970-01-01')) ? $main[$a]['TakeOutDate'] : ""; 
	
	$arr1[17]	= number_format($main[$a]['TakeOutAmount'],2); 
	$arr1[18]	= number_format($main[$a]['MRI'],2); 
	$arr1[19]	= number_format($main[$a]['DifferenceApprovedVSLoanable'],2); 
	$arr1[20]	= number_format($main[$a]['Adjustment'],2);  
	$arr1[21]	= number_format($main[$a]['PagibigLoanableAmount'],2);
	$arr1[22]	= number_format($main[$a]['Misc'],2); 
	$arr1[23]	= number_format($main[$a]['MoveInMRI'],2); 
	$arr1[24]	= number_format($main[$a]['MoveInOthers'],2); 
	$arr1[25]	= number_format($main[$a]['RefillingFee'],2); 
	$arr1[26]	= number_format($main[$a]['PagIbigUpgrading'],2); 
	$arr1[27]	= number_format($main[$a]['PagIbigLoanUpdate'],2);  
	$arr1[28]	= (!empty($main[$a]['CollectedDate']) && ($main[$a]['CollectedDate']!='1970-01-01')) ? $main[$a]['CollectedDate'] : ""; 
	$arr1[29]	= (!empty($main[$a]['DepositedDate']) && ($main[$a]['DepositedDate']!='1970-01-01')) ? $main[$a]['DepositedDate'] : "";
	$arr1[30]	= number_format($main[$a]['MoveInAmount'],2); 
	$arr2[] 	= $arr1;
}
$arr3['data'] = $arr2;
 
 
$fileName    = "buyer_payment_monitoring.xls";
$objPHPExcel = $objReader->load("templates/report_status_of_accounts_for_conversion.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][12])
											  ->setCellValue('L'.$row, $arr3['data'][$a][11])
											  ->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])
											  ->setCellValue('R'.$row, $arr3['data'][$a][18])
											  ->setCellValue('S'.$row, $arr3['data'][$a][19])
											  ->setCellValue('T'.$row, $arr3['data'][$a][20])
											  ->setCellValue('U'.$row, $arr3['data'][$a][21])
											  ->setCellValue('V'.$row, $arr3['data'][$a][22])
											  ->setCellValue('W'.$row, $arr3['data'][$a][23])
											  ->setCellValue('X'.$row, $arr3['data'][$a][24])
											  ->setCellValue('Y'.$row, $arr3['data'][$a][25])
											  ->setCellValue('Z'.$row, $arr3['data'][$a][26])
											  ->setCellValue('AA'.$row, $arr3['data'][$a][27])
											  ->setCellValue('AB'.$row, $arr3['data'][$a][28])
											  ->setCellValue('AC'.$row, $arr3['data'][$a][29])
											  ->setCellValue('AD'.$row, $arr3['data'][$a][30]); 
		$objPHPExcel->getActiveSheet()->getStyle('D'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');
		$objPHPExcel->getActiveSheet()->getStyle('E'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');
		$objPHPExcel->getActiveSheet()->getStyle('F'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');
		$objPHPExcel->getActiveSheet()->getStyle('P'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');
		$objPHPExcel->getActiveSheet()->getStyle('AC'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');
		//$objPHPExcel->getActiveSheet()->getStyle('AD'.$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'); 