<?  error_reporting(0);
date_default_timezone_set('Asia/Singapore');  
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	=>	'ReservationDate',
	1	=>	'Block',
	2	=>	'Lot',
	3	=>	'FullName',
	4	=>	'TotalCollectible',
	5	=>	'TotalReceived' 
);   
 
$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 da.Id as Id, r.Block as Block, r.Lot as Lot, r.ReservationDate as ReservationDate, da.ReservationId as ReservationId, CONCAT(r.FirstName,' ',r.LastName) as Fullname, da.TotalCollectible as TotalCollectible, da.TotalReceived as TotalReceived 
FROM discrepancies_alert AS da 
LEFT JOIN reservation AS r ON r.Id=da.ReservationId "; 

$sql			=	$resultQuery."  WHERE r.Status = 'active' ";

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 UPPER(DATE_FORMAT(r.ReservationDate,'%m/%d/%Y')) LIKE '%".$ind."%' ) ";   
		$sql.=" OR CONCAT(r.FirstName,' ',r.LastName) LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR TotalReceived LIKE '%".str_replace(',','',trim($ind))."%' ";
		$sql.=" OR TotalCollectible LIKE '%".str_replace(',','',trim($ind))."%' ) ";   
	}
}
 
$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']."   ";  

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

		for($a=0; $a<count($result); $a++){
			$nestedData=array();
			//$nestedData[] = '<span style="text-align:center; margin:0 auto"><a href="index.php?pg=payment&type=postdated_checks_received&rid='.$result[$a]['ReservationId'].'"><span class="btn btn-xs btn-info glyphicon glyphicon-tag" title="Postdated Checks Received for Equity"></span></a></span>'; 
			$nestedData[] = (($result[$a]['ReservationDate']=='1970-01-01') || ($result[$a]['ReservationDate']=='')) ? "" : date('F d, Y', strtotime($result[$a]['ReservationDate'])); 
			$nestedData[] = $result[$a]['Block'];
			$nestedData[] = $result[$a]['Lot']; 
			$nestedData[] = $result[$a]['Fullname'];
			$nestedData[] = number_format($result[$a]['TotalCollectible'],2);
			$nestedData[] = number_format($result[$a]['TotalReceived'],2); 
			$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();