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