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); } }
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. } }
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); } }
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; } }
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 }); }
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(); } } }