/// <summary> /// Get show light by its Imdb code /// </summary> /// <param name="imdbId">Show's Imdb code</param> /// <returns>The show</returns> public async Task <ShowLightJson> GetShowLightAsync(string imdbId, CancellationToken ct) { var timeoutPolicy = Policy.TimeoutAsync(Utils.Constants.DefaultRequestTimeoutInSecond, TimeoutStrategy.Pessimistic); try { return(await timeoutPolicy.ExecuteAsync(async cancellation => { var watch = Stopwatch.StartNew(); var restClient = new RestClient(Utils.Constants.PopcornApi); var request = new RestRequest("/{segment}/light/{show}", Method.GET); request.AddUrlSegment("segment", "shows"); request.AddUrlSegment("show", imdbId); var show = new ShowLightJson(); try { var response = await restClient.ExecuteTaskAsync(request, cancellation) .ConfigureAwait(false); if (response.ErrorException != null) { throw response.ErrorException; } show = JsonSerializer.Deserialize <ShowLightJson>(response.RawBytes); } catch (Exception exception) when(exception is TaskCanceledException) { Logger.Debug( "GetShowLightAsync cancelled."); } catch (Exception exception) { Logger.Error( $"GetShowLightAsync: {exception.Message}"); throw; } finally { watch.Stop(); var elapsedMs = watch.ElapsedMilliseconds; Logger.Debug( $"GetShowLightAsync ({imdbId}) in {elapsedMs} milliseconds."); } return show; }, ct).ConfigureAwait(false)); } catch (Exception ex) { Logger.Error(ex); throw; } }
/// <summary> /// Get show light by its Imdb code /// </summary> /// <param name="imdbId">Show's Imdb code</param> /// <returns>The show</returns> public async Task <ShowLightJson> GetShowLightAsync(string imdbId) { var watch = Stopwatch.StartNew(); var restClient = new RestClient(Utils.Constants.PopcornApi); var request = new RestRequest("/{segment}/light/{show}", Method.GET); request.AddUrlSegment("segment", "shows"); request.AddUrlSegment("show", imdbId); var show = new ShowLightJson(); try { var response = await restClient.ExecuteTaskAsync <ShowLightJson>(request).ConfigureAwait(false); if (response.ErrorException != null) { throw response.ErrorException; } show = response.Data; } catch (Exception exception) when(exception is TaskCanceledException) { Logger.Debug( "GetShowLightAsync cancelled."); } catch (Exception exception) { Logger.Error( $"GetShowLightAsync: {exception.Message}"); throw; } finally { watch.Stop(); var elapsedMs = watch.ElapsedMilliseconds; Logger.Debug( $"GetShowLightAsync ({imdbId}) in {elapsedMs} milliseconds."); } return(show); }
public async Task <IActionResult> Get([RequiredFromQuery] int page, [FromQuery] int limit, [FromQuery] int minimum_rating, [FromQuery] string query_term, [FromQuery] string genre, [FromQuery] string sort_by) { var nbShowsPerPage = 20; if (limit >= 20 && limit <= 50) { nbShowsPerPage = limit; } var currentPage = 1; if (page >= 1) { currentPage = page; } var queryTerm = string.Empty; if (!string.IsNullOrWhiteSpace(query_term)) { queryTerm = query_term; } var genreFilter = string.Empty; if (!string.IsNullOrWhiteSpace(genre)) { genreFilter = genre; } var hash = Convert.ToBase64String( Encoding.UTF8.GetBytes( $@"type=shows&page={page}&limit={limit}&minimum_rating={minimum_rating}&query_term={ query_term }&genre={genre}&sort_by={sort_by}")); try { var cachedShows = await _cachingService.GetCache(hash); if (cachedShows != null) { try { return(Json(JsonConvert.DeserializeObject <ShowLightResponse>(cachedShows))); } catch (Exception ex) { _loggingService.Telemetry.TrackException(ex); } } } catch (Exception ex) { _loggingService.Telemetry.TrackException(ex); } using (var context = new PopcornContextFactory().CreateDbContext(new string[0])) { var skipParameter = new SqlParameter("@skip", (currentPage - 1) * nbShowsPerPage); var takeParameter = new SqlParameter("@take", nbShowsPerPage); var ratingParameter = new SqlParameter("@rating", minimum_rating); var queryParameter = new SqlParameter("@Keywords", queryTerm); var genreParameter = new SqlParameter("@genre", genreFilter); var query = @" SELECT Show.Title, Show.Year, Rating.Percentage, Rating.Loved, Rating.Votes, Rating.Hated, Rating.Watching, Show.LastUpdated, Image.Banner, Image.Fanart, Image.Poster, Show.ImdbId, Show.TvdbId, Show.GenreNames, COUNT(*) OVER () as TotalCount FROM ShowSet AS Show INNER JOIN ImageShowSet AS Image ON Image.Id = Show.ImagesId INNER JOIN RatingSet AS Rating ON Rating.Id = Show.RatingId WHERE 1 = 1"; if (minimum_rating > 0 && minimum_rating < 10) { query += @" AND Rating >= @rating"; } if (!string.IsNullOrWhiteSpace(query_term)) { query += @" AND FREETEXT(Title, @Keywords)"; } if (!string.IsNullOrWhiteSpace(genre)) { query += @" AND CONTAINS(GenreNames, @genre)"; } if (!string.IsNullOrWhiteSpace(sort_by)) { switch (sort_by) { case "title": query += " ORDER BY Show.Title ASC"; break; case "year": query += " ORDER BY Show.Year DESC"; break; case "rating": query += " ORDER BY Rating.Percentage DESC"; break; case "loved": query += " ORDER BY Rating.Loved DESC"; break; case "votes": query += " ORDER BY Rating.Votes DESC"; break; case "watching": query += " ORDER BY Rating.Watching DESC"; break; case "date_added": query += " ORDER BY Show.LastUpdated DESC"; break; default: query += " ORDER BY Show.LastUpdated DESC"; break; } } else { query += " ORDER BY Show.LastUpdated DESC"; } query += @" OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY"; var showsQuery = await context.Database.ExecuteSqlQueryAsync(query, new CancellationToken(), skipParameter, takeParameter, ratingParameter, queryParameter, genreParameter); var reader = showsQuery.DbDataReader; var count = 0; var shows = new List <ShowLightJson>(); while (await reader.ReadAsync()) { var show = new ShowLightJson { Title = reader[0].GetType() != typeof(DBNull) ? (string)reader[0] : string.Empty, Year = reader[1].GetType() != typeof(DBNull) ? (int)reader[1] : 0, Rating = new RatingJson { Percentage = reader[2].GetType() != typeof(DBNull) ? (int)reader[2] : 0, Loved = reader[3].GetType() != typeof(DBNull) ? (int)reader[3] : 0, Votes = reader[4].GetType() != typeof(DBNull) ? (int)reader[4] : 0, Hated = reader[5].GetType() != typeof(DBNull) ? (int)reader[5] : 0, Watching = reader[6].GetType() != typeof(DBNull) ? (int)reader[6] : 0 }, Images = new ImageShowJson { Banner = reader[8].GetType() != typeof(DBNull) ? (string)reader[8] : string.Empty, Fanart = reader[9].GetType() != typeof(DBNull) ? (string)reader[9] : string.Empty, Poster = reader[10].GetType() != typeof(DBNull) ? (string)reader[10] : string.Empty, }, ImdbId = reader[11].GetType() != typeof(DBNull) ? (string)reader[11] : string.Empty, TvdbId = reader[12].GetType() != typeof(DBNull) ? (string)reader[12] : string.Empty, Genres = reader[13].GetType() != typeof(DBNull) ? (string)reader[13] : string.Empty }; shows.Add(show); count = reader[14].GetType() != typeof(DBNull) ? (int)reader[14] : 0; } var response = new ShowLightResponse { TotalShows = count, Shows = shows }; await _cachingService.SetCache(hash, JsonConvert.SerializeObject(response), TimeSpan.FromDays(1)); return (Json(response)); } }
public async Task <IActionResult> GetLight(string imdb) { var hash = Convert.ToBase64String( Encoding.UTF8.GetBytes($"light:{imdb}")); try { var cachedShow = await _cachingService.GetCache(hash); if (cachedShow != null) { try { return(Json(JsonConvert.DeserializeObject <ShowLightJson>(cachedShow))); } catch (Exception ex) { _loggingService.Telemetry.TrackException(ex); } } } catch (Exception ex) { _loggingService.Telemetry.TrackException(ex); } using (var context = new PopcornContextFactory().CreateDbContext(new string[0])) { var imdbParameter = new SqlParameter("@imdbId", imdb); var query = @" SELECT Show.Title, Show.Year, Rating.Percentage, Rating.Loved, Rating.Votes, Rating.Hated, Rating.Watching, Show.LastUpdated, Image.Banner, Image.Fanart, Image.Poster, Show.ImdbId, Show.TvdbId, Show.GenreNames FROM ShowSet AS Show INNER JOIN ImageShowSet AS Image ON Image.Id = Show.ImagesId INNER JOIN RatingSet AS Rating ON Rating.Id = Show.RatingId WHERE Show.ImdbId = @imdbId"; var showQuery = await context.Database.ExecuteSqlQueryAsync(query, new CancellationToken(), imdbParameter); var reader = showQuery.DbDataReader; var show = new ShowLightJson(); while (await reader.ReadAsync()) { show.Title = reader[0].GetType() != typeof(DBNull) ? (string)reader[0] : string.Empty; show.Year = reader[1].GetType() != typeof(DBNull) ? (int)reader[1] : 0; show.Rating = new RatingJson { Percentage = reader[2].GetType() != typeof(DBNull) ? (int)reader[2] : 0, Loved = reader[3].GetType() != typeof(DBNull) ? (int)reader[3] : 0, Votes = reader[4].GetType() != typeof(DBNull) ? (int)reader[4] : 0, Hated = reader[5].GetType() != typeof(DBNull) ? (int)reader[5] : 0, Watching = reader[6].GetType() != typeof(DBNull) ? (int)reader[6] : 0 }; show.Images = new ImageShowJson { Banner = reader[8].GetType() != typeof(DBNull) ? (string)reader[8] : string.Empty, Fanart = reader[9].GetType() != typeof(DBNull) ? (string)reader[9] : string.Empty, Poster = reader[10].GetType() != typeof(DBNull) ? (string)reader[10] : string.Empty }; show.ImdbId = reader[11].GetType() != typeof(DBNull) ? (string)reader[11] : string.Empty; show.TvdbId = reader[12].GetType() != typeof(DBNull) ? (string)reader[12] : string.Empty; show.Genres = reader[13].GetType() != typeof(DBNull) ? (string)reader[13] : string.Empty; } if (string.IsNullOrEmpty(show.ImdbId)) { return(BadRequest()); } await _cachingService.SetCache(hash, JsonConvert.SerializeObject(show)); return(Json(show)); } }
/// <summary> /// Search shows by criteria /// </summary> /// <param name="criteria">Criteria used for search</param> /// <param name="page">Page to return</param> /// <param name="limit">The maximum number of movies to return</param> /// <param name="genre">The genre to filter</param> /// <param name="ratingFilter">Used to filter by rating</param> /// <param name="sortBy">Sort by</param> /// <param name="ct">Cancellation token</param> /// <returns>Searched shows and the number of movies found</returns> public async Task <(IEnumerable <ShowLightJson> shows, int nbShows)> SearchShowsAsync(string criteria, int page, int limit, GenreJson genre, double ratingFilter, string sortBy, CancellationToken ct) { var timeoutPolicy = Policy.TimeoutAsync(Constants.DefaultRequestTimeoutInSecond, TimeoutStrategy.Optimistic); try { return(await timeoutPolicy.ExecuteAsync(async cancellation => { var optionsBuilder = new DbContextOptionsBuilder <PopcornContext>(); optionsBuilder.UseSqlServer( (ConfigurationManager.GetSection("settings") as NameValueCollection)["SQLConnectionString"], builder => { builder.CommandTimeout(Convert.ToInt32(TimeSpan.FromSeconds(60).TotalSeconds)); builder.EnableRetryOnFailure(); }); await using var context = new PopcornContext(optionsBuilder.Options); var watch = Stopwatch.StartNew(); if (limit < 1 || limit > 50) { limit = Constants.MaxShowsPerPage; } if (page < 1) { page = 1; } var count = 0; var shows = new List <ShowLightJson>(); var skipParameter = new SqlParameter("@skip", (page - 1) * limit); var takeParameter = new SqlParameter("@take", limit); var ratingParameter = new SqlParameter("@rating", Convert.ToInt32(ratingFilter).ToString()); var queryParameter = new SqlParameter("@Keywords", string.Format(@"""{0}""", criteria)); var genreParameter = new SqlParameter("@genre", genre != null ? genre.EnglishName : string.Empty); var query = @" SELECT Show.Title, Show.Year, Rating.Percentage, Rating.Loved, Rating.Votes, Rating.Hated, Rating.Watching, Show.LastUpdated, Image.Banner, Image.Poster, Show.ImdbId, Show.TvdbId, Show.GenreNames, COUNT(*) OVER () as TotalCount FROM ShowSet AS Show INNER JOIN ImageShowSet AS Image ON Image.Id = Show.ImagesId INNER JOIN RatingSet AS Rating ON Rating.Id = Show.RatingId WHERE Show.NumSeasons <> 0"; if (ratingFilter >= 0 && ratingFilter <= 100) { query += @" AND Rating.Percentage >= @rating"; } if (!string.IsNullOrWhiteSpace(criteria)) { query += @" AND (CONTAINS(Title, @Keywords) OR CONTAINS(ImdbId, @Keywords) OR CONTAINS(TvdbId, @Keywords))"; } if (!string.IsNullOrWhiteSpace(genre != null ? genre.EnglishName : string.Empty)) { query += @" AND CONTAINS(GenreNames, @genre)"; } if (!string.IsNullOrWhiteSpace(sortBy)) { switch (sortBy) { case "title": query += " ORDER BY Show.Title ASC"; break; case "year": query += " ORDER BY Show.Year DESC"; break; case "rating": query += " ORDER BY Rating.Percentage DESC"; break; case "loved": query += " ORDER BY Rating.Loved DESC"; break; case "votes": query += " ORDER BY Rating.Votes DESC"; break; case "watching": query += " ORDER BY Rating.Watching DESC"; break; case "date_added": query += " ORDER BY Show.LastUpdated DESC"; break; default: query += " ORDER BY Show.LastUpdated DESC"; break; } } else { query += " ORDER BY Show.LastUpdated DESC"; } query += @" OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY"; try { await using var command = context.Database.GetDbConnection().CreateCommand(); command.CommandText = query; command.CommandType = CommandType.Text; command.Parameters.Add(skipParameter); command.Parameters.Add(takeParameter); command.Parameters.Add(ratingParameter); command.Parameters.Add(queryParameter); command.Parameters.Add(genreParameter); await context.Database.OpenConnectionAsync(cancellation); await using var reader = await command.ExecuteReaderAsync(cancellation); while (await reader.ReadAsync(cancellation)) { var show = new ShowLightJson { Title = !await reader.IsDBNullAsync(0, cancellation) ? reader.GetString(0) : string.Empty, Year = !await reader.IsDBNullAsync(1, cancellation) ? reader.GetInt32(1) : 0, Rating = new RatingJson { Percentage = !await reader.IsDBNullAsync(2, cancellation) ? reader.GetInt32(2) : 0, Loved = !await reader.IsDBNullAsync(3, cancellation) ? reader.GetInt32(3) : 0, Votes = !await reader.IsDBNullAsync(4, cancellation) ? reader.GetInt32(4) : 0, Hated = !await reader.IsDBNullAsync(5, cancellation) ? reader.GetInt32(5) : 0, Watching = !await reader.IsDBNullAsync(6, cancellation) ? reader.GetInt32(6) : 0 }, Images = new ImageShowJson { Banner = !await reader.IsDBNullAsync(8, cancellation) ? reader.GetString(8) : string.Empty, Poster = !await reader.IsDBNullAsync(9, cancellation) ? reader.GetString(9) : string.Empty, }, ImdbId = !await reader.IsDBNullAsync(10, cancellation) ? reader.GetString(10) : string.Empty, TvdbId = !await reader.IsDBNullAsync(11, cancellation) ? reader.GetString(11) : string.Empty, Genres = !await reader.IsDBNullAsync(12, cancellation) ? reader.GetString(12) : string.Empty }; shows.Add(show); count = !await reader.IsDBNullAsync(13, cancellation) ? reader.GetInt32(13) : 0; } } catch (Exception exception) when(exception is TaskCanceledException) { Logger.Debug( "SearchShowsAsync cancelled."); } catch (Exception exception) { Logger.Error( $"SearchShowsAsync: {exception.Message}"); throw; } finally { watch.Stop(); var elapsedMs = watch.ElapsedMilliseconds; Logger.Trace( $"SearchShowsAsync ({criteria}, {page}, {limit}) in {elapsedMs} milliseconds."); } return (shows, count); }, ct)); } catch (Exception ex) { Logger.Error(ex); throw; } }
/// <summary> /// Get shows by ids /// </summary> /// <param name="imdbIds">The imdbIds of the shows, split by comma</param> /// <param name="ct">Cancellation token</param> /// <returns>Shows</returns> public async Task <(IEnumerable <ShowLightJson> movies, int nbMovies)> GetShowsByIds(IList <string> imdbIds, CancellationToken ct) { var timeoutPolicy = Policy.TimeoutAsync(Constants.DefaultRequestTimeoutInSecond, TimeoutStrategy.Optimistic); try { return(await timeoutPolicy.ExecuteAsync(async cancellation => { var optionsBuilder = new DbContextOptionsBuilder <PopcornContext>(); optionsBuilder.UseSqlServer( (ConfigurationManager.GetSection("settings") as NameValueCollection)["SQLConnectionString"], builder => { builder.CommandTimeout(Convert.ToInt32(TimeSpan.FromSeconds(60).TotalSeconds)); builder.EnableRetryOnFailure(); }); await using var context = new PopcornContext(optionsBuilder.Options); var watch = Stopwatch.StartNew(); var query = @" SELECT DISTINCT Show.Title, Show.Year, Rating.Percentage, Rating.Loved, Rating.Votes, Rating.Hated, Rating.Watching, Show.LastUpdated, Image.Banner, Image.Poster, Show.ImdbId, Show.TvdbId, Show.GenreNames, COUNT(*) OVER () as TotalCount FROM ShowSet AS Show INNER JOIN ImageShowSet AS Image ON Image.Id = Show.ImagesId INNER JOIN RatingSet AS Rating ON Rating.Id = Show.RatingId WHERE Show.ImdbId IN ({0})"; await using var command = context.Database.GetDbConnection().CreateCommand(); command.CommandType = CommandType.Text; var imdbParameters = new List <string>(); var index = 0; foreach (var imdb in imdbIds) { var paramName = "@imdb" + index; command.Parameters.Add(new SqlParameter(paramName, imdb)); imdbParameters.Add(paramName); index++; } command.CommandText = string.Format(query, string.Join(",", imdbParameters)); var count = 0; var shows = new List <ShowLightJson>(); try { await context.Database.OpenConnectionAsync(cancellation); await using var reader = await command.ExecuteReaderAsync(cancellation); while (await reader.ReadAsync(cancellation)) { var show = new ShowLightJson { Title = !await reader.IsDBNullAsync(0, cancellation) ? reader.GetString(0) : string.Empty, Year = !await reader.IsDBNullAsync(1, cancellation) ? reader.GetInt32(1) : 0, Rating = new RatingJson { Percentage = !await reader.IsDBNullAsync(2, cancellation) ? reader.GetInt32(2) : 0, Loved = !await reader.IsDBNullAsync(3, cancellation) ? reader.GetInt32(3) : 0, Votes = !await reader.IsDBNullAsync(4, cancellation) ? reader.GetInt32(4) : 0, Hated = !await reader.IsDBNullAsync(5, cancellation) ? reader.GetInt32(5) : 0, Watching = !await reader.IsDBNullAsync(6, cancellation) ? reader.GetInt32(6) : 0 }, Images = new ImageShowJson { Banner = !await reader.IsDBNullAsync(8, cancellation) ? reader.GetString(8) : string.Empty, Poster = !await reader.IsDBNullAsync(9, cancellation) ? reader.GetString(9) : string.Empty, }, ImdbId = !await reader.IsDBNullAsync(10, cancellation) ? reader.GetString(10) : string.Empty, TvdbId = !await reader.IsDBNullAsync(11, cancellation) ? reader.GetString(11) : string.Empty, Genres = !await reader.IsDBNullAsync(12, cancellation) ? reader.GetString(12) : string.Empty }; shows.Add(show); count = !await reader.IsDBNullAsync(13, cancellation) ? reader.GetInt32(13) : 0; } } catch (Exception exception) when(exception is TaskCanceledException) { Logger.Debug( "GetShowsByIds cancelled."); } catch (Exception exception) { Logger.Error( $"GetShowsByIds: {exception.Message}"); throw; } finally { watch.Stop(); var elapsedMs = watch.ElapsedMilliseconds; Logger.Trace( $"GetShowsByIds ({string.Join(",", imdbIds)}) in {elapsedMs} milliseconds."); } return (shows, count); }, ct)); } catch (Exception ex) { Logger.Error(ex); return(new List <ShowLightJson>(), 0); } }
/// <summary> /// Get show light by its Imdb code /// </summary> /// <param name="imdbId">Show's Imdb code</param> /// <param name="ct">Cancellation token</param> /// <returns>The show</returns> public async Task <ShowLightJson> GetShowLightAsync(string imdbId, CancellationToken ct) { var timeoutPolicy = Policy.TimeoutAsync(Constants.DefaultRequestTimeoutInSecond, TimeoutStrategy.Optimistic); try { return(await timeoutPolicy.ExecuteAsync(async cancellation => { var optionsBuilder = new DbContextOptionsBuilder <PopcornContext>(); optionsBuilder.UseSqlServer( (ConfigurationManager.GetSection("settings") as NameValueCollection)["SQLConnectionString"], builder => { builder.CommandTimeout(Convert.ToInt32(TimeSpan.FromSeconds(60).TotalSeconds)); builder.EnableRetryOnFailure(); }); await using var context = new PopcornContext(optionsBuilder.Options); var watch = Stopwatch.StartNew(); var imdbParameter = new SqlParameter("@imdbId", imdbId); var query = @" SELECT Show.Title, Show.Year, Rating.Percentage, Rating.Loved, Rating.Votes, Rating.Hated, Rating.Watching, Show.LastUpdated, Image.Banner, Image.Poster, Show.ImdbId, Show.TvdbId, Show.GenreNames FROM ShowSet AS Show INNER JOIN ImageShowSet AS Image ON Image.Id = Show.ImagesId INNER JOIN RatingSet AS Rating ON Rating.Id = Show.RatingId WHERE Show.ImdbId = @imdbId"; var show = new ShowLightJson(); try { await using var command = context.Database.GetDbConnection().CreateCommand(); command.CommandText = query; command.CommandType = CommandType.Text; command.Parameters.Add(imdbParameter); await context.Database.OpenConnectionAsync(cancellation); await using var reader = await command.ExecuteReaderAsync(cancellation); while (await reader.ReadAsync(cancellation)) { show.Title = !await reader.IsDBNullAsync(0, cancellation) ? reader.GetString(0) : string.Empty; show.Year = !await reader.IsDBNullAsync(1, cancellation) ? reader.GetInt32(1) : 0; show.Rating = new RatingJson { Percentage = !await reader.IsDBNullAsync(2, cancellation) ? reader.GetInt32(2) : 0, Loved = !await reader.IsDBNullAsync(3, cancellation) ? reader.GetInt32(3) : 0, Votes = !await reader.IsDBNullAsync(4, cancellation) ? reader.GetInt32(4) : 0, Hated = !await reader.IsDBNullAsync(5, cancellation) ? reader.GetInt32(5) : 0, Watching = !await reader.IsDBNullAsync(6, cancellation) ? reader.GetInt32(6) : 0 }; show.Images = new ImageShowJson { Banner = !await reader.IsDBNullAsync(8, cancellation) ? reader.GetString(8) : string.Empty, Poster = !await reader.IsDBNullAsync(9, cancellation) ? reader.GetString(9) : string.Empty, }; show.ImdbId = !await reader.IsDBNullAsync(10, cancellation) ? reader.GetString(10) : string.Empty; show.TvdbId = !await reader.IsDBNullAsync(11, cancellation) ? reader.GetString(11) : string.Empty; show.Genres = !await reader.IsDBNullAsync(12, cancellation) ? reader.GetString(12) : string.Empty; } } catch (Exception exception) when(exception is TaskCanceledException) { Logger.Debug( "GetShowLightAsync cancelled."); } catch (Exception exception) { Logger.Error( $"GetShowLightAsync: {exception.Message}"); throw; } finally { watch.Stop(); var elapsedMs = watch.ElapsedMilliseconds; Logger.Trace( $"GetShowLightAsync ({imdbId}) in {elapsedMs} milliseconds."); } return show; }, ct)); } catch (Exception ex) { Logger.Error(ex); throw; } }
public async Task <IActionResult> GetLight(string imdb) { var hash = Convert.ToBase64String( Encoding.UTF8.GetBytes($"light:{imdb}")); try { var cachedShow = await _cachingService.GetCache(hash); if (cachedShow != null) { try { return(Content(cachedShow, "application/json")); } catch (Exception ex) { _loggingService.Telemetry.TrackException(ex); } } } catch (Exception ex) { _loggingService.Telemetry.TrackException(ex); } using (var context = new PopcornContextFactory().CreateDbContext(new string[0])) { var imdbParameter = new SqlParameter("@imdbId", imdb); var query = @" SELECT Show.Title, Show.Year, Rating.Percentage, Rating.Loved, Rating.Votes, Rating.Hated, Rating.Watching, Show.LastUpdated, Image.Banner, Image.Fanart, Image.Poster, Show.ImdbId, Show.TvdbId, Show.GenreNames FROM ShowSet AS Show INNER JOIN ImageShowSet AS Image ON Image.Id = Show.ImagesId INNER JOIN RatingSet AS Rating ON Rating.Id = Show.RatingId WHERE Show.ImdbId = @imdbId"; var showQuery = await context.Database.ExecuteSqlQueryAsync(query, new CancellationToken(), imdbParameter); var reader = showQuery.DbDataReader; var show = new ShowLightJson(); while (await reader.ReadAsync()) { show.Title = !await reader.IsDBNullAsync(0) ? reader.GetString(0) : string.Empty; show.Year = !await reader.IsDBNullAsync(1) ? reader.GetInt32(1) : 0; show.Rating = new RatingJson { Percentage = !await reader.IsDBNullAsync(2) ? reader.GetInt32(2) : 0, Loved = !await reader.IsDBNullAsync(3) ? reader.GetInt32(3) : 0, Votes = !await reader.IsDBNullAsync(4) ? reader.GetInt32(4) : 0, Hated = !await reader.IsDBNullAsync(5) ? reader.GetInt32(5) : 0, Watching = !await reader.IsDBNullAsync(6) ? reader.GetInt32(6) : 0 }; show.Images = new ImageShowJson { Banner = !await reader.IsDBNullAsync(8) ? reader.GetString(8) : string.Empty, Fanart = !await reader.IsDBNullAsync(9) ? reader.GetString(9) : string.Empty, Poster = !await reader.IsDBNullAsync(10) ? reader.GetString(10) : string.Empty, }; show.ImdbId = !await reader.IsDBNullAsync(11) ? reader.GetString(11) : string.Empty; show.TvdbId = !await reader.IsDBNullAsync(12) ? reader.GetString(12) : string.Empty; show.Genres = !await reader.IsDBNullAsync(13) ? reader.GetString(13) : string.Empty; } if (string.IsNullOrEmpty(show.ImdbId)) { return(BadRequest()); } var json = JsonSerializer.ToJsonString(show, StandardResolver.SnakeCase); await _cachingService.SetCache(hash, json, TimeSpan.FromDays(1)); return(Content(json, "application/json")); } }
public async Task <IActionResult> Get([RequiredFromQuery] int page, [FromQuery] int limit, [FromQuery] int minimum_rating, [FromQuery] string query_term, [FromQuery] string genre, [FromQuery] string sort_by) { var nbShowsPerPage = 20; if (limit >= 20 && limit <= 50) { nbShowsPerPage = limit; } var currentPage = 1; if (page >= 1) { currentPage = page; } var queryTerm = string.Empty; if (!string.IsNullOrWhiteSpace(query_term)) { queryTerm = query_term; } var genreFilter = string.Empty; if (!string.IsNullOrWhiteSpace(genre)) { genreFilter = genre; } var hash = Convert.ToBase64String( Encoding.UTF8.GetBytes( $@"type=shows&page={page}&limit={limit}&minimum_rating={minimum_rating}&query_term={ query_term }&genre={genre}&sort_by={sort_by}")); try { var cachedShows = await _cachingService.GetCache(hash); if (cachedShows != null) { try { return(Content(cachedShows, "application/json")); } catch (Exception ex) { _loggingService.Telemetry.TrackException(ex); } } } catch (Exception ex) { _loggingService.Telemetry.TrackException(ex); } using (var context = new PopcornContextFactory().CreateDbContext(new string[0])) { var skipParameter = new SqlParameter("@skip", (currentPage - 1) * nbShowsPerPage); var takeParameter = new SqlParameter("@take", nbShowsPerPage); var ratingParameter = new SqlParameter("@rating", minimum_rating); var queryParameter = new SqlParameter("@Keywords", string.Format(@"""{0}""", queryTerm)); var genreParameter = new SqlParameter("@genre", genreFilter); var query = @" SELECT Show.Title, Show.Year, Rating.Percentage, Rating.Loved, Rating.Votes, Rating.Hated, Rating.Watching, Show.LastUpdated, Image.Banner, Image.Poster, Show.ImdbId, Show.TvdbId, Show.GenreNames, COUNT(*) OVER () as TotalCount FROM ShowSet AS Show INNER JOIN ImageShowSet AS Image ON Image.Id = Show.ImagesId INNER JOIN RatingSet AS Rating ON Rating.Id = Show.RatingId WHERE Show.NumSeasons <> 0"; if (minimum_rating > 0 && minimum_rating < 10) { query += @" AND Rating >= @rating"; } if (!string.IsNullOrWhiteSpace(query_term)) { query += @" AND (CONTAINS(Title, @Keywords) OR CONTAINS(ImdbId, @Keywords) OR CONTAINS(TvdbId, @Keywords))"; } if (!string.IsNullOrWhiteSpace(genre)) { query += @" AND CONTAINS(GenreNames, @genre)"; } if (!string.IsNullOrWhiteSpace(sort_by)) { switch (sort_by) { case "title": query += " ORDER BY Show.Title ASC"; break; case "year": query += " ORDER BY Show.Year DESC"; break; case "rating": query += " ORDER BY Rating.Percentage DESC"; break; case "loved": query += " ORDER BY Rating.Loved DESC"; break; case "votes": query += " ORDER BY Rating.Votes DESC"; break; case "watching": query += " ORDER BY Rating.Watching DESC"; break; case "date_added": query += " ORDER BY Show.LastUpdated DESC"; break; default: query += " ORDER BY Show.LastUpdated DESC"; break; } } else { query += " ORDER BY Show.LastUpdated DESC"; } query += @" OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY"; var showsQuery = await context.Database.ExecuteSqlQueryAsync(query, new CancellationToken(), skipParameter, takeParameter, ratingParameter, queryParameter, genreParameter); var reader = showsQuery.DbDataReader; var count = 0; var shows = new List <ShowLightJson>(); while (await reader.ReadAsync()) { var show = new ShowLightJson { Title = !await reader.IsDBNullAsync(0) ? reader.GetString(0) : string.Empty, Year = !await reader.IsDBNullAsync(1) ? reader.GetInt32(1) : 0, Rating = new RatingJson { Percentage = !await reader.IsDBNullAsync(2) ? reader.GetInt32(2) : 0, Loved = !await reader.IsDBNullAsync(3) ? reader.GetInt32(3) : 0, Votes = !await reader.IsDBNullAsync(4) ? reader.GetInt32(4) : 0, Hated = !await reader.IsDBNullAsync(5) ? reader.GetInt32(5) : 0, Watching = !await reader.IsDBNullAsync(6) ? reader.GetInt32(6) : 0 }, Images = new ImageShowJson { Banner = !await reader.IsDBNullAsync(8) ? reader.GetString(8) : string.Empty, Poster = !await reader.IsDBNullAsync(9) ? reader.GetString(9) : string.Empty, }, ImdbId = !await reader.IsDBNullAsync(10) ? reader.GetString(10) : string.Empty, TvdbId = !await reader.IsDBNullAsync(11) ? reader.GetString(11) : string.Empty, Genres = !await reader.IsDBNullAsync(12) ? reader.GetString(12) : string.Empty }; shows.Add(show); count = !await reader.IsDBNullAsync(13) ? reader.GetInt32(13) : 0; } var response = new ShowLightResponse { TotalShows = count, Shows = shows }; var json = JsonSerializer.ToJsonString(response, StandardResolver.SnakeCase); await _cachingService.SetCache(hash, json, TimeSpan.FromDays(1)); return(Content(json, "application/json")); } }
public async Task <IActionResult> GetShowByIds([FromBody] IEnumerable <string> imdbIds) { if (!imdbIds.Any()) { return(Json(new ShowLightResponse { Shows = new List <ShowLightJson>(), TotalShows = 0 })); } var hash = Convert.ToBase64String( Encoding.UTF8.GetBytes( $@"type=shows&imdbIds={string.Join(',', imdbIds)}")); try { var cachedShow = await _cachingService.GetCache(hash); if (cachedShow != null) { try { return(Content(cachedShow, "application/json")); } catch (Exception ex) { _loggingService.Telemetry.TrackException(ex); } } } catch (Exception ex) { _loggingService.Telemetry.TrackException(ex); } using (var context = new PopcornContextFactory().CreateDbContext(new string[0])) { var query = @" SELECT DISTINCT Show.Title, Show.Year, Rating.Percentage, Rating.Loved, Rating.Votes, Rating.Hated, Rating.Watching, Show.LastUpdated, Image.Banner, Image.Poster, Show.ImdbId, Show.TvdbId, Show.GenreNames, COUNT(*) OVER () as TotalCount FROM ShowSet AS Show INNER JOIN ImageShowSet AS Image ON Image.Id = Show.ImagesId INNER JOIN RatingSet AS Rating ON Rating.Id = Show.RatingId WHERE Show.ImdbId IN ({@imdbIds})"; using (var cmd = new SqlCommand(query, new SqlConnection(context.Database.GetDbConnection().ConnectionString))) { cmd.AddArrayParameters(imdbIds, "@imdbIds"); await cmd.Connection.OpenAsync(); var reader = await cmd.ExecuteReaderAsync(new CancellationToken()); var count = 0; var shows = new List <ShowLightJson>(); while (await reader.ReadAsync()) { var show = new ShowLightJson { Title = !await reader.IsDBNullAsync(0) ? reader.GetString(0) : string.Empty, Year = !await reader.IsDBNullAsync(1) ? reader.GetInt32(1) : 0, Rating = new RatingJson { Percentage = !await reader.IsDBNullAsync(2) ? reader.GetInt32(2) : 0, Loved = !await reader.IsDBNullAsync(3) ? reader.GetInt32(3) : 0, Votes = !await reader.IsDBNullAsync(4) ? reader.GetInt32(4) : 0, Hated = !await reader.IsDBNullAsync(5) ? reader.GetInt32(5) : 0, Watching = !await reader.IsDBNullAsync(6) ? reader.GetInt32(6) : 0 }, Images = new ImageShowJson { Banner = !await reader.IsDBNullAsync(8) ? reader.GetString(8) : string.Empty, Poster = !await reader.IsDBNullAsync(9) ? reader.GetString(9) : string.Empty, }, ImdbId = !await reader.IsDBNullAsync(10) ? reader.GetString(10) : string.Empty, TvdbId = !await reader.IsDBNullAsync(11) ? reader.GetString(11) : string.Empty, Genres = !await reader.IsDBNullAsync(12) ? reader.GetString(12) : string.Empty }; shows.Add(show); count = !await reader.IsDBNullAsync(13) ? reader.GetInt32(13) : 0; } var response = new ShowLightResponse { TotalShows = count, Shows = shows }; var json = JsonSerializer.ToJsonString(response, StandardResolver.SnakeCase); await _cachingService.SetCache(hash, json, TimeSpan.FromDays(1)); return(Content(json, "application /json")); } } }