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