public MeterList(ModelContext modelContext, MeterService meterService, DataService dataService)
{
this.RegisterRoutes(nameof(MeterList), new List <RouteTemplate>
{
new RouteTemplate(nameof(MeterDashboard))
{
UrlPattern = $"{nameof(MeterDashboard)}(/:id)"
},
});
this.OnRouted((sender, e) => System.Diagnostics.Debug.WriteLine(e.From));
var objLock = new object();
async Task <MeterListRow[]> GetRows(Meter meter)
{
Debug.WriteLine($">> GetRows {meter.MeterId}");
var latestState = await meterService.GetLatestContentInstanceAsync <State>(meter.MeterUrl + modelContext.App.StateContainer);
var stateStr = latestState != null?latestState.Valve.Description() : "N/A";
var route = this.GetRoute(nameof(MeterDashboard), $"{nameof(MeterDashboard)}/{meter.MeterId}");
//Subscribe to State to update a row and push updates to views
meter.State.Subscribe(newState =>
{
Debug.WriteLine($">> UpdateList {meter.MeterId}");
this.UpdateList("Meters", new MeterListRow(meter.MeterId, newState, this.GetRoute(nameof(MeterDashboard), $"{nameof(MeterDashboard)}/{meter.MeterId}")));
PushUpdates();
Debug.WriteLine($"<< UpdateList {meter.MeterId}");
});
Debug.WriteLine($"<< GetRows {meter.MeterId}");
lock (objLock)
{
var meters = new List <MeterListRow>(Get <MeterListRow[]>("Meters") ?? new MeterListRow[] { });
meters.Add(new MeterListRow(meter.MeterId, stateStr, route));
return(meters.ToArray());
}
}
var meterListRows = meterService.Meters.Select(l => Observable.FromAsync(() => GetRows(l)));
AddProperty <MeterListRow[]>("Meters")
.SubscribeTo(Observable.Concat(meterListRows))
.SubscribedBy(AddInternalProperty <bool>("Update"), _ =>
{
PushUpdates();
return(true);
});
}
public void ProcessFile()
{
_repo.Setup(x => x.GetCustomers())
.Returns(Customers())
.Verifiable();
//todo: Mock File reading
var _meterService = new MeterService(_repo.Object);
var errors = _meterService.ProcessFileAsync("filePath");
Assert.AreEqual(1, errors.Count);
}
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);
}
}
//list all meters and gateways
public void loadAll()
{
GateWayService gt = new GateWayService(new List <TextBox>());
List <Gateway> listGateWay = new List <Gateway>();
listGateWay = gt.ListAllGateways();
this.GridView_Gateways.DataSource = listGateWay;
this.GridView_Gateways.DataBind();
MeterService mt = new MeterService(new List <TextBox>());
List <Meter> listMeter = new List <Meter>();
listMeter = mt.ListAllMeters();
this.GridView_Meters.DataSource = listMeter;
this.GridView_Meters.DataBind();
}
public MetersControllerTest()
{
var collection = new List <Meter>
{
new Meter {
MeterId = "1"
}
};
var meterDbSet = this.CreateDbSet(collection.AsQueryable());
var stubContext = new Mock <MeterDbContext>();
stubContext.Setup(o => o.Meters).Returns(meterDbSet);
var meterService = new MeterService(stubContext.Object);
this.meterController = new MeterController(meterService);
}
//to insert new meter and gateway
protected void add_Click(object sender, EventArgs e)
{
if (this.options.SelectedItem.Text.Equals("Gateway") && !this.serialNumerBox.Text.Equals("") && !this.ipBox.Text.Equals(""))
{
List <TextBox> listText = new List <TextBox>();
listText.Add(this.serialNumerBox);
listText.Add(this.brandBox);
listText.Add(this.modelBox);
listText.Add(this.ipBox);
listText.Add(this.portBox);
GateWayService ms = new GateWayService(listText);
if (!ms.CheckDuplicate())
{
ms.RegisterGateWay();
cleanForm();
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "showInfo", "alert('This gateway has been saved');", true);
}
else
{
//alert alreay exists
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "showError", "alert('This gateway already exists');", true);
}
}
else if (!this.serialNumerBox.Text.Equals(""))
{
if (this.options.SelectedItem.Text.Equals("Light meter"))
{
List <TextBox> listText = new List <TextBox>();
listText.Add(this.serialNumerBox);
listText.Add(this.brandBox);
listText.Add(this.modelBox);
MeterService ms = new MeterService(listText);
if (!ms.CheckDuplicate("Light-"))
{
ms.RegisterMeter("Light-");
cleanForm();
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "showInfo", "alert('This meter has been saved');", true);
}
else
{
//alert alreay exists
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "showError", "alert('This meter already exists');", true);
}
}
else if (this.options.SelectedItem.Text.Equals("Water meter"))
{
List <TextBox> listText = new List <TextBox>();
listText.Add(this.serialNumerBox);
listText.Add(this.brandBox);
listText.Add(this.modelBox);
MeterService ms = new MeterService(listText);
if (!ms.CheckDuplicate("Water-"))
{
ms.RegisterMeter("Water-");
cleanForm();
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "showInfo", "alert('This meter has been saved');", true);
}
else
{
//alert alreay exists
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "showError", "alert('This meter already exists');", true);
}
}
}
else
{
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "showError", "alert('Someone data has not been writtens');", true);
}
}
/// <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;
}
}
/// <summary>
/// Detects the anomalies and generate alert.
/// </summary>
/// <param name="timerInfo">The timer information.</param>
/// <returns>
/// A <see cref="Task" /> representing the asynchronous operation.
/// </returns>
public static async Task ProcessAnomalyDetection([TimerTrigger("0 0 0 * * *")] TimerInfo timerInfo)
{
try
{
var alertService = new AlertService();
var meterService = new MeterService();
var requestModel = new AnomalyServiceRequest();
using (SqlConnection sqlConnection = new SqlConnection(ConfigurationSettings.DbConnectionString))
{
sqlConnection.Open();
var piServers = meterService.GetPiServerList(sqlConnection);
foreach (var piServer in piServers)
{
try
{
DateTime anomalyDetectionStartTime = piServer.PiServerCurrentDateTime.AddDays(-1).Date;
DateTime anomalyDetectionEndTime = anomalyDetectionStartTime.AddHours(24).AddSeconds(-1);
using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
{
var query = "SELECT PowerScout, Temperature, Timestamp, Visibility, kW_System, days, Breaker_details FROM LiveData WHERE Timestamp > @startTime AND Timestamp <= @endTime and PiServerName = @PiServerName ORDER BY Timestamp";
requestModel.GlobalParameters = new GlobalParameter();
Inputs inputs = new Inputs();
Input1 input = new Input1()
{
ColumnNames = new List <string> {
"PowerScout", "Temperature", "Timestamp", "Visibility", "kW_System", "days", "Breaker_details"
}
};
List <List <string> > values = new List <List <string> >();
using (SqlCommand selectDataCommand = new SqlCommand(query, sqlConnection, sqlTransaction))
{
selectDataCommand.Parameters.AddWithValue("@startTime", anomalyDetectionStartTime.ToString(Constants.DATE_TIME_FORMAT));
selectDataCommand.Parameters.AddWithValue("@endTime", anomalyDetectionEndTime.ToString(Constants.DATE_TIME_FORMAT));
selectDataCommand.Parameters.AddWithValue("@PiServerName", piServer.PiServerName);
using (SqlDataReader result = selectDataCommand.ExecuteReader())
{
List <string> rowValues = null;
while (result.Read())
{
rowValues = new List <string>();
rowValues.Add(SqlTypeConverter.ToString(result["PowerScout"]));
rowValues.Add(SqlTypeConverter.ToString(result["Temperature"]));
rowValues.Add(SqlTypeConverter.ToDateTime(result["Timestamp"]).ToString(Constants.DATE_TIME_FORMAT));
rowValues.Add(SqlTypeConverter.ToString(result["Visibility"]));
rowValues.Add(SqlTypeConverter.ToString(result["kW_System"]));
rowValues.Add(SqlTypeConverter.ToString(result["days"]));
rowValues.Add(SqlTypeConverter.ToString(result["Breaker_details"]));
values.Add(rowValues);
}
result.Close();
}
}
if (values.Count > 0)
{
input.Values = values;
inputs.input1 = input;
requestModel.Inputs = inputs;
var azureMLConfigurationService = new AzureMLConfigurationService();
var anomalyConfig = azureMLConfigurationService.GetAnomalyConfigData(sqlConnection, sqlTransaction);
var responseModel = await AzureMLClient.CallAzureMLAsync(requestModel, anomalyConfig.AzureMlAnomalyDetectionApiUrl, anomalyConfig.AzureMlAnomalyDetectionApiKey);
if (responseModel != null)
{
AddAnomalyToDatabase(sqlConnection, sqlTransaction, responseModel, piServer.PiServerName, anomalyDetectionStartTime, anomalyDetectionEndTime, alertService);
}
}
sqlTransaction.Commit();
}
}
catch (Exception e)
{
var errorMsg = string.Format("AnomalyDetectionJob Error : Anomaly detection failed for pi server - {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;
}
}
/// <summary>
/// Process electricity consumption to generate recommendation for power saving.
/// </summary>
/// <param name="timerInfo">The timer information.</param>
public static void ProcessConsumptionAlert([TimerTrigger("0 0 0 * * *")] 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 prevWeekDay = piServer.PiServerCurrentDateTime.Date.AddDays(-1);
DateTime lastWeekendSunday = prevWeekDay.AddDays(-(int)prevWeekDay.DayOfWeek);
// if today is weekend(saturday) move back to Friday.
if (prevWeekDay.DayOfWeek == DayOfWeek.Saturday)
{
prevWeekDay = prevWeekDay.AddDays(-1);
lastWeekendSunday = prevWeekDay.AddDays(-5);
}
// if today is weekend(sunday) move back to Friday.
else if (prevWeekDay.DayOfWeek == DayOfWeek.Sunday)
{
prevWeekDay = prevWeekDay.AddDays(-2);
lastWeekendSunday = prevWeekDay.AddDays(-5);
}
// Weekend Logic
DateTime lastWeekendSaturday = lastWeekendSunday.AddDays(-1);
List <DateTime> lastWeekendDays = new List <DateTime>();
lastWeekendDays.Add(lastWeekendSaturday);
lastWeekendDays.Add(lastWeekendSunday);
using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
{
long nonWorkingHourWeekendConsumption = 0;
foreach (var day in lastWeekendDays)
{
DateTime startdayTime = day.AddHours(5);
DateTime endDayTime = startdayTime.AddHours(24).AddSeconds(-1);
DayConsumptionInfo info = GetConsumption(startdayTime, endDayTime, sqlConnection, sqlTransaction, piServer.PiServerName);
nonWorkingHourWeekendConsumption += info.NonWorkingHourConsumption;
}
if (nonWorkingHourWeekendConsumption > 0)
{
long weekendConsumption = (nonWorkingHourWeekendConsumption / 2) * 52; // here 52 is for weeks in a year
GenerateConsumptionAlertAndNotification(sqlConnection, sqlTransaction, weekendConsumption, piServer);
}
// Previous Week Day Logic
DateTime startTime = prevWeekDay.AddHours(5);
DateTime endTime = startTime.AddHours(24).AddSeconds(-1);
DayConsumptionInfo weekDayInfo = GetConsumption(startTime, endTime, sqlConnection, sqlTransaction, piServer.PiServerName);
if (weekDayInfo.NonWorkingHourConsumption > 0)
{
long weekdayConsumption = (weekDayInfo.NonWorkingHourConsumption / 2) * 365;
GenerateConsumptionAlertAndNotification(sqlConnection, sqlTransaction, weekdayConsumption, piServer, false);
}
sqlTransaction.Commit();
}
}
catch (Exception e)
{
var errorMsg = string.Format("ConsumptionAlertJob Error : Consumption alert failed for pi server - {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;
}
}
/// <summary>
/// Processes half hourly data to generate daily consumption details .
/// </summary>
/// <param name="timerInfo">The timer information.</param>
public static void ProcessDailyConsumption([TimerTrigger("0 0/40 * * * *")] TimerInfo timerInfo)
{
try
{
using (SqlConnection sqlConnection = new SqlConnection(ConfigurationSettings.DbConnectionString))
{
sqlConnection.Open();
var meterService = new MeterService();
var piServerList = meterService.GetPiServerList(sqlConnection);
foreach (var piServer in piServerList)
{
try
{
using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
{
var meterList = meterService.GetMeterListByPiServer(sqlConnection, sqlTransaction, piServer.PiServerName);
var dailyConsumptionDataList = new List <AzureLiveData>();
var processedDataHistory = meterService.GetDailyConsumptionProcessedStatus(sqlConnection, sqlTransaction, piServer.PiServerName);
foreach (var meter in meterList)
{
List <AzureLiveData> azureLiveDataList = new List <AzureLiveData>();
var query = "select AMPS_SYSTEM_AVG, Breaker_details, Building, Daily_electric_cost, Daily_KWH_System, Monthly_electric_cost," +
"Monthly_KWH_System, PowerScout, Temperature, Timestamp, Visibility, kW_System, PiServerName from " +
"(SELECT CAST(Timestamp AS DATE) DailyTime, Max(Daily_KWH_System) DKS FROM LiveData " +
"WHERE {0} " +
"group by CAST(Timestamp AS DATE)) AS dailyData " +
"inner join LiveData as ALD " +
"on(dailyData.DKS = ALD.Daily_KWH_System and dailyData.DailyTime = CAST(ALD.Timestamp AS DATE)) " +
"WHERE {0} ";
if (processedDataHistory.ContainsKey(meter))
{
query = string.Format(query, "PowerScout = @meter AND PiServerName = @PiServerName AND Timestamp > @processedTime ");
}
else
{
query = string.Format(query, "PowerScout = @meter AND PiServerName = @PiServerName ");
}
using (SqlCommand sqlCommand = new SqlCommand(query, sqlConnection, sqlTransaction))
{
sqlCommand.Parameters.Add(new SqlParameter("@meter", meter));
sqlCommand.Parameters.Add(new SqlParameter("@PiserverName", piServer.PiServerName));
if (processedDataHistory.ContainsKey(meter))
{
sqlCommand.Parameters.Add(new SqlParameter("@processedTime", processedDataHistory[meter]));
}
using (SqlDataReader result = sqlCommand.ExecuteReader())
{
while (result.Read())
{
AzureLiveData azureLiveData = new AzureLiveData();
azureLiveData.AMPS_SYSTEM_AVG = SqlTypeConverter.ToDouble(result["AMPS_SYSTEM_AVG"]);
azureLiveData.Breaker_details = SqlTypeConverter.ToString(result["Breaker_details"]);
azureLiveData.Building = SqlTypeConverter.ToString(result["Building"]);
azureLiveData.Daily_electric_cost = SqlTypeConverter.ToDouble(result["Daily_electric_cost"]);
azureLiveData.Daily_KWH_System = SqlTypeConverter.ToDouble(result["Daily_KWH_System"]);
azureLiveData.Monthly_electric_cost = SqlTypeConverter.ToDouble(result["Monthly_electric_cost"]);
azureLiveData.Monthly_KWH_System = SqlTypeConverter.ToDouble(result["Monthly_KWH_System"]);
azureLiveData.PowerScout = SqlTypeConverter.ToString(result["PowerScout"]);
azureLiveData.Temperature = SqlTypeConverter.ToDouble(result["Temperature"]);
azureLiveData.Timestamp = SqlTypeConverter.ToDateTime(result["Timestamp"]);
azureLiveData.Visibility = SqlTypeConverter.ToDouble(result["Visibility"]);
azureLiveData.KW_System = SqlTypeConverter.ToDouble(result["kW_System"]);
azureLiveData.PiServerName = SqlTypeConverter.ToString(result["PiServerName"]);
azureLiveDataList.Add(azureLiveData);
}
result.Close();
}
}
var dailyData = azureLiveDataList.OrderByDescending(m => m.Timestamp).GroupBy(m => new { m.Timestamp.Date, m.Daily_KWH_System }).Select(s => s.First());
dailyConsumptionDataList.AddRange(dailyData);
}
if (dailyConsumptionDataList.Count > 0)
{
SaveDailyConsumptionData(sqlConnection, sqlTransaction, dailyConsumptionDataList);
Console.WriteLine("DailyConsumptionJob RowInserted : Piserver - {0}, Entries - {1}.", piServer.PiServerName, dailyConsumptionDataList.Count);
}
sqlTransaction.Commit();
}
}
catch (Exception e)
{
var errorMsg = string.Format("DailyConsumptionJob Error : Daily 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;
}
}
/// <summary>
/// Processes the daily consumption and predict next day electricity consumption.
/// </summary>
/// <param name="timerInfo">The timer information.</param>
/// <returns>A <see cref="Task"/> representing the asynchronous operation.</returns>
public static async Task ProcessDailyConsumptionPrediction([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
{
using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
{
var predictionProcessedStatus = meterService.GetDailyPredictionProcessedStatus(sqlConnection, sqlTransaction, piServer.PiServerName);
var meterlist = meterService.GetMeterListByPiServer(sqlConnection, sqlTransaction, piServer.PiServerName);
DateTime?timestamp = null;
int rows = 0;
foreach (var meter in meterlist)
{
if (predictionProcessedStatus.ContainsKey(meter))
{
timestamp = predictionProcessedStatus[meter].AddDays(-1);
}
else
{
timestamp = null;
}
var dailyConsumptionDetails = meterService.GetDailyConsumptionDetails(sqlConnection, sqlTransaction, meter, piServer.PiServerName, timestamp);
foreach (var dailyConsumptionDetail in dailyConsumptionDetails)
{
var response = await meterService.GetAnomalyPrediction(dailyConsumptionDetail, sqlConnection, sqlTransaction, piServer.PiServerName);
if (response != null)
{
rows++;
var dailyConsumptionPrediction = new DailyConsumptionPrediction();
List <string> columnNames = response.Results.Output1.Value.ColumnNames.ToList();
var values = response.Results.Output1.Value.Values.First();
dailyConsumptionPrediction.PowerScout = values[columnNames.IndexOf("PowerScout")];
dailyConsumptionPrediction.Timestamp = dailyConsumptionDetail.Timestamp.AddDays(1);
dailyConsumptionPrediction.Daily_Predicted_KWH_System = Convert.ToDouble(values[columnNames.IndexOf("Scored Labels")]);
dailyConsumptionPrediction.Building = dailyConsumptionDetail.Building;
dailyConsumptionPrediction.Breaker_details = dailyConsumptionDetail.Breaker_details;
meterService.AddDailyConsumptionPrediction(dailyConsumptionPrediction, sqlConnection, sqlTransaction, piServer.PiServerName);
}
}
}
if (rows > 0)
{
Console.WriteLine("DailyConsumptionPredictionJob RowInserted : PiServer - {0}, Rows - {1}", piServer.PiServerName, rows);
}
sqlTransaction.Commit();
}
}
catch (Exception e)
{
var errorMsg = string.Format("DailyConsumptionPredictionJob Error : Daily consumption prediction 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;
}
}
/// <summary>
/// Processes the daily consumption and generate monthly consumption.
/// </summary>
/// <param name="timerInfo">The timer information.</param>
public static void ProcessMonthlyConsumption([TimerTrigger("0 0/50 * * * *")] TimerInfo timerInfo)
{
try
{
using (SqlConnection sqlConnection = new SqlConnection(ConfigurationSettings.DbConnectionString))
{
sqlConnection.Open();
var meterService = new MeterService();
var piServerList = meterService.GetPiServerList(sqlConnection);
foreach (var piServer in piServerList)
{
try
{
using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
{
var meterlist = meterService.GetMeterListByPiServer(sqlConnection, sqlTransaction, piServer.PiServerName);
var monthlyConsumptionStatus = meterService.GetMonthlyConsumptionProcessedStatus(sqlConnection, sqlTransaction, piServer.PiServerName);
int rows = 0;
foreach (var meter in meterlist)
{
DateTime currentDate = default(DateTime);
if (!monthlyConsumptionStatus.ContainsKey(meter))
{
// if monthly consumption does not exists then process from start of daily consumption.
currentDate = meterService.GetDailyConsumptionInitialStatus(sqlConnection, sqlTransaction, piServer.PiServerName, meter);
}
else
{
// process from processed last month of monthly consumption.
currentDate = monthlyConsumptionStatus[meter];
}
do
{
var query = "SELECT Max(building) building, max(breaker_Details) breaker_Details, sum(Daily_electric_cost) Monthly_electric_cost, sum(Daily_KWH_System) Monthly_KWH_System, max(Timestamp) Timestamp FROM DailyConsumptionDetails WHERE PowerScout = @meter and PiServerName = @PiServerName and month([timestamp])= @CurrentMonth and year([timestamp])= @CurrentYear group by month([timestamp])";
MonthlyConsumptionData monthlyConsumptionData = null;
using (SqlCommand cmd = new SqlCommand(query, sqlConnection, sqlTransaction))
{
cmd.Parameters.Add(new SqlParameter("@meter", meter));
cmd.Parameters.Add(new SqlParameter("@CurrentMonth", currentDate.Month));
cmd.Parameters.Add(new SqlParameter("@CurrentYear", currentDate.Year));
cmd.Parameters.Add(new SqlParameter("@PiServerName", piServer.PiServerName));
SqlDataReader result = cmd.ExecuteReader();
while (result.Read())
{
monthlyConsumptionData = new MonthlyConsumptionData();
monthlyConsumptionData.Building = SqlTypeConverter.ToString(result["Building"]);
monthlyConsumptionData.Breaker_details = SqlTypeConverter.ToString(result["Breaker_details"]);
monthlyConsumptionData.Monthly_electric_cost = SqlTypeConverter.ToDouble(result["Monthly_electric_cost"]);
monthlyConsumptionData.Monthly_KWH_System = SqlTypeConverter.ToDouble(result["Monthly_KWH_System"]);
monthlyConsumptionData.Timestamp = SqlTypeConverter.ToDateTime(result["Timestamp"]);
}
result.Close();
}
if (monthlyConsumptionData != null)
{
using (SqlCommand cmdNew = new SqlCommand("SP_InsertUpdateMonthlyConsumptionDetails", sqlConnection, sqlTransaction))
{
cmdNew.Parameters.Add(new SqlParameter("@Building", monthlyConsumptionData.Building));
cmdNew.Parameters.Add(new SqlParameter("@Breaker_details", monthlyConsumptionData.Breaker_details));
cmdNew.Parameters.Add(new SqlParameter("@Month", currentDate.ToString("MMM")));
cmdNew.Parameters.Add(new SqlParameter("@Year", currentDate.Year.ToString()));
cmdNew.Parameters.Add(new SqlParameter("@Monthly_electric_cost", monthlyConsumptionData.Monthly_electric_cost));
cmdNew.Parameters.Add(new SqlParameter("@Monthly_KWH_System", monthlyConsumptionData.Monthly_KWH_System));
cmdNew.Parameters.Add(new SqlParameter("@PowerScout", meter));
cmdNew.Parameters.Add(new SqlParameter("@Timestamp", monthlyConsumptionData.Timestamp));
cmdNew.Parameters.Add(new SqlParameter("@PiServerName", piServer.PiServerName));
cmdNew.CommandType = CommandType.StoredProcedure;
cmdNew.ExecuteNonQuery();
}
rows++;
}
currentDate = currentDate.AddMonths(1);
}while (currentDate.Year < piServer.PiServerCurrentDateTime.Year ? true : currentDate.Year == piServer.PiServerCurrentDateTime.Year && currentDate.Month <= piServer.PiServerCurrentDateTime.Month); // process up-to current month and year.
}
if (rows > 0)
{
Console.WriteLine("MonthlyConsumptionJob RowInserted : Piserver - {0}, Rows - {1}", piServer.PiServerName, rows);
}
sqlTransaction.Commit();
}
}
catch (Exception e)
{
var errorMsg = string.Format("MonthlyConsumptionJob Error : monthly consumption failed for pi server - {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;
}
}
