//////////////////// ОДИН БОЛЬШОЙ ПОИСК !!! Если не введены никакие параметры, функция должна возвращать все кабинеты //////////////////
public static List <Cabinet> FindAll(Boolean deldate, Cabinet cabinet, Branch branch, int min, int max, String sort, String asсdesс, int page, int count, ref int countrecord) //deldate =false - все и удал и неудал!
{
List <Cabinet> list = new List <Cabinet>();
using (SampleContext db = new SampleContext())
{
var query = from b in db.Branches
join c in db.Cabinets on b.ID equals c.BranchID
//select new { BID = b.ID, BName = b.Name, BAddress = b.Address, BDeldate = b.Deldate, BEditdate = b.Editdate, BDirectorID = b.DirectorBranch, WID = w.ID };
select c;
// Последовательно просеиваем наш список
if (deldate != false) // Убираем удаленных, если нужно
{
query = query.Where(x => x.Deldate == null);
}
// if (cabinet.Number != "")
if (cabinet.Number != null)
{
query = query.Where(x => x.Number == cabinet.Number);
}
if (min != 0)
{
query = query.Where(x => x.Capacity >= min);
}
if (max != 0)
{
query = query.Where(x => x.Capacity <= max);
}
if (branch.ID != 0)
{
query = query.Where(x => x.BranchID <= branch.ID);
}
if (sort != null) // Сортировка, если нужно
{
query = Utilit.OrderByDynamic(query, sort, asсdesс);
}
// Я перепроверила все варианты - это должно работать правильно!
countrecord = query.GroupBy(u => u.ID).Count();
query = query.Skip((page - 1) * count).Take(count);
query = query.Distinct();
foreach (var p in query)
{
list.Add(new Cabinet {
ID = p.ID, Number = p.Number, Capacity = p.Capacity, BranchID = p.BranchID, Deldate = p.Deldate, Editdate = p.Editdate
});
}
return(list);
}
}
//////////////////// ОДИН БОЛЬШОЙ ПОИСК !!! Если не введены никакие параметры, функция должна возвращать все филиалы //////////////////
public static List <Branch> FindAll(Boolean deldate, Branch branch, Worker director, String sort, String asсdesс, int page, int count, ref int countrecord) //deldate =false - все и удал и неудал!
{
List <Branch> list = new List <Branch>();
using (SampleContext db = new SampleContext())
{
var query = from b in db.Branches
join w in db.Workers on b.DirectorBranch equals w.ID
select new { ID = b.ID, Name = b.Name, Address = b.Address, Deldate = b.Deldate, Editdate = b.Editdate, DirectorID = b.DirectorBranch, WID = w.ID };
// Последовательно просеиваем наш список
if (deldate != false) // Убираем удаленных, если нужно
{
query = query.Where(x => x.Deldate == null);
}
if (branch.Name != null)
{
query = query.Where(x => x.Name == branch.Name);
}
if (branch.Address != null)
{
query = query.Where(x => x.Address == branch.Address);
}
if (director.ID != 0)
{
query = query.Where(x => x.DirectorID == director.ID);
}
if (sort != null) // Сортировка, если нужно
{
query = Utilit.OrderByDynamic(query, sort, asсdesс);
}
// Я перепроверила все варианты - это должно работать правильно!
countrecord = query.GroupBy(u => u.ID).Count();
query = query.Skip((page - 1) * count).Take(count);
query = query.Distinct();
foreach (var p in query)
{
list.Add(new Branch {
ID = p.ID, Name = p.Name, Address = p.Address, DirectorBranch = p.DirectorID, Deldate = p.Deldate, Editdate = p.Editdate
});
}
return(list);
}
}
//////////////////// ОДИН БОЛЬШОЙ ПОИСК !!! Если не введены никакие параметры, функция должна возвращать все темы //////////////////
public static List <Grade> FindAll(Boolean deldate, Grade grade, Theme theme, Course course, Student student, DateTime mindate, DateTime maxdate, int min, int max, String sort, String asсdesс, int page, int count, ref int countrecord) //deldate =false - все и удал и неудал!
{
List <Grade> list = new List <Grade>();
using (SampleContext db = new SampleContext())
{
var query = from t in db.Themes
join g in db.Grades on t.ID equals g.ThemeID
join s in db.Students on g.StudentID equals s.ID
join sc in db.StudentsCourses on s.ID equals sc.StudentID
join tt in db.TimetablesThemes on t.ID equals tt.ThemeID
join time in db.Timetables on tt.TimetableID equals time.ID
select new { ID = g.ID, StudentID = g.StudentID, ThemeID = g.ThemeID, Mark = g.Mark, Deldate = g.Deldate, Editdate = g.Editdate, Date = t.Date, Course = sc.CourseID, TimetableCourse = time.CourseID };
// Left jion для соединения таблиц, чтобы высвечивались все оценки
//var query = from g in db.Grades
// join t in db.Themes on g.ThemeID equals t.ID
// into theme_grade_temp
// from theme_grade in theme_grade_temp.DefaultIfEmpty()
// join s in db.Students on g.StudentID equals s.ID
// into st_grade_temp
// from st_grade in st_grade_temp.DefaultIfEmpty()
// join sc in db.StudentsCourses on st_grade.ID equals sc.StudentID
// into st_cour_grade_temp
// from st_cour_grade in st_cour_grade_temp.DefaultIfEmpty()
// join tt in db.TimetablesThemes on theme_grade.ID equals tt.ThemeID
// into time_theme_temp
// from time_theme in time_theme_temp.DefaultIfEmpty()
// join time in db.Timetables on time_theme.TimetableID equals time.ID
// into time_time_theme_temp
// from time_time_theme in time_time_theme_temp.DefaultIfEmpty()
// select new { ID = g.ID, StudentID = g.StudentID, ThemeID = g.ThemeID, Mark = g.Mark, Deldate = g.Deldate, Editdate = g.Editdate, Date = (theme_grade == null ? DateTime.MinValue : theme_grade.Date), Course = (st_cour_grade == null ? 0 : st_cour_grade.CourseID), TimetableCourse = (time_time_theme == null ? 0 : time_time_theme.CourseID) };
// query = g.GroupBy(x => x.ID);
// Последовательно просеиваем наш список
if (deldate != false) // Убираем удаленных, если нужно
{
query = query.Where(x => x.Deldate == null);
}
if (theme.ID != 0)
{
query = query.Where(x => x.ThemeID == theme.ID);
}
if (course.ID != 0)
{
query = query.Where(x => x.Course == course.ID & x.Course == x.TimetableCourse); // Второе условие для того, чтобы при выборе курса выбирались
//не просто все ученика этого курса со всеми своими оценками(даже по другим курсам),
//а чтобы выбирались ученики и их оценки только за один выбранный курс
}
if (student.ID != 0)
{
query = query.Where(x => x.StudentID == student.ID);
}
if (mindate != DateTime.MinValue)
{
query = query.Where(x => x.Date >= mindate);
}
if (maxdate != DateTime.MaxValue)
{
query = query.Where(x => x.Date <= maxdate);
}
if (min != 0)
{
query = query.Where(x => x.Mark >= min);
}
if (max != 0)
{
query = query.Where(x => x.Mark <= max);
}
query = query.Distinct();
var query2 = query.GroupBy(s => new { s.ID, s.StudentID, s.ThemeID, s.Mark, s.Editdate, s.Deldate }, (key, group) => new
{
ID = key.ID,
Mark = key.Mark,
StudentID = key.StudentID,
ThemeID = key.ThemeID,
Editdate = key.Editdate,
Deldate = key.Deldate,
});
if (sort != null) // Сортировка, если нужно
{
query2 = Utilit.OrderByDynamic(query2, sort, asсdesс);
}
// Я перепроверила все варианты - это должно работать правильно!
countrecord = query2.GroupBy(u => u.ID).Count();
query2 = query2.Skip((page - 1) * count).Take(count); // Формирование страниц и кол-во записей на странице
foreach (var p in query2)
{
list.Add(new Grade {
ID = p.ID, StudentID = p.StudentID, ThemeID = p.ThemeID, Mark = p.Mark, Deldate = p.Deldate, Editdate = p.Editdate
});
}
return(list);
}
}
//public static List<Contract> GetCo() // Просто так, для получения нефильтрованного списка
//{
// // var context = new SampleContext();
// using (SampleContext db = new SampleContext())
// {
// var contracts = db.Contracts.ToList();
// return contracts;
// }
//}
//////////////////// ОДИН БОЛЬШОЙ ПОИСК !!! Если не введены никакие параметры, функция должна возвращать все договоры //////////////////
public static List <Contract> FindAll(Boolean deldate, Student student, Worker manager, Branch branch, Course course, DateTime mindate, DateTime maxdate, int min, int max, String sort, String asсdesс, int page, int count, ref int countrecord) //deldate =false - все и удал и неудал!
{
List <Contract> list = new List <Contract>();
using (SampleContext db = new SampleContext())
{
//var query = from b in db.Branches
// join w in db.Workers on b.DirectorBranch equals w.ID
// select new { BID = b.ID, BName = b.Name, BAddress = b.Address, BDeldate = b.Deldate, BEditdate = b.Editdate, BDirectorID = b.DirectorBranch, WID = w.ID };
var query = from c in db.Contracts
select c;
// Последовательно просеиваем наш список
if (deldate != false) // Убираем удаленных, если нужно
{
query = query.Where(x => x.Deldate == null);
}
if (branch.ID != 0)
{
query = query.Where(x => x.BranchID == branch.ID);
}
if (student.ID != 0)
{
query = query.Where(x => x.StudentID == student.ID);
}
if (manager.ID != 0)
{
query = query.Where(x => x.ManagerID == manager.ID);
}
if (course.ID != 0)
{
query = query.Where(x => x.CourseID == course.ID);
}
if (mindate != DateTime.MinValue)
{
query = query.Where(x => x.Date >= mindate);
}
if (maxdate != DateTime.MaxValue)
{
query = query.Where(x => x.Date <= maxdate);
}
if (min != 0)
{
query = query.Where(x => x.Cost >= min);
}
if (max != 0)
{
query = query.Where(x => x.Cost <= max);
}
if (sort != null) // Сортировка, если нужно
{
query = Utilit.OrderByDynamic(query, sort, asсdesс);
}
// Я перепроверила все варианты - это должно работать правильно!
countrecord = query.GroupBy(u => u.ID).Count();
query = query.Skip((page - 1) * count).Take(count);
query = query.Distinct();
foreach (var p in query)
{
list.Add(new Contract {
ID = p.ID, Date = p.Date, StudentID = p.StudentID, CourseID = p.CourseID, BranchID = p.BranchID, ManagerID = p.ManagerID, Cost = p.Cost, PayofMonth = p.PayofMonth, Canceldate = p.Canceldate, Deldate = p.Deldate, Editdate = p.Editdate
});
}
return(list);
}
}
//////////////////// ОДИН БОЛЬШОЙ ПОИСК !!! Если не введены никакие параметры, функция должна возвращать все оплаты //////////////////
public static List <Pay> FindAll(Boolean deldate, Pay pay, Contract contract, Worker teacher, Timetable timetable, Branch branch, DateTime mindate, DateTime maxdate, int min, int max, String sort, String asсdesс, int page, int count, ref int countrecord) //deldate =false - все и удал и неудал!
{
List <Pay> list = new List <Pay>();
using (SampleContext db = new SampleContext())
{
//var query = from b in db.Branches
// join w in db.Workers on b.DirectorBranch equals w.ID
// select new { BID = b.ID, BName = b.Name, BAddress = b.Address, BDeldate = b.Deldate, BEditdate = b.Editdate, BDirectorID = b.DirectorBranch, WID = w.ID };
var query = from p in db.Pays
select p;
// Последовательно просеиваем наш список
if (deldate != false) // Убираем удаленных, если нужно
{
query = query.Where(x => x.Deldate == null);
}
if (pay.Type != null)
{
query = query.Where(x => x.Type == pay.Type);
}
if (pay.Indicator != 0)
{
query = query.Where(x => x.Indicator == pay.Indicator);
}
if (branch.ID != 0)
{
query = query.Where(x => x.BranchID == branch.ID);
}
if (contract.ID != 0)
{
query = query.Where(x => x.ContractID == contract.ID);
}
if (teacher.ID != 0)
{
query = query.Where(x => x.WorkerID == teacher.ID);
}
if (timetable.ID != 0)
{
query = query.Where(x => x.TimetableID == timetable.ID);
}
if (mindate != DateTime.MinValue)
{
query = query.Where(x => x.Date >= mindate);
}
if (maxdate != DateTime.MaxValue)
{
query = query.Where(x => x.Date <= maxdate);
}
if (min != 0)
{
query = query.Where(x => x.Payment >= min);
}
if (max != 0)
{
query = query.Where(x => x.Payment <= max);
}
if (sort != null) // Сортировка, если нужно
{
query = Utilit.OrderByDynamic(query, sort, asсdesс);
}
countrecord = query.GroupBy(u => u.ID).Count();
query = query.Skip((page - 1) * count).Take(count);
query = query.Distinct();
foreach (var p in query)
{
list.Add(new Pay {
ID = p.ID, Date = p.Date, BranchID = p.BranchID, ContractID = p.ContractID, WorkerID = p.WorkerID, TimetableID = p.TimetableID, Indicator = p.Indicator, Payment = p.Payment, Purpose = p.Purpose, Type = p.Type, Deldate = p.Deldate, Editdate = p.Editdate
});
}
return(list);
}
}
//public static List<Student> GetSt()
//{
// // var context = new SampleContext();
// using (SampleContext context = new SampleContext())
// {
// var students = context.Students.ToList();
// return students;
// }
//}
//////////////////// ОДИН БОЛЬШОЙ ПОИСК !!! Если не введены никакие параметры, функция должна возвращать всех учеников //////////////////
public static List <Student> FindAll(Boolean deldate, Parent parent, Student student, Contract contracnt, Course course, String sort, String asсdesс, int page, int count, ref int countrecord) //deldate =false - все и удал и неудал!
{
List <Student> stList = new List <Student>();
using (SampleContext db = new SampleContext())
{
//////////////////////////////////////////////// НИКАК НЕ МОГУ СДЕЛАТЬ ЛЕВОЕ СОЕДИНЕНИЕ !!!!!!!!!!!!!!!!! //////////////////////////////////
// Соединение необходимых таблиц
//var query = from s in db.Students
// join sp in db.StudentsParents on s.ID equals sp.StudentID
// join p in db.Parents on sp.StudentID equals p.ID
// join c in db.Contracts on s.ID equals c.StudentID
// select new { SID = s.ID, SPhone = s.Phone, SFIO = s.FIO, SDelDate = s.Deldate, PID = p.ID, CID = c.ID };
//IQueryable<Student> query = from s in db.Students
// join sp in db.StudentsParents
// on s.ID equals sp.StudentID into studentGroup
// from m in studentGroup.DefaultIfEmpty()
// join c in db.Contracts
// on m.StudentID equals c.StudentID into contractGroup
// from co in contractGroup.DefaultIfEmpty()
// join p in db.Parents
// on sp.ParentID equals p.ID into contractGroup
// from co in contractGroup.DefaultIfEmpty()
// select new { SID = s.ID, SPhone = s.Phone, SFIO = s.FIO, SDelDate = s.Deldate, PID = p.ID, CID = c.ID };
// IQueryable<Student> v = db.Database.SqlQuery
// ("select * from Contracts Where Contracts.StudentID =" + "'" + id + "'" + "and Contracts.ManagerID =" + "'" + idm + "'");
//from s in db.Students
//join sp in db.StudentsParents
// on s.ID equals sp.StudentID into studentGroup
//from m in studentGroup.DefaultIfEmpty()
//join c in db.Contracts
// on m.StudentID equals c.StudentID into contractGroup
//from p in contractGroup.DefaultIfEmpty()
//from s in context.dc_tpatient_bookingd
//join bookingm in context.dc_tpatient_bookingm
// on d.bookingid equals bookingm.bookingid into bookingmGroup
//from m in bookingmGroup.DefaultIfEmpty()
//join patient in dc_tpatient
// on m.prid equals patient.prid into patientGroup
//from p in patientGroup.DefaultIfEmpty()
var query = from s in db.Students
join sp in db.StudentsParents on s.ID equals sp.StudentID
into std_prnt_temp
from std_prnt in std_prnt_temp.DefaultIfEmpty()
join p in db.Parents on std_prnt.StudentID equals p.ID
into prnt_temp
from prnt in prnt_temp.DefaultIfEmpty()
join c in db.Contracts on s.ID equals c.StudentID
into cntr_temp
from cntr in cntr_temp.DefaultIfEmpty()
join scour in db.StudentsCourses on s.ID equals scour.StudentID
into std_cour_temp
from stcour in std_cour_temp.DefaultIfEmpty()
//group new { s.ID, s.FIO, s.Phone } by s into percentGroup
//orderby percentGroup.Key
select new { ID = s.ID, Phone = s.Phone, FIO = s.FIO, Deldate = s.Deldate, Editdate = s.Editdate, PID = (prnt == null ? 0 : prnt.ID), CID = (cntr == null ? 0 : cntr.ID), CourseID = (stcour == null ? 0 : stcour.CourseID) };
//query = query.GroupBy(u => u.SID);
// Последовательно просеиваем наш список
if (deldate != false) // Убираем удаленных, если нужно
{
query = query.Where(x => x.Deldate == null);
}
if (student.FIO != null)
{
query = query.Where(x => x.FIO == student.FIO);
}
if (student.Phone != null)
{
query = query.Where(x => x.Phone == student.Phone);
}
if (parent.ID != 0)
{
query = query.Where(x => x.PID == parent.ID);
}
if (contracnt.ID != 0)
{
query = query.Where(x => x.CID == contracnt.ID);
}
if (course.ID != 0)
{
query = query.Where(x => x.CourseID == course.ID);
}
query = query.Distinct();
var query2 = query.GroupBy(s => new { s.ID, s.Phone, s.FIO, s.Deldate, s.Editdate }, (key, group) => new
{
ID = key.ID,
Phone = key.Phone,
FIO = key.FIO,
Deldate = key.Deldate,
Editdate = key.Editdate
});
// query2 = query2.Distinct();
if (sort != null) // Сортировка, если нужно
{
//if (askdesk == "desc")
//{
// query2 = query2.OrderByDescending(u => sort);
//}
//else
//{
// query2 = query2.OrderBy(u => sort);
//}
query2 = Utilit.OrderByDynamic(query2, sort, asсdesс);
}
countrecord = query2.Count();
//int countrecord = query2.GroupBy(u => u.ID).Count();
// var querycount = from query Select count(*);
//// int countrecord = 0;
// int countrecord =
// List<int> stid = new List<int>();
// foreach (var p in query)
// {
// if (stid.Find(x => x == p.SID) == 0)
// {
// stid.Add(p.SID);
// ++countrecord;
// }
// }
query2 = query2.Skip((page - 1) * count).Take(count); // Формирование страниц и кол-во записей на странице
foreach (var p in query2)
{
// if (stList.Find(x => x.ID == p.SID) == null)
{
stList.Add(new Student {
ID = p.ID, Phone = p.Phone, Deldate = p.Deldate, FIO = p.FIO, Editdate = p.Editdate
}); // Добавление ученика в лист, если такого еще нет, это для предохранения от дубликатов
}
}
return(stList);
////if (sort != null) // Сортировка, если нужно
////{
//// if (askdesk == "desk")
//// {
//// query = query.OrderByDescending(u => sort);
//// }
//// else
//// {
//// query = query.OrderBy(u => sort);
//// }
////}
////else { query = query.OrderBy(u => u.SID); }
////int countrecord1 = query.Count();
////int countrecord = query.GroupBy(u => u.SID).Count();
////// var querycount = from query Select count(*);
//////// int countrecord = 0;
////// int countrecord =
////// List<int> stid = new List<int>();
////// foreach (var p in query)
////// {
////// if (stid.Find(x => x == p.SID) == 0)
////// {
////// stid.Add(p.SID);
////// ++countrecord;
////// }
////// }
////query = query.Skip((page - 1) * count).Take(count); // Формирование страниц и кол-во записей на странице
////foreach (var p in query)
////{
//// if (stList.Find(x => x.ID == p.SID) == null)
//// {
//// stList.Add(new Student { ID = p.SID, Phone = p.SPhone, Deldate = p.SDelDate, FIO = p.SFIO }); // Добавление ученика в лист, если такого еще нет, это для предохранения от дубликатов
//// }
////}
////return stList;
}
}
//////////////////// ОДИН БОЛЬШОЙ ПОИСК !!! Если не введены никакие параметры, функция должна возвращать все филиалы //////////////////
public static List <Course> FindAll(Boolean deldate, Course course, Type type, Worker teacher, Branch branch, DateTime mindate, DateTime maxdate, int min, int max, String sort, String asсdesс, int page, int count, ref int countrecord) //deldate =false - все и удал и неудал!
{
List <Course> list = new List <Course>();
using (SampleContext db = new SampleContext())
{
//var query = from s in db.Students
// join sp in db.StudentsParents on s.ID equals sp.StudentID
// into std_prnt_temp
// from std_prnt in std_prnt_temp.DefaultIfEmpty()
// join p in db.Parents on std_prnt.StudentID equals p.ID
// into prnt_temp
// from prnt in prnt_temp.DefaultIfEmpty()
// join c in db.Contracts on s.ID equals c.StudentID
// into cntr_temp
// from cntr in cntr_temp.DefaultIfEmpty()
// join scour in db.StudentsCourses on s.ID equals scour.StudentID
// into std_cour_temp
// from stcour in std_cour_temp.DefaultIfEmpty()
// select new { SID = s.ID, SPhone = s.Phone, SFIO = s.FIO, SDelDate = s.Deldate, PID = (prnt == null ? 0 : prnt.ID), CID = (cntr == null ? 0 : cntr.ID), CourseID = (stcour == null ? 0 : stcour.CourseID) };
var query = from c in db.Courses
join w in db.TeachersCourses on c.ID equals w.CourseID
into c_teach_temp
from c_teach in c_teach_temp.DefaultIfEmpty()
select new { ID = c.ID, nameGroup = c.nameGroup, Cost = c.Cost, Deldate = c.Deldate, Editdate = c.Editdate, TypeID = c.TypeID, BranchID = c.BranchID, Start = c.Start, End = c.End, TeacherID = (c_teach == null ? 0 : c_teach.TeacherID) };
// Последовательно просеиваем наш список
if (deldate != false) // Убираем удаленных, если нужно
{
query = query.Where(x => x.Deldate == null);
}
if (course.nameGroup != null)
{
query = query.Where(x => x.nameGroup == course.nameGroup);
}
if (branch.ID != 0)
{
query = query.Where(x => x.BranchID == branch.ID);
}
if (type.ID != 0)
{
query = query.Where(x => x.TypeID == type.ID);
}
if (teacher.ID != 0)
{
query = query.Where(x => x.TeacherID == teacher.ID);
}
if (mindate != DateTime.MinValue)
{
query = query.Where(x => x.Start >= mindate);
}
if (maxdate != DateTime.MaxValue)
{
query = query.Where(x => x.End <= maxdate);
}
if (min != 0)
{
query = query.Where(x => x.Cost >= min);
}
if (max != 0)
{
query = query.Where(x => x.Cost <= max);
}
if (sort != null) // Сортировка, если нужно
{
query = Utilit.OrderByDynamic(query, sort, asсdesс);
}
countrecord = query.GroupBy(u => u.ID).Count();
query = query.Skip((page - 1) * count).Take(count);
query = query.Distinct();
foreach (var p in query)
{
list.Add(new Course {
ID = p.ID, nameGroup = p.nameGroup, BranchID = p.BranchID, TypeID = p.TypeID, Cost = p.Cost, Start = p.Start, End = p.End, Deldate = p.Deldate, Editdate = p.Editdate
});
}
return(list);
}
}
//////////////////// ОДИН БОЛЬШОЙ ПОИСК !!! Если не введены никакие параметры, функция должна возвращать все типы //////////////////
public static List <Type> FindAll(Boolean deldate, Type type, int minLes, int maxLes, double minCost, double maxCost, int minMonth, int maxMonth, String sort, String asсdesс, int page, int count, ref int countrecord) //deldate =false - все и удал и неудал!
{
List <Type> list = new List <Type>();
using (SampleContext db = new SampleContext())
{
var query = from t in db.Types
select t;
// Последовательно просеиваем наш список
if (deldate != false) // Убираем удаленных, если нужно
{
query = query.Where(x => x.Deldate == null);
}
if (type.Name != null)
{
query = query.Where(x => x.Name == type.Name);
}
if (minLes != 0)
{
query = query.Where(x => x.Lessons >= minLes);
}
if (maxLes != 0)
{
query = query.Where(x => x.Lessons <= maxLes);
}
if (minCost != 0)
{
query = query.Where(x => x.Cost >= minCost);
}
if (maxCost != 0)
{
query = query.Where(x => x.Cost <= maxCost);
}
if (minMonth != 0)
{
query = query.Where(x => x.Month >= minMonth);
}
if (maxMonth != 0)
{
query = query.Where(x => x.Month <= maxMonth);
}
if (sort != null) // Сортировка, если нужно
{
query = Utilit.OrderByDynamic(query, sort, asсdesс);
}
countrecord = query.GroupBy(u => u.ID).Count();
query = query.Skip((page - 1) * count).Take(count);
query = query.Distinct();
foreach (var p in query)
{
list.Add(new Type {
ID = p.ID, Name = p.Name, Cost = p.Cost, Lessons = p.Lessons, Month = p.Month, Note = p.Note, Deldate = p.Deldate, Editdate = p.Editdate
});
}
return(list);
}
}
//public static List<Worker> GetWo(SampleContext context)
//{
// // var context = new SampleContext();
// var workers = context.Workers.ToList();
// return workers;
//}
//////////////////// ОДИН БОЛЬШОЙ ПОИСК !!! Если не введены никакие параметры, функция должна возвращать все филиалы //////////////////
public static List <Worker> FindAll(Boolean deldate, Worker worker, Branch branch, String sort, String asсdesс, int page, int count, ref int countrecord) //deldate =false - все и удал и неудал!
{
List <Worker> list = new List <Worker>();
using (SampleContext db = new SampleContext())
{
//var query = from w in db.Workers
// join b in db.Branches on w.ID equals b.DirectorBranch
// select w;
var query = from w in db.Workers
select w;
// Последовательно просеиваем наш список
if (deldate != false) // Убираем удаленных, если нужно
{
query = query.Where(x => x.Deldate == null);
}
if (worker.FIO != null)
{
query = query.Where(x => x.FIO == worker.FIO);
}
if (worker.Position != null)
{
query = query.Where(x => x.Position == worker.Position);
}
if (worker.Type != 0)
{
query = query.Where(x => x.Type == worker.Type);
}
if (branch.ID != 0)
{
query = query.Where(x => x.BranchID == branch.ID);
}
if (sort != null) // Сортировка, если нужно
{
query = Utilit.OrderByDynamic(query, sort, asсdesс);
}
// int countrecord = 0;
//List<int> stid = new List<int>();
//foreach (var p in query)
//{
// if (stid.Find(x => x == p.ID) == 0)
// {
// stid.Add(p.ID);
// ++countrecord;
// }
//}
// Я перепроверила все варианты - это должно работать правильно!
countrecord = query.GroupBy(u => u.ID).Count();
query = query.Skip((page - 1) * count).Take(count);
foreach (var p in query)
{
list.Add(new Worker {
ID = p.ID, FIO = p.FIO, Type = p.Type, Position = p.Position, BranchID = p.BranchID, Deldate = p.Deldate, Editdate = p.Editdate
});
}
return(list);
}
}
//////////////////// ОДИН БОЛЬШОЙ ПОИСК !!! Если не введены никакие параметры, функция должна возвращать все элементы расписания //////////////////
public static List <Timetable> FindAll(Boolean deldate, Branch branch, Cabinet cabinet, Worker teacher, Course course, Student student, DateTime date, String sort, String asсdesс, int page, int count, ref int countrecord) //deldate =false - все и удал и неудал!
{
List <Timetable> list = new List <Timetable>();
using (SampleContext db = new SampleContext())
{
var query = from c in db.Cabinets
join t in db.Timetables on c.ID equals t.CabinetID
join tt in db.TimetablesTeachers on t.ID equals tt.TimetableID
join cour in db.Courses on t.CourseID equals cour.ID
join sc in db.StudentsCourses on t.CourseID equals sc.CourseID
select new { ID = t.ID, StudentID = sc.StudentID, CabinetID = t.CabinetID, CourseID = t.CourseID, Note = t.Note, Startlesson = t.Startlesson, Endlesson = t.Endlesson, Deldate = t.Deldate, Editdate = t.Editdate, BranchID = c.BranchID, TeacherID = tt.TeacherID };
// Последовательно просеиваем наш список
if (deldate != false) // Убираем удаленных, если нужно
{
query = query.Where(x => x.Deldate == null);
}
if (branch.ID != 0)
{
query = query.Where(x => x.BranchID == branch.ID);
}
if (course.ID != 0)
{
query = query.Where(x => x.CourseID == course.ID);
}
if (student.ID != 0)
{
query = query.Where(x => x.StudentID == student.ID);
}
if (cabinet.ID != 0)
{
query = query.Where(x => x.CabinetID == cabinet.ID);
}
if (teacher.ID != 0)
{
query = query.Where(x => x.TeacherID == teacher.ID);
}
// string format = "yyyy-MM-dd HH:mm:ss";
// Подсчет недели, которую необходимо отобразить!
DateTime firstdate = date.AddDays(-((date.DayOfWeek - System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat.FirstDayOfWeek + 7) % 7)).Date;
DateTime lastdate = firstdate.AddDays(+6);
if (firstdate != DateTime.MinValue)
{
query = query.Where(x => x.Startlesson >= firstdate);
}
if (lastdate != DateTime.MaxValue)
{
query = query.Where(x => x.Endlesson <= lastdate);
}
//, StudentID = sc.StudentID, CabinetID = t.CabinetID, CourseID = t.CourseID, Note = t.Note,
// Startlesson = t.Startlesson, Endlesson = t.Endlesson, Deldate = t.Deldate, Editdate = t.Editdate, BranchID = c.BranchID, TeacherID = tt.TeacherID
var query2 = query.GroupBy(t => new { t.ID, t.Startlesson, t.Endlesson, /*t.StudentID, */ t.CabinetID, t.CourseID, t.Note, t.Deldate, t.Editdate }, (key, group) => new
{
ID = key.ID,
CourseID = key.CourseID,
// StudentID = key.StudentID,
CabinetID = key.CabinetID,
Deldate = key.Deldate,
Editdate = key.Editdate,
Note = key.Note,
Startlesson = key.Startlesson,
Endlesson = key.Endlesson
});
// query = query.Distinct();
if (sort != null) // Сортировка, если нужно
{
query2 = Utilit.OrderByDynamic(query2, sort, asсdesс);
}
countrecord = query2.GroupBy(u => u.ID).Count();
query2 = query2.Skip((page - 1) * count).Take(count);
query2 = query2.Distinct();
foreach (var p in query2)
{
list.Add(new Timetable {
ID = p.ID, CourseID = p.CourseID, CabinetID = p.CabinetID, Startlesson = p.Startlesson, Endlesson = p.Endlesson, Note = p.Note, Deldate = p.Deldate, Editdate = p.Editdate
});
}
return(list);
}
}
//////////////////// ОДИН БОЛЬШОЙ ПОИСК !!! Если не введены никакие параметры, функция должна возвращать все темы //////////////////
public static List <Visit> FindAll(Boolean deldate, Visit visit, Theme theme, Course course, Student student, DateTime mindate, DateTime maxdate, String sort, String asсdesс, int page, int count, ref int countrecord) //deldate =false - все и удал и неудал!
{
List <Visit> list = new List <Visit>();
using (SampleContext db = new SampleContext())
{
var query = from tt in db.TimetablesThemes
join t in db.Timetables on tt.TimetableID equals t.ID
join v in db.Visits on t.ID equals v.TimetableID
join s in db.Students on v.StudentID equals s.ID
join sc in db.StudentsCourses on s.ID equals sc.StudentID
select new { ID = v.ID, StudentID = v.StudentID, TimetableID = v.TimetableID, Vis = v.Vis, Deldate = v.Deldate, Editdate = v.Editdate, Theme = tt.ThemeID, Course = sc.CourseID, Date = t.Startlesson };
// Последовательно просеиваем наш список
if (deldate != false) // Убираем удаленных, если нужно
{
query = query.Where(x => x.Deldate == null);
}
if (visit.ID != 0)
{
query = query.Where(x => x.Vis == visit.Vis);
}
if (course.ID != 0)
{
query = query.Where(x => x.Course == course.ID);
}
if (student.ID != 0)
{
query = query.Where(x => x.StudentID == student.ID);
}
if (theme.ID != 0)
{
query = query.Where(x => x.Theme == theme.ID);
}
if (mindate != DateTime.MinValue)
{
query = query.Where(x => x.Date >= mindate);
}
if (maxdate != DateTime.MaxValue)
{
query = query.Where(x => x.Date <= maxdate);
}
if (sort != null) // Сортировка, если нужно
{
query = Utilit.OrderByDynamic(query, sort, asсdesс);
}
countrecord = query.GroupBy(u => u.ID).Count();
query = query.Skip((page - 1) * count).Take(count);
query = query.Distinct();
foreach (var p in query)
{
list.Add(new Visit {
ID = p.ID, StudentID = p.StudentID, TimetableID = p.TimetableID, Vis = p.Vis, Deldate = p.Deldate, Editdate = p.Editdate
});
}
return(list);
}
}
//////////////////// ОДИН БОЛЬШОЙ ПОИСК !!! Если не введены никакие параметры, функция должна возвращать всех родителей //////////////////
public static List <Parent> FindAll(Boolean deldate, Parent parent, Student student, String sort, String asсdesс, int page, int count, ref int countrecord) //deldate =false - все и удал и неудал!
{
List <Parent> parentList = new List <Parent>();
using (SampleContext db = new SampleContext())
{
// Соединение необходимых таблиц
var parents = from p in db.Parents
join sp in db.StudentsParents on p.ID equals sp.ParentID
join s in db.Students on sp.StudentID equals s.ID
select new { ID = p.ID, Phone = p.Phone, FIO = p.FIO, Deldate = p.Deldate, Editdate = p.Editdate, SPhone = s.Phone, SFIO = s.FIO, SID = s.ID };
// Последовательно просеиваем наш список
if (deldate != false) // Убираем удаленных, если нужно
{
parents = parents.Where(x => x.Deldate == null);
}
if (student.Phone != null)
{
parents = parents.Where(x => x.SPhone == student.Phone);
}
if (student.FIO != null)
{
parents = parents.Where(x => x.SFIO == student.FIO);
}
if (student.ID != 0)
{
parents = parents.Where(x => x.SID == student.ID);
}
if (parent.FIO != null)
{
parents = parents.Where(x => x.FIO == parent.FIO);
}
if (parent.Phone != null)
{
parents = parents.Where(x => x.Phone == parent.Phone);
}
var query2 = parents.GroupBy(s => new { s.ID, s.FIO, s.Phone, s.Editdate, s.Deldate }, (key, group) => new
{
ID = key.ID,
FIO = key.FIO,
Phone = key.Phone,
Editdate = key.Editdate,
Deldate = key.Deldate,
});
if (sort != null) // Сортировка, если нужно
{
query2 = Utilit.OrderByDynamic(query2, sort, asсdesс);
}
countrecord = query2.GroupBy(u => u.ID).Count();
query2 = query2.Skip((page - 1) * count).Take(count); // Формирование страниц и кол-во записей на странице
foreach (var p in query2)
{
//if (sort != null) // Сортировка, если нужно
//{
// parents = Utilit.OrderByDynamic(parents, sort, askdesk);
//}
//parents = parents.Skip((page-1) * count).Take(count); // Формирование страниц и кол-во записей на странице
//foreach (var p in parents)
//{
// if (parentList.Find(x => x.ID == p.ID) == null)
// {
parentList.Add(new Parent {
ID = p.ID, Phone = p.Phone, Deldate = p.Deldate, FIO = p.FIO, Editdate = p.Editdate
}); // Добавление родителя в лист, если такого еще нет, это для предохранения от дубликатов
}
//}
}
return(parentList);
}
//////////////////// ОДИН БОЛЬШОЙ ПОИСК !!! Если не введены никакие параметры, функция должна возвращать все темы //////////////////
public static List <Theme> FindAll(Boolean deldate, Theme theme, Course course, DateTime mindate, DateTime maxdate, String sort, String asсdesс, int page, int count, ref int countrecord) //deldate =false - все и удал и неудал!
{
List <Theme> list = new List <Theme>();
using (SampleContext db = new SampleContext())
{ // Left jion для соединения таблиц, чтобы высвечивались все темы, не зависимо от того, если ли по этим темам занятия(Timetable)
var query = from t in db.Themes
join tt in db.TimetablesThemes on t.ID equals tt.ThemeID
into theme_time_temp
from theme_time in theme_time_temp.DefaultIfEmpty()
join s in db.Timetables on theme_time.TimetableID equals s.ID
into time_temp
from time in time_temp.DefaultIfEmpty()
select new { ID = t.ID, Date = t.Date, Tema = t.Tema, Homework = t.Homework, Deadline = t.Deadline, Deldate = t.Deldate, Editdate = t.Editdate, Course = (time == null ? 0 : time.CourseID) /*, ThTi = (theme_time == null ? 0 : theme_time.ID) */ };
// Последовательно просеиваем наш список
if (deldate != false) // Убираем удаленных, если нужно
{
query = query.Where(x => x.Deldate == null);
}
if (theme.Tema != null)
{
query = query.Where(x => x.Tema == theme.Tema);
}
if (course.ID != 0)
{
query = query.Where(x => x.Course == course.ID);
}
if (mindate != DateTime.MinValue)
{
query = query.Where(x => x.Date >= mindate);
}
if (maxdate != DateTime.MaxValue)
{
query = query.Where(x => x.Date <= maxdate);
}
if (sort != null) // Сортировка, если нужно
{
query = Utilit.OrderByDynamic(query, sort, asсdesс);
}
countrecord = query.GroupBy(u => u.ID).Count();
query = query.Skip((page - 1) * count).Take(count);
query = query.Distinct();
foreach (var p in query)
{
list.Add(new Theme {
ID = p.ID, Date = p.Date, Tema = p.Tema, Homework = p.Homework, Deadline = p.Deadline, Deldate = p.Deldate, Editdate = p.Editdate
});
}
return(list);
}
}
