protected void Page_Load(object sender, EventArgs e)
{
thermostatId = Convert.ToInt32(Request["thermostatId"]);
TimezoneDifference = Convert.ToInt32(Request["TimezoneDifference"]);
if (Request["startDate"] != null)
{
startDate = Convert.ToDateTime(Request["startDate"]).AddHours(TimezoneDifference);
}
if (Request["endDate"] != null)
{
endDate = Convert.ToDateTime(Request["endDate"]).AddDays(1).AddSeconds(-1).AddHours(TimezoneDifference);
}
if (Request["prevStartDate"] != null)
{
prevStartDate = Convert.ToDateTime(Request["prevStartDate"]).AddHours(TimezoneDifference);
}
if (Request["prevEndDate"] != null)
{
prevEndDate = Convert.ToDateTime(Request["prevEndDate"]).AddDays(1).AddSeconds(-1).AddHours(TimezoneDifference);
}
ThermostatMonitorLib.Snapshots snapshots = ThermostatMonitorLib.Snapshots.LoadRange(thermostatId, startDate, endDate);
DataTable dt = snapshots.GetHourlyStats(TimezoneDifference);
if (Request["prevStartDate"] != null)
{
AppendHistorical(dt);
}
Output(dt);
}
//Loads a wider range and trims off that seconds from the first and last snapshots to match the date range provided.
public static Snapshots LoadRange(int thermostatId, DateTime startDate, DateTime endDate)
{
Snapshots snapshots = Snapshots.LoadSnapshots("SELECT * FROM Snapshots WHERE thermostat_id=@ThermostatId and start_time BETWEEN @StartDate AND @EndDate ORDER BY start_time", CommandType.Text, new MySqlParameter[] {
new MySqlParameter("@ThermostatId", thermostatId),
new MySqlParameter("@StartDate", startDate.AddDays(-1)),
new MySqlParameter("@EndDate", endDate)
});
//filter through them and chop off seconds before and after the cycle;
Snapshots result = new Snapshots();
foreach (Snapshot existing in snapshots)
{
DateTime endTime = existing.StartTime.AddSeconds(existing.Seconds);
if (endTime > startDate)
{
Snapshot snapshot = existing;
if (snapshot.StartTime < startDate)
{
snapshot.Seconds = snapshot.Seconds - (int)new TimeSpan(startDate.Ticks - snapshot.StartTime.Ticks).TotalSeconds;
snapshot.StartTime = startDate;
}
if (endTime > endDate)
{
snapshot.Seconds = snapshot.Seconds - (int)new TimeSpan(endTime.Ticks - endDate.Ticks).TotalSeconds;
}
result.Add(snapshot);
}
}
return result;
}
//Loads a wider range and trims off that seconds from the first and last snapshots to match the date range provided.
public static Snapshots LoadRange(int thermostatId, DateTime startDate, DateTime endDate)
{
Snapshots snapshots = Snapshots.LoadSnapshots("SELECT * FROM Snapshots WHERE thermostat_id=@ThermostatId and start_time BETWEEN @StartDate AND @EndDate ORDER BY start_time", CommandType.Text, new MySqlParameter[] {
new MySqlParameter("@ThermostatId", thermostatId),
new MySqlParameter("@StartDate", startDate.AddDays(-1)),
new MySqlParameter("@EndDate", endDate)
});
//filter through them and chop off seconds before and after the cycle;
Snapshots result = new Snapshots();
foreach (Snapshot existing in snapshots)
{
DateTime endTime = existing.StartTime.AddSeconds(existing.Seconds);
if (endTime > startDate)
{
Snapshot snapshot = existing;
if (snapshot.StartTime < startDate)
{
snapshot.Seconds = snapshot.Seconds - (int)new TimeSpan(startDate.Ticks - snapshot.StartTime.Ticks).TotalSeconds;
snapshot.StartTime = startDate;
}
if (endTime > endDate)
{
snapshot.Seconds = snapshot.Seconds - (int)new TimeSpan(endTime.Ticks - endDate.Ticks).TotalSeconds;
}
result.Add(snapshot);
}
}
return(result);
}
public static Snapshots ConvertFromDT(DataTable dt)
{
Snapshots result = new Snapshots();
foreach (DataRow row in dt.Rows)
{
result.Add(Snapshot.GetSnapshot(row));
}
return(result);
}
public Snapshots Sort(string column, bool desc)
{
var sortedList = desc ? this.OrderByDescending(x => x.GetPropertyValue(column)) : this.OrderBy(x => x.GetPropertyValue(column));
Snapshots result = new Snapshots();
foreach (var i in sortedList)
{
result.Add((Snapshot)i);
}
return(result);
}
public static Snapshot LoadLastSnapshot(int thermostatId)
{
Snapshots result = Snapshots.LoadSnapshots("SELECT * FROM snapshots where thermostat_id=@ThermostatId and start_time = (select MAX(start_time) from snapshots where thermostat_id=@ThermostatId)", CommandType.Text, new MySqlParameter[] { new MySqlParameter("@ThermostatId", thermostatId) });
if (result.Count > 0)
{
return(result[0]);
}
else
{
return(null);
}
}
private void AppendHistorical(DataTable outputDt)
{
includeHistorical = true;
outputDt.Columns.Add("PrevCool", typeof(double));
outputDt.Columns.Add("PrevHeat", typeof(double));
ThermostatMonitorLib.Snapshots snapshots = ThermostatMonitorLib.Snapshots.LoadRange(thermostatId, prevStartDate, prevEndDate);
DataTable dt = snapshots.GetHourlyStats(TimezoneDifference);
foreach (DataRow row in dt.Rows)
{
int hour = Convert.ToInt32(row["Hour"]);
double cool = Convert.ToDouble(row["Cool"]);
double heat = Convert.ToDouble(row["Heat"]);
DataRow existingRow = GetRowByHour(outputDt, hour);
existingRow["PrevCool"] = cool;
existingRow["PrevHeat"] = heat;
}
}
public static Snapshots LoadSnapshotsByThermostatId(int thermostatId)
{
return(Snapshots.LoadSnapshots("snapshots_load_by_thermostat_id", CommandType.StoredProcedure, new MySqlParameter[] { new MySqlParameter("@thermostat_id", thermostatId) }));
}
public static Snapshots LoadAllSnapshots()
{
return(Snapshots.LoadSnapshots("snapshots_load_all", CommandType.StoredProcedure, null));
}
public static Snapshots LoadSnapshots(string sql, System.Data.CommandType commandType, MySqlParameter[] parameters)
{
return(Snapshots.ConvertFromDT(Utils.ExecuteQuery(sql, commandType, parameters)));
}
public static Snapshots LoadSnapshotsByThermostatId(System.Int32 thermostatId)
{
return(Snapshots.LoadSnapshots("LoadSnapshotsByThermostatId", CommandType.StoredProcedure, new SqlParameter[] { new SqlParameter("@ThermostatId", thermostatId) }));
}
