public AmazonOrderSearchQuery(AmazonOrderSearchModel model)
{
SearchText = model.SearchText;
OrderId = model.AmazonOrderId;
DateFrom = model.DateFrom;
DateTo = model.DateTo;
Status = model.ShippingStatus;
}
/// <summary>
/// Get best sellers report
/// </summary>
/// <param name="storeId">Store identifier (orders placed in a specific store); 0 to load all records</param>
/// <param name="vendorId">Vendor identifier; 0 to load all records</param>
/// <param name="categoryId">Category identifier; 0 to load all records</param>
/// <param name="manufacturerId">Manufacturer identifier; 0 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; 0 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 IPagedList <BestsellersReportLine> BestSellersReport(
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,
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 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
//join pc in _productCategoryRepository.Table on p.Id equals pc.ProductId into p_pc from pc in p_pc.DefaultIfEmpty()
//join pm in _productManufacturerRepository.Table on p.Id equals pm.ProductId into p_pm from pm in p_pm.DefaultIfEmpty()
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) &&
//(categoryId == 0 || pc.CategoryId == categoryId) &&
//(manufacturerId == 0 || pm.ManufacturerId == manufacturerId) &&
(categoryId == 0 || p.ProductCategories.Count(pc => pc.CategoryId == categoryId) > 0) &&
(manufacturerId == 0 || p.ProductManufacturers.Count(pm => pm.ManufacturerId == manufacturerId) >
0) &&
(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 BestsellersReportLine
{
ProductId = 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");
}
var result = new PagedList <BestsellersReportLine>(query2, pageIndex, pageSize);
return(result);
}
/// <summary>
/// Get best customers
/// </summary>
/// <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">Order shipment status; null to load all records</param>
/// <param name="orderBy">1 - order by order total, 2 - order by number of orders</param>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <returns>Report</returns>
public virtual IPagedList <BestCustomerReportLine> GetBestCustomersReport(DateTime?createdFromUtc,
DateTime?createdToUtc, OrderStatus?os, PaymentStatus?ps, ShippingStatus?ss, OrderByEnum orderBy,
int pageIndex = 0, int pageSize = 214748364)
{
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 c in _customerRepository.Table
join o in _orderRepository.Table on c.Id equals o.CustomerId
where (!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 &&
!c.Deleted
select new { c, o };
var query2 = from co in query1
group co by co.c.Id into g
select new
{
CustomerId = g.Key,
OrderTotal = g.Sum(x => x.o.OrderTotal),
OrderCount = g.Count()
};
query2 = orderBy switch
{
OrderByEnum.OrderByQuantity => query2.OrderByDescending(x => x.OrderTotal),
OrderByEnum.OrderByTotalAmount => query2.OrderByDescending(x => x.OrderCount),
_ => throw new ArgumentException("Wrong orderBy parameter", nameof(orderBy)),
};
var tmp = new PagedList <dynamic>(query2, pageIndex, pageSize);
return(new PagedList <BestCustomerReportLine>(tmp.Select(x => new BestCustomerReportLine
{
CustomerId = x.CustomerId,
OrderTotal = x.OrderTotal,
OrderCount = x.OrderCount
}),
tmp.PageIndex, tmp.PageSize, tmp.TotalCount));
}
/// <summary>
/// Search orders
/// </summary>
/// <param name="storeId">Store identifier; 0 to load all orders</param>
/// <param name="vendorId">Vendor identifier; null to load all orders</param>
/// <param name="customerId">Customer identifier; 0 to load all orders</param>
/// <param name="productId">Product identifier which was purchased in an order; 0 to load all orders</param>
/// <param name="affiliateId">Affiliate identifier; 0 to load all orders</param>
/// <param name="billingCountryId">Billing country identifier; 0 to load all orders</param>
/// <param name="warehouseId">Warehouse identifier, only orders with products from a specified warehouse will be loaded; 0 to load all orders</param>
/// <param name="paymentMethodSystemName">Payment method system name; null to load all records</param>
/// <param name="createdFromUtc">Created date from (UTC); null to load all records</param>
/// <param name="createdToUtc">Created date to (UTC); null to load all records</param>
/// <param name="os">Order status; null to load all orders</param>
/// <param name="ps">Order payment status; null to load all orders</param>
/// <param name="ss">Order shipment status; null to load all orders</param>
/// <param name="billingEmail">Billing email. Leave empty to load all records.</param>
/// <param name="orderNotes">Search in order notes. Leave empty to load all records.</param>
/// <param name="orderGuid">Search by order GUID (Global unique identifier) or part of GUID. Leave empty to load all orders.</param>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <returns>Orders</returns>
public virtual IPagedList <Order> SearchOrders(int storeId = 0,
int vendorId = 0, int customerId = 0,
int productId = 0, int affiliateId = 0, int warehouseId = 0,
int billingCountryId = 0, string paymentMethodSystemName = null,
DateTime?createdFromUtc = null, DateTime?createdToUtc = null,
OrderStatus?os = null, PaymentStatus?ps = null, ShippingStatus?ss = null,
string billingEmail = null, string orderNotes = null, string orderGuid = null,
int pageIndex = 0, int pageSize = int.MaxValue)
{
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;
if (storeId > 0)
{
query = query.Where(o => o.StoreId == storeId);
}
if (vendorId > 0)
{
query = query
.Where(o => o.OrderItems
.Any(orderItem => orderItem.Product.VendorId == vendorId));
}
if (customerId > 0)
{
query = query.Where(o => o.CustomerId == customerId);
}
if (productId > 0)
{
query = query
.Where(o => o.OrderItems
.Any(orderItem => orderItem.Product.Id == productId));
}
if (warehouseId > 0)
{
var manageStockInventoryMethodId = (int)ManageInventoryMethod.ManageStock;
query = query
.Where(o => o.OrderItems
.Any(orderItem =>
//"Use multiple warehouses" enabled
//we search in each warehouse
(orderItem.Product.ManageInventoryMethodId == manageStockInventoryMethodId &&
orderItem.Product.UseMultipleWarehouses &&
orderItem.Product.ProductWarehouseInventory.Any(pwi => pwi.WarehouseId == warehouseId))
||
//"Use multiple warehouses" disabled
//we use standard "warehouse" property
((orderItem.Product.ManageInventoryMethodId != manageStockInventoryMethodId ||
!orderItem.Product.UseMultipleWarehouses) &&
orderItem.Product.WarehouseId == warehouseId))
);
}
if (billingCountryId > 0)
{
query = query.Where(o => o.BillingAddress != null && o.BillingAddress.CountryId == billingCountryId);
}
if (!String.IsNullOrEmpty(paymentMethodSystemName))
{
query = query.Where(o => o.PaymentMethodSystemName == paymentMethodSystemName);
}
if (affiliateId > 0)
{
query = query.Where(o => o.AffiliateId == affiliateId);
}
if (createdFromUtc.HasValue)
{
query = query.Where(o => createdFromUtc.Value <= o.CreatedOnUtc);
}
if (createdToUtc.HasValue)
{
query = query.Where(o => createdToUtc.Value >= o.CreatedOnUtc);
}
if (orderStatusId.HasValue)
{
query = query.Where(o => orderStatusId.Value == o.OrderStatusId);
}
if (paymentStatusId.HasValue)
{
query = query.Where(o => paymentStatusId.Value == o.PaymentStatusId);
}
if (shippingStatusId.HasValue)
{
query = query.Where(o => shippingStatusId.Value == o.ShippingStatusId);
}
if (!String.IsNullOrEmpty(billingEmail))
{
query = query.Where(o => o.BillingAddress != null && !String.IsNullOrEmpty(o.BillingAddress.Email) && o.BillingAddress.Email.Contains(billingEmail));
}
if (!String.IsNullOrEmpty(orderNotes))
{
query = query.Where(o => o.OrderNotes.Any(on => on.Note.Contains(orderNotes)));
}
query = query.Where(o => !o.Deleted);
query = query.OrderByDescending(o => o.CreatedOnUtc);
if (!String.IsNullOrEmpty(orderGuid))
{
//filter by GUID. Filter in BLL because EF doesn't support casting of GUID to string
var orders = query.ToList();
orders = orders.FindAll(o => o.OrderGuid.ToString().ToLowerInvariant().Contains(orderGuid.ToLowerInvariant()));
return(new PagedList <Order>(orders, pageIndex, pageSize));
}
//database layer paging
return(new PagedList <Order>(query, pageIndex, pageSize));
}
/// <summary>
/// Search orders
/// </summary>
/// <param name="storeId">Store identifier; 0 to load all orders</param>
/// <param name="vendorId">Vendor identifier; null to load all orders</param>
/// <param name="customerId">Customer identifier; 0 to load all orders</param>
/// <param name="productId">Product identifier which was purchased in an order; 0 to load all orders</param>
/// <param name="affiliateId">Affiliate identifier; 0 to load all orders</param>
/// <param name="billingCountryId">Billing country identifier; 0 to load all orders</param>
/// <param name="warehouseId">Warehouse identifier, only orders with products from a specified warehouse will be loaded; 0 to load all orders</param>
/// <param name="paymentMethodSystemName">Payment method system name; null to load all records</param>
/// <param name="createdFromUtc">Created date from (UTC); null to load all records</param>
/// <param name="createdToUtc">Created date to (UTC); null to load all records</param>
/// <param name="os">Order status; null to load all orders</param>
/// <param name="ps">Order payment status; null to load all orders</param>
/// <param name="ss">Order shipment status; null to load all orders</param>
/// <param name="billingEmail">Billing email. Leave empty to load all records.</param>
/// <param name="orderNotes">Search in order notes. Leave empty to load all records.</param>
/// <param name="orderGuid">Search by order GUID (Global unique identifier) or part of GUID. Leave empty to load all orders.</param>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <returns>Orders</returns>
public virtual IPagedList <Order> SearchOrders(string storeId = "",
string vendorId = "", string customerId = "",
string productId = "", string affiliateId = "", string warehouseId = "",
string billingCountryId = "", string paymentMethodSystemName = null,
DateTime?createdFromUtc = null, DateTime?createdToUtc = null,
OrderStatus?os = null, PaymentStatus?ps = null, ShippingStatus?ss = null,
string billingEmail = null, string billingLastName = "", string orderGuid = null,
int pageIndex = 0, int pageSize = int.MaxValue)
{
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;
if (!String.IsNullOrEmpty(storeId))
{
query = query.Where(o => o.StoreId == storeId);
}
if (!String.IsNullOrEmpty(vendorId))
{
query = query
.Where(o => o.OrderItems
.Any(orderItem => orderItem.VendorId == vendorId));
}
if (!String.IsNullOrEmpty(customerId))
{
query = query.Where(o => o.CustomerId == customerId);
}
if (!String.IsNullOrEmpty(productId))
{
query = query
.Where(o => o.OrderItems
.Any(orderItem => orderItem.ProductId == productId));
}
if (!String.IsNullOrEmpty(warehouseId))
{
query = query
.Where(o => o.OrderItems
.Any(orderItem =>
orderItem.WarehouseId == warehouseId
));
}
if (!String.IsNullOrEmpty(billingCountryId))
{
query = query.Where(o => o.BillingAddress != null && o.BillingAddress.CountryId == billingCountryId);
}
if (!String.IsNullOrEmpty(paymentMethodSystemName))
{
query = query.Where(o => o.PaymentMethodSystemName == paymentMethodSystemName);
}
if (!String.IsNullOrEmpty(affiliateId))
{
query = query.Where(o => o.AffiliateId == affiliateId);
}
if (createdFromUtc.HasValue)
{
query = query.Where(o => createdFromUtc.Value <= o.CreatedOnUtc);
}
if (createdToUtc.HasValue)
{
query = query.Where(o => createdToUtc.Value >= o.CreatedOnUtc);
}
if (orderStatusId.HasValue)
{
query = query.Where(o => orderStatusId.Value == o.OrderStatusId);
}
if (paymentStatusId.HasValue)
{
query = query.Where(o => paymentStatusId.Value == o.PaymentStatusId);
}
if (shippingStatusId.HasValue)
{
query = query.Where(o => shippingStatusId.Value == o.ShippingStatusId);
}
if (!String.IsNullOrEmpty(billingEmail))
{
query = query.Where(o => o.BillingAddress != null && !String.IsNullOrEmpty(o.BillingAddress.Email) && o.BillingAddress.Email.Contains(billingEmail));
}
if (!String.IsNullOrEmpty(billingLastName))
{
query = query.Where(o => o.BillingAddress != null && !String.IsNullOrEmpty(o.BillingAddress.LastName) && o.BillingAddress.LastName.Contains(billingLastName));
}
query = query.Where(o => !o.Deleted);
query = query.OrderByDescending(o => o.CreatedOnUtc);
if (!String.IsNullOrEmpty(orderGuid))
{
//filter by GUID. Filter in BLL because EF doesn't support casting of GUID to string
var orders = query.ToList();
orders = orders.FindAll(o => o.OrderGuid.ToString().ToLowerInvariant().Contains(orderGuid.ToLowerInvariant()));
return(new PagedList <Order>(orders, pageIndex, pageSize));
}
//database layer paging
return(new PagedList <Order>(query, pageIndex, pageSize));
}
public IList <Order> GetOrders(IList <int> ids = null, DateTime?createdAtMin = null, DateTime?createdAtMax = null,
int limit = Configurations.DefaultLimit, int page = Configurations.DefaultPageValue, int sinceId = Configurations.DefaultSinceId,
OrderStatus?status = null, PaymentStatus?paymentStatus = null, ShippingStatus?shippingStatus = null, int?customerId = null,
int?storeId = null)
{
var query = GetOrdersQuery(createdAtMin, createdAtMax, status, paymentStatus, shippingStatus, ids, customerId, storeId);
if (sinceId > 0)
{
query = query.Where(order => order.Id > sinceId);
}
return(new ApiList <Order>(query, page - 1, limit));
}
/// <summary>
/// Get best customers
/// </summary>
/// <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">Order shipment status; null to load all records</param>
/// <param name="orderBy">1 - order by order total, 2 - order by number of orders</param>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <returns>Report</returns>
public virtual IPagedList <BestCustomerReportLine> GetBestCustomersReport(DateTime?createdFromUtc,
DateTime?createdToUtc, OrderStatus?os, PaymentStatus?ps, ShippingStatus?ss, int orderBy,
int pageIndex = 0, int pageSize = 214748364)
{
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 (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 (createdFromUtc.HasValue)
{
query = query.Where(o => createdFromUtc.Value <= o.CreatedOnUtc);
}
if (createdToUtc.HasValue)
{
query = query.Where(o => createdToUtc.Value >= o.CreatedOnUtc);
}
var query2 = from co in query
group co by co.CustomerId into g
select new
{
CustomerId = g.Key,
OrderTotal = g.Sum(x => x.OrderTotal),
OrderCount = g.Count()
};
switch (orderBy)
{
case 1:
{
query2 = query2.OrderByDescending(x => x.OrderTotal);
}
break;
case 2:
{
query2 = query2.OrderByDescending(x => x.OrderCount);
}
break;
default:
throw new ArgumentException("Wrong orderBy parameter", "orderBy");
}
var tmp = new PagedList <dynamic>(query2, pageIndex, pageSize);
return(new PagedList <BestCustomerReportLine>(tmp.Select(x => new BestCustomerReportLine
{
CustomerId = x.CustomerId,
OrderTotal = x.OrderTotal,
OrderCount = x.OrderCount
}),
tmp.PageIndex, tmp.PageSize, tmp.TotalCount));
}
/// <summary>
/// Get profit report
/// </summary>
/// <param name="storeId">Store identifier</param>
/// <param name="CustomerId">Customer identifier</param>
/// <param name="vendorId">Vendor identifier</param>
/// <param name="salesEmployeeId">Sales employee identifier</param>
/// <param name="billingCountryId">Billing country identifier; 0 to load all orders</param>
/// <param name="orderId">Order identifier</param>
/// <param name="paymentMethodSystemName">Payment method system name; null to load all records</param>
/// <param name="startTimeUtc">Start date</param>
/// <param name="endTimeUtc">End date</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="billingEmail">Billing email. Leave empty to load all records.</param>
/// <param name="tagid">Tag ident.</param>
/// <returns>Result</returns>
public virtual async Task <decimal> ProfitReport(string storeId = "", string customerId = "", string vendorId = "", string salesEmployeeId = "",
string billingCountryId = "", string orderId = "", string paymentMethodSystemName = null,
OrderStatus?os = null, PaymentStatus?ps = null, ShippingStatus?ss = null,
DateTime?startTimeUtc = null, DateTime?endTimeUtc = null,
string billingEmail = null, string billingLastName = "", string tagid = null)
{
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 (!string.IsNullOrEmpty(storeId))
{
query = query.Where(o => o.StoreId == storeId);
}
if (!string.IsNullOrEmpty(customerId))
{
query = query.Where(o => o.CustomerId == customerId);
}
if (!string.IsNullOrEmpty(orderId))
{
query = query.Where(o => o.Id == orderId);
}
if (!string.IsNullOrEmpty(vendorId))
{
query = query
.Where(o => o.OrderItems
.Any(orderItem => orderItem.VendorId == vendorId));
}
if (!string.IsNullOrEmpty(salesEmployeeId))
{
query = query.Where(o => o.SeId == salesEmployeeId);
}
if (!string.IsNullOrEmpty(billingCountryId))
{
query = query.Where(o => o.BillingAddress != null && o.BillingAddress.CountryId == billingCountryId);
}
if (!string.IsNullOrEmpty(paymentMethodSystemName))
{
query = query.Where(o => o.PaymentMethodSystemName == paymentMethodSystemName);
}
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);
}
if (!string.IsNullOrEmpty(billingEmail))
{
query = query.Where(o => o.BillingAddress != null && !string.IsNullOrEmpty(o.BillingAddress.Email) && o.BillingAddress.Email.Contains(billingEmail));
}
if (!string.IsNullOrEmpty(billingLastName))
{
query = query.Where(o => o.BillingAddress != null && !string.IsNullOrEmpty(o.BillingAddress.LastName) && o.BillingAddress.LastName.Contains(billingLastName));
}
//tag filtering
if (!string.IsNullOrEmpty(tagid))
{
query = query.Where(o => o.OrderTags.Any(y => y == tagid));
}
var query2 = from o in query
from p in o.OrderItems
select p;
var productCost = await query2.SumAsync(orderItem => orderItem.OriginalProductCost *orderItem.Quantity);
var reportSummary = await GetOrderAverageReportLine(
storeId : storeId,
vendorId : vendorId,
salesEmployeeId : salesEmployeeId,
customerId : customerId,
billingCountryId : billingCountryId,
orderId : orderId,
paymentMethodSystemName : paymentMethodSystemName,
os : os,
ps : ps,
ss : ss,
startTimeUtc : startTimeUtc,
endTimeUtc : endTimeUtc,
billingEmail : billingEmail,
tagid : tagid
);
var profit = Convert.ToDecimal(reportSummary.SumOrders - reportSummary.SumShippingExclTax - reportSummary.SumTax - productCost);
return(profit);
}
/// <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);
}
var query = _orderRepository.Collection
.Aggregate()
.Match(filter)
.Unwind <Order, UnwindedOrderItem>(x => x.OrderItems)
.Match(filterItem)
.Group(x => x.OrderItems.ProductId, g => new BestsellersReportLine
{
ProductId = g.Key,
TotalAmount = g.Sum(x => x.OrderItems.PriceExclTax),
TotalQuantity = g.Sum(x => x.OrderItems.Quantity),
});
if (orderBy == 1)
{
query = query.SortByDescending(x => x.TotalQuantity);
}
else
{
query = query.SortByDescending(x => x.TotalAmount);
}
var query2 = await query.ToListAsync();
var result = new PagedList <BestsellersReportLine>(query2, pageIndex, pageSize);
return(result);
}
public IPagedList <BestCustomerReportLine> GetBestCustomersReport(DateTime?createdFromUtc, DateTime?createdToUtc, OrderStatus?os, PaymentStatus?ps, ShippingStatus?ss, int orderBy, int pageIndex = 0, int pageSize = 214748364)
{
int?orderStatusId = null;
if (os.HasValue)
{
orderStatusId = (int)os.Value;
}
int?paymentStatus = null;
if (ps.HasValue)
{
paymentStatus = (int)ps.Value;
}
int?shippingStatus = null;
if (ss.HasValue)
{
shippingStatus = (int)ss.Value;
}
var query1 = from c in _customerRepository.Table
join o in _orderRepository.Table on c.Id equals o.CustomerId
where (!createdFromUtc.HasValue || createdFromUtc.Value <= c.CreatedOnUtc) &&
(!createdToUtc.HasValue || createdToUtc.Value >= c.CreatedOnUtc) &&
(!orderStatusId.HasValue || orderStatusId.Value == o.OrderStatusId) &&
(!paymentStatus.HasValue || paymentStatus.Value == o.PaymentStatusId) &&
(!shippingStatus.HasValue || shippingStatus.Value == o.ShippingStatusId) &&
!o.Deleted &&
!c.Deleted
select new { c, o };
var query2 = from co in query1
group co by co.c.Id into g
select new
{
CustomerId = g.Key,
OrderTotal = g.Sum(x => x.o.OrderTotal),
OrderCount = g.Count()
};
switch (orderBy)
{
case 1:
query2 = query2.OrderByDescending(x => x.OrderTotal);
break;
case 2:
query2 = query2.OrderByDescending(x => x.OrderCount);
break;
default:
throw new ArgumentException("Wrong orderBy parameter", "orderBy");
}
var temp = new PagedList <dynamic>(query2, pageIndex, pageSize);
return(new PagedList <BestCustomerReportLine>(temp.Select(m => new BestCustomerReportLine()
{
CustomerId = m.CustomerId,
OrderTotal = m.OrderTotal,
OrderCount = m.OrderCount
}),
temp.PageIndex, temp.PageSize, temp.TotalCount));
}
/// <summary>
/// Gets the best customers report.
/// </summary>
/// <param name="createdFromUtc">The created from UTC.</param>
/// <param name="createdToUtc">The created to UTC.</param>
/// <param name="os">The os.</param>
/// <param name="ps">The ps.</param>
/// <param name="ss">The ss.</param>
/// <param name="orderBy">The order by.</param>
/// <param name="pageIndex">Index of the page.</param>
/// <param name="pageSize">Size of the page.</param>
/// <returns>System.String.</returns>
/// <remarks>Guidebee IT</remarks>
public static string GetBestCustomersReport(DateTime?createdFromUtc, DateTime?createdToUtc, OrderStatus?os, PaymentStatus?ps, ShippingStatus?ss, int orderBy, int pageIndex = 0, int pageSize = 214748364)
{
var customerReportService = EngineContext.Current.Resolve <ICustomerReportService>();
var bestCustomerReportLineCore = customerReportService.GetBestCustomersReport(createdFromUtc, createdToUtc, os, ps, ss, orderBy, pageIndex, pageSize);
var bestCustomerReportLine = bestCustomerReportLineCore.MapSourcePageList <Nop.Core.Domain.Customers.BestCustomerReportLine, Domain.Customers.BestCustomerReportLine>(AutoMapperConfiguration.Mapper);
return(JsonConvert.SerializeObject(bestCustomerReportLine, Formatting.Indented, new PageListConverter <Domain.Customers.BestCustomerReportLine>()));
}
/// <summary>
/// Get "order by country" report
/// </summary>
/// <param name="storeId">Store identifier</param>
/// <param name="os">Order status</param>
/// <param name="ps">Payment status</param>
/// <param name="ss">Shipping status</param>
/// <param name="startTimeUtc">Start date</param>
/// <param name="endTimeUtc">End date</param>
/// <returns>Result</returns>
public virtual IList <OrderByCountryReportLine> GetCountryReport(int storeId, OrderStatus?os,
PaymentStatus?ps, ShippingStatus?ss, DateTime?startTimeUtc, DateTime?endTimeUtc)
{
#region Extensions by QuanNH
var _storeMappingService = Nop.Core.Infrastructure.EngineContext.Current.Resolve <Nop.Services.Stores.IStoreMappingService>();
storeId = _storeMappingService.CurrentStore();
#endregion
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 = (from oq in query
group oq by oq.BillingAddress.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
})
.ToList();
return(report);
}
private IQueryable <Order> GetOrdersQuery(DateTime?createdAtMin = null, DateTime?createdAtMax = null, OrderStatus?status = null,
PaymentStatus?paymentStatus = null, ShippingStatus?shippingStatus = null, IList <int> ids = null,
int?customerId = null, int?storeId = null)
{
var query = _orderRepository.TableNoTracking;
if (customerId != null)
{
query = query.Where(order => order.CustomerId == customerId);
}
if (ids != null && ids.Count > 0)
{
query = query.Where(c => ids.Contains(c.Id));
}
if (status != null)
{
query = query.Where(order => order.OrderStatusId == (int)status);
}
if (paymentStatus != null)
{
query = query.Where(order => order.PaymentStatusId == (int)paymentStatus);
}
if (shippingStatus != null)
{
query = query.Where(order => order.ShippingStatusId == (int)shippingStatus);
}
query = query.Where(order => !order.Deleted);
if (createdAtMin != null)
{
query = query.Where(order => order.CreatedOnUtc > createdAtMin.Value.ToUniversalTime());
}
if (createdAtMax != null)
{
query = query.Where(order => order.CreatedOnUtc < createdAtMax.Value.ToUniversalTime());
}
if (storeId != null)
{
query = query.Where(order => order.StoreId == storeId);
}
query = query.OrderBy(order => order.Id);
return(query);
}
/// <summary>
/// Gets all order items
/// </summary>
/// <param name="orderId">Order identifier; null to load all records</param>
/// <param name="customerId">Customer identifier; null 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">Order shipment status; null to load all records</param>
/// <param name="loadDownloableProductsOnly">Value indicating whether to load downloadable products only</param>
/// <returns>Orders</returns>
public virtual async Task <IList <OrderItem> > GetAllOrderItems(string orderId,
string customerId, DateTime?createdFromUtc, DateTime?createdToUtc,
OrderStatus?os, PaymentStatus?ps, ShippingStatus?ss,
bool loadDownloableProductsOnly)
{
var querymodel = new GetOrderQuery()
{
CreatedFromUtc = createdFromUtc,
CreatedToUtc = createdToUtc,
CustomerId = customerId,
Os = os,
OrderId = orderId,
Ps = ps,
Ss = ss,
};
var query = await _mediator.Send(querymodel);
var items = new List <OrderItem>();
foreach (var order in await query.ToListAsync())
{
foreach (var orderitem in order.OrderItems)
{
if (loadDownloableProductsOnly)
{
var product = await _productRepository.GetByIdAsync(orderitem.ProductId);
if (product == null)
{
product = await _productDeletedRepository.GetByIdAsync(orderitem.ProductId) as Product;
}
if (product != null)
{
if (product.IsDownload)
{
items.Add(orderitem);
}
}
}
else
{
items.Add(orderitem);
}
}
}
return(items);
}
/// <summary>
/// Bests the sellers report.
/// </summary>
/// <param name="categoryId">The category identifier.</param>
/// <param name="manufacturerId">The manufacturer identifier.</param>
/// <param name="storeId">The store identifier.</param>
/// <param name="vendorId">The vendor identifier.</param>
/// <param name="createdFromUtc">The created from UTC.</param>
/// <param name="createdToUtc">The created to UTC.</param>
/// <param name="os">The os.</param>
/// <param name="ps">The ps.</param>
/// <param name="ss">The ss.</param>
/// <param name="billingCountryId">The billing country identifier.</param>
/// <param name="orderBy">The order by.</param>
/// <param name="pageIndex">Index of the page.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="showHidden">if set to <c>true</c> [show hidden].</param>
/// <returns>System.String.</returns>
/// <remarks>Guidebee IT</remarks>
public static string BestSellersReport(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, int orderBy = 1, int pageIndex = 0, int pageSize = int.MaxValue, bool showHidden = false)
{
var orderReportService = EngineContext.Current.Resolve <IOrderReportService>();
var bestsellersReportLineCore = orderReportService.BestSellersReport(categoryId, manufacturerId, storeId, vendorId, createdFromUtc, createdToUtc, os, ps, ss, billingCountryId, orderBy, pageIndex, pageSize, showHidden);
var bestsellersReportLine = bestsellersReportLineCore.MapSourcePageList <BestsellersReportLine, Domain.Orders.BestsellersReportLine>(AutoMapperConfiguration.Mapper);
return(JsonConvert.SerializeObject(bestsellersReportLine, Formatting.Indented, new PageListConverter <Domain.Orders.BestsellersReportLine>()));
}
/// <summary>
/// Get best sellers report
/// </summary>
/// <param name="storeId">Store identifier</param>
/// <param name="vendorId">Vendor identifier</param>
/// <param name="categoryId">Category identifier</param>
/// <param name="manufacturerId">Manufacturer identifier</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 &= builderItem.Where(x => x.OrderItems.VendorId == vendorId);
}
if (!string.IsNullOrEmpty(storeId))
{
filter &= builder.Where(o => o.StoreId == storeId);
}
if (!string.IsNullOrEmpty(vendorId))
{
filter &= builder
.Where(o => o.OrderItems
.Any(orderItem => orderItem.VendorId == vendorId));
}
if (!string.IsNullOrEmpty(billingCountryId))
{
filter &= builder.Where(o => o.BillingAddress != null && o.BillingAddress.CountryId == billingCountryId);
}
if (orderStatusId.HasValue)
{
filter &= builder.Where(o => o.OrderStatusId == orderStatusId.Value);
}
if (paymentStatusId.HasValue)
{
filter &= builder.Where(o => o.PaymentStatusId == paymentStatusId.Value);
}
if (shippingStatusId.HasValue)
{
filter &= builder.Where(o => o.ShippingStatusId == shippingStatusId.Value);
}
if (createdFromUtc.HasValue)
{
filter &= builder.Where(o => createdFromUtc.Value <= o.CreatedOnUtc);
}
if (createdToUtc.HasValue)
{
filter &= builder.Where(o => createdToUtc.Value >= o.CreatedOnUtc);
}
FilterDefinition <BsonDocument> filterPublishedProduct =
!showHidden ? new BsonDocument("Product.Published", true) : new BsonDocument();
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="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="groupBy">1 - group by product variants, 2 - group by products</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <returns>Result</returns>
public virtual IList <BestsellersReportLine> BestSellersReport(DateTime?startTime,
DateTime?endTime, OrderStatus?os, PaymentStatus?ps, ShippingStatus?ss,
int billingCountryId = 0,
int recordsToReturn = 5, int orderBy = 1, int groupBy = 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 opv in _opvRepository.Table
join o in _orderRepository.Table on opv.OrderId equals o.Id
join pv in _productVariantRepository.Table on opv.ProductVariantId equals pv.Id
join p in _productRepository.Table on pv.ProductId equals p.Id
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) &&
(!p.Deleted) &&
(!pv.Deleted) &&
(billingCountryId == 0 || o.BillingAddress.CountryId == billingCountryId) &&
(showHidden || p.Published) &&
(showHidden || pv.Published)
select opv;
var query2 = groupBy == 1 ?
//group by product variants
from opv in query1
group opv by opv.ProductVariantId into g
select new
{
EntityId = g.Key,
TotalAmount = g.Sum(x => x.PriceExclTax),
TotalQuantity = g.Sum(x => x.Quantity),
}
:
//group by products
from opv in query1
group opv by opv.ProductVariant.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()
{
EntityId = x.EntityId,
TotalAmount = x.TotalAmount,
TotalQuantity = x.TotalQuantity
};
return(reportLine);
}).ToList();
return(result);
}
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);
}
/// <summary>
/// Get order average report
/// </summary>
/// <param name="os">Order status</param>
/// <param name="ps">Payment status</param>
/// <param name="ss">Shipping status</param>
/// <param name="startTimeUtc">Start date</param>
/// <param name="endTimeUtc">End date</param>
/// <param name="billingEmail">Billing email. Leave empty to load all records.</param>
/// <param name="ignoreCancelledOrders">A value indicating whether to ignore cancelled orders</param>
/// <returns>Result</returns>
public virtual OrderAverageReportLine GetOrderAverageReportLine(OrderStatus?os,
PaymentStatus?ps, ShippingStatus?ss, DateTime?startTimeUtc, DateTime?endTimeUtc,
string billingEmail, bool ignoreCancelledOrders = 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 query = _orderRepository.Table;
query = query.Where(o => !o.Deleted);
if (ignoreCancelledOrders)
{
int cancelledOrderStatusId = (int)OrderStatus.Cancelled;
query = query.Where(o => o.OrderStatusId != cancelledOrderStatusId);
}
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);
}
if (!String.IsNullOrEmpty(billingEmail))
{
query = query.Where(o => o.BillingAddress != null && !String.IsNullOrEmpty(o.BillingAddress.Email) && o.BillingAddress.Email.Contains(billingEmail));
}
var item = (from oq in query
group oq by 1 into result
select new { OrderCount = result.Count(), OrderTaxSum = result.Sum(o => o.OrderTax), OrderTotalSum = result.Sum(o => o.OrderTotal) }
).Select(r => new OrderAverageReportLine()
{
SumTax = r.OrderTaxSum, CountOrders = r.OrderCount, SumOrders = r.OrderTotalSum
}).FirstOrDefault();
item = item ?? new OrderAverageReportLine()
{
CountOrders = 0, SumOrders = decimal.Zero, SumTax = decimal.Zero
};
return(item);
}
/// <summary>
/// Get best customers
/// </summary>
/// <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">Order shippment status; null to load all records</param>
/// <param name="orderBy">1 - order by order total, 2 - order by number of orders</param>
/// <returns>Report</returns>
public virtual IList <BestCustomerReportLine> GetBestCustomersReport(DateTime?createdFromUtc,
DateTime?createdToUtc, OrderStatus?os, PaymentStatus?ps, ShippingStatus?ss, int orderBy)
{
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 c in _customerRepository.Table
join o in _orderRepository.Table on c.Id equals o.CustomerId
where (!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) &&
(!c.Deleted)
select new { c, o };
var query2 = from co in query1
group co by co.c.Id into g
select new
{
CustomerId = g.Key,
OrderTotal = g.Sum(x => x.o.OrderTotal),
OrderCount = g.Count()
};
switch (orderBy)
{
case 1:
{
query2 = query2.OrderByDescending(x => x.OrderTotal);
}
break;
case 2:
{
query2 = query2.OrderByDescending(x => x.OrderCount);
}
break;
default:
throw new ArgumentException("Wrong orderBy parameter", "orderBy");
}
//load 20 customers
query2 = query2.Take(20);
var result = query2.ToList().Select(x =>
{
return(new BestCustomerReportLine()
{
CustomerId = x.CustomerId,
OrderTotal = x.OrderTotal,
OrderCount = x.OrderCount
});
}).ToList();
return(result);
}
/// <summary>
/// Search orders
/// </summary>
/// <param name="startTime">Order start time; null to load all orders</param>
/// <param name="endTime">Order end time; null to load all orders</param>
/// <param name="os">Order status; null to load all orders</param>
/// <param name="ps">Order payment status; null to load all orders</param>
/// <param name="ss">Order shippment status; null to load all orders</param>
/// <param name="billingEmail">Billing email. Leave empty to load all records.</param>
/// <param name="orderGuid">Search by order GUID (Global unique identifier) or part of GUID. Leave empty to load all orders.</param>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <returns>Order collection</returns>
public virtual IPagedList <Order> SearchOrders(DateTime?startTime, DateTime?endTime,
OrderStatus?os, PaymentStatus?ps, ShippingStatus?ss, string billingEmail,
string orderGuid, int pageIndex, int pageSize)
{
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;
if (startTime.HasValue)
{
query = query.Where(o => startTime.Value <= o.CreatedOnUtc);
}
if (endTime.HasValue)
{
query = query.Where(o => endTime.Value >= o.CreatedOnUtc);
}
if (orderStatusId.HasValue)
{
query = query.Where(o => orderStatusId.Value == o.OrderStatusId);
}
if (paymentStatusId.HasValue)
{
query = query.Where(o => paymentStatusId.Value == o.PaymentStatusId);
}
if (shippingStatusId.HasValue)
{
query = query.Where(o => shippingStatusId.Value == o.ShippingStatusId);
}
if (!String.IsNullOrEmpty(billingEmail))
{
query = query.Where(o => o.BillingAddress != null && !String.IsNullOrEmpty(o.BillingAddress.Email) && o.BillingAddress.Email.Contains(billingEmail));
}
query = query.Where(o => !o.Deleted);
query = query.OrderByDescending(o => o.CreatedOnUtc);
var orders = query.ToList();
//filter by GUID. Filter in BLL because EF doesn't support casting of GUID to string
if (!String.IsNullOrEmpty(orderGuid))
{
orders = orders.FindAll(o => o.OrderGuid.ToString().ToLowerInvariant().Contains(orderGuid.ToLowerInvariant()));
}
return(new PagedList <Order>(orders, pageIndex, pageSize));
}
private IQueryable <Order> GetOrdersQuery(DateTime?createdAtMin = null, DateTime?createdAtMax = null, OrderStatus?status = null,
PaymentStatus?paymentStatus = null, ShippingStatus?shippingStatus = null, IList <int> ids = null,
int?customerId = null, int?storeId = null)
{
var query = _orderRepository.Table;
if (customerId != null)
{
query = query.Where(order => order.CustomerId == customerId);
}
if (ids != null && ids.Count > 0)
{
query = query.Where(c => ids.Contains(c.Id));
}
if (status != null)
{
query = query.Where(order => order.OrderStatusId == (int)status);
}
if (paymentStatus != null)
{
query = query.Where(order => order.PaymentStatusId == (int)paymentStatus);
}
if (shippingStatus != null)
{
query = query.Where(order => order.ShippingStatusId == (int)shippingStatus);
}
query = query.Where(order => !order.Deleted);
if (createdAtMin != null)
{
query = query.Where(order => order.CreatedOnUtc > createdAtMin.Value.ToUniversalTime());
}
if (createdAtMax != null)
{
query = query.Where(order => order.CreatedOnUtc < createdAtMax.Value.ToUniversalTime());
}
if (storeId != null)
{
query = query.Where(order => order.StoreId == storeId);
}
query = query.OrderBy(order => order.Id);
//query = query.Include(c => c.Customer);
//query = query.Include(c => c.BillingAddress);
//query = query.Include(c => c.ShippingAddress);
//query = query.Include(c => c.PickupAddress);
//query = query.Include(c => c.RedeemedRewardPointsEntry);
//query = query.Include(c => c.DiscountUsageHistory);
//query = query.Include(c => c.GiftCardUsageHistory);
//query = query.Include(c => c.OrderNotes);
//query = query.Include(c => c.OrderItems);
//query = query.Include(c => c.Shipments);
return(query);
}
/// <summary>
/// Gets all order product variants
/// </summary>
/// <param name="orderId">Order identifier; null to load all records</param>
/// <param name="customerId">Customer identifier; null to load all records</param>
/// <param name="startTime">Order start time; null to load all records</param>
/// <param name="endTime">Order end time; 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">Order shippment status; null to load all records</param>
/// <param name="loadDownloableProductsOnly">Value indicating whether to load downloadable products only</param>
/// <returns>Order collection</returns>
public virtual IList <OrderProductVariant> GetAllOrderProductVariants(int?orderId,
int?customerId, DateTime?startTime, DateTime?endTime,
OrderStatus?os, PaymentStatus?ps, ShippingStatus?ss,
bool loadDownloableProductsOnly)
{
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 = from opv in _opvRepository.Table
join o in _orderRepository.Table on opv.OrderId equals o.Id
join pv in _pvRepository.Table on opv.ProductVariantId equals pv.Id
where (!orderId.HasValue || orderId.Value == 0 || orderId == o.Id) &&
(!customerId.HasValue || customerId.Value == 0 || customerId == o.CustomerId) &&
(!startTime.HasValue || startTime.Value <= o.CreatedOnUtc) &&
(!endTime.HasValue || endTime.Value >= o.CreatedOnUtc) &&
(!orderStatusId.HasValue || orderStatusId == o.OrderStatusId) &&
(!paymentStatusId.HasValue || paymentStatusId.Value == o.PaymentStatusId) &&
(!shippingStatusId.HasValue || shippingStatusId.Value == o.ShippingStatusId) &&
(!loadDownloableProductsOnly || pv.IsDownload) &&
!o.Deleted
orderby o.CreatedOnUtc descending, opv.Id
select opv;
var orderProductVariants = query.ToList();
return(orderProductVariants);
}
/// <summary>
/// Gets all order items
/// </summary>
/// <param name="orderId">Order identifier; null to load all records</param>
/// <param name="customerId">Customer identifier; null 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">Order shipment status; null to load all records</param>
/// <param name="loadDownloableProductsOnly">Value indicating whether to load downloadable products only</param>
/// <returns>Orders</returns>
public virtual IList <OrderItem> GetAllOrderItems(string orderId,
string customerId, DateTime?createdFromUtc, DateTime?createdToUtc,
OrderStatus?os, PaymentStatus?ps, ShippingStatus?ss,
bool loadDownloableProductsOnly)
{
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 filter = builder.Where(x => true);
if (!String.IsNullOrEmpty(orderId))
{
filter = filter & builder.Where(o => o.Id == orderId);
}
if (!String.IsNullOrEmpty(customerId))
{
filter = filter & builder.Where(o => o.CustomerId == customerId);
}
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 => o.CreatedOnUtc >= createdFromUtc.Value);
}
if (createdToUtc.HasValue)
{
filter = filter & builder.Where(o => o.CreatedOnUtc <= createdToUtc.Value);
}
var query = _orderRepository.Collection.Aggregate().Match(filter).Unwind <Order, UnwindOrderItem>(x => x.OrderItems).ToListAsync().Result;
var items = new List <OrderItem>();
foreach (var item in query)
{
if (loadDownloableProductsOnly)
{
var product = _productRepository.GetById(item.OrderItems.ProductId);
if (product == null)
{
product = _productDeletedRepository.GetById(item.OrderItems.ProductId) as Product;
}
if (product != null)
{
if (product.IsDownload)
{
items.Add(item.OrderItems);
}
}
}
else
{
items.Add(item.OrderItems);
}
}
return(items);
}
/// <summary>
/// Get order average report
/// </summary>
/// <param name="storeId">Store identifier; pass 0 to ignore this parameter</param>
/// <param name="vendorId">Vendor identifier; pass 0 to ignore this parameter</param>
/// <param name="billingCountryId">Billing country identifier; 0 to load all orders</param>
/// <param name="orderId">Order identifier; pass 0 to ignore this parameter</param>
/// <param name="paymentMethodSystemName">Payment method system name; null to load all records</param>
/// <param name="os">Order status</param>
/// <param name="ps">Payment status</param>
/// <param name="ss">Shipping status</param>
/// <param name="startTimeUtc">Start date</param>
/// <param name="endTimeUtc">End date</param>
/// <param name="billingEmail">Billing email. Leave empty to load all records.</param>
/// <param name="billingLastName">Billing last name. Leave empty to load all records.</param>
/// <param name="ignoreCancelledOrders">A value indicating whether to ignore cancelled orders</param>
/// <param name="orderNotes">Search in order notes. Leave empty to load all records.</param>
/// <returns>Result</returns>
public virtual OrderAverageReportLine GetOrderAverageReportLine(int storeId = 0,
int vendorId = 0, int billingCountryId = 0,
int orderId = 0, string paymentMethodSystemName = null,
OrderStatus?os = null, PaymentStatus?ps = null, ShippingStatus?ss = null,
DateTime?startTimeUtc = null, DateTime?endTimeUtc = null,
string billingEmail = null, string billingLastName = "",
bool ignoreCancelledOrders = false, string orderNotes = null)
{
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 (orderId > 0)
{
query = query.Where(o => o.Id == orderId);
}
if (vendorId > 0)
{
query = query
.Where(o => o.OrderItems
.Any(orderItem => orderItem.Product.VendorId == vendorId));
}
if (billingCountryId > 0)
{
query = query.Where(o => o.BillingAddress != null && o.BillingAddress.CountryId == billingCountryId);
}
if (ignoreCancelledOrders)
{
var cancelledOrderStatusId = (int)OrderStatus.Cancelled;
query = query.Where(o => o.OrderStatusId != cancelledOrderStatusId);
}
if (!String.IsNullOrEmpty(paymentMethodSystemName))
{
query = query.Where(o => o.PaymentMethodSystemName == paymentMethodSystemName);
}
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);
}
if (!String.IsNullOrEmpty(billingEmail))
{
query = query.Where(o => o.BillingAddress != null && !String.IsNullOrEmpty(o.BillingAddress.Email) && o.BillingAddress.Email.Contains(billingEmail));
}
if (!String.IsNullOrEmpty(billingLastName))
{
query = query.Where(o => o.BillingAddress != null && !String.IsNullOrEmpty(o.BillingAddress.LastName) && o.BillingAddress.LastName.Contains(billingLastName));
}
if (!String.IsNullOrEmpty(orderNotes))
{
query = query.Where(o => o.OrderNotes.Any(on => on.Note.Contains(orderNotes)));
}
var item = (from oq in query
group oq by 1 into result
select new
{
OrderCount = result.Count(),
OrderShippingExclTaxSum = result.Sum(o => o.OrderShippingExclTax),
OrderTaxSum = result.Sum(o => o.OrderTax),
OrderTotalSum = result.Sum(o => o.OrderTotal)
}
).Select(r => new OrderAverageReportLine
{
CountOrders = r.OrderCount,
SumShippingExclTax = r.OrderShippingExclTaxSum,
SumTax = r.OrderTaxSum,
SumOrders = r.OrderTotalSum
})
.FirstOrDefault();
item = item ?? new OrderAverageReportLine
{
CountOrders = 0,
SumShippingExclTax = decimal.Zero,
SumTax = decimal.Zero,
SumOrders = decimal.Zero,
};
return(item);
}
/// <summary>
/// Gets all order items
/// </summary>
/// <param name="orderId">Order identifier; null to load all records</param>
/// <param name="customerId">Customer identifier; null 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">Order shipment status; null to load all records</param>
/// <param name="loadDownloableProductsOnly">Value indicating whether to load downloadable products only</param>
/// <returns>Orders</returns>
public virtual IList <OrderItem> GetAllOrderItems(int?orderId,
int?customerId, DateTime?createdFromUtc, DateTime?createdToUtc,
OrderStatus?os, PaymentStatus?ps, ShippingStatus?ss,
bool loadDownloableProductsOnly)
{
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 = 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 (!orderId.HasValue || orderId.Value == 0 || orderId == o.Id) &&
(!customerId.HasValue || customerId.Value == 0 || customerId == o.CustomerId) &&
(!createdFromUtc.HasValue || createdFromUtc.Value <= o.CreatedOnUtc) &&
(!createdToUtc.HasValue || createdToUtc.Value >= o.CreatedOnUtc) &&
(!orderStatusId.HasValue || orderStatusId == o.OrderStatusId) &&
(!paymentStatusId.HasValue || paymentStatusId.Value == o.PaymentStatusId) &&
(!shippingStatusId.HasValue || shippingStatusId.Value == o.ShippingStatusId) &&
(!loadDownloableProductsOnly || p.IsDownload) &&
!o.Deleted
orderby o.CreatedOnUtc descending, orderItem.Id
select orderItem;
var orderItems = query.ToList();
return(orderItems);
}
/// <summary>
/// Get profit report
/// </summary>
/// <param name="storeId">Store identifier; pass 0 to ignore this parameter</param>
/// <param name="vendorId">Vendor identifier; pass 0 to ignore this parameter</param>
/// <param name="orderId">Order identifier; pass 0 to ignore this parameter</param>
/// <param name="billingCountryId">Billing country identifier; 0 to load all orders</param>
/// <param name="paymentMethodSystemName">Payment method system name; null to load all records</param>
/// <param name="startTimeUtc">Start date</param>
/// <param name="endTimeUtc">End date</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="billingEmail">Billing email. Leave empty to load all records.</param>
/// <param name="billingLastName">Billing last name. Leave empty to load all records.</param>
/// <param name="orderNotes">Search in order notes. Leave empty to load all records.</param>
/// <returns>Result</returns>
public virtual decimal ProfitReport(int storeId = 0, int vendorId = 0,
int billingCountryId = 0, int orderId = 0, string paymentMethodSystemName = null,
OrderStatus?os = null, PaymentStatus?ps = null, ShippingStatus?ss = null,
DateTime?startTimeUtc = null, DateTime?endTimeUtc = null,
string billingEmail = null, string billingLastName = "", string orderNotes = null)
{
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;
}
//We cannot use String.IsNullOrEmpty() in SQL Compact
bool dontSearchEmail = String.IsNullOrEmpty(billingEmail);
//We cannot use String.IsNullOrEmpty() in SQL Compact
bool dontSearchLastName = String.IsNullOrEmpty(billingLastName);
//We cannot use String.IsNullOrEmpty() in SQL Compact
bool dontSearchOrderNotes = String.IsNullOrEmpty(orderNotes);
//We cannot use String.IsNullOrEmpty() in SQL Compact
bool dontSearchPaymentMethods = String.IsNullOrEmpty(paymentMethodSystemName);
var query = from orderItem in _orderItemRepository.Table
join o in _orderRepository.Table on orderItem.OrderId equals o.Id
where (storeId == 0 || storeId == o.StoreId) &&
(orderId == 0 || orderId == o.Id) &&
(billingCountryId == 0 || (o.BillingAddress != null && o.BillingAddress.CountryId == billingCountryId)) &&
(dontSearchPaymentMethods || paymentMethodSystemName == o.PaymentMethodSystemName) &&
(!startTimeUtc.HasValue || startTimeUtc.Value <= o.CreatedOnUtc) &&
(!endTimeUtc.HasValue || endTimeUtc.Value >= o.CreatedOnUtc) &&
(!orderStatusId.HasValue || orderStatusId == o.OrderStatusId) &&
(!paymentStatusId.HasValue || paymentStatusId == o.PaymentStatusId) &&
(!shippingStatusId.HasValue || shippingStatusId == o.ShippingStatusId) &&
(!o.Deleted) &&
(vendorId == 0 || orderItem.Product.VendorId == vendorId) &&
//we do not ignore deleted products when calculating order reports
//(!p.Deleted)
(dontSearchEmail || (o.BillingAddress != null && !String.IsNullOrEmpty(o.BillingAddress.Email) && o.BillingAddress.Email.Contains(billingEmail))) &&
(dontSearchLastName || (o.BillingAddress != null && !String.IsNullOrEmpty(o.BillingAddress.LastName) && o.BillingAddress.LastName.Contains(billingLastName))) &&
(dontSearchOrderNotes || o.OrderNotes.Any(oNote => oNote.Note.Contains(orderNotes)))
select orderItem;
var productCost = Convert.ToDecimal(query.Sum(orderItem => (decimal?)orderItem.OriginalProductCost * orderItem.Quantity));
var reportSummary = GetOrderAverageReportLine(
storeId: storeId,
vendorId: vendorId,
billingCountryId: billingCountryId,
orderId: orderId,
paymentMethodSystemName: paymentMethodSystemName,
os: os,
ps: ps,
ss: ss,
startTimeUtc: startTimeUtc,
endTimeUtc: endTimeUtc,
billingEmail: billingEmail,
billingLastName: billingLastName,
orderNotes: orderNotes);
var profit = reportSummary.SumOrders - reportSummary.SumShippingExclTax - reportSummary.SumTax - productCost;
return(profit);
}
/// <summary>
/// Search orders
/// </summary>
/// <param name="storeId">Store identifier; 0 to load all orders</param>
/// <param name="vendorId">Vendor identifier; null to load all orders</param>
/// <param name="customerId">Customer identifier; 0 to load all orders</param>
/// <param name="productId">Product identifier which was purchased in an order; 0 to load all orders</param>
/// <param name="affiliateId">Affiliate identifier; 0 to load all orders</param>
/// <param name="warehouseId">Warehouse identifier, only orders with products from a specified warehouse will be loaded; 0 to load all orders</param>
/// <param name="billingCountryId">Billing country identifier; 0 to load all orders</param>
/// <param name="ownerId">Owner identifier</param>
/// <param name="salesemployeeId">Sales employee identifier</param>
/// <param name="paymentMethodSystemName">Payment method system name; null to load all records</param>
/// <param name="createdFromUtc">Created date from (UTC); null to load all records</param>
/// <param name="createdToUtc">Created date to (UTC); null to load all records</param>
/// <param name="os">Order status; null to load all orders</param>
/// <param name="ps">Order payment status; null to load all orders</param>
/// <param name="ss">Order shipment status; null to load all orders</param>
/// <param name="billingEmail">Billing email. Leave empty to load all records.</param>
/// <param name="orderNotes">Search in order notes. Leave empty to load all records.</param>
/// <param name="orderGuid">Search by order GUID (Global unique identifier) or part of GUID. Leave empty to load all orders.</param>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="orderTagId">Order tag identifier</param>
/// <returns>Orders</returns>
public virtual async Task <IPagedList <Order> > SearchOrders(string storeId = "",
string vendorId = "", string customerId = "",
string productId = "", string affiliateId = "", string warehouseId = "",
string billingCountryId = "", string ownerId = "", string salesemployeeId = "", string paymentMethodSystemName = null,
DateTime?createdFromUtc = null, DateTime?createdToUtc = null,
int?os = null, PaymentStatus?ps = null, ShippingStatus?ss = null,
string billingEmail = null, string billingLastName = "", string orderGuid = null,
string orderCode = null, int pageIndex = 0, int pageSize = int.MaxValue, string orderTagId = "")
{
var querymodel = new GetOrderQuery()
{
AffiliateId = affiliateId,
BillingCountryId = billingCountryId,
BillingEmail = billingEmail,
BillingLastName = billingLastName,
CreatedFromUtc = createdFromUtc,
CreatedToUtc = createdToUtc,
CustomerId = customerId,
OrderGuid = orderGuid,
OrderCode = orderCode,
Os = os,
PageIndex = pageIndex,
PageSize = pageSize,
PaymentMethodSystemName = paymentMethodSystemName,
ProductId = productId,
Ps = ps,
Ss = ss,
StoreId = storeId,
VendorId = vendorId,
WarehouseId = warehouseId,
OrderTagId = orderTagId,
OwnerId = ownerId,
SalesEmployeeId = salesemployeeId
};
var query = await _mediator.Send(querymodel);
return(await PagedList <Order> .Create(query, pageIndex, pageSize));
}
/// <summary>
/// Get "order by country" report
/// </summary>
/// <param name="storeId">Store identifier</param>
/// <param name="os">Order status</param>
/// <param name="ps">Payment status</param>
/// <param name="ss">Shipping status</param>
/// <param name="startTimeUtc">Start date</param>
/// <param name="endTimeUtc">End date</param>
/// <returns>Result</returns>
public virtual async Task <IList <OrderByCountryReportLine> > GetCountryReport(string storeId, int?os,
PaymentStatus?ps, ShippingStatus?ss, DateTime?startTimeUtc, DateTime?endTimeUtc)
{
var query = _orderRepository.Table;
query = query.Where(o => !o.Deleted);
if (!String.IsNullOrEmpty(storeId))
{
query = query.Where(o => o.StoreId == storeId);
}
if (os.HasValue)
{
query = query.Where(o => o.OrderStatusId == os.Value);
}
if (ps.HasValue)
{
query = query.Where(o => o.PaymentStatusId == ps.Value);
}
if (ss.HasValue)
{
query = query.Where(o => o.ShippingStatusId == ss.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 = (from oq in query
group oq by oq.BillingAddress.CountryId into result
select new
{
CountryId = result.Key,
TotalOrders = result.Count(),
SumOrders = result.Sum(o => o.OrderTotal / o.CurrencyRate)
}
)
.OrderByDescending(x => x.SumOrders)
.Select(r => new OrderByCountryReportLine
{
CountryId = r.CountryId,
TotalOrders = r.TotalOrders,
SumOrders = r.SumOrders
});
return(await report.ToListAsync());
}
/// <summary>
/// Get "order by country" report
/// </summary>
/// <param name="storeId">Store identifier</param>
/// <param name="os">Order status</param>
/// <param name="ps">Payment status</param>
/// <param name="ss">Shipping status</param>
/// <param name="startTimeUtc">Start date</param>
/// <param name="endTimeUtc">End date</param>
/// <returns>Result</returns>
public virtual IList <OrderByCountryReportLine> GetCountryReport(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 = (from oq in query
group oq by oq.BillingAddress.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
}).ToList();
return(report);
}
/// <summary>
/// Get order average report
/// </summary>
/// <param name="storeId">Store identifier</param>
/// <param name="customerId">Customer identifier</param>
/// <param name="vendorId">Vendor identifier</param>
/// <param name="salesEmployeeId">Sales employee identifier</param>
/// <param name="billingCountryId">Billing country identifier</param>
/// <param name="orderId">Order identifier</param>
/// <param name="paymentMethodSystemName">Payment method system name; null to load all records</param>
/// <param name="os">Order status</param>
/// <param name="ps">Payment status</param>
/// <param name="ss">Shipping status</param>
/// <param name="startTimeUtc">Start date</param>
/// <param name="endTimeUtc">End date</param>
/// <param name="billingEmail">Billing email. Leave empty to load all records.</param>
/// <param name="ignoreCancelledOrders">A value indicating whether to ignore cancelled orders</param>
/// <param name="tagid">Tag ident.</param>
/// <returns>Result</returns>
public virtual async Task <OrderAverageReportLine> GetOrderAverageReportLine(string storeId = "", string customerId = "",
string vendorId = "", string salesEmployeeId = "", string billingCountryId = "",
string orderId = "", string paymentMethodSystemName = null,
OrderStatus?os = null, PaymentStatus?ps = null, ShippingStatus?ss = null,
DateTime?startTimeUtc = null, DateTime?endTimeUtc = null,
string billingEmail = null, string billingLastName = "", bool ignoreCancelledOrders = false,
string tagid = null)
{
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 filter = builder.Where(o => !o.Deleted);
if (!string.IsNullOrEmpty(storeId))
{
filter &= builder.Where(o => o.StoreId == storeId);
}
if (!string.IsNullOrEmpty(orderId))
{
filter &= builder.Where(o => o.Id == orderId);
}
if (!string.IsNullOrEmpty(customerId))
{
filter &= builder.Where(o => o.CustomerId == customerId);
}
if (!string.IsNullOrEmpty(vendorId))
{
filter &= builder
.Where(o => o.OrderItems
.Any(orderItem => orderItem.VendorId == vendorId));
}
if (!string.IsNullOrEmpty(salesEmployeeId))
{
filter &= builder.Where(o => o.SeId == salesEmployeeId);
}
if (!string.IsNullOrEmpty(billingCountryId))
{
filter &= builder.Where(o => o.BillingAddress != null && o.BillingAddress.CountryId == billingCountryId);
}
if (ignoreCancelledOrders)
{
var cancelledOrderStatusId = (int)OrderStatus.Cancelled;
filter &= builder.Where(o => o.OrderStatusId != cancelledOrderStatusId);
}
if (!string.IsNullOrEmpty(paymentMethodSystemName))
{
filter &= builder.Where(o => o.PaymentMethodSystemName == paymentMethodSystemName);
}
if (orderStatusId.HasValue)
{
filter &= builder.Where(o => o.OrderStatusId == orderStatusId.Value);
}
if (paymentStatusId.HasValue)
{
filter &= builder.Where(o => o.PaymentStatusId == paymentStatusId.Value);
}
if (shippingStatusId.HasValue)
{
filter &= builder.Where(o => o.ShippingStatusId == shippingStatusId.Value);
}
if (startTimeUtc.HasValue)
{
filter &= builder.Where(o => startTimeUtc.Value <= o.CreatedOnUtc);
}
if (endTimeUtc.HasValue)
{
filter &= builder.Where(o => endTimeUtc.Value >= o.CreatedOnUtc);
}
if (!string.IsNullOrEmpty(billingEmail))
{
filter &= builder.Where(o => o.BillingAddress != null && !String.IsNullOrEmpty(o.BillingAddress.Email) && o.BillingAddress.Email.Contains(billingEmail));
}
if (!string.IsNullOrEmpty(billingLastName))
{
filter &= builder.Where(o => o.BillingAddress != null && !String.IsNullOrEmpty(o.BillingAddress.LastName) && o.BillingAddress.LastName.Contains(billingLastName));
}
//tag filtering
if (!string.IsNullOrEmpty(tagid))
{
filter &= builder.Where(o => o.OrderTags.Any(y => y == tagid));
}
var query = await _orderRepository.Collection
.Aggregate()
.Match(filter)
.Group(x => 1, g => new OrderAverageReportLine {
CountOrders = g.Count(),
SumShippingExclTax = g.Sum(o => o.OrderShippingExclTax / o.CurrencyRate),
SumTax = g.Sum(o => o.OrderTax / o.CurrencyRate),
SumOrders = g.Sum(o => o.OrderTotal / o.CurrencyRate)
}).ToListAsync();
var item2 = query.Count() > 0 ? query.FirstOrDefault() : new OrderAverageReportLine {
CountOrders = 0,
SumShippingExclTax = decimal.Zero,
SumTax = decimal.Zero,
SumOrders = decimal.Zero,
};
return(item2);
}
