/// <summary>
/// 查询会员分销商申请
/// </summary>
/// <param name="filter">查询条件</param>
/// <remarks> 2016-04-08 刘伟豪 创建</remarks>
public void Seach(ref Pager <CBCrDealerApply> pager, ParaCrDealerApplyFilter filter)
{
ICrDealerApplyDao.Instance.Seach(ref pager, filter);
}
/// <summary>
/// 会员分销商申请查询
/// </summary>
/// <param name="pager">分页</param>
/// <param name="filter">查询参数</param>
public override void Seach(ref Pager <CBCrDealerApply> pager, ParaCrDealerApplyFilter condition)
{
using (var _context = Context.UseSharedConnection(true))
{
var sqlWhere = " 1=1 ";
if (condition.SysNo > 0)
{
sqlWhere += " and Ap.SysNo=@SysNo ";
}
if (condition.CustomerSysNo > 0)
{
sqlWhere += " and Ap.CustomerSysNo=@CustomerSysNo ";
}
if (condition.Status != 0)
{
sqlWhere += " and Ap.Status=@Status ";
}
if (condition.AppliedDateStartTime != null)
{
sqlWhere += " and Ap.AppliedDate >= @AppliedDateStartTime";
}
if (condition.AppliedDateEndTime != null)
{
sqlWhere += " and Ap.AppliedDate <= @AppliedDateEndTime";
}
if (condition.AuditedDateStartTime != null)
{
sqlWhere += " and Ap.AuditedDate >= @AuditedDateStartTime";
}
if (condition.AuditedDateEndTime != null)
{
sqlWhere += " and Ap.AuditedDate <= @AuditedDateEndTime";
}
if (!string.IsNullOrWhiteSpace(condition.KeyWord))
{
sqlWhere += " and (Cr.Name like @KeyWord or Cr.MobilePhoneNumber like @KeyWord)";
}
if (condition.LevelSysNo > 0)
{
sqlWhere += " and Cr.LevelSysNo=@LevelSysNo";
}
if (!string.IsNullOrWhiteSpace(condition.Name))
{
sqlWhere += " and Cr.Name like @Name";
}
if (!string.IsNullOrWhiteSpace(condition.MobilePhoneNumber))
{
sqlWhere += " and Cr.MobilePhoneNumber like @MobilePhoneNumber";
}
if (condition.DealerSysNo > -1 && condition.DealerSysNo != null)
{
sqlWhere += " and Cr.DealerSysNo = @DealerSysNo";
}
else
{
if (!condition.CanSearchAll)
{
sqlWhere += " and Cr.DealerSysNo = @BindDealerSysNo";
}
}
if (condition.BindStatus != null && condition.BindStatus > -1)
{
if (condition.BindStatus == 0)
{
sqlWhere += " and Ap.CustomerSysNo = 0 ";
}
if (condition.BindStatus > 0)
{
sqlWhere += " and Ap.CustomerSysNo > 0 ";
}
}
pager.Rows = _context.Select <CBCrDealerApply>(" Ap.*,Cr.Name,Cr.MobilePhoneNumber,Cr.LevelSysNo,Cr.LevelName,Cr.DealerSysNo,Cr.DealerName,cr.AreaName ")
.From(@"CrDealerApply Ap" +
@" Left Join (Select Cr.SysNo,Cr.Name,Cr.MobilePhoneNumber,Cr.LevelSysNo,Cl.LevelName,Ds.DealerName,Cr.DealerSysNo,(p.AreaName+' '+c.AreaName+' '+bsArea.AreaName) as AreaName
From CrCustomer Cr Left Join CrCustomerLevel Cl On Cr.LevelSysNo=Cl.SysNo Left Join DsDealer Ds On Ds.SysNo=Cr.DealerSysNo
left join bsArea on cr.AreaSysNo =bsArea.SysNo left join bsArea c on bsArea.ParentSysNo=c.SysNo left join bsArea p on p.SysNo=c.ParentSysNo ) Cr " +
" On Ap.CustomerSysNo=Cr.SysNo")
.Where(sqlWhere)
.Parameter("SysNo", condition.SysNo)
.Parameter("CustomerSysNo", condition.CustomerSysNo)
.Parameter("Status", condition.Status)
.Parameter("AppliedDateStartTime", condition.AppliedDateStartTime)
.Parameter("AppliedDateEndTime", condition.AppliedDateEndTime)
.Parameter("AuditedDateStartTime", condition.AuditedDateStartTime)
.Parameter("AuditedDateEndTime", condition.AuditedDateEndTime)
.Parameter("KeyWord", "%" + condition.KeyWord + "%")
.Parameter("LevelSysNo", condition.LevelSysNo)
.Parameter("Name", "%" + condition.Name + "%")
.Parameter("MobilePhoneNumber", "%" + condition.MobilePhoneNumber + "%")
.Parameter("DealerSysNo", condition.DealerSysNo)
.Parameter("BindDealerSysNo", condition.BindDealerSysNo)
.OrderBy(" Ap.AuditedDate Desc, Ap.AppliedDate Desc, Ap.SysNo Desc ")
.Paging(pager.CurrentPage, pager.PageSize)
.QueryMany();
pager.TotalRows = _context.Select <int>(" count(0) ")
.From(@"CrDealerApply Ap" +
" Left Join (Select Cr.SysNo,Cr.Name,Cr.MobilePhoneNumber,Cr.LevelSysNo,Cl.LevelName,Ds.DealerName,Cr.DealerSysNo From CrCustomer Cr Left Join CrCustomerLevel Cl On Cr.LevelSysNo=Cl.SysNo Left Join DsDealer Ds On Ds.SysNo=Cr.DealerSysNo) Cr " +
" On Ap.CustomerSysNo=Cr.SysNo")
.Where(sqlWhere)
.Parameter("SysNo", condition.SysNo)
.Parameter("CustomerSysNo", condition.CustomerSysNo)
.Parameter("Status", condition.Status)
.Parameter("AppliedDateStartTime", condition.AppliedDateStartTime)
.Parameter("AppliedDateEndTime", condition.AppliedDateEndTime)
.Parameter("AuditedDateStartTime", condition.AuditedDateStartTime)
.Parameter("AuditedDateEndTime", condition.AuditedDateEndTime)
.Parameter("KeyWord", "%" + condition.KeyWord + "%")
.Parameter("LevelSysNo", condition.LevelSysNo)
.Parameter("Name", "%" + condition.Name + "%")
.Parameter("MobilePhoneNumber", "%" + condition.MobilePhoneNumber + "%")
.Parameter("DealerSysNo", condition.DealerSysNo)
.Parameter("BindDealerSysNo", condition.BindDealerSysNo)
.QuerySingle();
}
}
/// <summary> /// 查询会员分销商申请 /// </summary> /// <param name="pager">分页</param> /// <param name="filter">查询条件</param> /// <remarks> 2016-04-08 刘伟豪 创建</remarks> public abstract void Seach(ref Pager <CBCrDealerApply> pager, ParaCrDealerApplyFilter filter);
