protected override void OnStart(string[] args)
{
AuxSystemLog.AppName = System.Reflection.Assembly.GetExecutingAssembly().GetName().Name;
bool conexionOk = AuxSql.TestServerConn();
if (!conexionOk)
{
string textoError = "Error conectando con la base de datos";
if (AuxSql.Excepcion != null)
{
textoError = $"Error conectando con la base de datos\r\n{AuxSql.Excepcion.Message}";
}
AuxSystemLog.Error(textoError);
throw new Exception(textoError);
}
this.listener = new TcpListener(IP_ADDRESS, SERVICE_PORT);
if (listener == null)
{
string textoError = $"Error creando TcpListener en {IP_ADDRESS}:{SERVICE_PORT}";
AuxSystemLog.Error(textoError);
throw new Exception(textoError);
}
this.hilo = new Thread(Execute)
{
IsBackground = true,
Name = "GPSService thread"
};
this.hilo.Start();
}
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();
});
}
/// <summary>
/// Create a DbCommand object and execute the function it receives as a parameter.
/// It's just a container to simplify the code
/// </summary>
/// <param name="fnProccess">Function that will be executed. Receives the DbCommand object as parameter</param>
public static void Exec(Action <DbCommand> fnProccess)
{
using (DbConnection conn = AuxSql.DuplicateConn())
{
using (DbCommand cmd = conn.CreateCommand())
{
fnProccess(cmd);
}
}
}
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);
}