public IActionResult EnrollNewStudentEntity(EnrollmentStudentRequest enrollmentStudentRequest) { return(Ok(_enrollmentsDbService.EnrollNewStudentEntity(enrollmentStudentRequest))); }
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); }
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); }