/// <summary> /// Метод, который получает все посещения по заданным параметрам /// </summary> /// <param name="studentVisit"></param> /// <returns></returns> public List <StudentVisit> GetStudentVisits(StudentVisitSearchObject studentVisit) { var studentVisits = new List <StudentVisit>(); conn.Open(); try { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = conn; cmd.CommandText = StudentVisitsDbRequest(studentVisit); var reader = cmd.ExecuteReader(); while (reader.Read()) { studentVisits.Add(new StudentVisit((int)reader["idpresense"], studentVisit.Student.FirstName, studentVisit.Student.LastName, studentVisit.Student.PastName, studentVisit.Student.Group, (DateTime)reader["date"], studentVisit.Classroom, studentVisit.Subject, (bool)reader["presense"])); } } catch (NullReferenceException) { throw; } finally { conn.Close(); conn.Dispose(); } return(studentVisits); }
private String StudentVisitsDbRequest(StudentVisitSearchObject studentVisit) { string request; var issecond = false; var date = studentVisit.DateTime.ToString("yyyy-MM-dd"); var selectIdStudent = $"select idstudent from vcsdb.students where firstname = '{studentVisit.Student.FirstName}' and lastname = '{studentVisit.Student.LastName}' and pastname = '{studentVisit.Student.PastName}'"; var selectIdSubject = $"select idsubject from vcsdb.subjects where subject = '{studentVisit.Subject}'"; var selectIdClassroom = $"select idclassroom from vcsdb.classrooms where classroom = '{studentVisit.Classroom}'"; var selectIdStudentGroup = $"select idstudentgroup from vcsdb.studentgroups where studentgroup = '{studentVisit.Student.Group}'"; var selectIdClass = $"select idclass from vcsdb.shedule where "; request = $"SELECT * FROM vcsdb.visits " + $"WHERE idstudent =({selectIdStudent}) " + $"AND date = '{date}' "; if (studentVisit.Classroom != null) { if (issecond) { selectIdClass += "and "; } else { issecond = true; } selectIdClass += $"idclassroom = ({selectIdClassroom}) "; } if (studentVisit.Student.Group != null) { if (issecond) { selectIdClass += "and "; } else { issecond = true; } selectIdClass += $"idstudentgroup = ({selectIdStudentGroup}) "; } if (studentVisit.Subject != null) { if (issecond) { selectIdClass += "and "; } else { issecond = true; } selectIdClass += $"idsubject = ({selectIdSubject}) "; } if (studentVisit.Classroom != null || studentVisit.Student.Group != null || studentVisit.Subject != null) { request += $"and idclass = ({selectIdClass})"; } request += ";"; return(request); }