コード例 #1
0
        public virtual IPagedList <TopCustomerReportLine> GetTopCustomersReport(
            DateTime?startTime,
            DateTime?endTime,
            OrderStatus?orderStatus,
            PaymentStatus?paymentStatus,
            ShippingStatus?shippingStatus,
            ReportSorting sorting,
            int pageIndex = 0,
            int pageSize  = int.MaxValue)
        {
            var orderStatusId    = orderStatus.HasValue ? (int)orderStatus.Value : (int?)null;
            var paymentStatusId  = paymentStatus.HasValue ? (int)paymentStatus.Value : (int?)null;
            var shippingStatusId = shippingStatus.HasValue ? (int)shippingStatus.Value : (int?)null;

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

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

            switch (sorting)
            {
            case ReportSorting.ByAmountAsc:
                groupedQuery = groupedQuery.OrderBy(x => x.OrderTotal);
                break;

            case ReportSorting.ByAmountDesc:
                groupedQuery = groupedQuery.OrderByDescending(x => x.OrderTotal);
                break;

            case ReportSorting.ByQuantityAsc:
                groupedQuery = groupedQuery.OrderBy(x => x.OrderCount).ThenByDescending(x => x.OrderTotal);
                break;

            case ReportSorting.ByQuantityDesc:
            default:
                groupedQuery = groupedQuery.OrderByDescending(x => x.OrderCount).ThenByDescending(x => x.OrderTotal);
                break;
            }

            var result = new PagedList <TopCustomerReportLine>(groupedQuery, pageIndex, pageSize);

            return(result);
        }
コード例 #2
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);
        }
コード例 #3
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());
        }
コード例 #4
0
        /// <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);
        }
コード例 #5
0
        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);
        }
コード例 #6
0
        public virtual ActionResult ListConfig(ReportSorting sorting = ReportSorting.Title)
        {
            var config = new DataTableConfig();
            config.Searching = true;
            config.SetupServerDataSource(Url.Action(MVC.Reporting.ListData()), FormMethod.Post);
            config.Columns = new List<DataTableConfig.Column>(columns);

            switch (sorting)
            {
                case ReportSorting.Title:
                    {
                        config.AddOrder((int)ReportListColumns.Title, DataTables.Mvc.Column.OrderDirection.Ascendant);
                        break;
                    }
                case ReportSorting.Project:
                    {
                        config.AddOrder((int)ReportListColumns.ProjectName, DataTables.Mvc.Column.OrderDirection.Descendant);
                        break;
                    }
                case ReportSorting.Step:
                    {
                        config.AddOrder((int)ReportListColumns.Step, DataTables.Mvc.Column.OrderDirection.Descendant);
                        break;
                    }

            }
            var json = JsonConvert.SerializeObject(config);

            return Content(json, "application/json");
        }
コード例 #7
0
        public static IQueryable <BestSellersReportLine> SelectAsBestSellersReportLine(this IQueryable <OrderItem> query, ReportSorting sorting = ReportSorting.ByQuantityDesc)
        {
            Guard.NotNull(query, nameof(query));

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

            switch (sorting)
            {
            case ReportSorting.ByAmountAsc:
                selector = selector.OrderBy(x => x.TotalAmount);
                break;

            case ReportSorting.ByAmountDesc:
                selector = selector.OrderByDescending(x => x.TotalAmount);
                break;

            case ReportSorting.ByQuantityAsc:
                selector = selector.OrderBy(x => x.TotalQuantity).ThenByDescending(x => x.TotalAmount);
                break;

            case ReportSorting.ByQuantityDesc:
            default:
                selector = selector.OrderByDescending(x => x.TotalQuantity).ThenByDescending(x => x.TotalAmount);
                break;
            }

            return(selector);
        }