public override void Execute(MeterDataSet meterDataSet)
{
// Get a time range for querying each system event that contains events in this meter data set
SystemEventResource systemEventResource = meterDataSet.GetResource <SystemEventResource>();
List <SystemEventResource.SystemEvent> systemEvents = systemEventResource.SystemEvents;
if (systemEvents.Count == 0)
{
return;
}
using (AdoDataConnection connection = meterDataSet.CreateDbConnection())
{
TableOperations <openXDA.Model.Line> lineTable = new TableOperations <openXDA.Model.Line>(connection);
TableOperations <AssetLocation> meterLocationLineTable = new TableOperations <AssetLocation>(connection);
TableOperations <Event> eventTable = new TableOperations <Event>(connection);
TableOperations <DoubleEndedFaultDistance> doubleEndedFaultDistanceTable = new TableOperations <DoubleEndedFaultDistance>(connection);
TableOperations <FaultCurve> faultCurveTable = new TableOperations <FaultCurve>(connection);
List <MappingNode> processedMappingNodes = new List <MappingNode>();
foreach (SystemEventResource.SystemEvent systemEvent in systemEvents)
{
// Get the full collection of events from the database that comprise the system event that overlaps this time range
List <Event> dbSystemEvent = eventTable.GetSystemEvent(systemEvent.StartTime, systemEvent.EndTime, m_timeTolerance);
foreach (IGrouping <int, Event> lineGrouping in dbSystemEvent.GroupBy(evt => evt.AssetID))
{
// Make sure this line connects two known meter locations
int meterLocationCount = meterLocationLineTable.QueryRecordCountWhere("AssetID = {0}", lineGrouping.Key);
if (meterLocationCount != 2)
{
continue;
}
// Determine the length of the line
double lineLength = lineTable
.QueryRecordsWhere("ID = {0}", lineGrouping.Key)
.Select(line =>
{
line.ConnectionFactory = meterDataSet.CreateDbConnection;
return(line.Path[0].Length);
})
.DefaultIfEmpty(double.NaN)
.First();
if (double.IsNaN(lineLength))
{
continue;
}
// Determine the nominal impedance of the line
ComplexNumber nominalImpedance = new ComplexNumber(
lineTable.QueryRecordsWhere("ID = {0}", lineGrouping.Key).Select(line =>
{
line.ConnectionFactory = meterDataSet.CreateDbConnection;
return(line.Path[0].R1);
}).FirstOrDefault(),
lineTable.QueryRecordsWhere("ID = {0}", lineGrouping.Key).Select(line =>
{
line.ConnectionFactory = meterDataSet.CreateDbConnection;
return(line.Path[0].X1);
}).FirstOrDefault());
if (!nominalImpedance.AllAssigned)
{
continue;
}
int leftEventID = 0;
int rightEventID = 0;
VICycleDataGroup leftCycleDataGroup = null;
VICycleDataGroup rightCycleDataGroup = null;
// Attempt to match faults during this system event that occurred
// on one end of the line with faults that occurred during this
// system even on the other end of the line
List <Mapping> mappings = GetMappings(connection, lineGrouping);
foreach (Mapping mapping in mappings)
{
if (mapping.Left.FaultType == FaultType.None || mapping.Right.FaultType == FaultType.None)
{
continue;
}
// Get the cycle data for each of the two mapped faults
if (mapping.Left.Fault.EventID != leftEventID)
{
leftEventID = mapping.Left.Fault.EventID;
leftCycleDataGroup = GetCycleData(connection, leftEventID);
}
if (mapping.Right.Fault.EventID != rightEventID)
{
rightEventID = mapping.Right.Fault.EventID;
rightCycleDataGroup = GetCycleData(connection, rightEventID);
}
if (leftCycleDataGroup == null || rightCycleDataGroup == null)
{
continue;
}
if (leftCycleDataGroup.IA == null || leftCycleDataGroup.IB == null || leftCycleDataGroup.IC == null)
{
continue;
}
if (rightCycleDataGroup.IA == null || rightCycleDataGroup.IB == null || rightCycleDataGroup.IC == null)
{
continue;
}
// Make sure double-ended distance has not already been calculated and entered into the database
RecordRestriction recordRestriction =
new RecordRestriction("LocalFaultSummaryID = {0}", mapping.Left.Fault.ID) |
new RecordRestriction("RemoteFaultSummaryID = {0}", mapping.Left.Fault.ID) |
new RecordRestriction("LocalFaultSummaryID = {0}", mapping.Right.Fault.ID) |
new RecordRestriction("RemoteFaultSummaryID = {0}", mapping.Right.Fault.ID);
if (doubleEndedFaultDistanceTable.QueryRecordCount(recordRestriction) > 0)
{
continue;
}
// Initialize the mappings with additional data needed for double-ended fault location
mapping.Left.Initialize(connection, leftCycleDataGroup, m_systemFrequency);
mapping.Right.Initialize(connection, rightCycleDataGroup, m_systemFrequency);
// Execute the double-ended fault location algorithm
ExecuteFaultLocationAlgorithm(lineLength, nominalImpedance, mapping.Left, mapping.Right);
ExecuteFaultLocationAlgorithm(lineLength, nominalImpedance, mapping.Right, mapping.Left);
try
{
// Create rows in the DoubleEndedFaultDistance table
DoubleEndedFaultDistance leftDistance = CreateDoubleEndedFaultDistance(lineLength, mapping.Left, mapping.Right);
DoubleEndedFaultDistance rightDistance = CreateDoubleEndedFaultDistance(lineLength, mapping.Right, mapping.Left);
doubleEndedFaultDistanceTable.AddNewRecord(leftDistance);
doubleEndedFaultDistanceTable.AddNewRecord(rightDistance);
// Add these nodes to the collection of processed mapping nodes
processedMappingNodes.Add(mapping.Left);
processedMappingNodes.Add(mapping.Right);
}
catch (Exception ex)
{
// Ignore errors regarding unique key constraints
// which can occur as a result of a race condition
bool isUniqueViolation = ExceptionHandler.IsUniqueViolation(ex);
if (!isUniqueViolation)
{
throw;
}
}
}
}
// Create a row in the FaultCurve table for every event that now has double-ended fault distance curves
foreach (IGrouping <int, MappingNode> grouping in processedMappingNodes.GroupBy(node => node.Fault.EventID))
{
FaultCurve faultCurve = CreateFaultCurve(connection, grouping);
faultCurveTable.AddNewRecord(faultCurve);
}
}
}
}
private static void GenerateEmail(AdoDataConnection connection, int eventID)
{
XDocument htmlDocument;
List <Attachment> attachments;
string subject;
string html;
bool alreadySent;
TableOperations <EventType> eventTypeTable = new TableOperations <EventType>(connection);
EventType faultEventType = eventTypeTable.QueryRecordWhere("Name = 'Fault'");
TableOperations <Event> eventTable = new TableOperations <Event>(connection);
string eventDetail = connection.ExecuteScalar <string>("SELECT EventDetail FROM EventDetail WHERE EventID = {0}", eventID);
List <IGrouping <int, Guid> > templateGroups;
using (IDbCommand command = connection.Connection.CreateCommand())
{
Func <string, object, IDbDataParameter> createParameter = (name, value) =>
{
IDbDataParameter parameter = command.CreateParameter();
parameter.ParameterName = name;
parameter.Value = value;
return(parameter);
};
command.CommandText = "GetEventEmailRecipients";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(createParameter("@eventID", eventID));
IDataAdapter adapter = (IDataAdapter)Activator.CreateInstance(connection.AdapterType, command);
using (adapter as IDisposable)
{
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
templateGroups = dataSet.Tables[0]
.Select()
.GroupBy(row => row.ConvertField <int>("TemplateID"), row => row.ConvertField <Guid>("UserAccountID"))
.ToList();
}
}
foreach (IGrouping <int, Guid> templateGroup in templateGroups)
{
string template = connection.ExecuteScalar <string>("SELECT Template FROM XSLTemplate WHERE ID = {0}", templateGroup.Key);
string paramString = string.Join(",", templateGroup.Select((userAccountID, index) => $"{{{index}}}"));
string sql = $"SELECT Email FROM UserAccount WHERE Email IS NOT NULL AND Email <> '' AND ID IN ({paramString})";
DataTable emailTable = connection.RetrieveData(sql, templateGroup.Cast <object>().ToArray());
List <string> recipients = emailTable.Select().Select(row => row.ConvertField <string>("Email")).ToList();
htmlDocument = XDocument.Parse(eventDetail.ApplyXSLTransform(template), LoadOptions.PreserveWhitespace);
htmlDocument.TransformAll("format", element => element.Format());
attachments = new List <Attachment>();
try
{
htmlDocument.TransformAll("chart", (element, index) =>
{
string chartEventID = (string)element.Attribute("eventID") ?? "-1";
string cid = $"event{chartEventID}_chart{index:00}.png";
Stream image = ChartGenerator.ConvertToChartImageStream(connection, element);
Attachment attachment = new Attachment(image, cid);
attachment.ContentId = attachment.Name;
attachments.Add(attachment);
return(new XElement("img", new XAttribute("src", $"cid:{cid}")));
});
htmlDocument.TransformAll("pqi", (element, index) =>
{
return(PQIGenerator.GetPqiInformation(connection, element));
});
htmlDocument.TransformAll("structure", (element, index) =>
{
return(StructureLocationGenerator.GetStructureLocationInformation(element));
});
htmlDocument.TransformAll("lightning", (element, index) =>
{
return(LightningGenerator.GetLightningInfo(connection, element));
});
htmlDocument.TransformAll("treeProbability", (element, index) =>
{
return(TreeProbabilityGenerator.GetTreeProbability(element));
});
htmlDocument.TransformAll("faultType", (element, index) =>
{
return(FaultTypeGenerator.GetFaultType(element));
});
subject = (string)htmlDocument.Descendants("title").FirstOrDefault() ?? "Fault detected by openXDA";
html = htmlDocument.ToString(SaveOptions.DisableFormatting).Replace("&", "&").Replace("<", "<").Replace(">", ">");
alreadySent = false;
try
{
Event dequeuedEvent = eventTable.QueryRecordWhere("ID = {0}", eventID);
List <Event> systemEvent = eventTable
.GetSystemEvent(dequeuedEvent.StartTime, dequeuedEvent.EndTime, s_timeTolerance)
.Where(evt => dequeuedEvent.LineID == evt.LineID)
.ToList();
string systemEventIDs = string.Join(",", systemEvent.Where(row => row.LineID == dequeuedEvent.LineID).Select(row => row.ID));
string query =
$"SELECT SentEmail.ID " +
$"FROM " +
$" SentEmail JOIN " +
$" EventSentEmail ON EventSentEmail.SentEmailID = SentEmail.ID " +
$"WHERE " +
$" EventSentEmail.EventID IN ({systemEventIDs}) AND " +
$" SentEmail.Message = {{0}}";
int sentEmailID = connection.ExecuteScalar(-1, DataExtensions.DefaultTimeoutDuration, query, html);
alreadySent = (sentEmailID != -1);
if (!alreadySent)
{
sentEmailID = LoadSentEmail(connection, recipients, subject, html);
}
LoadEventSentEmail(connection, systemEvent, sentEmailID);
}
catch (Exception ex)
{
// Failure to load the email into the database should
// not prevent us from attempting to send the email
Log.Error(ex.Message, ex);
}
if (!alreadySent)
{
SendEmail(recipients, subject, html, attachments);
}
}
finally
{
foreach (Attachment attachment in attachments)
{
attachment.Dispose();
}
}
}
if (templateGroups.Any())
{
Log.Info($"All emails sent for event ID {eventID}.");
}
}
