private void check_teacher_login()
{
if (txtPassword.Text == string.Empty || txtSchool.Text == string.Empty || txtUsername.Text == string.Empty)
{
MessageBox.Show("Sorry,\nAll textboxes must be filled");//tells user not enough details entered
}
else
{
string hashed_password = create_hash(txtPassword.Text);//Hashes the password to check against the copy on the database
clsDBConnector dbConnector = new clsDBConnector();
OleDbDataReader dr;
string sqlStr;
dbConnector.Connect();
sqlStr = "SELECT tblTeachers.Teacher_ID FROM (tblSchools INNER JOIN tblTeachers ON tblSchools.School_ID = tblTeachers.School_ID) WHERE (tblTeachers.UserName = '******') AND (tblTeachers.pwdHash = '" + hashed_password + "') AND (tblSchools.School_Name = '" + txtSchool.Text.ToLower() + "')";
dr = dbConnector.DoSQL(sqlStr);
if (dr.HasRows) //Checks if a teacher exists with that username, school and password
{
frmTeacher frmTeacher = new frmTeacher(txtUsername.Text); //Shows the teacher form as the logged teacher
frmTeacher.ShowDialog();
this.Close();
}
else
{
check_User_login();//Checks for a student if no teachers exist with those login details
}
}
}
private void nationwideYearLastScoreToolStripMenuItem_Click(object sender, EventArgs e)
{
clsDBConnector dbConnector = new clsDBConnector();
OleDbDataReader dr;
string sqlStr;
string avg_Score_string = "0";
dbConnector.Connect();
sqlStr = " SELECT AVG(last_score) AS last_scoreAVG" +
" FROM tblUsers" +
" WHERE School_Year = " + School_Year + "";
dr = dbConnector.DoSQL(sqlStr);
while (dr.Read())
{
avg_Score_string = (dr[0].ToString());
}
double avg_score = Convert.ToDouble(avg_Score_string);
avg_Score_string = avg_score.ToString("N0");
string caption = "Average";//displays the average calculated to 0 decimal places in a messagebox
string message = "The average last Score is " + avg_Score_string + " points.";
DialogResult result;
result = MessageBox.Show(message, caption);
}
private void btnEdit_Click(object sender, EventArgs e)
{
if (txtPwd.Text == string.Empty)//doesn't set the password to blank if no new password specified
{
clsDBConnector dbConnector = new clsDBConnector();
string cmdStr = "UPDATE tblUsers " +
"SET UserName ='******', RealName ='" + txtName.Text +
"', School_Year = " + nudSchoolYear.Value +
"\nWHERE (User_ID = " + editing_ID + ")";//makes sure only the details of the student being edited are changed
dbConnector.Connect();
dbConnector.DoDML(cmdStr);
dbConnector.close();
}
else if (txtPwd.Text != string.Empty) // password reset as well as new password is specified
{
clsDBConnector dbConnector = new clsDBConnector();
string cmdStr = "UPDATE tblUsers " +
"SET UserName ='******', RealName ='" + txtName.Text +
"', pwdHash ='" + hash_password(txtPwd.Text) +//Hashes the password before it is sent to the database
"', School_Year = " + nudSchoolYear.Value + " " +
"\nWHERE (User_ID = " + editing_ID + ")";
dbConnector.Connect();
dbConnector.DoDML(cmdStr);
dbConnector.close();
}
lblLastAction.Text = "User:"******" edited";
}
private void removeUser(int ID)
{
clsDBConnector dbConnector = new clsDBConnector();
string cmdStr = "DELETE FROM tblUsers " +//Deletes a user specified as ID
"WHERE (User_ID = " + ID + ")";
dbConnector.Connect();
dbConnector.DoDML(cmdStr);
dbConnector.close();
}
private void add_user(string hashed_password)
{
clsDBConnector dbConnector = new clsDBConnector();
string cmdStr = "INSERT INTO tblUsers (UserName, RealName, pwdHash, School_ID, Teacher_ID, School_Year) " +
"VALUES ('" + txtUser.Text + "' , '" + clean_string(txtName.Text) + "' , '" + hashed_password + "' , '" + school + "' , '" + teacher + "' , '" + nudSchoolYear.Value + "')";
dbConnector.Connect();
dbConnector.DoDML(cmdStr);
dbConnector.close();
}
private void update_highScore(int score)
{
clsDBConnector dbConnector = new clsDBConnector();
string cmdStr = "UPDATE tblUsers " +
"SET Hi_Score =" + score +
"\nWHERE (User_ID = " + user_ID + ")";
dbConnector.Connect();
dbConnector.DoDML(cmdStr);
dbConnector.close();
}
private void tsmiRemoveUser_Click(object sender, EventArgs e)
{
List <int> removing_ID = new List <int>();
int count = 0;
foreach (var item in lstVStudents.SelectedItems)
{
removing_ID.Add(Convert.ToInt32(lstVStudents.SelectedItems[count].Text));//generates an ID for all students to be removed
count++;
}
clsDBConnector dbConnector = new clsDBConnector();
OleDbDataReader dr;
dbConnector.Connect();
List <string> removing_Names = new List <string>();
foreach (var ID in removing_ID)//Gets the real names for every selected user
{
string sqlStr = "SELECT RealName FROM tblUsers WHERE (User_ID = " + ID + ")";
dr = dbConnector.DoSQL(sqlStr);
while (dr.Read())
{
removing_Names.Add(Convert.ToString(dr[0]));
}
}
string message = "";
//string message = string.Join(", ", removing_Names, ", ",removing_ID);
int counter = 0;
foreach (var item in removing_ID)//generates a string telling the teacher what users will be removed with their names and IDs
{
message += removing_Names[counter];
message += " (";
message += removing_ID[counter];
message += ")\n";
counter++;
}
if (MessageBox.Show("The following users will be removed\n" + message + "\nThis cannot be undone?", "REMOVE USERS", MessageBoxButtons.OKCancel) == DialogResult.OK)//Makes sure the teacher only deletes users if they want to
{
count = 0;
foreach (var IDs in removing_ID)//Removes each ID selected 1 after another
{
removeUser(IDs);
}
MessageBox.Show("Users Deleted");//Tells the teacher the process was succesful
}
else
{
MessageBox.Show("No changes made");//Tells the teacher they succesfully cancelled the remove action
}
}
private int get_schoolID()
{
clsDBConnector dbConnector = new clsDBConnector();
OleDbDataReader dr;
string sqlStr;
dbConnector.Connect();
sqlStr = "SELECT School_ID FROM tblTeachers WHERE (UserName = '******')";//Selects the SchoolID for the logged teacher
dr = dbConnector.DoSQL(sqlStr);
while (dr.Read())
{
School_ID = Convert.ToInt32(dr[0]);
}
return(School_ID);
}
private void get_userIDs()//Gets the needed IDs of the logged user
{
clsDBConnector dbConnector = new clsDBConnector();
OleDbDataReader dr;
string sqlStr;
dbConnector.Connect();
sqlStr = "SELECT User_ID, Teacher_ID FROM tblUsers WHERE (UserName = '******')";
dr = dbConnector.DoSQL(sqlStr);
while (dr.Read())
{
user_ID = Convert.ToInt32(dr[0]);
teacher_ID = Convert.ToInt32(dr[1]);
}
}
private void get_info(int editing_ID)
{
clsDBConnector dbConnector = new clsDBConnector();
OleDbDataReader dr;
string sqlStr;
dbConnector.Connect();
sqlStr = "SELECT UserName, RealName, School_Year" +
" FROM tblUsers" +
" WHERE User_ID = " + editing_ID;
dr = dbConnector.DoSQL(sqlStr);
while (dr.Read())
{
txtUser.Text = dr[0].ToString();
txtName.Text = dr[1].ToString();
nudSchoolYear.Value = Convert.ToInt32(dr[2]);
}
}
private int get_HighScore()
{
int score = 0;
clsDBConnector dbConnector = new clsDBConnector();
OleDbDataReader dr;
string sqlStr;
dbConnector.Connect();
sqlStr = "SELECT Hi_Score" +
" FROM tblUsers" +
" WHERE User_ID = " + user_ID;
dr = dbConnector.DoSQL(sqlStr);
while (dr.Read())
{
score = Convert.ToInt32(dr[0]);
}
return(score);//Returns the current high score of the logged user
}
private void update_Score(int score)
{
clsDBConnector dbConnector = new clsDBConnector();
string cmdStr = "UPDATE tblUsers " +
"SET last_score =" + score +
"\nWHERE (User_ID = " + user_ID + ")";//Sets the last score to equal the score
dbConnector.Connect();
dbConnector.DoDML(cmdStr);
dbConnector.close();
int high_score = get_HighScore(); //gets the users current high score
if (score > high_score) //checks if the new score is greater than current high score
{
MessageBox.Show("You got a new High Score Your new score is " + score + "Your old score was " + high_score, "New High Score!");
update_highScore(score);//updates the highscore on the database
}
}
private void load_Students()
{
clsDBConnector dbConnector = new clsDBConnector();
OleDbDataReader dr;
string sqlStr;
dbConnector.Connect();
sqlStr = "SELECT User_ID, RealName, TimePlayed, Hi_Score, last_score FROM tblUsers WHERE (Teacher_ID = " + teacher_ID + ")"; //Selects all users with the teacherID set to the logged teacher
dr = dbConnector.DoSQL(sqlStr);
lstVStudents.Items.Clear();
while (dr.Read())
{
lstVStudents.Items.Add(dr[0].ToString());
lstVStudents.Items[lstVStudents.Items.Count - 1].SubItems.Add(dr[1].ToString());
lstVStudents.Items[lstVStudents.Items.Count - 1].SubItems.Add(dr[2].ToString());
lstVStudents.Items[lstVStudents.Items.Count - 1].SubItems.Add(dr[3].ToString());
lstVStudents.Items[lstVStudents.Items.Count - 1].SubItems.Add(dr[4].ToString());
}
}
private void check_User_login()
{
string hashed_password = create_hash(txtPassword.Text);
clsDBConnector dbConnector = new clsDBConnector();
OleDbDataReader dr;
string sqlStr;
dbConnector.Connect();
sqlStr = "SELECT tblUsers.User_ID FROM ((tblSchools INNER JOIN tblTeachers ON tblSchools.School_ID = tblTeachers.School_ID) INNER JOIN tblUsers ON tblSchools.School_ID = tblUsers.School_ID AND tblTeachers.Teacher_ID = tblUsers.Teacher_ID) WHERE (tblUsers.UserName = '******') AND (tblUsers.pwdHash = '" + hashed_password + "') AND (tblSchools.School_Name = '" + txtSchool.Text.ToLower() + "')";
dr = dbConnector.DoSQL(sqlStr);
if (dr.HasRows) // Checks if a student exists with that username, school and password
{
frmOperators frmOperator = new frmOperators(txtUsername.Text); //Shows the Operator form as the logged user
frmOperator.ShowDialog();
this.Close();
}
else
{
MessageBox.Show("Login Failed");//tells user their login failed
}
}
