public List <FinalExam> GetFinalExams(int managerId)
{
List <FinalExam> finalExams = new List <FinalExam>();
conn.Open();
string sql = $"SELECT * FROM FinalExams " +
$"WHERE managerId = {managerId}";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
FinalExam finalExam = new FinalExam();
finalExam.ExamId = int.Parse(rdr[0].ToString());
finalExam.ExamDate = DateTime.Parse(rdr[1].ToString());
finalExam.Classroom = rdr[2].ToString();
StudyFieldManager manager = new StudyFieldManager();
manager.ManagerId = int.Parse(rdr[3].ToString());
finalExam.StudyFieldManager = manager;
finalExams.Add(finalExam);
}
rdr.Close();
conn.Close();
return(finalExams);
}
public StudyFieldManager GetStudyFieldManager(int editionId)
{
conn.Open();
string sql = $"SELECT SFM.managerId, SFM.userId FROM StudyFieldManagers SFM " +
$"JOIN Editions E ON E.managerId = SFM.managerId " +
$"WHERE E.edNumber = {editionId}";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
StudyFieldManager studyFieldManager = new StudyFieldManager();
studyFieldManager.ManagerId = int.Parse(rdr[0].ToString());
studyFieldManager.UserId = int.Parse(rdr[1].ToString());
rdr.Close();
conn.Close();
FillUserData(studyFieldManager);
return(studyFieldManager);
}
// tested
public List <SubmissionThesis> GetSubmissionTheses(int edition)
{
List <SubmissionThesis> submissions = new List <SubmissionThesis>();
List <int> thesesIds = new List <int>();
List <int> editiondsNum = new List <int>();
conn.Open();
string sql = $"SELECT ST.submissionId, ST.thesisTopic, ST.topicNumber, " +
$"ST.thesisObjectives, ST.thesisScope, ST.submissionStatus, ST.finalThesisId, " +
$"ST.edNumber " +
"FROM Editions E " +
"NATURAL JOIN SubmissionTheses ST " +
$"WHERE E.edNumber = {edition}";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
SubmissionThesis submission = new SubmissionThesis();
submission.SubmissionId = int.Parse(rdr[0].ToString());
submission.ThesisTopic = rdr[1].ToString();
submission.TopicNumber = int.Parse(rdr[2].ToString());
submission.ThesisObjectives = rdr[3].ToString();
submission.ThesisScope = rdr[4].ToString();
submission.Status = (ThesisStatus)int.Parse(rdr[5].ToString());
thesesIds.Add(int.Parse(rdr[6].ToString()));
editiondsNum.Add(int.Parse(rdr[7].ToString()));
submissions.Add(submission);
}
rdr.Close();
conn.Close();
// tworzenie kluczy obcych
for (int i = 0; i < submissions.Count; i++)
{
conn.Open();
int finalThesisId = thesesIds[i];
// create final thesis by id
string querry = $"SELECT * FROM FinalTheses " +
$"WHERE finalThesisId = {finalThesisId}";
cmd = new MySqlCommand(querry, conn);
rdr = cmd.ExecuteReader();
rdr.Read();
FinalThesis finalThesis = new FinalThesis();
finalThesis.FinalThesisId = int.Parse(rdr[0].ToString());
finalThesis.DeliveryDeadline = DateTime.Parse(rdr[1].ToString());
int participantId = int.Parse(rdr[2].ToString());
rdr.Close();
conn.Close();
conn.Open();
querry = $"SELECT * FROM Participants P " +
$"WHERE P.participantId = {participantId}";
cmd = new MySqlCommand(querry, conn);
rdr = cmd.ExecuteReader();
rdr.Read();
finalThesis.Participant = GetParticipantFromReader(rdr);
rdr.Close();
conn.Close();
submissions[i].FinalThesis = finalThesis;
// edition here
conn.Open();
querry = "SELECT SM.managerId, SM.userId, SM.primaryEmploymentPlace FROM Editions E" +
"NATURAL JOIN StudyFieldManager SM " +
$"WHERE edNumber = {edition}";
cmd = new MySqlCommand(querry, conn);
rdr = cmd.ExecuteReader();
rdr.Read();
Edition submissionEdition = new Edition();
submissionEdition.Number = edition;
StudyFieldManager manager = new StudyFieldManager();
manager.ManagerId = int.Parse(rdr[0].ToString());
manager.UserId = int.Parse(rdr[1].ToString());
manager.PrimaryEmploymentPlace = rdr[2].ToString();
submissionEdition.StudyFieldManager = manager;
submissions[i].Edition = submissionEdition;
submissions[i].Edition.StudyFieldManager = manager;
rdr.Close();
conn.Close();
}
foreach (SubmissionThesis s in submissions)
{
FillUserData(s.Edition.StudyFieldManager);
FillUserData(s.FinalThesis.Participant);
}
return(submissions);
}