public virtual EstacionEntity GetEstacionesFromReaderDesfase(IDataReader reader)
{
EstacionEntity entity = null;
try
{
entity = new EstacionEntity();
entity.Numero = reader["numero"] == System.DBNull.Value ? 0 : (int)reader["numero"];
entity.Idred = 0;
entity.Nombre = reader["nombre"] == System.DBNull.Value ? "Null" : (string)reader["nombre"];
entity.EstadoId = reader["estadoid"] == System.DBNull.Value ? 0 : (int)reader["estadoid"];
entity.MunicipioId = 0;
entity.Productor = "";
entity.Latitud = reader["latitud"] == System.DBNull.Value ? 0 : Convert.ToSingle(reader["latitud"]);
entity.Longitud = reader["longitud"] == System.DBNull.Value ? 0 : Convert.ToSingle(reader["longitud"]);
entity.Altitud = reader["h"] == System.DBNull.Value? 0 : Convert.ToSingle(reader["h"]);
entity.Inicio = Convert.ToDateTime(null);
entity.InicioParseo = "";
}
catch (Exception ex)
{
throw new Exception("Error converting data...", ex);
}
return(entity);
}
public override List <EstacionEntity> GetEstacionTri(int idEstacion, string fecha)
{
string fch1 = "", fch2 = "";
DateTime fch = Convert.ToDateTime(fecha);
if (fch.Month == 1 || fch.Month == 2 || fch.Month == 3)
{
fch1 = "01/01/" + fch.Year;
fch2 = "01/04/" + fch.Year;
}
if (fch.Month == 4 || fch.Month == 5 || fch.Month == 6)
{
fch1 = "01/04/" + fch.Year;
fch2 = "01/07/" + fch.Year;
}
if (fch.Month == 7 || fch.Month == 8 || fch.Month == 9)
{
fch1 = "01/07/" + fch.Year;
fch2 = "01/10/" + fch.Year;
}
if (fch.Month == 10 || fch.Month == 11 || fch.Month == 12)
{
fch1 = "01/10/" + fch.Year;
fch2 = "01/01/" + fch.Year + 1;
}
EstacionEntity entityEstacion = null;
List <EstacionEntity> List = new List <EstacionEntity>();
SqlConnection connection = new SqlConnection(DataAccess.SqlGlobalConectionString);
SqlCommand command = new SqlCommand("select count(datepart(hour, fecha)) as FLAG, avg(datepart(year, fecha)) as Y,avg(datepart(month, fecha)) as M,avg(datepart(day, fecha)) as D, avg(PM1_1H) as PM1_1H, avg(PM2P5_1H) as PM2P5_1H,avg(PM10_1H) as PM10_1H from caborca_estacion_" + idEstacion + " where fecha between '" + fch1 + "' and '" + fch2 + "' and datepart(minute,fecha)='00' and PM1_1H!=0 and PM2P5_1H!=0 and PM10_1H!=0 and PM1_1H<1000 and PM2P5_1H<1000 and PM10_1H<1000 group by datepart(year, fecha), datepart(month, fecha), datepart(day, fecha) having count(datepart(hour, fecha)) > = 18 order by Y, M, D", connection);
command.CommandType = System.Data.CommandType.Text;
SqlDataReader reader = null;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
reader = command.ExecuteReader();
while (reader.Read())
{
entityEstacion = new EstacionEntity();
entityEstacion = GetEstacionesFromReader1(reader);
List.Add(entityEstacion);
}
}
catch (Exception ex)
{
throw new Exception("Error al consultar Estaciones TRI..." + ex.Message, ex);
}
finally
{
connection.Close();
reader.Close();
connection.Dispose();
}
return(List);
}
//metodo auxiliar virtual=reescribible
public virtual EstacionEntity GetEstacionesFromReader(IDataReader reader)
{
EstacionEntity entity = null;
try
{
entity = new EstacionEntity();
entity.Numero = reader["numero"] == System.DBNull.Value ? 0 : (int)reader["numero"];
entity.Idred = reader["idred"] == System.DBNull.Value ? 0 : (int)reader["idred"];
entity.Nombre = reader["nombre"] == System.DBNull.Value ? "Null" : (string)reader["nombre"];
entity.EstadoId = reader["estadoid"] == System.DBNull.Value ? 0 : (int)reader["estadoid"];
entity.MunicipioId = reader["municipioid"] == System.DBNull.Value ? 0:(int)reader["municipioid"];
entity.Productor = reader["productor"] == System.DBNull.Value ? "Null": (string)reader["productor"];
entity.Latitud = reader["latitud"] == System.DBNull.Value ? 0 : Convert.ToSingle(reader["latitud"]);
entity.Longitud = reader["longitud"] == System.DBNull.Value ? 0 : Convert.ToSingle(reader["longitud"]);
entity.Altitud = reader["altitud"] == System.DBNull.Value ? 0 : Convert.ToSingle(reader["altitud"]);
entity.Inicio = reader["inicio"] == System.DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(reader["inicio"]);
DateTime InParse = reader["inicio"] == System.DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(reader["inicio"]);
entity.InicioParseo = InParse.Day + "/" + InParse.Month + "/" + InParse.Year;
}
catch (Exception ex) {
throw new Exception("Error converting data...", ex);
}
return(entity);
}
public virtual EstacionEntity GetEstacionesFromReader1(IDataReader reader)
{
EstacionEntity entity = null;
try
{
entity = new EstacionEntity();
string Y = reader["Y"] == System.DBNull.Value ? "00" : Convert.ToString(reader["Y"]);
string M = reader["M"] == System.DBNull.Value ? "00" : Convert.ToString(reader["M"]);
string D = reader["D"] == System.DBNull.Value ? "00" : Convert.ToString(reader["D"]);
entity.Fecha = Convert.ToDateTime(D + "/" + M + "/" + Y);
entity.FechaParseo = D + "/" + M + "/" + Y;
entity.PM1_1M = 0;
entity.PM1_5M = 0;
entity.PM1_15M = 0;
entity.PM1_1H = reader["PM1_1H"] == System.DBNull.Value ? 0 : Convert.ToDecimal(reader["PM1_1H"]);
entity.PM2P5_1M = 0;
entity.PM2P5_5M = 0;
entity.PM2P5_15M = 0;
entity.PM2P5_1H = reader["PM2P5_1H"] == System.DBNull.Value ? 0 : Convert.ToDecimal(reader["PM2P5_1H"]);
entity.PM10_1M = 0;
entity.PM10_5M = 0;
entity.PM10_15M = 0;
entity.PM10_1H = reader["PM10_1H"] == System.DBNull.Value ? 0 : Convert.ToDecimal(reader["PM10_1H"]);
entity.FLAG = reader["FLAG"] == System.DBNull.Value ? 0 : (int)reader["FLAG"];
}
catch (Exception ex)
{
throw new Exception("Error converting data...", ex);
}
return(entity);
}
public virtual EstacionEntity GetEstacionesFromReader(IDataReader reader)
{
EstacionEntity entity = null;
try
{
entity = new EstacionEntity();
entity.Fecha = reader["fecha"] == System.DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(reader["fecha"]);
DateTime fecha = reader["fecha"] == System.DBNull.Value ? DateTime.MinValue : (DateTime)reader["fecha"];
entity.FechaParseo = fecha.Day + "/" + fecha.Month + "/" + fecha.Year;
entity.PM1_1M = reader["PM1_1M"] == System.DBNull.Value ? 0 : Convert.ToDecimal(reader["PM1_1M"]);
entity.PM1_5M = reader["PM1_5M"] == System.DBNull.Value ? 0 : Convert.ToDecimal(reader["PM1_5M"]);
entity.PM1_15M = reader["PM1_15M"] == System.DBNull.Value ? 0 : Convert.ToDecimal(reader["PM1_15M"]);
entity.PM1_1H = reader["PM1_1H"] == System.DBNull.Value ? 0 : Convert.ToDecimal(reader["PM1_1H"]);
entity.PM2P5_1M = reader["PM2P5_1M"] == System.DBNull.Value ? 0 : Convert.ToDecimal(reader["PM2P5_1M"]);
entity.PM2P5_5M = reader["PM2P5_5M"] == System.DBNull.Value ? 0 : Convert.ToDecimal(reader["PM2P5_5M"]);
entity.PM2P5_15M = reader["PM2P5_15M"] == System.DBNull.Value ? 0 : Convert.ToDecimal(reader["PM2P5_15M"]);
entity.PM2P5_1H = reader["PM2P5_1H"] == System.DBNull.Value ? 0 : Convert.ToDecimal(reader["PM2P5_1H"]);
entity.PM10_1M = reader["PM10_1M"] == System.DBNull.Value ? 0 : Convert.ToDecimal(reader["PM10_1M"]);
entity.PM10_5M = reader["PM10_5M"] == System.DBNull.Value ? 0 : Convert.ToDecimal(reader["PM10_5M"]);
entity.PM10_15M = reader["PM10_15M"] == System.DBNull.Value ? 0 : Convert.ToDecimal(reader["PM10_15M"]);
entity.PM10_1H = reader["PM10_1H"] == System.DBNull.Value ? 0 : Convert.ToDecimal(reader["PM10_1H"]);
entity.FLAG = 0;
}
catch (Exception ex) {
throw new Exception("Error converting data...", ex);
}
return(entity);
}
//Aun no es necesario utilizar...
public override List <EstacionEntity> GetAllByFecha(int idEstacion, string fecha)
{
DateTime fch = Convert.ToDateTime(fecha);
EstacionEntity entityEstacion = null;
List <EstacionEntity> List = new List <EstacionEntity>();
SqlConnection connection = new SqlConnection(DataAccess.SqlGlobalConectionString);
SqlCommand command = new SqlCommand(" select fecha,PM1_1M,PM1_15M, PM1_1H, PM2P5_1M, PM2P5_15M, PM2P5_1H, PM10_1M, PM10_15M, PM10_1H, temp, supply from caborca_estacion_" + idEstacion, connection);
command.CommandType = System.Data.CommandType.Text;
SqlDataReader reader = null;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
reader = command.ExecuteReader();
while (reader.Read())
{
entityEstacion = new EstacionEntity();
entityEstacion = GetEstacionesFromReader2(reader);
List.Add(entityEstacion);
}
}
catch (Exception ex)
{
throw new Exception("Error al consultar Estaciones..." + ex.Message, ex);
}
finally
{
connection.Close();
reader.Close();
connection.Dispose();
}
return(List);
}
public override List <EstacionEntity> GetEstacionAnual(int idEstacion, string fecha)
{
DateTime fch = Convert.ToDateTime(fecha);
EstacionEntity entityEstacion = null;
List <EstacionEntity> List = new List <EstacionEntity>();
SqlConnection connection = new SqlConnection(DataAccess.SqlGlobalConectionString);
SqlCommand command = new SqlCommand("select count(datepart(hour, fecha)) as FLAG, avg(datepart(year, fecha)) as Y,avg(datepart(month, fecha)) as M,count(datepart(day,fecha)) as D,avg(PM1_1H) as PM1_1H, avg(PM2P5_1H) as PM2P5_1H,avg(PM10_1H) as PM10_1H from caborca_estacion_" + idEstacion + " where fecha >='01/01/" + fch.Year + "' and fecha <'01/01/" + (fch.Year + 1) + "' and datepart(minute,fecha)='00' and PM1_1H!=0 and PM2P5_1H!=0 and PM10_1H!=0 and PM1_1H<1000 and PM2P5_1H<1000 and PM10_1H<1000 group by datepart(year, fecha), datepart(month, fecha) having count(datepart(hour, fecha)) > = 18 order by Y, M ", connection);
command.CommandType = System.Data.CommandType.Text;
SqlDataReader reader = null;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
reader = command.ExecuteReader();
while (reader.Read())
{
entityEstacion = new EstacionEntity();
entityEstacion = GetEstacionesFromReader2(reader);
List.Add(entityEstacion);
}
}
catch (Exception ex)
{
throw new Exception("Error al consultar Estaciones..." + ex.Message, ex);
}
finally
{
connection.Close();
reader.Close();
connection.Dispose();
}
return(List);
}
public override List <EstacionEntity> GetEstacion(int idEstacion, string fecha)
{
DateTime fch = Convert.ToDateTime(fecha);
EstacionEntity entityEstacion = null;
List <EstacionEntity> List = new List <EstacionEntity>();
SqlConnection connection = new SqlConnection(DataAccess.SqlGlobalConectionString);
SqlCommand command = new SqlCommand("select fecha,PM1_1M, PM1_5M, PM1_15M, PM1_1H, PM2P5_1M, PM2P5_5M,PM2P5_15M,PM2P5_1H,PM10_1M,PM10_5M,PM10_15M,PM10_1H from caborca_estacion_" + idEstacion + " where DATEPART(YEAR,fecha)=" + fch.Year + " and datepart(MONTH,fecha)=" + fch.Month + " and DATEPART(DAY,fecha)=" + fch.Day + " and datepart(minute,fecha)='00' and PM1_1H!=0 and PM2P5_1H!=0 and PM10_1H!=0 and PM1_1H<1000 and PM2P5_1H<1000 and PM10_1H<1000 ", connection);
command.CommandType = System.Data.CommandType.Text;
SqlDataReader reader = null;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
reader = command.ExecuteReader();
while (reader.Read())
{
entityEstacion = new EstacionEntity();
entityEstacion = GetEstacionesFromReader(reader);
List.Add(entityEstacion);
}
}
catch (Exception ex)
{
throw new Exception("Error al consultar Estaciones..." + ex.Message, ex);
}
finally
{
connection.Close();
reader.Close();
connection.Dispose();
}
return(List);
}
public override List <EstacionEntity> GetEstacionesDesfase(int edo)
{
EstacionEntity entityEstacion = null;
List <EstacionEntity> List = new List <EstacionEntity>();
SqlConnection connection = new SqlConnection(DataAccess.SqlGlobalConectionString);
SqlCommand command = new SqlCommand("select S.numero,S.nombre,S.latitud,S.longitud,S.estadoid,datediff (hh,max(T.fecha),getdate()) as h from estado" + edo + " T,estaciones S where S.numero=T.numero and S.activa=1 group by S.numero,s.nombre,S.latitud,S.longitud,S.estadoid order by S.numero ", connection);
command.CommandType = System.Data.CommandType.Text;
SqlDataReader reader = null;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
reader = command.ExecuteReader();
while (reader.Read())
{
entityEstacion = new EstacionEntity();
entityEstacion = GetEstacionesFromReaderDesfase(reader);
List.Add(entityEstacion);
}
}
catch (Exception ex)
{
throw new Exception("Error al consultar Desfase por Estacion..." + ex.Message, ex);
}
finally
{
connection.Close();
reader.Close();
connection.Dispose();
}
return(List);
}
public override List <EstacionEntity> GetEstacionesByIdMunicipio(int idmun)
{
EstacionEntity entityEstacion = null;
List <EstacionEntity> List = new List <EstacionEntity>();
SqlConnection connection = new SqlConnection(DataAccess.SqlGlobalConectionString);
SqlCommand command = new SqlCommand("select numero,idred,nombre,estadoid,municipioid,productor,latitud,longitud,altitud,inicio from estaciones where municipioid=" + idmun + " ", connection);
command.CommandType = System.Data.CommandType.Text;
SqlDataReader reader = null;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
reader = command.ExecuteReader();
while (reader.Read())
{
entityEstacion = new EstacionEntity();
entityEstacion = GetEstacionesFromReader(reader);
List.Add(entityEstacion);
}
}
catch (Exception ex)
{
throw new Exception("Error al consultar Estaciones por Municipio..." + ex.Message, ex);
}
finally
{
connection.Close();
reader.Close();
connection.Dispose();
}
return(List);
}
