public void deleteQuestion(Question question)
{
logger.Debug("deleteQuestion()");
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
string query = "delete from question where id = " + question.Id + ";";
string answersIDs = "";
foreach (Answer answer in question.AnswerList)
answersIDs += answer.Id.ToString() + ",";
if (!String.IsNullOrEmpty(answersIDs))
answersIDs = answersIDs.Substring(0, answersIDs.Length - 1);
mysql.initializeConnection();
mysql.openMysqlConnection();
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
cmd.ExecuteNonQuery();
query = "delete from answer where id in (" + answersIDs + ");";
if (!String.IsNullOrEmpty(answersIDs))
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
cmd.ExecuteNonQuery();
mysql.closeMysqlConnection();
logger.Info("Question With ID " + question.Id + " Deleted");
}
private int getUserID()
{
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.initializeConnection();
mysql.openMysqlConnection();
string query = "select id from account where username = '******'";
int userID = -1;
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
userID = reader.GetInt32(0);
}
}
}
}
mysql.closeMysqlConnection();
return userID;
}
public void deleteQuestionaire(int question_id)
{
String query = "select * from questionaire";
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.openMysqlConnection();
MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection);
MySqlDataReader dr = cmd.ExecuteReader();
//Execute
mysql.closeMysqlConnection();
}
public List<Answer> findAnswer(int question_id)
{
logger.Debug("findAnswer()");
string query = "select id,answer,correct,create_date,account_id from answer A "
+ " inner join question_answer QA on QA.answer_id = A.id where QA.question_id = " + question_id;
List<Answer> listAnswer = new List<Answer>();
Answer tempAnswer;
int answer_id;
string answer;
bool correct;
DateTime date_answer;
int account_id;
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.initializeConnection();
mysql.openMysqlConnection();
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
using (MySqlDataReader reader = cmd.ExecuteReader())
if (reader.HasRows)
while (reader.Read())
{
answer_id = reader.GetInt32(0);
answer = reader.GetString(1);
correct = reader.GetInt32(2) == 1 ? true : false;
date_answer = reader.GetDateTime(3).Date;
account_id = reader.GetInt32(4);
tempAnswer = new Answer() { Id = answer_id, Answer_descr = answer, Correct = correct, Date = date_answer, Account = new Account() { Id = account_id } };
listAnswer.Add(tempAnswer);
}
mysql.closeMysqlConnection();
return listAnswer;
}
public void deleteAnswer(Answer answer)
{
logger.Debug("deleteAnswer()");
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.initializeConnection();
mysql.openMysqlConnection();
string query = "delete from answer where id = " + answer.Id + "";
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
cmd.ExecuteNonQuery();
mysql.closeMysqlConnection();
}
public static void MyClassInitialize(TestContext testContext)
{
mysql = new MysqlConnector("root", "Megastructures91", "127.0.0.1", "3306", "gps_zero");
mysql.initializeConnection();
}
private bool usernameExist(string username)
{
bool usernameExist = false;
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
string query = "select username from account where username = '******'";
mysql.initializeConnection();
mysql.openMysqlConnection();
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
using (MySqlDataReader reader = cmd.ExecuteReader())
if (reader.HasRows)
usernameExist = true;
mysql.closeMysqlConnection();
return usernameExist;
}
public List<Question> findQuestion(string usernameF,Subject subject, Department department)
{
logger.Debug("findQuestion()");
List<Question> questionList = new List<Question>();
string query = "";
if(usernameF != "all")
query = "select Q.id as question_id,Q.question,Q.create_date,A.id as user_id,Q.level_range,A.username from question Q " +
"inner join account A on A.id = Q.account_id " +
"inner join subject_department SD on Q.subject_department_id = SD.id where SD.subject_id = " + subject.Id + " and SD.department_id = " + department.Id + " and A.username = '******'; ";
else
query = "select Q.id as question_id,Q.question,Q.create_date,A.id as user_id,Q.level_range,A.username from question Q " +
"inner join account A on A.id = Q.account_id " +
"inner join subject_department SD on Q.subject_department_id = SD.id where SD.subject_id = " + subject.Id + " and SD.department_id = " + department.Id + " ; ";
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.initializeConnection();
mysql.openMysqlConnection();
//Question Fields
int question_id;
string question_descr;
int level_question;
DateTime date_question;
//Account Fields
int account_id;
string username;
//Questions
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader != null)
{
while (reader.Read())
{
question_id = reader.GetInt32(0);
question_descr = reader.GetString(1);
date_question = reader.GetDateTime(2).Date;
Debug.Write("NOWWWWWWWWWW");
Debug.Write(date_question.ToString("MM/dd/yyyy"));
account_id = reader.GetInt32(3);
level_question = reader.GetInt32(4);
username = reader.GetString(5);
questionList.Add(new Question() { Id = question_id, Question_descr = question_descr, Account = new Account() { Username = username }, Subject = subject, Department = department, Level = level_question, Date = date_question });
}
}
} // reader closed and disposed up here
} // command disposed here
mysql.closeMysqlConnection();
//Answer of Above Questions
if (questionList.Any())
{
mysql.openMysqlConnection();
setAnsersForIndividualQuestion(questionList, mysql);
mysql.closeMysqlConnection();
}
//Debug
foreach (Question question in questionList)
{
Debug.WriteLine(question.Question_descr);
foreach (Answer answer in question.AnswerList)
Debug.WriteLine(answer.Answer_descr);
}
return questionList;
}
public void updateQuestion(Question question)
{
string query = "select SD.id from subject_department SD where SD.subject_id=" + question.Subject.Id + " and SD.department_id=" + question.Department.Id + " ";
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.initializeConnection();
mysql.openMysqlConnection();
int subjectdepartment_id = -1;
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader != null)
while (reader.Read())
{
subjectdepartment_id = reader.GetInt32(0);
}
}
}
string formatForMySql = question.Date.ToString("yyyy-MM-dd");
query = "update question set question = '" + question.Question_descr + "',level_range=" + question.Level + ",create_date='" + formatForMySql + "',subject_department_id = " + subjectdepartment_id + " where id = " + question.Id + "";
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
cmd.ExecuteNonQuery();
mysql.closeMysqlConnection();
}
public void setAnsersForIndividualQuestion(List<Question> questionList, MysqlConnector mysql)
{
if (questionList == null)
return;
List<Answer> answerList = new List<Answer>();
string query;
//Answer Fields
int answer_id;
string answer_descr;
DateTime date_answer;
bool correct_answer;
foreach (Question question in questionList)
{
query = "select id as answer_id,answer,create_date,correct from answer A inner join question_answer QA on A.id = QA.answer_id and QA.question_id=" + question.Id + ";";
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader != null)
{
while (reader.Read())
{
answer_id = reader.GetInt32(0);
answer_descr = reader.GetString(1);
date_answer = reader.GetDateTime(2).Date;
correct_answer = (reader.GetInt32(3) == 0 ? false : true);
answerList.Add(new Answer() { Id = answer_id, Answer_descr = answer_descr, Date = date_answer, Correct = correct_answer, Account = question.Account });
}
question.AnswerList = answerList;
answerList = new List<Answer>();
}
} // reader closed and disposed up here
} // command disposed here
}
}
public void saveQuestion(Question question)
{
string query = "select SD.id from subject_department SD where SD.subject_id=" + question.Subject.Id + " and SD.department_id=" + question.Department.Id + " ";
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.initializeConnection();
mysql.openMysqlConnection();
long subjectdepartment_id = -1;
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader != null)
while (reader.Read())
{
subjectdepartment_id = reader.GetInt32(0);
}
}
}
string formatForMySql = question.Date.ToString("yyyy-MM-dd");
query = "insert into question(question,level_range,create_date,account_id,subject_department_id) values('" + question.Question_descr + "'," + question.Level + ",'" + formatForMySql + "'," + question.Account.Id + "," + subjectdepartment_id + ")";
long new_questionId;
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
{
cmd.ExecuteNonQuery();
new_questionId = cmd.LastInsertedId;
}
question.Id = (int)new_questionId;
mysql.closeMysqlConnection();
}
public void saveAnswer(Answer answer, int question_id)
{
logger.Debug("saveAnswer()");
string formatForMySql = answer.Date.ToString("yyyy-MM-dd");
string query = "insert into answer(answer,create_date,correct,account_id) values('" + answer.Answer_descr + "','" + formatForMySql + "'," + (answer.Correct ? 1 : 0) + "," + answer.Account.Id + ")";
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.initializeConnection();
mysql.openMysqlConnection();
long new_answer_id;
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
{
cmd.ExecuteNonQuery();
new_answer_id = cmd.LastInsertedId;
}
query = "insert into question_answer(question_id,answer_id) values(" + question_id + "," + new_answer_id + ")";
logger.Info("Answer iD " + new_answer_id);
logger.Info("Question ID " + question_id);
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
cmd.ExecuteNonQuery();
answer.Id = (int)new_answer_id;
mysql.closeMysqlConnection();
}
public void updateAnswer(Answer answer)
{
logger.Debug("updateAnswer()");
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.initializeConnection();
mysql.openMysqlConnection();
string formatForMySql = answer.Date.ToString("yyyy-MM-dd");
string query = "update answer set answer='" + answer.Answer_descr + "',create_date='" + formatForMySql + "',correct=" + (answer.Correct ? 1 : 0) + ",account_id=" + answer.Account.Id + " where id=" + answer.Id + "";
using (MySqlCommand cmd = new MySqlCommand(query, mysql.MysqlConnection))
cmd.ExecuteNonQuery();
mysql.closeMysqlConnection();
}
private void Login_button_Click(object sender, RoutedEventArgs e)
{
if (Login_username_textBox.Text.Equals(""))
{
Login_image_error_user_name.Visibility = Visibility.Visible;
}
else if (Login_passwordBox.Password.Equals(""))
{
Login_image_error_password.Visibility = Visibility.Visible;
}
else
{
//Graph of processing.
Login_Process.Visibility = Visibility.Visible;
//Graph of errors
Login_image_error_user_name.Visibility = Visibility.Hidden;
Login_image_error_password.Visibility = Visibility.Hidden;
//BY DEFALYT CONNECTION!!!
MysqlConnector connector = new MysqlConnector(Login_username_textBox.Text, Login_passwordBox.Password, CurrentUserInfo.HOSTNAME, CurrentUserInfo.DATABASE);
connector.initializeConnection();
try
{
connector.openMysqlConnection();
//Stop the graphics.
Login_Process.Visibility = Visibility.Hidden;
}
catch (Exception ee)
{
MessageBox.Show("Authentication Failed");
return;
}
//Retrive the connection string
//WORKS
//String connection = (new Model.RetriveStringConnection()).get_sc();
//Close the form.
//take the username and the password from reg to make connection
//this.Login_username_textBox.Text = reg.Registration_username_textBox.Text;
CurrentUserInfo.USERNAME = Login_username_textBox.Text;
CurrentUserInfo.PASSWORD = Login_passwordBox.Password.ToString();
CurrentUserInfo.ID = getUserID();
CurrentUserInfo.CURENT_ACCOUNT = new Account() { Id = CurrentUserInfo.ID, Username = CurrentUserInfo.USERNAME, Password = CurrentUserInfo.PASSWORD };
NavigationService nav = NavigationService.GetNavigationService(this);
nav.Navigate(new Uri("MainMenu.xaml", UriKind.RelativeOrAbsolute));
//CALL the menu from Costas. pass the password and the username
//this.Login_exit_button_Click(sender, e);
MessageBox.Show("Authentication Succed");
}
}
public List<Subject> findSubject()
{
List<Subject> subjectList = new List<Subject>();
List<Department> depList = new List<Department>();
String querySubjects = "select id,subject from subject";
String queryDepartments;
MysqlConnector mysql = new MysqlConnector(CurrentUserInfo.USERNAME,
CurrentUserInfo.PASSWORD,
CurrentUserInfo.HOSTNAME,
CurrentUserInfo.PORT,
CurrentUserInfo.DATABASE);
mysql.initializeConnection();
mysql.openMysqlConnection();
int subject_id, department_id;
string subject, department;
using (MySqlCommand cmd = new MySqlCommand(querySubjects, mysql.MysqlConnection))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader != null)
{
while (reader.Read())
{
subject_id = Int32.Parse(reader.GetString(0));
subject = reader.GetString(1);
subjectList.Add(new Subject() { Id = subject_id, Subject_descr = subject });
}
}
} // reader closed and disposed up here
} // command disposed here
foreach (Subject sub in subjectList)
{
queryDepartments = "select subject_id,department_id,department from subject_department SD inner join department S on S.id=SD.department_id " +
"AND SD.subject_id = " + sub.Id + ";";
using (MySqlCommand cmd = new MySqlCommand(queryDepartments, mysql.MysqlConnection))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader != null)
{
while (reader.Read())
{
Debug.WriteLine(reader.GetString(1));
department_id = reader.GetInt32(1);
department = reader.GetString(2);
depList.Add(new Department() { Department_descr = department, Id = department_id });
}
sub.DepList = depList;
depList = new List<Department>();
}
} // reader closed and disposed up here
} // command disposed here
}
return subjectList;
}