/// <summary>
/// Insert Question into database
/// </summary>
/// <param name="option1"></param>
/// <param name="option2"></param>
/// <param name="option3"></param>
/// <param name="option4"></param>
/// <param name="correctOptionInt"></param>
/// <param name="questionTitle"></param>
public void InsertQuestion(string option1, string option2, string option3, string option4, int correctOptionInt, string questionTitle)
{
switch (correctOptionInt)
{
case 1:
FormatForDatabaseInsertion(ref option4, ref option1);
break;
case 2:
FormatForDatabaseInsertion(ref option4, ref option2);
break;
case 3:
FormatForDatabaseInsertion(ref option4, ref option3);
break;
default:
break;
}
string query = String.Format("INSERT INTO Question(Option1, Option2, Option3, CorrectOption, QuestionTitle) " +
"VALUES('{0}', '{1}', '{2}', '{3}', '{4}')", option1, option2, option3, option4, questionTitle);;
BaseDaoImpl <Question> baseDao = new BaseDaoImpl <Question>();
int result = baseDao.ExecuteQuery(query);
int questionId = GetStudentQuestionId(option1, option2, option3, option4, questionTitle);
int customLevelId = GetCustomLevelId(option1, option2, option3, option4, questionTitle);
InsertStudentCustomQuestion(questionId, customLevelId);
}
/// <summary>
/// Link questionId and customLevelId in database
/// </summary>
/// <param name="questionId"></param>
/// <param name="customLevelId"></param>
private void InsertStudentCustomQuestion(int questionId, int customLevelId)
{
string query = String.Format("INSERT INTO StudentCustomQuestion(QuestionId, CustomLevelId) " +
"VALUES({0}, {1});", questionId, customLevelId);
BaseDaoImpl <int> baseDao = new BaseDaoImpl <int>();
int result = baseDao.ExecuteQuery(query);
}
/// <summary>
/// Updates the level name, monster, and time limit
/// </summary>
/// <param name="levelName"></param>
/// <param name="monsterId"></param>
/// <param name="timeLimit"></param>
public void UpdateLevelInitInfo(string levelName, int monsterId, int timeLimit)
{
string query = String.Format("UPDATE CustomLevel SET CustomLevelName = '{0}', MonsterId = {1}, TimeLimit = {2} WHERE CustomLevelId = {3}; "
, levelName, monsterId, timeLimit, Global.CustomLevelId);
BaseDaoImpl <int> baseDao = new BaseDaoImpl <int>();
int result = baseDao.ExecuteQuery(query);
}
/// <summary>
/// Get existing Student's Facebook account
/// </summary>
/// <param name="fbId"></param>
/// <returns>Retrun Student Object</returns>
public Student GetFacebookStudent(string fbId)
{
BaseDaoImpl <Student> baseDao = new BaseDaoImpl <Student>();
string query = String.Format("SELECT * FROM Student WHERE FacebookAccountId = {0}", fbId);
Student student = baseDao.RetrieveQuery(query);
return(student);
}
/// <summary>
/// Get existing Student's Google account
/// </summary>
/// <param name="googleId"></param>
/// <returns>Return Student Object</returns>
public Student GetGoogleStudent(string googleId)
{
BaseDaoImpl <Student> baseDao = new BaseDaoImpl <Student>();
string query = String.Format("SELECT * FROM Student WHERE GoogleAccountId = {0}", googleId);
Student student = baseDao.RetrieveQuery(query);
return(student);
}
static void Main(string[] args)
{
var baseDao = new BaseDaoImpl();
string name = baseDao.getName("aaa");
Console.WriteLine(name);
Console.ReadLine();
}
/// <summary>
/// Updates Student's Character
/// </summary>
/// <param name="charId"></param>
/// <param name="studentId"></param>
/// <returns>Return 1 if update query has executed successfully</returns>
public int UpdateStudentCharacter(int charId, int studentId)
{
BaseDaoImpl <Object> baseDao = new BaseDaoImpl <Object>();
string query = "UPDATE Student SET CharId = @CharId WHERE StudentId = @StudentId";
int result = baseDao.ExecuteQuery(query, new { CharId = charId, StudentId = studentId });
return(result);
}
/// <summary>
/// Get Student's campaign rank
/// </summary>
/// <param name="studentId"></param>
/// <returns>Return integer result of Student's campaign rank</returns>
public int GetCampaignRanking(int studentId)
{
BaseDaoImpl <int> baseDao = new BaseDaoImpl <int>();
string query = "SELECT (COUNT(0)+1) AS StudentRank FROM StudentLevelTotalScore s1, StudentLevelTotalScore s2 " +
"WHERE s1.StudentId = @StudentId AND s2.totalLevelScore > s1.totalLevelScore";
int result = baseDao.ExecuteScalar(query, new { StudentId = studentId });
return(result);
}
/// <summary>
/// Get Monster that belongs to selected CustomLevel
/// </summary>
/// <param name="customLevelId"></param>
/// <returns>Return Monster object</returns>
public Monster GetCustomLevelMonster(int customLevelId)
{
BaseDaoImpl <Monster> baseDao = new BaseDaoImpl <Monster>();
string query = String.Format("SELECT m.MonsterId, m.MonsterName FROM CustomLevel cl NATURAL JOIN Monster m " +
"WHERE cl.CustomLevelId = {0}", customLevelId);
Monster monster = baseDao.RetrieveQuery(query);
return(monster);
}
/// <summary>
/// Get Student's character
/// </summary>
/// <param name="studentId"></param>
/// <returns>Return Character object</returns>
public Character GetCharacter(int studentId)
{
BaseDaoImpl <Character> baseDao = new BaseDaoImpl <Character>();
string query = String.Format("SELECT c.CharId, c.CharName, c.CharSkill FROM Student s "
+ " INNER JOIN Characters c ON s.CharId = c.CharId WHERE s.StudentID = {0}", studentId);
Character character = baseDao.RetrieveQuery(query);
return(character);
}
/// <summary>
/// Insert Student record who logged in through Facebook
/// </summary>
/// <param name="studentName"></param>
/// <param name="studentEmail"></param>
/// <param name="fbId"></param>
/// <returns>Return int result 1 if insertion query has executed successfully</returns>
public int InsertFacebookStudent(string studentName, string studentEmail, string fbId)
{
BaseDaoImpl <Student> baseDao = new BaseDaoImpl <Student>();
string query = "INSERT INTO Student (StudentName, StudentEmail, FacebookAccountId) " +
"VALUES (@StudentName, @StudentEmail, @FacebookAccountId)";
int result = baseDao.ExecuteQuery(query, new { StudentName = studentName, StudentEmail = studentEmail, FacebookAccountId = fbId });
return(result);
}
/// <summary>
/// Get Monster which belongs to the assignment
/// </summary>
/// <param name="assignmentId"></param>
/// <returns>Return Monster object based on assignmentId</returns>
public Monster GetAssignmentMonster(int assignmentId)
{
BaseDaoImpl <Monster> baseDao = new BaseDaoImpl <Monster>();
string query = String.Format("SELECT m.MonsterId, m.MonsterName FROM Assignment a NATURAL JOIN Monster m " +
"WHERE a.AssignmentId = {0}", assignmentId);
Monster monster = baseDao.RetrieveQuery(query);
return(monster);
}
/// <summary>
/// Insert Student record who logged in through Google
/// </summary>
/// <param name="studentName"></param>
/// <param name="charId"></param>
/// <param name="studentEmail"></param>
/// <param name="studentUsername"></param>
/// <param name="studentPassword"></param>
/// <returns>Return int result 1 if insertion query has executed successfully</returns>
public int InsertGoogleStudent(string studentName, string studentEmail, string googleId)
{
BaseDaoImpl <Student> baseDao = new BaseDaoImpl <Student>();
string query = "INSERT INTO Student (StudentName, StudentEmail, GoogleAccountId) " +
"VALUES (@StudentName, @StudentEmail, @GoogleAccountId)";
int result = baseDao.ExecuteQuery(query, new { StudentName = studentName, StudentEmail = studentEmail, GoogleAccountId = googleId });
return(result);
}
/// <summary>
/// Check if Student who has logged in with Facebook account has an existing Chrracter
/// </summary>
/// <param name="fbId"></param>
/// <returns>Return true if Student has existing Character</returns>
public bool CheckFacebookCharExist(string fbId)
{
string query = "SELECT COUNT(1) FROM Student WHERE FacebookAccountId = @FacebookAccountId AND CharId != ''";
BaseDaoImpl <bool> baseDao = new BaseDaoImpl <bool>();
var studentObj = new { FacebookAccountId = fbId };
bool exist = baseDao.ExecuteScalar(query, studentObj);
return(exist);
}
/// <summary>
/// Check if there is an existing Student's Google account
/// </summary>
/// <param name="googleId"></param>
/// <returns>Return true if Student's Google account exists</returns>
public bool CheckGoogleExist(string googleId)
{
string query = "SELECT COUNT(1) FROM Student WHERE GoogleAccountId = @GoogleAccountId";
BaseDaoImpl <bool> baseDao = new BaseDaoImpl <bool>();
var studentObj = new { GoogleAccountId = googleId };
bool exist = baseDao.ExecuteScalar(query, studentObj);
return(exist);
}
/// <summary>
/// Delete selected CustomLevel
/// </summary>
/// <param name="customLevelId"></param>
/// <returns>Return 1 if delete query has executed succesfully</returns>
public int DeleteCustomLevel(int customLevelId)
{
BaseDaoImpl <Object> baseDao = new BaseDaoImpl <Object>();
string query = String.Format("DELETE FROM CustomLevel WHERE CustomLevelId = @CustomLevelId", customLevelId);
int result = baseDao.ExecuteQuery(query, new { CustomLevelId = customLevelId });
return(result);
}
/// <summary>
/// Check if Student has an existing account
/// </summary>
/// <param name="studentId"></param>
/// <returns>Return true if Student's record exist</returns>
public bool CheckStudentExist(int studentId)
{
string query = "SELECT COUNT(1) FROM Student WHERE StudentId = @StudentId";
BaseDaoImpl <bool> baseDao = new BaseDaoImpl <bool>();
var studentObj = new { StudentId = studentId };
bool exist = baseDao.ExecuteScalar(query, studentObj);
return(exist);
}
/// <summary>
/// Insert Student's score of completed Assignment
/// </summary>
/// <param name="studentId"></param>
/// <param name="assignmentId"></param>
/// <param name="assignmentScore"></param>
/// <returns>Return int 1 if insertion query executed successfully</returns>
public int InsertAssignmentScore(int studentId, int assignmentId, int assignmentScore)
{
BaseDaoImpl <Object> baseDao = new BaseDaoImpl <Object>();
string query = "INSERT INTO AssignmentScore (StudentId , AssignmentId ,Score) " +
"VALUES(@StudentId, @AssignmentId, @Score) " +
"ON DUPLICATE KEY UPDATE Score = @Score";
int result = baseDao.ExecuteQuery(query, new { StudentId = studentId, AssignmentId = assignmentId, Score = assignmentScore });
return(result);
}
/// <summary>
/// Insert StudentScore of cleared level
/// </summary>
/// <param name="studentId"></param>
/// <param name="worldId"></param>
/// <param name="sectionId"></param>
/// <param name="levelId"></param>
/// <param name="levelScore"></param>
/// <returns>Return int result 1 if insertion query has executed successfully</returns>
public int InsertStudentScore(int studentId, int worldId, int sectionId, int levelId, int levelScore)
{
BaseDaoImpl <int> baseDao = new BaseDaoImpl <int>();
string query = String.Format("INSERT INTO StudentScore (StudentId, WorldId, SectionId, LevelId, LevelScore) " +
"VALUES (@StudentId, @WorldId, @SectionId, @LevelId, @LevelScore) ON DUPLICATE KEY UPDATE LevelScore = @LevelScore",
studentId, worldId, sectionId, levelId, levelScore);
int result = baseDao.ExecuteQuery(query, new { StudentId = studentId, WorldId = worldId, SectionId = sectionId, LevelId = levelId, LevelScore = levelScore });
return(result);
}
/// <summary>
/// Get the total number of CompletedWorld for the Student
/// </summary>
/// <returns>Return the total count of Completed Worlds</returns>
public int GetCompletedWorldCount()
{
string query = String.Format("SELECT COUNT(1) FROM World_LastSection_LastLevel w, StudentScore s " +
"WHERE s.StudentId = {0} AND w.WorldId = s.WorldId AND w.LastSectionId = s.SectionId AND w.LastLevelId = s.LevelId;"
, Global.StudentId);
BaseDaoImpl <int> baseDao = new BaseDaoImpl <int>();
return(baseDao.RetrieveQuery(query));
}
/// <summary>
/// Insert Student's CustomLevelScore of cleared CustomLevel
/// </summary>
/// <param name="studentId"></param>
/// <param name="customLevelId"></param>
/// <param name="levelScore"></param>
/// <return>Return 1 if query has executed successfully</returns>
public int InsertCustomLevelScore(int studentId, int customLevelId, int levelScore)
{
BaseDaoImpl <Object> baseDao = new BaseDaoImpl <Object>();
string query = "INSERT INTO CustomLevelScore (StudentId , CustomLevelId ,LevelScore) " +
"VALUES(@StudentId, @CustomLevelId, @LevelScore) " +
"ON DUPLICATE KEY UPDATE LevelScore = @LevelScore";
int result = baseDao.ExecuteQuery(query, new { StudentId = studentId, CustomLevelId = customLevelId, LevelScore = levelScore });
return(result);
}
/// <summary>
/// Check if Student has cleared the selected World's Section
/// </summary>
/// <param name="worldId"></param>
/// <param name="sectionId"></param>
/// <param name="studentId"></param>
/// <returns>Return int result 1 if Student has cleared the section</returns>
public int CheckSectionCleared(int worldId, int sectionId, int studentId)
{
string query = String.Format("SELECT LevelId FROM ( SELECT l.LevelId FROM Level l WHERE l.WorldId = {0} AND l.SectionId = {1} UNION ALL " +
"SELECT ss.LevelId FROM StudentScore ss WHERE ss.WorldId = {0} AND ss.SectionId = {1} AND ss.StudentId = {2}) tbl " +
"GROUP BY LevelId HAVING count(*) = 1 ORDER BY LevelId", worldId, sectionId, studentId);
BaseDaoImpl <int> baseDao = new BaseDaoImpl <int>();
int result = baseDao.ExecuteScalar(query);
return(result);
}
/// <summary>
/// Insert new custom level into database
/// </summary>
/// <param name="levelName"></param>
/// <param name="monsterId"></param>
/// <param name="timeLimit"></param>
public void InsertCustomLevel(string levelName, int monsterId, int timeLimit)
{
this.levelName = levelName;
this.monsterId = monsterId;
this.timeLimit = timeLimit;
string query = String.Format("INSERT INTO CustomLevel(StudentId, CustomLevelName, MonsterId, TimeLimit) " +
"VALUES({0}, '{1}', {2}, {3});", Global.StudentId, levelName, monsterId, timeLimit);
BaseDaoImpl <int> baseDao = new BaseDaoImpl <int>();
int result = baseDao.ExecuteQuery(query);
}
/// <summary>
/// Get the CustomLevelId
/// </summary>
/// <param name="option1"></param>
/// <param name="option2"></param>
/// <param name="option3"></param>
/// <param name="correctOption"></param>
/// <param name="questionTitle"></param>
/// <returns> Return the acquired CustomLevelId</returns>
private int GetCustomLevelId(string option1, string option2, string option3, string correctOption, string questionTitle)
{
string query = String.Format(
"SELECT DISTINCT CustomLevelId FROM Question NATURAL JOIN CustomLevel " +
"WHERE StudentId = {0} AND CustomLevelName = '{1}' AND QuestionTitle = '{2}' " +
"AND Option1 = '{3}' AND Option2 = '{4}' AND Option3 = '{5}' AND CorrectOption = '{6}' " +
"AND MonsterId = {7} AND TimeLimit = {8};",
Global.StudentId, levelName, questionTitle, option1,
option2, option3, correctOption, monsterId, timeLimit);
BaseDaoImpl <int> baseDao = new BaseDaoImpl <int>();
int customLevelId = baseDao.RetrieveQuery(query);
return(customLevelId);
}
/// <summary>
/// Check database for existing Level Name
/// </summary>
/// <param name="name"></param>
/// <returns>Return -1 if there is existing level name, else return 1</returns>
public static int CheckValidLevelName(string name)
{
string query = String.Format("SELECT CustomLevelName FROM CustomLevel cl WHERE StudentId = '{0}' AND CustomLevelName = '{1}'; ", Global.StudentId, name);
BaseDaoImpl <string> baseDao = new BaseDaoImpl <string>();
name = baseDao.RetrieveQuery(query);
if (name == null)
{
return(1);
}
else
{
return(-1);
}
}
/// <summary>
/// Check database for existing level name
/// </summary>
/// <param name="oldName"></param>
/// <param name="newName"></param>
/// <returns>Return -1 if there is existing level name, else return 1 </returns>
public static int CheckValidLevelName(string oldName, string newName)
{
//student
string query = String.Format("SELECT CustomLevelName FROM(SELECT * FROM CustomLevel c2 WHERE c2.CustomLevelName <> '{0}') AS c " +
"WHERE c.StudentId = {1} AND c.CustomLevelName = '{2}'; ", oldName, Global.StudentId, newName);
//teacher
/*string query = String.Format("SELECT AssignmentName FROM(SELECT * FROM `Assignment` a2 WHERE a2.AssignmentName <> '{1}') AS a " +
* "WHERE TeacherId = {0} AND AssignmentName = '{1}'; ", Global.TeacherId, name);*/
BaseDaoImpl <string> baseDao = new BaseDaoImpl <string>();
string name = baseDao.RetrieveQuery(query);
if (name == null)
{
return(1);
}
return(-1);
}
/// <summary>
/// Update Question into database
/// </summary>
/// <param name="option1"></param>
/// <param name="option2"></param>
/// <param name="option3"></param>
/// <param name="option4"></param>
/// <param name="correctOptionInt"></param>
/// <param name="questionTitle"></param>
/// <param name="questionId"></param>
public void UpdateQuestion(string option1, string option2, string option3, string option4, int correctOptionInt, string questionTitle, int questionId)
{
switch (correctOptionInt)
{
case 1:
FormatForDatabaseInsertion(ref option4, ref option1);
break;
case 2:
FormatForDatabaseInsertion(ref option4, ref option2);
break;
case 3:
FormatForDatabaseInsertion(ref option4, ref option3);
break;
default:
break;
}
string query = String.Format("UPDATE Question SET Option1 = '{0}', Option2 = '{1}', " +
"Option3 = '{2}', CorrectOption = '{3}', QuestionTitle = '{4}' " +
"WHERE QuestionId = {5}", option1, option2, option3, option4, questionTitle, levelInfo.Question[questionId].QuestionId);
BaseDaoImpl <int> baseDao = new BaseDaoImpl <int>();
int result = baseDao.ExecuteQuery(query);
if (result <= 0)
{
GD.Print("Error updating question into database.");
}
else
{
GD.Print("Question updated into database successfully.");
}
}