public Dictionary <int, AnimeVideoQualityStat> GetEpisodeVideoQualityStatsByAnime(ISession session)
{
Dictionary <int, AnimeVideoQualityStat> dictStats = new Dictionary <int, AnimeVideoQualityStat>();
System.Data.IDbCommand command = session.Connection.CreateCommand();
command.CommandText = "SELECT anime.AnimeID, anime.MainTitle, anifile.File_Source, aniep.EpisodeNumber ";
command.CommandText += "from AnimeSeries ser ";
command.CommandText += "INNER JOIN AniDB_Anime anime on anime.AnimeID = ser.AniDB_ID ";
command.CommandText += "INNER JOIN AnimeEpisode ep on ep.AnimeSeriesID = ser.AnimeSeriesID ";
command.CommandText += "INNER JOIN AniDB_Episode aniep on ep.AniDB_EpisodeID = aniep.EpisodeID ";
command.CommandText += "INNER JOIN CrossRef_File_Episode xref on aniep.EpisodeID = xref.EpisodeID ";
command.CommandText += "INNER JOIN AniDB_File anifile on anifile.Hash = xref.Hash ";
command.CommandText += "INNER JOIN CrossRef_Subtitles_AniDB_File subt on subt.FileID = anifile.FileID ";
// See Note 1
command.CommandText += "WHERE aniep.EpisodeType = 1 "; // normal episodes only
command.CommandText += "GROUP BY anime.AnimeID, anime.MainTitle, anifile.File_Source, aniep.EpisodeNumber ";
command.CommandText += "ORDER BY anime.AnimeID, anime.MainTitle, anifile.File_Source, aniep.EpisodeNumber ";
using (IDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
int animeID = int.Parse(rdr[0].ToString());
string mainTitle = rdr[1].ToString().Trim();
string vidQual = rdr[2].ToString().Trim();
int epNumber = int.Parse(rdr[3].ToString());
/*
* if (animeID == 7656)
* {
* Debug.Print("");
* }
*/
if (!dictStats.ContainsKey(animeID))
{
AnimeVideoQualityStat stat = new AnimeVideoQualityStat();
stat.AnimeID = animeID;
stat.MainTitle = mainTitle;
stat.VideoQualityEpisodeCount = new Dictionary <string, int>();
stat.VideoQualityEpisodeCount[vidQual] = 1;
dictStats[animeID] = stat;
}
else
{
if (dictStats[animeID].VideoQualityEpisodeCount.ContainsKey(vidQual))
{
dictStats[animeID].VideoQualityEpisodeCount[vidQual]++;
}
else
{
dictStats[animeID].VideoQualityEpisodeCount[vidQual] = 1;
}
}
}
}
return(dictStats);
}
public AnimeVideoQualityStat GetEpisodeVideoQualityStatsForAnime(ISessionWrapper session, int aID)
{
AnimeVideoQualityStat stat = new AnimeVideoQualityStat();
stat.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 AnimeVideoQualityStat GetEpisodeVideoQualityStatsForAnime(ISessionWrapper session, int aID)
{
AnimeVideoQualityStat stat = new AnimeVideoQualityStat();
stat.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]);
AnimeVideoQualityStat stat;
if (!dictStats.TryGetValue(animeID, out stat))
{
stat = new AnimeVideoQualityStat
{
AnimeID = animeID,
MainTitle = mainTitle,
VideoQualityEpisodeCount = new Dictionary<string, int>()
};
dictStats.Add(animeID, stat);
}
int epCount;
stat.VideoQualityEpisodeCount.TryGetValue(vidQual, out epCount);
stat.VideoQualityEpisodeCount[vidQual] = epCount + 1;
}
}
}
return dictStats;
}
public Dictionary<int, AnimeVideoQualityStat> GetEpisodeVideoQualityStatsByAnime(ISession session)
{
Dictionary<int, AnimeVideoQualityStat> dictStats = new Dictionary<int, AnimeVideoQualityStat>();
System.Data.IDbCommand command = session.Connection.CreateCommand();
command.CommandText = "SELECT anime.AnimeID, anime.MainTitle, anifile.File_Source, aniep.EpisodeNumber ";
command.CommandText += "from AnimeSeries ser ";
command.CommandText += "INNER JOIN AniDB_Anime anime on anime.AnimeID = ser.AniDB_ID ";
command.CommandText += "INNER JOIN AnimeEpisode ep on ep.AnimeSeriesID = ser.AnimeSeriesID ";
command.CommandText += "INNER JOIN AniDB_Episode aniep on ep.AniDB_EpisodeID = aniep.EpisodeID ";
command.CommandText += "INNER JOIN CrossRef_File_Episode xref on aniep.EpisodeID = xref.EpisodeID ";
command.CommandText += "INNER JOIN AniDB_File anifile on anifile.Hash = xref.Hash ";
command.CommandText += "INNER JOIN CrossRef_Subtitles_AniDB_File subt on subt.FileID = anifile.FileID "; // See Note 1
command.CommandText += "WHERE aniep.EpisodeType = 1 "; // normal episodes only
command.CommandText += "GROUP BY anime.AnimeID, anime.MainTitle, anifile.File_Source, aniep.EpisodeNumber ";
command.CommandText += "ORDER BY anime.AnimeID, anime.MainTitle, anifile.File_Source, aniep.EpisodeNumber ";
using (IDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
int animeID = int.Parse(rdr[0].ToString());
string mainTitle = rdr[1].ToString().Trim();
string vidQual = rdr[2].ToString().Trim();
int epNumber = int.Parse(rdr[3].ToString());
if (animeID == 7656)
{
Debug.Print("");
}
if (!dictStats.ContainsKey(animeID))
{
AnimeVideoQualityStat stat = new AnimeVideoQualityStat();
stat.AnimeID = animeID;
stat.MainTitle = mainTitle;
stat.VideoQualityEpisodeCount = new Dictionary<string, int>();
stat.VideoQualityEpisodeCount[vidQual] = 1;
dictStats[animeID] = stat;
}
else
{
if (dictStats[animeID].VideoQualityEpisodeCount.ContainsKey(vidQual))
dictStats[animeID].VideoQualityEpisodeCount[vidQual]++;
else
dictStats[animeID].VideoQualityEpisodeCount[vidQual] = 1;
}
}
}
return dictStats;
}
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]);
AnimeVideoQualityStat stat;
if (!dictStats.TryGetValue(animeID, out stat))
{
stat = new AnimeVideoQualityStat
{
AnimeID = animeID,
MainTitle = mainTitle,
VideoQualityEpisodeCount = new Dictionary <string, int>()
};
dictStats.Add(animeID, stat);
}
int epCount;
stat.VideoQualityEpisodeCount.TryGetValue(vidQual, out epCount);
stat.VideoQualityEpisodeCount[vidQual] = epCount + 1;
}
}
}
return(dictStats);
}
