/// <summary>
/// 预收现金收款单查询
/// </summary>
/// <param name="filter">查询条件</param>
/// <param name="currentUserSysNo">当前登录用户系统编号</param>
/// <returns>分页查询</returns>
/// <remarks>2013-07-19 朱家宏 创建</remarks>
public Pager <CBFnReceiptVoucher> GetFnReceipt(ParaWarehouseFilter filter, int currentUserSysNo)
{
var pager = IFnReceiptVoucherDao.Instance.GetFnReceipt(filter, currentUserSysNo);
if (pager.Rows != null)
{
foreach (var voucher in pager.Rows)
{
voucher.Confirmer = Sys.SyUserBo.Instance.GetUserName(voucher.ConfirmedBy);
}
}
return(pager);
}
/// <summary> /// 分页查询收款单 /// </summary> /// <param name="filter">查询参数</param> /// <param name="currentUserSysNo">当前登录用户系统编号</param> /// <returns>查询收款单列表</returns> /// <remarks>2013-10-14 沈强 修改</remarks> public abstract Pager <CBFnReceiptVoucher> GetFnReceipt(ParaWarehouseFilter filter, int currentUserSysNo);
/// <summary>
/// 分页查询预收现金收款单
/// </summary>
/// <param name="filter">查询参数</param>
/// <param name="currentUserSysNo">当前登录用户系统编号</param>
/// <returns>查询预收现金收款单列表</returns>
/// <remarks>2013-10-14 沈强 修改</remarks>
public override Pager <CBFnReceiptVoucher> GetFnReceipt(ParaWarehouseFilter filter, int currentUserSysNo)
{
string sql = @"(select a.*
from fnreceiptvoucher a
where
a.IncomeType = {0} and
a.source = {1} and
{createby} --所在仓库的配送员
(@0 is null or a.createdby = @0) and --配送员系统编号
(@1 is null or a.Status=@1) and --状态
(@2 is null or a.CreatedDate>=@2) and --创建日期(起)
(@3 is null or a.CreatedDate<@3) and --创建日期(止)
(@4 is null or a.SourceSysNo=@4) and --单据来源编号
exists
(
select 1 from soorder b where b.sysno = a.Sourcesysno and a.source={1} and b.PayTypeSysNo = {2} and b.OrderSource = {3}
)
) tb";
if (filter.WarehouseSysNo == null)
{
#region
// sql = @"(select a.*
// from fnreceiptvoucher a
// where
// a.IncomeType = {0} and
// a.source = {1} and
// a.createdby in(select b.usersysno from SYUSERWAREHOUSE b where b.warehousesysno in(select c.warehousesysno from SYUSERWAREHOUSE c where c.usersysno = {2})) and --所在仓库的配送员
// (:DeliverySysNo is null or a.createdby = :DeliverySysNo) and --配送员系统编号
// (:Status is null or a.Status=:Status) and --状态
// (:BeginDate is null or a.CreatedDate>=:BeginDate) and --创建日期(起)
// (:EndDate is null or a.CreatedDate<:EndDate) and --创建日期(止)
// (:SourceSysNo is null or a.SourceSysNo=:SourceSysNo) --单据来源编号
// ) tb";
#endregion
string tmp = "a.createdby in(select b.usersysno from SYUSERWAREHOUSE b where b.warehousesysno in(select c.warehousesysno from SYUSERWAREHOUSE c where c.usersysno = {4})) and";
sql = sql.Replace("{createby}", tmp);
sql = string.Format(sql, (int)FinanceStatus.收款单收入类型.预付, (int)FinanceStatus.收款来源类型.销售单,
Model.SystemPredefined.PaymentType.现金预付, (int)OrderStatus.销售单来源.业务员下单,
currentUserSysNo);
}
else
{
#region
// sql =
// @"(select a.*
// from fnreceiptvoucher a
// where
// a.IncomeType = {0} and
// a.source = {1} and
// (:WarehouseSysno is null or a.createdby in(select b.usersysno from SYUSERWAREHOUSE b where b.warehousesysno = :WarehouseSysno)) and --所在仓库的配送员
// (:DeliverySysNo is null or a.createdby = :DeliverySysNo) and --配送员系统编号
// (:Status is null or a.Status=:Status) and --状态
// (:BeginDate is null or a.CreatedDate>=:BeginDate) and --创建日期(起)
// (:EndDate is null or a.CreatedDate<:EndDate) and --创建日期(止)
// (:SourceSysNo is null or a.SourceSysNo=:SourceSysNo) --单据来源编号
// ) tb";
#endregion
string tmp = "(@5 is null or a.createdby in(select b.usersysno from SYUSERWAREHOUSE b where b.warehousesysno = @5)) and";
sql = sql.Replace("{createby}", tmp);
sql = string.Format(sql, (int)FinanceStatus.收款单收入类型.预付, (int)FinanceStatus.收款来源类型.销售单,
Model.SystemPredefined.PaymentType.现金预付, (int)OrderStatus.销售单来源.业务员下单);
}
//查询日期上限+1
filter.EndDate = filter.EndDate == null ? (DateTime?)null : filter.EndDate.Value.AddDays(1);
object[] paras = null;
if (filter.WarehouseSysNo == null)
{
paras = new object[]
{
filter.DeliverySysNo,
filter.Status,
filter.BeginDate,
filter.EndDate,
filter.SourceSysNo
};
}
else
{
paras = new object[]
{
filter.DeliverySysNo,
filter.Status,
filter.BeginDate,
filter.EndDate,
filter.SourceSysNo,
filter.WarehouseSysNo
};
}
var dataList = Context.Select <CBFnReceiptVoucher>("tb.*").From(sql);
var dataCount = Context.Select <int>("count(1)").From(sql);
dataList.Parameters(paras);
dataCount.Parameters(paras);
var pager = new Pager <CBFnReceiptVoucher>
{
PageSize = filter.PageSize,
CurrentPage = filter.Id
};
pager.TotalRows = dataCount.QuerySingle();
pager.Rows = dataList.OrderBy("tb.LastUpdateDate desc").Paging(pager.CurrentPage, filter.PageSize).QueryMany();
return(pager);
}
