public CashierList WebSiteCashier(SearchCashierRequest request) { /* * DECLARE @p__linq__0 INT * SET @p__linq__0=10 * * SELECT [Extent3].* * FROM * [dbo].[OPC_Sale]AS * [Extent1] INNER JOIN[dbo].[OPC_SaleDetail]AS[Extent2]ON[Extent1].[SaleOrderNo]= * [Extent2].[SaleOrderNo] * INNER JOIN[dbo].[OrderItem]AS * [Extent3]ON[Extent2].[OrderItemId]= * [Extent3].[Id] * * INNER JOIN[dbo].[Order] * AS[Extent4]ON * [Extent3].[OrderNo]=[Extent4].[OrderNo] * INNER JOIN[dbo].[Brand]AS * [Extent5]ON[Extent3].[BrandId]=[Extent5].[Id] * INNER JOIN[dbo].[Store]AS[Extent6] * ON[Extent3].[StoreId]=[Extent6].[Id] * * LEFT OUTER JOIN[dbo].[OPC_RMADetail] * AS[Extent7]ON * [Extent3].[Id]= * [Extent7].[OrderItemId] * * INNER JOIN[dbo].[OrderTransaction]AS * [Extent8]ON[Extent4].[OrderNo]=[Extent8].[OrderNo] * * WHERE(( ([Extent1].[CashStatus] = @p__linq__0) AND(NOT([Extent1].[CashStatus]IS NULL OR @p__linq__0 IS NULL)))OR( * ([Extent1].[CashStatus]IS NULL)AND(@p__linq__0 IS NULL)))AND([Extent1].[SectionId] IS NOT NULL) * * --AND(CAST([Extent4].[CreateDate] AS datetime2)>=@p__linq__1)AND * --(CAST([Extent4].[CreateDate] AS datetime2)<@p__linq__2) * AND([Extent4].[PaymentMethodCode]=@p__linq__3) * AND(@p__linq__3 IS NOT NULL))AS[Project1] * ORDER BY[Project1].[OrderNo1]ASC,[Project1].[SaleOrderNo] ASC * */ using (var db = new YintaiHZhouContext()) { int cahStatus = EnumCashStatus.CashOver.AsId(); 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 querySale = db.OPC_Sales.Where( t => t.CashStatus == cahStatus && t.SectionId.HasValue) .Join(saleDetails.AsExpandable().Where(saleDetailFilter), t => t.SaleOrderNo, o => o.SaleOrderNo, (t, o) => new { Sale = t, SaleDetail = o }); var query2 = querySale.Join(orderItems, t => t.SaleDetail.OrderItemId, o => o.Id, (t, o) => new { OrderItem = o, t.SaleDetail, t.Sale }); var query3 = from r in db.OPC_RMADetails join rma in db.OPC_RMAs on r.RMANo equals rma.RMANo select new { rma_details = r, rma }; 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 r in query3 on q.OrderItem.Id equals r.rma_details.OrderItemId into rmas from r in rmas.DefaultIfEmpty() join t in orderTransactions.AsExpandable().Where(orderTransactionFilter) on o.OrderNo equals t.OrderNo orderby q.OrderItem.OrderNo, q.Sale.SaleOrderNo select new { RmaDetails = r.rma_details, rmacashno = r == null ? "" : r.rma.RmaCashNum, q.Sale, Order = o, q.OrderItem, Brand = b, Stroe = s.store, q.SaleDetail, OrderTransactions = t }; dynamic lst; if (!String.IsNullOrWhiteSpace(request.FinancialType)) { if (String.Compare(request.FinancialType, DefinitionField.Rma, StringComparison.OrdinalIgnoreCase) == 0) { lst = filter.Where(v => v.RmaDetails != null).ToList(); } else if (String.Compare(request.FinancialType, DefinitionField.Sales, StringComparison.OrdinalIgnoreCase) == 0) { lst = filter.Where(v => v.RmaDetails == null).ToList(); } else { lst = filter.ToList(); } } else { lst = filter.ToList(); } var lstDto = new CashierList(); foreach (var o in lst) { var dto = new WebSiteCashierSearchDto { OrderItemId = o.OrderItem.Id, Brand = o.Brand == null ? "" : o.Brand.Name, Color = o.OrderItem.ColorValueName, LabelPrice = o.OrderItem.UnitPrice, BuyDate = o.Order.CreateDate, OrderNo = o.Order.OrderNo, OrderSouce = o.Order.OrderSource, CashNum = o.Sale.CashNum, PaymentMethodName = o.Order.PaymentMethodName, SalePrice = o.OrderItem.ItemPrice, Count = o.OrderItem.Quantity, SectionCode = o.SaleDetail.SectionCode, Size = o.OrderItem.SizeValueName, StoreName = o.Stroe.Name, StyleNo = o.OrderItem.StoreItemNo, SalesOrderNo = o.SaleDetail.SaleOrderNo, SaleTotalPrice = o.Sale.SalesAmount, SalesCode = o.SaleDetail.ProdSaleCode, OrderChannelNo = o.OrderTransactions.TransNo }; if (o.RmaDetails == null) { dto.DetailType = "销售单"; // lstDto.Add(dto); } else { ////foreach (OPC_RMADetail rma in o.RmaDetails) ////{ //var dto = new WebSiteCashierSearchDto(); //dto.Brand = o.Brand == null ? "" : o.Brand.Name; //dto.Color = o.OrderItem.ColorValueName; //dto.LabelPrice = o.OrderItem.UnitPrice; //dto.BuyDate = o.Order.CreateDate; //dto.OrderNo = o.Order.OrderNo; //dto.OrderSouce = o.Order.OrderSource; //dto.CashNum = o.Sale.CashNum; //dto.PaymentMethodName = o.Order.PaymentMethodName; //dto.SalePrice = o.OrderItem.ItemPrice; //dto.Count = o.OrderItem.Quantity; //dto.SectionCode = o.SaleDetail.SectionCode; //dto.Size = o.OrderItem.SizeValueName; //dto.StoreName = o.Stroe.Name; //dto.StyleNo = o.OrderItem.StoreItemNo; dto.RmaCashNum = o.rmacashno; dto.DetailType = "退货单"; //} } lstDto.Add(dto); } return(lstDto); } }
/// <summary> /// 收银统计 /// </summary> /// <param name="request"></param> /// <returns></returns> public PagerInfo <WebSiteCashierSearchDto> GetPagedList4CashierStat(SearchCashierRequest request) { using (var db = new YintaiHZhouContext()) { int cahStatus = EnumCashStatus.CashOver.AsId(); 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 rmas = db.OPC_RMAs; var rmadetails = db.OPC_RMADetails; var salesorder = db.OPC_Sales; var paymentMethods = db.PaymentMethods; //var storesections = from store in stores.AsExpandable().Where(storeFilter) // join section in sections on store.Id equals section.StoreId // select new // { // store, // section // }; //var querySale = salesorder.Where( // t => // t.CashStatus == cahStatus && t.SectionId.HasValue) // .Join(saleDetails.AsExpandable().Where(saleDetailFilter), t => t.SaleOrderNo, o => o.SaleOrderNo, // (t, o) => new { Sale = t, SaleDetail = o }); //var query2 = querySale.Join(orderItems, t => t.SaleDetail.OrderItemId, o => o.Id, // (t, o) => new { OrderItem = o, t.SaleDetail, t.Sale }); //var rmaquery = from rmadetail in rmadetails // from rma in rmas.Where(v => v.RMACashStatus == cahStatus) // where rmadetail.RMANo == rma.RMANo // select new // { // rma, // rmadetail // }; var filter = from q in ((salesorder.Where( t => t.CashStatus == cahStatus && t.SectionId.HasValue) .Join(saleDetails.AsExpandable().Where(saleDetailFilter), t => t.SaleOrderNo, o => o.SaleOrderNo, (t, o) => new { Sale = t, SaleDetail = o })).Join(orderItems, t => t.SaleDetail.OrderItemId, o => o.Id, (t, o) => new { OrderItem = o, t.SaleDetail, t.Sale })) 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 ( from store in stores.AsExpandable().Where(storeFilter) join section in sections on store.Id equals section.StoreId select new { store, section } ) on q.Sale.SectionId equals s.section.Id //into store join r in ( from rmadetail in rmadetails from rma in rmas.Where(v => v.RMACashStatus == cahStatus) where rmadetail.RMANo == rma.RMANo select new { rma, rmadetail } ) on q.OrderItem.Id equals r.rmadetail.OrderItemId into rmaDetails from r in rmaDetails.DefaultIfEmpty() let trans = (from tran in orderTransactions join payment in paymentMethods on tran.PaymentCode equals payment.Code where tran.OrderNo == o.OrderNo select new { tran, payment } ) join ot in orderTransactions.AsExpandable().Where(orderTransactionFilter).Select(v => v.OrderNo).Distinct() on o.OrderNo equals ot orderby q.OrderItem.OrderNo, q.Sale.SaleOrderNo select new { RmaAndDetails = r, q.Sale, Order = o, q.OrderItem, Brand = b, Stroe = s.store, q.SaleDetail, opts = trans }; IQueryable <dynamic> lst; int totalCount; if (!String.IsNullOrWhiteSpace(request.FinancialType)) { if (String.Compare(request.FinancialType, DefinitionField.Rma, StringComparison.OrdinalIgnoreCase) == 0) { filter = filter.Where(v => v.RmaAndDetails != null); } else if (String.Compare(request.FinancialType, DefinitionField.Sales, StringComparison.OrdinalIgnoreCase) == 0) { filter = filter.Where(v => v.RmaAndDetails == null); } } totalCount = filter.Count(); var rst = filter.OrderByDescending(v => v.Order.CreateDate).Skip(request.PagerRequest.SkipCount).Take(request.PagerRequest.PageSize).ToList(); var lstDto = new List <WebSiteCashierSearchDto>(); foreach (var o in rst) { var defTrans = o.opts.ToList().OrderByDescending(v => v.tran.Amount).ThenByDescending(v => v.tran.PaymentCode).FirstOrDefault(); var dto = new WebSiteCashierSearchDto { OrderItemId = o.OrderItem.Id, Brand = o.Brand == null ? "" : o.Brand.Name, Color = o.OrderItem.ColorValueName, LabelPrice = o.OrderItem.UnitPrice ?? 0m, BuyDate = o.Order.CreateDate, OrderNo = o.Order.OrderNo, OrderSouce = o.Order.OrderSource, CashNum = o.Sale.CashNum, PaymentMethodName = defTrans == null ? String.Empty : defTrans.payment.Name, SalePrice = o.OrderItem.ItemPrice, Count = o.OrderItem.Quantity, SectionCode = o.SaleDetail.SectionCode, Size = o.OrderItem.SizeValueName, StoreName = o.Stroe.Name, StyleNo = o.OrderItem.StoreItemNo, SalesOrderNo = o.SaleDetail.SaleOrderNo, SaleTotalPrice = o.Sale.SalesAmount, SalesCode = o.SaleDetail.ProdSaleCode, OrderChannelNo = defTrans == null ? String.Empty : defTrans.tran.TransNo }; if (o.RmaAndDetails == null) { dto.DetailType = "销售单"; } else { dto.RmaCashNum = o.RmaAndDetails.rma.RmaCashNum; dto.DetailType = "退货单"; } lstDto.Add(dto); } return(new PagerInfo <WebSiteCashierSearchDto>(request.PagerRequest, totalCount) { Datas = lstDto }); } }