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 <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> 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> 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);
}
