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