<?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');
$currentUser 	= $_SESSION['danvillee_username'];
$timeStamp	= date('Y-m-d');
$autoload	=	new autoloadClass();
$mecon		=	new condbClass();
$formClass	=	new formClass();
$utility	=	new utilityClass();
$actArr 	= array();
$main		=	array();
$main2		=	array();
$main3		=	array();
$main4		=	array();
$rId = (isset($_GET['id'])) ? strip_tags(trim($_GET['id'])) : "434";
//$filterSearch = isset($_GET['filter']) ? strip_tags($_GET['filter']) : "";
$filterSearch = isset($_GET['filter']) ? str_replace("'"," ",$_GET['filter']) : "";
$filterSearch = ($filterSearch=="undefined") ? "" : $filterSearch;
$sql = "SELECT r.Project as Project, r.PricelistDate as PricelistDate, r.Model as Model, r.Phase as Phase, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName, bcm.Id as Id, bcm.ReservationId as ReservationId, bcm.ChequeDate as ChequeDate, bcm.ChequeNo as ChequeNo, bcm.Bank as Bank, bcm.Amount as Amount, bcm.Remarks as Remarks, bcm.ChequeStatus as ChequeStatus, bcm.ChequeProcessDate as ChequeProcessDate, bcm.OrNum as OrNum, bcm.ReturnRemarks as ReturnRemarks, bpm.NetEquity as NetEquity, bcm.ModePayment as ModePayment,bpm.MonthlyEquity as MonthlyEquity, bpm.PDCSubmittedStatus as PDCSubmitted, bpm.LackingPDC as LackingPDC,
bpm.TermsOfPayment as TermsOfPayment, bpm.PDCStatus as PDCStatus, bpm.StartOfEquity as StartOfEquity, bpm.Financing as Financing, bsm.PermitToOccupy as PermitToOccupy,
bcm.Transcode as Transcode, bcm.PenaltyStatus as PenaltyStatus, 
r.ConstructionStatusUponReservation as construction_status, 
 bpm.LoanableAmount as loanable_amount, bpm.TCP as tcp, 
 bsm.PTOStatus as PTOStatus, bpm.Financing as Financing, bsm.PermitToOccupy as PermitToOccupy, 
 bpm.PDCDownpayment as PDCDownpayment, bpm.PDCDiscount as PDCDiscount 
FROM buyer_cheque_monitoring bcm LEFT JOIN reservation r ON r.Id=bcm.ReservationId LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id LEFT JOIN buyer_status_monitoring bsm ON bsm.ReservationId=r.Id 
WHERE bcm.Status = 'active' AND bcm.ReservationId='$rId' ";

 
if(!empty($filterSearch)) { 
	$arr = explode(" ",$filterSearch);  
	foreach($arr as $ind){
		$ind = str_replace(',','',$ind);
		$sql2 ="  AND ( r.Block LIKE '%".$ind."%' ";
		$sql2.=" OR r.Lot LIKE '%".$ind."%' ";
		$sql2.=" OR r.Model LIKE '%".$ind."%' ";
		$sql2.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' ";

		$sql2.=" OR bcm.Transcode LIKE '%".$ind."%' ";   
		$sql2.=" OR bcm.PenaltyStatus LIKE '%".$ind."%' ";   

		$sql2.=" OR bcm.ChequeNo LIKE '%".$ind."%' "; 
		$sql2.=" OR bcm.ChequeStatus LIKE '%".$ind."%' "; 
		$sql2.=" OR bcm.Bank LIKE '%".$ind."%' "; 
		$sql2.=" OR bcm.ReturnRemarks LIKE '%".$ind."%' "; 
		$sql2.=" OR bcm.Amount LIKE '%".$ind."%' "; 
		$sql2.=" OR bcm.Amount LIKE '%".str_replace(',','',$ind)."%' "; 
		$sql2.=" OR bcm.Remarks LIKE '%".$ind."%' "; 
		$sql2.=" OR UPPER(DATE_FORMAT(bcm.ChequeDate,'%d-%b-%Y')) LIKE '%".$ind."%' ";   
		$sql2.=" OR UPPER(DATE_FORMAT(bcm.ChequeProcessDate,'%d-%b-%Y')) LIKE '%".$ind."%' )";   
	}
	$sql .= $sql2;
}


