public SaleDetailStatListDto WebSiteStatSaleDetail(SearchStatRequest request) { using (var db = new YintaiHZhouContext()) { //IQueryable<OPC_SaleDetail> query = // db.OPC_SaleDetails.Where(t => t.CreatedDate >= request.StartTime && t.CreatedDate < request.EndTime ); //IQueryable<OrderItem> queryOrder = // db.OrderItems.Where(t => t.CreateDate >= request.StartTime && t.CreateDate < request.EndTime); var orderFilter = OrderFiller(request); var storeFilter = StoreFiller(request); var orderTransactionFilter = OrderTransactionFiller(request); var saleDetailFilter = SaleDetailFiller(request); var stores = db.Stores; //var sections = db.Sections; var orderItems = db.OrderItems; var saleDetails = db.OPC_SaleDetails; var orderTransactions = db.OrderTransactions; var storeQuery = from store in stores.AsExpandable().Where(storeFilter) //from section in sections //where store.Id == section.StoreId select new { store, //section }; var query2 = saleDetails.AsExpandable().Where(saleDetailFilter).Join(orderItems, t => t.OrderItemId, o => o.Id, (t, o) => new { OrderItem = o, SaleDetail = t }); var filter = from q in query2 join o in db.Orders.AsExpandable().Where(orderFilter) on q.OrderItem.OrderNo equals o.OrderNo //into order join b in db.Brands on q.OrderItem.BrandId equals b.Id //into cs join s in storeQuery on q.OrderItem.StoreId equals s.store.Id //into store join t in orderTransactions.AsExpandable().Where(orderTransactionFilter) on o.OrderNo equals t.OrderNo select new { Order = o, q.OrderItem, Brand = b, Stroe = s.store, q.SaleDetail, t }; var lst = filter.ToList(); var lstDto = new SaleDetailStatListDto(); foreach (var o in lst) { var dto = new SaleDetailStatDto { OrderItemId = o.OrderItem.Id, Brand = o.Brand == null ? "" : o.Brand.Name, Color = o.OrderItem.ColorValueName, LabelPrice = o.OrderItem.UnitPrice.HasValue ? o.OrderItem.UnitPrice.Value : 0, BuyDate = o.Order.CreateDate, OrderNo = o.Order.OrderNo, OrderSouce = o.Order.OrderSource, OrderTransFee = o.Order.ShippingFee, PaymentMethodName = o.Order.PaymentMethodName, SalePrice = o.OrderItem.ItemPrice, SaleTotalPrice = o.OrderItem.ExtendPrice, SectionCode = o.SaleDetail.SectionCode, SellCount = o.SaleDetail.SaleCount, Size = o.OrderItem.SizeValueName, StoreName = o.Stroe == null ? "" : o.Stroe.Name, StyleNo = o.OrderItem.StoreItemNo, SalesCode = o.SaleDetail.ProdSaleCode, SalesOrderNo = o.SaleDetail.SaleOrderNo, OrderChannelNo = o.t.TransNo }; lstDto.Add(dto); } return(lstDto); } }
/// <summary> /// 销售单 统计 /// </summary> /// <param name="request"></param> /// <returns></returns> public PagerInfo <SaleDetailStatDto> GetPagedList4SaleStat(SearchStatRequest request) { using (var db = new YintaiHZhouContext()) { var orderFilter = OrderFiller(request); var storeFilter = StoreFiller(request); var orderTransactionFilter = OrderTransactionFiller(request); var saleDetailFilter = SaleDetailFiller(request); var stores = db.Stores; var sections = db.Sections; var orderItems = db.OrderItems; var saleDetails = db.OPC_SaleDetails; var orderTransactions = db.OrderTransactions; var saleorders = db.OPC_Sales; var paymentMethods = db.PaymentMethods; var storesections = from store in stores.AsExpandable().Where(storeFilter) from section in sections where store.Id == section.StoreId select new { store, section }; var query2 = saleDetails.AsExpandable().Where(saleDetailFilter).Join(orderItems, t => t.OrderItemId, o => o.Id, (t, o) => new { OrderItem = o, SaleDetail = t }); var filter = from q in query2 join so in saleorders on q.SaleDetail.SaleOrderNo equals so.SaleOrderNo join o in db.Orders.AsExpandable().Where(orderFilter) on q.OrderItem.OrderNo equals o.OrderNo //into order join b in db.Brands on q.OrderItem.BrandId equals b.Id //into cs join s in storesections on so.SectionId equals s.section.Id //into store let trans = (from tran in orderTransactions.AsExpandable().Where(orderTransactionFilter) join payment in paymentMethods on tran.PaymentCode equals payment.Code where tran.OrderNo == so.OrderNo select new { tran, payment } ) join ot in orderTransactions.AsExpandable().Where(orderTransactionFilter).Select(v => v.OrderNo).Distinct() on so.OrderNo equals ot select new { Order = o, q.OrderItem, Brand = b, Stroe = s.store, q.SaleDetail, trans }; var totalCount = filter.Count(); var lst = filter.OrderByDescending(v => v.Order.CreateDate).Skip(request.PagerRequest.SkipCount).Take(request.PagerRequest.PageSize).ToList(); var lstDto = new List <SaleDetailStatDto>(); foreach (var o in lst) { var defTrans = o.trans.ToList().OrderByDescending(v => v.tran.Amount).ThenByDescending(v => v.tran.PaymentCode).FirstOrDefault(); var dto = new SaleDetailStatDto { OrderItemId = o.OrderItem.Id, Brand = o.Brand == null ? String.Empty : o.Brand.Name, Color = o.OrderItem.ColorValueName, LabelPrice = o.OrderItem.UnitPrice.HasValue ? o.OrderItem.UnitPrice.Value : 0, BuyDate = o.Order.CreateDate, OrderNo = o.Order.OrderNo, OrderSouce = o.Order.OrderSource, OrderTransFee = o.Order.ShippingFee, PaymentMethodName = defTrans == null ? String.Empty : defTrans.payment.Name, SalePrice = o.OrderItem.ItemPrice, SaleTotalPrice = o.OrderItem.ExtendPrice, SectionCode = o.SaleDetail.SectionCode, SellCount = o.SaleDetail.SaleCount, Size = o.OrderItem.SizeValueName, StoreName = o.Stroe == null ? "" : o.Stroe.Name, StyleNo = o.OrderItem.StoreItemNo, SalesCode = o.SaleDetail.ProdSaleCode, SalesOrderNo = o.SaleDetail.SaleOrderNo, OrderChannelNo = defTrans == null ? String.Empty : defTrans.tran.TransNo }; lstDto.Add(dto); } return(new PagerInfo <SaleDetailStatDto>(request.PagerRequest, totalCount) { Datas = lstDto }); } }