/// <summary>
/// Creates the sql for an achievement. The sql is stored as a list of sql operation to perform.
/// </summary>
/// <param name="a">The achievement.</param>
/// <param name="DJID">The DJ's ID.</param>
/// <param name="sql">Out string that represents the sum of all sql statement.</param>
/// <param name="sqlCommands">List of sql commands that must be run to determine who qualifies for the achievement.</param>
/// <returns></returns>
public static ExpResponse CreateAchievementSQL(Achievement a, int DJID, out string sql, out List<SqlCommand> sqlCommands)
{
List<SqlCommand> commands = new List<SqlCommand>();
ExpResponse r = new ExpResponse();
SqlCommand cmd;
sql = string.Empty;
sqlCommands = new List<SqlCommand>();
foreach (AchievementSelect achieveSel in a.selectList)
{
r = CreateStatementGeneric(achieveSel, DJID, out cmd);
if (r.error)
return r;
sqlCommands.Add(cmd);
sql += CreateDebugSQLText(cmd) + "\n";
}
return r;
}
/// <summary>
/// Add an achievement to the list of DJ achievements stored in the server.
/// If the achievement is invalid, an error is returned.
/// </summary>
/// <param name="achievement">The achievement to add.</param>
/// <param name="DJKey">The DJKey for the venue.</param>
/// <returns>The outcome of the operation.</returns>
public Response DJAddAchievement(Achievement achievement, long DJKey)
{
int DJID = -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;
foreach(AchievementSelect a in achievement.selectList)
{
if (a.startDate.Year < 1754)
{
a.startDate = new DateTime(1900, 1, 1);
}
}
string sqlText;
List<SqlCommand> cmds;
r = AchievementParser.CreateAchievementSQL(achievement, DJID, out sqlText, out cmds);
if (r.error)
return r;
r = db.DJAddAchievement(DJID, achievement);
if (r.error)
return r;
achievement.ID = r.result;
r = Common.EvaluateAchievement(DJID, achievement, db);
if (r.error)
return r;
r.message = achievement.ID.ToString();
r.result = achievement.ID;
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 DJAddAchievement(int DJID, Achievement achievement)
{
MemoryStream streamAchievement = new MemoryStream();
DataContractSerializer achievementSerializer = new DataContractSerializer(typeof(Achievement));
achievementSerializer.WriteObject(streamAchievement, achievement);
byte[] serializedAchievementBytes = streamAchievement.ToArray();
ExpResponse r = new ExpResponse();
SqlCommand cmd = new SqlCommand("insert into Achievements (DJID, Object, Name, ObjectSize, Visible) values (@DJID, @achievement, @name, @objectSize, @visible); SELECT SCOPE_IDENTITY();", con);
cmd.Parameters.AddWithValue("@DJID", DJID);
cmd.Parameters.AddWithValue("@achievement", serializedAchievementBytes);
cmd.Parameters.AddWithValue("@name", achievement.name);
cmd.Parameters.AddWithValue("@objectSize", serializedAchievementBytes.Length);
cmd.Parameters.AddWithValue("@visible", Common.GetBitFromBool(achievement.visible));
try
{
r.result = int.Parse(cmd.ExecuteScalar().ToString());
return r;
}
catch (SqlException e)
{
if (e.Number == 2601)
r.setAll(true, "An achievement with that name already exists, please choose a new name.", e.StackTrace, e.Number);
else
r.setErMsgStk(true, "Exception in DJAddAchievement: " + e.Message, e.StackTrace);
return r;
}
catch (Exception e)
{
r.setErMsgStk(true, "Exception in DJAddAchievement: " + e.Message, e.StackTrace);
return r;
}
}
