public static void DeleteSubject(string subjectName)
{
/*
* Deletes the specified subject and groups
* with that subject, and those groups linked with staff.
*/
int subjectId = GetSubjectIdByName(subjectName);
SqlCommand comm = new SqlCommand("SELECT GroupId FROM Groups WHERE SubjectId = @SubjectId");
comm.Parameters.AddWithValue("@SubjectId", subjectId);
DataTable dt = SqlTools.GetTable(comm);
SqlCommand deleteCommand = new SqlCommand();
SqlParameter groupId = new SqlParameter("@GroupId", "");
deleteCommand.Parameters.Add(groupId);
for (int i = 0; i < dt.Rows.Count; i++)
{
deleteCommand.CommandText = "DELETE FROM StaffGroupsLink WHERE GroupId = @GroupId";
groupId.Value = (int)dt.Rows[i]["GroupId"];
SqlTools.ExecuteNonQuery(deleteCommand);
}
comm.CommandText = "DELETE FROM Groups WHERE SubjectId = @SubjectId";
SqlTools.ExecuteNonQuery(comm);
comm.CommandText = "DELETE FROM Subjects WHERE SubjectName = @SubjectName";
comm.Parameters.AddWithValue("@SubjectName", subjectName);
SqlTools.ExecuteNonQuery(comm);
AdminForm.RefreshLists();
}
public static BindingList <string> GetStaff(string groupName)
{
/*
* getStaff returns a List of strings of
* the staff within group specified.
*
* Arguments:
* groupName (string): Name of the group to find the staff members of.
*/
BindingList <string> staffList = new BindingList <string>();
// Returns a table with the staff members at that GroupID using StaffGroupsLink table.
SqlCommand command = new SqlCommand("SELECT Staff.StaffName FROM StaffGroupsLink INNER JOIN Staff ON StaffGroupsLink.StaffId=Staff.StaffId INNER JOIN Groups ON StaffGroupsLink.GroupId=Groups.GroupId WHERE Groups.GroupName = @GroupName");
command.Parameters.AddWithValue("@GroupName", groupName);
// Load the returned table into a new DataTable.
// This is used to grab rows and avoid doing a lot of
// sql querys which is time consuming.
DataTable dt = SqlTools.GetTable(command);
for (int i = 0; i < dt.Rows.Count; i++)
{
staffList.Add(dt.Rows[i]["StaffName"].ToString());
}
return(staffList);
}
public static double[] GetSubjectMLR(int subjectId)
{
/*
* GetSubjectMLR gets the subject MLR line based from previous results of that subject.
* This is stored in the database and refreshed only when an admin modifies previous subject
* results. This is to avoid heavy processing at runtime - the line will never
* change until new exam results are added.
* Read more at GradeUtils.calculateGrade().
*
* Arguments:
* SubjectId (int): Id of the subject to get the MLR of.
*/
SqlCommand command = new SqlCommand("SELECT SubjectWeightConstant, SubjectHomeworkWeight, SubjectTestWeight, SubjectMTGWeight FROM Subjects WHERE SubjectId = @SubjectId");
command.Parameters.AddWithValue("@SubjectId", subjectId);
DataTable dt = SqlTools.GetTable(command);
try
{
// Try to return values. They might not exist for the subject yet so it could throw an error.
return(new double[] { (double)dt.Rows[0]["SubjectWeightConstant"],
(double)dt.Rows[0]["SubjectHomeworkWeight"],
(double)dt.Rows[0]["SubjectTestWeight"],
(double)dt.Rows[0]["SubjectMTGWeight"] });
}
catch (Exception)
{
// If it doesn't exist, don't worry, just return nothing.
return(new double[] { 0, 0, 0, 0 });
}
}
private void PopulateList()
{
SqlCommand comm = new SqlCommand();
if (isHomework)
{
comm.CommandText = "SELECT HomeworkName FROM Homeworks WHERE GroupId=@GroupId";
comm.Parameters.AddWithValue("@GroupId", groupId);
DataTable dt = SqlTools.GetTable(comm);
for (int i = 0; i < dt.Rows.Count; i++)
{
AssignmentsList.Add((string)dt.Rows[i]["HomeworkName"]);
}
}
else
{
comm.CommandText = "SELECT TestName FROM Tests WHERE GroupId=@GroupId";
comm.Parameters.AddWithValue("@GroupId", groupId);
DataTable dt = SqlTools.GetTable(comm);
for (int i = 0; i < dt.Rows.Count; i++)
{
AssignmentsList.Add((string)dt.Rows[i]["TestName"]);
}
}
}
public static void PopulateList()
{
StudentList.Clear();
DataTable dt = SqlTools.GetTable("SELECT StudentName FROM Students");
for (int i = 0; i < dt.Rows.Count; i++)
{
StudentList.Add((string)dt.Rows[i]["StudentName"]);
}
}
public static int GetStudentIdByName(string studentName)
{
/*
* Get a student's database ID by their name
*/
SqlCommand comm = new SqlCommand("SELECT StudentId FROM Students WHERE StudentName=@StudentName");
comm.Parameters.AddWithValue("@StudentName", studentName);
return((int)SqlTools.GetTable(comm).Rows[0]["StudentId"]);
}
private void UpdateTable()
{
dt = SqlTools.GetTable(command);
// Hide ID rows and add user friendly headers
PreviousResultView.DataSource = dt;
PreviousResultView.Columns["ResultId"].Visible = false;
PreviousResultView.Columns["SubjectId"].Visible = false;
PreviousResultView.Columns["HomeworkResult"].HeaderText = "Homework grade average";
PreviousResultView.Columns["TestResult"].HeaderText = "Test grade average";
PreviousResultView.Columns["MTGResult"].HeaderText = "Minimum target grade";
PreviousResultView.Columns["FinalResult"].HeaderText = "Final grade";
}
public static PermissionLevel GetPermissionLevel(int staffId)
{
/*
* Get the permission level of the staff member based on staff ID.
*/
SqlCommand comm = new SqlCommand("SELECT PermissionLevel FROM Staff WHERE StaffId = @StaffId");
comm.Parameters.AddWithValue("@StaffId", staffId);
DataTable dt = SqlTools.GetTable(comm);
return((PermissionLevel)dt.Rows[0]["PermissionLevel"]);
}
public static string GetSubjectName(string groupName)
{
/*
* Gets the subject assinged to a group.
*/
SqlCommand command = new SqlCommand("SELECT Subjects.SubjectName FROM Groups INNER JOIN Subjects ON Groups.SubjectId=Subjects.SubjectID WHERE Groups.GroupName = @GroupName");
command.Parameters.AddWithValue("@GroupName", groupName);
DataTable dt = SqlTools.GetTable(command);
return(dt.Rows[0]["SubjectName"].ToString());
}
public static int GetStudentTableId(string studentName)
{
/*
* GetStudentTableId gets the actual identity value assigned to
* a student in the database by the student name.
*/
SqlCommand comm = new SqlCommand("SELECT StudentId FROM Students WHERE StudentName = @StudentName");
comm.Parameters.AddWithValue("@StudentName", studentName);
DataTable dt = SqlTools.GetTable(comm);
return((int)dt.Rows[0]["StudentId"]);
}
public static string GetStaffNameById(int staffId)
{
/*
* Returns the string of the StaffName based
* on the given string staffId.
*/
SqlCommand comm = new SqlCommand("SELECT StaffName FROM Staff WHERE StaffId = @StaffId");
comm.Parameters.AddWithValue("@StaffId", staffId);
DataTable dt = SqlTools.GetTable(comm);
return((string)dt.Rows[0]["StaffName"]);
}
public static int GetStaffIdByUsername(string staffUsername)
{
/*
* Returns the integer of the StaffId based
* on the given string staffUsername.
*/
SqlCommand comm = new SqlCommand("SELECT StaffId FROM Staff WHERE StaffUsername = @StaffUsername");
comm.Parameters.AddWithValue("@StaffUsername", staffUsername);
DataTable dt = SqlTools.GetTable(comm);
return((int)dt.Rows[0]["StaffId"]);
}
private void PopulateGroupList()
{
teacherGroups.Clear();
SqlCommand command = new SqlCommand("SELECT Groups.GroupName FROM StaffGroupsLink INNER JOIN Staff ON StaffGroupsLink.StaffId=Staff.StaffId INNER JOIN Groups ON StaffGroupsLink.GroupId=Groups.GroupId WHERE StaffGroupsLink.StaffId = @StaffId");
command.Parameters.AddWithValue("@StaffId", StaffId);
DataTable dt = SqlTools.GetTable(command);
for (int i = 0; i < dt.Rows.Count; i++)
{
teacherGroups.Add((string)dt.Rows[i]["GroupName"]);
}
}
private void PopulateStudentsList(int GroupId)
{
studentsList.Clear();
SqlCommand command = new SqlCommand("SELECT StudentName FROM StudentGroupsLink INNER JOIN Students ON StudentGroupsLink.StudentId=Students.StudentId WHERE StudentGroupsLink.GroupId=@GroupId");
command.Parameters.AddWithValue("@GroupId", GroupId);
DataTable dt = SqlTools.GetTable(command);
for (int i = 0; i < dt.Rows.Count; i++)
{
studentsList.Add((string)dt.Rows[i]["StudentName"]);
}
}
public static string GetUsername(string studentName)
{
/*
* getUsername gets the college username of the
* specified student defined by string
* studentName.
*/
SqlCommand comm = new SqlCommand("SELECT StudentCollegeId FROM Students WHERE StudentName = @StudentName");
comm.Parameters.AddWithValue("@StudentName", studentName);
DataTable dt = SqlTools.GetTable(comm);
return((string)dt.Rows[0]["StudentCollegeId"]);
}
public static string GetUsername(string staffName)
{
/*
* getUsername gets the login username of the
* specified staff member defined by string
* staffName.
*/
SqlCommand comm = new SqlCommand("SELECT StaffUsername FROM Staff WHERE StaffName = @StaffName");
comm.Parameters.AddWithValue("@StaffName", staffName);
DataTable dt = SqlTools.GetTable(comm);
return((string)dt.Rows[0]["StaffUsername"]);
}
public static void PopulateList()
{
/*
* Populates the global list, SubjectList,
* with the subjects in the Subjects table.
*/
SubjectList.Clear();
DataTable dt = SqlTools.GetTable("SELECT SubjectName FROM Subjects");
for (int i = 0; i < dt.Rows.Count; i++)
{
SubjectList.Add((string)dt.Rows[i]["SubjectName"]);
}
}
public static void PopulateList()
{
StaffList.Clear();
DataTable dt = SqlTools.GetTable("SELECT StaffName FROM Staff");
for (int i = 0; i < dt.Rows.Count; i++)
{
string name = dt.Rows[i]["StaffName"].ToString();
// Make sure to skip system admin account - we're the admin.
if (name != "System Admin")
{
StaffList.Add(name);
}
}
}
public static int GetGroupIdByName(string groupName)
{
/*
* GetSubjectIdByName gets the SubjectId assigned to a
* subject name and returns the ID.
*
* Arguments:
* subjectName (string): Name of the subject.
*/
SqlCommand command = new SqlCommand("SELECT GroupId FROM Groups WHERE GroupName = @GroupName");
command.Parameters.AddWithValue("@GroupName", groupName);
DataTable dt = SqlTools.GetTable(command);
return((int)dt.Rows[0]["GroupId"]);
}
public static int GetYearIdByName(string academicYearName)
{
/*
* GetYearIdByName gets the AcademicYearId assigned to a
* year name and returns the ID.
*
* Arguments:
* academicYearName (string): Name of the academic year.
*/
SqlCommand command = new SqlCommand("SELECT AcademicYearId FROM AcademicYears WHERE AcademicYearName = @AcademicYearName");
command.Parameters.AddWithValue("@AcademicYearName", academicYearName);
DataTable dt = SqlTools.GetTable(command);
return((int)dt.Rows[0]["AcademicYearId"]);
}
public static void PopulateList()
{
/*
* PopulateList gets all the Groups
* available in the database and adds them
* to the List<string> GroupList.
*/
// Make sure list is clear of all entries.
GroupList.Clear();
DataTable dt = SqlTools.GetTable("SELECT GroupName FROM Groups");
for (int i = 0; i < dt.Rows.Count; i++)
{
GroupList.Add(dt.Rows[i]["GroupName"].ToString());
}
}
public static string GetAcademicYear(string groupName)
{
/*
* getAcademicYear gets the academic year
* assigned to a group and returns the join
* result.
*
* Arguments:
* groupName (string): Name of the group to find the year of.
*/
SqlCommand command = new SqlCommand("SELECT AcademicYears.AcademicYearName FROM Groups INNER JOIN AcademicYears ON Groups.AcademicYearId=AcademicYears.AcademicYearId WHERE Groups.GroupName = @GroupName");
command.Parameters.AddWithValue("@GroupName", groupName);
DataTable dt = SqlTools.GetTable(command);
return(dt.Rows[0]["AcademicYearName"].ToString());
}
private void SearchButton_Click(object sender, EventArgs e)
{
SqlCommand comm = new SqlCommand("SELECT StaffName FROM Staff WHERE StaffUsername = @StaffID");
comm.Parameters.AddWithValue("@StaffID", staffIDTextBox.Text);
try
{
DataTable dt = SqlTools.GetTable(comm);
staffNameLabel.Text = $"Name: { dt.Rows[0]["StaffName"]}";
staffName = dt.Rows[0]["StaffName"].ToString();
validID = true;
}
catch
{
// Do nothing. SQL will fail if the ID is incorrect but no operation
// needs to be made.
}
}
+ subjectMLRLine[3] * MTG; // Minimum target grade weight calculation.
/*
* Get the ID of any assignment - there are only two different types of assignment,
* homeworks and tests, so just check if it's a homework or not
*/
public static int getAssignmentId(string assignmentName, bool isHomework)
{
SqlCommand comm = new SqlCommand();
if (isHomework)
{
comm.CommandText = "SELECT HomeworkId FROM Homeworks WHERE HomeworkName=@HomeworkName";
comm.Parameters.AddWithValue("@HomeworkName", assignmentName);
DataTable dt = SqlTools.GetTable(comm);
return((int)dt.Rows[0]["HomeworkId"]);
}
else
{
comm.CommandText = "SELECT TestId FROM Tests WHERE TestName=@TestName";
comm.Parameters.AddWithValue("@TestName", assignmentName);
DataTable dt = SqlTools.GetTable(comm);
return((int)dt.Rows[0]["TestId"]);
}
}
public static void PopulateYears(ComboBox cb, string academicYear = null)
{
/*
* Fill the combo box with all the available years
* that can be assigned to a group.
* If the group is pre-existing, make sure it is
* preselected.
*/
DataTable dt = SqlTools.GetTable("SELECT AcademicYearName FROM AcademicYears");
for (int i = 0; i < dt.Rows.Count; i++)
{
cb.Items.Add(dt.Rows[i]["AcademicYearName"].ToString());
}
if (academicYear != null)
{
cb.SelectedIndex = cb.FindStringExact(academicYear);
}
}
private void PopulateSubjects()
{
/*
* Fill the combo box with all the subjects
* that can be assigned to a group.
* If the group is pre-existing, make sure
* it is preselected.
*/
DataTable dt = SqlTools.GetTable("SELECT SubjectName FROM Subjects");
for (int i = 0; i < dt.Rows.Count; i++)
{
subjectsComboBox.Items.Add(dt.Rows[i]["SubjectName"].ToString());
}
if (!newGroup)
{
subjectsComboBox.SelectedIndex = subjectsComboBox.FindStringExact(Subject);
}
}
public static int GetSubjectId(int GroupId)
{
/*
* GetSubjectId gets the SubjectId assigned to a
* group.
*
* Arguments:
* GetSubjectId (int): Id of the group.
*/
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT Subjects.SubjectId FROM Subjects ";
command.CommandText += "INNER JOIN Groups ON Subjects.SubjectId=Groups.SubjectId ";
command.CommandText += "WHERE Groups.GroupId=@GroupId";
command.Parameters.AddWithValue("@GroupId", GroupId);
DataTable dt = SqlTools.GetTable(command);
return((int)dt.Rows[0]["SubjectId"]);
}
public static BindingList <string> GetGroups(string staffName)
{
/*
* getGroups returns a List of strings of
* the groups within staff member specified.
*
* Arguments:
* staffName (string): Name of the group to find the staff members of.
*/
BindingList <string> groupList = new BindingList <string>();
// Returns a table with the staff members at that GroupID using StaffGroupsLink table.
SqlCommand command = new SqlCommand("SELECT Groups.GroupName FROM StaffGroupsLink INNER JOIN Staff ON StaffGroupsLink.StaffId=Staff.StaffId INNER JOIN Groups ON StaffGroupsLink.GroupId=Groups.GroupId WHERE Staff.StaffName = @StaffName");
command.Parameters.AddWithValue("@StaffName", staffName);
DataTable dt = SqlTools.GetTable(command);
for (int i = 0; i < dt.Rows.Count; i++)
{
groupList.Add(dt.Rows[i]["GroupName"].ToString());
}
return(groupList);
}
private void studentsListBox_SelectedIndexChanged(object sender, EventArgs e)
{
SqlCommand comm = new SqlCommand();
comm.Parameters.AddWithValue("@StudentId", Students.GetStudentIdByName(studentsListBox.SelectedItem.ToString()));
comm.Parameters.AddWithValue("@AssignmentId", assignmentId);
if (isHomework)
{
comm.CommandText = "SELECT FinalGrade FROM HomeworkResults WHERE StudentId=@StudentId AND HomeworkId=@AssignmentId";
}
else
{
comm.CommandText = "SELECT FinalGrade FROM TestResults WHERE StudentId=@StudentId AND TestId=@AssignmentId";
}
try
{
string grade = (string)SqlTools.GetTable(comm).Rows[0]["FinalGrade"];
resultsComboBox.SelectedItem = grade;
}
catch (Exception)
{
// There isn't a result for them yet.
}
}
private void LoginButton_Click(object sender, EventArgs e)
{
string username = usernameBox.Text;
Debug.WriteLine(username);
// Run plain-text password through algorithm
string password = HashingAlgorithm(passwordBox.Text);
try
{
SqlCommand comm = new SqlCommand("SELECT StaffPassword FROM Staff WHERE StaffUsername = @StaffUsername");
comm.Parameters.AddWithValue("@StaffUsername", username);
DataTable dt = SqlTools.GetTable(comm);
// If password needs to be reset, don't continue
if ((string)dt.Rows[0]["StaffPassword"] == "")
{
ResetPasswordForm pwForm = new ResetPasswordForm(username);
pwForm.ShowDialog();
pwForm.Dispose();
}
else
{
if ((string)dt.Rows[0]["StaffPassword"] == password)
{
switch (Staff.GetPermissionLevel(username))
{
case PermissionLevel.Overseer:
int staffId = Staff.GetStaffIdByUsername(username);
OverseerForm of = new OverseerForm
{
StaffId = staffId,
isTutor = false
};
Hide();
of.FormClosed += (s, args) => Close();
of.Show();
break;
case PermissionLevel.Admin:
AdminForm af = new AdminForm();
Hide();
af.FormClosed += (s, args) => Close();
af.Show();
break;
case PermissionLevel.Teacher:
staffId = Staff.GetStaffIdByUsername(username);
TeacherMainForm tf = new TeacherMainForm
{
StaffId = staffId,
StaffName = Staff.GetStaffNameById(staffId),
};
Hide();
tf.FormClosed += (s, args) => Close();
tf.Show();
break;
default:
MessageBox.Show("Future system will have lower permission levels");
break;
}
}
else
{
// The password must be wrong if the username is right.
invalidPasswordLabel.Show();
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex);
// Unfortunately, we have to assume the username is wrong.
// The end-user can't have done much else wrong - except a bad
// username.
invalidUsernameLabel.Show();
throw ex;
}
}
