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