public ActionResult ViewLessons()
{
List <DateTime> dates = LessonsDB.GetLessonsDates();
List <List <LessonFullInf> > lessons = new List <List <LessonFullInf> >();
int lessonsCount = LessonsDB.GetLessonsCount();
for (int i = 0; i < dates.Count(); i++)
{
lessons.Add(LessonsDB.GetLessonsInDate(dates[i]));
}
List <Class> classes = ClassesDB.GetClasses();
List <Subject> subjects = SubjectsDB.GetSubjects();
ViewBag.Subjects = subjects;
ViewBag.Classes = classes;
ViewBag.Dates = dates;
ViewBag.Lessons = lessons;
return(View());
}
public ActionResult FilterLessons(int Class_ID, string from, string to)
{
StringBuilder exprForDate = new StringBuilder();
StringBuilder exprForLessons = new StringBuilder();
exprForDate.Append("SELECT DISTINCT Lesson_Date FROM Lessons ");
exprForLessons.Append("Lesson_ID, Lessons.Class_ID, Class_Name, Lessons.Teacher_ID, Name, Surname, Patronymic, Lessons.Subject_ID, Subject_Name, Lesson_Date ");
exprForLessons.Append("FROM Lessons, Classes, Teachers, Subjects WHERE Classes.Class_ID = Lessons.Class_ID AND ");
exprForLessons.Append(" Teachers.Teacher_ID = Lessons.Teacher_ID AND Subjects.Subject_ID = Lessons.Subject_ID ");
List <DateTime> dates = new List <DateTime>();
List <List <LessonFullInf> > lessons = new List <List <LessonFullInf> >();
DateTime fromDate = new DateTime();
DateTime toDate = new DateTime();
try
{
fromDate = Convert.ToDateTime(from);
}
catch
{
from = "00.00.0001";
}
try
{
toDate = Convert.ToDateTime(to);
}
catch
{
to = "00.00.0001";
}
if (Class_ID != 0 && from != "01.01.0001" && to != "00.00.0001")
{
exprForDate.Append($"WHERE Lesson_Date >= '{fromDate.ToString("yyyy-MM-dd")}' AND Lesson_Date <= '{toDate.ToString("yyyy-MM-dd")}'");
dates = LessonsDB.GetFilterDates(exprForDate.ToString());
for (int i = 0; i < dates.Count(); i++)
{
lessons.Add(LessonsDB.GetLessonsInDateAndClass(dates[i], Class_ID));
}
for (int i = 0; i < dates.Count(); i++)
{
if (lessons[i].Count() == 0)
{
dates.RemoveAt(i);
lessons.RemoveAt(i);
i--;
}
}
}
if (Class_ID != 0 && from == "01.01.0001" && to != "00.00.0001")
{
exprForDate.Append($"WHERE Lesson_Date <= '{toDate.ToString("yyyy-MM-dd")}'");
dates = LessonsDB.GetFilterDates(exprForDate.ToString());
for (int i = 0; i < dates.Count(); i++)
{
lessons.Add(LessonsDB.GetLessonsInDateAndClass(dates[i], Class_ID));
}
for (int i = 0; i < dates.Count(); i++)
{
if (lessons[i].Count() == 0)
{
dates.RemoveAt(i);
lessons.RemoveAt(i);
i--;
}
}
}
if (Class_ID != 0 && from != "01.01.0001" && to == "00.00.0001")
{
exprForDate.Append($"WHERE Lesson_Date >= '{fromDate.ToString("yyyy-MM-dd")}' ");
dates = LessonsDB.GetFilterDates(exprForDate.ToString());
for (int i = 0; i < dates.Count(); i++)
{
lessons.Add(LessonsDB.GetLessonsInDateAndClass(dates[i], Class_ID));
}
for (int i = 0; i < dates.Count(); i++)
{
if (lessons[i].Count() == 0)
{
dates.RemoveAt(i);
lessons.RemoveAt(i);
i--;
}
}
}
if (Class_ID != 0 && from == "01.01.0001" && to == "00.00.0001")
{
dates = LessonsDB.GetFilterDates(exprForDate.ToString());
for (int i = 0; i < dates.Count(); i++)
{
lessons.Add(LessonsDB.GetLessonsInDateAndClass(dates[i], Class_ID));
}
for (int i = 0; i < dates.Count(); i++)
{
if (lessons[i].Count() == 0)
{
dates.RemoveAt(i);
lessons.RemoveAt(i);
i--;
}
}
}
if (Class_ID == 0 && from != "01.01.0001" && to != "00.00.0001")
{
exprForDate.Append($"WHERE Lesson_Date >= '{fromDate.ToString("yyyy-MM-dd")}' AND Lesson_Date <= '{toDate.ToString("yyyy-MM-dd")}'");
dates = LessonsDB.GetFilterDates(exprForDate.ToString());
for (int i = 0; i < dates.Count(); i++)
{
lessons.Add(LessonsDB.GetLessonsInDate(dates[i]));
}
}
if (Class_ID == 0 && from == "01.01.0001" && to != "00.00.0001")
{
exprForDate.Append($"WHERE Lesson_Date <= '{toDate.ToString("yyyy-MM-dd")}'");
dates = LessonsDB.GetFilterDates(exprForDate.ToString());
for (int i = 0; i < dates.Count(); i++)
{
lessons.Add(LessonsDB.GetLessonsInDate(dates[i]));
}
}
if (Class_ID == 0 && from != "01.01.0001" && to == "00.00.0001")
{
exprForDate.Append($"WHERE Lesson_Date >= '{fromDate.ToString("yyyy-MM-dd")}' ");
dates = LessonsDB.GetFilterDates(exprForDate.ToString());
for (int i = 0; i < dates.Count(); i++)
{
lessons.Add(LessonsDB.GetLessonsInDate(dates[i]));
}
}
ViewBag.Dates = dates;
ViewBag.Lessons = lessons;
return(PartialView());
}