public List <LessonInfo> getLessonsList(DateTime from = new DateTime(),
DateTime to = new DateTime(),
StudentInfo studentInfo = new StudentInfo(),
SubjectInfo subjectInfo = new SubjectInfo())
{
List <LessonInfo> list = new List <LessonInfo>();
//---------------------------------------------
DateTime def = new DateTime();
if (to == def)
{
to = new DateTime(9999, to.Month, to.Day);
}
//---------------------------------------------
SQLiteCommand cmd = connection.CreateCommand();
string query = @"SELECT * FROM lessons WHERE @from <= DateAndTime and DateAndTime <= @to";
//---------------------------------------------
StudentInfo def_stud = new StudentInfo();
if (!studentInfo.Equals(def_stud))
{
query += $" and student_id = {studentInfo.id}";
}
//---------------------------------------------
SubjectInfo def_subj = new SubjectInfo();
if (!subjectInfo.Equals(def_subj))
{
query += $" and subject_id = {subjectInfo.id}";
}
//---------------------------------------------
query += ";";
//---------------------------------------------
cmd.CommandText = query;
string fromS = from.ToString(@"yyyy/MM/dd HH:mm:ss");
string toS = to.ToString(@"yyyy/MM/dd HH:mm:ss");
cmd.Parameters.AddWithValue("@from", fromS);
cmd.Parameters.AddWithValue("@to", toS);
SQLiteDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
LessonInfo lessonInfo = new LessonInfo();
lessonInfo.id = Convert.ToInt32(sqlReader["id"].ToString());
//-------------
string formatString = "yyyyMMddHHmmss";
string sample = sqlReader["DateAndTime"].ToString();
string[] words = sample.Split(new char[] { ' ', '.', ':' });
sample = string.Join("", words);
lessonInfo.DateAndTime = DateTime.ParseExact(sample, formatString, null);
//-------------
lessonInfo.duration = Int32.Parse(sqlReader["duration"].ToString());
//-------------
int student_id = Int32.Parse(sqlReader["student_id"].ToString());
StudentsListManager studentsListManager = new StudentsListManager(dbPath);
lessonInfo.studentInfo = studentsListManager.getStudentInfoById(student_id);
//-------------
int subject_id = Int32.Parse(sqlReader["subject_id"].ToString());
SubjectsListManager subjectsListManager = new SubjectsListManager(dbPath);
lessonInfo.subjectInfo = subjectsListManager.getSubjectInfoById(subject_id);
//-------------
lessonInfo.topic = sqlReader["topic"].ToString();
lessonInfo.done = sqlReader["done"].ToString().Equals("0") ? false : true;
lessonInfo.price = Int32.Parse(sqlReader["price"].ToString());
lessonInfo.paid = sqlReader["paid"].ToString().Equals("0") ? false : true;
lessonInfo.remark = sqlReader["remark"].ToString();
list.Add(lessonInfo);
}
return(list);
}
