internal string GetQuestionAnswer(long questionID)
{
string answer = "";
try
{
string SQL = "SELECT [answer] FROM [answers] WHERE [question] = @question AND [group] = @group";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
using (var cmd = new MySqlCommand(SQL, con))
{
cmd.Parameters.AddWithValue("question", questionID);
cmd.Parameters.AddWithValue("group", ID);
con.Open();
using (var rdr = cmd.ExecuteReader())
{
if (rdr.HasRows)
{
answer = DataReader.ReadAnswer(rdr);
}
}
}
}
}
catch (ComponentsException ex)
{
ErrorTrapper.Log(ex, LogOptions.PromptTheUser);
}
return(answer);
}
/**************************************************
********** STATIC METHODS ************************
**************************************************/
/// <summary>
/// Gets all Levels
/// </summary>
/// <returns>List of Levels</returns>
internal static List <Level> GetAll()
{
List <Level> levels = new List <Level>();
try
{
string SQL = "SELECT * FROM [levels]";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
using (var cmd = new MySqlCommand(SQL, con))
{
con.Open();
using (var rdr = cmd.ExecuteReader())
{
if (rdr.HasRows)
{
levels = DataReader.ReadLevels(rdr);
}
}
}
}
}
catch (ComponentsException ex)
{
ErrorTrapper.Log(ex, LogOptions.PromptTheUser);
}
return(levels);
}
/// <summary>
/// Checks the data
/// </summary>
private void Check()
{
try
{
string SQL = "SELECT [score] FROM [scores] WHERE [group] = @group AND [question] = @question";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
using (var cmd = new MySqlCommand(SQL, con))
{
cmd.Parameters.AddWithValue("group", group);
cmd.Parameters.AddWithValue("question", question);
con.Open();
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
if (DBNull.Value != rdr["score"])
{
score = Convert.ToInt16(rdr["score"]);
}
}
}
}
}
}
catch (Exception ex)
{
ErrorTrapper.Log(ex, LogOptions.LogToFile);
}
}
/**************************************************
********** STATIC METHODS ************************
**************************************************/
/// <summary>
/// Gets all Members associated with the Group
/// </summary>
/// <param name="groupID">ID of the Group</param>
/// <returns>List of Members</returns>
internal static List <Member> GetGroupMembers(long groupID)
{
List <Member> members = new List <Member>();
try
{
string SQL = "SELECT * FROM [members] WHERE [group] = @group";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
using (var cmd = new MySqlCommand(SQL, con))
{
cmd.Parameters.AddWithValue("group", groupID);
con.Open();
using (var rdr = cmd.ExecuteReader())
{
if (rdr.HasRows)
{
members = DataReader.ReadMembers(rdr);
}
}
}
}
}
catch (ComponentsException ex)
{
ErrorTrapper.Log(ex, LogOptions.PromptTheUser);
}
return(members);
}
/**************************************************
********** PRIVATE METHODS ***********************
**************************************************/
/// <summary>
/// Gets all the Questions associated with the Game
/// </summary>
/// <param name="gameID">ID of the Game</param>
/// <returns>List of Questions</returns>
private List <Question> GetQuestions()
{
List <Question> questions = new List <Question>();
try
{
string SQL = "SELECT * FROM [questions] WHERE [game] = @game";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
using (var cmd = new MySqlCommand(SQL, con))
{
cmd.Parameters.AddWithValue("game", ID);
con.Open();
using (var rdr = cmd.ExecuteReader())
{
if (rdr.HasRows)
{
questions = DataReader.ReadQuestions(rdr);
}
}
}
}
}
catch (ComponentsException ex)
{
ErrorTrapper.Log(ex, LogOptions.PromptTheUser);
}
return(questions);
}
/// <summary>
/// Gets the Game data by its Sequence
/// </summary>
/// <param name="sequence">Sequence of the Game</param>
/// <returns>Game object</returns>
internal static Game GetBySequence(short sequence)
{
Game game = new Game();
try
{
string SQL = "SELECT * FROM [games] WHERE [sequence] = @sequence";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
using (var cmd = new MySqlCommand(SQL, con))
{
cmd.Parameters.AddWithValue("sequence", sequence);
con.Open();
using (var rdr = cmd.ExecuteReader())
{
if (rdr.HasRows)
{
game = DataReader.ReadGames(rdr)[0];
}
}
}
}
}
catch (ComponentsException ex)
{
ErrorTrapper.Log(ex, LogOptions.PromptTheUser);
}
return(game);
}
/// <summary>
/// Gets the Sequence of the last Game
/// </summary>
/// <returns>Sequence number</returns>
internal static int GetLastSequence()
{
string SQL = "SELECT MAX([sequence]) AS 'last' FROM [games]";
int sequence = 0;
try
{
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
MySqlCommand cmd = new MySqlCommand(SQL, con);
con.Open();
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (DBNull.Value != rdr["last"])
{
sequence = Convert.ToInt32(rdr["last"]);
}
}
}
}
catch (ComponentsException ex)
{
ErrorTrapper.Log(ex, LogOptions.PromptTheUser);
}
return(sequence);
}
/// <summary>
/// Sets the current Question
/// </summary>
/// <param name="questionID">ID of the Question</param>
private static void SetQuestion(long questionID)
{
string SQL = "UPDATE [current] SET [name] = @name, [value] = @value";
try
{
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
using (var cmd = new MySqlCommand(SQL, con))
{
cmd.Parameters.AddWithValue("name", "Question");
cmd.Parameters.AddWithValue("value", questionID);
con.Open();
if (cmd.ExecuteNonQuery() == 0)
{
SQL = "INSERT INTO [current] ([name], [value]) VALUES (@name, @value)";
cmd.ExecuteNonQuery();
}
}
}
}
catch (Exception ex)
{
ErrorTrapper.Log(ex, LogOptions.LogToFile);
}
}
/**
* */
/// <summary>
/// Gets all choices associated with the Question
/// </summary>
/// <param name="questionID">ID of the Question</param>
/// <returns>List of Choices</returns>
internal List <Choice> GetChoices()
{
List <Choice> choices = new List <Choice>();
try
{
string SQL = "SELECT * FROM [choices] WHERE [question] = @question";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
using (var cmd = new MySqlCommand(SQL, con))
{
cmd.Parameters.AddWithValue("question", ID);
con.Open();
using (var rdr = cmd.ExecuteReader())
{
if (rdr.HasRows)
{
choices = DataReader.ReadChoices(rdr);
}
}
}
}
}
catch (ComponentsException ex)
{
ErrorTrapper.Log(ex, LogOptions.PromptTheUser);
}
return(choices);
}
internal static short GetNextSequence(long gameID)
{
short sequence = 0;
try
{
string SQL = "SELECT MAX([sequence]) AS last FROM [questions] WHERE [game] = @game";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
MySqlCommand cmd = new MySqlCommand(SQL, con);
cmd.Parameters.AddWithValue("game", gameID);
con.Open();
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (DBNull.Value != rdr["last"])
{
sequence = Convert.ToInt16(rdr["last"]);
}
}
}
}
catch (ComponentsException ex)
{
ErrorTrapper.Log(ex, LogOptions.PromptTheUser);
}
return(++sequence);
}
/// <summary>
/// Gets the Current Question
/// </summary>
/// <returns>Question object</returns>
private static Question GetQuestion()
{
string SQL = "SELECT * FROM [current] WHERE [name] = @name";
Question question = new Question();
try
{
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
using (var cmd = new MySqlCommand(SQL, con))
{
cmd.Parameters.AddWithValue("name", "Question");
con.Open();
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
if (DBNull.Value != rdr["value"])
{
question = Question.GetByID(Convert.ToInt64(rdr["value"]));
}
}
}
}
}
}
catch (Exception ex)
{
ErrorTrapper.Log(ex, LogOptions.LogToFile);
}
return(question);
}
/**************************************************
********** STATIC METHODS ************************
**************************************************/
/// <summary>
/// Gets all the Groups that were present during the Game
/// </summary>
/// <param name="gameID">ID of the Game</param>
/// <returns>List of Groups</returns>
internal static List <Group> GetPresent(long gameID)
{
List <Group> groups = new List <Group>();
try
{
string SQL = "SELECT [groups].[id], [groups].[name] FROM [groups] JOIN [attendance] ON [groups].[id] = [attendance].[group] WHERE [attendance].[game] = @game AND [groups].[active] = @active ORDER BY [groups].[name] ASC";
using (var con = new MySqlConnection(Builder.ConnectionString))
{
using (var cmd = new MySqlCommand(SQL, con))
{
cmd.Parameters.AddWithValue("game", gameID);
cmd.Parameters.AddWithValue("active", true);
con.Open();
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Group group = new Group();
group.ID = Convert.ToInt64(rdr["id"], CultureInfo.CurrentCulture);
group.Name = rdr["name"].ToString();
group.Members = Member.GetGroupMembers(group.ID);
groups.Add(group);
}
}
}
}
}
catch (Exception ex)
{
ErrorTrapper.Log(ex, LogOptions.PromptTheUser);
}
return(groups);
}
/**************************************************
********** STATIC METHODS ************************
**************************************************/
/// <summary>
/// Gets all Groups
/// </summary>
/// <returns>List of Groups</returns>
internal static List <Group> GetAll()
{
List <Group> groups = new List <Group>();
try
{
string SQL = "SELECT * FROM [groups] WHERE [active] = @active ORDER BY [name] ASC";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
using (var cmd = new MySqlCommand(SQL, con))
{
cmd.Parameters.AddWithValue("active", true);
con.Open();
using (var rdr = cmd.ExecuteReader())
{
if (rdr.HasRows)
{
groups = DataReader.ReadGroups(rdr);
}
}
}
}
}
catch (ComponentsException ex)
{
ErrorTrapper.Log(ex, LogOptions.PromptTheUser);
}
return(groups);
}
/// <summary>
/// Gets the Member data by its ID
/// </summary>
/// <param name="id">ID of the Member</param>
/// <returns>Member object</returns>
internal static Member GetByID(long id)
{
Member member = new Member();
try
{
string SQL = "SELECT * FROM [members] WHERE [id] = @id";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
using (var cmd = new MySqlCommand(SQL, con))
{
cmd.Parameters.AddWithValue("id", id);
con.Open();
using (var rdr = cmd.ExecuteReader())
{
if (rdr.HasRows)
{
member = DataReader.ReadMembers(rdr)[0];
}
}
}
}
}
catch (ComponentsException ex)
{
ErrorTrapper.Log(ex, LogOptions.PromptTheUser);
}
return(member);
}
/**************************************************
********** METHODS *******************************
**************************************************/
/// <summary>
/// Saves the data
/// </summary>
/// <returns>True if saved successfully, otherwise false</returns>
internal bool Save()
{
bool saved = false;
try
{
Validate(this);
if (ID > 0)
{
string SQL = "UPDATE [groups] SET [name] = @name WHERE [id] = @id";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
MySqlCommand cmd = new MySqlCommand(SQL, con);
cmd.Parameters.AddWithValue("name", Name);
cmd.Parameters.AddWithValue("id", ID);
con.Open();
saved = cmd.ExecuteNonQuery() > 0;
}
}
else
{
string SQL = "INSERT INTO [groups] ([name]) VALUES (@name)";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
MySqlCommand cmd = new MySqlCommand(SQL, con);
cmd.Parameters.AddWithValue("name", Name);
con.Open();
saved = cmd.ExecuteNonQuery() > 0;
if (saved)
{
ID = cmd.LastInsertedId;
}
}
}
if (saved)
{
List <Member> oldMembers = Member.GetGroupMembers(ID);
foreach (Member oldMember in oldMembers)
{
if (!Members.Contains(oldMember))
{
oldMember.Delete();
}
}
foreach (Member member in Members)
{
member.Group = ID;
member.Save();
}
}
}
catch (ComponentsException ex)
{
ErrorTrapper.Log(ex, LogOptions.PromptTheUser);
}
return(saved);
}
/**************************************************
********** STATIC METHODS ************************
**************************************************/
/// <summary>
/// Gets all Games
/// </summary>
/// <returns>List of Games</returns>
internal static List <Game> GetAll(Filter filter)
{
List <Game> games = new List <Game>();
try
{
string SQL = "SELECT * FROM [games]";
switch (filter)
{
case Filter.Finished:
case Filter.Unfinished:
SQL += " WHERE [finished] = @finished";
break;
}
using (var con = new MySqlConnection(Builder.ConnectionString))
{
using (var cmd = new MySqlCommand(SQL, con))
{
switch (filter)
{
case Filter.Finished:
cmd.Parameters.AddWithValue("finished", true);
break;
case Filter.Unfinished:
cmd.Parameters.AddWithValue("finished", false);
break;
}
con.Open();
using (var rdr = cmd.ExecuteReader())
{
if (rdr.HasRows)
{
games = DataReader.ReadGames(rdr);
}
}
}
}
}
catch (ComponentsException ex)
{
ErrorTrapper.Log(ex, LogOptions.PromptTheUser);
}
return(games);
}
/**************************************************
********** METHODS *******************************
**************************************************/
/// <summary>
/// Saves the data
/// </summary>
/// <returns>True if saved successfully, otherwise false</returns>
internal bool Save()
{
bool saved = false;
try
{
Validate(this);
if (ID > 0)
{
string SQL = "UPDATE [levels] SET [name] = @name, [score] = @score, [special] = @special WHERE [id] = @id";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
MySqlCommand cmd = new MySqlCommand(SQL, con);
cmd.Parameters.AddWithValue("name", Name);
cmd.Parameters.AddWithValue("score", Score);
cmd.Parameters.AddWithValue("special", Special);
cmd.Parameters.AddWithValue("id", ID);
con.Open();
saved = cmd.ExecuteNonQuery() > 0;
}
}
else
{
string SQL = "INSERT INTO [levels] ([name], [score], [special]) VALUES (@name, @score, @special)";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
MySqlCommand cmd = new MySqlCommand(SQL, con);
cmd.Parameters.AddWithValue("name", Name);
cmd.Parameters.AddWithValue("score", Score);
cmd.Parameters.AddWithValue("special", Special);
con.Open();
saved = cmd.ExecuteNonQuery() > 0;
if (saved)
{
ID = cmd.LastInsertedId;
}
}
}
}
catch (ComponentsException ex)
{
ErrorTrapper.Log(ex, LogOptions.PromptTheUser);
}
return(saved);
}
/**************************************************
********** METHODS *******************************
**************************************************/
/// <summary>
/// Saves the data
/// </summary>
/// <returns>True if saved successfully, otherwise false</returns>
internal bool Save()
{
bool saved = false;
try
{
Validate(this);
if (ID > 0)
{
string SQL = "UPDATE [games] SET [sequence] = @sequence, [date] = @date, [finished] = @finished WHERE [id] = @id";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
MySqlCommand cmd = new MySqlCommand(SQL, con);
cmd.Parameters.AddWithValue("sequence", Sequence);
cmd.Parameters.AddWithValue("date", Date);
cmd.Parameters.AddWithValue("finished", Finished);
cmd.Parameters.AddWithValue("id", ID);
con.Open();
saved = cmd.ExecuteNonQuery() > 0;
}
}
else
{
string SQL = "INSERT INTO [games] ([sequence], [date]) VALUES (@sequence, @date)";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
MySqlCommand cmd = new MySqlCommand(SQL, con);
cmd.Parameters.AddWithValue("sequence", Sequence);
cmd.Parameters.AddWithValue("date", Date);
con.Open();
saved = cmd.ExecuteNonQuery() > 0;
if (saved)
{
ID = cmd.LastInsertedId;
}
}
}
}
catch (ComponentsException ex)
{
ErrorTrapper.Log(ex, LogOptions.PromptTheUser);
}
return(saved);
}
/**************************************************
********** PRIVATE METHODS ***********************
**************************************************/
/// <summary>
/// Computes the total Score of the Group for the Game. If the given GameID is equal to 0, the score will be computed for all Games.
/// </summary>
/// <param name="gameID">ID of the Game</param>
/// <returns>Score number</returns>
private static short ComputeScore(long gameID, long groupID)
{
string SQL = "SELECT SUM([score]) AS 'score' FROM [scores] JOIN [questions] ON [scores].[question] = [questions].[id] WHERE [group] = @group";
if (gameID > 0)
{
SQL += " AND [questions].[game] = @game";
}
short score = 0;
try
{
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
using (var cmd = new MySqlCommand(SQL, con))
{
cmd.Parameters.AddWithValue("group", groupID);
if (gameID > 0)
{
cmd.Parameters.AddWithValue("game", gameID);
}
con.Open();
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
if (rdr["score"] != DBNull.Value)
{
score = Convert.ToInt16(rdr["score"]);
}
}
}
}
}
}
catch (Exception ex)
{
ErrorTrapper.Log(ex, LogOptions.LogToFile);
}
return(score);
}
/**************************************************
********** PRIVATE METHODS ***********************
**************************************************/
private void Present()
{
try
{
string SQL = "INSERT INTO [attendance] ([game], [group]) VALUES (@game, @group)";
using (var con = new MySqlConnection(Builder.ConnectionString))
{
using (var cmd = new MySqlCommand(SQL, con))
{
cmd.Parameters.AddWithValue("game", game);
cmd.Parameters.AddWithValue("group", group);
con.Open();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
ErrorTrapper.Log(ex, LogOptions.LogToFile);
}
}
private void Absent()
{
try
{
string SQL = "DELETE FROM [attendance] WHERE [game] = @game AND [group] = @group";
using (var con = new MySqlConnection(Builder.ConnectionString))
{
using (var cmd = new MySqlCommand(SQL, con))
{
cmd.Parameters.AddWithValue("game", game);
cmd.Parameters.AddWithValue("group", group);
con.Open();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
ErrorTrapper.Log(ex, LogOptions.LogToFile);
}
}
/// <summary>
/// Deletes the data
/// </summary>
/// <returns>True if deleted successfully, otherwise false</returns>
internal bool Delete()
{
bool deleted = false;
try
{
string SQL = "DELETE FROM [members] WHERE [id] = @id";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
MySqlCommand cmd = new MySqlCommand(SQL, con);
cmd.Parameters.AddWithValue("id", ID);
con.Open();
deleted = cmd.ExecuteNonQuery() > 0;
}
}
catch (ComponentsException ex)
{
ErrorTrapper.Log(ex, LogOptions.PromptTheUser);
}
return(deleted);
}
/// <summary>
/// Saves the Score data
/// </summary>
private void Insert()
{
try
{
string SQL = "INSERT INTO [scores] ([group], [question], [score]) VALUES (@group, @question, @score)";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
using (var cmd = new MySqlCommand(SQL, con))
{
cmd.Parameters.AddWithValue("group", group);
cmd.Parameters.AddWithValue("question", question);
cmd.Parameters.AddWithValue("score", score);
con.Open();
saved = cmd.ExecuteNonQuery() > 0;
}
}
}
catch (Exception ex)
{
ErrorTrapper.Log(ex, LogOptions.LogToFile);
}
}
private void Update()
{
try
{
string SQL = "UPDATE [scores] SET [score] = @score WHERE [group] = @group AND [question] = @question";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
using (var cmd = new MySqlCommand(SQL, con))
{
cmd.Parameters.AddWithValue("group", group);
cmd.Parameters.AddWithValue("question", question);
cmd.Parameters.AddWithValue("score", score);
con.Open();
saved = cmd.ExecuteNonQuery() > 0;
}
}
}
catch (Exception ex)
{
ErrorTrapper.Log(ex, LogOptions.LogToFile);
}
}
/**************************************************
********** METHODS *******************************
**************************************************/
/// <summary>
/// Saves the data
/// </summary>
/// <returns>True if saved successfully, otherwise false</returns>
internal bool Save()
{
bool saved = false;
try
{
Validate(this);
if (ID > 0)
{
string SQL = "UPDATE [questions] SET [game] = @game, [level] = @level, [sequence] = @sequence, [passplay] = @passplay, [question_statement] = @question_statement, [bible_reference] = @bible_reference, [choices_layout] = @choices_layout WHERE [id] = @id";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
MySqlCommand cmd = new MySqlCommand(SQL, con);
cmd.Parameters.AddWithValue("game", Game);
cmd.Parameters.AddWithValue("level", Level);
cmd.Parameters.AddWithValue("sequence", Sequence);
cmd.Parameters.AddWithValue("passplay", PassPlay);
cmd.Parameters.AddWithValue("question_statement", QuestionStatement);
cmd.Parameters.AddWithValue("bible_reference", BibleReference);
cmd.Parameters.AddWithValue("choices_layout", ChoicesLayout);
cmd.Parameters.AddWithValue("id", ID);
con.Open();
saved = cmd.ExecuteNonQuery() > 0;
}
}
else
{
string SQL = "INSERT INTO [questions] ([game], [level], [sequence], [passplay], [question_statement], [bible_reference], [choices_layout]) VALUES (@game, @level, @sequence, @passplay, @question_statement, @bible_reference, @choices_layout)";
using (MySqlConnection con = new MySqlConnection(Builder.ConnectionString))
{
MySqlCommand cmd = new MySqlCommand(SQL, con);
cmd.Parameters.AddWithValue("game", Game);
cmd.Parameters.AddWithValue("level", Level);
cmd.Parameters.AddWithValue("sequence", Sequence);
cmd.Parameters.AddWithValue("passplay", PassPlay);
cmd.Parameters.AddWithValue("question_statement", QuestionStatement);
cmd.Parameters.AddWithValue("bible_reference", BibleReference);
cmd.Parameters.AddWithValue("choices_layout", ChoicesLayout);
con.Open();
saved = cmd.ExecuteNonQuery() > 0;
if (saved)
{
ID = cmd.LastInsertedId;
}
}
}
if (saved)
{
foreach (Choice choice in Choices)
{
choice.Question = ID;
saved = choice.Save();
}
}
}
catch (ComponentsException ex)
{
saved = false;
if (Delete())
{
foreach (Choice choice in Choices)
{
if (choice.ID > 0)
{
choice.Delete();
}
}
}
ErrorTrapper.Log(ex, LogOptions.PromptTheUser);
}
return(saved);
}