public AnimeVideoQualityStat GetEpisodeVideoQualityStatsForAnime(ISessionWrapper session, int aID) { AnimeVideoQualityStat stat = new AnimeVideoQualityStat { VideoQualityEpisodeCount = new Dictionary <string, int>() }; System.Data.IDbCommand command = session.Connection.CreateCommand(); command.CommandText = "SELECT anime.AnimeID, anime.MainTitle, anifile.File_Source, aniep.EpisodeNumber " + "from AnimeSeries ser " + "INNER JOIN AniDB_Anime anime on anime.AnimeID = ser.AniDB_ID " + "INNER JOIN AnimeEpisode ep on ep.AnimeSeriesID = ser.AnimeSeriesID " + "INNER JOIN AniDB_Episode aniep on ep.AniDB_EpisodeID = aniep.EpisodeID " + "INNER JOIN CrossRef_File_Episode xref on aniep.EpisodeID = xref.EpisodeID " + "INNER JOIN AniDB_File anifile on anifile.Hash = xref.Hash " + "INNER JOIN CrossRef_Subtitles_AniDB_File subt on subt.FileID = anifile.FileID "; // See Note 1 command.CommandText += "WHERE aniep.EpisodeType = 1 " // normal episodes only + "AND anime.AnimeID = " + aID.ToString() + " GROUP BY anime.AnimeID, anime.MainTitle, anifile.File_Source, aniep.EpisodeNumber "; using (IDataReader rdr = command.ExecuteReader()) { while (rdr.Read()) { stat.AnimeID = int.Parse(rdr[0].ToString()); stat.MainTitle = rdr[1].ToString().Trim(); string vidQual = rdr[2].ToString().Trim(); int epNumber = int.Parse(rdr[3].ToString()); if (!stat.VideoQualityEpisodeCount.ContainsKey(vidQual)) { stat.VideoQualityEpisodeCount[vidQual] = 1; } else { stat.VideoQualityEpisodeCount[vidQual]++; } } } return(stat); }
public Dictionary <int, AnimeVideoQualityStat> GetEpisodeVideoQualityStatsByAnime(ISessionWrapper session, IReadOnlyCollection <int> animeIds = null) { Dictionary <int, AnimeVideoQualityStat> dictStats = new Dictionary <int, AnimeVideoQualityStat>(); using (IDbCommand command = session.Connection.CreateCommand()) { command.CommandText = "SELECT anime.AnimeID, anime.MainTitle, anifile.File_Source, aniep.EpisodeNumber " + "FROM AnimeSeries ser " + "INNER JOIN AniDB_Anime anime on anime.AnimeID = ser.AniDB_ID " + "INNER JOIN AnimeEpisode ep on ep.AnimeSeriesID = ser.AnimeSeriesID " + "INNER JOIN AniDB_Episode aniep on ep.AniDB_EpisodeID = aniep.EpisodeID " + "INNER JOIN CrossRef_File_Episode xref on aniep.EpisodeID = xref.EpisodeID " + "INNER JOIN AniDB_File anifile on anifile.Hash = xref.Hash " + "INNER JOIN CrossRef_Subtitles_AniDB_File subt on subt.FileID = anifile.FileID "; // See Note 1 command.CommandText += "WHERE aniep.EpisodeType = 1 "; // normal episodes only if (animeIds != null) { if (animeIds.Count == 0) { return(dictStats); // No anime IDs means no results. So, no need to perform query } command.CommandText += "AND anime.AnimeID IN (" + String.Join(",", animeIds) + ") "; } command.CommandText += "GROUP BY anime.AnimeID, anime.MainTitle, anifile.File_Source, aniep.EpisodeNumber " + "ORDER BY anime.AnimeID, anime.MainTitle, anifile.File_Source, aniep.EpisodeNumber "; using (IDataReader rdr = command.ExecuteReader()) { while (rdr.Read()) { int animeID = Convert.ToInt32(rdr[0]); string mainTitle = rdr[1].ToString().Trim(); string vidQual = rdr[2].ToString().Trim(); int epNumber = Convert.ToInt32(rdr[3]); if (!dictStats.TryGetValue(animeID, out AnimeVideoQualityStat stat)) { stat = new AnimeVideoQualityStat { AnimeID = animeID, MainTitle = mainTitle, VideoQualityEpisodeCount = new Dictionary <string, int>() }; dictStats.Add(animeID, stat); } stat.VideoQualityEpisodeCount.TryGetValue(vidQual, out int epCount); stat.VideoQualityEpisodeCount[vidQual] = epCount + 1; } } } return(dictStats); }