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);
}