<?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");

$lat = 0;
$lon = 0;
$tipo = 'zona';
$valor = 0;



if($_REQUEST['lat']){$lat = $_REQUEST['lat'];}
if($_REQUEST['lon']){$lon = $_REQUEST['lon'];}
if($_REQUEST['tipo']){$tipo = $_REQUEST['tipo'];}
if($_REQUEST['valor']){$valor = $_REQUEST['valor'];}

$sql = '';

switch ($tipo){
    case "id":
        $sql = "SELECT X(Endpoint(SHAPE)) AS lon1, Y(Endpoint(SHAPE)) AS lat1 FROM conexiones WHERE activo = 1 AND id =" . $valor;
        break;
    case "padron":
        $sql = "SELECT lonc AS lon1, latc AS lat1 FROM padrones WHERE padron =" . $valor;
        break;
    case "servicio":
        $sql = "SELECT X(Endpoint(SHAPE)) AS lon1, Y(Endpoint(SHAPE)) AS lat1 FROM conexiones WHERE activo = 1 AND  servicios LIKE '%\"" . $valor . "\"%'";
        break;
    case "colector":
        $sql = "SELECT X(Endpoint(SHAPE)) AS lon1, Y(Endpoint(SHAPE)) AS lat1 FROM conexiones WHERE activo = 1 AND colector = " . $valor;
        break;
    case "tgm":
        $sql = "SELECT X(Endpoint(SHAPE)) AS lon1, Y(Endpoint(SHAPE)) AS lat1 FROM conexiones WHERE activo = 1 AND tgms  LIKE '%" . $valor . "%'";
        break;
    case "suministro":
        $sql = "SELECT X(Endpoint(SHAPE)) AS lon1, Y(Endpoint(SHAPE)) AS lat1 FROM conexiones WHERE activo = 1 AND oses  LIKE '%" . $valor . "%'";
        break;
    case "medidor":
        $sql = "SELECT X(Endpoint(SHAPE)) AS lon1, Y(Endpoint(SHAPE)) AS lat1 FROM conexiones WHERE activo = 1 AND medidores  LIKE '%" . $valor . "%'";
        break;                     
}

if ($sql != '')
{
    $resultado = mysqli_query($con,$sql);
    if ($resultado != FALSE){
        if (mysqli_num_rows($resultado) > 0){
            $row = mysqli_fetch_assoc($resultado);
                $lat = $row["lat1"];
                $lon = $row["lon1"];
        }
}
}



$amplitud  = 0.003;
$geometria_zona = "GEOMFROMTEXT('POLYGON((" . ($lon - $amplitud) . " " . ($lat - $amplitud) . "," . ($lon - $amplitud) . " " . ($lat + $amplitud) . "," . ($lon + $amplitud) . " " . ($lat + $amplitud) . "," . ($lon + $amplitud) . " " . ($lat - $amplitud) . "," . ($lon - $amplitud) . " " . ($lat - $amplitud) . "))')";

$res = array();
$res_conexiones = array();
$res_padrones = array();
$res_colectores = array();
$res_calles = array();
$res_servicios = array();
$res_centro = array();
$res_servicios_ep = array();
$res_conexiones_inactivas = array();

$sql = "SELECT id, X(Startpoint(SHAPE)) AS lon1, Y(Startpoint(SHAPE)) AS lat1,  X(Endpoint(SHAPE)) AS lon2, Y(Endpoint(SHAPE)) AS lat2,padron, colector, profundidad, tipo, seccion, distmed, distmei, alta_fuent , alta_descr, alta_fecha, servicios, tgms, oses, medidores, sur, historial, etapa, ccz, accion, fotos, audios, activo, puertas, z_col, via, id_tramite, id_con_ant, calle , empresa, tramite, descripcion, cantino, areaper, areaimp, caudal FROM conexiones WHERE activo = 1 AND INTERSECTS(SHAPE, " . $geometria_zona . ")";

$resultado = mysqli_query($con,$sql);

