/// <summary>
/// Applies a selection for top customers report.
/// </summary>
/// <param name="query">Customer query to select the report from.</param>
/// <param name="startTime">Defines start time order filter.</param>
/// <param name="endTime">Defines end time order filter.</param>
/// <param name="orderStatus">Defines order filter by <see cref="OrderStatus"/>.</param>
/// <param name="paymentStatus">Defines order filter by <see cref="PaymentStatus"/>.</param>
/// <param name="shippingStatus">Defines order filter by <see cref="ShippingStatus"/>.</param>
/// <param name="sorting">Defines sorting by <see cref="ReportSorting"/>.</param>
/// <returns>Query of top customers report.</returns>
public static IQueryable <TopCustomerReportLine> SelectAsTopCustomerReportLine(
this IQueryable <Customer> query,
DateTime?startTime = null,
DateTime?endTime = null,
OrderStatus?orderStatus = null,
PaymentStatus?paymentStatus = null,
ShippingStatus?shippingStatus = null,
ReportSorting sorting = ReportSorting.ByQuantityDesc)
{
Guard.NotNull(query, nameof(query));
// TODO: (mh) (core) Bad API-design: a method named .SelectAs...() indicates that only projection
// is applied (...select new TopCustomerReportLine {}). But this method does also filtering. That is
// the ONE thing we wanted to avoid: monolithic code. This method needs a split-up: a filter part
// and a projection part. Details with MC.
var orderStatusId = orderStatus.HasValue ? (int)orderStatus.Value : (int?)null;
var paymentStatusId = paymentStatus.HasValue ? (int)paymentStatus.Value : (int?)null;
var shippingStatusId = shippingStatus.HasValue ? (int)shippingStatus.Value : (int?)null;
var db = query.GetDbContext <SmartDbContext>();
var query2 =
from c in query.AsNoTracking()
join o in db.Orders.AsNoTracking() on c.Id equals o.CustomerId
where (!startTime.HasValue || startTime.Value <= o.CreatedOnUtc) &&
(!endTime.HasValue || endTime.Value >= o.CreatedOnUtc) &&
(!orderStatusId.HasValue || orderStatusId == o.OrderStatusId) &&
(!paymentStatusId.HasValue || paymentStatusId == o.PaymentStatusId) &&
(!shippingStatusId.HasValue || shippingStatusId == o.ShippingStatusId)
select new { c, o };
var groupedQuery =
from co in query2
group co by co.c.Id into g
select new TopCustomerReportLine
{
CustomerId = g.Key,
OrderTotal = g.Sum(x => x.o.OrderTotal),
OrderCount = g.Count()
};
groupedQuery = sorting switch
{
ReportSorting.ByAmountAsc => groupedQuery.OrderBy(x => x.OrderTotal),
ReportSorting.ByAmountDesc => groupedQuery.OrderByDescending(x => x.OrderTotal),
ReportSorting.ByQuantityAsc => groupedQuery.OrderBy(x => x.OrderCount).ThenByDescending(x => x.OrderTotal),
_ => groupedQuery.OrderByDescending(x => x.OrderCount).ThenByDescending(x => x.OrderTotal),
};
return(groupedQuery);
}
// TODO: (ms) (core) This method seems to be not needed
//public virtual async Task<OrderAverageReportLine> GetOrderAverageReportLineAsync(
// int storeId,
// int[] orderStatusIds,
// int[] paymentStatusIds,
// int[] shippingStatusIds,
// DateTime? startTimeUtc,
// DateTime? endTimeUtc,
// string billingEmail,
// bool ignoreCancelledOrders = false)
//{
// //var query = _db.Orders
// // .ApplyStandardFilter(storeId: storeId)
// // .ApplyDateFilter(startTimeUtc, endTimeUtc)
// // .ApplyBillingFilter(billingEmail)
// // .ApplyStatusFilter(orderStatusIds, paymentStatusIds, shippingStatusIds);
//}
// TODO: (ms) (core) refactor method parameters -> qury extensions - reutrns query
// apply status filter
// apply shipping filer
// apply time filter
public virtual Task <IPagedList <BestSellersReportLine> > BestSellersReportAsync(
int storeId,
DateTime?startTime,
DateTime?endTime,
int?orderStatusId = null,
int?paymentStatusId = null,
int?shippingStatusId = null,
int?billingCountryId = null,
int pageIndex = 0,
int pageSize = int.MaxValue,
ReportSorting sorting = ReportSorting.ByQuantityDesc,
bool showHidden = false)
{
var query =
from orderItem in _db.OrderItems
join o in _db.Orders.AsNoTracking() on orderItem.OrderId equals o.Id
join p in _db.Products.AsNoTracking() on orderItem.ProductId equals p.Id
where (storeId == 0 || storeId == o.StoreId) &&
(!startTime.HasValue || startTime.Value <= o.CreatedOnUtc) &&
(!endTime.HasValue || endTime.Value >= o.CreatedOnUtc) &&
(!orderStatusId.HasValue || orderStatusId == o.OrderStatusId) &&
(!paymentStatusId.HasValue || paymentStatusId == o.PaymentStatusId) &&
(!shippingStatusId.HasValue || shippingStatusId == o.ShippingStatusId) &&
(billingCountryId == 0 || o.BillingAddress.CountryId == billingCountryId) &&
(!p.IsSystemProduct) &&
(showHidden || p.Published)
select orderItem;
// Group by product ID.
var groupedQuery =
from orderItem in query
group orderItem by orderItem.ProductId into g
select new BestSellersReportLine
{
ProductId = g.Key,
TotalAmount = g.Sum(x => x.PriceExclTax),
TotalQuantity = g.Sum(x => x.Quantity)
};
groupedQuery = sorting switch
{
ReportSorting.ByAmountAsc => groupedQuery.OrderBy(x => x.TotalAmount),
ReportSorting.ByAmountDesc => groupedQuery.OrderByDescending(x => x.TotalAmount),
ReportSorting.ByQuantityAsc => groupedQuery.OrderBy(x => x.TotalQuantity).ThenByDescending(x => x.TotalAmount),
_ => groupedQuery.OrderByDescending(x => x.TotalQuantity).ThenByDescending(x => x.TotalAmount),
};
return(groupedQuery.ToPagedList(pageIndex, pageSize).LoadAsync());
}
/// <summary>
/// Applies a selection for bestsellers report.
/// </summary>
/// <param name="query">Order item query to select report from.</param>
/// <param name="sorting">Sorting setting to define Bestsellers report type.</param>
/// <returns>Query of bestsellers report.</returns>
public static IQueryable <BestsellersReportLine> SelectAsBestsellersReportLine(this IQueryable <OrderItem> query, ReportSorting sorting = ReportSorting.ByQuantityDesc)
{
Guard.NotNull(query, nameof(query));
var selector = query
.GroupBy(x => x.ProductId)
.Select(x => new BestsellersReportLine
{
ProductId = x.Key,
TotalAmount = x.Sum(x => x.PriceExclTax),
TotalQuantity = x.Sum(x => x.Quantity)
});
selector = sorting switch
{
ReportSorting.ByAmountAsc => selector.OrderBy(x => x.TotalAmount),
ReportSorting.ByAmountDesc => selector.OrderByDescending(x => x.TotalAmount),
ReportSorting.ByQuantityAsc => selector.OrderBy(x => x.TotalQuantity).ThenByDescending(x => x.TotalAmount),
_ => selector.OrderByDescending(x => x.TotalQuantity).ThenByDescending(x => x.TotalAmount),
};
return(selector);
}