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;
}
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);
}
private List <TableEntry> GetFinalGrades(DiaryConnection db, int GroupId, int SubjectId, int lecturerId, string typeOfGrades, Semester semester)
{
var finalGrades = new List <TableEntry>();
var fG = (from p in db.TableEntry
join b in db.Students on p.StudentId equals b.Id
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
where g.GroupId == GroupId && g.TeacherId == lecturerId &&
g.SubjectId == SubjectId && d.Number == semester.Number &&
d.Year == semester.Year && c.TypeOfKnowledgeControl == typeOfGrades
select new
{
TableOfGradeId = p.TableOfGradeId,
Id = p.Id,
Date = p.Date,
Value = p.Value,
StudentId = p.StudentId
}).ToList(); // обёрнуто в ToList для избежания проблем с DataReader.
foreach (var p in fG)
{
finalGrades.Add(new TableEntry {
TableOfGradeId = p.TableOfGradeId,
Id = p.Id,
Date = p.Date,
Value = p.Value,
StudentId = p.StudentId
});
}
return(finalGrades);
}
public void GetAllFinalGrades(DiaryConnection db, int GroupId, int lecturerId, int SubjectId, Semester semester)
{
this.ExamGrades = GetFinalGrades(db, GroupId, SubjectId, lecturerId, "Exam", semester);
this.PassFailTestGrades = GetFinalGrades(db, GroupId, SubjectId, lecturerId, "PassFailTest", semester);
this.PracticalWorkGrades = GetFinalGrades(db, GroupId, SubjectId, lecturerId, "PracticalWork", semester);
this.CoursePaperGrades = GetFinalGrades(db, GroupId, SubjectId, lecturerId, "CoursePaper", semester);
}
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 List <Groups> GetGroupsForLecturer(DiaryConnection db, int lecturerId, Semester semester)
{
//var groups = new List<Groups>();
var groups = (from p in db.Groups
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 && d.Number == semester.Number &&
d.Year == semester.Year
select new Groups
{
Id = p.Id,
Number = p.Number,
YearOfAdmission = p.YearOfAdmission,
Faculty = p.Faculty,
Degree = p.Degree,
MonitorId = p.MonitorId
}).ToList(); // обёрнуто в ToList для избежания проблем с DataReader.
/*foreach (var b in fG)
* {
* groups.Add(new Groups(b.Id, b.Number, b.YearOfAdmission, b.Faculty, b.Degree, b.StudentId));
* }*/
//Вычисление номера группы в текущем году.
/*foreach (var b in groups)
* {
* b.Number = DiarySchema.Groups.GetActualGroupNumber(b, DateTime.Today.Year);
* }*/
return(groups);
}
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);
}
