<?php
header('Content-Type: application/json;charset= utf-8');
require("dbinfo.php");
$con = mysqli_connect($sqlname, $username, $password, $db);
if(!$con){exit;}
mysqli_set_charset($con,"utf8");

if($_REQUEST['maxx']){$maxx = $_REQUEST['maxx'];}
if($_REQUEST['maxy']){$maxy = $_REQUEST['maxy'];}
if($_REQUEST['minx']){$minx = $_REQUEST['minx'];}
if($_REQUEST['miny']){$miny = $_REQUEST['miny'];}


$maxx = $maxx + 0.005;
$maxy = $maxy + 0.005;
$minx = $minx - 0.005;
$miny = $miny - 0.005;


$res = array();
$res_conexiones = array();
$res_tramos = array();
$res_calles = array();
$res_padrones = array();


$sql = "SELECT id, X(Startpoint(SHAPE)) AS lon1, Y(Startpoint(SHAPE)) AS lat1,  X(Endpoint(SHAPE)) AS lon2, Y(Endpoint(SHAPE)) AS lat2 FROM conexiones WHERE activo = 1 AND X(Startpoint(SHAPE)) > " . $minx . " AND X(Startpoint(SHAPE)) < " . $maxx . " AND Y(Startpoint(SHAPE)) > " . $miny .  " AND Y(Startpoint(SHAPE)) < " . $maxy . " LIMIT 8000"; 
$resultado = mysqli_query($con,$sql);
if ($resultado != FALSE){
	if (mysqli_num_rows($resultado) > 0){
		while($row = mysqli_fetch_assoc($resultado))
		{
			$elem = array(
							'id'=>$row["id"],
							'lon1'=>$row["lon1"],
                            'lat1'=>$row["lat1"],
							'lon2'=>$row["lon2"],
                            'lat2'=>$row["lat2"]
      	   				);
			 array_push($res_conexiones,$elem);
			}
		}
}	


$sql = "SELECT ASTEXT(SHAPE) AS geom, elemred, X(Startpoint(SHAPE)) AS lon1, Y(Startpoint(SHAPE)) AS lat1,  X(Endpoint(SHAPE)) AS lon2, Y(Endpoint(SHAPE)) AS lat2,id_psig_aa,id_psig_ab,zarriba,zabajo,z_tapa_aa,z_tapa_ab,longitud FROM cat_tramos WHERE activo = 1 AND X(Startpoint(SHAPE)) > " . $minx . " AND X(Startpoint(SHAPE)) < " . $maxx . " AND Y(Startpoint(SHAPE)) > " . $miny .  " AND Y(Startpoint(SHAPE)) < " . $maxy . " LIMIT 3000"; 

$resultado = mysqli_query($con,$sql);
if ($resultado != FALSE){
	if (mysqli_num_rows($resultado) > 0){
		while($row = mysqli_fetch_assoc($resultado))
		{
			$elem = array(
                            'geom'=>$row["geom"],
							'id_tramo'=>$row["elemred"],
							'lon1'=>$row["lon1"],
                            'lat1'=>$row["lat1"],
							'lon2'=>$row["lon2"],
                            'lat2'=>$row["lat2"],
                            'id_psig_aa'=>$row["id_psig_aa"],
                            'id_psig_ab'=>$row["id_psig_ab"],
                            'zarriba'=>$row["zarriba"],
                            'zabajo'=>$row["zabajo"],
                            'z_tapa_aa'=>$row["z_tapa_aa"],
                            'z_tapa_ab'=>$row["z_tapa_ab"],
							'longitud'=>$row["longitud"]
       				);
			 array_push($res_tramos,$elem);
			}
		}
}			 



$sql = "SELECT X(Startpoint(SHAPE)) AS lon1, Y(Startpoint(SHAPE)) AS lat1,  X(Endpoint(SHAPE)) AS lon2, Y(Endpoint(SHAPE)) AS lat2,nom_calle,cod_nombre FROM calles_im WHERE X(Startpoint(SHAPE)) > " . $minx . " AND X(Startpoint(SHAPE)) < " . $maxx . " AND Y(Startpoint(SHAPE)) > " . $miny .  " AND Y(Startpoint(SHAPE)) < " . $maxy . " LIMIT 2000"; 

$resultado = mysqli_query($con,$sql);
if ($resultado != FALSE){
	if (mysqli_num_rows($resultado) > 0){
		while($row = mysqli_fetch_assoc($resultado))
		{
			$elem = array(
							'lon1'=>$row["lon1"],
                            'lat1'=>$row["lat1"],
							'lon2'=>$row["lon2"],
                            'lat2'=>$row["lat2"],
                            'calle'=>$row["nom_calle"],
                            'via'=>$row["cod_nombre"]
       				);
			 array_push($res_calles,$elem);
			}
		}
}

$sql = "SELECT ASTEXT(GEOMETRIA) AS geom, padron FROM padrones WHERE  X(Centroid(GEOMETRIA)) > " . $minx . " AND X(Centroid(GEOMETRIA)) < " . $maxx . " AND Y(Centroid(GEOMETRIA)) > " . $miny .  " AND Y(Centroid(GEOMETRIA)) < " . $maxy . " LIMIT 25000"; 

$resultado = mysqli_query($con,$sql);
if ($resultado != FALSE){
	if (mysqli_num_rows($resultado) > 0){
		while($row = mysqli_fetch_assoc($resultado))
		{
			$elem = array(
                            'geom'=>$row["geom"],
							'padron'=>$row["padron"]
       				);
			 array_push($res_padrones,$elem);
			}
		}
}	

$sql = "SELECT MAX(id) as max FROM conexiones"; 

$resultado = mysqli_query($con,$sql);
if ($resultado != FALSE){
	if (mysqli_num_rows($resultado) > 0){
		while($row = mysqli_fetch_assoc($resultado))
		{
			$maxid = $row["max"];
		}
}	
}

//array_push($res, $res_conexiones);
array_push($res, $res_conexiones);
array_push($res, $res_tramos);
array_push($res, $res_calles);
array_push($res, $res_padrones);
array_push($res, $maxid);

mysqli_close($con);
echo json_encode($res);
?>



