public async Task <GetStatisticsResponse> GetStatiststics(GetStatisticsRequest dto)
{
string query = string.Empty;
List <dynamic> result = new List <dynamic>();
List <StatisticItemDto> statItems = new List <StatisticItemDto>();
bool filterByMcc = dto.MccList?.Any() ?? false;
string queryNoMccFilter = $@"SELECT count(s.id) as Count, date(s.created) as Day, c.mcc as Mcc, sum(c.pricePerSms) as TotalPrice, c.PricePerSms
FROM app2sms.Sms s
INNER JOIN app2sms.Country c on c.id = s.countryId
where date(s.created) >= @dateFrom and date(s.created) <= @dateTo
group by c.mcc, date(s.created)";
string queryAllFilters = @"SELECT count(s.id) as Count, date(s.created) as Day, c.mcc as Mcc, sum(c.pricePerSms) as TotalPrice, c.PricePerSms
FROM app2sms.Sms s
INNER JOIN app2sms.Country c on c.id = s.countryId
where date(s.created) >= @dateFrom and date(s.created) <= @dateTo
and c.mcc in (@MccList)
group by c.mcc, date(s.created)";
query = filterByMcc ? queryAllFilters : queryNoMccFilter;
using (var db = _dbFactory.OpenDbConnection())
{
result = await db.SelectAsync <dynamic>(query, new { dto.MccList, dto.DateFrom, dto.DateTo });
}
result.ForEach(row =>
{
StatisticItemDto item = ConvertExtensions.ConvertToStatDto(row);
statItems.Add(item);
});
return(new GetStatisticsResponse()
{
StatisticsItems = statItems
});
}