/// <summary>
/// Close the connection to the database. Call this when you are done
/// using this connection. Alternatively dispose of the resource to
/// automatically call this method.
/// </summary>
/// <returns></returns>
public Response CloseConnection()
{
Response r = new Response();
try
{
con.Close();
return r;
}
catch (Exception e)
{
r.error = true;
r.message = "Error opening SQL connection" + e.Message;
return r;
}
}
/// <summary>
/// Returns a database valid column name for each possible clause keyword.
/// </summary>
/// <param name="a">A single select statement for an achievement.</param>
/// <returns>The column name.</returns>
private static string ClauseKeywordToString(AchievementSelect a)
{
string keyword = string.Empty;
Response r = new Response();
switch (a.clauseKeyword)
{
case ClauseKeyword.Artist:
keyword = "Artist";
break;
case ClauseKeyword.Title:
keyword = "Title";
break;
case ClauseKeyword.SongID:
keyword = "SongID";
break;
default:
return string.Empty;
}
return keyword;
}
/// <summary>
/// Change a user's password.
/// </summary>
/// <param name="username">The username.</param>
/// <param name="oldPassword">The old password.</param>
/// <param name="newPassword">The new password.</param>
/// <param name="role">The role: DJ or Mobile</param>
/// <returns>The outcome of the operation.</returns>
public Response ChangePassword(int ID, string role, string newPassword)
{
Response r = new Response();
if (!role.Equals("DJ") && !role.Equals("Mobile"))
{
r.error = true;
r.message = "Bad role";
return r;
}
using (DatabaseConnectivity db = new DatabaseConnectivity())
{
// Try to establish a database connection
r = db.OpenConnection();
if (r.error)
return r;
// Get the salt from the database and salt/hash the password.
string salt = Common.CreateSalt(16);
if (role == "DJ")
r = db.DJSetSalt(ID, salt);
else
r = db.MobileSetSalt(ID, salt);
if (r.error)
return r;
string saltHashPassword = Common.CreatePasswordHash(newPassword, salt);
if (role == "DJ")
r = db.DJSetPassword(ID, saltHashPassword);
else
r = db.MobileSetPassword(ID, saltHashPassword);
if (r.error)
return r;
return r;
}
}
/// <summary>
/// Change a user's email.
/// </summary>
/// <param name="username">The username.</param>
/// <param name="password">The password.</param>
/// <param name="newEmail">The new email address.</param>
/// <param name="role">The role, DJ or mobile.</param>
/// <returns>The outcome of the operation.</returns>
public Response ChangeEmail(int ID, string role, string newEmail)
{
Response r = new Response();
if (!role.Equals("DJ") && !role.Equals("Mobile"))
{
r.error = true;
r.message = "Bad role";
return r;
}
// Validate the email address.
try
{
var address = new System.Net.Mail.MailAddress(newEmail);
}
catch
{
r.error = true;
r.message = "Email address is not valid";
return r;
}
using (DatabaseConnectivity db = new DatabaseConnectivity())
{
// Try to establish a database connection
r = db.OpenConnection();
if (r.error)
return r;
if (role == "DJ")
r = db.DJSetEmail(ID, newEmail);
else
r = db.MobileSetEmail(ID, newEmail);
if (r.error)
return r;
return r;
}
}
/// <summary>
/// Get the Device ID of a Mobile Client's phone.
/// </summary>
/// <param name="mobileID">The mobile client id.</param>
/// <param name="deviceID">Outputs the device id of the phone.</param>
/// <returns>Response indicating the success of the operation.</returns>
public Response MobileGetDeviceID(int mobileID, out string deviceID)
{
deviceID = String.Empty;
Response r = new Response();
try
{
SqlCommand cmd = new SqlCommand("select DeviceID from MobileUsers where ID = @mobileID", con);
cmd.Parameters.AddWithValue("@mobileID", mobileID);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
deviceID = reader["DeviceID"].ToString(); ;
return r;
}
r.error = true;
r.message = "MobileID invalid in MobileGetDeviceID";
return r;
}
}
catch (Exception e)
{
r.error = true;
r.message = "Exception in MobileSetDeviceID: " + e.Message;
return r;
}
}
public Response MobileClearPasswordResetID(int mobileID, string value)
{
Response r = new Response();
SqlCommand cmd = new SqlCommand("delete from MobilePasswordResets where ID = @mobileID or Value = @value;", con);
cmd.Parameters.AddWithValue("@mobileID", mobileID);
cmd.Parameters.AddWithValue("@value", value);
try
{
cmd.ExecuteNonQuery();
return r;
}
catch (Exception e)
{
r.error = true;
r.message = "Exception in MobileClearPasswordResetID: " + e.Message;
return r;
}
}
/// <summary>
/// A generic way to execute a non-query SQL command.
/// </summary>
/// <param name="cmd">The command.</param>
/// <returns>The outcome of the operation. Resposne.Result will contain the number of affected rows.</returns>
private Response DBNonQuery(SqlCommand cmd)
{
Response r = new Response();
r.result = 0;
try
{
cmd.Connection = con;
r.result = cmd.ExecuteNonQuery();
return r;
}
catch (Exception e)
{
r.error = true;
r.message = "Exception in DBNonQuery\n " + e.Message + e.StackTrace;
return r;
}
}
/// <summary>
/// Rate a song.
/// </summary>
/// <param name="songID">The songID.</param>
/// <param name="rating">The rating -1 to 5.</param>
/// <param name="venueID">The venueID of the song.</param>
/// <param name="userKey">client mobile key.</param>
/// <returns>The outcome of the opearation.</returns>
public Response MobileRateSong(int songID, int rating, int venueID, long userKey)
{
int mobileID = -1;
int venueStatus;
int songExists;
using (DatabaseConnectivity db = new DatabaseConnectivity())
{
Response r = new Response();
if (rating < -1 || rating > 5)
{
r.error = true;
r.message = "Rating must be between -1 and 5 (inclusive).";
return r;
}
// Try to establish a database connection
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 (Response)Common.LogError(r.message, Environment.StackTrace, r, 0);
// Make sure the venueID exists.
r = db.DJGetStatus(venueID);
if (r.error)
return (Response)Common.LogError(r.message, Environment.StackTrace, r, 0);
if (!int.TryParse(r.message.Trim(), out venueStatus))
{
r.error = true;
r.message= "MobileGetPlayLists venueID parse fail (Bad venueID given?)";
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;
}
// Set the song rating.
r = db.MobileSetSongRating(mobileID, songID, rating);
if (r.error)
return (Response)Common.LogError(r.message, Environment.StackTrace, r, 0);
return r;
}
}
/// <summary>
/// A generic way to execute a query on the database.
/// </summary>
/// <param name="cmd">The command.</param>
/// <param name="columns">The columns of results requested.</param>
/// <returns>The outcome of the operation.</returns>
private Response DBQuery(SqlCommand cmd, string[] columns)
{
Response r = new Response();
r.result = 0;
try
{
cmd.Connection = con;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
r.result++;
for (int i = 0; i < columns.Length - 1; i++)
r.message += reader[columns[i]].ToString().Trim() + Common.DELIMINATOR;
if (columns.Length > 0)
r.message += reader[columns[columns.Length - 1]].ToString().Trim();
r.message += "\n";
}
}
return r;
}
catch (Exception e)
{
r.error = true;
r.message = "Exception in DBQuery: " + e.Message;
return r;
}
}
public Response ValidatePasswordResetKey(string key, bool isDJ, out int ID)
{
Response r = new Response();
ID = -1;
using (DatabaseConnectivity db = new DatabaseConnectivity())
{
// Try to establish a database connection
r = db.OpenConnection();
if (r.error)
return r;
if(isDJ)
{
r = db.DJGetPasswordResetID(key, out ID);
if (r.error)
return r;
}
else
{
r = db.MobileGetPasswordResetID(key, out ID);
if (r.error)
return r;
}
return r;
}
}
public Response DJSetPasswordReset(int DJID, string value)
{
Response r = new Response();
SqlCommand cmd = new SqlCommand("delete from DJPasswordResets where ID = @ID;", con);
cmd.Parameters.AddWithValue("@ID", DJID);
cmd.ExecuteNonQuery();
SqlCommand cmd2 = new SqlCommand("insert into DJPasswordResets(ID, Value) values (@ID, @value);", con);
cmd2.Parameters.AddWithValue("@ID", DJID);
cmd2.Parameters.AddWithValue("@value", value);
cmd2.ExecuteNonQuery();
try
{
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
return r;
}
catch (Exception e)
{
r.error = true;
r.message = "Exception in DJSetPasswordReset: " + e.Message;
return r;
}
}
/// <summary>
/// Update a DJ's email address.
/// </summary>
/// <param name="DJID">The DJ's unique ID.</param>
/// <param name="email"></param>
/// <returns>The otucome of the operation.</returns>
public Response DJSetEmail(int DJID, string email)
{
Response r = new Response();
SqlCommand cmd = new SqlCommand("Update DJUsers set Email = @email where ID = @ID;", con);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@ID", DJID);
try
{
r.result = cmd.ExecuteNonQuery();
return r;
}
catch (Exception e)
{
r.error = true;
r.message = "Exception in DJSetEmail: " + e.Message;
return r;
}
}
/// <summary>
/// Remove the given songs from the DJ's library.
/// </summary>
/// <param name="songs">The songs to add.</param>
/// <param name="DJID">The DJ's ID.</param>
/// <returns>The outcome of the operation.</returns>
public Response DJRemoveSongs(List<Song> songs, int DJID)
{
int songsNotFound = 0;
int songsRemoved = 0;
Response r = new Response();
foreach (Song s in songs)
{
SqlCommand cmd = new SqlCommand("delete from DJSongs where DJListID = @DJID and Title = @title and Artist = @artist and PathOnDisk = @pathOnDisk;");
cmd.Parameters.AddWithValue("@DJID", DJID);
cmd.Parameters.AddWithValue("@title", s.title);
cmd.Parameters.AddWithValue("@artist", s.artist);
cmd.Parameters.AddWithValue("@pathOnDisk", s.pathOnDisk);
r = DBNonQuery(cmd);
if (r.error)
return r;
if (r.result == 0)
songsNotFound++;
else
songsRemoved++;
}
if (songsNotFound > 0)
r.message = "Warning: " + songsNotFound + " song(s) in the list were not found and thus were not removed";
r.result = songsRemoved;
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>
/// Get all the DJ usernames associated with this email address.
/// </summary>
/// <param name="email">The email address.</param>
/// <param name="usernames">Out usernames.</param>
/// <returns>The outcome of the operation.</returns>
public Response DJGetUsernamesByEmail(string email, out List<string> usernames)
{
usernames = new List<string>();
Response r = new Response();
SqlCommand cmd = new SqlCommand("select Username from DJUsers where Email = @email ;", con);
cmd.Parameters.AddWithValue("@email", email);
try
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
usernames.Add(reader[0].ToString());
}
return r;
}
}
catch (Exception e)
{
r.error = true;
r.message = "Exception in DJGetUsernamesByEmail: " + e.Message;
return r;
}
}
/// <summary>
/// Get the password salt associated with a DJ.
/// </summary>
/// <param name="username">The DJ's username</param>
/// <param name="salt">Out parameter for the salt.</param>
/// <returns>The outcome of the oepration.</returns>
public Response DJGetSalt(string username, out string salt)
{
salt = string.Empty;
Response r = new Response();
SqlCommand cmd = new SqlCommand("select Salt from DJUsers where Username = @username;", con);
cmd.Parameters.AddWithValue("@username", username);
try
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
salt = reader[0].ToString();
return r;
}
else
{
r.error = true;
r.message = "Error in DJGetSalt: Username could not be found";
return r;
}
}
}
catch (Exception e)
{
r.error = true;
r.message = "Exception in DJGetSalt: " + e.Message;
return r;
}
}
/// <summary>
/// A generic way to execute a scalar operation on the database.
/// </summary>
/// <param name="cmd">The command.</param>
/// <returns>The first fow of the result as an integer is stored in r.result.</returns>
private Response DBScalar(SqlCommand cmd)
{
Response r = new Response();
try
{
cmd.Connection = con;
var v = cmd.ExecuteScalar();
r.result = int.Parse(v.ToString());
return r;
}
catch (Exception e)
{
r.error = true;
r.message = "Exception in DBScalar\n " + e.Message + e.StackTrace;
return r;
}
}
public Response MobileGetPasswordResetID(string value, out int mobileID)
{
Response r = new Response();
SqlCommand cmd = new SqlCommand("select ID from MobilePasswordResets where Value = @value;", con);
cmd.Parameters.AddWithValue("@value", value);
try
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
mobileID = int.Parse(reader[0].ToString());
}
else
{
mobileID = -1;
}
}
return r;
}
catch (Exception e)
{
r.error = true;
r.message = "Exception in MobileGetPasswordResetID: " + e.Message;
mobileID = -1;
return r;
}
}
/// <summary>
/// "Weblogin" to the system. Returns the user's ID upon success.
/// </summary>
/// <param name="username">The username</param>
/// <param name="password">The password</param>
/// <param name="role">The role, DJ or Mobile</param>
/// <param name="ID">Our parameter of the user ID.</param>
/// <returns>The outcome of the operation.</returns>
public Response Login(string username, string password, string role, out int ID)
{
ID = 0;
Response r = new Response();
if (!role.Equals("DJ") && !role.Equals("Mobile"))
{
r.error = true;
r.message = "Bad role";
return r;
}
using (DatabaseConnectivity db = new DatabaseConnectivity())
{
// Try to establish a database connection
r = db.OpenConnection();
if (r.error)
return r;
// Get the salt from the database and salt/hash the password.
string salt;
if (role == "DJ")
r = db.DJGetSalt(username, out salt);
else
r = db.MobileGetSalt(username, out salt);
if (r.error)
return r;
string saltHashPassword = Common.CreatePasswordHash(password, salt);
// Check validity of username/password.
if (role == "DJ")
r = db.DJValidateUsernamePassword(username, saltHashPassword);
else
r = db.MobileValidateUsernamePassword(username, saltHashPassword);
if (r.error)
return r;
// If the username/password couldn't be found, inform user.
if (r.message.Trim() == string.Empty)
{
r.error = true;
r.message = "Username/Password is incorrect.";
return r;
}
// Get the ID
if (!int.TryParse(r.message.Trim(), out ID))
{
r.error = true;
r.message = "Exception in ChangeEmail: Unable to parse ID from DB!";
return r;
}
return r;
}
}
/// <summary>
/// Update a DJ's salt.
/// </summary>
/// <param name="DJID">The DJ's unique ID.</param>
/// <param name="salt">The new salt.</param>
/// <returns>The outcome of the operation.</returns>
public Response DJSetSalt(int DJID, string salt)
{
Response r = new Response();
SqlCommand cmd = new SqlCommand("Update DJUsers set Salt = @salt where ID = @ID;", con);
cmd.Parameters.AddWithValue("@salt", salt);
cmd.Parameters.AddWithValue("@ID", DJID);
try
{
r.result = cmd.ExecuteNonQuery();
return r;
}
catch (Exception e)
{
r.error = true;
r.message = "Exception in DJSetPassword: " + e.Message;
return r;
}
}
/// <summary>
/// Starts the password reset process for users who forgot their passwords.
/// </summary>
/// <param name="email">The email address of the user.</param>
/// <param name="key">Out parameter for the unique key this user will temporarily be associated with.</param>
/// <param name="role">The role: DJ or Mobile</param>
/// <returns>The outcome of the operation.</returns>
public Response StartPasswordReset(string email, string username, bool isDJ, string websiteAddress)
{
Response r = new Response();
using (DatabaseConnectivity db = new DatabaseConnectivity())
{
// Try to establish a database connection
r = db.OpenConnection();
if (r.error)
return r;
int ID;
if (isDJ)
{
r = db.DJValidateUsernameEmail(username, email, out ID);
if (r.error)
return r;
}
else
{
r = db.MobileValidateUsernameEmail(username, email, out ID);
if (r.error)
return r;
}
if(ID == -1)
{
r.error=true;
r.message="Username / email / Are you a DJ incorrect";
return r;
}
string random = Common.CreateSalt(32);
Regex rgx = new Regex("[^a-zA-Z0-9 -]");
random = rgx.Replace(random, "x");
int uniqueIsNegOne = 0;
while (uniqueIsNegOne != -1)
{
if (isDJ)
{
r = db.DJGetPasswordResetID(random, out uniqueIsNegOne);
if (r.error)
return r;
}
else
{
r = db.MobileGetPasswordResetID(random, out uniqueIsNegOne);
if (r.error)
return r;
}
random = Common.CreateSalt(32);
random = rgx.Replace(random, "x");
}
if (isDJ)
{
r = db.DJSetPasswordReset(ID, random);
if (r.error)
return r;
}
else
{
r = db.MobileSetPasswordReset(ID, random);
if (r.error)
return r;
}
try
{
string resetURL = websiteAddress + "?DJ=" + isDJ.ToString() + "&key=" + random;
MailMessage mail = GeneratePasswordResetEmail(email, resetURL);
SmtpClient mailServer = new SmtpClient("smtp.live.com");
mailServer.Port = 25;
mailServer.UseDefaultCredentials = false;
mailServer.Credentials = new System.Net.NetworkCredential(mobiokeUsername, mobiokePassword);
mailServer.EnableSsl = true;
mailServer.Send(mail);
return r;
}
catch (Exception e)
{
r.error = true;
r.message = "Exception in SendEmailWithUsername: " + e.Message;
return r;
}
}
}
/// <summary>
/// Simple test to see if a client can connect to the server.
/// </summary>
/// <param name="s">A strong to reverse.</param>
/// <returns>A response containing the reversed string and string length.</returns>
public Response test(string s)
{
Response r = new Response();
for (int i = s.Length - 1; i >= 0; i--)
r.message += s[i];
r.result = s.Length;
return r;
}
/// <summary>
/// Get the venue that is associated with the mobile ID. Set result and message to the venue if able.
/// </summary>
/// <param name="mobileID">The mobile ID of the client.</param>
/// <param name="db">The databse conenctivity to use.</param>
/// <returns>The outcome of the operation.</returns>
private Response MobileGetVenue(int mobileID, DatabaseConnectivity db)
{
int venueID = -1;
Response r = new Response();
r = db.MobileGetVenue(mobileID);
if (r.error)
return r;
if (!int.TryParse(r.message.Trim(), out venueID))
{
r.error = true;
r.message = "Could not parse venueID from DB";
return r;
}
r.result = venueID;
return r;
}
/// <summary>
/// Get all mobile client ids that are logged into this DJ.
/// </summary>
/// <param name="venueID">The id of the venue/DJ.</param>
/// <param name="clients">Out list of client IDs.</param>
/// <returns>The otucome of the operation.</returns>
public Response DJGetAssociatedClients(int venueID, out List<int> clients)
{
clients = new List<int>();
Response r = new Response();
SqlCommand cmd = new SqlCommand("select ID from MobileUsers where Venue = @venueID;", con);
cmd.Parameters.AddWithValue("@venueID", venueID);
try
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
clients.Add(int.Parse(reader[0].ToString()));
}
}
return r;
}
catch (Exception e)
{
r.error = true;
r.message = "Exception in DJGetAssociatedClients: " + e.Message;
return r;
}
}
/// <summary>
/// Convert the database representation of a queue to the object representation. Fill all fields except for path on disk.
/// </summary>
/// <param name="raw">The database representation.</param>
/// <param name="queue">The out parameter to store the queue in.</param>
/// <param name="DJID">The ID of the venue.</param>
/// <param name="mobileID">The ID of the client.</param>
/// <param name="db">The databse conenctivity to use.</param>
/// <returns>The outcome of the operation.</returns>
private Response DBToNearlyFullList(string raw, out List<queueSinger> queue, int DJID, int mobileID, 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 DJGetQueue!";
return r;
}
u.userName = r.message.Trim();
qs.user = u;
for (int j = 1; j < parts.Length; j++)
{
Song song;
r = Common.GetSongInformation(int.Parse(parts[j]), DJID, mobileID, out song, db);
if (r.error)
return r;
qs.songs.Add(song);
}
queue.Add(qs);
count++;
}
return r;
}
public Response DJValidateUsernameEmail(string username, string email, out int DJID)
{
Response r = new Response();
SqlCommand cmd = new SqlCommand("select ID from DJUsers where Email = @email and Username = @username ;", con);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@username", username);
try
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
DJID = int.Parse(reader[0].ToString());
return r;
}
DJID = -1;
return r;
}
}
catch (Exception e)
{
r.error = true;
r.message = "Exception in DJValidateUsernameEmail: " + e.Message;
DJID = -1;
return r;
}
}
/// <summary>
/// Create a playlist. Returns the ID of the playlist in message.
/// </summary>
/// <param name="name">Playlist Name</param>
/// <param name="venueID">VenueID the playlist is associated with.</param>
/// <param name="userKey">client mobile key.</param>
/// <returns>The outcome of the opearation.</returns>
public Response MobileCreatePlaylist(string name, int venueID, long userKey)
{
Response r = new Response();
if (name.Length < 1 || name.Length > 20)
{
r.error = true;
r.message = "Name must be between 1 and 20 characters.";
return r;
}
int mobileID = -1;
int venueStatus;
using (DatabaseConnectivity db = new DatabaseConnectivity())
{
// Try to establish a database connection
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;
// Make sure the venueID exists.
r = db.DJGetStatus(venueID);
if (r.error)
return (Response)Common.LogError(r.message, Environment.StackTrace, r, 0);
if (!int.TryParse(r.message.Trim(), out venueStatus))
{
r.error = true;
r.message = "Could not validate venue";
if (r.error)
return r;
}
r = db.MobileCreatePlaylist(name, venueID, mobileID, DateTime.Now);
if(r.error)
return (Response)Common.LogError(r.message, Environment.StackTrace, r, 0);
return r;
}
}
/// <summary>
/// Set a setting in the Settings table in the databse.
/// </summary>
/// <param name="name">The name of the setting.</param>
/// <param name="value">Out value of the setting.</param>
/// <returns>The outcome of the operation.</returns>
public Response GetSetting(string name, out string value)
{
value = string.Empty;
Response r = new Response();
SqlCommand cmd = new SqlCommand("select value from Settings where Name = @name ;", con);
cmd.Parameters.AddWithValue("@name", name);
try
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
value = reader[0].ToString();
return r;
}
else
{
r.error = true;
r.message = "Error in MobileGetSalt: Username could not be found";
return r;
}
}
}
catch (Exception e)
{
r.error = true;
r.message = "Exception in MobileGetSalt: " + e.Message;
return r;
}
}
/// <summary>
/// Convert a DJID to a DJKey.
/// </summary>
/// <param name="DJID">The DJID</param>
/// <param name="DJKey">OUT parameter for the DJKey</param>
/// <returns></returns>
private Response DJGenerateKey(int DJID, out long DJKey, DatabaseConnectivity db)
{
DJKey = -1;
Response r = new Response();
System.Security.Cryptography.SHA1 sha = new System.Security.Cryptography.SHA1CryptoServiceProvider();
Random rand = new Random();
byte[] randomBytes = new byte[64];
byte[] result;
long tempKey;
for (; ; )
{
rand.NextBytes(randomBytes);
result = sha.ComputeHash(randomBytes);
tempKey = BitConverter.ToInt64(result, 0);
r = db.DJGetIDFromKey(tempKey);
if (r.error)
return r;
if (r.message.Trim().Length != 0)
continue;
r = db.DJSetKey(DJID, tempKey);
if (r.error)
return r;
DJKey = tempKey;
return r;
}
}
/// <summary>
/// Add songs to a DJ's library. If a song with a matching artist and title exists,
/// the path on disk and duration are updated to the new values. Otherwise, a new
/// song is added to the library.
/// </summary>
/// <param name="songs">List of songs to add to library</param>
/// <param name="DJID">DJ unique identifier</param>
/// <returns>Response encoding the sucess of the operation</returns>
public Response DJAddSongsUpdatingDuplicates(List<Song> songs, int DJID)
{
Response r = new Response();
r.result = 0;
try
{
string cmdText = @"Merge DJSongs as target
using (values(@pathOnDisk, @duration))
as source (PathOnDisk, Duration)
on target.Title = @title and target.Artist = @title and DJListID = @DJID
when matched then
update set PathOnDisk = source.PathOnDisk, Duration = source.Duration
when not matched then
insert (DJListID, Title, Artist, PathOnDisk, Duration)
values (@DJID, @title, @artist, @pathOnDisk, @duration);";
SqlCommand cmd = new SqlCommand(cmdText, con);
foreach (Song s in songs)
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@DJID", DJID);
cmd.Parameters.AddWithValue("@title", s.title);
cmd.Parameters.AddWithValue("@artist", s.artist);
cmd.Parameters.AddWithValue("@pathOnDisk", s.pathOnDisk);
cmd.Parameters.AddWithValue("@duration", s.duration);
cmd.Connection = con;
r.result += cmd.ExecuteNonQuery();
}
return r;
}
catch (Exception e)
{
r.error = true;
r.message = "Exception in AddSongs: " + e.Message;
return r;
}
}