private static string GetInfluxDBDuration(QueryDuration duration)
{
switch (duration)
{
case QueryDuration.D1h: return("1h");
case QueryDuration.D6h: return("6h");
case QueryDuration.D12h: return("12h");
case QueryDuration.D24h: return("24h");
case QueryDuration.D7d: return("7d");
case QueryDuration.D30d: return("30d");
case QueryDuration.D60d: return("60d");
case QueryDuration.D180d: return("180d");
case QueryDuration.D365d: return("365d");
default:
throw new ArgumentOutOfRangeException(nameof(duration));
}
}
public static async Task <string> GetStatsQuery(DevicePersistenceData data,
QueryDuration queryDuration,
InfluxDBLoginInformation loginInformation,
TimeSpan?groupInterval)
{
groupInterval = groupInterval ?? GetDefaultInfluxDBGroupInterval(queryDuration);
string subquery = await CreateRegularTimeSeries(data, queryDuration,
loginInformation, groupInterval.Value).ConfigureAwait(false);
StringBuilder stb = new StringBuilder();
stb.Append("SELECT ");
stb.Append(Invariant($"MIN(\"{data.Field}\")"));
stb.Append(Invariant($",MAX(\"{data.Field}\")"));
stb.Append(Invariant($",MEAN(\"{data.Field}\")"));
stb.Append(Invariant($",MEDIAN(\"{data.Field}\")"));
stb.Append(Invariant($",MODE(\"{data.Field}\")"));
stb.Append(Invariant($",PERCENTILE(\"{data.Field}\", 95) as \"95 Percentile\""));
stb.Append(Invariant($",STDDEV(\"{data.Field}\") as \"Standard Deviation\""));
stb.AppendFormat(CultureInfo.InvariantCulture, "FROM (SELECT * FROM ({0}) WHERE time >= now() - {1})", subquery, GetInfluxDBDuration(queryDuration));
stb.Append(" LIMIT 100000");
return(stb.ToString());
}
private static TimeSpan GetDefaultInfluxDBGroupInterval(QueryDuration duration)
{
switch (duration)
{
case QueryDuration.D1h: return(TimeSpan.FromSeconds(1));
case QueryDuration.D6h: return(TimeSpan.FromSeconds(5));
case QueryDuration.D12h: return(TimeSpan.FromSeconds(15));
case QueryDuration.D24h: return(TimeSpan.FromMinutes(1));
case QueryDuration.D7d: return(TimeSpan.FromMinutes(5));
case QueryDuration.D30d: return(TimeSpan.FromMinutes(30));
case QueryDuration.D60d: return(TimeSpan.FromHours(1));
case QueryDuration.D180d: return(TimeSpan.FromHours(12));
case QueryDuration.D365d: return(TimeSpan.FromHours(24));
default:
throw new ArgumentOutOfRangeException(nameof(duration));
}
}
public static TimeSpan GetTimeSpan(QueryDuration duration)
{
switch (duration)
{
case QueryDuration.D1h: return(TimeSpan.FromHours(1));
case QueryDuration.D6h: return(TimeSpan.FromHours(6));
case QueryDuration.D12h: return(TimeSpan.FromHours(12));
case QueryDuration.D24h: return(TimeSpan.FromHours(24));
case QueryDuration.D7d: return(TimeSpan.FromHours(24 * 7));
case QueryDuration.D30d: return(TimeSpan.FromHours(30 * 24));
case QueryDuration.D60d: return(TimeSpan.FromHours(60 * 24));
case QueryDuration.D180d: return(TimeSpan.FromHours(180 * 24));
case QueryDuration.D365d: return(TimeSpan.FromHours(365 * 24));
default:
throw new ArgumentOutOfRangeException(nameof(duration));
}
}
private static string BuildHistogramUri(string finalQuery, QueryDuration queryDuration)
{
return(BuildUri(pageUrl, new NameValueCollection()
{
{ PageTypeId, DeviceHistogramPageType },
{ QueryPartId, finalQuery },
{ QueryDurationId, queryDuration.ToString() },
}));
}
public static async Task <string> GetDeviceHistogramTabQuery(DevicePersistenceData data,
QueryDuration queryDuration,
InfluxDBLoginInformation loginInformation)
{
string duration = GetInfluxDBDuration(queryDuration);
// Find last element before duration
string query = Invariant($"SELECT last(*) from \"{data.Measurement}\" WHERE \"{PluginConfig.DeviceRefIdTag}\" = '{data.DeviceRefId}' and time < now() - {duration} order by time asc");
var time = await InfluxDBHelper.GetTimeValueForQuery(query, loginInformation).ConfigureAwait(false);
string timeRestriction = time.HasValue ? Invariant($"time >= {new DateTimeOffset(time.Value).ToUnixTimeSeconds()}s") : Invariant($"time >= now() - {duration}");
return(Invariant($"SELECT {GetFields(data)[0]} FROM \"{data.Measurement}\" WHERE \"{PluginConfig.DeviceRefIdTag}\" = '{data.DeviceRefId}' AND {timeRestriction} ORDER BY time ASC"));
}
private static async Task <string> CreateRegularTimeSeries(DevicePersistenceData data,
QueryDuration queryDuration,
InfluxDBLoginInformation loginInformation,
TimeSpan groupByInterval,
bool fileLinear = false)
{
string duration = GetInfluxDBDuration(queryDuration);
// Find last element before duration
string query = Invariant($"SELECT last(*) from \"{data.Measurement}\" WHERE \"{PluginConfig.DeviceRefIdTag}\" = '{data.DeviceRefId}' and time < now() - {duration} order by time asc");
var time = await InfluxDBHelper.GetTimeValueForQuery(query, loginInformation).ConfigureAwait(false);
string timeRestriction = time.HasValue ? Invariant($"time >= {new DateTimeOffset(time.Value).ToUnixTimeSeconds()}s") : Invariant($"time >= now() - {duration}");
string fillOption = fileLinear ? "linear" : "previous";
return(Invariant($"SELECT MEAN(\"{data.Field}\") as \"{data.Field}\" from \"{data.Measurement}\" WHERE \"{PluginConfig.DeviceRefIdTag}\" = '{data.DeviceRefId}' and {timeRestriction} GROUP BY time({(int)groupByInterval.TotalSeconds}s) fill({fillOption})"));
}
private string GetQueryResultFrame(DevicePersistenceData data, IFrameType frameType,
QueryDuration duration, IFrameGrouping grouping)
{
StringBuilder stb = new StringBuilder();
string iFrameUrl = null;
HSHelper hSHelper = new HSHelper(HS);
string deviceName = hSHelper.GetName(data.DeviceRefId);
switch (frameType)
{
case IFrameType.TableHistory:
var query = InfluxDbQueryBuilder.GetDeviceHistoryTabQuery(data, deviceName, duration);
iFrameUrl = BuildTableUri(query, 10);
break;
case IFrameType.ChartHistory:
var chartQuery = InfluxDbQueryBuilder.GetGroupedDeviceHistoryTabQuery(data, deviceName, duration,
pluginConfig.DBLoginInformation,
GetTimeSpan(grouping)).ResultForSync();
iFrameUrl = BuildChartUri(chartQuery, string.Empty);
break;
case IFrameType.AverageStats:
var statsQuery = InfluxDbQueryBuilder.GetStatsQuery(data, duration,
pluginConfig.DBLoginInformation,
GetTimeSpan(grouping)).ResultForSync();
iFrameUrl = BuildStatsUri(statsQuery);
break;
case IFrameType.Histogram:
var histogramQuery = InfluxDbQueryBuilder.GetDeviceHistogramTabQuery(data, duration, pluginConfig.DBLoginInformation).ResultForSync();
iFrameUrl = BuildHistogramUri(histogramQuery, duration);
break;
default:
throw new ArgumentOutOfRangeException(nameof(frameType));
}
stb.Append(@"<style>iframe{width: 1px;min-width: 100%;border: none; width: 100%; height: 475px}</style>");
stb.Append(Invariant($"<iframe id=\"tableFrame\" src=\"{iFrameUrl}\" scrolling=\"no\"></iframe>"));
stb.Append(Invariant($"<script>iFrameResize({{log:false}}, '#{TableFrameId}')</script>"));
return(stb.ToString());
}
public static async Task <string> GetGroupedDeviceHistoryTabQuery(DevicePersistenceData data,
string deviceName,
QueryDuration queryDuration,
InfluxDBLoginInformation loginInformation,
TimeSpan?groupInterval)
{
groupInterval = groupInterval ?? GetDefaultInfluxDBGroupInterval(queryDuration);
string subquery = await CreateRegularTimeSeries(data, queryDuration,
loginInformation, groupInterval.Value).ConfigureAwait(false);
StringBuilder stb = new StringBuilder();
stb.Append("SELECT ");
stb.Append(Invariant($"\"{data.Field}\" as \"{deviceName}\""));
stb.AppendFormat(CultureInfo.InvariantCulture, "FROM (SELECT * FROM ({0}) WHERE time >= now() - {1})", subquery, GetInfluxDBDuration(queryDuration));
return(stb.ToString());
}
public static string GetDeviceHistoryTabQuery(DevicePersistenceData data,
string deviceName,
QueryDuration queryDuration)
{
StringBuilder stb = new StringBuilder();
stb.Append("SELECT ");
stb.Append(GetFields(data)[0]);
stb.Append(" AS \"");
stb.Append(deviceName);
stb.Append("\" from \"");
stb.Append(data.Measurement);
stb.Append("\" WHERE ");
stb.Append(PluginConfig.DeviceRefIdTag);
stb.Append("='");
stb.AppendFormat(CultureInfo.InvariantCulture, "{0}", data.DeviceRefId);
stb.Append("'");
stb.AppendFormat(CultureInfo.InvariantCulture, " AND time > now() - {0} ORDER BY time DESC", GetInfluxDBDuration(queryDuration));
return(stb.ToString());
}
public string GetDeviceHistoryTab(int refId)
{
var dataKeyPair = pluginConfig.DevicePersistenceData.FirstOrDefault(x => x.Value.DeviceRefId == refId);
var data = dataKeyPair.Value;
if ((data != null) && !string.IsNullOrWhiteSpace(pluginConfig.DBLoginInformation.DB))
{
bool hasNumericData = !string.IsNullOrWhiteSpace(data.Field);
IFrameType DefaultFrameType = hasNumericData ? IFrameType.ChartHistory : IFrameType.TableHistory;
QueryDuration DefaultDuration = QueryDuration.D6h;
IFrameGrouping DefaultGrouping = IFrameGrouping.Auto;
StringBuilder stb = new StringBuilder();
IncludeDataTableFiles(stb);
IncludeResourceScript(stb, "iframeSizer.min.js");
stb.Append(@"<table style='width:100%;border-spacing:0px;'");
stb.Append("<tr height='5'><td></td></tr>");
stb.Append("<tr><td>");
stb.Append("Type:");
NameValueCollection iframeType = new NameValueCollection();
AddEnumValue(iframeType, IFrameType.TableHistory);
if (hasNumericData)
{
AddEnumValue(iframeType, IFrameType.ChartHistory);
AddEnumValue(iframeType, IFrameType.AverageStats);
}
stb.Append(FormDropDown(IFrameTypeId, iframeType, DefaultFrameType.ToString(),
150, string.Empty, true, DeviceUtiltyPageName));
stb.Append(" Duration:");
NameValueCollection duration = CreateNameValueCreation <QueryDuration>();
stb.Append(FormDropDown(IFrameDurationId, duration, DefaultDuration.ToString(),
100, string.Empty, true, DeviceUtiltyPageName));
stb.Append(" Grouping:");
NameValueCollection grouping = CreateNameValueCreation <IFrameGrouping>();
stb.Append(FormDropDown(IFrameGroupingId, grouping, DefaultGrouping.ToString(),
100, string.Empty, true, DeviceUtiltyPageName, hasNumericData));
stb.Append("</td></tr>");
stb.Append("<tr height='5'><td></td></tr>");
stb.Append("<tr><td class='tablecell'>");
stb.Append(DivStart(resultsDivPartId, string.Empty));
stb.Append(GetQueryResultFrame(data, DefaultFrameType, DefaultDuration, DefaultGrouping));
stb.Append(DivEnd());
stb.Append("</td></tr>");
stb.Append("<tr height='5'><td></td></tr>");
stb.Append("<tr><td>");
stb.Append(PageTypeButton(Invariant($"Edit{data.Id}"), "Edit", EditDevicePageType, id: data.Id));
stb.Append(" ");
stb.Append(PageTypeButton(Invariant($"Queries{data.Id}"), "More Queries", HistoryDevicePageType, id: data.Id));
stb.Append(" ");
stb.Append(FormButton(DoneButtonId, "Done", string.Empty, DeviceUtiltyPageName));
stb.Append("</td></tr>");
stb.Append("</table>");
return(stb.ToString());
}
return(string.Empty);
}
private string BuildHistogramPage(NameValueCollection parts)
{
StringBuilder stb = new StringBuilder();
var query = parts[QueryPartId] ?? string.Empty;
var duration = parts[QueryDurationId] ?? string.Empty;
QueryDuration queryDuration = QueryDuration.D1h;
_ = Enum.TryParse <QueryDuration>(duration, out queryDuration);
IncludeDataTableFiles(stb);
IncludeResourceScript(stb, "iframeResizer.contentWindow.min.js");
try
{
var culture = CultureInfo.CurrentUICulture;
TimeSpan durationTimeSpan = InfluxDbQueryBuilder.GetTimeSpan(queryDuration);
var queryData = GetData(HttpUtility.UrlDecode(query));
var histogram = InfluxDBHelper.CreateHistogram(queryData, durationTimeSpan);
if (histogram.Count > 0)
{
//Display the first row/column only
stb.Append("<table id=\"results\" class=\"cell-border compact\" style=\"width:100%\">");
stb.Append(@"<thead align='left'><tr>");
stb.Append(@"<th>Value</th>");
stb.Append(@"<th>Total time</th>");
stb.Append(@"<th>Percentage</th>");
stb.Append(@"</tr></thead>");
stb.Append(@"<tbody>");
var firstRow = queryData[0];
foreach (var pair in histogram)
{
stb.Append(@"<tr class='tablecell'>");
stb.Append(@"<td>");
stb.Append(HtmlEncode(FirstCharToUpper(pair.Key, culture)));
stb.Append(@"</td>");
stb.Append(@"<td>");
stb.Append(HtmlEncode(InfluxDBHelper.GetSerieValue(culture, pair.Value)));
stb.Append(@"</td>");
stb.Append(@"<td>");
double percentage = 100 * pair.Value.TotalMilliseconds / durationTimeSpan.TotalMilliseconds;
stb.Append(HtmlEncode(InfluxDBHelper.GetSerieValue(culture, percentage)));
stb.Append(@"</td>");
stb.Append(@"</tr>");
}
stb.Append(@"</tbody>");
stb.AppendLine(@"</table>");
}
return(stb.ToString());
}
catch (Exception ex)
{
return(Invariant($"<br><div style='color:Red'>{ex.GetFullMessage()}</div><br>"));
}
}
