Esempio n. 1
0
        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);
        }
Esempio n. 3
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));;
        }