/// <summary>
/// Return the movie with a given id
/// </summary>
/// <param name="movieId">The id of the movie to get</param>
/// <returns>The movie with the given id, or null if no such movie exists</returns>
public Movie GetMovie(int movieId) {
SqlCommand command = new SqlCommand("SELECT * FROM Movie WHERE id =" + movieId, connection);
SqlDataReader reader = command.ExecuteReader();
if (reader.Read()) {
reader.Close();
command.CommandText = "SELECT * FROM Files WHERE id =" + movieId;
reader = command.ExecuteReader();
reader.Read();
Movie mov = new Movie() {
Id = movieId,
RentPrice = int.Parse(reader["rentPrice"].ToString()),
BuyPrice = int.Parse(reader["buyPrice"].ToString()),
Uri = reader["URI"].ToString(),
Title = reader["title"].ToString(),
Description = reader["description"].ToString(),
Year = short.Parse(reader["year"].ToString()),
CoverUri = reader["coverURI"].ToString(),
ViewCount = int.Parse(reader["viewCount"].ToString())
};
reader.Close();
return mov;
}
reader.Close();
return null;
}
/// <summary>
/// Updates a movie to contain the given values
/// </summary>
/// <param name="movie">A movie object which contains the new values of the movie</param>
/// <param name="adminId">The id of the admin who authorises </param>
/// <returns>True if the update is sucessful, false otherwise </returns>
public bool UpdateMovie(Movie movie, int adminId)
{
//Ensure that the admin id does infact belong to an admin
SqlCommand command = new SqlCommand("SELECT id FROM Admin WHERE id =" + adminId, connection);
if (command.ExecuteScalar() == null) return false;
command.CommandText = "UPDATE Files " +
"SET title = '" + movie.Title.Replace("'", "''") + "', " +
"description = '" + movie.Description.Replace("'", "''") + "', " +
"rentPrice = " + movie.RentPrice + ", " +
"buyPrice = " + movie.BuyPrice + ", " +
"year = " + movie.Year + ", " +
"coverURI = '" + movie.CoverUri.Replace("'", "''") + "' " +
"WHERE id =" + movie.Id;
return (command.ExecuteNonQuery() > 0);
}
/// <summary>
/// Creates a movie in the database
/// </summary>
/// <param name="managerId">Id of the manager who wants to create the movie</param>
/// <param name="tmpId">The temporary id the movie got when uploaded to the server</param>
/// <param name="title">The title of the movie</param>
/// <param name="year">The release year of the movie</param>
/// <param name="buyPrice">The price to buy the movie</param>
/// <param name="rentPrice">The price to rent the movie</param>
/// <param name="director">The director of the movie</param>
/// <param name="genres">The genres the movie fit into</param>
/// <param name="description">A description of the movie</param>
/// <returns>The movie including id and uri or null if the movie could not be added to the database</returns>
public Movie CreateMovie(int managerId, int tmpId, IList<string> genres, Movie movie, IList<string> directors) {
SqlCommand command = new SqlCommand("SELECT * FROM Admin WHERE id =" + managerId, connection);
if (command.ExecuteScalar() != null) {
//Get the uri from the StagedFile table
command.CommandText = "SELECT path FROM StagedFile WHERE id =" + tmpId;
object tmpUri = command.ExecuteScalar();
if (tmpUri == null) return null;
string uri = tmpUri.ToString();
//Add the file information into to Files table
command.CommandText = "INSERT INTO Files" +
"(title, rentPrice, buyPrice, URI, year, description, coverURI, viewCount) " +
"VALUES('" +
movie.Title.Replace("'", "''") + "', " +
movie.RentPrice + ", " +
movie.BuyPrice + ", '" +
uri.Replace("'", "''") + "', " +
movie.Year + ", '" +
movie.Description.Replace("'", "''") + "', '" +
movie.CoverUri.Replace("'", "''") + "', " +
"0)";
//If the information is successfully added continue to add info to the Movie table and GenreFile table
if (command.ExecuteNonQuery() > 0) {
command.CommandText = "SELECT IDENT_CURRENT('Files')";
int fileId = Int32.Parse(command.ExecuteScalar().ToString());
command.CommandText = "INSERT INTO Movie VALUES(" + fileId + ")";
command.ExecuteNonQuery();
//Add genres to the file if any exist
if (genres.Count > 0)
AddAllGenres(fileId, genres);
if (directors.Count > 0)
AddAllDirectors(fileId, directors);
command.CommandText = "DELETE FROM StagedFile WHERE id=" + tmpId;
command.ExecuteNonQuery();
movie.Id = fileId;
movie.Uri = uri;
return movie;
}
}
return null;
}
/// <summary>
/// Returns all movies purchased by a given user
/// </summary>
/// <param name="userId">The id of the user to return all purchased movies for</param>
/// <returns>All movies purchased by the user</returns>
public IList<Purchase> GetMovies(int userId) {
//Get information on which movies the user has purchased
IList<Purchase> purchases = new List<Purchase>();
SqlCommand command = new SqlCommand("SELECT * FROM UserFile WHERE uid=" + userId,
connection);
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read()) {
int fileId = Int32.Parse(reader["fid"].ToString());
SqlCommand filequery = new SqlCommand("SELECT * FROM Files WHERE id =" + fileId,
connection);
SqlCommand moviequery = new SqlCommand("SELECT * FROM Movie WHERE id =" + fileId,
connection);
SqlDataReader fr = filequery.ExecuteReader();
SqlDataReader mr = moviequery.ExecuteReader();
if (fr.Read()) {
if (mr.Read()) {
Console.WriteLine(fr["description"].ToString());
Movie mov = new Movie() {
Id = fileId,
BuyPrice = int.Parse(fr["buyPrice"].ToString()),
Description = fr["description"].ToString(),
Title = fr["title"].ToString(),
Uri = fr["URI"].ToString(),
Year = short.Parse(fr["year"].ToString()),
RentPrice = int.Parse(fr["rentPrice"].ToString())
};
purchases.Add(new Purchase(mov, (DateTime)reader["endTime"]));
}
}
}
}
Console.WriteLine(purchases.Count);
Console.WriteLine(purchases.Count);
return purchases;
}
/// <summary>
/// Returns all files purchased by a given user
/// </summary>
/// <param name="userId">The id of the user to return all purchased files for</param>
/// <returns>All files purchased by the user</returns>
public IList<Purchase> GetPurchases(int userId) {
//Get information on which movies the user has purchased
IList<Purchase> purchases = new List<Purchase>();
SqlCommand command = new SqlCommand("SELECT * FROM UserFile WHERE uid=" + userId,
connection);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read()) {
int fileId = Int32.Parse(reader["fid"].ToString());
SqlCommand filequery = new SqlCommand("SELECT * FROM Files WHERE id =" + fileId,
connection);
SqlCommand moviequery = new SqlCommand("SELECT * FROM Movie WHERE id =" + fileId,
connection);
SqlCommand songquery = new SqlCommand("SELECT * FROM Song WHERE id =" + fileId,
connection);
SqlDataReader fr = filequery.ExecuteReader();
SqlDataReader mr = moviequery.ExecuteReader();
SqlDataReader sr = songquery.ExecuteReader();
if (fr.Read()) {
if (mr.Read()) {
Movie mov = new Movie() {
Id = fileId,
BuyPrice = int.Parse(fr["buyPrice"].ToString()),
Description = fr["description"].ToString(),
Title = fr["title"].ToString(),
Uri = fr["URI"].ToString(),
Year = short.Parse(fr["year"].ToString()),
RentPrice = int.Parse(fr["rentPrice"].ToString())
};
purchases.Add(new Purchase(mov, (DateTime)reader["endTime"]));
} else if (sr.Read()) {
Song song = new Song() {
Id = fileId,
BuyPrice = int.Parse(fr["buyPrice"].ToString()),
Description = fr["description"].ToString(),
Title = fr["title"].ToString(),
Uri = fr["URI"].ToString(),
Year = short.Parse(fr["year"].ToString()),
RentPrice = int.Parse(fr["rentPrice"].ToString()),
Album = sr["album"].ToString()
};
purchases.Add(new Purchase(song, (DateTime.Parse(reader["endTime"].ToString()))));
}
}
}
reader.Close();
return purchases;
}
public SuccessFlagId CreateMovie(int managerid, int tmpid, string title, int release, string[] directors, string[] genres, string description, int rentalPrice, int purchasePrice, string coverUri) {
Movie mov = new Movie()
{
Title = title,
Year = (short)release,
Description = description,
RentPrice = rentalPrice,
BuyPrice = purchasePrice,
CoverUri = coverUri
};
db.Open();
IList<string> genr = new List<string>(genres);
Movie mov1 = db.CreateMovie(managerid, tmpid, genr, mov, directors);
if (mov1 == null)
throw new ArgumentException("Ensure you entered a valid tmpId and a valid admin id");
else
return new SuccessFlagId()
{
id = mov1.Id,
success = true
};
}
public static void SortBy(Movie[] movs, String property) {
property = property.ToLower();
switch (property) {
case "director":
case "directors":
ByDirector(movs);
break;
default:
SortBy((File[])movs, property);
break;
}
}
protected static void ByDirector(Movie[] movs) {
Array.Sort(movs, (m1, m2) => m1.Directors.First().CompareTo(m2.Directors.First())); //This is a pretty useless sort.
}