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