<?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; 
$requestData= $_REQUEST; 
$columns = array(
	0	=>	'Id',
	1	=>	'ReservationDate', 
	2	=>	'Block', 
	3	=>	'Lot', 
	4	=>	'FullName',
	5	=>	'Model', 
	6	=>	'ConstructionStatusUponReservation', 
	7	=>	'PFNet', 
	8	=>	'Discount', 
	9	=>	'TermsOfPayment', 
	10	=>	'Broker', 
	11	=>	'Agent',
	12	=>	'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"; 
$resultQuery 		= "SELECT r.Id as Id, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as Fullname, r.ReservationDate as ReservationDate, r.Model as Model,r.Broker as Broker, r.Agent as Agent, bpm.Discount as Discount, r.Project as Project, r.Phase as Phase, r.PricelistDate as PricelistDate, bpm.NetEquity as PFNet, bpm.TermsOfPayment as TermsOfPayment, bpm.PaymentMode as PaymentMode, r.ReservationStatus as ReservationStatus, r.EmployeeType, r.ConstructionStatusUponReservation as ConstructionStatusUponReservation FROM reservation r LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id  "; 
 
$sql			=	$resultQuery." WHERE r.Status='active' AND r.ReservationStatus='transferred' ";

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 CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' "; 
		$sql.=" OR r.Lot LIKE '%".$ind."%' "; 
		$sql.=" OR r.Model LIKE '%".$ind."%' "; 
		$sql.=" OR r.ConstructionStatusUponReservation LIKE '%".$ind."%' "; 
		$sql.=" OR r.EmployeeType LIKE '%".$ind."%' "; 
		$sql.=" OR r.ReservationStatus LIKE '%".$ind."%' "; 
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%d-%b-%Y')) LIKE '%".$ind."%' ";  
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR r.Broker LIKE '%".htmlentities($ind)."%' "; 
		$sql.=" OR r.Agent LIKE '%".htmlentities($ind)."%' "; 
		$sql.=" OR bpm.Discount LIKE '%".$ind."%' "; 		 
		$sql.=" OR bpm.NetEquity LIKE '%".str_replace(",","",$ind)."%' "; 
		$sql.=" OR bpm.TermsOfPayment LIKE '%".$ind."%' ";
		$sql.=" OR bpm.PaymentMode 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']."   "; 
  
$data = array(); 
$mecon->query($sql);
$result			= $mecon->resultset(); 

			 for($a=0; $a<count($result); $a++){
				$nestedData=array(); 
				$nestedData[] = $result[$a]['Id'];
				$nestedData[] = (($result[$a]['ReservationDate']=='1970-01-01') || ($result[$a]['ReservationDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['ReservationDate'])); 
				$nestedData[] = trim(html_entity_decode(stripslashes($result[$a]['Block'])));
				$nestedData[] = trim(html_entity_decode(stripslashes($result[$a]['Lot'])));
				 
				$fullName 		= strtoupper(trim(html_entity_decode(stripslashes($result[$a]['Fullname'])))); 
				$nestedData[] 	= wordwrap($fullName,20,"<br>\n");

				$nestedData[] = trim(html_entity_decode(stripslashes($result[$a]['Model'])));
				//$nestedData[] = $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[] = trim(html_entity_decode(stripslashes($result[$a]['ConstructionStatusUponReservation'])));
				$nestedData[] = number_format($result[$a]['PFNet'],2);
				$nestedData[] = number_format($result[$a]['Discount'],2); 
				$nestedData[] = ($result[$a]['PaymentMode']=='cash') ? "cash" : $result[$a]['TermsOfPayment'];
			 

				$brokerName 	= trim(html_entity_decode(stripslashes($result[$a]['Broker'])));
				$nestedData[] 	= wordwrap($brokerName,20,"<br>\n");

				$agentName 		= trim(html_entity_decode(stripslashes($result[$a]['Agent'])));
				$nestedData[] 	= wordwrap($agentName,20,"<br>\n");

				$nestedData[] = strtoupper(trim(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();