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