public IActionResult GetList(int agency, int status, int page) { ApplicationListDto result = _applicationService.GetApplicationList( new GetApplicationList() { agency = agency, page = page, process = Zit.Dto.DtoModels.Process.assessment, status = status }); return(new OkObjectResult(new WrappedResponse <ApplicationListDto>() { content = result })); }
public ApplicationListDto GetApplicationList(GetApplicationList getApplicationList) { int intGlobalStatus = getApplicationList.status; int intGlobalAgency = getApplicationList.agency; int countToSkip = (getApplicationList.page - 1) * 50; int userId = _userContextRepository.GetCurrentUserId(); int process = (int)getApplicationList.process; string query = $@" select r.{nameof(Applications.Id)} as {nameof(ApplicationListItemDbDto.ApplicationId)} , r.{nameof(Applications.ApplicationDate)} as {nameof(ApplicationListItemDbDto.ApplicationDate)} , r.{nameof(Applications.CAT_RECORD_TYPE)} as {nameof(ApplicationListItemDbDto.CAT_RECORD_TYPE)} , r.{nameof(Applications.CAT_RECORD_NUM)} as {nameof(ApplicationListItemDbDto.CAT_RECORD_NUM)} , r.{nameof(Applications.DOCUMENT_OUT_DATE)} as {nameof(ApplicationListItemDbDto.DOCUMENT_OUT_DATE)} , r.{nameof(Applications.DOCUMENT_OUT_NUM)} as {nameof(ApplicationListItemDbDto.DOCUMENT_OUT_NUM)} , r.{nameof(Applications.Process)} as {nameof(ApplicationListItemDbDto.Process)} , r.{nameof(Applications.REESTR_NUM)} as {nameof(ApplicationListItemDbDto.REESTR_NUM)} , r.{nameof(Applications.Status)} as {nameof(ApplicationListItemDbDto.Status)} , aa.{nameof(Applications_Assessment.OTI_EXPERTIES_TYPE)} as {nameof(ApplicationListItemDbDto.OTI_EXPERTIES_TYPE)} , aa.{nameof(Applications_Assessment.OTI_EXPERTIES_DATE)} as {nameof(ApplicationListItemDbDto.OTI_EXPERTIES_DATE)} , u.{nameof(Users.ScopeUser)} from {nameof(Applications)} r join {nameof(Applications_Assessment)} aa on r.{nameof(Applications.Id)} = aa.{nameof(Applications_Assessment.Application_Id)} left join {nameof(Users)} u on r.{nameof(Applications.User_Id)} = u.{nameof(Users.Id)} join ( SELECT {nameof(Applications.Id)} FROM ( SELECT {nameof(Applications.Id)}, MAX({nameof(Applications.ApplicationDate)}) as LastDate from {nameof(Applications)} WHERE {nameof(Applications.Status)} = @{nameof(intGlobalStatus)} and {nameof(Applications.Agency)} = @{nameof(intGlobalAgency)} and {nameof(Applications.User_Id)} = @{nameof(userId)} and {nameof(Applications.Process)} = @{nameof(process)} group by {nameof(Applications.Id)} ) SubQueryAlias order by LastDate desc OFFSET @{nameof(countToSkip)} ROWS -- skip FETCH NEXT 50 ROWS ONLY -- take ) t on r.{nameof(Applications.Id)} = t.{nameof(Applications.Id)} "; string countQuery = $@" SELECT COUNT({nameof(Applications.Id)}) from {nameof(Applications)} WHERE {nameof(Applications.Status)} = @{nameof(intGlobalStatus)} and {nameof(Applications.Agency)} = @{nameof(intGlobalAgency)} and {nameof(Applications.User_Id)} = @{nameof(userId)} and {nameof(Applications.Process)} = @{nameof(process)} "; var resultDb = _unitOfWork.DbConnection.Query <ApplicationListItemDbDto>(query, new { intGlobalStatus, intGlobalAgency, countToSkip, userId, process }, transaction: _unitOfWork.DbTransaction); var result = resultDb.Select(x => _listItemMapper.Map(x)); ApplicationListDto applicationListDto = new ApplicationListDto(); applicationListDto.TotalCount = _unitOfWork.DbConnection.QuerySingleOrDefault <int>(countQuery, new { intGlobalStatus, intGlobalAgency, userId, process }, transaction: _unitOfWork.DbTransaction); applicationListDto.SelectedElements = result.ToArray(); return(applicationListDto); }