private async Task <int> ExportVaccations(UntisDocument untisDocument, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Title,
EcfHeaders.Description,
EcfHeaders.TemporalExpressions);
}
foreach (var holiday in untisDocument.Holidays)
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, holiday.Id);
ecfTableWriter.TrySetValue(EcfHeaders.Title, holiday.ShortName);
ecfTableWriter.TrySetValue(EcfHeaders.Description, holiday.LongName);
ecfTableWriter.TrySetValue(EcfHeaders.TemporalExpressions, holiday.GetEcfTemporalExpressions());
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
return(ecfRecordCounter);
}
private async Task <int> ExportSubjects(UntisDocument untisDocument, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code,
EcfHeaders.Name,
EcfHeaders.Description,
EcfHeaders.Color);
}
foreach (var subject in untisDocument.Subjects)
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, subject.Id);
ecfTableWriter.TrySetValue(EcfHeaders.Code, subject.ShortName);
ecfTableWriter.TrySetValue(EcfHeaders.Name, subject.LongName);
ecfTableWriter.TrySetValue(EcfHeaders.Description, subject.GetEcfDescription(untisDocument.Descriptions));
ecfTableWriter.TrySetValue(EcfHeaders.Color, subject.BackgroundColor);
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
return(ecfRecordCounter);
}
private async Task <int> ExportSchoolClasses(XlsReader xlsReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfCache = new HashSet <string>();
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code);
}
while (xlsReader.ReadLine())
{
var schoolClass = new ExportSchoolClass(_config, xlsReader);
if (!string.IsNullOrEmpty(schoolClass.Id) && !ecfCache.Contains(schoolClass.Id))
{
ecfTableWriter.SetValue(EcfHeaders.Id, schoolClass.Id);
ecfTableWriter.SetValue(EcfHeaders.Code, schoolClass.Code);
await ecfTableWriter.WriteAsync();
ecfCache.Add(schoolClass.Id);
ecfRecordCounter++;
}
}
return(ecfRecordCounter);
}
private async Task <int> ExportStudents(UntisDocument untisDocument, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.LastName,
EcfHeaders.FirstName,
EcfHeaders.Gender,
EcfHeaders.Birthdate);
}
foreach (var student in untisDocument.Students)
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, student.Id);
ecfTableWriter.TrySetValue(EcfHeaders.LastName, student.LastName);
ecfTableWriter.TrySetValue(EcfHeaders.FirstName, student.FirstName);
ecfTableWriter.TrySetValue(EcfHeaders.Gender, student.GetEcfGender());
ecfTableWriter.TrySetValue(EcfHeaders.Birthdate, student.GetEcfBirthdate());
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
return(ecfRecordCounter);
}
private async Task <int> ExportStudentSchoolClassAttendances(UntisDocument untisDocument, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.StudentId,
EcfHeaders.SchoolClassId);
}
foreach (var student in untisDocument.Students)
{
ecfTableWriter.TrySetValue(EcfHeaders.StudentId, student.Id);
ecfTableWriter.TrySetValue(EcfHeaders.SchoolClassId, student.ClassId);
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
return(ecfRecordCounter);
}
private async Task <int> ExportSchoolClasses(BbsPlanungDbReader bbsPlanungDbReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code,
EcfHeaders.Notes);
}
await foreach (var schoolClass in bbsPlanungDbReader.SchoolClassesAsync(_config.EcfExport.SchoolNo))
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, schoolClass.Code);
ecfTableWriter.TrySetValue(EcfHeaders.Code, schoolClass.Code);
ecfTableWriter.TrySetValue(EcfHeaders.Teacher1Id, schoolClass.Teacher);
ecfTableWriter.TrySetValue(EcfHeaders.Notes, schoolClass.Notes);
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
return(ecfRecordCounter);
}
private async Task <int> ExportStudentSchoolClassAttendances(XlsReader xlsReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.StudentId,
EcfHeaders.SchoolClassId);
}
while (xlsReader.ReadLine())
{
var student = new ExportStudent(_config, xlsReader);
var schoolClass = new ExportSchoolClass(_config, xlsReader);
if (!string.IsNullOrEmpty(schoolClass.Id))
{
ecfTableWriter.SetValue(EcfHeaders.StudentId, student.Id);
ecfTableWriter.SetValue(EcfHeaders.SchoolClassId, schoolClass.Id);
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
}
return(ecfRecordCounter);
}
private async Task <int> ExportDepartments(UntisDocument untisDocument, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code,
EcfHeaders.Name);
}
foreach (var department in untisDocument.Departments)
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, department.Id);
ecfTableWriter.TrySetValue(EcfHeaders.Code, department.ShortName);
ecfTableWriter.TrySetValue(EcfHeaders.Name, department.LongName);
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
return(ecfRecordCounter);
}
private async Task <int> ExportStudentSubjects(BbsPlanungDbReader bbsPlanungDbReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.StudentId,
EcfHeaders.SchoolClassId);
}
await foreach (var student in bbsPlanungDbReader.StudentsAsync(_config.EcfExport.SchoolNo))
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, student.Id.ToString() + "_" + student.SchoolClass);
ecfTableWriter.TrySetValue(EcfHeaders.StudentId, student.Id.ToString());
ecfTableWriter.TrySetValue(EcfHeaders.SchoolClassId, student.SchoolClass);
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
return(ecfRecordCounter);
}
private async Task <int> ExportAbsenceReasons(CsvReader csvReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code,
EcfHeaders.Name,
EcfHeaders.StatisticalCode);
}
var gpuReader = new GpuReader <GpuAbsenceReason>(csvReader);
await foreach (var reason in gpuReader.ReadAsync())
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, reason.ShortName);
ecfTableWriter.TrySetValue(EcfHeaders.Code, reason.ShortName);
ecfTableWriter.TrySetValue(EcfHeaders.Name, reason.LongName);
ecfTableWriter.TrySetValue(EcfHeaders.StatisticalCode, reason.StatisticalCode);
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
return(ecfRecordCounter);
}
private async Task <int> ExportTeachers(BbsPlanungDbReader bbsPlanungDbReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code,
EcfHeaders.LastName,
EcfHeaders.FirstName,
EcfHeaders.Gender,
EcfHeaders.Birthdate);
}
await foreach (var teacher in bbsPlanungDbReader.TeachersAsync(_config.EcfExport.SchoolNo))
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, teacher.Id);
ecfTableWriter.TrySetValue(EcfHeaders.Code, teacher.Code);
ecfTableWriter.TrySetValue(EcfHeaders.LastName, teacher.Lastname);
ecfTableWriter.TrySetValue(EcfHeaders.FirstName, teacher.Firstname);
ecfTableWriter.TrySetValue(EcfHeaders.Gender, teacher.GetGender());
ecfTableWriter.TrySetValue(EcfHeaders.Birthdate, teacher.GetBirthdate());
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
return(ecfRecordCounter);
}
private async Task <int> ExportStudentSchoolClassAttendances(CsvTableReader csvTableReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
await csvTableReader.ReadHeadersAsync();
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.StudentId,
EcfHeaders.SchoolClassId);
}
while (await csvTableReader.ReadAsync() > 0)
{
var student = new CsvExportStudent(csvTableReader);
var schoolClass = new CsvExportSchoolClass(csvTableReader);
if (!string.IsNullOrEmpty(schoolClass.Id))
{
ecfTableWriter.TrySetValue(EcfHeaders.StudentId, student.Id);
ecfTableWriter.TrySetValue(EcfHeaders.SchoolClassId, schoolClass.Id);
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
}
return(ecfRecordCounter);
}
private async Task <int> ExportTimeframes(UntisDocument untisDocument, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code,
EcfHeaders.Name,
EcfHeaders.TimeSlots);
}
foreach (var timeGrid in untisDocument.TimeGrids)
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, timeGrid.GetEcfCode());
ecfTableWriter.TrySetValue(EcfHeaders.Code, timeGrid.GetEcfCode());
ecfTableWriter.TrySetValue(EcfHeaders.Name, timeGrid.GetEcfCode());
ecfTableWriter.TrySetValue(EcfHeaders.TimeSlots, timeGrid.GetEcfTimeSlots());
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
return(ecfRecordCounter);
}
private async Task <int> ExportSchoolClasses(EdoosysDbReader edoosysDbReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
if ((_config.EcfExport?.SchoolNo != null) && (_config.EcfExport?.SchoolYearCode != null))
{
var ecfCache = new HashSet <string>();
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code);
}
await foreach (var schoolClass in edoosysDbReader.SchoolClassesAsync(_config.EcfExport.SchoolNo, _config.EcfExport.SchoolYearCode))
{
if (_config.EcfExport.NoSchoolClassGroups)
{
if (!ecfCache.Contains(schoolClass.RootId))
{
if (_ecfSchoolClassesCache.Contains(schoolClass.RootId))
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, schoolClass.RootId);
ecfTableWriter.TrySetValue(EcfHeaders.Code, schoolClass.RootCode);
ecfTableWriter.TrySetValue(EcfHeaders.Name, schoolClass.RootName);
await ecfTableWriter.WriteAsync();
ecfCache.Add(schoolClass.RootId);
ecfRecordCounter++;
}
}
}
else
{
if (_ecfSchoolClassesCache.Contains(schoolClass.Id))
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, schoolClass.Id);
ecfTableWriter.TrySetValue(EcfHeaders.Code, $"{schoolClass.RootCode}_{schoolClass.Code}");
ecfTableWriter.TrySetValue(EcfHeaders.Name, $"{schoolClass.RootCode}_{schoolClass.Code}");
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
}
}
return(ecfRecordCounter);
}
else
{
throw new Exception("No school no and/or no school year for edoo.sys database defined");
}
}
private async Task <int> ExportTeachers(CsvTableReader csvTableReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfCache = new HashSet <string>();
var ecfRecordCounter = 0;
await csvTableReader.ReadHeadersAsync();
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code);
}
while (await csvTableReader.ReadAsync() > 0)
{
if (csvTableReader.TryGetValue("Alle Lehrkräfte (Kürzel) mit Fach", out var value))
{
var csvLineParser = new CsvLineParser(',');
var subValues = csvLineParser.Read(value);
csvLineParser.Configuration.Separator = ' ';
foreach (var subValue in subValues)
{
if (!string.IsNullOrEmpty(subValue))
{
var subValueParts = csvLineParser.Read(subValue.Trim());
if (subValueParts.Length == 2)
{
var teacherCode = subValueParts[0];
if (!string.IsNullOrEmpty(teacherCode) && !ecfCache.Contains(teacherCode))
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, teacherCode);
ecfTableWriter.TrySetValue(EcfHeaders.Code, teacherCode);
await ecfTableWriter.WriteAsync();
ecfCache.Add(teacherCode);
ecfRecordCounter++;
}
}
}
}
}
}
return(ecfRecordCounter);
}
private async Task <int> ExportSubjects(FbConnection fbConnection, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
string sql = $"select * from \"Faecher\" where \"Mandant\" = @tenantId";
using var fbTransaction = fbConnection.BeginTransaction();
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
fbCommand.Parameters.Add("@tenantId", _tenantId);
using var reader = await fbCommand.ExecuteReaderAsync();
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code,
EcfHeaders.StatisticalCode,
EcfHeaders.InternalCode,
EcfHeaders.Name,
EcfHeaders.SubjectTypeId,
EcfHeaders.SubjectCategoryId,
EcfHeaders.SubjectGroupId);
}
while (await reader.ReadAsync())
{
ecfTableWriter.SetValue(EcfHeaders.Id, reader["ID"]);
ecfTableWriter.SetValue(EcfHeaders.Code, reader["Kuerzel"]);
ecfTableWriter.SetValue(EcfHeaders.StatisticalCode, reader["StatistikID"]);
ecfTableWriter.SetValue(EcfHeaders.InternalCode, reader["Schluessel"]);
ecfTableWriter.SetValue(EcfHeaders.Name, reader["Bezeichnung"]);
ecfTableWriter.SetValue(EcfHeaders.SubjectTypeId, reader["Kategorie"]);
ecfTableWriter.SetValue(EcfHeaders.SubjectCategoryId, reader["Aufgabenbereich"]);
ecfTableWriter.SetValue(EcfHeaders.SubjectGroupId, reader["Gruppe"]);
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
return(ecfRecordCounter);
}
private async Task <int> ExportStudents(CsvTableReader csvTableReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfCache = new HashSet <string>();
var ecfRecordCounter = 0;
await csvTableReader.ReadHeadersAsync();
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.LastName,
EcfHeaders.FirstName,
EcfHeaders.MiddleName,
EcfHeaders.NickName,
EcfHeaders.Salutation,
EcfHeaders.Gender,
EcfHeaders.Birthdate);
}
while (await csvTableReader.ReadAsync() > 0)
{
var student = new ExportStudent(_config, csvTableReader);
if (!ecfCache.Contains(student.Id))
{
ecfTableWriter.SetValue(EcfHeaders.Id, student.Id);
ecfTableWriter.SetValue(EcfHeaders.LastName, student.LastName);
ecfTableWriter.SetValue(EcfHeaders.FirstName, student.FirstName);
ecfTableWriter.SetValue(EcfHeaders.MiddleName, student.MiddleName);
ecfTableWriter.SetValue(EcfHeaders.NickName, student.NickName);
ecfTableWriter.SetValue(EcfHeaders.Salutation, student.Salutation);
ecfTableWriter.SetValue(EcfHeaders.Gender, student.Gender);
ecfTableWriter.SetValue(EcfHeaders.Birthdate, student.BirthDate);
await ecfTableWriter.WriteAsync();
ecfCache.Add(student.Id);
ecfRecordCounter++;
}
}
return(ecfRecordCounter);
}
private async Task <int> ExportSchoolClassTypes(EcfTableWriter ecfTableWriter)
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code,
EcfHeaders.StatisticalCode,
EcfHeaders.InternalCode,
EcfHeaders.Name);
await ecfTableWriter.WriteAsync("0", "STANDARD", "00", "00", "Standardklasse");
await ecfTableWriter.WriteAsync("1", "GANZTAGS", "01", "01", "Ganztagsklasse");
await ecfTableWriter.WriteAsync("2", "KURSE", "02", "02", "Oberstufenjahrgang (Nur Kurse)");
await ecfTableWriter.WriteAsync("3", "LK+GK", "03", "03", "Oberstufenjahrgang (LK und GK)");
await ecfTableWriter.WriteAsync("4", "ABSCHLUSS", "04", "04", "Abschlussklasse");
await ecfTableWriter.WriteAsync("5", "KOMBI", "05", "05", "Kombinationsklasse");
await ecfTableWriter.WriteAsync("6", "KINDER", "06", "06", "Schulkindergarten");
await ecfTableWriter.WriteAsync("7", "STANDARD+O", "07", "07", "Standardklasse mit Oberstufensynchronisation");
return(await Task.FromResult(8));
}
private async Task <int> ExportStudentSubjects(CsvTableReader csvTableReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
await csvTableReader.ReadHeadersAsync();
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.StudentId,
EcfHeaders.SchoolClassId,
EcfHeaders.SubjectId);
}
while (await csvTableReader.ReadAsync() > 0)
{
var student = new ExportStudent(_config, csvTableReader);
var schoolClass = new ExportSchoolClass(_config, csvTableReader);
if (!string.IsNullOrEmpty(schoolClass.Id))
{
for (int i = 1; i < 20; i++)
{
var subject = new ExportSubject(_config, csvTableReader, $"Fach{i}");
if (!string.IsNullOrEmpty(subject.Id))
{
ecfTableWriter.SetValue(EcfHeaders.StudentId, student.Id);
ecfTableWriter.SetValue(EcfHeaders.SchoolClassId, schoolClass.Id);
ecfTableWriter.SetValue(EcfHeaders.SubjectId, subject.Id);
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
}
}
}
return(ecfRecordCounter);
}
private async Task <int> ExportTeachers(EdoosysDbReader edoosysDbReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
if ((_config.EcfExport?.SchoolNo != null) && (_config.EcfExport?.SchoolYearCode != null))
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code,
EcfHeaders.LastName,
EcfHeaders.FirstName,
EcfHeaders.Gender,
EcfHeaders.Birthdate);
}
await foreach (var teacher in edoosysDbReader.TeachersAsync(_config.EcfExport.SchoolNo, _config.EcfExport.SchoolYearCode))
{
if (_ecfTeacherCache.Contains(teacher.Id))
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, teacher.Id);
ecfTableWriter.TrySetValue(EcfHeaders.Code, teacher.Code);
ecfTableWriter.TrySetValue(EcfHeaders.LastName, teacher.Lastname);
ecfTableWriter.TrySetValue(EcfHeaders.FirstName, teacher.Firstname);
ecfTableWriter.TrySetValue(EcfHeaders.Gender, Converter.GetGender(teacher.Gender));
ecfTableWriter.TrySetValue(EcfHeaders.Birthdate, Converter.GetDate(teacher.Birthdate));
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
}
return(ecfRecordCounter);
}
else
{
throw new Exception("No school no and/or no school year for edoo.sys database defined");
}
}
private async Task <int> ExportTeacherAbsences(CsvReader csvReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.TeacherId,
EcfHeaders.StartTimepoint,
EcfHeaders.EndTimepoint,
EcfHeaders.ReasonId,
EcfHeaders.Description);
}
var gpuReader = new GpuReader <GpuAbsence>(csvReader);
await foreach (var absence in gpuReader.ReadAsync())
{
if (absence.Type == GpuAbsenceType.Teacher)
{
if (absence.IsInsideTerm(_untisDocument.GeneralSettings) && absence.GetEcfTeacherId(_untisDocument.Teachers) != null)
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, absence.Id);
ecfTableWriter.TrySetValue(EcfHeaders.TeacherId, absence.GetUntisTeacherId());
ecfTableWriter.TrySetValue(EcfHeaders.StartTimepoint, absence.StartDate);
ecfTableWriter.TrySetValue(EcfHeaders.EndTimepoint, absence.EndDate);
ecfTableWriter.TrySetValue(EcfHeaders.ReasonId, absence.Reason);
ecfTableWriter.TrySetValue(EcfHeaders.Description, absence.Text);
await ecfTableWriter.WriteAsync();
_untisAbsencesCache.Add(absence.Id);
ecfRecordCounter++;
}
}
}
return(ecfRecordCounter);
}
private async Task <int> ExportCourses(UntisDocument untisDocument, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Title,
EcfHeaders.BlockNo,
EcfHeaders.Description,
EcfHeaders.SubjectId,
EcfHeaders.SchoolClassIdList,
EcfHeaders.TeacherIdList,
EcfHeaders.ValidFrom,
EcfHeaders.ValidTo);
}
foreach (var lesson in untisDocument.Lessons)
{
if (!string.IsNullOrEmpty(lesson.SubjectId))
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, lesson.Id);
ecfTableWriter.TrySetValue(EcfHeaders.Title, lesson.GetEcfCourseTitle(_untisDocument.Subjects));
ecfTableWriter.TrySetValue(EcfHeaders.BlockNo, lesson.Block);
ecfTableWriter.TrySetValue(EcfHeaders.Description, lesson.Text);
ecfTableWriter.TrySetValue(EcfHeaders.SubjectId, lesson.SubjectId);
ecfTableWriter.TrySetValue(EcfHeaders.SchoolClassIdList, lesson.ClassIds);
ecfTableWriter.TrySetValue(EcfHeaders.TeacherIdList, lesson.GetEcfTeacherIdList());
ecfTableWriter.TrySetValue(EcfHeaders.ValidFrom, lesson.ValidFrom);
ecfTableWriter.TrySetValue(EcfHeaders.ValidTo, lesson.ValidTo);
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
}
return(ecfRecordCounter);
}
private async Task <int> ExportSubstituteLessons(CsvReader csvReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.CourseId,
EcfHeaders.RoomIdList,
EcfHeaders.SchoolClassIdList,
EcfHeaders.TeacherIdList,
EcfHeaders.TemporalExpressions);
}
var gpuReader = new GpuReader <GpuSubstitution>(csvReader);
await foreach (var substitution in gpuReader.ReadAsync())
{
if (substitution.Date >= _untisDocument.GeneralSettings.TermBeginDate &&
substitution.Date <= _untisDocument.GeneralSettings.TermEndDate &&
substitution.Type != GpuSubstitutionType.Cancellation &&
substitution.Type != GpuSubstitutionType.Exemption &&
substitution.GetEcfCourseId(_untisDocument.Lessons) != null)
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, substitution.GetEcfId());
ecfTableWriter.TrySetValue(EcfHeaders.CourseId, substitution.GetEcfCourseId(_untisDocument.Lessons));
ecfTableWriter.TrySetValue(EcfHeaders.RoomIdList, substitution.GetEcfRoomIdList());
ecfTableWriter.TrySetValue(EcfHeaders.SchoolClassIdList, substitution.GetEcfSchoolClassIdList());
ecfTableWriter.TrySetValue(EcfHeaders.TeacherIdList, substitution.GetEcfTeacherIdList());
ecfTableWriter.TrySetValue(EcfHeaders.TemporalExpressions, substitution.GetEcfTemporalExpressions(_untisDocument.TimeGrids, _untisDocument.Lessons));
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
}
return(ecfRecordCounter);
}
private async Task <int> ExportStudentSubjects(EdoosysDbReader edoosysDbReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
if ((_config.EcfExport?.SchoolNo != null) && (_config.EcfExport?.SchoolYearCode != null))
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.StudentId,
EcfHeaders.SchoolClassId,
EcfHeaders.SubjectId,
EcfHeaders.TeacherId);
}
await foreach (var studentSubject in edoosysDbReader.StudentSubjectsAsync(_config.EcfExport.SchoolNo, _config.EcfExport.SchoolYearCode, activeStudentsOnly: true))
{
var schoolClassId = _config.EcfExport.NoSchoolClassGroups ? studentSubject.SchoolClassRootId : studentSubject.SchoolClassId;
ecfTableWriter.TrySetValue(EcfHeaders.Id, GenerateKey(studentSubject.StudentId, studentSubject.SubjectId, schoolClassId, studentSubject.TeacherId));
ecfTableWriter.TrySetValue(EcfHeaders.StudentId, studentSubject.StudentId);
ecfTableWriter.TrySetValue(EcfHeaders.SubjectId, studentSubject.SubjectId);
ecfTableWriter.TrySetValue(EcfHeaders.SchoolClassId, schoolClassId);
ecfTableWriter.TrySetValue(EcfHeaders.TeacherId, studentSubject.TeacherId);
await ecfTableWriter.WriteAsync();
_ecfTeacherCache.Add(studentSubject.TeacherId);
ecfRecordCounter++;
}
return(ecfRecordCounter);
}
else
{
throw new Exception("No school no and/or no school year for edoo.sys database defined");
}
}
private async Task <int> ExportScheduledLessons(UntisDocument untisDocument, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.CourseId,
EcfHeaders.SubjectId,
EcfHeaders.SchoolClassIdList,
EcfHeaders.TeacherIdList,
EcfHeaders.RoomIdList,
EcfHeaders.TemporalExpressions);
}
foreach (var lesson in untisDocument.Lessons)
{
if (!string.IsNullOrEmpty(lesson.SubjectId))
{
foreach (var lessonTime in lesson.Times.FindAll(x => x.SlotGroupFirstSlot == null))
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, lessonTime.GetEcfId(lesson));
ecfTableWriter.TrySetValue(EcfHeaders.CourseId, lesson.Id);
ecfTableWriter.TrySetValue(EcfHeaders.SubjectId, lesson.SubjectId);
ecfTableWriter.TrySetValue(EcfHeaders.SchoolClassIdList, lesson.ClassIds);
ecfTableWriter.TrySetValue(EcfHeaders.TeacherIdList, lesson.GetEcfTeacherIdList());
ecfTableWriter.TrySetValue(EcfHeaders.RoomIdList, lessonTime.RoomIds);
ecfTableWriter.TrySetValue(EcfHeaders.TemporalExpressions, lesson.GetEcfTemporalExpressions(lessonTime, untisDocument.GeneralSettings));
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
}
}
return(ecfRecordCounter);
}
private async Task <int> ExportLessonGaps(CsvReader csvReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.LessonId,
EcfHeaders.Reasons,
EcfHeaders.Resolutions,
EcfHeaders.Description,
EcfHeaders.TemporalExpressions);
}
var gpuReader = new GpuReader <GpuSubstitution>(csvReader);
await foreach (var substitution in gpuReader.ReadAsync())
{
if (substitution.Date >= _untisDocument.GeneralSettings.TermBeginDate &&
substitution.Date <= _untisDocument.GeneralSettings.TermEndDate &&
substitution.GetEcfLessonId(_untisDocument.Lessons) != null)
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, substitution.GetEcfLessonGapId());
ecfTableWriter.TrySetValue(EcfHeaders.LessonId, substitution.GetEcfLessonId(_untisDocument.Lessons));
ecfTableWriter.TrySetValue(EcfHeaders.Reasons, substitution.GetEcfReasons(_untisAbsencesCache));
ecfTableWriter.TrySetValue(EcfHeaders.Resolutions, substitution.GetEcfResolutions());
ecfTableWriter.TrySetValue(EcfHeaders.Description, substitution.Remark);
ecfTableWriter.TrySetValue(EcfHeaders.TemporalExpressions, substitution.GetEcfTemporalExpressions(_untisDocument.TimeGrids, _untisDocument.Lessons));
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
}
return(ecfRecordCounter);
}
private async Task <int> ExportEducationalPrograms(FbConnection fbConnection, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
string sql = $"select * from \"Bildungsgaenge\"";
using var fbTransaction = fbConnection.BeginTransaction();
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
using var reader = await fbCommand.ExecuteReaderAsync();
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code,
EcfHeaders.StatisticalCode,
EcfHeaders.InternalCode,
EcfHeaders.Name);
}
while (await reader.ReadAsync())
{
ecfTableWriter.SetValue(EcfHeaders.Id, reader["Kuerzel"]);
ecfTableWriter.SetValue(EcfHeaders.Code, reader["Kuerzel"]);
ecfTableWriter.SetValue(EcfHeaders.StatisticalCode, reader["StatistikID"]);
ecfTableWriter.SetValue(EcfHeaders.InternalCode, reader["Schluessel"]);
ecfTableWriter.SetValue(EcfHeaders.Name, reader["Bezeichnung"]);
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
return(ecfRecordCounter);
}
private async Task <int> ExportSubjects(CsvTableReader csvTableReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfCache = new HashSet <string>();
var ecfRecordCounter = 0;
await csvTableReader.ReadHeadersAsync();
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code);
}
while (await csvTableReader.ReadAsync() > 0)
{
for (int i = 1; i < 20; i++)
{
var subject = new ExportSubject(_config, csvTableReader, $"Fach{i}");
if (!string.IsNullOrEmpty(subject.Id) && !ecfCache.Contains(subject.Id))
{
ecfTableWriter.SetValue(EcfHeaders.Id, subject.Id);
ecfTableWriter.SetValue(EcfHeaders.Code, subject.Code);
await ecfTableWriter.WriteAsync();
ecfCache.Add(subject.Id);
ecfRecordCounter++;
}
}
}
return(ecfRecordCounter);
}
private async Task <int> ExportSchoolClasses(UntisDocument untisDocument, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code,
EcfHeaders.Name1,
EcfHeaders.Description,
EcfHeaders.DepartmentId,
EcfHeaders.Color,
EcfHeaders.ValidFrom,
EcfHeaders.ValidTo);
}
foreach (var schoolClass in untisDocument.Classes)
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, schoolClass.Id);
ecfTableWriter.TrySetValue(EcfHeaders.Code, schoolClass.ShortName);
ecfTableWriter.TrySetValue(EcfHeaders.Name1, schoolClass.LongName);
ecfTableWriter.TrySetValue(EcfHeaders.Description, schoolClass.GetEcfDescription(untisDocument.Descriptions));
ecfTableWriter.TrySetValue(EcfHeaders.DepartmentId, schoolClass.DepartmentId);
ecfTableWriter.TrySetValue(EcfHeaders.Color, schoolClass.BackgroundColor);
ecfTableWriter.TrySetValue(EcfHeaders.ValidFrom, schoolClass.ValidFrom);
ecfTableWriter.TrySetValue(EcfHeaders.ValidTo, schoolClass.ValidTo);
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
return(ecfRecordCounter);
}
private async Task <int> ExportDepartments(FbConnection fbConnection, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
string sql = $"select * from \"Abteilungen\" where \"Mandant\" = @tenantId";
using var fbTransaction = fbConnection.BeginTransaction();
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
fbCommand.Parameters.Add("@tenantId", _tenantId);
using var reader = await fbCommand.ExecuteReaderAsync();
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code,
EcfHeaders.Name);
}
while (await reader.ReadAsync())
{
ecfTableWriter.SetValue(EcfHeaders.Id, reader["Kuerzel"]);
ecfTableWriter.SetValue(EcfHeaders.Code, reader["Kuerzel"]);
ecfTableWriter.SetValue(EcfHeaders.Name, reader["Bezeichnung"]);
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
return(ecfRecordCounter);
}