Try your search with a different keyword or use * as a wildcard.
using System.Globalization;
using Nop.Core;
using Nop.Core.Domain.Catalog;
using Nop.Core.Domain.Common;
using Nop.Core.Domain.Directory;
using Nop.Core.Domain.Orders;
using Nop.Core.Domain.Payments;
using Nop.Core.Domain.Shipping;
using Nop.Data;
using Nop.Services.Catalog;
using Nop.Services.Directory;
using Nop.Services.Helpers;
using Nop.Services.Stores;
namespace Nop.Services.Orders;
///
/// Order report service
///
public partial class OrderReportService : IOrderReportService
{
#region Fields
protected readonly CurrencySettings _currencySettings;
protected readonly ICurrencyService _currencyService;
protected readonly IDateTimeHelper _dateTimeHelper;
protected readonly IPriceFormatter _priceFormatter;
protected readonly IRepository _addressRepository;
protected readonly IRepository _orderRepository;
protected readonly IRepository _orderItemRepository;
protected readonly IRepository _orderNoteRepository;
protected readonly IRepository _productRepository;
protected readonly IRepository _productCategoryRepository;
protected readonly IRepository _productManufacturerRepository;
protected readonly IRepository _productWarehouseInventoryRepository;
protected readonly IStoreMappingService _storeMappingService;
protected readonly IWorkContext _workContext;
#endregion
#region Ctor
public OrderReportService(
CurrencySettings currencySettings,
ICurrencyService currencyService,
IDateTimeHelper dateTimeHelper,
IPriceFormatter priceFormatter,
IRepository addressRepository,
IRepository orderRepository,
IRepository orderItemRepository,
IRepository orderNoteRepository,
IRepository productRepository,
IRepository productCategoryRepository,
IRepository productManufacturerRepository,
IRepository productWarehouseInventoryRepository,
IStoreMappingService storeMappingService,
IWorkContext workContext)
{
_currencySettings = currencySettings;
_currencyService = currencyService;
_dateTimeHelper = dateTimeHelper;
_priceFormatter = priceFormatter;
_addressRepository = addressRepository;
_orderRepository = orderRepository;
_orderItemRepository = orderItemRepository;
_orderNoteRepository = orderNoteRepository;
_productRepository = productRepository;
_productCategoryRepository = productCategoryRepository;
_productManufacturerRepository = productManufacturerRepository;
_productWarehouseInventoryRepository = productWarehouseInventoryRepository;
_storeMappingService = storeMappingService;
_workContext = workContext;
}
#endregion
#region Utilities
///
/// Search order items
///
/// Store identifier (orders placed in a specific store); 0 to load all records
/// Vendor identifier; 0 to load all records
/// Category identifier; 0 to load all records
/// Manufacturer identifier; 0 to load all records
/// Order created date from (UTC); null to load all records
/// Order created date to (UTC); null to load all records
/// Order status; null to load all records
/// Order payment status; null to load all records
/// Shipping status; null to load all records
/// Billing country identifier; 0 to load all records
/// A value indicating whether to show hidden records
/// Result query
protected virtual IQueryable SearchOrderItems(
int categoryId = 0,
int manufacturerId = 0,
int storeId = 0,
int vendorId = 0,
DateTime? createdFromUtc = null,
DateTime? createdToUtc = null,
OrderStatus? os = null,
PaymentStatus? ps = null,
ShippingStatus? ss = null,
int billingCountryId = 0,
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 orderItems = 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
join oba in _addressRepository.Table on o.BillingAddressId equals oba.Id
where (storeId == 0 || storeId == o.StoreId) &&
(!createdFromUtc.HasValue || createdFromUtc.Value <= o.CreatedOnUtc) &&
(!createdToUtc.HasValue || createdToUtc.Value >= o.CreatedOnUtc) &&
(!orderStatusId.HasValue || orderStatusId == o.OrderStatusId) &&
(!paymentStatusId.HasValue || paymentStatusId == o.PaymentStatusId) &&
(!shippingStatusId.HasValue || shippingStatusId == o.ShippingStatusId) &&
!o.Deleted && !p.Deleted &&
(vendorId == 0 || p.VendorId == vendorId) &&
(billingCountryId == 0 || oba.CountryId == billingCountryId) &&
(showHidden || p.Published)
select orderItem;
if (categoryId > 0)
{
orderItems = from orderItem in orderItems
join p in _productRepository.Table on orderItem.ProductId equals p.Id
join pc in _productCategoryRepository.Table on p.Id equals pc.ProductId
into p_pc
from pc in p_pc.DefaultIfEmpty()
where pc.CategoryId == categoryId
select orderItem;
}
if (manufacturerId > 0)
{
orderItems = from orderItem in orderItems
join p in _productRepository.Table on orderItem.ProductId equals p.Id
join pm in _productManufacturerRepository.Table on p.Id equals pm.ProductId
into p_pm
from pm in p_pm.DefaultIfEmpty()
where pm.ManufacturerId == manufacturerId
select orderItem;
}
return orderItems;
}
#endregion
#region Methods
///
/// Get "order by country" report
///
/// Store identifier
/// Order status
/// Payment status
/// Shipping status
/// Start date
/// End date
///
/// A task that represents the asynchronous operation
/// The task result contains the result
///
public virtual async Task> GetCountryReportAsync(int storeId, OrderStatus? os,
PaymentStatus? ps, ShippingStatus? ss, DateTime? startTimeUtc, DateTime? endTimeUtc)
{
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 query = _orderRepository.Table;
query = query.Where(o => !o.Deleted);
if (storeId > 0)
query = query.Where(o => o.StoreId == storeId);
if (orderStatusId.HasValue)
query = query.Where(o => o.OrderStatusId == orderStatusId.Value);
if (paymentStatusId.HasValue)
query = query.Where(o => o.PaymentStatusId == paymentStatusId.Value);
if (shippingStatusId.HasValue)
query = query.Where(o => o.ShippingStatusId == shippingStatusId.Value);
if (startTimeUtc.HasValue)
query = query.Where(o => startTimeUtc.Value <= o.CreatedOnUtc);
if (endTimeUtc.HasValue)
query = query.Where(o => endTimeUtc.Value >= o.CreatedOnUtc);
var report = await (from oq in query
join a in _addressRepository.Table on oq.BillingAddressId equals a.Id
group oq by a.CountryId
into result
select new
{
CountryId = result.Key,
TotalOrders = result.Count(),
SumOrders = result.Sum(o => o.OrderTotal)
})
.OrderByDescending(x => x.SumOrders)
.Select(r => new OrderByCountryReportLine
{
CountryId = r.CountryId,
TotalOrders = r.TotalOrders,
SumOrders = r.SumOrders
})
.ToListAsync();
return report;
}
///
/// Get order average report
///
/// Store identifier; pass 0 to ignore this parameter
/// Vendor identifier; pass 0 to ignore this parameter
/// Product identifier which was purchased in an order; 0 to load all orders
/// Warehouse identifier; pass 0 to ignore this parameter
/// Billing country identifier; 0 to load all orders
/// Order identifier; pass 0 to ignore this parameter
/// Payment method system name; null to load all records
/// Order status identifiers
/// Payment status identifiers
/// Shipping status identifiers
/// Start date
/// End date
/// Billing phone. Leave empty to load all records.
/// Billing email. Leave empty to load all records.
/// Billing last name. Leave empty to load all records.
/// Search in order notes. Leave empty to load all records.
///
/// A task that represents the asynchronous operation
/// The task result contains the result
///
public virtual async Task GetOrderAverageReportLineAsync(int storeId = 0,
int vendorId = 0, int productId = 0, int warehouseId = 0, int billingCountryId = 0,
int orderId = 0, string paymentMethodSystemName = null,
List osIds = null, List psIds = null, List ssIds = null,
DateTime? startTimeUtc = null, DateTime? endTimeUtc = null,
string billingPhone = null, string billingEmail = null, string billingLastName = "", string orderNotes = null)
{
var query = _orderRepository.Table;
query = query.Where(o => !o.Deleted);
if (storeId > 0)
query = query.Where(o => o.StoreId == storeId);
if (orderId > 0)
query = query.Where(o => o.Id == orderId);
if (vendorId > 0)
{
query = from o in query
join oi in _orderItemRepository.Table on o.Id equals oi.OrderId
join p in _productRepository.Table on oi.ProductId equals p.Id
where p.VendorId == vendorId
select o;
query = query.Distinct();
}
if (productId > 0)
{
query = from o in query
join oi in _orderItemRepository.Table on o.Id equals oi.OrderId
where oi.ProductId == productId
select o;
query = query.Distinct();
}
if (warehouseId > 0)
{
var manageStockInventoryMethodId = (int)ManageInventoryMethod.ManageStock;
query = from o in query
join oi in _orderItemRepository.Table on o.Id equals oi.OrderId
join p in _productRepository.Table on oi.ProductId equals p.Id
join pwi in _productWarehouseInventoryRepository.Table on p.Id equals pwi.ProductId
where
//"Use multiple warehouses" enabled
//we search in each warehouse
(p.ManageInventoryMethodId == manageStockInventoryMethodId && p.UseMultipleWarehouses && pwi.WarehouseId == warehouseId) ||
//"Use multiple warehouses" disabled
//we use standard "warehouse" property
((p.ManageInventoryMethodId != manageStockInventoryMethodId || !p.UseMultipleWarehouses) && p.WarehouseId == warehouseId)
select o;
query = query.Distinct();
}
query = from o in query
join oba in _addressRepository.Table on o.BillingAddressId equals oba.Id
where
(billingCountryId <= 0 || (oba.CountryId == billingCountryId)) &&
(string.IsNullOrEmpty(billingPhone) || (!string.IsNullOrEmpty(oba.PhoneNumber) && oba.PhoneNumber.Contains(billingPhone))) &&
(string.IsNullOrEmpty(billingEmail) || (!string.IsNullOrEmpty(oba.Email) && oba.Email.Contains(billingEmail))) &&
(string.IsNullOrEmpty(billingLastName) || (!string.IsNullOrEmpty(oba.LastName) && oba.LastName.Contains(billingLastName)))
select o;
if (!string.IsNullOrEmpty(paymentMethodSystemName))
query = query.Where(o => o.PaymentMethodSystemName == paymentMethodSystemName);
if (osIds != null && osIds.Any())
query = query.Where(o => osIds.Contains(o.OrderStatusId));
if (psIds != null && psIds.Any())
query = query.Where(o => psIds.Contains(o.PaymentStatusId));
if (ssIds != null && ssIds.Any())
query = query.Where(o => ssIds.Contains(o.ShippingStatusId));
if (startTimeUtc.HasValue)
query = query.Where(o => startTimeUtc.Value <= o.CreatedOnUtc);
if (endTimeUtc.HasValue)
query = query.Where(o => endTimeUtc.Value >= o.CreatedOnUtc);
if (!string.IsNullOrEmpty(orderNotes))
{
query = from o in query
join n in _orderNoteRepository.Table on o.Id equals n.OrderId
where n.Note.Contains(orderNotes)
select o;
query = query.Distinct();
}
var item = await (from oq in query
group oq by 1
into result
select new
{
OrderCount = result.Count(),
OrderShippingExclTaxSum = result.Sum(o => o.OrderShippingExclTax),
OrderPaymentFeeExclTaxSum = result.Sum(o => o.PaymentMethodAdditionalFeeExclTax),
OrderTaxSum = result.Sum(o => o.OrderTax),
OrderTotalSum = result.Sum(o => o.OrderTotal),
OrederRefundedAmountSum = result.Sum(o => o.RefundedAmount),
}).Select(r => new OrderAverageReportLine
{
CountOrders = r.OrderCount,
SumShippingExclTax = r.OrderShippingExclTaxSum,
OrderPaymentFeeExclTaxSum = r.OrderPaymentFeeExclTaxSum,
SumTax = r.OrderTaxSum,
SumOrders = r.OrderTotalSum,
SumRefundedAmount = r.OrederRefundedAmountSum
})
.FirstOrDefaultAsync();
item ??= new OrderAverageReportLine
{
CountOrders = 0,
SumShippingExclTax = decimal.Zero,
OrderPaymentFeeExclTaxSum = decimal.Zero,
SumTax = decimal.Zero,
SumOrders = decimal.Zero
};
return item;
}
///
/// Get order average report
///
/// Store identifier
/// Order status
///
/// A task that represents the asynchronous operation
/// The task result contains the result
///
public virtual async Task OrderAverageReportAsync(int storeId, OrderStatus os)
{
var item = new OrderAverageReportLineSummary
{
OrderStatus = os
};
var orderStatuses = new List { (int)os };
var nowDt = await _dateTimeHelper.ConvertToUserTimeAsync(DateTime.Now);
var timeZone = await _dateTimeHelper.GetCurrentTimeZoneAsync();
//today
var t1 = new DateTime(nowDt.Year, nowDt.Month, nowDt.Day);
DateTime? startTime1 = _dateTimeHelper.ConvertToUtcTime(t1, timeZone);
var todayResult = await GetOrderAverageReportLineAsync(storeId,
osIds: orderStatuses,
startTimeUtc: startTime1);
item.SumTodayOrders = todayResult.SumOrders;
item.CountTodayOrders = todayResult.CountOrders;
//week
var fdow = CultureInfo.CurrentCulture.DateTimeFormat.FirstDayOfWeek;
var today = new DateTime(nowDt.Year, nowDt.Month, nowDt.Day);
var t2 = today.AddDays(-(today.DayOfWeek - fdow));
DateTime? startTime2 = _dateTimeHelper.ConvertToUtcTime(t2, timeZone);
var weekResult = await GetOrderAverageReportLineAsync(storeId,
osIds: orderStatuses,
startTimeUtc: startTime2);
item.SumThisWeekOrders = weekResult.SumOrders;
item.CountThisWeekOrders = weekResult.CountOrders;
//month
var t3 = new DateTime(nowDt.Year, nowDt.Month, 1);
DateTime? startTime3 = _dateTimeHelper.ConvertToUtcTime(t3, timeZone);
var monthResult = await GetOrderAverageReportLineAsync(storeId,
osIds: orderStatuses,
startTimeUtc: startTime3);
item.SumThisMonthOrders = monthResult.SumOrders;
item.CountThisMonthOrders = monthResult.CountOrders;
//year
var t4 = new DateTime(nowDt.Year, 1, 1);
DateTime? startTime4 = _dateTimeHelper.ConvertToUtcTime(t4, timeZone);
var yearResult = await GetOrderAverageReportLineAsync(storeId,
osIds: orderStatuses,
startTimeUtc: startTime4);
item.SumThisYearOrders = yearResult.SumOrders;
item.CountThisYearOrders = yearResult.CountOrders;
//all time
var allTimeResult = await GetOrderAverageReportLineAsync(storeId, osIds: orderStatuses);
item.SumAllTimeOrders = allTimeResult.SumOrders;
item.CountAllTimeOrders = allTimeResult.CountOrders;
return item;
}
///
/// Get sales summary report
///
/// Category identifier; 0 to load all records
/// Product identifier; 0 to load all records
/// Manufacturer identifier; 0 to load all records
/// Store identifier (orders placed in a specific store); 0 to load all records
/// Vendor identifier; 0 to load all records
/// Order created date from (UTC); null to load all records
/// Order created date to (UTC); null to load all records
/// Order status identifiers; null to load all orders
/// Payment status identifiers; null to load all orders
/// Billing country identifier; 0 to load all records
/// 0 - group by day, 1 - group by week, 2 - group by total month
/// Page index
/// Page size
///
/// A task that represents the asynchronous operation
/// The task result contains the result
///
public virtual async Task> SalesSummaryReportAsync(
int categoryId = 0,
int productId = 0,
int manufacturerId = 0,
int storeId = 0,
int vendorId = 0,
DateTime? createdFromUtc = null,
DateTime? createdToUtc = null,
List osIds = null,
List psIds = null,
int billingCountryId = 0,
GroupByOptions groupBy = GroupByOptions.Day,
int pageIndex = 0,
int pageSize = int.MaxValue)
{
//var orderItems = SearchOrderItems(categoryId, manufacturerId, storeId, vendorId, createdFromUtc, createdToUtc, os, ps, null, billingCountryId);
var query = _orderRepository.Table;
query = query.Where(o => !o.Deleted);
//filter by date
if (createdFromUtc.HasValue)
query = query.Where(o => createdFromUtc.Value <= o.CreatedOnUtc);
if (createdToUtc.HasValue)
query = query.Where(o => createdToUtc.Value >= o.CreatedOnUtc);
//filter by order status
if (osIds != null && osIds.Any())
query = query.Where(o => osIds.Contains(o.OrderStatusId));
//filter by payment status
if (psIds != null && psIds.Any())
query = query.Where(o => psIds.Contains(o.PaymentStatusId));
//filter by category
if (categoryId > 0)
{
query = from o in query
join oi in _orderItemRepository.Table on o.Id equals oi.OrderId
join p in _productRepository.Table on oi.ProductId equals p.Id
join pc in _productCategoryRepository.Table on p.Id equals pc.ProductId
where pc.CategoryId == categoryId
select o;
query = query.Distinct();
}
//filter by manufacturer
if (manufacturerId > 0)
{
query = from o in query
join oi in _orderItemRepository.Table on o.Id equals oi.OrderId
join p in _productRepository.Table on oi.ProductId equals p.Id
join pm in _productManufacturerRepository.Table on p.Id equals pm.ProductId
where pm.ManufacturerId == manufacturerId
select o;
query = query.Distinct();
}
//filter by country
if (billingCountryId > 0)
{
query = from o in query
join oba in _addressRepository.Table on o.BillingAddressId equals oba.Id
where
billingCountryId <= 0 || oba.CountryId == billingCountryId
select o;
query = query.Distinct();
}
//filter by product
if (productId > 0)
{
query = from o in query
join oi in _orderItemRepository.Table on o.Id equals oi.OrderId
where oi.ProductId == productId
select o;
query = query.Distinct();
}
//filter by store
if (storeId > 0)
query = query.Where(o => o.StoreId == storeId);
if (vendorId > 0)
{
query = from o in query
join oi in _orderItemRepository.Table on o.Id equals oi.OrderId
join p in _productRepository.Table on oi.ProductId equals p.Id
where p.VendorId == vendorId
select o;
query = query.Distinct();
}
var primaryStoreCurrency = await _currencyService.GetCurrencyByIdAsync(_currencySettings.PrimaryStoreCurrencyId);
var userTimeZone = await _dateTimeHelper.GetCurrentTimeZoneAsync();
var utcOffsetInMinutes = userTimeZone.BaseUtcOffset.TotalMinutes;
var items = groupBy switch
{
GroupByOptions.Day => from oq in query
group oq by oq.CreatedOnUtc.AddMinutes(utcOffsetInMinutes).Date into result
let orderItems = _orderItemRepository.Table.Where(oi => result.Any(x => x.Id == oi.OrderId))
select new
{
SummaryDate = result.Key,
OrderSummaryType = groupBy,
OrderCount = result.Count(),
OrderShippingExclTaxSum = result.Sum(o => o.OrderShippingExclTax),
OrderPaymentFeeExclTaxSum = result.Sum(o => o.PaymentMethodAdditionalFeeExclTax),
OrderTaxSum = result.Sum(o => o.OrderTax),
OrderTotalSum = result.Sum(o => o.OrderTotal),
OrderRefundedAmountSum = result.Sum(o => o.RefundedAmount),
OrderTotalCost = orderItems.Sum(oi => (decimal?)oi.OriginalProductCost * oi.Quantity)
},
GroupByOptions.Week => from oq in query
group oq by oq.CreatedOnUtc.AddMinutes(utcOffsetInMinutes).AddDays(-(int)oq.CreatedOnUtc.AddMinutes(utcOffsetInMinutes).DayOfWeek).Date into result
let orderItems = _orderItemRepository.Table.Where(oi => result.Any(x => x.Id == oi.OrderId))
select new
{
SummaryDate = result.Key,
OrderSummaryType = groupBy,
OrderCount = result.Count(),
OrderShippingExclTaxSum = result.Sum(o => o.OrderShippingExclTax),
OrderPaymentFeeExclTaxSum = result.Sum(o => o.PaymentMethodAdditionalFeeExclTax),
OrderTaxSum = result.Sum(o => o.OrderTax),
OrderTotalSum = result.Sum(o => o.OrderTotal),
OrderRefundedAmountSum = result.Sum(o => o.RefundedAmount),
OrderTotalCost = orderItems.Sum(oi => (decimal?)oi.OriginalProductCost * oi.Quantity)
},
GroupByOptions.Month => from oq in query
group oq by oq.CreatedOnUtc.AddMinutes(utcOffsetInMinutes).AddDays(1 - oq.CreatedOnUtc.AddMinutes(utcOffsetInMinutes).Day).Date into result
let orderItems = _orderItemRepository.Table.Where(oi => result.Any(x => x.Id == oi.OrderId))
select new
{
SummaryDate = result.Key,
OrderSummaryType = groupBy,
OrderCount = result.Count(),
OrderShippingExclTaxSum = result.Sum(o => o.OrderShippingExclTax),
OrderPaymentFeeExclTaxSum = result.Sum(o => o.PaymentMethodAdditionalFeeExclTax),
OrderTaxSum = result.Sum(o => o.OrderTax),
OrderTotalSum = result.Sum(o => o.OrderTotal),
OrderRefundedAmountSum = result.Sum(o => o.RefundedAmount),
OrderTotalCost = orderItems.Sum(oi => (decimal?)oi.OriginalProductCost * oi.Quantity)
},
_ => throw new ArgumentException("Wrong groupBy parameter", nameof(groupBy)),
};
var ssReport =
from orderItem in items
orderby orderItem.SummaryDate descending
select new SalesSummaryReportLine
{
SummaryDate = orderItem.SummaryDate,
SummaryType = (int)orderItem.OrderSummaryType,
NumberOfOrders = orderItem.OrderCount,
Profit = orderItem.OrderTotalSum
- orderItem.OrderShippingExclTaxSum
- orderItem.OrderPaymentFeeExclTaxSum
- orderItem.OrderTaxSum
- orderItem.OrderRefundedAmountSum
- Convert.ToDecimal(orderItem.OrderTotalCost),
Shipping = orderItem.OrderShippingExclTaxSum.ToString(CultureInfo.CurrentCulture),
Tax = orderItem.OrderTaxSum.ToString(CultureInfo.CurrentCulture),
OrderTotal = orderItem.OrderTotalSum.ToString(CultureInfo.CurrentCulture)
};
var report = await ssReport.ToPagedListAsync(pageIndex, pageSize);
static string formatSummary(DateTime dt, GroupByOptions groupByOption)
{
const string dayFormat = "MMM dd, yyyy";
return groupByOption switch
{
GroupByOptions.Week => $"{dt.ToString(dayFormat)} - {dt.AddDays(6).ToString(dayFormat)}",
GroupByOptions.Day => dt.ToString(dayFormat),
GroupByOptions.Month => dt.ToString("MMMM, yyyy"),
_ => ""
};
}
foreach (var reportLine in report)
{
reportLine.Summary = formatSummary(reportLine.SummaryDate, groupBy);
reportLine.ProfitStr = await _priceFormatter.FormatPriceAsync(reportLine.Profit, true, false);
reportLine.Shipping = await _priceFormatter
.FormatShippingPriceAsync(Convert.ToDecimal(reportLine.Shipping), true, primaryStoreCurrency, (await _workContext.GetWorkingLanguageAsync()).Id, false);
reportLine.Tax = await _priceFormatter.FormatPriceAsync(Convert.ToDecimal(reportLine.Tax), true, false);
reportLine.OrderTotal = await _priceFormatter.FormatPriceAsync(Convert.ToDecimal(reportLine.OrderTotal), true, false);
}
return report;
}
///
/// Get best sellers report
///
/// Store identifier (orders placed in a specific store); 0 to load all records
/// Vendor identifier; 0 to load all records
/// Category identifier; 0 to load all records
/// Manufacturer identifier; 0 to load all records
/// Order created date from (UTC); null to load all records
/// Order created date to (UTC); null to load all records
/// Order status; null to load all records
/// Order payment status; null to load all records
/// Shipping status; null to load all records
/// Billing country identifier; 0 to load all records
/// 1 - order by quantity, 2 - order by total amount
/// Page index
/// Page size
/// A value indicating whether to show hidden records
///
/// A task that represents the asynchronous operation
/// The task result contains the result
///
public virtual async Task> BestSellersReportAsync(
int categoryId = 0,
int manufacturerId = 0,
int storeId = 0,
int vendorId = 0,
DateTime? createdFromUtc = null,
DateTime? createdToUtc = null,
OrderStatus? os = null,
PaymentStatus? ps = null,
ShippingStatus? ss = null,
int billingCountryId = 0,
OrderByEnum orderBy = OrderByEnum.OrderByQuantity,
int pageIndex = 0,
int pageSize = int.MaxValue,
bool showHidden = false)
{
var bestSellers = SearchOrderItems(categoryId, manufacturerId, storeId, vendorId, createdFromUtc, createdToUtc, os, ps, ss, billingCountryId, showHidden);
var bsReport =
//group by products
from orderItem in bestSellers
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)
};
bsReport = from item in bsReport
join p in _productRepository.Table on item.ProductId equals p.Id
select new BestsellersReportLine
{
ProductId = item.ProductId,
TotalAmount = item.TotalAmount,
TotalQuantity = item.TotalQuantity,
ProductName = p.Name
};
bsReport = orderBy switch
{
OrderByEnum.OrderByQuantity => bsReport.OrderByDescending(x => x.TotalQuantity),
OrderByEnum.OrderByTotalAmount => bsReport.OrderByDescending(x => x.TotalAmount),
_ => throw new ArgumentException("Wrong orderBy parameter", nameof(orderBy)),
};
var result = await bsReport.ToPagedListAsync(pageIndex, pageSize);
return result;
}
///
/// Get best sellers total amount
///
/// Store identifier (orders placed in a specific store); 0 to load all records
/// Vendor identifier; 0 to load all records
/// Category identifier; 0 to load all records
/// Manufacturer identifier; 0 to load all records
/// Order created date from (UTC); null to load all records
/// Order created date to (UTC); null to load all records
/// Order status; null to load all records
/// Order payment status; null to load all records
/// Shipping status; null to load all records
/// Billing country identifier; 0 to load all records
/// A value indicating whether to show hidden records
///
/// A task that represents the asynchronous operation
/// The task result contains the result
///
public virtual async Task BestSellersReportTotalAmountAsync(
int categoryId = 0,
int manufacturerId = 0,
int storeId = 0,
int vendorId = 0,
DateTime? createdFromUtc = null,
DateTime? createdToUtc = null,
OrderStatus? os = null,
PaymentStatus? ps = null,
ShippingStatus? ss = null,
int billingCountryId = 0,
bool showHidden = false)
{
return await SearchOrderItems(categoryId, manufacturerId, storeId, vendorId, createdFromUtc, createdToUtc, os, ps, ss, billingCountryId, showHidden: showHidden)
.SumAsync(bestseller => bestseller.PriceExclTax);
}
///
/// Gets a list of products (identifiers) purchased by other customers who purchased a specified product
///
/// Store identifier
/// Product identifier
/// Records to return
/// A values indicating whether to load only products marked as "visible individually"; "false" to load all records; "true" to load "visible individually" only
/// A value indicating whether to show hidden records
///
/// A task that represents the asynchronous operation
/// The task result contains the products
///
public virtual async Task GetAlsoPurchasedProductsIdsAsync(int storeId, int productId,
int recordsToReturn = 5, bool visibleIndividuallyOnly = true, bool showHidden = false)
{
if (productId == 0)
throw new ArgumentException("Product ID is not specified");
//this inner query should retrieve all orders that contains a specified product ID
var query1 = from orderItem in _orderItemRepository.Table
where orderItem.ProductId == productId
select orderItem.OrderId;
var query2 = from orderItem in _orderItemRepository.Table
join p in _productRepository.Table on orderItem.ProductId equals p.Id
join o in _orderRepository.Table on orderItem.OrderId equals o.Id
where query1.Contains(orderItem.OrderId) &&
p.Id != productId &&
(showHidden || p.Published) &&
!o.Deleted &&
(storeId == 0 || o.StoreId == storeId) &&
!p.Deleted &&
(!visibleIndividuallyOnly || p.VisibleIndividually)
select new { orderItem, p };
var query3 = from orderItem_p in query2
group orderItem_p by orderItem_p.p.Id into g
select new
{
ProductId = g.Key,
ProductsPurchased = g.Sum(x => x.orderItem.Quantity)
};
query3 = query3.OrderByDescending(x => x.ProductsPurchased);
if (recordsToReturn > 0)
query3 = query3.Take(recordsToReturn);
var report = await query3.ToListAsync();
var ids = new List();
foreach (var reportLine in report)
ids.Add(reportLine.ProductId);
return ids.ToArray();
}
///
/// Gets a list of products that were never sold
///
/// Vendor identifier (filter products by a specific vendor); 0 to load all records
/// Store identifier (filter products by a specific store); 0 to load all records
/// Category identifier; 0 to load all records
/// Manufacturer identifier; 0 to load all records
/// Order created date from (UTC); null to load all records
/// Order created date to (UTC); null to load all records
/// Page index
/// Page size
/// A value indicating whether to show hidden records
///
/// A task that represents the asynchronous operation
/// The task result contains the products
///
public virtual async Task> ProductsNeverSoldAsync(int vendorId = 0, int storeId = 0,
int categoryId = 0, int manufacturerId = 0,
DateTime? createdFromUtc = null, DateTime? createdToUtc = null,
int pageIndex = 0, int pageSize = int.MaxValue, bool showHidden = false)
{
var simpleProductTypeId = (int)ProductType.SimpleProduct;
var availableProductsQuery =
from oi in _orderItemRepository.Table
join o in _orderRepository.Table on oi.OrderId equals o.Id
where (!createdFromUtc.HasValue || createdFromUtc.Value <= o.CreatedOnUtc) &&
(!createdToUtc.HasValue || createdToUtc.Value >= o.CreatedOnUtc) &&
!o.Deleted
select new { oi.ProductId };
var query =
from p in _productRepository.Table
join oi in availableProductsQuery on p.Id equals oi.ProductId
into p_oi
from oi in p_oi.DefaultIfEmpty()
where oi == null &&
p.ProductTypeId == simpleProductTypeId &&
!p.Deleted &&
(vendorId == 0 || p.VendorId == vendorId) &&
(showHidden || p.Published)
select p;
if (categoryId > 0)
{
query = from p in query
join pc in _productCategoryRepository.Table on p.Id equals pc.ProductId
into p_pc
from pc in p_pc.DefaultIfEmpty()
where pc.CategoryId == categoryId
select p;
}
if (manufacturerId > 0)
{
query = from p in query
join pm in _productManufacturerRepository.Table on p.Id equals pm.ProductId
into p_pm
from pm in p_pm.DefaultIfEmpty()
where pm.ManufacturerId == manufacturerId
select p;
}
//apply store mapping constraints
query = await _storeMappingService.ApplyStoreMapping(query, storeId);
query = query.OrderBy(p => p.Name);
var products = await query.ToPagedListAsync(pageIndex, pageSize);
return products;
}
///
/// Get profit report
///
/// Store identifier; pass 0 to ignore this parameter
/// Vendor identifier; pass 0 to ignore this parameter
/// Product identifier which was purchased in an order; 0 to load all orders
/// Warehouse identifier; pass 0 to ignore this parameter
/// Order identifier; pass 0 to ignore this parameter
/// Billing country identifier; 0 to load all orders
/// Payment method system name; null to load all records
/// Start date
/// End date
/// Order status identifiers; null to load all records
/// Payment status identifiers; null to load all records
/// Shipping status identifiers; null to load all records
/// Billing phone. Leave empty to load all records.
/// Billing email. Leave empty to load all records.
/// Billing last name. Leave empty to load all records.
/// Search in order notes. Leave empty to load all records.
///
/// A task that represents the asynchronous operation
/// The task result contains the result
///
public virtual async Task ProfitReportAsync(int storeId = 0, int vendorId = 0, int productId = 0,
int warehouseId = 0, int billingCountryId = 0, int orderId = 0, string paymentMethodSystemName = null,
List osIds = null, List psIds = null, List ssIds = null,
DateTime? startTimeUtc = null, DateTime? endTimeUtc = null,
string billingPhone = null, string billingEmail = null, string billingLastName = "", string orderNotes = null)
{
var dontSearchPhone = string.IsNullOrEmpty(billingPhone);
var dontSearchEmail = string.IsNullOrEmpty(billingEmail);
var dontSearchLastName = string.IsNullOrEmpty(billingLastName);
var dontSearchOrderNotes = string.IsNullOrEmpty(orderNotes);
var dontSearchPaymentMethods = string.IsNullOrEmpty(paymentMethodSystemName);
var orders = _orderRepository.Table;
if (osIds != null && osIds.Any())
orders = orders.Where(o => osIds.Contains(o.OrderStatusId));
if (psIds != null && psIds.Any())
orders = orders.Where(o => psIds.Contains(o.PaymentStatusId));
if (ssIds != null && ssIds.Any())
orders = orders.Where(o => ssIds.Contains(o.ShippingStatusId));
var manageStockInventoryMethodId = (int)ManageInventoryMethod.ManageStock;
var query = from orderItem in _orderItemRepository.Table
join o in orders on orderItem.OrderId equals o.Id
join p in _productRepository.Table on orderItem.ProductId equals p.Id
join oba in _addressRepository.Table on o.BillingAddressId equals oba.Id
where (storeId == 0 || storeId == o.StoreId) &&
(orderId == 0 || orderId == o.Id) &&
(billingCountryId == 0 || (oba.CountryId == billingCountryId)) &&
(dontSearchPaymentMethods || paymentMethodSystemName == o.PaymentMethodSystemName) &&
(!startTimeUtc.HasValue || startTimeUtc.Value <= o.CreatedOnUtc) &&
(!endTimeUtc.HasValue || endTimeUtc.Value >= o.CreatedOnUtc) &&
!o.Deleted &&
(vendorId == 0 || p.VendorId == vendorId) &&
(productId == 0 || orderItem.ProductId == productId) &&
(warehouseId == 0 ||
//"Use multiple warehouses" enabled
//we search in each warehouse
p.ManageInventoryMethodId == manageStockInventoryMethodId &&
p.UseMultipleWarehouses &&
_productWarehouseInventoryRepository.Table.Any(pwi =>
pwi.ProductId == orderItem.ProductId && pwi.WarehouseId == warehouseId)
||
//"Use multiple warehouses" disabled
//we use standard "warehouse" property
(p.ManageInventoryMethodId != manageStockInventoryMethodId ||
!p.UseMultipleWarehouses) &&
p.WarehouseId == warehouseId) &&
//we do not ignore deleted products when calculating order reports
//(!p.Deleted)
(dontSearchPhone || (!string.IsNullOrEmpty(oba.PhoneNumber) &&
oba.PhoneNumber.Contains(billingPhone))) &&
(dontSearchEmail || (!string.IsNullOrEmpty(oba.Email) && oba.Email.Contains(billingEmail))) &&
(dontSearchLastName ||
(!string.IsNullOrEmpty(oba.LastName) && oba.LastName.Contains(billingLastName))) &&
(dontSearchOrderNotes || _orderNoteRepository.Table.Any(oNote =>
oNote.OrderId == o.Id && oNote.Note.Contains(orderNotes)))
select orderItem;
var productCost = Convert.ToDecimal(await query.SumAsync(orderItem => (decimal?)orderItem.OriginalProductCost * orderItem.Quantity));
var reportSummary = await GetOrderAverageReportLineAsync(
storeId,
vendorId,
productId,
warehouseId,
billingCountryId,
orderId,
paymentMethodSystemName,
osIds,
psIds,
ssIds,
startTimeUtc,
endTimeUtc,
billingPhone,
billingEmail,
billingLastName,
orderNotes);
var profit = reportSummary.SumOrders
- reportSummary.SumShippingExclTax
- reportSummary.OrderPaymentFeeExclTaxSum
- reportSummary.SumTax
- reportSummary.SumRefundedAmount
- productCost;
return profit;
}
#endregion
}