public void Test3()
{
var term = new AcademicTerm()
{
School = "Northeastern University",
Term = "Fall",
Year = 2016
};
var course = new Course()
{
DeptNumber = "CS 5800",
Name = "Algorithms and Data",
Instructor = "Rajmohan Rajaraman",
GoalModeEnabled = true,
GoalEarned = 80
};
term.AddCourse(course);
var midterms = new Section()
{
Name = "Midterms",
Weight = .35
};
course.AddSection(midterms);
var final = new Section()
{
Name = "Final",
Weight = .65
};
course.AddSection(final);
var a1 = new Assignment(1)
{
Name = "Midterm 1",
Earned = 65,
Worth = 100
};
midterms.AddAssignment(a1);
var a2 = new Assignment(2)
{
Name = "Midterm 2",
Earned = 90,
Worth = 100
};
midterms.AddAssignment(a2);
var a3 = new Assignment(3)
{
Name = "Midterm 3",
Earned = 0,
Worth = 100,
GoalSelected = true
};
midterms.AddAssignment(a3);
var a4 = new Assignment(4)
{
Name = "Final 1",
Earned = 95,
Worth = 100
};
final.AddAssignment(a4);
var a5 = new Assignment(5)
{
Name = "Final 2",
Earned = 0,
Worth = 100,
GoalSelected = true
};
final.AddAssignment(a5);
var a6 = new Assignment(6)
{
Name = "Final 3",
Earned = 0,
Worth = 100,
GoalSelected = true
};
final.AddAssignment(a6);
course.CalculateGoals(ObjectiveType.Weighted);
}
//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<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;
}
}