//
// GetMovieDetail:
//
// Given a MOVIE ID, returns detailed information about this movie --- all
// the reviews, the total number of reviews, average rating, etc. If the
// movie cannot be found, null is returned.
//
public MovieDetail GetMovieDetail(int MovieID)
{
Movie m = GetMovie(MovieID);
string sql = string.Format(@"SELECT AVG(cast(Rating as float)) FROM Reviews where MovieID = {0};", MovieID);
object average = dataTier.ExecuteScalarQuery(sql);
Double avg = 0;
if (average != DBNull.Value)
{
avg = Convert.ToDouble(average);
}
string sql1 = string.Format(@"SELECT Count(*) FROM Reviews where MovieID = {0};", MovieID);
int noOfR = Convert.ToInt32(dataTier.ExecuteScalarQuery(sql1));
List <Review> reviews = new List <Review>();
string sql2 = string.Format(@"SELECT * FROM Reviews where MovieID = {0} Order by Rating Desc, UserID ASC;", MovieID);
DataSet ds = dataTier.ExecuteNonScalarQuery(sql2);
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
Review review = new Review(Convert.ToInt32(row["ReviewID"]), Convert.ToInt32(row["MovieID"]), Convert.ToInt32(row["UserID"]), Convert.ToInt32(row["Rating"]));
reviews.Add(review);
}
MovieDetail movieDetail = new MovieDetail(m, avg, noOfR, reviews);
return(movieDetail);
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------------
//
// GetMovieDetail: ***
//
// Given a MOVIE ID, returns detailed information about this movie --- all
// the reviews, the total number of reviews, average rating, etc. If the
// movie cannot be found, null is returned.
//
public MovieDetail GetMovieDetail(int MovieID)
{
string sql2 = string.Format(@" Select ReviewID, MovieID, UserID, Rating from Reviews Where MovieID = {0} Order By Rating DESC;", MovieID);//SQL: Retrive Review List with Ratings and User ID
DataSet result2 = dataTier.ExecuteNonScalarQuery(sql2);
List <Review> reviews = new List <Review>();
int totalReviews = 0;
double sumReviews = 0;
foreach (DataTable table2 in result2.Tables)
{
foreach (DataRow row in table2.Rows)
{
Review reviewResult = new Review(Convert.ToInt32(row["ReviewID"]), Convert.ToInt32(row["MovieID"]), Convert.ToInt32(row["UserID"]), Convert.ToInt32(row["Rating"]));
reviews.Add(reviewResult);
sumReviews += Convert.ToInt32(row["Rating"]);
totalReviews++;
}
}
double avgRating = (sumReviews / totalReviews);
double displayRating = Math.Round(avgRating, 1);
MovieDetail m = new MovieDetail(GetMovie(MovieID), displayRating, totalReviews, reviews);
if (reviews == null)
{
return(null);
}
else
{
return(m);
}
}
//
// GetMovieDetail:
//
// Given a MOVIE ID, returns detailed information about this movie --- all
// the reviews, the total number of reviews, average rating, etc. If the
// movie cannot be found, null is returned.
//
public MovieDetail GetMovieDetail(int MovieID)
{
//
// TODO!
//
List <Review> reviews = new List <Review>();
MovieDetail movies = null;
Movie movie = null;
string sql = string.Format(@"SELECT * FROM Reviews INNER JOIN Movies ON Reviews.MovieID = Movies.MovieID
WHERE Reviews.MovieID = '{0}'
ORDER BY Rating DESC, UserID ASC;", MovieID);
DataSet data = dataTier.ExecuteNonScalarQuery(sql);
double rat = 0.0;
int count = 0;
foreach (DataRow row in data.Tables["TABLE"].Rows)
{
movie = new Movie(Convert.ToInt32(row["MovieID"]), Convert.ToString(row["MovieName"]));
Review review = new Review(Convert.ToInt32(row["ReviewID"]), Convert.ToInt32(row["MovieID"]), Convert.ToInt32(row["UserID"]), Convert.ToInt32(row["Rating"]));
reviews.Add(review);
rat = rat + Convert.ToInt32(row["Rating"]);
count++;
movies = new MovieDetail(movie, Math.Round((rat / count), 1), MovieID, reviews);
}
return(movies);
}
//
// GetMovieDetail:
//
// Given a MOVIE ID, returns detailed information about this movie --- all
// the reviews, the total number of reviews, average rating, etc. If the
// movie cannot be found, null is returned.
//
public MovieDetail GetMovieDetail(int MovieID)
{
// Get AVG rating, reviews, num reviews, and movie object
// SQL string to get reviews
string SQL1 = string.Format(@"
SELECT ReviewID, MovieID, UserID, Rating
FROM Reviews
WHERE MovieID = " + MovieID + @"
ORDER BY Rating DESC, UserID ASC");
// SQL string to get movie name
string SQL2 = string.Format(@"
SELECT MovieName
FROM Movies
WHERE MovieID = " + MovieID);
// Execute SQL
DataSet ds = dataTier.ExecuteNonScalarQuery(SQL1);
object result = dataTier.ExecuteScalarQuery(SQL2);
// Loop through table of reviews and count reviews, sum rating
Review r;
int r_ID, m_ID, u_ID, rating;
int num_reviews = 0;
double sum_ratings = 0;
List <Review> reviews = new List <Review>();
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
r_ID = Convert.ToInt32(row["ReviewID"]);
m_ID = Convert.ToInt32(row["MovieID"]);
u_ID = Convert.ToInt32(row["UserID"]);
rating = Convert.ToInt32(row["Rating"]);
num_reviews++;
sum_ratings += rating;
r = new Review(r_ID, m_ID, u_ID, rating);
reviews.Add(r);
}
// Compute Average Rating
double avg_rating = 0.0;
if (num_reviews > 0)
{
avg_rating = sum_ratings / num_reviews;
}
// Create movie detail object and return it
Movie m = new Movie(MovieID, (string)result);
MovieDetail md = new MovieDetail(m, avg_rating, num_reviews, reviews);
return(md);
}
//
// GetMovieDetail:
//
// Given a MOVIE ID, returns detailed information about this movie --- all
// the reviews, the total number of reviews, average rating, etc. If the
// movie cannot be found, null is returned.
//
public MovieDetail GetMovieDetail(int MovieID)
{
//
// DONE!
//
if (GetMovie(MovieID) == null)
{
return(null);
}
string value = GetMovie(MovieID).MovieName.Replace("'", "''");
string sql1 = string.Format(@"
SELECT ROUND (AVG (CAST (Rating AS Float)), 4) AS AvgRating
FROM Reviews
INNER JOIN Movies ON Reviews.MovieID = Movies.MovieID
WHERE MovieName = '{0}';", value);
object averageRating = dataTier.ExecuteScalarQuery(sql1);
string sql2 = string.Format(@"
SELECT COUNT (Rating)
FROM Reviews
WHERE MovieID = {0}", MovieID);
object numberRatings = dataTier.ExecuteScalarQuery(sql2);
string sql3 = string.Format(@"
SELECT UserID, Rating, ReviewID
FROM Reviews
WHERE MovieID = {0}
ORDER BY Rating Desc, UserID ASC;", MovieID);
DataSet ds = dataTier.ExecuteNonScalarQuery(sql3);
DataTable dt = ds.Tables["TABLE"];
List <Review> reviews = new List <Review>();
foreach (DataRow row in dt.Rows)
{
reviews.Add(new Review(Convert.ToInt32(row["ReviewID"]), MovieID, Convert.ToInt32(row["UserID"]), Convert.ToInt32(row["Rating"])));
}
if (reviews.Count <= 0)
{
numberRatings = 0;
averageRating = 0;
}
MovieDetail movieDetail = new MovieDetail(GetMovie(MovieID), Convert.ToDouble(averageRating), Convert.ToInt32(numberRatings), reviews);
return(movieDetail);
}
//
// GetMovieDetail:
//
// Given a MOVIE ID, returns detailed information about this movie --- all
// the reviews, the total number of reviews, average rating, etc. If the
// movie cannot be found, null is returned.
//
public MovieDetail GetMovieDetail(int MovieID)
{
IReadOnlyList <Review> list = new List <Review>();
List <Review> Review = new List <Review>();
try
{
// get the Review objects
string sql = string.Format(@"Select ReviewID, MovieID, UserID, Rating from Reviews
where MovieID = '{0}'
order by Rating DESC, UserID ASC;", MovieID);
DataSet ds = dataTier.ExecuteNonScalarQuery(sql);
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
var temp = new Review(Convert.ToInt32(row["ReviewID"]),
Convert.ToInt32(row["MovieID"]),
Convert.ToInt32(row["UserID"]),
Convert.ToInt32(row["Rating"]));
Review.Add(temp);
}
Movie movieName = GetMovie(MovieID);
string sql1 = string.Format(@"Select Round(AVG(CONVERT(float, Rating)), 4) as AvgRating,
count(*) as NumReviews from Reviews where MovieID = '{0}';",
MovieID);
DataSet ds1 = dataTier.ExecuteNonScalarQuery(sql1);
if (ds1.Tables["TABLE"].Rows.Count == 0)
{
return(null); // check if the movie exists
}
DataRow rows = ds1.Tables["TABLE"].Rows[0];
MovieDetail temp1 = new MovieDetail(movieName,
Convert.ToDouble(rows["AvgRating"]),
Convert.ToInt32(rows["NumReviews"]),
Review);
return(temp1);
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetAllNamedUsers: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
}
//
// GetMovieDetail:
//
// Given a MOVIE ID, returns detailed information about this movie --- all
// the reviews, the total number of reviews, average rating, etc. If the
// movie cannot be found, null is returned.
//
public MovieDetail GetMovieDetail(int MovieID)
{
//
// TODO!
//
Movie movie = GetMovie(MovieID);
if (movie == null)
{
return(null);
}
string AvgSql = string.Format(@"Select Round (Avg(convert (float,Rating)),4) As Average From Reviews
Where MovieID={0};", MovieID);
object avgResult = dataTier.ExecuteScalarQuery(AvgSql);
double average = Convert.ToDouble(avgResult);
string numReviewsSql = string.Format(@"Select count (*) From Reviews
Where MovieID = '{0}'", MovieID);
int TotalReviews = Convert.ToInt32(dataTier.ExecuteScalarQuery(numReviewsSql));
string allReviewsSql = string.Format(@"Select * From Reviews
Where MovieID = '{0}'
Order by Rating DESC", MovieID);
DataSet ds = dataTier.ExecuteNonScalarQuery(allReviewsSql);
List <Review> newList = new List <Review>();
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
int RID = Convert.ToInt32(row["ReviewID"]);
int MID = Convert.ToInt32(row["MovieID"]);
int UID = Convert.ToInt32(row["UserID"]);
int Rate = Convert.ToInt32(row["Rating"]);
Review newReview = new Review(RID, MID, UID, Rate);
newList.Add(newReview);
}
MovieDetail MD = new MovieDetail(movie, average, TotalReviews, newList);
return(MD);
}
private void lstMovies_SelectedIndexChanged(object sender, EventArgs e)
{
this.txtMovieID.Items.Clear();
this.movieRating.Items.Clear();
this.userID.Items.Clear();
this.occupation.Items.Clear();
this.secondBox.Items.Clear();
string dbfilename = this.txtDatabase.Text;
BusinessTier.Business biztier = new BusinessTier.Business(dbfilename);
BusinessTier.User user = biztier.GetNamedUser(this.lstMovies.Text);
BusinessTier.Movie movie = biztier.GetMovie(this.lstMovies.Text);
if (user == null)
{
this.txtMovieID.Items.Add(string.Format("{0}", movie.MovieID));
var details = biztier.GetMovieDetail(movie.MovieID);
this.movieRating.Items.Add(string.Format("{0}", details.AvgRating));
BusinessTier.MovieDetail detail = biztier.GetMovieDetail(movie.MovieID);
foreach (BusinessTier.Review i in detail.Reviews)
{
this.secondBox.Items.Add(string.Format("{0}: {1}", i.UserID, i.Rating));
}
}
if (movie == null)
{
this.userID.Items.Add(string.Format("{0}", user.UserID));
this.occupation.Items.Add(string.Format("{0}", user.Occupation));
BusinessTier.UserDetail detail = biztier.GetUserDetail(user.UserID);
foreach (BusinessTier.Review i in detail.Reviews)
{
var movieName = biztier.GetMovie(i.MovieID);
this.secondBox.Items.Add(string.Format("{0}-> {1}", movieName.MovieName, i.Rating));
}
}
}
//
// GetMovieDetail:
//
// Given a MOVIE ID, returns detailed information about this movie --- all
// the reviews, the total number of reviews, average rating, etc. If the
// movie cannot be found, null is returned.
//
public MovieDetail GetMovieDetail(int MovieID)
{
string sql = string.Format(@"Select * From Reviews where MovieID ='" + MovieID + "' order by Rating desc, userId asc");
var data = dataTier.ExecuteNonScalarQuery(sql);
List <Review> reviews = new List <Review>();
if (data.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
var data2 = dataTier.ExecuteScalarQuery(@"Select AVG(Convert(float, Rating)) from Reviews where MovieID = '" + MovieID + "'");
var movie = GetMovie(MovieID);
double avgRating = Convert.ToDouble(Convert.ToString(data2)); //??
var numReviews = reviews.Count;
foreach (DataRow r in data.Tables[0].Rows)
{
int reviewId = Convert.ToInt32(Convert.ToString(r.ItemArray[0]));
int movieId = Convert.ToInt32(Convert.ToString(r.ItemArray[1]));
int userId = Convert.ToInt32(Convert.ToString(r.ItemArray[2]));
int rating = Convert.ToInt32(Convert.ToString(r.ItemArray[3]));
var newReview = new Review(reviewId, movieId, userId, rating);
reviews.Add(newReview);
}
// MovieDetail(Movie m, double avgRating, int numReviews, IReadOnlyList < Review > reviews)
var movDetail = new MovieDetail(movie, avgRating, numReviews, reviews);
return(movDetail);
}
}
// GetTopMoviesByAvgRating:
//
// Returns the top N movies in descending order by average rating. If two
// movies have the same rating, the movies are presented in ascending order
// by name. If N < 1, an EMPTY LIST is returned.
public IReadOnlyList<Movie> GetTopMoviesByAvgRating(int N)
{
List<Movie> movies = new List<Movie>();
List<MovieDetail> movie_detail = new List<MovieDetail>();
string query = string.Format(@"SELECT * FROM Movies;");
DataSet ds = dataTier.ExecuteNonScalarQuery(query);
int rows = ds.Tables[0].Rows.Count;
for (int i = 0; i < rows; i++)
{
int movie_id = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[0]);
MovieDetail md = GetMovieDetail(movie_id);
movie_detail.Add(md);
}
List<MovieDetail> newList = movie_detail.OrderByDescending(o => o.AvgRating).ToList();
for (int i = 0; i < N; i++) {
movies.Add(newList[i].movie);
}
return movies;
}
//
// GetMovieDetail:
//
// Given a MOVIE ID, returns detailed information about this movie --- all
// the reviews, the total number of reviews, average rating, etc. If the
// movie cannot be found, null is returned.
//
public MovieDetail GetMovieDetail(int MovieID)
{
// SQL Query
string SQL = string.Format("SELECT MovieID FROM Movies WHERE MovieID = '{0}';", MovieID);
// Execute Query
var Q1 = dataTier.ExecuteScalarQuery(SQL);
if (Q1 == null)
{
// SQL Query fails, return null
return(null);
}
else
{
List <Review> review_List = new List <Review>();
// SQL Query
string SQL2 = string.Format(@"SELECT ROUND(AVG(CAST(Rating AS float)),4) FROM Reviews
Inner Join
(
SELECT MovieID From Movies
WHERE MovieID = '{0}'
) AS M
On M.MovieID = Reviews.MovieID", MovieID);
// Execute Query
var Q2 = dataTier.ExecuteScalarQuery(SQL2);
double avg;
// Check if no average rating is found
if (Q2.ToString() == "")
{
// Average rating = 0
avg = 0;
}
else
{
avg = Convert.ToDouble(Q2);
}
// SQL Query
string SQL3 = string.Format(@"SELECT *
FROM Movies
WHERE MovieID = '{0}'", MovieID);
// Execute Query
var Q3 = dataTier.ExecuteNonScalarQuery(SQL3);
var t = Q3.Tables["TABLE"].Rows[0];
// SQL Query
string SQL4 = string.Format(@"SELECT COUNT(ReviewID)
FROM Reviews
WHERE MovieID = '{0}'", MovieID);
// Execute Query
var Q4 = dataTier.ExecuteScalarQuery(SQL4);
int sum = System.Int32.Parse(Q4.ToString());
Movie m = new Movie(MovieID, t["MovieName"].ToString());
// SQL Query
string SQL5 = string.Format(@"SELECT *
FROM Reviews
WHERE MovieID = {0}
ORDER BY Rating DESC, UserID ASC", MovieID);
// Execute Query
var Q5 = dataTier.ExecuteNonScalarQuery(SQL5);
Review R;
// Add to list
foreach (DataRow rowFour in Q5.Tables["TABLE"].Rows)
{
int review_id = System.Int32.Parse(rowFour["ReviewID"].ToString());
int user_id = System.Int32.Parse(rowFour["UserID"].ToString());
int rating = System.Int32.Parse(rowFour["Rating"].ToString());
R = new Review(review_id, MovieID, user_id, rating);
review_List.Add(R);
}
MovieDetail details = new MovieDetail(m, avg, sum, review_List);
return(details);
}
}
//
// GetMovieDetail:
//
// Given a MOVIE ID, returns detailed information about this movie --- all
// the reviews, the total number of reviews, average rating, etc. If the
// movie cannot be found, null is returned.
//
public MovieDetail GetMovieDetail(int MovieID)
{
string moviename = "";
string sql = string.Format("Select MovieName From Movies Where MovieID = {0}", MovieID);
DataSet movieData = dataTier.ExecuteNonScalarQuery(sql);
DataTable dt = movieData.Tables["Table"];
if (dt.Rows.Count == 0) // not found
{
return(null);
}
else
{
foreach (DataRow row in dt.Rows)
{
moviename = row["MovieName"].ToString();
}
}
Movie m = new Movie(MovieID, moviename);
string sql2 = string.Format(@"SELECT ROUND(AVG(CAST(Rating AS Float)), 4) AS AvgRating
FROM Reviews Where MovieID = {0};", MovieID);
object result = dataTier.ExecuteScalarQuery(sql2);
double avgRating;
if (result == null)
{
avgRating = 0.0;
}
else
{
avgRating = Convert.ToDouble(result.ToString());
}
string sql3 = string.Format(@"Select Count(*) As Total From Reviews
Where MovieID = {0};", MovieID);
object result2 = dataTier.ExecuteScalarQuery(sql3);
int numReviews;
if (result2 == null)
{
numReviews = 0;
}
else
{
numReviews = Convert.ToInt32(result2.ToString());
}
string sql4 = string.Format(@"Select * From Reviews Where MovieID = {0} Order By Rating DESC, UserID ASC", MovieID);
DataSet reviewData = dataTier.ExecuteNonScalarQuery(sql4);
DataTable dt2 = reviewData.Tables["Table"];
List <Review> reviews = new List <Review>();
if (dt2.Rows.Count == 0)
{
return(null);
}
else
{
foreach (DataRow row in dt2.Rows)
{
int reviewid = Convert.ToInt32(row["ReviewID"].ToString());
int userid = Convert.ToInt32(row["UserID"].ToString());
int rating = Convert.ToInt32(row["Rating"].ToString());
Review r = new Review(reviewid, MovieID, userid, rating);
reviews.Add(r);
}
}
MovieDetail md = new MovieDetail(m, avgRating, numReviews, reviews);
return(md);
}
//
// GetMovieDetail:
//
// Given a MOVIE ID, returns detailed information about this movie --- all
// the reviews, the total number of reviews, average rating, etc. If the
// movie cannot be found, null is returned.
//
public MovieDetail GetMovieDetail(int MovieID)
{
string query;
//////////////// get the name
query = String.Format(@"SELECT MovieName FROM Movies WHERE MovieID = {0}", MovieID);
// receiving 1 object, so Scalar Query
object MOVIENAME = dataTier.ExecuteScalarQuery(query);
if (MOVIENAME == null)
{
return(null);
}
/////////////// end
/////////////// get the AvgRating and NumReviews
double scoreTotal, NumReviews, AVG;
// used coalesce so that it would return 0 for SUM if no ratings are found, rather than a weird null.
query = String.Format(@"SELECT COALESCE(SUM(Rating), 0) AS Sum, COALESCE(COUNT(Rating), 0) AS Count FROM Reviews WHERE MovieID = {0}", MovieID);
// receiving 2 things, so NonScalar and returns a dataset rather than an object
DataSet RATINGDATA = dataTier.ExecuteNonScalarQuery(query);
if (RATINGDATA == null)
{
return(null);
}
DataRow X = RATINGDATA.Tables[0].Rows[0];
scoreTotal = (int)(X["Sum"]);
NumReviews = (int)X["Count"];
if (scoreTotal == 0) // so it doesnt return NaN instead, it will return 0 if no reviews are found
{
AVG = 0;
}
else
{
AVG = (double)scoreTotal / (double)NumReviews;
}
///////////////
/////////////// get the list of reviews
List <Review> ReviewList = new List <Review>(); // the List of Reviews to return
query = String.Format(@"SELECT * FROM Reviews WHERE MovieID = {0} ORDER BY Rating DESC", MovieID);
// receiving 2 things, so NonScalar and returns a dataset rather than an object
DataSet REVIEWS = dataTier.ExecuteNonScalarQuery(query);
if (REVIEWS == null)
{
return(null);
}
// traverse the dataset and add each review's data to a List format of Review objects
foreach (DataTable table in REVIEWS.Tables)
{
foreach (DataRow row in table.Rows)
{
//foreach(DataRow row in REVIEWS.Tables[0].Rows) {
if (row != null)
{
Review rev = new Review((int)row["ReviewID"], (int)row["MovieID"], (int)row["UserID"], (short)row["Rating"]);
ReviewList.Add(rev);
}
}
}
/////////////// end
/////////////// Put it all together and return it
Movie m = new Movie(MovieID, (string)MOVIENAME); // create the movie object
double AVG2 = Math.Round(AVG, 5); // round the avg for the avgreview double
// DECIDED TO TRUNCATE IT LESS ^^^^ BECAUSE IT MESSES UP THE WAY HE WANTS IT SORTED
MovieDetail DETAIL = new MovieDetail(m, AVG2, (int)NumReviews, ReviewList); // create the detail object.
return(DETAIL);
/////////////// end
}
