<?php 
set_time_limit(0);
error_reporting(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']) : "";
$tobedate 	= isset($_REQUEST['tobe_stat_date'])   ? $_REQUEST['tobe_stat_date']	  : "";
 
$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, bpm.LoanableAmount as LoanableAmount,bsp.Facility as Facility, bsp.DateDelivered as DateDelivered, bsp.ReturnedDate as ReturnedDate, bsp.BankOfficer as BankOfficer, bsp.LOG as LOG, bsp.Booking as Booking, bsp.TakeOut as TakeOut, bsp.Reason as Reason, r.Project, r.Model FROM reservation r LEFT JOIN buyer_status_folder_bank bsp ON bsp.ReservationId=r.Id LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id "; 


$sql .=	 "  WHERE r.Status = 'active' AND bpm.Financing='bank' AND (r.ScheduleLoan='delivered') ";  


if($tobedate=='yes' || $isfilterdate=='yes'){
$sql .= " AND bsp.DateDelivered BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";

}

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.Model LIKE '%".$ind."%' ";
		$sql.=" OR r.Project LIKE '%".$ind."%' ";
		$sql.=" OR bsp.Facility LIKE '%".$ind."%' ";
		$sql.=" OR bsp.Reason LIKE '%".$ind."%' ";
		$sql.=" OR bsp.BankOfficer LIKE '%".$ind."%' ";
		$sql.=" OR bpm.LoanableAmount LIKE '%".$ind."%' ";
		$sql.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(bsp.TakeOut,'%m/%d/%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsp.Booking,'%m/%d/%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsp.LOG,'%m/%d/%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsp.DateDelivered,'%m/%d/%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsp.ReturnedDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";     
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%m/%d/%Y')) LIKE '%".$ind."%'  )";
	}
}
/*
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))."') ";
	} 
*/

if($isfilterdate=="yes"){
	if(!empty($monthFrom) && !empty($monthTo)){
		$sql .= " AND bsp.DateDelivered BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
	}else if( !empty($monthFrom)){
		$sql .= " AND bsp.DateDelivered >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
	}else if( !empty($monthTo)){
		$sql .= " AND bsp.DateDelivered <= ('".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]	= stripslashes(html_entity_decode($main[$a]['Fullname'])); 
	$arr1[2]	= $main[$a]['Block'];
	$arr1[3]	= $main[$a]['Lot'];
	$arr1[4]	= number_format($main[$a]['LoanableAmount'],2);
	$arr1[5]	= $main[$a]['Facility']; 
	$arr1[6]	= (!empty($main[$a]['DateDelivered'])  && ($main[$a]['DateDelivered']!='1970-01-01')) 	?	$main[$a]['DateDelivered'] : "";
	$arr1[7]	= (!empty($main[$a]['ReturnedDate'])  && ($main[$a]['ReturnedDate']!='1970-01-01')) ?	$main[$a]['ReturnedDate'] : "";
	$arr1[8]	= $main[$a]['BankOfficer']; 
	$arr1[9]	= (!empty($main[$a]['LOG'])  && ($main[$a]['LOG']!='1970-01-01'))						? 	$main[$a]['LOG']			:	"";
	$arr1[10]	= (!empty($main[$a]['Booking'])  && ($main[$a]['Booking']!='1970-01-01')) 	? 	$main[$a]['Booking']	: "";
	$arr1[11]	= (!empty($main[$a]['TakeOut'])  && ($main[$a]['TakeOut']!='1970-01-01'))				? 	$main[$a]['TakeOut']		:	"";
	$arr1[12]	= $main[$a]['Reason'];
	$arr2[] 	= $arr1;
}

$arr3['data'] = $arr2;
$fileName    = "buyer_status_folder_bank.xls";
$objPHPExcel = $objReader->load("templates/report_buyer_status_folder_bank.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][11])
											  ->setCellValue('L'.$row, $arr3['data'][$a][12]);
											  
			$objPHPExcel->getActiveSheet()->getStyle('F'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');
			$objPHPExcel->getActiveSheet()->getStyle('G'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');								  
			$objPHPExcel->getActiveSheet()->getStyle('I'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');								  
			$objPHPExcel->getActiveSheet()->getStyle('J'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');								  
			$objPHPExcel->getActiveSheet()->getStyle('K'.$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'); 