public static GradeData calculateStudentGrades(int studentId, int groupId)
{
double hwAverage = 0, testAverage = 0, mtg = 0;
SqlCommand comm = new SqlCommand();
comm.CommandText = "SELECT FinalGrade FROM HomeworkResults ";
comm.CommandText += "INNER JOIN Homeworks ON HomeworkResults.HomeworkId=Homeworks.HomeworkId ";
comm.CommandText += "WHERE HomeworkResults.StudentId=@StudentId AND Homeworks.GroupId=@GroupId";
comm.Parameters.AddWithValue("@StudentId", studentId);
comm.Parameters.AddWithValue("@GroupId", groupId);
// Get a table of all homework results.
DataTable dt = SqlTools.GetTable(comm);
if (dt.Rows.Count != 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
hwAverage += Grades[(string)dt.Rows[i]["FinalGrade"]];
}
hwAverage /= dt.Rows.Count;
// Use LINQ to find the closest value to the divided average.
hwAverage = GradeVals.Aggregate((x, y) => Math.Abs(x - hwAverage) < Math.Abs(y - hwAverage) ? x : y);
}
// Do the same with test results.
comm.CommandText = "SELECT FinalGrade FROM TestResults ";
comm.CommandText += "INNER JOIN Tests ON TestResults.TestId=Tests.TestId ";
comm.CommandText += "WHERE TestResults.StudentId=@StudentId AND Tests.GroupId=@GroupId";
dt = SqlTools.GetTable(comm);
if (dt.Rows.Count != 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
testAverage += Grades[(string)dt.Rows[i]["FinalGrade"]];
}
testAverage /= dt.Rows.Count;
testAverage = GradeVals.Aggregate((x, y) => Math.Abs(x - testAverage) < Math.Abs(y - testAverage) ? x : y);
}
// Grab minimum target grade.
// Get subject ID
int subjectId = Groups.GetSubjectId(groupId);
comm.CommandText = "SELECT Grade FROM MinimumTargetGrades WHERE SubjectId=@SubjectId AND StudentId=@StudentId";
comm.Parameters.AddWithValue("@SubjectId", subjectId);
dt = SqlTools.GetTable(comm);
if (dt.Rows.Count != 0)
{
mtg = Grades[(string)dt.Rows[0]["Grade"]];
}
// Work out their predicted grade based from this average data.
double[] MLR = Subjects.GetSubjectMLR(subjectId);
double predicted = calculateGrade(hwAverage, testAverage, mtg, MLR);
predicted = GradeVals.Aggregate((x, y) => Math.Abs(x - predicted) < Math.Abs(y - predicted) ? x : y);
return(new GradeData
{
HwAverage = hwAverage,
TestAverage = testAverage,
MTG = mtg,
Predicted = predicted
});
}
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();
}
}
}