$sql .= "  ORDER BY bcm.Id, 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++){  

	$models						= $main[$a]['Model']; 
	$project					= $main[$a]['Project']; 
	$pricedate					= $main[$a]['PricelistDate']; 
	$phase						= $main[$a]['Phase']; 
	$block						= $main[$a]['Block']; 
	$lot						= $main[$a]['Lot']; 
	$other = $formClass->getOtherDetails($pricedate,$models,$project,$phase,$block,$lot);		
	/**********************/


	$arr1[0]	= $a+1;
	//$arr1[1]	= $main[$a]['Id'];
	$arr1[1]	= $a+1;
	$arr1[22]	= $main[$a]['Project'];
	$arr1[2]	= $main[$a]['Block'];
	$arr1[3]	= $main[$a]['Lot'];
	$arr1[4]	= stripslashes(html_entity_decode($main[$a]['FullName']));	 
	$arr1[5]	= (!empty($main[$a]['ChequeDate']) && ($main[$a]['ChequeDate']!='1970-01-01')) ? $main[$a]['ChequeDate'] : "";
	
	$arr1[6]	= number_format($main[$a]['Amount'],2);  
	$arr1[7]	= $main[$a]['Bank'];
	$arr1[8]	= $main[$a]['ChequeNo'];
	$arr1[9]	= strtoupper($main[$a]['ReturnRemarks']);
	$arr1[10]	= $main[$a]['NetEquity'];
	 
	$arr1[11]	= !empty($main[$a]['OrNum']) ? $main[$a]['OrNum'] : "";
	

	

	if(strtoupper($main[$a]['ChequeStatus'])=="RETURNED"){
		$checkStatus = "RETURNED DUE TO DAIF";
	}else if(strtoupper($main[$a]['ChequeStatus'])=="CLOSED"){
		$checkStatus = "RETURNED DUE TO ACCOUNT CLOSED";
	}else{
		$checkStatus = strtoupper($main[$a]['ChequeStatus']);
	} 
	$arr1[12]	= $checkStatus;
	//$arr1[12]	= strtoupper($main[$a]['ChequeStatus']);

	$arr1[13]	= (!empty($main[$a]['ChequeProcessDate']) && ($main[$a]['ChequeProcessDate']!='1970-01-01')) ? $main[$a]['ChequeProcessDate'] : ""; 
	$arr1[14]	= isset($main[$a]['Remarks']) ? strtoupper($main[$a]['Remarks']) : "";


	if(strtoupper($main[$a]['ModePayment'])=="OVER_THE_COUNTER_DEPOSIT"){
			$mode_payment = "OVER THE COUNTER DEPOSIT";
	}else{
			$mode_payment = strtoupper($main[$a]['ModePayment']);
	}
	$arr1[15]	= !empty($main[$a]['ModePayment']) ? $mode_payment : "";
	
	$arr1[16]	= !empty($main[$a]['MonthlyEquity']) ? number_format($main[$a]['MonthlyEquity'],2) : $main[$a]['MonthlyEquity'];
	$arr1[17]	= !empty($main[$a]['PDCSubmitted']) ? $main[$a]['PDCSubmitted'] : "";
	$arr1[18]	= !empty($main[$a]['LackingPDC']) ? $main[$a]['LackingPDC'] : "";
	$arr1[19]	= !empty($main[$a]['TermsOfPayment']) ? $main[$a]['TermsOfPayment'] : "";
	$arr1[20]	= isset($main[$a]['PDCStatus']) ? strtoupper($main[$a]['PDCStatus']) : "";
	$arr1[21]	= (!empty($main[$a]['StartOfEquity']) && ($main[$a]['StartOfEquity']!='1970-01-01')) ? $main[$a]['StartOfEquity'] : ""; 
	$arr1[23]	= isset($main[$a]['Financing']) ? strtoupper($main[$a]['Financing']) : ""; 
	$arr1[24]	= (!empty($main[$a]['PermitToOccupy']) && ($main[$a]['PermitToOccupy']!='1970-01-01')) ? $main[$a]['PermitToOccupy'] : ""; 

	$arr1[25]	= isset($main[$a]['Transcode']) ? strtoupper($main[$a]['Transcode']) : ""; 
	$arr1[26]	= isset($main[$a]['PenaltyStatus']) ? strtoupper($main[$a]['PenaltyStatus']) : ""; 
	
