<?  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	=>	'Id',
	2	=>	'Block',
	3	=>	'Lot',
	4	=>	'FullName',
	5	=>	'ReservationDate',
	6	=>	'Facility',
	7	=>	'DateDelivered',
	8	=>	'ReturnedDate',
	9	=>	'BankOfficer',
	10	=>	'LOG',
	11	=>	'Booking',
	12	=>	'TakeOut',
	13	=>	'Reason',
	14 => 'ReservationStatus'
);   

$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')));
$tobe 		= isset($_REQUEST['tobe_stat'])   ? $_REQUEST['tobe_stat']	  : "yes";
$tobedate	= isset($_REQUEST['tobe_stat_date'])   ? $_REQUEST['tobe_stat_date']	  : "yes";   
  
$resultQuery = "SELECT r.Id, r.Block, r.Lot, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname, r.ReservationDate, r.ReservationStatus, r.Project, r.Model, r.ScheduleLoan, r.ToBeDeliveredDate,
bsf.Facility as Facility, bsf.DateDelivered as DateDelivered, bsf.ReturnedDate as ReturnedDate, bsf.BankOfficer as BankOfficer, bsf.LOG as LOG, bsf.Booking as Booking, bsf.TakeOut as TakeOut, bsf.Reason as Reason FROM reservation r LEFT JOIN buyer_status_folder_bank bsf ON bsf.ReservationId=r.Id LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id ";
$sql =	$resultQuery."  WHERE r.Status = 'active' AND (bpm.Financing='bank' OR bpm.PaymentMode='bank')  ";


		if($tobedate=="yes"){
			$sql .= " AND bsf.DateDelivered BETWEEN ('".date('Y-m-d', strtotime($rangeStart))."') AND ('".date('Y-m-d', strtotime($rangeEnd))."')";
		}
  

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 bsf.BankOfficer LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR bsf.Facility LIKE '%".$ind."%' ";
		$sql.=" OR bsf.Reason 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(bsf.ReturnedDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsf.LOG,'%m/%d/%Y')) LIKE '%".$ind."%' ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsf.TakeOut,'%m/%d/%Y')) LIKE '%".$ind."%' ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsf.Booking,'%m/%d/%Y')) LIKE '%".$ind."%' ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsf.DateDelivered,'%m/%d/%Y')) LIKE '%".$ind."%' ) ";   
	}
} 


