private static void GenerateEmail(int eventID) { SystemInfoDataContext systemInfo; MeterInfoDataContext meterInfo; FaultLocationInfoDataContext faultInfo; EventTableAdapter eventAdapter; EventTypeTableAdapter eventTypeAdapter; int faultTypeID; string eventDetail; MeterData.EventRow eventRow; List <int> faultedMeters; List <int> meterGroups; List <Recipient> recipients; XslCompiledTransform transform; XDocument htmlDocument; List <Attachment> attachments; string subject; string html; systemInfo = s_dbAdapterContainer.GetAdapter <SystemInfoDataContext>(); meterInfo = s_dbAdapterContainer.GetAdapter <MeterInfoDataContext>(); faultInfo = s_dbAdapterContainer.GetAdapter <FaultLocationInfoDataContext>(); eventAdapter = s_dbAdapterContainer.GetAdapter <EventTableAdapter>(); eventTypeAdapter = s_dbAdapterContainer.GetAdapter <EventTypeTableAdapter>(); faultTypeID = eventTypeAdapter.GetData() .Where(eventType => eventType.Name == "Fault") .Select(eventType => eventType.ID) .FirstOrDefault(); eventDetail = eventAdapter.GetEventDetail(eventID); eventRow = eventAdapter.GetDataByID(eventID)[0]; faultedMeters = eventAdapter.GetSystemEvent(eventRow.StartTime, eventRow.EndTime, s_timeTolerance) .Where(evt => evt.LineID == eventRow.LineID) .Where(evt => evt.EventTypeID == faultTypeID) .Select(evt => evt.MeterID) .ToList(); meterGroups = meterInfo.GroupMeters .Where(groupMeter => faultedMeters.Contains(groupMeter.MeterID)) .Select(groupMeter => groupMeter.GroupID) .ToList(); foreach (FaultEmailTemplate template in faultInfo.FaultEmailTemplates.ToList()) { recipients = template.GetRecipients(systemInfo.Recipients, meterGroups); if (recipients.Count == 0) { continue; } using (StringReader templateReader = new StringReader(template.Template)) using (StringReader dataReader = new StringReader(eventDetail)) using (XmlReader xmlTemplateReader = XmlReader.Create(templateReader)) using (XmlReader xmlDataReader = XmlReader.Create(dataReader)) using (StringWriter transformWriter = new StringWriter()) { transform = new XslCompiledTransform(); transform.Load(xmlTemplateReader); transform.Transform(xmlDataReader, null, transformWriter); htmlDocument = XDocument.Parse(transformWriter.ToString(), LoadOptions.PreserveWhitespace); } htmlDocument.TransformAll("format", element => element.Format()); attachments = new List <Attachment>(); try { htmlDocument.TransformAll("chart", (element, index) => { string cid = $"chart{index:00}.png"; Stream image = ChartGenerator.ConvertToChartImageStream(s_dbAdapterContainer, element); Attachment attachment = new Attachment(image, cid); attachment.ContentId = attachment.Name; attachments.Add(attachment); return(new XElement("img", new XAttribute("src", $"cid:{cid}"))); }); subject = (string)htmlDocument.Descendants("title").FirstOrDefault() ?? "Fault detected by openXDA"; html = htmlDocument.ToString(SaveOptions.DisableFormatting).Replace("&", "&"); SendEmail(recipients, subject, html, attachments); LoadEmail(eventID, recipients, subject, html); } finally { foreach (Attachment attachment in attachments) { attachment.Dispose(); } } } }
// Static Methods public static Stream ConvertToChartImageStream(DbAdapterContainer dbAdapterContainer, XElement chartElement) { ChartGenerator chartGenerator; Lazy <DataRow> faultSummary; Lazy <double> systemFrequency; DateTime inception; DateTime clearing; int width; int height; double prefaultCycles; double postfaultCycles; string title; List <string> keys; List <string> names; DateTime startTime; DateTime endTime; int eventID; int faultID; // Read parameters from the XML data and set up defaults eventID = Convert.ToInt32((string)chartElement.Attribute("eventID") ?? "-1"); faultID = Convert.ToInt32((string)chartElement.Attribute("faultID") ?? "-1"); prefaultCycles = Convert.ToDouble((string)chartElement.Attribute("prefaultCycles") ?? "NaN"); postfaultCycles = Convert.ToDouble((string)chartElement.Attribute("postfaultCycles") ?? "NaN"); title = (string)chartElement.Attribute("yAxisTitle"); keys = GetKeys(chartElement); names = GetNames(chartElement); width = Convert.ToInt32((string)chartElement.Attribute("width")); height = Convert.ToInt32((string)chartElement.Attribute("height")); startTime = DateTime.MinValue; endTime = DateTime.MaxValue; using (AdoDataConnection connection = new AdoDataConnection(dbAdapterContainer.Connection, typeof(SqlDataAdapter), false)) { faultSummary = new Lazy <DataRow>(() => connection.RetrieveData("SELECT * FROM FaultSummary WHERE ID = {0}", faultID).Select().FirstOrDefault()); systemFrequency = new Lazy <double>(() => connection.ExecuteScalar(60.0D, "SELECT Value FROM Setting WHERE Name = 'SystemFrequency'")); // If prefaultCycles is specified and we have a fault summary we can use, // we can determine the start time of the chart based on fault inception if (!double.IsNaN(prefaultCycles) && (object)faultSummary.Value != null) { inception = faultSummary.Value.ConvertField <DateTime>("Inception"); startTime = inception.AddSeconds(-prefaultCycles / systemFrequency.Value); } // If postfaultCycles is specified and we have a fault summary we can use, // we can determine the start time of the chart based on fault clearing if (!double.IsNaN(postfaultCycles) && (object)faultSummary.Value != null) { inception = faultSummary.Value.ConvertField <DateTime>("Inception"); clearing = inception.AddSeconds(faultSummary.Value.ConvertField <double>("DurationSeconds")); endTime = clearing.AddSeconds(postfaultCycles / systemFrequency.Value); } // Create the chart generator to generate the chart chartGenerator = new ChartGenerator(dbAdapterContainer, eventID); using (Chart chart = chartGenerator.GenerateChart(title, keys, names, startTime, endTime)) { // Set the chart size based on the specified width and height; // this allows us to dynamically change font sizes and line // widths before converting the chart to an image SetChartSize(chart, width, height); // Determine if either the minimum or maximum of the y-axis is specified explicitly if ((object)chartElement.Attribute("yAxisMaximum") != null) { chart.ChartAreas[0].AxisY.Maximum = Convert.ToDouble((string)chartElement.Attribute("yAxisMaximum")); } if ((object)chartElement.Attribute("yAxisMinimum") != null) { chart.ChartAreas[0].AxisY.Minimum = Convert.ToDouble((string)chartElement.Attribute("yAxisMinimum")); } // If the calculation cycle is to be highlighted, determine whether the highlight should be in the range of a single index or a full cycle. // If we have a fault summary we can use, apply the appropriate highlight based on the calculation cycle if (string.Equals((string)chartElement.Attribute("highlightCalculation"), "index", StringComparison.OrdinalIgnoreCase)) { if ((object)faultSummary.Value != null) { int calculationCycle = faultSummary.Value.ConvertField <int>("CalculationCycle"); DateTime calculationTime = chartGenerator.ToDateTime(calculationCycle); double calculationPosition = chart.ChartAreas[0].AxisX.Minimum + (calculationTime - startTime).TotalSeconds; chart.ChartAreas[0].CursorX.Position = calculationPosition; } } else if (string.Equals((string)chartElement.Attribute("highlightCalculation"), "cycle", StringComparison.OrdinalIgnoreCase)) { if ((object)faultSummary.Value != null) { int calculationCycle = faultSummary.Value.ConvertField <int>("CalculationCycle"); DateTime calculationTime = chartGenerator.ToDateTime(calculationCycle); double calculationPosition = chart.ChartAreas[0].AxisX.Minimum + (calculationTime - startTime).TotalSeconds; chart.ChartAreas[0].CursorX.SelectionStart = calculationPosition; chart.ChartAreas[0].CursorX.SelectionEnd = calculationPosition + 1.0D / 60.0D; } } // Convert the generated chart to an image return(ConvertToImageStream(chart, ChartImageFormat.Png)); } } }
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}."); } }
private static Chart GenerateChart(ChartGenerator generator, XElement chartElement) { Chart chart; FaultSummaryTableAdapter faultSummaryAdapter; FaultLocationData.FaultSummaryDataTable faultSummaries; FaultLocationData.FaultSummaryRow faultSummary; int width; int height; double prefaultCycles; double postfaultCycles; string title; List <string> keys; List <string> names; DateTime startTime; DateTime endTime; int faultID; faultSummaryAdapter = s_dbAdapterContainer.GetAdapter <FaultSummaryTableAdapter>(); faultSummaries = faultSummaryAdapter.GetDataBy(generator.EventID); faultID = Convert.ToInt32((string)chartElement.Attribute("faultID")); faultSummary = faultSummaries .Where(row => row.ID == faultID) .FirstOrDefault(row => row.IsSelectedAlgorithm != 0); if ((object)faultSummary == null) { return(null); } prefaultCycles = Convert.ToDouble((string)chartElement.Attribute("prefaultCycles")); postfaultCycles = Convert.ToDouble((string)chartElement.Attribute("postfaultCycles")); title = (string)chartElement.Attribute("yAxisTitle"); keys = GetKeys(chartElement); names = GetNames(chartElement); startTime = faultSummary.Inception.AddSeconds(-prefaultCycles / 60.0D); endTime = faultSummary.Inception.AddSeconds(faultSummary.DurationSeconds).AddSeconds(postfaultCycles / 60.0D); chart = generator.GenerateChart(title, keys, names, startTime, endTime); width = Convert.ToInt32((string)chartElement.Attribute("width")); height = Convert.ToInt32((string)chartElement.Attribute("height")); SetChartSize(chart, width, height); if ((object)chartElement.Attribute("yAxisMaximum") != null) { chart.ChartAreas[0].AxisY.Maximum = Convert.ToDouble((string)chartElement.Attribute("yAxisMaximum")); } if ((object)chartElement.Attribute("yAxisMinimum") != null) { chart.ChartAreas[0].AxisY.Minimum = Convert.ToDouble((string)chartElement.Attribute("yAxisMinimum")); } if (string.Equals((string)chartElement.Attribute("highlightCalculation"), "index", StringComparison.OrdinalIgnoreCase)) { DateTime calculationTime = generator.ToDateTime(faultSummary.CalculationCycle); double calculationPosition = chart.ChartAreas[0].AxisX.Minimum + (calculationTime - startTime).TotalSeconds; chart.ChartAreas[0].CursorX.Position = calculationPosition; } else if (string.Equals((string)chartElement.Attribute("highlightCalculation"), "cycle", StringComparison.OrdinalIgnoreCase)) { DateTime calculationTime = generator.ToDateTime(faultSummary.CalculationCycle); double calculationPosition = chart.ChartAreas[0].AxisX.Minimum + (calculationTime - startTime).TotalSeconds; chart.ChartAreas[0].CursorX.SelectionStart = calculationPosition; chart.ChartAreas[0].CursorX.SelectionEnd = calculationPosition + 1.0D / 60.0D; } return(chart); }
private static void GenerateEmail(int eventID) { SystemInfoDataContext systemInfo; MeterInfoDataContext meterInfo; FaultLocationInfoDataContext faultInfo; EventTableAdapter eventAdapter; EventTypeTableAdapter eventTypeAdapter; int faultTypeID; string eventDetail; MeterData.EventRow eventRow; List <int> faultedMeters; List <int> meterGroups; List <Recipient> recipients; Dictionary <int, ChartGenerator> generators; XslCompiledTransform transform; XDocument htmlDocument; List <XElement> formatParents; List <XElement> chartElements; List <XElement> chartParents; Attachment[] attachments; string subject; string html; systemInfo = s_dbAdapterContainer.GetAdapter <SystemInfoDataContext>(); meterInfo = s_dbAdapterContainer.GetAdapter <MeterInfoDataContext>(); faultInfo = s_dbAdapterContainer.GetAdapter <FaultLocationInfoDataContext>(); eventAdapter = s_dbAdapterContainer.GetAdapter <EventTableAdapter>(); eventTypeAdapter = s_dbAdapterContainer.GetAdapter <EventTypeTableAdapter>(); faultTypeID = eventTypeAdapter.GetData() .Where(eventType => eventType.Name == "Fault") .Select(eventType => eventType.ID) .FirstOrDefault(); eventDetail = eventAdapter.GetEventDetail(eventID); eventRow = eventAdapter.GetDataByID(eventID)[0]; faultedMeters = eventAdapter.GetSystemEvent(eventRow.StartTime, eventRow.EndTime, s_timeTolerance) .Where(evt => evt.LineID == eventRow.LineID) .Where(evt => evt.EventTypeID == faultTypeID) .Select(evt => evt.MeterID) .ToList(); meterGroups = meterInfo.GroupMeters .Where(groupMeter => faultedMeters.Contains(groupMeter.MeterID)) .Select(groupMeter => groupMeter.GroupID) .ToList(); foreach (FaultEmailTemplate template in faultInfo.FaultEmailTemplates.ToList()) { recipients = template.GetRecipients(systemInfo.Recipients, meterGroups); if (recipients.Count == 0) { continue; } using (StringReader templateReader = new StringReader(template.Template)) using (StringReader dataReader = new StringReader(eventDetail)) using (XmlReader xmlTemplateReader = XmlReader.Create(templateReader)) using (XmlReader xmlDataReader = XmlReader.Create(dataReader)) using (StringWriter transformWriter = new StringWriter()) { transform = new XslCompiledTransform(); transform.Load(xmlTemplateReader); transform.Transform(xmlDataReader, null, transformWriter); htmlDocument = XDocument.Parse(transformWriter.ToString(), LoadOptions.PreserveWhitespace); } formatParents = htmlDocument .Descendants("format") .Select(element => element.Parent) .Distinct() .ToList(); foreach (XElement parent in formatParents) { parent.ReplaceNodes(parent.Nodes().Select(Format)); } chartElements = htmlDocument .Descendants("chart") .ToList(); for (int i = 0; i < chartElements.Count; i++) { chartElements[i].SetAttributeValue("cid", string.Format("chart{0:00}.png", i)); } chartParents = chartElements .Select(element => element.Parent) .Distinct() .ToList(); generators = new Dictionary <int, ChartGenerator>(); foreach (XElement parent in chartParents) { parent.ReplaceNodes(parent.Nodes().Select(ToImageElement)); } subject = (string)htmlDocument.Descendants("title").FirstOrDefault() ?? "Fault detected by openXDA"; html = htmlDocument.ToString(SaveOptions.DisableFormatting).Replace("&", "&"); attachments = null; try { attachments = chartElements .Select(element => { int chartEvent = Convert.ToInt32((string)element.Attribute("eventID")); ChartGenerator generator = generators.GetOrAdd(chartEvent, id => new ChartGenerator(s_dbAdapterContainer, id)); Stream image; Attachment attachment; using (Chart chart = GenerateChart(generator, element)) { image = ConvertToImage(chart, ChartImageFormat.Png); attachment = new Attachment(image, (string)element.Attribute("cid")); attachment.ContentId = attachment.Name; return(attachment); } }) .ToArray(); SendEmail(recipients, subject, html, attachments); LoadEmail(eventID, recipients, subject, html); } finally { if ((object)attachments != null) { foreach (Attachment attachment in attachments) { attachment.Dispose(); } } } } }
// Static Methods public static Stream ConvertToChartImageStream(DbAdapterContainer dbAdapterContainer, XElement chartElement) { ChartGenerator chartGenerator; Lazy<DataRow> faultSummary; Lazy<double> systemFrequency; DateTime inception; DateTime clearing; int width; int height; double prefaultCycles; double postfaultCycles; string title; List<string> keys; List<string> names; DateTime startTime; DateTime endTime; int eventID; int faultID; // Read parameters from the XML data and set up defaults eventID = Convert.ToInt32((string)chartElement.Attribute("eventID") ?? "-1"); faultID = Convert.ToInt32((string)chartElement.Attribute("faultID") ?? "-1"); prefaultCycles = Convert.ToDouble((string)chartElement.Attribute("prefaultCycles") ?? "NaN"); postfaultCycles = Convert.ToDouble((string)chartElement.Attribute("postfaultCycles") ?? "NaN"); title = (string)chartElement.Attribute("yAxisTitle"); keys = GetKeys(chartElement); names = GetNames(chartElement); width = Convert.ToInt32((string)chartElement.Attribute("width")); height = Convert.ToInt32((string)chartElement.Attribute("height")); startTime = DateTime.MinValue; endTime = DateTime.MaxValue; using (AdoDataConnection connection = new AdoDataConnection(dbAdapterContainer.Connection, typeof(SqlDataAdapter), false)) { faultSummary = new Lazy<DataRow>(() => connection.RetrieveData("SELECT * FROM FaultSummary WHERE ID = {0}", faultID).Select().FirstOrDefault()); systemFrequency = new Lazy<double>(() => connection.ExecuteScalar(60.0D, "SELECT Value FROM Setting WHERE Name = 'SystemFrequency'")); // If prefaultCycles is specified and we have a fault summary we can use, // we can determine the start time of the chart based on fault inception if (!double.IsNaN(prefaultCycles) && (object)faultSummary.Value != null) { inception = faultSummary.Value.ConvertField<DateTime>("Inception"); startTime = inception.AddSeconds(-prefaultCycles / systemFrequency.Value); } // If postfaultCycles is specified and we have a fault summary we can use, // we can determine the start time of the chart based on fault clearing if (!double.IsNaN(postfaultCycles) && (object)faultSummary.Value != null) { inception = faultSummary.Value.ConvertField<DateTime>("Inception"); clearing = inception.AddSeconds(faultSummary.Value.ConvertField<double>("DurationSeconds")); endTime = clearing.AddSeconds(postfaultCycles / systemFrequency.Value); } // Create the chart generator to generate the chart chartGenerator = new ChartGenerator(dbAdapterContainer, eventID); using (Chart chart = chartGenerator.GenerateChart(title, keys, names, startTime, endTime)) { // Set the chart size based on the specified width and height; // this allows us to dynamically change font sizes and line // widths before converting the chart to an image SetChartSize(chart, width, height); // Determine if either the minimum or maximum of the y-axis is specified explicitly if ((object)chartElement.Attribute("yAxisMaximum") != null) chart.ChartAreas[0].AxisY.Maximum = Convert.ToDouble((string)chartElement.Attribute("yAxisMaximum")); if ((object)chartElement.Attribute("yAxisMinimum") != null) chart.ChartAreas[0].AxisY.Minimum = Convert.ToDouble((string)chartElement.Attribute("yAxisMinimum")); // If the calculation cycle is to be highlighted, determine whether the highlight should be in the range of a single index or a full cycle. // If we have a fault summary we can use, apply the appropriate highlight based on the calculation cycle if (string.Equals((string)chartElement.Attribute("highlightCalculation"), "index", StringComparison.OrdinalIgnoreCase)) { if ((object)faultSummary.Value != null) { int calculationCycle = faultSummary.Value.ConvertField<int>("CalculationCycle"); DateTime calculationTime = chartGenerator.ToDateTime(calculationCycle); double calculationPosition = chart.ChartAreas[0].AxisX.Minimum + (calculationTime - startTime).TotalSeconds; chart.ChartAreas[0].CursorX.Position = calculationPosition; } } else if (string.Equals((string)chartElement.Attribute("highlightCalculation"), "cycle", StringComparison.OrdinalIgnoreCase)) { if ((object)faultSummary.Value != null) { int calculationCycle = faultSummary.Value.ConvertField<int>("CalculationCycle"); DateTime calculationTime = chartGenerator.ToDateTime(calculationCycle); double calculationPosition = chart.ChartAreas[0].AxisX.Minimum + (calculationTime - startTime).TotalSeconds; chart.ChartAreas[0].CursorX.SelectionStart = calculationPosition; chart.ChartAreas[0].CursorX.SelectionEnd = calculationPosition + 1.0D / 60.0D; } } // Convert the generated chart to an image return ConvertToImageStream(chart, ChartImageFormat.Png); } } }
private static void GenerateEmail(int eventID) { SystemInfoDataContext systemInfo; MeterInfoDataContext meterInfo; FaultLocationInfoDataContext faultInfo; EventTableAdapter eventAdapter; EventTypeTableAdapter eventTypeAdapter; EventRow eventRow; EventDataTable systemEvent; int faultTypeID; string eventDetail; XDocument htmlDocument; List <Attachment> attachments; string subject; string html; bool alreadySent; systemInfo = s_dbAdapterContainer.GetAdapter <SystemInfoDataContext>(); meterInfo = s_dbAdapterContainer.GetAdapter <MeterInfoDataContext>(); faultInfo = s_dbAdapterContainer.GetAdapter <FaultLocationInfoDataContext>(); eventAdapter = s_dbAdapterContainer.GetAdapter <EventTableAdapter>(); eventTypeAdapter = s_dbAdapterContainer.GetAdapter <EventTypeTableAdapter>(); faultTypeID = eventTypeAdapter.GetData() .Where(eventType => eventType.Name == "Fault") .Select(eventType => eventType.ID) .FirstOrDefault(); // Load the system event before the eventDetail record to avoid race conditions causing missed emails eventRow = eventAdapter.GetDataByID(eventID)[0]; systemEvent = eventAdapter.GetSystemEvent(eventRow.StartTime, eventRow.EndTime, s_timeTolerance); eventDetail = eventAdapter.GetEventDetail(eventID); List <IGrouping <int, Guid> > templateGroups; using (SqlCommand command = new SqlCommand("GetEventEmailRecipients", s_dbAdapterContainer.Connection)) using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { DataTable recipientTable = new DataTable(); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@eventID", eventID); adapter.Fill(recipientTable); templateGroups = recipientTable .Select() .GroupBy(row => row.ConvertField <int>("TemplateID"), row => row.ConvertField <Guid>("UserAccountID")) .ToList(); } foreach (IGrouping <int, Guid> templateGroup in templateGroups) { string template; List <string> recipients; using (AdoDataConnection connection = new AdoDataConnection(s_dbAdapterContainer.Connection, typeof(SqlDataAdapter), false)) { 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()); recipients = emailTable.Select().Select(row => row.ConvertField <string>("Email")).ToList(); } htmlDocument = XDocument.Parse(eventDetail.ApplyXSLTransform(template), LoadOptions.PreserveWhitespace); htmlDocument.TransformAll("format", element => element.Format()); htmlDocument.TransformAll("structure", element => { string structureString = ""; string lat = "0"; string lng = "0"; try { var doc = Dcsoup.Parse(new Uri(element.Attribute("url").Value + $"?id={element.Value}"), 5000); structureString = doc.Select("span[id=strno]").Text; lat = structureString.Split('(', ',', ')')[1]; lng = structureString.Split('(', ',', ')')[2]; } catch (Exception ex) { structureString = "Structure and location unavailable..."; return(new XElement("span", structureString)); } return(new XElement(new XElement("a", new XAttribute("href", $"http://www.google.com/maps/place/{lat},{lng}"), new XElement("span", structureString)))); }); attachments = new List <Attachment>(); try { htmlDocument.TransformAll("chart", (element, index) => { string cid = $"chart{index:00}.png"; Stream image = ChartGenerator.ConvertToChartImageStream(s_dbAdapterContainer, element); Attachment attachment = new Attachment(image, cid); attachment.ContentId = attachment.Name; attachments.Add(attachment); return(new XElement("img", new XAttribute("src", $"cid:{cid}"))); }); subject = (string)htmlDocument.Descendants("title").FirstOrDefault() ?? "Fault detected by openXDA"; html = htmlDocument.ToString(SaveOptions.DisableFormatting).Replace("&", "&"); alreadySent = false; try { int sentEmailID; using (AdoDataConnection connection = new AdoDataConnection(s_dbAdapterContainer.Connection, typeof(SqlDataAdapter), false)) { string systemEventIDs = string.Join(",", systemEvent.Where(row => row.LineID == eventRow.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}}"; sentEmailID = connection.ExecuteScalar(-1, DataExtensions.DefaultTimeoutDuration, query, html); } alreadySent = (sentEmailID != -1); if (!alreadySent) { sentEmailID = LoadSentEmail(recipients, subject, html); } LoadEventSentEmail(eventRow, 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}."); } }