//	$arr1[27]	=  $main[$a]['PDCDownpayment']; 
//	$arr1[28]	=  $main[$a]['PDCDiscount'];


	$arr1[27]	= ($main[$a]['PDCDownpayment']!=0.00) ?  trim(html_entity_decode(stripslashes(str_replace(',','',$main[$a]['PDCDownpayment'])))) : str_replace(',','',$other['downpayment']); 
  	$arr1[28]	=  ($main[$a]['PDCDiscount']!=0.00) ?  trim(html_entity_decode(stripslashes(str_replace(',','',$main[$a]['PDCDiscount'])))) :  0.00; 


	$arr1[29]	= isset($main[$a]['construction_status']) ? strtoupper($main[$a]['construction_status']) : "";
	$arr1[30]	= isset($main[$a]['PTOStatus']) ? strtoupper($main[$a]['PTOStatus']) : "";


	$arr2[] 	= $arr1;
}

$arr3['data'] = $arr2;
 
 
$fileName    = "postdated_checks_received.xls";
$objPHPExcel = $objReader->load("templates/postdated_checks.xls"); 
 
$mval		=	isset($arr3['data'][0][10]) ? number_format($arr3['data'][0][10],2) : "";
$aval		=   isset($arr3['data'][0][4]) ? $arr3['data'][0][4] : "";
$bval		=   isset($arr3['data'][0][2]) ? $arr3['data'][0][2] : "";
$cval		=   isset($arr3['data'][0][3]) ? $arr3['data'][0][3] : "";

$MonthlyEquity	=   isset($arr3['data'][0][16]) ? $arr3['data'][0][16] : "";
$PDCSubmitted	=   isset($arr3['data'][0][17]) ? $arr3['data'][0][17] : "";
$LackingPDC		=   isset($arr3['data'][0][18]) ? $arr3['data'][0][18] : "";
$TermsOfPayment	=   isset($arr3['data'][0][19]) ? $arr3['data'][0][19] : "";
$PDCStatus		=   isset($arr3['data'][0][20]) ? str_replace('_',' ',$arr3['data'][0][20]) : "";
$StartOfEquity	=   isset($arr3['data'][0][21]) ? $arr3['data'][0][21] : "";
$Project		=   isset($arr3['data'][0][22]) ? $arr3['data'][0][22] : "";
$Financing		=   isset($arr3['data'][0][23]) ? $arr3['data'][0][23] : "";
$PermitToOccupy =   isset($arr3['data'][0][24]) ? $arr3['data'][0][24] : "";

$ConstructionStatus 		=   isset($arr3['data'][0][29]) ? $arr3['data'][0][29] : ""; 
$PDCDownpayment =   isset($arr3['data'][0][27]) ? $arr3['data'][0][27] : "";
$PDCDiscount 	=   isset($arr3['data'][0][28]) ? $arr3['data'][0][28] : ""; 
$NetEquity 		=   isset($arr3['data'][0][10]) ? $arr3['data'][0][10] : ""; 
$PTOStatus 		=   isset($arr3['data'][0][30]) ? $arr3['data'][0][30] : ""; 
 

$objPHPExcel->getActiveSheet()->setCellValue('B2', $Project)->setCellValue('B3', $aval)->setCellValue('B4', $bval)
->setCellValue('B5', $cval)
->setCellValue('B6', $ConstructionStatus)
->setCellValue('E3', number_format($PDCDownpayment,2))
->setCellValue('E4', number_format($NetEquity,2))
->setCellValue('E5', number_format($PDCDiscount,2)) 
->setCellValue('H3', $MonthlyEquity)
->setCellValue('H4', $TermsOfPayment)
->setCellValue('H5', $StartOfEquity)
->setCellValue('H6', $Financing)
->setCellValue('K3', $PDCSubmitted)
->setCellValue('K4', $LackingPDC)
->setCellValue('K5', $PDCStatus)
->setCellValue('K6',$PTOStatus);

 
 $objPHPExcel->getActiveSheet()
        ->setCellValue(
            'E6',
            number_format(($PDCDownpayment + $NetEquity) - $PDCDiscount,2)
        );

if($PTOStatus=='Temporary PTO' || $PTOStatus=='TEMPORARY PTO'){   
		$styleArray2 = array('color' => array('rgb' => 'FF0000'),'font' => array('bold' => true)); 
		$objPHPExcel->getActiveSheet()->getStyle('K6')->applyFromArray($styleArray2); 
}


