Esempio n. 1
0
        /// <summary>
        /// Applies a selection for top customers report.
        /// </summary>
        /// <param name="query">Customer query to select the report from.</param>
        /// <param name="startTime">Defines start time order filter.</param>
        /// <param name="endTime">Defines end time order filter.</param>
        /// <param name="orderStatus">Defines order filter by <see cref="OrderStatus"/>.</param>
        /// <param name="paymentStatus">Defines order filter by <see cref="PaymentStatus"/>.</param>
        /// <param name="shippingStatus">Defines order filter by <see cref="ShippingStatus"/>.</param>
        /// <param name="sorting">Defines sorting by <see cref="ReportSorting"/>.</param>
        /// <returns>Query of top customers report.</returns>
        public static IQueryable <TopCustomerReportLine> SelectAsTopCustomerReportLine(
            this IQueryable <Customer> query,
            DateTime?startTime            = null,
            DateTime?endTime              = null,
            OrderStatus?orderStatus       = null,
            PaymentStatus?paymentStatus   = null,
            ShippingStatus?shippingStatus = null,
            ReportSorting sorting         = ReportSorting.ByQuantityDesc)
        {
            Guard.NotNull(query, nameof(query));

            // TODO: (mh) (core) Bad API-design: a method named .SelectAs...() indicates that only projection
            // is applied (...select new TopCustomerReportLine {}). But this method does also filtering. That is
            // the ONE thing we wanted to avoid: monolithic code. This method needs a split-up: a filter part
            // and a projection part. Details with MC.

            var orderStatusId    = orderStatus.HasValue ? (int)orderStatus.Value : (int?)null;
            var paymentStatusId  = paymentStatus.HasValue ? (int)paymentStatus.Value : (int?)null;
            var shippingStatusId = shippingStatus.HasValue ? (int)shippingStatus.Value : (int?)null;

            var db = query.GetDbContext <SmartDbContext>();

            var query2 =
                from c in query.AsNoTracking()
                join o in db.Orders.AsNoTracking() on c.Id equals o.CustomerId
                where (!startTime.HasValue || startTime.Value <= o.CreatedOnUtc) &&
                (!endTime.HasValue || endTime.Value >= o.CreatedOnUtc) &&
                (!orderStatusId.HasValue || orderStatusId == o.OrderStatusId) &&
                (!paymentStatusId.HasValue || paymentStatusId == o.PaymentStatusId) &&
                (!shippingStatusId.HasValue || shippingStatusId == o.ShippingStatusId)
                select new { c, o };

            var groupedQuery =
                from co in query2
                group co by co.c.Id into g
                select new TopCustomerReportLine
            {
                CustomerId = g.Key,
                OrderTotal = g.Sum(x => x.o.OrderTotal),
                OrderCount = g.Count()
            };

            groupedQuery = sorting switch
            {
                ReportSorting.ByAmountAsc => groupedQuery.OrderBy(x => x.OrderTotal),
                ReportSorting.ByAmountDesc => groupedQuery.OrderByDescending(x => x.OrderTotal),
                ReportSorting.ByQuantityAsc => groupedQuery.OrderBy(x => x.OrderCount).ThenByDescending(x => x.OrderTotal),
                _ => groupedQuery.OrderByDescending(x => x.OrderCount).ThenByDescending(x => x.OrderTotal),
            };

            return(groupedQuery);
        }
