public IActionResult enrollStudent(EnrollStudentDTO req)
{
Student student = new Student
{
IndexNumber = req.IndexNumber,
LastName = req.LastName,
FirstName = req.FirstName,
BirthDate = req.BirthDate
};
if (!context.Study.Any(s => s.Name.Equals(req.StudyName)))
{
return(null);
}
if (context.Student.Any(s => s.IndexNumber.Equals(student.IndexNumber)))
{
return(null);
}
Student addStudent = new Student
{
IndexNumber = student.IndexNumber,
LastName = student.LastName,
BirthDate = student.BirthDate,
FirstName = student.FirstName,
IdEnrollment = 1
};
context.Student.Add(addStudent);
context.SaveChanges();
int idStudy = context.Study.Single(s => s.Name.Equals(req.StudyName)).IdStudy;
int idEnrollment = context.Enrollment.Where(e => e.Semester == 1 && e.IdStudy == idStudy)
.OrderByDescending(e => e.StartDate).First().IdEnrollment;
if (idEnrollment == 0)
{
idEnrollment = context.Enrollment.Max(e => e.IdEnrollment) + 1;
Enrollment newEnrollment = new Enrollment
{
IdEnrollment = idEnrollment,
Semester = 1,
IdStudy = idStudy,
StartDate = DateTime.Now
};
context.Enrollment.Add(newEnrollment);
context.SaveChanges();
}
addStudent.IdEnrollment = idEnrollment;
context.SaveChanges();
var resp = context.Enrollment.Single(e => e.IdEnrollment == idEnrollment);
return(new OkObjectResult(new EnrollStudentDTO2
{
Semester = resp.Semester,
IdStudy = resp.IdStudy,
StartDate = resp.StartDate,
IdEnrollment = resp.IdEnrollment
}));
}
public async Task <IActionResult> enrollStudent([FromBody] EnrollStudentDTO enrollStudentDTO)
{
EnrollStudentDTO d = new EnrollStudentDTO();
var enroll = _service.EnrollStudent(enrollStudentDTO);
if (enroll == null)
{
return(this.BadRequest());
}
return(CreatedAtRoute("Get", new { id = enrollStudentDTO.IndexNumber }, enroll));
}
public IActionResult EnrollStudent(EnrollStudentDTO req)
{
return(service.enrollStudent(req));
}
public EnrollmentDTO EnrollStudent(EnrollStudentDTO enrollStudentDTO)
{
string connectionString = "Data Source=db-mssql;Initial Catalog=s17417;Integrated Security=True";
EnrollmentDTO enrollment;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand())
{
int IdStudy;
connection.Open();
var transaction = connection.BeginTransaction();
try
{
// enrollment = new EnrollmentDTO();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = "Select IdStudy from Studies where name=@Name";
command.Parameters.AddWithValue("Name", enrollStudentDTO.Studies);
var reader = command.ExecuteReader();
reader.Read();
if (!reader.HasRows)
{
return(null);
}
IdStudy = reader.GetInt32(reader.GetOrdinal("IdStudy"));
reader.Close();
/*enrollment.Semester = 1;
* enrollment.IdEnrollment = 1;
* enrollment.IdStudy = 1;
* enrollment.StarDate = DateTime.Now.ToString();
* return enrollment;*/
command.CommandText = "Select * from Enrollment where Semester=1 AND IdStudy=" + IdStudy;
reader = command.ExecuteReader();
reader.Read();
enrollment = new EnrollmentDTO();
if (reader.HasRows)
{
enrollment.IdEnrollment = reader.GetInt32(reader.GetOrdinal("IdEnrollment"));
enrollment.Semester = reader.GetInt32(reader.GetOrdinal("Semester"));
enrollment.StarDate = reader["StartDate"].ToString();
enrollment.IdStudy = reader.GetInt32(reader.GetOrdinal("IdEnrollment"));
reader.Close();
}
else
{
enrollment.IdStudy = IdStudy;
enrollment.Semester = 1;
enrollment.StarDate = DateTime.Now.ToString();
command.CommandText = "insert into Enrollment (idEnrollment, Semester,IdStudy,StartDate)" +
"values((Select Max(IdEnrollment) + 1 from Enrollment)," + enrollment.Semester + "," + enrollment.IdStudy + "," + enrollment.StarDate + ")";
command.ExecuteNonQuery();
command.CommandText = "Select IdEnrollment,StartDate from Enrollment where " + enrollment.Semester + " AND IdStudy=" + enrollment.IdStudy;
//reader = command.ExecuteReader();
reader.Read();
enrollment.IdEnrollment = reader.GetInt32(reader.GetOrdinal("IdEnrollment"));
enrollment.StarDate = reader["StartDate"].ToString();
reader.Close();
}
command.CommandText = "select 1 from Student where IndexNumber=@IndexNumber";
command.Parameters.AddWithValue("IndexNumber", enrollStudentDTO.IndexNumber);
reader = command.ExecuteReader();
reader.Read();
if (reader.HasRows)
{
reader.Close();
transaction.Rollback();
return(null);
}
reader.Close();
command.CommandText = "Insert into Student(IndexNumber, FirstName, LastName, BirthDate, IdEnrollment)" +
"values(@IndexNumber, @FirstName,@LastName,@BirthDate," + enrollment.IdEnrollment + ")";
command.Parameters.AddWithValue("FirstName", enrollStudentDTO.FirstName);
command.Parameters.AddWithValue("LastName", enrollStudentDTO.LastName);
command.Parameters.AddWithValue("BirthDate", enrollStudentDTO.BirthDate);
command.ExecuteNonQuery();
} catch (SqlException e)
{
transaction.Rollback();
return(null);
}
transaction.Commit();
return(enrollment);
}
}
