public IEnumerable <View.Country> GetVisited(TripGetBinding binding)
{
using (var context = GetMainContext())
{
var countries = context.TripCities
.Include(x => x.Trip)
.Include(x => x.City)
.Where(x => x.Trip.UserId == UserId)
.Where(x => x.Trip.TimestampEnd < DateTime.Now)
.WhereIf(binding.From.HasValue, x => x.Trip.TimestampEnd > binding.From.Value)
.WhereIf(binding.To.HasValue, x => x.Trip.TimestampStart < binding.To.Value)
.Select(x => new { EnteredOn = x.EnteredOn, x.City.Country, TimestampStart = x.Trip.TimestampStart })
.OrderBy(x => x.TimestampStart)
.ThenBy(x => x.EnteredOn)
.Select(x => new View.Country(x.Country))
.ToList();
//TODO: add user birth city
//var birthCountry = User.BirthCityId.HasValue ? context.Cities.Include(x => x.Country)
// .SingleOrDefault(x => x.Id == User.BirthCityId.Value)
// .Country : null;
//if (birthCountry != null)
//{
// var existingBirthCountry = countries.FirstOrDefault(x => x.Id == birthCountry.ValueId);
// if (existingBirthCountry != null)
// countries.Remove(existingBirthCountry);
// countries.Insert(0, new View.Country(birthCountry));
//}
return(countries.Distinct(new View.CountryComparer()));
}
}
public async Task <IEnumerable <KeyValuePair <int, int> > > DaysByYear(TripGetBinding binding)
{
using (var context = GetMainContext())
{
var query = context.Trips.WhereUser(UserId)
.Where(binding)
.Where(x => x.TimestampEnd < DateTime.Now);
var left = await query.Where(x => x.TimestampEnd.Year != x.TimestampStart.Year)
.Select(x => new Tuple <DateTime, DateTime>(x.TimestampStart, new DateTime(x.TimestampStart.Year, 12, 31, 23, 59, 59)))
.ToListAsync();
var right = await query.Where(x => x.TimestampEnd.Year != x.TimestampStart.Year)
.Select(x => new Tuple <DateTime, DateTime>(new DateTime(x.TimestampEnd.Year, 1, 1, 0, 0, 0), x.TimestampEnd))
.ToListAsync();
var center = await query.Where(x => x.TimestampEnd.Year == x.TimestampStart.Year)
.Select(x => new Tuple <DateTime, DateTime>(x.TimestampStart, x.TimestampEnd))
.ToListAsync();
return(left.Concat(right)
.Concat(center)
.GroupBy(x => x.Item1.Year)
.Select(x => new KeyValuePair <int, int>(x.Key, x.Sum(y => y.Item2.Date.Subtract(y.Item1.Date).Days + (y.Item2.Hour > 6 ? 1 : 0))))
.OrderBy(x => x.Key));
}
}
public static IQueryable <Trip> Where(this IQueryable <Trip> query, TripGetBinding b) => query.WhereIf(b.Search, x => x.Name.ToLower().Contains(b.Search.ToLower())) .WhereIf(b.From.HasValue, x => x.TimestampEnd > b.From.Value) .WhereIf(b.To.HasValue, x => x.TimestampStart < b.To.Value) .WhereIf(b.CityId, x => x.Cities.Select(y => y.ValueId).Any(y => b.CityId.Contains(y))) .WhereIf(b.IsDomestic.HasValue, x => x.IsDomestic == b.IsDomestic) .WhereIf(b.CountryId, x => x.Cities.Select(y => y.Country.ValueId).Any(y => b.CountryId.Contains(y)));
public static IOrderedQueryable <Trip> OrderBy(this IQueryable <Trip> query, TripGetBinding b)
{
switch (b.OrderBy)
{
case TripSort.Date:
return(query.OrderBy(b.OrderAscending, x => x.TimestampStart));
case TripSort.Duration:
return(query.OrderBy(b.OrderAscending, x => x.TimestampEnd.Subtract(x.TimestampStart).TotalMinutes));
default:
return(query.OrderBy(b.OrderAscending, x => x.TimestampStart));
}
}
public PagedView <View.Trip.Trip> Get(TripGetBinding binding)
{
using (var context = GetMainContext())
{
var query = context.Trips
.WhereUser(UserId)
.Include(x => x.Cities)
.ThenInclude(x => x.Country)
.Where(binding);
return(query.OrderBy(binding)
.Select(x => new View.Trip.Trip(x))
.ToPagedView(binding));
}
}
