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