public ArrayList Execute()
{
//build the query
ArrayList movies = new ArrayList();
string whereClause = string.Empty;
string orderClause = string.Empty;
string fromClause = "actors,movie,movieinfo,path";
if (CurrentLevel > 0)
{
whereClause =
"where actors.idactor=movieinfo.idDirector and movieinfo.idmovie=movie.idmovie and movie.idpath=path.idpath";
}
for (int i = 0; i < CurrentLevel; ++i)
{
BuildSelect((FilterDefinition)currentView.Filters[i], ref whereClause, ref fromClause);
}
BuildWhere((FilterDefinition)currentView.Filters[CurrentLevel], ref whereClause);
BuildRestriction((FilterDefinition)currentView.Filters[CurrentLevel], ref whereClause);
BuildOrder((FilterDefinition)currentView.Filters[CurrentLevel], ref orderClause);
//execute the query
string sql;
if (CurrentLevel == 0)
{
bool useMovieInfoTable = false;
bool useAlbumTable = false;
bool useActorsTable = false;
bool useGenreTable = false;
FilterDefinition defRoot = (FilterDefinition)currentView.Filters[0];
string table = GetTable(defRoot.Where, ref useMovieInfoTable, ref useAlbumTable, ref useActorsTable,
ref useGenreTable);
if (table == "actors")
{
sql = String.Format("select * from actors ");
if (whereClause != string.Empty)
{
sql += "where " + whereClause;
}
if (orderClause != string.Empty)
{
sql += orderClause;
}
VideoDatabase.GetMoviesByFilter(sql, out movies, true, false, false);
}
else if (table == "genre")
{
sql = String.Format("select * from genre ");
if (whereClause != string.Empty)
{
sql += "where " + whereClause;
}
if (orderClause != string.Empty)
{
sql += orderClause;
}
VideoDatabase.GetMoviesByFilter(sql, out movies, false, false, true);
}
else if (defRoot.Where == "year")
{
movies = new ArrayList();
sql = String.Format("select distinct iYear from movieinfo ");
SQLiteResultSet results = VideoDatabase.GetResults(sql);
for (int i = 0; i < results.Rows.Count; i++)
{
IMDBMovie movie = new IMDBMovie();
movie.Year = (int)Math.Floor(0.5d + Double.Parse(DatabaseUtility.Get(results, i, "iYear")));
movies.Add(movie);
}
}
else
{
whereClause =
"where actors.idActor=movieinfo.idDirector and movieinfo.idmovie=movie.idmovie and movie.idpath=path.idpath";
BuildRestriction(defRoot, ref whereClause);
sql = String.Format("select * from {0} {1} {2}",
fromClause, whereClause, orderClause);
VideoDatabase.GetMoviesByFilter(sql, out movies, true, true, true);
}
}
else if (CurrentLevel < MaxLevels - 1)
{
bool useMovieInfoTable = false;
bool useAlbumTable = false;
bool useActorsTable = false;
bool useGenreTable = false;
FilterDefinition defCurrent = (FilterDefinition)currentView.Filters[CurrentLevel];
string table = GetTable(defCurrent.Where, ref useMovieInfoTable, ref useAlbumTable, ref useActorsTable,
ref useGenreTable);
sql = String.Format("select distinct {0}.* {1} {2} {3}",
table, fromClause, whereClause, orderClause);
VideoDatabase.GetMoviesByFilter(sql, out movies, useActorsTable, useMovieInfoTable, useGenreTable);
}
else
{
sql =
String.Format(
"select movieinfo.fRating,actors.strActor,movieinfo.strCredits,movieinfo.strTagLine,movieinfo.strPlotOutline,movieinfo.strPlot,movieinfo.strVotes,movieinfo.strCast,movieinfo.iYear,movieinfo.strGenre,movieinfo.strPictureURL,movieinfo.strTitle,path.strPath,movie.discid,movieinfo.IMDBID,movieinfo.idMovie,path.cdlabel,movieinfo.mpaa,movieinfo.runtime,movieinfo.iswatched, movieinfo.strUserReview from {0} {1} {2}",
fromClause, whereClause, orderClause);
VideoDatabase.GetMoviesByFilter(sql, out movies, true, true, true);
}
return(movies);
}
public ArrayList Execute()
{
//build the query
ArrayList movies = new ArrayList();
string whereClause = string.Empty;
string orderClause = string.Empty;
string fromClause = string.Empty;
string defViewFields = VideoDatabase.DefaultVideoViewFields;
for (int i = 0; i < CurrentLevel; ++i)
{
BuildSelect((FilterDefinition)currentView.Filters[i], ref whereClause, ref fromClause);
}
BuildWhere((FilterDefinition)currentView.Filters[CurrentLevel], ref whereClause);
BuildRestriction((FilterDefinition)currentView.Filters[CurrentLevel], ref whereClause);
BuildOrder((FilterDefinition)currentView.Filters[CurrentLevel], ref orderClause);
_parentWhere = whereClause;
//execute the query
string sql;
if ((CurrentLevel >= 0) && (CurrentLevel < MaxLevels))
{
bool useMovieInfoTable = false;
bool useAlbumTable = false;
bool useActorsTable = false;
bool useGenreTable = false;
bool useUserGroupsTable = false;
bool useMovieCollectionTable = false;
string join = string.Empty;
string fields = defViewFields;
FilterDefinition defCurrent = (FilterDefinition)currentView.Filters[CurrentLevel];
string view = defCurrent.Where;
// Actor, Director, Title Index
if ((view == "actorindex") || (view == "directorindex") || (view == "titleindex"))
{
sql = String.Format("SELECT {0} AS IX, COUNT ({1}) " +
"FROM movieView " +
"WHERE {1} <> 'unknown' AND {1} IS NOT NULL {2} GROUP BY IX ",
GetFieldId(view), GetFieldName(view), (!string.IsNullOrEmpty(whereClause) ? "AND " + whereClause : ""));
VideoDatabase.GetIndexByFilter(sql, true, out movies);
return(movies);
}
// Year
if (view == "year")
{
sql = String.Format("SELECT DISTINCT {0} FROM movieView {1}",
GetFieldId(view), (!string.IsNullOrEmpty(whereClause) ? "WHERE " + whereClause : ""));
SQLiteResultSet results = VideoDatabase.GetResults(sql);
for (int i = 0; i < results.Rows.Count; i++)
{
IMDBMovie movie = new IMDBMovie();
movie.Year = DatabaseUtility.GetAsInt(results, i, "iYear");
movies.Add(movie);
}
return(movies);
}
string table = GetTable(view, ref useMovieInfoTable, ref useAlbumTable, ref useActorsTable,
ref useGenreTable, ref useUserGroupsTable, ref useMovieCollectionTable);
// Recently added, Recently watched
if ((view == "recently added") || (view == "recently watched"))
{
try
{
if (string.IsNullOrEmpty(defCurrent.Restriction))
{
defCurrent.Restriction = "7";
}
TimeSpan ts = new TimeSpan(Convert.ToInt32(defCurrent.Restriction), 0, 0, 0);
DateTime searchDate = DateTime.Today - ts;
whereClause = String.Format("WHERE {0} >= '{1}'",
GetFieldName(view),
searchDate.ToString("yyyy-MM-dd" + " 00:00:00"));
useMovieInfoTable = true;
}
catch (Exception ex)
{
Log.Error("GUIVideoSelectDVDHandler: Execute {0}", ex.Message);
}
}
// Director
else if (view == "director")
{
fields = "idActorDirector, strActorDirector, strIMDBActorDirectorID";
whereClause = "WHERE strActorDirector <> 'unknown' AND strActorDirector IS NOT NULL";
}
// Actor
else if (view == "actor")
{
fields = "idActor, strActor, imdbActorId";
whereClause = "WHERE strActor <> 'unknown' AND strActor IS NOT NULL";
}
// Genre
else if (view == "genre")
{
fields = "idSingleGenre, strSingleGenre";
whereClause = "WHERE strSingleGenre IS NOT NULL";
}
// User groups
else if (view == "user groups" || view == "user groups only")
{
fields = "idGroup, strGroup";
whereClause = "WHERE strGroup IS NOT NULL";
}
// Collections
else if (view == "movie collections" || view == "movie collections only")
{
fields = "idCollection, strCollection, strCollectionDescription";
whereClause = "WHERE strCollection IS NOT NULL";
}
// Title
else
{
fields = defViewFields;
whereClause = string.Empty; // Already storred in ParentWhere
useMovieInfoTable = true;
}
table = "movieView"; // MP1-4775
if (!string.IsNullOrEmpty(ParentWhere))
{
if (!string.IsNullOrEmpty(whereClause))
{
whereClause = whereClause + " AND " + ParentWhere;
}
else
{
whereClause = "WHERE " + ParentWhere;
}
}
sql = String.Format("SELECT DISTINCT {0} FROM {1} {2} {3} {4}",
fields, table, join, whereClause, orderClause);
VideoDatabase.GetMoviesByFilter(sql, out movies, useActorsTable, useMovieInfoTable, useGenreTable, useUserGroupsTable, useMovieCollectionTable);
if ((view == "user groups") || (view == "movie collections"))
{
ArrayList moviesExt = new ArrayList();
sql = String.Format("SELECT DISTINCT {0} FROM {1} WHERE {2} IS NULL {3} ORDER BY strTitle",
defViewFields, table, GetFieldId(view), (!string.IsNullOrEmpty(ParentWhere) ? "AND " + ParentWhere : ""));
VideoDatabase.GetMoviesByFilter(sql, out moviesExt, false, true, false, false, false);
movies.AddRange(moviesExt);
}
}
return(movies);
}
private void MovieSelect_Load(object sender, EventArgs e)
{
//If MovingPictures is selected
if (TraktSettings.MovingPictures > -1 && File.Exists(Path.Combine(Config.GetFolder(Config.Dir.Plugins), @"Windows\MovingPictures.dll")))
{
//Load the Movies from Moving Pictures
try
{
LoadMoviesFromMovingPictures();
}
catch (Exception)
{
TraktLogger.Info("Tried to load MovingPictures but failed, check minimum requirements are met!");
}
}
//If MyVideos is selected, always installed
if (TraktSettings.MyVideos > -1)
{
string sql = "SELECT movieinfo.strTitle, path.strPath, files.strFilename " +
"FROM movieInfo " +
"LEFT JOIN files " +
"ON movieInfo.idMovie=files.idMovie " +
"LEFT JOIN path " +
"ON files.idPath=path.idPath " +
"ORDER BY strTitle";
SQLiteResultSet results = VideoDatabase.GetResults(sql);
for (int row = 0; row < results.Rows.Count; row++)
{
string title = DatabaseUtility.Get(results, row, 0);
string filename = string.Concat(DatabaseUtility.Get(results, row, 1), DatabaseUtility.Get(results, row, 2));
if (string.IsNullOrEmpty(filename))
{
continue;
}
if (!_blockedFilenames.Contains(filename))
{
unCheckedMovies.Add(new MovieSelectItem {
MovieTitle = title, Filename = new List <string> {
filename
}
});
}
else
{
checkedMovies.Add(new MovieSelectItem {
MovieTitle = title, Filename = new List <string> {
filename
}
});
}
}
}
//If MyFilms is selected
if (TraktSettings.MyFilms > -1 && File.Exists(Path.Combine(Config.GetFolder(Config.Dir.Plugins), @"Windows\MyFilms.dll")))
{
//Load the Movies from MyFilms
try
{
LoadMoviesFromMyFilms();
}
catch (Exception)
{
TraktLogger.Info("Tried to load My Films but failed, check minimum requirements are met!");
}
}
foreach (MovieSelectItem movie in checkedMovies)
{
if (!checkedListBoxMovies.Items.Contains(movie))
{
checkedListBoxMovies.Items.Add(movie, true);
}
}
foreach (MovieSelectItem movie in unCheckedMovies)
{
if (!checkedListBoxMovies.Items.Contains(movie))
{
checkedListBoxMovies.Items.Add(movie, false);
}
}
checkedListBoxMovies.ItemCheck += new ItemCheckEventHandler(checkedListBoxMovies_ItemCheck);
}
public ArrayList Execute()
{
//build the query
ArrayList movies = new ArrayList();
string whereClause = string.Empty;
string orderClause = string.Empty;
string fromClause = string.Empty;
if (CurrentLevel == MaxLevels - 1)
{
whereClause = "WHERE movieinfo.idmovie=movie.idmovie AND movie.idpath=path.idpath";
fromClause = "movie,movieinfo,path";
}
for (int i = 0; i < CurrentLevel; ++i)
{
BuildSelect((FilterDefinition)currentView.Filters[i], ref whereClause, ref fromClause);
}
BuildWhere((FilterDefinition)currentView.Filters[CurrentLevel], ref whereClause);
BuildRestriction((FilterDefinition)currentView.Filters[CurrentLevel], ref whereClause);
BuildOrder((FilterDefinition)currentView.Filters[CurrentLevel], ref orderClause);
//execute the query
string sql;
if (CurrentLevel == 0)
{
bool useMovieInfoTable = false;
bool useAlbumTable = false;
bool useActorsTable = false;
bool useGenreTable = false;
bool useUserGroupsTable = false;
FilterDefinition defRoot = (FilterDefinition)currentView.Filters[0];
string table = GetTable(defRoot.Where, ref useMovieInfoTable, ref useAlbumTable, ref useActorsTable,
ref useGenreTable, ref useUserGroupsTable);
if (string.IsNullOrEmpty(table) && defRoot.Where == "actorindex")
{
sql = String.Format("SELECT UPPER(SUBSTR(strActor,1,1)) AS IX, COUNT (strActor) FROM actors WHERE idActor NOT IN (SELECT DISTINCT idDirector FROM movieinfo WHERE strDirector <> 'unknown') AND strActor <> 'unknown' GROUP BY IX ");
VideoDatabase.GetIndexByFilter(sql, true, out movies);
}
else if (string.IsNullOrEmpty(table) && defRoot.Where == "directorindex")
{
sql = String.Format("SELECT UPPER(SUBSTR(strActor,1,1)) AS IX, COUNT (strActor) FROM actors WHERE idActor IN (SELECT DISTINCT idDirector FROM movieinfo WHERE strDirector <> 'unknown') GROUP BY IX ");
VideoDatabase.GetIndexByFilter(sql, true, out movies);
}
else if (string.IsNullOrEmpty(table) && defRoot.Where == "titleindex")
{
sql = String.Format("SELECT UPPER(SUBSTR(strTitle,1,1)) AS IX, COUNT (strTitle) FROM movieinfo GROUP BY IX ");
VideoDatabase.GetIndexByFilter(sql, true, out movies);
}
else if (table == "actors")
{
if (defRoot.Where == "director")
{
sql = String.Format("SELECT idActor, strActor, imdbActorId FROM actors WHERE idActor IN (SELECT DISTINCT idDirector FROM movieinfo WHERE strDirector <> 'unknown') AND strActor <> 'unknown' ");
}
else
{
sql = String.Format("SELECT * FROM actors WHERE strActor <> 'unknown' ");
}
if (whereClause != string.Empty && defRoot.Where == "director")
{
sql += "WHERE " + whereClause;
}
if (whereClause != string.Empty && defRoot.Where == "actor")
{
sql += "AND " + whereClause;
}
if (orderClause != string.Empty)
{
sql += orderClause;
}
VideoDatabase.GetMoviesByFilter(sql, out movies, true, false, false, false);
}
else if (table == "genre")
{
sql = String.Format("SELECT * FROM genre ");
if (whereClause != string.Empty)
{
sql += "WHERE " + whereClause;
}
if (orderClause != string.Empty)
{
sql += orderClause;
}
VideoDatabase.GetMoviesByFilter(sql, out movies, false, false, true, false);
}
else if (table == "usergroup")
{
sql = String.Format("SELECT * FROM usergroup ");
if (whereClause != string.Empty)
{
sql += "WHERE " + whereClause;
}
if (orderClause != string.Empty)
{
sql += orderClause;
}
VideoDatabase.GetMoviesByFilter(sql, out movies, false, false, false, true);
ArrayList moviesExt = new ArrayList();
sql = String.Format("SELECT * FROM movieinfo WHERE idMovie NOT IN (SELECT DISTINCT idMovie FROM usergrouplinkmovie) ORDER BY strTitle");
VideoDatabase.GetMoviesByFilter(sql, out moviesExt, false, true, false, false);
movies.AddRange(moviesExt);
}
else if (defRoot.Where == "year")
{
movies = new ArrayList();
sql = String.Format("SELECT DISTINCT iYear FROM movieinfo ");
SQLiteResultSet results = VideoDatabase.GetResults(sql);
for (int i = 0; i < results.Rows.Count; i++)
{
IMDBMovie movie = new IMDBMovie();
movie.Year = (int)Math.Floor(0.5d + Double.Parse(DatabaseUtility.Get(results, i, "iYear")));
movies.Add(movie);
}
}
// Recently added
else if (defRoot.Where == "recently added")
{
try
{
if (string.IsNullOrEmpty(defRoot.Restriction))
{
defRoot.Restriction = "7";
}
TimeSpan ts = new TimeSpan(Convert.ToInt32(defRoot.Restriction), 0, 0, 0);
DateTime searchDate = DateTime.Today - ts;
whereClause = String.Format("WHERE movieinfo.dateAdded >= '{0}'",
searchDate.ToString("yyyy-MM-dd" + " 00:00:00"));
sql = String.Format("SELECT * FROM movieinfo {0} {1}", whereClause, orderClause);
VideoDatabase.GetMoviesByFilter(sql, out movies, false, true, false, false);
}
catch (Exception) { }
}
// Recently watched
else if (defRoot.Where == "recently watched")
{
try
{
if (string.IsNullOrEmpty(defRoot.Restriction))
{
defRoot.Restriction = "7";
}
TimeSpan ts = new TimeSpan(Convert.ToInt32(defRoot.Restriction), 0, 0, 0);
DateTime searchDate = DateTime.Today - ts;
whereClause = String.Format("WHERE movieinfo.dateWatched >= '{0}'",
searchDate.ToString("yyyy-MM-dd" + " 00:00:00"));
sql = String.Format("SELECT * FROM movieinfo {0} {1}", whereClause, orderClause);
VideoDatabase.GetMoviesByFilter(sql, out movies, false, true, false, false);
}
catch (Exception) { }
}
else
{
whereClause =
"WHERE movieinfo.idmovie=movie.idmovie AND movie.idpath=path.idpath";
BuildRestriction(defRoot, ref whereClause);
sql = String.Format("SELECT * FROM {0} {1} {2}",
fromClause, whereClause, orderClause);
VideoDatabase.GetMoviesByFilter(sql, out movies, false, true, true, true);
}
}
else if (CurrentLevel < MaxLevels - 1)
{
bool useMovieInfoTable = false;
bool useAlbumTable = false;
bool useActorsTable = false;
bool useGenreTable = false;
bool useUserGroupsTable = false;
string join = string.Empty;
string fields = "*";
FilterDefinition defCurrent = (FilterDefinition)currentView.Filters[CurrentLevel];
string table = GetTable(defCurrent.Where, ref useMovieInfoTable, ref useAlbumTable, ref useActorsTable,
ref useGenreTable, ref useUserGroupsTable);
if (defCurrent.Where == "director")
{
fields = "idActor, strActor, imdbActorId";
join = "INNER JOIN movieinfo ON movieinfo.idDirector = actors.idActor";
}
if (whereClause != string.Empty)
{
if (!whereClause.ToUpperInvariant().Trim().StartsWith("WHERE"))
{
whereClause = "WHERE" + whereClause;
}
}
if (defCurrent.Where == "actor")
{
if (whereClause != string.Empty)
{
whereClause = whereClause + " AND idActor NOT IN (SELECT idDirector FROM movieinfo)";
}
else
{
whereClause = " WHERE idActor NOT IN (SELECT idDirector FROM movieinfo)";
}
}
sql = String.Format("SELECT DISTINCT {0} FROM {1} {2} {3} {4}",
fields, table, join, whereClause, orderClause);
VideoDatabase.GetMoviesByFilter(sql, out movies, useActorsTable, useMovieInfoTable, useGenreTable, useUserGroupsTable);
}
else
{
sql =
String.Format(
"SELECT DISTINCT movieinfo.idMovie, " +
"movieinfo.idDirector, " +
"movieinfo.strDirector, " +
"movieinfo.strPlotOutline, " +
"movieinfo.strPlot, " +
"movieinfo.strTagLine, " +
"movieinfo.strVotes, " +
"movieinfo.fRating, " +
"movieinfo.strCast, " +
"movieinfo.strCredits, " +
"movieinfo.iYear, " +
"movieinfo.strGenre, " +
"movieinfo.strPictureURL, " +
"movieinfo.strTitle, " +
"movieinfo.IMDBID, " +
"movieinfo.mpaa, " +
"movieinfo.runtime, " +
"movieinfo.iswatched, " +
"movieinfo.strUserReview, " +
"movieinfo.strFanartURL, " +
"movieinfo.dateAdded, " +
"movieinfo.dateWatched, " +
"movieinfo.studios, " +
"movieinfo.country, " +
"movieinfo.language, " +
"movieinfo.lastupdate, " +
"movieinfo.strSortTitle, " +
"path.strPath, " +
"movie.discid, " +
"path.cdlabel " +
"FROM {0} {1} {2}",
fromClause, whereClause, orderClause);
VideoDatabase.GetMoviesByFilter(sql, out movies, true, true, true, true);
}
return(movies);
}
