private List <Movie> Search(string title, string director, string year, string genre, string actor)
{
DBConnect helper = new DBConnect();
List <Movie> movies = new List <Movie>();
if (title.Length > 2)
{
List <Movie> fromTitle = helper.SelectMovie("SELECT * FROM Movies WHERE title LIKE '%" + title + "%'");
movies.AddRange(fromTitle);
}
if (director.Length > 2)
{
List <Movie> fromDirector = helper.SelectMovie("SELECT * FROM Movies WHERE director LIKE '%" + director + "%'");
movies.AddRange(fromDirector);
}
if (year.Length > 2)
{
List <Movie> fromYear = helper.SelectMovie("SELECT * FROM Movies WHERE year LIKE '%" + year + "%'");
movies.AddRange(fromYear);
}
if (genre.Length > 2)
{
List <Movie> fromGenre = helper.SelectMovieByGenre(genre);
movies.AddRange(fromGenre);
}
if (actor.Length > 2)
{
List <Movie> fromActor = helper.SelectMovieByActor(actor);
movies.AddRange(fromActor);
}
return(movies);
}
public List <Movie> selectRandomMovies(int n)
{
//Build Recomendations
List <Movie> list = new List <Movie>();
//Open connection
if (this.OpenConnection() == true)
{
String query = "SELECT MID FROM `Movies` ORDER BY RAND() LIMIT " + n;
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
DBConnect temp = new DBConnect();
//Console.Out.WriteLine(dataReader["MID"]);
list.AddRange(temp.SelectMovie("SELECT * FROM Movies WHERE MID='" + dataReader["MID"] + "'"));
}
dataReader.Close();
}
return(list);
}
public List <Movie> Search(String searchTerm)
{
DBConnect helper = new DBConnect();
List <Movie> movies;
if (searchTerm.Length > 2)
{
movies = helper.SelectMovie("SELECT * FROM Movies WHERE title LIKE '%" + searchTerm + "%'");
}
else
{
movies = new List <Movie>();
}
return(movies);
}
public List <Movie> SelectMovieByActor(string actor)
{
List <Movie> list = new List <Movie>();
string query = "SELECT * FROM WasIn,Actors WHERE Actors.AID=WasIn.AID AND Actors.name LIKE '%" + actor + "%'";
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
DBConnect temp = new DBConnect();
//Console.Out.WriteLine(dataReader["MID"]);
list.AddRange(temp.SelectMovie("SELECT * FROM Movies WHERE MID='" + dataReader["MID"] + "'"));
}
}
return(list);
}
public List <Movie> selectMoviesFromWatchList(int UID)
{
List <Movie> list = new List <Movie>();
string query = "SELECT MID FROM WatchList WHERE UID ='" + UID + "'";
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
DBConnect temp = new DBConnect();
//Console.Out.WriteLine(dataReader["MID"]);
list.AddRange(temp.SelectMovie("SELECT * FROM Movies WHERE MID='" + dataReader["MID"] + "'"));
}
}
return(list);
}
public List<Movie> Search(String searchTerm)
{
DBConnect helper = new DBConnect();
List<Movie> movies;
if (searchTerm.Length > 2)
{
movies = helper.SelectMovie("SELECT * FROM Movies WHERE title LIKE '%" + searchTerm + "%'");
}
else
{
movies = new List<Movie>();
}
return movies;
}
private List<Movie> Search(string title, string director, string year, string genre, string actor)
{
DBConnect helper = new DBConnect();
List<Movie> movies = new List<Movie>();
if (title.Length > 2)
{
List<Movie> fromTitle = helper.SelectMovie("SELECT * FROM Movies WHERE title LIKE '%" + title + "%'");
movies.AddRange(fromTitle);
}
if (director.Length > 2)
{
List<Movie> fromDirector = helper.SelectMovie("SELECT * FROM Movies WHERE director LIKE '%" + director + "%'");
movies.AddRange(fromDirector);
}
if (year.Length > 2)
{
List<Movie> fromYear = helper.SelectMovie("SELECT * FROM Movies WHERE year LIKE '%" + year + "%'");
movies.AddRange(fromYear);
}
if (genre.Length > 2)
{
List<Movie> fromGenre = helper.SelectMovieByGenre(genre);
movies.AddRange(fromGenre);
}
if (actor.Length > 2)
{
List<Movie> fromActor = helper.SelectMovieByActor(actor);
movies.AddRange(fromActor);
}
return movies;
}
//===========End FOR WATCHLIST============
public List<Movie> selectRecomendations(int UID)
{
//Build Recomendations
string query = "SELECT avg(R.value) as avg,F.GID as GeID FROM `FitsIn` F,Ratings R WHERE R.UID =" + UID + " AND F.MID = R.MID GROUP BY F.GID";
List<Movie> list = new List<Movie>();
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
double value1 = -1;
int GID1 = -1;
double value2 = -1;
int GID2 = -1;
while (dataReader.Read())
{
if (double.Parse(dataReader["avg"] + "") > value1)
{
value1 = double.Parse(dataReader["avg"] + "");
GID1 = int.Parse(dataReader["GeID"] + "");
}
else if (double.Parse(dataReader["avg"] + "") > value2)
{
value2 = double.Parse(dataReader["avg"] + "");
GID2 = int.Parse(dataReader["GeID"] + "");
}
//genreScores.Add(double.Parse(dataReader["avg"] + ""), int.Parse(dataReader["GeID"] + ""));
}
dataReader.Close();
//Pick top n Genre
const int n = 2;
int curGID;
for (int i = 0; i < n; i++)
{
curGID = -1;
if (i == 0)
{
curGID =GID1;
}
else if (i == 1)
{
curGID = GID2;
}
if (curGID != -1)
{
query = "SELECT MID FROM `FitsIn` WHERE GID = " + curGID + " ORDER BY RAND() LIMIT 5";
//Open connection
//Create Command
cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
DBConnect temp = new DBConnect();
//Console.Out.WriteLine(dataReader["MID"]);
list.AddRange(temp.SelectMovie("SELECT * FROM Movies WHERE MID='" + dataReader["MID"] + "'"));
}
dataReader.Close();
}
}
}
return list;
}
public List<Movie> selectRandomMovies(int n)
{
//Build Recomendations
List<Movie> list = new List<Movie>();
//Open connection
if (this.OpenConnection() == true)
{
String query = "SELECT MID FROM `Movies` ORDER BY RAND() LIMIT "+n;
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
DBConnect temp = new DBConnect();
//Console.Out.WriteLine(dataReader["MID"]);
list.AddRange(temp.SelectMovie("SELECT * FROM Movies WHERE MID='" + dataReader["MID"] + "'"));
}
dataReader.Close();
}
return list;
}
public List<Movie> selectMoviesFromWatchList(int UID)
{
List<Movie> list = new List<Movie>();
string query = "SELECT MID FROM WatchList WHERE UID ='" + UID + "'";
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
DBConnect temp = new DBConnect();
//Console.Out.WriteLine(dataReader["MID"]);
list.AddRange(temp.SelectMovie("SELECT * FROM Movies WHERE MID='" + dataReader["MID"] + "'"));
}
}
return list;
}
public List<Movie> SelectMovieByGenre(string genre)
{
List<Movie> list = new List<Movie>();
string query = "SELECT * FROM FitsIn,Genres WHERE Genres.GID=FitsIn.GID AND name LIKE '%" + genre + "%'";
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
DBConnect temp = new DBConnect();
//Console.Out.WriteLine(dataReader["MID"]);
list.AddRange( temp.SelectMovie("SELECT * FROM Movies WHERE MID='"+dataReader["MID"]+"'"));
}
}
return list;
}
//===========End FOR WATCHLIST============
public List <Movie> selectRecomendations(int UID)
{
//Build Recomendations
string query = "SELECT avg(R.value) as avg,F.GID as GeID FROM `FitsIn` F,Ratings R WHERE R.UID =" + UID + " AND F.MID = R.MID GROUP BY F.GID";
List <Movie> list = new List <Movie>();
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
double value1 = -1;
int GID1 = -1;
double value2 = -1;
int GID2 = -1;
while (dataReader.Read())
{
if (double.Parse(dataReader["avg"] + "") > value1)
{
value1 = double.Parse(dataReader["avg"] + "");
GID1 = int.Parse(dataReader["GeID"] + "");
}
else if (double.Parse(dataReader["avg"] + "") > value2)
{
value2 = double.Parse(dataReader["avg"] + "");
GID2 = int.Parse(dataReader["GeID"] + "");
}
//genreScores.Add(double.Parse(dataReader["avg"] + ""), int.Parse(dataReader["GeID"] + ""));
}
dataReader.Close();
//Pick top n Genre
const int n = 2;
int curGID;
for (int i = 0; i < n; i++)
{
curGID = -1;
if (i == 0)
{
curGID = GID1;
}
else if (i == 1)
{
curGID = GID2;
}
if (curGID != -1)
{
query = "SELECT MID FROM `FitsIn` WHERE GID = " + curGID + " ORDER BY RAND() LIMIT 5";
//Open connection
//Create Command
cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
DBConnect temp = new DBConnect();
//Console.Out.WriteLine(dataReader["MID"]);
list.AddRange(temp.SelectMovie("SELECT * FROM Movies WHERE MID='" + dataReader["MID"] + "'"));
}
dataReader.Close();
}
}
}
return(list);
}