/// <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>
/// 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>
/// 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>
/// Generates database valid operations for each possive select keyword value.
/// </summary>
/// <param name="a">A single statment for an achievement.</param>
/// <returns>The keyword or sql operaiton.</returns>
private static string SelectKeywordToString(AchievementSelect a)
{
string keyword = string.Empty;
switch (a.selectKeyword)
{
case SelectKeyword.Max:
case SelectKeyword.Newest:
keyword = "desc";
break;
case SelectKeyword.Min:
case SelectKeyword.Oldest:
keyword = "asc";
break;
case SelectKeyword.CountGTE:
keyword = ">=";
break;
case SelectKeyword.CountLTE:
keyword = "<=";
break;
default:
return string.Empty;
}
return keyword;
}
/// <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;
}
}
