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();
}
private void continueButton_Click(object sender, EventArgs e)
{
SqlCommand command = new SqlCommand();
SqlParameter group = new SqlParameter("@GroupId", groupId);
command.Parameters.Add(group);
if (isAssignment)
{
command.CommandText = "INSERT INTO Homeworks VALUES (@HomeworkName, @GroupId)";
command.Parameters.AddWithValue("@HomeworkName", assignmentNameTextBox.Text);
SqlTools.ExecuteNonQuery(command);
}
else
{
command.CommandText = "INSERT INTO Tests VALUES (@TestName, @GroupId)";
command.Parameters.AddWithValue("@TestName", assignmentNameTextBox.Text);
SqlTools.ExecuteNonQuery(command);
}
EditResultsForm erf = new EditResultsForm(Students, groupId, isAssignment, assignmentNameTextBox.Text);
Hide();
erf.ShowDialog();
erf.Dispose();
}
public static void AddNewSubject(string subjectName)
{
/*
* Adds a new subject to the table.
*/
SqlCommand comm = new SqlCommand("INSERT INTO Subjects (SubjectName) VALUES (@SubjectName)");
comm.Parameters.AddWithValue("@SubjectName", subjectName);
SqlTools.ExecuteNonQuery(comm);
AdminForm.RefreshLists();
}
private void saveStaffButton_Click(object sender, System.EventArgs e)
{
PermissionLevel permissionLevel = PermissionLevel.Teacher;
if (overseerCheckBox.Checked)
{
permissionLevel = PermissionLevel.Overseer;
}
if (!newMember)
{
SqlCommand comm = new SqlCommand("UPDATE Staff SET StaffName = @StaffName, PermissionLevel = @PermissionLevel, StaffUsername = @StaffUsername WHERE StaffId = @StaffId");
comm.Parameters.AddWithValue("@StaffName", staffNameBox.Text);
comm.Parameters.AddWithValue("@PermissionLevel", (int)permissionLevel);
comm.Parameters.AddWithValue("@StaffUsername", staffUsernameBox.Text);
comm.Parameters.AddWithValue("@StaffId", Staff.GetStaffIdByName(staffName));
SqlTools.ExecuteNonQuery(comm);
if (resetPasswordTickBox.Checked)
{
comm.CommandText = "UPDATE Staff SET StaffPassword = '' WHERE StaffId = @StaffId";
SqlTools.ExecuteNonQuery(comm);
}
comm.CommandText = "DELETE FROM StaffGroupsLink WHERE StaffId = @StaffId";
SqlTools.ExecuteNonQuery(comm);
SqlParameter p = new SqlParameter("@GroupId", "");
comm.Parameters.Add(p);
comm.CommandText = "INSERT INTO StaffGroupsLink (GroupId, StaffId) VALUES (@GroupId, @StaffId)";
foreach (string o in GroupList)
{
p.Value = Groups.GetGroupIdByName(o);
SqlTools.ExecuteNonQuery(comm);
}
Close();
}
else
{
SqlCommand comm = new SqlCommand("INSERT INTO Staff (StaffName, PermissionLevel, StaffUsername, StaffPassword) VALUES (@StaffName, @PermissionLevel, @StaffUsername, @StaffPassword)");
comm.Parameters.AddWithValue("@StaffName", staffNameBox.Text);
comm.Parameters.AddWithValue("@PermissionLevel", (int)permissionLevel);
comm.Parameters.AddWithValue("@StaffUsername", staffUsernameBox.Text);
comm.Parameters.AddWithValue("@StaffPassword", "");
SqlTools.ExecuteNonQuery(comm);
comm.Parameters.AddWithValue("@StaffId", Staff.GetStaffIdByName(staffNameBox.Text));
SqlParameter p = new SqlParameter("@GroupId", "");
comm.Parameters.Add(p);
comm.CommandText = "INSERT INTO StaffGroupsLink (GroupId, StaffId) VALUES (@GroupId, @StaffId)";
foreach (string o in GroupList)
{
p.Value = Groups.GetGroupIdByName(o);
SqlTools.ExecuteNonQuery(comm);
}
Close();
}
}
public static void DeleteStudent(string studentName)
{
/*
* Deletes the student with the specified student name
* from the students table and any links to it.
*/
int studentId = GetStudentTableId(studentName);
SqlCommand comm = new SqlCommand("DELETE FROM Students WHERE StudentId = @StudentId");
comm.Parameters.AddWithValue("@StudentId", studentId);
SqlTools.ExecuteNonQuery(comm);
AdminForm.RefreshLists();
}
public static void DeleteStaffMember(string staffName)
{
/*
* Delete staff member by specified staff name,
* and group links with that staff member in.
*/
int staffId = Staff.GetStaffIdByName(staffName);
SqlCommand comm = new SqlCommand("DELETE FROM StaffGroupsLink WHERE StaffId = @StaffId");
comm.Parameters.AddWithValue("@StaffId", staffId);
SqlTools.ExecuteNonQuery(comm);
comm.CommandText = "DELETE FROM Staff WHERE StaffName = @StaffName";
comm.Parameters.AddWithValue("@StaffName", staffName);
SqlTools.ExecuteNonQuery(comm);
// Repopulate list.
AdminForm.RefreshLists();
}
public static void EditStudent(string studentName, string studentCollegeId, int academicYearId, BindingList <string> GroupList, out int newstudentId, int studentId = -1)
{
/*
* Adds or edits a student.
*/
SqlCommand comm = new SqlCommand();
comm.Parameters.AddWithValue("@StudentName", studentName);
comm.Parameters.AddWithValue("@StudentCollegeId", studentCollegeId);
comm.Parameters.AddWithValue("@AcademicYearId", academicYearId);
if (studentId == -1)
{
// If a new student is to be added, insert all values.
comm.CommandText = "INSERT INTO Students (StudentName, StudentCollegeId, AcademicYearId) ";
comm.CommandText += "VALUES (@StudentName, @StudentCollegeId, @AcademicYearId)";
SqlTools.ExecuteNonQuery(comm);
// Look up new student's ID.
comm.Parameters.AddWithValue("@StudentId", GetStudentIdByName(studentName));
}
else
{
comm.CommandText = "UPDATE Students SET StudentName = @StudentName, AcademicYearId = @AcademicYearId, StudentCollegeId = @StudentCollegeId WHERE StudentId=@StudentId";
comm.Parameters.AddWithValue("@StudentId", studentId);
SqlTools.ExecuteNonQuery(comm);
// It is easier to just delete all the matches in group links and re-add them then evaluate what has changed.
comm.CommandText = "DELETE FROM StudentGroupsLink WHERE StudentId=@StudentId";
SqlTools.ExecuteNonQuery(comm);
}
// Both conditions need the groups to be inserted.
// Define a new parameter which has a value that can be changed (group ID)
SqlParameter idParameter = new SqlParameter("@GroupId", 0);
comm.Parameters.Add(idParameter);
foreach (string item in GroupList)
{
// For each group, insert it into the link table.
idParameter.Value = Groups.GetGroupIdByName(item);
comm.CommandText = "INSERT INTO StudentGroupsLink VALUES (@StudentId, @GroupId)";
SqlTools.ExecuteNonQuery(comm);
}
// Return the new student ID (stays the same if a new user was not added)
newstudentId = studentId;
}
private void savePasswordButton_Click(object sender, EventArgs e)
{
string password = pwBox.Text;
if (password.Length > 0)
{
if (password == pwBoxConfirm.Text)
{
SqlCommand comm = new SqlCommand("UPDATE Staff SET StaffPassword = @password WHERE StaffUsername = @StaffUsername");
comm.Parameters.AddWithValue("@password", LoginForm.HashingAlgorithm(password));
comm.Parameters.AddWithValue("@StaffUsername", username);
SqlTools.ExecuteNonQuery(comm);
}
}
else
{
MessageBox.Show("Password cannot be empty!");
}
this.Close();
}
public static void DeleteGroup(string groupName)
{
/*
* DeleteGroup deletes the given group from
* the database.
*
* Arguments:
* groupName (string): The name of the group to delete.
*/
int groupId = GetGroupIdByName(groupName);
// Delete all references to Group in StaffGroupsLink
SqlCommand comm = new SqlCommand("DELETE FROM StaffGroupsLink WHERE GroupId = @GroupId");
comm.Parameters.AddWithValue("@GroupId", groupId);
SqlTools.ExecuteNonQuery(comm);
// Delete from Groups table
comm.CommandText = "DELETE FROM Groups WHERE GroupId = @GroupId";
SqlTools.ExecuteNonQuery(comm);
// Repopulate the list to view affected groups.
AdminForm.RefreshLists();
}
private void saveResultButton_Click(object sender, EventArgs e)
{
/*
* Insert result into results table
*/
SqlCommand comm = new SqlCommand();
comm.Parameters.AddWithValue("@StudentId", Students.GetStudentIdByName(studentsListBox.SelectedItem.ToString()));
comm.Parameters.AddWithValue("@FinalGrade", (string)resultsComboBox.SelectedItem);
comm.Parameters.AddWithValue("@AssignmentId", assignmentId);
// Update the result, however if the affected rowcount is 0, insert a new result as there is nothing to update.
if (isHomework)
{
comm.CommandText = "UPDATE HomeworkResults SET FinalGrade=@FinalGrade WHERE StudentId=@StudentId AND HomeworkId=@AssignmentId ";
comm.CommandText += "IF @@ROWCOUNT = 0 INSERT INTO HomeworkResults VALUES (@assignmentId, @StudentId, @FinalGrade)";
SqlTools.ExecuteNonQuery(comm);
}
else
{
comm.CommandText = "UPDATE TestResults SET FinalGrade=@FinalGrade WHERE StudentId=@StudentId AND TestId=@AssignmentId ";
comm.CommandText += "IF @@ROWCOUNT = 0 INSERT INTO TestResults VALUES (@assignmentId, @StudentId, @FinalGrade)";
SqlTools.ExecuteNonQuery(comm);
}
}
public static void UpdateMTGs(int studentId, Dictionary <int, string> grades)
{
/*
* Update a students minimum target grades.
*/
// Delete all the mtg's. This is easier than finding what changed and updating them.
SqlCommand comm = new SqlCommand("DELETE FROM MinimumTargetGrades WHERE StudentId=@StudentId");
comm.Parameters.AddWithValue("@StudentId", studentId);
SqlTools.ExecuteNonQuery(comm);
comm.CommandText = "INSERT INTO MinimumTargetGrades VALUES (@StudentId, @SubjectId, @Grade)";
SqlParameter subjectParameter = new SqlParameter("@SubjectId", 0);
SqlParameter gradeParameter = new SqlParameter("@Grade", 0);
comm.Parameters.Add(subjectParameter);
comm.Parameters.Add(gradeParameter);
// For each key, add the subject and minimum target grade.
foreach (int subjectId in grades.Keys)
{
subjectParameter.Value = subjectId;
gradeParameter.Value = grades[subjectId];
SqlTools.ExecuteNonQuery(comm);
}
}
public static void UpdateSubjectMLR(int subjectId)
{
/*
* UpdateSubjectMLR is responsible for re-running the MLR line
* calculation for a subject. This is a difficult calcuation,
* so this shouldn't be done too often. After the calcuation is
* done, store the values of each variable in the MLR equation
* in the database.
*/
List <double> homeworkResults = new List <double>();
List <double> testResults = new List <double>();
List <double> minimumTargetGrades = new List <double>();
List <double> finalResults = new List <double>();
/* Populate all the lists.
* Remember to convert grades to their double eqv. using the dictonary. */
SqlCommand command = new SqlCommand("SELECT HomeworkResult FROM PreviousResults WHERE SubjectId = @SubjectId");
command.Parameters.AddWithValue("@SubjectId", subjectId);
DataTable dt = SqlTools.GetTable(command);
for (int i = 0; i < dt.Rows.Count; i++)
{
homeworkResults.Add(Grades[(string)dt.Rows[i]["HomeworkResult"]]);
}
command.CommandText = "SELECT TestResult FROM PreviousResults WHERE SubjectId = @SubjectId";
dt = SqlTools.GetTable(command);
for (int i = 0; i < dt.Rows.Count; i++)
{
testResults.Add(Grades[(string)dt.Rows[i]["TestResult"]]);
}
command.CommandText = "SELECT MTGResult FROM PreviousResults WHERE SubjectId = @SubjectId";
dt = SqlTools.GetTable(command);
for (int i = 0; i < dt.Rows.Count; i++)
{
minimumTargetGrades.Add(Grades[(string)dt.Rows[i]["MTGResult"]]);
}
command.CommandText = "SELECT FinalResult FROM PreviousResults WHERE SubjectId = @SubjectId";
dt = SqlTools.GetTable(command);
for (int i = 0; i < dt.Rows.Count; i++)
{
finalResults.Add(Grades[(string)dt.Rows[i]["FinalResult"]]);
}
/*
* Calculate the MLR line.
* Returned double array:
* MLRLine[0]: Constant
* MLRLine[1]: Homework result weight
* MLRLine[2]: Test result weight
* MLRLine[3]: Minimum target grade weight
*/
double[] MLRLine = GradeUtils.calculateSubjectMLR(homeworkResults, testResults, minimumTargetGrades, finalResults);
/*
* Setup the SQL command.
* SubjectID doesn't change here.
*/
command.CommandText = "UPDATE Subjects SET SubjectWeightConstant = @SubjectWeightConstant, " +
"SubjectHomeworkWeight = @SubjectHomeworkWeight, " +
"SubjectTestWeight = @SubjectTestWeight, " +
"SubjectMTGWeight = @SubjectMTGWeight WHERE SubjectID = @SubjectID";
// Add each parameter.
command.Parameters.AddWithValue("@SubjectWeightConstant", MLRLine[0]);
command.Parameters.AddWithValue("@SubjectHomeworkWeight", MLRLine[1]);
command.Parameters.AddWithValue("@SubjectTestWeight", MLRLine[2]);
command.Parameters.AddWithValue("@SubjectMTGWeight", MLRLine[3]);
// Finally, execute the update
SqlTools.ExecuteNonQuery(command);
}
private void SaveGroupButton_Click(object sender, EventArgs e)
{
/*
* SaveButton executes the SQL query needed for inserting
* a new Group and its related staff members.
* newGroup defines whether a group is being edited or
* a new group is being created.
*/
if (!newGroup)
{
// Delete all cases of the group beforehand to avoid conflicts.
SqlCommand comm = new SqlCommand("DELETE FROM StaffGroupsLink WHERE GroupId = @GroupId");
comm.Parameters.AddWithValue("@GroupId", groupId);
SqlTools.ExecuteNonQuery(comm);
SqlParameter staffId = new SqlParameter("@StaffId", "");
// Insert the new group-staff links with the selected staff
comm.CommandText = "INSERT INTO StaffGroupsLink (GroupId, StaffId) VALUES (@GroupId, @StaffId)";
comm.Parameters.Add(staffId);
foreach (string o in staffList)
{
// Loop over each Staff ID in the list.
staffId.Value = Staff.GetStaffIdByName(o);
SqlTools.ExecuteNonQuery(comm);
}
// Update with the new subject if changed
comm.CommandText = "UPDATE Groups SET SubjectId = @SubjectId WHERE GroupId = @GroupId";
comm.Parameters.AddWithValue("@SubjectId", Subjects.GetSubjectIdByName(subjectsComboBox.SelectedItem.ToString()));
SqlTools.ExecuteNonQuery(comm);
// Update with the new Academic Year if changed
comm.CommandText = "UPDATE Groups SET AcademicYearId = @AcademicYearId WHERE GroupId = @GroupId";
comm.Parameters.AddWithValue("@AcademicYearId", Groups.GetYearIdByName(academicYearComboBox.SelectedItem.ToString()));
SqlTools.ExecuteNonQuery(comm);
// Repopulate the list with the new group.
AdminForm.RefreshLists();
Close();
}
else
{
// New group
if (groupNameTextBox.Text != "" && academicYearComboBox.SelectedIndex != -1 && subjectsComboBox.SelectedIndex != -1 && lecturerBox.Items.Count != 0)
{
// Insert the parameters into the query.
SqlCommand comm = new SqlCommand("INSERT INTO Groups (GroupName, SubjectId, AcademicYearId) VALUES (@GroupName, @SubjectId, @AcademicYearId)");
comm.Parameters.AddWithValue("@GroupName", groupNameTextBox.Text);
comm.Parameters.AddWithValue("@SubjectId", Subjects.GetSubjectIdByName(subjectsComboBox.SelectedItem.ToString()));
comm.Parameters.AddWithValue("@AcademicYearId", Groups.GetYearIdByName(academicYearComboBox.SelectedItem.ToString()));
SqlTools.ExecuteNonQuery(comm);
SqlParameter StaffId = new SqlParameter("@StaffId", "");
comm.Parameters.Add(StaffId);
// Get the newly created group ID
comm.Parameters.AddWithValue("@GroupId", Groups.GetGroupIdByName(groupNameTextBox.Text));
comm.CommandText = "INSERT INTO StaffGroupsLink (GroupId, StaffId) VALUES (@GroupId, @StaffId)";
foreach (string o in lecturerBox.Items)
{
// Loop through the staff ID's and add them
StaffId.Value = Staff.GetStaffIdByName(o);
SqlTools.ExecuteNonQuery(comm);
}
AdminForm.RefreshLists();
Close();
}
}
}
