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