$baseRow = 10; 
$objPHPExcel->getActiveSheet()->setCellValue('A10', 'ITEM')
											  ->setCellValue('B10', 'DATE')
											  ->setCellValue('C10', 'BANK/BRANCH')
											  ->setCellValue('D10', 'CK#')
											  ->setCellValue('E10', 'AMOUNT')
											  ->setCellValue('F10', 'PAYMENT MODE')
											  ->setCellValue('G10', 'TRANSCODE')
											  ->setCellValue('H10', 'REMARKS')
											  ->setCellValue('I10', 'OR NUM')
											  ->setCellValue('J10', 'CK PROCESSED / VALIDATION DATE')
											  ->setCellValue('K10', 'STATUS')
											  ->setCellValue('L10', 'RETURNED CHECK REASONS')
											  ->setCellValue('M10', 'INTEREST/PENALTY');

$objPHPExcel->getActiveSheet()->getStyle('A10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);									  
$objPHPExcel->getActiveSheet()->getStyle('B10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);									  
$objPHPExcel->getActiveSheet()->getStyle('C10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('D10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('E10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('F10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('G10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('H10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('I10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('J10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('K10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('L10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('M10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))
	,'font' => array('bold' => true));
$objPHPExcel->getActiveSheet()->getStyle('A10:M10')->applyFromArray($styleArray);
unset($styleArray);   
						
 
 $tot = "";
 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][5])
											  ->setCellValue('C'.$row, $arr3['data'][$a][7])
											  ->setCellValue('D'.$row, $arr3['data'][$a][8])
											  ->setCellValue('E'.$row, $arr3['data'][$a][6])
											  ->setCellValue('F'.$row, $arr3['data'][$a][15])
											  ->setCellValue('G'.$row, $arr3['data'][$a][25])
											  ->setCellValue('H'.$row, $arr3['data'][$a][14])
											  ->setCellValue('I'.$row, $arr3['data'][$a][11])
											  ->setCellValue('J'.$row, $arr3['data'][$a][13])
											  ->setCellValue('K'.$row, $arr3['data'][$a][12])
											  ->setCellValue('L'.$row, $arr3['data'][$a][9]) 
											  ->setCellValue('M'.$row, $arr3['data'][$a][26]);
											  
	 	//if(strtoupper($arr3['data'][$a][12])=='RETURNED TO BUYER' || strtoupper($arr3['data'][$a][12])=='RETURNED BY BANK' || strtoupper($arr3['data'][$a][12])=='CLOSED' || strtoupper($arr3['data'][$a][12])=='RETURNED' || strtoupper($arr3['data'][$a][12])=='HOLD'){ //27JUL25 

if(strtoupper($arr3['data'][$a][12])!='CLEARED' && strtoupper($arr3['data'][$a][12])!='PAST DUE CLEARED'){
											 //$tot += str_replace(',','',$arr3['data'][$a][6]);
											  $tot += str_replace(',','',0);
							}else{
									  $tot += str_replace(',','',$arr3['data'][$a][6]);
							}							
											  
		$objPHPExcel->getActiveSheet()->getStyle('B'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');  
		$objPHPExcel->getActiveSheet()->getStyle('H5')->getNumberFormat()->setFormatCode('mm/dd/yyyy');  
		
$objPHPExcel->getActiveSheet()->getStyle('H3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('H4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('H5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
$objPHPExcel->getActiveSheet()->getStyle('A'.$row.':M'.$row)->applyFromArray($styleArray);
unset($styleArray); 
 }
 
 $finalRow = count($arr3['data']) + $baseRow + 1;
 $receiveRow 	= count($arr3['data']) + $baseRow + 10;
 //$tot 			= !empty($tot) ? number_format($tot,2) : $tot;
 $tot 			= !empty($tot) ? number_format($tot,2) : 0;
 $objPHPExcel->getActiveSheet()->setCellValue('A'.$receiveRow, 'RECEIVED BY')->setCellValue('D'.$finalRow, 'TOTAL')->setCellValue('E'.$finalRow,$tot);

 $objPHPExcel->getActiveSheet()->setCellValue('E7', $tot);
 
$styleArray = array('font' => array('bold' => true));
$objPHPExcel->getActiveSheet()->getStyle('D'.$finalRow.':'.'E'.$finalRow)->applyFromArray($styleArray);

$objPHPExcel->getActiveSheet()->getStyle('E3')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('E4')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('E5')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('E6')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('E7')->applyFromArray($styleArray);

$objPHPExcel->getActiveSheet()->getStyle('H3')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('H4')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('H5')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('H6')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('H7')->applyFromArray($styleArray);

$objPHPExcel->getActiveSheet()->getStyle('K3')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('K4')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('K5')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('K6')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('K7')->applyFromArray($styleArray);

$objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B3')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B4')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B5')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B6')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B7')->applyFromArray($styleArray);
						unset($styleArray);  
						 
									 
$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'); 