/// <summary>
/// GET: api/AlarmReads/5
/// Accesses the database through Rest Api
/// </summary>
/// <returns>IHttpActionResult 'OK' with the alarm with the given ID if it exists or IHttpActionResult 'Not Found' if it does not exists</returns>
public IHttpActionResult GetAlarmById(int id)
{
AlarmComplete alarm = null;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM Alarms a join SensorsParameters sp on a.Sensor_Parameter_Id = sp.Read_Id WHERE Id=@id;";
cmd.Parameters.AddWithValue("@id", id);
cmd.Connection = conn;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
alarm = new AlarmComplete
(
(int)reader["Id"],
(int)reader["Sensor_Parameter_Id"],
(string)reader["Parameter"],
(int)reader["Read_Value"],
(string)reader["Location"],
(DateTime)reader["DateTime"],
(string)reader["Alarm_Message"]
);
}
reader.Close();
conn.Close();
}
catch (Exception ex)
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
throw ex;
}
if (alarm == null)
{
return(NotFound());
}
else
{
return(Ok(alarm));
}
}
public IEnumerable <AlarmComplete> GetAlarmsBetweenTwoDatesOnCity(string date1, string date2, string city)
{
List <AlarmComplete> lista = new List <AlarmComplete>();
AlarmComplete alarm = null;
try
{
conn.Open();
//11-30-2017
SqlCommand cmd = new SqlCommand();
//cmd.CommandText = "SELECT * FROM SensorsParameters WHERE CONVERT(varchar(15), DateTime, 112) = '20171130' AND Location = @city";
cmd.CommandText = "SELECT * FROM SensorsParameters s JOIN Alarms a ON s.Read_Id = a.Sensor_Parameter_Id WHERE CONVERT(varchar(15), s.DateTime, 112) BETWEEN @date1 AND @date2 AND s.Location = @city;";
cmd.Parameters.AddWithValue("@date1", date1);
cmd.Parameters.AddWithValue("@date2", date2);
cmd.Parameters.AddWithValue("@city", city);
cmd.Connection = conn;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
alarm = new AlarmComplete
(
(int)reader["Id"],
(int)reader["Sensor_Parameter_Id"],
(string)reader["Parameter"],
(int)reader["Read_Value"],
(string)reader["Location"],
(DateTime)reader["DateTime"],
(string)reader["Alarm_Message"]
);
lista.Add(alarm);
}
reader.Close();
conn.Close();
}
catch (Exception ex)
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
throw ex;
}
return(lista);
}
/// <summary>
/// GET: api/AlarmReads
/// Accesses the database through Rest Api
/// </summary>
/// <returns>IEnumerable with all the alarms</returns>
public IEnumerable <AlarmComplete> GetAllAlarms()
{
List <AlarmComplete> alarms = new List <AlarmComplete>();
AlarmComplete alarm = null;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM Alarms a join SensorsParameters sp on a.Sensor_Parameter_Id = sp.Read_Id;";
cmd.Connection = conn;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
alarm = new AlarmComplete
(
(int)reader["Id"],
(int)reader["Sensor_Parameter_Id"],
(string)reader["Parameter"],
(int)reader["Read_Value"],
(string)reader["Location"],
(DateTime)reader["DateTime"],
(string)reader["Alarm_Message"]
);
alarms.Add(alarm);
}
reader.Close();
conn.Close();
}
catch (Exception ex)
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
throw ex;
}
return(alarms);
}
