public IAsyncEnumerable <GameView> GetGames(GameQueryOptions opts)
{
var q = new SqlKata.Query("games_view");
if (opts.Order == SortOrder.Desc)
{
q.OrderByDesc("season", "day");
}
else
{
q.OrderBy("season", "day");
}
if (opts.Season != null)
{
q.Where("season", opts.Season.Value);
}
if (opts.Day != null)
{
q.Where("day", opts.Day.Value);
}
if (opts.Before != null)
{
q.Where("start_time", "<", opts.Before.Value);
}
if (opts.After != null)
{
q.Where("start_time", ">", opts.After.Value);
}
if (opts.HasOutcomes != null)
{
q.Where("has_outcomes", opts.HasOutcomes.Value);
}
if (opts.HasStarted != null)
{
q.Where("has_started", opts.HasStarted.Value);
}
if (opts.HasFinished != null)
{
q.Where("has_finished", opts.HasFinished.Value);
}
if (opts.Team != null)
{
q.Where(q => q.WhereIn("home_team", opts.Team).OrWhereIn("away_team", opts.Team));
}
if (opts.Pitcher != null)
{
q.Where(q => q.WhereIn("home_pitcher", opts.Pitcher).OrWhereIn("away_pitcher", opts.Pitcher));
}
if (opts.Weather != null)
{
q.WhereIn("weather", opts.Weather);
}
if (opts.Count != null)
{
q.Limit(opts.Count.Value);
}
return(_db.QueryKataAsync <GameView>(q));
}
// dto has properties for the paging, sorting, and filtering route segments defined in the Startup.cs file
public ViewResult List(GameGridDTO values)
{
// get grid builder, which loads route segment values and stores them in session
var builder = new GameGridBuilder(HttpContext.Session, values,
defaultSortField: nameof(Game.Name));
// create a GameQueryOptions object to build a query expression for a page of data
var options = new GameQueryOptions
{
Includes = "Genre, Type, Format, Rating, Publisher",
OrderByDirection = builder.CurrentRoute.SortDirection,
PageNumber = builder.CurrentRoute.PageNumber,
PageSize = builder.CurrentRoute.PageSize
};
// call the SortFilter() method of the GameQueryOptions object and pass it the builder
// object. It uses the route information and the properties of the builder object to
// add sort and filter options to the query expression.
options.SortFilter(builder);
// create view model and add page of game data, data for drop-downs,
// the current route, and the total number of pages.
var vm = new GameListViewModel
{
Games = data.Games.List(options),
Genres = data.Genres.List(new QueryOptions <Genre>
{
OrderBy = g => g.Name
}),
Types = data.Types.List(new QueryOptions <Type>
{
OrderBy = t => t.Name
}),
Formats = data.Formats.List(new QueryOptions <Format>
{
OrderBy = f => f.Name
}),
Ratings = data.Ratings.List(new QueryOptions <Rating>
{
OrderBy = r => r.Name
}),
Publishers = data.Publishers.List(new QueryOptions <Publisher>
{
OrderBy = p => p.Name
}),
CurrentRoute = builder.CurrentRoute,
TotalPages = builder.GetTotalPages(data.Games.Count)
};
// pass view model to view
return(View(vm));
}
public async Task <IActionResult> GetGames([FromQuery] GameQueryOptions opts)
{
var games = await _store.GetGames(new GameStore.GameQueryOptions
{
Before = opts.Before,
After = opts.After,
Count = opts.Count,
Season = opts.Season,
Tournament = opts.Tournament,
Day = opts.Day,
HasOutcomes = opts.Outcomes,
HasStarted = opts.Started,
HasFinished = opts.Finished,
Order = opts.Order,
Team = opts.Team,
Pitcher = opts.Pitcher,
Weather = opts.Weather,
Sim = opts.Sim
}).ToListAsync();
if (opts.Format == ResponseFormat.Json)
{
return(Ok(new ApiResponse <ApiGame>
{
Data = games.Select(g => new ApiGame(g))
}));
}
else
{
await using var sw = new StringWriter();
await using (var w = new CsvWriter(sw, CultureInfo.InvariantCulture))
{
w.Configuration.TypeConverterOptionsCache.GetOptions <DateTimeOffset?>().Formats = new [] { "yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'" };
w.Configuration.TypeConverterCache.AddConverter <bool>(new LowercaseBooleanConverter());
await w.WriteRecordsAsync(games.Select(x => new CsvGame(x)));
}
return(Ok(sw.ToString()));
}
}
