<?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	=>	'Id',
	2	=>	'Block',
	3	=>	'Lot',
	4	=>	'FullName',
	5	=>	'ReservationDate',
	6	=>	'Window',
	7	=>	'DateDelivered',
	8	=>	'DateInspection',
	9	=>	'Appraiser',
	10	=>	'NOA',
	11	=>	'PostApproval',
	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']	  : "";
$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.Window, bsf.DateDelivered, bsf.DateInspection, bsf.Appraiser, bsf.NOA, bsf.PostApproval, bsf.TakeOut, bsf.Reason FROM reservation r LEFT JOIN buyer_status_folder_pagibig 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='pagibig' OR bpm.Financing='pag-ibig' OR bpm.PaymentMode='pag-ibig' OR bpm.PaymentMode='pagibig') ";  
 
		if($tobe=="yes"){
			$sql .= " AND (r.ScheduleLoan='to_be_delivered' OR r.ScheduleLoan='to_be_deliver') "; 
		}else{			
			$sql .= " AND (r.ScheduleLoan='delivered') "; 
		}

		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.Appraiser LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR bsf.Window 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.DateInspection,'%m/%d/%Y')) LIKE '%".$ind."%' ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsf.NOA,'%m/%d/%Y')) LIKE '%".$ind."%' ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsf.TakeOut,'%m/%d/%Y')) LIKE '%".$ind."%' ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsf.PostApproval,'%m/%d/%Y')) LIKE '%".$ind."%' ";   
		$sql.=" OR UPPER(DATE_FORMAT(bsf.DateDelivered,'%m/%d/%Y')) LIKE '%".$ind."%' ) ";   
	}
}
 /*
	if( !empty($rangeStart) && !empty($rangeEnd) &&  $tobedate=="yes"){
		$sql .= " AND (bsf.DateDelivered BETWEEN ('".date('Y-m-d', strtotime($rangeStart))."') AND ('".date('Y-m-d', strtotime($rangeEnd))."')";
	}	*/
 

/*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_pagibig&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]['Window']); 
			$nestedData[] = (($result[$a]['DateDelivered']=='1970-01-01') || ($result[$a]['DateDelivered']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['DateDelivered']));
			$nestedData[] = (($result[$a]['DateInspection']=='1970-01-01') || ($result[$a]['DateInspection']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['DateInspection']));
			$nestedData[] = strtoupper($result[$a]['Appraiser']); 
			$nestedData[] = (($result[$a]['NOA']=='1970-01-01') || ($result[$a]['NOA']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['NOA']));
			$nestedData[] = (($result[$a]['PostApproval']=='1970-01-01') || ($result[$a]['PostApproval']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['PostApproval']));
			$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();