<?  
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	=>	'LoanableAmount',
	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')));
$tobedate 	= isset($_REQUEST['tobe_stat_date'])   ? $_REQUEST['tobe_stat_date']	  : "yes";

$resultQuery 		= "SELECT r.ReservationStatus as ReservationStatus, r.Id as Id, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as Fullname, r.ReservationDate as ReservationDate, bpm.LoanableAmount as LoanableAmount,bsp.Facility as Facility, bsp.DateDelivered as DateDelivered, bsp.ReturnedDate as ReturnedDate, bsp.BankOfficer as BankOfficer, bsp.LOG as LOG, bsp.Booking as Booking, bsp.TakeOut as TakeOut, bsp.Reason as Reason, r.Project, r.Model FROM reservation r LEFT JOIN buyer_status_folder_bank bsp ON bsp.ReservationId=r.Id LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id "; 
 
//$sql			=	$resultQuery."  WHERE r.Status = 'active' AND (r.ScheduleLoan='to_be_delivered' OR r.ScheduleLoan='delivered' OR r.ScheduleLoan='to_be_deliver')"; 
$sql			=	$resultQuery."  WHERE r.Status = 'active' AND (r.ScheduleLoan='delivered')  AND bpm.Financing='bank' "; 
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 bsp.Facility LIKE '%".$ind."%' ";
		$sql.=" OR bsp.Reason LIKE '%".$ind."%' ";
		$sql.=" OR bsp.BankOfficer LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR bpm.LoanableAmount LIKE '%".$ind."%' ";
		$sql.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(bsp.TakeOut,'%m/%d/%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsp.Booking,'%m/%d/%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsp.LOG,'%m/%d/%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsp.DateDelivered,'%m/%d/%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsp.ReturnedDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";     
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%m/%d/%Y')) LIKE '%".$ind."%'  )";     
	}
}
 
if($tobedate=="yes"){
		if( !empty($requestData['columns'][0]['search']['value']) && !empty($requestData['columns'][1]['search']['value']) ){
			$sql .= " AND bsp.DateDelivered 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 bsp.DateDelivered >= ('".date('Y-m-d', strtotime($requestData['columns'][0]['search']['value']))."') ";
		}else if( !empty($requestData['columns'][1]['search']['value'])){
			$sql .= " AND bsp.DateDelivered <= ('".date('Y-m-d', strtotime($requestData['columns'][1]['search']['value']))."') ";
		}

		if( !empty($rangeStart) && !empty($rangeEnd) ){
			$sql .= " AND bsp.DateDelivered BETWEEN ('".date('Y-m-d', strtotime($rangeStart))."') AND ('".date('Y-m-d', strtotime($rangeEnd))."')";
		}else if( !empty($rangeStart)){
			$sql .= " AND bsp.DateDelivered >= ('".date('Y-m-d', strtotime($rangeStart))."') ";
		}else if( !empty($rangeEnd)){
			$sql .= " AND bsp.DateDelivered <= ('".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);
$data = array();
$result = $mecon->resultset(); 
 
		for($a=0; $a<count($result); $a++){
			$nestedData	=	array(); 
	$nestedData[] = $result[$a]['Id'];  
	$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[] = html_entity_decode(stripslashes($result[$a]['Fullname']));
	$nestedData[] = number_format($result[$a]['LoanableAmount'],2);
	$nestedData[] = $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[] = $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[] = html_entity_decode(stripslashes($result[$a]['Reason']));
	$nestedData[] = strtoupper(html_entity_decode(stripslashes($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();