// tested
public List <Attendance> GetAttendances(Course course)
{
List <Attendance> attendances = new List <Attendance>();
conn.Open();
string sql = $"SELECT P.*, CU.*, L.userId FROM Courses C " +
$"JOIN ClassesUnits CU ON C.courseId = CU.courseID " +
$"JOIN Lecturers L ON L.lecturerId = CU.lecturerId " +
$"JOIN Attendances A ON A.classUnitId = CU.classUnitId " +
$"JOIN Participants P ON P.participantId = A.participantId " +
$"WHERE C.courseId = {course.CourseId}";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Attendance attendance = new Attendance();
// tworze referencje na uczestnika
attendance.Participant = GetParticipantFromReader(rdr);
// tworze referencje na jednostke zajec
int dataOffset = 12;
ClassesUnit classesUnit = new ClassesUnit();
classesUnit.ClassUnitId = int.Parse(rdr[dataOffset].ToString());
classesUnit.ClassBeginning = DateTime.Parse(rdr[dataOffset + 1].ToString());
classesUnit.ClassEnding = DateTime.Parse(rdr[dataOffset + 2].ToString());
classesUnit.ClassroomNumber = rdr[dataOffset + 3].ToString();
classesUnit.ClassesForm = (ClassesForm)int.Parse(rdr[dataOffset + 4].ToString());
// jednostka zajec ma kurs, wiec kolejna referencja
classesUnit.ClassCourse = course;
// jednostka zajec ma prowadzacego, wiec kolejna
Lecturer lecturer = new Lecturer();
lecturer.LecturerId = int.Parse(rdr[dataOffset + 5].ToString());
lecturer.UserId = int.Parse(rdr[dataOffset + 6].ToString());
classesUnit.ClassLecturer = lecturer;
attendance.ClassesUnit = classesUnit;
attendances.Add(attendance);
}
rdr.Close();
conn.Close();
// uzupelniam dane uzytkownikow
foreach (Attendance a in attendances)
{
FillUserData(a.Participant);
FillUserData(a.ClassesUnit.ClassLecturer);
}
return(attendances);
}
public List <ClassesUnit> GetClassesUnitsDate(Course course)
{
List <ClassesUnit> classesUnits = new List <ClassesUnit>();
conn.Open();
string sql = $"SELECT CU.classUnitId, CU.classBeginning, CU.classEnding FROM Courses C " +
"NATURAL JOIN ClassesUnits CU " +
$"WHERE C.courseId = {course.CourseId} ORDER BY 2";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
ClassesUnit classUnit = new ClassesUnit();
classUnit.ClassUnitId = int.Parse(rdr[0].ToString());
classUnit.ClassBeginning = DateTime.Parse(rdr[1].ToString());
classUnit.ClassEnding = DateTime.Parse(rdr[2].ToString());
classesUnits.Add(classUnit);
}
rdr.Close();
conn.Close();
return(classesUnits);
}
