public ClassOccupanyDetails ProcessDataRow(string serial_number, DateTime timestamp)
{
ClassOccupanyDetails classModel = new ClassOccupanyDetails();
try
{
//Console.WriteLine("ProcessDataRow()");
// Console.WriteLine("ProcessDataRow() :: RowObject ==" + rowObject);
//Console.WriteLine("ProcessDataRow() :: tableCSV == " + tableCSV);
foreach (DataRow row in tableCSV.Select().Where(x => x.Field <string>("Location").Contains("CHEM")))
{
if (IsValidSessionMonth(row, timestamp))
{
//Step 3 Checking for Valid Day
if (IsValidDay(row, timestamp))
{
//Step 4 Checking for valid Class timing in a day
if (IsValidClassTiming(row, timestamp))
{
classModel.IsClassOccupied = 1;
classModel.ClassTotalCapacity = Convert.ToInt32(row["Capacity"].ToString());
classModel.ClassOccupiedValue = Convert.ToInt32(row["Actual"]);
}
}
}
}
//Below code was done after considering the class and powerscout for one to one mapping but now commented because powerscouts are common.
/*
* //step 1 comparing serial number
* foreach (DataRow row in tableCSV.Select().Where(x => x.Field<string>("Powerscout meter serial number").Equals(serial_number)))
* {
*
*
*
* //Step 2 Checking for valid month
* if (IsValidSessionMonth(row, timestamp))
* {
* //Step 3 Checking for Valid Day
* if (IsValidDay(row, timestamp))
* {
* //Step 4 Checking for valid Class timing in a day
* if (IsValidClassTiming(row, timestamp))
* {
* classModel.IsClassOccupied = 1;
* classModel.ClassTotalCapacity = Convert.ToInt64(row["Capacity"].ToString());
* classModel.ClassOccupiedValue = Convert.ToInt64(row["Actual"]);
* }
* }
*
*
* }
* }*/
}
catch (Exception exception)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("{0} > Exception: {1}", DateTime.Now, exception.Message);
Console.ResetColor();
Utility.Log("Exception Occured :: ProcessDataRow() :: " + exception.Message);
}
return(classModel);
}
void ProcessDataByPIServer(string piServerName)
{
while (true)
{
try
{
double utcConversionTime = GetAndTimezone();
//To Do get connection basis of PI server using Connection Manager
SqlConnection piConnection = ConnectionManager.Instance().GetPISQLConnection(piServerName);
ConnectionManager.Instance().OpenSQLConnection(piConnection);
Console.WriteLine("Pi SQL Connection Opened");
SqlConnection weatherConnection = ConnectionManager.Instance().GetPISQLConnection(piServerName);
ConnectionManager.Instance().OpenSQLConnection(weatherConnection);
//We need this meterlist, bcoz we going to process data meter by meter
List <string> meterList = new List <string>();
meterList = getMeterList(piServerName);
ProcessedDataModel processedDataInfo = BlobStorageManager.Instance().GetLastProcessedData <ProcessedDataModel>(piServerName, Constants.THRESHOLD_METER_STORAGE_FILENAME_PREFIX);
if (processedDataInfo == null)
{
processedDataInfo = new ProcessedDataModel {
MeterTimestamp = new Dictionary <string, DateTime>()
}
}
;
Dictionary <string, DateTime> meterTimestamp = processedDataInfo.MeterTimestamp;
//Todo need to add validation here at timestamp whether it is null or not, if it is null then have to add default value
meterList.All(meter =>
{
if (!meterTimestamp.ContainsKey(meter))
{
SqlConnection meterFirstEntryConn = ConnectionManager.Instance().GetPISQLConnection(piServerName);
ConnectionManager.Instance().OpenSQLConnection(meterFirstEntryConn);
SqlCommand getTimestamp = new SqlCommand("SELECT TOP 1 Timestamp FROM PowergridView WHERE PowerScout = @meter order by Timestamp", meterFirstEntryConn);
getTimestamp.Parameters.Add(new SqlParameter("meter", meter));
SqlDataReader result = getTimestamp.ExecuteReader();
while (result.Read()) //Runs only once
{
DateTime updatedTime = Utility.TrimDateToMinute(((DateTime)result[0]).AddMinutes(-1));
meterTimestamp.Add(meter, Convert.ToDateTime(updatedTime));
}
ConnectionManager.Instance().CloseSQLConnection(meterFirstEntryConn);
}
//This is bcoz we are saving value in threshold file with addition of utcconversion time in pidb time.So this condition should be basedon pi time
DateTime startTime = Utility.TrimDateToMinute(meterTimestamp[meter]);
DateTime endTime = startTime.AddMinutes(Constants.TIME_WINDOW_FOR_HALF_HOURLY_DATA);
SqlCommand command;
//I know this code is wrong have to covert into timestamp string or add a certain value to timestamp to make proper timestamp
command = new SqlCommand("SELECT * FROM PowergridView WHERE PowerScout = @meter AND Timestamp > @startTime AND Timestamp <= @endTime ORDER BY Timestamp", piConnection);
// Add the parameters.
command.Parameters.Add(new SqlParameter("@startTime", startTime.ToString(Constants.DATE_TIME_FORMAT)));
command.Parameters.Add(new SqlParameter("@endTime", endTime.ToString(Constants.DATE_TIME_FORMAT)));
command.Parameters.Add(new SqlParameter("@meter", meter));
SqlDataReader pireader = command.ExecuteReader();
List <AzureDataModel> meterDataList = new List <AzureDataModel>();
DateTime lastProcessedDate = DateTime.Now;
while (pireader.Read())
{
AzureDataModel data = new AzureDataModel();
string serialNumber = Convert.ToString(pireader["Serial Number"]);
DateTime utcDate;
DateTime.TryParse(pireader["Timestamp"].ToString(), out utcDate);
WeatherDetails weatherDetails = GetWeatherDetails(utcDate, weatherConnection);
lastProcessedDate = utcDate;
utcDate = utcDate.AddHours(utcConversionTime);
var utcSQLFormattedDate = utcDate.ToString(Constants.DATE_TIME_FORMAT);
ClassScheduleManager.Instance().ReInitialize(piServerName);
ClassOccupanyDetails classDetails = ClassScheduleManager.Instance().ProcessDataRow(serialNumber, utcDate);
if (pireader["Id"] != DBNull.Value)
{
data.Id = Convert.ToInt32(pireader["Id"]);
}
if (pireader["Amps L1"] != DBNull.Value)
{
data.AMPS_L1 = Convert.ToDouble(pireader["Amps L1"]);
}
if (pireader["Amps L2"] != DBNull.Value)
{
data.AMPS_L2 = Convert.ToDouble(pireader["Amps L2"]);
}
if (pireader["Amps L3"] != DBNull.Value)
{
data.AMPS_L3 = Convert.ToDouble(pireader["Amps L3"]);
}
if (pireader["Amps System Avg"] != DBNull.Value)
{
data.AMPS_SYSTEM_AVG = Convert.ToDouble(pireader["Amps System Avg"]);
}
if (pireader["Breaker Details"] != DBNull.Value)
{
data.Breaker_details = Convert.ToString(pireader["Breaker Details"]);
}
if (pireader["Breaker Label"] != DBNull.Value)
{
data.Breaker_label = Convert.ToString(pireader["Breaker Label"]);
}
if (pireader["Building"] != DBNull.Value)
{
data.Building = Convert.ToString(pireader["Building"]);
}
data.ClassOccupanyRemaining = classDetails.ClassOccupanyRemaining;
data.ClassOccupiedValue = classDetails.ClassOccupiedValue;
data.TotalClassCapacity = classDetails.ClassTotalCapacity;
data.isClassOccupied = classDetails.IsClassOccupied;
if (pireader["Daily Electric Cost"] != DBNull.Value)
{
data.Daily_electric_cost = Convert.ToDouble(pireader["Daily Electric Cost"]);
}
if (pireader["Daily kWh System"] != DBNull.Value)
{
data.Daily_KWH_System = Convert.ToDouble(pireader["Daily kWh System"]);
}
if (pireader["kW L1"] != DBNull.Value)
{
data.KW_L1 = Convert.ToDouble(pireader["kW L1"]);
}
if (pireader["kW L2"] != DBNull.Value)
{
data.KW_L2 = Convert.ToDouble(pireader["kW L2"]);
}
if (pireader["kW L3"] != DBNull.Value)
{
data.KW_L3 = Convert.ToDouble(pireader["kW L3"]);
}
if (pireader["Monthly Electric Cost"] != DBNull.Value)
{
data.Monthly_electric_cost = Convert.ToDouble(pireader["Monthly Electric Cost"]);
}
if (pireader["Monthly kWh System"] != DBNull.Value)
{
data.Monthly_KWH_System = Convert.ToDouble(pireader["Monthly kWh System"]);
}
if (pireader["PowerScout"] != DBNull.Value)
{
data.PowerScout = Convert.ToString(pireader["PowerScout"]);
}
if (pireader["Rated Amperage"] != DBNull.Value)
{
data.Rated_Amperage = Convert.ToDouble(pireader["Rated Amperage"]);
}
data.Pressure = Convert.ToDouble(weatherDetails.Pressure);
data.Relative_humidity = Convert.ToDouble(weatherDetails.RelativeHumidity);
if (pireader["Rolling Hourly kWh System"] != DBNull.Value)
{
data.Rolling_hourly_kwh_system = Convert.ToDouble(pireader["Rolling Hourly kWh System"]);
}
if (pireader["Serial Number"] != DBNull.Value)
{
data.Serial_number = Convert.ToString(pireader["Serial Number"]);
}
data.Temperature = Convert.ToDouble(weatherDetails.Temperature);
//Check here
data.Timestamp = Convert.ToDateTime(utcSQLFormattedDate);
if (pireader["Type"] != DBNull.Value)
{
data.Type = Convert.ToString(pireader["Type"]);
}
data.Visibility = Convert.ToDouble(weatherDetails.Visibility);
if (pireader["Volts L1 to Neutral"] != DBNull.Value)
{
data.Volts_L1_to_neutral = Convert.ToDouble(pireader["Volts L1 to Neutral"]);
}
if (pireader["Volts L2 to Neutral"] != DBNull.Value)
{
data.Volts_L2_to_neutral = Convert.ToDouble(pireader["Volts L2 to Neutral"]);
}
if (pireader["Volts L3 to Neutral"] != DBNull.Value)
{
data.Volts_L3_to_neutral = Convert.ToDouble(pireader["Volts L3 to Neutral"]);
}
if (pireader["kW System"] != DBNull.Value)
{
data.kW_System = Convert.ToDouble(pireader["kW System"]);
}
data.PiServerName = piServerName;
meterDataList.Add(data);
meterTimestamp[meter] = lastProcessedDate;
//count++;
}
pireader.Close();
//Hack Hack Hack
if (meterDataList != null && meterDataList.Count != 0)
{
//This condition means we get all (29)entries of that perticular half hour
if (Utility.TrimDateToMinute(lastProcessedDate) == endTime.AddMinutes(-1))
{
Console.WriteLine("Now going to update Database");
updateDatabase(meterDataList);
processedDataInfo.MeterTimestamp = meterTimestamp;
Console.Write("Storing value to Blob : " + processedDataInfo);
BlobStorageManager.Instance().SetLastProcessedData <ProcessedDataModel>(piServerName, Constants.THRESHOLD_METER_STORAGE_FILENAME_PREFIX, processedDataInfo);
}
else
{
//will wait for half an hour if there is not all entries in selected half hour block i.e 29 entries
Console.WriteLine("**************Sleeping*******************");
Thread.Sleep(1800000);
}
}
return(true);
});
ConnectionManager.Instance().CloseSQLConnection(piConnection);
ConnectionManager.Instance().CloseSQLConnection(weatherConnection);
}
catch (Exception e)
{
Console.WriteLine("*********Exception Occured ******" + e.Message);
}
}
}
