ConvertToChartImageStream() public static method

public static ConvertToChartImageStream ( DbAdapterContainer dbAdapterContainer, System.Xml.Linq.XElement chartElement ) : Stream
dbAdapterContainer DbAdapterContainer
chartElement System.Xml.Linq.XElement
return Stream
Beispiel #1
0
        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("&amp;", "&").Replace("&lt;", "<").Replace("&gt;", ">");
                    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}.");
            }
        }
Beispiel #2
0
        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("&amp;", "&");
                    SendEmail(recipients, subject, html, attachments);
                    LoadEmail(eventID, recipients, subject, html);
                }
                finally
                {
                    foreach (Attachment attachment in attachments)
                    {
                        attachment.Dispose();
                    }
                }
            }
        }
Beispiel #3
0
        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("&amp;", "&");
                    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}.");
            }
        }