public void AddParticipantWithCourse(Participant participant, Course course, ParticipantGradeList list)
{
conn.Open();
string sql = "INSERT INTO ParticipantsWithCourses " +
"(participantId, courseId, participantGradeListId) VALUES " +
$"('{participant.ParticipantId}', '{course.CourseId}', '{list.ParticipantGradeListId}')";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
}
public IActionResult Add(IFormCollection form)
{
List <bool> fieldsValidation = GetGradeFieldsValidation(form);
bool dataIsValid = ValidateGradeData(form, fieldsValidation);
if (!dataIsValid)
{
var gradeValue = GradeConverter.GetGradeString(form["GradeId"]);
ViewBag.fieldsValidation = fieldsValidation;
ViewBag.form = form;
List <Grade> grades = Enum.GetValues(typeof(Grade))
.Cast <Grade>()
.ToList();
IEnumerable <SelectListItem> selectList = from g in grades
select new SelectListItem
{
Value = g.ToString(),
Text = GradeConverter.ParseGrade(g).ToString()
};
ViewData["Grades"] = new SelectList(selectList, "Value", "Text", gradeValue);
return(View());
}
PartialCourseGrade partialCourseGrade = new PartialCourseGrade();
int maxId = manager.GetMaxGradeId();
partialCourseGrade.PartialGradeId = maxId + 1;
partialCourseGrade.GradeDate = Convert.ToDateTime(form["DataTextField"]);
partialCourseGrade.GradeValue = GradeConverter.GetGradeString(form["GradeId"]);
partialCourseGrade.Comment = form["ComentTextArea"];
Models.Participant participant = new Models.Participant();
Course course = new Course();
participant.ParticipantId = staticParticipantId;
course.CourseId = staticCourseId;
ParticipantGradeList list = manager.GetParticipantGradeList(participant, course);
partialCourseGrade.ParticipantGradeList = list;
manager.AddGrade(partialCourseGrade);
return(RedirectToAction("Index"));
}
public ParticipantGradeList GetParticipantGradeList(Participant participant, Course course)
{
ParticipantGradeList list = new ParticipantGradeList();
conn.Open();
string sql = $"SELECT PGL.participantGradeListId FROM ParticipantGradeLists PGL " +
$"JOIN ParticipantsWithCourses PWC ON PGL.participantGradeListId = PWC.participantGradeListId " +
$"JOIN Participants P ON PWC.participantId = P.participantId " +
$"WHERE P.participantId = {participant.ParticipantId} AND PWC.courseId = {course.CourseId}";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
list.ParticipantGradeListId = int.Parse(rdr[0].ToString());
}
else
{
rdr.Close();
sql = $"SELECT MAX(participantGradeListId) FROM ParticipantGradeLists";
cmd = new MySqlCommand(sql, conn);
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
list.ParticipantGradeListId = int.Parse(rdr[0].ToString()) + 1;
rdr.Close();
sql = $"INSERT INTO ParticipantGradeLists (participantGradeListId) VALUES " +
$"('{list.ParticipantGradeListId}')";
cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
AddParticipantWithCourse(participant, course, list);
return(list);
}
else
{
list.ParticipantGradeListId = 0;
rdr.Close();
sql = $"INSERT INTO ParticipantGradeLists (participantGradeListId) VALUES " +
$"('{list.ParticipantGradeListId}')";
cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
AddParticipantWithCourse(participant, course, list);
return(list);
}
}
rdr.Close();
conn.Close();
return(list);
}
