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