<?  
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	=>	'ToBeDeliveredDate', 
	1	=>	'Block',
	2	=>	'Lot', 
	3	=>	'Id', 
	4	=>	'Id', 
	5	=>	'FullName',
	6	=>	'LoanableAmount', 
	7	=>	'ScheduleLoan', 
	8	=>	'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, bpm.LoanableAmount as LoanableAmount, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname, r.PricelistDate as PricelistDate, r.Phase as Phase, r.Project as Project, r.Model as Model, r.ReservationDate as ReservationDate, r.Project, r.Model, r.ScheduleLoan as ScheduleLoan, r.ToBeDeliveredDate as ToBeDeliveredDate FROM reservation r LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id  "; 
 
$sql			=	$resultQuery."  WHERE r.Status = 'active' AND bpm.Financing='pagibig' AND (r.ScheduleLoan='to_be_delivered' OR r.ScheduleLoan='delivered') ";
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 r.Lot LIKE '%".$ind."%' ";
		$sql.=" OR r.Project LIKE '%".$ind."%' ";
		//$sql.=" OR r.ScheduleLoan LIKE '%".$ind."%' ";
		$sql.=" OR r.Model LIKE '%".$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 r.ScheduleLoan = '".strtolower(str_replace(" ","_",$requestData['search']['value']))."' ";
		$sql.=" OR UPPER(DATE_FORMAT(r.ToBeDeliveredDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(r.ToBeDeliveredDate,'%d-%b-%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%d-%b-%Y')) LIKE '%".$ind."%' ) ";   
	}
	//$sql.=" OR r.ScheduleLoan = '".strtolower(str_replace(" ","_",$requestData['search']['value']))."' ";
}

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

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

$mecon->query($sql);
$result			= $mecon->resultset(); 
$totalData		= count($result);
$totalFiltered	=	$totalData;
  
  
$sql.=" ORDER BY cast(". $columns[$requestData['order'][0]['column']]." as unsigned)   ".$requestData['order'][0]['dir']." , cast(Block as unsigned) asc, cast(Lot as unsigned) asc LIMIT ".$requestData['start']." ,".$requestData['length']."   "; 

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

		for($a=0; $a<count($result); $a++){
			$nestedData=array();  
			$nestedData[] = (($result[$a]['ToBeDeliveredDate']=='1970-01-01') || ($result[$a]['ToBeDeliveredDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['ToBeDeliveredDate'])); 
			$nestedData[] = html_entity_decode(stripslashes($result[$a]['Block']));
			$nestedData[] = html_entity_decode(stripslashes($result[$a]['Lot'])); 
			//$nestedData[] = $result[$a]['LotSize'];
			$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['lot_size'];
			$nestedData[] = $other['floor_area'];
 
			$fullName 		= strtoupper(trim(html_entity_decode(stripslashes($result[$a]['Fullname'])))); 
			$nestedData[] 	= wordwrap($fullName,30,"<br>\n");


			$nestedData[] = number_format($result[$a]['LoanableAmount'],2);    
			 
			$nestedData[] = str_replace("_"," ",strtoupper(html_entity_decode(stripslashes($result[$a]['ScheduleLoan'])))); 
			$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);
exit();
ob_end_flush();
?>