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