<?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['castalia_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']) : "";
	$arr1[15]	= !empty($main[$a]['ModePayment']) ? strtoupper($main[$a]['ModePayment']) : "";
	
	$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', 'PROCESS 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($arr3['data'][$a][12]=='RETURNED'){
											 //$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;
 $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'); 