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