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); } } }