<?  
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; 
$formClass		=	new formClass();
$columns = array(
	0	=>	'Id',
	1	=>	'ReservationDate',
	2	=>	'Block',
	3	=>	'Lot',
	4	=>	'Id',
	5	=>	'Id',
	6	=>	'FullName',
	7	=>	'LoanableAmount',
	8	=>	'BuyerStandardReqDetails',
	9	=>	'BuyerStandardReqDetails', 
	10	=>	'TaxDecLandReleased',
	11	=>	'TaxDecBuildingReleased',
	12	=>	'TaxReceiptLandReleased',
	13	=>	'TaxReceiptBuildingReleased',
	14	=>	'BuildingPermitReleased',
	15	=>	'ElectricalPermitReleased', 
	16	=>	'SanitaryPermitReleased', 
	17	=>	'OccupancyPermitReleased', 
	18	=>	'Road', 
	19	=>	'SideWalkGutter', 
	20	=>	'Meralco', 
	21	=>	'Water', 
	22	=>	'HouseConstruction',
	23	=>	'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 as Id, bpm.LoanableAmount as LoanableAmount, r.Phase as Phase, r.PricelistDate as PricelistDate, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname,  r.ReservationDate, m.HouseConstruction as HouseConstruction, m.MonitoringFolderDetails as MonitoringFolderDetails, r.Model, r.Project, r.ReservationStatus as ReservationStatus FROM reservation r LEFT JOIN monitoring_folders m ON m.ReservationId=r.Id LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id   ";*/


/*SELECT r.Id as Id, bpm.LoanableAmount as LoanableAmount, r.Phase as Phase, r.PricelistDate as PricelistDate, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname, r.ReservationDate, r.Model, r.Project, r.ReservationStatus as ReservationStatus, units_block_lot.TDLandNo, units_block_lot.TDBldgNo, permits_and_licenses.TaxDecLandProcessed FROM reservation r LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id LEFT JOIN units_block_lot ON units_block_lot.Block=r.Block AND units_block_lot.Lot=r.Lot LEFT JOIN permits_and_licenses ON permits_and_licenses.UnitId=units_block_lot.UnitId
*/


$resultQuery = "SELECT r.Block, r.Lot, r.Id as Id, bpm.LoanableAmount as LoanableAmount, r.Phase as Phase, r.PricelistDate as PricelistDate, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname,  r.ReservationDate, m.BuyerStandardReqDetails as BuyerStandardReqDetails, r.Model, r.Project, r.ReservationStatus as ReservationStatus, pal.TaxDecLandProcessed as TaxDecLandProcessed, pal.TaxDecLandReleased as TaxDecLandReleased, pal.TaxDecBuildingProcessed as TaxDecBuildingProcessed, pal.TaxDecBuildingReleased as TaxDecBuildingReleased, pal.TaxReceiptLandProcessed as TaxReceiptLandProcessed, pal.TaxReceiptLandReleased as TaxReceiptLandReleased, pal.TaxReceiptBuildingProcessed as TaxReceiptBuildingProcessed, pal.TaxReceiptBuildingReleased as TaxReceiptBuildingReleased, pal.BuildingPermitProcessed as BuildingPermitProcessed, pal.BuildingPermitReleased as BuildingPermitReleased, pal.ElectricalPermitProcessed as ElectricalPermitProcessed, pal.ElectricalPermitReleased as ElectricalPermitReleased, pal.SanitaryPermitProcessed as SanitaryPermitProcessed, pal.SanitaryPermitReleased as SanitaryPermitReleased, pal.OccupancyPermitProcessed as OccupancyPermitProcessed, pal.OccupancyPermitReleased as OccupancyPermitReleased, cau.Road as Road, cau.SideWalkGutter as SideWalkGutter, cau.Meralco as Meralco, cau.Water as Water, cau.HouseConstruction as HouseConstruction, cau.HouseConstructionPercentage as HouseConstructionPercentage, ubl.TDLandNo, ubl.TDBldgNo FROM reservation r LEFT JOIN checklist m ON m.ReservationId=r.Id LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id
LEFT JOIN units_block_lot ubl ON ubl.Block=r.Block AND ubl.Lot=r.Lot
LEFT JOIN units u ON u.Id=ubl.UnitId
LEFT JOIN construction_and_utilities cau ON cau.UnitId=u.Id
LEFT JOIN permits_and_licenses pal ON pal.UnitId=u.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 m.BuyerStandardReqDetails LIKE '%".$ind."%' "; 
		$sql.=" OR cau.HouseConstruction LIKE '%".$ind."%' "; 
		$sql.=" OR r.ReservationStatus LIKE '%".$ind."%' ";
		$sql.=" OR r.Project LIKE '%".$ind."%' ";
		$sql.=" OR r.Model LIKE '%".$ind."%' ";
		$sql.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%d-%b-%Y')) LIKE '%".$ind."%' ) ";   
	}
}
 
