<?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	=>	'ReservationDate',
	2	=>	'Block',
	3	=>	'Lot',
	4	=>	'FullName', 
	5	=>	'Broker', 
	6	=>	'ChequeDate', 
	7	=>	'Amount', 
	8	=>	'ReturnRemarks', 
	9	=>	'ChequeProcessDate', 
	10	=>	'Remarks' 
);   

$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, r.Broker as Broker, bcm.ChequeDate as ChequeDate, bcm.ChequeNo as ChequeNo, bcm.Bank as Bank, bcm.Amount as Amount, 
bcm.Remarks as Remarks, bcm.ChequeStatus as ChequeStatus, bcm.ReturnRemarks as ReturnRemarks, bcm.ChequeProcessDate as ChequeProcessDate FROM reservation r LEFT JOIN buyer_cheque_monitoring bcm ON bcm.ReservationId=r.Id "; 
  
//$sql			=	$resultQuery."  WHERE  r.Status='active' AND bcm.ReturnRemarks IS NOT NULL AND (bcm.ChequeStatus!='Hold' OR bcm.ChequeStatus!='No Check' OR bcm.ChequeStatus!='Returned') ";
$sql			=	$resultQuery."  WHERE  r.Status='active' AND bcm.ReturnRemarks IS NOT NULL AND (bcm.ChequeStatus!='Hold' OR bcm.ChequeStatus!='No Check' OR bcm.ChequeStatus='' OR bcm.ChequeStatus!='Returned') ";
 

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 r.Broker LIKE '%".htmlentities($ind)."%' ";  
		$sql.=" OR bcm.Amount LIKE '%".$ind."%' ";  
		$sql.=" OR bcm.Remarks LIKE '%".$ind."%' ";  
		$sql.=" OR bcm.ReturnRemarks LIKE '%".$ind."%' ";  
		$sql.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(bcm.ChequeProcessDate,'%d-%b-%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(bcm.ChequeProcessDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$cdate = date('m/Y', strtotime($ind));
		$sql.=" OR UPPER(DATE_FORMAT(bcm.ChequeDate,'%m/%Y')) LIKE '%".$cdate."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(bcm.ChequeDate,'%d-%b-%Y')) LIKE '%".$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 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']."   "; 

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

$data = array();
 

$lack = "";
		for($a=0; $a<count($result); $a++){
				$nestedData	=	array();
				$nestedData[] = $a+1;
				$nestedData[] = (($result[$a]['ReservationDate']=='1970-01-01') || ($result[$a]['ReservationDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['ReservationDate']));
				$nestedData[] = html_entity_decode(stripslashes($result[$a]['Block']));
				$nestedData[] = html_entity_decode(stripslashes($result[$a]['Lot']));
				$nestedData[] = strtoupper(html_entity_decode(stripslashes($result[$a]['Fullname'])));
				$nestedData[] = html_entity_decode(stripslashes($result[$a]['Broker']));
				$nestedData[] = (($result[$a]['ChequeDate']=='1970-01-01') || ($result[$a]['ChequeDate']=='')) ? "" : date('F Y', strtotime($result[$a]['ChequeDate']));
				$nestedData[] = number_format($result[$a]['Amount'],2);
				$nestedData[] = html_entity_decode(stripslashes($result[$a]['ReturnRemarks']));  
				$nestedData[] = (($result[$a]['ChequeProcessDate']=='1970-01-01') || ($result[$a]['ChequeProcessDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['ChequeProcessDate']));
				$nestedData[] = html_entity_decode(stripslashes($result[$a]['Remarks']));  
			$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();
