public IActionResult RegisterNewStudent(RegistrationStudentRequest registrationStudent)
{
EnrollmentStatus enrollmentStatus = _enrollmentDBService.RegisterStudent(registrationStudent);
if (enrollmentStatus.Status == 400)
{
return(BadRequest(enrollmentStatus.Message));
}
return(Ok(enrollmentStatus.enrollment));
}
EnrollmentStatus IEnrollmentDBService.RegisterStudent(RegistrationStudentRequest registrationStudentRequest)
{
throw new NotImplementedException();
}
public EnrollmentStatus RegisterStudent(RegistrationStudentRequest registrationStudentRequest)
{
EnrollmentStatus registerStudentStatus = new EnrollmentStatus();
using (var client = new SqlConnection(SqlConn))
using (var command = new SqlCommand())
{
command.Connection = client;
client.Open();
var transaction = client.BeginTransaction();
command.Transaction = transaction;
try
{
command.CommandText = $"SELECT IdStudy FROM STUDIES WHERE Name=@name";
command.Parameters.AddWithValue("name", registrationStudentRequest.Studies);
var dataReader = command.ExecuteReader();
if (!dataReader.Read())
{
dataReader.Close();
transaction.Rollback();
registerStudentStatus.enrollment = null;
registerStudentStatus.Status = 400;
registerStudentStatus.Message = "Nie ma takich studiów";
return(registerStudentStatus);
}
string IdStudy = dataReader["IdStudy"].ToString();
dataReader.Close();
command.CommandText = $"SELECT IdEnrollment, StartDate FROM Enrollment WHERE Enrollment.Semester = 1 AND Enrollment.IdStudy = @IdStudy AND Enrollment.StartDate = (SELECT MAX(StartDate) FROM Enrollment WHERE Enrollment.Semester = 1 AND Enrollment.IdStudy = @IdStudy);";
command.Parameters.AddWithValue("IdStudy", IdStudy);
dataReader = command.ExecuteReader();
DateTime StartDate = DateTime.Now;
int IdEnrollemnt = -1;
if (!dataReader.Read())
{
command.CommandText = $"INSERT INTO Enrollment VALUES(SELECT MAX(IdEnrollment) + 1 FROM Enrollment WHERE Enrollment.IdStudy = @IdStudy) + 1, 1, @IdStudy, @StartDate)";
command.Parameters.AddWithValue("IdStudy", IdStudy);
command.Parameters.AddWithValue("StartDate", DateTime.Now);
command.ExecuteReader();
dataReader.Close();
command.CommandText = $"SELECT IdEnrollment, StartDate FROM Enrollment WHERE Enrollment.Semester = 1 AND Enrollment.IdStudy = @IdStudy AND Enrollment.StartDate = (SELECT MAX(StartDate) FROM Enrollment WHERE Enrollment.Semester = 1 AND Enrollment.IdStudy = @IdStudy);";
command.Parameters.AddWithValue("IdStudy", IdStudy);
dataReader = command.ExecuteReader();
dataReader.Read();
}
else
{
StartDate = DateTime.Parse(dataReader["StartDate"].ToString());
}
IdEnrollemnt = int.Parse(dataReader["IdEnrollment"].ToString());
dataReader.Close();
command.CommandText = $"SELECT IndexNumber FROM Student WHERE IndexNumber = @IndexNumber";
command.Parameters.AddWithValue("IndexNumber", registrationStudentRequest.IndexNumber);
dataReader = command.ExecuteReader();
if (dataReader.Read())
{
dataReader.Close();
transaction.Rollback();
registerStudentStatus.enrollment = null;
registerStudentStatus.Status = 400;
registerStudentStatus.Message = "Student o tym numerze indeksu już zapisany";
return(registerStudentStatus);
}
dataReader.Close();
command.CommandText = $"INSERT INTO Student VALUES(@IndexNumber, @FirstName, @LastName, @BirthDate, @IdEnrollment)";
command.Parameters.Clear();
command.Parameters.AddWithValue("IndexNumber", registrationStudentRequest.IndexNumber);
command.Parameters.AddWithValue("FirstName", registrationStudentRequest.FirstName);
command.Parameters.AddWithValue("LastName", registrationStudentRequest.LastName);
command.Parameters.AddWithValue("BirthDate", registrationStudentRequest.BirthDate);
command.Parameters.AddWithValue("IdEnrollment", IdEnrollemnt);
command.ExecuteNonQuery();
transaction.Commit();
EnrollmentResponse enrollment = new EnrollmentResponse()
{
IdEnrollment = IdEnrollemnt,
Semester = 1,
IdStudy = int.Parse(IdStudy),
StartDate = StartDate,
};
registerStudentStatus.enrollment = enrollment;
registerStudentStatus.Status = 201;
registerStudentStatus.Message = "Student added";
}
catch (SqlException exc)
{
transaction.Rollback();
registerStudentStatus.enrollment = null;
registerStudentStatus.Status = 400;
registerStudentStatus.Message = "Nastąpił wyjątek";
}
}
return(registerStudentStatus);
}
