public PagingSpec <Movie> MoviesWhereCutoffUnmet(PagingSpec <Movie> pagingSpec, List <QualitiesBelowCutoff> qualitiesBelowCutoff)
{
//I know this is bad, but if you have a better Idea please tell me.
if (pagingSpec.SortKey == "downloadedQuality")
{
var mapper = _database.GetDataMapper();
var offset = pagingSpec.PagingOffset();
var limit = pagingSpec.PageSize;
var direction = "ASC";
if (pagingSpec.SortDirection == NzbDrone.Core.Datastore.SortDirection.Descending)
{
direction = "DESC";
}
var whereClause = BuildQualityCutoffWhereClauseSpecial(qualitiesBelowCutoff);
var q = mapper.Query <Movie>($"SELECT * from \"Movies\" , \"MovieFiles\", \"QualityDefinitions\" WHERE Movies.MovieFileId=MovieFiles.Id AND instr(MovieFiles.Quality, ('quality\": ' || QualityDefinitions.Quality || \",\")) > 0 AND {whereClause} ORDER BY QualityDefinitions.Title {direction} LIMIT {offset},{limit};");
var q2 = mapper.Query <Movie>($"SELECT * from \"Movies\" , \"MovieFiles\", \"QualityDefinitions\" WHERE Movies.MovieFileId=MovieFiles.Id AND instr(MovieFiles.Quality, ('quality\": ' || QualityDefinitions.Quality || \",\")) > 0 AND {whereClause} ORDER BY QualityDefinitions.Title ASC;");
//var ok = q.BuildQuery();
pagingSpec.Records = q.ToList();
pagingSpec.TotalRecords = q2.Count();
}
else
{
pagingSpec.TotalRecords = MoviesWhereCutoffUnmetQuery(pagingSpec, qualitiesBelowCutoff).GetRowCount();
pagingSpec.Records = MoviesWhereCutoffUnmetQuery(pagingSpec, qualitiesBelowCutoff).ToList();
}
return(pagingSpec);
}
/*public override PagingSpec<Movie> GetPaged(PagingSpec<Movie> pagingSpec)
* {
* if (pagingSpec.SortKey == "downloadedQuality")
* {
* var mapper = _database.GetDataMapper();
* var offset = pagingSpec.PagingOffset();
* var limit = pagingSpec.PageSize;
* var direction = "ASC";
* if (pagingSpec.SortDirection == NzbDrone.Core.Datastore.SortDirection.Descending)
* {
* direction = "DESC";
* }
* var q = mapper.Query<Movie>($"SELECT * from \"Movies\" , \"MovieFiles\", \"QualityDefinitions\" WHERE Movies.MovieFileId=MovieFiles.Id AND instr(MovieFiles.Quality, ('quality\": ' || QualityDefinitions.Quality || \",\")) > 0 ORDER BY QualityDefinitions.Title {direction} LIMIT {offset},{limit};");
* var q2 = mapper.Query<Movie>("SELECT * from \"Movies\" , \"MovieFiles\", \"QualityDefinitions\" WHERE Movies.MovieFileId=MovieFiles.Id AND instr(MovieFiles.Quality, ('quality\": ' || QualityDefinitions.Quality || \",\")) > 0 ORDER BY QualityDefinitions.Title ASC;");
*
* //var ok = q.BuildQuery();
* var q3 = Query.OrderBy("json_extract([t2].[quality], '$.quality') DESC");
*
* pagingSpec.Records = q3.ToList();
* pagingSpec.TotalRecords = q3.GetRowCount();
*
* }
* else
* {
* pagingSpec = base.GetPaged(pagingSpec);
* //pagingSpec.Records = GetPagedQuery(Query, pagingSpec).ToList();
* //pagingSpec.TotalRecords = GetPagedQuery(Query, pagingSpec).GetRowCount();
* }
*
* if (pagingSpec.Records.Count == 0 && pagingSpec.Page != 1)
* {
* var lastPossiblePage = pagingSpec.TotalRecords / pagingSpec.PageSize + 1;
* pagingSpec.Page = lastPossiblePage;
* return GetPaged(pagingSpec);
* }
*
* return pagingSpec;
* }*/
/*protected override SortBuilder<Movie> GetPagedQuery(QueryBuilder<Movie> query, PagingSpec<Movie> pagingSpec)
* {
* return DataMapper.Query<Movie>().Join<Movie, AlternativeTitle>(JoinType.Left, m => m.AlternativeTitles,
* (m, t) => m.Id == t.MovieId).Where(pagingSpec.FilterExpression)
* .OrderBy(pagingSpec.OrderByClause(), pagingSpec.ToSortDirection())
* .Skip(pagingSpec.PagingOffset())
* .Take(pagingSpec.PageSize);
* }*/
/*protected override SortBuilder<Movie> GetPagedQuery(QueryBuilder<Movie> query, PagingSpec<Movie> pagingSpec)
* {
* var newQuery = base.GetPagedQuery(query.Join<Movie, AlternativeTitle>(JoinType.Left, m => m.JoinAlternativeTitles, (movie, title) => title.MovieId == movie.Id), pagingSpec);
* System.Console.WriteLine(newQuery.ToString());
* return newQuery;
* }*/
public SortBuilder <Movie> GetMoviesWithoutFilesQuery(QueryBuilder <Movie> Query, PagingSpec <Movie> pagingSpec)
{
return(Query.Where(pagingSpec.FilterExpression)
.AndWhere(m => m.MovieFileId == 0)
.OrderBy(pagingSpec.OrderByClause(x => x.SortTitle), pagingSpec.ToSortDirection())
.Skip(pagingSpec.PagingOffset())
.Take(pagingSpec.PageSize));
}
private SortBuilder <Movie> MoviesWhereCutoffUnmetQuery(QueryBuilder <Movie> Query, PagingSpec <Movie> pagingSpec, List <QualitiesBelowCutoff> qualitiesBelowCutoff)
{
return(Query.Where(pagingSpec.FilterExpression)
.AndWhere(m => m.MovieFileId != 0)
.AndWhere(BuildQualityCutoffWhereClause(qualitiesBelowCutoff))
.OrderBy(pagingSpec.OrderByClause(x => x.SortTitle), pagingSpec.ToSortDirection())
.Skip(pagingSpec.PagingOffset())
.Take(pagingSpec.PageSize));
}
private SortBuilder <History> GetPagedQuery(PagingSpec <History> pagingSpec)
{
return(Query.Join <History, Series>(JoinType.Inner, h => h.Series, (h, s) => h.SeriesId == s.Id)
.Join <History, Episode>(JoinType.Inner, h => h.Episode, (h, e) => h.EpisodeId == e.Id)
.Where(pagingSpec.FilterExpression)
.OrderBy(pagingSpec.OrderByClause(), pagingSpec.ToSortDirection())
.Skip(pagingSpec.PagingOffset())
.Take(pagingSpec.PageSize));
}
private SortBuilder <Movie> MoviesWhereCutoffUnmetQuery(PagingSpec <Movie> pagingSpec, List <QualitiesBelowCutoff> qualitiesBelowCutoff)
{
return(Query.Join <Movie, MovieFile>(JoinType.Left, e => e.MovieFile, (e, s) => e.MovieFileId == s.Id)
.Where(pagingSpec.FilterExpression)
.AndWhere(m => m.MovieFileId != 0)
.AndWhere(BuildQualityCutoffWhereClause(qualitiesBelowCutoff))
.OrderBy(pagingSpec.OrderByClause(), pagingSpec.ToSortDirection())
.Skip(pagingSpec.PagingOffset())
.Take(pagingSpec.PageSize));
}
private SortBuilder <Episode> GetMissingEpisodesQuery(PagingSpec <Episode> pagingSpec, DateTime currentTime, int startingSeasonNumber)
{
return(Query.Join <Episode, Series>(JoinType.Inner, e => e.Series, (e, s) => e.SeriesId == s.Id)
.Where(pagingSpec.FilterExpression)
.AndWhere(e => e.EpisodeFileId == 0)
.AndWhere(e => e.SeasonNumber >= startingSeasonNumber)
.AndWhere(BuildAirDateUtcCutoffWhereClause(currentTime))
.OrderBy(pagingSpec.OrderByClause(), pagingSpec.ToSortDirection())
.Skip(pagingSpec.PagingOffset())
.Take(pagingSpec.PageSize));
}
public virtual PagingSpec <TModel> GetPaged(PagingSpec <TModel> pagingSpec)
{
pagingSpec.Records = GetPagedQuery(pagingSpec)
.Skip(pagingSpec.PagingOffset())
.Take(pagingSpec.PageSize)
.ToList();
pagingSpec.TotalRecords = GetPagedQuery(pagingSpec).Count();
return(pagingSpec);
}
public void should_calcuate_expected_offset(int page, int pageSize, int expected)
{
var pagingSpec = new PagingSpec<Episode>
{
Page = page,
PageSize = pageSize,
SortDirection = SortDirection.Ascending,
SortKey = "AirDate"
};
pagingSpec.PagingOffset().Should().Be(expected);
}
private SortBuilder <Episode> EpisodesWhereCutoffUnmetQuery(PagingSpec <Episode> pagingSpec, List <QualitiesBelowCutoff> qualitiesBelowCutoff, int startingSeasonNumber)
{
return(Query.Join <Episode, Series>(JoinType.Inner, e => e.Series, (e, s) => e.SeriesId == s.Id)
.Join <Episode, EpisodeFile>(JoinType.Left, e => e.EpisodeFile, (e, s) => e.EpisodeFileId == s.Id)
.Where(pagingSpec.FilterExpression)
.AndWhere(e => e.EpisodeFileId != 0)
.AndWhere(e => e.SeasonNumber >= startingSeasonNumber)
.AndWhere(BuildQualityCutoffWhereClause(qualitiesBelowCutoff))
.OrderBy(pagingSpec.OrderByClause(), pagingSpec.ToSortDirection())
.Skip(pagingSpec.PagingOffset())
.Take(pagingSpec.PageSize));
}
private SortBuilder <Episode> GetEpisodesWithoutFilesQuery(PagingSpec <Episode> pagingSpec, DateTime currentTime, int startingSeasonNumber)
{
return(Query.Join <Episode, Series>(JoinType.Inner, e => e.Series, (e, s) => e.SeriesId == s.Id)
.Where(e => e.EpisodeFileId == 0)
.AndWhere(e => e.SeasonNumber >= startingSeasonNumber)
.AndWhere(e => e.AirDateUtc <= currentTime)
.AndWhere(e => e.Monitored)
.AndWhere(e => e.Series.Monitored)
.OrderBy(pagingSpec.OrderByClause(), pagingSpec.ToSortDirection())
.Skip(pagingSpec.PagingOffset())
.Take(pagingSpec.PageSize));
}
public void should_calcuate_expected_offset(int page, int pageSize, int expected)
{
var pagingSpec = new PagingSpec <Episode>
{
Page = page,
PageSize = pageSize,
SortDirection = SortDirection.Ascending,
SortKey = "AirDate"
};
pagingSpec.PagingOffset().Should().Be(expected);
}
public override PagingSpec <History> GetPaged(PagingSpec <History> pagingSpec)
{
var pagingQuery = Query.Join <History, Series>(JoinType.Inner, h => h.Series, (h, s) => h.SeriesId == s.Id)
.Join <History, Episode>(JoinType.Inner, h => h.Episode, (h, e) => h.EpisodeId == e.Id)
.OrderBy(pagingSpec.OrderByClause(), pagingSpec.ToSortDirection())
.Skip(pagingSpec.PagingOffset())
.Take(pagingSpec.PageSize);
pagingSpec.Records = pagingQuery.ToList();
//TODO: Use the same query for count and records
pagingSpec.TotalRecords = Count();
return(pagingSpec);
}
private QueryBuilder <Album> GetCutOffAlbumsQuery(PagingSpec <Album> pagingSpec, List <QualitiesBelowCutoff> qualitiesBelowCutoff)
{
string sortKey;
string monitored = "(Albums.[Monitored] = 0) OR (Artists.[Monitored] = 0)";
if (pagingSpec.FilterExpressions.FirstOrDefault().ToString().Contains("True"))
{
monitored = "(Albums.[Monitored] = 1) AND (Artists.[Monitored] = 1)";
}
if (pagingSpec.SortKey == "releaseDate")
{
sortKey = "Albums." + pagingSpec.SortKey;
}
else if (pagingSpec.SortKey == "artist.sortName")
{
sortKey = "Artists." + pagingSpec.SortKey.Split('.').Last();
}
else if (pagingSpec.SortKey == "albumTitle")
{
sortKey = "Albums.title";
}
else
{
sortKey = "Albums.releaseDate";
}
string query = string.Format("SELECT Albums.* " +
"FROM Albums " +
"JOIN Artists on Albums.ArtistMetadataId == Artists.ArtistMetadataId " +
"JOIN AlbumReleases ON AlbumReleases.AlbumId == Albums.Id " +
"JOIN Tracks ON Tracks.AlbumReleaseId == AlbumReleases.Id " +
"JOIN TrackFiles ON TrackFiles.Id == Tracks.TrackFileId " +
"WHERE {0} " +
"AND AlbumReleases.Monitored = 1 " +
"GROUP BY Albums.Id " +
"HAVING {1} " +
"ORDER BY {2} {3} LIMIT {4} OFFSET {5}",
monitored,
BuildQualityCutoffWhereClause(qualitiesBelowCutoff),
sortKey,
pagingSpec.ToSortDirection(),
pagingSpec.PageSize,
pagingSpec.PagingOffset());
return(Query.QueryText(query));
}
public override PagingSpec <Movie> GetPaged(PagingSpec <Movie> pagingSpec)
{
if (pagingSpec.SortKey == "downloadedQuality")
{
var mapper = _database.GetDataMapper();
var offset = pagingSpec.PagingOffset();
var limit = pagingSpec.PageSize;
var direction = "ASC";
if (pagingSpec.SortDirection == NzbDrone.Core.Datastore.SortDirection.Descending)
{
direction = "DESC";
}
var q = mapper.Query <Movie>($"SELECT * from \"Movies\" , \"MovieFiles\", \"QualityDefinitions\" WHERE Movies.MovieFileId=MovieFiles.Id AND instr(MovieFiles.Quality, ('quality\": ' || QualityDefinitions.Quality || \",\")) > 0 ORDER BY QualityDefinitions.Title {direction} LIMIT {offset},{limit};");
var q2 = mapper.Query <Movie>("SELECT * from \"Movies\" , \"MovieFiles\", \"QualityDefinitions\" WHERE Movies.MovieFileId=MovieFiles.Id AND instr(MovieFiles.Quality, ('quality\": ' || QualityDefinitions.Quality || \",\")) > 0 ORDER BY QualityDefinitions.Title ASC;");
//var ok = q.BuildQuery();
pagingSpec.Records = q.ToList();
pagingSpec.TotalRecords = q2.Count();
}
else
{
pagingSpec = base.GetPaged(pagingSpec);
//pagingSpec.Records = GetPagedQuery(Query, pagingSpec).ToList();
//pagingSpec.TotalRecords = GetPagedQuery(Query, pagingSpec).GetRowCount();
}
if (pagingSpec.Records.Count == 0 && pagingSpec.Page != 1)
{
var lastPossiblePage = pagingSpec.TotalRecords / pagingSpec.PageSize + 1;
pagingSpec.Page = lastPossiblePage;
return(GetPaged(pagingSpec));
}
return(pagingSpec);
}