private LazyQuery <T> GetAllTVShows <T>() where T : WebTVShowBasic, new()
{
// pre-read watched information
var watchedCount = new Dictionary <string, WatchedCount>();
string watchQuery =
"SELECT e.SeriesID, e.Watched, COUNT(*) AS cnt " +
"FROM online_episodes e " +
"INNER JOIN local_episodes l ON e.CompositeID = l.CompositeID " +
"WHERE e.Hidden = 0 " +
"GROUP BY e.Watched, e.SeriesID";
ReadList <bool>(watchQuery, delegate(SQLiteDataReader reader) {
var seriesId = reader.GetInt32(0).ToString();
if (!watchedCount.ContainsKey(seriesId))
{
watchedCount[seriesId] = new WatchedCount();
}
var count = reader.GetInt32(2);
if (!reader.GetBoolean(1))
{
watchedCount[seriesId].UnwatchedEpisodes = count;
}
else
{
watchedCount[seriesId].WatchedEpisodes = count;
}
return(true);
});
string sql =
"SELECT DISTINCT s.ID, MIN(l.Parsed_Name) AS parsed_name, s.Pretty_Name, s.Genre, STRFTIME('%Y', s.FirstAired) AS year, s.Actors, s.Rating, s.ContentRating, " +
"s.Summary, s.Status, s.Network, s.AirsDay, s.AirsTime, s.Runtime, s.IMDB_ID, s.added, " +
"s.BannerFileNames, s.CurrentBannerFileName, s.PosterFileNames, s.PosterBannerFileName, " +
"GROUP_CONCAT(f.LocalPath || '?' || f.Rating, '|') AS fanart_list, " + // it's ugly, I feel guilty. ? is used because it can't be used in a filename.
"COUNT(e.ID) AS season_count " +
"FROM online_series AS s " +
"INNER JOIN local_series AS l ON s.ID = l.ID AND l.Hidden = 0 " +
"LEFT JOIN Fanart AS f ON s.ID = f.seriesID AND f.LocalPath != '' AND f.SeriesName = 'false' " +
"LEFT JOIN season AS e ON s.ID = e.SeriesID " +
"WHERE s.ID != 0 AND %where " +
"GROUP BY s.ID, s.Pretty_Name, s.Genre, s.BannerFileNames, s.FirstAired, " +
"s.PosterFileNames, s.Actors, s.Summary, s.Network, s.AirsDay, s.AirsTime, s.Runtime, s.Rating, s.ContentRating, s.Status, " +
"s.IMDB_ID, s.added " +
"%order";
return(new LazyQuery <T>(this, sql, new List <SQLFieldMapping>()
{
new SQLFieldMapping("s", "ID", "Id", DataReaders.ReadIntAsString),
new SQLFieldMapping("s", "ID", "ExternalId", CustomReaders.ExternalIdReader, new ExternalSiteReaderParameters(DataReaders.ReadIntAsString, "TVDB")),
new SQLFieldMapping("s", "Pretty_Name", "Title", CustomReaders.FixNameReader),
new SQLFieldMapping("s", "Genre", "Genres", DataReaders.ReadPipeList),
new SQLFieldMapping("", "year", "Year", DataReaders.ReadStringAsInt),
new SQLFieldMapping("s", "Actors", "Actors", CustomReaders.ActorReader),
new SQLFieldMapping("s", "Rating", "Rating", DataReaders.ReadFloat),
new SQLFieldMapping("s", "ContentRating", "ContentRating", DataReaders.ReadString),
new SQLFieldMapping("s", "Summary", "Summary", DataReaders.ReadString),
new SQLFieldMapping("s", "Status", "Status", DataReaders.ReadString),
new SQLFieldMapping("s", "Network", "Network", DataReaders.ReadString),
new SQLFieldMapping("s", "AirsDay", "AirsDay", DataReaders.ReadString),
new SQLFieldMapping("s", "AirsTime", "AirsTime", DataReaders.ReadString),
new SQLFieldMapping("s", "Runtime", "Runtime", DataReaders.ReadInt32),
new SQLFieldMapping("s", "IMDB_ID", "ExternalId", CustomReaders.ExternalIdReader, new ExternalSiteReaderParameters(DataReaders.ReadString, "IMDB")),
new SQLFieldMapping("s", "added", "DateAdded", DataReaders.ReadDateTime),
new SQLFieldMapping("s", "BannerFileNames", "Artwork", CustomReaders.PreferedArtworkReader, new ArtworkReaderParameters(WebFileType.Banner, configuration["banner"])),
new SQLFieldMapping("s", "PosterFileNames", "Artwork", CustomReaders.PreferedArtworkReader, new ArtworkReaderParameters(WebFileType.Poster, configuration["banner"])),
new SQLFieldMapping("", "fanart_list", "Artwork", CustomReaders.FanartArtworkReader, new ArtworkReaderParameters(WebFileType.Backdrop, configuration["fanart"])),
new SQLFieldMapping("", "season_count", "SeasonCount", DataReaders.ReadInt32)
}, delegate(T obj)
{
// cannot rely on information provided by MPTVSeries here because they count differently, which makes things inconsistent
obj.EpisodeCount = watchedCount.ContainsKey(obj.Id) ? watchedCount[obj.Id].WatchedEpisodes + watchedCount[obj.Id].UnwatchedEpisodes : 0;
obj.UnwatchedEpisodeCount = watchedCount.ContainsKey(obj.Id) ? watchedCount[obj.Id].UnwatchedEpisodes : 0;
return obj;
}));
}
private LazyQuery <T> GetAllTVShows <T>() where T : WebTVShowBasic, new()
{
// pre-read watched information
var watchedCount = new Dictionary <string, WatchedCount>();
string watchQuery =
"SELECT e.SeriesID, e.Watched, COUNT(*) AS cnt " +
"FROM online_episodes e " +
"INNER JOIN local_episodes l ON e.CompositeID = l.CompositeID " +
"WHERE e.Hidden = 0 " +
"GROUP BY e.Watched, e.SeriesID";
ReadList <bool>(watchQuery, delegate(SQLiteDataReader reader) {
var seriesId = reader.GetInt32(0).ToString();
if (!watchedCount.ContainsKey(seriesId))
{
watchedCount[seriesId] = new WatchedCount();
}
var watched = reader.GetInt32(1);
var count = reader.GetInt32(2);
if (watched == 0)
{
watchedCount[seriesId].UnwatchedEpisodes = count;
}
else
{
watchedCount[seriesId].WatchedEpisodes = count;
}
return(true);
});
Delegates.ReadValue fixNameReader = delegate(SQLiteDataReader reader, int index)
{
// MPTvSeries does some magic with the name: if it's empty in the online series, use the Parsed_Name from the local series. I prefer
// a complete database, but we can't fix that easily. See DB Classes/DBSeries.cs:359 in MPTvSeries source
string data = reader.ReadString(index);
if (data.Length > 0)
{
return(data);
}
return(reader.ReadString(index - 1));
};
string sql =
"SELECT DISTINCT s.ID, l.Parsed_Name, s.Pretty_Name, s.Genre, s.BannerFileNames, STRFTIME('%Y', s.FirstAired) AS year, " +
"s.PosterFileNames, s.fanart, s.Actors, s.Summary, s.Network, s.AirsDay, s.AirsTime, s.Runtime, s.Rating, s.ContentRating, s.Status, " +
"s.IMDB_ID, s.added " +
"FROM online_series AS s " +
"INNER JOIN local_series AS l ON s.ID = l.ID AND l.Hidden = 0 " +
"WHERE s.ID != 0 AND s.HasLocalFiles = 1 AND %where " +
"%order";
return(new LazyQuery <T>(this, sql, new List <SQLFieldMapping>()
{
new SQLFieldMapping("s", "ID", "Id", DataReaders.ReadIntAsString),
new SQLFieldMapping("s", "ID", "ExternalId", CustomReaders.ExternalIdReader, new ExternalSiteReaderParameters(DataReaders.ReadIntAsString, "TVDB")),
new SQLFieldMapping("s", "Pretty_Name", "Title", fixNameReader),
new SQLFieldMapping("s", "Genre", "Genres", DataReaders.ReadPipeList),
new SQLFieldMapping("s", "BannerFileNames", "Artwork", CustomReaders.ArtworkReader, new ArtworkReaderParameters(WebFileType.Banner, configuration["banner"])),
new SQLFieldMapping("", "year", "Year", DataReaders.ReadStringAsInt),
new SQLFieldMapping("s", "PosterFileNames", "Artwork", CustomReaders.ArtworkReader, new ArtworkReaderParameters(WebFileType.Poster, configuration["banner"])),
new SQLFieldMapping("s", "fanart", "Artwork", CustomReaders.ArtworkReader, new ArtworkReaderParameters(WebFileType.Backdrop, configuration["fanart"])),
new SQLFieldMapping("s", "Actors", "Actors", CustomReaders.ActorReader),
new SQLFieldMapping("s", "Rating", "Rating", DataReaders.ReadFloat),
new SQLFieldMapping("s", "ContentRating", "ContentRating", DataReaders.ReadString),
new SQLFieldMapping("s", "Summary", "Summary", DataReaders.ReadString),
new SQLFieldMapping("s", "Status", "Status", DataReaders.ReadString),
new SQLFieldMapping("s", "Network", "Network", DataReaders.ReadString),
new SQLFieldMapping("s", "AirsDay", "AirsDay", DataReaders.ReadString),
new SQLFieldMapping("s", "AirsTime", "AirsTime", DataReaders.ReadString),
new SQLFieldMapping("s", "Runtime", "Runtime", DataReaders.ReadInt32),
new SQLFieldMapping("s", "IMDB_ID", "ExternalId", CustomReaders.ExternalIdReader, new ExternalSiteReaderParameters(DataReaders.ReadString, "IMDB")),
new SQLFieldMapping("s", "added", "DateAdded", DataReaders.ReadDateTime)
}, delegate(T obj)
{
// cannot rely on information provided by MPTVSeries here because they count different
var eps = (LazyQuery <WebTVEpisodeBasic>)(GetAllEpisodes <WebTVEpisodeBasic>().Where(x => x.ShowId == obj.Id)); // and the nice way is... ?
obj.EpisodeCount = watchedCount[obj.Id].WatchedEpisodes + watchedCount[obj.Id].UnwatchedEpisodes;
obj.UnwatchedEpisodeCount = watchedCount[obj.Id].UnwatchedEpisodes;
return obj;
}));
}
