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



$rangeStart = isset($_REQUEST['month_from']) ?  $_REQUEST['month_from'] : date('Y-m-d', strtotime(date('01-m-Y')));
$rangeEnd 	= isset($_REQUEST['month_to'])   ? $_REQUEST['month_to']	  : date("Y-m-t", strtotime(date('Y-m-d')));
 
$tobedate 	= isset($_REQUEST['check_tobedelivered_date'])   ? $_REQUEST['check_tobedelivered_date'] : 'false';
$notselectedchecks 	= isset($_REQUEST['notselectedchecks'])   ? $_REQUEST['notselectedchecks'] : '';

//$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.PDCSubmitted 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 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'  ";

$mainQuery 		= "SELECT bcm.Id as Id, bcm.ChequeDate as ChequeDate, bcm.ChequeNo as ChequeNo, UPPER(bcm.Bank) as Bank, bcm.Amount as Amount, bcm.Remarks as Remarks, bcm.ChequeStatus as ChequeStatus,bcm.ChequeProcessDate as ChequeProcessDate, UPPER(r.Project) as Project, UPPER(r.Model) as Model, r.Phase as Phase, r.Block as Block, r.Lot as Lot, UPPER(CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName)) as FullName, bcm.ReservationId as ReservationId, bcm.ReturnRemarks as ReturnRemarks, r.Id as rid FROM buyer_cheque_monitoring bcm LEFT JOIN reservation r ON r.Id=bcm.ReservationId "; 
  
$sql			=	$mainQuery."  WHERE (bcm.Bank)!='' AND bcm.Status = 'active'"; 


if($notselectedchecks!=""){
	$sql .= " AND bcm.Id NOT IN ($notselectedchecks)";
}

$sql .= "  AND (bcm.ChequeStatus IS NULL OR (bcm.ChequeStatus!='Cleared' AND bcm.ChequeStatus!='Replacement' AND bcm.ChequeStatus!='Returned' AND bcm.ChequeStatus!='Due Date' AND bcm.ChequeStatus!='Hold' AND bcm.ChequeStatus!='No Payment' AND bcm.ChequeStatus!='No Check') OR bcm.ChequeStatus='Replaced') ";
 
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.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."%' )";   
	}*/

		$sql2 ="  AND ( r.Block LIKE '%".$filterSearch."%' ";
		$sql2.=" OR r.Lot LIKE '%".$filterSearch."%' ";
		$sql2.=" OR r.Model LIKE '%".$filterSearch."%' ";
		$sql2.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($filterSearch)."%' ";  
		$sql2.=" OR bcm.ChequeNo LIKE '%".$filterSearch."%' "; 
		$sql2.=" OR bcm.ChequeStatus LIKE '%".$filterSearch."%' "; 
		$sql2.=" OR bcm.Bank LIKE '%".$filterSearch."%' "; 
		$sql2.=" OR bcm.ReturnRemarks LIKE '%".$filterSearch."%' "; 
		$sql2.=" OR bcm.Amount LIKE '%".$filterSearch."%' "; 
		$sql2.=" OR bcm.Amount LIKE '%".str_replace(',','',$filterSearch)."%' "; 
		$sql2.=" OR bcm.Remarks LIKE '%".$filterSearch."%' "; 
		$sql2.=" OR UPPER(DATE_FORMAT(bcm.ChequeDate,'%d-%b-%Y')) LIKE '%".$filterSearch."%' ";   
		$sql2.=" OR UPPER(DATE_FORMAT(bcm.ChequeProcessDate,'%d-%b-%Y')) LIKE '%".$filterSearch."%' )";   


	$sql .= $sql2;
}
 
