public List <AlarmLog> getDailyAlarmsByCity(string cityName, DateTime dateTime) { string query = string.Format(@" SELECT al.Description, al.DateTime, s.Param, s.Value FROM {0} al JOIN {1} s ON al.SensorDataUID = s.SensorDataUID WHERE CONVERT(varchar, al.DateTime, 23) = @date AND CityId = @cityId ORDER BY 2 DESC", DatabaseTableConstant.tableAlarms, DatabaseTableConstant.tableSensorData); List <AlarmLog> dataAlarms = new List <AlarmLog>(); string date = dateTime.ToString("yyyy-MM-dd"); if (dateTime == null) { return(null); } int cityId = fetchCityIdFromName(cityName); if (cityId == -1) { return(null); } using (connection) { SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("date", date); command.Parameters.AddWithValue("cityId", cityId); try { connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { AlarmLog alarm = new AlarmLog { Description = reader["Description"] + "", DateTime = (DateTime)reader["DateTime"], Parameter = reader["Param"] + "", Value = int.Parse(reader["Value"] + "") }; dataAlarms.Add(alarm); } } } catch (Exception e) { Debug.WriteLine((e.ToString())); } return(dataAlarms); } }
public List <AlarmLog> getDailyAlarmsByCityBetweenDates(string cityName, DateTime startDate, DateTime endDate) { string query; bool allcity = false; int cityId = -1; if (startDate == null || endDate == null || startDate > endDate) { return(null); } if (cityName == null) { query = string.Format(@" SELECT al.Description, al.DateTime, s.Param, s.Value, City_Name FROM {0} al JOIN {1} s ON al.SensorDataUID = s.SensorDataUID JOIN {2} c ON c.Id = s.CityId WHERE al.DateTime >= @startDate AND al.DateTime <= @endDate ORDER BY 2 DESC", DatabaseTableConstant.tableAlarms, DatabaseTableConstant.tableSensorData, DatabaseTableConstant.tableCity); allcity = true; } else { query = string.Format(@" SELECT al.Description, al.DateTime, s.Param, s.Value, City_Name FROM {0} al JOIN {1} s ON al.SensorDataUID = s.SensorDataUID JOIN {2} c ON c.Id = s.CityId WHERE CityId = @cityId AND al.DateTime >= @startDate AND al.DateTime <= @endDate ORDER BY 2 DESC", DatabaseTableConstant.tableAlarms, DatabaseTableConstant.tableSensorData, DatabaseTableConstant.tableCity); cityId = fetchCityIdFromName(cityName); if (cityId == -1) { return(null); } } List <AlarmLog> dataAlarms = new List <AlarmLog>(); using (connection) { SqlCommand command = new SqlCommand(query, connection); if (!allcity) { command.Parameters.AddWithValue("cityId", cityId); } command.Parameters.AddWithValue("startDate", startDate.ToString("yyyy-MM-dd 00:00:00")); command.Parameters.AddWithValue("endDate", endDate.ToString("yyyy-MM-dd 23:59:59")); try { connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { AlarmLog alarm = new AlarmLog { City = reader["City_Name"] + "", Description = reader["Description"] + "", DateTime = (DateTime)reader["DateTime"], Parameter = reader["Param"] + "", Value = int.Parse(reader["Value"] + "") }; dataAlarms.Add(alarm); } } } catch (Exception e) { Debug.WriteLine((e.ToString())); } return(dataAlarms); } }