private static List <StatisticDay> getList(string sql)
{
OdbcConnection connection = DBManager.GetOdbcConnection();
connection.Open();
OdbcCommand command = new OdbcCommand(sql, connection);
OdbcDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
if (reader.HasRows)
{
List <StatisticDay> list = StatisticDay.getList(reader);
connection.Close();
return(list);
}
connection.Close();
return(null);
}
public IEnumerable <StatisticDay> GetDailySensorMaxMinAvgOnParameterBetweenDatesOnCity(string parameter, string date1, string date2, string city)
{
List <StatisticDay> lista = new List <StatisticDay>();
StatisticDay statistic = null;
try
{
conn.Open();
//20171130
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT DATEPART(DAY, DateTime) AS DAY, Parameter, Location, MIN(Read_Value) AS MIN, MAX(Read_Value) AS MAX, AVG(Read_Value) AS AVG FROM SensorsParameters WHERE CONVERT(varchar(15), DateTime, 112) BETWEEN @date1 AND @date2 AND Location = @city AND Parameter = @parameter GROUP BY DATEPART(DAY, DateTime), Parameter, Location;";
cmd.Parameters.AddWithValue("@parameter", parameter);
cmd.Parameters.AddWithValue("@city", city);
cmd.Parameters.AddWithValue("@date1", date1);
cmd.Parameters.AddWithValue("@date2", date2);
cmd.Connection = conn;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
statistic = new StatisticDay(
(int)reader["MIN"],
(int)reader["MAX"],
(int)reader["AVG"],
(string)reader["Location"],
(string)reader["Parameter"],
(int)reader["DAY"]
);
lista.Add(statistic);
}
reader.Close();
conn.Close();
}
catch (Exception ex)
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
throw ex;
}
return(lista);
}
