<?php
error_reporting(0);
if (substr_count($_SERVER['HTTP_ACCEPT_ENCODING'], 'gzip')) ob_start("ob_gzhandler"); else ob_start();
session_start();  
include_once('../config/minho.php');
include_once('../config/autoloadClass.php');
$autoload		=	new autoloadClass(); 
$mecon			=	new condbClass();
$formClass		=	new formClass();
$requestData	=	$_REQUEST; 
$columns = array(
	0	=>	'Id', 
	1	=>	'ReservationDate',
	2	=>	'Block',
	3	=>	'Lot',
	4	=>	'FullName', 
	5	=>	'Broker', 
	//6	=>	'HouseConstruction', 
	6	=>	'Id', 
	7	=>	'ReservationStatus', 
);   
$tobedate 	= isset($_REQUEST['tobe_stat_date'])   ? $_REQUEST['tobe_stat_date']	  : "yes";
$rangeStart = isset($_REQUEST['range_start']) ?  $_REQUEST['range_start'] : date('Y-m-d', strtotime(date('01-m-Y')));
$rangeEnd 	= isset($_REQUEST['range_end'])   ? $_REQUEST['range_end']	  : date("Y-m-t", strtotime(date('Y-m-d')));


 

$resultQuery 		= "SELECT r.ReservationStatus as ReservationStatus, r.Id as Id, r.Block as Block, r.Lot as Lot, r.Model as Model, r.Project as Project, r.ReservationDate as ReservationDate, r.Phase as Phase, r.PricelistDate as PricelistDate, CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName, r.Broker as Broker, ck.BuyerStandardReqDetails as BuyerStandardReqDetails, ck.AdditionalReqDetails as AdditionalReqDetails, r.EmployeeType as EmployeeType, r.EmployeeTypeBorrower1 as EmployeeTypeBorrower1, r.EmployeeTypeBorrower2 as EmployeeTypeBorrower2, r.EmployeeTypeBorrower3 as EmployeeTypeBorrower3, ck.CoBorrower1StandardReqDetails as CoBorrower1StandardReqDetails, ck.CoBorrower1AdditionalReqDetails as CoBorrower1AdditionalReqDetails, ck.CoBorrower2StandardReqDetails as CoBorrower2StandardReqDetails, ck.CoBorrower2AdditionalReqDetails as CoBorrower2AdditionalReqDetails, ck.CoBorrower3StandardReqDetails as CoBorrower3StandardReqDetails, ck.CoBorrower3AdditionalReqDetails as CoBorrower3AdditionalReqDetails, r.NoCoBorrower as NoCoBorrower, m.HouseConstruction as HouseConstruction, bsfp.TakeOut as TakeOut FROM reservation r LEFT JOIN checklist ck ON ck.ReservationId=r.Id LEFT JOIN monitoring_folders m ON m.ReservationId=r.Id LEFT JOIN buyer_status_folder_pagibig bsfp ON bsfp.ReservationId=r.Id ";  
 
$sql			=	$resultQuery."  WHERE (bsfp.TakeOut='1970-01-01' OR bsfp.TakeOut IS NULL) AND (r.ReservationStatus!='transferred' AND  r.ReservationStatus!='cancelled') AND r.Status='active' AND (ck.BuyerStandardReqDetails NOT LIKE '%postdated_checks%' OR ck.BuyerStandardReqDetails NOT LIKE '%twenty_four_checks%')";

//$sql = $resultQuery ." WHERE (bsfp.TakeOut='1970-01-01' OR bsfp.TakeOut IS NULL) AND (r.ReservationStatus!='transferred' AND  r.ReservationStatus!='cancelled') AND r.Status='active' AND ck.BuyerStandardReqDetails LIKE '%postdated_checks%' AND  ck.BuyerStandardReqDetails LIKE '%twenty_four_checks%'";
 

