// GET: Member public ActionResult GetMemberList(MembersParameters parameter) { PagedResult <MembersStatistics> membersStatisticsList = _memberService.GetMembersStatisticsList(parameter); List <SelectListItem> UserTypeList = EnumExt.GetSelectList(typeof(UserTypeEnum)); UserTypeList.Insert(0, new SelectListItem { Text = "全部", Value = "" }); ViewData["UserType"] = new SelectList(UserTypeList, "Value", "Text"); ViewBag.memberList = _memberService.GetMembersList(); return(View(membersStatisticsList)); }
/// <summary> /// 获取会员统计列表 /// </summary> /// <param name="parameter"></param> /// <returns></returns> public PagedResult <MembersStatistics> GetMembersStatisticsList(MembersParameters parameter) { StringBuilder strb = new StringBuilder("where 1 = 1"); if (!string.IsNullOrEmpty(parameter.UserType)) { strb.Append($" and UserType = {parameter.UserType} "); } if (!string.IsNullOrEmpty(parameter.MemberID)) { strb.Append($" and Members.MemberID = {parameter.MemberID} "); } strb.Append($" and (SocialSecurityPeople.SocialSecurityPeopleName like '%{parameter.SocialSecurityPeopleName}%' or SocialSecurityPeople.SocialSecurityPeopleName is null)"); string innersql = "select Members.MemberID,Max(Members.UserType) UserType, MAX(members.MemberName) MemberName,MAX(members.MemberPhone) MemberPhone, COUNT(SocialSecurityPeople.SocialSecurityPeopleID) SocialSecurityPeopleCount,MAX(ISNULL(members.Account,0)) Account," + " case when exists(" + " select * from SocialSecurityPeople" + " left join SocialSecurity on SocialSecurityPeople.SocialSecurityPeopleID = SocialSecurity.SocialSecurityPeopleID" + " left join AccumulationFund on SocialSecurityPeople.SocialSecurityPeopleID = AccumulationFund.SocialSecurityPeopleID" + $" where MemberID = members.MemberID and(SocialSecurity.Status = {(int)SocialSecurityStatusEnum.Renew} or AccumulationFund.Status = {(int)SocialSecurityStatusEnum.Renew})" + " ) " + " then '待续费' else '正常' end AccountStatus" + " from Members" + " left join SocialSecurityPeople on Members.MemberID = SocialSecurityPeople.MemberID" + $" {strb.ToString()}" + " group by Members.MemberID"; string sqlstr = "select * from (select ROW_NUMBER() OVER(ORDER BY t.MemberID )AS Row,t.* from" + $" ({innersql}) t) tt" + " where tt.Row BETWEEN @StartIndex AND @EndIndex"; List <MembersStatistics> memberList = DbHelper.Query <MembersStatistics>(sqlstr, new { StartIndex = parameter.SkipCount, EndIndex = parameter.TakeCount }); int totalCount = DbHelper.QuerySingle <int>($"select count(0) from ({innersql}) t"); return(new PagedResult <MembersStatistics> { PageIndex = parameter.PageIndex, PageSize = parameter.PageSize, TotalItemCount = totalCount, Items = memberList }); }