public IActionResult GenericQuery(GenericTimescaleQueryModel model)
{
Stopwatch sw = new Stopwatch();
sw.Start();
var conn = new NpgsqlConnection(DbContext.Database.GetDbConnection().ConnectionString);
conn.Open();
using (var cmd = conn.CreateCommand())
{
//auto bucket size if custom range
if (model.Range == QueryRange.CUSTOM)
{
TimeSpan range = model.End.Value - model.Start.Value;
if (range.TotalDays > 365 * 2)
{
model.BucketType = BucketType.Week;
model.BucketSize = 1;
model.Limit = -1;
}
else if (range.TotalDays > 365)
{
model.BucketType = BucketType.Day;
model.BucketSize = 1;
model.Limit = -1;
}
else if (range.TotalDays > 7)
{
model.BucketType = BucketType.Hour;
model.BucketSize = 5;
model.Limit = -1;
}
else if (range.TotalDays > 1)
{
model.BucketType = BucketType.Hour;
model.BucketSize = 1;
model.Limit = -1;
}
else if (range.TotalHours > 1)
{
model.BucketType = BucketType.Minute;
model.BucketSize = 5;
model.Limit = -1;
}
else if (range.TotalMinutes < 60)
{
model.BucketType = BucketType.Second;
model.BucketSize = 5;
model.Limit = -1;
}
}
//bucketSize
var bucketSizeString = string.Format("{0} {1}", model.BucketSize, model.BucketType.ToString().ToLowerInvariant());
//fields
var valueFields = new List <string>();
foreach (var field in model.Metrics)
{
if (model.Range == QueryRange.LIVE)
{
valueFields.Add($"{field.Metric} AS {field.Metric}");
}
else
{
valueFields.Add($"{field.Aggregation.ToString()}({field.Metric}) AS {field.Metric}");
}
}
var valueFieldsString = string.Join(", ", valueFields);
//where clause (date range)
bool success = QueryRangeHelper.GetDatesOfRange(model.Range, model.Start, model.End, out DateTime dtUTCStart, out DateTime dtUTCEnd);
string limitString = model.Limit > 0 ? "LIMIT @limit" : "";
if (model.Range == QueryRange.LIVE)
{
//real records, no time_buckets
cmd.CommandText = string.Format($"SELECT time AS ke, {valueFieldsString} FROM soundusage WHERE time >= @start AND time <= @end ORDER BY ke DESC {limitString};");
}
else
{
cmd.CommandText = string.Format($"SELECT time_bucket_gapfill('{bucketSizeString}', time, '{dtUTCStart.ToString("yyyy-MM-dd")}', '{dtUTCEnd.ToString("yyyy-MM-dd")}') AS ke, {valueFieldsString} FROM soundusage WHERE time >= @start AND time <= @end GROUP BY ke ORDER BY ke DESC {limitString};");
}
cmd.CommandType = System.Data.CommandType.Text;
if (model.Limit > 0)
{
cmd.Parameters.AddWithValue("@limit", model.Limit);
}
cmd.Parameters.AddWithValue("@start", dtUTCStart);
cmd.Parameters.AddWithValue("@end", dtUTCEnd);
List <GenericQueryRecordViewModel> list = new List <GenericQueryRecordViewModel>();
using (var result = cmd.ExecuteReader())
{
while (result.Read())
{
List <dynamic> values = new List <dynamic>();
foreach (var metric in model.Metrics)
{
if (result[result.GetOrdinal(metric.Metric)] == DBNull.Value)
{
values.Add(null);
}
else
{
values.Add(result.GetDouble(result.GetOrdinal(metric.Metric)));
}
}
list.Add(new GenericQueryRecordViewModel()
{
K = result.GetDateTime(result.GetOrdinal("ke")),
Value = values
});
}
}
conn.Close();
//reverse list
list = list.OrderBy(a => a.K).ToList();
sw.Stop();
return(Ok(new
{
Series = model.Metrics.Select(s => s.Metric).ToArray(),
Values = list,
Duration = sw.Elapsed.TotalMilliseconds
}));
}
}