/// <summary>
/// Order
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
private static Expression <Func <Order, bool> > OrderFiller(SearchStatRequest filter)
{
var query = PredicateBuilder.True <Order>();
if (filter == null)
{
return(query);
}
//订单号与时间互斥
if (!String.IsNullOrWhiteSpace(filter.OrderNo))
{
query = PredicateBuilder.And(query, v => v.OrderNo == filter.OrderNo);
return(query);
}
if (filter.StartDate != null)
{
query = PredicateBuilder.And(query, v => v.CreateDate >= filter.StartDate);
}
if (filter.EndDate != null)
{
query = PredicateBuilder.And(query, v => v.CreateDate < filter.EndDate);
}
return(query);
}
/// <summary>
/// RMA
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public PagedReturnGoodsStatListDto WebSiteStatReturnGoodsPaged(SearchStatRequest request)
{
var paged = GetPagedList4RmaStat(request);
var result = new PagedReturnGoodsStatListDto(request.PagerRequest, paged.TotalCount, paged.Datas);
return(result);
}
/// <summary>
/// 销售单
/// </summary>
/// <param name="request"></param>
public PagedSaleDetailStatListDto WebSiteStatSaleDetailPaged(SearchStatRequest request)
{
var paged = GetPagedList4SaleStat(request);
var result = new PagedSaleDetailStatListDto(request.PagerRequest, paged.TotalCount, paged.Datas);
return(result);
}
public PagedReturnGoodsStatListDto WebSiteStatReturnDetail(SearchStatRequest request)
{
//request.FormatDate();
//_orderItemRepository.SetCurrentUser(_accountService.GetByUserID(UserId));
var lst = _orderItemRepository.WebSiteStatReturnGoodsPaged(request);
lst.Stat();
return(lst);
}
public void TestWebSiteStatReturnDetail()
{
SearchStatRequest dto = new SearchStatRequest(10000);
dto.StartTime = new DateTime(2000, 1, 1);
dto.EndTime = DateTime.Now;
var o = Service.WebSiteStatReturnDetail(dto);
Assert.IsNotNull(o);
Assert.AreNotEqual(o.Count, 0);
}
public void TestWebSiteStatSaleDetail()
{
SearchStatRequest dto = new SearchStatRequest(10000);
dto.StartTime = new DateTime(2014, 4, 26);
dto.EndTime = new DateTime(2014, 5, 7);
//Service.UserId = 1;
var o = Service.WebSiteStatSaleDetail(dto);
Assert.IsNotNull(o);
Assert.AreNotEqual(o.Count, 0);
}
public IHttpActionResult GetList4SalesDetailsReport([FromUri] SearchStatRequest request, [UserProfile] UserProfile userProfile)
{
request.StoreId = CheckStoreId(request.StoreId);
var result = CheckRole4Store(userProfile, request.StoreId);
if (!result.Result)
{
return(BadRequest(result.Error));
}
request.DataRoleStores = userProfile.StoreIds == null ? null : userProfile.StoreIds.ToList();
request.ArrangeParams();
var pagedinfo = _orderItemRepository.GetPagedList4SaleStat(request);
return(RetrunHttpActionResult(pagedinfo));
}
/// <summary>
/// SaleDetailFiller
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
private static Expression <Func <OPC_SaleDetail, bool> > SaleDetailFiller(SearchStatRequest filter)
{
var query = PredicateBuilder.True <OPC_SaleDetail>();
if (filter == null)
{
return(query);
}
if (!String.IsNullOrWhiteSpace(filter.SalesOrderNo))
{
query = PredicateBuilder.And(query, v => v.SaleOrderNo == filter.SalesOrderNo);
}
return(query);
}
public IHttpActionResult WebSiteStatReturnDetail([FromUri] SearchStatRequest request, [UserProfile] UserProfile userProfile)
{
request.StoreId = CheckStoreId(request.StoreId);
var result = CheckRole4Store(userProfile, request.StoreId);
if (!result.Result)
{
return(BadRequest(result.Error));
}
request.DataRoleStores = userProfile.StoreIds == null ? null : userProfile.StoreIds.ToList();
request.ArrangeParams();
return(DoFunction(() =>
{
//_orderService.UserId = uid;
return _orderService.WebSiteStatReturnDetail(request);
}));
}
/// <summary>
/// StoreFiller 包含了权限判断
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
private static Expression <Func <Store, bool> > StoreFiller(SearchStatRequest filter)
{
var query = PredicateBuilder.True <Store>();
if (filter == null)
{
return(query);
}
if (filter.StoreId != null)
{
query = PredicateBuilder.And(query, v => v.Id == filter.StoreId);
}
if (filter.DataRoleStores != null && filter.StoreId == null)
{
query = PredicateBuilder.And(query, v => filter.DataRoleStores.Contains(v.Id));
}
return(query);
}
/// <summary>
/// OrderTransactionFiller
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
private static Expression <Func <OrderTransaction, bool> > OrderTransactionFiller(SearchStatRequest filter)
{
var query = PredicateBuilder.True <OrderTransaction>();
if (filter == null)
{
return(query);
}
if (!String.IsNullOrWhiteSpace(filter.OrderChannelNo))
{
query = PredicateBuilder.And(query, v => v.TransNo == filter.OrderChannelNo);
}
var c = filter as SearchCashierRequest;
if (c != null)
{
//TODO: 确认下 客户端传来的是 code 还是name
if (!String.IsNullOrWhiteSpace(c.PayType))
{
query = PredicateBuilder.And(query, v => v.PaymentCode == c.PayType);
}
}
return(query);
}
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);
}
}
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>
/// 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
});
}
}
/// <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
});
}
}
