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 ContourTileData GetContourTileData(ContourQuery contourQuery)
{
string key = new ConnectionStringParser().ComposeConnectionString(contourQuery);
ContourTileData contourTileData = new ContourTileData();
CacheItemPolicy cacheItemPolicy = new CacheItemPolicy()
{
SlidingExpiration = TimeSpan.FromMinutes(1)
};
contourTileData = (ContourTileData)s_contourDataCache.AddOrGetExisting(key, contourTileData, cacheItemPolicy) ?? contourTileData;
if ((object)contourTileData.IDWFunction != null && (object)contourTileData.ColorFunction != null)
{
return(contourTileData);
}
using (ManualResetEvent waitHandle = new ManualResetEvent(false))
{
ManualResetEvent cachedWaitHandle = Interlocked.CompareExchange(ref contourTileData.WaitHandle, waitHandle, null);
try
{
try
{
if ((object)cachedWaitHandle != null)
{
cachedWaitHandle.WaitOne();
return(contourTileData);
}
}
catch (ObjectDisposedException)
{
return(contourTileData);
}
List <TrendingDataLocation> locations = GetFrameFromDailySummary(contourQuery);
Func <double, double> colorFunction = GetColorScale(contourQuery);
IDWFunc idwFunction = GetIDWFunction(contourQuery, locations);
if (locations.Any())
{
double latDif = locations.Max(location => location.Latitude) - locations.Min(location => location.Latitude);
double lonDif = locations.Max(location => location.Longitude) - locations.Min(location => location.Longitude);
contourTileData.MinLatitude = locations.Min(location => location.Latitude) - (latDif * 0.1D);
contourTileData.MaxLatitude = locations.Max(location => location.Latitude) + (latDif * 0.1D);
contourTileData.MinLongitude = locations.Min(location => location.Longitude) - (lonDif * 0.1D);
contourTileData.MaxLongitude = locations.Max(location => location.Longitude) + (lonDif * 0.1D);
}
contourTileData.IDWFunction = idwFunction;
contourTileData.ColorFunction = colorFunction;
return(contourTileData);
}
finally
{
waitHandle.Set();
}
}
}
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 InverseDistanceWeightingFunction GetIDWFunction(ContourQuery contourQuery, List <TrendingDataLocation> locations = null)
{
CoordinateReferenceSystem crs = new EPSG3857();
List <double> xList = new List <double>();
List <double> yList = new List <double>();
List <double> valueList = new List <double>();
if ((object)locations == null)
{
locations = GetFrameFromDailySummary(contourQuery);
}
locations
.Select(location =>
{
GeoCoordinate Coordinate = new GeoCoordinate(location.Latitude, location.Longitude);
double?Value =
(contourQuery.DataType == "Average") ? location.Average :
(contourQuery.DataType == "Minimum") ? location.Minimum :
(contourQuery.DataType == "Maximum") ? location.Maximum :
null;
return(new { Coordinate, Value });
})
.Where(obj => (object)obj.Value != null)
.ToList()
.ForEach(obj =>
{
xList.Add(obj.Coordinate.Longitude);
yList.Add(obj.Coordinate.Latitude);
valueList.Add(obj.Value.GetValueOrDefault());
});
if (valueList.Count == 0)
{
xList.Add(0.0D);
yList.Add(0.0D);
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
valueList.Add(connection.ExecuteScalar <double>("SELECT NominalValue FROM ContourColorScale WHERE Name = {0}", contourQuery.ColorScaleName));
}
}
return(new InverseDistanceWeightingFunction()
.SetXCoordinates(xList.ToArray())
.SetYCoordinates(yList.ToArray())
.SetValues(valueList.ToArray())
.SetDistanceFunction((x1, y1, x2, y2) =>
{
GeoCoordinate coordinate1 = new GeoCoordinate(y1, x1);
GeoCoordinate coordinate2 = new GeoCoordinate(y2, x2);
return crs.Distance(coordinate1, coordinate2);
}));
}
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;
}
private ContourTileData GetContourTileData(ContourQuery contourQuery)
{
string key = new ConnectionStringParser().ComposeConnectionString(contourQuery);
ContourTileData contourTileData = new ContourTileData();
CacheItemPolicy cacheItemPolicy = new CacheItemPolicy() { SlidingExpiration = TimeSpan.FromMinutes(1) };
contourTileData = (ContourTileData)s_contourDataCache.AddOrGetExisting(key, contourTileData, cacheItemPolicy) ?? contourTileData;
if ((object)contourTileData.IDWFunction != null && (object)contourTileData.ColorFunction != null)
return contourTileData;
using (ManualResetEvent waitHandle = new ManualResetEvent(false))
{
ManualResetEvent cachedWaitHandle = Interlocked.CompareExchange(ref contourTileData.WaitHandle, waitHandle, null);
try
{
try
{
if ((object)cachedWaitHandle != null)
{
cachedWaitHandle.WaitOne();
return contourTileData;
}
}
catch (ObjectDisposedException)
{
return contourTileData;
}
List<TrendingDataLocation> locations = GetFrameFromDailySummary(contourQuery);
Func<double, double> colorFunction = GetColorScale(contourQuery);
IDWFunc idwFunction = GetIDWFunction(contourQuery, locations);
contourTileData.MinLatitude = locations.Min(location => location.Latitude) - GetLatFromMiles(50.0D);
contourTileData.MaxLatitude = locations.Max(location => location.Latitude) + GetLatFromMiles(50.0D);
contourTileData.MinLongitude = locations.Min(location => location.Longitude) - GetLngFromMiles(50.0D, 0.0D);
contourTileData.MaxLongitude = locations.Max(location => location.Longitude) + GetLngFromMiles(50.0D, 0.0D);
contourTileData.IDWFunction = idwFunction;
contourTileData.ColorFunction = colorFunction;
return contourTileData;
}
finally
{
waitHandle.Set();
}
}
}
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);
}
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<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;
}
private InverseDistanceWeightingFunction GetIDWFunction(ContourQuery contourQuery, List<TrendingDataLocation> locations = null)
{
CoordinateReferenceSystem crs = new EPSG3857();
List<double> xList = new List<double>();
List<double> yList = new List<double>();
List<double> valueList = new List<double>();
if ((object)locations == null)
locations = GetFrameFromDailySummary(contourQuery);
locations
.Select(location =>
{
GeoCoordinate Coordinate = new GeoCoordinate(location.Latitude, location.Longitude);
double? Value =
(contourQuery.DataType == "Average") ? location.Average :
(contourQuery.DataType == "Minimum") ? location.Minimum :
(contourQuery.DataType == "Maximum") ? location.Maximum :
null;
return new { Coordinate, Value };
})
.Where(obj => (object)obj.Value != null)
.ToList()
.ForEach(obj =>
{
xList.Add(obj.Coordinate.Longitude);
yList.Add(obj.Coordinate.Latitude);
valueList.Add(obj.Value.GetValueOrDefault());
});
if (valueList.Count == 0)
{
xList.Add(0.0D);
yList.Add(0.0D);
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
valueList.Add(connection.ExecuteScalar<double>("SELECT NominalValue FROM ContourColorScale WHERE Name = {0}", contourQuery.ColorScaleName));
}
}
return new InverseDistanceWeightingFunction()
.SetXCoordinates(xList.ToArray())
.SetYCoordinates(yList.ToArray())
.SetValues(valueList.ToArray())
.SetDistanceFunction((x1, y1, x2, y2) =>
{
GeoCoordinate coordinate1 = new GeoCoordinate(y1, x1);
GeoCoordinate coordinate2 = new GeoCoordinate(y2, x2);
return crs.Distance(coordinate1, coordinate2);
});
}
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());
}
public void getContourTile()
{
ContourQuery contourQuery = new ContourQuery()
{
StartDate = HttpContext.Current.Request.QueryString["StartDate"],
EndDate = HttpContext.Current.Request.QueryString["EndDate"],
ColorScaleName = HttpContext.Current.Request.QueryString["ColorScaleName"],
DataType = HttpContext.Current.Request.QueryString["DataType"],
UserName = HttpContext.Current.Request.QueryString["Username"],
Meters = HttpContext.Current.Request.QueryString["Meters"],
MeterIds = HttpContext.Current.Request.QueryString["MeterIds"]
};
ContourTileData contourTileData = GetContourTileData(contourQuery);
double minLat = contourTileData.MinLatitude;
double maxLat = contourTileData.MaxLatitude;
double minLng = contourTileData.MinLongitude;
double maxLng = contourTileData.MaxLongitude;
CoordinateReferenceSystem crs = s_crs;
IDWFunc idwFunction = contourTileData.IDWFunction;
Func <double, double> colorFunction = contourTileData.ColorFunction;
int tileX = Convert.ToInt32(HttpContext.Current.Request.QueryString["x"]);
int tileY = Convert.ToInt32(HttpContext.Current.Request.QueryString["y"]);
int zoom = Convert.ToInt32(HttpContext.Current.Request.QueryString["zoom"]);
int tileSize = 256;
int offsetX = tileSize * tileX;
int offsetY = tileSize * tileY;
uint[] pixelData = new uint[tileSize * tileSize];
for (int x = 0; x < tileSize; x++)
{
GSF.Drawing.Point validationPixel = new GSF.Drawing.Point(offsetX + x, 0.0D);
GeoCoordinate validationCoordinate = crs.Translate(validationPixel, zoom);
if (validationCoordinate.Longitude < minLng || validationCoordinate.Longitude > maxLng)
{
continue;
}
for (int y = 0; y < tileSize; y++)
{
GSF.Drawing.Point offsetPixel = new GSF.Drawing.Point(offsetX + x, offsetY + y);
GeoCoordinate pixelCoordinate = crs.Translate(offsetPixel, zoom);
if (pixelCoordinate.Latitude < minLat || pixelCoordinate.Latitude > maxLat)
{
continue;
}
double interpolatedValue = idwFunction(pixelCoordinate.Longitude, pixelCoordinate.Latitude);
uint color = (uint)colorFunction(interpolatedValue);
pixelData[y * tileSize + x] = color;
}
}
using (Bitmap bitmap = BitmapExtensions.FromPixelData(256, pixelData))
{
HttpContext.Current.Response.ContentType = "image/png";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename=tile{0}x{1}.png", tileX, tileY));
bitmap.Save(HttpContext.Current.Response.OutputStream, ImageFormat.Png);
}
}
private InverseDistanceWeightingFunction GetIDWFunction(ContourQuery contourQuery, List<TrendingDataLocation> locations = null)
{
CoordinateReferenceSystem crs = new EPSG3857();
List<double> xList = new List<double>();
List<double> yList = new List<double>();
List<double> valueList = new List<double>();
if ((object)locations == null)
locations = GetFrameFromDailySummary(contourQuery);
locations
.Select(location =>
{
GeoCoordinate Coordinate = new GeoCoordinate(location.Latitude, location.Longitude);
double? Value =
(contourQuery.DataType == "Average") ? location.Average :
(contourQuery.DataType == "Minimum") ? location.Minimum :
(contourQuery.DataType == "Maximum") ? location.Maximum :
null;
return new { Coordinate, Value };
})
.Where(obj => (object)obj.Value != null)
.ToList()
.ForEach(obj =>
{
xList.Add(obj.Coordinate.Longitude);
yList.Add(obj.Coordinate.Latitude);
valueList.Add(obj.Value.GetValueOrDefault());
});
return new InverseDistanceWeightingFunction()
.SetXCoordinates(xList.ToArray())
.SetYCoordinates(yList.ToArray())
.SetValues(valueList.ToArray())
.SetDistanceFunction((x1, y1, x2, y2) =>
{
GeoCoordinate coordinate1 = new GeoCoordinate(y1, x1);
GeoCoordinate coordinate2 = new GeoCoordinate(y2, x2);
return crs.Distance(coordinate1, coordinate2);
});
}
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 ContourAnimationInfo getContourAnimations(ContourQuery contourQuery)
{
List<List<TrendingDataLocation>> frames = GetFramesFromHistorian(contourQuery);
PiecewiseLinearFunction colorScale = GetColorScale(contourQuery);
Func<double, double> colorFunc = colorScale;
// The actual startDate is the timestamp of the
// first frame after contourQuery.GetStartDate()
DateTime startDate = contourQuery.GetStartDate();
int stepSize = contourQuery.StepSize;
int startTimeOffset = (int)Math.Ceiling((startDate - startDate.Date).TotalMinutes / stepSize);
startDate = startDate.Date.AddMinutes(startTimeOffset * stepSize);
double minLat = frames.Min(frame => frame.Min(location => location.Latitude)) - GetLatFromMiles(50.0D);
double maxLat = frames.Min(frame => frame.Max(location => location.Latitude)) + GetLatFromMiles(50.0D);
double minLng = frames.Min(frame => frame.Min(location => location.Longitude)) - GetLngFromMiles(50.0D, 0.0D);
double maxLng = frames.Min(frame => frame.Max(location => location.Longitude)) + GetLngFromMiles(50.0D, 0.0D);
GeoCoordinate topLeft = new GeoCoordinate(maxLat, minLng);
GeoCoordinate bottomRight = new GeoCoordinate(minLat, maxLng);
GSF.Drawing.Point topLeftPoint = s_crs.Translate(topLeft, contourQuery.Resolution);
GSF.Drawing.Point bottomRightPoint = s_crs.Translate(bottomRight, contourQuery.Resolution);
topLeftPoint = new GSF.Drawing.Point(Math.Floor(topLeftPoint.X), Math.Floor(topLeftPoint.Y));
bottomRightPoint = new GSF.Drawing.Point(Math.Ceiling(bottomRightPoint.X), Math.Ceiling(bottomRightPoint.Y));
topLeft = s_crs.Translate(topLeftPoint, contourQuery.Resolution);
bottomRight = s_crs.Translate(bottomRightPoint, contourQuery.Resolution);
int width = (int)(bottomRightPoint.X - topLeftPoint.X + 1);
int height = (int)(bottomRightPoint.Y - topLeftPoint.Y + 1);
int animationID;
string timeZoneID = null;
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
connection.ExecuteNonQuery("INSERT INTO ContourAnimation(ColorScaleName, StartTime, EndTime, StepSize) VALUES({0}, {1}, {2}, {3})", contourQuery.ColorScaleName, contourQuery.GetStartDate(), contourQuery.GetEndDate(), contourQuery.StepSize);
animationID = connection.ExecuteScalar<int>("SELECT @@IDENTITY");
if (contourQuery.IncludeWeather)
timeZoneID = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'XDATimeZone'");
}
GSF.Threading.CancellationToken cancellationToken = new GSF.Threading.CancellationToken();
s_cancellationTokens[animationID] = cancellationToken;
ProgressCounter progressCounter = new ProgressCounter(frames.Count);
s_progressCounters[animationID] = progressCounter;
Action<int> createFrame = i =>
{
List<TrendingDataLocation> frame = frames[i];
IDWFunc idwFunction = GetIDWFunction(contourQuery, frame);
uint[] pixelData;
if (contourQuery.IncludeWeather)
{
TimeZoneInfo tzInfo = !string.IsNullOrEmpty(timeZoneID)
? TimeZoneInfo.FindSystemTimeZoneById(timeZoneID)
: TimeZoneInfo.Local;
// Weather data is only available in 5-minute increments
DateTime frameTime = TimeZoneInfo.ConvertTimeToUtc(startDate.AddMinutes(stepSize * i), tzInfo);
double minutes = (frameTime - frameTime.Date).TotalMinutes;
int weatherMinutes = (int)Math.Ceiling(minutes / 5) * 5;
NameValueCollection queryString = HttpUtility.ParseQueryString(string.Empty);
queryString["service"] = "WMS";
queryString["request"] = "GetMap";
queryString["layers"] = "nexrad-n0r-wmst";
queryString["format"] = "image/png";
queryString["transparent"] = "true";
queryString["version"] = "1.1.1";
queryString["time"] = frameTime.Date.AddMinutes(weatherMinutes).ToString("o");
queryString["height"] = height.ToString();
queryString["width"] = width.ToString();
queryString["srs"] = "EPSG:3857";
GSF.Drawing.Point topLeftProjected = s_crs.Projection.Project(topLeft);
GSF.Drawing.Point bottomRightProjected = s_crs.Projection.Project(bottomRight);
queryString["bbox"] = string.Join(",", topLeftProjected.X, bottomRightProjected.Y, bottomRightProjected.X, topLeftProjected.Y);
string weatherURL = "http://mesonet.agron.iastate.edu/cgi-bin/wms/nexrad/n0r-t.cgi?" + queryString.ToString();
using (WebClient client = new WebClient())
using (MemoryStream stream = new MemoryStream(client.DownloadData(weatherURL)))
using (Bitmap bitmap = new Bitmap(stream))
{
pixelData = bitmap.ToPixelData();
}
}
else
{
pixelData = new uint[width * height];
}
if (cancellationToken.IsCancelled)
return;
for (int x = 0; x < width; x++)
{
if (cancellationToken.IsCancelled)
return;
for (int y = 0; y < height; y++)
{
if (cancellationToken.IsCancelled)
return;
if (pixelData[y * width + x] > 0)
continue;
GSF.Drawing.Point offsetPixel = new GSF.Drawing.Point(topLeftPoint.X + x, topLeftPoint.Y + y);
GeoCoordinate pixelCoordinate = s_crs.Translate(offsetPixel, contourQuery.Resolution);
double interpolatedValue = idwFunction(pixelCoordinate.Longitude, pixelCoordinate.Latitude);
pixelData[y * width + x] = (uint)colorFunc(interpolatedValue);
}
}
if (cancellationToken.IsCancelled)
return;
using (Bitmap bitmap = BitmapExtensions.FromPixelData(width, pixelData))
using (MemoryStream stream = new MemoryStream())
{
bitmap.Save(stream, ImageFormat.Png);
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
connection.ExecuteNonQuery("INSERT INTO ContourAnimationFrame VALUES({0}, {1}, {2})", animationID, i, stream.ToArray());
}
}
progressCounter.Increment();
};
Task.Run(() =>
{
ICancellationToken token;
ProgressCounter counter;
Parallel.For(0, frames.Count, createFrame);
s_cancellationTokens.TryRemove(animationID, out token);
s_progressCounters.TryRemove(animationID, out counter);
if (cancellationToken.IsCancelled)
{
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
connection.ExecuteNonQuery("DELETE FROM ContourAnimationFrame WHERE ContourAnimationID = {0}", animationID);
connection.ExecuteNonQuery("DELETE FROM ContourAnimation WHERE ID = {0}", animationID);
}
}
});
s_cleanUpAnimationOperation.TryRunOnceAsync();
return new ContourAnimationInfo()
{
AnimationID = animationID,
ColorDomain = colorScale.Domain,
ColorRange = colorScale.Range,
MinLatitude = bottomRight.Latitude,
MaxLatitude = topLeft.Latitude,
MinLongitude = topLeft.Longitude,
MaxLongitude = bottomRight.Longitude,
Infos = frames.Select((frame, index) => new ContourInfo()
{
Locations = frame,
URL = string.Format("./mapService.asmx/getContourAnimationFrame?animation={0}&frame={1}", animationID, index),
Date = contourQuery.GetStartDate().AddMinutes(index * contourQuery.StepSize).ToString()
}).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();
}
public ContourAnimationInfo getContourAnimations(ContourQuery contourQuery)
{
List <List <TrendingDataLocation> > frames = GetFramesFromHistorian(contourQuery);
PiecewiseLinearFunction colorScale = GetColorScale(contourQuery);
Func <double, double> colorFunc = colorScale;
// The actual startDate is the timestamp of the
// first frame after contourQuery.GetStartDate()
DateTime startDate = contourQuery.GetStartDate();
int stepSize = contourQuery.StepSize;
int startTimeOffset = (int)Math.Ceiling((startDate - startDate.Date).TotalMinutes / stepSize);
startDate = startDate.Date.AddMinutes(startTimeOffset * stepSize);
double latDif = frames.Min(frame => frame.Max(location => location.Latitude)) - frames.Min(frame => frame.Min(location => location.Latitude));
double lonDif = frames.Min(frame => frame.Max(location => location.Longitude)) - frames.Min(frame => frame.Min(location => location.Longitude));
double minLat = frames.Min(frame => frame.Min(location => location.Latitude)) - (latDif * 0.1D);
double maxLat = frames.Min(frame => frame.Max(location => location.Latitude)) + (latDif * 0.1D);
double minLng = frames.Min(frame => frame.Min(location => location.Longitude)) - (lonDif * 0.1D);
double maxLng = frames.Min(frame => frame.Max(location => location.Longitude)) + (lonDif * 0.1D);
GeoCoordinate topLeft = new GeoCoordinate(maxLat, minLng);
GeoCoordinate bottomRight = new GeoCoordinate(minLat, maxLng);
GSF.Drawing.Point topLeftPoint = s_crs.Translate(topLeft, contourQuery.Resolution);
GSF.Drawing.Point bottomRightPoint = s_crs.Translate(bottomRight, contourQuery.Resolution);
topLeftPoint = new GSF.Drawing.Point(Math.Floor(topLeftPoint.X), Math.Floor(topLeftPoint.Y));
bottomRightPoint = new GSF.Drawing.Point(Math.Ceiling(bottomRightPoint.X), Math.Ceiling(bottomRightPoint.Y));
topLeft = s_crs.Translate(topLeftPoint, contourQuery.Resolution);
bottomRight = s_crs.Translate(bottomRightPoint, contourQuery.Resolution);
int width = (int)(bottomRightPoint.X - topLeftPoint.X + 1);
int height = (int)(bottomRightPoint.Y - topLeftPoint.Y + 1);
int animationID;
string timeZoneID = null;
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
connection.ExecuteNonQuery("INSERT INTO ContourAnimation(ColorScaleName, StartTime, EndTime, StepSize) VALUES({0}, {1}, {2}, {3})", contourQuery.ColorScaleName, contourQuery.GetStartDate(), contourQuery.GetEndDate(), contourQuery.StepSize);
animationID = connection.ExecuteScalar <int>("SELECT @@IDENTITY");
if (contourQuery.IncludeWeather)
{
timeZoneID = connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'XDATimeZone'");
}
}
GSF.Threading.CancellationToken cancellationToken = new GSF.Threading.CancellationToken();
s_cancellationTokens[animationID] = cancellationToken;
ProgressCounter progressCounter = new ProgressCounter(frames.Count);
s_progressCounters[animationID] = progressCounter;
Action <int> createFrame = i =>
{
List <TrendingDataLocation> frame = frames[i];
IDWFunc idwFunction = GetIDWFunction(contourQuery, frame);
uint[] pixelData;
if (contourQuery.IncludeWeather)
{
TimeZoneInfo tzInfo = !string.IsNullOrEmpty(timeZoneID)
? TimeZoneInfo.FindSystemTimeZoneById(timeZoneID)
: TimeZoneInfo.Local;
// Weather data is only available in 5-minute increments
DateTime frameTime = TimeZoneInfo.ConvertTimeToUtc(startDate.AddMinutes(stepSize * i), tzInfo);
double minutes = (frameTime - frameTime.Date).TotalMinutes;
int weatherMinutes = (int)Math.Ceiling(minutes / 5) * 5;
NameValueCollection queryString = HttpUtility.ParseQueryString(string.Empty);
queryString["service"] = "WMS";
queryString["request"] = "GetMap";
queryString["layers"] = "nexrad-n0r-wmst";
queryString["format"] = "image/png";
queryString["transparent"] = "true";
queryString["version"] = "1.1.1";
queryString["time"] = frameTime.Date.AddMinutes(weatherMinutes).ToString("o");
queryString["height"] = height.ToString();
queryString["width"] = width.ToString();
queryString["srs"] = "EPSG:3857";
GSF.Drawing.Point topLeftProjected = s_crs.Projection.Project(topLeft);
GSF.Drawing.Point bottomRightProjected = s_crs.Projection.Project(bottomRight);
queryString["bbox"] = string.Join(",", topLeftProjected.X, bottomRightProjected.Y, bottomRightProjected.X, topLeftProjected.Y);
string weatherURL = "http://mesonet.agron.iastate.edu/cgi-bin/wms/nexrad/n0r-t.cgi?" + queryString.ToString();
using (WebClient client = new WebClient())
using (MemoryStream stream = new MemoryStream(client.DownloadData(weatherURL)))
using (Bitmap bitmap = new Bitmap(stream))
{
pixelData = bitmap.ToPixelData();
}
}
else
{
pixelData = new uint[width * height];
}
if (cancellationToken.IsCancelled)
{
return;
}
for (int x = 0; x < width; x++)
{
if (cancellationToken.IsCancelled)
{
return;
}
for (int y = 0; y < height; y++)
{
if (cancellationToken.IsCancelled)
{
return;
}
if (pixelData[y * width + x] > 0)
{
continue;
}
GSF.Drawing.Point offsetPixel = new GSF.Drawing.Point(topLeftPoint.X + x, topLeftPoint.Y + y);
GeoCoordinate pixelCoordinate = s_crs.Translate(offsetPixel, contourQuery.Resolution);
double interpolatedValue = idwFunction(pixelCoordinate.Longitude, pixelCoordinate.Latitude);
pixelData[y * width + x] = (uint)colorFunc(interpolatedValue);
}
}
if (cancellationToken.IsCancelled)
{
return;
}
using (Bitmap bitmap = BitmapExtensions.FromPixelData(width, pixelData))
using (MemoryStream stream = new MemoryStream())
{
bitmap.Save(stream, ImageFormat.Png);
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
connection.ExecuteNonQuery("INSERT INTO ContourAnimationFrame VALUES({0}, {1}, {2})", animationID, i, stream.ToArray());
}
}
progressCounter.Increment();
};
Task.Run(() =>
{
ICancellationToken token;
ProgressCounter counter;
Parallel.For(0, frames.Count, createFrame);
s_cancellationTokens.TryRemove(animationID, out token);
s_progressCounters.TryRemove(animationID, out counter);
if (cancellationToken.IsCancelled)
{
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
connection.ExecuteNonQuery("DELETE FROM ContourAnimationFrame WHERE ContourAnimationID = {0}", animationID);
connection.ExecuteNonQuery("DELETE FROM ContourAnimation WHERE ID = {0}", animationID);
}
}
});
s_cleanUpAnimationOperation.TryRunOnceAsync();
return(new ContourAnimationInfo()
{
AnimationID = animationID,
ColorDomain = colorScale.Domain,
ColorRange = colorScale.Range,
MinLatitude = bottomRight.Latitude,
MaxLatitude = topLeft.Latitude,
MinLongitude = topLeft.Longitude,
MaxLongitude = bottomRight.Longitude,
Infos = frames.Select((frame, index) => new ContourInfo()
{
Locations = frame,
URL = string.Format("./mapService.asmx/getContourAnimationFrame?animation={0}&frame={1}", animationID, index),
Date = contourQuery.GetStartDate().AddMinutes(index * contourQuery.StepSize).ToString()
}).ToList()
});
}
public void getContourTile()
{
ContourQuery contourQuery = new ContourQuery()
{
StartDate = HttpContext.Current.Request.QueryString["StartDate"],
EndDate = HttpContext.Current.Request.QueryString["EndDate"],
ColorScaleName = HttpContext.Current.Request.QueryString["ColorScaleName"],
DataType = HttpContext.Current.Request.QueryString["DataType"],
UserName = HttpContext.Current.Request.QueryString["Username"],
Meters = HttpContext.Current.Request.QueryString["Meters"]
};
ContourTileData contourTileData = GetContourTileData(contourQuery);
double minLat = contourTileData.MinLatitude;
double maxLat = contourTileData.MaxLatitude;
double minLng = contourTileData.MinLongitude;
double maxLng = contourTileData.MaxLongitude;
CoordinateReferenceSystem crs = s_crs;
IDWFunc idwFunction = contourTileData.IDWFunction;
Func<double, double> colorFunction = contourTileData.ColorFunction;
int tileX = Convert.ToInt32(HttpContext.Current.Request.QueryString["x"]);
int tileY = Convert.ToInt32(HttpContext.Current.Request.QueryString["y"]);
int zoom = Convert.ToInt32(HttpContext.Current.Request.QueryString["zoom"]);
int tileSize = 256;
int offsetX = tileSize * tileX;
int offsetY = tileSize * tileY;
uint[] pixelData = new uint[tileSize * tileSize];
for (int x = 0; x < tileSize; x++)
{
GSF.Drawing.Point validationPixel = new GSF.Drawing.Point(offsetX + x, 0.0D);
GeoCoordinate validationCoordinate = crs.Translate(validationPixel, zoom);
if (validationCoordinate.Longitude < minLng || validationCoordinate.Longitude > maxLng)
continue;
for (int y = 0; y < tileSize; y++)
{
GSF.Drawing.Point offsetPixel = new GSF.Drawing.Point(offsetX + x, offsetY + y);
GeoCoordinate pixelCoordinate = crs.Translate(offsetPixel, zoom);
if (pixelCoordinate.Latitude < minLat || pixelCoordinate.Latitude > maxLat)
continue;
double interpolatedValue = idwFunction(pixelCoordinate.Longitude, pixelCoordinate.Latitude);
uint color = (uint)colorFunction(interpolatedValue);
pixelData[y * tileSize + x] = color;
}
}
using (Bitmap bitmap = BitmapExtensions.FromPixelData(256, pixelData))
{
HttpContext.Current.Response.ContentType = "image/png";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename=tile{0}x{1}.png", tileX, tileY));
bitmap.Save(HttpContext.Current.Response.OutputStream, ImageFormat.Png);
}
}
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));
}
}