public void EditSubmissionThesisTest()
{
IDao dao = new DatabaseManager();
SubmissionThesis submission = new SubmissionThesis();
submission.SubmissionId = 1;
submission.ThesisTopic = "Tresc";
submission.TopicNumber = 2;
submission.ThesisObjectives = "Cele pracy";
submission.ThesisScope = "Cele pracy";
submission.Status = ThesisStatus.APPROVED;
FinalThesis finalThesis = new FinalThesis();
finalThesis.FinalThesisId = 1;
submission.FinalThesis = finalThesis;
Edition edition = new Edition();
edition.Number = 1;
submission.Edition = edition;
Assert.IsTrue(dao.EditSubmissionThesis(submission));
submission.SubmissionId = 100000;
Assert.IsFalse(dao.EditSubmissionThesis(submission));
try
{
SubmissionThesis incorrectSubmission = new SubmissionThesis();
incorrectSubmission.SubmissionId = -1;
dao.EditSubmissionThesis(incorrectSubmission);
Assert.Fail();
}
catch (ArgumentException) { }
}
// tested
public FinalThesisReview GetReview(int reviewId)
{
conn.Open();
string sql = $"SELECT FTR.*, P.*, L.* FROM FinalThesesReview FTR " +
$"JOIN FinalTheses FT ON FTR.finalThesisId = FT.finalThesisId " +
$"JOIN Participants P ON P.participantId = FT.participantId " +
$"JOIN Lecturers L ON L.lecturerId = FT.lecturerId " +
$"WHERE formId = {reviewId}";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
FinalThesisReview review = new FinalThesisReview();
rdr.Read();
review.FormId = int.Parse(rdr[0].ToString());
review.TitleCompability = rdr[1].ToString();
review.ThesisStructureComment = rdr[2].ToString();
review.NewProblem = rdr[3].ToString();
review.SourcesUse = rdr[4].ToString();
review.FormalWorkSide = rdr[5].ToString();
review.WayToUse = rdr[6].ToString();
review.SubstantiveThesisGrade = rdr[7].ToString();
review.ThesisGrade = rdr[8].ToString();
review.FormDate = DateTime.Parse(rdr[9].ToString());
review.FormStatus = (ThesisStatus)int.Parse(rdr[10].ToString());
FinalThesis finalThesis = new FinalThesis();
finalThesis.FinalThesisId = int.Parse(rdr[11].ToString());
int dataOffset = 12;
finalThesis.Participant = GetParticipantFromReader(rdr, dataOffset);
review.FinalThesis = finalThesis;
Lecturer lecturer = new Lecturer();
lecturer.LecturerId = int.Parse(rdr[24].ToString());
lecturer.UserId = int.Parse(rdr[25].ToString());
rdr.Close();
conn.Close();
FillUserData(lecturer);
FillUserData(finalThesis.Participant);
finalThesis.Lecturer = lecturer;
return(review);
}
// tested
public List <FinalThesisReview> GetReviews(int lecturerId)
{
List <FinalThesisReview> reviews = new List <FinalThesisReview>();
conn.Open();
string sql = $"SELECT FTR.*, P.* " +
"FROM Lecturers L " +
"NATURAL JOIN FinalTheses FT " +
"JOIN Participants P ON FT.participantId = P.participantId " +
"JOIN FinalThesesReview FTR ON FTR.finalThesisId = FT.finalThesisId " +
$"WHERE L.lecturerId = {lecturerId}";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
FinalThesisReview review = new FinalThesisReview();
review.FormId = int.Parse(rdr[0].ToString());
review.TitleCompability = rdr[1].ToString();
review.ThesisStructureComment = rdr[2].ToString();
review.NewProblem = rdr[3].ToString();
review.SourcesUse = rdr[4].ToString();
review.FormalWorkSide = rdr[5].ToString();
review.WayToUse = rdr[6].ToString();
review.SubstantiveThesisGrade = rdr[7].ToString();
review.ThesisGrade = rdr[8].ToString();
review.FormDate = DateTime.Parse(rdr[9].ToString());
review.FormStatus = (ThesisStatus)int.Parse(rdr[10].ToString());
FinalThesis finalThesis = new FinalThesis();
finalThesis.FinalThesisId = int.Parse(rdr[11].ToString());
int dataOffset = 12;
finalThesis.Participant = GetParticipantFromReader(rdr, dataOffset);
review.FinalThesis = finalThesis;
reviews.Add(review);
}
rdr.Close();
conn.Close();
foreach (FinalThesisReview r in reviews)
{
FillUserData(r.FinalThesis.Participant);
}
return(reviews);
}
public SubmissionThesis GetSubmissionThesis(int submissionThesisId)
{
conn.Open();
string sql = $"SELECT ST.submissionId, ST.thesisTopic, ST.topicNumber, ST.thesisObjectives, ST.thesisScope, " +
$"ST.finalThesisId, FT.lecturerId, L.userId, P.participantId, P.userId " +
$"FROM SubmissionTheses ST JOIN FinalTheses FT ON ST.finalThesisId = FT.finalThesisId " +
$"JOIN Lecturers L ON FT.lecturerId = L.lecturerId JOIN Participants P ON P.participantId = FT.participantId " +
$"WHERE ST.submissionId = {submissionThesisId}";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
SubmissionThesis submissionThesis = new SubmissionThesis();
submissionThesis.SubmissionId = int.Parse(rdr[0].ToString());
submissionThesis.ThesisTopic = rdr[1].ToString();
submissionThesis.TopicNumber = int.Parse(rdr[2].ToString());
submissionThesis.ThesisObjectives = rdr[3].ToString();
submissionThesis.ThesisScope = rdr[4].ToString();
FinalThesis finalThesis = new FinalThesis();
finalThesis.FinalThesisId = int.Parse(rdr[5].ToString());
Lecturer lecturer = new Lecturer();
lecturer.LecturerId = int.Parse(rdr[6].ToString());
lecturer.UserId = int.Parse(rdr[7].ToString());
Participant participant = new Participant();
participant.ParticipantId = int.Parse(rdr[8].ToString());
participant.UserId = int.Parse(rdr[9].ToString());
conn.Close();
FillUserData(lecturer);
finalThesis.Lecturer = lecturer;
FillUserData(participant);
finalThesis.Participant = participant;
submissionThesis.FinalThesis = finalThesis;
return(submissionThesis);
}
// 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);
}
