/// <summary>
/// Return the song with a given id
/// </summary>
/// <param name="songId">The id of the song to get</param>
/// <returns>The song with the given id, or null if no such movie exists</returns>
public Song GetSong(int songId) {
SqlCommand command = new SqlCommand("SELECT * FROM Song WHERE id =" + songId, connection);
SqlDataReader reader = command.ExecuteReader();
if (reader.Read()) {
string album = reader["album"].ToString();
reader.Close();
command.CommandText = "SELECT * FROM Files WHERE id =" + songId;
reader = command.ExecuteReader();
reader.Read();
Song song = new Song() {
Id = songId,
Album = album,
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 song;
}
reader.Close();
return null;
}
/// <summary>
/// Updates a song to contain new values
/// </summary>
/// <param name="song">The song object containing the new information</param>
/// <returns>true if the new values are succesfully added, false otherwise.</returns>
public bool UpdateSong(Song song, 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 = '" + song.Title.Replace("'", "''") + "', " +
"description = '" + song.Description.Replace("'", "''") + "', " +
"rentPrice = " + song.RentPrice + ", " +
"buyPrice = " + song.BuyPrice + ", " +
"year = " + song.Year + ", " +
"coverURI = '" + song.CoverUri.Replace("'", "''") + "'" +
"WHERE id =" + song.Id +
"UPDATE Song SET album = '" + song.Album.Replace("'", "''") + "' " +
"WHERE id = " + song.Id);
return command.ExecuteNonQuery() > 0;
}
public SuccessFlagId CreateSong(int managerid, int tmpid, string title, string description, int release, string artist, string album, string[] genres, int rentalPrice, int purchasePrice, string coverUri) {
Song song = new Song()
{
Title = title,
Year = (short)release,
Description = description,
Album = album,
RentPrice = rentalPrice,
BuyPrice = purchasePrice,
CoverUri = coverUri
};
//TODO: REMOVE THIS
//throw new ArgumentException("Something is going wrong. heres a print to help you... Title: " + song.Title + "; Year:" + song.Year + "; Desc:" + song.Description + "; Album:" + song.Album + "; RentPrice:" + song.RentPrice + ";Purrprice:" + song.BuyPrice + ";coverURI:" + song.CoverUri);
db.Open();
IList<string> genr = new List<string>(genres);
Song song1 = db.CreateSong(managerid, tmpid, genr, song, new List<string>{artist});
db.Close();
if (song1 == null) {
Utils.BadReq("Ensure you entered a valid tmpId and a valid admin id");
return new SuccessFlagId() { success = false };
} else {
return new SuccessFlagId() {
id = song1.Id,
success = true
};
}
}
/// <summary>
/// Returns all songs purchased by a given user
/// </summary>
/// <param name="userId">The id of the user to return all purchased songs for</param>
/// <returns>All songs purchased by the user</returns>
public IList<Purchase> GetSongs(int userId) {
//Get information on which songs 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 songquery = new SqlCommand("SELECT * FROM Song WHERE id =" + fileId,
connection);
SqlDataReader fr = filequery.ExecuteReader();
SqlDataReader sr = songquery.ExecuteReader();
if (fr.Read()) {
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()))));
}
}
}
}
return purchases;
}
/// <summary>
/// Creates a database record for a song which has been uploaded to the server previously
/// </summary>
/// <param name="managerId">the id of the manager who creates the song record</param>
/// <param name="tmpId">the tmp id the song file has in the system</param>
/// <param name="title">the title of the song</param>
/// <param name="year">The year the song was released</param>
/// <param name="buyPrice">The buy price of the song</param>
/// <param name="rentPrice">The rent price of the song</param>
/// <param name="album">The album the song is featured on</param>
/// <param name="artist">The artist who made the song</param>
/// <param name="genres">The genres the song fits into</param>
/// <param name="description">The description of the song</param>
/// <returns>The Song object including id and uri , or null if the song could not be added to the database</returns>
public Song CreateSong(int managerId, int tmpId, IList<String> genres, Song song, IList<string> artists) {
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('" +
song.Title.Replace("'", "''") + "', " +
song.RentPrice + ", " +
song.BuyPrice + ", '" +
uri.Replace("'", "''") + "', " +
song.Year + ", '" +
song.Description.Replace("'", "''") + "', '" +
song.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 Song VALUES(" + fileId + ", '" + song.Album + "')";
command.ExecuteNonQuery();
//Add genres to the file if any exist
if (genres.Count() > 0)
AddAllGenres(fileId, genres);
if (artists.Count() > 0)
AddAllArtists(fileId, artists);
command.CommandText = "DELETE FROM StagedFile WHERE id=" + tmpId;
command.ExecuteNonQuery();
song.Uri = uri;
song.Id = fileId;
return song;
}
}
return null;
}
protected static void ByAlbum(Song[] songs) {
Array.Sort(songs, (s1, s2) => s1.Album.CompareTo(s2.Album));
}
protected static void ByArtist(Song[] songs) {
Array.Sort(songs, (s1, s2) => s1.Artists.First().CompareTo(s2.Artists.First()));
}
public static void SortBy(Song[] songs, String property) {
property = property.ToLower();
switch (property) {
case "album":
ByAlbum(songs);
break;
case "artist":
ByArtist(songs);
break;
default :
SortBy((File[]) songs, property);
break;
}
}