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

$res = array();

$mensaje_array = array();

$tipo = '';
if( $_REQUEST["tipo"] ) {  $tipo = $_REQUEST['tipo'];}


if ($tipo == 1)
{
//_CONTROL DE CONEXIONES QUE NO CRUZAN PADRON REFERIDO EN DATOS

          $sql = "SELECT A.id AS ID, A.padron AS PADRON, A.calle AS CALLE, A.puertas AS PUERTAS ";
          $sql .= ", (X(STARTPOINT(A.SHAPE)) + X(ENDPOINT(A.SHAPE))) / 2  AS XC ";
          $sql .= ", (Y(STARTPOINT(A.SHAPE)) + Y(ENDPOINT(A.SHAPE))) / 2  AS YC ";
          $sql .= "FROM conexiones AS A INNER JOIN ";
          $sql .= "(SELECT padron, GEOMETRIA FROM padrones   GROUP BY padron HAVING COUNT(*) = 1) AS B ";
          $sql .= " ON A.padron = B.padron  ";
          $sql .= " WHERE activo = 1 AND NOT ST_Intersects(A.SHAPE,B.GEOMETRIA) GROUP BY ID ";
          $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'],
                            'xc'=>$row['XC'],
                            'yc'=>$row['YC'],
                            'tipo'=>'Padron cruzado',
                            'padron'=>$row['PADRON'],
                            'calle'=>$row['CALLE'],
                            'puertas'=>$row['PUERTAS']
                              );
                    array_push($res,$elem);
                  }
                  $mensaje =  mysqli_num_rows($resultado) . " - Padrones cruzados ";
                  array_push($mensaje_array,$mensaje);
              }
          }            

          $resultado = null;


//_CONTROL DE CONEXIONES de PADRONES MUTADOS

$sql = "SELECT A.id AS ID , A.padron AS PADRON, A.calle AS CALLE, A.puertas AS PUERTAS ";
$sql .= ", (X(STARTPOINT(A.SHAPE)) + X(ENDPOINT(A.SHAPE))) / 2  AS XC ";
$sql .= ", (Y(STARTPOINT(A.SHAPE)) + Y(ENDPOINT(A.SHAPE))) / 2  AS YC ";
$sql .= "FROM conexiones AS A WHERE NOT EXISTS ";
$sql .= "(SELECT B.PADRON from padrones AS B WHERE B.padron = A.padron LIMIT 1) AND A.padron <> 0 ";

$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'],
                  'xc'=>$row['XC'],
                  'yc'=>$row['YC'],
                  'tipo'=>'Padron mutado',
                  'padron'=>$row['PADRON'],
                  'calle'=>$row['CALLE'],
                  'puertas'=>$row['PUERTAS']
                    );
          array_push($res,$elem);
        }
        $mensaje =  mysqli_num_rows($resultado) . " - Padrones mutados ";
        array_push($mensaje_array,$mensaje);
    }
}            

