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