public async Task <List <OrderStats> > GetHourlyStats(int year, int?terminalId, int?pointOfSaleId)
{
var connection = _db.Database.GetDbConnection();
var param = new
{
year,
terminalId,
pointOfSaleId
};
var orderStats = await connection.QueryAsync <OrderStats>(
sql : await SqlScript.GetAsync("GetHourlyOrderStats.sql"),
param : param);
var paymentStats = await connection.QueryAsync <PaymentStats>(
sql : await SqlScript.GetAsync("GetHourlyPaymentStats.sql"),
param : param);
var productStats = await connection.QueryAsync <ProductStats>(
sql : await SqlScript.GetAsync("GetHourlyProductStats.sql"),
param : param);
foreach (var order in orderStats)
{
order.Payments = paymentStats.Where(x => x.PeriodStart == order.PeriodStart).ToList();
order.Products = productStats.Where(x => x.PeriodStart == order.PeriodStart).ToList();
}
return(orderStats.AsList());
}
public async Task <List <OrderStats> > GetHourlyStats(DateTimeOffset periodStart, DateTimeOffset periodEnd, StatsKind kind, int?terminalId, int?pointOfSaleId)
{
var connection = _db.Database.GetDbConnection();
var param = new
{
periodStart,
periodEnd,
kind,
terminalId,
pointOfSaleId
};
var orderStats = await connection.QueryAsync <OrderStats>(
sql : await SqlScript.GetAsync("GetOrderStats.sql"),
param : param);
var paymentStats = await connection.QueryAsync <PaymentStats>(
sql : await SqlScript.GetAsync("GetPaymentStats.sql"),
param : param);
var productSaleStats = await connection.QueryAsync <ProductSaleStats>(
sql : await SqlScript.GetAsync("GetProductSaleStats.sql"),
param : param);
var productServingStats = await connection.QueryAsync <ProductServingStats>(
sql : await SqlScript.GetAsync("GetProductServingStats.sql"),
param : param);
var periodStarts = orderStats.Select(x => x.PeriodStart)
.Union(productServingStats.Select(x => x.PeriodStart))
.OrderBy(x => x)
.ToList();
var result = new List <OrderStats>(periodStarts.Count);
foreach (var startOfPeriod in periodStarts)
{
var order = orderStats.FirstOrDefault(x => x.PeriodStart == startOfPeriod) ?? new OrderStats()
{
PeriodStart = startOfPeriod
};
var productStats = new Dictionary <int, ProductStats>();
foreach (var sale in productSaleStats.Where(x => x.PeriodStart == order.PeriodStart))
{
var stats = new ProductStats()
{
ProductId = sale.ProductId,
ProductName = sale.ProductName,
OrderCount = sale.OrderCount,
SaleQuantity = sale.ProductQuantity,
SaleTotal = sale.Total,
};
productStats.Add(sale.ProductId, stats);
}
foreach (var serving in productServingStats.Where(x => x.PeriodStart == order.PeriodStart))
{
if (!productStats.TryGetValue(serving.ProductId, out var stats))
{
stats = productStats[serving.ProductId] = new ProductStats()
{
ProductId = serving.ProductId,
ProductName = serving.ProductName
};
}
stats.ServingCount = serving.ServingCount;
stats.ServingQuantity = serving.ProductQuantity;
}
order.Payments = paymentStats.Where(x => x.PeriodStart == order.PeriodStart).ToList();
order.Products = productStats.Values.OrderBy(x => x.ProductName).ToList();
result.Add(order);
}
return(result);
}