private static void LoadEmail(int eventID, List <Recipient> recipients, string subject, string body)
{
EventTableAdapter eventAdapter = s_dbAdapterContainer.GetAdapter <EventTableAdapter>();
MeterData.EventRow eventRow = eventAdapter.GetDataByID(eventID)[0];
MeterData.EventDataTable systemEvent = eventAdapter.GetSystemEvent(eventRow.StartTime, eventRow.EndTime, s_timeTolerance);
FaultEmailTableAdapter faultEmailAdapter = s_dbAdapterContainer.GetAdapter <FaultEmailTableAdapter>();
FaultLocationData.FaultEmailDataTable faultEmailTable = new FaultLocationData.FaultEmailDataTable();
FaultLocationData.EventFaultEmailDataTable eventFaultEmailTable = new FaultLocationData.EventFaultEmailDataTable();
DateTime now = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, s_timeZone);
string toLine = string.Join("; ", recipients.Select(recipient => recipient.Email));
BulkLoader bulkLoader;
faultEmailTable.AddFaultEmailRow(now, toLine, subject, body);
faultEmailAdapter.Update(faultEmailTable);
foreach (MeterData.EventRow evt in systemEvent)
{
if (eventRow.LineID == evt.LineID)
{
eventFaultEmailTable.AddEventFaultEmailRow(evt.ID, faultEmailTable[0].ID);
}
}
bulkLoader = new BulkLoader();
bulkLoader.Connection = s_dbAdapterContainer.Connection;
bulkLoader.CommandTimeout = s_dbAdapterContainer.CommandTimeout;
bulkLoader.Load(eventFaultEmailTable);
}
private Dictionary <BreakerOperationType, int> GetBreakerOperationTypeLookup(DbAdapterContainer dbAdapterContainer)
{
MeterData.BreakerOperationTypeDataTable breakerOperationTypeTable = new MeterData.BreakerOperationTypeDataTable();
BreakerOperationType breakerOperationType = default(BreakerOperationType);
foreach (BreakerOperationType operationType in Enum.GetValues(typeof(BreakerOperationType)))
{
breakerOperationTypeTable.AddBreakerOperationTypeRow(operationType.ToString(), operationType.ToString());
}
BulkLoader bulkLoader = new BulkLoader();
bulkLoader.Connection = dbAdapterContainer.Connection;
bulkLoader.CommandTimeout = dbAdapterContainer.CommandTimeout;
bulkLoader.MergeTableFormat = "MERGE INTO {0} AS Target " +
"USING {1} AS Source " +
"ON Source.Name = Target.Name " +
"WHEN NOT MATCHED THEN " +
" INSERT (Name, Description) " +
" VALUES (Source.Name, Source.Description);";
bulkLoader.Load(breakerOperationTypeTable);
dbAdapterContainer.GetAdapter <BreakerOperationTypeTableAdapter>().Fill(breakerOperationTypeTable);
return(breakerOperationTypeTable
.Where(row => Enum.TryParse(row.Name, out breakerOperationType)).Select(row => Tuple.Create(breakerOperationType, row.ID))
.ToDictionary(tuple => tuple.Item1, tuple => tuple.Item2));
}
public override void Load(DbAdapterContainer dbAdapterContainer)
{
MeterData.EventDataTable eventTable;
Dictionary <EventKey, MeterData.EventRow> eventLookup;
MeterData.EventRow eventRow;
BulkLoader bulkLoader;
eventTable = dbAdapterContainer.GetAdapter <EventTableAdapter>().GetDataByFileGroup(m_meterDataSet.FileGroup.ID);
eventLookup = eventTable.Where(evt => evt.MeterID == m_meterDataSet.Meter.ID).ToDictionary(CreateEventKey);
foreach (Tuple <EventKey, MeterData.BreakerOperationRow> breakerOperation in m_breakerOperations)
{
if (eventLookup.TryGetValue(breakerOperation.Item1, out eventRow))
{
breakerOperation.Item2.EventID = eventRow.ID;
m_breakerOperationTable.AddBreakerOperationRow(breakerOperation.Item2);
}
}
bulkLoader = new BulkLoader();
bulkLoader.Connection = dbAdapterContainer.Connection;
bulkLoader.CommandTimeout = dbAdapterContainer.CommandTimeout;
bulkLoader.Load(m_breakerOperationTable);
}
private static void LoadEventSentEmail(EventRow eventRow, EventDataTable systemEvent, int sentEmailID)
{
BulkLoader bulkLoader;
DataTable eventSentEmailTable;
using (AdoDataConnection connection = new AdoDataConnection(s_dbAdapterContainer.Connection, typeof(SqlDataAdapter), false))
{
// Query an empty table with matching schema --
// union table to itself to eliminate unique key constraints
eventSentEmailTable = connection.RetrieveData("SELECT * FROM EventSentEmail WHERE 1 IS NULL UNION ALL SELECT * FROM EventSentEmail WHERE 1 IS NULL");
eventSentEmailTable.TableName = "EventSentEmail";
}
foreach (MeterData.EventRow evt in systemEvent)
{
if (eventRow.LineID == evt.LineID)
{
eventSentEmailTable.Rows.Add(0, evt.ID, sentEmailID);
}
}
bulkLoader = new BulkLoader();
bulkLoader.Connection = s_dbAdapterContainer.Connection;
bulkLoader.CommandTimeout = s_dbAdapterContainer.CommandTimeout;
bulkLoader.Load(eventSentEmailTable);
}
public override void Load(DbAdapterContainer dbAdapterContainer)
{
BulkLoader loader = new BulkLoader();
loader.Connection = dbAdapterContainer.Connection;
loader.CommandTimeout = dbAdapterContainer.CommandTimeout;
loader.Load(m_disturbanceSeverityTable);
}
public override void Load(DbAdapterContainer dbAdapterContainer)
{
BulkLoader hourlySummaryLoader;
BulkLoader channelNormalLoader;
if (m_hourlySummaryTable.Count == 0 && m_channelNormalTable.Count == 0)
{
return;
}
Log.Info("Loading hourly summary data into the database...");
hourlySummaryLoader = new BulkLoader();
channelNormalLoader = new BulkLoader();
hourlySummaryLoader.Connection = dbAdapterContainer.Connection;
hourlySummaryLoader.CommandTimeout = dbAdapterContainer.CommandTimeout;
channelNormalLoader.Connection = dbAdapterContainer.Connection;
channelNormalLoader.CommandTimeout = dbAdapterContainer.CommandTimeout;
hourlySummaryLoader.MergeTableFormat = "MERGE INTO {0} WITH (TABLOCK) AS Target " +
"USING {1} AS Source " +
"ON Source.ChannelID = Target.ChannelID AND Source.Time = Target.Time " +
"WHEN MATCHED THEN " +
" UPDATE SET " +
" Maximum = CASE WHEN Target.ValidCount = 0 OR Source.Maximum > Target.Maximum THEN Source.Maximum ELSE Target.Maximum END, " +
" Minimum = CASE WHEN Target.ValidCount = 0 OR Source.Minimum < Target.Minimum THEN Source.Minimum ELSE Target.Minimum END, " +
" Average = CASE WHEN Target.ValidCount = 0 THEN Source.Average ELSE Target.Average * (CAST(Target.ValidCount AS FLOAT) / (Target.ValidCount + Source.ValidCount)) + Source.Average * (CAST(Source.ValidCount AS FLOAT) / (Target.ValidCount + Source.ValidCount)) END, " +
" ValidCount = Source.ValidCount + Target.ValidCount, " +
" InvalidCount = Source.InvalidCount + Target.InvalidCount " +
"WHEN NOT MATCHED THEN " +
" INSERT (ChannelID, Time, Maximum, Minimum, Average, ValidCount, InvalidCount) " +
" VALUES (Source.ChannelID, Source.Time, Source.Maximum, Source.Minimum, Source.Average, Source.ValidCount, Source.InvalidCount);";
channelNormalLoader.MergeTableFormat = "MERGE INTO {0} AS Target " +
"USING {1} AS Source " +
"ON Target.ChannelID = Source.ChannelID " +
"WHEN MATCHED THEN " +
" UPDATE SET " +
" Average = Target.Average * (CAST(Target.Count AS FLOAT) / (Target.Count + Source.Count)) + Source.Average * (CAST(Source.Count AS FLOAT) / (Target.Count + Source.Count)), " +
" MeanSquare = Target.MeanSquare * (CAST(Target.Count AS FLOAT) / (Target.Count + Source.Count)) + Source.MeanSquare * (CAST(Source.Count AS FLOAT) / (Target.Count + Source.Count)), " +
" StandardDeviation = Target.MeanSquare * (CAST(Target.Count AS FLOAT) / (Target.Count + Source.Count)) + Source.MeanSquare * (CAST(Source.Count AS FLOAT) / (Target.Count + Source.Count)) - POWER(Target.Average * (CAST(Target.Count AS FLOAT) / (Target.Count + Source.Count)) + Source.Average * (CAST(Source.Count AS FLOAT) / (Target.Count + Source.Count)), 2), " +
" Count = Target.Count + Source.Count " +
"WHEN NOT MATCHED THEN " +
" INSERT (ChannelID, Average, MeanSquare, StandardDeviation, Count) " +
" VALUES (Source.ChannelID, Source.Average, Source.MeanSquare, SQRT(Source.MeanSquare - Source.Average * Source.Average), Source.Count); ";
hourlySummaryLoader.Load(m_hourlySummaryTable);
channelNormalLoader.Load(m_channelNormalTable);
Log.Info(string.Format("Loaded {0} hourly summary records into the database.", m_hourlySummaryTable.Count));
}
public void AddRecordInFirstLoader()
{
var loader1 = Substitute.For <IRecordLoader>();
loader1.Add(Arg.Any <IRecord>()).Returns(true);
var loader2 = Substitute.For <IRecordLoader>();
var loader = new BulkLoader(new [] { loader1, loader2 }, new Sequence(), Substitute.For <ILogger>());
loader.Load(new IRecord[] { new Header() });
loader1.ReceivedWithAnyArgs().Add(null);
loader2.DidNotReceiveWithAnyArgs().Add(null);
}
public void ProcessesAllRecords()
{
var loader1 = Substitute.For <IRecordLoader>();
loader1.Add(Arg.Any <IRecord>()).Returns(true, false);
var loader2 = Substitute.For <IRecordLoader>();
loader2.Add(Arg.Any <IRecord>()).Returns(true);
var loader = new BulkLoader(new [] { loader1, loader2 }, new Sequence(), Substitute.For <ILogger>());
loader.Load(new IRecord[] { new Header(), new Trailer() });
loader1.ReceivedWithAnyArgs().Add(null);
loader2.ReceivedWithAnyArgs().Add(null);
}
public void ThrowsExceptionOnAnyError_TODO_CHANGE_THIS_BEHAVIOR()
{
var loader1 = Substitute.For <IRecordLoader>();
loader1.Add(Arg.Any <IRecord>()).Returns(x => throw new Exception(), x => true);
var loader2 = Substitute.For <IRecordLoader>();
loader2.Add(Arg.Any <IRecord>()).Returns(true);
var loader = new BulkLoader(new [] { loader1, loader2 }, new Sequence(), Substitute.For <ILogger>());
Assert.Throws <Exception>(() => loader.Load(new IRecord[] { new Header(), new Trailer() }));
loader1.ReceivedWithAnyArgs().Add(null);
loader2.DidNotReceiveWithAnyArgs().Add(null);
}
public override void Load(DbAdapterContainer dbAdapterContainer)
{
BulkLoader bulkLoader = new BulkLoader();
if (m_alarmLogTable.Count == 0)
{
return;
}
Log.Info("Loading alarm data into the database...");
bulkLoader.Connection = m_dbAdapterContainer.Connection;
bulkLoader.CommandTimeout = m_dbAdapterContainer.CommandTimeout;
bulkLoader.Load(m_alarmLogTable);
Log.Info(string.Format("Loaded {0} alarm log records into the database...", m_alarmLogTable.Count));
}
public void DropThroughLogsWarning()
{
var logger = Substitute.For <ILogger>();
var loader1 = Substitute.For <IRecordLoader>();
loader1.Add(Arg.Any <IRecord>()).Returns(false);
var loader2 = Substitute.For <IRecordLoader>();
loader2.Add(Arg.Any <IRecord>()).Returns(false);
var loader = new BulkLoader(new [] { loader1, loader2 }, new Sequence(), logger);
loader.Load(new IRecord[] { new Header() });
logger.ReceivedWithAnyArgs().Warning <Type, IRecord>(messageTemplate: null, propertyValue0: null, propertyValue1: null);
}
public void BadFilter()
{
var loader = new BulkLoader();
var model = new FolderModel();
bool threwCorrectException = false;
try
{
loader.Load(Directory.GetCurrentDirectory(), "\\x\\y", model);
}
catch (Exception ex)
{
if (ex is ArgumentException)
{
threwCorrectException = true;
}
}
Assert.IsTrue(threwCorrectException);
}
public void BadDirectory()
{
var loader = new BulkLoader();
var model = new FolderModel();
bool threwCorrectException = false;
try
{
loader.Load(@"xxx\yyy\zzz\www\ttt", "*.txt", model);
}
catch (Exception ex)
{
if (ex is DirectoryNotFoundException)
{
threwCorrectException = true;
}
}
Assert.IsTrue(threwCorrectException);
}
private Dictionary <EventClassification, int> GetEventTypeLookup(DbAdapterContainer dbAdapterContainer)
{
MeterData.EventTypeDataTable eventTypeTable = new MeterData.EventTypeDataTable();
EventClassification eventClassification = default(EventClassification);
foreach (EventClassification classification in Enum.GetValues(typeof(EventClassification)))
{
eventTypeTable.AddEventTypeRow(classification.ToString(), classification.ToString());
}
BulkLoader bulkLoader = new BulkLoader();
bulkLoader.Connection = dbAdapterContainer.Connection;
bulkLoader.CommandTimeout = dbAdapterContainer.CommandTimeout;
bulkLoader.MergeTableFormat = "MERGE INTO {0} AS Target " +
"USING {1} AS Source " +
"ON Source.Name = Target.Name " +
"WHEN NOT MATCHED THEN " +
" INSERT (Name, Description) " +
" VALUES (Source.Name, Source.Description);";
bulkLoader.Load(eventTypeTable);
dbAdapterContainer.GetAdapter <EventTypeTableAdapter>().Fill(eventTypeTable);
return(eventTypeTable
.Where(row => Enum.TryParse(row.Name, out eventClassification))
.Select(row => new { EventClassification = eventClassification, row.ID })
.ToList()
.GroupBy(obj => obj.EventClassification)
.ToDictionary(grouping => grouping.Key, grouping =>
{
if (grouping.Count() > 1)
{
Log.Warn($"Found duplicate event type: {grouping.Key}");
}
return grouping.First().ID;
}));
}
private Dictionary <BreakerOperationType, int> GetBreakerOperationTypeLookup(DbAdapterContainer dbAdapterContainer)
{
BreakerOperationTypeDataTable breakerOperationTypeTable = new BreakerOperationTypeDataTable();
BreakerOperationType breakerOperationType = default(BreakerOperationType);
foreach (BreakerOperationType operationType in Enum.GetValues(typeof(BreakerOperationType)))
{
breakerOperationTypeTable.AddBreakerOperationTypeRow(operationType.ToString(), operationType.ToString());
}
BulkLoader bulkLoader = new BulkLoader();
bulkLoader.Connection = dbAdapterContainer.Connection;
bulkLoader.CommandTimeout = dbAdapterContainer.CommandTimeout;
bulkLoader.MergeTableFormat = "MERGE INTO {0} AS Target " +
"USING {1} AS Source " +
"ON Source.Name = Target.Name " +
"WHEN NOT MATCHED THEN " +
" INSERT (Name, Description) " +
" VALUES (Source.Name, Source.Description);";
bulkLoader.Load(breakerOperationTypeTable);
dbAdapterContainer.GetAdapter <BreakerOperationTypeTableAdapter>().Fill(breakerOperationTypeTable);
foreach (IGrouping <string, BreakerOperationTypeRow> grouping in breakerOperationTypeTable.GroupBy(row => row.Name))
{
if (grouping.Count() > 1)
{
Log.Warn($"Found duplicate breaker operation type: {grouping.Key}");
}
}
return(breakerOperationTypeTable
.Where(row => Enum.TryParse(row.Name, out breakerOperationType))
.Select(row => new { BreakerOperationType = breakerOperationType, row.ID })
.ToList()
.DistinctBy(obj => obj.BreakerOperationType)
.ToDictionary(obj => obj.BreakerOperationType, obj => obj.ID));
}
public override void Load(DbAdapterContainer dbAdapterContainer)
{
EventDataTable eventTable;
Dictionary <EventKey, EventRow> eventLookup;
EventRow eventRow;
BulkLoader bulkLoader;
eventTable = dbAdapterContainer.GetAdapter <EventTableAdapter>().GetDataByFileGroup(m_meterDataSet.FileGroup.ID);
eventLookup = eventTable
.Where(evt => evt.MeterID == m_meterDataSet.Meter.ID)
.GroupBy(CreateEventKey)
.ToDictionary(grouping => grouping.Key, grouping =>
{
if (grouping.Count() > 1)
{
Log.Warn($"Found duplicate events for meter {m_meterDataSet.Meter.AssetKey}: {string.Join(", ", grouping.Select(evt => evt.ID))}");
}
return(grouping.First());
});
foreach (Tuple <EventKey, BreakerOperationRow> breakerOperation in m_breakerOperations)
{
if (eventLookup.TryGetValue(breakerOperation.Item1, out eventRow))
{
breakerOperation.Item2.EventID = eventRow.ID;
m_breakerOperationTable.AddBreakerOperationRow(breakerOperation.Item2);
}
}
bulkLoader = new BulkLoader();
bulkLoader.Connection = dbAdapterContainer.Connection;
bulkLoader.CommandTimeout = dbAdapterContainer.CommandTimeout;
bulkLoader.Load(m_breakerOperationTable);
}
private static void LoadEventSentEmail(EventRow eventRow, EventDataTable systemEvent, int sentEmailID)
{
BulkLoader bulkLoader;
DataTable eventSentEmailTable;
using (AdoDataConnection connection = new AdoDataConnection(s_dbAdapterContainer.Connection, typeof(SqlDataAdapter), false))
{
// Query an empty table with matching schema --
// union table to itself to eliminate unique key constraints
eventSentEmailTable = connection.RetrieveData("SELECT * FROM EventSentEmail WHERE 1 IS NULL UNION ALL SELECT * FROM EventSentEmail WHERE 1 IS NULL");
eventSentEmailTable.TableName = "EventSentEmail";
}
foreach (MeterData.EventRow evt in systemEvent)
{
if (eventRow.LineID == evt.LineID)
eventSentEmailTable.Rows.Add(0, evt.ID, sentEmailID);
}
bulkLoader = new BulkLoader();
bulkLoader.Connection = s_dbAdapterContainer.Connection;
bulkLoader.CommandTimeout = s_dbAdapterContainer.CommandTimeout;
bulkLoader.Load(eventSentEmailTable);
}
public override void Load(DbAdapterContainer dbAdapterContainer)
{
BulkLoader bulkLoader;
EventTableAdapter eventAdapter;
EventDataTableAdapter eventDataAdapter;
Dictionary <EventKey, MeterData.EventRow> eventLookup;
Dictionary <WaveformKey, MeterData.EventDataRow> eventDataLookup;
MeterData.EventRow eventRow;
if (m_eventDataTable.Count == 0)
{
return;
}
Log.Info("Loading event data into the database...");
// Create the bulk loader for loading data into the database
bulkLoader = new BulkLoader();
bulkLoader.Connection = dbAdapterContainer.Connection;
bulkLoader.CommandTimeout = dbAdapterContainer.CommandTimeout;
// Write event data to the database
bulkLoader.Load(m_eventDataTable);
// Query database for event data IDs and store them in a lookup table by waveform key
eventDataAdapter = dbAdapterContainer.GetAdapter <EventDataTableAdapter>();
eventDataAdapter.FillByFileGroup(m_eventDataTable, m_meterDataSet.FileGroup.ID);
eventDataLookup = m_eventDataTable
.GroupBy(CreateWaveformKey)
.ToDictionary(grouping => grouping.Key, grouping =>
{
if (grouping.Count() > 1)
{
Log.Warn($"Duplicate waveform found for meter {m_meterDataSet.Meter.AssetKey}: {string.Join(", ", grouping.Select(waveform => waveform.ID))}");
}
return(grouping.First());
});
// Update the event rows with the IDs from the event data table
foreach (Tuple <WaveformKey, MeterData.EventRow> tuple in m_eventList)
{
tuple.Item2.EventDataID = eventDataLookup[tuple.Item1].ID;
m_eventTable.AddEventRow(tuple.Item2);
}
// Write events to the database
bulkLoader.Load(m_eventTable);
// Query database for events and store them in a lookup table by event key
eventAdapter = dbAdapterContainer.GetAdapter <EventTableAdapter>();
eventAdapter.FillByFileGroup(m_eventTable, m_meterDataSet.FileGroup.ID);
eventLookup = m_eventTable
.GroupBy(CreateEventKey)
.ToDictionary(grouping => grouping.Key, grouping =>
{
if (grouping.Count() > 1)
{
Log.Warn($"Duplicate event found for meter {m_meterDataSet.Meter.AssetKey}: {string.Join(", ", grouping.Select(evt => evt.ID))}");
}
return(grouping.First());
});
// Update the disturbance rows with the IDs from the event table
foreach (Tuple <EventKey, MeterData.DisturbanceRow> tuple in m_disturbanceList)
{
if (eventLookup.TryGetValue(tuple.Item1, out eventRow))
{
tuple.Item2.EventID = eventRow.ID;
m_disturbanceTable.AddDisturbanceRow(tuple.Item2);
}
}
// Write disturbances to the database
bulkLoader.Load(m_disturbanceTable);
Log.Info(string.Format("Loaded {0} events into the database.", m_eventTable.Count));
}