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