/// <summary> /// 公积金待办停业务 /// </summary> /// <param name="parameter"></param> /// <returns></returns> public async Task <ActionResult> AccumulationFundWaitingStop(AccumulationFundParameter parameter) { ViewData["SocialSecurityPeopleName"] = parameter.SocialSecurityPeopleName; ViewData["IdentityCard"] = parameter.IdentityCard; PagedResult <AccumulationFundShowModel> list = await _accumulationFundService.GetAccumulationFundList(parameter); List <SelectListItem> UserTypeList = EnumExt.GetSelectList(typeof(UserTypeEnum)); UserTypeList.Insert(0, new SelectListItem { Text = "全部", Value = "" }); ViewData["UserType"] = new SelectList(UserTypeList, "Value", "Text", parameter.UserType); return(View(list)); }
/// <summary> /// 获取公积金列表 /// </summary> /// <param name="parameter"></param> /// <returns></returns> public async Task <PagedResult <AccumulationFundShowModel> > GetAccumulationFundList(AccumulationFundParameter parameter) { string userTypeSql = string.IsNullOrEmpty(parameter.UserType) ? "1=1" : "UserType=" + parameter.UserType; string innersqlstr = $@"SELECT dbo.Members.UserType, dbo.SocialSecurityPeople.SocialSecurityPeopleID, dbo.SocialSecurityPeople.SocialSecurityPeopleName, dbo.SocialSecurityPeople.IdentityCard, dbo.SocialSecurityPeople.HouseholdProperty, dbo.AccumulationFund.AccumulationFundID, dbo.AccumulationFund.AccumulationFundArea, dbo.AccumulationFund.AccumulationFundBase, dbo.AccumulationFund.PayProportion, dbo.AccumulationFund.PayTime, dbo.AccumulationFund.PayMonthCount, dbo.AccumulationFund.PayBeforeMonthCount, dbo.AccumulationFund.Status, dbo.Members.MemberName, dbo.Members.MemberID, dbo.AccumulationFund.StopDate, dbo.AccumulationFund.ApplyStopDate, 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 1 else 0 end IsArrears FROM dbo.AccumulationFund INNER JOIN dbo.SocialSecurityPeople ON dbo.AccumulationFund.SocialSecurityPeopleID = dbo.SocialSecurityPeople.SocialSecurityPeopleID INNER JOIN dbo.Members ON dbo.SocialSecurityPeople.MemberID = dbo.Members.MemberID"; string sql = $"select * from (select ROW_NUMBER() OVER(ORDER BY S.AccumulationFundID )AS Row,s.* from ({innersqlstr}) s where " + userTypeSql + $" and Status = {parameter.Status} and SocialSecurityPeopleName like '%{parameter.SocialSecurityPeopleName}%' and IdentityCard like '%{parameter.IdentityCard}%' ) ss WHERE ss.Row BETWEEN @StartIndex AND @EndIndex"; List <AccumulationFundShowModel> modelList = await DbHelper.QueryAsync <AccumulationFundShowModel>(sql, new { StartIndex = parameter.SkipCount, EndIndex = parameter.TakeCount }); int totalCount = await DbHelper.QuerySingleAsync <int>($"SELECT COUNT(0) AS TotalCount FROM ({innersqlstr}) s where " + userTypeSql + $" and Status = {parameter.Status} and SocialSecurityPeopleName like '%{parameter.SocialSecurityPeopleName}%' and IdentityCard like '%{parameter.IdentityCard}%'"); return(new PagedResult <AccumulationFundShowModel> { PageIndex = parameter.PageIndex, PageSize = parameter.PageSize, TotalItemCount = totalCount, Items = modelList }); }