public static async Task <bool> GradeValue(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId, int id)
{
var success = 0;
var sql =
"UPDATE \"Noten\" " +
"SET " +
" \"Bezeichnung\" = @Name, \"Notenwert\" = @Value, \"Notenart\" = @GradeSystemId " +
"WHERE" +
" \"Mandant\" = @TenantId AND " +
" \"ID\" = @Id";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.VarChar, tenantId);
Helper.SetParamValue(fbCommand, "@Id", FbDbType.VarChar, id);
Helper.SetParamValue(fbCommand, "@Name", FbDbType.VarChar, ecfTableReader.GetValue <string>("Name"));
Helper.SetParamValue(fbCommand, "@Value", FbDbType.Integer, ecfTableReader.GetValue <string>("Value"));
Helper.SetParamValue(fbCommand, "@GradeSystemId", FbDbType.SmallInt, ValueConvert.GradeSystem(ecfTableReader.GetValue <string>("GradeSystemId")));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[UPDATE ERROR] [Noten] {e.Message}");
}
return(success > 0);
}
public static async Task <bool> Custodian(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId)
{
Guid guidExtern = GuidFactory.Create(GuidFactory.DnsNamespace, ecfTableReader.GetValue <string>("Id"));
var success = 0;
var sql =
"INSERT INTO \"Sorgeberechtigte\" " +
"(" +
" \"Mandant\", \"GUIDExtern\", \"Nachname\", \"Vorname\", \"Anrede\", " +
" \"Strasse\", \"Land\", \"PLZ\", \"Ort\", \"Email\", \"TelefonPrivat\", \"TelefonBeruf\", " +
" \"Status2\" " +
") " +
"VALUES ( " +
" @TenantId, @GUIDExtern, @LastName, @FirstName, @Salutation, " +
" @AddressLines, @Country, @PostalCode, @Locality, @Email, @HomePhoneNumber, @OfficePhoneNumber, " +
" @Status2 " +
")";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.Integer, tenantId);
Helper.SetParamValue(fbCommand, "@GUIDExtern", FbDbType.VarChar, guidExtern);
Helper.SetParamValue(fbCommand, "@LastName", FbDbType.VarChar, ecfTableReader.GetValue <string>("LastName"));
Helper.SetParamValue(fbCommand, "@FirstName", FbDbType.VarChar, ecfTableReader.GetValue <string>("FirstName"));
Helper.SetParamValue(fbCommand, "@Salutation", FbDbType.VarChar, ValueConvert.Salutation(ecfTableReader.GetValue <string>("Salutation")));
Helper.SetParamValue(fbCommand, "@AddressLines", FbDbType.VarChar, ecfTableReader.GetValue <string>("AddressLines"));
Helper.SetParamValue(fbCommand, "@Country", FbDbType.VarChar, ecfTableReader.GetValue <string>("CountryId"));
Helper.SetParamValue(fbCommand, "@PostalCode", FbDbType.VarChar, ecfTableReader.GetValue <string>("PostalCode"));
Helper.SetParamValue(fbCommand, "@Locality", FbDbType.VarChar, ecfTableReader.GetValue <string>("Locality"));
Helper.SetParamValue(fbCommand, "@Email", FbDbType.VarChar, ecfTableReader.GetValue <string>("Email"));
Helper.SetParamValue(fbCommand, "@HomePhoneNumber", FbDbType.VarChar, ecfTableReader.GetValue <string>("HomePhoneNumber"));
Helper.SetParamValue(fbCommand, "@OfficePhoneNumber", FbDbType.VarChar, ecfTableReader.GetValue <string>("OfficePhoneNumber"));
Helper.SetParamValue(fbCommand, "@Status2", FbDbType.SmallInt, StatusType.Active);
// Status
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[INSERT ERROR] [Sorgeberechtigte] {e.Message}");
}
return(success > 0);
}
public static async Task <bool> StudentSubject(FbConnection fbConnection, int tenantId, int studentId, Career career, StudentSubjects studentSubject)
{
var success = 0;
var sql =
"INSERT INTO \"SchuelerFachdaten\" " +
"(" +
" \"Mandant\", \"SchuelerZeitraumID\", \"Schueler\", " +
" \"Klasse\", \"Zeitraum\", \"Fach\", \"Lehrer\", " +
" \"KursNr\", \"Unterrichtsart\", \"Endnote1\", " +
" \"Leistungsart\", \"Bestanden\" " +
") " +
"VALUES ( " +
" @TenantId, @StudentTermId, @StudentId, " +
" @SchoolClassId, @SchoolTermId, @SubjectId, @TeacherId, " +
" @CourseNo, @CourseTypeId, @Grade1ValueId, " +
" @Grade1AchievementTypeId, @Passfail " +
")";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId);
Helper.SetParamValue(fbCommand, "@StudentTermId", FbDbType.BigInt, career.MagellanValues.StudentTermId);
Helper.SetParamValue(fbCommand, "@StudentId", FbDbType.BigInt, studentId);
Helper.SetParamValue(fbCommand, "@SchoolClassId", FbDbType.BigInt, career.MagellanValues.SchoolClassId);
Helper.SetParamValue(fbCommand, "@SchoolTermId", FbDbType.BigInt, career.MagellanValues.SchoolTermId);
Helper.SetParamValue(fbCommand, "@SubjectId", FbDbType.BigInt, studentSubject.MagellanValues.SubjectId);
Helper.SetParamValue(fbCommand, "@TeacherId", FbDbType.BigInt, studentSubject.MagellanValues.TeacherId);
Helper.SetParamValue(fbCommand, "@CourseNo", FbDbType.SmallInt, studentSubject.EcfValues.CourseNo);
Helper.SetParamValue(fbCommand, "@CourseTypeId", FbDbType.SmallInt, studentSubject.EcfValues.CourseTypeId);
Helper.SetParamValue(fbCommand, "@Grade1ValueId", FbDbType.BigInt, studentSubject.MagellanValues.Grade1ValueId);
Helper.SetParamValue(fbCommand, "@Grade1AchievementTypeId", FbDbType.VarChar, studentSubject.EcfValues.Grade1AchievementTypeId);
Helper.SetParamValue(fbCommand, "@Passfail", FbDbType.VarChar, ValueConvert.Passfail(studentSubject.EcfValues.Passfail));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
await fbTransaction.RollbackAsync();
Console.WriteLine($"[INSERT ERROR] [SchuelerFachdaten] {e.Message}");
}
return(success > 0);
}
public static async Task <bool> Student(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId, int id)
{
var success = 0;
var sql =
"UPDATE \"Schueler\" " +
"SET " +
" \"Anrede\" = @Salutation, \"Nachname\" = @LastName, \"Vorname\" = @FirstName, \"Geschlecht\" = @Gender, " +
" \"Geburtsdatum\" = @Birthdate, \"Strasse\" = @AddressLines, \"PLZ\" = @PostalCode, \"Ort\" = @Locality, " +
" \"Gemeinde\" = @Region, \"EMail\" = @Email, \"Telefon\" = @HomePhoneNumber, \"Mobil\" = @MobileNumber, " +
" \"Krankenkasse\" = @HealthInsuranceProvider, \"ZugangAm\" = @EntryDate, \"Grundschuleintritt\" = @FirstEntryDate " +
"WHERE" +
" \"Mandant\" = @TenantId AND " +
" \"ID\" = @Id";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.VarChar, tenantId);
Helper.SetParamValue(fbCommand, "@Id", FbDbType.VarChar, id);
Helper.SetParamValue(fbCommand, "@Salutation", FbDbType.VarChar, ValueConvert.Salutation(ecfTableReader.GetValue <string>("Salutation")));
Helper.SetParamValue(fbCommand, "@LastName", FbDbType.VarChar, ecfTableReader.GetValue <string>("LastName"));
Helper.SetParamValue(fbCommand, "@FirstName", FbDbType.VarChar, ecfTableReader.GetValue <string>("FirstName"));
Helper.SetParamValue(fbCommand, "@Gender", FbDbType.VarChar, ValueConvert.Gender(ecfTableReader.GetValue <string>("Gender")));
Helper.SetParamValue(fbCommand, "@Birthdate", FbDbType.VarChar, ecfTableReader.GetValue <string>("Birthdate"));
Helper.SetParamValue(fbCommand, "@AddressLines", FbDbType.VarChar, ecfTableReader.GetValue <string>("AddressLines"));
Helper.SetParamValue(fbCommand, "@PostalCode", FbDbType.VarChar, ecfTableReader.GetValue <string>("PostalCode"));
Helper.SetParamValue(fbCommand, "@Locality", FbDbType.VarChar, ecfTableReader.GetValue <string>("Locality"));
Helper.SetParamValue(fbCommand, "@Region", FbDbType.VarChar, ecfTableReader.GetValue <string>("RegionId"));
Helper.SetParamValue(fbCommand, "@Email", FbDbType.VarChar, ecfTableReader.GetValue <string>("Email"));
Helper.SetParamValue(fbCommand, "@HomePhoneNumber", FbDbType.VarChar, ecfTableReader.GetValue <string>("HomePhoneNumber"));
Helper.SetParamValue(fbCommand, "@MobileNumber", FbDbType.VarChar, ecfTableReader.GetValue <string>("MobileNumber"));
Helper.SetParamValue(fbCommand, "@HealthInsuranceProvider", FbDbType.VarChar, ecfTableReader.GetValue <string>("HealthInsuranceProvider"));
Helper.SetParamValue(fbCommand, "@EntryDate", FbDbType.Date, ecfTableReader.GetValue <string>("EntryDate"));
Helper.SetParamValue(fbCommand, "@FirstEntryDate", FbDbType.Date, ecfTableReader.GetValue <string>("FirstEntryDate"));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[UPDATE ERROR] [Schueler] {e.Message}");
}
return(success > 0);
}
public static async Task <DbResult> SchoolClass(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId)
{
var id = -1;
Guid guidExtern = GuidFactory.Create(GuidFactory.DnsNamespace, ecfTableReader.GetValue <string>("FederationId"));
var sql =
"INSERT INTO \"Klassen\" " +
"(" +
" \"Mandant\", \"Kuerzel\", \"KuerzelStatistik\", \"Langname1\", \"Langname2\", " +
" \"GUIDExtern\", \"Schulform\", \"Klassenart\", \"Notenart\" " +
") " +
"VALUES ( " +
" @TenantId, @Code, @StatisticalCode, @Name1, @Name2, " +
" @GUIDExtern, @SchoolCategoryId, @SchoolClassTypeId, @GradeSystemId" +
") RETURNING ID";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId);
Helper.SetParamValue(fbCommand, "@Code", FbDbType.VarChar, ecfTableReader.GetValue <string>("Code"));
Helper.SetParamValue(fbCommand, "@StatisticalCode", FbDbType.VarChar, ecfTableReader.GetValue <string>("StatisticalCode"));
Helper.SetParamValue(fbCommand, "@Name1", FbDbType.VarChar, ecfTableReader.GetValue <string>("Name1"));
Helper.SetParamValue(fbCommand, "@Name2", FbDbType.VarChar, ecfTableReader.GetValue <string>("Name2"));
Helper.SetParamValue(fbCommand, "@GUIDExtern", FbDbType.Guid, guidExtern);
Helper.SetParamValue(fbCommand, "@SchoolCategoryId", FbDbType.VarChar, ecfTableReader.GetValue <string>("SchoolCategoryId"));
Helper.SetParamValue(fbCommand, "@SchoolClassTypeId", FbDbType.SmallInt, ecfTableReader.GetValue <string>("SchoolClassTypeId"));
Helper.SetParamValue(fbCommand, "@GradeSystemId", FbDbType.SmallInt, ValueConvert.GradeSystem(ecfTableReader.GetValue <string>("GradeSystemId")));
FbParameter IdParam = fbCommand.Parameters.Add("@ClassId", FbDbType.Integer, Int32.MaxValue, "ID");
IdParam.Direction = ParameterDirection.Output;
id = (int)await fbCommand.ExecuteScalarAsync();
await fbTransaction.CommitAsync();
return(new DbResult(true, id));
}
catch (Exception e)
{
await fbTransaction.RollbackAsync();
Console.WriteLine($"[INSERT ERROR] [Klassen] {e.Message}");
return(new DbResult(false, id));
}
}
public static async Task <bool> Teacher(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId)
{
var success = 0;
var sql =
"INSERT INTO \"tblLehrer\" " +
"(" +
" \"Mandant\", \"GUIDExtern\", \"Kuerzel\", \"Anrede\", \"Nachname\", \"Vorname\", " +
" \"Geschlecht\", \"Geburtsdatum\", \"Strasse\", \"PLZ\", \"Ort\", \"Email\" " +
") " +
"VALUES ( " +
" @TenantId, @GUIDExtern, @Code, @Salutation, @LastName, @FirstName, @Gender, " +
" @Birthdate, @AddressLines, @PostalCode, @Locality, @Email " +
")";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId);
Helper.SetParamValue(fbCommand, "@GUIDExtern", FbDbType.BigInt, ecfTableReader.GetValue <string>("Id"));
Helper.SetParamValue(fbCommand, "@Code", FbDbType.VarChar, ecfTableReader.GetValue <string>("Code"));
Helper.SetParamValue(fbCommand, "@Salutation", FbDbType.VarChar, ValueConvert.Salutation(ecfTableReader.GetValue <string>("Salutation")));
Helper.SetParamValue(fbCommand, "@LastName", FbDbType.VarChar, ecfTableReader.GetValue <string>("LastName"));
Helper.SetParamValue(fbCommand, "@FirstName", FbDbType.VarChar, ecfTableReader.GetValue <string>("FirstName"));
Helper.SetParamValue(fbCommand, "@Gender", FbDbType.VarChar, ValueConvert.Gender(ecfTableReader.GetValue <string>("Gender")));
Helper.SetParamValue(fbCommand, "@Birthdate", FbDbType.Date, ecfTableReader.GetValue <string>("Birthdate"));
Helper.SetParamValue(fbCommand, "@AddressLines", FbDbType.VarChar, ecfTableReader.GetValue <string>("AddressLines"));
Helper.SetParamValue(fbCommand, "@PostalCode", FbDbType.VarChar, ecfTableReader.GetValue <string>("PostalCode"));
Helper.SetParamValue(fbCommand, "@Locality", FbDbType.VarChar, ecfTableReader.GetValue <string>("Locality"));
Helper.SetParamValue(fbCommand, "@Email", FbDbType.VarChar, ecfTableReader.GetValue <string>("Email"));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[INSERT ERROR] [Lehrer] {e.Message}");
}
return(success > 0);
}
public static async Task <bool> Teacher(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId, int id)
{
var success = 0;
var sql =
"UPDATE \"tblLehrer\" " +
"SET " +
" \"Anrede\" = @Salutation, \"Nachname\" = @LastName, \"Vorname\" = @FirstName, " +
" \"Geschlecht\" = @Gender, \"Geburtsdatum\" = @Birthdate, \"Strasse\" = @AddressLines, " +
" \"PLZ\" = @PostalCode, \"Ort\" = @Locality, \"Email\" = @Email " +
"WHERE" +
" \"Mandant\" = @TenantId AND " +
" \"ID\" = @Id";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.VarChar, tenantId);
Helper.SetParamValue(fbCommand, "@Id", FbDbType.VarChar, id);
Helper.SetParamValue(fbCommand, "@Salutation", FbDbType.VarChar, ValueConvert.Salutation(ecfTableReader.GetValue <string>("Salutation")));
Helper.SetParamValue(fbCommand, "@LastName", FbDbType.VarChar, ecfTableReader.GetValue <string>("LastName"));
Helper.SetParamValue(fbCommand, "@FirstName", FbDbType.VarChar, ecfTableReader.GetValue <string>("FirstName"));
Helper.SetParamValue(fbCommand, "@Gender", FbDbType.VarChar, ValueConvert.Gender(ecfTableReader.GetValue <string>("Gender")));
Helper.SetParamValue(fbCommand, "@Birthdate", FbDbType.VarChar, ecfTableReader.GetValue <string>("Birthdate"));
Helper.SetParamValue(fbCommand, "@AddressLines", FbDbType.VarChar, ecfTableReader.GetValue <string>("AddressLines"));
Helper.SetParamValue(fbCommand, "@PostalCode", FbDbType.VarChar, ecfTableReader.GetValue <string>("PostalCode"));
Helper.SetParamValue(fbCommand, "@Locality", FbDbType.VarChar, ecfTableReader.GetValue <string>("Locality"));
Helper.SetParamValue(fbCommand, "@Email", FbDbType.VarChar, ecfTableReader.GetValue <string>("Email"));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[UPDATE ERROR] [tblLehrer] {e.Message}");
}
return(success > 0);
}
public static async Task <bool> GradeValue(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId)
{
var success = 0;
var sql =
$"INSERT INTO \"Noten\" " +
"( \"Mandant\", \"Notenkuerzel\", \"Bezeichnung\", \"Notenart\", \"Notenwert\" )" +
"VALUES ( @TenantId, @Code, @Name, @GradeSystemId, @Value )";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
// Token based table with max. length of 20, code will be cut, if longer
string code = ecfTableReader.GetValue <string>("Code");
if (code.Length > 20)
{
code = code.Substring(0, 20);
}
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId);
Helper.SetParamValue(fbCommand, "@Code", FbDbType.VarChar, code);
Helper.SetParamValue(fbCommand, "@Name", FbDbType.VarChar, ecfTableReader.GetValue <string>("Name"));
Helper.SetParamValue(fbCommand, "@GradeSystemId", FbDbType.SmallInt, ValueConvert.GradeSystem(ecfTableReader.GetValue <string>("GradeSystemId")));
Helper.SetParamValue(fbCommand, "@Value", FbDbType.Integer, ecfTableReader.GetValue <string>("Value"));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[INSERT ERROR] [Noten] {e.Message}");
}
return(success > 0);
}
public static async Task <bool> StudentSubject(FbConnection fbConnection, int tenantId, int studentSubjectId, StudentSubjects studentSubject)
{
var success = 0;
var sql =
"UPDATE \"SchuelerFachdaten\" " +
"SET " +
" \"Lehrer\" = @TeacherId, \"Endnote1\" = @Grade1ValueId, " +
" \"Leistungsart\" = @Grade1AchievementTypeId, \"Bestanden\" = @Passfail " +
"WHERE" +
" \"Mandant\" = @TenantId AND " +
" \"ID\" = @StudentSubjectId ";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId);
Helper.SetParamValue(fbCommand, "@StudentSubjectId", FbDbType.BigInt, studentSubjectId);
Helper.SetParamValue(fbCommand, "@TeacherId", FbDbType.BigInt, studentSubject.MagellanValues.TeacherId);
Helper.SetParamValue(fbCommand, "@Grade1ValueId", FbDbType.BigInt, studentSubject.MagellanValues.Grade1ValueId);
Helper.SetParamValue(fbCommand, "@Grade1AchievementTypeId", FbDbType.VarChar, studentSubject.EcfValues.Grade1AchievementTypeId);
Helper.SetParamValue(fbCommand, "@Passfail", FbDbType.VarChar, ValueConvert.Passfail(studentSubject.EcfValues.Passfail));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
await fbTransaction.RollbackAsync();
Console.WriteLine($"[UPDATE ERROR] [SchuelerFachdaten] {e.Message}");
}
return(success > 0);
}
public static async Task <bool> SchoolClass(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId, int schoolClassesId)
{
var success = 0;
var sql =
"UPDATE \"Klassen\" " +
"SET " +
" \"Langname1\" = @Name1, \"Langname2\" = @Name2, \"KuerzelStatistik\" = @StatisticalCode, " +
" \"Schulform\" = @SchoolCategoryId, \"Klassenart\" = @SchoolClassTypeId, \"Notenart\" = @GradeSystemId " +
"WHERE" +
" \"Mandant\" = @TenantId AND " +
" \"ID\" = @Id";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId);
Helper.SetParamValue(fbCommand, "@Id", FbDbType.BigInt, schoolClassesId);
Helper.SetParamValue(fbCommand, "@Name1", FbDbType.VarChar, ecfTableReader.GetValue <string>("Name1"));
Helper.SetParamValue(fbCommand, "@Name2", FbDbType.VarChar, ecfTableReader.GetValue <string>("Name2"));
Helper.SetParamValue(fbCommand, "@StatisticalCode", FbDbType.VarChar, ecfTableReader.GetValue <string>("StatisticalCode"));
Helper.SetParamValue(fbCommand, "@SchoolCategoryId", FbDbType.VarChar, ecfTableReader.GetValue <string>("SchoolCategoryId"));
Helper.SetParamValue(fbCommand, "@SchoolClassTypeId", FbDbType.SmallInt, ecfTableReader.GetValue <string>("SchoolClassTypeId"));
Helper.SetParamValue(fbCommand, "@GradeSystemId", FbDbType.SmallInt, ValueConvert.GradeSystem(ecfTableReader.GetValue <string>("GradeSystemId")));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[UPDATE ERROR] [Klassen] {e.Message}");
}
return(success > 0);
}
public static async Task <bool> StudentCustodian(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId, int studentId, int custodianId)
{
var success = 0;
var sql =
"INSERT INTO \"SchuelerSorgebe\" " +
"(" +
" \"Mandant\", \"Schueler\", \"Sorgebe\", \"Verhaeltnis\", " +
" \"Benachrichtigung\", \"TelefonPrioritaet\", \"Position\" " +
" )" +
"VALUES ( " +
" @TenantId, @StudentId, @CustodianId, @RelationshipType, @CustodianNotification, @ContactPriority, @Order " +
")";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId);
Helper.SetParamValue(fbCommand, "@StudentId", FbDbType.BigInt, studentId);
Helper.SetParamValue(fbCommand, "@CustodianId", FbDbType.BigInt, custodianId);
Helper.SetParamValue(fbCommand, "@RelationshipType", FbDbType.SmallInt, ValueConvert.RelationShip(ecfTableReader.GetValue <string>("RelationshipType")));
Helper.SetParamValue(fbCommand, "@CustodianNotification", FbDbType.SmallInt, ValueConvert.Notification(ecfTableReader.GetValue <string>("CustodianNotification")));
Helper.SetParamValue(fbCommand, "@ContactPriority", FbDbType.SmallInt, ValueConvert.Priority(ecfTableReader.GetValue <string>("ContactPriority")));
Helper.SetParamValue(fbCommand, "@Order", FbDbType.SmallInt, ecfTableReader.GetValue <string>("Order"));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[INSERT ERROR] [SchuelerSorgebe] {e.Message}");
}
return(success > 0);
}
public static async Task <DbResult> Student(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId)
{
Guid guidExtern = GuidFactory.Create(GuidFactory.DnsNamespace, ecfTableReader.GetValue <string>("Id"));
var id = -1;
var sql =
"INSERT INTO \"Schueler\" " +
"(" +
" \"Mandant\", \"GUIDExtern\", \"Status\", \"Anrede\", \"Nachname\", \"Vorname\", \"Geschlecht\", " +
" \"Geburtsdatum\", \"Strasse\", \"PLZ\", \"Ort\", \"Gemeinde\", \"EMail\", \"Telefon\", \"Mobil\", " +
" \"Staatsangeh1\", \"Staatsangeh2\", \"Verkehrssprache\", \"Konfession\", \"Krankenkasse\", \"ZugangAm\", " +
" \"Grundschuleintritt\" " +
") " +
"VALUES ( " +
" @TenantId, @GUIDExtern, @Status, @Salutation, @LastName, @FirstName, @Gender, " +
" @Birthdate, @AddressLines, @PostalCode, @Locality, @Region, @Email, @HomePhoneNumber, @MobileNumber, " +
" @Nationality1, @Nationality2, @NativeLanguage, @Religion, @HealthInsuranceProvider, @EntryDate, " +
" @FirstEntryDate " +
") RETURNING ID";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.Integer, tenantId);
Helper.SetParamValue(fbCommand, "@GUIDExtern", FbDbType.VarChar, guidExtern);
Helper.SetParamValue(fbCommand, "@Status", FbDbType.SmallInt, 2);
Helper.SetParamValue(fbCommand, "@Salutation", FbDbType.VarChar, ValueConvert.Salutation(ecfTableReader.GetValue <string>("Salutation")));
Helper.SetParamValue(fbCommand, "@LastName", FbDbType.VarChar, ecfTableReader.GetValue <string>("LastName"));
Helper.SetParamValue(fbCommand, "@FirstName", FbDbType.VarChar, ecfTableReader.GetValue <string>("FirstName"));
Helper.SetParamValue(fbCommand, "@Gender", FbDbType.VarChar, ValueConvert.Gender(ecfTableReader.GetValue <string>("Gender")));
Helper.SetParamValue(fbCommand, "@Birthdate", FbDbType.Date, ecfTableReader.GetValue <string>("Birthdate"));
Helper.SetParamValue(fbCommand, "@AddressLines", FbDbType.VarChar, ecfTableReader.GetValue <string>("AddressLines"));
Helper.SetParamValue(fbCommand, "@PostalCode", FbDbType.VarChar, ecfTableReader.GetValue <string>("PostalCode"));
Helper.SetParamValue(fbCommand, "@Locality", FbDbType.VarChar, ecfTableReader.GetValue <string>("Locality"));
Helper.SetParamValue(fbCommand, "@Region", FbDbType.VarChar, ecfTableReader.GetValue <string>("RegionId"));
Helper.SetParamValue(fbCommand, "@Email", FbDbType.VarChar, ecfTableReader.GetValue <string>("Email"));
Helper.SetParamValue(fbCommand, "@HomePhoneNumber", FbDbType.VarChar, ecfTableReader.GetValue <string>("HomePhoneNumber"));
Helper.SetParamValue(fbCommand, "@MobileNumber", FbDbType.VarChar, ecfTableReader.GetValue <string>("MobileNumber"));
Helper.SetParamValue(fbCommand, "@Nationality1", FbDbType.VarChar, ecfTableReader.GetValue <string>("Nationality1Id"));
Helper.SetParamValue(fbCommand, "@Nationality2", FbDbType.VarChar, ecfTableReader.GetValue <string>("Nationality2Id"));
Helper.SetParamValue(fbCommand, "@NativeLanguage", FbDbType.VarChar, ecfTableReader.GetValue <string>("NativeLanguage"));
Helper.SetParamValue(fbCommand, "@Religion", FbDbType.VarChar, ecfTableReader.GetValue <string>("ReligionId"));
Helper.SetParamValue(fbCommand, "@HealthInsuranceProvider", FbDbType.VarChar, ecfTableReader.GetValue <string>("HealthInsuranceProvider"));
Helper.SetParamValue(fbCommand, "@EntryDate", FbDbType.Date, ecfTableReader.GetValue <string>("EntryDate"));
Helper.SetParamValue(fbCommand, "@FirstEntryDate", FbDbType.Date, ecfTableReader.GetValue <string>("FirstEntryDate"));
FbParameter IdParam = fbCommand.Parameters.Add("@Id", FbDbType.Integer, Int32.MaxValue, "ID");
IdParam.Direction = ParameterDirection.Output;
id = (int)await fbCommand.ExecuteScalarAsync();
await fbTransaction.CommitAsync();
return(new DbResult(true, id));
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[INSERT ERROR] [Schueler] {e.Message}");
return(new DbResult(false, id));
}
}
public static async Task <bool> StudentCustodian(FbConnection fbConnection, EcfTableReader ecfTableReader, int tenantId, int id)
{
var success = 0;
var sql =
"UPDATE \"SchuelerSorgebe\" " +
"SET \"Verhaeltnis\" = @RelationshipType " +
"WHERE" +
" \"Mandant\" = @TenantId AND " +
" \"ID\" = @Id";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId);
Helper.SetParamValue(fbCommand, "@Id", FbDbType.BigInt, id);
Helper.SetParamValue(fbCommand, "@RelationshipType", FbDbType.SmallInt, ValueConvert.RelationShip(ecfTableReader.GetValue <string>("RelationshipType")));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[UPDATE ERROR] [StudentCustodian] {e.Message}");
}
return(success > 0);
}
