/// <summary>
/// Method for generating all D-students names from all of the groups
/// </summary>
/// <param name="university">DBContext</param>
/// <param name="filepath">Where to save</param>
/// <param name="TypeOfOrder">Sorting type</param>
public void WriteAllDStudents(string filepath, string TypeOfOrder = "OrderBy")
{
using (UniversityClassesDataContext db = new UniversityClassesDataContext(connectString))
{
BuildInformation building = new BuildInformation();
Dictionary <string, List <string> > ordered_group = building.GetAllDStudents(db, TypeOfOrder);
Application excelApp = new Application();
Workbook workBook = excelApp.Workbooks.Add();
Worksheet workSheet = workBook.ActiveSheet;
workSheet.Cells[1, "A"] = "Group";
workSheet.Cells[1, "B"] = "Allocated students";
int i = 2;
foreach (var group in ordered_group)
{
workSheet.Cells[i, "A"] = $"{group.Key}";
foreach (var student in group.Value)
{
workSheet.Cells[i, "B"] = student;
i++;
}
}
workBook.Close(true, filepath);
excelApp.Quit();
}
}
/// <summary>
/// Deletes the entity upon the defined query
/// </summary>
/// <param name="query">Delete Query</param>
public void Delete(Expression <Func <T, bool> > query)
{
using (UniversityClassesDataContext db = new UniversityClassesDataContext(connectString))
{
try
{
db.GetTable <T>().DeleteOnSubmit(db.GetTable <T>().Where(query).Single());
db.SubmitChanges();
}
catch (SqlException ex)
{
StringBuilder errorMessages = new StringBuilder();
for (int ind = 0; ind < ex.Errors.Count; ind++)
{
errorMessages.Append("Index #" + ind + "\n" +
"Message: " + ex.Errors[ind].Message + "\n" +
"Error Number: " + ex.Errors[ind].Number + "\n" +
"LineNumber: " + ex.Errors[ind].LineNumber + "\n" +
"Source: " + ex.Errors[ind].Source + "\n" +
"Procedure: " + ex.Errors[ind].Procedure + "\n");
}
throw new Exception(errorMessages.ToString());
}
}
}
/// <summary>
/// Gets a one item from the table
/// </summary>
/// <param name="idSelector">Get query</param>
/// <returns></returns>
public T Get(Func <T, bool> idSelector)
{
using (UniversityClassesDataContext db = new UniversityClassesDataContext(connectString))
{
try
{
db.DeferredLoadingEnabled = false;
return(db.GetTable <T>().Single(idSelector));
}
catch (SqlException ex)
{
StringBuilder errorMessages = new StringBuilder();
for (int ind = 0; ind < ex.Errors.Count; ind++)
{
errorMessages.Append("Index #" + ind + "\n" +
"Message: " + ex.Errors[ind].Message + "\n" +
"Error Number: " + ex.Errors[ind].Number + "\n" +
"LineNumber: " + ex.Errors[ind].LineNumber + "\n" +
"Source: " + ex.Errors[ind].Source + "\n" +
"Procedure: " + ex.Errors[ind].Procedure + "\n");
}
throw new Exception(errorMessages.ToString());
}
}
}
/// <summary>
/// Adds a new record to the DB
/// </summary>
/// <param name="entity">Current Object</param>
/// <param name="IdPropertyName">Name of the property containing identity Column</param>
/// <returns><see cref="System.Object"/> </returns>
public void Add(T entity, string IdPropertyName)
{
using (UniversityClassesDataContext db = new UniversityClassesDataContext(connectString))
{
try
{
db.GetTable <T>().InsertOnSubmit(entity);
db.SubmitChanges();
}
catch (SqlException ex)
{
StringBuilder errorMessages = new StringBuilder();
for (int ind = 0; ind < ex.Errors.Count; ind++)
{
errorMessages.Append("Index #" + ind + "\n" +
"Message: " + ex.Errors[ind].Message + "\n" +
"Error Number: " + ex.Errors[ind].Number + "\n" +
"LineNumber: " + ex.Errors[ind].LineNumber + "\n" +
"Source: " + ex.Errors[ind].Source + "\n" +
"Procedure: " + ex.Errors[ind].Procedure + "\n");
}
throw new Exception(errorMessages.ToString());
}
}
}
/// <summary>
/// Updates Entity
/// </summary>
/// <param name="entity">Entity which hold the updated information</param>
/// <param name="query">query to get the same entity from db and replace it</param>
public virtual void Update(T entity, Expression <Func <T, bool> > query)
{
using (UniversityClassesDataContext db = new UniversityClassesDataContext(connectString))
{
object propertyValue = null;
T entityFromDB = db.GetTable <T>().Where(query).SingleOrDefault();
if (null == entityFromDB)
{
throw new NullReferenceException("Query Supplied to Get entity from DB is invalid, NULL value returned");
}
PropertyInfo[] properties = entityFromDB.GetType().GetProperties();
foreach (PropertyInfo property in properties)
{
propertyValue = null;
if (null != property.GetSetMethod())
{
PropertyInfo entityProperty = entity.GetType().GetProperty(property.Name);
if (entityProperty.PropertyType.BaseType == Type.GetType("System.ValueType") || entityProperty.PropertyType == Type.GetType("System.String"))
{
propertyValue = entity.GetType().GetProperty(property.Name).GetValue(entity, null);
}
if (null != propertyValue)
{
property.SetValue(entityFromDB, propertyValue, null);
}
}
}
try
{
db.SubmitChanges();
}
catch (SqlException ex)
{
StringBuilder errorMessages = new StringBuilder();
for (int ind = 0; ind < ex.Errors.Count; ind++)
{
errorMessages.Append("Index #" + ind + "\n" +
"Message: " + ex.Errors[ind].Message + "\n" +
"Error Number: " + ex.Errors[ind].Number + "\n" +
"LineNumber: " + ex.Errors[ind].LineNumber + "\n" +
"Source: " + ex.Errors[ind].Source + "\n" +
"Procedure: " + ex.Errors[ind].Procedure + "\n");
}
throw new Exception(errorMessages.ToString());
}
}
}
/// <summary>
/// Generates report about speciality results in one session in XLXS file
/// </summary>
/// <param name="id">Session ID</param>
/// <param name="filepath">filepath to save</param>
public void WriteSpecialityGrade(int id, string filepath, string TypeOfOrder)
{
using (UniversityClassesDataContext db = new UniversityClassesDataContext(connectString))
{
BuildInformation buildInformation = new BuildInformation();
Dictionary <string, float> examiner_marks;
Dictionary <string, float> group_ordered;
Dictionary <string, float> spec_marks;
buildInformation.GetSpecialitySessionResults(id, db, out group_ordered, out spec_marks, out examiner_marks, TypeOfOrder);
Application excelApp = new Application();
Workbook workBook = excelApp.Workbooks.Add();
Worksheet workSheet = workBook.ActiveSheet;
Session session = db.Session.Where(p => p.ID == id).FirstOrDefault();
workSheet.Cells[1, "A"] = "Session";
workSheet.Cells[1, "B"] = "Group";
workSheet.Cells[1, "C"] = "Average Mark";
workSheet.Cells[2, "A"] = $"{session.StartDate} - {session.EndDate}";
int i = 2;
foreach (var group in group_ordered)
{
workSheet.Cells[i, "B"] = group.Key;
workSheet.Cells[i, "C"] = group.Value;
i++;
}
workSheet.Cells[i, "A"] = "Specialization mark";
foreach (var spec in spec_marks)
{
workSheet.Cells[i, "B"] = spec.Key;
workSheet.Cells[i, "C"] = spec.Value;
i++;
}
workSheet.Cells[i, "A"] = "Examiner mark";
foreach (var examiner in examiner_marks)
{
workSheet.Cells[i, "B"] = examiner.Key;
workSheet.Cells[i, "C"] = examiner.Value;
i++;
}
workBook.Close(true, filepath);
excelApp.Quit();
}
}
/// <summary>
/// Method for getting information about all D-Students
/// </summary>
/// <param name="db">DBContext</param>
/// <param name="filepath">path to the file</param>
/// <param name="TypeOfOrder">how to sort</param>
/// <returns>group name and students names</returns>
public Dictionary <string, List <string> > GetAllDStudents(UniversityClassesDataContext db, string TypeOfOrder = "OrderBy")
{
Dictionary <string, List <string> > group_allocated = new Dictionary <string, List <string> >();
var Grades = from grade in db.Grade.ToList()
where grade.Mark < 4
select grade;
var students = from grd in Grades.ToList()
select grd.StudentID;
var student_allocated = from student in db.Student.ToList()
where students.Contains(student.ID)
select student;
student_allocated = student_allocated.Distinct();
var groups = from student in student_allocated.ToList()
select student.GroupID;
var Groups = from grp in db.Group.ToList()
where groups.Contains(grp.ID)
select grp;
foreach (var t in Groups)
{
var student_count = from student in db.Student.ToList()
where student.GroupID == t.ID
select student.LastName;
group_allocated.Add(t.Name, student_count.ToList());
}
SortingMethods methods = new SortingMethods();
return(methods.OrderList(group_allocated, TypeOfOrder));
}
/// <summary>
/// Method for getting information about one group session results
/// </summary>
/// <param name="id">session id</param>
/// <param name="db">db context</param>
/// <param name="group_ordered">ordered groups</param>
/// <param name="spec_marks">marks by specialies</param>
/// <param name="examiner_marks">all examiners marks</param>
public void GetSpecialitySessionResults(int id, UniversityClassesDataContext db, out Dictionary <string, float> group_ordered,
out Dictionary <string, float> spec_marks, out Dictionary <string, float> examiner_marks, string TypeOfOrder = "OrderBy")
{
var grades = from grade in db.Grade.ToList()
where grade.SessionID == id
select grade;
var students = from grd in grades.ToList()
select grd.StudentID;
var groups = from student in db.Student.ToList()
where students.Contains(student.ID)
select student.GroupID;
groups = groups.Distinct();
var groupsInNeed = from grp in db.Group.ToList()
where groups.Contains(grp.ID)
select grp;
Dictionary <string, float> group_contains = new Dictionary <string, float>();
foreach (var t in groupsInNeed)
{
var student_count = from student in db.Student
where student.GroupID == t.ID
select student.ID;
var marks = from grd in grades
where student_count.Contains(grd.StudentID.Value)
select grd.Mark;
float mark = (marks.Sum().Value / marks.Count());
group_contains.Add(t.Name, mark);
}
SortingMethods sorting = new SortingMethods();
group_ordered = sorting.OrderList(group_contains, TypeOfOrder);
var Specializations = from group_spec in groupsInNeed
select group_spec.SpecializationID;
spec_marks = new Dictionary <string, float>();
foreach (var spec in Specializations)
{
var specialization = db.Specialization.Where(special_elem => special_elem.ID == spec).SingleOrDefault();
var special_group_name = from group_elem in groupsInNeed
where (specialization as Specialization).ID == group_elem.SpecializationID
select group_elem;
float aver_mark = 0;
foreach (var group_elem in special_group_name)
{
var mark = from mark_elem in group_ordered
where mark_elem.Key == group_elem.Name
select mark_elem.Value;
aver_mark += mark.SingleOrDefault();
}
if (aver_mark != 0)
{
aver_mark /= special_group_name.Count();
}
spec_marks.Add((specialization as Specialization).Name, aver_mark);
}
examiner_marks = new Dictionary <string, float>();
foreach (var examiner in db.Examiner)
{
var examiner_exams = from exam in db.Exam
where exam.ExaminerID == examiner.ID && exam.SessionID == id
select exam;
float aver_mark = 0;
int grades_count = 0;
foreach (var exam in examiner_exams)
{
var grades_ = from grade in grades
where grade.ExamID == exam.ID
select grade.Mark.Value;
aver_mark += grades_.Sum();
grades_count += grades.Count();
}
if (aver_mark != 0)
{
aver_mark /= grades_count;
}
examiner_marks.Add(examiner.FirstName + " " + examiner.LastName, aver_mark);
}
}
/// <summary>
/// Builds information about all sessions and it's dynamics
/// </summary>
/// <param name="db">db contex</param>
/// <param name="results_ordered">ordered results</param>
/// <param name="ordered_sessions">orderes sessions</param>
/// <param name="subject_mark">all marks from subjects</param>
public void GetAllSessionsResult(UniversityClassesDataContext db, out Dictionary <Session, Dictionary <string, List <float> > > results_ordered,
out List <Session> ordered_sessions, out Dictionary <string, List <float> > subject_mark, string TypeOfOrder = "OrderBy")
{
Dictionary <Session, Dictionary <string, List <float> > > results = new Dictionary <Session, Dictionary <string, List <float> > >();
subject_mark = new Dictionary <string, List <float> >();
foreach (Session session_elem in db.Session)
{
Dictionary <string, List <float> > group_contains = new Dictionary <string, List <float> >();
var Grades = from grade in db.Grade.ToList()
where grade.SessionID == session_elem.ID
select grade;
var students = from grd in Grades.ToList()
select grd.StudentID;
var groups = from student in db.Student.ToList()
where students.Contains(student.ID)
select student.GroupID;
groups = groups.Distinct();
var Groups = from grp in db.Group.ToList()
where groups.Contains(grp.ID)
select grp;
foreach (var t in Groups)
{
var student_count = from student in db.Student.ToList()
where student.GroupID == t.ID
select student.ID;
var marks = from grd in Grades
where student_count.Contains(grd.StudentID.Value)
select grd.Mark;
float mark_Aver = (marks.Sum().Value / marks.Count());
float mark_max = marks.Max().Value;
float mark_min = marks.Min().Value;
group_contains.Add(t.Name, new List <float>()
{
mark_max, mark_Aver, mark_min
});
}
results.Add(session_elem, group_contains);
}
SortingMethods sorting = new SortingMethods();
results_ordered = sorting.OrderList(results, TypeOfOrder);
var ordered_sessions_ = from session in db.Session
orderby session.StartDate
select session;
ordered_sessions = ordered_sessions_.ToList();
foreach (Subject subject in db.Subject)
{
List <float> marks = new List <float>();
foreach (var session in ordered_sessions)
{
float mark = 0;
var exxx = from ex in db.Exam.ToList()
where ex.SessionID == session.ID && ex.SubjectID == subject.ID
select ex.ID;
var grades = from grd in db.Grade.ToList()
where exxx.Contains(grd.ExamID.Value)
select grd.Mark;
if (grades.Count() != 0)
{
mark = grades.Sum().Value / grades.Count();
}
marks.Add(mark);
}
subject_mark.Add(subject.Name, marks);
}
}
/// <summary>
/// Generates report about all session's progress in XLXS file
/// </summary>
/// <param name="filepath"></param>
/// <param name="TypeOfOrder"></param>
public void WriteAllSessionProgress(string filepath, string TypeOfOrder)
{
using (UniversityClassesDataContext db = new UniversityClassesDataContext(connectString))
{
Dictionary <string, List <float> > subject_mark;
Dictionary <Session, Dictionary <string, List <float> > > results_ordered;
List <Session> ordered_sessions;
BuildInformation build = new BuildInformation();
build.GetAllSessionsResult(db, out results_ordered,
out ordered_sessions, out subject_mark, TypeOfOrder);
Application excelApp = new Application();
Workbook workBook = excelApp.Workbooks.Add();
Worksheet workSheet = workBook.ActiveSheet;
workSheet.Cells[1, "A"] = "Session";
workSheet.Cells[1, "B"] = "Group";
workSheet.Cells[1, "C"] = "Max Mark";
workSheet.Cells[1, "D"] = "Average mark";
workSheet.Cells[1, "E"] = "Min Mark";
int i = 2;
foreach (var session_elem in results_ordered)
{
workSheet.Cells[i, "A"] = $"{session_elem.Key.StartDate} - {session_elem.Key.EndDate}";
foreach (var group in session_elem.Value)
{
workSheet.Cells[i, "B"] = group.Key;
workSheet.Cells[i, "C"] = group.Value[0];
workSheet.Cells[i, "D"] = group.Value[1];
workSheet.Cells[i, "E"] = group.Value[2];
i++;
}
}
workSheet.Cells[i, "A"] = "Subject";
int j = 2;
foreach (Session session in ordered_sessions)
{
workSheet.Cells[i, j] = $"{session.StartDate} - {session.EndDate}";
j++;
}
i += 1;
foreach (var subject in subject_mark)
{
j = 1;
workSheet.Cells[i, j] = subject.Key;
j++;
foreach (var mark in subject.Value)
{
workSheet.Cells[i, j] = mark;
j++;
}
i++;
}
workBook.Close(true, filepath);
excelApp.Quit();
}
}
