public static async Task <bool> insertUserAsync(string uName, string uEmail, string uPass)
{
moviesProjectContext context = new moviesProjectContext();
bool flag = true;
try
{
//query = "INSERT INTO `users` (`userEmail`, `userName` , `userPassword`) VALUES('" + uEmail + "', '" + uName + "', '" + uPass + "')";
await context.Users.AddAsync(new User()
{
UserEmail = uEmail,
UserName = uName,
UserPassword = uPass,
IsAdmin = false
});
await context.SaveChangesAsync();
}
catch (Exception e)
{
flag = false;
}
return(flag);
}
public static async Task <List <MovieFirebase> > GetMoviesAsync(String uEmail)
{
moviesProjectContext context = new moviesProjectContext();
try
{
List <MovieFirebase> MovieList = new List <MovieFirebase>();
//query = "SELECT movieId FROM watchlist WHERE userEmail='" + uEmail + "'";
var movies = context.Watchlists.AsQueryable().Where(x => x.UserEmail.Equals(uEmail)).ToList();
foreach (Watchlist item in movies)
{
MovieFirebase movie = new MovieFirebase();
movie = await MovieMethods.GetMovie(item.MovieId);
MovieList.Add(movie);
}
return(MovieList);
}
catch (Exception e)
{
return(null);
}
}
public static async Task <bool> insertRatingAsync(int movieId, string userEmail, string commentContent, bool like)
{
bool flag = true;
try
{
//String query = "REPLACE INTO ratings VALUES (0," + movieId + ",'" + userEmail+"','"+commentContent+"',"+like+")";
moviesProjectContext context = new moviesProjectContext();
if (await context.Ratings.AsQueryable().AnyAsync(x => x.UserEmail == userEmail && x.MovieId == movieId))
{
var item = await context.Ratings.AsQueryable().SingleAsync(x => x.UserEmail == userEmail && x.MovieId == movieId);
item.Like = like;
item.CommentContent = commentContent;
}
else
{
await context.Ratings.AddAsync(new Rating()
{
UserEmail = userEmail,
MovieId = movieId,
CommentContent = commentContent,
Like = like
});
}
await context.SaveChangesAsync();
}
catch (Exception ex)
{
flag = false;
}
return(flag);
}
public static async Task <Dictionary <string, List <Rating> > > getMovieRatingsAsync(int MovieId, int page, string userEmail)
{
moviesProjectContext context = new moviesProjectContext();
List <Rating> Rlist = new List <Rating>();
List <Rating> userRating = new List <Rating>();
Dictionary <string, List <Rating> > dictionary = new Dictionary <string, List <Rating> >();
Rating rating = new Rating();
try
{
if (userEmail != "" && page == 0)
{
//query = "SELECT * FROM ratings WHERE movieId='" + MovieId + "' AND userEmail='" + userEmail + "'";
rating = await context.Ratings.AsQueryable().SingleOrDefaultAsync(x => x.UserEmail == userEmail && x.MovieId == MovieId);
userRating.Add(rating);
dictionary.Add("UserRating", userRating);
}
//query = "SELECT * FROM ratings WHERE movieId='" + MovieId + "' AND userEmail != '" + userEmail + "' LIMIT " + page*10+",10";
Rlist = context.Ratings.AsQueryable().Where(x => x.MovieId == MovieId && x.UserEmail != userEmail).Skip(page * 10).Take(10).ToList();
dictionary.Add("Ratings", Rlist);
}
catch (Exception e)
{
return(null);
}
return(dictionary);
}
public static async Task <Dictionary <string, decimal> > getMovieAverageAsync(int MovieId)
{
decimal like = 0, dislike = 0, percentage = 0;
moviesProjectContext context = new moviesProjectContext();
Dictionary <string, decimal> dictionary = new Dictionary <string, decimal>();
try
{
//String query = "Select sum(case when `like` = 1 then 1 else 0 end) AS Truecount,sum(case when `like` = 0 then 1 else 0 end) AS Falsecount FROM ratings WHERE movieId=" + MovieId + "";
like = context.Ratings.AsQueryable().Where(x => x.MovieId == MovieId && x.Like == true).Count();
dislike = context.Ratings.AsQueryable().Where(x => x.MovieId == MovieId && x.Like == false).Count();
}
catch (Exception e)
{
percentage = -1;
}
if (like > 0 || dislike > 0)
{
percentage = (like / (like + dislike)) * 100;
}
dictionary.Add("likes", like);
dictionary.Add("dislikes", dislike);
dictionary.Add("percentage", percentage);
return(dictionary);
}
public static async Task <Rating> getMovieSingleRatingAsync(int MovieId, string userEmail)
{
moviesProjectContext context = new moviesProjectContext();
Rating rating = new Rating();
//String query = "SELECT * FROM ratings WHERE movieId='" + MovieId + "' AND userEmail='" + userEmail + "'";
rating = await context.Ratings.AsQueryable().SingleOrDefaultAsync(x => x.UserEmail == userEmail && x.MovieId == MovieId);
return(rating);
}
public static async Task <User> getUser(String Email)
{
moviesProjectContext context = new moviesProjectContext();
User user = null;
try
{
// query = "SELECT * FROM users WHERE userEmail='" + Email + "'";
user = await context.Users.AsQueryable().SingleOrDefaultAsync(x => x.UserEmail == Email);
}
catch (Exception e)
{
user = null;
}
return(user);
}
public static bool authUser(String Email, String Pass)
{
moviesProjectContext context = new moviesProjectContext();
bool flag = false;
try
{
//query = "SELECT * FROM users WHERE userEmail='"+Email+"' AND userPassword='******'";
if (context.Users.Any(x => x.UserEmail == Email && x.UserPassword == Pass))
{
flag = true;
}
}catch (Exception ex)
{
flag = false;
}
return(flag);
}
public static async Task <bool> IsInList(String Email, int movieId)
{
moviesProjectContext context = new moviesProjectContext();
bool Flag = false;
try
{
//query = "SELECT movieId FROM watchlist WHERE userEmail='" + Email + "' AND movieId='"+movieId+"'";
if (await context.Watchlists.AsQueryable().AnyAsync(x => x.UserEmail == Email && x.MovieId == movieId))
{
return(true);
}
}
catch (Exception e)
{
Flag = true;
}
return(Flag);
}
public static async Task <bool> removeFromWLAsync(string uEmail, int MovieId)
{
moviesProjectContext context = new moviesProjectContext();
try
{
List <Movie> MovieList = new List <Movie>();
// query = "DELETE FROM `watchlist` WHERE userEmail='" + uEmail + "' AND movieId=" + MovieId + "";
var itemToRemove = await context.Watchlists.AsQueryable().SingleOrDefaultAsync(x => x.MovieId == MovieId && x.UserEmail.ToUpper() == uEmail.ToUpper()); //returns a single item.
if (itemToRemove != null)
{
context.Watchlists.Remove(itemToRemove);
await context.SaveChangesAsync();
}
}
catch (Exception ex)
{
return(false);
}
return(true);
}
public static async Task <bool> insertInWLAsync(string uEmail, int MovieId)
{
moviesProjectContext context = new moviesProjectContext();
try
{
//query = "INSERT INTO `watchlist` (`userEmail`, `movieId` ) VALUES('" + uEmail + "', " + MovieId + ")";
await context.Watchlists.AddAsync(new Watchlist()
{
UserEmail = uEmail,
MovieId = MovieId
});
await context.SaveChangesAsync();
}
catch (Exception ex)
{
return(false);
}
return(true);
}
public static async Task <bool> ChangePass(string uEmail, string uPass, string newPass)
{
moviesProjectContext context = new moviesProjectContext();
bool flag = false;
try
{
if (await context.Users.AsQueryable().AnyAsync(x => x.UserEmail == uEmail && x.UserPassword == uPass))
{
var item = await context.Users.AsQueryable().SingleAsync(x => x.UserEmail == uEmail && x.UserPassword == uPass);
item.UserPassword = newPass;
flag = true;
}
await context.SaveChangesAsync();
}
catch (Exception e)
{
flag = false;
}
return(flag);
}
public static async Task <bool> deleteRatingAsync(int movieId, string userEmail)
{
moviesProjectContext context = new moviesProjectContext();
bool flag = true;
try
{
//String query = "DELETE FROM RATINGS WHERE movieId = '" + movieId + "' AND userEmail = '"+userEmail+"'";
var itemToRemove = await context.Ratings.AsQueryable().SingleOrDefaultAsync(x => x.MovieId == movieId && x.UserEmail == userEmail); //returns a single item.
if (itemToRemove != null)
{
context.Ratings.Remove(itemToRemove);
await context.SaveChangesAsync();
}
}
catch (Exception e)
{
flag = false;
}
return(flag);
}
