public ReturnGoodsStatListDto WebSiteStatReturnGoods(SearchStatRequest request)
{
using (var db = new YintaiHZhouContext())
{
//IQueryable<OPC_SaleDetail> query =
// db.OPC_SaleDetails.Where(t => t.CreatedDate >= request.StartTime && t.CreatedDate < 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 queryOrder =
orderItems
.Join(db.OPC_RMADetails, t => t.Id, o => o.OrderItemId,
(t, o) => new { OrderItem = t, RmaDetail = o });
var query2 = saleDetails.AsExpandable().Where(saleDetailFilter).Join(queryOrder, t => t.OrderItemId, o => o.OrderItem.Id,
(t, o) => new { o.OrderItem, SaleDetail = t, o.RmaDetail });
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 db.OPC_RMAs on q.RmaDetail.RMANo equals r.RMANo //into saleRma
join t in orderTransactions.Where(orderTransactionFilter) on o.OrderNo equals t.OrderNo
select
new
{
Rma = r,
q.RmaDetail,
Order = o,
q.OrderItem,
Brand = b,
Stroe = s.store,
q.SaleDetail,
t
};
var lst = filter.ToList();
var lstDto = new ReturnGoodsStatListDto();
foreach (var o in lst)
{
var dto = new ReturnGoodsStatDto
{
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,
ApplyRmaDate = o.RmaDetail.CreatedDate,
RMANo = o.RmaDetail.RMANo,
ReturnGoodsCount = o.RmaDetail.BackCount
};
dto.OrderTransFee = o.Order.ShippingFee;
dto.RmaDate = o.Rma.CreatedDate;
dto.SectionCode = o.SaleDetail.SectionCode;
dto.RmaPrice = o.RmaDetail.Price;
dto.Size = o.OrderItem.SizeValueName;
dto.StoreName = o.Stroe.Name;
dto.StyleNo = o.OrderItem.StoreItemNo;
dto.SalesCode = o.SaleDetail.ProdSaleCode;
//dto.SalesOrderNo = o.SaleDetail.SaleOrderNo;
dto.RmaStatusName = ((EnumRMAStatus)o.RmaDetail.Status).GetDescription();
dto.SalesOrderNo = o.SaleDetail.SaleOrderNo;
dto.OrderChannelNo = o.t.TransNo;
lstDto.Add(dto);
}
return(lstDto);
}
}
/// <summary>
/// RMA 统计
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public PagerInfo <ReturnGoodsStatDto> GetPagedList4RmaStat(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 rmadetails = db.OPC_RMADetails;
var rmas = db.OPC_RMAs;
var orders = db.Orders;
var brands = db.Brands;
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 queryOrder = orderItems
.Join(rmadetails, t => t.Id, o => o.OrderItemId,
(t, o) => new { OrderItem = t, RmaDetail = o });
var query2 = saleDetails.AsExpandable().Where(saleDetailFilter).Join(queryOrder, t => t.OrderItemId, o => o.OrderItem.Id,
(t, o) => new { o.OrderItem, SaleDetail = t, o.RmaDetail });
var filter = from q in query2
join so in saleorders on q.SaleDetail.SaleOrderNo equals so.SaleOrderNo
join ot in orderTransactions.AsExpandable().Where(orderTransactionFilter).Select(v => v.OrderNo).Distinct() on so.OrderNo equals ot
let trans = (from tran in orderTransactions
join payment in paymentMethods on tran.PaymentCode equals payment.Code
where tran.OrderNo == so.OrderNo
select new
{
tran,
payment
}
)
join o in orders.AsExpandable().Where(orderFilter) on q.OrderItem.OrderNo equals o.OrderNo //into order
join b in brands on q.OrderItem.BrandId equals b.Id //into cs
join s in storesections on so.SectionId equals s.section.Id //into store
join r in rmas on q.RmaDetail.RMANo equals r.RMANo //into saleRma
select
new
{
Rma = r,
q.RmaDetail,
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 <ReturnGoodsStatDto>();
foreach (var o in lst)
{
var defTrans = o.trans.ToList().OrderByDescending(v => v.tran.Amount).ThenByDescending(v => v.tran.PaymentCode).FirstOrDefault();
var dto = new ReturnGoodsStatDto
{
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,
ApplyRmaDate = o.RmaDetail.CreatedDate,
RMANo = o.RmaDetail.RMANo,
ReturnGoodsCount = o.RmaDetail.BackCount,
RmaAmount = o.RmaDetail.Amount
};
dto.OrderTransFee = o.Order.ShippingFee;
dto.RmaDate = o.Rma.CreatedDate;
dto.SectionCode = o.SaleDetail.SectionCode;
dto.RmaPrice = o.RmaDetail.Price;
dto.Size = o.OrderItem.SizeValueName;
dto.StoreName = o.Stroe.Name;
dto.StyleNo = o.OrderItem.StoreItemNo;
dto.SalesCode = o.SaleDetail.ProdSaleCode;
//dto.SalesOrderNo = o.SaleDetail.SaleOrderNo;
dto.RmaStatusName = ((EnumRMAStatus)o.Rma.Status).GetDescription();
dto.SalesOrderNo = o.Rma.SaleOrderNo;
dto.OrderChannelNo = defTrans == null ? String.Empty : defTrans.tran.TransNo;
lstDto.Add(dto);
}
return(new PagerInfo <ReturnGoodsStatDto>(request.PagerRequest, totalCount)
{
Datas = lstDto
});
}
}