public virtual IPagedList <TopCustomerReportLine> GetTopCustomersReport(
DateTime?startTime,
DateTime?endTime,
OrderStatus?orderStatus,
PaymentStatus?paymentStatus,
ShippingStatus?shippingStatus,
ReportSorting sorting,
int pageIndex = 0,
int pageSize = int.MaxValue)
{
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 query =
from c in _customerRepository.TableUntracked
join o in _orderRepository.TableUntracked 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) &&
(!o.Deleted) &&
(!c.Deleted)
select new { c, o };
var groupedQuery =
from co in query
group co by co.c.Id into g
select new TopCustomerReportLine
{
CustomerId = g.Key,
OrderTotal = g.Sum(x => x.o.OrderTotal),
OrderCount = g.Count()
};
switch (sorting)
{
case ReportSorting.ByAmountAsc:
groupedQuery = groupedQuery.OrderBy(x => x.OrderTotal);
break;
case ReportSorting.ByAmountDesc:
groupedQuery = groupedQuery.OrderByDescending(x => x.OrderTotal);
break;
case ReportSorting.ByQuantityAsc:
groupedQuery = groupedQuery.OrderBy(x => x.OrderCount).ThenByDescending(x => x.OrderTotal);
break;
case ReportSorting.ByQuantityDesc:
default:
groupedQuery = groupedQuery.OrderByDescending(x => x.OrderCount).ThenByDescending(x => x.OrderTotal);
break;
}
var result = new PagedList <TopCustomerReportLine>(groupedQuery, pageIndex, pageSize);
return(result);
}
/// <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);
}
public virtual IPagedList <BestsellersReportLine> BestSellersReport(
int storeId,
DateTime?startTime,
DateTime?endTime,
OrderStatus?orderStatus,
PaymentStatus?paymentStatus,
ShippingStatus?shippingStatus,
int billingCountryId = 0,
int pageIndex = 0,
int pageSize = int.MaxValue,
ReportSorting sorting = ReportSorting.ByQuantityDesc,
bool showHidden = false)
{
var orderStatusId = orderStatus.HasValue ? (int)orderStatus.Value : (int?)null;
var paymentStatusId = paymentStatus.HasValue ? (int)paymentStatus.Value : (int?)null;
var shippingStatusId = shippingStatus.HasValue ? (int)shippingStatus : (int?)null;
var query =
from orderItem in _orderItemRepository.TableUntracked
join o in _orderRepository.TableUntracked on orderItem.OrderId equals o.Id
join p in _productRepository.TableUntracked 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) &&
(!o.Deleted) &&
(!p.Deleted) && (!p.IsSystemProduct) &&
(billingCountryId == 0 || o.BillingAddress.CountryId == billingCountryId) &&
(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)
};
switch (sorting)
{
case ReportSorting.ByAmountAsc:
groupedQuery = groupedQuery.OrderBy(x => x.TotalAmount);
break;
case ReportSorting.ByAmountDesc:
groupedQuery = groupedQuery.OrderByDescending(x => x.TotalAmount);
break;
case ReportSorting.ByQuantityAsc:
groupedQuery = groupedQuery.OrderBy(x => x.TotalQuantity).ThenByDescending(x => x.TotalAmount);
break;
case ReportSorting.ByQuantityDesc:
default:
groupedQuery = groupedQuery.OrderByDescending(x => x.TotalQuantity).ThenByDescending(x => x.TotalAmount);
break;
}
var result = new PagedList <BestsellersReportLine>(groupedQuery, pageIndex, pageSize);
return(result);
}
public virtual ActionResult ListConfig(ReportSorting sorting = ReportSorting.Title)
{
var config = new DataTableConfig();
config.Searching = true;
config.SetupServerDataSource(Url.Action(MVC.Reporting.ListData()), FormMethod.Post);
config.Columns = new List<DataTableConfig.Column>(columns);
switch (sorting)
{
case ReportSorting.Title:
{
config.AddOrder((int)ReportListColumns.Title, DataTables.Mvc.Column.OrderDirection.Ascendant);
break;
}
case ReportSorting.Project:
{
config.AddOrder((int)ReportListColumns.ProjectName, DataTables.Mvc.Column.OrderDirection.Descendant);
break;
}
case ReportSorting.Step:
{
config.AddOrder((int)ReportListColumns.Step, DataTables.Mvc.Column.OrderDirection.Descendant);
break;
}
}
var json = JsonConvert.SerializeObject(config);
return Content(json, "application/json");
}
public static IQueryable <BestSellersReportLine> SelectAsBestSellersReportLine(this IQueryable <OrderItem> query, ReportSorting sorting = ReportSorting.ByQuantityDesc)
{
Guard.NotNull(query, nameof(query));
// Group by product ID.
var selector =
from oi in query
group oi by oi.ProductId into g
select new BestSellersReportLine
{
ProductId = g.Key,
TotalAmount = g.Sum(x => x.PriceExclTax),
TotalQuantity = g.Sum(x => x.Quantity)
};
switch (sorting)
{
case ReportSorting.ByAmountAsc:
selector = selector.OrderBy(x => x.TotalAmount);
break;
case ReportSorting.ByAmountDesc:
selector = selector.OrderByDescending(x => x.TotalAmount);
break;
case ReportSorting.ByQuantityAsc:
selector = selector.OrderBy(x => x.TotalQuantity).ThenByDescending(x => x.TotalAmount);
break;
case ReportSorting.ByQuantityDesc:
default:
selector = selector.OrderByDescending(x => x.TotalQuantity).ThenByDescending(x => x.TotalAmount);
break;
}
return(selector);
}
