private List <TrendingDataLocation> GetFrameFromDailySummary(ContourQuery contourQuery)
{
List <TrendingDataLocation> locations = new List <TrendingDataLocation>();
using (AdoDataConnection conn = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
using (DataContext dataContext = new DataContext(conn))
using (IDbCommand cmd = conn.Connection.CreateCommand())
{
var meters = dataContext.Table <Meter>().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();
}
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("@meterIds", string.Join(",", meters.Select(x => x.ID))));
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"];
locations.Add(ourStatus);
}
}
}
return(locations);
}
private List<TrendingDataLocation> GetFrameFromDailySummary(ContourQuery contourQuery)
{
List<TrendingDataLocation> locations = new List<TrendingDataLocation>();
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"];
locations.Add(ourStatus);
}
}
}
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 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
locations = locations
.OrderBy(location => location.id)
.Where((location, index) => (meterSelections[index / byteSize] & (0x80 >> (index % byteSize))) > 0)
.ToList();
}
return locations;
}
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 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<TrendingDataLocation> GetFrameFromDailySummary(ContourQuery contourQuery)
{
List<TrendingDataLocation> locations = new List<TrendingDataLocation>();
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"];
locations.Add(ourStatus);
}
}
}
return locations;
}
public IHttpActionResult Post(ContourQuery contourQuery)
{
try
{
List <TrendingDataLocation> locations = new List <TrendingDataLocation>();
DataTable colorScale;
using (AdoDataConnection connection = new AdoDataConnection("dbOpenXDA"))
{
DataTable table = connection.RetrieveData(@"
DECLARE @EventDateFrom DATETIME = {0}
DECLARE @EventDateTo DATETIME = {1}
DECLARE @colorScaleName AS varchar(max) = {2}
DECLARE @meterIds AS varchar(max) = {3}
DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE)
DECLARE @endDate DATE = CAST(@EventDateTo AS DATE)
DECLARE @thedatefrom AS DATE
DECLARE @thedateto AS DATE
SET @thedatefrom = CAST(@EventDateFrom AS DATE)
SET @thedateto = CAST(@EventDateTo AS DATE)
SELECT
Meter.ID,
Meter.Name,
Data.Minimum AS Minimum,
Data.Maximum AS Maximum,
Data.Average AS Average,
Location.Longitude,
Location.Latitude
FROM
Meter LEFT OUTER JOIN
(
SELECT
ContourChannel.MeterID AS MID,
MIN(Minimum/COALESCE(ContourChannel.PerUnitValue, 1)) AS Minimum,
MAX(Maximum/COALESCE(ContourChannel.PerUnitValue, 1)) AS Maximum,
AVG(Average/COALESCE(ContourChannel.PerUnitValue, 1)) AS Average
FROM
ContourChannel JOIN
DailyTrendingSummary ON DailyTrendingSummary.ChannelID = ContourChannel.ChannelID
WHERE Date >= @thedatefrom AND Date <= @thedateto AND ContourColorScaleName = @colorScaleName
GROUP BY ContourChannel.MeterID
) AS Data ON Data.MID = Meter.ID JOIN
Location ON Meter.LocationID = Location.ID
WHERE Meter.ID IN (SELECT * FROM String_To_Int_Table(@meterIds, ','))
ORDER BY Meter.Name
", contourQuery.GetStartDate(), contourQuery.GetEndDate(), contourQuery.ColorScaleName, contourQuery.MeterIds);
foreach (DataRow row in table.Rows)
{
TrendingDataLocation ourStatus = new TrendingDataLocation();
ourStatus.Latitude = (double)row["Latitude"];
ourStatus.Longitude = (double)row["Longitude"];
ourStatus.Name = (string)row["Name"];
ourStatus.Average = row.Field <double?>("Average");
ourStatus.Maximum = row.Field <double?>("Maximum");
ourStatus.Minimum = row.Field <double?>("Minimum");
ourStatus.ID = (int)row["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 = connection.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(Ok(new ContourInfo()
{
Locations = locations,
ColorDomain = colorDomain,
ColorRange = colorRange,
DateTo = contourQuery.EndDate,
DateFrom = contourQuery.StartDate
}));
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}