public IActionResult EnrollStudent(PostStudentRequest student)
{
var response = _service.PostStudent(student);
switch (response)
{
case "Not Found":
return(NotFound());
case "BadRequest":
return(BadRequest());
default:
return(Ok(response));
}
}
public string PostStudent(PostStudentRequest student)
{
Enrollment enrollment = new Enrollment();
using (var connection = new SqlConnection(_connStr))
using (var query = new SqlCommand())
{
query.Connection = connection;
connection.Open();
var transaction = connection.BeginTransaction();
try
{
query.CommandText = "SELECT IdStudy FROM Studies where Name=@name";
query.Parameters.AddWithValue("name", student.Studies);
query.Transaction = transaction;
var studs = query.ExecuteReader();
if (!studs.Read())
{
return("Not Found");
}
int idstudies = (int)studs["IdStudy"];
studs.Close();
query.CommandText =
"SELECT IdEnrollment FROM Enrollment WHERE IdEnrollment >= (SELECT MAX(IdEnrollment) FROM Enrollment)";
var idenr = query.ExecuteReader();
int idEnroll = (int)idenr["IdEnrollment"] + 10;
idenr.Close();
query.CommandText =
"SELECT idEnrollment, StartDate from Enrollment WHERE idStudy=@idStudy AND Semester=1 ORDER BY StartDate";
query.Parameters.AddWithValue("idStudy", idstudies);
DateTime enrollDate;
var enrol = query.ExecuteReader();
if (!enrol.Read())
{
enrollDate = DateTime.Now;
query.CommandText = "INSERT INTO Enrollment VALUES(@id, @Semester, @IdStud, @StartDate)";
query.Parameters.AddWithValue("id", idEnroll);
query.Parameters.AddWithValue("Semester", 1);
query.Parameters.AddWithValue("IdStud", idstudies);
query.Parameters.AddWithValue("StartDate", enrollDate);
enrol.Close();
query.ExecuteNonQuery();
}
else
{
idEnroll = (int)enrol["IdEnrollment"];
enrollDate = (DateTime)enrol["StartDate"];
enrol.Close();
}
enrollment.IdEnrollment = idEnroll;
enrollment.Semester = 1;
enrollment.IdStudy = idstudies;
enrollment.StartDate = enrollDate;
query.CommandText = "SELECT IndexNumber FROM Student WHERE IndexNumber=@indexNum";
query.Parameters.AddWithValue("indexNum", student.IndexNumber);
DateTime bDate = Convert.ToDateTime(student.BirthDate);
string formattedDate = bDate.ToString("yyyy-MM-dd HH:mm:ss.fff");
try
{
query.CommandText =
"INSERT INTO Student(IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) VALUES (@index, @fName, @lName, @birthDate, @idEnrollment)";
query.Parameters.AddWithValue("index", student.IndexNumber);
query.Parameters.AddWithValue("fName", student.FirstName);
query.Parameters.AddWithValue("lName", student.LastName);
query.Parameters.AddWithValue("birthDate", formattedDate);
query.Parameters.AddWithValue("idEnrollment", idEnroll);
query.ExecuteNonQuery();
transaction.Commit();
}
catch (SqlException ex)
{
transaction.Rollback();
return("BadRequest");
}
}
catch (SqlException exc)
{
transaction.Rollback();
return("BadRequest");
}
return(enrollment.ToString());
}
}
