public List <Reading> GetReadingsBetween(short sensorId, long timestamp1, long timestamp2)
{
List <Reading> readings = new List <Reading>();
List <string> sensorTypes = GetSensorTypes();
if (sensorTypes == null)
{
return(null);
}
string attributes = "t1.Reading, ";
string from = $"{ sensorTypes[0] } t1";
string where = "t1.Status = 1";
int counter = 1;
foreach (string type in sensorTypes)
{
if (counter == 1)
{
counter++;
continue;
}
from += $" JOIN { type } t{ counter } ON (t{ counter - 1 }.Sensor_Id = t{ counter }.Sensor_Id AND t{ counter - 1 }.Timestamp = t{ counter }.Timestamp)";
attributes += $"t{ counter }.Reading, ";
where += $" AND t{ counter }.Status = 1";
counter++;
}
string select = $"SELECT t1.Sensor_Id, { attributes } t1.Timestamp FROM { from } WHERE { where } AND t1.Sensor_Id = @sensorId AND (t1.Timestamp > @timestamp1 AND t1.Timestamp < @timestamp2)";
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
SqlCommand cmd = new SqlCommand(select, sqlConnection);
cmd.Parameters.AddWithValue("@sensorId", sensorId);
cmd.Parameters.AddWithValue("@timestamp1", timestamp1);
cmd.Parameters.AddWithValue("@timestamp2", timestamp2);
using (SqlDataReader reader = cmd.ExecuteReader())
{
int read_counter = 0;
while (reader.Read())
{
read_counter++;
short _sensorId = reader.GetInt16(0);
Dictionary <string, string> _readings = new Dictionary <string, string>();
counter = 1;
foreach (string type in sensorTypes)
{
float _float = (float)reader.GetSqlDouble(counter++).Value;
_readings.Add(type, _float.ToString());
}
long _timestamp = reader.GetInt64(counter++);
Reading reading = new Reading
{
SensorId = _sensorId,
Readings = _readings,
Timestamp = _timestamp,
Status = ValueType.VALID
};
readings.Add(reading);
if (read_counter >= 164)
{
break;
}
}
}
}
return(readings);
}
