public List <SalesOrderModel> GetPagedList(PagerRequest pagerRequest, out int totalCount, SaleOrderFilter filter, SaleOrderSortOrder sortOrder) { var salesFilter = Filler(filter); var storeFilter = Store4Filler(filter); var orderFilter = Order4Filler(filter); using (var db = GetYintaiHZhouContext()) { var salesorders = db.OPC_Sales; var sections = db.Sections; var stores = db.Stores; var ordertrans = db.OrderTransactions; var paymentMethods = db.PaymentMethods; var orders = db.Orders; #region cankao //var otps = from tran in ordertrans // join payment in paymentMethods on tran.PaymentCode equals payment.Code // select new // { // tran, // payment // //transno = tran.TransNo, // //paymentname = payment.Name, // //paymentcode = payment.Code // }; //var storesections = sections.Join(stores.AsExpandable().Where(storeFilter), s => s.StoreId, // x => x.Id, (section, store) => new { section, store }); //var query = from so in salesorders.AsExpandable().Where(salesFilter) // join ss in storesections on so.SectionId equals ss.section.Id // join order in orders.AsExpandable().Where(orderFilter) on so.OrderNo equals order.OrderNo // let trans = ( from trans in otps // where so.OrderNo == trans.tran.OrderNo // select trans // ) // select new // { // salesorder = so, // storesections = ss, // order, // trans // }; //totalCount = query.Count(); //var rstt = query.OrderByDescending(v => v.salesorder.Id).Skip(pagerRequest.SkipCount).Take(pagerRequest.PageSize) // .Select(v => // new SalesOrderModel // { // Id = v.salesorder.Id, // OrderNo = v.salesorder.OrderNo, // SaleOrderNo = v.salesorder.SaleOrderNo, // SalesType = v.salesorder.SalesType, // ShipViaId = v.salesorder.ShipViaId, // Status = v.salesorder.Status, // ShippingCode = v.salesorder.ShippingCode, // ShippingFee = v.salesorder.ShippingFee.HasValue ? v.salesorder.ShippingFee.Value : 0m, // ShippingStatus = v.salesorder.ShippingStatus, // //ShippingStatusName = v.salesorder.ShippingStatus.HasValue ? ((EnumSaleOrderStatus)v.salesorder.ShippingStatus.Value).GetDescription() : String.Empty, // ShippingRemark = v.salesorder.ShippingRemark, // SellDate = v.order.CreateDate, // IfTrans = v.salesorder.IfTrans, // TransStatus = v.salesorder.TransStatus, // SalesAmount = v.salesorder.SalesAmount, // SalesCount = v.salesorder.SalesCount, // CashStatus = v.salesorder.CashStatus, // CashNum = v.salesorder.CashNum, // CashDate = v.salesorder.CashDate, // //CashStatusName = ((EnumCashStatus)v.salesorder.CashStatus).GetDescription(), // //SectionId = v.salesorder.SectionId??0, // PrintTimes = v.salesorder.PrintTimes ?? 0, // Remark = v.salesorder.Remark, // RemarkDate = v.salesorder.RemarkDate, // //store // StoreId = v.storesections.store.Id, // StoreName = v.storesections.store.Name, // StoreAddress = v.storesections.store.Location, // StoreTelephone = v.storesections.store.Tel, // //section // SectionId = v.storesections.section.Id, // SectionName = v.storesections.section.Name, // SectionCode = v.storesections.section.SectionCode, // CreatedDate = v.salesorder.CreatedDate, // CreatedUser = v.salesorder.CreatedUser, // UpdatedDate = v.salesorder.UpdatedDate, // UpdatedUser = v.salesorder.UpdatedUser ?? 0, // //ot.ot // //ShippingSale // //ShipViaId = shipp_let.FirstOrDefault() // //ShipViaName = ss!=null? ss.ShipViaName:String.Empty, // //ShippingCode = ss!=null? ss.ShippingCode:String.Empty, // //ShippingFee = ss!=null? ss.ShippingFee:String.Empty, // //ShippingStatus == ss!=null? ss.ShippingStatus:String.Empty, // //Order // CustomerName = v.order.ShippingContactPerson, // ReceivePerson = v.order.ShippingContactPerson, // CustomerPhone = v.order.ShippingContactPhone, // OrderSource = v.order.OrderSource, // //CustomerAddress = v.oo.ShippingAddress, // //CustomerRemark = v.oo.Memo, // Invoice = v.order.NeedInvoice, // InvoiceSubject = v.order.InvoiceSubject, // //IfReceipt = v.oo.NeedInvoice, // //ReceiptHead = v.oo.InvoiceSubject, // //ReceiptContent = v.oo.InvoiceDetail, // ShippingSaleId = v.salesorder.ShippingSaleId, // OrderProductType = v.order.OrderProductType ?? 1, // ////OT // //TransNo = v.ootp.trans.Any(v=>v.tran.TransNo), // //pay // //PayType = v.pm.Name, // OrderTransactionModels = v.trans.Select(s => new OrderTransactionModel // { // PaymentCode = s.payment.Code, // PaymentName = s.payment.Name, // TransNo = s.tran.TransNo // }), // Memo = v.order.Memo, // PromotionRules = v.order.PromotionRules, // PromotionDesc = v.order.PromotionDesc // }).ToList(); #endregion #region var otps = from tran in ordertrans join payment in paymentMethods on tran.PaymentCode equals payment.Code select new { tran, payment }; var storesections = sections.Join(stores.AsExpandable().Where(storeFilter), s => s.StoreId, x => x.Id, (section, store) => new { section, store }); var query = from so in salesorders.AsExpandable().Where(salesFilter) join ss in storesections on so.SectionId equals ss.section.Id join order in orders.AsExpandable().Where(orderFilter) on so.OrderNo equals order.OrderNo let trans = (from trans in otps where so.OrderNo == trans.tran.OrderNo select trans ) select new { salesorder = so, storesections = ss, order, otp = trans //.OrderByDescending(v => v.tran.Amount).ThenByDescending(v => v.tran.PaymentCode).Skip(0).Take(1) }; totalCount = query.Count(); var rst = query.OrderByDescending(v => v.salesorder.Id).Skip(pagerRequest.SkipCount).Take(pagerRequest.PageSize) .Select(v => new SalesOrderModel { Id = v.salesorder.Id, OrderNo = v.salesorder.OrderNo, SaleOrderNo = v.salesorder.SaleOrderNo, SalesType = v.salesorder.SalesType, ShipViaId = v.salesorder.ShipViaId, Status = v.salesorder.Status, ShippingCode = v.salesorder.ShippingCode, ShippingFee = v.salesorder.ShippingFee.HasValue ? v.salesorder.ShippingFee.Value : 0m, ShippingStatus = v.salesorder.ShippingStatus, //ShippingStatusName = v.salesorder.ShippingStatus.HasValue ? ((EnumSaleOrderStatus)v.salesorder.ShippingStatus.Value).GetDescription() : String.Empty, ShippingRemark = v.salesorder.ShippingRemark, SellDate = v.order.CreateDate, IfTrans = v.salesorder.IfTrans, TransStatus = v.salesorder.TransStatus, SalesAmount = v.salesorder.SalesAmount, SalesCount = v.salesorder.SalesCount, CashStatus = v.salesorder.CashStatus, CashNum = v.salesorder.CashNum, CashDate = v.salesorder.CashDate, //CashStatusName = ((EnumCashStatus)v.salesorder.CashStatus).GetDescription(), //SectionId = v.salesorder.SectionId??0, PrintTimes = v.salesorder.PrintTimes ?? 0, Remark = v.salesorder.Remark, RemarkDate = v.salesorder.RemarkDate, //store StoreId = v.storesections.store.Id, StoreName = v.storesections.store.Name, StoreAddress = v.storesections.store.Location, StoreTelephone = v.storesections.store.Tel, //section SectionId = v.storesections.section.Id, SectionName = v.storesections.section.Name, SectionCode = v.storesections.section.SectionCode, CreatedDate = v.salesorder.CreatedDate, CreatedUser = v.salesorder.CreatedUser, UpdatedDate = v.salesorder.UpdatedDate, UpdatedUser = v.salesorder.UpdatedUser ?? 0, //ot.ot //ShippingSale //ShipViaId = shipp_let.FirstOrDefault() //ShipViaName = ss!=null? ss.ShipViaName:String.Empty, //ShippingCode = ss!=null? ss.ShippingCode:String.Empty, //ShippingFee = ss!=null? ss.ShippingFee:String.Empty, //ShippingStatus == ss!=null? ss.ShippingStatus:String.Empty, //Order CustomerName = v.order.ShippingContactPerson, ReceivePerson = v.order.ShippingContactPerson, CustomerPhone = v.order.ShippingContactPhone, OrderSource = v.order.OrderSource, //CustomerAddress = v.oo.ShippingAddress, //CustomerRemark = v.oo.Memo, NeedInvoice = v.order.NeedInvoice, InvoiceSubject = v.order.InvoiceSubject, Invoice = v.order.InvoiceDetail, //IfReceipt = v.oo.NeedInvoice, //ReceiptHead = v.oo.InvoiceSubject, //ReceiptContent = v.oo.InvoiceDetail, ShippingSaleId = v.salesorder.ShippingSaleId, OrderProductType = v.order.OrderProductType ?? 1, ////OT //TransNo = v.ootp.trans.Any(v=>v.tran.TransNo), //pay //PayType = v.pm.Name, //TransNo = v.otp != null ? v.otp[0].tran.TransNo : String.Empty, //PayType = v.otp != null ? v.otp.payment.Name : String.Empty, OrderTransactionModels = v.otp.Select(s => new OrderTransactionModel { PaymentCode = s.payment.Code, PaymentName = s.payment.Name, TransNo = s.tran.TransNo, Amount = s.tran.Amount }), Memo = v.order.Memo, PromotionRules = v.order.PromotionRules, PromotionDesc = v.order.PromotionDesc }).ToList(); #endregion #region old //var q1 = salesorders.AsExpandable().Where(salesFilter) // .Join( // sections.Join(stores.AsExpandable().Where(storeFilter), s => s.StoreId, // x => x.Id, (section, store) => new { section, store }), // o => o.SectionId, s => s.section.Id, (o, s) => new { o, store = s }) // .Join(ordertrans, o => o.o.OrderNo, ot => ot.OrderNo, (o, ot) => new { o, ot }); //var q = from ot in q1 // //join ss in db.OPC_ShippingSales on ot.o.o.ShippingSaleId equals ss.Id into tmp1 // //from ss in tmp1.DefaultIfEmpty() // join p in paymentMethods on ot.ot.PaymentCode equals p.Code // join oo in orders.AsExpandable().Where(orderFilter) on ot.o.o.OrderNo equals oo.OrderNo // select new //SalesOrderModel // { // ot, // oo, // pm = p // }; //totalCount = q.Count(); //var rst = q.OrderByDescending(v => v.ot.o.o.CreatedDate).ThenBy(v => v.ot.o.o.OrderNo).Skip(pagerRequest.SkipCount).Take(pagerRequest.PageSize) // .Select(v => // new SalesOrderModel // { // Id = v.ot.o.o.Id, // OrderNo = v.ot.o.o.OrderNo, // SaleOrderNo = v.ot.o.o.SaleOrderNo, // SalesType = v.ot.o.o.SalesType, // ShipViaId = v.ot.o.o.ShipViaId, // Status = v.ot.o.o.Status, // ShippingCode = v.ot.o.o.ShippingCode, // ShippingFee = v.ot.o.o.ShippingFee.HasValue ? v.ot.o.o.ShippingFee.Value : 0m, // ShippingStatus = v.ot.o.o.ShippingStatus, // //ShippingStatusName = v.ot.o.o.ShippingStatus.HasValue ? ((EnumSaleOrderStatus)v.ot.o.o.ShippingStatus.Value).GetDescription() : String.Empty, // ShippingRemark = v.ot.o.o.ShippingRemark, // SellDate = v.ot.ot.CreateDate, // IfTrans = v.ot.o.o.IfTrans, // TransStatus = v.ot.o.o.TransStatus, // SalesAmount = v.ot.o.o.SalesAmount, // SalesCount = v.ot.o.o.SalesCount, // CashStatus = v.ot.o.o.CashStatus, // CashNum = v.ot.o.o.CashNum, // CashDate = v.ot.o.o.CashDate, // //CashStatusName = ((EnumCashStatus)v.ot.o.o.CashStatus).GetDescription(), // //SectionId = v.ot.o.o.SectionId??0, // PrintTimes = v.ot.o.o.PrintTimes ?? 0, // Remark = v.ot.o.o.Remark, // RemarkDate = v.ot.o.o.RemarkDate, // //store // StoreId = v.ot.o.store.store.Id, // StoreName = v.ot.o.store.store.Name, // StoreAddress = v.ot.o.store.store.Location, // StoreTelephone = v.ot.o.store.store.Tel, // //section // SectionId = v.ot.o.store.section.Id, // SectionName = v.ot.o.store.section.Name, // SectionCode = v.ot.o.store.section.SectionCode, // CreatedDate = v.ot.o.o.CreatedDate, // CreatedUser = v.ot.o.o.CreatedUser, // UpdatedDate = v.ot.o.o.UpdatedDate, // UpdatedUser = v.ot.o.o.UpdatedUser ?? 0, // //ot.ot // //ShippingSale // //ShipViaId = shipp_let.FirstOrDefault() // //ShipViaName = ss!=null? ss.ShipViaName:String.Empty, // //ShippingCode = ss!=null? ss.ShippingCode:String.Empty, // //ShippingFee = ss!=null? ss.ShippingFee:String.Empty, // //ShippingStatus == ss!=null? ss.ShippingStatus:String.Empty, // //Order // CustomerName = v.oo.ShippingContactPerson, // ReceivePerson = v.oo.ShippingContactPerson, // CustomerPhone = v.oo.ShippingContactPhone, // OrderSource = v.oo.OrderSource, // //CustomerAddress = v.oo.ShippingAddress, // //CustomerRemark = v.oo.Memo, // Invoice = v.oo.NeedInvoice, // InvoiceSubject = v.oo.InvoiceSubject, // //IfReceipt = v.oo.NeedInvoice, // //ReceiptHead = v.oo.InvoiceSubject, // //ReceiptContent = v.oo.InvoiceDetail, // ////OT // TransNo = v.ot.ot.TransNo, // ShippingSaleId = v.ot.o.o.ShippingSaleId, // OrderProductType = v.oo.OrderProductType ?? 1, // //pay // PayType = v.pm.Name, // Memo = v.oo.Memo, // PromotionRules = v.oo.PromotionRules, // PromotionDesc = v.oo.PromotionDesc // }).ToList(); #endregion return(rst); //return AutoMapper.Mapper.Map<List<OPC_SaleClone>, List<OPC_Sale>>(rst); } }
public List <SalesOrderModel> GetPagedList(PagerRequest pagerRequest, out int totalCount, SaleOrderFilter filter, SaleOrderSortOrder sortOrder) { return(_salesOrderRepository.GetPagedList(pagerRequest, out totalCount, filter, sortOrder)); }