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> 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> 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> 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> 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> 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);
}
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> ExportTeachers(FbConnection fbConnection, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
string sql = $"select * from \"Lehrer\" where \"Mandant\" = @tenantId and \"Status\" = 1";
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.LastName,
EcfHeaders.FirstName,
EcfHeaders.MiddleName,
EcfHeaders.Salutation,
EcfHeaders.Gender,
EcfHeaders.Birthdate,
EcfHeaders.Birthname,
EcfHeaders.PlaceOfBirth,
EcfHeaders.MaritalStatusId,
EcfHeaders.AddressLines,
EcfHeaders.PostalCode,
EcfHeaders.Locality,
//EcfHeaders.CountryId,
EcfHeaders.HomePhoneNumber,
EcfHeaders.OfficePhoneNumber,
EcfHeaders.EmailAddress,
EcfHeaders.MobileNumber,
//EcfHeaders.Nationality1Id,
//EcfHeaders.Nationality2Id,
EcfHeaders.NativeLanguageId,
EcfHeaders.CorrespondenceLanguageId);
//EcfHeaders.ReligionId);
}
while (await reader.ReadAsync())
{
ecfTableWriter.SetValue(EcfHeaders.Id, reader["ID"]);
ecfTableWriter.SetValue(EcfHeaders.Code, reader["Kuerzel"]);
ecfTableWriter.SetValue(EcfHeaders.LastName, reader["Nachname"]);
ecfTableWriter.SetValue(EcfHeaders.FirstName, reader["Vorname"]);
ecfTableWriter.SetValue(EcfHeaders.MiddleName, reader["Vorname2"]);
ecfTableWriter.SetValue(EcfHeaders.Salutation, reader.GetSalutation("Anrede"));
ecfTableWriter.SetValue(EcfHeaders.Gender, reader.GetGender("Geschlecht"));
ecfTableWriter.SetValue(EcfHeaders.Birthdate, reader.GetDate("Geburtsdatum"));
ecfTableWriter.SetValue(EcfHeaders.Birthname, reader["Geburtsname"]);
ecfTableWriter.SetValue(EcfHeaders.PlaceOfBirth, reader["Geburtsort"]);
ecfTableWriter.SetValue(EcfHeaders.MaritalStatusId, reader["Ehestand"]);
ecfTableWriter.SetValue(EcfHeaders.AddressLines, reader["Strasse"]);
ecfTableWriter.SetValue(EcfHeaders.PostalCode, reader["PLZ"]);
ecfTableWriter.SetValue(EcfHeaders.Locality, reader["Ort"]);
//ecfTableWriter.SetValue(EcfHeaders.CountryId, reader["Land"]);
ecfTableWriter.SetValue(EcfHeaders.HomePhoneNumber, reader["Telefon"]);
ecfTableWriter.SetValue(EcfHeaders.OfficePhoneNumber, reader["TelefonDienst"]);
ecfTableWriter.SetValue(EcfHeaders.EmailAddress, reader["Email"]);
ecfTableWriter.SetValue(EcfHeaders.MobileNumber, reader["Mobil"]);
//ecfTableWriter.SetValue(EcfHeaders.Nationality1Id, reader["Staatsangeh"]);
//ecfTableWriter.SetValue(EcfHeaders.Nationality2Id, reader["Staatsangeh2"]);
ecfTableWriter.SetValue(EcfHeaders.NativeLanguageId, reader["Muttersprache"]);
ecfTableWriter.SetValue(EcfHeaders.CorrespondenceLanguageId, reader["Verkehrssprache"]);
//ecfTableWriter.SetValue(EcfHeaders.ReligionId, reader["Konfession"]);
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
return(ecfRecordCounter);
}
private async Task <int> ExportStudentSubjects(FbConnection fbConnection, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
string sql;
if (_version >= 7)
{
sql =
$"select F.\"ID\", F.\"Klasse\", S.\"ID\" as \"Schueler\", F.\"KursNr\", F.\"Unterrichtsart\", F.\"Fachstatus\", F.\"Fach\", " +
$"F.\"Niveau\", F.\"Schwerpunkt\", K.\"Zugang\", K.\"Abgang\", L.\"ID\" as \"Lehrer\" from \"SchuelerFachdaten\" as F " +
$"join \"SchuelerZeitraeume\" as Z " +
$"on Z.\"ID\" = F.\"SchuelerZeitraumID\" " +
$"join \"SchuelerKlassen\" as K " +
$"on Z.\"ID\" = K.\"SchuelerZeitraumID\" " +
$"join \"Schueler\" as S " +
$"on Z.\"Schueler\" = S.\"ID\" " +
$"left join \"Lehrer\" as L " +
$"on F.\"Lehrer\" = L.\"ID\" " +
$"where Z.\"Mandant\" = @tenantId and Z.\"Zeitraum\" = @schoolTermId and S.\"Status\" in (2, 3) and (S.\"IDIntern\" is NULL) " +
$"union all " +
$"select F.\"ID\", F.\"Klasse\", S.\"IDIntern\" as \"Schueler\", F.\"KursNr\", F.\"Unterrichtsart\", F.\"Fachstatus\", F.\"Fach\", " +
$"F.\"Niveau\", F.\"Schwerpunkt\", K.\"Zugang\", K.\"Abgang\", L.\"ID\" as \"Lehrer\" from \"SchuelerFachdaten\" as F " +
$"join \"SchuelerZeitraeume\" as Z " +
$"on Z.\"ID\" = F.\"SchuelerZeitraumID\" " +
$"join \"SchuelerKlassen\" as K " +
$"on Z.\"ID\" = K.\"SchuelerZeitraumID\" " +
$"join \"Schueler\" as S " +
$"on Z.\"Schueler\" = S.\"IDIntern\" " +
$"join \"Schueler\" as I " +
$"on I.\"ID\" = S.\"IDIntern\" " +
$"left join \"Lehrer\" as L " +
$"on F.\"Lehrer\" = L.\"ID\" and L.\"Status\" = 1 " +
$"where Z.\"Mandant\" = @tenantId and Z.\"Zeitraum\" = @schoolTermId and I.\"Status\" in (2, 3) and not (S.\"IDIntern\" is NULL)";
}
else
{
sql =
$"select F.\"ID\", F.\"Klasse\", F.\"Schueler\", F.\"KursNr\", F.\"Unterrichtsart\", F.\"Fachstatus\", F.\"Fach\", " +
$"F.\"Niveau\", F.\"Schwerpunkt\", K.\"Zugang\", K.\"Abgang\", L.\"ID\" as \"Lehrer\" from \"SchuelerFachdaten\" as F " +
$"join \"SchuelerZeitraeume\" as Z " +
$"on Z.\"Mandant\" = F.\"Mandant\" and Z.\"Klasse\" = F.\"Klasse\" and Z.\"Schueler\" = F.\"Schueler\" and Z.\"Zeitraum\" = F.\"Zeitraum\" " +
$"join \"SchuelerKlassen\" as K " +
$"on Z.\"Mandant\" = K.\"Mandant\" and Z.\"Klasse\" = K.\"Klasse\" and Z.\"Schueler\" = K.\"Schueler\" " +
$"join \"Schueler\" as S " +
$"on Z.\"Mandant\" = S.\"Mandant\" and Z.\"Schueler\" = S.\"ID\" " +
$"left join \"Lehrer\" as L " +
$"on F.\"Mandant\" = L.\"Mandant\" and F.\"Lehrer\" = L.\"ID\" and L.\"Status\" = 1 " +
$"where Z.\"Mandant\" = @tenantId and Z.\"Zeitraum\" = @schoolTermId and S.\"Status\" in (2, 3)";
}
using var fbTransaction = fbConnection.BeginTransaction();
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
fbCommand.Parameters.Add("@tenantId", _tenantId);
fbCommand.Parameters.Add("@schoolTermId", _schoolTermId);
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.SchoolClassId,
EcfHeaders.StudentId,
EcfHeaders.CourseNo,
EcfHeaders.CourseTypeId,
EcfHeaders.CourseCategoryId,
EcfHeaders.SubjectId,
EcfHeaders.SubjectLevelId,
EcfHeaders.SubjectFocusId,
EcfHeaders.TeacherId,
EcfHeaders.EntryDate,
EcfHeaders.ExitDate);
}
while (await reader.ReadAsync())
{
ecfTableWriter.SetValue(EcfHeaders.Id, reader["ID"]);
ecfTableWriter.SetValue(EcfHeaders.SchoolClassId, reader["Klasse"]);
ecfTableWriter.SetValue(EcfHeaders.StudentId, reader["Schueler"]);
ecfTableWriter.SetValue(EcfHeaders.CourseNo, reader.GetShortOrDefault("KursNr", 0));
ecfTableWriter.SetValue(EcfHeaders.CourseTypeId, reader["Unterrichtsart"]);
ecfTableWriter.SetValue(EcfHeaders.CourseCategoryId, reader["Fachstatus"]);
ecfTableWriter.SetValue(EcfHeaders.SubjectId, reader["Fach"]);
ecfTableWriter.SetValue(EcfHeaders.SubjectLevelId, reader["Niveau"]);
ecfTableWriter.SetValue(EcfHeaders.SubjectFocusId, reader["Schwerpunkt"]);
ecfTableWriter.SetValue(EcfHeaders.TeacherId, reader["Lehrer"]);
ecfTableWriter.SetValue(EcfHeaders.EntryDate, reader.GetDate("Zugang"));
ecfTableWriter.SetValue(EcfHeaders.ExitDate, reader.GetDate("Abgang"));
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
return(ecfRecordCounter);
}
private async Task <int> ExportStudentSchoolClassAttendances(FbConnection fbConnection, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
string sql;
if (_version >= 7)
{
sql =
$"select Z.*, K.\"Zugang\", K.\"Abgang\" " +
$"from \"SchuelerZeitraeume\" as Z " +
$"join \"SchuelerKlassen\" as K " +
$"on Z.\"ID\" = K.\"SchuelerZeitraumID\" " +
$"join \"Schueler\" as S " +
$"on Z.\"Schueler\" = S.\"ID\" " +
$"where Z.\"Mandant\" = @tenantId and Z.\"Zeitraum\" = @schoolTermId and S.\"Status\" in (2, 3) and (S.\"IDIntern\" is NULL) " +
$"union all " +
$"select Z.*, K.\"Zugang\", K.\"Abgang\" " +
$"from \"SchuelerZeitraeume\" as Z " +
$"join \"SchuelerKlassen\" as K " +
$"on Z.\"ID\" = K.\"SchuelerZeitraumID\" " +
$"join \"Schueler\" as S " +
$"on Z.\"Schueler\" = S.\"IDIntern\" " +
$"join \"Schueler\" as I " +
$"on I.\"ID\" = S.\"IDIntern\" " +
$"where Z.\"Mandant\" = @tenantId and Z.\"Zeitraum\" = @schoolTermId and I.\"Status\" in (2, 3) and not (S.\"IDIntern\" is NULL)";
}
else
{
sql =
$"select Z.*, K.\"Zugang\", K.\"Abgang\" from \"SchuelerZeitraeume\" as Z " +
$"join \"SchuelerKlassen\" as K " +
$"on Z.\"Mandant\" = K.\"Mandant\" and Z.\"Klasse\" = K.\"Klasse\" and Z.\"Schueler\" = K.\"Schueler\" " +
$"join \"Schueler\" as S " +
$"on Z.\"Mandant\" = S.\"Mandant\" and Z.\"Schueler\" = S.\"ID\" " +
$"where Z.\"Mandant\" = @tenantId and Z.\"Zeitraum\" = @schoolTermId and S.\"Status\" in (2, 3)";
}
using var fbTransaction = fbConnection.BeginTransaction();
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
fbCommand.Parameters.Add("@tenantId", _tenantId);
fbCommand.Parameters.Add("@schoolTermId", _schoolTermId);
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.SchoolClassId,
EcfHeaders.StudentId,
EcfHeaders.EntryDate,
EcfHeaders.ExitDate);
}
while (await reader.ReadAsync())
{
ecfTableWriter.SetValue(EcfHeaders.Id, reader["ID"]);
ecfTableWriter.SetValue(EcfHeaders.SchoolClassId, reader["Klasse"]);
ecfTableWriter.SetValue(EcfHeaders.StudentId, reader["Schueler"]);
ecfTableWriter.SetValue(EcfHeaders.EntryDate, reader.GetDate("Zugang"));
ecfTableWriter.SetValue(EcfHeaders.ExitDate, reader.GetDate("Abgang"));
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
return(ecfRecordCounter);
}
private async Task <int> ExportStudents(FbConnection fbConnection, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
string sql;
if (_version >= 7)
{
sql =
$"select S.* from \"Schueler\" S " +
$"join \"SchuelerZeitraeume\" SZ " +
$"on S.\"ID\" = SZ.\"Schueler\" and S.\"Mandant\" = SZ.\"Mandant\" " +
$"where S.\"Mandant\" = @tenantId and SZ.\"Zeitraum\" = @schoolTermId and S.\"Status\" in (2, 3) and (S.\"IDIntern\" is NULL)";
}
else
{
sql =
$"select S.* from \"Schueler\" S " +
$"join \"SchuelerZeitraeume\" SZ " +
$"on S.\"ID\" = SZ.\"Schueler\" and S.\"Mandant\" = SZ.\"Mandant\" " +
$"where S.\"Mandant\" = @tenantId and SZ.\"Zeitraum\" = @schoolTermId and S.\"Status\" in (2, 3)";
}
using var fbTransaction = fbConnection.BeginTransaction();
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
fbCommand.Parameters.Add("@tenantId", _tenantId);
fbCommand.Parameters.Add("@schoolTermId", _schoolTermId);
using var reader = await fbCommand.ExecuteReaderAsync();
var ecfCache = new HashSet <int>();
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.LastName,
EcfHeaders.FirstName,
EcfHeaders.MiddleName,
EcfHeaders.Birthname,
EcfHeaders.Salutation,
EcfHeaders.Gender,
EcfHeaders.Birthdate,
EcfHeaders.Birthname,
EcfHeaders.PlaceOfBirth,
//EcfHeaders.CountryOfBirthId,
EcfHeaders.AddressLines,
EcfHeaders.PostalCode,
EcfHeaders.Locality,
//EcfHeaders.CountryId,
EcfHeaders.HomePhoneNumber,
EcfHeaders.EmailAddress,
EcfHeaders.MobileNumber,
//EcfHeaders.Nationality1Id,
//EcfHeaders.Nationality2Id,
EcfHeaders.NativeLanguageId,
EcfHeaders.CorrespondenceLanguageId);
//EcfHeaders.ReligionId);
}
while (await reader.ReadAsync())
{
var studentId = (int)reader["ID"];
if (!ecfCache.Contains(studentId))
{
ecfTableWriter.SetValue(EcfHeaders.Id, studentId);
ecfTableWriter.SetValue(EcfHeaders.LastName, reader["Nachname"]);
ecfTableWriter.SetValue(EcfHeaders.FirstName, reader["Vorname"]);
ecfTableWriter.SetValue(EcfHeaders.MiddleName, reader["Vorname2"]);
ecfTableWriter.SetValue(EcfHeaders.Birthname, reader["Geburtsname"]);
ecfTableWriter.SetValue(EcfHeaders.Salutation, reader.GetSalutation("Anrede"));
ecfTableWriter.SetValue(EcfHeaders.Gender, reader.GetGender("Geschlecht"));
ecfTableWriter.SetValue(EcfHeaders.Birthdate, reader.GetDate("Geburtsdatum"));
ecfTableWriter.SetValue(EcfHeaders.Birthname, reader["Geburtsname"]);
ecfTableWriter.SetValue(EcfHeaders.PlaceOfBirth, reader["Geburtsort"]);
//ecfTableWriter.SetValue(EcfHeaders.CountryOfBirthId, reader["Geburtsland"]);
ecfTableWriter.SetValue(EcfHeaders.AddressLines, reader["Strasse"]);
ecfTableWriter.SetValue(EcfHeaders.PostalCode, reader["PLZ"]);
ecfTableWriter.SetValue(EcfHeaders.Locality, reader["Ort"]);
//ecfTableWriter.SetValue(EcfHeaders.CountryId, reader["Land"]);
ecfTableWriter.SetValue(EcfHeaders.HomePhoneNumber, reader["Telefon"]);
ecfTableWriter.SetValue(EcfHeaders.EmailAddress, reader["Email"]);
ecfTableWriter.SetValue(EcfHeaders.MobileNumber, reader["Mobil"]);
//ecfTableWriter.SetValue(EcfHeaders.Nationality1Id, reader["Staatsangeh1"]);
//ecfTableWriter.SetValue(EcfHeaders.Nationality2Id, reader["Staatsangeh2"]);
ecfTableWriter.SetValue(EcfHeaders.NativeLanguageId, reader["Muttersprache"]);
ecfTableWriter.SetValue(EcfHeaders.CorrespondenceLanguageId, reader["Verkehrssprache"]);
//ecfTableWriter.SetValue(EcfHeaders.ReligionId, reader["Konfession"]);
await ecfTableWriter.WriteAsync();
ecfCache.Add(studentId);
ecfRecordCounter++;
}
}
return(ecfRecordCounter);
}
private async Task <int> ExportSchoolClasses(FbConnection fbConnection, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
string sql =
$"select K.*, L1.\"ID\" as \"Lehrer1\", L2.\"ID\" as \"Lehrer2\" from \"KlassenAnsicht\" as K " +
$"left join \"Lehrer\" as L1 " +
$"on K.\"Mandant\" = L1.\"Mandant\" and K.\"Klassenleiter1\" = L1.\"ID\" and L1.\"Status\" = 1 " +
$"left join \"Lehrer\" as L2 " +
$"on K.\"Mandant\" = L2.\"Mandant\" and K.\"Klassenleiter2\" = L2.\"ID\" and L2.\"Status\" = 1 " +
$"where K.\"Mandant\" = @tenantId and K.\"Zeitraum\" = @schoolTermId ";
using var fbTransaction = fbConnection.BeginTransaction();
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
fbCommand.Parameters.Add("@tenantId", _tenantId);
fbCommand.Parameters.Add("@schoolTermId", _schoolTermId);
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.Name1,
EcfHeaders.Name2,
EcfHeaders.SchoolClassTypeId,
EcfHeaders.SchoolClassLevelId,
EcfHeaders.DepartmentId,
EcfHeaders.SchoolTypeId,
EcfHeaders.SchoolCategoryId,
EcfHeaders.SchoolOrganisationId,
EcfHeaders.Teacher1Id,
EcfHeaders.Teacher2Id,
EcfHeaders.FormOfTeachingId);
}
while (await reader.ReadAsync())
{
ecfTableWriter.SetValue(EcfHeaders.Id, reader["Id"]);
ecfTableWriter.SetValue(EcfHeaders.Code, reader["Kuerzel"]);
ecfTableWriter.SetValue(EcfHeaders.StatisticalCode, reader["KuerzelStatistik"]);
ecfTableWriter.SetValue(EcfHeaders.Name1, reader["Langname1"]);
ecfTableWriter.SetValue(EcfHeaders.Name2, reader["Langname2"]);
ecfTableWriter.SetValue(EcfHeaders.SchoolClassTypeId, reader["Klassenart"]);
ecfTableWriter.SetValue(EcfHeaders.SchoolClassLevelId, reader["Klassenstufe"]);
ecfTableWriter.SetValue(EcfHeaders.DepartmentId, reader["Abteilung"]);
ecfTableWriter.SetValue(EcfHeaders.SchoolTypeId, reader["Schulart"]);
ecfTableWriter.SetValue(EcfHeaders.SchoolCategoryId, reader["Schulform"]);
ecfTableWriter.SetValue(EcfHeaders.SchoolOrganisationId, reader["Organisation"]);
ecfTableWriter.SetValue(EcfHeaders.Teacher1Id, reader["Lehrer1"]);
ecfTableWriter.SetValue(EcfHeaders.Teacher2Id, reader["Lehrer2"]);
ecfTableWriter.SetValue(EcfHeaders.FormOfTeachingId, reader["Unterrichtsform"]);
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
return(ecfRecordCounter);
}