<?  
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	=>	'TakeOutDate',
	6	=>	'PermitToConstructNo',
	7	=>	'ConstructionBondPaidAmount',
	8	=>	'ConstructionBondPaid',
	9	=>	'PaymentDetails',
	10	=>	'FullRefundOrWithViolation',
	11	=>	'Penalty',
	12	=>	'Amount',
	13	=>	'RefundDate',
	14	=>	'CheckReleaseDetails',
	15	=>	'Remarks',
	16	=>	'Remarks2',
	17	=>	'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.Id, r.Block, r.Lot, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname, bpm.TakeOutDate, r.ReservationDate, r.ReservationStatus as ReservationStatus, r.Project, r.Model, bm.PermitToConstructNo as PermitToConstructNo, bm.ConstructionBondPaid as ConstructionBondPaid, bm.RefundDate as RefundDate, bm.FullRefundOrWithViolation as FullRefundOrWithViolation, bm.Penalty as Penalty, bm.Remarks as Remarks, bm.Amount as Amount, bm.ConstructionBondPaidAmount as ConstructionBondPaidAmount, bm.Remarks2 as Remarks2, bm.PaymentDetails as PaymentDetails, bm.CheckReleaseDetails as CheckReleaseDetails FROM reservation r 
	LEFT JOIN buyer_house_renovation_monitoring bm ON bm.ReservationId=r.Id 
	LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id "; 
   

$sql			=	$resultQuery."  WHERE r.Status = 'active' ";
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 bm.PermitToConstructNo LIKE '%".$ind."%' ";  
		$sql.=" OR bm.FullRefundOrWithViolation LIKE '%".$ind."%' "; 
		$sql.=" OR bm.Penalty LIKE '%".$ind."%' "; 
		$sql.=" OR bm.Remarks LIKE '%".$ind."%' "; 
		$sql.=" OR bm.Amount LIKE '%".$ind."%' "; 
		$sql.=" OR bm.ConstructionBondPaidAmount LIKE '%".$ind."%' "; 
		$sql.=" OR bm.CheckReleaseDetails LIKE '%".$ind."%' "; 
		$sql.=" OR bm.Remarks2 LIKE '%".$ind."%' "; 
		$sql.=" OR bm.PaymentDetails LIKE '%".$ind."%' ";  
		$sql.=" OR UPPER(DATE_FORMAT(bm.ConstructionBondPaid,'%m/%d/%Y')) LIKE '%".$ind."%' "; 
		$sql.=" OR UPPER(DATE_FORMAT(bm.RefundDate,'%m/%d/%Y')) LIKE '%".$ind."%' "; 
		$sql.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(bpm.TakeOutDate,'%m/%d/%Y')) LIKE '%".$ind."%' ) ";		 
	}
}

if($tobedate=="yes"){
	if( !empty($rangeStart) && !empty($rangeEnd) ){
		$sql .= " AND bpm.TakeOutDate BETWEEN ('".date('Y-m-d', strtotime($rangeStart))."') AND ('".date('Y-m-d', strtotime($rangeEnd))."')";
	}else if( !empty($rangeStart)){
		$sql .= " AND bpm.TakeOutDate >= ('".date('Y-m-d', strtotime($rangeStart))."') ";
	}else if( !empty($rangeEnd)){
		$sql .= " AND bpm.TakeOutDate <= ('".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[] = '<a href="index.php?pg=payment&type=buyer_house_renovation_monitoring&action=edit&id='.$result[$a]['Id'].'"><span class="btn btn-xs btn-warning glyphicon glyphicon-home" title="Buyer House Renovation Monitoring"></span></a>';
			$nestedData[] = $result[$a]['Id'];
			$nestedData[] = $result[$a]['Block'];
			$nestedData[] = $result[$a]['Lot'];
			//$nestedData[] = $result[$a]['Fullname'];

	
$nestedData[] = '<span style="word-wrap:break-word; word-break:break-all;">'.wordwrap($result[$a]['Fullname'],20,"<br>\n").'</span>';
			
			$nestedData[] = (($result[$a]['TakeOutDate']=='1970-01-01') || ($result[$a]['TakeOutDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['TakeOutDate']));
				$nestedData[] = $result[$a]['PermitToConstructNo']; 
 
				$nestedData[] = !empty($result[$a]['ConstructionBondPaidAmount']) ? number_format($result[$a]['ConstructionBondPaidAmount'],2) : "";

				$nestedData[] = (($result[$a]['ConstructionBondPaid']=='1970-01-01') || ($result[$a]['ConstructionBondPaid']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['ConstructionBondPaid']));

				$nestedData[] = $result[$a]['PaymentDetails']; 
				$nestedData[] = $result[$a]['FullRefundOrWithViolation'];
				$nestedData[] = !empty($result[$a]['Penalty']) ? number_format($result[$a]['Penalty'],2) : "";
				$nestedData[] = !empty($result[$a]['Amount']) ? number_format($result[$a]['Amount'],2) : "";

				$nestedData[] = (($result[$a]['RefundDate']=='1970-01-01') || ($result[$a]['RefundDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['RefundDate']));

				$nestedData[] = $result[$a]['CheckReleaseDetails']; 
				
				 

$nestedData[] = '<span style="word-wrap:break-word; word-break:break-all;">'.wordwrap($result[$a]['Remarks'],20,"<br>\n").'</span>';



$nestedData[] = '<span style="word-wrap:break-word; word-break:break-all;">'.wordwrap($result[$a]['Remarks2'],20,"<br>\n").'</span>';
	
				
				$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();