public int SelectQuestionListID()
{
MySqlCommand SelectQuestionIDCommand = new MySqlCommand("SELECT MAX(`id`) FROM `question_list` WHERE 1");
List <string> results = DatabaseClass.GetData(SelectQuestionIDCommand);
return(Convert.ToInt32(results[0]));
}
public int SelectQuestionIDAddToQuestionList(string question)
{
MySqlCommand Get_Question_ID = new MySqlCommand("SELECT * FROM question WHERE `question` = @val1");
Get_Question_ID.Parameters.AddWithValue("@val1", question);
return(Convert.ToInt32(DatabaseClass.GetData(Get_Question_ID, true)[0]));
}
private static string GetNameFromUniqueID(string user_ID)
{
MySqlCommand GetFirstNameCommand = new MySqlCommand("SELECT `firstname` FROM `user` WHERE `unique_id` = @val1");
GetFirstNameCommand.Parameters.AddWithValue("@val1", user_ID);
List <string> results = DatabaseClass.GetData(GetFirstNameCommand, true);
return(results[0]);
}
public int SelectQuestionID(string difficulty, string category)
{
MySqlCommand getMaxId = new MySqlCommand("SELECT `id` FROM `question` WHERE `category` = @val1 AND `difficulty` LIKE @val2 ORDER BY `id` DESC");
getMaxId.Parameters.AddWithValue("@val1", category);
getMaxId.Parameters.AddWithValue("@val2", difficulty);
List <string> Id = DatabaseClass.GetData(getMaxId, true);
return(random.Next(0, Convert.ToInt32(Id[0])));
}
bool IRegisterDatabaseContext.InsertUser(UserDTO userDTO)
{
MySqlCommand storeData = new MySqlCommand("INSERT INTO `user`(`firstname`, `lastname`, `email`, `password`, `birth_day`) VALUES (@val1,@val2,@val3,@val4,@val5);");
storeData.Parameters.AddWithValue("@val1", userDTO.FirstName);
storeData.Parameters.AddWithValue("@val2", userDTO.LastName);
storeData.Parameters.AddWithValue("@val3", userDTO.Email);
storeData.Parameters.AddWithValue("@val4", userDTO.Password);
storeData.Parameters.AddWithValue("@val5", userDTO.BirthDay);
return(DatabaseClass.StoreData(storeData, true));
}
public bool InsertToScoreboard(SubmitDTO submitDTO)
{
InsertQuestionList(submitDTO.QuestionList);
MySqlCommand SelectQuestionIDCommand = new MySqlCommand("INSERT INTO `scoreboard` ( `user`, `category`, `difficulty`, `amount_of_questions`, `question_list`, `score`, `date`) VALUES(@val1,@val2,@val3,@val4,@val5,@val6, current_timestamp());");
SelectQuestionIDCommand.Parameters.AddWithValue("@val1", submitDTO.UniqueID);
SelectQuestionIDCommand.Parameters.AddWithValue("@val2", submitDTO.Category);
SelectQuestionIDCommand.Parameters.AddWithValue("@val3", submitDTO.Difficulty);
SelectQuestionIDCommand.Parameters.AddWithValue("@val4", 10);
SelectQuestionIDCommand.Parameters.AddWithValue("@val5", SelectQuestionListID());
SelectQuestionIDCommand.Parameters.AddWithValue("@val6", submitDTO.Score);
return(DatabaseClass.StoreData(SelectQuestionIDCommand, true));
}
public List <ScoreboardDTO> ConvertToList(List <string> scoreboardID, List <ScoreboardDTO> ScoreboardData)
{
foreach (string ID in scoreboardID)
{
MySqlCommand GetScoreboardID = new MySqlCommand("SELECT * FROM `scoreboard` WHERE `id` = @val1");
GetScoreboardID.Parameters.AddWithValue("@val1", ID);
var results = DatabaseClass.GetData(GetScoreboardID, true);
ScoreboardData.Add(new ScoreboardDTO(results[0], results[1], results[2], results[3], results[4], results[5], results[6], Convert.ToDateTime(results[7])));
}
foreach (ScoreboardDTO user in ScoreboardData)
{
user.FirstName = GetNameFromUniqueID(Convert.ToString(user.UserID));
}
return(ScoreboardData);
}
UserDTO ILoginDatabaseContext.GetLoginData(string email, string password)
{
MySqlCommand getUserData = new MySqlCommand("SELECT `unique_id`,`firstname`,`lastname` FROM user WHERE email=@val1 AND password=@val2");
getUserData.Parameters.AddWithValue("@val1", email);
getUserData.Parameters.AddWithValue("@val2", password);
List <string> results = DatabaseClass.GetData(getUserData, true);
if (results != null && results.Count > 0)
{
return(new UserDTO {
UniqueID = results[0], FirstName = results[1], LastName = results[2], Email = email
});
}
return(null);
}
public QuestionDTO SelectQuestionDatabase(string difficulty, string category)
{
QuestionDTO questionDTO = new QuestionDTO {
};
MySqlCommand getQuestionData = new MySqlCommand("SELECT * FROM `question` WHERE `id`= @val1");
getQuestionData.Parameters.AddWithValue("@val1", SelectQuestionID(difficulty, category));
List <string> results = DatabaseClass.GetData(getQuestionData, true);
questionDTO.Question = results[2];
List <string> incorrect_Answers = new List <string> {
results[3], results[4], results[5]
};
questionDTO.IncorrectAnswers = incorrect_Answers;
questionDTO.CorrectAnswer = results[6];
return(questionDTO);
}
public bool InsertQuestionList(List <string> Question_List)
{
if (Question_List.Count() == 10)
{
MySqlCommand InsertQuestionListCommand = new MySqlCommand("INSERT INTO `question_list`(`1`, `2`, `3`, `4`, `5`, `6`, `7`, `8`, `9`, `10`) VALUES (@val1,@val2,@val3,@val4,@val5,@val6,@val7,@val8,@val9,@val10)");
InsertQuestionListCommand.Parameters.AddWithValue("@val1", Question_List[0]);
InsertQuestionListCommand.Parameters.AddWithValue("@val2", Question_List[1]);
InsertQuestionListCommand.Parameters.AddWithValue("@val3", Question_List[2]);
InsertQuestionListCommand.Parameters.AddWithValue("@val4", Question_List[3]);
InsertQuestionListCommand.Parameters.AddWithValue("@val5", Question_List[4]);
InsertQuestionListCommand.Parameters.AddWithValue("@val6", Question_List[5]);
InsertQuestionListCommand.Parameters.AddWithValue("@val7", Question_List[6]);
InsertQuestionListCommand.Parameters.AddWithValue("@val8", Question_List[7]);
InsertQuestionListCommand.Parameters.AddWithValue("@val9", Question_List[8]);
InsertQuestionListCommand.Parameters.AddWithValue("@val10", Question_List[9]);
return(DatabaseClass.StoreData(InsertQuestionListCommand, true));
}
return(false);
}
public IEnumerable <ScoreboardDTO> SelectScoreboardData(string difficulty, int category, string timeSpan)
{
List <ScoreboardDTO> ListScoreboardData = new List <ScoreboardDTO>();
if (difficulty == "0" || category == 0)
{
category = 15;
difficulty = "easy";
}
DateTime now = DateTime.Now;
DateTime startDate = now;
switch (timeSpan)
{
case "PastWeek":
startDate = now.AddDays(-7);
break;
case "PastMonth":
startDate = now.AddDays(-30);
break;
case "AllTime":
startDate = now.AddYears(-40);
break;
}
string startString = startDate.ToString("yyyy-MM-dd HH:mm:ss");
MySqlCommand SelectQuestionIDCommand = new MySqlCommand("SELECT `id` FROM `scoreboard` WHERE `category` = @val1 AND `difficulty` LIKE @val2 AND `date` BETWEEN @val3 AND @val4 ORDER BY score DESC LIMIT 5");
SelectQuestionIDCommand.Parameters.AddWithValue("@val1", category);
SelectQuestionIDCommand.Parameters.AddWithValue("@val2", difficulty);
SelectQuestionIDCommand.Parameters.AddWithValue("@val3", startString);
SelectQuestionIDCommand.Parameters.AddWithValue("@val4", now.ToString("yyyy-MM-dd HH:mm:ss"));
List <string> scoreboardID = DatabaseClass.GetData(SelectQuestionIDCommand, true);
ListScoreboardData = ConvertToList(scoreboardID, ListScoreboardData);
return(ListScoreboardData);
}
public bool InsertQuestionDatabase(QuestionDTO questionDTO, string difficulty, string category)
{
MySqlCommand checkQuestion = new MySqlCommand("SELECT * FROM question WHERE `question` = @val1");
checkQuestion.Parameters.AddWithValue("@val1", questionDTO.Question);
List <string> results = DatabaseClass.GetData(checkQuestion, true);
if (results.Count == 0)
{
MySqlCommand insertQuestion = new MySqlCommand("INSERT INTO `question`(`question`, `category`,`incorrect_answer1`, `incorrect_answer2`, `incorrect_answer3`, `correct_answer`,`difficulty`) VALUES (@val1,@val2,@val3,@val4,@val5,@val6,@val7)");
insertQuestion.Parameters.AddWithValue("@val1", questionDTO.Question);
insertQuestion.Parameters.AddWithValue("@val2", category);
insertQuestion.Parameters.AddWithValue("@val3", questionDTO.IncorrectAnswers[0]);
insertQuestion.Parameters.AddWithValue("@val4", questionDTO.IncorrectAnswers[1]);
insertQuestion.Parameters.AddWithValue("@val5", questionDTO.IncorrectAnswers[2]);
insertQuestion.Parameters.AddWithValue("@val6", questionDTO.CorrectAnswer);
insertQuestion.Parameters.AddWithValue("@val7", difficulty);
if (DatabaseClass.StoreData(insertQuestion, true) != true)
{
return(false);
}
}
return(true);
}