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