private Dictionary <int, LanguageStat> GetAudioLanguageStatsByAnimeResults(ISessionWrapper session,
string animeIdPredicate)
{
Dictionary <int, LanguageStat> dictStats = new Dictionary <int, LanguageStat>();
string query = GetAudioLanguageStatsByAnimeSQL(animeIdPredicate);
var rows = session.CreateSQLQuery(query)
.AddScalar("AnimeID", NHibernateUtil.Int32)
.AddScalar("MainTitle", NHibernateUtil.String)
.AddScalar("LanguageName", NHibernateUtil.String)
.List <object[]>();
foreach (object[] cols in rows)
{
int animeID = Convert.ToInt32(cols[0]);
string mainTitle = cols[1].ToString().Trim();
string lanName = cols[2].ToString().Trim();
if (!dictStats.TryGetValue(animeID, out LanguageStat stat))
{
stat = new LanguageStat
{
AnimeID = animeID,
MainTitle = mainTitle,
LanguageNames = new List <string>()
};
dictStats.Add(animeID, stat);
}
stat.LanguageNames.Add(lanName);
}
return(dictStats);
}
public Dictionary <int, LanguageStat> GetAudioLanguageStatsForAnime()
{
Dictionary <int, LanguageStat> dictStats = new Dictionary <int, LanguageStat>();
using (var session = DatabaseFactory.SessionFactory.OpenSession())
{
System.Data.IDbCommand command = session.Connection.CreateCommand();
command.CommandText = "SELECT anime.AnimeID, anime.MainTitle, lan.LanguageName ";
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_Languages_AniDB_File audio on audio.FileID = anifile.FileID ";
command.CommandText += "INNER JOIN Language lan on audio.LanguageID = lan.LanguageID ";
command.CommandText += "GROUP BY anime.AnimeID, anime.MainTitle, lan.LanguageName ";
using (IDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
int animeID = int.Parse(rdr[0].ToString());
string mainTitle = rdr[1].ToString().Trim();
string lanName = rdr[2].ToString().Trim();
if (animeID == 7656)
{
Debug.Print("");
}
if (!dictStats.ContainsKey(animeID))
{
LanguageStat stat = new LanguageStat
{
AnimeID = animeID,
MainTitle = mainTitle,
LanguageNames = new List <string>()
};
stat.LanguageNames.Add(lanName);
dictStats[animeID] = stat;
}
else
{
dictStats[animeID].LanguageNames.Add(lanName);
}
}
}
}
return(dictStats);
}
private Dictionary <int, LanguageStat> GetSubtitleLanguageStatsByAnimeResults(ISessionWrapper session,
string animeIdPredicate)
{
Dictionary <int, LanguageStat> dictStats = new Dictionary <int, LanguageStat>();
string query = "SELECT DISTINCT anime.AnimeID, anime.MainTitle, lan.LanguageName "
+ "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 "
+ "INNER JOIN Language lan on subt.LanguageID = lan.LanguageID "
+ "WHERE anime.AnimeID " + animeIdPredicate;
var rows = session.CreateSQLQuery(query)
.AddScalar("AnimeID", NHibernateUtil.Int32)
.AddScalar("MainTitle", NHibernateUtil.String)
.AddScalar("LanguageName", NHibernateUtil.String)
.List <object[]>();
foreach (object[] cols in rows)
{
int animeID = Convert.ToInt32(cols[0]);
string mainTitle = cols[1].ToString().Trim();
string lanName = cols[2].ToString().Trim();
LanguageStat stat = null;
if (!dictStats.TryGetValue(animeID, out stat))
{
stat = new LanguageStat
{
AnimeID = animeID,
MainTitle = mainTitle,
LanguageNames = new List <string>()
};
dictStats.Add(animeID, stat);
}
stat.LanguageNames.Add(lanName);
}
return(dictStats);
}
public Dictionary <int, LanguageStat> GetSubtitleLanguageStatsByAnime(IEnumerable <int> animeIds)
{
if (!animeIds.Any())
{
return(new Dictionary <int, LanguageStat>());
}
var rows = Repo.Instance.AnimeSeries.GetAll()
.Join(Repo.Instance.AniDB_Anime.GetAll(), s => s.AniDB_ID, j => j.AnimeID, (ser, anime) => new { ser, anime })
.Join(Repo.Instance.AnimeEpisode.GetAll(), s => s.ser.AnimeSeriesID, j => j.AnimeSeriesID, (cmb, ep) => new { cmb.ser, cmb.anime, ep })
.Join(Repo.Instance.AniDB_Episode.GetAll(), s => s.ep.AniDB_EpisodeID, aniep => aniep.EpisodeID, (cmb, aniep) => new { cmb.ser, cmb.anime, cmb.ep, aniep })
.Join(Repo.Instance.CrossRef_File_Episode.GetAll(), s => s.aniep.EpisodeID, xref => xref.EpisodeID, (cmb, xref) => new { cmb.ser, cmb.anime, cmb.ep, cmb.aniep, xref })
.Join(Repo.Instance.AniDB_File.GetAll(), s => s.xref.Hash, anifile => anifile.Hash, (cmb, anifile) => new { cmb.ser, cmb.anime, cmb.ep, cmb.aniep, cmb.xref, anifile })
.Join(Repo.Instance.CrossRef_Subtitles_AniDB_File.GetAll(), s => s.anifile.FileID, audio => audio.FileID, (cmb, audio) => new { cmb.ser, cmb.anime, cmb.ep, cmb.aniep, cmb.xref, cmb.anifile, audio })
.Join(Repo.Instance.Language.GetAll(), s => s.audio.LanguageID, lan => lan.LanguageID, (cmb, lan) => new { cmb.ser, cmb.anime, cmb.ep, cmb.aniep, cmb.xref, cmb.anifile, cmb.audio, lan })
.Where(s => animeIds.Contains(s.anime.AnimeID))
.Select(s => (s.anime.AnimeID, s.anime.MainTitle, s.lan.LanguageName))
.Distinct();
Dictionary <int, LanguageStat> dictStats = new Dictionary <int, LanguageStat>();
foreach ((int animeID, string mainTitle, string lanName) in rows)
{
if (!dictStats.TryGetValue(animeID, out LanguageStat stat))
{
stat = new LanguageStat
{
AnimeID = animeID,
MainTitle = mainTitle,
LanguageNames = new List <string>()
};
dictStats.Add(animeID, stat);
}
stat.LanguageNames.Add(lanName);
}
return(dictStats);
}