/// <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)
{
ExpResponse r = new ExpResponse();
if (!role.Equals("DJ") && !role.Equals("Mobile"))
{
r.setErMsgStk(true, "Bad Role Given", Environment.StackTrace);
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Web);
}
using (DatabaseConnectivity db = new DatabaseConnectivity())
{
// Try to establish a database connection
r = db.OpenConnection();
if (r.error)
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Web);
// 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 Common.LogErrorRetNewMsg(r, Messages.ERR_CRED_WRONG, Common.LogFile.Web);
string saltHashPassword = Common.CreatePasswordHash(newPassword, salt);
if (role == "DJ")
r = db.DJSetPassword(ID, saltHashPassword);
else
r = db.MobileSetPassword(ID, saltHashPassword);
if (r.error)
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Web);
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)
{
ExpResponse r = new ExpResponse();
if (!role.Equals("DJ") && !role.Equals("Mobile"))
{
r.setErMsgStk(true, "Bad Role Given", Environment.StackTrace);
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Web);
}
// Validate the email address.
try
{
var address = new System.Net.Mail.MailAddress(newEmail);
}
catch
{
r.setErMsg(true, Messages.ERR_BAD_EMAIL);
return r;
}
using (DatabaseConnectivity db = new DatabaseConnectivity())
{
// Try to establish a database connection
r = db.OpenConnection();
if (r.error)
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Web);
if (role == "DJ")
r = db.DJSetEmail(ID, newEmail);
else
r = db.MobileSetEmail(ID, newEmail);
if (r.error)
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Web);
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 ExpResponse DBQuery(SqlCommand cmd, string[] columns)
{
ExpResponse r = new ExpResponse();
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.setErMsgStk(true, "Exception in DBQuery:" + e.Message, e.StackTrace);
return r;
}
}
/// <summary>
/// Creates the sql for an achievement statement that involves selecting on oldest or newest. Returns an error of the achievement
/// statement cannot be parsed.
/// </summary>
/// <param name="a">The achievement statement.</param>
/// <param name="DJID">The DJ's unique ID.</param>
/// <param name="cmd">Out sql command to evaluate the statement.</param>
/// <returns>The outcome of the operation.</returns>
private static ExpResponse CreateStatementOldestNewest(AchievementSelect a, int DJID, out SqlCommand cmd)
{
ExpResponse r = new ExpResponse();
cmd = new SqlCommand();
int offset;
if (!int.TryParse(a.selectValue, out offset))
{
r.setErMsgStk(true, "Could not parse offset", Environment.StackTrace);
return r;
}
offset--;
if (offset < 0)
offset = 0;
cmd.CommandText = "select MobileID from MobileSongHistory inner join DJSongs on MobileSongHistory.SongID = DJSongs.SongID ";
cmd.CommandText+= "where DJSongs." + ClauseKeywordToString(a) + " like @clauseKeyword ";
cmd.Parameters.AddWithValue("@clauseKeyword", a.clauseValue);
cmd.CommandText += "and VenueID = @DJID and DateSung >= @minDate and DateSung <= @maxDate ";
cmd.Parameters.AddWithValue("@DJID", DJID);
cmd.Parameters.AddWithValue("@minDate", a.startDate);
cmd.Parameters.AddWithValue("@maxDate", a.endDate);
cmd.CommandText += "order by DateSung " + SelectKeywordToString(a) + " ";
cmd.CommandText += "offset @offset rows fetch next @count rows only;";
cmd.Parameters.AddWithValue("@offset", offset);
cmd.Parameters.AddWithValue("@count", 1);
return r;
}
/// <summary>
/// Sends the username associated with the email address listed to the email address.
/// </summary>
/// <param name="email">The email address of the user.</param>
/// <param name="role">The role: DJ or Mobile</param>
/// <returns>The outcome of the operation.</returns>
public Response SendEmailWithUsername(string email)
{
ExpResponse r = new ExpResponse();
using (DatabaseConnectivity db = new DatabaseConnectivity())
{
// Try to establish a database connection
r = db.OpenConnection();
if (r.error)
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Web);
List<string> DJUsernames;
List<string> mobileUsernames;
r = db.DJGetUsernamesByEmail(email, out DJUsernames);
if (r.error)
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Web);
r = db.MobileGetUsernamesByEmail(email, out mobileUsernames);
if (r.error)
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Web);
if (DJUsernames.Count == 0 && mobileUsernames.Count == 0)
{
r.setErMsg(true, Messages.MSG_EMAIL_NOT_FOUND);
return r;
}
List<string> usernames = new List<string>();
List<string> roles = new List<string>();
foreach (string djUsername in DJUsernames)
{
usernames.Add(djUsername);
roles.Add("DJ");
}
foreach (string mobileUsername in mobileUsernames)
{
usernames.Add(mobileUsername);
roles.Add("Singer");
}
try
{
MailMessage mail = GenerateUsernameEmail(email, usernames, roles);
SmtpClient mailServer = new SmtpClient("smtp.live.com");
mailServer.Port = 25;
mailServer.UseDefaultCredentials = false;
mailServer.Credentials = new System.Net.NetworkCredential(Settings.EMAIL_ADR, Settings.EMAIL_PSWD);
mailServer.EnableSsl = true;
mailServer.Send(mail);
return r;
}
catch (Exception e)
{
r.setErMsgStk(true, e.Message, e.StackTrace);
return Common.LogErrorRetNewMsg(r, Messages.ERR_EMAIL_SERVER, Common.LogFile.Web);
}
}
}
/// <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;
ExpResponse r = new ExpResponse();
if (!role.Equals("DJ") && !role.Equals("Mobile"))
{
r.setErMsgStk(true, "Bad Role Given", Environment.StackTrace);
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Web);
}
using (DatabaseConnectivity db = new DatabaseConnectivity())
{
// Try to establish a database connection
r = db.OpenConnection();
if (r.error)
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Web);
// 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 Common.LogErrorRetNewMsg(r, Messages.ERR_CRED_WRONG, Common.LogFile.Web);
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 Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Web);
// If the username/password couldn't be found, inform user.
if (r.message.Trim() == string.Empty)
{
r.setErMsg(true, Messages.ERR_CRED_WRONG);
return r;
}
// Get the ID
if (!int.TryParse(r.message.Trim(), out ID))
{
r.setErMsgStk(true, "Exception in ChangeEmail: Unable to parse ID from DB!", Environment.StackTrace);
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Web);
}
return r;
}
}
/// <summary>
/// Select a random song from a list of weigted songs.
/// </summary>
/// <param name="rand">Randon number generator to use.</param>
/// <param name="all">The weighted list of songs.</param>
/// <returns>The index of the random song in the collection.</returns>
private ExpResponse selectRandomSong(Random rand, List<KeyValuePair<string[], int>> all, out int index)
{
ExpResponse r = new ExpResponse();
index = 0;
try
{
int totalSongScore = 0;
foreach (KeyValuePair<string[], int> s in all)
totalSongScore += s.Value;
int sum = 0;
// Exception on below line, totalSongScore must be zero for hugo account on rick.
int rn = rand.Next(1, totalSongScore + 1);
foreach (KeyValuePair<string[], int> s in all)
{
sum += s.Value;
if (rn <= sum)
return r;
index++;
}
r.setErMsgStk(true, "Had to select first song", Environment.StackTrace);
return r;
}
catch (Exception e)
{
r.setErMsgStk(true, 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())
{
ExpResponse r = new ExpResponse();
if (rating < -1 || rating > 5)
{
r.setErMsg(true, "Rating must be between -1 and 5 (inclusive).");
return r;
}
// Try to establish a database connection
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;
}
// Make sure the venueID exists.
r = db.DJGetStatus(venueID);
if (r.error)
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Mobile);
if (!int.TryParse(r.message.Trim(), out venueStatus))
{
r.setErMsgStk(true, "MobileGetPlayLists venueID parse fail (Bad venueID given?)", 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.setErMsgStk(true, "Could not find song", Environment.StackTrace);
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Mobile);
}
// Set the song rating.
r = db.MobileSetSongRating(mobileID, songID, rating);
if (r.error)
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Mobile);
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>
internal ExpResponse DJGetSalt(string username, out string salt)
{
salt = string.Empty;
ExpResponse r = new ExpResponse();
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.setErMsgStk(true, "Error in DJGetSalt: Username could not be found", Environment.StackTrace);
return r;
}
}
}
catch (Exception e)
{
r.setErMsgStk(true, "Exception in DJGetSalt:" + e.Message, e.StackTrace);
return r;
}
}
/// <summary>
/// Try to get the the DJID that corresponds to the password reset key value.
/// DJID is set to -1 if it doesn't exist, otherwise is >0.
/// </summary>
/// <param name="value">The unique password reset key.</param>
/// <param name="DJID">Out DJID</param>
/// <returns>The outcome of the operaiton.</returns>
internal ExpResponse DJGetPasswordResetID(string value, out int DJID)
{
ExpResponse r = new ExpResponse();
SqlCommand cmd = new SqlCommand("select ID from DJPasswordResets where Value = @value;", con);
cmd.Parameters.AddWithValue("@value", value);
try
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
DJID = int.Parse(reader[0].ToString());
}
else
{
DJID = -1;
}
}
return r;
}
catch (Exception e)
{
DJID = -1;
r.setErMsgStk(true, "Exception in DJGetPasswordResetID:" + e.Message, e.StackTrace);
return r;
}
}
internal ExpResponse DJGetBannedUsers(int DJID, out List<User> bannedUsers)
{
bannedUsers = new List<User>();
ExpResponse r = new ExpResponse();
SqlCommand cmd = new SqlCommand("select MobileID from DJBannedUsers where DJID = @DJID;", con);
cmd.Parameters.AddWithValue("@DJID", DJID);
try
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
User u = new User();
u.userID = reader.GetInt32(0);
bannedUsers.Add(u);
}
}
return r;
}
catch (Exception e)
{
r.setErMsgStk(true, "Exception in DJGetBannedUsers: " + e.Message, e.StackTrace);
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>
internal ExpResponse DJGetAssociatedClients(int venueID, out List<int> clients)
{
clients = new List<int>();
ExpResponse r = new ExpResponse();
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.setErMsgStk(true, "Exception in DJGetAssociatedClients:" + e.Message, e.StackTrace);
return r;
}
}
internal ExpResponse DJDeleteAchievement(int DJID, int achievementID)
{
ExpResponse r = new ExpResponse();
SqlCommand cmd = new SqlCommand("delete from Achievements where DJID = @DJID", con);
cmd.Parameters.AddWithValue("@DJID", DJID);
if (achievementID != -1)
{
cmd.CommandText += " and ID = @achievementID";
cmd.Parameters.AddWithValue("@achievementID", achievementID);
}
cmd.CommandText += ";";
try
{
r.result = cmd.ExecuteNonQuery();
return r;
}
catch (Exception e)
{
r.setErMsgStk(true, "Exception in DJDeleteAchievement: " + e.Message, e.StackTrace);
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 ExpResponse DBScalar(SqlCommand cmd)
{
ExpResponse r = new ExpResponse();
try
{
cmd.Connection = con;
var v = cmd.ExecuteScalar();
r.result = int.Parse(v.ToString());
return r;
}
catch (Exception e)
{
r.setErMsgStk(true, "Exception in DBScalar:" + e.Message, e.StackTrace);
return r;
}
}
/// <summary>
/// Deletes any trace of a password reset from the DB that either matches the
/// DJID or matches the password reset key value.
/// </summary>
/// <param name="DJID">The DJID</param>
/// <param name="value">The unique password reset key</param>
/// <returns>The outcome of the operation.</returns>
internal ExpResponse DJClearPasswordResetID(int DJID, string value)
{
ExpResponse r = new ExpResponse();
SqlCommand cmd = new SqlCommand("delete from DJPasswordResets where ID = @DJID or Value = @value;", con);
cmd.Parameters.AddWithValue("@DJID", DJID);
cmd.Parameters.AddWithValue("@value", value);
try
{
cmd.ExecuteNonQuery();
return r;
}
catch (Exception e)
{
r.setErMsgStk(true, "Exception in DJClearPasswordResetID:" + e.Message, e.StackTrace);
return r;
}
}
internal ExpResponse DJUnbanUser(int DJID, int mobileID)
{
ExpResponse r = new ExpResponse();
SqlCommand cmd = new SqlCommand("delete from DJBannedUsers where DJID = @DJID and MobileID = @mobileID;", con);
cmd.Parameters.AddWithValue("@DJID", DJID);
cmd.Parameters.AddWithValue("@mobileID", mobileID);
try
{
r.result = cmd.ExecuteNonQuery();
return r;
}
catch (Exception e)
{
r.setErMsgStk(true, "Exception in DJUnbanUser: " + e.Message, e.StackTrace);
return r;
}
}
/// <summary>
/// Validate a DJ's username and email are consistant and exist. If they exist, the DJID is set and > 0,
/// if they do not exist the DJID is set to -1.
/// </summary>
/// <param name="username">DJ's Username</param>
/// <param name="email">DJ's email</param>
/// <param name="DJID">Out DJID</param>
/// <returns>The outcome of the operation</returns>
internal ExpResponse DJValidateUsernameEmail(string username, string email, out int DJID)
{
ExpResponse r = new ExpResponse();
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)
{
DJID = -1;
r.setErMsgStk(true, "Exception in DJValidateUsernameEmail:" + e.Message, e.StackTrace);
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>
internal ExpResponse DJGetUsernamesByEmail(string email, out List<string> usernames)
{
usernames = new List<string>();
ExpResponse r = new ExpResponse();
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.setErMsgStk(true, "Exception in DJGetUsernamesByEmail:" + e.Message, e.StackTrace);
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 ExpResponse DBToNearlyFullList(string raw, out List<queueSinger> queue, int DJID, int mobileID, DatabaseConnectivity db)
{
queue = new List<queueSinger>();
ExpResponse r = new ExpResponse();
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.setErMsgStk(true, "Error in DBToNearlyFullList", Environment.StackTrace);
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.setErMsgStk(true, "DB Username lookup exception in DJGetQueue!", Environment.StackTrace);
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;
}
/// <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>
internal ExpResponse DJListSongs(int DJID, out List<Song> songs)
{
ExpResponse r = new ExpResponse();
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.setErMsgStk(true, "Exception in DJListSongs: " + e.Message, e.StackTrace);
return r;
}
}
/// <summary>
/// Returns a random user from a weigted collection of users.
/// </summary>
/// <param name="rand">The random number generator to use.</param>
/// <param name="all">The weighted collection of users.</param>
/// <returns>The index of the random user in the collection.</returns>
private ExpResponse selectRandWeightedUser(Random rand, List<UserAndSongs> all, out int index)
{
ExpResponse r = new ExpResponse();
index = 0;
try
{
int totalUserScore = 0;
foreach (UserAndSongs uas in all)
totalUserScore += uas.commonScore;
//string mes = "User count: " + all.Count + "\r\n";
//mes += "Total score: " + totalUserScore + "\r\n";
int sum = 0;
int rn = rand.Next(1, totalUserScore + 1);
// mes += "Random generated to be: " + rn + "\r\n";
foreach (UserAndSongs uas in all)
{
sum += uas.commonScore;
//mes += "Iteration " + index + " sum:" + sum + "\r\n";
if (rn <= sum)
{
//mes += "RETURNING with a final rn: " + rn + "sum: " + sum + "\r\n";
//Common.LogSimpleError(Common.LogFile.Debug, mes);
return r;
}
index++;
}
r.setErMsgStk(true, "Had to select first user", Environment.StackTrace);
return r;
}
catch (Exception e)
{
r.setErMsgStk(true, e.Message, e.StackTrace);
return r;
}
}
internal ExpResponse DJModifyAchievement(int DJID, Achievement achievement)
{
ExpResponse r = new ExpResponse();
try
{
MemoryStream streamAchievement = new MemoryStream();
DataContractSerializer achievementSerializer = new DataContractSerializer(typeof(Achievement));
achievementSerializer.WriteObject(streamAchievement, achievement);
byte[] serializedAchievementBytes = streamAchievement.ToArray();
SqlCommand cmd = new SqlCommand("update Achievements set Object = @achievement, Name = @name, ObjectSize = @achievementSize, Visible = @visible where ID = @achievementID and DJID = @DJID;", con);
cmd.Parameters.AddWithValue("@achievement", serializedAchievementBytes);
cmd.Parameters.AddWithValue("@name", achievement.name);
cmd.Parameters.AddWithValue("@achievementSize", serializedAchievementBytes.Length);
cmd.Parameters.AddWithValue("@visible", Common.GetBitFromBool(achievement.visible));
cmd.Parameters.AddWithValue("@achievementID", achievement.ID);
cmd.Parameters.AddWithValue("@DJID", DJID);
r.result = int.Parse(cmd.ExecuteScalar().ToString());
return r;
}
catch (SqlException e)
{
r.setAll(true, "SQLException in DBDJModifyAchievement number: " + e.Number + " " + e.Message, e.StackTrace, e.Number);
return r;
}
catch (Exception e)
{
r.setErMsgStk(true, "Exception in DJModifyAchievement: " + e.Message, e.StackTrace);
return r;
}
}
internal ExpResponse AwardAchievement(int mobileID, int achievementID)
{
ExpResponse r = new ExpResponse();
string cmdText = @"Merge AwardedAchievements as t
using(select @mobileID as MobileID, @achievementID as AchievementID) as s
on t.MobileID = s.MobileID and t.AchievementID = s.AchievementID
when not matched then
insert(MobileID, AchievementID) values (@mobileID, @achievementID);";
using (SqlCommand cmd = new SqlCommand(cmdText, con))
{
cmd.Parameters.AddWithValue("@mobileID", mobileID);
cmd.Parameters.AddWithValue("@achievementID", achievementID);
try
{
r.result = cmd.ExecuteNonQuery();
return r;
}
catch (Exception e)
{
r.setErMsgStk(true, "Exception in AwardAchievement: " + e.Message, e.StackTrace);
return r;
}
}
}
internal ExpResponse DJRemoveUsersFromVenue(int DJID)
{
ExpResponse r = new ExpResponse();
SqlCommand cmd = new SqlCommand("update MobileUsers set Venue = @null where Venue = @DJID;", con);
cmd.Parameters.AddWithValue("@null", DBNull.Value);
cmd.Parameters.AddWithValue("@DJID", DJID);
try
{
r.result = cmd.ExecuteNonQuery();
return r;
}
catch (SqlException e)
{
r.setErMsgStk(true, "Exception in DJRemoveUsersFromVenue ID: " + e.Number + " " + e.Message, e.StackTrace);
return r;
}
catch (Exception e)
{
r.setErMsgStk(true, "Exception in DJRemoveUsersFromVenue: " + e.Message, e.StackTrace);
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)
{
ExpResponse r = new ExpResponse();
using (DatabaseConnectivity db = new DatabaseConnectivity())
{
// Try to establish a database connection
r = db.OpenConnection();
if (r.error)
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Web);
int ID;
if (isDJ)
r = db.DJValidateUsernameEmail(username, email, out ID);
else
r = db.MobileValidateUsernameEmail(username, email, out ID);
if (r.error)
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Web);
if(ID == -1)
{
r.setErMsg(true, Messages.MSG_EMAIL_NOT_FOUND);
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);
else
r = db.MobileGetPasswordResetID(random, out uniqueIsNegOne);
if(r.error)
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Web);
random = Common.CreateSalt(32);
random = rgx.Replace(random, "x");
}
if (isDJ)
r = db.DJSetPasswordReset(ID, random);
else
r = db.MobileSetPasswordReset(ID, random);
if(r.error)
return Common.LogErrorRetNewMsg(r, Messages.ERR_SERVER, Common.LogFile.Web);
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(Settings.EMAIL_ADR, Settings.EMAIL_PSWD);
mailServer.EnableSsl = true;
mailServer.Send(mail);
return r;
}
catch (Exception e)
{
r.setErMsgStk(true, e.Message, e.StackTrace);
return Common.LogErrorRetNewMsg(r, Messages.ERR_EMAIL_SERVER, Common.LogFile.Web);
}
}
}
/// <summary>
/// Sets up the DB to start the password reset process. The value is tored in the DB along with the ID.
/// </summary>
/// <param name="DJID">The DJ's ID</param>
/// <param name="value">The unique key that will represent this password reset.</param>
/// <returns>The outcome of the operation</returns>
internal ExpResponse DJSetPasswordReset(int DJID, string value)
{
ExpResponse r = new ExpResponse();
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.setErMsgStk(true, "Exception in DJSetPasswordReset:" + e.Message, e.StackTrace);
return r;
}
}
/// <summary>
/// Method to handle creating the sql for an achievement statement. Calls other methods depending on the
/// specific achievement select keyword. Returns an erorr if the achievement was invalid, or cannot be parsed.
/// </summary>
/// <param name="a">The achievement statement.</param>
/// <param name="DJID">The DJ's unique ID.</param>
/// <param name="cmd">Out sql command to evaluate the statement.</param>
/// <returns>The outcome of the operation.</returns>
private static ExpResponse CreateStatementGeneric(AchievementSelect a, int DJID, out SqlCommand cmd)
{
switch (a.selectKeyword)
{
case SelectKeyword.Max:
case SelectKeyword.Min:
return CreateStatementMinMax(a, DJID, out cmd);
case SelectKeyword.Newest:
case SelectKeyword.Oldest:
return CreateStatementOldestNewest(a, DJID, out cmd);
case SelectKeyword.CountGTE:
case SelectKeyword.CountLTE:
return CreateStatementCount(a, DJID, out cmd);
default:
ExpResponse r = new ExpResponse();
r.setErMsgStk(true, "Bad select keyword CreateStatementGeneric", Environment.StackTrace);
cmd = new SqlCommand();
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>
internal ExpResponse DJSetSalt(int DJID, string salt)
{
ExpResponse r = new ExpResponse();
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.setErMsgStk(true, "Exception in DJSetSalt:" + e.Message, e.StackTrace);
return r;
}
}
/// <summary>
/// Creates the sql for an achievement statement that involves selecting on a count. Returns an error of the achievement
/// statement cannot be parsed.
/// </summary>
/// <param name="a">The achievement statement.</param>
/// <param name="DJID">The DJ's unique ID.</param>
/// <param name="cmd">Out sql command to evaluate the statement.</param>
/// <returns>The outcome of the operation.</returns>
private static ExpResponse CreateStatementCount(AchievementSelect a, int DJID, out SqlCommand cmd)
{
ExpResponse r = new ExpResponse();
cmd = new SqlCommand();
int value;
if (!int.TryParse(a.selectValue, out value))
{
r.setErMsgStk(true, "Could not select value", Environment.StackTrace);
return r;
}
if (value < 0)
{
r.setErMsgStk(true, "Select value was less than 0, abort", Environment.StackTrace);
return r;
}
// In this case, statement must be all users that don't have a count > 0.
if (value == 0 && a.selectKeyword == SelectKeyword.CountLTE)
{
cmd.CommandText = "select MobileID from MobileSongHistory where MobileID not in ";
cmd.CommandText+= "( ";
cmd.CommandText += "select MobileID from MobileSongHistory inner join DJSongs on MobileSongHistory.SongID = DJSongs.SongID ";
cmd.CommandText+= "where DJSongs." + ClauseKeywordToString(a) + " like @clauseKeyword and VenueID = @DJID and DateSung >= @minDate and DateSung <= @maxDate ";
cmd.CommandText+= "group by MobileID having count(mobileID) > 0";
cmd.CommandText+= ") ";
cmd.CommandText+= "and VenueID = @DJID and DateSung >= @minDate and DateSung <= @maxDate ";
cmd.CommandText+= "group by MobileID;";
cmd.Parameters.AddWithValue("@clauseKeyword", a.clauseValue);
cmd.Parameters.AddWithValue("@DJID", DJID);
cmd.Parameters.AddWithValue("@minDate", a.startDate);
cmd.Parameters.AddWithValue("@maxDate", a.endDate);
}
// In this case, select all users who have sang a song 0 or more times, simply returns all users.
else if (value == 0 && a.selectKeyword == SelectKeyword.CountGTE)
{
cmd.CommandText = "select MobileID form MobileSongHistory where VenueID = @DJID group by MobileID";
cmd.Parameters.AddWithValue("@DJID", DJID);
}
// Not a special case, just regular stuff.
else
{
cmd.CommandText = "select MobileID from MobileSongHistory inner join DJSongs on MobileSongHistory.SongID = DJSongs.SongID ";
cmd.CommandText += "where DJSongs." + ClauseKeywordToString(a) + " like @clauseKeyword ";
cmd.Parameters.AddWithValue("@clauseKeyword", a.clauseValue);
cmd.CommandText += "and VenueID = @DJID and DateSung >= @minDate and DateSung <= @maxDate ";
cmd.Parameters.AddWithValue("@DJID", DJID);
cmd.Parameters.AddWithValue("@minDate", a.startDate);
cmd.Parameters.AddWithValue("@maxDate", a.endDate);
cmd.CommandText += "group by MobileID having count(mobileID) " + SelectKeywordToString(a) + " @value;";
cmd.Parameters.AddWithValue("@value", a.selectValue);
}
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 ExpResponse DBNonQuery(SqlCommand cmd)
{
ExpResponse r = new ExpResponse();
r.result = 0;
try
{
cmd.Connection = con;
r.result = cmd.ExecuteNonQuery();
return r;
}
catch (Exception e)
{
r.setErMsgStk(true, "Exception in DBNonQuery:" + e.Message, e.StackTrace);
return r;
}
}
