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