/*
if($tobedate=="yes" && $tobe=="yes"){
		if( !empty($rangeStart) && !empty($rangeEnd) ){
			
			if($tobe=="yes"){
				$sql .= " AND (bsf.DateDelivered BETWEEN ('".date('Y-m-d', strtotime($rangeStart))."') AND ('".date('Y-m-d', strtotime($rangeEnd))."')";
				$sql .= " OR r.ToBeDeliveredDate BETWEEN ('".date('Y-m-d', strtotime($rangeStart))."') AND ('".date('Y-m-d', strtotime($rangeEnd))."'))";
			}else{
				$sql .= " AND bsf.DateDelivered BETWEEN ('".date('Y-m-d', strtotime($rangeStart))."') AND ('".date('Y-m-d', strtotime($rangeEnd))."')";
			}
			
		}else if( !empty($rangeStart)){ 
			if($tobe=="yes"){
				$sql .= " AND (bsf.DateDelivered >= ('".date('Y-m-d', strtotime($rangeStart))."') ";
				$sql .= " OR r.ToBeDeliveredDate >= ('".date('Y-m-d', strtotime($rangeStart))."') )";
			}else{
				 $sql .= " AND bsf.DateDelivered >= ('".date('Y-m-d', strtotime($rangeStart))."') ";
			} 
			
		}else if( !empty($rangeEnd)){
			
			if($tobe=="yes"){
				$sql .= " AND (bsf.DateDelivered <= ('".date('Y-m-d', strtotime($rangeEnd))."') ";
				$sql .= " OR r.ToBeDeliveredDate <= ('".date('Y-m-d', strtotime($rangeEnd))."')) ";
			}else{
				$sql .= " AND bsf.DateDelivered <= ('".date('Y-m-d', strtotime($rangeEnd))."') ";
			}
		} 
		
		if($tobe=="yes"){
			$sql .= " AND (r.ScheduleLoan='to_be_delivered' OR r.ScheduleLoan='delivered' OR r.ScheduleLoan='to_be_deliver') "; 
		}

}else if($tobedate=="yes" && $tobe=="no"){
		if( !empty($rangeStart) && !empty($rangeEnd) ){
			
			if($tobe=="yes"){
				$sql .= " AND (bsf.DateDelivered BETWEEN ('".date('Y-m-d', strtotime($rangeStart))."') AND ('".date('Y-m-d', strtotime($rangeEnd))."')";
				$sql .= " OR r.ToBeDeliveredDate BETWEEN ('".date('Y-m-d', strtotime($rangeStart))."') AND ('".date('Y-m-d', strtotime($rangeEnd))."'))";
			}else{
				$sql .= " AND bsf.DateDelivered BETWEEN ('".date('Y-m-d', strtotime($rangeStart))."') AND ('".date('Y-m-d', strtotime($rangeEnd))."')";
			}
			
		}else if( !empty($rangeStart)){ 
			if($tobe=="yes"){
				$sql .= " AND (bsf.DateDelivered >= ('".date('Y-m-d', strtotime($rangeStart))."') ";
				$sql .= " OR r.ToBeDeliveredDate >= ('".date('Y-m-d', strtotime($rangeStart))."') )";
			}else{
				 $sql .= " AND bsf.DateDelivered >= ('".date('Y-m-d', strtotime($rangeStart))."') ";
			} 
			
		}else if( !empty($rangeEnd)){
			
			if($tobe=="yes"){
				$sql .= " AND (bsf.DateDelivered <= ('".date('Y-m-d', strtotime($rangeEnd))."') ";
				$sql .= " OR r.ToBeDeliveredDate <= ('".date('Y-m-d', strtotime($rangeEnd))."')) ";
			}else{
				$sql .= " AND bsf.DateDelivered <= ('".date('Y-m-d', strtotime($rangeEnd))."') ";
			}
		} 
		
		if($tobe=="no"){
			$sql .= " AND ( r.ScheduleLoan='delivered' ) "; 
		}

}else if($tobedate=="no" && $tobe=="yes"){  
		$sql .= " AND ( r.ScheduleLoan='to_be_delivered' OR r.ScheduleLoan='to_be_deliver' OR r.ScheduleLoan='delivered' ) "; 
}else if($tobedate=="no" && $tobe=="no"){  
		$sql .= " AND ( r.ScheduleLoan='delivered' ) "; 
}*/
 

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

$sql.=" ORDER BY cast(". $columns[$requestData['order'][0]['column']]." as unsigned)   ".$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 href="index.php?pg=payment&type=buyer_status_folder_bank&action=edit&id='.$result[$a]['Id'].'"><span class="btn btn-xs btn-warning glyphicon glyphicon-heart" title="Buyer Status Monitoring"></span></a>';
			$nestedData[] = $result[$a]['Id'];
			$nestedData[] = $result[$a]['Block'];
			$nestedData[] = $result[$a]['Lot'];
			$nestedData[] = $result[$a]['Fullname'];
			$nestedData[] = (($result[$a]['ReservationDate']=='1970-01-01') || ($result[$a]['ReservationDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['ReservationDate']));
			
			$nestedData[] = strtoupper($result[$a]['Facility']); 
			$nestedData[] = (($result[$a]['DateDelivered']=='1970-01-01') || ($result[$a]['DateDelivered']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['DateDelivered']));
			$nestedData[] = (($result[$a]['ReturnedDate']=='1970-01-01') || ($result[$a]['ReturnedDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['ReturnedDate']));
			$nestedData[] = strtoupper($result[$a]['BankOfficer']); 
			$nestedData[] = (($result[$a]['LOG']=='1970-01-01') || ($result[$a]['LOG']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['LOG']));
			$nestedData[] = (($result[$a]['Booking']=='1970-01-01') || ($result[$a]['Booking']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['Booking']));
			$nestedData[] = (($result[$a]['TakeOut']=='1970-01-01') || ($result[$a]['TakeOut']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['TakeOut']));
			$nestedData[] = strtoupper($result[$a]['Reason']);
			$nestedData[] = strtoupper($result[$a]['ReservationStatus']); 
			$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();