Ejemplo n.º 1
0
        public async Task <StatisticsOptionsModel> Handle(StatisticsOptionsQuery message, CancellationToken token)
        {
            var queryBuilder = new StatisticsQueryBuilder
            {
                Query    = $@"SELECT O.Text AS Label, O.Id AS Code, OQ.Flagged AS Flagged, COUNT(*) as Value
                  FROM Answers AS A
                  INNER JOIN OptionsToQuestions AS OQ ON OQ.Id = A.IdOptionToQuestion
                  INNER JOIN Options AS O ON O.Id = OQ.IdOption
                  INNER JOIN Observers Obs ON Obs.Id = A.IdObserver
                  INNER JOIN Ngos N ON O.IdNgo = N.Id
                  WHERE OQ.Id = {message.QuestionId} AND N.IsActive =1",
                CacheKey = $"StatisticiOptiuni-{message.QuestionId}"
            };

            queryBuilder.AndOngFilter(message.Organizator, message.IdONG);
            queryBuilder.Append("GROUP BY O.Text, O.Id, OQ.Flagged");

            return(await _cacheService.GetOrSaveDataInCacheAsync(queryBuilder.CacheKey,
                                                                 async() =>
            {
                var records = await _context.OptionsStatistics
                              .FromSqlRaw(queryBuilder.Query)
                              .ToListAsync(cancellationToken: token);

                return new StatisticsOptionsModel
                {
                    QuestionId = message.QuestionId,
                    Options = records.Select(s => new OptiuniStatisticsModel
                    {
                        OptionId = s.Code,
                        Label = s.Label,
                        Value = s.Value.ToString(),
                        Flagged = s.Flagged
                    })
                              .ToList(),
                    Total = records.Sum(s => s.Value)
                };
            },
                                                                 new DistributedCacheEntryOptions
            {
                AbsoluteExpirationRelativeToNow = new TimeSpan(message.CacheHours, message.CacheMinutes, message.CacheMinutes)
            }
                                                                 ));
        }
Ejemplo n.º 2
0
        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)
            }
                                                                 ));
        }
Ejemplo n.º 3
0
        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
            });
        }
Ejemplo n.º 4
0
        public async Task <ApiListResponse <SimpleStatisticsModel> > Handle(StatisticsObserversNumberQuery message, CancellationToken token)
        {
            var queryBuilder = new StatisticsQueryBuilder
            {
                Query    = @"SELECT COUNT(distinct a.IdObserver) as [Value], CountyCode as Label
                          FROM Answers a (nolock) 
                          INNER JOIN Observers o on a.IdObserver = o.Id
                          INNER JOIN Ngos N ON O.IdNgo = N.Id
                          WHERE N.IsActive = 1",
                CacheKey = "StatisticiObservatori"
            };

            queryBuilder.WhereOngFilter(message.Organizator, message.IdONG);
            //queryBuilder.Append("GROUP BY J.Name ORDER BY Value DESC");
            queryBuilder.Append("group by 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()
            });
        }