public PagedView <KeyValuePair <View.Beer.BeerStyle, int> > SumVolumeByStyle(ConsumationGetBinding binding)
{
using (var context = GetMainContext())
{
var grouped = context.Consumations
.WhereUser(UserId)
.Where(binding, context)
.Include(x => x.Beer)
.ThenInclude(x => x.BeerStyle)
.GroupBy(x => new
{
x.Beer.BeerStyle.ValueId,
x.Beer.BeerStyle.Name
});
return(grouped.OrderByDescending(x => x.Sum(y => y.Volume))
.Select(x => new KeyValuePair <View.Beer.BeerStyle, int>(
new ()
{
Id = x.Key.ValueId,
Name = x.Key.Name
}, x.Sum(y => y.Volume)))
.ToPagedView(binding, grouped.Count()));
}
}
public int SumVolume(ConsumationGetBinding binding)
{
using (var context = GetMainContext())
{
return(context.Consumations.WhereUser(UserId)
.Where(binding, context)
.Sum(x => x.Volume));
}
}
public int Count(ConsumationGetBinding binding)
{
using (var context = GetMainContext())
{
return(context.Consumations.WhereUser(UserId)
.Where(binding, context)
.Count());
}
}
public async Task <GoogleCloudDialogflowV2WebhookResponse> GetConsumationSum(GoogleCloudDialogflowV2WebhookRequest request)
{
var binding = new ConsumationGetBinding(request.ToFilteredBinding());
int sum = _consumationHandler.SumVolume(binding);
return(new GoogleCloudDialogflowV2WebhookResponse()
{
FulfillmentText = $"You've drank {sum / 1000} liters."
});
}
public int CountBeers(ConsumationGetBinding binding)
{
using (var context = GetMainContext())
{
return(context.Consumations.WhereUser(UserId)
.Where(binding, context)
.Select(x => x.BeerId)
.Distinct()
.Count());
}
}
public async Task <GoogleCloudDialogflowV2WebhookResponse> GetConsumationCount(GoogleCloudDialogflowV2WebhookRequest request)
{
var binding = new ConsumationGetBinding(request.ToFilteredBinding());
int count = _consumationHandler.Count(binding);
return(new GoogleCloudDialogflowV2WebhookResponse()
{
FulfillmentText = $"You've drank {count} beers."
});
}
public IEnumerable <KeyValuePair <int, int> > SumVolumeByYear(ConsumationGetBinding binding)
{
using (var context = GetMainContext())
{
return(context.Consumations.WhereUser(UserId)
.Where(binding, context)
.GroupBy(x => x.Date.Year)
.Select(x => new KeyValuePair <int, int>(x.Key, x.Sum(y => y.Volume)))
.ToList()
.OrderBy(x => x.Key));
}
}
public PagedView <View.Consumation.Consumation> Get(ConsumationGetBinding binding)
{
using (var context = GetMainContext())
{
return(context.Consumations.WhereUser(UserId)
.Where(binding, context)
.Include(x => x.Beer)
.ThenInclude(x => x.BeerStyle)
.OrderByDescending(x => x.Date)
.Select(x => new View.Consumation.Consumation(x))
.ToPagedView(binding));
}
}
public IEnumerable <GroupedByMonth <int> > SumVolumeByMonthOfYear(ConsumationGetBinding binding)
{
using (var context = GetMainContext())
{
return(context.Consumations.WhereUser(UserId)
.Where(binding, context)
.GroupBy(x => new { x.Date.Year, x.Date.Month })
.Select(x => new GroupedByMonth <int>(x.Sum(y => y.Volume), x.Key.Year, x.Key.Month))
.ToList()
.OrderBy(x => x.Year)
.ThenBy(x => x.Month));
}
}
public IEnumerable <KeyValuePair <string, int> > CountByMonth(ConsumationGetBinding binding)
{
using (var context = GetMainContext())
{
var to = binding.To ?? DateTime.Now;
return(context.Consumations.WhereUser(UserId)
.Where(binding, context)
.GroupBy(x => x.Date.ToString("MMMM"))
.Select(x => new KeyValuePair <string, int>(x.Key, x.Count()))
.ToList());
}
}
public IEnumerable <KeyValuePair <int, int> > CountByYear(ConsumationGetBinding binding)
{
using (var context = GetMainContext())
{
var to = binding.To ?? DateTime.Now;
return(context.Consumations.WhereUser(UserId)
.Where(binding, context)
.GroupBy(x => x.Date.Year)
.Select(x => new KeyValuePair <int, int>(x.Count(), x.Key))
.ToList()
.FillMissingYears(year => new KeyValuePair <int, int>(0, year), binding.From?.Year, to.Year));
}
}
public async Task <IEnumerable <KeyValuePair <int, int> > > AverageByMonth(ConsumationGetBinding binding)
{
using (var context = GetMainContext())
{
//TODO: Take leap years into account
return(await context.Consumations.WhereUser(UserId)
.Where(binding, context)
.GroupBy(x => x.Date.Year)
.OrderBy(x => x.Key)
.Select(x => new KeyValuePair <int, int>(x.Key, x.Sum(y => y.Volume) / 365))
.ToListAsync());
}
}
public IEnumerable <KeyValuePair <int, int> > SumVolumeByDayOfWeek(ConsumationGetBinding binding)
{
using (var sqlConnection = GetSqlConnection())
{
return(sqlConnection.Query <KeyValuePair <int, int> >(SqlLoader.Load(SqlScripts.GetConsumationSumByDayOfWeek),
new {
binding.From,
binding.To,
UserId = UserId
})
.Select(x => new KeyValuePair <int, int>(x.Key == 1 ? 6 : x.Key - 2, x.Value))
.OrderBy(x => x.Key));
}
}
public IEnumerable <KeyValuePair <string, int> > CountByMonthOfYear(ConsumationGetBinding binding)
{
using (var context = GetMainContext())
{
var to = binding.To ?? DateTime.Now;
return(context.Consumations.WhereUser(UserId)
.Where(binding, context)
.GroupBy(x => new { x.Date.Year, x.Date.Month })
.Select(x => new GroupedByMonth <int>(x.Count(), x.Key.Year, x.Key.Month))
.ToList()
.FillMissingMonths(datetime => new GroupedByMonth <int>(0, datetime.Year, datetime.Month), binding.From, to)
.Select(x => new KeyValuePair <string, int>($"{x.Year}-{x.Month}", x.Data)));
}
}
public IEnumerable <(DateTime From, DateTime To)> ConsecutiveDates(ConsumationGetBinding binding)
{
using (var context = GetMainContext())
{
return(context.Consumations.WhereUser(UserId)
.Where(binding, context)
.Select(x => x.Date)
.Distinct()
.ToList()
.ConsecutiveDates()
.Select(x => new { Range = x, Count = x.To.Subtract(x.From).Days + 1 })
.OrderByDescending(x => x.Count)
.Select(x => x.Range)
.ToList());
}
}
public async Task <IEnumerable <View.Country.Country> > GetCountries(ConsumationGetBinding binding)
{
using (var context = GetMainContext())
{
return(await context.Consumations
.WhereUser(UserId)
.Where(binding, context)
.Include(x => x.Beer)
.ThenInclude(x => x.BeerBrand)
.ThenInclude(x => x.Country)
.Select(x => x.Beer.BeerBrand.Country)
.Where(x => x != null)
.Distinct()
.Select(x => new View.Country.Country(x))
.ToListAsync());
}
}
public PagedView <KeyValuePair <View.Beer.Beer, int> > CountByBeer(ConsumationGetBinding binding)
{
using (var context = GetMainContext())
{
return(context.Consumations.WhereUser(UserId)
.Where(binding, context)
.Include(x => x.Beer)
.GroupBy(x => new
{
x.Beer.ValueId,
x.Beer.Name
})
.OrderByDescending(x => x.Count())
.Select(x => new KeyValuePair <View.Beer.Beer, int>(new View.Beer.Beer()
{
Id = x.Key.ValueId,
Name = x.Key.Name
}, x.Count()))
.ToPagedView(binding));
}
}
public IActionResult GetSumByDayOfWeek(ConsumationGetBinding binding) => Ok(_consumationHandler.SumVolumeByDayOfWeek(binding));
public IEnumerable <KeyValuePair <string, int> > GetCountByMonthOfYear([FromQuery] ConsumationGetBinding binding) => _consumationHandler.CountByMonthOfYear(binding);
public async Task <IEnumerable <KeyValuePair <int, decimal> > > AlcoholByYear(ConsumationGetBinding binding)
{
using (var context = GetMainContext())
{
return(await context.Consumations.WhereUser(UserId)
.Where(binding, context)
.GroupBy(x => x.Date.Year)
.OrderBy(x => x.Key)
.Select(x => new KeyValuePair <int, decimal>(x.Key, x.Sum(y => y.Volume * y.Beer.Abv / 100)))
.ToListAsync());
}
}
public IActionResult GetCountByYear([FromQuery] ConsumationGetBinding binding) => Ok(_consumationHandler.CountByYear(binding));
public IEnumerable <KeyValuePair <View.Beer.BeerServing, int> > SumVolumeByServing(ConsumationGetBinding binding)
{
using (var context = GetMainContext())
{
var grouped = context.Consumations.WhereUser(UserId)
.Where(binding, context)
.Include(x => x.BeerServing)
.GroupBy(x => new
{
x.BeerServing.Name,
x.BeerServing.ValueId
});
return(grouped.OrderByDescending(x => x.Sum(y => y.Volume))
.Select(x => new KeyValuePair <View.Beer.BeerServing, int>(new()
{
Id = x.Key.ValueId,
Name = x.Key.Name
}, x.Sum(y => y.Volume)))
.ToList());
}
}
public int GetBrandCount(ConsumationGetBinding binding) => _consumationHandler.CountBrands(binding);
public static IQueryable <Consumation> Where(this IQueryable <Consumation> query, ConsumationGetBinding binding, MainContext context)
{
var beerId = context.Beers.GetId(binding.BeerId);
var beerBrandId = context.BeerBrands.GetId(binding.BrandId);
var countryId = context.Countries.GetId(binding.CountryId);
var beerStyleId = context.BeerStyles.GetId(binding.StyleId);
return(query.Include(x => x.Beer)
.WhereIf(binding.From.HasValue, x => x.Date >= binding.From.Value)
.WhereIf(binding.To.HasValue, x => x.Date <= binding.To.Value)
.WhereIf(binding.Serving.HasValue, x => x.BeerServingId == (int)binding.Serving.Value)
.WhereIf(!string.IsNullOrWhiteSpace(binding.CountryId), x => x.Beer.BeerBrand.CountryId == countryId.Value)
.WhereIf(beerId.HasValue, x => x.BeerId == beerId.Value)
.WhereIf(!string.IsNullOrWhiteSpace(binding.StyleId), x => x.Beer.BeerStyleId == beerStyleId)
.WhereIf(beerBrandId.HasValue, x => x.Beer.BeerBrandId == beerBrandId.Value));
}
public IActionResult GetCountByBeer(ConsumationGetBinding binding) => Ok(_consumationHandler.CountByBeer(binding));
public async Task <IActionResult> GetCountryBoundaries(ConsumationGetBinding binding)
{
var countries = await _consumationHandler.GetCountries(binding);
return(Ok(_countryHandler.GetBoundaries(countries)));
}
public int GetSum(ConsumationGetBinding binding) => _consumationHandler.SumVolume(binding);
public IActionResult GetSumVolumeByCountry(ConsumationGetBinding binding) => Ok(_consumationHandler.SumVolumeByCountry(binding));
public async Task <IActionResult> GetCountries(ConsumationGetBinding binding) => Ok(await _consumationHandler.GetCountries(binding));
public IActionResult GetSumByMonthOfYear(ConsumationGetBinding binding) => Ok(_consumationHandler.SumVolumeByMonthOfYear(binding));
