public static StatisticItemDto ConvertToStatDto(dynamic row)
{
DateTime date = row.Day;
string day = date.ToString("yyyy-MM-dd");
StatisticItemDto dto = new StatisticItemDto
{
Count = (int)row.Count,
Day = day,
Mcc = row.Mcc,
TotalPrice = Math.Round(row.TotalPrice, 2),
PricePerSms = Math.Round(row.PricePerSms, 2)
};
return(dto);
}
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
});
}
