private void BuildSelect(FilterDefinition filter, ref string whereClause, ref string fromClause) { if (whereClause != "") { whereClause += " AND "; } string cleanValue = DatabaseUtility.RemoveInvalidChars(filter.SelectedValue); if (cleanValue == "#" && (filter.Where == "actorindex" || filter.Where == "directorindex" || filter.Where == "titleindex")) { string nWordChar = VideoDatabase.NonwordCharacters(); whereClause += String.Format(" {0} IN ({1})", GetFieldId(filter.Where), nWordChar); } else { whereClause += String.Format(" {0}='{1}'", GetFieldId(filter.Where), cleanValue); } bool useMovieInfoTable = false; bool useAlbumTable = false; bool useActorsTable = false; bool useGenreTable = false; bool useUserGroupsTable = false; bool useMovieCollectionTable = false; string table = GetTable(filter.Where, ref useMovieInfoTable, ref useAlbumTable, ref useActorsTable, ref useGenreTable, ref useUserGroupsTable, ref useMovieCollectionTable); if (useGenreTable) { whereClause += " AND idSingleGenre IS NOT NULL"; return; } if (useUserGroupsTable) { whereClause += " AND idGroup IS NOT NULL"; return; } if (useMovieCollectionTable) { whereClause += " AND idCollection IS NOT NULL"; return; } if (useActorsTable) { // if ((CurrentLevel > 0) && (CurrentLevel == MaxLevels - 1)) { whereClause += string.Format(" AND {0} <> 'unknown' AND {0} IS NOT NULL", GetFieldName(filter.Where)); } return; } }
private void BuildSelect(FilterDefinition filter, ref string whereClause, ref string fromClause) { if (whereClause != "") { whereClause += " AND "; } string cleanValue = DatabaseUtility.RemoveInvalidChars(filter.SelectedValue); if (filter.Where == "actorindex" || filter.Where == "directorindex" || filter.Where == "titleindex") { if (cleanValue == "#") { string nWordChar = VideoDatabase.NonwordCharacters(); if (filter.Where == "actorindex" || filter.Where == "directorindex") { whereClause += @" SUBSTR(strActor,1,1) IN (" + nWordChar + ")"; } else { whereClause += @" SUBSTR(strTitle,1,1) IN (" + nWordChar + ")"; } } else { whereClause += String.Format(" {0}='{1}'", GetFieldId(filter.Where), cleanValue); } } else { whereClause += String.Format(" {0}='{1}'", GetFieldId(filter.Where), cleanValue); } bool useMovieInfoTable = false; bool useAlbumTable = false; bool useActorsTable = false; bool useGenreTable = false; bool useUserGroupsTable = false; string table = GetTable(filter.Where, ref useMovieInfoTable, ref useAlbumTable, ref useActorsTable, ref useGenreTable, ref useUserGroupsTable); if (useGenreTable) { fromClause += String.Format(",genre,genrelinkmovie"); whereClause += " AND genre.idGenre=genrelinkMovie.idGenre AND genrelinkMovie.idMovie=movieinfo.idMovie"; return; } if (useUserGroupsTable) { fromClause += String.Format(",usergroup,usergrouplinkmovie"); whereClause += " AND usergroup.idGroup=usergrouplinkmovie.idGroup AND usergrouplinkMovie.idMovie=movieinfo.idMovie"; return; } if (useActorsTable) { if (CurrentLevel == MaxLevels - 1 && filter.Where == "actor") { fromClause += String.Format(",actors ,actorlinkmovie"); whereClause += " AND actors.idActor=actorlinkmovie.idActor AND actorlinkmovie.idMovie=movieinfo.idMovie"; } return; } }