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); }