public async Task <ApiListResponse <SimpleStatisticsModel> > Handle(StatisticiNumarObservatoriQuery message)
{
//var queryBuilder = new StatisticiQueryBuilder
//{
// Query = @"SELECT J.Nume AS Label, COUNT(*) as Value
// FROM Judet J
// INNER JOIN SectieDeVotare AS SV ON SV.IdJudet = J.IdJudet
// INNER JOIN [Raspuns] AS R ON R.IdSectieDeVotare = SV.IdSectieDeVotarre
// INNER JOIN Observator O ON O.IdObservator = R.IdObservator",
// CacheKey = "StatisticiObservatori"
//};
var queryBuilder = new StatisticiQueryBuilder
{
Query = @"select count(distinct r.idobservator) as [Value], codjudet as Label
from raspuns r (nolock) inner join observator o on r.idobservator = o.idobservator ",
CacheKey = "StatisticiObservatori"
};
queryBuilder.WhereOngFilter(message.Organizator, message.IdONG);
//queryBuilder.Append("GROUP BY J.Nume ORDER BY Value DESC");
queryBuilder.Append("group by 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 <OptiuniModel> Handle(StatisticiOptiuniQuery message, CancellationToken token)
{
var queryBuilder = new StatisticiQueryBuilder
{
Query = $@"SELECT OB.Text AS Label, OB.Id AS Cod, RD.Flagged AS RaspunsCuFlag, COUNT(*) as Value
FROM Answers AS R
INNER JOIN OptionsToQuestions AS RD ON RD.Id = R.IdOptionToQuestion
INNER JOIN Options AS OB ON OB.Id = RD.Id
INNER JOIN Observers O ON O.Id = R.IdObserver
WHERE RD.Id = {message.IdIntrebare}",
CacheKey = $"StatisticiOptiuni-{message.IdIntrebare}"
};
queryBuilder.AndOngFilter(message.Organizator, message.IdONG);
queryBuilder.Append("GROUP BY OB.Text, OB.Id, RD.Flagged");
return(await _cacheService.GetOrSaveDataInCacheAsync(queryBuilder.CacheKey,
async() =>
{
var records = await _context.StatisticiOptiuni
.FromSql(queryBuilder.Query)
.ToListAsync();
return new OptiuniModel
{
IdIntrebare = message.IdIntrebare,
Optiuni = records.Select(s => new OptiuniStatisticsModel
{
IdOptiune = s.Cod,
Label = s.Label,
Value = s.Value.ToString(),
RaspunsCuFlag = s.RaspunsCuFlag
})
.ToList(),
Total = records.Sum(s => s.Value)
};
},
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()
});
}
private async Task <ApiListResponse <SimpleStatisticsModel> > GetSesizariSectii(StatisticiTopSesizariQuery message, CancellationToken token)
{
var queryBuilder = new StatisticiQueryBuilder
{
Query = @"SELECT R.CountyCode AS Label, R.PollingStationNumber AS Cod, 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
WHERE RD.Flagged = 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.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> > 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)
}
));
}
public async Task <ApiListResponse <SimpleStatisticsModel> > Handle(StatisticiNumarObservatoriQuery message, CancellationToken token)
{
var queryBuilder = new StatisticiQueryBuilder
{
Query = @"select count(distinct a.IdObserver) as [Value], CountyCode as Label
from Answers a (nolock) inner join Observers o on a.IdObserver = o.Id ",
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() =>
{
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()
});
}
