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
});
}
}
