private async Task <ApiListResponse <SimpleStatisticsModel> > GetSesizariSectii(StatisticiTopSesizariQuery message, CancellationToken token)
{
var queryBuilder = new StatisticsQueryBuilder
{
Query = @"SELECT R.CountyCode AS Label, R.PollingStationNumber AS Code, COUNT(*) as Value
FROM Answers AS R
INNER JOIN OptionsToQuestions AS RD ON RD.Id = R.IdOptionToQuestion
INNER JOIN Observers O ON O.Id = R.IdObserver
INNER JOIN Ngos N ON O.IdNgo = N.Id
INNER JOIN Questions I ON I.Id = RD.IdQuestion
INNER JOIN FormSections fs on i.IdSection = fs.Id
INNER JOIN Forms f on fs.IdForm = f.Id
WHERE RD.Flagged = 1 AND N.IsActive =1",
CacheKey = "StatisticiSectii"
};
queryBuilder.AndOngFilter(message.Organizator, message.IdONG);
queryBuilder.AndFormularFilter(message.Formular);
queryBuilder.Append("GROUP BY R.CountyCode, R.PollingStationNumber");
// get or save paginated response in cache
return(await _cacheService.GetOrSaveDataInCacheAsync($"{queryBuilder.CacheKey}-{message.Page}",
async() =>
{
var records = await _context.ComposedStatistics
.FromSqlRaw(queryBuilder.GetPaginatedQuery(message.Page, message.PageSize))
.ToListAsync(cancellationToken: token);
return new ApiListResponse <SimpleStatisticsModel>
{
Data = records.Select(x => _mapper.Map <SimpleStatisticsModel>(x)).ToList(),
Page = message.Page,
PageSize = message.PageSize,
TotalItems = await _context.ComposedStatistics.FromSqlRaw(queryBuilder.Query).CountAsync(cancellationToken: token)
};
},
new DistributedCacheEntryOptions
{
AbsoluteExpirationRelativeToNow = new TimeSpan(message.CacheHours, message.CacheMinutes, message.CacheMinutes)
}
));
}
private async Task <ApiListResponse <SimpleStatisticsModel> > GetSesizariJudete(StatisticiTopSesizariQuery message, CancellationToken token)
{
var queryBuilder = new StatisticsQueryBuilder
{
Query = @"SELECT R.CountyCode AS Label, COUNT(*) as Value
FROM Answers AS R
INNER JOIN OptionsToQuestions AS RD ON RD.Id = R.IdOptionToQuestion
INNER JOIN Observers O ON O.Id = R.IdObserver
INNER JOIN Questions I ON I.Id = RD.IdQuestion
INNER JOIN Ngos N ON O.IdNgo = N.Id
INNER JOIN FormSections fs on i.IdSection = fs.Id
INNER JOIN Forms f on fs.IdForm = f.Id
WHERE RD.Flagged = 1 AND N.IsActive = 1",
CacheKey = "StatisticiJudete"
};
queryBuilder.AndOngFilter(message.Organizator, message.IdONG);
queryBuilder.AndFormularFilter(message.Formular);
queryBuilder.Append("GROUP BY R.CountyCode ORDER BY Value DESC");
// get or save all records in cache
var records = await _cacheService.GetOrSaveDataInCacheAsync(queryBuilder.CacheKey,
async() => await _context.SimpleStatistics
.FromSqlRaw(queryBuilder.Query)
.ToListAsync(cancellationToken: token),
new DistributedCacheEntryOptions
{
AbsoluteExpirationRelativeToNow = new TimeSpan(message.CacheHours, message.CacheMinutes, message.CacheMinutes)
}
);
// perform count and pagination on the records retrieved from the cache
var pagedList = records.Paginate(message.Page, message.PageSize);
return(new ApiListResponse <SimpleStatisticsModel>
{
Data = pagedList.Select(x => _mapper.Map <SimpleStatisticsModel>(x)).ToList(),
Page = message.Page,
PageSize = message.PageSize,
TotalItems = records.Count
});
}
public async Task <ApiListResponse <SimpleStatisticsModel> > Handle(StatisticiTopSesizariQuery message, CancellationToken token)
{
return(message.Grupare == StatisticsGroupingTypes.Judet
? await GetSesizariJudete(message, token)
: await GetSesizariSectii(message, token));
}
private async Task <ApiListResponse <SimpleStatisticsModel> > GetSesizariSectii(StatisticiTopSesizariQuery message)
{
StatisticiQueryBuilder queryBuilder = new StatisticiQueryBuilder
{
Query = @"SELECT R.CodJudet AS Label, R.NumarSectie AS Cod, COUNT(*) as Value
FROM Raspuns AS R
INNER JOIN RaspunsDisponibil AS RD ON RD.IdRaspunsDisponibil = R.IdRaspunsDisponibil
INNER JOIN Observator O ON O.IdObservator = R.IdObservator
INNER JOIN Intrebare I ON I.IdIntrebare = RD.IdIntrebare
WHERE RD.RaspunsCuFlag = 1",
CacheKey = "StatisticiSectii"
};
queryBuilder.AndOngFilter(message.Organizator, message.IdONG);
queryBuilder.AndFormularFilter(message.Formular);
queryBuilder.Append("GROUP BY R.CodJudet, R.NumarSectie");
// get or save paginated response in cache
return(await _cacheService.GetOrSaveDataInCacheAsync($"{queryBuilder.CacheKey}-{message.Page}",
async() =>
{
var records = await _context.StatisticiCompuse
.FromSql(queryBuilder.GetPaginatedQuery(message.Page, message.PageSize))
.ToListAsync();
return new ApiListResponse <SimpleStatisticsModel>
{
Data = records.Select(x => _mapper.Map <SimpleStatisticsModel>(x)).ToList(),
Page = message.Page,
PageSize = message.PageSize,
TotalItems = await _context.StatisticiCompuse.FromSql(queryBuilder.Query).CountAsync()
};
},
new DistributedCacheEntryOptions
{
AbsoluteExpirationRelativeToNow = new TimeSpan(message.CacheHours, message.CacheMinutes, message.CacheMinutes)
}
));
}
private async Task <ApiListResponse <SimpleStatisticsModel> > GetSesizariJudete(StatisticiTopSesizariQuery message)
{
StatisticiQueryBuilder queryBuilder = new StatisticiQueryBuilder
{
Query = @"SELECT R.CodJudet AS Label, COUNT(*) as Value
FROM Raspuns AS R
INNER JOIN RaspunsDisponibil AS RD ON RD.IdRaspunsDisponibil = R.IdRaspunsDisponibil
INNER JOIN Observator O ON O.IdObservator = R.IdObservator
INNER JOIN Intrebare I ON I.IdIntrebare = RD.IdIntrebare
WHERE RD.RaspunsCuFlag = 1",
CacheKey = "StatisticiJudete"
};
queryBuilder.AndOngFilter(message.Organizator, message.IdONG);
queryBuilder.AndFormularFilter(message.Formular);
queryBuilder.Append("GROUP BY R.CodJudet ORDER BY Value DESC");
// get or save all records in cache
var records = await _cacheService.GetOrSaveDataInCacheAsync(queryBuilder.CacheKey,
async() =>
{
return(await _context.StatisticiSimple
.FromSql(queryBuilder.Query)
.ToListAsync());
},
new DistributedCacheEntryOptions
{
AbsoluteExpirationRelativeToNow = new TimeSpan(message.CacheHours, message.CacheMinutes, message.CacheMinutes)
}
);
// perform count and pagination on the records retrieved from the cache
var pagedList = records.Paginate(message.Page, message.PageSize);
return(new ApiListResponse <SimpleStatisticsModel>
{
Data = pagedList.Select(x => _mapper.Map <SimpleStatisticsModel>(x)).ToList(),
Page = message.Page,
PageSize = message.PageSize,
TotalItems = records.Count()
});
}
public async Task <ApiListResponse <SimpleStatisticsModel> > Handle(StatisticiTopSesizariQuery message)
{
return(message.Grupare == TipGrupareStatistici.Judet
? await GetSesizariJudete(message)
: await GetSesizariSectii(message));
}