$resultado = null;



          //_CONTROL DE CONEXIONES cortas

          $sql = "SELECT id as ID, SHAPE , padron AS PADRON, calle AS CALLE, puertas AS PUERTAS ";
          $sql .= ", (X(STARTPOINT(SHAPE)) + X(ENDPOINT(SHAPE))) / 2  AS XC ";
          $sql .= ", (Y(STARTPOINT(SHAPE)) + Y(ENDPOINT(SHAPE))) / 2  AS YC ";
          $sql .= "FROM conexiones ";
          $sql .= "WHERE activo = 1  HAVING ST_DISTANCE(STARTPOINT(SHAPE),ENDPOINT(SHAPE)) < 0.00001";
          $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'],
                            'xc'=>$row['XC'],
                            'yc'=>$row['YC'],
                            'tipo'=>'Conexion corta',
                            'padron'=>$row['PADRON'],
                            'calle'=>$row['CALLE'],
                            'puertas'=>$row['PUERTAS']
                              );
                    array_push($res,$elem);
                  }
                  $mensaje =   mysqli_num_rows($resultado) . " - Conexiones cortas  " ;
                  array_push($mensaje_array,$mensaje);
              }
          } 
          $resultado = null;

          //_CONTROL DE CONEXIONES largas

          $sql = "SELECT id as ID, SHAPE , padron AS PADRON, calle AS CALLE, puertas AS PUERTAS ";
          $sql .= ", (X(STARTPOINT(SHAPE)) + X(ENDPOINT(SHAPE))) / 2  AS XC ";
          $sql .= ", (Y(STARTPOINT(SHAPE)) + Y(ENDPOINT(SHAPE))) / 2  AS YC ";
          $sql .= "FROM conexiones ";
          $sql .= "WHERE activo = 1  HAVING ST_DISTANCE(STARTPOINT(SHAPE),ENDPOINT(SHAPE)) > 0.0006";
          $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'],
                            'xc'=>$row['XC'],
                            'yc'=>$row['YC'],
                            'tipo'=>'Conexion larga',
                            'padron'=>$row['PADRON'],
                            'calle'=>$row['CALLE'],
                            'puertas'=>$row['PUERTAS']
                              );
                    array_push($res,$elem);
                  }
                  $mensaje =   mysqli_num_rows($resultado) . " - Conexiones largas  ";
                  array_push($mensaje_array,$mensaje);
              }
          } 
          $resultado = null;

          //_CONTROL DE CONEXIONES ALEJADAS DE COLECTOR_____________________________________
          $colectores_alejados = 0;

          $sql = "SELECT A.id AS ID , A.SHAPE, B.SHAPE , A.padron AS PADRON, A.calle AS CALLE, A.puertas AS PUERTAS ";
          $sql .= ", (X(STARTPOINT(A.SHAPE)) + X(ENDPOINT(A.SHAPE))) / 2  AS XC ";
          $sql .= ", (Y(STARTPOINT(A.SHAPE)) + Y(ENDPOINT(A.SHAPE))) / 2  AS YC ";
          $sql .= "FROM conexiones AS A INNER JOIN cat_tramos AS B  ";
          $sql .= "ON A.colector = B.elemred  "; 
          $sql .= "WHERE A.activo = 1  HAVING ST_DISTANCE(ST_ENDPOINT(A.SHAPE),B.SHAPE) > 0.00001023 AND ST_NUMPOINTS(B.SHAPE) < 3";
          $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'],
                            'xc'=>$row['XC'],
                            'yc'=>$row['YC'],
                            'tipo'=>'Colector alejado',
                            'padron'=>$row['PADRON'],
                            'calle'=>$row['CALLE'],
                            'puertas'=>$row['PUERTAS']
                              );
                    array_push($res,$elem);
                  }
                  $colectores_alejados = $colectores_alejados + mysqli_num_rows($resultado);
              }
          } 
          $resultado = null;

          $sql = "SELECT A.id AS ID , A.SHAPE, B.SHAPE, ASTEXT(B.SHAPE) AS GEOMCOLECTOR, ST_X(ST_ENDPOINT(A.SHAPE)) as LON, ST_Y(ST_ENDPOINT(A.SHAPE)) AS LAT ";
          $sql .= ", A.padron AS PADRON, A.calle AS CALLE, A.puertas AS PUERTAS"; 
          $sql .= ", (ST_X(ST_STARTPOINT(A.SHAPE)) + ST_X(ST_ENDPOINT(A.SHAPE))) / 2  AS XC ";
          $sql .= ", (ST_Y(ST_STARTPOINT(A.SHAPE)) + ST_Y(ST_ENDPOINT(A.SHAPE))) / 2  AS YC ";
          $sql .= "FROM conexiones AS A INNER JOIN cat_tramos AS B  ";
          $sql .= "ON A.colector = B.elemred  "; 
          $sql .= "WHERE A.activo = 1  HAVING ST_NUMPOINTS(B.SHAPE) > 2";
          $resultado = mysqli_query($con,$sql);
          if ($resultado != FALSE){
            if (mysqli_num_rows($resultado) > 0){
              while($row = mysqli_fetch_assoc($resultado))
              {
                $linestring = $row['GEOMCOLECTOR'];
                $lat = $row['LAT'];
                $lon = $row['LON'];
                $res_pu_cercano = dl_geometria::dl_punto_cercano_linestring($lat,$lon,$linestring);
                if ($res_pu_cercano['distancia'] > 1)
                      {
                        $elem = array(
                            'id'=>$row['ID'],
                            'xc'=>$row['XC'],
                            'yc'=>$row['YC'],
                            'tipo'=>'Colector alejado',
                            'padron'=>$row['PADRON'],
                            'calle'=>$row['CALLE'],
                            'puertas'=>$row['PUERTAS']
                              );
                    array_push($res,$elem);
                    $colectores_alejados++;
                    $dista = $res_pu_cercano['distancia'];
                    //echo("problema con id:" . $row['ID'] .  " distancia= " . $dista . PHP_EOL);
                      }
              }
              $mensaje =   $colectores_alejados . " - Colectores alejados   ";
              array_push($mensaje_array,$mensaje);
            } 
          } 
          $resultado = null;

          $sql = "SELECT id as ID ";
          $sql .= ", padron AS PADRON, calle AS CALLE, puertas AS PUERTAS"; 
          $sql .= ", (X(STARTPOINT(SHAPE)) + X(ENDPOINT(SHAPE))) / 2  AS XC ";
          $sql .= ", (Y(STARTPOINT(SHAPE)) + Y(ENDPOINT(SHAPE))) / 2  AS YC ";
          $sql .= "FROM conexiones ";
          $sql .= "WHERE activo = 1 AND colector < 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'],
                            'xc'=>$row['XC'],
                            'yc'=>$row['YC'],
                            'tipo'=>'Colector cero',
                            'padron'=>$row['PADRON'],
                            'calle'=>$row['CALLE'],
                            'puertas'=>$row['PUERTAS']
                              );
                    array_push($res,$elem);
                  }
                  $mensaje =  mysqli_num_rows($resultado) . " - Colector 0 ";
                  array_push($mensaje_array,$mensaje);
              }
          } 
  
          $resultado = null;

}


