private static void ActualizaDireccion(int idGps, int idDireccion)
{
AuxSql.Exec(cmd =>
{
cmd.CommandText = $"UPDATE gps SET direccion= {idDireccion} WHERE id = {idGps}";
cmd.ExecuteNonQuery();
});
}
public static void InsertaError(byte[] datos)
{
AuxSql.Exec(cmd =>
{
AuxSql.AddParam(cmd, "datos", DbType.Binary, datos);
cmd.CommandText = "INSERT INTO gps_error (datos) VALUES (:datos)";
cmd.ExecuteNonQuery();
});
}
public static int ReadPointDir(double lng, double lat, int metros = 5)
{
int id = 0;
AuxSql.Exec(cmd =>
{
AuxSql.AddParam(cmd, "lat", DbType.Double, lat);
AuxSql.AddParam(cmd, "lng", DbType.Double, lng);
AuxSql.AddParam(cmd, "met", DbType.Int32, metros);
cmd.CommandText = "SELECT id"
+ " FROM direcciones"
+ " WHERE ST_Distance(posicion ,ST_SetSRID(ST_MakePoint(:lng, :lat), 4326)::geography) < :met"
+ " ORDER BY posicion <-> ST_SetSRID(ST_MakePoint(:lng, :lat), 4326)::geography LIMIT 1";
id = AuxSql.ReadInt(cmd);
});
return(id);
}
public void InsertaLectura()
{
AuxSql.Exec(cmd =>
{
AuxSql.AddParam(cmd, "imei", DbType.String, imei);
AuxSql.AddParam(cmd, "fecha", DbType.DateTime, fecha);
AuxSql.AddParam(cmd, "lng", DbType.Double, lng);
AuxSql.AddParam(cmd, "lat", DbType.Double, lat);
AuxSql.AddParam(cmd, "velocidad", DbType.Decimal, velocidad);
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO gps (imei, fecha, posicion, velocidad, datos) VALUES");
sb.AppendFormat(" (:imei, :fecha, ST_SetSRID(ST_MakePoint(:lng, :lat), 4326), :velocidad, '{0}'::jsonb)", datosJson);
sb.Append(" RETURNING id");
cmd.CommandText = sb.ToString();
int id = AuxSql.ReadInt(cmd);
this.LeeDireccion(id, lng, lat);
});
}
public static int Insert(Direccion dir)
{
int id = 0;
AuxSql.Exec(cmd =>
{
AuxSql.AddParam(cmd, "lat", DbType.Double, dir.Lat);
AuxSql.AddParam(cmd, "lng", DbType.Double, dir.Lng);
AuxSql.AddParam(cmd, "calle", DbType.String, dir.Calle);
AuxSql.AddParam(cmd, "numero", DbType.String, dir.Numero);
AuxSql.AddParam(cmd, "localidad", DbType.String, dir.Localidad);
AuxSql.AddParam(cmd, "provincia", DbType.String, dir.Provincia);
AuxSql.AddParam(cmd, "cpostal", DbType.String, dir.Cpostal);
AuxSql.AddParam(cmd, "pais", DbType.String, dir.Pais);
cmd.CommandText = "INSERT INTO direcciones (posicion, calle, numero, localidad, provincia, cpostal, pais)"
+ " VALUES"
+ " (ST_SetSRID(ST_MakePoint(:lng, :lat), 4326), :calle, :numero, :localidad, :provincia, :cpostal, :pais) RETURNING id";
id = AuxSql.ReadInt(cmd);
});
return(id);
}