/// <summary>
/// 帐号分页查询
/// </summary>
/// <param name="parameter"></param>
/// <returns></returns>
public PagingModel<Account> GetPagingAccount(AccountParam parameter)
{
PagingModel<Account> pagingModel = new PagingModel<Account>();
IAccountDal accountDal = new AccountDal();
//过滤
parameter.OpenId = DotNet.Utils.Untility.StringHelper.FilterHtml(parameter.OpenId);
parameter.LoginName = DotNet.Utils.Untility.StringHelper.FilterHtml(parameter.LoginName);
parameter.Mobile = DotNet.Utils.Untility.StringHelper.FilterHtml(parameter.Mobile);
parameter.Email = DotNet.Utils.Untility.StringHelper.FilterHtml(parameter.Email);
pagingModel = accountDal.GetPaging(parameter);
return pagingModel;
}
public ActionResult AccountList()
{
#region 域选择
IDomainService domainService = new DomainService();
var domainList = domainService.GetListDomain(new DomainParam() { });
List<SelectListItem> domainSelectList = new List<SelectListItem>()
{
new SelectListItem(){ Text="全部",Value="0",Selected=true }
};
foreach (var domain in domainList)
{
SelectListItem item = new SelectListItem()
{
Text = domain.DomainName,
Value = domain.DomainId.ToString(),
};
domainSelectList.Add(item);
}
this.ViewData["submitDomainId"] = domainSelectList;
#endregion
int submitDomainId = DotNet.Utils.Untility.ConvertFactory.ToInt32(HttpUtility.HtmlEncode(Request["submitDomainId"]), 0);
string openId = HttpUtility.HtmlEncode(Request["openId"]);
string loginName = HttpUtility.HtmlEncode(Request["loginName"]);
string mobile = HttpUtility.HtmlEncode(Request["mobile"]);
string email = HttpUtility.HtmlEncode(Request["email"]);
IAccountService accountService = new AccountService();
AccountParam parameter = new AccountParam()
{
PageIndex = DotNet.Utils.Untility.ConvertFactory.ToInt32(HttpUtility.UrlEncode(Request["page"]), 1),
PageSize = 12,
SubmitDomainId = submitDomainId,
IsPaging = true,
OpenId = openId,
LoginName=loginName,
Mobile=mobile,
Email=email,
AccountId = DotNet.Utils.Untility.ConvertFactory.ToInt32(HttpUtility.UrlEncode(Request["accountId"]), 0)
};
var model = accountService.GetPagingAccount(parameter);
return View(model);
}
/// <summary>
/// 帐号分页查询
/// </summary>
/// <param name="parameter"></param>
/// <returns></returns>
public PagingModel<Account> GetPaging(AccountParam parameter)
{
PagingModel<Account> pagingModel = new PagingModel<Account>()
{
PageIndex = parameter.PageIndex,
PageSize = parameter.PageSize
};
using (var connection = ConnectionFactory.GetMasterSql())
{
//跳过的页数
int skip = (parameter.PageIndex - 1) * parameter.PageSize;
StringBuilder sb = new StringBuilder();
sb.AppendFormat(@" and DelFlag=@DelFlag ");
#region where条件和参数
DynamicParameters param = new DynamicParameters();
param.Add("@DelFlag", (int)DelFlagEnum.Noraml);
//按照帐号的OpenId进行搜索
if (!string.IsNullOrEmpty(parameter.OpenId))
{
sb.AppendFormat(" and OpenId=@OpenId");
param.Add("@OpenId", parameter.OpenId);
}
//按照帐号Id进行搜索
if (parameter.AccountId > 0)
{
sb.AppendFormat(" and AccountId=@AccountId");
param.Add("@AccountId", parameter.AccountId);
}
//按照提交域的Id进行查询
if (parameter.SubmitDomainId > 0)
{
sb.AppendFormat(" and SubmitDomainId=@SubmitDomainId");
param.Add("@SubmitDomainId", parameter.SubmitDomainId);
}
//按照登录名查询
if (!string.IsNullOrEmpty(parameter.LoginName))
{
sb.AppendFormat(" and LoginName=@LoginName");
param.Add("@LoginName", parameter.LoginName);
}
//按照绑定手机号码查询
if (!string.IsNullOrEmpty(parameter.Mobile))
{
sb.AppendFormat(" and Mobile=@Mobile");
sb.AppendFormat(" and MobileBinding=@MobileBinding");
param.Add("@Mobile", parameter.Mobile);
param.Add("@MobileBinding",(int)BindingEnum.Binded);
}
//按照绑定Email查询
if (!string.IsNullOrEmpty(parameter.Email))
{
sb.AppendFormat(" and Email=@Email");
sb.AppendFormat(" and EmailBinding=@EmailBinding");
param.Add("@Email", parameter.Email);
param.Add("@EmailBinding", (int)BindingEnum.Binded);
}
#endregion
string countSql = string.Format("Select count(*) from B_Account where 1=1 {0}", sb.ToString());
var totalCount = connection.ExecuteScalar<int>(countSql, param);
pagingModel.TotalRecord = totalCount;
string sql = string.Format("Select top {0} * from (Select row_number() over (order by AccountId) AS RowNumber,* from B_Account ) as tb where RowNumber>{1} {2}", parameter.PageSize, skip, sb.ToString());
var query = connection.Query<Account>(sql, param);
pagingModel.Data = query.ToList();
}
return pagingModel;
}
