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());
}
