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