if(!empty($requestData['search']['value']) ) { 
	$arr = explode(" ",$requestData['search']['value']);  
	foreach($arr as $ind){
		$ind = str_replace(',','',$ind);		
		$sql.=" AND (r.Block LIKE '%".$ind."%' ";
		$sql.=" OR m.HouseConstruction LIKE '%".$ind."%' ";
		$sql.=" OR r.Lot LIKE '%".$ind."%' ";
		$sql.=" OR r.Project LIKE '%".$ind."%' ";
		$sql.=" OR r.Model LIKE '%".$ind."%' "; 
		//$sql.=" OR r.Broker LIKE '%".htmlentities($ind)."%' ";   
		//$sql.=" OR r.Broker LIKE '%".str_replace("'","\\\\''",$ind)."%' ";   
		$sql.=" OR r.Broker LIKE '%".addslashes($ind)."%' ";   
		$sql.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(bsfp.TakeOut,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(bsfp.TakeOut,'%d-%b-%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%d-%b-%Y')) LIKE '%".$ind."%' ) ";   
	}
}       

if($tobedate=="yes"){
		if( !empty($requestData['columns'][0]['search']['value']) && !empty($requestData['columns'][1]['search']['value']) ){
			$sql .= " AND r.ReservationDate 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 r.ReservationDate >= ('".date('Y-m-d', strtotime($requestData['columns'][0]['search']['value']))."') ";
		}else if( !empty($requestData['columns'][1]['search']['value'])){
			$sql .= " AND r.ReservationDate <= ('".date('Y-m-d', strtotime($requestData['columns'][1]['search']['value']))."') ";
		}


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

$mecon->query($sql);
$result			= $mecon->resultset(); 
$totalData		= count($result);
$totalFiltered	=	$totalData;

$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."  ,cast(Block as unsigned) asc, cast(Lot as unsigned) asc  LIMIT ".$requestData['start']." ,".$requestData['length']."   "; 
 

$mecon->query($sql);
$result			= $mecon->resultset(); 
  
$data = array();
 


$lack = "";
		for($a=0; $a<count($result); $a++){
			$nestedData	=	array(); 
			//main
			$buyerDetail		= str_replace('""','',$result[$a]['BuyerStandardReqDetails']);
			$decodeBuyerDetail	= json_decode($buyerDetail);  
			$buyerDetailCtr		= count($decodeBuyerDetail);
			
			 
				if($buyerDetailCtr==0){
					$lack = "";
				}else{
					if($buyerDetailCtr==0){
						$buyerDetail = '[]';
					} 
					 
					$empType = $result[$a]['EmployeeType'];
					$lacking = getMainLackDoc2($buyerDetail,$empType);
				}
			$noBorrower	= $result[$a]['NoCoBorrower'];	
			
			$buyerDetailCtr1 = "";
			$buyerDetailCtr2 = "";
			$buyerDetailCtr3 = "";
			if($noBorrower!=0){
				
				//borrower1
			$buyerDetail1		= $result[$a]['CoBorrower1StandardReqDetails'];
			$decodeBuyerDetail1	= json_decode($buyerDetail1);  
			$buyerDetailCtr1		= count($decodeBuyerDetail1);
			  
				if($buyerDetailCtr1==0){
					$lack = "";
				}else{
					if($buyerDetailCtr1==0){
						$buyerDetail1 = '[]';
					} 

					$empType1 = $result[$a]['EmployeeTypeBorrower1'];
					$lacking .= " [Co-Borrower1] :".getMainLackDoc2($buyerDetail1,$empType1);
				}
				
				//borrower2
			$buyerDetail2		= $result[$a]['CoBorrower2StandardReqDetails'];
			$decodeBuyerDetail2	= json_decode($buyerDetail2);  
			$buyerDetailCtr2		= count($decodeBuyerDetail2);
 			
				if($buyerDetailCtr2==0){
					$lack = "";
				}else{
					if($buyerDetailCtr2==0){
						$buyerDetail2 = '[]';
					} 
					
					
					$empType2 = $result[$a]['EmployeeTypeBorrower2'];
					$lacking .= " [Co-Borrower2] :".getMainLackDoc2($buyerDetail2,$empType2);
				}
				
				//borrower3
			$buyerDetail3		= $result[$a]['CoBorrower3StandardReqDetails'];
			$decodeBuyerDetail3	= json_decode($buyerDetail3);
			$buyerDetailCtr3	= count($decodeBuyerDetail3);
			 
				if($buyerDetailCtr3==0){
					$lack = "";
				}else{
					if($buyerDetailCtr3==0){
						$buyerDetail3 = '[]';
					} 
					
					
					$empType3 = $result[$a]['EmployeeTypeBorrower3'];
					$lacking .= " [Co-Borrower3] :".getMainLackDoc2($buyerDetail3,$empType3);
				}
				 
		 }
		 
		 //start save
				$nestedData[] = $result[$a]['Id'];
				$nestedData[] = (($result[$a]['ReservationDate']=='1970-01-01') || ($result[$a]['ReservationDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['ReservationDate']));
				$nestedData[] = html_entity_decode(stripslashes($result[$a]['Block']));
				$nestedData[] = html_entity_decode(stripslashes($result[$a]['Lot']));
				$nestedData[] = '<a href="index.php?pg=payment&type=postdated_checks_status&rid='.$result[$a]['Id'].'">'.strtoupper(html_entity_decode(stripslashes($result[$a]['FullName']))).'</a>';
				//$nestedData[] = html_entity_decode(stripslashes($result[$a]['Broker']));
				$nestedData[] = str_replace("\'","'",$result[$a]['Broker']);
				//$nestedData[] = html_entity_decode(stripslashes($result[$a]['PricelistStatus'])); 
				$pricedate	= html_entity_decode(stripslashes($result[$a]['PricelistDate'])); 
				$phase		= html_entity_decode(stripslashes($result[$a]['Phase'])); 
				$block		= html_entity_decode(stripslashes($result[$a]['Block'])); 
				$lot		= html_entity_decode(stripslashes($result[$a]['Lot'])); 
				$project	= html_entity_decode(stripslashes($result[$a]['Project'])); 
				$models		= html_entity_decode(stripslashes($result[$a]['Model'])); 
				
				
				$other = $formClass->getOtherDetails($pricedate,$models,$project,$phase,$block,$lot);		
				//$nestedData[] = $other['pricelist_status'];
				//$nestedData[] = (!empty($result[$a]['HouseConstruction']) || $result[$a]['HouseConstruction']=NULL) ? $result[$a]['HouseConstruction'] : "";
				//$nestedData[] = (!empty($result[$a]['TakeOut']) || $result[$a]['TakeOut']=NULL) ? $result[$a]['TakeOut'] : "";
				//$nestedData[] = (!empty($result[$a]['HouseConstruction']) || $result[$a]['HouseConstruction']=NULL) ? $result[$a]['HouseConstruction'] : $other['pricelist_status'];
				
				 
				
				$nestedData[] = ($buyerDetailCtr==0 && $buyerDetailCtr1==0 && $buyerDetailCtr2==0 && $buyerDetailCtr3==0) ? "New buyer - to submit all Documentary Requirements" : $lacking;
				$nestedData[] = strtoupper(html_entity_decode(stripslashes($result[$a]['ReservationStatus'])));  
			$data[] = $nestedData;
		} 
$json_data = array(
			"draw"            => intval( $requestData['draw'] ),
			"recordsTotal"    => intval( $totalData ),
			"recordsFiltered" => intval( $totalFiltered ),
			"data"            => $data
			);
			 
echo json_encode($json_data);



//MAIN DETAILS
function getMainLackDoc2($mainDet,$empType){  
	$lack		= "";
	$lack2		= "";
	$lacks		= "";
	$submitArr  = array();
	$submitArr	= json_decode($mainDet);    
	
$diffArr  = array();
$completeMainArr  = array();
	//Complete MAIN
	$completeMainArr = array("postdated_checks","twenty_four_checks","tinno");
	$diffArr = array_diff($completeMainArr,$submitArr); 
	  
	foreach($diffArr as $key => $val){
		if($val=="twenty_four_checks"){
			$lack .= " Twenty four 24 Blank Checks for HDMF Amortization  ";
		}else if($val=="postdated_checks"){
			$lack .= " Postdated Checks for Miscellaneous Fee *, ";
		} 
	}
	  
	$lacks = $lack . $lack2;
	  
	$output	=	 trim(wordwrap($lacks,180,"<br>\n"), ","); 
	return $output;
} 

 
exit();
ob_end_flush();