if($tobedate!=='false'){
	if( !empty($requestData['columns'][0]['search']['value']) && !empty($requestData['columns'][1]['search']['value']) ){
		$sql .= " AND bcm.ChequeDate BETWEEN ('".date('Y-m-d', strtotime($requestData['columns'][0]['search']['value']))."') AND ('".date('Y-m-d', strtotime($requestData['columns'][1]['search']['value']))."')";
	}else if( !empty($requestData['columns'][0]['search']['value'])){
		$sql .= " AND bcm.ChequeDate >= ('".date('Y-m-d', strtotime($requestData['columns'][0]['search']['value']))."') ";
	}else if( !empty($requestData['columns'][1]['search']['value'])){
		$sql .= " AND bcm.ChequeDate <= ('".date('Y-m-d', strtotime($requestData['columns'][1]['search']['value']))."') ";
	}


	if( !empty($rangeStart) && !empty($rangeEnd) ){
		$sql .= " AND bcm.ChequeDate BETWEEN ('".date('Y-m-d', strtotime($rangeStart))."') AND ('".date('Y-m-d', strtotime($rangeEnd))."')";
	}else if( !empty($rangeStart)){
		$sql .= " AND bcm.ChequeDate >= ('".date('Y-m-d', strtotime($rangeStart))."') ";
	}else if( !empty($rangeEnd)){
		$sql .= " AND bcm.ChequeDate <= ('".date('Y-m-d', strtotime($rangeEnd))."') ";
	}
}

$sql .= ' ORDER BY bcm.ChequeDate, 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]	= (!empty($main[$a]['ChequeDate']) && ($main[$a]['ChequeDate']!='1970-01-01')) ? $main[$a]['ChequeDate'] : "";
	$arr1[2]	= $main[$a]['ChequeNo']; 
	$arr1[3]	= $main[$a]['Bank']; 
	$arr1[4]	= (!empty($main[$a]['Amount']) || $main[$a]['Amount']!=NULL || $main[$a]['Amount']!="") ? number_format($main[$a]['Amount'],2) : $main[$a]['Amount']; 
	$arr1[5]	= $main[$a]['Remarks']; 
	$arr1[6]	= strtoupper($main[$a]['ChequeStatus']);
	$arr1[7]	= $main[$a]['ReturnRemarks'];  
	$arr1[8] = (($main[$a]['ChequeProcessDate']=='1970-01-01') || ($main[$a]['ChequeProcessDate']=='')) ? "" : date('m/d/Y', strtotime($main[$a]['ChequeProcessDate']));  
	$arr1[9]	= strtoupper($main[$a]['Model']); 
	$arr1[10]	= $main[$a]['Phase']; 
	$arr1[11]	= $main[$a]['Block']; 
	$arr1[12]	= $main[$a]['Lot']; 
	$arr1[13]	= strtoupper($main[$a]['FullName']);
	 
	$arr2[] 	= $arr1;
}

$arr3['data'] = $arr2;
 
 
$fileName    = "cheque_for_clearing.xls";
$objPHPExcel = $objReader->load("templates/cheque_for_clearing.xls"); 
  
$baseRow = 1; 
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'CHEQUE DATE')
											  ->setCellValue('B1', 'CHEQUE NO')
											  ->setCellValue('C1', 'BANK')
											  ->setCellValue('D1', 'AMOUNT')
											  ->setCellValue('E1', 'REMARKS')
											  ->setCellValue('F1', 'CHEQUE STATUS')
											  ->setCellValue('G1', 'RETURN REMARKS')
											  ->setCellValue('H1', 'CHEQUE PROCESS DATE')
											  ->setCellValue('I1', 'MODEL')
											  ->setCellValue('J1', 'PHASE')
											  ->setCellValue('K1', 'BLOCK')
											  ->setCellValue('L1', 'LOT')
											  ->setCellValue('M1', 'FULL NAME');
 
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);				  
$objPHPExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('E1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('G1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('H1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('I1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('J1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('K1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('L1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('M1')->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('A1:M1')->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][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])
											  ->setCellValue('M'.$row, $arr3['data'][$a][13]);
											    
		$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');  
		$objPHPExcel->getActiveSheet()->getStyle('H'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');  
		
$objPHPExcel->getActiveSheet()->getStyle('B'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle('D'.$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); 
 }
   						 
									 
//$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'); 