Ejemplo n.º 1
0
        public EnrollmentResponse EnrollNewStudent(EnrollmentStudentRequest enrollmentStudentRequest)
        {
            using var connection = new SqlConnection(AppSettingsUtils.GetConnectionString());
            using var command    = new SqlCommand { Connection = connection };
            connection.Open();
            var transaction = connection.BeginTransaction();

            command.Transaction = transaction;

            command.CommandText = "SELECT s.IdStudy FROM Studies s WHERE s.Name = @StudiesName";
            command.Parameters.AddWithValue("StudiesName", enrollmentStudentRequest.Studies);
            var dataReader = command.ExecuteReader();

            if (!dataReader.Read())
            {
                throw new ResourceNotFoundException(
                          $"Studies by name {enrollmentStudentRequest.Studies} does not exist in database");
            }
            var idStudy = Parse(dataReader["IdStudy"].ToString());

            dataReader.Close();
            command.Parameters.Clear();
            command.CommandText =
                "SELECT * FROM Enrollment E WHERE E.Semester = 1 AND E.IdStudy = @IdStudy";
            command.Parameters.AddWithValue("IdStudy", idStudy);
            dataReader = command.ExecuteReader();

            var enrollmentResponse = new EnrollmentResponse();

            if (!dataReader.Read())
            {
                dataReader.Close();
                command.Parameters.Clear();

                command.CommandText =
                    @"INSERT INTO Enrollment(IdEnrollment, Semester, StartDate, IdStudy) 
                OUTPUT INSERTED.IdEnrollment, INSERTED.Semester, INSERTED.StartDate, INSERTED.IdStudy 
                VALUES((SELECT MAX(E.IdEnrollment) FROM Enrollment E) + 1, @Semester, @StartDate, @IdStudy);";
                command.Parameters.AddWithValue("Semester", 1);
                command.Parameters.AddWithValue("StartDate", DateTime.Now);
                command.Parameters.AddWithValue("IdStudy", idStudy);

                enrollmentResponse.IdEnrollment = Parse(command.ExecuteScalar().ToString());
                enrollmentResponse.Semester     = Parse(command.Parameters["Semester"].Value.ToString());
                enrollmentResponse.IdStudy      = Parse(command.Parameters["IdStudy"].Value.ToString());
                enrollmentResponse.StartDate    =
                    DateTime.Parse(command.Parameters["StartDate"].Value.ToString()).ToString("yyyy-MM-dd");
            }
            else
            {
                enrollmentResponse.IdEnrollment = Parse(dataReader["IdEnrollment"].ToString());
                enrollmentResponse.Semester     = Parse(dataReader["Semester"].ToString());
                enrollmentResponse.IdStudy      = Parse(dataReader["IdStudy"].ToString());
                enrollmentResponse.StartDate    =
                    DateTime.Parse(dataReader["StartDate"].ToString()).ToString("yyyy-MM-dd");
            }

            dataReader.Close();
            command.Parameters.Clear();
            command.CommandText = "SELECT S.IndexNumber FROM Student S WHERE IndexNumber = @indexNumber";
            command.Parameters.AddWithValue("indexNumber", enrollmentStudentRequest.Index);
            dataReader = command.ExecuteReader();
            if (dataReader.Read())
            {
                throw new BadRequestException("Student Index number not unique");;
            }

            dataReader.Close();
            command.Parameters.Clear();
            var salt = PasswordUtils.GenerateSalt();

            command.CommandText =
                @"INSERT INTO Student(IndexNumber, FirstName, LastName, BirthDate, IdEnrollment, Password, Salt) 
                VALUES (@IndexNumber, @FirstName, @LastName, @BirthDate, @IdEnrollment, @Password, @Salt)";
            command.Parameters.AddWithValue("IndexNumber", enrollmentStudentRequest.Index);
            command.Parameters.AddWithValue("FirstName", enrollmentStudentRequest.FirstName);
            command.Parameters.AddWithValue("LastName", enrollmentStudentRequest.LastName);
            command.Parameters.AddWithValue("BirthDate", enrollmentStudentRequest.BirthDate);
            command.Parameters.AddWithValue("IdEnrollment", enrollmentResponse.IdEnrollment);
            command.Parameters.AddWithValue("Password",
                                            PasswordUtils.CreateSaltedPasswordHash(enrollmentStudentRequest.Password, salt));
            command.Parameters.AddWithValue("Salt", salt);
            command.ExecuteNonQuery();

            transaction.Commit();
            return(enrollmentResponse);
        }
Ejemplo n.º 2
0
        public EnrollmentResponse EnrollNewStudentEntity(EnrollmentStudentRequest enrollmentStudentRequest)
        {
            var context = new StudentDbContext();
            var studies = context.Studies
                          .FirstOrDefault(stud => enrollmentStudentRequest.Studies.Equals(stud.Name));

            if (studies == null)
            {
                throw new ResourceNotFoundException("Studies with name = " + enrollmentStudentRequest.Studies
                                                    + " dont exist");
            }

            var enrollment = context.Enrollment.FirstOrDefault(enroll => 1.Equals(enroll.Semester) &&
                                                               studies.IdStudy.Equals(enroll.IdStudy));

            var enrollmentResponse = new EnrollmentResponse();

            if (enrollment == null)
            {
                var enrollmentNew = new Enrollment
                {
                    IdEnrollment = context.Enrollment.Select(enroll =>
                                                             enroll.IdEnrollment).OrderByDescending(i => i).First() + 1,
                    Semester  = 1,
                    StartDate = DateTime.Now,
                    IdStudy   = studies.IdStudy
                };

                context.Enrollment.Add(enrollmentNew);

                enrollmentResponse.IdEnrollment = enrollmentNew.IdEnrollment;
                enrollmentResponse.Semester     = enrollmentNew.Semester;
                enrollmentResponse.IdStudy      = enrollmentNew.IdStudy;
                enrollmentResponse.StartDate    = enrollmentNew.StartDate.ToString("yyyy-MM-dd");
            }
            else
            {
                enrollmentResponse.IdEnrollment = enrollment.IdEnrollment;
                enrollmentResponse.Semester     = enrollment.Semester;
                enrollmentResponse.IdStudy      = enrollment.IdStudy;
                enrollmentResponse.StartDate    = enrollment.StartDate.ToString("yyyy-MM-dd");
            }

            if (context.Student.Any(student => enrollmentStudentRequest.Index.Equals(student.IndexNumber)))
            {
                throw new BadRequestException("Student Index number not unique");
                ;
            }

            var salt = PasswordUtils.GenerateSalt();

            context.Student.Add(new Student
            {
                IndexNumber  = enrollmentStudentRequest.Index,
                FirstName    = enrollmentStudentRequest.FirstName,
                LastName     = enrollmentStudentRequest.LastName,
                BirthDate    = enrollmentStudentRequest.BirthDate,
                IdEnrollment = enrollmentResponse.IdEnrollment,
                Password     = PasswordUtils.CreateSaltedPasswordHash(enrollmentStudentRequest.Password, salt),
                Salt         = salt
            });

            context.SaveChanges();

            return(enrollmentResponse);
        }