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