<?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	=>	'FullName',
	2	=>	'Block',
	3	=>	'Lot',
	4	=>	'Broker',
	5	=>	'ReleaseType',
	6	=>	'ReleaseAmount',
	7	=>	'ReleaseWHT',
	8	=>	'ReleaseDate',
	9	=>	'ReleasesStatus',
	10  => '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";



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


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

$resultQuery 		= "(SELECT r.Id as Id, r.Block as Block, r.Lot as Lot, r.Broker as Broker, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname, r.ReservationDate, r.ReservationStatus, r.Project, r.Model,
rb.FirstReleaseAmount as ReleaseAmount, rb.FirstReleaseType as ReleaseType, rb.FirstReleaseWHT as ReleaseWHT, rb.FirstReleaseDate as ReleaseDate, rb.ReleasesStatus as ReleasesStatus FROM reservation r LEFT JOIN releases_to_broker rb ON rb.ReservationId=r.Id WHERE rb.FirstReleaseCheckbox='on' AND r.Status='active' $whereCond)
UNION
(SELECT r.Id as Id, r.Block as Block, r.Lot as Lot, r.Broker as Broker,  CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname, r.ReservationDate, r.ReservationStatus, r.Project, r.Model, rb.SecondReleaseAmount as ReleaseAmount, rb.SecondReleaseType as ReleaseType, rb.SecondReleaseWHT as ReleaseWHT, rb.SecondReleaseDate as ReleaseDate, rb.ReleasesStatus as ReleasesStatus FROM reservation r LEFT JOIN releases_to_broker rb ON rb.ReservationId=r.Id WHERE rb.SecondReleaseCheckbox='on' AND r.Status='active' $whereCond)
UNION
(SELECT r.Id as Id, r.Block as Block, r.Lot as Lot, r.Broker as Broker,  CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname, r.ReservationDate, r.ReservationStatus, r.Project, r.Model, rb.ThirdReleaseAmount as ReleaseAmount, rb.ThirdReleaseType as ReleaseType, rb.ThirdReleaseWHT as ReleaseWHT, rb.ThirdReleaseDate as ReleaseDate, rb.ReleasesStatus as ReleasesStatus FROM reservation r LEFT JOIN releases_to_broker rb ON rb.ReservationId=r.Id WHERE rb.ThirdReleaseCheckbox='on' AND r.Status='active' $whereCond)
"; 

$sql			=	$resultQuery ;

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

//$sql .= " ORDER BY Broker, Id, ReleaseType LIMIT ".$requestData['start']." ,".$requestData['length'] ;

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

		for($a=0; $a<count($result); $a++){
			$nestedData=array(); 
			$nestedData[] = $a+1;
			$nestedData[] = ($result[$a]['ReleaseType']=='1st') ? $result[$a]['Fullname'] : ""; 
			$nestedData[] = ($result[$a]['ReleaseType']=='1st') ? $result[$a]['Block'] : "";
			$nestedData[] = ($result[$a]['ReleaseType']=='1st') ? $result[$a]['Lot'] : "";
			$nestedData[] = ($result[$a]['ReleaseType']=='1st') ? $result[$a]['Broker'] : ""; 
			$nestedData[] = $result[$a]['ReleaseType'];
			$nestedData[] = number_format($result[$a]['ReleaseAmount'],2);
			$nestedData[] = number_format($result[$a]['ReleaseWHT'],2);
			$nestedData[] = $result[$a]['ReleaseDate'];
			$nestedData[] = ($result[$a]['ReleaseType']=='1st') ? $result[$a]['ReleasesStatus'] : ""; 
			 
			$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();