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)); } }