private async Task RunAsync(CancellationToken cancellationToken)
{
ILogger logger = new ApplicationInsightsLogger();
string brokerIp;
int numberOfThreads;
try
{
brokerIp = CloudConfigurationManager.GetSetting("BrokerIp");
var threadSet = int.TryParse(CloudConfigurationManager.GetSetting("Threads"), out numberOfThreads);
if (!threadSet)
{
numberOfThreads = DefaultNumberOfThreads;
}
}
catch (Exception exception)
{
brokerIp = string.Empty;
numberOfThreads = DefaultNumberOfThreads;
logger.LogException(exception);
}
while (!cancellationToken.IsCancellationRequested)
{
try
{
var concurrentConnectonTest = new ConcurrentConnectionTest();
var concurrentConnectonTestSetup = new TestSetup(logger, brokerIp, concurrentConnectonTest, numberOfThreads);
concurrentConnectonTestSetup.RunTest(int.MaxValue, new TimeSpan(0, 60, 0), new TimeSpan(0, 0, 3), new TimeSpan(0, 0, 3), new TimeSpan(0, 0, 30), new TimeSpan(0, 1, 0));
}
catch (Exception exception)
{
logger.LogEvent("Incomplete", "Completed test using " + numberOfThreads + " threads");
logger.LogException(exception);
}
await Task.Delay(new TimeSpan(0, 1, 0));
GC.Collect();
GC.WaitForPendingFinalizers();
GC.WaitForFullGCComplete();
GC.Collect();
}
}
private async Task RunAsync(CancellationToken cancellationToken)
{
ILogger logger = new ApplicationInsightsLogger();
var brokerIp = CloudConfigurationManager.GetSetting("BrokerIp");
int numberOfThreads;
var threadSet = int.TryParse(CloudConfigurationManager.GetSetting("Threads"), out numberOfThreads);
if (!threadSet)
{
numberOfThreads = DefaultNumberOfThreads;
}
while (!cancellationToken.IsCancellationRequested)
{
try
{
var throughputTest = new MessageThroughputTest();
var testSetup = new TestSetup(logger, brokerIp, throughputTest, numberOfThreads);
testSetup.RunTest();
//var concurrentConnectonTest = new ConcurrentConnectionTest();
//var concurrentConnectonTestSetup = new TestSetup(logger, brokerIp, concurrentConnectonTest, numberOfThreads);
//concurrentConnectonTestSetup.RunTest(int.MaxValue, new TimeSpan(0, 10, 0), new TimeSpan(0, 1, 0), new TimeSpan(0, 1, 0));
}
catch (Exception exception)
{
logger.LogException(exception);
}
await Task.Delay(new TimeSpan(0, 0, 10));
GC.Collect();
GC.WaitForPendingFinalizers();
GC.WaitForFullGCComplete();
GC.Collect();
}
}
/// <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;
}
}