<?  
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	=>	'Fullname', 
	1	=>	'Block',
	2	=>	'Lot',
	3	=>	'ReservationDate',
	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 r.Id as Id, r.Block as Block, r.Lot as Lot, r.Model as Model, r.Project as Project, r.ReservationDate as ReservationDate, CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as Fullname, da.TotalCollectible as TotalCollectible, da.TotalReceived as TotalReceived  FROM discrepancies_alert da LEFT JOIN reservation r ON da.ReservationId=r.Id ";  
$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 r.Project LIKE '%".$ind."%' ";
		$sql.=" OR r.Model LIKE '%".$ind."%' ";  
		$sql.=" OR da.TotalCollectible LIKE '%".$ind."%' ";  
		$sql.=" OR da.TotalReceived LIKE '%".$ind."%' ";   
		$sql.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' "; 
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%d-%b-%Y')) LIKE '%".$ind."%' ) ";   
	}
}       
/*
if($tobedate=="yes"){
		if( !empty($requestData['columns'][0]['search']['value']) && !empty($requestData['columns'][1]['search']['value']) ){
			$sql .= " AND bcm.ChequeDate 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 bcm.ChequeDate >= ('".date('Y-m-d', strtotime($requestData['columns'][0]['search']['value']))."') ";
		}else if( !empty($requestData['columns'][1]['search']['value'])){
			$sql .= " AND bcm.ChequeDate <= ('".date('Y-m-d', strtotime($requestData['columns'][1]['search']['value']))."') ";
		}
  
		if( !empty($rangeStart) && !empty($rangeEnd) ){
			$sql .= " AND bcm.ChequeDate BETWEEN ('".date('Y-m-d', strtotime($rangeStart))."') AND ('".date('Y-m-d', strtotime($rangeEnd))."')";
		}else if( !empty($rangeStart)){
			$sql .= " AND bcm.ChequeDate >= ('".date('Y-m-d', strtotime($rangeStart))."') ";
		}else if( !empty($rangeEnd)){
			$sql .= " AND bcm.ChequeDate <= ('".date('Y-m-d', strtotime($rangeEnd))."') ";
		}
}*/


$mecon->query($sql);
$result			= $mecon->resultset(); 
$totalData		= count($result);
$totalFiltered	=	$totalData;

//$sql.=" ORDER BY bcm.ChequeDate DESC, ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']." ,cast(r.Block as unsigned) asc, cast(r.Lot as unsigned) asc 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']."   "; 


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

$data = array();
 

$lack = "";
		for($a=0; $a<count($result); $a++){
				$nestedData	=	array();
				//$nestedData[] = $a+1;
				$nestedData[] = "<a href='index.php?pg=payment&type=postdated_checks_received&rid=".$result[$a]['Id']."'>".strtoupper(html_entity_decode(stripslashes($result[$a]['Fullname'])))."</a>";
				$nestedData[] = html_entity_decode(stripslashes($result[$a]['Block']));
				$nestedData[] = html_entity_decode(stripslashes($result[$a]['Lot'])); 
				$nestedData[] = (($result[$a]['ReservationDate']=='1970-01-01') || ($result[$a]['ReservationDate']=='')) ? "" : date('F d, Y', strtotime($result[$a]['ReservationDate']));
				$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();
