/// <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"));
}
}
}
