public PagedCashierList WebSiteCashier(SearchCashierRequest request)
{
//request.FormatDate();
//_orderItemRepository.SetCurrentUser(_accountService.GetByUserID(UserId));
if (!String.IsNullOrWhiteSpace(request.FinancialType))
{
var finacialItems = _enumService.All(DefinitionField.Financial);
var item = finacialItems.FirstOrDefault(v => String.Compare(v.Key, request.FinancialType, StringComparison.OrdinalIgnoreCase) == 0);
if (item != null)
{
switch (item.Key)
{
case "-1": //全部
request.FinancialType = String.Empty;
break;
case "0": //进账
request.FinancialType = DefinitionField.Sales;
break;
case "5": //退帐
request.FinancialType = DefinitionField.Rma;
break;
}
}
}
var lst = _orderItemRepository.WebSiteCashierPaged(request);
lst.Stat();
return(lst);
}
/// <summary>
/// 收银
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public PagedCashierList WebSiteCashierPaged(SearchCashierRequest request)
{
var paged = GetPagedList4CashierStat(request);
var result = new PagedCashierList(request.PagerRequest, paged.TotalCount, paged.Datas);
return(result);
}
public void TestWebSiteCashier_2()
{
var req = new SearchCashierRequest(10000);
//dto.StartTime = new DateTime(2000, 1, 1);
req.StartTime = new DateTime(2000, 1, 1);
req.EndTime = DateTime.Now;
req.FinancialType = "SALES";
var o = Service.WebSiteCashier(req);
Assert.IsNotNull(o);
Assert.AreNotEqual(o.Count, 0);
}
public void TestWebSiteCashier_1()
{
var req = new SearchCashierRequest(10000);
//dto.StartTime = new DateTime(2000, 1, 1);
req.StartTime = new DateTime(2000, 1, 1);
req.EndTime = DateTime.Now;
req.FinancialType = "RMA";
req.OrderChannelNo = "1218686401201404233163476538";
var o = Service.WebSiteCashier(req);
Assert.IsNotNull(o);
Assert.AreNotEqual(o.Count, 0);
}
public IHttpActionResult GetList4CashierDetailsReport([FromUri] SearchCashierRequest 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();
#region 参数调整
if (!String.IsNullOrWhiteSpace(request.FinancialType))
{
var finacialItems = _enumService.All(DefinitionField.Financial);
var item = finacialItems.FirstOrDefault(v => String.Compare(v.Key, request.FinancialType, StringComparison.OrdinalIgnoreCase) == 0);
if (item != null)
{
switch (item.Key)
{
case "-1": //全部
request.FinancialType = String.Empty;
break;
case "0": //进账
request.FinancialType = DefinitionField.Sales;
break;
case "5": //退帐
request.FinancialType = DefinitionField.Rma;
break;
}
}
}
#endregion
var pagedinfo = _orderItemRepository.GetPagedList4CashierStat(request);
return(RetrunHttpActionResult(pagedinfo));
}
public IHttpActionResult WebSiteCashier([FromUri] SearchCashierRequest 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.WebSiteCashier(request);
}));
}
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
});
}
}
