<?  
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	=>	'rCellphoneNo',
	4	=>	'Amount', 
	5	=>	'ChequeDate', 
	6	=>	'dayspastdue' 
);     

//$rangeStart = isset($_REQUEST['range_start']) 	? $_REQUEST['range_start'] 		: date('Y-m-d', strtotime(date('01-m-Y'))); //30days range
//$rangeEnd 	= isset($_REQUEST['range_end'])   	? $_REQUEST['range_end']	  	: date("Y-m-t", strtotime(date('Y-m-d'))); 	//30days range

$rangeStart = isset($_REQUEST['range_start']) ?  	$_REQUEST['range_start'] 	: date("Y-m-d",strtotime(date('Y-m-d') . "-1 Year")); //one year range 24DEC24
$rangeEnd 	= isset($_REQUEST['range_end'])   ? 	$_REQUEST['range_end']	  	: date("Y-m-d", strtotime(date('Y-m-d'))); //one year range 24DEC24

 
$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, r.CellphoneNo as rCellphoneNo,  DATEDIFF(NOW(),bcm.ChequeDate) as dayspastdue 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') ";
$sql			=	$resultQuery."  WHERE  r.Status='active' AND bcm.Amount!=0.00 AND (bcm.ChequeDate!='1970-01-01' OR bcm.ChequeDate!='') AND (bcm.ChequeStatus='' OR bcm.ChequeStatus IS NULL) "; 

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 DATEDIFF(NOW(),bcm.ChequeDate) 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 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[] = html_entity_decode(stripslashes($result[$a]['rCellphoneNo']));
				$nestedData[] = number_format($result[$a]['Amount'],2);
				$nestedData[] = (($result[$a]['ChequeDate']=='1970-01-01') || ($result[$a]['ChequeDate']=='')) ? "" : date('F d, Y', strtotime($result[$a]['ChequeDate']));
				 
				 if ($result[$a]['dayspastdue'] > 0){
				 	$color = "red";
				 }else{
					$color = "green";
				 }
				
				
				$nestedData[] = "<span style='color:".$color."'>".html_entity_decode(stripslashes($result[$a]['dayspastdue']))."</span>";  
			$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();
