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));
}
}
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------------
// GetUserDetail: ***
// Given a USER ID, returns detailed information about this user --- all
// the reviews submitted by this user, the total number of reviews, average
// rating given, etc. If the user cannot be found, null is returned.
public UserDetail GetUserDetail(int UserID)
{
string sql = string.Format(@" Select ReviewID, MovieID, UserID, Rating from Reviews Where UserID = {0};", UserID);
string sql2 = string.Format(@" Select UserName from Users Where UserID = {0};", UserID);
DataSet result = dataTier.ExecuteNonScalarQuery(sql);
object result2 = dataTier.ExecuteScalarQuery(sql2);
List <Review> reviews = new List <Review>();
int totalReviews = 0;
double sumReviews = 0;
foreach (DataTable table2 in result.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);
UserDetail m = new UserDetail(GetNamedUser(Convert.ToString(result2)), displayRating, totalReviews, reviews);
if (reviews == null)
{
return(null);
}
else
{
return(m);
}
}
//
// GetUserDetail:
//
// Given a USER ID, returns detailed information about this user --- all
// the reviews submitted by this user, the total number of reviews, average
// rating given, etc. If the user cannot be found, null is returned.
//
public UserDetail GetUserDetail(int UserID)
{
// SQL Query
string SQL = string.Format(@"SELECT UserName, UserID, Occupation
FROM Users
WHERE UserID = '{0}';", UserID);
// Execute SQL
var Q1 = dataTier.ExecuteScalarQuery(SQL);
if (Q1 == null)
{
// SQL Query fails, return null
return(null);
}
else
{
// Execute Query
DataSet ds = dataTier.ExecuteNonScalarQuery(SQL);
var T = ds.Tables["TABLE"].Rows[0];
User user;
user = new User(UserID, T["UserName"].ToString(), T["Occupation"].ToString());
// SQL Query
string SQL2 = string.Format(@"SELECT ROUND(AVG(Cast(Rating as float)),4)
FROM Reviews
Inner Join
(
SELECT UserID FROM Users
WHERE UserID = '{0}'
) AS Temp
ON Temp.UserID = Reviews.UserID", UserID);
double avg = 0;
// Execute Query
var Q2 = dataTier.ExecuteScalarQuery(SQL2);
if (Q2 == DBNull.Value)
{
// SQL Query fails, return null
return(null);
}
else
{
// Convert to double
avg = Convert.ToDouble(Q2);
}
// SQL Query
string SQL3 = string.Format(@"SELECT COUNT(ReviewID)
FROM Reviews
WHERE UserID = '{0}'", UserID);
// Execute Query
var Q3 = dataTier.ExecuteScalarQuery(SQL3);
int sum = 0;
if (Q3 == null)
{
// SQL Query fails, return null
return(null);
}
else
{
sum = System.Int32.Parse(Q3.ToString());
}
// SQL Query
string SQL4 = string.Format(@"SELECT ReviewID, MovieID, UserID, Rating
FROM Reviews
WHERE UserID = '{0}'
ORDER BY Rating DESC", UserID);
// Execute Query
DataSet ds2 = dataTier.ExecuteNonScalarQuery(SQL4);
List <Review> review_List = new List <Review>();
Review review;
int review_id;
int movie_id;
int rating;
// Add to list
foreach (DataRow rowTwo in ds2.Tables["TABLE"].Rows)
{
review_id = System.Int32.Parse(rowTwo["ReviewID"].ToString());
movie_id = System.Int32.Parse(rowTwo["MovieID"].ToString());
rating = System.Int32.Parse(rowTwo["Rating"].ToString());
review = new Review(review_id, movie_id, UserID, rating);
review_List.Add(review);
}
UserDetail details;
details = new UserDetail(user, avg, sum, review_List);
return(details);
}
}
//
// GetUserDetail:
//
// Given a USER ID, returns detailed information about this user --- all
// the reviews submitted by this user, the total number of reviews, average
// rating given, etc. If the user cannot be found, null is returned.
//
public UserDetail GetUserDetail(int UserID)
{
//
// DONE!
//
List <Review> reviews = new List <Review>();
string sql = string.Format(@"
SELECT UserID, UserName, Occupation
FROM Users
WHERE UserID = {0}", UserID);
object result = dataTier.ExecuteScalarQuery(sql);
if (result != null)
{
DataSet ds = dataTier.ExecuteNonScalarQuery(sql);
DataTable dt = ds.Tables["TABLE"];
DataRow row = dt.Rows[0];
User user = new User(UserID, row["UserName"].ToString(), row["Occupation"].ToString());
string sql1 = string.Format(@"
SELECT ROUND (AVG (CAST (Rating AS Float)), 4)
FROM Reviews
WHERE UserID = {0}", UserID);
object averageRating = dataTier.ExecuteScalarQuery(sql1);
string sql2 = string.Format(@"
SELECT COUNT(Rating)
FROM Reviews
WHERE UserID = {0}", UserID);
object numberRatings = dataTier.ExecuteScalarQuery(sql2);
string sql3 = string.Format(@"
SELECT MovieID, Rating, ReviewID
FROM Reviews
WHERE UserID = {0}
ORDER BY Rating Desc, UserID ASC;", UserID);
DataSet ds2 = dataTier.ExecuteNonScalarQuery(sql3);
DataTable dt2 = ds2.Tables["TABLE"];
foreach (DataRow row2 in dt2.Rows)
{
reviews.Add(new Review(Convert.ToInt32(row2["ReviewID"]), Convert.ToInt32(row2["MovieID"]), UserID, Convert.ToInt32(row2["Rating"])));
}
if (reviews.Count <= 0)
{
averageRating = 0;
numberRatings = 0;
}
UserDetail userDetail = new UserDetail(user, Convert.ToDouble(averageRating), Convert.ToInt32(numberRatings), reviews);
return(userDetail);
}
return(null);
}
//
// GetUserDetail:
//
// Given a USER ID, returns detailed information about this user --- all
// the reviews submitted by this user, the total number of reviews, average
// rating given, etc. If the user cannot be found, null is returned.
//
public UserDetail GetUserDetail(int UserID)
{
User u = GetUser(UserID);
if (u == null)
{
return(null);
}
int numReviews;
double avgRating;
string sql = string.Format(@"SELECT ROUND(AVG(CAST(Rating AS Float)), 2) AS AvgRating
FROM Reviews Where UserID = {0};", UserID);
object result = dataTier.ExecuteScalarQuery(sql);
if (result == null)
{
avgRating = 0.0;
}
else
{
avgRating = Convert.ToDouble(result.ToString());
}
string sql2 = string.Format(@"Select Count(*) As Total From Reviews
Where UserID = {0};", UserID);
object result2 = dataTier.ExecuteScalarQuery(sql2);
if (result2 == null)
{
numReviews = 0;
}
else
{
numReviews = Convert.ToInt32(result2.ToString());
}
string sql3 = string.Format(@"Select * From Reviews
Inner Join Movies On Reviews.MovieID = Movies.MovieID
Where UserID = {0}
Order By Movies.MovieName ASC, Rating ASC", UserID);
DataSet reviewData = dataTier.ExecuteNonScalarQuery(sql3);
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 movieid = Convert.ToInt32(row["MovieID"].ToString());
int reviewid = Convert.ToInt32(row["ReviewID"].ToString());
int rating = Convert.ToInt32(row["Rating"].ToString());
Review r = new Review(reviewid, movieid, UserID, rating);
reviews.Add(r);
}
}
UserDetail ud = new UserDetail(u, avgRating, numReviews, reviews);
return(ud);
}
//
// GetUserDetail:
//
// Given a USER ID, returns detailed information about this user --- all
// the reviews submitted by this user, the total number of reviews, average
// rating given, etc. If the user cannot be found, null is returned.
//
public UserDetail GetUserDetail(int UserID)
{
string query;
//////////////// (this gets the User object) get the UserName and then use GetNamedUser(string username) to get the full object also containing their occupation
query = String.Format(@"SELECT UserName FROM Users WHERE UserID = {0}", UserID);
// receiving 1 object, so Scalar Query
object USERNAME = dataTier.ExecuteScalarQuery(query);
if (USERNAME == null) // was it a valid ID?
{
return(null);
}
string userName = USERNAME.ToString(); // convert the name to string
User USER = GetNamedUser(userName); // get the USER object (which gets stored in UserDetail later)
/////////////// end
/////////////// get the AvgRating and NumReviews (gets stored in UserDetail object later on)
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 UserID = {0}", UserID);
// 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]; // grab the first row from the returned data set table
scoreTotal = (int)(X["Sum"]); // grab the sum value
NumReviews = (int)X["Count"]; // grab the count value
if (scoreTotal == 0) // checks to see if no reviews are found, returning 0 if none are found.
{
AVG = 0;
}
else
{
AVG = (double)scoreTotal / (double)NumReviews; // divide them to compute the avg
}
///////////////
/////////////// get the list of reviews
List <Review> ReviewList = new List <Review>(); // the List of Reviews to return
query = String.Format(@"SELECT * FROM Reviews WHERE UserID = {0} ORDER BY Rating DESC", UserID);
// 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)
{
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
double AVG2 = Math.Round(AVG, 4); // round the avg for the avgreview double, truncating it
UserDetail DETAIL = new UserDetail(USER, AVG2, (int)NumReviews, ReviewList); // create the detail object.
return(DETAIL);
/////////////// end
}