Esempio n. 2
0
        // TODO: (ms) (core) This method seems to be not needed
        //public virtual async Task<OrderAverageReportLine> GetOrderAverageReportLineAsync(
        //    int storeId,
        //    int[] orderStatusIds,
        //    int[] paymentStatusIds,
        //    int[] shippingStatusIds,
        //    DateTime? startTimeUtc,
        //    DateTime? endTimeUtc,
        //    string billingEmail,
        //    bool ignoreCancelledOrders = false)
        //{
        //    //var query = _db.Orders
        //    //    .ApplyStandardFilter(storeId: storeId)
        //    //    .ApplyDateFilter(startTimeUtc, endTimeUtc)
        //    //    .ApplyBillingFilter(billingEmail)
        //    //    .ApplyStatusFilter(orderStatusIds, paymentStatusIds, shippingStatusIds);

        //}

        // TODO: (ms) (core) refactor method parameters -> qury extensions - reutrns query
        // apply status filter
        // apply shipping filer
        // apply time filter
        public virtual Task <IPagedList <BestSellersReportLine> > BestSellersReportAsync(
            int storeId,
            DateTime?startTime,
            DateTime?endTime,
            int?orderStatusId     = null,
            int?paymentStatusId   = null,
            int?shippingStatusId  = null,
            int?billingCountryId  = null,
            int pageIndex         = 0,
            int pageSize          = int.MaxValue,
            ReportSorting sorting = ReportSorting.ByQuantityDesc,
            bool showHidden       = false)
        {
            var query =
                from orderItem in _db.OrderItems
                join o in _db.Orders.AsNoTracking() on orderItem.OrderId equals o.Id
                join p in _db.Products.AsNoTracking() on orderItem.ProductId equals p.Id
                where (storeId == 0 || storeId == o.StoreId) &&
                (!startTime.HasValue || startTime.Value <= o.CreatedOnUtc) &&
                (!endTime.HasValue || endTime.Value >= o.CreatedOnUtc) &&
                (!orderStatusId.HasValue || orderStatusId == o.OrderStatusId) &&
                (!paymentStatusId.HasValue || paymentStatusId == o.PaymentStatusId) &&
                (!shippingStatusId.HasValue || shippingStatusId == o.ShippingStatusId) &&
                (billingCountryId == 0 || o.BillingAddress.CountryId == billingCountryId) &&
                (!p.IsSystemProduct) &&
                (showHidden || p.Published)
                select orderItem;

            // Group by product ID.
            var groupedQuery =
                from orderItem in query
                group orderItem by orderItem.ProductId into g
                select new BestSellersReportLine
            {
                ProductId     = g.Key,
                TotalAmount   = g.Sum(x => x.PriceExclTax),
                TotalQuantity = g.Sum(x => x.Quantity)
            };

            groupedQuery = sorting switch
            {
                ReportSorting.ByAmountAsc => groupedQuery.OrderBy(x => x.TotalAmount),
                ReportSorting.ByAmountDesc => groupedQuery.OrderByDescending(x => x.TotalAmount),
                ReportSorting.ByQuantityAsc => groupedQuery.OrderBy(x => x.TotalQuantity).ThenByDescending(x => x.TotalAmount),
                _ => groupedQuery.OrderByDescending(x => x.TotalQuantity).ThenByDescending(x => x.TotalAmount),
            };

            return(groupedQuery.ToPagedList(pageIndex, pageSize).LoadAsync());
        }
        /// <summary>
        /// Applies a selection for bestsellers report.
        /// </summary>
        /// <param name="query">Order item query to select report from.</param>
        /// <param name="sorting">Sorting setting to define Bestsellers report type.</param>
        /// <returns>Query of bestsellers report.</returns>
        public static IQueryable <BestsellersReportLine> SelectAsBestsellersReportLine(this IQueryable <OrderItem> query, ReportSorting sorting = ReportSorting.ByQuantityDesc)
        {
            Guard.NotNull(query, nameof(query));

            var selector = query
                           .GroupBy(x => x.ProductId)
                           .Select(x => new BestsellersReportLine
            {
                ProductId     = x.Key,
                TotalAmount   = x.Sum(x => x.PriceExclTax),
                TotalQuantity = x.Sum(x => x.Quantity)
            });

            selector = sorting switch
            {
                ReportSorting.ByAmountAsc => selector.OrderBy(x => x.TotalAmount),
                ReportSorting.ByAmountDesc => selector.OrderByDescending(x => x.TotalAmount),
                ReportSorting.ByQuantityAsc => selector.OrderBy(x => x.TotalQuantity).ThenByDescending(x => x.TotalAmount),
                _ => selector.OrderByDescending(x => x.TotalQuantity).ThenByDescending(x => x.TotalAmount),
            };

            return(selector);
        }