if ($resultado != FALSE){
	if (mysqli_num_rows($resultado) > 0){
		while($row = mysqli_fetch_assoc($resultado))
		{
		   // $row = array_map('utf8_encode',$row);
			$elem = array(
							'id'=>$row["id"],
							'lon1'=>$row["lon1"],
                            'lat1'=>$row["lat1"],
							'lon2'=>$row["lon2"],
                            'lat2'=>$row["lat2"],
                            'padron'=>$row["padron"],
                            'colector'=>$row["colector"],
                            'profundidad'=>$row["profundidad"],
                            'tipo'=>$row["tipo"],
                            'seccion'=>$row["seccion"],
                            'distmed'=>$row["distmed"],
                            'distmei'=>$row["distmei"],
                            'alta_fuent'=>$row["alta_fuent"],
                            'alta_descr'=>$row["alta_descr"],
                            'alta_fecha'=>$row["alta_fecha"],
                            'servicios'=>$row["servicios"],
                            'tgms'=>$row["tgms"],
                            'oses'=>$row["oses"],
                            'medidores'=>$row["medidores"],
                            'sur'=>$row["sur"],
                            'historial'=>$row["historial"],
                            'etapa'=>$row["etapa"],
                            'ccz'=>$row["ccz"],
                            'accion'=>$row["accion"],
                            'fotos'=>$row["fotos"],
                            'audios'=>$row["audios"],
                            'activo'=>$row["activo"],
                            'puertas'=>$row["puertas"],
                            'z_col'=>$row["z_col"],
                            'via'=>$row["via"],
                            'id_tramite'=>$row["id_tramite"],
                            'id_con_ant'=>$row["id_con_ant"],
                            'calle'=>$row["calle"],
                            'empresa'=>$row["empresa"],
                            'tramite'=>$row["tramite"],
                            'descripcion'=>$row["descripcion"],
                            'cantino'=>$row["cantino"],
                            'areaper'=>$row["areaper"],
                            'areaimp'=>$row["areaimp"],
                            'caudal'=>$row["caudal"]
      	   				);
			 $exito_en_consulta = TRUE;
           // $elem = array_map('utf8_encode', $elem);
			 array_push($res_conexiones,$elem);
			}
		}
}			 


$sql = "SELECT id, padron, historial FROM conexiones WHERE activo = 0 AND INTERSECTS(SHAPE, " . $geometria_zona . ")";

$resultado = mysqli_query($con,$sql);

if ($resultado != FALSE){
	if (mysqli_num_rows($resultado) > 0){
		while($row = mysqli_fetch_assoc($resultado))
		{
		   // $row = array_map('utf8_encode',$row);
			$elem = array(
							'id'=>$row["id"],
                            'padron'=>$row["padron"],
                            'historial'=>$row["historial"]
                        );
			 $exito_en_consulta = TRUE;
           // $elem = array_map('utf8_encode', $elem);
			 array_push($res_conexiones_inactivas,$elem);
			}
		}
}	




$sql = "SELECT ASTEXT(SHAPE) AS geom, nom_calle, cod_nombre FROM calles_im  WHERE INTERSECTS(SHAPE, " . $geometria_zona . ")";

$resultado = mysqli_query($con,$sql);

if ($resultado != FALSE){
	if (mysqli_num_rows($resultado) > 0){
		while($row = mysqli_fetch_assoc($resultado))
		{
		    // $row = array_map('utf8_encode',$row);
			$elem = array(
							'geom'=>$row["geom"],
							'nom_calle'=>$row["nom_calle"],
                            'cod_nombre'=>$row["cod_nombre"]
                        );
                        $exito_en_consulta = TRUE;
                       // $elem = array_map('utf8_encode', $elem);
                        array_push($res_calles,$elem);
        }
    }
}	



$sql = "SELECT B.geom, B.tiposec, B.tipotra, B.elemred, B.dim1, B.dim2, B.zarriba, B.zabajo, B.longitud, B.latc, B.lonc, B.desc_tramo, B.desc_secci FROM (SELECT ASTEXT(A.SHAPE) AS geom,(SELECT codigo FROM adm_clases WHERE id = A.tiposec) AS tiposec,(SELECT codigo FROM adm_tipos WHERE id = A.tipotra) AS tipotra,A.elemred AS elemred, A.dim1 AS dim1, A.dim2 AS dim2, A.zarriba AS zarriba,A.zabajo AS zabajo, A.longitud AS longitud,Y(ST_STARTPOINT(A.SHAPE)) AS latc,X(ST_STARTPOINT(A.SHAPE)) AS lonc,(SELECT descripcion FROM adm_tipos WHERE id = A.tipotra) AS desc_tramo,(SELECT descripcion FROM adm_clases WHERE id = A.tiposec) AS desc_secci FROM cat_tramos AS A WHERE activo = 1 AND INTERSECTS(A.SHAPE, " . $geometria_zona . ")) AS B WHERE 1";
$resultado = mysqli_query($con,$sql);
if ($resultado != FALSE){
	if (mysqli_num_rows($resultado) > 0){
		while($row = mysqli_fetch_assoc($resultado))
		{
		    // $row = array_map('utf8_encode',$row);
			$elem = array(
							'geom'=>$row["geom"],
							'tiposec'=>$row["tiposec"],
                            'tipotra'=>$row["tipotra"],
                            'elemred'=>$row["elemred"],
                            'dim1'=>$row["dim1"],
                            'dim2'=>$row["dim2"],
                            'zarriba'=>$row["zarriba"],
                            'zabajo'=>$row["zabajo"],
                            'longitud'=>$row["longitud"],
                            'desc_tramo'=>$row["desc_tramo"],
                            'desc_secci'=>$row["desc_secci"]
                        );
                        $exito_en_consulta = TRUE;
                      //  $elem = array_map('utf8_encode', $elem);
                        array_push($res_colectores,$elem);
        }
    }
}



