public void ProcessData()
{
try
{
if (!((string.IsNullOrEmpty(ConfigurationSettings.PiServer)) || (string.IsNullOrEmpty(ConfigurationSettings.AzureConnectionString)) || (string.IsNullOrEmpty(ConfigurationSettings.StorageConnectionString)) ||
(string.IsNullOrEmpty(ConfigurationSettings.BlobContainerName))))
{
Console.WriteLine("Init ProcessData");
ConnectionManager.Instance().Initialize();
Console.WriteLine("Done with Console manager initialization");
BlobStorageManager.Instance().ConfigureBlobStorage();
Console.WriteLine("Done with Blob Storage configuration");
string piServer = ConnectionManager.Instance().GetPIServer();
Thread piThread = new Thread(() => { ProcessDataByPIServer(piServer); });
piThread.Start();
Thread sensorThread = new Thread(() => { InsertSensorData(piServer); });
sensorThread.Start();
}
else
{
Console.WriteLine("Doesn't have sufficient data in app config for connection");
}
}
catch (Exception e)
{
Console.WriteLine("Error occured in processData" + e.Message);
}
}
public void InsertSensorData(string piServerName, double timeZone)
{
try
{
var sensorList = this.GetSensors(piServerName);
ProcessedSensorDataModel processedDataInfo = BlobStorageManager.Instance().GetLastProcessedData <ProcessedSensorDataModel>(piServerName, Constants.THRESHOLD_SENSOR_STORAGE_FILENAME_PREFIX);
if (processedDataInfo == null)
{
processedDataInfo = new ProcessedSensorDataModel();
}
DateTime processedTimestamp = processedDataInfo.ProcessedTimestamp;
DateTime firstEntryTimeStamp = processedTimestamp;
//To do need to check this
if (processedTimestamp == null || processedTimestamp == DateTime.MinValue || processedTimestamp == default(DateTime))
{
SqlConnection sensorFirstEntryConn = ConnectionManager.Instance().GetPISQLConnection(piServerName);
ConnectionManager.Instance().OpenSQLConnection(sensorFirstEntryConn);
//Read data from PI Server
SqlCommand getTimestamp = new SqlCommand("SELECT TOP 1 TimeStamp FROM SensorData order by Timestamp", sensorFirstEntryConn);
SqlDataReader result = getTimestamp.ExecuteReader();
while (result.Read()) //Runs only once
{
firstEntryTimeStamp = (DateTime)result[0];
}
ConnectionManager.Instance().CloseSQLConnection(sensorFirstEntryConn);
}
SqlConnection getPiSensorDataConn = ConnectionManager.Instance().GetPISQLConnection(piServerName);
ConnectionManager.Instance().OpenSQLConnection(getPiSensorDataConn);
SqlCommand getPiSensorDataCommand = (processedTimestamp == null || processedTimestamp == DateTime.MinValue || processedTimestamp == default(DateTime)) ? new SqlCommand("SELECT * FROM SensorData WHERE TimeStamp >= @TimeStamp", getPiSensorDataConn) : new SqlCommand("SELECT * FROM SensorData WHERE TimeStamp > @TimeStamp", getPiSensorDataConn);
processedTimestamp = firstEntryTimeStamp;
getPiSensorDataCommand.Parameters.Add(new SqlParameter("@TimeStamp", processedTimestamp));
SqlDataReader piSensorDataReader = getPiSensorDataCommand.ExecuteReader();
try
{
while (piSensorDataReader.Read())
{
var sensorDetail = sensorList.Where(sensor => sensor.Sensor_Name.Equals(piSensorDataReader["Name"])).FirstOrDefault();
if (sensorDetail == null)
{
this.AddNewSensorToAzureAndGenerateNotification(piSensorDataReader["Name"].ToString(), piServerName);
//reinit sensor list after insertion of new sensor
sensorList = this.GetSensors(piServerName);
sensorDetail = sensorList.Where(sensor => sensor.Sensor_Name.Equals(piSensorDataReader["Name"])).FirstOrDefault();
this.AddNewAlert(0, sensorDetail.Sensor_Id, "Device Alert", "New device found with name " + sensorDetail.Sensor_Name, DateTime.UtcNow, 0, piServerName);
}
DateTime utcDate;
DateTime localDate;
DateTime.TryParse(piSensorDataReader["TimeStamp"].ToString(), out utcDate);
localDate = utcDate.AddHours(Convert.ToDouble(timeZone));
var formattedDate = localDate.ToString(Constants.DATE_TIME_FORMAT);
SqlConnection azureSQLConnection = new SqlConnection();
azureSQLConnection.ConnectionString = ConfigurationSettings.AzureConnectionString;
azureSQLConnection.Open();
SqlCommand updateAzureDBCommand = new SqlCommand("INSERT INTO SensorLiveData (Sensor_Id,Temperature,Brightness,Humidity,Timestamp,PiServerName) VALUES (@SensorId,@Temperature,@Brightness,@Humidity,@Timestamp,@PiServerName)", azureSQLConnection);
updateAzureDBCommand.Parameters.Add(new SqlParameter("@SensorId", sensorDetail.Sensor_Id));
updateAzureDBCommand.Parameters.Add(new SqlParameter("@Temperature", piSensorDataReader["Temperature"]));
updateAzureDBCommand.Parameters.Add(new SqlParameter("@Brightness", piSensorDataReader["Brightness"]));
updateAzureDBCommand.Parameters.Add(new SqlParameter("@Humidity", piSensorDataReader["Humidity"]));
updateAzureDBCommand.Parameters.Add(new SqlParameter("@Timestamp", formattedDate));
updateAzureDBCommand.Parameters.Add(new SqlParameter("@PiServerName", piServerName));
updateAzureDBCommand.ExecuteNonQuery();
processedTimestamp = Convert.ToDateTime(piSensorDataReader["TimeStamp"]);
azureSQLConnection.Close();
}
}
catch (Exception e)
{
}
finally
{
ConnectionManager.Instance().CloseSQLConnection(getPiSensorDataConn);
}
processedDataInfo.ProcessedTimestamp = processedTimestamp;
Console.WriteLine("Storing Sensor Details : " + processedDataInfo);
BlobStorageManager.Instance().SetLastProcessedData <ProcessedSensorDataModel>(piServerName, Constants.THRESHOLD_SENSOR_STORAGE_FILENAME_PREFIX, processedDataInfo);
}
catch (Exception ex)
{
}
}
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);
}
}
}
public void ProcessData()
{
try
{
Utility.Log("PI server : " + ConfigurationSettings.PiServer);
Utility.Log("Azure ConnectionString : " + ConfigurationSettings.AzureConnectionString);
Utility.Log("Pi Server ConnectionString : " + ConfigurationSettings.PiServerConnectionString);
if (!((string.IsNullOrEmpty(ConfigurationSettings.PiServer)) || (string.IsNullOrEmpty(ConfigurationSettings.AzureConnectionString)) || (string.IsNullOrEmpty(ConfigurationSettings.PiServerConnectionString))
))
{
Console.WriteLine("Init ProcessData");
Utility.Log("Init ProcessData");
if (utcConversionTime != GetAndTimezone())
{
utcConversionTime = GetAndTimezone();
PIConfigurationInfoModel piConfig = new PIConfigurationInfoModel();
string serverName = ConfigurationSettings.PiServer;
piConfig.PiServerDesc = serverName;
piConfig.PiServerName = serverName;
piConfig.PiServerURL = ConfigurationSettings.PiServerConnectionString;
piConfig.UTCConversionTime = utcConversionTime;
piConfig.CreatedOn = DateTime.UtcNow;
ConnectionManager.Instance().InserPIConfigurationDetailsToDB(piConfig);
}
ConnectionManager.Instance().Initialize();
Console.WriteLine("Done with Console manager initialization");
string storageConnectionString = ConfigurationSettings.GetCloudConfigDataModel().BlobStorageURL;
//patch for now
storageConnectionString = ConfigurationSettings.StorageConnectionString;
//
if (!string.IsNullOrEmpty(storageConnectionString))
{
BlobStorageManager.Instance().ConfigureBlobStorage(storageConnectionString);
Console.WriteLine("Done with Blob Storage configuration");
Utility.Log("Done with Console manager initialization");
string piServer = ConnectionManager.Instance().GetPIServer();
Thread piThread = new Thread(() => { ProcessDataByPIServer(piServer); });
piThread.Start();
//Thread sensorThread = new Thread(() => { InsertSensorData(piServer); });
//sensorThread.Start();
}
else
{
Utility.Log("Does not have storage connection string ");
}
}
else
{
Console.WriteLine("Doesn't have sufficient data in app config for connection");
Console.ReadLine();
Utility.Log("Doesn't have sufficient data in app config for connection");
}
}
catch (Exception e)
{
Console.WriteLine("Error occured in processData" + e.Message);
Utility.Log("Error occured in processData" + e.Message);
}
}
