<?php 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');


$projectName  = $_SESSION['castalia_project'];
$projectVenue = $_SESSION['castalia_project_venue'];

$autoload	=	new autoloadClass();
$mecon		=	new condbClass();
$formClass	=	new formClass();
$main		=	array();
$main2		=	array();
$main3		=	array();
$main4		=	array();
$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']) : "";
 $monthName = "January";
 $row = ""; 
 $arr3 = array();  
 
 //$arr3 = getLoanByMonthDetails($filterSearch,$monthFrom,$monthTo,$monthName);
 
function getLoanMonth($filterSearch,$monthFrom,$monthTo,$isfilterdate){
	$autoload		=	new autoloadClass(); 
	$mecon			=	new condbClass();	
	$formClass		=	new formClass();
	
	$sql = "SELECT MONTHNAME(r.ToBeDeliveredDate) as ReservationMonth, COUNT(r.ToBeDeliveredDate) as ctrMonth, SUM(bpm.LoanableAmount) as SubLoanableAmount, r.ScheduleLoan, r.ToBeDeliveredDate FROM reservation r LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id   WHERE r.Status = 'active' AND bpm.Financing='pagibig' AND (r.ScheduleLoan='to_be_delivered' OR r.ScheduleLoan='delivered') "; 
	
	
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.ScheduleLoan 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 '%".$ind."%' "; 
		$sql.=" OR r.ScheduleLoan = '".strtolower(str_replace(" ","_",$filterSearch))."' ";
		$sql.=" OR UPPER(DATE_FORMAT(r.ToBeDeliveredDate,'%m/%d/%Y')) 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.ToBeDeliveredDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
	}else if( !empty($monthFrom)){
		$sql .= " AND r.ToBeDeliveredDate >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
	}else if( !empty($monthTo)){
		$sql .= " AND r.ToBeDeliveredDate <= ('".date('Y-m-d', strtotime($monthTo))."') ";
	} 
} 
	
	$sql .= " GROUP BY  MONTHNAME(r.ToBeDeliveredDate) ORDER BY MONTH(r.ToBeDeliveredDate) ASC, cast(r.Block as unsigned) asc, cast(r.Lot as unsigned) asc ";
	$mecon->query($sql);
	$main = $mecon->resultset(); 
	$counter = count($main); 
	$carr1 = array();
	$carr2 = array();  
	$carr3 = array();  

	for($a=0; $a<$counter; $a++){
		$carr1[0]	= $main[$a]['ReservationMonth'];
		$carr1[1]	= $main[$a]['SubLoanableAmount']; 
		$carr1[2]	= $main[$a]['ctrMonth']; 
		$carr2[] 	= $carr1;
	}
	$carr3['data'] = $carr2;
	return $carr3; 
}




function getLoanByMonthDetails($filterSearch,$monthFrom,$monthTo,$monthName,$isfilterdate){
	$autoload		=	new autoloadClass(); 
	$mecon			=	new condbClass();	
	$formClass			=	new formClass();
	
$sql = "SELECT MONTHNAME(r.ToBeDeliveredDate) as ReservationMonth, 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.TermsOfPayment as TermsOfPayment,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, r.ToBeDeliveredDate, r.Phase, r.PricelistDate, r.ScheduleLoan FROM reservation r LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id   WHERE r.Status = 'active' AND bpm.Financing='pagibig' AND (r.ScheduleLoan='to_be_delivered' OR r.ScheduleLoan='delivered') AND MONTHNAME(r.ToBeDeliveredDate)='$monthName'"; 

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.ScheduleLoan LIKE '%".$ind."%' ";
		$sql.=" OR r.Block LIKE '%".$ind."%' ";
		$sql.=" OR r.Lot LIKE '%".$ind."%' ";
		$sql.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".$ind."%' "; 
		$sql.=" OR bpm.LoanableAmount LIKE '%".$ind."%' "; 
		$sql.=" OR r.ScheduleLoan = '".strtolower(str_replace(" ","_",$filterSearch))."' ";
		$sql.=" OR UPPER(DATE_FORMAT(r.ToBeDeliveredDate,'%m/%d/%Y')) 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.ToBeDeliveredDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
	}else if( !empty($monthFrom)){
		$sql .= " AND r.ToBeDeliveredDate >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
	}else if( !empty($monthTo)){
		$sql .= " AND r.ToBeDeliveredDate <= ('".date('Y-m-d', strtotime($monthTo))."') ";
	} 
} 
	

	$sql .= " ORDER BY r.ToBeDeliveredDate desc, cast(Block as unsigned) asc, cast(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]	= strtoupper(stripslashes(html_entity_decode($main[$a]['FullName'])));
		$arr1[2]	= $main[$a]['Block'];
		$arr1[3]	= $main[$a]['Lot'];
		
				$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[8] = $other['floor_area'];
				
		$arr1[5]	= (!empty($main[$a]['ToBeDeliveredDate']) && ($main[$a]['ToBeDeliveredDate']!='1970-01-01')) ? $main[$a]['ToBeDeliveredDate'] : ""; 
		$arr1[6]	= number_format($main[$a]['LoanableAmount'],2);   
		$arr1[7]	= $main[$a]['ReservationMonth'];
		$arr1[9]	= str_replace("_"," ",strtoupper($main[$a]['ScheduleLoan']));
		$arr2[] 	= $arr1;
	}
	$arr3['data'] = $arr2;
	return $arr3;
} 
 
 
 