if ($tipo == 2)
{

      //_CONTROL DE CONEXIONES a PLUVIALES

      $sql = "SELECT A.id as ID ";
      $sql .= ", A.padron AS PADRON, A.calle AS CALLE, A.puertas AS PUERTAS"; 
      $sql .= ", (X(STARTPOINT(A.SHAPE)) + X(ENDPOINT(A.SHAPE))) / 2  AS XC ";
      $sql .= ", (Y(STARTPOINT(A.SHAPE)) + Y(ENDPOINT(A.SHAPE))) / 2  AS YC ";
      $sql .= "FROM conexiones A ";
      $sql .= "WHERE A.activo = 1 AND A.colector > 0 ";
      $sql .= "AND EXISTS(SELECT elemred FROM cat_tramos WHERE elemred = A.colector AND tipotra = 49)";
      $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'],
                        'xc'=>$row['XC'],
                        'yc'=>$row['YC'],
                        'tipo'=>'Conexion a Pluvial',
                        'padron'=>$row['PADRON'],
                        'calle'=>$row['CALLE'],
                        'puertas'=>$row['PUERTAS']
                          );
                array_push($res,$elem);
              }
              $mensaje =  mysqli_num_rows($resultado) . " - Conexiones a Pluvial ";
              array_push($mensaje_array,$mensaje);
          }
      } 

      $resultado = null;

}

if ($tipo == 3)
{

      //_CONTROL DE CONEXIONES padron 0

      $sql = "SELECT id as ID ";
      $sql .= ", padron AS PAD, calle AS CAL, puertas AS PUE"; 
      $sql .= ", (X(STARTPOINT(SHAPE)) + X(ENDPOINT(SHAPE))) / 2  AS XC ";
      $sql .= ", (Y(STARTPOINT(SHAPE)) + Y(ENDPOINT(SHAPE))) / 2  AS YC ";
      $sql .= "FROM conexiones ";
      $sql .= "WHERE activo = 1 AND padron < 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'],
                        'xc'=>$row['XC'],
                        'yc'=>$row['YC'],
                        'tipo'=>'Padron cero',
                        'padron'=>$row['PAD'],
                        'calle'=>$row['CAL'],
                        'puertas'=>$row['PUE']
                          );
                array_push($res,$elem);
              }
              $mensaje =  mysqli_num_rows($resultado) . " - Padrones 0 ";
              array_push($mensaje_array,$mensaje);
          }
      } 

      $resultado = null;

}



