// When selecting a user in the list, retrieve their id and occupation
private void listBox_users_SelectedIndexChanged(object sender, EventArgs e)
{
string userName = this.listBox_users.Text;
this.textBox_userID.Clear();
this.textBox_occupation.Clear();
string filename = this.textBox_filename.Text;
if (!fileExists(filename))
{
return;
}
BusinessTier.Business biztier = new BusinessTier.Business(filename);
BusinessTier.User user = biztier.GetNamedUser(userName);
if (user == null)
{
MessageBox.Show("ERROR: User not found!");
return;
}
this.textBox_userID.Text = user.UserID.ToString();
this.textBox_occupation.Text = user.Occupation.ToString();
}
//
// GetUser:
//
// Retrieves User object based on USER ID; returns null if user is not
// found.
//
// NOTE: if the user exists in the Users table, then a meaningful name and
// occupation are returned in the User object. If the user does not exist
// in the Users table, then the user id has to be looked up in the Reviews
// table to see if he/she has submitted 1 or more reviews as an "anonymous"
// user. If the id is found in the Reviews table, then the user is an
// "anonymous" user, so a User object with name = "<UserID>" and no occupation
// ("") is returned. In other words, name = the user’s id surrounded by < >.
//
public User GetUser(int UserID)
{
BusinessTier.User User = null;
string sql = string.Format(@"SELECT UserName FROM Users WHERE UserID = {0};", UserID);
object UserNameQuery = dataTier.ExecuteScalarQuery(sql); // Query that returns a UserName
if (UserNameQuery == null)
{
sql = string.Format(@"SELECT (*) FROM Reviews WHERE UserID = {0};", UserID);
UserNameQuery = dataTier.ExecuteScalarQuery(sql); // Query that returns a UserName
if (UserNameQuery != null)
{
User = new BusinessTier.User(UserID, "<UserID>", "");
}
}
sql = string.Format(@"SELECT Occupation FROM Users WHERE UserID = {0};", UserID);
object OccupationQuery = dataTier.ExecuteScalarQuery(sql); // Query that returns an Occupation
string UserName = UserNameQuery.ToString();
string Occupation = OccupationQuery.ToString();
User = new BusinessTier.User(UserID, UserName, Occupation);
return(User);
}
//-----------------------------------------------------------------------------------------------------------------------------------------
private void button3_Click(object sender, EventArgs e) // Search User Reviews
{
listBox2.Items.Clear(); // clear display
string dbfilename = this.textBox3.Text; //get DB name from text box: //where the database is located
BusinessTier.Business biztier = new BusinessTier.Business(dbfilename);
string username = this.textBox10.Text; //get user’s input from where they are clicking
BusinessTier.User user = biztier.GetNamedUser(username);
if (user == null)
{
MessageBox.Show("Invalid User");
return;
}
BusinessTier.UserDetail ratings = biztier.GetUserDetail(user.UserID); // from Object browser
listBox2.Items.Add(username);
listBox2.Items.Add(" ");
foreach (var x in ratings.Reviews)
{
var movie = biztier.GetMovie(x.MovieID);
string movieName = movie.MovieName;
listBox2.Items.Add(movieName + " -> " + x.Rating);
}
this.textBox10.Clear();// clear textBox
}
private void button4_Click(object sender, EventArgs e)// insert Review
{
//textBox6: User
//textBox7: Movie
//textBox8: Rating
//addReview(int,int,int);
string dbfilename = this.textBox3.Text; //get DB name from text box: //where the database is located
BusinessTier.Business biztier = new BusinessTier.Business(dbfilename);
string username = this.textBox6.Text; //get user input from user
string movie = this.textBox7.Text; //get movie input from user
string rating = this.textBox8.Text; //get rating input from user
BusinessTier.Movie movieObj = biztier.GetMovie(movie);
BusinessTier.User userObj = biztier.GetNamedUser(username);
if (userObj == null)
{
MessageBox.Show("Invalid Username");
return;
}
else if (movieObj == null)
{
MessageBox.Show("Invalid Movie");
return;
}
else if (Convert.ToInt32(rating) < 1 || Convert.ToInt32(rating) > 5)
{
MessageBox.Show("Rating must be 1 <= 5");
return;
}
else
{
var x = biztier.AddReview(movieObj.MovieID, userObj.UserID, Convert.ToInt32(rating));
if (x == null)
{
MessageBox.Show("fail");
}
}
this.textBox6.Clear();
this.textBox7.Clear();
this.textBox8.Clear();
}
//
// 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 OBJECT -----------------------------------------------------------------------------
BusinessTier.User User = GetUser(UserID);
// -----------------------------------------------------------------------------------------
// AVG RATING ------------------------------------------------------------------------------
double avgRating;
string sql = string.Format(@"SELECT AVG(CAST(Rating as float)) FROM Reviews WHERE UserID = {0};", UserID);
object query = dataTier.ExecuteScalarQuery(sql);
if (query.ToString() != "")
{
avgRating = Convert.ToDouble(query.ToString());
}
else
{
avgRating = 0.0;
}
// -----------------------------------------------------------------------------------------
// NUM OF REVIEWS --------------------------------------------------------------------------
sql = string.Format(@"SELECT Count(UserID) FROM Reviews WHERE UserID = {0};", UserID);
query = dataTier.ExecuteScalarQuery(sql);
int numReviews = Convert.ToInt32(query.ToString());
// -----------------------------------------------------------------------------------------
// BUILDING LIST OF REVIEWS ----------------------------------------------------------------
List <Review> reviews = new List <Review>();
// HARVESTS ALL DATA WE NEED EXCEPT MOVIEID (ALREADY HAVE)
sql = string.Format(@"SELECT DISTINCT ReviewID, Rating, MovieID FROM Reviews WHERE UserID = {0} ORDER BY Rating DESC, ReviewID ASC;", UserID);
DataSet reviewsSet = dataTier.ExecuteNonScalarQuery(sql);
foreach (DataRow row in reviewsSet.Tables["TABLE"].Rows)
{
reviews.Add(new BusinessTier.Review(Convert.ToInt32(row["ReviewID"]),
Convert.ToInt32(row["MovieID"]),
UserID,
Convert.ToInt32(row["Rating"])));
}
// -----------------------------------------------------------------------------------------
BusinessTier.UserDetail UserDetail = new BusinessTier.UserDetail(User, avgRating, numReviews, reviews);
return(UserDetail);
}
private void listBox2_SelectedIndexChanged(object sender, EventArgs e)
{
string dbfilename = this.textBox1.Text;
BusinessTier.Business biztier = new BusinessTier.Business(dbfilename);
if (!biztier.TestConnection())
{
return;
}
string selected = listBox2.Text;
BusinessTier.User user = biztier.GetNamedUser(selected);
textBox7.Text = user.Occupation;
textBox6.Text = Convert.ToString(user.UserID);
getuserreviews();
}
//-----------------------------------------------------------------------------------------------------------------------------------------
private void listBox2_SelectedIndexChanged(object sender, EventArgs e)
{
//ListBox2: User's selection
//textBox4: User ID
//textBox5: Occupation
string dbfilename = this.textBox3.Text; //get DB name from text box: //where the database is located
BusinessTier.Business biztier = new BusinessTier.Business(dbfilename);
string username = this.listBox2.Text; //get user’s input from where they are clicking
BusinessTier.User user = biztier.GetNamedUser(username); //obtain user object
this.textBox4.Text = "" + user.UserID;
this.textBox5.Text = "" + user.Occupation;
}
/* Displays all of the users reviews in a listbox */
public void getAllUserReviews()
{
int index = this.listBox1.SelectedIndex;
BusinessTier.Business btier = new BusinessTier.Business(this.textBox1.Text);
// test connection to database
if (!btier.TestConnection())
{
MessageBox.Show("Can't connect to the database!");
return;
}
// Get user if the user exists
BusinessTier.User user = btier.GetNamedUser(this.listBox1.Items[index].ToString());
if (user == null)
{
MessageBox.Show("User does not exist!");
return;
}
else
{
// Get all of the users reviews
this.listBox2.Items.Clear();
BusinessTier.UserDetail userDetails = btier.GetUserDetail(user.UserID);
if (userDetails.NumReviews == 0)
{
this.listBox2.Items.Add("No Reviews");
}
else
{
// For all reviews, get movie name + rating and insert
this.listBox2.Items.Add(user.UserName);
this.listBox2.Items.Add("");
foreach (BusinessTier.Review U in userDetails.Reviews)
{
BusinessTier.Movie m = btier.GetMovie(U.MovieID);
this.listBox2.Items.Add(m.MovieName + "->" + U.Rating);
}
}
}
}
/* Inserts a rating into the database */
private void insert_movieRating_Click(object sender, EventArgs e)
{
// Check that a movie was selected
if (string.IsNullOrWhiteSpace(this.textBox7.Text))
{
MessageBox.Show("Please select a Movie from the list!");
return;
}
// Check that a Username was selected
if (string.IsNullOrWhiteSpace(this.textBox4.Text))
{
MessageBox.Show("Please select a Username from the list!");
return;
}
// Check that Rating is valid
int rating = 0;
string s = this.textBox5.Text;
bool validRating = int.TryParse(s, out rating);
if (!(validRating && (rating <= 5) && (rating >= 1)))
{
MessageBox.Show("Please enter a rating between 1 and 5!");
return;
}
// test connection to database
BusinessTier.Business btier = new BusinessTier.Business(this.textBox1.Text);
if (!btier.TestConnection())
{
MessageBox.Show("Can't connect to the database!");
return;
}
// Get movie ID and user ID
BusinessTier.Movie M = btier.GetMovie(this.textBox7.Text);
BusinessTier.User U = btier.GetNamedUser(this.textBox4.Text);
// Insert review
btier.AddReview(M.MovieID, U.UserID, rating);
MessageBox.Show("Review was succesfully inserted!");
}
private void listBox2_SelectedIndexChanged(object sender, EventArgs e) //Username Listbox
{
string dbfilename = this.textBox1.Text;
this.listBox3.Items.Clear();
this.textBox4.Clear();
this.textBox5.Clear();
this.textBox7.Text = this.listBox2.Text;
BusinessTier.Business biztier = new BusinessTier.Business(dbfilename); //open object
string username = this.listBox2.Text;
var tempname = username;
BusinessTier.User users = biztier.GetNamedUser(username); //obtain movie object
var userReviewinfo = biztier.GetUserDetail(users.UserID);
if (users == null)
{
MessageBox.Show("Error 404!");
}
else
{
this.listBox3.Items.Add(username);
this.listBox3.Items.Add("");
foreach (var review in userReviewinfo.Reviews)
{
if (review == null)
{
String temp = String.Format("No Review");
this.listBox3.Items.Add(temp);
}
else
{
var movie = biztier.GetMovie(review.MovieID);
string info = String.Format("{0} -> {1}", movie.MovieName, review.Rating);
this.listBox3.Items.Add(info);
}
}
this.textBox4.Text = users.UserID.ToString();
this.textBox5.Text = users.Occupation.ToString();
}
}
//
// GetNamedUser:
//
// Retrieves User object based on USER NAME; returns null if user is not
// found.
//
// NOTE: there are "named" users from the Users table, and anonymous users
// that only exist in the Reviews table. This function only looks up "named"
// users from the Users table.
//
public User GetNamedUser(string UserName)
{
string sql = string.Format(@"SELECT UserID FROM Users WHERE UserName = '******';", UserName.Replace("'", "''"));
object UserIDQuery = dataTier.ExecuteScalarQuery(sql); // Query that returns a UserName
int UserID = Convert.ToInt32(UserIDQuery.ToString());
if (UserIDQuery == null)
{
return(null);
}
sql = string.Format(@"SELECT Occupation FROM Users WHERE UserID = {0};", UserID);
object OccupationQuery = dataTier.ExecuteScalarQuery(sql); // Query that returns an Occupation
string Occupation = OccupationQuery.ToString();
UserName.Replace("''", "'");
BusinessTier.User User = new BusinessTier.User(UserID, UserName, Occupation);
return(User);
}
private void listBox2_SelectedIndexChanged(object sender, EventArgs e)
{
String dbfilename = this.dbname.Text; // get DB name from text box:
BusinessTier.Business biztier = new BusinessTier.Business(dbfilename);
if (biztier.TestConnection())
{
string uname = this.listBox2.Text;
BusinessTier.User userdetails = biztier.GetNamedUser(uname);
this.textBox1.Text = Convert.ToString(userdetails.UserID);
this.textBox4.Text = userdetails.Occupation;
}
else
{
MessageBox.Show("Connection could not be established");
return;
}
}
/* Gets the users ID and occupation and inserts into text boxes */
private void getUserID_Occupation()
{
int index = this.listBox1.SelectedIndex;
BusinessTier.Business btier = new BusinessTier.Business(this.textBox1.Text);
// test connection to database
if (!btier.TestConnection())
{
MessageBox.Show("Can't connect to the database!");
return;
}
// Get user ID and occupation if user exists
BusinessTier.User user = btier.GetNamedUser(this.listBox1.Items[index].ToString());
if (user == null)
{
MessageBox.Show("User does not exist!");
return;
}
else
{
// User exists, display their User ID and Occupation, if any
this.textBox2.Text = user.UserID.ToString();
if (string.IsNullOrWhiteSpace(user.Occupation))
{
this.textBox3.Text = "N/A";
}
else
{
this.textBox3.Text = user.Occupation.ToString();
}
}
// Done
this.textBox4.Text = this.listBox1.Items[index].ToString();
}
private void InsertReview_Click(object sender, EventArgs e)
{
if (!fileExists(filename))
{
return;
}
if (!connectionTest(filename))
{
return;
}
if (this.ListDisplay.SelectedIndex < 0)
{
MessageBox.Show("Please select a movie...");
return;
}
if (this.ListDisplay2.SelectedIndex < 0)
{
MessageBox.Show("Please select a user...");
return;
}
if (this.RatingBox.SelectedIndex < 0)
{
MessageBox.Show("Please select a rating...");
return;
}
filename = this.txtFilename.Text;
BusinessTier.Business biztier = new BusinessTier.Business(filename);
string movieId = MovieIdBox.Text; // MovieNameBox.Text;
string userId = userIdBox.Text;
string rating = RatingBox.Text;
BusinessTier.Movie movie = biztier.GetMovie(Convert.ToInt32(movieId));
BusinessTier.User user = biztier.GetUser(Convert.ToInt32(userId));
if (movie == null)
{
MessageBox.Show(String.Format("ID: {0} not found..!", movieId)); return;
}
else if (user == null)
{
MessageBox.Show(String.Format("ID: {0} not found..!", userId)); return;
}
int rate = Convert.ToInt32(rating);
if (rate < 1 || rate > 5)
{
MessageBox.Show("Rating is not in range..!"); return;
}
var result = biztier.AddReview(movie.MovieID, user.UserID, rate);
if (result == null)
{
MessageBox.Show("Insert failed...");
}
else
{
MessageBox.Show(String.Format("Insert Success with new Review ID: {0}", result.ReviewID.ToString()));
}
}
