/// <summary> /// 分页查询代理商资金列表 /// </summary> /// <param name="pager">分页</param> /// <param name="filter">查询条件</param> /// <remarks> 2016-04-18 刘伟豪 创建 </remarks> public override void SeachAgentPrePayment(ref Pager <CBDsAgent> pager, ParaDsAgentFilter filter) { string sqlWhere = " 1=1 "; if (!filter.IsBindAllDealer) { if (filter.IsAgent) { sqlWhere += " And Ag.UserSysNo=@4"; } } string sql = @"(Select Ag.* ,Lv.LevelName ,Us.Account ,dbo.func_getaereapath(Ag.AreaSysNo) AreaAllName ,Ap.TotalPrestoreAmount ,Ap.AvailableAmount ,Ap.FrozenAmount ,Ap.SysNo As AgentPrePaymentSysNo From DsAgent Ag Left Join DsDealerLevel Lv On Ag.LevelSysNo=Lv.SysNo Left Join SyUser Us On Ag.UserSysNo=Us.SysNo Left Join DsAgentPrePayment Ap On Ag.SysNo=Ap.AgentSysNo Where (@0=0 Or Ag.AreaSysNo=@0) And (@1=-1 Or Ag.Status=@1) And (@2=0 Or Ag.LevelSysNo=@2) And (@3='' Or @3 Is Null Or CHARINDEX(@3,Ag.Name) > 0) And " + sqlWhere + ") tb"; var dataList = Context.Select <CBDsAgent>("tb.*").From(sql); var dataCount = Context.Select <int>("count(0)").From(sql); var paras = new object[] { filter.AreaSysNo, filter.Status, filter.LevelSysNo, filter.Name, filter.CurrentUserSysNo }; dataList = dataList.Parameters(paras); dataCount = dataCount.Parameters(paras); pager.Rows = dataList.OrderBy("tb.LastUpdateDate Desc,tb.SysNo Desc").Paging(pager.CurrentPage, pager.PageSize).QueryMany(); pager.TotalRows = dataCount.QuerySingle(); }
/// <summary> /// 分页查询代理商资金列表 /// </summary> /// <param name="pager">分页</param> /// <param name="filter">查询条件</param> /// <remarks> 2016-04-18 刘伟豪 创建 </remarks> public abstract void SeachAgentPrePayment(ref Pager <CBDsAgent> pager, ParaDsAgentFilter filter);
/// <summary> /// 分页查询代理商 /// </summary> /// <param name="pager">分页</param> /// <param name="filter">查询条件</param> /// <remarks> 2016-04-13 刘伟豪 创建 </remarks> public abstract void Search(ref Pager <CBDsAgent> pager, ParaDsAgentFilter filter);
/// <summary> /// 分页查询代理商资金列表 /// </summary> /// <param name="pager">分页</param> /// <param name="filter">查询条件</param> /// <remarks> 2016-04-18 刘伟豪 创建 </remarks> public void SeachAgentPrePayment(ref Pager <CBDsAgent> pager, ParaDsAgentFilter filter) { IDsAgentDao.Instance.SeachAgentPrePayment(ref pager, filter); }
/// <summary> /// 分页查询代理商 /// </summary> /// <param name="pager">分页</param> /// <param name="filter">查询条件</param> /// <remarks> 2016-04-13 刘伟豪 创建 </remarks> public override void Search(ref Pager <CBDsAgent> pager, ParaDsAgentFilter filter) { using (var _context = Context.UseSharedConnection(true)) { var sqlWhere = " 1=1 "; if (filter.SysNo > 0) { sqlWhere += " and Ag.SysNo=@SysNo "; } if (filter.LevelSysNo > 0) { sqlWhere += " and Ag.LevelSysNo=@LevelSysNo "; } if (!string.IsNullOrWhiteSpace(filter.Name)) { sqlWhere += " and Ag.Name like @Name"; } if (!string.IsNullOrWhiteSpace(filter.Contact)) { sqlWhere += " and Ag.Contact like @Contact"; } if (!string.IsNullOrWhiteSpace(filter.MobilePhoneNumber)) { sqlWhere += " and Ag.MobilePhoneNumber like @MobilePhoneNumber"; } if (filter.Status > -1) { sqlWhere += " and Ag.Status=@Status "; } if (filter.StartTime != null) { sqlWhere += " and Ag.CreatedDate >= @StartTime"; } if (filter.EndTime != null) { sqlWhere += " and Ag.CreatedDate <= @EndTime"; } if (filter.UpdateDateStartTime != null) { sqlWhere += " and Ag.LastUpdateDate >= @UpdateDateStartTime"; } if (filter.UpdateDateEndTime != null) { sqlWhere += " and Ag.LastUpdateDate <= @UpdateDateEndTime"; } if (!string.IsNullOrWhiteSpace(filter.KeyWord)) { sqlWhere += " and (Ag.Name like @KeyWord or Us.Account like @KeyWord)"; } if (!string.IsNullOrWhiteSpace(filter.Account)) { sqlWhere += " and Us.Account like @Account"; } pager.Rows = _context.Select <CBDsAgent>(" Ag.*,Lv.LevelName,Us.Account,dbo.func_getaereapath(Ag.AreaSysNo) AreaAllName,Ap.TotalPrestoreAmount,Ap.AvailableAmount,Ap.FrozenAmount,Ap.SysNo As AgentPrePaymentSysNo ") .From(@"DsAgent Ag Left Join DsDealerLevel Lv On Ag.LevelSysNo=Lv.SysNo Left Join SyUser Us On Ag.UserSysNo=Us.SysNo Left Join DsAgentPrePayment Ap On Ag.SysNo=Ap.AgentSysNo") .Where(sqlWhere) .Parameter("SysNo", filter.SysNo) .Parameter("LevelSysNo", filter.LevelSysNo) .Parameter("Name", "%" + filter.Name + "%") .Parameter("Contact", "%" + filter.Contact + "%") .Parameter("MobilePhoneNumber", "%" + filter.MobilePhoneNumber + "%") .Parameter("Status", filter.Status) .Parameter("StartTime", filter.StartTime) .Parameter("EndTime", filter.EndTime) .Parameter("UpdateDateStartTime", filter.UpdateDateStartTime) .Parameter("UpdateDateEndTime", filter.UpdateDateEndTime) .Parameter("KeyWord", "%" + filter.KeyWord + "%") .Parameter("Account", "%" + filter.Account + "%") .OrderBy(" Ag.LastUpdateDate Desc,Ag.SysNo Desc ") .Paging(pager.CurrentPage, pager.PageSize) .QueryMany(); pager.TotalRows = _context.Select <int>(" count(0) ") .From(@"DsAgent Ag Left Join DsDealerLevel Lv On Ag.LevelSysNo=Lv.SysNo Left Join SyUser Us On Ag.UserSysNo=Us.SysNo Left Join DsAgentPrePayment Ap On Ag.SysNo=Ap.AgentSysNo") .Where(sqlWhere) .Parameter("SysNo", filter.SysNo) .Parameter("LevelSysNo", filter.LevelSysNo) .Parameter("Name", "%" + filter.Name + "%") .Parameter("Contact", "%" + filter.Contact + "%") .Parameter("MobilePhoneNumber", "%" + filter.MobilePhoneNumber + "%") .Parameter("Status", filter.Status) .Parameter("StartTime", filter.StartTime) .Parameter("EndTime", filter.EndTime) .Parameter("UpdateDateStartTime", filter.UpdateDateStartTime) .Parameter("UpdateDateEndTime", filter.UpdateDateEndTime) .Parameter("KeyWord", "%" + filter.KeyWord + "%") .Parameter("Account", "%" + filter.Account + "%") .QuerySingle(); } }