$fileName    = "schedule_of_loan_folders.xls";
$objPHPExcel = $objReader->load("templates/schedule_of_loan_folders.xls"); 

$baseRow = 4;
  $total = 0;
  
$arr3 = getLoanMonth($filterSearch,$monthFrom,$monthTo,$isfilterdate); 
$marr = array(); 
$rowc = 0;
$row = $baseRow + 1; 
 for($a=0; $a<count($arr3['data']); $a++){  
				$monthName	= $arr3['data'][$a][0];
				$subtot		= number_format($arr3['data'][$a][1],2);
				$monthCtr	= $arr3['data'][$a][2];
				$marr = getLoanByMonthDetails($filterSearch,$monthFrom,$monthTo,$monthName,$isfilterdate); 
				$objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1); 
				$objPHPExcel->getActiveSheet()->setCellValue('H'.$row,$monthName);  
				
				$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
				$styleArray2 = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),'font' => array('bold' => true));
				$objPHPExcel->getActiveSheet()->getStyle('A1'.':I1')->applyFromArray($styleArray);
				$objPHPExcel->getActiveSheet()->getStyle('A2'.':I2')->applyFromArray($styleArray);
				$objPHPExcel->getActiveSheet()->getStyle('A'.$row.':I'.$row)->applyFromArray($styleArray); 
				unset($styleArray); 
				
				for($c=0; $c<count($marr['data']); $c++){  
					$detRow = $row + $c + 1;    
					$objPHPExcel->getActiveSheet()->insertNewRowBefore($detRow,1); 
					$objPHPExcel->getActiveSheet()->setCellValue('A'.$detRow, $marr['data'][$c][0])
												  ->setCellValue('B'.$detRow, $marr['data'][$c][5])
												  ->setCellValue('C'.$detRow, $marr['data'][$c][2])
												  ->setCellValue('D'.$detRow, $marr['data'][$c][3])
												  ->setCellValue('E'.$detRow, $marr['data'][$c][4])
												  ->setCellValue('F'.$detRow, $marr['data'][$c][8])
												  ->setCellValue('G'.$detRow, $marr['data'][$c][1])
												  ->setCellValue('H'.$detRow, $marr['data'][$c][6])
												  ->setCellValue('I'.$detRow, $marr['data'][$c][9]);
					$objPHPExcel->getActiveSheet()->getStyle('B'.$detRow)->getNumberFormat()->setFormatCode('mm/dd/yyyy');
				}
				 
				$rowc = $row+$monthCtr+1; 
				$objPHPExcel->getActiveSheet()->setCellValue('G'.$rowc,"SUB-TOTAL");  
				$objPHPExcel->getActiveSheet()->setCellValue('H'.$rowc,$subtot);  
				$styleArray2 = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),'font' => array('bold' => true));
				$objPHPExcel->getActiveSheet()->getStyle('G'.$rowc.':H'.$rowc)->applyFromArray($styleArray2); 
				unset($styleArray2); 
				$row += $monthCtr + 3; 
				$total += str_replace(",","",$subtot);
 }  
 $row = $rowc + 2;
 $objPHPExcel->getActiveSheet()->setCellValue('G'.$row, "TOTAL");
 $objPHPExcel->getActiveSheet()->setCellValue('H'.$row, number_format($total,2));

$objPHPExcel->getActiveSheet()->setCellValue('A1', $projectName);


 $styleArray2 = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),'font' => array('bold' => true));
				$objPHPExcel->getActiveSheet()->getStyle('G'.$row.':H'.$row)->applyFromArray($styleArray2); 
				unset($styleArray2); 



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