public SearchResultsViewModel(WebSearchResult res, string url)
{
Score = res.Score;
URL = url;
Type = (SearchResultType)res.Type;
Title = GetTitle(res);
}
public IEnumerable<WebSearchResult> Search(string text)
{
string sql =
"SELECT id, title, actors, year, genres, alternate_titles " +
"FROM movie_info " +
"WHERE title LIKE @search OR alternate_titles LIKE @search OR actors LIKE @search";
return ReadList<WebSearchResult>(sql, delegate(SQLiteDataReader reader)
{
var result = new WebSearchResult()
{
Type = WebMediaType.Movie,
Id = reader.ReadIntAsString(0),
Title = reader.ReadString(1),
Details = new WebDictionary<string>()
{
{ "Year", reader.ReadIntAsString(3) },
{ "Genres", String.Join(", ", reader.ReadPipeList(4)) },
}
};
// simple score
int score = result.Title.Contains(text, false) ? (int)Math.Round(40 + (decimal)text.Length / result.Title.Length * 40) : 0;
var validAlternate = reader.ReadPipeList(5).Where(x => x.Contains(text, false));
score = Math.Max(score, validAlternate.Count() > 0 ? validAlternate.Max(x => 40 + (int)Math.Round((decimal)text.Length / x.Length * 30)) : 0);
// actors
var valid = reader.ReadPipeList(2).Where(x => x.Contains(text, false));
score = Math.Max(score, valid.Count() > 0 ? valid.Max(x => 40 + (int)Math.Round((decimal)text.Length / x.Length * 30)) : 0);
// set
result.Score = score;
return result;
}, new SQLiteParameter("@search", "%" + text + "%"));
}
private string GetTitle(WebSearchResult result)
{
switch (result.Type)
{
case WebMediaType.MusicAlbum:
return String.Format(FormStrings.SearchResultAlbum, result.Title, result.Details["Artist"]);
case WebMediaType.MusicTrack:
return String.Format(FormStrings.SearchResultTrack, result.Title, result.Details["Artist"]);
case WebMediaType.TVEpisode:
return String.Format("{0} ({1} {2}x{3})", result.Title, result.Details["ShowName"], result.Details["SeasonNumber"], result.Details["EpisodeNumber"]);
default:
return result.Title;
}
}
private string CreateLink(WebSearchResult result)
{
switch (result.Type)
{
case WebMediaType.Movie:
return Url.Action("Details", "MovieLibrary", new { movie = result.Id });
case WebMediaType.MusicAlbum:
return Url.Action("Album", "MusicLibrary", new { album = result.Id });
case WebMediaType.MusicArtist:
return Url.Action("Albums", "MusicLibrary", new { artist = result.Id });
case WebMediaType.MusicTrack:
return Url.Action("Album", "MusicLibrary", new { album = result.Details["AlbumId"], track = result.Id });
case WebMediaType.TVEpisode:
return Url.Action("Details", "TVShowsLibrary", new { episode = result.Id });
case WebMediaType.TVSeason:
return Url.Action("Episodes", "TVShowsLibrary", new { season = result.Id });
case WebMediaType.TVShow:
return Url.Action("Seasons", "TVShowsLibrary", new { show = result.Id });
default:
return null;
}
}
public IEnumerable<WebSearchResult> Search(string text)
{
using (DatabaseConnection connection = OpenConnection())
{
IEnumerable<WebSearchResult> results;
SQLiteParameter param = new SQLiteParameter("@search", "%" + text + "%");
// search for shows
string showSql = "SELECT ID, Pretty_Name FROM online_series WHERE Pretty_Name LIKE @search";
IEnumerable<WebSearchResult> shows = ReadList<WebSearchResult>(showSql, delegate(SQLiteDataReader reader)
{
string title = reader.ReadString(1);
return new WebSearchResult()
{
Type = WebMediaType.TVShow,
Id = reader.ReadIntAsString(0),
Title = title,
Score = 40 + (int)Math.Round((decimal)text.Length / title.Length * 40),
};
}, param);
results = shows;
string actorShowSql = "SELECT ID, Pretty_Name, Actors FROM online_series WHERE Actors LIKE @search";
IEnumerable<WebSearchResult> actorShows = ReadList<WebSearchResult>(actorShowSql, delegate(SQLiteDataReader reader)
{
var valid = reader.ReadPipeList(2).Where(x => x.IndexOf(text, StringComparison.CurrentCultureIgnoreCase) >= 0); // contains is case sensitive
if (valid.Count() == 0)
return null;
return new WebSearchResult()
{
Type = WebMediaType.TVShow,
Id = reader.ReadIntAsString(0),
Title = reader.ReadString(1),
Score = valid.Max(x => 40 + (int)Math.Round((decimal)text.Length / x.Length * 30))
};
}, param);
results = results.Concat(actorShows);
#region Episodes
// initialize
List<SQLiteParameter> parameters = new List<SQLiteParameter>() { param };
// search for episodes
Regex episodeRegex = new Regex(@"^(.*) ([0-9]{1,3})x([0-9]{1,3})$");
Match episodeResult = episodeRegex.Match(text);
string episodeRegexWhere = "0";
if (episodeResult.Success)
{
episodeRegexWhere = "((os.Pretty_Name LIKE @show OR ls.Parsed_Name LIKE @show) AND e.SeasonIndex = @season AND e.EpisodeIndex = @episode)";
parameters.Add(new SQLiteParameter("@show", "%" + episodeResult.Groups[1].Value.Trim() + "%"));
parameters.Add(new SQLiteParameter("@season", episodeResult.Groups[2].Value));
parameters.Add(new SQLiteParameter("@episode", episodeResult.Groups[3].Value));
}
// execute
string episodeSql =
"SELECT e.EpisodeID, e.EpisodeName, e.EpisodeIndex, e.SeasonIndex, e.SeriesID, e.GuestStars, os.Pretty_Name, MIN(ls.Parsed_Name) AS Parsed_Name " +
"FROM online_episodes e " +
"INNER JOIN online_series os ON os.ID = e.SeriesID " +
"INNER JOIN local_series ls ON ls.ID = e.SeriesID " +
"WHERE e.EpisodeName LIKE @search OR e.GuestStars LIKE @search OR " + episodeRegexWhere + " " +
"GROUP BY e.EpisodeID, e.EpisodeName, e.EpisodeIndex, e.SeasonIndex, e.SeriesID, e.GuestStars, os.Pretty_Name ";
IEnumerable<WebSearchResult> episodes = ReadList<WebSearchResult>(episodeSql, delegate(SQLiteDataReader reader)
{
// read the data
string title = reader.ReadString(1);
string showTitle = reader.ReadString(6);
WebSearchResult result = new WebSearchResult()
{
Type = WebMediaType.TVEpisode,
Id = reader.ReadIntAsString(0),
Title = title,
Details = new SerializableDictionary<string>()
{
{ "EpisodeNumber", reader.ReadIntAsString(2) },
{ "SeasonNumber", reader.ReadIntAsString(3) },
{ "ShowId", reader.ReadIntAsString(4) },
{ "ShowName", !String.IsNullOrEmpty(showTitle) ? showTitle : reader.ReadString(7) }
}
};
// title
int score = title.Contains(text, false) ? 40 + (int)Math.Round((decimal)text.Length / title.Length * 40) : 0;
// guest stars
var valid = reader.ReadPipeList(5).Where(x => x.Contains(text, false)); // .Contains() is case sensitive
score = Math.Max(score, valid.Count() > 0 ? valid.Max(x => 20 + (int)Math.Round((decimal)text.Length / x.Length * 40)) : 0);
// regex match
if (episodeResult.Success &&
(result.Details["ShowName"].Contains(episodeResult.Groups[1].Value.Trim(), false) &&
result.Details["SeasonNumber"] == episodeResult.Groups[2].Value &&
result.Details["EpisodeNumber"] == episodeResult.Groups[3].Value))
score = 100;
// set score and return
result.Score = score;
return result;
}, parameters.ToArray());
// when there are multiple matches with 100%, set them all to 95% as we apparantly aren't sure
if (episodes.Count(x => x.Score == 100) > 1)
{
episodes = episodes.Select(x =>
{
if (x.Score == 100)
x.Score = 95;
return x;
});
}
results = results.Concat(episodes);
#endregion
// fancy season search: <showname> s<season>
Regex seasonRegex = new Regex(@"^(.*) s([0-9]{1,3})$");
Match seasonResult = seasonRegex.Match(text);
if (seasonResult.Success)
{
string sql =
"SELECT DISTINCT s.ID, s.SeasonIndex, s.SeriesID, o.Pretty_Name, MIN(l.Parsed_Name) AS Parsed_Name " +
"FROM season s " +
"INNER JOIN online_series o ON s.SeriesID = o.ID " +
"INNER JOIN local_series l ON s.SeriesID = l.ID " +
"WHERE (o.Pretty_Name = @show OR l.Parsed_Name = @show) AND s.SeasonIndex = @season " +
"GROUP BY s.ID, s.SeasonIndex, s.SeriesID, o.Pretty_Name ";
results = ReadList<WebSearchResult>(sql, delegate(SQLiteDataReader reader)
{
string showTitle = !String.IsNullOrEmpty(reader.ReadString(3)) ? reader.ReadString(3) : reader.ReadString(4);
return new WebSearchResult()
{
Type = WebMediaType.TVSeason,
Id = reader.ReadString(0),
Title = showTitle + " (season " + reader.ReadInt32(1) + ")",
Score = 100,
Details = new SerializableDictionary<string>()
{
{ "SeasonNumber", reader.ReadIntAsString(1) },
{ "ShowId", reader.ReadIntAsString(2) },
{ "ShowName", showTitle }
}
};
}, new SQLiteParameter("@show", seasonResult.Groups[1].Value.Trim()),
new SQLiteParameter("@season", seasonResult.Groups[2].Value))
.Concat(results);
}
// return
return results;
}
}
