public WeeklyConsumptionPrediction GetWeeklyConsumptionPredictionForGivenDate(SqlConnection sqlConnection, SqlTransaction sqlTransaction, string powerScout, DateTime startDate, string piServerName) { var query = "select top(1) * from WeeklyConsumptionPrediction where PowerScout = @param1 and CAST(Start_Time AS DATE) = @param2 and PiServerName = @PiServerName"; WeeklyConsumptionPrediction weeklyConsumptionPrediction = null; using (SqlCommand sqlCommand = new SqlCommand(query, sqlConnection, sqlTransaction)) { sqlCommand.Parameters.AddWithValue("@param1", powerScout); sqlCommand.Parameters.AddWithValue("@param2", startDate.Date.ToString("yyyy-MM-dd")); sqlCommand.Parameters.AddWithValue("@PiServerName", piServerName); using (SqlDataReader result = sqlCommand.ExecuteReader()) { while (result.Read()) { weeklyConsumptionPrediction = new WeeklyConsumptionPrediction(); weeklyConsumptionPrediction.Breaker_details = SqlTypeConverter.ToString(result["Breaker_details"]); weeklyConsumptionPrediction.Start_Time = SqlTypeConverter.ToDateTime(result["Start_Time"]); weeklyConsumptionPrediction.End_Time = SqlTypeConverter.ToDateTime(result["End_Time"]); weeklyConsumptionPrediction.Building = SqlTypeConverter.ToString(result["Building"]); weeklyConsumptionPrediction.PowerScout = SqlTypeConverter.ToString(result["PowerScout"]); weeklyConsumptionPrediction.Id = SqlTypeConverter.ToInt32(result["Id"]); weeklyConsumptionPrediction.Weekly_Predicted_KWH_System = SqlTypeConverter.ToDouble(result["Weekly_Predicted_KWH_System"]); } result.Close(); } } return(weeklyConsumptionPrediction); }
public void UpdateWeeklyConsumptionPrediction(WeeklyConsumptionPrediction weeklyConsumptionPrediction, SqlConnection sqlConnection, SqlTransaction sqlTransaction) { var query = "UPDATE [dbo].[WeeklyConsumptionPrediction] set [Start_Time] = @param1, [End_Time] = @param2, [Weekly_Predicted_KWH_System] = @param3 where [Id] = @param4"; using (SqlCommand sqlCommand = new SqlCommand(query, sqlConnection, sqlTransaction)) { sqlCommand.Parameters.AddWithValue("@param1", weeklyConsumptionPrediction.Start_Time); sqlCommand.Parameters.AddWithValue("@param2", weeklyConsumptionPrediction.End_Time); sqlCommand.Parameters.AddWithValue("@param3", weeklyConsumptionPrediction.Weekly_Predicted_KWH_System); sqlCommand.Parameters.AddWithValue("@param4", weeklyConsumptionPrediction.Id); sqlCommand.ExecuteNonQuery(); } }
private static void SaveWeeklyPrediction(WeeklyConsumptionPrediction weeklyConsumptionPrediction, SqlConnection sqlConnection, SqlTransaction sqlTransaction, string piServerName) { var meterService = new MeterService(); var weeklyPrediction = meterService.GetWeeklyConsumptionPredictionForGivenDate(sqlConnection, sqlTransaction, weeklyConsumptionPrediction.PowerScout, weeklyConsumptionPrediction.Start_Time, piServerName); if (weeklyPrediction == null) { meterService.AddWeeklyConsumptionPrediction(weeklyConsumptionPrediction, sqlConnection, sqlTransaction, piServerName); } else { meterService.UpdateWeeklyConsumptionPrediction(weeklyConsumptionPrediction, sqlConnection, sqlTransaction); } }
public void AddWeeklyConsumptionPrediction(WeeklyConsumptionPrediction weeklyConsumptionPrediction, SqlConnection sqlConnection, SqlTransaction sqlTransaction, string piServerName) { var query = "INSERT INTO [dbo].[WeeklyConsumptionPrediction]([PowerScout],[Breaker_details],[Start_Time],[End_Time],[Weekly_Predicted_KWH_System],[Building], [PiServerName]) VALUES(@param1, @param2, @param3, @param4, @param5,@param6, @PiServerName)"; using (SqlCommand sqlCommand = new SqlCommand(query, sqlConnection, sqlTransaction)) { sqlCommand.Parameters.AddWithValue("@param1", weeklyConsumptionPrediction.PowerScout); sqlCommand.Parameters.AddWithValue("@param2", weeklyConsumptionPrediction.Breaker_details); sqlCommand.Parameters.AddWithValue("@param3", weeklyConsumptionPrediction.Start_Time); sqlCommand.Parameters.AddWithValue("@param4", weeklyConsumptionPrediction.End_Time); sqlCommand.Parameters.AddWithValue("@param5", weeklyConsumptionPrediction.Weekly_Predicted_KWH_System); sqlCommand.Parameters.AddWithValue("@param6", weeklyConsumptionPrediction.Building ?? string.Empty); sqlCommand.Parameters.AddWithValue("@PiServerName", piServerName); sqlCommand.ExecuteNonQuery(); } }
/// <summary> /// Process current day consumption and predict current week electricity consumption. /// </summary> /// <param name="timerInfo">The timer information.</param> /// <returns>A <see cref="Task"/> representing the asynchronous operation.</returns> public static async Task ProcessWeeklyConsumptionPrediction([TimerTrigger("0 0/50 * * * *")] TimerInfo timerInfo) { try { var meterService = new MeterService(); using (SqlConnection sqlConnection = new SqlConnection(ConfigurationSettings.DbConnectionString)) { sqlConnection.Open(); var piServers = meterService.GetPiServerList(sqlConnection); foreach (var piServer in piServers) { try { DateTime today = piServer.PiServerCurrentDateTime.AddDays(-1); int prev = (int)(today.DayOfWeek - 1); int next = (int)(7 - today.DayOfWeek); var rowModified = 0; using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction()) { var powerScouts = meterService.GetMeterListByPiServer(sqlConnection, sqlTransaction, piServer.PiServerName); foreach (var powerScout in powerScouts) { double weeklyPredictedKWH = 0; var dailyConsumptionDetails = meterService.GetTodaysDailyConsumptionDetails(sqlConnection, sqlTransaction, powerScout, piServer); if (dailyConsumptionDetails != null) { DateTime initialDate = dailyConsumptionDetails.Timestamp; if (prev > 0) { for (int i = 1; i <= prev; i++) { dailyConsumptionDetails.Timestamp = today.AddDays(-i); var response = await meterService.GetAnomalyPrediction(dailyConsumptionDetails, sqlConnection, sqlTransaction, piServer.PiServerName, false); if (response != null) { List <string> columnNames = response.Results.Output1.Value.ColumnNames.ToList(); weeklyPredictedKWH += Convert.ToDouble(response.Results.Output1.Value.Values[0][columnNames.IndexOf("Scored Labels")]); } } } for (int i = 0; i <= next; i++) { dailyConsumptionDetails.Timestamp = today.AddDays(i); var response = await meterService.GetAnomalyPrediction(dailyConsumptionDetails, sqlConnection, sqlTransaction, piServer.PiServerName, false); if (response != null) { List <string> columnNames = response.Results.Output1.Value.ColumnNames.ToList(); weeklyPredictedKWH += Convert.ToDouble(response.Results.Output1.Value.Values[0][columnNames.IndexOf("Scored Labels")]); } } if (weeklyPredictedKWH != 0) { var weeklyConsumptionPrediction = new WeeklyConsumptionPrediction(); weeklyConsumptionPrediction.Start_Time = today.AddDays(-prev).Date; weeklyConsumptionPrediction.End_Time = today.AddDays(next).Date; weeklyConsumptionPrediction.PowerScout = powerScout; weeklyConsumptionPrediction.Breaker_details = dailyConsumptionDetails.Breaker_details; weeklyConsumptionPrediction.Weekly_Predicted_KWH_System = weeklyPredictedKWH; weeklyConsumptionPrediction.Building = dailyConsumptionDetails.Building; SaveWeeklyPrediction(weeklyConsumptionPrediction, sqlConnection, sqlTransaction, piServer.PiServerName); rowModified++; } } } sqlTransaction.Commit(); } Console.WriteLine("WeeklyConsumptionPredictionJob Insert/Update - PiServer - {0}, Rows {1}", piServer.PiServerName, rowModified); } catch (Exception e) { var errorMsg = string.Format("WeeklyConsumptionPredictionJob Error : Weekly consumption failed for PiServer - {0}", piServer.PiServerName); Console.WriteLine(errorMsg); Console.WriteLine("Error Message - {0}", e.Message); Console.WriteLine("StackTrace - {0}", e.StackTrace); ApplicationInsightsLogger.LogException(e, new Dictionary <string, string> { { "Job Error Message", errorMsg } }); } } sqlConnection.Close(); } } catch (Exception e) { ApplicationInsightsLogger.LogException(e); throw; } }