$sql = "SELECT ASTEXT(GEOMETRIA) AS geom, padron, ph, saneado, latc, lonc, areatot FROM padrones WHERE INTERSECTS(GEOMETRIA, " . $geometria_zona . ")";
$resultado = mysqli_query($con,$sql);

if ($resultado != FALSE){
	if (mysqli_num_rows($resultado) > 0){
		while($row = mysqli_fetch_assoc($resultado))
		{
		   // $row = array_map('utf8_encode',$row);
            $pos = strpos($row["geom"],"),");
            if ($pos === false)
            {
                $geometria = $row["geom"];
            }else
            {
                $geometria = substr($row["geom"],0,$pos) . ")";
            }
			$elem = array(
							'geom'=>$geometria,
							'padron'=>$row["padron"],
                            'ph'=>$row["ph"],
                            'saneado'=>$row["saneado"],
                            'areatot'=>$row["areatot"]
                        );
                        $exito_en_consulta = TRUE;
                       // $elem = array_map('utf8_encode', $elem);
                        array_push($res_padrones,$elem);
        }
    }
}



$sql = "SELECT SERVICIO, PADRON, VIA, CALLE, OSES, MEDIDORES, TGMS, PUERTAS, lonc, latc, CALLE FROM servicios WHERE PADRON <> 0 AND INTERSECTS(GEOMFROMTEXT(CONCAT('POINT(', lonc, ' ', latc, ')'))" . "," . $geometria_zona . ")";
$resultado = mysqli_query($con,$sql);
if ($resultado != FALSE){
	if (mysqli_num_rows($resultado) > 0){
		while($row = mysqli_fetch_assoc($resultado))
		{
		   //  $row = array_map('utf8_encode',$row);
			$elem = array(
							'servicio'=>$row["SERVICIO"],
							'padron'=>$row["PADRON"],
                            'via'=>$row["VIA"],
                            'calle'=>$row["CALLE"],
                            'oses'=>$row["OSES"],
                            'medidores'=>$row["MEDIDORES"],
                            'tgms'=>$row["TGMS"],
                            'puertas'=>$row["PUERTAS"],
                            'lonc'=>$row["lonc"],
                            'latc'=>$row["latc"]
                        );
                        $exito_en_consulta = TRUE;
                     //   $elem = array_map('utf8_encode', $elem);
                        array_push($res_servicios,$elem);
        }
    }
}


$sql = "SELECT SERVICIO, PADRON, VIA, CALLE, OSES, MEDIDORES, TGMS, PUERTAS, CALLE FROM servicios WHERE PADRON = 0 ";
$resultado = mysqli_query($con,$sql);

if ($resultado != FALSE){
	if (mysqli_num_rows($resultado) > 0){
		while($row = mysqli_fetch_assoc($resultado))
		{
		   //  $row = array_map('utf8_encode',$row);
			$elem = array(
							'servicio'=>$row["SERVICIO"],
							'padron'=>$row["PADRON"],
                            'via'=>$row["VIA"],
                            'calle'=>$row["CALLE"],
                            'oses'=>$row["OSES"],
                            'medidores'=>$row["MEDIDORES"],
                            'tgms'=>$row["TGMS"],
                            'puertas'=>$row["PUERTAS"]
                        );
                        $exito_en_consulta = TRUE;
                     //   $elem = array_map('utf8_encode', $elem);
                        array_push($res_servicios_ep,$elem);
        }
    }
}








array_push($res_centro,$lat);
array_push($res_centro,$lon);


array_push($res, $res_conexiones);
array_push($res, $res_colectores);
array_push($res, $res_calles);
array_push($res, $res_padrones);
array_push($res, $res_servicios);
array_push($res, $res_centro);
array_push($res, $res_servicios_ep);
array_push($res, $res_conexiones_inactivas);
mysqli_close($con);

echo json_encode($res);
?>
