private static DayConsumptionInfo GetConsumption(DateTime startTime, DateTime endTime, SqlConnection sqlConnection, SqlTransaction sqlTransaction, string piServerName)
{
var query = "SELECT Timestamp, Daily_KWH_System FROM LiveData WHERE Timestamp > @startTime AND Timestamp <= @endTime and PiServerName = @PiServerName ORDER BY Timestamp";
List <AzureLiveData> meterDataList = new List <AzureLiveData>();
using (SqlCommand sqlCommand = new SqlCommand(query, sqlConnection, sqlTransaction))
{
sqlCommand.Parameters.Add(new SqlParameter("@startTime", startTime.ToString(Constants.DATE_TIME_FORMAT)));
sqlCommand.Parameters.Add(new SqlParameter("@endTime", endTime.ToString(Constants.DATE_TIME_FORMAT)));
sqlCommand.Parameters.AddWithValue("@PiServerName", piServerName);
using (SqlDataReader result = sqlCommand.ExecuteReader())
{
while (result.Read())
{
AzureLiveData data = new AzureLiveData();
data.Timestamp = SqlTypeConverter.ToDateTime(result["Timestamp"]);
data.Daily_KWH_System = SqlTypeConverter.ToDouble(result["Daily_KWH_System"]);
meterDataList.Add(data);
}
result.Close();
}
}
if (meterDataList.Count == 0)
{
return(new DayConsumptionInfo());
}
DateTime startDate = meterDataList.First().Timestamp.Date;
DateTime endDate = meterDataList.Last().Timestamp.Date;
var activeHourddataOfDay = meterDataList.Where(x => x.Timestamp.Date == startDate && x.Timestamp.Hour < 22).Select(x => x.Daily_KWH_System);
var inActiveHourSameDayData = meterDataList.Where(x => x.Timestamp.Date == startDate && x.Timestamp.Hour >= 22).Select(x => x.Daily_KWH_System);
var inActiveHourDifferentDayData = meterDataList.Where(x => x.Timestamp.Date == endDate).Select(x => x.Daily_KWH_System);
long activeHourConsumption = activeHourddataOfDay.Count() > 0 ? Convert.ToInt64(activeHourddataOfDay.Max() - activeHourddataOfDay.Min()) : default(long);
long inActiveHourSameDayConsumption = inActiveHourSameDayData.Count() > 0 ? Convert.ToInt64(inActiveHourSameDayData.Max() - inActiveHourSameDayData.Min()) : default(long);
long inActiveHourDifferentDayConsumption = inActiveHourDifferentDayData.Count() > 0 ? Convert.ToInt64(inActiveHourDifferentDayData.Max() - inActiveHourDifferentDayData.Min()) : default(long);
return(new DayConsumptionInfo()
{
ActiveHourConsumption = activeHourConsumption,
NonWorkingHourConsumption = inActiveHourSameDayConsumption + inActiveHourDifferentDayConsumption
});
}
/// <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;
}
}