<?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	=>	'Block',
	1	=>	'Lot', 
	2	=>	'TaxDecLandProcessed',
	3	=>	'TaxDecLandReleased',
	4	=>	'TDLandNo',
	5	=>	'TaxDecBuildingProcessed',
	6	=>	'TaxDecBuildingReleased',
	7	=>	'TDBldgNo',
	8	=>	'TaxReceiptLandProcessed',
	9	=>	'TaxReceiptLandReleased',
	10	=>	'TaxReceiptBuildingProcessed',
	11	=>	'TaxReceiptBuildingReleased',
	12	=>	'BuildingPermitProcessed',
	13	=>	'BuildingPermitReleased',
	14	=>	'ElectricalPermitProcessed',
	15	=>	'ElectricalPermitReleased',
	16	=>	'SanitaryPermitProcessed',
	17	=>	'SanitaryPermitReleased',
	18	=>	'OccupancyPermitProcessed',
  	19	=>	'OccupancyPermitReleased',
  	20  =>  'Id' 
);  

$mainQuery 		= "SELECT ubl.Id AS Id, ubl.Block AS Block, ubl.Lot AS Lot, 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, ubl.TDLandNo AS TDLandNo, ubl.TDBldgNo AS TDBldgNo  FROM permits_and_licenses pal LEFT JOIN units_block_lot ubl ON ubl.UnitId=pal.UnitId ";
$sql			=	$mainQuery."  WHERE pal.Block!='null' ";
if(!empty($requestData['search']['value']) ) { 
	$arr = explode(" ",$requestData['search']['value']);  
	foreach($arr as $ind){ 
		$ind = str_replace(',','',$ind);
		$sql.=" AND ( ubl.Block LIKE '%".$ind."%' ";
		$sql.=" OR ubl.Lot LIKE '%".$ind."%' ";
		$sql.=" OR ubl.TDLandNo LIKE '%".$ind."%' ";
		$sql.=" OR ubl.TDBldgNo LIKE '%".$ind."%' ";
 		$sql.=" OR UPPER(DATE_FORMAT(pal.TaxDecLandProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.TaxDecLandReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.TaxDecBuildingProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.TaxDecBuildingReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.TaxReceiptLandProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.TaxReceiptLandReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.TaxReceiptBuildingProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.TaxReceiptBuildingReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.BuildingPermitProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.BuildingPermitReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.ElectricalPermitProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.ElectricalPermitReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.SanitaryPermitProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.SanitaryPermitReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.OccupancyPermitProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.OccupancyPermitReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
 		$sql.="  ) ";   


	}
}
 
$mecon->query($sql);
$result			= $mecon->resultset(); 
$totalData		= count($result);
$totalFiltered	=	$totalData;
  
//$sql.=" ORDER BY ".$columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']."   "; 


$sql.=" ORDER BY cast(ubl.Block as unsigned)   ".$requestData['order'][0]['dir'].", cast(ubl.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[] = (!empty($result[$a]['Id'])) 	? $result[$a]['Id'] 	: ""; 
				$nestedData[] = (!empty($result[$a]['Block']))  ? $result[$a]['Block']  : ""; 
				$nestedData[] = (!empty($result[$a]['Lot'])) 	? $result[$a]['Lot'] 	: ""; 



$nestedData[] = (!empty($result[$a]['TaxDecLandProcessed']) && $result[$a]['TaxDecLandProcessed']!='1970-01-01') ? date('m/d/Y',strtotime($result[$a]['TaxDecLandProcessed'])) : ""; 
$nestedData[] = (!empty($result[$a]['TaxDecLandReleased']) && $result[$a]['TaxDecLandReleased']!='1970-01-01')  ? date('m/d/Y',strtotime($result[$a]['TaxDecLandReleased']))  : ""; 

$nestedData[] = (!empty($result[$a]['TDLandNo'])) ? $result[$a]['TDLandNo'] : ""; 


$nestedData[] = (!empty($result[$a]['TaxDecBuildingProcessed']) && $result[$a]['TaxDecBuildingProcessed']!='1970-01-01') ? date('m/d/Y',strtotime($result[$a]['TaxDecBuildingProcessed'])) : ""; 
$nestedData[] = (!empty($result[$a]['TaxDecBuildingReleased']) && $result[$a]['TaxDecBuildingReleased']!='1970-01-01') ? date('m/d/Y',strtotime($result[$a]['TaxDecBuildingReleased'])) : ""; 

$nestedData[] = (!empty($result[$a]['TDBldgNo'])) ? $result[$a]['TDBldgNo'] : ""; 

$nestedData[] = (!empty($result[$a]['TaxReceiptLandProcessed']) && $result[$a]['TaxReceiptLandProcessed']!='1970-01-01') ? date('m/d/Y',strtotime($result[$a]['TaxReceiptLandProcessed'])) : ""; 
$nestedData[] = ($result[$a]['TaxReceiptLandReleased']!='1970-01-01') ? date('m/d/Y',strtotime($result[$a]['TaxReceiptLandReleased'])) : ""; 
$nestedData[] = ($result[$a]['TaxReceiptBuildingProcessed']!='1970-01-01') ? date('m/d/Y',strtotime($result[$a]['TaxReceiptBuildingProcessed'])) : ""; 
$nestedData[] = ($result[$a]['TaxReceiptBuildingReleased']!='1970-01-01') ? date('m/d/Y',strtotime($result[$a]['TaxReceiptBuildingReleased'])) : ""; 
$nestedData[] = ($result[$a]['BuildingPermitProcessed']!='1970-01-01') ? date('m/d/Y',strtotime($result[$a]['BuildingPermitProcessed'])) : ""; 
$nestedData[] = (!empty($result[$a]['BuildingPermitReleased']) && $result[$a]['BuildingPermitReleased']!='1970-01-01') ? date('m/d/Y',strtotime($result[$a]['BuildingPermitReleased'])) : ""; 
$nestedData[] = (!empty($result[$a]['ElectricalPermitProcessed']) && $result[$a]['ElectricalPermitProcessed']!='1970-01-01') ? date('m/d/Y',strtotime($result[$a]['ElectricalPermitProcessed'])) : "";
$nestedData[] = (!empty($result[$a]['ElectricalPermitReleased']) && $result[$a]['ElectricalPermitReleased']!='1970-01-01') ? date('m/d/Y',strtotime($result[$a]['ElectricalPermitReleased'])) : ""; 
$nestedData[] = (!empty($result[$a]['SanitaryPermitProcessed']) && $result[$a]['SanitaryPermitProcessed']!='1970-01-01') ? date('m/d/Y',strtotime($result[$a]['SanitaryPermitProcessed'])) : ""; 
$nestedData[] = (!empty($result[$a]['SanitaryPermitReleased']) && $result[$a]['SanitaryPermitReleased']!='1970-01-01') ? date('m/d/Y',strtotime($result[$a]['SanitaryPermitReleased'])) : ""; 
$nestedData[] = (!empty($result[$a]['OccupancyPermitProcessed']) && $result[$a]['OccupancyPermitProcessed']!='1970-01-01') ? date('m/d/Y',strtotime($result[$a]['OccupancyPermitProcessed'])) : ""; 
$nestedData[] = (!empty($result[$a]['OccupancyPermitReleased']) && $result[$a]['OccupancyPermitReleased']!='1970-01-01') ? date('m/d/Y',strtotime($result[$a]['OccupancyPermitReleased'])) : ""; 
//$nestedData[] = $result[$a]['Id'];
$nestedData[] = "";








				$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();