private HashSet <Tuple <int, SeriesID, DateTime> > FindExistingTrendingPoints(MeterDataSet meterDataSet, Historian historian)
{
HashSet <Tuple <int, SeriesID, DateTime> > existingTrendingPoints = new HashSet <Tuple <int, SeriesID, DateTime> >();
Dictionary <Channel, List <DataGroup> > trendingDataGroups = meterDataSet.GetResource <TrendingGroupsResource>().TrendingGroups;
IEnumerable <IGrouping <Tuple <DateTime, DateTime>, TrendingRange> > channelGroups = trendingDataGroups
.SelectMany(kvp => kvp.Value.Select(dataGroup => new TrendingRange(kvp.Key, dataGroup)))
.GroupBy(trendingRange => Tuple.Create(trendingRange.StartTime, trendingRange.EndTime));
foreach (IGrouping <Tuple <DateTime, DateTime>, TrendingRange> channelGroup in channelGroups)
{
DateTime startTime = channelGroup.Key.Item1;
DateTime stopTime = channelGroup.Key.Item2;
IEnumerable <int> channels = channelGroup.Select(trendingRange => trendingRange.Channel.ID);
IEnumerable <TrendingDataPoint> trendingPoints = null;
try
{
trendingPoints = historian.Read(channels, startTime, stopTime);
foreach (TrendingDataPoint trendingPoint in trendingPoints)
{
existingTrendingPoints.Add(GetKey(trendingPoint));
}
}
finally
{
(trendingPoints as IDisposable)?.Dispose();
}
}
return(existingTrendingPoints);
}
private string GetTHDJsonString(IGrouping <int, DisturbanceData> assetGroup, DateTime startDate, DateTime endDate)
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
List <int> channelIds = new TableOperations <Channel>(connection).QueryRecordsWhere("MeterID = {0} AND MeasurementCharacteristicID = (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD')", assetGroup.Key).Select(x => x.ID).ToList();
List <openHistorian.XDALink.TrendingDataPoint> historianPoints = new List <openHistorian.XDALink.TrendingDataPoint>();
try
{
using (Historian historian = new Historian(HistorianServer, HistorianInstance))
{
foreach (openHistorian.XDALink.TrendingDataPoint point in historian.Read(channelIds, startDate, endDate))
{
if (point.Value < 0.1)
{
point.Value *= 100;
}
if (point.SeriesID.ToString() == "Average" && point.Value >= 0 && point.Value <= 10)
{
historianPoints.Add(point);
}
}
return("{" + string.Join(",", historianPoints.GroupBy(x => (int)(x.Value / 0.1)).Where(x => x.Key > 0).OrderBy(x => x.Key).Select(x => $"\"{x.Key}\":\"{x.Count()}\"")) + "}");
}
}
catch (KeyNotFoundException ex)
{
return("");
}
catch (SocketException ex)
{
return("");
}
catch (Exception ex)
{
throw ex;
}
}
}
private void LoadRangeLimits(MeterDataSet meterDataSet)
{
Dictionary <Channel, List <DataGroup> > trendingGroups = meterDataSet.GetResource <TrendingGroupsResource>().TrendingGroups;
DateTime startTime = trendingGroups
.SelectMany(kvp => kvp.Value)
.Select(dataGroup => dataGroup.StartTime)
.DefaultIfEmpty(DateTime.MaxValue)
.Min().Date;
DateTime endTime = trendingGroups
.SelectMany(kvp => kvp.Value)
.Select(dataGroup => dataGroup.EndTime)
.DefaultIfEmpty(DateTime.MinValue)
.Max();
if (endTime != endTime.Date)
{
endTime = endTime.Date.AddDays(1.0D);
}
int days = (int)Math.Ceiling((endTime - startTime).TotalDays);
if (days < 0)
{
return;
}
using (AdoDataConnection connection = meterDataSet.CreateDbConnection())
using (Historian historian = new Historian(m_historianSettings.Server, m_historianSettings.InstanceName))
{
TableOperations <ChannelAlarmSummary> channelAlarmSummaryTable = new TableOperations <ChannelAlarmSummary>(connection);
TableOperations <MeterAlarmSummary> meterAlarmSummaryTable = new TableOperations <MeterAlarmSummary>(connection);
Dictionary <int, Channel> channelLookup = meterDataSet.Meter.Channels
.Where(channel => channel.ID > 0)
.ToDictionary(channel => channel.ID);
for (int i = 0; i < days; i++)
{
DateTime queryStart = startTime.AddDays(i);
DateTime queryEnd = queryStart.AddDays(1.0D).AddTicks(-1L);
List <ChannelAlarmSummary> channelAlarmSummaries = historian.Read(meterDataSet.Meter.Channels.Select(channel => channel.ID), queryStart, queryEnd)
.Where(trendingPoint => trendingPoint.SeriesID == SeriesID.Average)
.GroupBy(trendingPoint => trendingPoint.ChannelID)
.SelectMany(channelGroup =>
{
Channel channel = channelLookup[channelGroup.Key];
List <AlarmRangeLimit> alarmRangeLimits = InitializeRangeLimitTable(connection, channel);
return(channelGroup
.SelectMany(TrendingPoint => alarmRangeLimits.Where(rangeLimit => rangeLimit.Enabled).Select(RangeLimit => new { TrendingPoint, RangeLimit }))
.Where(obj => CheckAlarm(channel, obj.TrendingPoint, obj.RangeLimit))
.GroupBy(obj => obj.RangeLimit.AlarmTypeID)
.Select(alarmTypeGroup => CreateChannelAlarmSummary(channelGroup.Key, alarmTypeGroup.Key, queryStart, alarmTypeGroup.Count())));
})
.ToList();
List <MeterAlarmSummary> meterAlarmSummaries = channelAlarmSummaries
.GroupBy(channelAlarmSummary => channelAlarmSummary.AlarmTypeID)
.Select(channelSummaryGroup => CreateMeterAlarmSummary(meterDataSet.Meter.ID, channelSummaryGroup.Key, queryStart, channelSummaryGroup.Sum(channelAlarmSummary => channelAlarmSummary.AlarmPoints)))
.ToList();
foreach (MeterAlarmSummary meterAlarmSummary in meterAlarmSummaries)
{
meterAlarmSummaryTable.Upsert(meterAlarmSummary);
}
foreach (ChannelAlarmSummary channelAlarmSummary in channelAlarmSummaries)
{
channelAlarmSummaryTable.Upsert(channelAlarmSummary);
}
}
}
}
private void LoadRangeLimits(MeterDataSet meterDataSet)
{
MeterAlarmSummaryTableAdapter meterAlarmSummaryAdapter;
ChannelAlarmSummaryTableAdapter channelAlarmSummaryAdapter;
HourOfWeekLimitTableAdapter hourlyLimitAdapter;
MeterAlarmSummaryDataTable meterAlarmSummaryTable;
ChannelAlarmSummaryDataTable channelAlarmSummaryTable;
Dictionary <Channel, List <DataGroup> > trendingGroups;
DateTime startTime;
DateTime endTime;
int days;
meterAlarmSummaryAdapter = m_dbAdapterContainer.GetAdapter <MeterAlarmSummaryTableAdapter>();
channelAlarmSummaryAdapter = m_dbAdapterContainer.GetAdapter <ChannelAlarmSummaryTableAdapter>();
hourlyLimitAdapter = m_dbAdapterContainer.GetAdapter <HourOfWeekLimitTableAdapter>();
meterAlarmSummaryTable = new MeterAlarmSummaryDataTable();
channelAlarmSummaryTable = new ChannelAlarmSummaryDataTable();
trendingGroups = meterDataSet.GetResource <TrendingGroupsResource>().TrendingGroups;
startTime = trendingGroups
.SelectMany(kvp => kvp.Value)
.Select(dataGroup => dataGroup.StartTime)
.DefaultIfEmpty(DateTime.MaxValue)
.Min().Date;
endTime = trendingGroups
.SelectMany(kvp => kvp.Value)
.Select(dataGroup => dataGroup.EndTime)
.DefaultIfEmpty(DateTime.MinValue)
.Max();
if (endTime != endTime.Date)
{
endTime = endTime.Date.AddDays(1.0D);
}
days = (int)Math.Ceiling((endTime - startTime).TotalDays);
if (days < 0)
{
return;
}
using (Historian historian = new Historian(m_historianSettings.Server, m_historianSettings.InstanceName))
{
for (int i = 0; i < days; i++)
{
DateTime queryStart = startTime.AddDays(i);
DateTime queryEnd = queryStart.AddDays(1.0D).AddTicks(-1L);
List <ChannelAlarmSummaryRow> channelAlarmSummaries = historian.Read(meterDataSet.Meter.Channels.Select(channel => channel.ID), queryStart, queryEnd)
.Where(trendingPoint => trendingPoint.SeriesID == SeriesID.Average)
.GroupBy(trendingPoint => trendingPoint.ChannelID)
.SelectMany(channelGroup =>
{
Channel channel = m_dbAdapterContainer.GetAdapter <MeterInfoDataContext>().Channels.Single(ch => ch.ID == channelGroup.Key);
AlarmRangeLimitDataTable rangeLimitTable = InitializeRangeLimitTable(channel);
return(channelGroup
.SelectMany(TrendingPoint => rangeLimitTable.Where(rangeLimit => rangeLimit.Enabled != 0).Select(RangeLimit => new { TrendingPoint, RangeLimit }))
.Where(obj => CheckAlarm(channel, obj.TrendingPoint, obj.RangeLimit))
.GroupBy(obj => obj.RangeLimit.AlarmTypeID)
.Select(alarmTypeGroup => CreateSummaryRow(channelAlarmSummaryTable, channelGroup.Key, alarmTypeGroup.Key, queryStart, alarmTypeGroup.Count())));
})
.ToList();
IEnumerable <MeterAlarmSummaryRow> meterAlarmSummaries = channelAlarmSummaries
.GroupBy(channelAlarmSummary => channelAlarmSummary.AlarmTypeID)
.Select(channelSummaryGroup => CreateSummaryRow(meterAlarmSummaryTable, meterDataSet.Meter.ID, channelSummaryGroup.Key, queryStart, channelSummaryGroup.Sum(channelAlarmSummary => channelAlarmSummary.AlarmPoints)));
foreach (MeterAlarmSummaryRow meterAlarmSummary in meterAlarmSummaries)
{
meterAlarmSummaryAdapter.Upsert(meterAlarmSummary);
}
foreach (ChannelAlarmSummaryRow channelAlarmSummary in channelAlarmSummaries)
{
channelAlarmSummaryAdapter.Upsert(channelAlarmSummary);
}
}
}
}
private void LoadHourOfWeekLimits(MeterDataSet meterDataSet)
{
TrendingGroupsResource trendingGroupsResource = meterDataSet.GetResource <TrendingGroupsResource>();
Dictionary <Channel, List <DataGroup> > trendingGroups = trendingGroupsResource.TrendingGroups;
DateTime startTime = trendingGroups
.SelectMany(kvp => kvp.Value)
.Select(dataGroup => dataGroup.StartTime)
.DefaultIfEmpty(DateTime.MaxValue)
.Min();
DateTime endTime = trendingGroups
.SelectMany(kvp => kvp.Value)
.Select(dataGroup => dataGroup.EndTime)
.DefaultIfEmpty(DateTime.MinValue)
.Max();
int days = (int)Math.Ceiling((endTime - startTime).TotalDays);
if (days < 0)
{
return;
}
using (Historian historian = new Historian(m_historianSettings.Server, m_historianSettings.InstanceName))
using (AdoDataConnection connection = meterDataSet.CreateDbConnection())
{
TableOperations <HourOfWeekLimit> hourOfWeekLimitTable = new TableOperations <HourOfWeekLimit>(connection);
TableOperations <MeterAlarmSummary> meterAlarmSummaryTable = new TableOperations <MeterAlarmSummary>(connection);
TableOperations <ChannelAlarmSummary> channelAlarmSummaryTable = new TableOperations <ChannelAlarmSummary>(connection);
for (int i = 0; i < days; i++)
{
DateTime queryStart = startTime.AddDays(days);
DateTime queryEnd = queryStart.AddDays(1.0D).AddTicks(-1L);
List <ChannelAlarmSummary> channelAlarmSummaries = historian.Read(meterDataSet.Meter.Channels.Select(channel => channel.ID), queryStart, queryEnd)
.Where(trendingPoint => trendingPoint.SeriesID == SeriesID.Average)
.GroupBy(trendingPoint => trendingPoint.ChannelID)
.SelectMany(channelGroup =>
{
List <HourOfWeekLimit> hourOfWeekLimits = hourOfWeekLimitTable
.QueryRecordsWhere("ChannelID = {0}", channelGroup.Key)
.ToList();
return(channelGroup
.Join(hourOfWeekLimits, trendingPoint => GetHourOfWeek(trendingPoint.Timestamp), hourlyLimit => hourlyLimit.HourOfWeek, (TrendingPoint, HourlyLimit) => new { TrendingPoint, HourlyLimit })
.Where(obj => obj.TrendingPoint.Value <obj.HourlyLimit.Low || obj.TrendingPoint.Value> obj.HourlyLimit.High)
.GroupBy(obj => obj.HourlyLimit.AlarmTypeID)
.Select(alarmTypeGroup => CreateChannelAlarmSummary(channelGroup.Key, alarmTypeGroup.Key, queryStart, alarmTypeGroup.Count())));
})
.ToList();
List <MeterAlarmSummary> meterAlarmSummaries = channelAlarmSummaries
.GroupBy(channelAlarmSummary => channelAlarmSummary.AlarmTypeID)
.Select(channelSummaryGroup => CreateMeterAlarmSummary(meterDataSet.Meter.ID, channelSummaryGroup.Key, queryStart, channelSummaryGroup.Sum(channelAlarmSummary => channelAlarmSummary.AlarmPoints)))
.ToList();
foreach (MeterAlarmSummary meterAlarmSummary in meterAlarmSummaries)
{
meterAlarmSummaryTable.Upsert(meterAlarmSummary);
}
foreach (ChannelAlarmSummary channelAlarmSummary in channelAlarmSummaries)
{
channelAlarmSummaryTable.Upsert(channelAlarmSummary);
}
}
}
}
public IHttpActionResult Get(int channelID, string date)
{
try
{
string historianServer;
string historianInstance;
IEnumerable <int> channelIDs = new List <int>()
{
channelID
};
DateTime startDate = Convert.ToDateTime(date);
DateTime endDate = startDate.AddDays(1);
TrendingDataSet trendingDataSet = new TrendingDataSet();
DateTime epoch = new DateTime(1970, 1, 1);
using (AdoDataConnection connection = new AdoDataConnection("dbOpenXDA"))
{
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.InstanceName'") ?? "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, 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, 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(Ok(trendingDataSet));
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
private List <List <TrendingDataLocation> > GetFramesFromHistorian(ContourQuery contourQuery)
{
DataTable idTable;
string historianServer;
string historianInstance;
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
var meters = (new TableOperations <Meter>(connection)).QueryRecordsWhere("ID IN (SELECT MeterID FROM MeterAssetGroup WHERE AssetGroupID IN (SELECT AssetGroupID FROM UserAccountAssetGroup WHERE UserAccountID = (SELECT ID FROM UserAccount WHERE Name = {0})))", contourQuery.UserName);
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);
var ids = string.Join("", meterSelections.Select(x => Convert.ToString(x, 2)));
// 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 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 list of meters
meters = meters
.OrderBy(meter => meter.ID)
.Where((meter, index) => (meterSelections[index / byteSize] & (0x80 >> (index % byteSize))) > 0)
.ToList();
}
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 (" + string.Join(",", meters.Select(x => x.ID)) + ")";
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.InstanceName'") ?? "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 (openHistorian.XDALink.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 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();
}
public TrendingDataSet GetData()
{
TrendingDataSet trendingDataSet = new TrendingDataSet();
Dictionary <string, string> query = Request.QueryParameters();
IEnumerable <int> channelIDs = new List <int>()
{
Convert.ToInt32(query["ChannelID"])
};
string channelID = query["ChannelID"];
DateTime startDate = Convert.ToDateTime(query["targetDate"]);
string target = query["ChannelID"] + query["targetDate"];
if (s_memoryCache.Contains(target))
{
return((TrendingDataSet)s_memoryCache.Get(target));
}
string historianServer = m_dataContext.Connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Historian.Server'") ?? "127.0.0.1";
string historianInstance = m_dataContext.Connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Historian.Instance'") ?? "XDA";
DateTime endDate = startDate.AddDays(1);
DateTime epoch = new DateTime(1970, 1, 1);
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 openXDA.Model.TrendingDataPoint());
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 = m_dataContext.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 = m_dataContext.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
});
}
s_memoryCache.Add(target, trendingDataSet, new CacheItemPolicy()
{
SlidingExpiration = TimeSpan.FromMinutes(10.0D)
});
return(trendingDataSet);
}
public Task <Dictionary <string, List <double[]> > > GetData(CancellationToken cancellationToken)
{
Dictionary <string, string> query = Request.QueryParameters();
int channelID = int.Parse(query["ChannelID"]);
int pixels = int.Parse(query["pixels"]);
DateTime startDate = DateTime.ParseExact(query["StartDate"], "yyyy-MM-ddTHH:mm", CultureInfo.CurrentCulture);
DateTime endDate = DateTime.ParseExact(query["EndDate"], "yyyy-MM-ddTHH:mm", CultureInfo.CurrentCulture);
DateTime epoch = new DateTime(1970, 1, 1);
return(Task.Factory.StartNew(() =>
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
string target = "TrendingDataDisplayGetData" + channelID.ToString() + startDate.ToString("yyyyMMddHHmm") + endDate.ToString("yyyyMMddHHmm");
Dictionary <string, List <double[]> > data = (Dictionary <string, List <double[]> >)s_memoryCache.Get(target);
if (data == null)
{
data = new Dictionary <string, List <double[]> >();
data.Add("Minimum", new List <double[]>());
data.Add("Maximum", new List <double[]>());
data.Add("Average", new List <double[]>());
string historianServer = connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Historian.Server'") ?? "127.0.0.1";
string 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(new[] { channelID }, startDate, endDate))
{
data[point.SeriesID.ToString()].Add(new[] { point.Timestamp.Subtract(epoch).TotalMilliseconds, point.Value });
}
}
s_memoryCache.Add(target, data, new CacheItemPolicy {
SlidingExpiration = TimeSpan.FromMinutes(0.5D)
});
}
return Downsample(data, pixels, new Range <DateTime>(startDate, endDate));
}
}, cancellationToken));
}
public void ProcessMonthToDateData()
{
DateTime endDate = DateTime.UtcNow;
DateTime startDate = new DateTime(endDate.Year, endDate.Month, 1);
string historianServer = DataContext.Connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Historian.Server'") ?? "127.0.0.1";
string historianInstance = DataContext.Connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Historian.Instance'") ?? "XDA";
IEnumerable <PQMarkCompanyMeter> meters = DataContext.Table <PQMarkCompanyMeter>().QueryRecordsWhere("Enabled = 1");
if (!meters.Any())
{
return;
}
DataTable table = DataContext.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 + @"'"
);
IEnumerable <DisturbanceData> dd = table.Select().Select(row => DataContext.Table <DisturbanceData>().LoadRecord(row));
var meterGroups = dd.GroupBy(x => x.MeterID);
foreach (var meterGroup in meterGroups)
{
OnLogStatusMessage(string.Format("PQMark aggregation: Processing {0}", meterGroup.Key));
// Get Easy Counts for SARFI 90, 70, 50, 10
int sarfi90 = meterGroup.Where(x => x.PerUnitMagnitude <= 0.9).Count();
int sarfi70 = meterGroup.Where(x => x.PerUnitMagnitude <= 0.7).Count();
int sarfi50 = meterGroup.Where(x => x.PerUnitMagnitude <= 0.5).Count();
int sarfi10 = meterGroup.Where(x => x.PerUnitMagnitude <= 0.1).Count();
// Get Counts for semi
int semi = meterGroup.Where(x => {
double vc = 0.0D;
int point1 = Curves.SemiCurve.TakeWhile(y => y.Item2 < x.DurationSeconds).Count() - 1;
if (point1 == -1)
{
return(false);
}
else if (point1 + 1 == Curves.SemiCurve.Count())
{
vc = Curves.SemiCurve[point1].Item1;
}
else if (Curves.SemiCurve[point1].Item2 == Curves.SemiCurve[point1 + 1].Item2)
{
vc = Curves.SemiCurve[point1 + 1].Item1;
}
else
{
double slope = (Curves.SemiCurve[point1 + 1].Item1 - Curves.SemiCurve[point1].Item1) / (Curves.SemiCurve[point1 + 1].Item2 - Curves.SemiCurve[point1].Item2);
vc = slope * (x.DurationSeconds - Curves.SemiCurve[point1].Item2) + Curves.SemiCurve[point1].Item1;
}
double value = (1.0D - x.PerUnitMagnitude) / (1.0D - vc);
return(value >= 1.0D);
}).Count();
// Get counts for ITIC
int iticLower = meterGroup.Where(x => {
double vc = 0.0D;
int point1 = Curves.IticLowerCurve.TakeWhile(y => y.Item2 < x.DurationSeconds).Count() - 1;
if (point1 == -1)
{
return(false);
}
else if (point1 + 1 == Curves.IticLowerCurve.Count())
{
vc = Curves.IticLowerCurve[point1].Item1;
}
else if (Curves.IticLowerCurve[point1].Item2 == Curves.IticLowerCurve[point1 + 1].Item2)
{
vc = Curves.IticLowerCurve[point1 + 1].Item1;
}
else
{
double slope = (Curves.IticLowerCurve[point1 + 1].Item1 - Curves.IticLowerCurve[point1].Item1) / (Curves.IticLowerCurve[point1 + 1].Item2 - Curves.IticLowerCurve[point1].Item2);
vc = slope * (x.DurationSeconds - Curves.IticLowerCurve[point1].Item2) + Curves.IticLowerCurve[point1].Item1;
}
double value = (1.0D - x.PerUnitMagnitude) / (1.0D - vc);
return(value >= 1.0D);
}).Count();
int iticUpper = meterGroup.Where(x => {
double vc = 0.0D;
int point1 = Curves.IticUpperCurve.TakeWhile(y => y.Item2 < x.DurationSeconds).Count() - 1;
if (point1 == -1)
{
return(false);
}
else if (point1 + 1 == Curves.IticUpperCurve.Count())
{
vc = Curves.IticUpperCurve[point1].Item1;
}
else if (Curves.IticUpperCurve[point1].Item2 == Curves.IticUpperCurve[point1 + 1].Item2)
{
vc = Curves.IticUpperCurve[point1 + 1].Item2;
}
else
{
double slope = (Curves.IticUpperCurve[point1 + 1].Item1 - Curves.IticUpperCurve[point1].Item1) / (Curves.IticUpperCurve[point1 + 1].Item2 - Curves.IticUpperCurve[point1].Item2);
vc = slope * (x.DurationSeconds - Curves.IticUpperCurve[point1].Item2) + Curves.IticUpperCurve[point1].Item1;
}
double value = (1.0D - x.PerUnitMagnitude) / (1.0D - vc);
return(value >= 1.0D);
}).Count();
List <int> channelIds = DataContext.Table <Channel>().QueryRecordsWhere("MeterID = {0} AND MeasurementCharacteristicID = (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD')", meterGroup.Key).Select(x => x.ID).ToList();
List <openHistorian.XDALink.TrendingDataPoint> historianPoints = new List <openHistorian.XDALink.TrendingDataPoint>();
using (Historian historian = new Historian(historianServer, historianInstance))
{
foreach (openHistorian.XDALink.TrendingDataPoint point in historian.Read(channelIds, startDate, endDate))
{
if (point.Value < 0.1)
{
point.Value *= 100;
}
if (point.SeriesID.ToString() == "Average" && point.Value >= 0 && point.Value <= 10)
{
historianPoints.Add(point);
}
}
}
string thdjson = "{" + string.Join(",", historianPoints.GroupBy(x => (int)(x.Value / 0.1)).Where(x => x.Key > 0).OrderBy(x => x.Key).Select(x => $"\"{x.Key}\":\"{x.Count()}\"")) + "}";
PQMarkAggregate record = DataContext.Table <PQMarkAggregate>().QueryRecordWhere("MeterID = {0} AND Year = {1} AND Month = {2}", meterGroup.Key, startDate.Year, startDate.Month);
if (record != null)
{
record.ITIC = iticLower + iticUpper;
record.SEMI = semi;
record.SARFI90 = sarfi90;
record.SARFI70 = sarfi70;
record.SARFI50 = sarfi50;
record.SARFI10 = sarfi10;
record.THDJson = thdjson;
DataContext.Table <PQMarkAggregate>().UpdateRecord(record);
}
else
{
record = new PQMarkAggregate()
{
MeterID = meterGroup.Key,
Year = startDate.Year,
Month = startDate.Month,
ITIC = iticLower + iticUpper,
SEMI = semi,
SARFI90 = sarfi90,
SARFI70 = sarfi70,
SARFI50 = sarfi50,
SARFI10 = sarfi10,
THDJson = thdjson
};
DataContext.Table <PQMarkAggregate>().AddNewRecord(record);
}
}
OnLogStatusMessage(string.Format("PQMark data aggregation is complete..."));
}
public Task <string[][]> GetData(CancellationToken cancellationToken)
{
Dictionary <string, string> query = Request.QueryParameters();
int meterID = int.Parse(query["meterID"]);
string level = query["level"];
string type = query["type"];
int phaseID = int.Parse(query["phase"]);
DateTime startTime = DateTime.ParseExact(query["date"] + "T00:00:00", "yyyy-MM-ddTHH:mm:ss", CultureInfo.CurrentCulture);
DateTime endTime = DateTime.ParseExact(query["date"] + "T23:59:59", "yyyy-MM-ddTHH:mm:ss", CultureInfo.CurrentCulture);
return(Task.Factory.StartNew(() =>
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
string target = "SpectralDataDisplay" + meterID.ToString() + startTime.ToString("yyyyMMdd") + level + type + phaseID.ToString();
List <List <string> > data = (List <List <string> >)s_memoryCache.Get(target);
if (data == null)
{
Dictionary <DateTime, Dictionary <string, double> > dict = new Dictionary <DateTime, Dictionary <string, double> >();
IEnumerable <Channel> channels = (new TableOperations <Channel>(connection)).QueryRecords(GetRecordRestriction(meterID, phaseID, type));
IEnumerable <string> headers = channels.Select(x => GetBucket(type, x));
data = new List <List <string> >();
data.Add((new List <string>()
{
"TimeStamp"
}).Concat(headers).ToList());
string historianServer = connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Historian.Server'") ?? "127.0.0.1";
string historianInstance = connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Historian.Instance'") ?? "XDA";
using (Historian historian = new Historian(historianServer, historianInstance))
{
IEnumerable <IGrouping <DateTime, TrendingDataPoint> > points = historian.Read(channels.Select(x => x.ID).ToArray(), startTime, endTime).Where(x => x.SeriesID.ToString() == level).GroupBy(x => x.Timestamp);
foreach (var grouping in points)
{
dict.Add(grouping.Key, new Dictionary <string, double>());
foreach (TrendingDataPoint trendingDataPoint in grouping)
{
dict[grouping.Key].Add(GetBucket(type, channels.First(x => x.ID == trendingDataPoint.ChannelID)), trendingDataPoint.Value);
}
}
foreach (var pair in dict)
{
List <string> row = new List <string>()
{
pair.Key.ToString()
};
foreach (string header in headers)
{
row.Add(pair.Value[header].ToString());
}
data.Add(row);
}
}
s_memoryCache.Add(target, data, new CacheItemPolicy {
SlidingExpiration = TimeSpan.FromMinutes(5.0D)
});
}
return data.Select(x => x.ToArray()).ToArray();
}
}, cancellationToken));
}
public void ProcessSmartAlarmsNormal(IEnumerable <int> meterIds, IEnumerable <int> typeIds, DateTime startDate, DateTime endDate, int sigmaLevel, int decimals, bool ignoreLargeValues, bool overwriteOldAlarms, int largeValueLevel)
{
int progressTotal = (meterIds.Any() ? meterIds.Count() : 1);
int progressCount = 0;
ProgressUpdatedOverall("", (int)(100 * (progressCount) / progressTotal));
string historianServer = DataContext.Connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Historian.Server'") ?? "127.0.0.1";
string historianInstance = DataContext.Connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Historian.Instance'") ?? "XDA";
foreach (int meterId in meterIds)
{
string characteristicList = "(" + string.Join(",", typeIds) + ")";
IEnumerable <int> channelIds = DataContext.Table <Channel>().QueryRecordsWhere("MeterID = {0} AND MeasurementCharacteristicID IN " + characteristicList, meterId).Select(x => x.ID);
string meterName = DataContext.Connection.ExecuteScalar <string>("Select Name from Meter where ID = {0}", meterId);
ProgressUpdatedOverall(meterName, (int)(100 * (progressCount) / progressTotal));
List <TrendingData> trendingData = new List <TrendingData>();
List <RunningAvgStdDev> normalRunningData = new List <RunningAvgStdDev>();
ProgressUpdatedByMeter("Querying openHistorian...", 0);
using (openHistorian.XDALink.Historian historian = new Historian(historianServer, historianInstance))
{
foreach (openHistorian.XDALink.TrendingDataPoint point in historian.Read(channelIds, startDate, endDate))
{
RunningAvgStdDev normalRecord = normalRunningData.FirstOrDefault(x => x.ChannelID == point.ChannelID);
if (normalRecord == null)
{
normalRecord = new RunningAvgStdDev()
{
ChannelID = point.ChannelID,
Count = 0,
Sum = 0,
SumOfSquares = 0
};
normalRunningData.Add(normalRecord);
}
if (point.SeriesID.ToString() == "Average")
{
normalRecord.Sum += point.Value;
normalRecord.SumOfSquares += (point.Value * point.Value);
++normalRecord.Count;
}
}
if (ignoreLargeValues)
{
normalRunningData = normalRunningData.Select(x =>
{
double average = x.Sum / (x.Count != 0 ? x.Count : 1);
x.FirstPassStdDev = Math.Sqrt(Math.Abs((x.SumOfSquares - 2 * average * x.Sum + x.Count * average * average) / ((x.Count != 1 ? x.Count : 2) - 1)));
x.Count = 0;
x.Sum = 0;
x.SumOfSquares = 0;
return(x);
}).ToList();
ProgressUpdatedByMeter("Querying openHistorian for second pass...", 0);
foreach (openHistorian.XDALink.TrendingDataPoint point in historian.Read(channelIds, startDate, endDate))
{
int hourOfWeek = (int)point.Timestamp.DayOfWeek * 24 + point.Timestamp.Hour;
RunningAvgStdDev normalRecord = normalRunningData.FirstOrDefault(x => x.ChannelID == point.ChannelID);
if ((point.SeriesID.ToString() == "Average" && point.Value > (normalRecord.FirstPassStdDev * largeValueLevel)) || (point.SeriesID.ToString() == "Average" && point.Value < (normalRecord.FirstPassStdDev * largeValueLevel)))
{
continue;
}
if (point.SeriesID.ToString() == "Average")
{
normalRecord.Sum += point.Value;
normalRecord.SumOfSquares += (point.Value * point.Value);
++normalRecord.Count;
}
}
}
}
int innerProgressTotal = (channelIds.Any() ? channelIds.Count() : 1);
int innerProgressCount = 0;
foreach (int channelId in channelIds)
{
string channelName = DataContext.Connection.ExecuteScalar <string>("Select Name from Channel where ID = {0}", channelId);
ProgressUpdatedByMeter(channelName, (int)(100 * (innerProgressCount) / innerProgressTotal));
RunningAvgStdDev record = normalRunningData.Where(x => x.ChannelID == channelId).FirstOrDefault();
if (record != null)
{
double average = record.Sum / (record.Count != 0 ? record.Count : 1);
double stdDev = Math.Sqrt(Math.Abs((record.SumOfSquares - 2 * average * record.Sum + record.Count * average * average) / ((record.Count != 1 ? record.Count : 2) - 1)));
float high = (float)Math.Round(average + stdDev * sigmaLevel, decimals);
float low = (float)Math.Round(average - stdDev * sigmaLevel, decimals);
AlarmRangeLimit hwl = DataContext.Table <AlarmRangeLimit>().QueryRecordWhere("ChannelID = {0}", record.ChannelID);
if (hwl == null)
{
AlarmRangeLimit newRecord = new AlarmRangeLimit()
{
ChannelID = record.ChannelID,
AlarmTypeID = 5,
Severity = 1,
High = high,
Low = low,
RangeInclusive = 0,
PerUnit = 0,
Enabled = 1,
IsDefault = false
};
DataContext.Table <AlarmRangeLimit>().AddNewRecord(newRecord);
}
else if (hwl != null && overwriteOldAlarms)
{
hwl.High = high;
hwl.Low = low;
DataContext.Table <AlarmRangeLimit>().UpdateRecord(hwl);
}
}
ProgressUpdatedByMeter(channelName, (int)(100 * (++innerProgressCount) / innerProgressTotal));
}
ProgressUpdatedOverall(meterName, (int)(100 * (++progressCount) / progressTotal));
}
}
public Task <Dictionary <string, List <double[]> > > GetData(CancellationToken cancellationToken)
{
Dictionary <string, string> query = Request.QueryParameters();
int meterId = int.Parse(query["MeterID"]);
int measurementCharacteristicId = int.Parse(query["MeasurementCharacteristicID"]);
int measurementTypeId = int.Parse(query["MeasurementTypeID"]);
int pixels = int.Parse(query["pixels"]);
string type = query["type"] ?? "Average";
DateTime startDate = DateTime.Parse(query["StartDate"]);
DateTime endDate = DateTime.Parse(query["EndDate"]);
DateTime epoch = new DateTime(1970, 1, 1);
return(Task.Factory.StartNew(() =>
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
string target = "PeriodicDataDisplay" + meterId.ToString() + measurementCharacteristicId.ToString() + startDate.Subtract(epoch).TotalMilliseconds.ToString() + endDate.Subtract(epoch).TotalMilliseconds.ToString() + type;
Dictionary <string, List <double[]> > data = (Dictionary <string, List <double[]> >)s_memoryCache.Get(target);
if (data == null)
{
data = new Dictionary <string, List <double[]> >();
IEnumerable <Channel> channels = (new TableOperations <Channel>(connection)).QueryRecordsWhere("MeterID = {0} AND MeasurementCharacteristicID = {1} AND MeasurementTypeID = {2}", meterId, measurementCharacteristicId, measurementTypeId);
string historianServer = connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Historian.Server'") ?? "127.0.0.1";
string 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(channels.Select(x => x.ID), startDate, endDate))
{
if (point.SeriesID.ToString() == type)
{
Channel channel = channels.First(x => x.ID == point.ChannelID);
if (!data.ContainsKey(channel.Name))
{
data.Add(channel.Name, new List <double[]>()
{
new[] { point.Timestamp.Subtract(epoch).TotalMilliseconds, point.Value }
});
}
else
{
data[channel.Name].Add(new[] { point.Timestamp.Subtract(epoch).TotalMilliseconds, point.Value });
}
}
}
}
s_memoryCache.Add(target, data, new CacheItemPolicy {
SlidingExpiration = TimeSpan.FromMinutes(0.5D)
});
}
Console.WriteLine(stopWatch.Elapsed);
return Downsample(data, pixels, new Range <DateTime>(startDate, endDate));
}
}, cancellationToken));
}
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 void ProcessMeasurement(Meter meter, PQMeasurement measurement, string historianServer, string historianInstance, DateTime startTime, DateTime endTime)
{
Channel channel;
PQTrendStat record;
using (DataContext dataContext = new DataContext("systemSettings"))
{
channel = dataContext.Table <Channel>().QueryRecordWhere("MeasurementTypeID = {0} AND MeasurementCharacteristicID = {1} AND PhaseID = {2} AND HarmonicGroup = {3} AND MeterID = {4}", measurement.MeasurementTypeID, measurement.MeasurementCharacteristicID, measurement.PhaseID, measurement.HarmonicGroup, meter.ID);
record = dataContext.Table <PQTrendStat>().QueryRecordWhere("MeterID = {0} AND Date = {1} AND PQMeasurementTypeID = {2}", meter.ID, startTime, measurement.ID);
}
if (record == null)
{
record = new PQTrendStat();
record.MeterID = meter.ID;
record.PQMeasurementTypeID = measurement.ID;
record.Date = startTime;
}
record.Max = null;
record.Min = null;
record.Avg = null;
record.CP99 = null;
record.CP95 = null;
record.CP05 = null;
record.CP01 = null;
if (channel != null)
{
using (Historian historian = new Historian(historianServer, historianInstance))
{
IEnumerable <openHistorian.XDALink.TrendingDataPoint> data = historian.Read(new[] { channel.ID }, startTime, endTime).ToList();
if (!data.Any())
{
return;
}
IEnumerable <openHistorian.XDALink.TrendingDataPoint> avgList = data.Where(x => x.SeriesID == SeriesID.Average).OrderBy(x => x.Value);
try
{
record.Max = data.Where(x => x.SeriesID == SeriesID.Maximum).Select(x => x.Value).Max();
record.Min = data.Where(x => x.SeriesID == SeriesID.Minimum).Select(x => x.Value).Min();
record.Avg = avgList.Select(x => x.Value).Average();
}
catch (Exception ex)
{
if (PQTrendingWebReportSettings.Verbose)
{
Log.Error(ex.Message, ex);
}
}
try
{
int index99 = (int)(avgList.Count() * (0.995));
int index01 = (int)(avgList.Count() * (0.005));
record.CP99 = avgList.Where((x, i) => i >= index01 && i <= index99).Select(x => x.Value).Max();
record.CP01 = avgList.Where((x, i) => i >= index01 && i <= index99).Select(x => x.Value).Min();
}
catch (Exception ex)
{
record.CP99 = null;
record.CP01 = null;
if (PQTrendingWebReportSettings.Verbose)
{
Log.Error(ex.Message, ex);
}
}
try
{
int index95 = (int)(avgList.Count() * (0.975));
int index05 = (int)(avgList.Count() * (0.025));
record.CP95 = avgList.Where((x, i) => i >= index05 && i <= index95).Select(x => x.Value).Max();
record.CP05 = avgList.Where((x, i) => i >= index05 && i <= index95).Select(x => x.Value).Min();
}
catch (Exception ex)
{
record.CP95 = null;
record.CP05 = null;
if (PQTrendingWebReportSettings.Verbose)
{
Log.Error(ex.Message, ex);
}
}
}
using (DataContext dataContext = new DataContext("systemSettings"))
{
if (record.Avg != null && record.Max != null && record.Min != null)
{
dataContext.Table <PQTrendStat>().AddNewOrUpdateRecord(record);
}
}
}
}
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;
}
private void ProcessMeasurement(Meter meter, StepChangeMeasurement measurement, string historianServer, string historianInstance, DateTime startTime, DateTime endTime)
{
using (DataContext dataContext = new DataContext("systemSettings"))
using (Historian historian = new Historian(historianServer, historianInstance))
{
PQMeasurement pqMeasurement = dataContext.Table <PQMeasurement>().QueryRecordWhere("ID = {0}", measurement.PQMeasurementID);
Channel channel = dataContext.Table <Channel>().QueryRecordWhere("MeasurementTypeID = {0} AND MeasurementCharacteristicID = {1} AND PhaseID = {2} AND HarmonicGroup = {3} AND MeterID = {4}", pqMeasurement.MeasurementTypeID, pqMeasurement.MeasurementCharacteristicID, pqMeasurement.PhaseID, pqMeasurement.HarmonicGroup, meter.ID);
StepChangeStat record = dataContext.Table <StepChangeStat>().QueryRecordWhere("MeterID = {0} AND Date = {1} AND StepChangeMeasurementID = {2}", meter.ID, startTime, measurement.ID);
Unit unit = dataContext.Table <Unit>().QueryRecordWhere("ID = {0}", pqMeasurement.UnitID);
if (record == null)
{
record = new StepChangeStat();
record.MeterID = meter.ID;
record.StepchangeMeasurementID = measurement.ID;
record.Date = startTime;
}
record.Value = 0;
if (channel != null)
{
IEnumerable <openHistorian.XDALink.TrendingDataPoint> data = historian.Read(new[] { channel.ID }, startTime.AddHours(-1), endTime).Where(x => x.SeriesID == SeriesID.Average).ToList();
// no associated data for this measurement; just return without creating the record.
if (!data.Any())
{
return;
}
try
{
foreach (openHistorian.XDALink.TrendingDataPoint point in data)
{
if (point.Timestamp >= startTime)
{
double lastHourAvg = data.Where(x => x.Timestamp >= point.Timestamp.AddHours(-1) && x.Timestamp < point.Timestamp).Select(x => x.Value).Average();
double std = data.StandardDeviation(x => x.Value);
double average = data.Average(x => x.Value);
// if value is outside 5 sigma, do not check for step change
if (point.Value > average + std * 5 || point.Value < average - std * 5)
{
continue;
}
if (unit.Name == "Percent")
{
if (Math.Abs(point.Value - lastHourAvg) > measurement.Setting)
{
record.Value++;
}
}
else
{
if ((Math.Abs(point.Value - lastHourAvg) * 100 / lastHourAvg) > measurement.Setting)
{
record.Value++;
}
}
}
}
}
catch (Exception)
{
// no associated data for this measurement; just return without creating the record.
return;
}
}
if (record.Value > 0)
{
dataContext.Table <StepChangeStat>().AddNewOrUpdateRecord(record);
}
}
}
