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 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> 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> StudentSubject(FbConnection fbConnection, int tenantId, int studentTermId, StudentSubjects studentSubject)
{
string sql =
"SELECT \"ID\" FROM \"SchuelerFachdaten\" " +
"WHERE " +
" \"Mandant\" = @TenantId AND " +
" \"SchuelerZeitraumID\" = @SchuelerZeitraumId AND " +
" \"Fach\" = @SubjectId AND " +
" \"Unterrichtsart\" = @CourseTypeId AND " +
" \"KursNr\" = @CourseNo ";
using var fbTransaction = fbConnection.BeginTransaction();
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId);
Helper.SetParamValue(fbCommand, "@SchuelerZeitraumId", FbDbType.BigInt, studentTermId);
Helper.SetParamValue(fbCommand, "@SubjectId", FbDbType.BigInt, studentSubject.MagellanValues.SubjectId);
Helper.SetParamValue(fbCommand, "@CourseNo", FbDbType.SmallInt, studentSubject.EcfValues.CourseNo);
Helper.SetParamValue(fbCommand, "@CourseTypeId", FbDbType.VarChar, studentSubject.EcfValues.CourseTypeId);
var sqlReader = await fbCommand.ExecuteReaderAsync();
var id = -1;
var numberOfRecords = 0;
while (sqlReader.Read())
{
// do things
id = (int)sqlReader["ID"];
numberOfRecords++;
}
if (numberOfRecords == 1)
{
return(new DbResult(true, id));
}
return(new DbResult(false, id));;
}