/// <summary>
/// Lazy Loads the text for each Control in form
/// </summary>
/// <param name="ControlDefaultValues">A dictionary with ControlName-Text pairs</param>
public static Thread LazyLoadTranslations(FinOrgForm f)
{
try
{
Thread t = new Thread(() =>
{
// Get a list of items to fetch
IEnumerable <string> items = f.ControlDefaultValues.Values;
if (Translations != null)
{
items = items.Except(Translations.Keys).ToArray();
}
SqlConnection con = FinOrgForm.getSqlConnection();
try
{
con.Open();
SqlCommand cmd = new SqlCommand(string.Format("SELECT text, {0} FROM TRANSLATIONS WHERE text IN ({{keys}});", currentLanguage), con);
cmd.AddArrayParameters(items, "keys");
if (cmd.Parameters.Count > 0)
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
// Only Keys NOT Present in Translations Dictionary is fetched from Database
if (!Translations.ContainsKey(reader["text"].ToString()))
{
Translations.Add(reader["text"].ToString(), reader[currentLanguage].ToString());
}
}
}
}
con.Close();
}
catch (Exception e)
{
con.Close();
MessageBox.Show(e.Message, "FinOrg Languages LazyLoadTranslations");
}
if (LANG_DEBUG_MODE) // Copies the value from form to database currentLanguage field
{
InsertFormTranslations(f.ControlDefaultValues);
}
// Apply Translation
f.BeginInvoke(new Action(() =>
{
ApplyTranslation(f);
}));
});
// finish this fast
t.Priority = ThreadPriority.Highest;
t.Start();
return(t);
}
catch (Exception ex)
{
return(null);
}
}
public void AtualizarTelefones(ClienteCorrecaoDTO cliente)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con.Conectar();
cmd.CommandText = @"DELETE FROM Telefone WHERE id_cliente = @idCliente AND id_telefone NOT IN (@idsTelefones)";
cmd.Parameters.AddWithValue("@idCliente", cliente.IdCliente);
var idsTelefones = new List <int>();
foreach (var telefone in cliente.NumeroTelefone)
{
idsTelefones.Add(telefone.IdTelefone);
}
cmd.AddArrayParameters("@idsTelefones", idsTelefones);
cmd.ExecuteNonQuery();
con.Desconectar();
}
catch (Exception e)
{
throw e;
}
}
public IEnumerable <ClienteListagemDTO> ListarClientes(int[] idsStatus, int idUsuario)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con.Conectar();
// Query pra trazer somente clientes que e posso mexer como usuário
// cmd.CommandText = @"SELECT * FROM Cliente c
// WHERE id_status IN (@idsStatus) AND
// NOT EXISTS(SELECT * FROM Analise a
// WHERE c.id_cliente = a.id_cliente AND
// (id_usuario = @idUsuario AND (c.id_status <> 7 AND c.id_status <> 1)))";
// cmd.Parameters.AddWithValue("@idUsuario", idUsuario);
// Query que eu trago todos usuários, mas não vou permirir mexer em alguns caso eu seja o ultimo que mexeu nele
cmd.CommandText = @"SELECT * FROM Cliente cli
INNER JOIN Endereco e ON (e.id_cliente = cli.id_cliente)
INNER JOIN Cidade c ON (c.id_cidade = e.id_cidade)
INNER JOIN Estado es ON (es.id_estado = c.id_estado)
INNER JOIN Pais p ON (p.id_pais = es.id_pais)
INNER JOIN Analise a ON (a.id_cliente = cli.id_cliente)
WHERE cli.id_status IN (@idsStatus) AND a.id_analise IN (SELECT MAX(a.id_analise) FROM Analise a GROUP BY(a.id_cliente))";
cmd.AddArrayParameters("@idsStatus", idsStatus);
SqlDataReader dr = cmd.ExecuteReader();
List <ClienteListagemDTO> listaCliente = new List <ClienteListagemDTO>();
while (dr.Read())
{
ClienteListagemDTO cliente = new ClienteListagemDTO();
cliente.Id = Convert.ToInt32(dr["id_cliente"]);
cliente.Nome = dr["nome"].ToString();
cliente.CPF = dr["cpf"].ToString();
cliente.RG = dr["rg"].ToString();
cliente.DataNascimento = Convert.ToDateTime(dr["data_nascimento"]);
cliente.Email = dr["email"].ToString();
cliente.IdStatus = Convert.ToInt32(dr["id_status"]);
cliente.UsuarioPermitido = Convert.ToInt32(dr["id_usuario"]) != idUsuario ||
Convert.ToInt32(dr["id_status"]) == (int)EStatus.Cadastrado ? true : false;
cliente.NomePais = dr["nome_pais"].ToString();
listaCliente.Add(cliente);
}
con.Desconectar();
return(listaCliente);
}
catch (Exception e)
{
throw e;
}
}
private void deletePropertiesForPicture(IList <Guid> Ids)
{
if (Ids.Count == 0)
{
Log.Debug("No properties for picture to delete... skipping");
return;
}
string query = "delete from PropertyData WHERE Id in ({@id})";
SqlCommand cmd = getCommand(query);
cmd.AddArrayParameters("@id", Ids);
cmd.ExecuteNonQuery();
}
private IList <Property> GetPropertiesByIds(IList <Guid> propIds)
{
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
IList <Property> properties = new List <Property>();
if (propIds.Count == 0)
{
return(properties);
}
string query = "SELECT * from PropertyData WHERE Id IN ({@propIds})";
SqlCommand cmd = getCommand(query);
cmd.AddArrayParameters("@propIds", propIds);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Property tempProp = new Property();
tempProp.ID = reader.GetGuid(0);
tempProp.Name = reader.GetString(1);
tempProp.TagNumber = reader.GetInt32(2);
if (!reader.IsDBNull(3))
{
tempProp.Value = reader.GetString(3);
}
else
{
tempProp.Value = "";
}
tempProp.Comment = reader.GetString(4);
properties.Add(tempProp);
}
}
return(properties);
}
private void CreatePrincipals(User user)
{
var principalQuery = "insert into Principals(UserId, EntityId, DomainId, ContextName, CanIgnoreContextFilters) select @UserId, 1, DomainID,'DistributorContext',@canIgnoreContextFilters from domain where DomainName in (@domainString)";
try
{
string conString = "data source=" + serverName + ";initial catalog=" + dbName + ";integrated security=SSPI;persist security info=false;";
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
using (SqlCommand setPrincipalCmd = new SqlCommand(principalQuery, con))
{
setPrincipalCmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = user.UserId;
setPrincipalCmd.Parameters.Add("@canIgnoreContextFilters", SqlDbType.Bit).Value = user.CanIgnoreContextFilters ? 1 : 0;
setPrincipalCmd.AddArrayParameters("domainString", user.Domains);
int x = setPrincipalCmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "User Principal Creation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void MapFunctionalTiles(User user)
{
var mapFunctionalTilesQuery = "insert into UserFunctionalTypeMap select @UserId as UserId,ReportFunctionalTypeId, @CanEditTileReports AS IsEdit from ReportFunctionalTypes where ReportFunctionalType in (@ReportFunctionalTypes)";
try
{
string conString = "data source=" + serverName + ";initial catalog=" + dbName + ";integrated security=SSPI;persist security info=false;";
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
using (SqlCommand mapFunctionalTilesCmd = new SqlCommand(mapFunctionalTilesQuery, con))
{
mapFunctionalTilesCmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = user.UserId;
mapFunctionalTilesCmd.Parameters.Add("@CanEditTileReports", SqlDbType.VarChar).Value = user.CanEditTileReports;
mapFunctionalTilesCmd.AddArrayParameters("ReportFunctionalTypes", user.FunctionalTiles);
int x = mapFunctionalTilesCmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Map Functional Tiles Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
void AddParamaters(SqlCommand command, IDictionary<string, object> parameters)
{
if (parameters != null)
{
foreach (KeyValuePair<string, object> parameter in parameters)
{
var objects = parameter.Value as IEnumerable<IDictionary<string, object>>;
if (objects != null)
{
command.AddObjectsParamaters(objects, parameter.Key);
continue;
}
var values = parameter.Value as IEnumerable<object>;
if (values != null)
{
command.AddArrayParameters(values, parameter.Key);
continue;
}
command.Parameters.AddWithValue(parameter.Key, parameter.Value ?? DBNull.Value);
}
}
}
public async Task <IActionResult> GetSimilar([FromBody] IEnumerable <string> imdbIds, [RequiredFromQuery] int page, [FromQuery] int limit,
[FromQuery] int minimum_rating, [FromQuery] string query_term,
[FromQuery] string genre, [FromQuery] string sort_by)
{
var nbMoviesPerPage = 20;
if (limit >= 20 && limit <= 50)
{
nbMoviesPerPage = 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;
}
if (!imdbIds.Any())
{
return(Json(new MovieLightResponse
{
Movies = new List <MovieLightJson>(),
TotalMovies = 0
}));
}
var hash = Convert.ToBase64String(
Encoding.UTF8.GetBytes(
$@"type=movies&similar&imdbIds={string.Join(',', imdbIds)}&page={page}&limit={limit}&minimum_rating={minimum_rating}&query_term={
query_term
}&genre={genre}&sort_by={sort_by}"));
try
{
var cachedMovies = await _cachingService.GetCache(hash);
if (cachedMovies != null)
{
try
{
return(Content(cachedMovies, "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) * nbMoviesPerPage);
var takeParameter = new SqlParameter("@take", nbMoviesPerPage);
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 DISTINCT
Movie.Title, Movie.Year, Movie.Rating, Movie.PosterImage, Movie.ImdbCode, Movie.GenreNames, COUNT(*) OVER () as TotalCount
FROM
MovieSet AS Movie
WHERE
Movie.ImdbCode IN (SELECT
Similar.TmdbId
FROM
Similar AS Similar
INNER JOIN
(
SELECT Movie.ID
FROM
MovieSet AS Movie
WHERE
Movie.ImdbCode IN ({@imdbIds})
) Movie
ON Similar.MovieId = Movie.Id)
AND 1 = 1";
if (minimum_rating > 0 && minimum_rating < 10)
{
query += @" AND
Rating >= @rating";
}
if (!string.IsNullOrWhiteSpace(query_term))
{
query += @" AND
(CONTAINS(Movie.Title, @Keywords) OR CONTAINS(Movie.ImdbCode, @Keywords))";
}
if (!string.IsNullOrWhiteSpace(genre))
{
query += @" AND
CONTAINS(Movie.GenreNames, @genre)";
}
query += " ORDER BY Movie.Rating DESC";
query += @" OFFSET @skip ROWS
FETCH NEXT @take ROWS ONLY";
using (var cmd = new SqlCommand(query,
new SqlConnection(context.Database.GetDbConnection().ConnectionString)))
{
cmd.AddArrayParameters(imdbIds, "@imdbIds");
cmd.Parameters.Add(skipParameter);
cmd.Parameters.Add(takeParameter);
cmd.Parameters.Add(ratingParameter);
cmd.Parameters.Add(queryParameter);
cmd.Parameters.Add(genreParameter);
await cmd.Connection.OpenAsync();
var reader = await cmd.ExecuteReaderAsync(new CancellationToken());
var count = 0;
var movies = new List <MovieLightJson>();
while (await reader.ReadAsync())
{
var movie = new MovieLightJson
{
Title = !await reader.IsDBNullAsync(0) ? reader.GetString(0) : string.Empty,
Year = !await reader.IsDBNullAsync(1) ? reader.GetInt32(1) : 0,
Rating = !await reader.IsDBNullAsync(2) ? reader.GetDouble(2) : 0d,
PosterImage = !await reader.IsDBNullAsync(3) ? reader.GetString(3) : string.Empty,
ImdbCode = !await reader.IsDBNullAsync(4) ? reader.GetString(4) : string.Empty,
Genres = !await reader.IsDBNullAsync(5) ? reader.GetString(5) : string.Empty
};
movies.Add(movie);
count = !await reader.IsDBNullAsync(6) ? reader.GetInt32(6) : 0;
}
var response = new MovieLightResponse
{
TotalMovies = count,
Movies = movies
};
var json = JsonSerializer.ToJsonString(response, StandardResolver.SnakeCase);
await _cachingService.SetCache(hash, json, TimeSpan.FromDays(1));
return(Content(json, "application/json"));
}
}
}
public async Task <IActionResult> GetMoviesByIds([FromBody] IEnumerable <string> imdbIds)
{
if (!imdbIds.Any())
{
return(Json(new MovieLightResponse
{
Movies = new List <MovieLightJson>(),
TotalMovies = 0
}));
}
var hash = Convert.ToBase64String(
Encoding.UTF8.GetBytes(
$@"type=movies&imdbIds={string.Join(',', imdbIds)}"));
try
{
var cachedMovies = await _cachingService.GetCache(hash);
if (cachedMovies != null)
{
try
{
return(Content(cachedMovies, "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
Movie.Title, Movie.Year, Movie.Rating, Movie.PosterImage, Movie.ImdbCode, Movie.GenreNames, COUNT(*) OVER () as TotalCount
FROM
MovieSet AS Movie
WHERE
Movie.ImdbCode IN ({@imdbIds})
ORDER BY Movie.Rating DESC";
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 movies = new List <MovieLightJson>();
while (await reader.ReadAsync())
{
var movie = new MovieLightJson
{
Title = !await reader.IsDBNullAsync(0) ? reader.GetString(0) : string.Empty,
Year = !await reader.IsDBNullAsync(1) ? reader.GetInt32(1) : 0,
Rating = !await reader.IsDBNullAsync(2) ? reader.GetDouble(2) : 0d,
PosterImage = !await reader.IsDBNullAsync(3) ? reader.GetString(3) : string.Empty,
ImdbCode = !await reader.IsDBNullAsync(4) ? reader.GetString(4) : string.Empty,
Genres = !await reader.IsDBNullAsync(5) ? reader.GetString(5) : string.Empty
};
movies.Add(movie);
count = !await reader.IsDBNullAsync(6) ? reader.GetInt32(6) : 0;
}
var response = new MovieLightResponse
{
TotalMovies = count,
Movies = movies
};
var json = JsonSerializer.ToJsonString(response, StandardResolver.SnakeCase);
await _cachingService.SetCache(hash, json, TimeSpan.FromDays(1));
return(Content(json, "application/json"));
}
}
}
public (List <Books> books, int total) GetBooks(int userId = 0, int filter = 0, int pageNumber = 1, int pageSize = 0, string sortBy = "Title")
{
int startIndex = (pageNumber - 1) * pageSize;
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
string command = @"Select b.*, c.Name as CategoryName, c.Id as CategoryId, t.UserId as UserId, t.TimeOccured as DateRented from
Books b inner join Category c on b.CategoryId = c.Id
full outer join LibraryTransaction t on t.BookId = b.Id and t.Status = 1
where b.Id is not null";
if (filter == 1)
{
command += " and b.BookStatus = 0";
}
if (filter == 2 && userId != 0)
{
command += " and t.UserId = @UserId";
}
command += " order by " + sortBy;
SqlCommand sqlCommand = conn.CreateCommand();
sqlCommand.CommandText = command;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.AddWithValue("UserId", userId);
SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);
DataSet dbSet = new DataSet();
adapter.Fill(dbSet);
DataTable booksTable = dbSet.Tables["Table"];
int total = booksTable.Rows.Count;
if (booksTable != null && booksTable.Rows.Count > 0)
{
var books = booksTable.AsEnumerable().Select(_ => new Books
{
Id = (int)_["Id"],
Title = _["Title"].ToString(),
Subtitle = _["Subtitle"].ToString(),
BookStatus = getBookStatus((int)_["BookStatus"]),
ImageUrl = _["ImageUrl"].ToString(),
CurrentUser = new User {
UserId = _["UserId"] == DBNull.Value ? 0 : (int)_["UserId"]
},
DateRented = checkDate(_.IsNull("DateRented"), _["DateRented"]),
Category = new Category {
Id = (int)_["CategoryId"], Name = _["CategoryName"].ToString()
}
}).ToList();
//Get the Authors
List <int> Ids = booksTable.AsEnumerable().Select(_ => (int)_["Id"]).ToList();
string nextCommand = @"Select a.*, p.BookId from Author a inner join Publishes p on a.id = p.AuthorId where p.BookId in ({BookIds})";
sqlCommand.CommandText = nextCommand;
sqlCommand.AddArrayParameters("BookIds", Ids);
SqlDataAdapter nexAdapter = new SqlDataAdapter(sqlCommand);
dbSet = new DataSet();
nexAdapter.Fill(dbSet);
DataTable authorTable = dbSet.Tables["Table"];
if (authorTable != null && authorTable.Rows.Count > 0)
{
var authors = authorTable.AsEnumerable().Select(_ =>
{
return((int)_["BookId"], new Author
{
Name = _["Name"].ToString(),
Id = (int)_["Id"],
ImageUrl = _["ImageUrl"].ToString()
});
}).ToList();
foreach (Books book in books)
{
book.Authors = authors.Where(_ => _.Item1 == book.Id)
.Select(_ => _.Item2).ToList();
}
}
return(books, total);
}
else
{
return(new List <Books>(), 0);
}
}
}
public async Task <IActionResult> GetSimilar([FromBody] IEnumerable <string> imdbIds, [RequiredFromQuery] int page, [FromQuery] int limit)
{
if (!imdbIds.Any())
{
return(Json(new MovieLightResponse
{
Movies = new List <MovieLightJson>(),
TotalMovies = 0
}));
}
var nbMoviesPerPage = 20;
if (limit >= 20 && limit <= 50)
{
nbMoviesPerPage = limit;
}
var currentPage = 1;
if (page >= 1)
{
currentPage = page;
}
var hash = Convert.ToBase64String(
Encoding.UTF8.GetBytes(
$@"type=movies&page={page}&limit={limit}&imdbId={string.Join(',', imdbIds)}"));
try
{
var cachedMovies = await _cachingService.GetCache(hash);
if (cachedMovies != null)
{
try
{
return(Json(JsonConvert.DeserializeObject <MovieLightResponse>(cachedMovies)));
}
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) * nbMoviesPerPage);
var takeParameter = new SqlParameter("@take", nbMoviesPerPage);
var query = @"
SELECT DISTINCT
Movie.Title, Movie.Year, Movie.Rating, Movie.PosterImage, Movie.ImdbCode, Movie.GenreNames, Torrent.Peers, Torrent.Seeds, COUNT(*) OVER () as TotalCount
FROM
MovieSet AS Movie
INNER JOIN
TorrentMovieSet AS Torrent
ON
Torrent.MovieId = Movie.Id
INNER JOIN
Similar
ON
Similar.MovieId = Movie.Id
AND
Torrent.Quality = '720p'
WHERE
Similar.TmdbId IN ({@imdbIds})
ORDER BY Movie.Rating DESC";
query += @" OFFSET @skip ROWS
FETCH NEXT @take ROWS ONLY";
using (var cmd = new SqlCommand(query, new SqlConnection(context.Database.GetDbConnection().ConnectionString)))
{
cmd.AddArrayParameters(imdbIds, "@imdbIds");
cmd.Parameters.Add(skipParameter);
cmd.Parameters.Add(takeParameter);
await cmd.Connection.OpenAsync();
var reader = await cmd.ExecuteReaderAsync(new CancellationToken());
var count = 0;
var movies = new List <MovieLightJson>();
while (await reader.ReadAsync())
{
var movie = new MovieLightJson
{
Title = reader[0].GetType() != typeof(DBNull) ? (string)reader[0] : string.Empty,
Year = reader[1].GetType() != typeof(DBNull) ? (int)reader[1] : 0,
Rating = reader[2].GetType() != typeof(DBNull) ? (double)reader[2] : 0d,
PosterImage = reader[3].GetType() != typeof(DBNull) ? (string)reader[3] : string.Empty,
ImdbCode = reader[4].GetType() != typeof(DBNull) ? (string)reader[4] : string.Empty,
Genres = reader[5].GetType() != typeof(DBNull) ? (string)reader[5] : string.Empty
};
movies.Add(movie);
count = reader[8].GetType() != typeof(DBNull) ? (int)reader[8] : 0;
}
var response = new MovieLightResponse
{
TotalMovies = count,
Movies = movies
};
await _cachingService.SetCache(hash, JsonConvert.SerializeObject(response), TimeSpan.FromDays(1));
return
(Json(response));
}
}
}
private static void AddInClause <T>(Tuple <string, IEnumerable <T> > inClause, SqlCommand command)
{
command.AddArrayParameters(inClause.Item1, inClause.Item2);
}
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"));
}
}
}
