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 string BuildHistoryPage(NameValueCollection parts, DevicePersistenceData data)
{
StringBuilder stb = new StringBuilder();
IncludeDataTableFiles(stb);
IncludeResourceScript(stb, "iframeSizer.min.js");
HSHelper hsHelper = new HSHelper(HS);
string header = Invariant($"History - {hsHelper.GetName(data.DeviceRefId)}");
stb.Append(FormStart("ftmDeviceHistory", "IdHistory", "Post"));
stb.Append(@"<div>");
stb.Append(@"<table class='full_width_table'>");
stb.Append(Invariant($"<tr><td class='tableheader'>{header}</td></tr>"));
var queries = GetDefaultValueQueries(data);
string querySelection = parts[QueryPartId];
if (string.IsNullOrWhiteSpace(querySelection))
{
querySelection = queries?.FirstOrDefault().Key;
}
NameValueCollection collection = new NameValueCollection();
foreach (var query in queries)
{
collection.Add(query.Key, query.Key);
}
stb.Append(Invariant($"<tr><td>{FormDropDown(HistoryQueryTypeId, collection, querySelection, 400, string.Empty, true)}</td></tr>"));
string finalQuery = Invariant(queries[querySelection]);
stb.Append(Invariant($"<tr height='10'><td>{HtmlTextBox(RecordId, data.Id.ToString(CultureInfo.InvariantCulture), @type: "hidden")}</td></tr>"));
stb.Append("<tr><td>");
stb.Append(DivStart(QueryTestDivId, string.Empty));
stb.Append(Invariant($"{TextArea(QueryTestId, finalQuery)}"));
stb.Append(DivEnd());
stb.Append(Invariant($"<br>{FormButton(HistoryRunQueryButtonName, "Run Query", "Run Query")}"));
stb.Append(" ");
stb.Append(Invariant($"{FormButton(HistoryShowChartButtonName, "Show Chart", "Show Chart")}</td></tr>"));
stb.Append("<tr height='5'><td></td></tr>");
stb.Append(Invariant($"<tr><td class='tableheader'>Results</td></tr>"));
stb.Append("<tr><td>");
stb.Append(DivStart(HistoryResultDivId, string.Empty));
BuildQueryTableIFrame(stb, finalQuery);
stb.Append(DivEnd());
stb.Append("</td><tr>");
stb.Append("<tr height='5'><td></td></tr>");
stb.Append(Invariant($"<tr><td>{HistoryBackButton()}</td></tr>"));
stb.Append("</table>");
stb.Append("</div>");
stb.Append(FormEnd());
return(stb.ToString());
}
public static List <string> GetFields(DevicePersistenceData data)
{
List <string> fields = new List <string>();
if (!string.IsNullOrWhiteSpace(data.Field))
{
fields.Add(Invariant($"\"{data.Field}\""));
}
else if (!string.IsNullOrWhiteSpace(data.FieldString))
{
fields.Add(Invariant($"\"{data.FieldString}\""));
}
return(fields);
}
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());
}
private void HandleSavingPersistencePostBack(NameValueCollection parts, string form)
{
if (form == NameToIdWithPrefix(DeletePersistenceSave))
{
this.pluginConfig.RemoveDevicePersistenceData(parts[RecordId]);
this.pluginConfig.FireConfigChanged();
this.divToUpdate.Add(SaveErrorDivId, RedirectPage(Invariant($"/{pageUrl}?{TabId}=1")));
}
else if (form == NameToIdWithPrefix(EditPersistenceCancel))
{
this.pluginConfig.RemoveDevicePersistenceData(parts[RecordId]);
this.pluginConfig.FireConfigChanged();
this.divToUpdate.Add(SaveErrorDivId, RedirectPage(Invariant($"/{pageUrl}?{TabId}=1")));
}
else if ((form == NameToIdWithPrefix(EditPersistenceSave)) ||
(form == NameToIdWithPrefix(FillDefaultValuesButtonName)))
{
StringBuilder results = new StringBuilder();
string deviceId = parts[DeviceRefIdId];
if (!int.TryParse(deviceId, out int deviceRefId))
{
results.AppendLine("Device is not valid.<br>");
}
if (form == NameToIdWithPrefix(FillDefaultValuesButtonName))
{
if (results.Length == 0)
{
HSHelper hSHelper = new HSHelper(HS);
hSHelper.Fill(deviceRefId, out var typeString, out var maxValidValue, out var minValidValue);
divToUpdate.Add(MeasurementDivId, HtmlTextBox(MeasurementId, typeString ?? string.Empty));
if (!string.IsNullOrEmpty(typeString))
{
divToUpdate.Add(FieldDivId, HtmlTextBox(FieldId, PluginConfig.DefaultFieldValueString));
divToUpdate.Add(MaxValidValueDivId, HtmlTextBox(MaxValidValueId, maxValidValue?.ToString(CultureInfo.InvariantCulture) ?? string.Empty));
divToUpdate.Add(MinValidValueDivId, HtmlTextBox(MinValidValueId, minValidValue?.ToString(CultureInfo.InvariantCulture) ?? string.Empty));
}
}
}
else
{
string measurement = parts[MeasurementId];
if (string.IsNullOrWhiteSpace(measurement))
{
results.AppendLine("Measurement is not valid.<br>");
}
string field = parts[FieldId];
string fieldString = parts[FieldStringId];
if (string.IsNullOrWhiteSpace(field) && string.IsNullOrWhiteSpace(fieldString))
{
results.AppendLine("Both Field and FieldString are not valid. One of them need to valid.<br>");
}
var trackedTypeString = parts[TrackedTypeId];
TrackedType?trackingType = null;
if (Enum.TryParse <TrackedType>(trackedTypeString, out var trackingTypeTemp))
{
trackingType = trackingTypeTemp;
}
else
{
results.AppendLine("Record on value change not valid");
}
string tagsString = parts[TagsId];
var tagsList = tagsString.Split(new string[] { "\r\n", "\n", "\r" }, StringSplitOptions.None);
var tags = new Dictionary <string, string>();
foreach (var tagString in tagsList)
{
if (string.IsNullOrWhiteSpace(tagString))
{
continue;
}
var pair = tagString.Split('=');
if (pair.Length != 2)
{
results.AppendLine(Invariant($"Unknown tag type: {tagString}. Format tagType= value<br>"));
}
else
{
tags.Add(pair[0], pair[1]);
}
}
string maxValidValueString = parts[MaxValidValueId];
string minValidValueString = parts[MinValidValueId];
double?maxValidValue = null;
double?minValidValue = null;
if (!string.IsNullOrEmpty(maxValidValueString))
{
if (double.TryParse(parts[MaxValidValueId], out var value))
{
maxValidValue = value;
}
else
{
results.AppendLine("Max valid value is not valid.<br>");
}
}
if (!string.IsNullOrEmpty(minValidValueString))
{
if (double.TryParse(parts[MinValidValueId], out var value))
{
minValidValue = value;
}
else
{
results.AppendLine("Min valid value is not valid.<br>");
}
}
if (maxValidValue.HasValue && minValidValue.HasValue)
{
if ((maxValidValue.Value - minValidValue.Value) <= 0)
{
results.AppendLine("Max and Min valid values are not valid.<br>");
}
}
if ((maxValidValue.HasValue || minValidValue.HasValue) && string.IsNullOrWhiteSpace(field))
{
results.AppendLine("Max and Min valid values don't mean anything without field to store them.<br>");
}
if (results.Length > 0)
{
this.divToUpdate.Add(SaveErrorDivId, results.ToString());
}
else
{
string persistenceId = parts[RecordId];
if (string.IsNullOrWhiteSpace(persistenceId))
{
persistenceId = System.Guid.NewGuid().ToString();
}
var persistenceData = new DevicePersistenceData(persistenceId, deviceRefId, measurement, field, fieldString, tags, maxValidValue, minValidValue, trackingType);
this.pluginConfig.AddDevicePersistenceData(persistenceData);
this.pluginConfig.FireConfigChanged();
this.divToUpdate.Add(SaveErrorDivId, RedirectPage(Invariant($"/{pageUrl}?{TabId}=1")));
}
}
}
}
private string BuildAddNewPersistenceWebPageBody([AllowNull] DevicePersistenceData data)
{
HSHelper hsHelper = new HSHelper(HS);
var devices = hsHelper.GetDevices();
var devicesSorted = devices.OrderBy(x => x.Value);
var options = new Dictionary <int, string>();
foreach (var device in devicesSorted)
{
options.Add(device.Key, device.Value);
}
int deviceRefId = data != null ? data.DeviceRefId : -1;
string measurement = data != null ? data.Measurement : string.Empty;
string field = data?.Field ?? string.Empty;
string fieldString = data?.FieldString ?? string.Empty;
string maxValidValue = data?.MaxValidValue?.ToString(CultureInfo.InvariantCulture) ?? string.Empty;
string minValidValue = data?.MinValidValue?.ToString(CultureInfo.InvariantCulture) ?? string.Empty;
var trackedType = data?.TrackedType ?? TrackedType.Value;
string tags = string.Empty;
string id = data != null ? data.Id : string.Empty;
string buttonLabel = data != null ? "Save" : "Add";
string header = data != null ? "Edit Persistence" : "Add New DB Persistence";
if (data != null && data.Tags != null)
{
foreach (var tag in data.Tags)
{
tags += Invariant($"{tag.Key}={tag.Value}{Environment.NewLine}");
}
}
StringBuilder stb = new StringBuilder();
IncludeResourceCSS(stb, "chosen.css");
IncludeResourceScript(stb, "chosen.jquery.min.js");
stb.Append(PageBuilderAndMenu.clsPageBuilder.FormStart("ftmDeviceChange", "IdChange", "Post"));
stb.Append(@"<div>");
stb.Append(@"<table class='full_width_table'>");
stb.Append("<tr height='5'><td></td><td></td></tr>");
stb.Append(Invariant($"<tr><td class='tableheader' colspan=2>{header}</td></tr>"));
stb.Append(Invariant($"<tr><td class='tablecell'>Name:</td><td class='tablecell'>"));
stb.Append(FormDropDownChosen(DeviceRefIdId, options, deviceRefId));
stb.Append(Invariant($" "));
stb.Append(FormButton(FillDefaultValuesButtonName, "Fill Default Values", "Fill default values"));
stb.Append(Invariant($"</td></tr>"));
stb.Append(Invariant($"<tr><td class='tablecell'>Measurement:</td><td class='tablecell'>"));
stb.Append(DivStart(MeasurementDivId, string.Empty));
stb.Append(HtmlTextBox(MeasurementId, measurement));
stb.Append(DivEnd());
stb.Append("</td></tr>");
stb.Append(Invariant($"<tr><td class='tablecell'>Field for value:</td><td class='tablecell'>"));
stb.Append(DivStart(FieldDivId, string.Empty));
stb.Append(HtmlTextBox(FieldId, field));
stb.Append(DivEnd());
stb.Append("</td></tr>");
stb.Append(Invariant($"<tr><td class='tablecell'>Max valid value:</td><td class='tablecell'>"));
stb.Append(DivStart(MaxValidValueDivId, string.Empty));
stb.Append(HtmlTextBox(MaxValidValueId, maxValidValue));
stb.Append(DivEnd());
stb.Append("</td></tr>");
stb.Append(Invariant($"<tr><td class='tablecell'>Min valid value:</td><td class='tablecell'>"));
stb.Append(DivStart(MinValidValueDivId, string.Empty));
stb.Append(HtmlTextBox(MinValidValueId, minValidValue));
stb.Append(DivEnd());
stb.Append("</td></tr>");
stb.Append(Invariant($"<tr><td class='tablecell'>Field for string value:</td><td class='tablecell'>{HtmlTextBox(FieldStringId, fieldString)}</td></tr>"));
NameValueCollection trackedTypeCollection = CreateNameValueCreation <TrackedType>();
stb.Append(Invariant($"<tr><td class='tablecell'>Record On Change of:</td><td class='tablecell'>{FormDropDown(TrackedTypeId, trackedTypeCollection, trackedType.ToString(), 100, string.Empty, false)}</td></tr>"));
stb.Append(Invariant($"<tr><td class='tablecell'>Tags:</td><td class='tablecell'><p><small>Name and locations are automatically added as tags.</small></p>{TextArea(TagsId, tags, cols: 35)}</td></tr>"));
stb.Append(Invariant($"<tr><td colspan=2>{HtmlTextBox(RecordId, id, type: "hidden")}<div id='{SaveErrorDivId}' style='color:Red'></div></td><td></td></tr>"));
stb.Append(Invariant($"<tr><td colspan=2>{FormPageButton(EditPersistenceSave, buttonLabel)}"));
if (data != null)
{
stb.Append(FormPageButton(DeletePersistenceSave, "Delete"));
}
stb.Append(FormPageButton(EditPersistenceCancel, "Cancel"));
stb.Append(Invariant($"</td></tr>"));
stb.Append("<tr height='5'><td colspan=2></td></tr>");
stb.Append(@"</table>");
stb.Append(@"</div>");
stb.Append(PageBuilderAndMenu.clsPageBuilder.FormEnd());
return(stb.ToString());
}
private static IDictionary <string, FormattableString> GetDefaultValueQueries(DevicePersistenceData data)
{
List <string> fields = InfluxDbQueryBuilder.GetFields(data);
var queries = new Dictionary <string, FormattableString>()
{
{
"Last 100 stored values",
$"SELECT {string.Join(",", fields)} from \"{data.Measurement}\" WHERE {PluginConfig.DeviceRefIdTag}='{data.DeviceRefId}' ORDER BY time DESC LIMIT 100"
},
};
if (!string.IsNullOrWhiteSpace(data.Field))
{
var standardFields = Invariant($"MIN(\"{data.Field}\"), MAX(\"{data.Field}\"), MEAN(\"{data.Field}\"), MEDIAN(\"{data.Field}\"), PERCENTILE(\"{data.Field}\", 95) AS \"95 percentile\"");
var subQuery24h = Invariant($"SELECT MEAN(\"{data.Field}\") as \"{data.Field}\" FROM \"{data.Measurement}\" WHERE {PluginConfig.DeviceRefIdTag}='{data.DeviceRefId}' AND time > now() - 24h GROUP BY time(1s) fill(previous)");
queries.Add(
"Min/Max Values",
$"SELECT MIN(\"{data.Field}\"), MAX(\"{data.Field}\") from \"{data.Measurement}\" WHERE {PluginConfig.DeviceRefIdTag}='{data.DeviceRefId}'"
);
queries.Add(
"Min/Max/Average/Medium/Percentile Values(24h)",
$"SELECT {standardFields} FROM ({subQuery24h})"
);
queries.Add(
"Min/Max/Average/Medium/Percentile Values By Hour(24h)",
$"SELECT {standardFields} FROM ({subQuery24h}) GROUP BY time(1h)"
);
}
return(queries);
}
