<?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();
$requestData	=	$_REQUEST; 
$columns = array(
	0	=>	'Id',
	1	=>	'Project',
	2	=>	'Model',
	3	=>	'PricelistDate',
	4	=>	'Phase',
	5	=>	'Block',
	6	=>	'Lot',
	7	=>	'ReservationStatus'
);  

$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')));
$tobedate 	= isset($_REQUEST['tobe_stat_date'])   ? $_REQUEST['tobe_stat_date']	  : "yes";
$mainQuery 		= "SELECT Id, Project, Model, UPPER(DATE_FORMAT(PricelistDate,'%m/%d/%Y')) as PricelistDate,UPPER(DATE_FORMAT(ReservationDate,'%m/%d/%Y')) as ReservationDate, Phase, Block, Lot, CONCAT(LastName,', ',FirstName,' ',MiddleName) as FullName, Broker, Agent, Spouse, HomeTel, CellphoneNo, SpouseContactNo, Address1, Address2, EmployerName, EmployeeType, GrossIncome, DiscountPromo, Position, BuyerProfile, SpouseIncome, TinNo, OfficeTel, AggregateIncome, NoCoBorrower, ReservationStatus, Status FROM  reservation"; 

$sql			=	$mainQuery."  WHERE Status = 'active' ";
if(!empty($requestData['search']['value']) ) { 
	$arr = explode(" ",$requestData['search']['value']);  
	foreach($arr as $ind){
		$ind = str_replace(',','',$ind);
		$sql.=" AND (Model LIKE '%".$ind."%' "; 
		$sql.=" OR Phase LIKE '%".$ind."%' ";
		$sql.=" OR Block LIKE '%".$ind."%' ";
		$sql.=" OR Lot LIKE '%".$ind."%' "; 
		$sql.=" OR UPPER(DATE_FORMAT(PricelistDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR ReservationStatus  LIKE '%".$ind."%' )";
	}
}

if($tobedate=="yes"){
	if( !empty($rangeStart) && !empty($rangeEnd) ){
		$sql .= " AND ReservationDate BETWEEN ('".date('Y-m-d', strtotime($rangeStart))."') AND ('".date('Y-m-d', strtotime($rangeEnd))."')";
	}else if( !empty($rangeStart)){
		$sql .= " AND ReservationDate >= ('".date('Y-m-d', strtotime($rangeStart))."') ";
	}else if( !empty($rangeEnd)){
		$sql .= " AND 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']." LIMIT ".$requestData['start']." ,".$requestData['length']."   "; 

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


 
$data = array(); 
$mecon->query($sql);
$result			= $mecon->resultset(); 
 
			 for($a=0; $a<count($result); $a++){
			 $nestedData=array(); 
				$nestedData[]	= $result[$a]['Id']; 
				$nestedData[]	= '<a href="index.php?pg=reservation&type=reservation&action=view&id='.$result[$a]['Id'].'" target="_blank">'.strtoupper($result[$a]['Project']).'</a>'; 
				$nestedData[]	= strtoupper($result[$a]['Model']);   
				$nestedData[] = (($result[$a]['PricelistDate']=='1970-01-01') || ($result[$a]['PricelistDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['PricelistDate'])); 
				$nestedData[]	= $result[$a]['Phase']; 
				$nestedData[]	= $result[$a]['Block']; 
				$nestedData[]	= $result[$a]['Lot'];   
				$nestedData[]	= strtoupper($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();