public async Task <ApiListResponse <RaspunsModel> > Handle(RaspunsuriQuery message) { string queryUnPaged = $@"SELECT IdSectieDeVotare AS IdSectie, R.IdObservator AS IdObservator, O.NumeIntreg AS Observator, CONCAT(CodJudet, ' ', NumarSectie) AS Sectie, MAX(DataUltimeiModificari) AS DataUltimeiModificari FROM Raspuns R INNER JOIN OBSERVATOR O ON O.IdObservator = R.IdObservator INNER JOIN RaspunsDisponibil RD ON RD.IdRaspunsDisponibil = R.IdRaspunsDisponibil WHERE RD.RaspunsCuFlag = {Convert.ToInt32(message.Urgent)}"; if (!message.Organizator) { queryUnPaged = $"{queryUnPaged} AND O.IdOng = {message.IdONG}"; } queryUnPaged = $"{queryUnPaged} GROUP BY IdSectieDeVotare, CodJudet, NumarSectie, R.IdObservator, O.NumeIntreg, CodJudet"; var queryPaged = $@"{queryUnPaged} ORDER BY DataUltimeiModificari DESC OFFSET {(message.Page - 1) * message.PageSize} ROWS FETCH NEXT {message.PageSize} ROWS ONLY"; var sectiiCuObservatoriPaginat = await _context.RaspunsSectie .FromSql(queryPaged) .ToListAsync(); var count = await _context.RaspunsSectie .FromSql(queryUnPaged) .CountAsync(); return(new ApiListResponse <RaspunsModel> { Data = sectiiCuObservatoriPaginat.Select(x => _mapper.Map <RaspunsModel>(x)).ToList(), Page = message.Page, PageSize = message.PageSize, TotalItems = count }); }
public async Task <ApiListResponse <RaspunsModel> > Handle(RaspunsuriQuery message, CancellationToken cancellationToken) { var queryUnPaged = $@"SELECT IdPollingStation AS IdSectie, R.IdObserver AS IdObservator, O.Name AS Observator, CONCAT(CountyCode, ' ', PollingStationNumber) AS Sectie, MAX(LastModified) AS DataUltimeiModificari FROM Answers R INNER JOIN Observers O ON O.Id = R.IdObserver INNER JOIN OptionsToQuestions RD ON RD.Id = R.IdOptionToQuestion WHERE RD.Flagged = {Convert.ToInt32(message.Urgent)}"; if (!message.Organizator) { queryUnPaged = $"{queryUnPaged} AND O.IdNgo = {message.IdONG}"; } queryUnPaged = $"{queryUnPaged} GROUP BY IdPollingStation, CountyCode, PollingStationNumber, R.IdObserver, O.Name, CountyCode"; var queryPaged = $@"{queryUnPaged} ORDER BY MAX(LastModified) DESC OFFSET {(message.Page - 1) * message.PageSize} ROWS FETCH NEXT {message.PageSize} ROWS ONLY"; var sectiiCuObservatoriPaginat = await _context.RaspunsSectie .FromSql(queryPaged) .ToListAsync(cancellationToken: cancellationToken); var count = await _context.RaspunsSectie .FromSql(queryUnPaged) .CountAsync(cancellationToken: cancellationToken); return(new ApiListResponse <RaspunsModel> { Data = sectiiCuObservatoriPaginat.Select(x => _mapper.Map <RaspunsModel>(x)).ToList(), Page = message.Page, PageSize = message.PageSize, TotalItems = count }); }