<?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();
$utility	=	new utilityClass();
$actArr 	= 	array();
$main		=	array();
$main2		=	array();
$main3		=	array();
$main4		=	array();
$rId 		= 	(isset($_GET['id'])) ? strip_tags(trim($_GET['id'])) : "";
$cmid 		= 	(isset($_GET['cmid'])) ? strip_tags(trim($_GET['cmid'])) : "";


$mainCheckSource 	= ""; //to track the past due history of this check number  
$mainCheckSource 	= $utility->getFieldId("ChequeNo","Id",$cmid,"buyer_cheque_monitoring");

//$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.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.PenaltyStatus as PenaltyStatus, bcm.Transcode as Transcode FROM buyer_cheque_monitoring_past_due 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' AND bcm.ChequeMonitorId='$cmid' ";

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.ChequeNo LIKE '%".$ind."%' "; 
		$sql2.=" OR bcm.ChequeStatus LIKE '%".$ind."%' "; 
		$sql2.=" OR bcm.Bank LIKE '%".$ind."%' "; 
		$sql2.=" OR bcm.PenaltyStatus LIKE '%".$ind."%' "; 
		$sql2.=" OR bcm.ReturnRemarks LIKE '%".$ind."%' "; 
		$sql2.=" OR bcm.Transcode LIKE '%".$ind."%' "; 
		$sql2.=" OR bcm.ModePayment LIKE '%".$ind."%' "; 
		$sql2.=" OR bcm.PenaltyStatus 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++){  
	$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'] : "";
	$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']) ? str_replace('_',' ',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(html_entity_decode($main[$a]['Transcode'])) 	 : ""; 
	$arr1[26]	= isset($main[$a]['PenaltyStatus']) ? strtoupper($main[$a]['PenaltyStatus']) : ""; 
	
	$arr2[] 	= $arr1;
}

$arr3['data'] = $arr2;
 
 
$fileName    = "postdated_checks_received_past_due.xls";
$objPHPExcel = $objReader->load("templates/postdated_checks_past_due.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]) ? $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] : "";

//$objPHPExcel->getActiveSheet()->setCellValue('B2', $Project)->setCellValue('B3', $aval)->setCellValue('B4', $bval)->setCellValue('B5', $cval)->setCellValue('E3', $mval)->setCellValue('H3', $MonthlyEquity)->setCellValue('H4', $TermsOfPayment)->setCellValue('H5', $StartOfEquity)->setCellValue('K3', $PDCSubmitted)->setCellValue('K4', $LackingPDC)->setCellValue('K5', $PDCStatus)->setCellValue('E5', $Financing)->setCellValue('K6',$PermitToOccupy);

$baseRow = 3; 



$objPHPExcel->getActiveSheet()->setCellValue('A3', 'ITEM')
->setCellValue('B3', 'DATE')
->setCellValue('C3', 'BANK/BRANCH')
->setCellValue('D3', 'CK#')
->setCellValue('E3', 'AMOUNT')
->setCellValue('F3', 'MODE')
->setCellValue('G3', 'TRANSCODE')
->setCellValue('H3', 'REMARKS')
->setCellValue('I3', 'OR #')
->setCellValue('J3', 'PROCESS DATE')
->setCellValue('K3', 'STATUS')
->setCellValue('L3', 'RETURNED CHECK REASONS')
->setCellValue('M3', 'INTEREST/PENALTY');
  
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
$objPHPExcel->getActiveSheet()->getStyle('C3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('D3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('E3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('F3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('G3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('H3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('I3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('J3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('K3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('L3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('M3')->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('A3:M3')->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('J'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');  
		
$objPHPExcel->getActiveSheet()->getStyle('B'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('J'.$row)->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 + 8;
 $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('E4', $tot);
 
$styleArray = array('font' => array('bold' => true));
$objPHPExcel->getActiveSheet()->getStyle('D'.$finalRow.':'.'E'.$finalRow)->applyFromArray($styleArray);
//$objPHPExcel->getActiveSheet()->getStyle('E4')->applyFromArray($styleArray);
//$objPHPExcel->getActiveSheet()->getStyle('H4')->applyFromArray($styleArray);
//$objPHPExcel->getActiveSheet()->getStyle('H3')->applyFromArray($styleArray);
//$objPHPExcel->getActiveSheet()->getStyle('K3')->applyFromArray($styleArray);
//$objPHPExcel->getActiveSheet()->getStyle('H5')->applyFromArray($styleArray);
//$objPHPExcel->getActiveSheet()->getStyle('K5')->applyFromArray($styleArray);
//$objPHPExcel->getActiveSheet()->getStyle('K6')->applyFromArray($styleArray);
//$objPHPExcel->getActiveSheet()->getStyle('E5')->applyFromArray($styleArray);
//$objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($styleArray);
						unset($styleArray);  
						 

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'PAST DUE HISTORY (CK #'.$mainCheckSource.')');

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