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

$sql = '';
$historia = '';
if($_REQUEST['sql']){$sql = $_REQUEST['sql'];}

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

$fecha = dl_fecha::dl_ahora_texto();

//$sql = "INSERT INTO cat_tramos (SHAPE, elemred, id_psig_aa, id_psig_ab, zarriba, zabajo, z_tapa_aa, z_tapa_ab, prof_aa, prof_ab, longitud,ccz, calle, via, historial,alta_fuente,alta_fecha,etapa,tiposec,tipotra,dim1,dim2,dim3,dim4,contrato,proyecto,material,accion) VALUES (GEOMFROMTEXT('LINESTRING(-56.313333524857164 -34.78812698959354,-56.31326450955801 -34.78815382724925)'),(SELECT MAX(elemred) + 1 FROM cat_tramos CAT),0,0,50.12,50.05,0,0,0,0,6.980530787094619,'','CNO LOS CAMALOTES',4027,'[{'descripcion':'NUEVO TRAMO POR IMPORTACION','fecha':'2022-09-05 16:54:53','usuario':'daniel'}]','INGRESO CON APLICACION DE CATATRO','2022-09-05 16:54:53','Proyectado',47,50,200,0,0,0,1,1,'PVC','Sin asignar');INSERT INTO cat_tramos (SHAPE, elemred, id_psig_aa, id_psig_ab, zarriba, zabajo, z_tapa_aa, z_tapa_ab, prof_aa, prof_ab, longitud,ccz, calle, via, historial,alta_fuente,alta_fecha,etapa,tiposec,tipotra,dim1,dim2,dim3,dim4,contrato,proyecto,material,accion) VALUES (GEOMFROMTEXT('LINESTRING(-56.31357712115264 -34.78801587682787,-56.313333524857164 -34.78812698959354)'),(SELECT MAX(elemred) + 1 FROM cat_tramos CAT),0,0,50.22,50.12,0,0,0,0,25.46639486571362,'','CNO LOS CAMALOTES',4027,'[{'descripcion':'NUEVO TRAMO POR IMPORTACION','fecha':'2022-09-05 16:54:53','usuario':'daniel'}]','INGRESO CON APLICACION DE CATATRO','2022-09-05 16:54:53','Proyectado',47,50,200,0,0,0,1,1,'PVC','Sin asignar');INSERT INTO cat_tramos (SHAPE, elemred, id_psig_aa, id_psig_ab, zarriba, zabajo, z_tapa_aa, z_tapa_ab, prof_aa, prof_ab, longitud,ccz, calle, via, historial,alta_fuente,alta_fecha,etapa,tiposec,tipotra,dim1,dim2,dim3,dim4,contrato,proyecto,material,accion) VALUES (GEOMFROMTEXT('LINESTRING(-56.31123342607263 -34.789105835565216,-56.310820022348295 -34.78947341062751)'),(SELECT MAX(elemred) + 1 FROM cat_tramos CAT),0,0,49.03,48.78,0,0,0,0,55.607126762961514,'','CNO LOS CAMALOTES',4027,'[{'descripcion':'NUEVO TRAMO POR IMPORTACION','fecha':'2022-09-05 16:54:53','usuario':'daniel'}]','INGRESO CON APLICACION DE CATATRO','2022-09-05 16:54:53','Proyectado',47,50,200,0,0,0,1,1,'PVC','Sin asignar');";


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


                    $sql = "SELECT MAX(id) as maxid, historial, X(Startpoint(SHAPE)) AS lon1, Y(Startpoint(SHAPE)) AS lat1,  X(Endpoint(SHAPE)) AS lon2, Y(Endpoint(SHAPE)) AS lat2 FROM conexiones WHERE 1";
                    $resultado = mysqli_query($con,$sql);
                    $row = mysqli_fetch_assoc($resultado);
                    $id_conexion = $row["maxid"];
                    $historia = $row["historial"];
                    $lat1 = $row["lat1"];
                    $lat2 = $row["lat2"];
                    $lon1 = $row["lon1"];
                    $lon2 = $row["lon2"];
                    $lat3 = ($lat1 + $lat2) / 2.0;
                    $lon3 = ($lon1 + $lon2) / 2.0;
                    $ccz3 = 'Sin Asignar';
                    $sqlx = "SELECT NOMBRE, ASTEXT(SHAPE) AS geom FROM geo_cczs WHERE CONTAINS(SHAPE, GEOMFROMTEXT('POINT(" . $lon3 . " " . $lat3 . ")',1))";
                    $resultadox = mysqli_query($con,$sqlx);
                    if ($resultadox != FALSE){
                        if (mysqli_num_rows($resultadox) == 1)
                        {
                            $rowx = mysqli_fetch_assoc($resultadox);
                            $ccz3=$rowx['NOMBRE'];
                        }  
                        elseif (mysqli_num_rows($resultadox) > 1)
                        {
                            while($rowx = mysqli_fetch_assoc($resultadox))
                            {
                                $geom=$rowx['geom'];
                                $esinterior = dl_geometria::dl_punto_en_poligono($lat3,$lon3,$geom);
                                if ($esinterior){$ccz3=$rowx['NOMBRE'];};
                            }
                        }
                    }

/*
                    $sqlz = "SELECT A.padron as padron FROM padrones A WHERE EXISTS(SELECT B.id FROM conexiones B WHERE B.padron = 0 AND B.id = " . $id_conexion . " AND INTERSECTS(A.GEOMETRIA,B.SHAPE)) LIMIT 1";
                    $resultadoz = mysqli_query($con,$sqlz);
                    if ($resultadoz != FALSE){
                            $rowz = mysqli_fetch_assoc($resultadoz);
                            $padron=$rowz['padron'];
                            if ($padron > 0)
                            {
                            $sql77 = "UPDATE conexiones SET padron = " . $padron  . " WHERE id = " . $id_conexion . " AND padron = 0";
                            }
                            $resultado77= mysqli_query($con,$sql77); 
                    }
*/

                   
                    $sql66 = "UPDATE conexiones SET ccz = '" . $ccz3  . "' WHERE id = " . $id_conexion;
                    $resultado66= mysqli_query($con,$sql66);   
                        $historia = str_replace("'","",$historia);
                        if($id_conexion > 0 && $historia != '')
                        {
                            $sql13 = "INSERT INTO log (id_conex,descripcion,tipo,usuario,fecha) VALUES (" . $id_conexion . ",'INGRESO POR IMPORTACION','IMPORTACION','" . $usuario . "','" . $fecha . "')";
                        $resultado13 = mysqli_query($con,$sql13);
                        }   

            

mysqli_close($con);
echo ($historial);
?>





