public static void ProcessStudentsSubjects(EcfTableReader ecfTableReader, List <Students> students)
{
while (ecfTableReader.ReadAsync().Result > 0)
{
var studentId = ecfTableReader.GetValue <string>("StudentId");
var student = students.Find(s => s.EcfId.Equals(studentId));
var schoolTermId = ecfTableReader.GetValue <string>("SchoolTermId");
var classTermId = ecfTableReader.GetValue <string>("SchoolClassId");
var subjectId = ecfTableReader.GetValue <string>("SubjectId");
if (!String.IsNullOrEmpty(studentId) && !String.IsNullOrEmpty(schoolTermId) && !String.IsNullOrEmpty(classTermId) && !String.IsNullOrEmpty(subjectId) && (student != null))
{
var careerId = ecfTableReader.GetValue <string>("Id");
var career = student.Career.Find(c => c.EcfValues.Id.Equals(careerId));
if (career == null)
{
career = new Career(
ecfTableReader.GetValue <string>("Id"),
ecfTableReader.GetValue <string>("SchoolTermId"),
ecfTableReader.GetValue <string>("SchoolClassId"),
ecfTableReader.GetValue <string>("StudentId")
);
student.Career.Add(career);
}
StudentSubjects studentSubjects = new StudentSubjects(
ecfTableReader.GetValue <string>("SubjectId"),
ecfTableReader.GetValue <string>("TeacherId"),
ecfTableReader.GetValue <string>("Grade1ValueId"),
ecfTableReader.GetValue <string>("CourseNo"),
ecfTableReader.GetValue <string>("CourseTypeId"),
ecfTableReader.GetValue <string>("Grade1AchievementTypeId"),
ecfTableReader.GetValue <string>("Passfail")
);
career.StudentSubjects.Add(studentSubjects);
}
}
}
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 <DbResult> StudentSchoolClassAttendance(FbConnection fbConnection, int tenantId, int studentId, Career career)
{
var id = -1;
var sql =
"INSERT INTO \"SchuelerZeitraeume\" " +
"(" +
" \"Mandant\", \"KlassenZeitraumID\", \"Schueler\", " +
" \"Klasse\", \"Zeitraum\", \"Gewechselt\", " +
" \"TeilnahmeZusatzangebot\", \"SportBefreit\" " +
") " +
"VALUES ( " +
" @TenantId, @ClassTermId, @StudentId, " +
" @SchoolClassId, @SchoolTermId, @Gewechselt, " +
" @TeilnahmeZusatzangebot, @SportBefreit " +
") 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, "@ClassTermId", FbDbType.BigInt, career.MagellanValues.ClassTermId);
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, "@Gewechselt", FbDbType.VarChar, career.MagellanValues.Gewechselt);
Helper.SetParamValue(fbCommand, "@TeilnahmeZusatzangebot", FbDbType.VarChar, "N");
Helper.SetParamValue(fbCommand, "@SportBefreit", FbDbType.VarChar, "N");
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)
{
await fbTransaction.RollbackAsync();
Console.WriteLine($"[INSERT ERROR] [SchuelerZeitraeume] {e.Message}");
return(new DbResult(false, id));
}
}