<?  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, 

IF(rb.Balancecheckbox='on' AND rb.FullCheckbox='on', 'Full', IF(rb.Balancecheckbox='on' AND rb.FullCheckbox='', 'Partial', IF(rb.FullCheckbox='on', 'Full', ''))) as ReleaseType,  

IF(rb.Balancecheckbox='on' AND rb.FullCheckbox='on', rb.TotalAmount, IF(rb.Balancecheckbox='on' AND rb.FullCheckbox='', rb.PartialAmount, IF(rb.FullCheckbox='on', rb.TotalAmount, ''))) as ReleaseAmount,  

IF(rb.Balancecheckbox='on' AND rb.FullCheckbox='on', rb.TotalWHT, IF(rb.Balancecheckbox='on' AND rb.FullCheckbox='', rb.PartialWHT, IF(rb.FullCheckbox='on', rb.TotalWHT, ''))) as ReleaseWHT,  


IF(rb.Balancecheckbox='on' AND rb.FullCheckbox='on', DATE_FORMAT(rb.TotalDate, '%m/%d/%Y'), IF(rb.Balancecheckbox='on' AND rb.FullCheckbox='', DATE_FORMAT(rb.PartialDate, '%m/%d/%Y'), IF(rb.FullCheckbox='on', DATE_FORMAT(rb.TotalDate, '%m/%d/%Y'), ''))) as ReleaseDate, 

rb.ReleasesStatus as ReleasesStatus FROM releases_to_broker_for_wht rb  LEFT JOIN reservation r ON rb.ReservationId=r.Id WHERE 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']." , cast(r.Block as unsigned) asc, cast(r.Lot as unsigned) asc , Id, ReleaseType 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[] = $result[$a]['Id'];
			$nestedData[] = $result[$a]['Fullname'];
			$nestedData[] = $result[$a]['Block'];
			$nestedData[] = $result[$a]['Lot'];
			$nestedData[] = $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]['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();