/// <summary> /// Add a song to a playlist. /// </summary> /// <param name="songID">The songID</param> /// <param name="playListID">The PlaylistID</param> /// <param name="userKey">client mobile key.</param> /// <returns>The outcome of the opearation.</returns> public Response MobileAddSongToPlaylist(int songID, int playListID, long userKey) { int venueID = -1; int songExists; int mobileID; using (DatabaseConnectivity db = new DatabaseConnectivity()) { // Try to establish a database connection Response r = db.OpenConnection(); if (r.error) return (Response)Common.LogError(r.message, Environment.StackTrace, r, 0); // Convert the userKey to MobileID r = MobileKeyToID(userKey, out mobileID, db); if (r.error) return (Response)Common.LogError(r.message, Environment.StackTrace, r, 0); // Make sure the client isn't already logged out. r = MobileCheckStatus(mobileID, "!0", db); if (r.error) return r; // Get the venue information from the playlist in DB. r = db.MobileGetVenueFromPlaylist(playListID, mobileID); if (r.error) return (Response)Common.LogError(r.message, Environment.StackTrace, r, 0); if (!int.TryParse(r.message.Trim(), out venueID)) { r.error = true; r.message = "Could not figure out Venue from DB"; return r; } // Check to see if song exists. r = db.SongExists(venueID, songID); if (r.error) return (Response)Common.LogError(r.message, Environment.StackTrace, r, 0); if (!int.TryParse(r.message.Trim(), out songExists)) { r.error = true; r.message = "Could not find song in venue's library."; return r; } // Object to represent the song to add. Song song = new Song(); song.ID = songID; // Get the current songs in the playlist. List<Song> songs; r = db.MobileGetSongsFromPlaylist(playListID, mobileID, out songs); if (r.error) return (Response)Common.LogError(r.message, Environment.StackTrace, r, 0); // Check to see if the song already exists. foreach (Song s in songs) { if (s.ID == song.ID) { r.error = true; r.message = "You already have that song in this playlist"; return r; } } // Otherwise, add this to the playlist. songs.Add(song); r = db.MobileSetPlaylistSongs(playListID, mobileID, songs); if (r.error) return (Response)Common.LogError(r.message, Environment.StackTrace, r, 0); return r; } }
/// <summary> /// Add a song request to the queue. Automatically figures out of the user is already in the queue or not. /// If the song request userID is > 0, matches based on registered user id. /// If the song request userID is 0, matches based in registered user name. /// If the song request uesrID is less than 0, matches based on temporary user name. /// Automaticlaly creates the temporary user if needed. /// </summary> /// <param name="sr">The song request to add.</param> /// <param name="queueIndex">The position to add the user in, if they don't already have song requests in the queue.</param> /// <param name="DJKey">The DJ's assigned key.</param> /// <returns>The outcome of the operation. If the operation is sucessful, the client ID number is returned in result and message.</returns> public Response DJAddQueue(SongRequest sr, int queueIndex, long DJKey) { int DJID = -1; int songID = -1; int clientID = -1; using (DatabaseConnectivity db = new DatabaseConnectivity()) { // Try to establish a database connection Response r = db.OpenConnection(); if (r.error) return r; // Convert the DJKey to a DJID r = DJKeyToID(DJKey, out DJID, db); if (r.error) return r; // Make sure the DJ isn't logged out. r = DJValidateStatus(DJID, "2", db); if (r.error) return r; // Check to see if song exists. r = db.SongExists(DJID, sr.songID); if (r.error) return r; // Make sure the songExists method returned a song. if (!int.TryParse(r.message.Trim(), out songID)) { r.error = true; r.message = "Could not find song"; return r; } if (sr.user.userID < -1) sr.user.userID = -1; // when userID == -1, we are dealing with creating an anonmymous user. if (sr.user.userID == -1) { // See if this username exists. r = db.DJValidateTempUserName(sr.user.userName, DJID); if (r.error) return r; // In this case, the username does not exist. if (r.message.Trim().Length == 0) { // Add the tempUser. r = db.DJAddTempUser(sr.user.userName, DJID); if (r.error) return r; // Get the tempUser's ID from the DB. r = db.DJValidateTempUserName(sr.user.userName, DJID); if (r.error) return r; // Parse the ID. if (!int.TryParse(r.message.Trim(), out clientID)) { r.error = true; r.message = "Unable to get the clientID of the new user."; return r; } } // In this case, the username already exists. else { // Get the tempUser's ID from the DB. r = db.DJValidateTempUserName(sr.user.userName, DJID); if (r.error) return r; // Parse the ID. if (!int.TryParse(r.message.Trim(), out clientID)) { r.error = true; r.message = "Unable to get the clientID of the temp user."; return r; } } } // When userID == 0, we look the user up by username instead of userID. else if (sr.user.userID == 0) { r = db.MobileValidateUsername(sr.user.userName); if (r.error) return r; if (!int.TryParse(r.message.Trim(), out clientID)) { r.error = true; r.message = "Client name could not be validated."; return r; } } // If a userID is passed in. else { r = db.MobileValidateID(sr.user.userID); if (r.error) return r; // See if an ID was returned. if (r.message.Trim() == String.Empty) { string s = r.message.Trim(); r.error = true; r.message = "Client ID could not be validated."; return r; } clientID = sr.user.userID; } // Get the current song Requests r = db.GetSongRequests(DJID); if (r.error) return r; string requests = r.message; string newRequests = string.Empty; // If there were no requests, simply add the single request. if (requests.Trim().Length == 0) { newRequests = clientID.ToString() + "~" + sr.songID.ToString(); //r = Common.PushMessageToMobile(sr.user.userID, "queue", db); Common.PushMessageToUsersOfDJ(DJID, "queue", db); r = db.SetSongRequests(DJID, newRequests); return r; } // Since there is a list of requests, call to parse the raw string data into an list of queuesingers. List<queueSinger> queue; r = Common.DBToMinimalList(requests, out queue); if (r.error) return r; // Search to see if the user is already in this list of singers. for (int i = 0; i < queue.Count; i++) { // We found the userID already in here. if (queue[i].user.userID == clientID) { // Loop through the songs to see if the user is already singing this song. for (int j = 0; j < queue[i].songs.Count; j++) { if (queue[i].songs[j].ID == sr.songID) { r.error = true; r.message = "User is already singing that song"; return r; } } // They dont' already have the song in the list, add them to the list Song s = new Song(); s.ID = sr.songID; queue[i].songs.Add(s); Common.MinimalListToDB(queue, out newRequests); r = db.SetSongRequests(DJID, newRequests); if (r.error) return r; Common.PushMessageToMobile(clientID, "queue", db); //Common.PushMessageToUsersOfDJ(DJID, "queue", db); r.message = clientID.ToString(); r.result = clientID; return r; } } // Now they are not in the queue, add them at queueIndex. queueSinger qs = new queueSinger(); qs.songs = new List<Song>(); qs.user = sr.user; qs.user.userID = clientID; Song song = new Song(); song.ID = sr.songID; qs.songs.Add(song); if (queueIndex < 0) queueIndex = 0; if (queueIndex > queue.Count) queueIndex = queue.Count; queue.Insert(queueIndex, qs); Common.MinimalListToDB(queue, out newRequests); r = db.SetSongRequests(DJID, newRequests); if (r.error) return r; Common.PushMessageToUsersOfDJ(DJID, "queue", db); r.message = clientID.ToString(); r.result = clientID; return r; } }
/// <summary> /// Create a song request. /// </summary> /// <param name="songID">The songID</param> /// <param name="userKey">client mobile key.</param> /// <returns>The outcome of the opearation.</returns> public Response MobileSongRequest(int songID, long userKey) { int venueID = -1; int songExists; int mobileID; using (DatabaseConnectivity db = new DatabaseConnectivity()) { // Try to establish a database connection Response r = db.OpenConnection(); if (r.error) return (Response)Common.LogError(r.message, Environment.StackTrace, r, 0); // Convert the userKey to MobileID r = MobileKeyToID(userKey, out mobileID, db); if (r.error) return (Response)Common.LogError(r.message, Environment.StackTrace, r, 0); // Make sure the client isn't already logged out. r = MobileCheckStatus(mobileID, "!0", db); if (r.error) return r; // Get the venueID r = MobileGetVenue(mobileID, db); if (r.error) return (Response)Common.LogError(r.message, Environment.StackTrace, r, 0); venueID = r.result; // Make sure the venue is accepting songs. r = VenueCheckStatus(venueID, "2", db); if (r.error) return r; // Check to see if song exists. r = db.SongExists(venueID, songID); if (r.error) return (Response)Common.LogError(r.message, Environment.StackTrace, r, 0); if (!int.TryParse(r.message.Trim(), out songExists)) { r.error = true; r.message = "Could not find song"; return r; } // Get the current song Requests r = db.GetSongRequests(venueID); if (r.error) return (Response)Common.LogError(r.message, Environment.StackTrace, r, 0); string requests = r.message; string newRequests = string.Empty; if (requests.Trim().Length == 0) { requests = mobileID.ToString() + "~" + songID.ToString(); r = db.SetSongRequests(venueID, requests); if (r.error) return r; r = Common.PushMessageToMobile(mobileID, "queue", db); if (r.error) Common.LogError(r.message, Environment.StackTrace, null, 0); return r; } // Since there is a list of requests, call to parse the raw string data into an list of queuesingers. List<queueSinger> queue; r = Common.DBToMinimalList(requests, out queue); if (r.error) return (Response)Common.LogError(r.message, Environment.StackTrace, r, 0); // Search to see if the user is already in this list of singers. for (int i = 0; i < queue.Count; i++) { // We found the userID already in here. if (queue[i].user.userID == mobileID) { // Loop through the songs to see if the user is already singing this song. for (int j = 0; j < queue[i].songs.Count; j++) { if (queue[i].songs[j].ID == songID) { r.error = true; r.message = "You are already in queue to sing that song"; return r; } } // They dont' already have the song in the list, add them to the list Song s = new Song(); s.ID = songID; queue[i].songs.Add(s); Common.MinimalListToDB(queue, out newRequests); r = db.SetSongRequests(venueID, newRequests); if(r.error) return (Response)Common.LogError(r.message, Environment.StackTrace, r, 0); Common.PushMessageToUsersOfDJ(venueID, "queue", db); return r; } } queueSinger qs = new queueSinger(); qs.songs = new List<Song>(); User u = new User(); u.userID = mobileID; qs.user = u; Song song = new Song(); song.ID = songID; qs.songs.Add(song); queue.Add(qs); Common.MinimalListToDB(queue, out newRequests); r = db.SetSongRequests(venueID, newRequests); if(r.error) return (Response)Common.LogError(r.message, Environment.StackTrace, r, 0); Common.PushMessageToUsersOfDJ(venueID, "queue", db); return r; } }
/// <summary> /// Allows a client to search for songs in a venue. If title or artist are blank, they are not used in the search. /// </summary> /// <param name="title">The title.</param> /// <param name="artist">The artist.</param> /// <param name="start">The index of the first result.</param> /// <param name="count">The number of results to return.</param> /// <param name="venueID">The venueID to search from.</param> /// <param name="userKey">The userKey of the mobile user.</param> /// <returns>The outcome of the operation.</returns> public List<Song> MobileSongSearch(string title, string artist, int start, int count, int venueID, long userKey) { int venueStatus; int mobileID = -1; List<Song> songs = new List<Song>(); using (DatabaseConnectivity db = new DatabaseConnectivity()) { // Try to establish a database connection Response r = db.OpenConnection(); if (r.error) return (List<Song>)Common.LogError(r.message, Environment.StackTrace, null, 0); // Convert the userKey to MobileID r = MobileKeyToID(userKey, out mobileID, db); if (r.error) return (List<Song>)Common.LogError(r.message, Environment.StackTrace, null, 0); // Make sure the client isn't already logged out. r = MobileCheckStatus(mobileID, "!0", db); if (r.error) return (List<Song>)Common.LogError(r.message, Environment.StackTrace, null, 0); // Make sure the venueID exists. r = db.DJGetStatus(venueID); if (r.error) return (List<Song>)Common.LogError(r.message, Environment.StackTrace, null, 0); if (!int.TryParse(r.message.Trim(), out venueStatus)) return (List<Song>)Common.LogError("MobileSongSeach venueID parse fail (bad venueID given?)", Environment.StackTrace, null, 0); // Complete the search. r = db.MobileSearchSongs(title.Trim(), artist.Trim(), venueID, start, count); if (r.error) return (List<Song>)Common.LogError(r.message, Environment.StackTrace, null, 0); if (r.message.Trim() == string.Empty) return songs; string[] songLines = r.message.Trim().Split('\n'); foreach (string songLine in songLines) { string[] songParts = Common.splitByDel(songLine); Song song = new Song(); int id; if (!int.TryParse(songParts[0], out id)) return (List<Song>)Common.LogError("Exception in MobileListSongsSQL: could not parse song id", Environment.StackTrace, null, 0); song.ID = id; song.title = songParts[1]; song.artist = songParts[2]; song.duration = int.Parse(songParts[3]); Common.LoadSongRating(ref song, mobileID, db); song.pathOnDisk = ""; songs.Add(song); } return songs; } }
/// <summary> /// Get the songs from a playlist. Songs saved in message. /// </summary> /// <param name="playListID">The playlist ID.</param> /// <param name="userID">The mobile client's ID.</param> /// <returns>The outcome of the operation.</returns> public Response MobileGetSongsFromPlaylist(int playListID, int userID, out List<Song> songs) { Response r = new Response(); songs = new List<Song>(); SqlCommand cmd = new SqlCommand("select SongID from PlayListSongs where PlayListID = @playListID;", con); cmd.Parameters.AddWithValue("@playListID", playListID); try { using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Song song = new Song(); song.ID = int.Parse(reader[0].ToString()); songs.Add(song); } } return r; } catch (Exception e) { r.error = true; r.message = "Exception in MobileGetSongsFromPlaylist: " + e.Message; return r; } }
/// <summary> /// List all of a DJ's songs. /// </summary> /// <param name="DJID">The DJ's ID.</param> /// <param name="songs">Out parameter that will store all the songs.</param> /// <returns>The outcome of the operation.</returns> public Response DJListSongs(int DJID, out List<Song> songs) { Response r = new Response(); songs = new List<Song>(); SqlCommand cmd = new SqlCommand("select * from DJSongs where DJListID = @DJID;", con); cmd.Parameters.AddWithValue("@DJID", DJID); try { using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Song song = new Song(); song.ID = int.Parse(reader["SongID"].ToString()); song.title = reader["Title"].ToString(); song.artist = reader["Artist"].ToString(); song.pathOnDisk = reader["PathOnDisk"].ToString(); song.duration = int.Parse(reader["Duration"].ToString()); songs.Add(song); } } return r; } catch (Exception e) { r.error = true; r.message = "Exception in DJListSongs: " + e.Message; return r; } }
/// <summary> /// Loads the song rating into the referenced song. /// </summary> /// <param name="song">The referenced song to set the song rating of.</param> /// <param name="mobileID">The ID of the mobile user.</param> /// <param name="db">Connectivity of the database.</param> /// <returns>The outcome of the operation.</returns> public static Response LoadSongRating(ref Song song, int mobileID, DatabaseConnectivity db) { int rating; Response r = db.MobileGetSongRating(mobileID, song.ID); if (r.error) return r; if (r.message.Trim().Length == 0) { song.rating = -1; return r; } if (!int.TryParse(r.message.Trim(), out rating)) { LogError("Load song rating fail, the message is: '" + r.message.Trim() + "'", "", null, 2); r.error = true; r.message = "Could not parse rating"; return r; } song.rating = rating; r.message = rating.ToString(); r.result = rating; return r; }
/// <summary> /// Takes the database representaiton of the queue and expands it into a queue that is fully /// filled except for song ratings. /// </summary> /// <param name="raw">The databse representation of the queue.</param> /// <param name="queue">Out object representation of the queue.</param> /// <param name="DJID">The DJID of the DJ.</param> /// <param name="db">An object that allows for database connectivity.</param> /// <returns>The outcome of the operation.</returns> public static Response DBToFullList(string raw, out List<queueSinger> queue, int DJID, DatabaseConnectivity db) { queue = new List<queueSinger>(); Response r = new Response(); int count = 0; string[] clientRequests = raw.Split('`'); for (int i = 0; i < clientRequests.Length; i++) { string[] parts = clientRequests[i].Split('~'); if (parts.Length == 0) { r.error = true; r.message = "Error in DBtoList 1"; return r; } queueSinger qs = new queueSinger(); qs.songs = new List<Song>(); User u = new User(); u.userID = int.Parse(parts[0]); if (u.userID < 0) r = db.DJGetTempUserName(u.userID, DJID); else r = db.MobileIDtoUsername(u.userID); if (r.error) return r; if (r.message.Trim().Length == 0) { r.error = true; r.message = "DB Username lookup exception in DBToFullList!"; return r; } u.userName = r.message.Trim(); qs.user = u; for (int j = 1; j < parts.Length; j++) { Song s = new Song(); r = GetSongInformation(int.Parse(parts[j]), DJID, -1, out s, db, true); if (r.error) return r; qs.songs.Add(s); } queue.Add(qs); count++; } return r; }
/// <summary> /// Takes the database representation of the queue and expands it into a queue that is minimally /// filled with data. The queue is only filled to contain user IDs and song IDs. /// </summary> /// <param name="raw">The database representation of the queue.</param> /// <param name="queue">Out object represenation of the queue.</param> /// <returns>The outcome of the operation.</returns> public static Response DBToMinimalList(string raw, out List<queueSinger> queue) { int count = 0; Response r = new Response(); queue = new List<queueSinger>(); string[] clientRequests = raw.Split('`'); for (int i = 0; i < clientRequests.Length; i++) { string[] parts = clientRequests[i].Split('~'); if (parts.Length == 0) { r.error = true; r.message = "Error in DBtoList 1"; return r; } queueSinger qs = new queueSinger(); qs.songs = new List<Song>(); User u = new User(); u.userID = int.Parse(parts[0]); qs.user = u; for (int j = 1; j < parts.Length; j++) { Song s = new Song(); s.ID = int.Parse(parts[j]); qs.songs.Add(s); } queue.Add(qs); count++; } return r; }
/// <summary> /// Gets the song information of a song from the database. If mobileID is -1, song rating is not included. /// If includePath is set to true, the pathondisk is set, otherwise it is not set. /// </summary> /// <param name="songID">The songID of the song.</param> /// <param name="venueID">The ID of the venue that has this song.</param> /// <param name="mobileID">The mobile ID of client.</param> /// <param name="song">The out parameter that is filled with song information.</param> /// <param name="db">The conenctivity to the database.</param> /// <param name="includePath">Whether or not to include the pathOnDisk in the song.</param> /// <returns>The outcome of the operation.</returns> public static Response GetSongInformation(int songID, int venueID, int mobileID, out Song song, DatabaseConnectivity db, bool includePath = false) { song = new Song(); Response r = db.SongInformation(venueID, songID); if (r.error) return r; if (r.message.Trim().Length == 0) { r.error = true; r.message = "Could not find song."; return r; } string[] songParts = splitByDel(r.message); if (songParts.Length < 4) { r.error = true; r.message = "Song did not have 4 parts"; return r; } song.ID = songID; song.title = songParts[0]; song.artist = songParts[1]; if (includePath) song.pathOnDisk = songParts[2]; int duration; if (!int.TryParse(songParts[3], out duration)) { r.error = true; r.message = "Could not parse the duration"; return r; } song.duration = duration; if (mobileID == -1) return r; return LoadSongRating(ref song, mobileID, db); }
/// <summary> /// Add a song to a playlist. /// </summary> /// <param name="songID">The songID</param> /// <param name="playListID">The PlaylistID</param> /// <param name="userKey">client mobile key.</param> /// <returns>The outcome of the opearation.</returns> public Response MobileAddSongToPlaylist(int songID, int playListID, long userKey) { int venueID = -1; int songExists; int mobileID; using (DatabaseConnectivity db = new DatabaseConnectivity()) { // Try to establish a database connection ExpResponse r = db.OpenConnection(); if (r.error) return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Mobile); // Convert the userKey to MobileID r = MobileKeyToID(userKey, out mobileID, db); if (r.error) return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Mobile); // Make sure the client isn't already logged out. bool validStatus; r = MobileCheckStatus(mobileID, "!0", db, out validStatus); if (r.error) return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Mobile); if (!validStatus) { r.setErMsg(true, Messages.ERR_STATUS_IS_NOT_IN); return r; } // Get the venue information from the playlist in DB. r = db.MobileGetVenueFromPlaylist(playListID, mobileID); if (r.error) return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Mobile); if (!int.TryParse(r.message.Trim(), out venueID)) { r.setErMsgStk(true, "Could not figure out Venue from DB", Environment.StackTrace); return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Mobile); } // Check to see if song exists. r = db.SongExists(venueID, songID); if (r.error) return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Mobile); if (!int.TryParse(r.message.Trim(), out songExists)) { r.setErMsg(true, Messages.MSG_SONG_NOT_FOUND); return r; } // Object to represent the song to add. Song song = new Song(); song.ID = songID; // Get the current songs in the playlist. List<Song> songs; r = db.MobileGetSongsFromPlaylist(playListID, mobileID, out songs); if (r.error) return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Mobile); // Check to see if the song already exists. foreach (Song s in songs) { if (s.ID == song.ID) { r.setErMsg(true, Messages.ERR_PLYLST_DUP_SONG); return r; } } // Otherwise, add this to the playlist. songs.Add(song); r = db.MobileSetPlaylistSongs(playListID, mobileID, songs); if (r.error) return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Mobile); return r; } }
/// <summary> /// Generates up to maxSuggestsions song suggestions for a user based off of their song history. /// Suggested songs are from the same artists as songs they have previously sung with a bias /// to artists they have sung more often. Suggestions are always song they have not sung yet. /// </summary> /// <param name="maxSuggestions"></param> /// <param name="mobileID"></param> /// <param name="DJID"></param> /// <param name="songs"></param> /// <param name="db"></param> /// <returns></returns> private ExpResponse SuggestSongsNotSungByMostSungArtists(int maxSuggestions, int mobileID, int DJID, out List<Song> songs, DatabaseConnectivity db) { List<SongAndCount> sAc; songs = new List<Song>(); ExpResponse r; // Get up to 10 unique artists this user has sung, ordered by the most sung first. r = db.MobileGetUniqueArtistsSung(mobileID, 0, 10, out sAc); if (r.error) return r; // Loop through the unique artists. // store total number of song sings in singCount // Store the iteration singCount in tmp for each sAc. int singCount = 0; for (int i = 0; i < sAc.Count; i++) { singCount += sAc[i].count; sAc[i].tmp = singCount; } // Generate a random number between 0 and the number of total sings. // Loop through the artists, if the random number is less than the // store singCount iteration number, choose to suggest a song based off that artist. Random rand = new Random(DateTime.Now.Millisecond); for (int i = 0; i < maxSuggestions; i++) { int rn = rand.Next(0, singCount); for (int j = 0; j < sAc.Count; j++) { if (rn < sAc[j].tmp) { sAc[j].tmp2++; break; } } } // For each artist, if we selected to suggest based off them, do so the number of times requested. foreach (SongAndCount s in sAc) { if (s.tmp2 > 0) { List<int> songIDs; r = db.MobileGetRandomSongsFromExactArtistNeverSung(s.song.artist, DJID, s.tmp2, mobileID, out songIDs); if (r.error) return r; foreach (int id in songIDs) { Song song = new Song(); song.ID = id; songs.Add(song); } } } return r; }
/// <summary> /// Allows a client to search for songs in a venue. If title or artist are blank, they are not used in the search. /// </summary> /// <param name="title">The title.</param> /// <param name="artist">The artist.</param> /// <param name="start">The index of the first result.</param> /// <param name="count">The number of results to return.</param> /// <param name="venueID">The venueID to search from.</param> /// <param name="userKey">The userKey of the mobile user.</param> /// <returns>The outcome of the operation.</returns> public List<Song> MobileSongSearch(string title, string artist, int start, int count, int venueID, long userKey) { int venueStatus; int mobileID = -1; List<Song> songs = new List<Song>(); using (DatabaseConnectivity db = new DatabaseConnectivity()) { // Try to establish a database connection ExpResponse r = db.OpenConnection(); if (r.error) return Common.LogErrorRetGen<List<Song>>(r, null, Common.LogFile.Mobile); // Convert the userKey to MobileID r = MobileKeyToID(userKey, out mobileID, db); if (r.error) return Common.LogErrorRetGen<List<Song>>(r, null, Common.LogFile.Mobile); // Make sure the client isn't already logged out. bool validStatus; r = MobileCheckStatus(mobileID, "!0", db, out validStatus); if (r.error) return Common.LogErrorRetGen<List<Song>>(r, null, Common.LogFile.Mobile); if (!validStatus) { r.setErMsgStk(true, "User: "******" has invalid status", Environment.StackTrace); return Common.LogErrorRetGen<List<Song>>(r, null, Common.LogFile.Mobile); } // Make sure the venueID exists. r = db.DJGetStatus(venueID); if (r.error) return Common.LogErrorRetGen<List<Song>>(r, null, Common.LogFile.Mobile); if (!int.TryParse(r.message.Trim(), out venueStatus)) { r.setErMsgStk(true, "VenueID parse fail", Environment.StackTrace); return Common.LogErrorRetGen<List<Song>>(r, null, Common.LogFile.Mobile); } // Complete the search. r = db.MobileSearchSongs(title.Trim(), artist.Trim(), venueID, start, count); if (r.error) return Common.LogErrorRetGen<List<Song>>(r, null, Common.LogFile.Mobile); if (r.message.Trim() == string.Empty) return songs; string[] songLines = r.message.Trim().Split('\n'); foreach (string songLine in songLines) { string[] songParts = Common.splitByDel(songLine); Song song = new Song(); int id; if (!int.TryParse(songParts[0], out id)) { r.setErMsgStk(true, "Could not parse song ID", Environment.StackTrace); return Common.LogErrorRetGen<List<Song>>(r, null, Common.LogFile.Mobile); } song.ID = id; song.title = songParts[1]; song.artist = songParts[2]; song.duration = int.Parse(songParts[3]); Common.LoadSongRating(ref song, mobileID, db); song.pathOnDisk = ""; songs.Add(song); } return songs; } }
internal ExpResponse MobileGetSongFromTitleArtist(string title, string artist, int DJID, out Song song) { song = null; ExpResponse r = new ExpResponse(); SqlCommand cmd = new SqlCommand("select SongID, Duration from DJSongs where DJListID = @DJID and Title like @title and Artist like @artist;", con); cmd.Parameters.AddWithValue("@DJID", DJID); cmd.Parameters.AddWithValue("@title", title); cmd.Parameters.AddWithValue("@artist", artist); try { using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { song = new Song(); song.ID = reader.GetInt32(0); song.duration = reader.GetInt32(1); song.artist = artist; song.title = title; } } return r; } catch (Exception e) { r.setErMsgStk(true, "Exception in MobileGetSongFromTitleArtist: " + e.Message, e.StackTrace); return r; } }
/// <summary> /// Gets the most or the least popular songs at a venue. /// </summary> /// <param name="venueID">The venue ID, -1 signifies any venue.</param> /// <param name="start">Results start at given index.</param> /// <param name="count">Get count results.</param> /// <param name="date">The date results must be before or after (inclusive)</param> /// <param name="beforeOrOnDate">If true, results must be before this date (inclusive), otherwise after(inclusive)</param> /// <param name="songs">Out list of songs.</param> /// <param name="counts">Out count of how often the songs exist.</param> /// <returns></returns> internal ExpResponse GetMostPopularSongs(int venueID, int start, int count, out List<Song> songs, out List<int> counts) { ExpResponse r = new ExpResponse(); songs = new List<Song>(); counts = new List<int>(); if (count == 0) return r; //select SongID, count(SongID) from MobileSongHistory where venueID > '0' and DateSung > '2010' //group by SongID order by count(SongID) desc offset 2 rows fetch next 3 rows only; SqlCommand cmd = new SqlCommand("select SongID, count(SongID) from MobileSongHistory ", con); if (venueID != -1) { cmd.CommandText += "where VenueID = @venueID "; cmd.Parameters.AddWithValue("@venueID", venueID); } cmd.CommandText += "group by SongID order by count(SongID), NEWID() desc "; cmd.CommandText += "offset @start rows fetch next @count rows only;"; cmd.Parameters.AddWithValue("@start", start); cmd.Parameters.AddWithValue("@count", count); try { using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Song s = new Song(); s.ID = reader.GetInt32(0); songs.Add(s); counts.Add(reader.GetInt32(1)); } } return r; } catch (Exception e) { r.setErMsgStk(true, "Exception in GetMostPopularSongs:" + e.Message, e.StackTrace); return r; } }