public ActionResult Get(int eventID)
{
using (AdoDataConnection connection = new AdoDataConnection(m_configuration["OpenXDA:ConnectionString"], m_configuration["OpenXDA:DataProviderString"]))
{
return(Ok(connection.RetrieveData(@"
With WorstSeverityCode as (
SELECT
EventID,
MAX(DisturbanceSeverity.SeverityCode) as SeverityCode
FROM
Disturbance INNER HASH JOIN
DisturbanceSeverity ON Disturbance.ID = DisturbanceSeverity.DisturbanceID
WHERE
PhaseID = (SELECT ID FROM Phase WHERE Name = 'Worst') AND
EventID = {0}
GROUP BY
EventID
), WorstSeverityRecord as (
SELECT
Disturbance.*, DisturbanceSeverity.SeverityCode, row_number() over (Partition By Disturbance.EventID Order By Disturbance.EventTypeID) as Ranking
FROM
Disturbance INNER HASH JOIN
DisturbanceSeverity ON Disturbance.ID = DisturbanceSeverity.DisturbanceID INNER HASH JOIN
WorstSeverityCode ON Disturbance.EventID = WorstSeverityCode.EventID AND DisturbanceSeverity.SeverityCode = WorstSeverityCode.SeverityCode
WHERE
PhaseID IN (SELECT ID FROM Phase WHERE Name != 'Worst') AND Disturbance.EventID = {0}
)
SELECT
Meter.Name as Meter,
Event.StartTime,
Phase.Name as Phase,
EventType.Name as EventType,
CAST(ROUND(WorstSeverityRecord.DurationCycles, 2) as VARCHAR(20)) + ' cycles' as Duration,
CAST(WorstSeverityRecord.Magnitude as VARCHAR(20)) + ' Amps (RMS)' as Magnitude,
CAST(ROUND((1 - WorstSeverityRecord.PerUnitMagnitude) * 100,1) as VARCHAR(20)) + '%' as SagDepth
FROM
Event JOIN
Meter ON Event.MeterID = Meter.ID LEFT JOIN
WorstSeverityRecord ON Ranking = 1 LEFT JOIN
Phase ON WorstSeverityRecord.PhaseID = Phase.ID LEFT JOIN
EventType ON WorstSeverityRecord.EventTypeID = EventType.ID
WHERE
Event.ID = {0}
", eventID)));
}
}
public IEnumerable <object> GetLightningData()
{
Dictionary <string, string> query = Request.QueryParameters();
int eventID = int.Parse(query["eventID"]);
using (AdoDataConnection connection = new AdoDataConnection("dbOpenXDA"))
{
const string QueryFormat =
"SELECT * " +
"FROM " +
" LightningStrike LEFT OUTER JOIN " +
" VaisalaExtendedLightningData ON VaisalaExtendedLightningData.LightningStrikeID = LightningStrike.ID " +
"WHERE EventID = {0}";
object ToLightningStrike(DataRow row) => new
{
Service = row.ConvertField <string>("Service"),
UTCTime = row.ConvertField <DateTime>("UTCTime"),
DisplayTime = row.ConvertField <string>("DisplayTime"),
Amplitude = row.ConvertField <double>("Amplitude"),
Latitude = row.ConvertField <double>("Latitude"),
Longitude = row.ConvertField <double>("Longitude"),
PeakCurrent = row.ConvertField <int>("PeakCurrent"),
FlashMultiplicity = row.ConvertField <int>("FlashMultiplicity"),
ParticipatingSensors = row.ConvertField <int>("ParticipatingSensors"),
DegreesOfFreedom = row.ConvertField <int>("DegreesOfFreedom"),
EllipseAngle = row.ConvertField <double>("EllipseAngle"),
SemiMajorAxisLength = row.ConvertField <double>("SemiMajorAxisLength"),
SemiMinorAxisLength = row.ConvertField <double>("SemiMinorAxisLength"),
ChiSquared = row.ConvertField <double>("ChiSquared"),
Risetime = row.ConvertField <double>("Risetime"),
PeakToZeroTime = row.ConvertField <double>("PeakToZeroTime"),
MaximumRateOfRise = row.ConvertField <double>("MaximumRateOfRise"),
CloudIndicator = row.ConvertField <bool>("CloudIndicator"),
AngleIndicator = row.ConvertField <bool>("AngleIndicator"),
SignalIndicator = row.ConvertField <bool>("SignalIndicator"),
TimingIndicator = row.ConvertField <bool>("TimingIndicator")
};
return(connection
.RetrieveData(QueryFormat, eventID)
.AsEnumerable()
.Select(ToLightningStrike));
}
}
public ActionResult Post([FromBody] PostData postData)
{
using (AdoDataConnection lightningConnection = new AdoDataConnection(m_configuration["Lightning:ConnectionString"], m_configuration["Lightning:DataProviderString"]))
{
DataTable data = lightningConnection.RetrieveData(@"
DECLARE @EndOfPeriod DATETIME2 = DATEADD(" + postData.Units + @",{1}, CAST({0} as DATETIME2));
DECLARE @BeginningOfPeriod DATETIME2 = DATEADD(" + postData.Units + @",-{1}*2, @EndOfPeriod);
SELECT DisplayTime, Amplitude, Latitude, Longitude, 'Weatherbug' as Service
FROM TX_Lightning.LIGHTNING_WEATHERBUG
WHERE
eventtime >= @BeginningOfPeriod and eventtime < @EndOfPeriod AND
Latitude BETWEEN {2} and {3} AND
Longitude BETWEEN {4} AND {5}
", postData.DateTime.Replace("T", " "), postData.Tolerance, postData.SWLat, postData.NELat, postData.SWLng, postData.NELng);
return(Ok(data));
}
}
public void ProcessAllData(bool onlyEmpty = false)
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
DataTable table = connection.RetrieveData(TempTableQuery(false) + Query);
if (table.Rows.Count == 0)
{
return;
}
IEnumerable <DisturbanceData> dd = table.Select().Select(row => new TableOperations <DisturbanceData>(connection).LoadRecord(row));
foreach (var assetGroup in dd.GroupBy(x => x.MeterID))
{
int meterID = assetGroup.Key;
if (meterID == 0)
{
continue;
}
OnLogStatusMessage(string.Format("PQMark aggregation: Processing {0}", assetGroup.Key));
foreach (var yearGroup in assetGroup.GroupBy(x => x.StartTime.Year))
{
int year = yearGroup.Key;
foreach (var dateGroup in yearGroup.GroupBy(x => x.StartTime.Month))
{
int month = dateGroup.Key;
DateTime startDate = new DateTime(yearGroup.Key, dateGroup.Key, 1);
DateTime endDate = startDate.AddMonths(1).AddSeconds(-1);
PQMarkAggregate record = new TableOperations <PQMarkAggregate>(connection).QueryRecordWhere("MeterID = {0} AND Year = {1} AND Month = {2}", meterID, year, month);
if (onlyEmpty && record != null)
{
continue;
}
ProcessMonthOfData(dateGroup, meterID, year, month, startDate, endDate);
}
}
}
OnLogStatusMessage(string.Format("PQMark data aggregation is complete..."));
}
}
private void GenerateEmail(int month, int year)
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
string sql = $"SELECT Email FROM UserAccount WHERE Email IS NOT NULL AND Email <> '' AND ID IN (SELECT UserAccountID FROM EmailGroupUserAccount WHERE EmailGroupID IN (SELECT EmailGroupID FROM EmailGroupType WHERE EmailTypeID IN (SELECT ID FROM EmailType WHERE EmailCategoryID = (SELECT ID FROM EmailCategory WHERE Name = 'PQReport'))))";
DataTable emailTable = connection.RetrieveData(sql);
List <string> recipients = emailTable.Select().Select(row => row.ConvertField <string>("Email")).ToList();
string template = connection.ExecuteScalar <string>("SELECT Template FROM XSLTemplate WHERE Name = 'PQReport'");
string data = connection.ExecuteScalar <string>(@"
SELECT
{0} as [Month],
{1} as [Year],
(select value from DashSettings where Name = 'System.XDAInstance') as [XDALink],
(SELECT
Meter.AssetKey as [Meter],
Report.Results as [Result],
Report.ID as [ReportID]
FROM
Report JOIN
Meter ON Report.MeterID = Meter.ID
WHERE
Month = {0} AND
Year = {1}
FOR XML RAW('Report') ,TYPE, ELEMENTS) as [Reports]
FOR XML RAW ('PQReport'),TYPE, ELEMENTS
", month, year);
XDocument htmlDocument = XDocument.Parse(data.ApplyXSLTransform(template), LoadOptions.PreserveWhitespace);
try
{
string subject = (string)htmlDocument.Descendants("title").FirstOrDefault() ?? "Fault detected by openXDA";
string html = htmlDocument.ToString(SaveOptions.DisableFormatting).Replace("&", "&").Replace("<", "<").Replace(">", ">");
SendEmail(recipients, subject, html);
LoadSentEmail(connection, recipients, subject, html);
}
catch (Exception ex)
{
Log.Error(ex.ToString());
}
}
}
private IEnumerable <string> GetOpenMICMeters()
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
DataTable table = connection.RetrieveData(@"
SELECT AdditionalFieldValue.Value
FROM
AdditionalFieldValue JOIN
AdditionalField ON AdditionalFieldValue.AdditionalFieldID = AdditionalField.ID
WHERE
AdditionalField.ParentTable = 'Meter' AND
AdditionalField.FieldName = 'OpenMICAcronym'
");
return(table.Select().Select(x => x["Value"].ToString()));
}
//return ControllerHelpers.Get<IEnumerable<string>>("OpenMIC", "api/Operations/Meters");
}
private IOrderedEnumerable <PeriodicMeasurements> GetMeasurementCharacteristicsForWebReport()
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
string target = "PeriodicDataDisplayMeasurementCharacteristicsForWebReport";
IOrderedEnumerable <PeriodicMeasurements> data = (IOrderedEnumerable <PeriodicMeasurements>)s_memoryCache.Get(target);
if (data == null)
{
string query = @"
SELECT DISTINCT
MeasurementType.Name as MeasurementType,
MeasurementType.ID as MeasurementTypeID,
MeasurementCharacteristic.Name as MeasurementCharacteristic,
MeasurementCharacteristic.ID as MeasurementCharacteristicID
FROM
Channel JOIN
MeasurementCharacteristic ON MeasurementCharacteristic.ID = Channel.MeasurementCharacteristicID JOIN
MeasurementType ON MeasurementType.ID = Channel.MeasurementTypeID
WHERE
Channel.ID IN (
SELECT
Channel.ID
FROM
StepChangeMeasurement JOIN
PQMeasurement ON StepChangeMeasurement.PQMeasurementID = PQMeasurement.ID LEFT JOIN
Channel ON
PQMeasurement.MeasurementTypeID = Channel.MeasurementTypeID AND
PQMeasurement.MeasurementCharacteristicID = Channel.MeasurementCharacteristicID
)
";
data = connection.RetrieveData(query).Select().Select(row => new PeriodicMeasurements()
{
MeasurementType = row["MeasurementType"].ToString(), MeasurementTypeID = int.Parse(row["MeasurementTypeID"].ToString()), MeasurementCharacteristic = row["MeasurementCharacteristic"].ToString(), MeasurementCharacteristicID = int.Parse(row["MeasurementCharacteristicID"].ToString())
}).OrderBy(x => x.MeasurementCharacteristic).ThenBy(x => x.MeasurementType);
s_memoryCache.Add(target, data, new CacheItemPolicy {
SlidingExpiration = TimeSpan.FromMinutes(1.0D)
});
}
return(data);
}
}
public IHttpActionResult GetTreeProbability(int eventID)
{
try
{
using (AdoDataConnection connection = new AdoDataConnection(SettingsCategory))
{
string query = @"
SELECT
Event.ID,
FaultSummary.Inception as FaultTime,
Event.AssetID,
Meter.Name as StationName,
Location.LocationKey as StationID,
LineView.AssetKey as LineAssetKey,
LineView.AssetName as LineName,
LineView.Length,
ROUND(FaultSummary.Distance,2) as FaultDistance,
FaultSummary.FaultType,
ROUND(FaultSummary.DurationCycles,2) as FaultDuration,
FaultSummary.CurrentMagnitude,
FaultSummary.ID as FaultID,
DoubleEndedFaultDistance.Distance as DblDist,
FaultCauseMetrics.TreeFaultResistance as TreeFaultResistance
FROM
Event inner join
Meter on Event.MeterID = Meter.ID inner join
Location on Meter.LocationID = Location.ID inner join
LineView on Event.AssetID = LineView.ID inner join
FaultSummary on Event.ID = FaultSummary.EventID and [IsSelectedAlgorithm] = 1 AND IsSuppressed = 0 AND IsValid <> 0 left join
FaultCauseMetrics ON Event.ID = FaultCauseMetrics.EventID AND FaultCauseMetrics.FaultNumber = 1 left join
DoubleEndedFaultDistance on FaultSummary.ID = DoubleEndedFaultDistance.LocalFaultSummaryID
WHERE
Event.ID = {0}
";
return(Ok(connection.RetrieveData(query, eventID)));
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
public ActionResult Get(int eventID)
{
using (AdoDataConnection connection = new AdoDataConnection(m_configuration["OpenXDA:ConnectionString"], m_configuration["OpenXDA:DataProviderString"]))
{
return(Ok(connection.RetrieveData(@"
SELECT
TOP 1
e2.*,
DATEDIFF(SECOND, e1.StartTime, e2.StartTime) as Difference
FROM
Event e1 JOIN
Event e2 ON e1.MeterID = e2.MeterID AND e2.ID != {0}
WHERE
e1.ID = {0} AND e1.StartTime < e2.StartTime
ORDER BY
Difference ASC
", eventID)));
}
}
public IHttpActionResult GetExtendedDataBases()
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
try
{
TableNameAttribute tableNameAttribute;
string tableName;
if (typeof(T).TryGetAttribute(out tableNameAttribute))
{
tableName = tableNameAttribute.TableName;
}
else
{
tableName = typeof(T).Name;
}
string query = @"SELECT MIN(UpdatedOn) AS lastUpdate, AdditionalField.ExternalDB AS name
FROM
AdditionalField LEFT JOIN AdditionalFieldValue ON AdditionalField.ID = AdditionalFieldValue.AdditionalFieldID
WHERE AdditionalField.ParentTable = {0} AND AdditionalField.ExternalDB IS NOT NULL AND AdditionalField.ExternalDB <> ''
GROUP BY AdditionalField.ExternalDB";
DataTable table = connection.RetrieveData(query, tableName);
List <ExtDB> result = new List <ExtDB>();
foreach (DataRow row in table.Rows)
{
result.Add(new ExtDB()
{
name = row.ConvertField <string>("name"), lastupdate = row.ConvertField <DateTime>("lastUpdate")
});
}
return(Ok(result));
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
}
public IHttpActionResult GetSelectableMeterUsingSearchableList([FromBody] PostData postData)
{
string whereClause = BuildWhereClause(postData.Searches);
if (string.IsNullOrWhiteSpace(whereClause))
{
whereClause = "WHERE ID NOT IN (SELECT MeterID FROM ComplianceMeter)";
}
else
{
whereClause = whereClause + " AND ID NOT IN (SELECT MeterID FROM ComplianceMeter)";
}
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
string sql = @"
DECLARE @PivotColumns NVARCHAR(MAX) = N''
SELECT @PivotColumns = @PivotColumns + '[AFV_' + t.FieldName + '],'
FROM (Select DISTINCT FieldName FROM AdditionalField WHERE ParentTable = 'Meter') AS t
DECLARE @SQLStatement NVARCHAR(MAX) = N'
SELECT * FROM (
SELECT m.*, (CONCAT(''AFV_'',af.FieldName)) AS FieldName, afv.Value FROM
Meter m LEFT JOIN
AdditionalField af on af.ParentTable = ''Meter'' LEFT JOIN
AdditionalFieldValue afv ON m.ID = afv.ParentTableID AND af.ID = afv.AdditionalFieldID
) as t
PIVOT(
MAX(t.Value)
FOR t.FieldName in (' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
) as pvt
" + whereClause.Replace("'", "''") + @"
ORDER BY " + postData.OrderBy + " " + (postData.Ascending ? "ASC" : "DESC") + @"
'
exec sp_executesql @SQLStatement";
DataTable table = connection.RetrieveData(sql, "");
return(Ok(table));
}
}
public DataTable GetTimeCorrelatedSags()
{
Dictionary <string, string> query = Request.QueryParameters();
int eventID = int.Parse(query["eventId"]);
if (eventID <= 0)
{
return(new DataTable());
}
using (AdoDataConnection connection = new AdoDataConnection("dbOpenXDA"))
{
double timeTolerance = connection.ExecuteScalar <double>("SELECT Value FROM Setting WHERE Name = 'TimeTolerance'");
DateTime startTime = connection.ExecuteScalar <DateTime>("SELECT StartTime FROM Event WHERE ID = {0}", eventID);
DateTime endTime = connection.ExecuteScalar <DateTime>("SELECT EndTime FROM Event WHERE ID = {0}", eventID);
DateTime adjustedStartTime = startTime.AddSeconds(-timeTolerance);
DateTime adjustedEndTime = endTime.AddSeconds(timeTolerance);
DataTable dataTable = connection.RetrieveData(TimeCorrelatedSagsSQL, adjustedStartTime, adjustedEndTime);
return(dataTable);
}
}
public IHttpActionResult Get(int siteID, int month, int day, int year)
{
try
{
using (AdoDataConnection connection = new AdoDataConnection("dbOpenXDA"))
{
DataTable table = connection.RetrieveData(@"
DECLARE @MeterID AS INT = {0}
DECLARE @EventDate as DateTime = {1}
select
Distinct [dbo].[Channel].[ID] as channelid,
@EventDate as date,
[dbo].[Channel].[Name] as channelname,
[dbo].[Meter].[ID] as meterid,
[dbo].[MeasurementType].[Name] as measurementtype,
[dbo].[MeasurementCharacteristic].[Name] as characteristic,
[dbo].[Phase].[Name] as phasename,
[dbo].[ChannelDataQualitySummary].[ExpectedPoints] as ExpectedPoints,
[dbo].[ChannelDataQualitySummary].[GoodPoints] as GoodPoints,
[dbo].[ChannelDataQualitySummary].[LatchedPoints] as LatchedPoints,
[dbo].[ChannelDataQualitySummary].[UnreasonablePoints] as UnreasonablePoints,
[dbo].[ChannelDataQualitySummary].[NoncongruentPoints] as NoncongruentPoints,
[dbo].[ChannelDataQualitySummary].[DuplicatePoints] as DuplicatePoints
from [dbo].[ChannelDataQualitySummary]
join [dbo].[Channel] on [dbo].[ChannelDataQualitySummary].[ChannelID] = [dbo].[Channel].[ID]
join [dbo].[Meter] on [dbo].[Channel].[MeterID] = @MeterID
join [dbo].[MeasurementType] on [dbo].[MeasurementType].[ID] = [dbo].[Channel].[MeasurementTypeID]
join [dbo].[MeasurementCharacteristic] on [dbo].[MeasurementCharacteristic].[ID] = [dbo].[Channel].[MeasurementCharacteristicID]
join [dbo].[Phase] on [dbo].[Phase].[ID] = [dbo].[Channel].[PhaseID]
where [dbo].[ChannelDataQualitySummary].[Date] = @EventDate and [dbo].[Meter].[ID] = @MeterID
", siteID, new DateTime(year, month, day));
return(Ok(table));
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
public ActionResult GetComponents(int eventID)
{
using (AdoDataConnection pqiConnection = new AdoDataConnection(m_configuration["IndustrialPQ:ConnectionString"], m_configuration["IndustrialPQ:DataProviderString"]))
using (AdoDataConnection upqiConnection = new AdoDataConnection(m_configuration["UserIndustrialPQ:ConnectionString"], m_configuration["UserIndustrialPQ:DataProviderString"]))
using (AdoDataConnection xdaConnection = new AdoDataConnection(m_configuration["OpenXDA:ConnectionString"], m_configuration["OpenXDA:DataProviderString"]))
{
int facilityID = xdaConnection.ExecuteScalar <int>("SELECT FacilityID FROM MeterFacility WHERE MeterID = (SELECT MeterID FROM Event WHERE ID = {0})", eventID);
DataTable pqiTable = pqiConnection.RetrieveData(@"
select
asec.Title,
tc.TestCurveID,
ac.CurveDB
From
UserIndustrialPQ.dbo.FacilityAudit fa JOIN
UserIndustrialPQ.dbo.AuditSection asec on asec.FacilityAuditID = fa.FacilityAuditID JOIN
UserIndustrialPQ.dbo.AuditCurve ac on ac.AuditSectionID = asec.AuditSectionID JOIN
IndustrialPQ.dbo.TestCurve tc on ac.CurveID = tc.TestCurveID JOIN
IndustrialPQ.dbo.Component com on tc.ComponentID = com.ComponentID
WHERE
fa.FacilityID = {0} AND ac.CurveDB = 'EPRI'
", facilityID);
DataTable upqiTable = upqiConnection.RetrieveData(@"
select
asec.Title,
tc.TestCurveID,
ac.CurveDB
From
UserIndustrialPQ.dbo.FacilityAudit fa JOIN
UserIndustrialPQ.dbo.AuditSection asec on asec.FacilityAuditID = fa.FacilityAuditID JOIN
UserIndustrialPQ.dbo.AuditCurve ac on ac.AuditSectionID = asec.AuditSectionID JOIN
UserIndustrialPQ.dbo.TestCurve tc on ac.CurveID = tc.TestCurveID JOIN
UserIndustrialPQ.dbo.Component com on tc.ComponentID = com.ComponentID
WHERE
fa.FacilityID = {0} AND ac.CurveDB = 'USER'
", facilityID);
pqiTable.Merge(upqiTable);
return(Ok(pqiTable));
}
}
public DataTable GetTimeCorrelatedSags()
{
Dictionary <string, string> query = Request.QueryParameters();
int eventID = int.Parse(query["eventId"]);
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
const string SQL =
"SELECT DISTINCT " +
" Event.ID AS EventID, " +
" EventType.Name AS EventType, " +
" Event.StartTime, " +
" Meter.Name AS MeterName, " +
" MeterLine.LineName " +
"FROM " +
" Disturbance JOIN " +
" EventType DisturbanceType ON Disturbance.EventTypeID = DisturbanceType.ID JOIN " +
" Event ON Disturbance.EventID = Event.ID JOIN " +
" EventType ON Event.EventTypeID = EventType.ID JOIN " +
" Meter ON Event.MeterID = Meter.ID JOIN " +
" MeterLine ON " +
" Event.MeterID = MeterLine.MeterID AND " +
" Event.LineID = MeterLine.LineID " +
"WHERE " +
" DisturbanceType.Name = 'Sag' AND " +
" Disturbance.StartTime <= {1} AND " +
" Disturbance.EndTime >= {0} " +
"ORDER BY " +
" Event.StartTime, " +
" Meter.Name, " +
" MeterLine.LineName";
double timeTolerance = connection.ExecuteScalar <double>("SELECT Value FROM Setting WHERE Name = 'TimeTolerance'");
DateTime startTime = connection.ExecuteScalar <DateTime>("SELECT StartTime FROM Event WHERE ID = {0}", eventID);
DateTime endTime = connection.ExecuteScalar <DateTime>("SELECT EndTime FROM Event WHERE ID = {0}", eventID);
DateTime adjustedStartTime = startTime.AddSeconds(-timeTolerance);
DateTime adjustedEndTime = endTime.AddSeconds(timeTolerance);
DataTable dataTable = connection.RetrieveData(SQL, adjustedStartTime, adjustedEndTime);
return(dataTable);
}
}
public IHttpActionResult GetMeterAssets(int meterID, string sort, int ascending)
{
if (GetRoles == string.Empty || User.IsInRole(GetRoles))
{
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
DataTable records = connection.RetrieveData($@"
SELECT
Asset.ID,
Asset.VoltageKV,
Asset.AssetKey,
Asset.Description,
Asset.AssetName,
Asset.AssetTypeID,
AssetType.Name as AssetType,
COUNT(Channel.ID) as Channels
FROM
Asset JOIN
AssetType ON Asset.AssetTypeID = AssetType.ID JOIN
MeterAsset ON Asset.ID = MeterAsset.AssetID LEFT JOIN
Channel ON Asset.ID = Channel.AssetID AND Channel.MeterID = MeterAsset.MeterID
WHERE
MeterAsset.MeterID = {{0}}
GROUP BY
Asset.ID,
Asset.VoltageKV,
Asset.AssetKey,
Asset.Description,
Asset.AssetName,
Asset.AssetTypeID,
AssetType.Name
ORDER BY {sort} {(ascending == 0 ? "DESC" : "")}
", meterID);
return(Ok(records));
}
}
else
{
return(Unauthorized());
}
}
private IEnumerable <DisturbanceData> GetDisturbanceDatas(DateTime startDate, DateTime endDate)
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings")) {
IEnumerable <PQMarkCompanyMeter> meters = new TableOperations <PQMarkCompanyMeter>(connection).QueryRecordsWhere("Enabled = 1");
if (!meters.Any())
{
return(null);
}
DataTable table = connection.RetrieveData(
@"SELECT Event.MeterID, Disturbance.PerUnitMagnitude, Disturbance.StartTime, Disturbance.EndTime, Disturbance.DurationSeconds, Disturbance.DurationCycles
FROM Disturbance JOIN
Event ON Event.ID = Disturbance.EventID
WHERE Disturbance.PhaseID = (SELECT ID FROM Phase WHERE Name = 'Worst') AND
Event.MeterID IN (" + string.Join(",", meters.Select(x => x.MeterID)) + @") AND
Event.StartTime Between '" + startDate + @"' AND '" + endDate + @"'"
);
return(table.Select().Select(row => new TableOperations <DisturbanceData>(connection).LoadRecord(row)));
}
}
public DataTable GetHarmonics()
{
Dictionary <string, string> query = Request.QueryParameters();
int eventId = int.Parse(query["eventId"]);
using (AdoDataConnection connection = new AdoDataConnection("dbOpenXDA"))
{
DataTable dataTable = connection.RetrieveData(@"
SELECT
MeasurementType.Name + ' ' + Phase.Name as Channel,
SpectralData
FROM
SnapshotHarmonics JOIN
Channel ON Channel.ID = SnapshotHarmonics.ChannelID JOIN
MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID JOIN
Phase ON Channel.PhaseID = Phase.ID
WHERE EventID = {0}", eventId);
return(dataTable);
}
}
public IHttpActionResult GetMeterConfiguration(int eventID)
{
using (AdoDataConnection connection = new AdoDataConnection(SettingsCategory))
{
const string SQL = @"
SELECT
Meter.AssetKey as MeterKey,
FileGroupMeterConfiguration.MeterConfigurationID
FROM
FileGroup JOIN
Event ON Event.FileGroupID = FileGroup.ID LEFT JOIN
FileGroupMeterConfiguration ON FileGroup.ID = FileGroupMeterConfiguration.FileGroupID JOIN
Meter ON Event.MeterID = Meter.ID
WHERE
Event.ID = {0}
";
DataTable dataTable = connection.RetrieveData(SQL, eventID);
return(Ok(dataTable.Select().First().ItemArray));
}
}
public void ExportStatsToCSV(Stream returnStream, NameValueCollection requestParameters)
{
int eventId = int.Parse(requestParameters["eventId"]);
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
using (StreamWriter writer = new StreamWriter(returnStream))
{
DataTable dataTable = connection.RetrieveData("SELECT * FROM OpenSEEScalarStatView WHERE EventID = {0}", eventId);
DataRow row = dataTable.AsEnumerable().First();
Dictionary <string, string> dict = row.Table.Columns.Cast <DataColumn>().ToDictionary(c => c.ColumnName, c => row[c].ToString());
if (dict.Keys.Count() == 0)
{
return;
}
// Write the CSV header to the file
writer.WriteLine(string.Join(",", dict.Keys));
writer.WriteLine(string.Join(",", dict.Values));
}
}
public IHttpActionResult GetIncidentGroups(string modelName, [FromBody] JObject record)
{
int circuitID;
DateTime startTime;
DateTime endTime;
DataTable table;
// Proxy all other requests
SecurityPrincipal securityPrincipal = RequestContext.Principal as SecurityPrincipal;
if ((object)securityPrincipal == null || (object)securityPrincipal.Identity == null || !securityPrincipal.IsInRole("Viewer,Administrator"))
{
return(BadRequest($"User \"{RequestContext.Principal?.Identity.Name}\" is unauthorized."));
}
try
{
circuitID = record["circuitId"]?.Value <int>() ?? 0;
startTime = DateTime.Parse(record["startDate"].ToString());
endTime = DateTime.Parse(record["endDate"].ToString());
}
catch (Exception ex)
{
return(BadRequest($"{ex.ToString()}"));
}
using (AdoDataConnection conn = new AdoDataConnection("systemSettings"))
{
try
{
string s = $"select * from GetNearbyIncidentsByCircuit({circuitID},'{startTime.ToString()}', '{endTime.ToString()}', 0)";
table = conn.RetrieveData(s);
return(Ok(table));
}
catch (Exception ex)
{
return(BadRequest(ex.ToString()));
}
}
}
public List <EventView> Get(int templateID)
{
NameValueCollection queryParameters = Request.RequestUri.ParseQueryString();
string countValue = queryParameters["count"];
if (!int.TryParse(countValue, out int count))
{
count = 50;
}
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
string getTriggerSQL =
"SELECT TriggersEmailSQL " +
"FROM " +
" EventEmailParameters JOIN " +
" EmailType ON EventEmailParameters.EmailTypeID = EmailType.ID " +
"WHERE XSLTemplateID = {0}";
string triggerFormat = connection.ExecuteScalar <string>(getTriggerSQL, templateID);
string triggerSQL = string.Format(triggerFormat, "E.ID");
string getEvents =
$"SELECT TOP {count} EventView.* " +
$"FROM " +
$" Event as E CROSS APPLY " +
$" ({triggerSQL}) EmailTrigger(Value) JOIN" +
$" EventView ON E.ID = EventView.ID " +
$"WHERE EmailTrigger.Value <> 0 " +
$"ORDER BY EventView.StartTime DESC";
TableOperations <EventView> eventTable = new TableOperations <EventView>(connection);
return(connection.RetrieveData(getEvents)
.AsEnumerable()
.Select(eventTable.LoadRecord)
.ToList());
}
}
public IActionResult Get()
{
try
{
using (AdoDataConnection sCConnection = new AdoDataConnection(m_configuration["SystemCenter:ConnectionString"], m_configuration["SystemCenter:DataProviderString"]))
using (AdoDataConnection connection = new AdoDataConnection(m_configuration["OpenXDA:ConnectionString"], m_configuration["OpenXDA:DataProviderString"]))
{
string orgId = (User.Identity as ClaimsIdentity).Claims.FirstOrDefault(c => c.Type == "org_id")?.Value;
DataTable meters = sCConnection.RetrieveData(@"SELECT OpenXDAMeterID FROM CompanyMeter WHERE CompanyID = (SELECT ID FROM Company WHERE CompanyID = {0})", orgId);
if (meters.Rows.Count == 0)
{
return(Ok(new DataTable()));
}
return(Ok(connection.RetrieveData("SELECT * FROM Meter WHERE ID IN (" + string.Join(",", meters.Select().Select(row => row["OpenXDAMeterID"])) + ")").Select().OrderBy(x => x["Name"]).CopyToDataTable()));
}
}
catch (Exception ex) {
m_logger.LogError(ex.Message);
return(StatusCode(500, ex));
}
}
public Task <DataTable> GetChart(CancellationToken cancellationToken)
{
Dictionary <string, string> query = Request.QueryParameters();
string stat = query["stat"];
string measurementId = query["measurementID"];
string meterId = query["meterID"];
DateTime toDate = DateTime.ParseExact(query["date"], "yyyy-MM-dd", CultureInfo.CurrentCulture);
DateTime startDate = toDate.AddDays(-30);
string[] stats = { "Max", "CP99", "CP95", "Avg", "CP05", "CP01", "Min" };
string notSqlInjection = "Avg";
if (stats.Contains(stat))
{
notSqlInjection = stat;
}
return(Task.Factory.StartNew(() =>
{
string target = "Chart" + meterId + measurementId + stat + startDate.ToString();
if (s_memoryCache.Contains(target))
{
return (DataTable)s_memoryCache.Get(target);
}
else
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
DataTable table = connection.RetrieveData("SELECT DISTINCT Date, " + notSqlInjection + " as Value FROM PQTrendStat WHERE MeterID = (SELECT TOP 1 ID FROM METER WHERE Name = {0}) AND PQMeasurementTypeID = (SELECT TOP 1 ID FROM PQMeasurement WHERE Name = {1}) AND Date BETWEEN {2} AND {3}", meterId, measurementId, startDate, toDate);
s_memoryCache.Add(target, table, new CacheItemPolicy {
SlidingExpiration = TimeSpan.FromMinutes(10.0D)
});
return table;
}
}
}, cancellationToken));
}
public ActionResult GetCount()
{
using (AdoDataConnection sCConnection = new AdoDataConnection(m_configuration["SystemCenter:ConnectionString"], m_configuration["SystemCenter:DataProviderString"]))
using (AdoDataConnection connection = new AdoDataConnection(m_configuration["OpenXDA:ConnectionString"], m_configuration["OpenXDA:DataProviderString"]))
{
string orgId = (User.Identity as ClaimsIdentity).Claims.FirstOrDefault(c => c.Type == "org_id")?.Value;
DataTable meters = sCConnection.RetrieveData(@"SELECT OpenXDAMeterID FROM CompanyMeter WHERE CompanyID = (SELECT ID FROM Company WHERE CompanyID = {0})", orgId);
if (meters.Rows.Count == 0)
{
return(Ok(new DataTable()));
}
return(Ok(connection.ExecuteScalar <int>(@"
SELECT
COUNT(*)
FROM
Event
WHERE
StartTime BETWEEN DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AND GETDATE() AND
MeterID IN (" + string.Join(",", meters.Select().Select(row => row["OpenXDAMeterID"])) + @")
")));
}
}
public IHttpActionResult Get(int eventID, int timeWindow)
{
try
{
using (AdoDataConnection connection = new AdoDataConnection(SettingsCategory))
using (AdoDataConnection xdaConnection = new AdoDataConnection("dbOpenXDA"))
{
DateTime eventTime = xdaConnection.ExecuteScalar <DateTime>("SELECT StartTime FROM Event WHERE ID = {0}", eventID);
DataTable table = connection.RetrieveData(@"
SELECT
alarmdatetime as Time,
stationname + ' ' + alarmpoint as Alarm,
alarmstatus as Status
FROM soealarmdetailwithhierarchy
WHERE alarmdatetime between {0} and {1}
", eventTime.AddSeconds(-1 * timeWindow), eventTime.AddSeconds(timeWindow));
return(Ok(table));
}
}
catch (Exception ex) {
return(InternalServerError(ex));
}
}
public void ProcessMonthToDateData()
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
DateTime endDate = DateTime.UtcNow;
DateTime startDate = new DateTime(endDate.Year, endDate.Month, 1);
DataTable table = connection.RetrieveData(TempTableQuery(true) + Query, startDate, endDate);
if (table.Rows.Count == 0)
{
return;
}
IEnumerable <DisturbanceData> dd = table.Select().Select(row => new TableOperations <DisturbanceData>(connection).LoadRecord(row));
foreach (var assetGroup in dd.GroupBy(x => x.MeterID))
{
ProcessMonthOfData(assetGroup, assetGroup.Key, startDate.Month, startDate.Year, startDate, endDate);
}
}
OnLogStatusMessage(string.Format("PQMark data aggregation is complete..."));
}
/// <summary>
/// Checks if the alarms in Grafana match the alarms in openHistorian and updates them if necessary.
/// </summary>
/// <param name="query">The Grafana query including the panelID and dashboardID.</param>
/// <param name="alarms">Relevant alarms in the OpenHistorian.</param>
public static void UpdateAlerts(QueryRequest query, List <GrafanaAlarm> alarms)
{
// Start by getting the Alarms that are in the Grafana instance
using (AdoDataConnection connection = GetGrafanaDB())
{
DataTable grafanaAlarms = connection.RetrieveData(GrafanaAlarmQuery, query.dashboardId, query.panelId);
List <DataRow> alarmsToUpdate = grafanaAlarms.AsEnumerable()
.Where(item => CheckUpdate(alarms, item))
.ToList();
// TODO: Should these alarms be removed? Result currently unused.
HashSet <int> alarmIDs = new HashSet <int>(alarms.Select(alarm => alarm.ID));
List <DataRow> removeAlarms = grafanaAlarms.AsEnumerable().Where(row => alarmIDs.Contains(row.Field <int>("value"))).ToList();
HashSet <int> alarmRecordIDs = new HashSet <int>(grafanaAlarms.AsEnumerable().Select(row => row.Field <int>("value")));
List <GrafanaAlarm> alarmsToAdd = alarms.Where(alarm => alarmRecordIDs.Contains(alarm.ID)).ToList();
alarmsToUpdate.ForEach(alarmRecord => Update(alarms, alarmRecord, connection));
alarmsToAdd.ForEach(alarm => Add(alarm, query, connection));
}
}
/// <summary>
/// Retrieves <see cref="MeasurementGroup"/> information for the group with the given ID.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="groupID">The ID of the measurement group to be retrieved.</param>
/// <returns>Measurement group with the given ID.</returns>
public static MeasurementGroup GetMeasurementGroup(AdoDataConnection database, int groupID)
{
DataTable measurementGroupTable;
bool createdConnection = false;
DataRow row;
try
{
createdConnection = CreateConnection(ref database);
measurementGroupTable = database.RetrieveData(DefaultTimeout, "SELECT * FROM MeasurementGroup WHERE ID = {0}", groupID);
if (measurementGroupTable.Rows.Count == 0)
{
return(null);
}
row = measurementGroupTable.Rows[0];
MeasurementGroup measurementGroup = new MeasurementGroup()
{
NodeID = database.Guid(row, "NodeID"),
ID = row.ConvertField <int>("ID"),
Name = row.Field <string>("Name"),
Description = row.Field <object>("Description").ToNonNullString(),
FilterExpression = row.Field <object>("FilterExpression").ToNonNullString(),
};
return(measurementGroup);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
public IHttpActionResult GetConfigFilesLastWrites(int meterID, string sort, int ascending)
{
if (GetRoles == string.Empty || User.IsInRole(GetRoles))
{
string orderByExpression = "ConfigFileChanges.LastWriteTime DESC";
if (sort != null && sort != string.Empty)
{
orderByExpression = $"ConfigFileChanges.{sort} {(ascending == 1 ? "ASC" : "DESC")}";
}
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
string sql = $@"
SELECT
ConfigFileChanges.*
FROM
ConfigFileChanges CROSS APPLY
(SELECT FileName, MAX(LastWriteTime) LastWriteTime FROM ConfigFileChanges cfc WHERE cfc.FileName = ConfigFileChanges.FileName AND cfc.MeterID = ConfigFileChanges.MeterID GROUP BY FileName) as mlwt
WHERE
MeterID = {{0}} AND
ConfigFileChanges.LastWriteTime = mlwt.LastWriteTime
ORDER BY
{orderByExpression}
";
DataTable table = connection.RetrieveData(sql, meterID);
return(Ok(table));
}
}
else
{
return(Unauthorized());
}
}
public ContourInfo getLocationsTrendingData(ContourQuery contourQuery)
{
List<TrendingDataLocation> locations = new List<TrendingDataLocation>();
DataTable colorScale;
using (AdoDataConnection conn = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
using (IDbCommand cmd = conn.Connection.CreateCommand())
{
cmd.Parameters.Add(new SqlParameter("@EventDateFrom", contourQuery.GetStartDate()));
cmd.Parameters.Add(new SqlParameter("@EventDateTo", contourQuery.GetEndDate()));
cmd.Parameters.Add(new SqlParameter("@colorScaleName", contourQuery.ColorScaleName));
cmd.Parameters.Add(new SqlParameter("@username", contourQuery.UserName));
cmd.CommandText = "dbo.selectMeterLocationsTrendingData";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
TrendingDataLocation ourStatus = new TrendingDataLocation();
ourStatus.Latitude = (double)rdr["Latitude"];
ourStatus.Longitude = (double)rdr["Longitude"];
ourStatus.name = (string)rdr["Name"];
ourStatus.Average = (rdr.IsDBNull(rdr.GetOrdinal("Average")) ? (double?)null : (double)rdr["Average"]);
ourStatus.Maximum = (rdr.IsDBNull(rdr.GetOrdinal("Maximum")) ? (double?)null : (double)rdr["Maximum"]);
ourStatus.Minimum = (rdr.IsDBNull(rdr.GetOrdinal("Minimum")) ? (double?)null : (double)rdr["Minimum"]);
ourStatus.id = (int)rdr["id"];
ourStatus.data.Add(ourStatus.Average);
ourStatus.data.Add(ourStatus.Maximum);
ourStatus.data.Add(ourStatus.Minimum);
locations.Add(ourStatus);
}
}
string query =
"SELECT " +
" ContourColorScalePoint.Value, " +
" ContourColorScalePoint.Color " +
"FROM " +
" ContourColorScale JOIN " +
" ContourColorScalePoint ON ContourColorScalePoint.ContourColorScaleID = ContourColorScale.ID " +
"WHERE ContourColorScale.Name = {0} " +
"ORDER BY ContourColorScalePoint.OrderID";
colorScale = conn.RetrieveData(query, contourQuery.ColorScaleName);
}
double[] colorDomain = colorScale
.Select()
.Select(row => row.ConvertField<double>("Value"))
.ToArray();
double[] colorRange = colorScale
.Select()
.Select(row => (double)(uint)row.ConvertField<int>("Color"))
.ToArray();
return new ContourInfo()
{
Locations = locations,
ColorDomain = colorDomain,
ColorRange = colorRange,
};
}
private PiecewiseLinearFunction GetColorScale(ContourQuery contourQuery)
{
DataTable colorScale;
using (AdoDataConnection conn = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
string query =
"SELECT " +
" ContourColorScalePoint.Value, " +
" ContourColorScalePoint.Color " +
"FROM " +
" ContourColorScale JOIN " +
" ContourColorScalePoint ON ContourColorScalePoint.ContourColorScaleID = ContourColorScale.ID " +
"WHERE ContourColorScale.Name = {0} " +
"ORDER BY ContourColorScalePoint.OrderID";
colorScale = conn.RetrieveData(query, contourQuery.ColorScaleName);
}
double[] colorDomain = colorScale
.Select()
.Select(row => row.ConvertField<double>("Value"))
.ToArray();
double[] colorRange = colorScale
.Select()
.Select(row => (double)(uint)row.ConvertField<int>("Color"))
.ToArray();
return new PiecewiseLinearFunction()
.SetDomain(colorDomain)
.SetRange(colorRange);
}
private List<List<TrendingDataLocation>> GetFramesFromHistorian(ContourQuery contourQuery)
{
DataTable idTable;
string historianServer;
string historianInstance;
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
string query =
"SELECT " +
" Channel.ID AS ChannelID, " +
" Meter.ID AS MeterID, " +
" Meter.Name AS MeterName, " +
" MeterLocation.Latitude, " +
" MeterLocation.Longitude, " +
" Channel.PerUnitValue " +
"FROM " +
" Meter JOIN " +
" MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN " +
" Channel ON " +
" Channel.MeterID = Meter.ID AND " +
" Channel.ID IN (SELECT ChannelID FROM ContourChannel WHERE ContourColorScaleName = {1}) " +
"WHERE " +
" Meter.ID IN (SELECT * FROM authMeters({0}))";
idTable = connection.RetrieveData(query, contourQuery.UserName, contourQuery.ColorScaleName);
historianServer = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'Historian.Server'") ?? "127.0.0.1";
historianInstance = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'Historian.Instance'") ?? "XDA";
}
List<DataRow> meterRows = idTable
.Select()
.DistinctBy(row => row.ConvertField<int>("MeterID"))
.ToList();
DateTime startDate = contourQuery.GetStartDate();
DateTime endDate = contourQuery.GetEndDate();
int stepSize = contourQuery.StepSize;
// The frames to be included are those whose timestamps fall
// within the range which is specified by startDate and
// endDate. We start by aligning startDate and endDate with
// the nearest frame timestamps which fall within that range
int startTimeOffset = (int)Math.Ceiling((startDate - startDate.Date).TotalMinutes / stepSize);
startDate = startDate.Date.AddMinutes(startTimeOffset * stepSize);
int endTimeOffset = (int)Math.Floor((endDate - endDate.Date).TotalMinutes / stepSize);
endDate = endDate.Date.AddMinutes(endTimeOffset * stepSize);
// Since each frame includes data from all timestamps between
// the previous frame's timestamp and its own timestamp, we
// must include one additional frame of data before startDate
startDate = startDate.AddMinutes(-stepSize);
int frameCount = (int)((endDate - startDate).TotalMinutes / stepSize);
List<Dictionary<int, TrendingDataLocation>> frames = Enumerable.Repeat(meterRows, frameCount)
.Select(rows => rows.Select(row => new TrendingDataLocation()
{
id = row.ConvertField<int>("MeterID"),
name = row.ConvertField<string>("MeterName"),
Latitude = row.ConvertField<double>("Latitude"),
Longitude = row.ConvertField<double>("Longitude")
}))
.Select(locations => locations.ToDictionary(location => location.id))
.ToList();
Dictionary<int, double?> nominalLookup = idTable
.Select("ChannelID IS NOT NULL")
.ToDictionary(row => row.ConvertField<int>("ChannelID"), row => row.ConvertField<double?>("PerUnitValue"));
Dictionary<int, List<TrendingDataLocation>> lookup = idTable
.Select("ChannelID IS NOT NULL")
.Select(row =>
{
int meterID = row.ConvertField<int>("MeterID");
return new
{
ChannelID = row.ConvertField<int>("ChannelID"),
Frames = frames.Select(locationLookup => locationLookup[meterID]).ToList()
};
})
.ToDictionary(obj => obj.ChannelID, obj => obj.Frames);
using (Historian historian = new Historian(historianServer, historianInstance))
{
foreach (TrendingDataPoint point in historian.Read(lookup.Keys, startDate, endDate))
{
List<TrendingDataLocation> locations = lookup[point.ChannelID];
// Use ceiling to sort data into the next nearest frame.
// Subtract 1 because startDate was shifted to include one additional frame of data
int frameIndex = (int)Math.Ceiling((point.Timestamp - startDate).TotalMinutes / stepSize) - 1;
if (frameIndex < 0 || frameIndex >= locations.Count)
continue;
TrendingDataLocation frame = locations[frameIndex];
double nominal = nominalLookup[point.ChannelID] ?? 1.0D;
double value = point.Value / nominal;
switch (point.SeriesID)
{
case SeriesID.Minimum:
frame.Minimum = Math.Min(value, frame.Minimum ?? value);
break;
case SeriesID.Maximum:
frame.Maximum = Math.Max(value, frame.Maximum ?? value);
break;
case SeriesID.Average:
frame.Aggregate(value);
frame.Average = frame.GetAverage();
break;
}
}
}
return frames
.Select(frame => frame.Values.ToList())
.ToList();
}
/// <summary>
/// Retrieves a <see cref="Measurement"/> information from the database based on the signal ID of the measurement.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="signalID">Signal ID of the measurement.</param>
/// <returns><see cref="Measurement"/> information.</returns>
public static Measurement GetMeasurement(AdoDataConnection database, Guid signalID)
{
bool createdConnection = false;
DataTable measurementTable;
DataRow row;
try
{
createdConnection = CreateConnection(ref database);
measurementTable = database.RetrieveData(DefaultTimeout, "SELECT * FROM MeasurementDetail WHERE SignalID = {0}", signalID);
if (measurementTable.Rows.Count == 0)
return null;
row = measurementTable.Rows[0];
Measurement measurement = new Measurement()
{
SignalID = database.Guid(row, "SignalID"),
HistorianID = row.ConvertNullableField<int>("HistorianID"),
PointID = row.ConvertField<int>("PointID"),
DeviceID = row.ConvertNullableField<int>("DeviceID"),
PointTag = row.Field<string>("PointTag"),
AlternateTag = row.Field<string>("AlternateTag"),
SignalTypeID = row.ConvertField<int>("SignalTypeID"),
PhasorSourceIndex = row.ConvertNullableField<int>("PhasorSourceIndex"),
SignalReference = row.Field<string>("SignalReference"),
Adder = row.ConvertField<double>("Adder"),
Multiplier = row.ConvertField<double>("Multiplier"),
Description = row.Field<string>("Description"),
Enabled = Convert.ToBoolean(row.Field<object>("Enabled")),
m_historianAcronym = row.Field<string>("HistorianAcronym"),
m_deviceAcronym = row.Field<object>("DeviceAcronym") == null ? string.Empty : row.Field<string>("DeviceAcronym"),
m_signalName = row.Field<string>("SignalName"),
m_signalAcronym = row.Field<string>("SignalAcronym"),
m_signalSuffix = row.Field<string>("SignalTypeSuffix"),
m_phasorLabel = row.Field<string>("PhasorLabel"),
m_framesPerSecond = Convert.ToInt32(row.Field<object>("FramesPerSecond") ?? 30),
m_id = row.Field<string>("ID"),
m_companyAcronym = row.Field<object>("CompanyAcronym") == null ? string.Empty : row.Field<string>("CompanyAcronym"),
m_companyName = row.Field<object>("CompanyName") == null ? string.Empty : row.Field<string>("CompanyName"),
Selected = false
};
return measurement;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
// Static Methods
// ReSharper disable UnusedMember.Local
// ReSharper disable UnusedParameter.Local
private static void OptimizeLocalHistorianSettings(AdoDataConnection connection, string nodeIDQueryString, ulong trackingVersion, string arguments, Action<string> statusMessage, Action<Exception> processException)
{
// Make sure setting exists to allow user to by-pass local historian optimizations at startup
ConfigurationFile configFile = ConfigurationFile.Current;
CategorizedSettingsElementCollection settings = configFile.Settings["systemSettings"];
settings.Add("OptimizeLocalHistorianSettings", true, "Determines if the defined local historians will have their settings optimized at startup");
// See if this node should optimize local historian settings
if (settings["OptimizeLocalHistorianSettings"].ValueAsBoolean())
{
statusMessage("Optimizing settings for local historians...");
// Load the defined local system historians
IEnumerable<DataRow> historians = connection.RetrieveData($"SELECT AdapterName FROM RuntimeHistorian WHERE NodeID = {nodeIDQueryString} AND TypeName = 'openHistorian.Adapters.LocalOutputAdapter'").AsEnumerable();
List<string> validHistorians = new List<string>();
string name, acronym;
// Apply settings optimizations to local historians
foreach (DataRow row in historians)
{
acronym = row.Field<string>("AdapterName").ToLower();
validHistorians.Add(acronym);
}
// Local statics historian is valid regardless of historian type
if (!validHistorians.Contains("stat"))
validHistorians.Add("stat");
// Sort valid historians for binary search
validHistorians.Sort();
// Create a list to track categories to remove
HashSet<string> categoriesToRemove = new HashSet<string>();
// Search for unused settings categories
foreach (PropertyInformation info in configFile.Settings.ElementInformation.Properties)
{
name = info.Name;
if (name.EndsWith("AdoMetadataProvider") && validHistorians.BinarySearch(name.Substring(0, name.IndexOf("AdoMetadataProvider", StringComparison.Ordinal))) < 0)
categoriesToRemove.Add(name);
if (name.EndsWith("OleDbMetadataProvider") && validHistorians.BinarySearch(name.Substring(0, name.IndexOf("OleDbMetadataProvider", StringComparison.Ordinal))) < 0)
categoriesToRemove.Add(name);
if (name.EndsWith("RestWebServiceMetadataProvider") && validHistorians.BinarySearch(name.Substring(0, name.IndexOf("RestWebServiceMetadataProvider", StringComparison.Ordinal))) < 0)
categoriesToRemove.Add(name);
if (name.EndsWith("MetadataService") && validHistorians.BinarySearch(name.Substring(0, name.IndexOf("MetadataService", StringComparison.Ordinal))) < 0)
categoriesToRemove.Add(name);
if (name.EndsWith("TimeSeriesDataService") && validHistorians.BinarySearch(name.Substring(0, name.IndexOf("TimeSeriesDataService", StringComparison.Ordinal))) < 0)
categoriesToRemove.Add(name);
if (name.EndsWith("HadoopReplicationProvider") && validHistorians.BinarySearch(name.Substring(0, name.IndexOf("HadoopReplicationProvider", StringComparison.Ordinal))) < 0)
categoriesToRemove.Add(name);
}
if (categoriesToRemove.Count > 0)
{
statusMessage("Removing unused local historian configuration settings...");
// Remove any unused settings categories
foreach (string category in categoriesToRemove)
{
configFile.Settings.Remove(category);
}
}
// Save any applied changes
configFile.Save();
}
}
private static List<string> GetFaultCurveInfo(AdoDataConnection connection, int eventID)
{
const string query =
"SELECT Algorithm " +
"FROM FaultCurve LEFT OUTER JOIN FaultLocationAlgorithm ON Algorithm = MethodName " +
"WHERE EventID = {0} " +
"ORDER BY CASE WHEN ExecutionOrder IS NULL THEN 1 ELSE 0 END, ExecutionOrder";
DataTable table = connection.RetrieveData(query, eventID);
return table.Rows
.Cast<DataRow>()
.Select(row => row.Field<string>("Algorithm"))
.ToList();
}
private List<List<TrendingDataLocation>> GetFramesFromHistorian(ContourQuery contourQuery)
{
DataTable idTable;
string historianServer;
string historianInstance;
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
string query =
"SELECT " +
" Channel.ID AS ChannelID, " +
" Meter.ID AS MeterID, " +
" Meter.Name AS MeterName, " +
" MeterLocation.Latitude, " +
" MeterLocation.Longitude, " +
" Channel.PerUnitValue " +
"FROM " +
" Meter JOIN " +
" MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN " +
" Channel ON " +
" Channel.MeterID = Meter.ID AND " +
" Channel.ID IN (SELECT ChannelID FROM ContourChannel WHERE ContourColorScaleName = {1}) " +
"WHERE Meter.ID IN (SELECT * FROM authMeters({0}))";
idTable = connection.RetrieveData(query, contourQuery.UserName, contourQuery.ColorScaleName);
historianServer = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'Historian.Server'") ?? "127.0.0.1";
historianInstance = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'Historian.Instance'") ?? "XDA";
}
if (!string.IsNullOrEmpty(contourQuery.Meters))
{
const int byteSize = 8;
// Meter selections are stored as a base-64 string without padding, using '-' instead of '+' and '_' instead of '/'
string padding = "A==".Remove(3 - (contourQuery.Meters.Length + 3) % 4);
string base64 = contourQuery.Meters.Replace('-', '+').Replace('_', '/') + padding;
byte[] meterSelections = Convert.FromBase64String(base64);
// The resulting byte array is a simple set of bitflags ordered by meter ID and packed into the most significant bits.
// In order to properly interpret the bytes, we must first group and order the data by meter ID to determine the location
// of each meter's bitflag. Then we can filter out the unwanted data from the original table of IDs
idTable.Select()
.Select((Row, Index) => new { Row, Index })
.GroupBy(obj => obj.Row.ConvertField<int>("MeterID"))
.OrderBy(grouping => grouping.Key)
.Where((grouping, index) => (meterSelections[index / byteSize] & (0x80 >> (index % byteSize))) == 0)
.SelectMany(grouping => grouping)
.OrderByDescending(obj => obj.Index)
.ToList()
.ForEach(obj => idTable.Rows.RemoveAt(obj.Index));
}
List<DataRow> meterRows = idTable
.Select()
.DistinctBy(row => row.ConvertField<int>("MeterID"))
.ToList();
DateTime startDate = contourQuery.GetStartDate();
DateTime endDate = contourQuery.GetEndDate();
int stepSize = contourQuery.StepSize;
// The frames to be included are those whose timestamps fall
// within the range which is specified by startDate and
// endDate. We start by aligning startDate and endDate with
// the nearest frame timestamps which fall within that range
int startTimeOffset = (int)Math.Ceiling((startDate - startDate.Date).TotalMinutes / stepSize);
startDate = startDate.Date.AddMinutes(startTimeOffset * stepSize);
int endTimeOffset = (int)Math.Floor((endDate - endDate.Date).TotalMinutes / stepSize);
endDate = endDate.Date.AddMinutes(endTimeOffset * stepSize);
// Since each frame includes data from all timestamps between
// the previous frame's timestamp and its own timestamp, we
// must include one additional frame of data before startDate
startDate = startDate.AddMinutes(-stepSize);
int frameCount = (int)((endDate - startDate).TotalMinutes / stepSize);
List<Dictionary<int, TrendingDataLocation>> frames = Enumerable.Repeat(meterRows, frameCount)
.Select(rows => rows.Select(row => new TrendingDataLocation()
{
id = row.ConvertField<int>("MeterID"),
name = row.ConvertField<string>("MeterName"),
Latitude = row.ConvertField<double>("Latitude"),
Longitude = row.ConvertField<double>("Longitude")
}))
.Select(locations => locations.ToDictionary(location => location.id))
.ToList();
Dictionary<int, double?> nominalLookup = idTable
.Select("ChannelID IS NOT NULL")
.ToDictionary(row => row.ConvertField<int>("ChannelID"), row => row.ConvertField<double?>("PerUnitValue"));
Dictionary<int, List<TrendingDataLocation>> lookup = idTable
.Select("ChannelID IS NOT NULL")
.Select(row =>
{
int meterID = row.ConvertField<int>("MeterID");
return new
{
ChannelID = row.ConvertField<int>("ChannelID"),
Frames = frames.Select(locationLookup => locationLookup[meterID]).ToList()
};
})
.ToDictionary(obj => obj.ChannelID, obj => obj.Frames);
using (Historian historian = new Historian(historianServer, historianInstance))
{
foreach (TrendingDataPoint point in historian.Read(lookup.Keys, startDate, endDate))
{
List<TrendingDataLocation> locations = lookup[point.ChannelID];
// Use ceiling to sort data into the next nearest frame.
// Subtract 1 because startDate was shifted to include one additional frame of data
int frameIndex = (int)Math.Ceiling((point.Timestamp - startDate).TotalMinutes / stepSize) - 1;
if (frameIndex < 0 || frameIndex >= locations.Count)
continue;
TrendingDataLocation frame = locations[frameIndex];
double nominal = nominalLookup[point.ChannelID] ?? 1.0D;
double value = point.Value / nominal;
switch (point.SeriesID)
{
case SeriesID.Minimum:
frame.Minimum = Math.Min(value, frame.Minimum ?? value);
break;
case SeriesID.Maximum:
frame.Maximum = Math.Max(value, frame.Maximum ?? value);
break;
case SeriesID.Average:
frame.Aggregate(value);
frame.Average = frame.GetAverage();
break;
}
}
}
return frames
.Select(frame => frame.Values.ToList())
.ToList();
}
private static void ValidateAlarmStatistics(AdoDataConnection connection, Guid nodeID, string source)
{
const string MissingStatisticsFormat = "SELECT DISTINCT Severity FROM Alarm WHERE Severity <> 0 AND Severity NOT IN (SELECT Arguments FROM Statistic WHERE Source = {0} AND MethodName = {1})";
const string MaxSignalIndexFormat = "SELECT COALESCE(MAX(SignalIndex), 0) FROM Statistic WHERE Source = {0}";
const string InsertAlarmStatisticFormat = "INSERT INTO Statistic(Source, SignalIndex, Name, Description, AssemblyName, TypeName, MethodName, Arguments, Enabled, DataType, DisplayFormat, IsConnectedState, LoadOrder) VALUES({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12})";
string methodName;
DataTable missingStatistics;
int signalIndex;
int severity;
string name;
string description;
// Add statistics for the alarms defined in the Alarm table.
methodName = string.Format("Get{0}Statistic_MeasurementCountForSeverity", source);
missingStatistics = connection.RetrieveData(MissingStatisticsFormat, source, methodName);
if (missingStatistics.Rows.Count > 0)
{
signalIndex = connection.ExecuteScalar<int>(MaxSignalIndexFormat, source);
foreach (DataRow missingStatistic in missingStatistics.Rows)
{
signalIndex++;
severity = missingStatistic.ConvertField<int>("Severity");
name = string.Format("Alarm Severity {0}", severity);
description = string.Format("Number of measurements received while alarm with severity {0} was raised during the last reporting interval.", severity);
connection.ExecuteNonQuery(InsertAlarmStatisticFormat, source, signalIndex, name, description, "DataQualityMonitoring.dll", "DataQualityMonitoring.AlarmStatistics", methodName, severity, 1, "System.Int32", "{0:N0}", 0, 1001 - severity);
}
}
}
/// <summary>
/// LoadKeys <see cref="PowerCalculation"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns>Collection of <see cref="int"/>.</returns>
public static IList<int> LoadKeys(AdoDataConnection database)
{
var createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
var calculationList = new List<int>();
var queryFormat = "SELECT ID FROM PowerCalculation WHERE NodeID = {0}";
var calculationTable = database.RetrieveData(queryFormat, database.CurrentNodeID());
foreach (DataRow row in calculationTable.Rows)
{
calculationList.Add(row.ConvertField<int>("ID"));
}
return calculationList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves <see cref="MeasurementGroup"/> information for the group with the given ID.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="groupID">The ID of the measurement group to be retrieved.</param>
/// <returns>Measurement group with the given ID.</returns>
public static MeasurementGroup GetMeasurementGroup(AdoDataConnection database, int groupID)
{
DataTable measurementGroupTable;
bool createdConnection = false;
DataRow row;
try
{
createdConnection = CreateConnection(ref database);
measurementGroupTable = database.RetrieveData(DefaultTimeout, "SELECT * FROM MeasurementGroup WHERE ID = {0}", groupID);
if (measurementGroupTable.Rows.Count == 0)
return null;
row = measurementGroupTable.Rows[0];
MeasurementGroup measurementGroup = new MeasurementGroup()
{
NodeID = database.Guid(row, "NodeID"),
ID = row.ConvertField<int>("ID"),
Name = row.Field<string>("Name"),
Description = row.Field<object>("Description").ToNonNullString(),
FilterExpression = row.Field<object>("FilterExpression").ToNonNullString(),
};
return measurementGroup;
}
finally
{
if (createdConnection && database != null)
database.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());
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}.");
}
private static void MeasurementDeviceAssociation(AdoDataConnection connection, string nodeIDQueryString, ulong trackingVersion, string arguments, Action<string> statusMessage, Action<Exception> processException)
{
if (string.IsNullOrEmpty(arguments))
{
statusMessage("WARNING: No arguments supplied to MeasurementDeviceAssociation data operation - no action will be performed. Expecting \"deviceAcronym\" and \"lookupExpression\" settings at a minimum.");
return;
}
Dictionary<string, string> args = arguments.ParseKeyValuePairs();
string deviceAcronym;
if (!args.TryGetValue("DeviceAcronym", out deviceAcronym))
{
statusMessage("WARNING: No \"deviceAcronyym\" argument supplied to MeasurementDeviceAssociation data operation - no action will be performed. Expecting \"deviceAcronym\" and \"lookupExpression\" settings at a minimum.");
return;
}
string lookupExpression;
if (!args.TryGetValue("LookupExpression", out lookupExpression))
{
statusMessage("WARNING: No \"lookupExpression\" argument supplied to MeasurementDeviceAssociation data operation - no action will be performed. Expecting \"deviceAcronym\" and \"lookupExpression\" settings at a minimum.");
return;
}
// Make sure device acronym exists
if (connection.ExecuteScalar<int>($"SELECT COUNT(*) FROM Device WHERE NodeID={nodeIDQueryString} AND Acronym={{0}}", deviceAcronym) == 0)
{
// Lookup virtual device protocol
if (connection.ExecuteScalar<int>("SELECT COUNT(*) FROM Protocol WHERE Acronym='VirtualInput'") == 0)
{
statusMessage("WARNING: No VirutalInput device protocol was found in source database configuration for MeasurementDeviceAssociation data operation - no action will be performed.");
return;
}
statusMessage($"Creating new \"{deviceAcronym}\" virtual device...");
int virtualProtocolID = connection.ExecuteScalar<int>("SELECT ID FROM Protocol WHERE Acronym='VirtualInput'");
// Create new virtual device record
connection.ExecuteNonQuery($"INSERT INTO Device(NodeID, Acronym, Name, ProtocolID, Enabled) VALUES({nodeIDQueryString}, {{0}}, {{1}}, {{2}}, 1)", deviceAcronym, deviceAcronym, virtualProtocolID);
}
statusMessage($"Validating \"{deviceAcronym}\" virtual device measurement associations...");
// Get device ID
int deviceID = connection.ExecuteScalar<int>($"SELECT ID FROM Device WHERE NodeID={nodeIDQueryString} AND Acronym={{0}}", deviceAcronym);
// Get measurements that should be associated with device ID but are not currently
IEnumerable<DataRow> measurements = connection.RetrieveData($"SELECT PointID FROM Measurement WHERE ({lookupExpression}) AND (DeviceID IS NULL OR DeviceID <> {{0}})", deviceID).AsEnumerable();
int associatedMeasurements = 0;
foreach (DataRow row in measurements)
{
connection.ExecuteNonQuery("UPDATE Measurement SET DeviceID={0} WHERE PointID={1}", deviceID, row.Field<int>("PointID"));
associatedMeasurements++;
}
if (associatedMeasurements > 0)
statusMessage($"Associated \"{associatedMeasurements}\" measurements to \"{deviceAcronym}\" virtual device...");
}
private static void LoadEventSentEmail(EventRow eventRow, EventDataTable systemEvent, int sentEmailID)
{
BulkLoader bulkLoader;
DataTable eventSentEmailTable;
using (AdoDataConnection connection = new AdoDataConnection(s_dbAdapterContainer.Connection, typeof(SqlDataAdapter), false))
{
// Query an empty table with matching schema --
// union table to itself to eliminate unique key constraints
eventSentEmailTable = connection.RetrieveData("SELECT * FROM EventSentEmail WHERE 1 IS NULL UNION ALL SELECT * FROM EventSentEmail WHERE 1 IS NULL");
eventSentEmailTable.TableName = "EventSentEmail";
}
foreach (MeterData.EventRow evt in systemEvent)
{
if (eventRow.LineID == evt.LineID)
eventSentEmailTable.Rows.Add(0, evt.ID, sentEmailID);
}
bulkLoader = new BulkLoader();
bulkLoader.Connection = s_dbAdapterContainer.Connection;
bulkLoader.CommandTimeout = s_dbAdapterContainer.CommandTimeout;
bulkLoader.Load(eventSentEmailTable);
}
public TrendingDataSet getTrendsforChannelIDDate(string ChannelID, string targetDate)
{
//DateTime epoch = new DateTime(1970, 1, 1);
//string theSproc = "dbo.selectTrendingDataByChannelIDDate2";
//DataSet dataSet = new DataSet();
//TrendingDataSet trendingDataSet = new TrendingDataSet();
//using (SqlConnection connection = new SqlConnection(connectionstring))
//using (SqlCommand command = connection.CreateCommand())
//using (SqlDataAdapter adapter = new SqlDataAdapter(command))
//{
// command.CommandText = theSproc;
// command.CommandType = CommandType.StoredProcedure;
// command.Parameters.AddWithValue("@EventDate", targetDate);
// command.Parameters.AddWithValue("@ChannelID", ChannelID);
// command.CommandTimeout = 300;
// connection.Open();
// adapter.Fill(dataSet);
// trendingDataSet.ChannelData = dataSet.Tables[0].Rows
// .Cast<DataRow>()
// .Select(row => new TrendingDataPoint()
// {
// Time = row.Field<DateTime>("thedate").Subtract(epoch).TotalMilliseconds,
// Maximum = row.Field<double>("themaximum"),
// Minimum = row.Field<double>("theminimum"),
// Average = row.Field<double>("theaverage")
// })
// .ToArray();
// trendingDataSet.AlarmLimits = dataSet.Tables[1].Rows
// .Cast<DataRow>()
// .Select(row => new TrendingAlarmLimit()
// {
// TimeStart = row.Field<DateTime>("thedatefrom").Subtract(epoch).TotalMilliseconds,
// TimeEnd = row.Field<DateTime>("thedateto").Subtract(epoch).TotalMilliseconds,
// High = row.Field<double?>("alarmlimithigh"),
// Low = row.Field<double?>("alarmlimitlow")
// })
// .ToArray();
// trendingDataSet.OffNormalLimits = dataSet.Tables[2].Rows
// .Cast<DataRow>()
// .Select(row => new TrendingAlarmLimit()
// {
// TimeStart = row.Field<DateTime>("thedatefrom").Subtract(epoch).TotalMilliseconds,
// TimeEnd = row.Field<DateTime>("thedateto").Subtract(epoch).TotalMilliseconds,
// High = row.Field<double?>("offlimithigh"),
// Low = row.Field<double?>("offlimitlow")
// })
// .ToArray();
//}
string historianServer;
string historianInstance;
IEnumerable<int> channelIDs = new List<int>() { Convert.ToInt32(ChannelID) };
DateTime startDate = Convert.ToDateTime(targetDate);
DateTime endDate = startDate.AddDays(1);
TrendingDataSet trendingDataSet = new TrendingDataSet();
DateTime epoch = new DateTime(1970, 1, 1);
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
historianServer = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'Historian.Server'") ?? "127.0.0.1";
historianInstance = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'Historian.Instance'") ?? "XDA";
using (Historian historian = new Historian(historianServer, historianInstance))
{
foreach (openHistorian.XDALink.TrendingDataPoint point in historian.Read(channelIDs, startDate, endDate))
{
if (!trendingDataSet.ChannelData.Exists(x => x.Time == point.Timestamp.Subtract(epoch).TotalMilliseconds))
{
trendingDataSet.ChannelData.Add(new TrendingDataDatum());
trendingDataSet.ChannelData[trendingDataSet.ChannelData.Count - 1].Time = point.Timestamp.Subtract(epoch).TotalMilliseconds;
}
if (point.SeriesID.ToString() == "Average")
trendingDataSet.ChannelData[trendingDataSet.ChannelData.IndexOf(x => x.Time == point.Timestamp.Subtract(epoch).TotalMilliseconds)].Average = point.Value;
else if (point.SeriesID.ToString() == "Minimum")
trendingDataSet.ChannelData[trendingDataSet.ChannelData.IndexOf(x => x.Time == point.Timestamp.Subtract(epoch).TotalMilliseconds)].Minimum = point.Value;
else if (point.SeriesID.ToString() == "Maximum")
trendingDataSet.ChannelData[trendingDataSet.ChannelData.IndexOf(x => x.Time == point.Timestamp.Subtract(epoch).TotalMilliseconds)].Maximum = point.Value;
}
}
IEnumerable<DataRow> table = Enumerable.Empty<DataRow>();
table = connection.RetrieveData(" Select {0} AS thedatefrom, " +
" DATEADD(DAY, 1, {0}) AS thedateto, " +
" CASE WHEN AlarmRangeLimit.PerUnit <> 0 AND Channel.PerUnitValue IS NOT NULL THEN AlarmRangeLimit.High * PerUnitValue ELSE AlarmRangeLimit.High END AS alarmlimithigh," +
" CASE WHEN AlarmRangeLimit.PerUnit <> 0 AND Channel.PerUnitValue IS NOT NULL THEN AlarmRangeLimit.Low * PerUnitValue ELSE AlarmRangeLimit.Low END AS alarmlimitlow " +
" FROM AlarmRangeLimit JOIN " +
" Channel ON AlarmRangeLimit.ChannelID = Channel.ID " +
"WHERE AlarmRangeLimit.AlarmTypeID = (SELECT ID FROM AlarmType where Name = 'Alarm') AND " +
" AlarmRangeLimit.ChannelID = {1}", startDate, Convert.ToInt32(ChannelID)).Select();
foreach (DataRow row in table)
{
trendingDataSet.AlarmLimits.Add(new TrendingAlarmLimit() { High = row.Field<double?>("alarmlimithigh"), Low = row.Field<double?>("alarmlimitlow"), TimeEnd = row.Field<DateTime>("thedateto").Subtract(epoch).TotalMilliseconds, TimeStart = row.Field<DateTime>("thedatefrom").Subtract(epoch).TotalMilliseconds });
}
table = Enumerable.Empty<DataRow>();
table = connection.RetrieveData(" DECLARE @dayOfWeek INT = DATEPART(DW, {0}) - 1 " +
" DECLARE @hourOfWeek INT = @dayOfWeek * 24 " +
" ; WITH HourlyIndex AS" +
" ( " +
" SELECT @hourOfWeek AS HourOfWeek " +
" UNION ALL " +
" SELECT HourOfWeek + 1 " +
" FROM HourlyIndex" +
" WHERE (HourOfWeek + 1) < @hourOfWeek + 24" +
" ) " +
" SELECT " +
" DATEADD(HOUR, HourlyIndex.HourOfWeek - @hourOfWeek, {0}) AS thedatefrom, " +
" DATEADD(HOUR, HourlyIndex.HourOfWeek - @hourOfWeek + 1, {0}) AS thedateto, " +
" HourOfWeekLimit.High AS offlimithigh, " +
" HourOfWeekLimit.Low AS offlimitlow " +
" FROM " +
" HourlyIndex LEFT OUTER JOIN " +
" HourOfWeekLimit ON HourOfWeekLimit.HourOfWeek = HourlyIndex.HourOfWeek " +
" WHERE " +
" HourOfWeekLimit.ChannelID IS NULL OR " +
" HourOfWeekLimit.ChannelID = {1} ", startDate, Convert.ToInt32(ChannelID)).Select();
foreach (DataRow row in table)
{
trendingDataSet.OffNormalLimits.Add(new TrendingAlarmLimit() { High = row.Field<double?>("offlimithigh"), Low = row.Field<double?>("offlimitlow"), TimeEnd = row.Field<DateTime>("thedateto").Subtract(epoch).TotalMilliseconds, TimeStart = row.Field<DateTime>("thedatefrom").Subtract(epoch).TotalMilliseconds });
}
}
return trendingDataSet;
}
// 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);
}
}
}
