public static ConvertToChartImageStream ( DbAdapterContainer dbAdapterContainer, System.Xml.Linq.XElement chartElement ) : Stream | ||
dbAdapterContainer | DbAdapterContainer | |
chartElement | System.Xml.Linq.XElement | |
return | Stream |
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 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(); } } } }
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}."); } }