public BestsellerDTO(BestsellersReportLine Bs)
{
var _productService = EngineContext.Current.Resolve <IProductService>();
Product = new ProductDTO(_productService.GetProductById(Bs.ProductId));
Amount = Bs.TotalAmount;
Quantity = Bs.TotalQuantity;
}
/// <summary>
/// Get best sellers report
/// </summary>
/// <param name="storeId">Store identifier; "" to load all records</param>
/// <param name="vendorId">Vendor identifier; "" to load all records</param>
/// <param name="categoryId">Category identifier; "" to load all records</param>
/// <param name="manufacturerId">Manufacturer identifier; "" to load all records</param>
/// <param name="createdFromUtc">Order created date from (UTC); null to load all records</param>
/// <param name="createdToUtc">Order created date to (UTC); null to load all records</param>
/// <param name="os">Order status; null to load all records</param>
/// <param name="ps">Order payment status; null to load all records</param>
/// <param name="ss">Shipping status; null to load all records</param>
/// <param name="billingCountryId">Billing country identifier; "" to load all records</param>
/// <param name="orderBy">1 - order by quantity, 2 - order by total amount</param>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <returns>Result</returns>
public virtual async Task <IPagedList <BestsellersReportLine> > BestSellersReport(
string storeId = "", string vendorId = "",
DateTime?createdFromUtc = null, DateTime?createdToUtc = null,
OrderStatus?os = null, PaymentStatus?ps = null, ShippingStatus?ss = null,
string billingCountryId = "",
int orderBy = 1,
int pageIndex = 0, int pageSize = int.MaxValue,
bool showHidden = false)
{
int?orderStatusId = null;
if (os.HasValue)
{
orderStatusId = (int)os.Value;
}
int?paymentStatusId = null;
if (ps.HasValue)
{
paymentStatusId = (int)ps.Value;
}
int?shippingStatusId = null;
if (ss.HasValue)
{
shippingStatusId = (int)ss.Value;
}
var builder = Builders <Order> .Filter;
var builderItem = Builders <UnwindedOrderItem> .Filter;
var filter = builder.Where(o => !o.Deleted);
var filterItem = builderItem.Where(x => true);
if (!String.IsNullOrEmpty(vendorId))
{
filterItem = filterItem & builderItem.Where(x => x.OrderItems.VendorId == vendorId);
}
if (!String.IsNullOrEmpty(storeId))
{
filter = filter & builder.Where(o => o.StoreId == storeId);
}
if (!String.IsNullOrEmpty(vendorId))
{
filter = filter & builder
.Where(o => o.OrderItems
.Any(orderItem => orderItem.VendorId == vendorId));
}
if (!String.IsNullOrEmpty(billingCountryId))
{
filter = filter & builder.Where(o => o.BillingAddress != null && o.BillingAddress.CountryId == billingCountryId);
}
if (orderStatusId.HasValue)
{
filter = filter & builder.Where(o => o.OrderStatusId == orderStatusId.Value);
}
if (paymentStatusId.HasValue)
{
filter = filter & builder.Where(o => o.PaymentStatusId == paymentStatusId.Value);
}
if (shippingStatusId.HasValue)
{
filter = filter & builder.Where(o => o.ShippingStatusId == shippingStatusId.Value);
}
if (createdFromUtc.HasValue)
{
filter = filter & builder.Where(o => createdFromUtc.Value <= o.CreatedOnUtc);
}
if (createdToUtc.HasValue)
{
filter = filter & builder.Where(o => createdToUtc.Value >= o.CreatedOnUtc);
}
FilterDefinition <BsonDocument> filterPublishedProduct = new BsonDocument("Product.Published", true);
var groupBy = new BsonDocument
{
new BsonElement("_id", "$OrderItems.ProductId"),
new BsonElement("TotalAmount", new BsonDocument("$sum", new BsonDocument("$divide", new BsonArray {
"$OrderItems.PriceExclTax", "$CurrencyRate"
}))),
new BsonElement("TotalQuantity", new BsonDocument("$sum", "$OrderItems.Quantity"))
};
var query = _orderRepository.Collection
.Aggregate()
.Match(filter)
.Unwind <Order, UnwindedOrderItem>(x => x.OrderItems)
.Match(filterItem)
.Lookup("Product", "OrderItems.ProductId", "_id", "Product")
.Match(filterPublishedProduct)
.Group(groupBy);
if (orderBy == 1)
{
query = query.SortByDescending(x => x["TotalQuantity"]);
}
else
{
query = query.SortByDescending(x => x["TotalAmount"]);
}
var query2 = new List <BestsellersReportLine>();
await query.ForEachAsync(q =>
{
var line = new BestsellersReportLine();
line.ProductId = q["_id"].ToString();
line.TotalAmount = q["TotalAmount"].AsDecimal;
line.TotalQuantity = q["TotalQuantity"].AsInt32;
query2.Add(line);
});
var result = new PagedList <BestsellersReportLine>(query2, pageIndex, pageSize);
return(result);
}
/// <summary>
/// Get best sellers report
/// </summary>
/// <param name="storeId">Store identifier</param>
/// <param name="startTime">Order start time; null to load all</param>
/// <param name="endTime">Order end time; null to load all</param>
/// <param name="os">Order status; null to load all records</param>
/// <param name="ps">Order payment status; null to load all records</param>
/// <param name="ss">Shipping status; null to load all records</param>
/// <param name="billingCountryId">Billing country identifier; 0 to load all records</param>
/// <param name="recordsToReturn">Records to return</param>
/// <param name="orderBy">1 - order by quantity, 2 - order by total amount</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <returns>Result</returns>
public virtual IList <BestsellersReportLine> BestSellersReport(int storeId,
DateTime?startTime, DateTime?endTime,
OrderStatus?os, PaymentStatus?ps, ShippingStatus?ss,
int billingCountryId = 0,
int recordsToReturn = 5, int orderBy = 1, bool showHidden = false)
{
int?orderStatusId = null;
if (os.HasValue)
{
orderStatusId = (int)os.Value;
}
int?paymentStatusId = null;
if (ps.HasValue)
{
paymentStatusId = (int)ps.Value;
}
int?shippingStatusId = null;
if (ss.HasValue)
{
shippingStatusId = (int)ss.Value;
}
var query1 = from orderItem in _orderItemRepository.Table
join o in _orderRepository.Table on orderItem.OrderId equals o.Id
join p in _productRepository.Table 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) &&
(billingCountryId == 0 || o.BillingAddress.CountryId == billingCountryId) &&
(showHidden || p.Published)
select orderItem;
var query2 =
//group by products
from orderItem in query1
group orderItem by orderItem.ProductId into g
select new
{
EntityId = g.Key,
TotalAmount = g.Sum(x => x.PriceExclTax),
TotalQuantity = g.Sum(x => x.Quantity),
};
switch (orderBy)
{
case 1:
{
query2 = query2.OrderByDescending(x => x.TotalQuantity);
}
break;
case 2:
{
query2 = query2.OrderByDescending(x => x.TotalAmount);
}
break;
default:
throw new ArgumentException("Wrong orderBy parameter", "orderBy");
}
if (recordsToReturn != 0 && recordsToReturn != int.MaxValue)
{
query2 = query2.Take(recordsToReturn);
}
var result = query2.ToList().Select(x =>
{
var reportLine = new BestsellersReportLine()
{
ProductId = x.EntityId,
TotalAmount = x.TotalAmount,
TotalQuantity = x.TotalQuantity
};
return(reportLine);
}).ToList();
return(result);
}
