public static int GetSteps(this QboxQueryResolution resolution, DateTime from, DateTime to)
{
switch (resolution)
{
case QboxQueryResolution.FiveMinutes:
return(24 * 12);
case QboxQueryResolution.QuarterOfHour:
return(24 * 4);
case QboxQueryResolution.Hour:
return(24);
case QboxQueryResolution.Day:
return((to - from).Days);
case QboxQueryResolution.Month:
return((to.Year - from.Year) * 12 + to.Month - from.Month);
case QboxQueryResolution.Year:
return(to.Year - from.Year);
default:
throw new NotSupportedException();
}
}
public static DateTime TruncateTime(this QboxQueryResolution resolution, DateTime measureTime)
{
switch (resolution)
{
case QboxQueryResolution.FiveMinutes:
return(measureTime.Truncate(TimeSpan.FromMinutes(5)));
case QboxQueryResolution.QuarterOfHour:
return(measureTime.Truncate(TimeSpan.FromMinutes(15)));
case QboxQueryResolution.Hour:
return(new DateTime(measureTime.Year, measureTime.Month, measureTime.Day, measureTime.Hour, 0, 0, measureTime.Kind));
case QboxQueryResolution.Day:
return(new DateTime(measureTime.Year, measureTime.Month, measureTime.Day, 0, 0, 0, measureTime.Kind));
case QboxQueryResolution.Month:
return(new DateTime(measureTime.Year, measureTime.Month, 1, 0, 0, 0, measureTime.Kind));
case QboxQueryResolution.Year:
return(new DateTime(measureTime.Year, 1, 1, 0, 0, 0, measureTime.Kind));
default:
throw new NotSupportedException();
}
}
private static QboxDataQuery GetDrillDownQboxDataQuery(DateTime measureTime, QboxQueryResolution resolution, bool adjustHours)
{
QboxQueryResolution resolutionNew = resolution - 1;
var query = new QboxDataQuery
{
AdjustHours = adjustHours,
Resolution = resolutionNew,
From = resolution.TruncateTime(measureTime)
};
switch (resolution)
{
case QboxQueryResolution.QuarterOfHour:
case QboxQueryResolution.Hour:
case QboxQueryResolution.Day:
query.To = query.From.AddDays(1);
break;
case QboxQueryResolution.Month:
query.To = query.From.AddMonths(1);
break;
case QboxQueryResolution.Year:
query.To = query.From.AddYears(1);
break;
default:
throw new NotSupportedException();
}
return(query);
}
private static List <QboxCounterData> FillGaps(DateTime from, DateTime to, QboxQueryResolution resolution, List <QboxCounterData> itemsFound)
{
int steps = resolution.GetSteps(from, to);
var items = new List <QboxCounterData>();
for (int i = 0; i < steps; i++)
{
double delta = 1.0 * i / steps * (to - from).TotalMinutes;
var measureTime = from.AddMinutes(delta);
var measureTimeRounded = resolution.TruncateTime(measureTime);
string labelText = resolution.GetLabelText(measureTimeRounded);
var existing = itemsFound.FirstOrDefault(it => it.LabelText == labelText);
if (existing != null)
{
items.Add(existing);
}
else
{
items.Add(new QboxCounterData
{
MeasureTime = measureTimeRounded,
LabelText = labelText,
LabelValue = resolution.GetLabelValue(measureTimeRounded)
});
}
}
return(items.OrderBy(e => e.MeasureTime).ToList());
}
public static (DateTime start, DateTime end) GetTruncatedTimeFrame(this QboxQueryResolution resolution, DateTime from, DateTime to)
{
switch (resolution)
{
case QboxQueryResolution.FiveMinutes:
case QboxQueryResolution.QuarterOfHour:
case QboxQueryResolution.Hour:
case QboxQueryResolution.Day:
return(new DateTime(from.Year, from.Month, from.Day, 0, 0, 0, from.Kind), new DateTime(to.Year, to.Month, to.Day, 0, 0, 0, to.Kind));
case QboxQueryResolution.Month:
case QboxQueryResolution.Year:
return(new DateTime(from.Year, from.Month, 1, 0, 0, 0, from.Kind), new DateTime(to.Year, to.Month, 1, 0, 0, 0, to.Kind));
default:
throw new NotSupportedException();
}
}
public static int GetLabelValue(this QboxQueryResolution resolution, DateTime measureTime)
{
switch (resolution)
{
case QboxQueryResolution.FiveMinutes:
case QboxQueryResolution.QuarterOfHour:
case QboxQueryResolution.Hour:
return(measureTime.Hour);
case QboxQueryResolution.Day:
return(measureTime.Day);
case QboxQueryResolution.Month:
return(measureTime.Month);
case QboxQueryResolution.Year:
return(measureTime.Year);
default:
throw new NotSupportedException();
}
}
public static string GetLabelText(this QboxQueryResolution resolution, DateTime measureTime)
{
switch (resolution)
{
case QboxQueryResolution.FiveMinutes:
case QboxQueryResolution.QuarterOfHour:
return(measureTime.ToString("HH:mm"));
case QboxQueryResolution.Hour:
return(measureTime.ToString("HH u"));
case QboxQueryResolution.Day:
return(measureTime.Day.ToString());
case QboxQueryResolution.Month:
return(measureTime.ToString("MMM yy", new CultureInfo("nl-NL")));
case QboxQueryResolution.Year:
return(measureTime.ToString("yyyy"));
default:
throw new NotSupportedException();
}
}
/// <inheritdoc cref="IAzureTablesService.QueryDataAsync(string, DateTime, DateTime, QboxQueryResolution, bool)"/>
public async Task <QboxPagedDataQueryResult <QboxCounterData> > QueryDataAsync(string serialNumber, DateTime from, DateTime to, QboxQueryResolution resolution, bool adjustHours)
{
Guard.NotNullOrEmpty(serialNumber, nameof(serialNumber));
int adjustedHours = 0;
DateTime fromQueryParameter = from;
if (adjustHours)
{
fromQueryParameter = from.AddDays(-1);
adjustedHours = TimeZoneUtils.GetHoursDifferenceFromUTC(from);
}
string fromPartitionKey = PartitionKeyHelper.Construct(serialNumber, fromQueryParameter);
string toPartitionKey = PartitionKeyHelper.Construct(serialNumber, to);
string fromRowKey = DateTimeRowKeyHelper.Construct(fromQueryParameter);
string toRowKey = DateTimeRowKeyHelper.Construct(to);
_logger.LogInformation("Querying Table {table} with PartitionKey {fromPartitionKey} to {toPartitionKey} and RowKey {fromRowKey} to {toRowKey} and AdjustHours = {adjustHours}", _measurementTable.Name, fromPartitionKey, toPartitionKey, fromRowKey, toRowKey, adjustHours);
var queue = new ConcurrentQueue <List <MeasurementEntity> >();
var tasks = EachDay(fromQueryParameter, to).Select(async date =>
{
var result = await _measurementTable.Set.Where(m =>
m.PartitionKey == PartitionKeyHelper.Construct(serialNumber, date) &&
string.CompareOrdinal(m.RowKey, fromRowKey) <= 0 && string.CompareOrdinal(m.RowKey, toRowKey) > 0
).ToListAsync();
queue.Enqueue(result);
});
await Task.WhenAll(tasks);
var entities = queue.SelectMany(x => x)
.Where(e => e.MeasureTimeAdjusted != true) // Exclude adjusted measurements here. Not possible in real query above !
.Where(e => !adjustHours || e.MeasureTime > from.AddHours(-adjustedHours)) // Filter
.OrderBy(e => e.MeasureTime).ToList();
// Adjust MeasureTime if needed
if (adjustHours)
{
foreach (var entity in entities)
{
entity.MeasureTime = entity.MeasureTime.AddHours(adjustedHours);
}
}
var deltas = entities.Zip(entities.Skip(1), (current, next) => new QboxCounterData
{
MeasureTime = next.MeasureTime,
Delta0181 = next.Counter0181 - current.Counter0181 ?? 0,
Delta0182 = next.Counter0182 - current.Counter0182 ?? 0,
Delta0281 = next.Counter0281 - current.Counter0281 ?? 0,
Delta0282 = next.Counter0282 - current.Counter0282 ?? 0,
Delta2421 = next.Counter2421 - current.Counter2421 ?? 0,
}).ToList();
if (deltas.Count > 0)
{
deltas.Insert(0, new QboxCounterData
{
MeasureTime = entities[0].MeasureTime,
Delta0181 = 0,
Delta0182 = 0,
Delta0281 = 0,
Delta0282 = 0,
Delta2421 = 0
});
}
var groupedByTimeFrame = from delta in deltas
group delta by new
{
MeasureTimeRounded = resolution.TruncateTime(delta.MeasureTime)
}
into g
select new QboxCounterData
{
LabelText = resolution.GetLabelText(g.Key.MeasureTimeRounded),
LabelValue = resolution.GetLabelValue(g.Key.MeasureTimeRounded),
MeasureTime = g.Key.MeasureTimeRounded,
Delta0181 = g.Sum(x => x.Delta0181),
Delta0182 = g.Sum(x => x.Delta0182),
Delta0281 = g.Sum(x => x.Delta0281) * -1,
Delta0282 = g.Sum(x => x.Delta0282) * -1,
Delta2421 = g.Sum(x => x.Delta2421)
};
var itemsFound = groupedByTimeFrame.ToList();
var items = FillGaps(from, to, resolution, itemsFound);
var overview = new QboxCounterData
{
LabelText = "overview",
MeasureTime = DateTime.UtcNow,
Delta0181 = entities.Max(e => e.Counter0181) - entities.Min(e => e.Counter0181) ?? 0,
Delta0182 = entities.Max(e => e.Counter0182) - entities.Min(e => e.Counter0182) ?? 0,
Delta0281 = (entities.Max(e => e.Counter0281) - entities.Min(e => e.Counter0281) ?? 0) * -1,
Delta0282 = (entities.Max(e => e.Counter0282) - entities.Min(e => e.Counter0282) ?? 0) * -1,
Delta2421 = entities.Max(e => e.Counter2421) - entities.Min(e => e.Counter2421) ?? 0
};
// Define DrillDownQuery
if (resolution > QboxQueryResolution.FiveMinutes)
{
foreach (var item in items)
{
item.DrillDownQuery = GetDrillDownQboxDataQuery(item.MeasureTime, resolution, adjustHours);
}
}
return(new QboxPagedDataQueryResult <QboxCounterData>
{
Overview = overview,
Items = items,
Count = items.Count
});
}
private string ConstructCacheKey(string serialNumber, DateTime fromTruncated, DateTime toTruncated, QboxQueryResolution resolution)
{
return($"{serialNumber}:{fromTruncated.Ticks}:{toTruncated.Ticks}:{resolution}");
}
