/// <summary> /// Updates the grade item by CRN and old name for all students. /// </summary> /// <param name="newItem">The new item.</param> /// <param name="CRN">The CRN.</param> /// <param name="oldgradename">The oldgradename.</param> public void UpdateGradeItemByCRNAndOldNameForAllStudents(GradedItem newItem, int CRN, string oldgradename) { StudentDAL studentGetter = new StudentDAL(); List <Student> students = studentGetter.GetStudentsByCRN(CRN); MySqlConnection conn = DbConnection.GetConnection(); using (conn) { conn.Open(); foreach (var t in students) { GradedItem grade = new GradedItem(newItem.Name, t, 0.0, null, newItem.PossiblePoints, newItem.GradeType, 0, newItem.IsPublic, newItem.TimeGraded); var selectQuery = "UPDATE grade_items SET grade_total_points=@grade_total, grade_type=@grade_type, grade_name=@grade_newname, is_public=@is_public WHERE student_uid = @studentUID AND grade_name = @grade_oldname"; using (MySqlCommand cmd = new MySqlCommand(selectQuery, conn)) { cmd.Parameters.AddWithValue("@studentUID", grade.Student.StudentUID); cmd.Parameters.AddWithValue("@grade_total", grade.PossiblePoints); cmd.Parameters.AddWithValue("@grade_type", grade.GradeType); cmd.Parameters.AddWithValue("@is_public", grade.IsPublic); cmd.Parameters.AddWithValue("@grade_newname", grade.Name); cmd.Parameters.AddWithValue("@grade_oldname", oldgradename); cmd.ExecuteNonQuery(); } } conn.Close(); } }
/// <summary> /// Inserts the new graded item by CRN for all students. /// </summary> /// <param name="newItem">The new item.</param> /// <param name="CRN">The CRN.</param> public void InsertNewGradedItemByCRNForAllStudents(GradedItem newItem, int CRN) { StudentDAL studentGetter = new StudentDAL(); List <Student> students = studentGetter.GetStudentsByCRN(CRN); MySqlConnection conn = DbConnection.GetConnection(); using (conn) { conn.Open(); foreach (var t in students) { GradedItem grade = new GradedItem(newItem.Name, t, 0.0, null, newItem.PossiblePoints, newItem.GradeType, 0, newItem.IsPublic, newItem.TimeGraded); var selectQuery = "INSERT INTO grade_items(student_uid, grade_total_points, grade_earned_points, grade_type, grade_name, grade_feedback, is_public) VALUES (@studentUID,@grade_total,@grade_points,@grade_type,@grade_name,@grade_feedback,@is_public)"; using (MySqlCommand cmd = new MySqlCommand(selectQuery, conn)) { cmd.Parameters.AddWithValue("@studentUID", grade.Student.StudentUID); cmd.Parameters.AddWithValue("@grade_total", grade.PossiblePoints); cmd.Parameters.AddWithValue("@grade_points", grade.Grade); cmd.Parameters.AddWithValue("@grade_type", grade.GradeType); cmd.Parameters.AddWithValue("@grade_name", grade.Name); cmd.Parameters.AddWithValue("@grade_feedback", grade.Feedback); cmd.Parameters.AddWithValue("@is_public", grade.IsPublic); cmd.ExecuteNonQuery(); } var query = "INSERT INTO grade_belongs_to_courses (grade_item_id, courses_CRN) VALUES ((SELECT grade_items.grade_item_id FROM grade_items WHERE grade_items.student_uid = @studentUID AND grade_items.grade_name = @grade_name),@CRN)"; using (MySqlCommand cmd = new MySqlCommand(query, conn)) { cmd.Parameters.AddWithValue("@studentUID", grade.Student.StudentUID); cmd.Parameters.AddWithValue("@grade_name", grade.Name); cmd.Parameters.AddWithValue("@CRN", CRN); cmd.ExecuteNonQuery(); } } conn.Close(); } }
/// <summary> /// Deletes the graded item by CRN for all students. /// </summary> /// <param name="gradedItem">The graded item.</param> /// <param name="CRN">The CRN.</param> public void deleteGradedItemByCRNForAllStudents(GradedItem gradedItem, int CRN) { StudentDAL studentGetter = new StudentDAL(); List <Student> students = studentGetter.GetStudentsByCRN(CRN); MySqlConnection conn = DbConnection.GetConnection(); using (conn) { conn.Open(); foreach (var t in students) { var selectQuery = "DELETE grade_items FROM grade_items INNER JOIN grade_belongs_to_courses ON grade_items.grade_item_id = grade_belongs_to_courses.grade_item_id WHERE grade_items.student_uid = @studentUID AND grade_belongs_to_courses.courses_CRN = @CRNCheck AND grade_items.grade_name = @grade_name"; using (MySqlCommand cmd = new MySqlCommand(selectQuery, conn)) { cmd.Parameters.AddWithValue("@studentUID", t.StudentUID); cmd.Parameters.AddWithValue("@grade_name", gradedItem.Name); cmd.Parameters.AddWithValue("@CRNCheck", CRN); cmd.ExecuteNonQuery(); } var query = "DELETE grade_belongs_to_courses FROM grade_belongs_to_courses WHERE grade_belongs_to_courses.grade_item_id = (SELECT grade_items.grade_item_id FROM grade_items WHERE grade_items.student_uid = @studentUID AND grade_items.grade_name = @gradeName) AND grade_belongs_to_courses.courses_CRN = @CRN"; using (MySqlCommand cmd = new MySqlCommand(query, conn)) { cmd.Parameters.AddWithValue("@studentUID", t.StudentUID); cmd.Parameters.AddWithValue("@gradeName", gradedItem.Name); cmd.Parameters.AddWithValue("@CRN", CRN); cmd.ExecuteNonQuery(); } } conn.Close(); } }
/// <summary> /// Updates the course rubric. /// </summary> /// <param name="crn">The CRN.</param> /// <param name="assignmentType">Type of the assignment.</param> /// <param name="assignmentWeight">The assignment weight.</param> /// <param name="original_AssignmentType">Type of the original assignment.</param> /// <param name="original_AssignmentWeight">The original assignment weight.</param> /// <param name="index">The index.</param> /// <param name="original_Index">Index of the original.</param> /// <param name="original_Crn">The original CRN.</param> public void UpdateCourseRubric(int crn, string assignmentType, int assignmentWeight, string original_AssignmentType, int original_AssignmentWeight, int index, int original_Index, int original_Crn) { List <RubricItem> rubric = GetCourseRubricByCRN(crn); RubricItem original_item = rubric.Find(x => x.AssignmentType.Equals(original_AssignmentType) && x.AssignmentWeight == original_AssignmentWeight); string assignment_types = ""; string weight_per_types = ""; for (int i = 0; i < rubric.Count; i++) { if (i == original_item.Index && i != rubric.Count - 1) { assignment_types += assignmentType + "/"; weight_per_types += assignmentWeight + "/"; } else if (i != rubric.Count - 1) { assignment_types += rubric[i].AssignmentType + "/"; weight_per_types += rubric[i].AssignmentWeight + "/"; } else if (original_item.Index == i && i == rubric.Count - 1) { assignment_types += assignmentType; weight_per_types += assignmentWeight; } else { assignment_types += rubric[i].AssignmentType; weight_per_types += rubric[i].AssignmentWeight; } } MySqlConnection conn = DbConnection.GetConnection(); using (conn) { conn.Open(); var selectQuery = "UPDATE rubrics SET assignment_types=@assignment_types, weight_per_type=@weight_per_type WHERE CRN = @CRN"; using (MySqlCommand cmd = new MySqlCommand(selectQuery, conn)) { cmd.Parameters.AddWithValue("@assignment_types", assignment_types); cmd.Parameters.AddWithValue("@weight_per_type", weight_per_types); cmd.Parameters.AddWithValue("@CRN", crn); cmd.ExecuteNonQuery(); } StudentDAL studentGetter = new StudentDAL(); List <Student> students = studentGetter.GetStudentsByCRN(crn); foreach (var student in students) { var updateQuery = "UPDATE grade_items SET grade_type = @newType WHERE grade_item_id = (SELECT grade_items.grade_item_id FROM grade_belongs_to_courses WHERE grade_belongs_to_courses.courses_CRN = @CRN AND grade_belongs_to_courses.grade_item_id = grade_items.grade_item_id AND grade_items.grade_type = @oldType AND grade_items.student_uid = @studentUID)"; using (MySqlCommand cmd = new MySqlCommand(updateQuery, conn)) { cmd.Parameters.AddWithValue("@newType", assignmentType); cmd.Parameters.AddWithValue("@oldType", original_AssignmentType); cmd.Parameters.AddWithValue("@CRN", crn); cmd.Parameters.AddWithValue("@studentUID", student.StudentUID); cmd.ExecuteNonQuery(); } } conn.Close(); } }