<? 
error_reporting(0);
ob_start("ob_gzhandler");
session_start();
session_cache_limiter('private');
$cache_limiter 	= session_cache_limiter();
session_cache_expire(60);
$cache_expire 	= session_cache_expire(); 
date_default_timezone_set('Asia/Manila'); 
$CURRENT_DATE 	= date('Y-m-d H:i:s');
include_once('../config/minho.php');
include_once('../config/autoloadClass.php');
$autoload		=	new autoloadClass(); 
$mecon			=	new condbClass();
$userType       = 	$_SESSION['castalia_user_type'];
$username       = 	$_SESSION['castalia_username'];

$requestData	=	$_REQUEST; 
$columns = array(
	0	=>	'Id',
	1	=>	'ReservationDate',
	2	=>	'Buyer',
	3	=>	'Block',
	4	=>	'Lot',
	5 	=>  'ContactNo' 
); 


$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";
 
$todayDate 		= date('Y-m-d');
$resultQuery 	= "SELECT c.ReservationId as Id, DATE_FORMAT(r.ReservationDate,'%m/%d/%Y') as ReservationDate, CONCAT(r.LastName,', ',r.FirstName) as Buyer, r.Block as Block, r.Lot as Lot, r.CellphoneNo as ContactNo 
FROM checklist c LEFT JOIN reservation r ON r.Id=c.ReservationId ";

//ORDER BY `c.ReservationId`  DESC";  
$sql			=	$resultQuery."    
WHERE 	1=1 AND (c.BillingDate < DATE_SUB(now(), INTERVAL 6 MONTH)  AND (c.BillingDate!='1970-01-01' OR c.BillingDate!=NULL) 
	OR  c.BarangayDate < DATE_SUB(now(), INTERVAL 6 MONTH)  AND (c.BarangayDate!='1970-01-01' OR c.BarangayDate!=NULL)  
	OR  c.PaySlipDate < DATE_SUB(now(), INTERVAL 6 MONTH)  AND (c.PaySlipDate!='1970-01-01' OR c.PaySlipDate!=NULL)  
	OR  c.NotarizedDate < DATE_SUB(now(), INTERVAL 12 MONTH)  AND (c.NotarizedDate!='1970-01-01' OR c.NotarizedDate!=NULL)  

	OR  c.Co1BillingDate < DATE_SUB(now(), INTERVAL 6 MONTH)  AND (c.Co1BillingDate!='1970-01-01' OR c.Co1BillingDate!=NULL) 
	OR  c.Co1BarangayDate < DATE_SUB(now(), INTERVAL 6 MONTH)  AND (c.Co1BarangayDate!='1970-01-01' OR c.Co1BarangayDate!=NULL)  
	OR  c.Co1PaySlipDate < DATE_SUB(now(), INTERVAL 6 MONTH)  AND (c.Co1PaySlipDate!='1970-01-01' OR c.Co1PaySlipDate!=NULL)  
	OR  c.Co1NotarizedDate < DATE_SUB(now(), INTERVAL 12 MONTH)  AND (c.Co1NotarizedDate!='1970-01-01' OR c.Co1NotarizedDate!=NULL)  

	OR  c.Co2BillingDate < DATE_SUB(now(), INTERVAL 6 MONTH)  AND (c.Co2BillingDate!='1970-01-01' OR c.Co2BillingDate!=NULL) 
	OR  c.Co2BarangayDate < DATE_SUB(now(), INTERVAL 6 MONTH)  AND (c.Co2BarangayDate!='1970-01-01' OR c.Co2BarangayDate!=NULL)  
	OR  c.Co2PaySlipDate < DATE_SUB(now(), INTERVAL 6 MONTH)  AND (c.Co2PaySlipDate!='1970-01-01' OR c.Co2PaySlipDate!=NULL)  
	OR  c.Co2NotarizedDate < DATE_SUB(now(), INTERVAL 12 MONTH)  AND (c.Co2NotarizedDate!='1970-01-01' OR c.Co2NotarizedDate!=NULL)  


	OR  c.Co3BillingDate < DATE_SUB(now(), INTERVAL 6 MONTH)  AND (c.Co3BillingDate!='1970-01-01' OR c.Co3BillingDate!=NULL) 
	OR  c.Co3BarangayDate < DATE_SUB(now(), INTERVAL 6 MONTH)  AND (c.Co3BarangayDate!='1970-01-01' OR c.Co3BarangayDate!=NULL)  
	OR  c.Co3PaySlipDate < DATE_SUB(now(), INTERVAL 6 MONTH)  AND (c.Co3PaySlipDate!='1970-01-01' OR c.Co3PaySlipDate!=NULL)  
	OR  c.Co3NotarizedDate < DATE_SUB(now(), INTERVAL 12 MONTH)  AND (c.Co3NotarizedDate!='1970-01-01' OR c.Co3NotarizedDate!=NULL))  	";
 

 
if(!empty($requestData['search']['value']) ) { 
	$arr = explode(" ",$requestData['search']['value']);  
	foreach($arr as $ind){
		$ind = str_replace(',','',$ind); 
		$sql.=" AND (CONCAT(r.LastName,', ',r.FirstName) LIKE '%".$ind."%' ";
		$sql.=" OR r.Lot LIKE '%".$ind."%' "; 
		$sql.=" OR r.Block LIKE '%".$ind."%' ";  
		$sql.=" OR r.CellphoneNo LIKE '%".$ind."%' "; 
		$sql.=" OR DATE_FORMAT(r.ReservationDate,'%m/%d/%Y') LIKE '%".$ind."%' )"; 
	}
}
 

if($tobedate=="yes"){
	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(r.Block as unsigned) asc, cast(r.Lot as unsigned) asc  LIMIT ".$requestData['start']." ,".$requestData['length']."   "; 
$mecon->query($sql);
$data = array();
$result = $mecon->resultset();
  
			 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[]	= "<a href='index.php?pg=reservation&type=checklist_of_requirements&action=edit&id=".$result[$a]['Id']."'>".$result[$a]['Buyer']."</a>";
				$nestedData[]	= $result[$a]['Block'];
				$nestedData[]	= $result[$a]['Lot'];
				$nestedData[]	= $result[$a]['ContactNo'];
				$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();