<?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");
ini_set('memory_limit','-1');
set_time_limit(0);

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_psingulares = array();
$res_captaciones = array();
$res_calles = array();
$res_tipos = array();
$res_clases = 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 2000"; 
$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 ; 

$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 elem_red, X(SHAPE) AS lon1, Y(SHAPE) AS lat1,z_tapa,salto,z_ab FROM cat_psingulares WHERE activo = 1 AND X(SHAPE) > " . $minx . " AND X(SHAPE) < " . $maxx . " AND Y(SHAPE) > " . $miny .  " AND Y(SHAPE) < " . $maxy ; 
$resultado = mysqli_query($con,$sql);
if ($resultado != FALSE){
	if (mysqli_num_rows($resultado) > 0){
		while($row = mysqli_fetch_assoc($resultado))
		{
			$elem = array(
							'id_psingular'=>$row["elem_red"],
							'lon1'=>$row["lon1"],
                            'lat1'=>$row["lat1"],
                            'z_tapa'=>$row["z_tapa"]
       				);
			 array_push($res_psingulares,$elem);
			}
		}
}	



$sql = "SELECT elem_red, X(SHAPE) AS lon1, Y(SHAPE) AS lat1,id_tramo FROM cat_captaciones WHERE activo = 1 AND X(SHAPE) > " . $minx . " AND X(SHAPE) < " . $maxx . " AND Y(SHAPE) > " . $miny .  " AND Y(SHAPE) < " . $maxy ; 
$resultado = mysqli_query($con,$sql);
if ($resultado != FALSE){
	if (mysqli_num_rows($resultado) > 0){
		while($row = mysqli_fetch_assoc($resultado))
		{
			$elem = array(
							'id_captacion'=>$row["elem_red"],
							'lon1'=>$row["lon1"],
                            'lat1'=>$row["lat1"],
                            'id_tramo'=>$row["id_tramo"]
       				);
			 array_push($res_captaciones,$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 ; 

$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 id, elemento, codigo, descripcion, diagrama FROM adm_tipos WHERE 1";
$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"],
                'elemento'=>$row["elemento"],
                'codigo'=>$row["codigo"],
                'descripcion'=>$row["descripcion"],
                'diagrama'=>$row["diagrama"]
            );
            array_push($res_tipos, $elem);
		}
}
}	

$sql = "SELECT id, elemento, codigo, descripcion, diagrama, id_tipo FROM adm_clases WHERE 1";
$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"],
                'elemento'=>$row["elemento"],
                'codigo'=>$row["codigo"],
                'descripcion'=>$row["descripcion"],
                'diagrama'=>$row["diagrama"],
                'id_tipo'=>$row["id_tipo"],
            );
            array_push($res_clases, $elem);
		}
}
}




//array_push($res, $res_conexiones);
array_push($res, $res_tramos);
array_push($res, $res_psingulares);
array_push($res, $res_captaciones);
array_push($res, $res_calles);
array_push($res, $res_tipos);
array_push($res, $res_clases);

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



