public ActionResult GetMovieInformation(string title)
{
List <FullMovie> moviesForTitle = new List <FullMovie>();
NpgsqlConnection conn = OpenConnection();
string sql_movie = string.Format("SELECT idmovies as Id, title, year FROM movies where title like \'% {0} %\' ", title);
var movieInfo = conn.Query <MovieInfo>(sql_movie).ToList();
foreach (MovieInfo movie in movieInfo)
{
string sql_genres = string.Format("SELECT g.idgenres as Id, g.genre as Genre from public.genres g where g.idgenres in (select mg.idgenres from public.movies_genres mg where mg.idmovies = {0})", movie.Id);
var genreInfo = conn.Query <GenreInfo>(sql_genres).ToList();
string sql_keywords = string.Format(@"select k.idkeywords as Id, k.keyword from public.keywords k
where k.idkeywords in (select mk.idkeywords from public.movies_keywords mk where mk.idmovies = {0})", movie.Id);
var keywordsInfo = conn.Query <KeywordsInfo>(sql_keywords).ToList();
string sql_actors = string.Format(@"select a.idactors as Id, a.fname as FirstName, a.lname as LastName, a.gender, aka.name as CharacterName from public.actors a, public.aka_names aka
where a.idactors in (select ai.idactors from public.acted_in ai where ai.idmovies = {0})
and a.idactors = aka.idactors", movie.Id);
var actorInfo = conn.Query <ActorForMovie>(sql_actors).ToList();
FullMovie fullMovieInformation = new FullMovie()
{
MovieInformation = movie,
Genres = genreInfo,
Keywords = keywordsInfo,
Actors = actorInfo
};
moviesForTitle.Add(fullMovieInformation);
}
JsonResult result = new JsonResult(moviesForTitle);
conn.Close();
return(result);
}
public ActionResult GetMovieInformation(long id)
{
NpgsqlConnection conn = OpenConnection();
string sql_movie = string.Format("SELECT idmovies as Id, title, year FROM movies where idmovies = {0}", id);
var movieInfo = conn.Query <MovieInfo>(sql_movie).ToList().FirstOrDefault();
string sql_genres = string.Format("SELECT g.idgenres as Id, g.genre as Genre from public.genres g where g.idgenres in (select mg.idgenres from public.movies_genres mg where mg.idmovies = {0})", id);
var genreInfo = conn.Query <GenreInfo>(sql_genres).ToList();
string sql_keywords = string.Format(@"select k.idkeywords as Id, k.keyword from public.keywords k
where k.idkeywords in (select mk.idkeywords from public.movies_keywords mk where mk.idmovies = {0})", id);
var keywordsInfo = conn.Query <KeywordsInfo>(sql_keywords).ToList();
string sql_actors = string.Format(@"select a.idactors as Id, a.fname as FirstName, a.lname as LastName, a.gender, aka.name as CharacterName from public.actors a, public.aka_names aka
where a.idactors in (select ai.idactors from public.acted_in ai where ai.idmovies = {0})
and a.idactors = aka.idactors", id);
var actorInfo = conn.Query <ActorForMovie>(sql_actors);
FullMovie fullMovieInformation = new FullMovie()
{
MovieInformation = movieInfo,
Genres = genreInfo,
Keywords = keywordsInfo,
Actors = actorInfo
};
JsonResult result = new JsonResult(fullMovieInformation);
conn.Close();
return(result);
}
public ActionResult GetMovieInformation(long id)
{
// Connect to the demo keyspace on our cluster running at 127.0.0.1
Cluster cluster = Cluster.Builder().AddContactPoint("127.0.0.1").Build();
ISession session = cluster.Connect("imdb");
string query = string.Format("SELECT id,title,year,location,language,actors_name FROM tbl_movies_by_id WHERE id={0}", id);
Row result = session.Execute(query).First();
IEnumerable <ActorForMovie> actorsForMovie = null;
IEnumerable <string> r = result["actors_name"] as IEnumerable <string>;
if (actorsForMovie != null && actorsForMovie.Count() > 0)
{
actorsForMovie = new List <ActorForMovie>();
actorsForMovie = r.Select(a => new ActorForMovie
{
FirstName = a
});
}
FullMovie movie = new FullMovie
{
MovieInformation = new MovieInfo
{
Id = result["id"] == null ? 0 : Int32.Parse(result["id"].ToString()),
Title = result["title"] == null ? null : result["title"].ToString(),
Year = result["year"] == null ? 0 : Int32.Parse(result["year"].ToString())
},
Actors = actorsForMovie
};
JsonResult json_result = new JsonResult(movie);
return(json_result);
}
public ActionResult GetMovieInformation(string[] title)
{
// Connect to the demo keyspace on our cluster running at 127.0.0.1
Cluster cluster = Cluster.Builder().AddContactPoint("127.0.0.1").Build();
ISession session = cluster.Connect("imdb");
string longestWord = title.OrderByDescending(s => s.Length).First();
string query = string.Format("SELECT id,title,year,location,language,actors_name FROM tbl_movies_by_word WHERE word='{0}'", longestWord);
List <Row> result = session.Execute(query).ToList();
List <FullMovie> allMovies = new List <FullMovie>();
foreach (Row row in result)
{
int count = 0;
for (int i = 0; i < title.Length; i++)
{
if (row["title"] != null && row["title"].ToString().ToLower().Contains(title[i].ToLower()))
{
count++;
}
}
if (count == title.Length)
{
IEnumerable <ActorForMovie> actorsForMovie = null;
IEnumerable <string> r = row["actors_name"] as IEnumerable <string>;
if (actorsForMovie != null && actorsForMovie.Count() > 0)
{
actorsForMovie = new List <ActorForMovie>();
actorsForMovie = r.Select(a => new ActorForMovie
{
FirstName = a
});
}
FullMovie movie = new FullMovie
{
MovieInformation = new MovieInfo
{
Id = row["id"] == null ? 0 : Int32.Parse(row["id"].ToString()),
Title = row["title"] == null ? null : row["title"].ToString(),
Year = row["year"] == null ? 0 : Int32.Parse(row["year"].ToString())
},
Actors = actorsForMovie
};
allMovies.Add(movie);
}
}
JsonResult json_result = new JsonResult(allMovies);
return(json_result);
}
public async Task <FullMovie> AddToDBFullMovieDeails(FullMovie fullMovie)
{
using (var trans = _context.Database.BeginTransaction())
{
try
{
//AddMovie
await _context.Movies.AddAsync(fullMovie.movie);
// AddMovieGenres
var movieGenres = new List <MovieGenres>();
foreach (var item in fullMovie.genres_collection)
{
movieGenres.Add(new MovieGenres
{
movie_id = fullMovie.movie.id,
movie_genre_id = _context.Genres.FirstOrDefault(g => g.genre_id == item.id && g.material_type_id == 1).id,
});
}
await _context.MovieGenres.AddRangeAsync(movieGenres);
//AddKeyWords
var KeyWords = new List <KeyWords>();
foreach (var item in fullMovie.keyWords_collection)
{
KeyWords.Add(new KeyWords
{
keyword_id = item.id,
material_type_id = 1,
name = item.name
});
}
await _context.KeyWords.AddRangeAsync(KeyWords);
await _context.SaveChangesAsync();
//AddMovieKeyWords
var movieKeyWords = new List <MovieKeyWords>();
foreach (var item in fullMovie.keyWords_collection)
{
var movieKeyWord = await _context.KeyWords.FirstOrDefaultAsync(k => k.keyword_id == item.id && k.material_type_id == 1);
movieKeyWords.Add(new MovieKeyWords
{
movie_id = fullMovie.movie.id,
movie_ = fullMovie.movie,
movie_keyword_ = movieKeyWord,
movie_keyword_id = movieKeyWord.id
});
}
await _context.MovieKeyWords.AddRangeAsync(movieKeyWords);
//AddMovieCredits
var movieCredits = new List <MovieCredits>();
foreach (var item in fullMovie.casts_collection)
{
movieCredits.Add(new MovieCredits
{
movie_id = fullMovie.movie.id,
credit_id = item.credit_id,
is_cast = true,
movie_ = fullMovie.movie
});
}
foreach (var item in fullMovie.crews_collection)
{
movieCredits.Add(new MovieCredits
{
movie_id = fullMovie.movie.id,
credit_id = item.credit_id,
is_cast = false,
movie_ = fullMovie.movie
});
}
await _context.MovieCredits.AddRangeAsync(movieCredits);
await _context.MovieCasts.AddRangeAsync(fullMovie.casts_collection);
await _context.MovieCrews.AddRangeAsync(fullMovie.crews_collection);
await _context.SaveChangesAsync();
trans.Commit();
return(fullMovie);
}
catch (Exception ex)
{
trans.Rollback();
throw new Exception(ex.Message);
}
finally
{
trans.Dispose();
}
}
}
