/// <summary> /// 退换货单分页查询 /// </summary> /// <param name="filter">筛选条件</param> /// <returns>可分页的退换货表</returns> /// <remarks>2013-07-11 朱家宏 创建</remarks> public abstract Pager <CBRcReturn> GetAll(ParaRmaFilter filter);
/// <summary>
/// 退换货单分页查询
/// </summary>
/// <param name="filter">筛选条件</param>
/// <returns>退换货表</returns>
/// <remarks>2013-07-11 朱家宏 创建</remarks>
public override Pager <CBRcReturn> GetAll(ParaRmaFilter filter)
{
const string sql =
@"(select a.sysno,a.ordersysno,a.refundtotalamount,a.status,a.createdate,a.rmatype,a.source,a.handledepartment,a.CustomerSysNo,
c.account as customerAccount,
e.warehousename,e.backwarehousename
from rcreturn a
left join soOrder b on b.sysno=a.ordersysno
left join crCustomer c on c.sysno=a.customersysno
left join WhwareHouse e on e.SYSNO = a.warehousesysno
where
(@0 is null or c.account=@0) and --会员号
(@1 is null or a.sysno=@1) and --RmaId
(@2 is null or a.OrderSysNo=@2) and --OrderSysNo
(@3 is null or exists (select 1 from splitstr(@3,',') tmp where tmp.col = b.ordersource)) and --销售单来源
(@4 is null or @4 = '' or exists (select 1 from splitstr(@4,',') tmp where tmp.col = a.status)) and --退换货状态
(@5 is null or exists (select 1 from splitstr(@5,',') tmp where tmp.col = a.source)) and --申请单来源
(@6 is null or a.RmaType=@6) and --RmaType
(@7 is null or a.createDate>=@7) and --创建日期(起)
(@8 is null or a.createDate<@8) and --创建日期(止)
(@9 is null or c.SysNo=@9) and --会员编号
(@10='true' or (a.warehousesysno is null or a.warehousesysno=0)) and
(@11 is null or exists (select 1 from splitstr(@11,',') tmp where tmp.col = a.HandleDepartment)) and --申请单处理部门
(@12 is null or exists (select 1 from splitstr(@12,',') tmp where tmp.col = e.sysNo))
) tb";
var orderSources = filter.OrderSources != null?string.Join(",", filter.OrderSources) : null;
var rmaStatuses = filter.RmaStatuses != null?string.Join(",", filter.RmaStatuses) : null;
var rmaSources = filter.RmaSources != null?string.Join(",", filter.RmaSources) : null;
var handleDepartments = filter.HandleDepartments != null?string.Join(",", filter.HandleDepartments) : null;
var storeSysNos = filter.StoreSysNoList != null?string.Join(",", filter.StoreSysNoList) : null;
//查询日期上限+1
filter.EndDate = filter.EndDate == null ? (DateTime?)null : filter.EndDate.Value.AddDays(1);
var paras = new object[]
{
filter.CustomerAccount, // filter.CustomerAccount,
filter.RmaId, /// filter.RmaId,
filter.OrderSysNo, // filter.OrderSysNo,
orderSources, // orderSources,
rmaStatuses, // rmaStatuses,
rmaSources, // rmaSources,
filter.RmaType, // filter.RmaType,
filter.BeginDate, // filter.BeginDate,
filter.EndDate, // filter.EndDate,
filter.CustomerSysNo, // filter.CustomerSysNo,
filter.HasWarehouse.ToString().ToLower(),
handleDepartments, // handleDepartments,
storeSysNos //, storeSysNos
};
var dataList = Context.Select <CBRcReturn>("tb.*").From(sql);
var dataCount = Context.Select <int>("count(0)").From(sql);
dataList.Parameters(paras);
dataCount.Parameters(paras);
var pager = new Pager <CBRcReturn>
{
PageSize = filter.PageSize,
CurrentPage = filter.Id
};
pager.TotalRows = dataCount.QuerySingle();
pager.Rows = dataList.OrderBy("tb.createdate desc").Paging(pager.CurrentPage, filter.PageSize).QueryMany();
return(pager);
}
