public void GetNamesOfStudents(DiaryConnection db, int GroupId, int lecturerId, int SubjectId, Semester semester)
{
//достаём из бд имена студентов по пред за выбранный семестр для журнала
var namesOfStudents = new List <String>();
var sN = db.Students.Where(p => p.GroupId == GroupId).Select(p => new { Name = p.FullName }).ToList();
foreach (var b in sN)
{
namesOfStudents.Add(b.Name);
}
this.NamesOfStudents = namesOfStudents;
}
public static Semester Today()
{
Semester semester;
using (DiaryConnection db = new DiaryConnection())
{
//Надо дописать с обращением к базе данных
var toDay = DateTime.Today;
semester = db.Semester.Where(p => p.BeginningDate <toDay && p.EndDate> toDay).First();
}
return(semester);
}
public void GetNamesOfSubject(DiaryConnection db, int StudentId, Semester semester)
{
//достаём из бд список предметов за выбранный семестр для журнала
var namesOfSubject = new List <String>();
var sN = (from p in db.Subjects
join c in db.TeachersGroupsSubjects on p.Id
equals c.SubjectId
join h in db.TableOfGrades on c.Id equals h.TeachersGroupsSubjectId
join d in db.Semester on h.SemesterId equals d.Id
join g in db.Groups on c.GroupId equals g.Id
join s in db.Students on g.Id equals s.GroupId
where s.Id == StudentId && d.Number == semester.Number && d.Year == semester.Year && h.TypeOfKnowledgeControl == "Journal"
select new { Id = p.Id, Name = p.Name }).ToList(); // обёрнуто в ToList для избежания проблем с DataReader.
foreach (var b in sN)
{
namesOfSubject.Add(b.Name);
}
this.NamesOfSubject = namesOfSubject;
}
private List <FinalGrade> GetFinalGrades(DiaryConnection db, int StudentId, string typeOfGrades, Semester semester)
{
var finalGrades = new List <FinalGrade>();
var fG = (from p in db.TableEntry
join c in db.TableOfGrades on p.TableOfGradeId equals c.Id
join d in db.Semester on c.SemesterId equals d.Id
join g in db.TeachersGroupsSubjects on c.TeachersGroupsSubjectId equals g.Id
join s in db.Subjects on g.SubjectId equals s.Id
where p.StudentId == StudentId && d.Number == semester.Number && d.Year == semester.Year && c.TypeOfKnowledgeControl == typeOfGrades
select new
{
Value = p.Value,
Subject = s.Name
}).ToList(); // обёрнуто в ToList для избежания проблем с DataReader.
foreach (var b in fG)
{
finalGrades.Add(new FinalGrade(b.Value, b.Subject));
}
return(finalGrades);
}
public List <ViewSubjects> GetSubjectsForLecturer(DiaryConnection db, int lecturerId, Semester semester)
{
var subjects = new List <ViewSubjects>();
var fG = (from p in db.Subjects
join b in db.TeachersGroupsSubjects on p.Id equals b.SubjectId
join c in db.TableOfGrades on b.Id equals c.TeachersGroupsSubjectId
join d in db.Semester on c.SemesterId equals d.Id
where b.TeacherId == lecturerId && c.Semester == semester &&
d.Year == semester.Year
select new
{
Name = p.Name,
Id = p.Id
}).ToList(); // обёрнуто в ToList для избежания проблем с DataReader.
foreach (var b in fG)
{
subjects.Add(new ViewSubjects(b.Id, b.Name));
}
return(subjects);
}
public void GetRefToSemesters(DiaryConnection db, int studentId)
{
List <List <Semester> > orderedSemesters = new List <List <Semester> >();
List <string> years = new List <string>();
var semesters = (from p in db.Semester
join c in db.TableOfGrades on p.Id equals c.SemesterId
join g in db.TeachersGroupsSubjects on c.TeachersGroupsSubjectId equals g.Id
join f in db.Groups on g.GroupId equals f.Id
join h in db.Students on f.Id equals h.GroupId
where h.Id == studentId
select new
{
Id = p.Id,
BeginningDate = p.BeginningDate,
EndDate = p.EndDate,
Number = p.Number,
Year = p.Year
}).Distinct();
List <Semester> findSemesters = new List <Semester>();
foreach (var item in semesters)
{
findSemesters.Add(new Semester {
Id = item.Id, BeginningDate = item.BeginningDate, EndDate = item.EndDate, Number = item.Number, Year = item.Year
});
if (!years.Contains(item.Year))
{
years.Add(item.Year);
}
}
foreach (var item in years)
{
orderedSemesters.Add(findSemesters.Where(p => p.Year == item).ToList());
}
OrderedSemesters = orderedSemesters;
}
public void GetSubjectGrades(DiaryConnection db, int StudentId, Semester semester)
{
//извлекаем id предметов.
//фактически повторный запрос
var idSubject = (from p in db.Subjects
join c in db.TeachersGroupsSubjects on p.Id equals c.SubjectId
join h in db.TableOfGrades on c.Id equals h.TeachersGroupsSubjectId
join d in db.Semester on h.SemesterId equals d.Id
join g in db.Groups on c.GroupId equals g.Id
join s in db.Students on g.Id equals s.GroupId
where s.Id == StudentId && d.Number == semester.Number && d.Year == semester.Year && h.TypeOfKnowledgeControl == "Journal"
select new { Id = p.Id }).ToList();
//достаём из бд список оценок к найденым предметам
var subGr = new List <List <TableEntry> >();
//достаём оценки отдельно для каждого предмета
foreach (var b in idSubject)
{
var listFfGrades = (from p in db.TableEntry //db.TableEntrys.Join(db.TableOfGrades, p => p.TableOfGradeId ,c => c.Id,(p,c) => ).Join
join c in db.TableOfGrades on p.TableOfGradeId equals c.Id
join d in db.Semester on c.SemesterId equals d.Id
join g in db.TeachersGroupsSubjects on c.TeachersGroupsSubjectId equals g.Id
//join s in db.Subjects on g.SubjectId equals s.Id
where p.StudentId == StudentId && g.SubjectId == b.Id && d.Number == semester.Number && d.Year == semester.Year
select new
{
Value = p.Value,
Date = p.Date,
Id = p.Id,
TableOfGradeId = p.TableOfGradeId,
StudentId = p.StudentId
}).ToList(); // обёрнуто в ToList для избежания проблем с DataReader.
// из списка анонимного типа в список TableEntry
var list1 = new List <TableEntry>(); //вложенный список
foreach (var c in listFfGrades)
{
list1.Add(new TableEntry
{
Id = c.Id,
StudentId = c.StudentId,
Date = c.Date,
TableOfGradeId = c.TableOfGradeId,
Value = c.Value
});
}
subGr.Add(list1);
}
this.ListOfGrades = subGr;
//пока у нас учтены только те дни в которых стоят оценки, надо заполнить пустые клетки в таблице
List <List <string> > subjectsGrades = new List <List <string> >();
foreach (var b in subGr)
{
DateTime startSemestr = new DateTime(2017, 2, 10);
DateTime endSemestr = new DateTime(2017, 6, 10);
DateTime day = new DateTime(2017, 2, 10);
//оценки за 1 предмет
List <string> subjGrades = new List <string>();
while (day <= endSemestr)
{
var toDayTableEntry = b.Find(x => x.Date == day);
if (toDayTableEntry != null)
{
subjGrades.Add((string)toDayTableEntry.Value);
}
else
{
subjGrades.Add("");
}
day = day.AddDays(1);
}
subjectsGrades.Add(subjGrades);
}
this.SubjectsGrades = subjectsGrades;
}