if($tobedate=="yes"){
		if( !empty($requestData['columns'][0]['search']['value']) && !empty($requestData['columns'][1]['search']['value']) ){
			$sql .= " AND r.ReservationDate 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 r.ReservationDate >= ('".date('Y-m-d', strtotime($requestData['columns'][0]['search']['value']))."') ";
		}else if( !empty($requestData['columns'][1]['search']['value'])){
			$sql .= " AND r.ReservationDate <= ('".date('Y-m-d', strtotime($requestData['columns'][1]['search']['value']))."') ";
		}

		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']."   "; 

//,cast(r.Block as unsigned) asc, cast(r.Lot as unsigned) asc
 
$data = array(); 
$mecon->query($sql);
$result			= $mecon->resultset(); 

$detArr = array(); 
				$req_completed 		= "";	
				$signed_loan_docs 	= "";
				$sworn_cert 		= "";
				$tax_dec_land 		= "";
				$tax_dec_building 	= ""; 
				$tax_land_receipt 	= ""; 
				$tax_building_receipt 	= ""; 
				$building_permit 	= ""; 
				$electrical_permit 	= ""; 
				$sanitary_permit 	= ""; 
				$occupancy_permit 	= ""; 
				$road 	= ""; 
				$sidewalk_and_gutter 	= ""; 
				$meralco 	= ""; 
				$water 	= "";  
				
		for($a=0; $a<count($result); $a++){
			$nestedData=array(); 
			$nestedData[] = $result[$a]['Id'];
			$nestedData[] = (($result[$a]['ReservationDate']=='1970-01-01') || ($result[$a]['ReservationDate']=='') || ($result[$a]['ReservationDate']==NULL)) ? "" : date('m/d/Y', strtotime($result[$a]['ReservationDate']));
			$nestedData[] = $result[$a]['Block'];
			$nestedData[] = $result[$a]['Lot']; 
			//$nestedData[] = $result[$a]['LotSize'];  
			$pricedate	= (($result[$a]['PricelistDate']=='1970-01-01') || ($result[$a]['PricelistDate']=='') || ($result[$a]['PricelistDate']==NULL)) ? "" : date('Y-m-d', strtotime($result[$a]['PricelistDate']));
			$phase		= html_entity_decode(stripslashes($result[$a]['Phase'])); 
			$block		= html_entity_decode(stripslashes($result[$a]['Block'])); 
			$lot		= html_entity_decode(stripslashes($result[$a]['Lot'])); 
			$project	= html_entity_decode(stripslashes($result[$a]['Project'])); 
			$models		= html_entity_decode(stripslashes($result[$a]['Model'])); 
			if($pricedate!='1970-01-01' || $pricedate!='' || $pricedate!=NULL){
			$other = $formClass->getOtherDetails($pricedate,$models,$project,$phase,$block,$lot);		
			$nestedData[] = $other['lot_size'];
			$nestedData[] = $other['floor_area'];
			//$nestedData[] = "";
			}else{
			$nestedData[] = "";
			$nestedData[] = "";
			}
			
			
			$nestedData[] = html_entity_decode($result[$a]['Fullname']);
			$nestedData[] = (!empty($result[$a]['LoanableAmount']) || $result[$a]['LoanableAmount']!=NULL)	? number_format($result[$a]['LoanableAmount'],2) : "";

			$detArr = json_decode(str_replace('""','',$result[$a]['BuyerStandardReqDetails']),true);
			 if(count($detArr)!=0){ 
					foreach($detArr as $key => $detInd){
						if($detInd=="completed_documentary_requirement"){	$req_completed = "OK";	} 
						if($detInd=="signed_loan_documents"){	$signed_loan_docs = "OK";	 }  
					} 
						$nestedData[] = $req_completed; 
						$nestedData[] = $signed_loan_docs;  
						
			 }else{
				$nestedData[] = ""; 
				$nestedData[] = "";
			 } 
		 
						
						$nestedData[] = ($result[$a]['TaxDecLandProcessed']!='1970-01-01' && $result[$a]['TaxDecLandReleased']!='1970-01-01' && $result[$a]['TaxDecLandProcessed']!=NULL && $result[$a]['TaxDecLandReleased']!=NULL) ? 'OK' : '';						

						$nestedData[] = ($result[$a]['TaxDecBuildingProcessed']!='1970-01-01' && $result[$a]['TaxDecBuildingReleased']!='1970-01-01' && $result[$a]['TaxDecBuildingProcessed']!=NULL && $result[$a]['TaxDecBuildingReleased']!=NULL) ? 'OK' : ''; 

						$nestedData[] = ($result[$a]['TaxReceiptLandProcessed']!='1970-01-01' && $result[$a]['TaxReceiptLandReleased']!='1970-01-01' && $result[$a]['TaxReceiptLandProcessed']!=NULL && $result[$a]['TaxReceiptLandReleased']!=NULL) ? 'OK' : ''; 

						$nestedData[] = ($result[$a]['TaxReceiptBuildingProcessed']!='1970-01-01' && $result[$a]['TaxReceiptBuildingReleased']!='1970-01-01' && $result[$a]['TaxReceiptBuildingProcessed']!=NULL  && $result[$a]['TaxReceiptBuildingReleased']!=NULL) ? 'OK' : ''; 
						
						$nestedData[] = ($result[$a]['BuildingPermitProcessed']!='1970-01-01' && $result[$a]['BuildingPermitReleased']!='1970-01-01' && $result[$a]['BuildingPermitProcessed']!=NULL && $result[$a]['BuildingPermitReleased']!=NULL) ? 'OK' : ''; 
						
						$nestedData[] = ($result[$a]['ElectricalPermitProcessed']!='1970-01-01' && $result[$a]['ElectricalPermitReleased']!='1970-01-01' && $result[$a]['ElectricalPermitProcessed']!=NULL && $result[$a]['ElectricalPermitReleased']!=NULL) ? 'OK' : ''; 
						
						$nestedData[] = ($result[$a]['SanitaryPermitProcessed']!='1970-01-01' && $result[$a]['SanitaryPermitReleased']!='1970-01-01' && $result[$a]['SanitaryPermitProcessed']!=NULL && $result[$a]['SanitaryPermitReleased']!=NULL) ? 'OK' : ''; 
						
						$nestedData[] = ($result[$a]['OccupancyPermitProcessed']!='1970-01-01' && $result[$a]['OccupancyPermitReleased']!='1970-01-01' && $result[$a]['OccupancyPermitProcessed']!=NULL && $result[$a]['OccupancyPermitReleased']!=NULL) ? 'OK' : ''; 
						
						$nestedData[] = (strtolower($result[$a]['Road'])=='yes') ? 'OK' : ''; 
						$nestedData[] = (strtolower($result[$a]['SideWalkGutter'])=='yes') ? 'OK' : ''; 
						$nestedData[] = (strtolower($result[$a]['Meralco'])=='yes') ? 'OK' : '';  
						$nestedData[] = (strtolower($result[$a]['Water'])=='yes') ? 'OK' : '';  
			 $nestedData[] = (strtolower($result[$a]['HouseConstruction'])=='yes') ? 'OK' : '';   
			 //$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();