/// <summary>
/// Return the GameType currently set in DisciplineMeta for the selected class name and year or nothing, if no GameType
/// is set.
/// </summary>
/// <param name="className">The class name of the class the GameType is to be returned.</param>
/// <param name="year">The year for which the GameType is valid.</param>
/// <returns>
/// A member of the Enum GameType that represents the GameType set in DisciplineMeta for the corresponding class
/// in the given year.
/// </returns>
/// <remarks></remarks>
public static Game?GetGameType(string className, short year)
{
Game?result = null;
using (HonglornDb db = new HonglornDb())
{
Discipline[] disciplines = (from c in db.DisciplineCollection
where c.ClassName == className &&
c.Year == year
select new[] {
c.MaleSprint,
c.MaleJump,
c.MaleThrow,
c.MaleMiddleDistance,
c.FemaleSprint,
c.FemaleJump,
c.FemaleThrow,
c.FemaleMiddleDistance
}).SingleOrDefault();
if (disciplines.All(d => d is CompetitionDiscipline))
{
result = Game.Competition;
}
else if (disciplines.All(d => d is TraditionalDiscipline))
{
result = Game.Traditional;
}
}
return(result);
}
public static void CreateOrUpdateCompetitionDiscipline(Guid disciplinePKey, DisciplineType type, string name, string unit, bool lowIsBetter)
{
using (HonglornDb db = new HonglornDb())
{
CompetitionDiscipline competition = db.CompetitionDiscipline.Find(disciplinePKey);
if (competition == null)
{
// Create
db.CompetitionDiscipline.Add(new CompetitionDiscipline
{
Type = type,
Name = name,
Unit = unit,
LowIsBetter = lowIsBetter
});
}
else
{
// Update
competition.Type = type;
competition.Name = name;
competition.Unit = unit;
competition.LowIsBetter = lowIsBetter;
}
db.SaveChanges();
}
}
public static DisciplineCollection ConfiguredDisciplines(string className, short year)
{
using (HonglornDb db = new HonglornDb())
{
DisciplineCollection collection = (from c in db.DisciplineCollection
where c.ClassName == className &&
c.Year == year
select c).SingleOrDefault();
if (collection != null)
{
// Pre-load properties; otherwise they won't be available after the context is disposed.
IEnumerable <Expression <Func <DisciplineCollection, Discipline> > > references = new Expression <Func <DisciplineCollection, Discipline> >[]
{
c => c.MaleSprint,
c => c.MaleJump,
c => c.MaleThrow,
c => c.MaleMiddleDistance,
c => c.FemaleSprint,
c => c.FemaleJump,
c => c.FemaleThrow,
c => c.FemaleMiddleDistance
};
foreach (Expression <Func <DisciplineCollection, Discipline> > reference in references)
{
db.Entry(collection).Reference(reference).Load();
}
}
return(collection);
}
}
public static ICollection <CompetitionDiscipline> AllCompetitionDisciplines()
{
using (HonglornDb db = new HonglornDb())
{
return(db.CompetitionDiscipline.ToArray());
}
}
static Certificate DetermineTraditionalCertificate(Sex sex, short yearOfBirth, ushort totalScore)
{
Certificate result;
int studentAge = DateTime.Now.Year - yearOfBirth;
using (HonglornDb db = new HonglornDb())
{
var scoreBoundaries = (from meta in db.TraditionalReportMeta
where meta.Sex == sex &&
meta.Age == studentAge
select new { meta.HonoraryCertificateScore, meta.VictoryCertificateScore }).Single();
if (totalScore >= scoreBoundaries.HonoraryCertificateScore)
{
result = Certificate.Honorary;
}
else if (totalScore >= scoreBoundaries.VictoryCertificateScore)
{
result = Certificate.Victory;
}
else
{
result = Certificate.Participation;
}
}
return(result);
}
/// <summary>
/// Get the years for which student data is present in the database.
/// </summary>
/// <returns>A short collection representing the valid years.</returns>
public static ICollection <short> YearsWithStudentData()
{
using (HonglornDb db = new HonglornDb())
{
return((from relations in db.StudentCourseRel
select relations.Year).Distinct().OrderByDescending(year => year).ToArray());
}
}
/// <summary>
/// Get a the course names for which there is at least one student present in the given year.
/// </summary>
/// <param name="year">The year for which the valid course names should be retrieved.</param>
/// <returns>All valid course names.</returns>
public static ICollection <string> ValidCourseNames(short year)
{
using (HonglornDb db = new HonglornDb())
{
return((from r in db.StudentCourseRel
where r.Year == year
select r.CourseName).Distinct().OrderBy(name => name).ToArray());
}
}
public static ICollection <TraditionalDiscipline> FilteredTraditionalDisciplines(DisciplineType disciplineType, Sex sex)
{
using (HonglornDb db = new HonglornDb())
{
return((from d in db.TraditionalDiscipline
where d.Type == disciplineType && d.Sex == sex
select d).OrderBy(d => d.Name).ToArray());
}
}
public static ICollection <CompetitionDiscipline> FilteredCompetitionDisciplines(DisciplineType disciplineType)
{
using (HonglornDb db = new HonglornDb())
{
return((from d in db.CompetitionDiscipline
where d.Type == disciplineType
select d).OrderBy(d => d.Name).ToArray());
}
}
public static ICollection <Student> GetStudents(string course, short year)
{
using (HonglornDb db = new HonglornDb())
{
return((from s in db.Student
where s.StudentCourseRel.Any(rel => rel.Year == year && rel.CourseName == course)
orderby s.Surname, s.Forename, s.YearOfBirth descending
select s).Include(s => s.Competition).ToArray());
}
}
/// <summary>
/// Imports data of a single student into the database.
/// </summary>
/// <remarks></remarks>
static void ImportSingleStudent(Student student, string courseName, short year)
{
//todo: handle exception
GetClassName(courseName); //check whether the course name can be mapped to a class name
//todo: verify year
using (HonglornDb db = new HonglornDb())
{
IQueryable <Student> studentQuery = from s in db.Student
where s.Forename == student.Forename &&
s.Surname == student.Surname &&
s.Sex == student.Sex &&
s.YearOfBirth == student.YearOfBirth
select s;
Student existingStudent = studentQuery.SingleOrDefault();
if (existingStudent == null)
{
Student newStudent = new Student
{
Forename = student.Forename,
Surname = student.Surname,
Sex = student.Sex,
YearOfBirth = student.YearOfBirth
};
newStudent.AddStudentCourseRel(year, courseName);
db.Student.Add(newStudent);
}
else
{
IEnumerable <StudentCourseRel> courseInformationQuery = from r in existingStudent.StudentCourseRel
where r.Year == year
select r;
StudentCourseRel existingCourseInformation = courseInformationQuery.SingleOrDefault();
if (existingCourseInformation == null)
{
existingStudent.AddStudentCourseRel(year, courseName);
}
else
{
existingCourseInformation.CourseName = courseName;
}
}
db.SaveChanges();
}
}
public static void UpdateSingleStudentCompetition(Guid studentPKey, short year, float?sprint, float?jump, float? @throw, float?middleDistance)
{
using (HonglornDb db = new HonglornDb())
{
Student student = db.Student.Find(studentPKey);
if (student != null)
{
Competition existingCompetition = (from c in student.Competition
where c.Year == year
select c).SingleOrDefault();
if ((sprint ?? jump ?? @throw ?? middleDistance) == null)
{
// Delete
if (existingCompetition != null)
{
db.Competition.Remove(existingCompetition);
}
}
else
{
if (existingCompetition == null)
{
// Create
student.Competition.Add(new Competition
{
Year = year,
Sprint = sprint,
Jump = jump,
Throw = @throw,
MiddleDistance = middleDistance
});
}
else
{
// Update
existingCompetition.Sprint = sprint;
existingCompetition.Jump = jump;
existingCompetition.Throw = @throw;
existingCompetition.MiddleDistance = middleDistance;
}
}
db.SaveChanges();
}
else
{
throw new ArgumentException($"No {nameof(Student)} with such key in database: {studentPKey}");
}
}
}
public static ICollection <Result> GetResults(string course, short year)
{
ICollection <Result> results;
ICollection <Student> students = GetStudents(course, year);
string className = GetClassName(course);
using (HonglornDb db = new HonglornDb())
{
DisciplineCollection disciplines = (from d in db.DisciplineCollection
where d.ClassName == className &&
d.Year == year
select d).SingleOrDefault();
if (disciplines == null)
{
throw new DataException($"No disciplines have been configured for class {className} in year {year}. Therefore, no results can be calculated.");
}
Discipline[] disciplineArray = new[] { disciplines.MaleSprint, disciplines.MaleJump, disciplines.MaleThrow, disciplines.MaleMiddleDistance, disciplines.FemaleSprint, disciplines.FemaleJump, disciplines.FemaleThrow, disciplines.FemaleMiddleDistance };
if (disciplineArray.All(d => d is TraditionalDiscipline))
{
TraditionalDisciplineCollection disciplineCollection = new TraditionalDisciplineCollection()
{
MaleSprint = disciplines.MaleSprint as TraditionalDiscipline,
MaleJump = disciplines.MaleJump as TraditionalDiscipline,
MaleThrow = disciplines.MaleThrow as TraditionalDiscipline,
MaleMiddleDistance = disciplines.MaleMiddleDistance as TraditionalDiscipline,
FemaleSprint = disciplines.FemaleSprint as TraditionalDiscipline,
FemaleJump = disciplines.FemaleJump as TraditionalDiscipline,
FemaleThrow = disciplines.FemaleThrow as TraditionalDiscipline,
FemaleMiddleDistance = disciplines.FemaleMiddleDistance as TraditionalDiscipline
};
results = CalculateTraditionalResults(students, year, disciplineCollection);
}
else if (disciplineArray.All(d => d is CompetitionDiscipline))
{
throw new NotImplementedException();
}
else
{
throw new DataException($"For class {className} in year {year}, some configured disciplines are traditional disciplines, while other disciplines are competition disciplines. A result can only be calculated when all disciplines are of the same type.");
}
}
return(results);
}
public static DataTable GetStudentCompetitionTable(string courseName, short year)
{
// Prepare table
DataTable table = new DataTable();
DataColumn pKeyColumn = table.Columns.Add(nameof(Student.PKey), typeof(Guid));
DataColumn surnameColumn = table.Columns.Add(nameof(Student.Surname), typeof(string));
DataColumn forenameColumn = table.Columns.Add(nameof(Student.Forename), typeof(string));
DataColumn sexColumn = table.Columns.Add(nameof(Student.Sex), typeof(Sex));
DataColumn sprintColumn = table.Columns.Add(nameof(Competition.Sprint), typeof(float));
DataColumn jumpColumn = table.Columns.Add(nameof(Competition.Jump), typeof(float));
DataColumn throwColumn = table.Columns.Add(nameof(Competition.Throw), typeof(float));
DataColumn middleDistanceColumn = table.Columns.Add(nameof(Competition.MiddleDistance), typeof(float));
using (HonglornDb db = new HonglornDb())
{
IEnumerable <Student> studentList = (from s in db.Student
where s.StudentCourseRel.Any(rel => rel.Year == year && rel.CourseName == courseName)
orderby s.Surname, s.Forename, s.YearOfBirth descending
select s).ToList();
foreach (Student student in studentList)
{
Competition competition = (from c in student.Competition
where c.Year == year
select c).SingleOrDefault();
DataRow newRow = table.NewRow();
newRow.SetField(pKeyColumn, student.PKey);
newRow.SetField(surnameColumn, student.Surname);
newRow.SetField(forenameColumn, student.Forename);
newRow.SetField(sexColumn, student.Sex);
newRow.SetField(sprintColumn, competition?.Sprint);
newRow.SetField(jumpColumn, competition?.Jump);
newRow.SetField(throwColumn, competition?.Throw);
newRow.SetField(middleDistanceColumn, competition?.MiddleDistance);
table.Rows.Add(newRow);
}
}
return(table);
}
public static void DeleteCompetitionDisciplineByPKey(Guid pKey)
{
try
{
using (HonglornDb db = new HonglornDb())
{
CompetitionDiscipline discipline = new CompetitionDiscipline
{
PKey = pKey
};
db.Entry(discipline).State = EntityState.Deleted;
db.SaveChanges();
}
}
catch (Exception ex)
{
throw new ArgumentException($"A {nameof(CompetitionDiscipline)} with PKey {pKey} does not exist in the database.", ex);
}
}
