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); }