if ($tipo == 4)
{

      //_CONTROL DE CONEXIONES SIN TARIFA
      $sql = "SELECT id as ID ";
      $sql .= ", padron AS PAD, calle AS CAL, puertas AS PUE"; 
      $sql .= ", (X(STARTPOINT(SHAPE)) + X(ENDPOINT(SHAPE))) / 2  AS XC ";
      $sql .= ", (Y(STARTPOINT(SHAPE)) + Y(ENDPOINT(SHAPE))) / 2  AS YC ";
      $sql .= "FROM conexiones ";
      $sql .= "WHERE activo = 1 AND servicios = ''";
      $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'],
                        'xc'=>$row['XC'],
                        'yc'=>$row['YC'],
                        'tipo'=>'Conexion sin tarifa',
                        'padron'=>$row['PAD'],
                        'calle'=>$row['CAL'],
                        'puertas'=>$row['PUE']
                          );
                array_push($res,$elem);
              }
              $mensaje =  mysqli_num_rows($resultado) . " - Conexiones sin tarifa ";
              array_push($mensaje_array,$mensaje);
          }
      } 
      $resultado = null;

      //_CONTROL DE TARIFAS SIN CONEXION 
      $sql = "SELECT A.SERVICIO as ID , A.lonc AS XC , A.latc AS YC, A.PADRON, A.CALLE, A.PUERTAS FROM servicios AS A WHERE  NOT EXISTS ";
      $sql .= "(SELECT B.PADRON FROM conexiones AS B WHERE B.activo = 1 AND A.PADRON = B.PADRON AND B.servicios LIKE CONCAT('%\"',A.SERVICIO,'\"%')) ";
      $sql .= "AND NOT EXISTS (SELECT C.SERVICIO FROM "; 
      $sql .= "(SELECT DISTINCT(M.SERVICIO) AS SERVICIO FROM servicios M  INNER JOIN ";
      $sql .= "(SELECT ";
      $sql .= " T.padron,";
      $sql .= " JSON_UNQUOTE(J.padron) AS servicio ";
      $sql .= " FROM ";
      $sql .= " conexiones T,";
      $sql .= " JSON_TABLE(T.servicios, '$[*]' COLUMNS (padron VARCHAR(255) PATH '$')) AS J";
      $sql .= " WHERE ";
      $sql .= " JSON_VALID(T.servicios) AND ";
      $sql .= " TRIM(T.servicios) <> '' ";
      $sql .= " ) AS N";
      $sql .= " ON N.servicio = M.SERVICIO WHERE N.padron <> M.PADRON ";
      $sql .= " ) AS C WHERE C.SERVICIO = A.SERVICIO) AND PADRON > 0";

      $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'],
                        'xc'=>$row['XC'],
                        'yc'=>$row['YC'],
                        'tipo'=>'Tarifa sin conexion',
                        'padron'=>$row['PADRON'],
                        'calle'=>$row['CALLE'],
                        'puertas'=>$row['PUERTAS']
                          );
                array_push($res,$elem);
              }
              $mensaje =  mysqli_num_rows($resultado) . " - Tarifa sin conexion ";
              array_push($mensaje_array,$mensaje);
          }
      } 
      $resultado = null;


      //_CONTROL DE TARIFAS SIN CONEXION en EP
      $sql = "SELECT A.SERVICIO as ID , A.lonc AS XC , A.latc AS YC, A.PADRON , A.CALLE, A.PUERTAS FROM servicios AS A WHERE  NOT EXISTS ";
      $sql .= "(SELECT B.PADRON FROM conexiones AS B WHERE B.activo = 1 AND A.PADRON = B.PADRON AND B.servicios LIKE CONCAT('%\"',A.SERVICIO,'\"%')) ";
      $sql .= "AND NOT EXISTS (SELECT C.SERVICIO FROM "; 
      $sql .= "(SELECT DISTINCT(M.SERVICIO) AS SERVICIO FROM servicios M  INNER JOIN ";
      $sql .= "(SELECT ";
      $sql .= " T.padron,";
      $sql .= " JSON_UNQUOTE(J.padron) AS servicio ";
      $sql .= " FROM ";
      $sql .= " conexiones T,";
      $sql .= " JSON_TABLE(T.servicios, '$[*]' COLUMNS (padron VARCHAR(255) PATH '$')) AS J";
      $sql .= " WHERE ";
      $sql .= " JSON_VALID(T.servicios) AND ";
      $sql .= " TRIM(T.servicios) <> '' ";
      $sql .= " ) AS N";
      $sql .= " ON N.servicio = M.SERVICIO WHERE N.padron <> M.PADRON ";
      $sql .= " ) AS C WHERE C.SERVICIO = A.SERVICIO) AND PADRON = 0";

      $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'],
                        'xc'=>$row['XC'],
                        'yc'=>$row['YC'],
                        'tipo'=>'Tarifa sin conexion ep',
                        'padron'=>$row['PADRON'],
                        'calle'=>$row['CALLE'],
                        'puertas'=>$row['PUERTAS']
                          );
                array_push($res,$elem);
              }
              $mensaje =  mysqli_num_rows($resultado) . " - Tarifa sin conexion en EP";
              array_push($mensaje_array,$mensaje);
          }
      } 
      $resultado = null;

      $sql = "SELECT C.SERVICIO, C.PADRON AS PAD, C.CALLE AS CAL, C.PUERTAS AS PUERT, D.latc AS YC, D.lonc AS XC FROM";
      $sql .= " (SELECT A.SERVICIO AS SERVICIO, B.PADRON AS PADRON, B.CALLE AS CALLE, B.PUERTAS AS PUERTAS FROM ";
      $sql .= " (SELECT DISTINCT(M.SERVICIO) AS SERVICIO FROM servicios M  INNER JOIN ";
      $sql .= " (SELECT ";
      $sql .= " T.padron,";
      $sql .= " JSON_UNQUOTE(J.padron) AS servicio ";
      $sql .= " FROM ";
      $sql .= " conexiones T,";
      $sql .= " JSON_TABLE(T.servicios, '$[*]' COLUMNS (padron VARCHAR(255) PATH '$')) AS J";
      $sql .= " WHERE ";
      $sql .= " JSON_VALID(T.servicios) AND ";
      $sql .= " TRIM(T.servicios) <> '' ";
      $sql .= " ) AS N";
      $sql .= " ON N.servicio = M.SERVICIO WHERE N.padron <> M.PADRON ";
      $sql .= " ) AS A INNER JOIN servicios AS B ON A.SERVICIO = B.SERVICIO) AS C ";
      $sql .= " INNER JOIN padrones AS D ON D.padron = C.PADRON";
            $resultado = mysqli_query($con,$sql);
            if ($resultado != FALSE){
              if (mysqli_num_rows($resultado) > 0){
                while($row = mysqli_fetch_assoc($resultado))
                {
                        $elem = array(
                              'id'=>$row['SERVICIO'],
                              'xc'=>$row['XC'],
                              'yc'=>$row['YC'],
                              'tipo'=>'Tarifa padron cruzado',
                              'padron'=>$row['PAD'],
                              'calle'=>$row['CAL'],
                              'puertas'=>$row['PUERT']
                                );
                      array_push($res,$elem);
                    }
                    $mensaje =  mysqli_num_rows($resultado) . " - Tarifa padron cruzado ";
                    array_push($mensaje_array,$mensaje);
                }
            } 
            $resultado = null;

}




/*
$nombre_respaldo = "mantenimiento.csv";
$archivo_sql = fopen($nombre_respaldo,"w");
for($i=0;$i<count($res);$i++)
{
  fwrite($archivo_sql, $res[$i]['xc'] . ";" . $res[$i]['yc'] . ";" . $res[$i]['id'] . ";" . $res[$i]['tipo'] . PHP_EOL );
}
fclose($archivo_sql);
*/

$res_total = array();
array_push($res_total,$res);
$elem = array('mensaje'=>$mensaje_array);
array_push($res_total,$elem);

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