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