public IActionResult EnrollStudent(StudentRequest studentRequest) { int idStudy; if (studentRequest.IndexNumber == null || studentRequest.FirstName == null || studentRequest.LastName == null || studentRequest.Birthdate == null || studentRequest.Studies == null) { return(NotFound("Nie pełne dane")); } ///////////// var com = new SqlCommand() { CommandText = "select s.IdStudy from Studies s where s.Name=@studies" }; com.Parameters.AddWithValue("studies", studentRequest.Studies); var result1 = dbservice.ExecuteSelect(com); if (result1.Count == 0) { return(BadRequest("Nie znaleziono kierunku")); } else { idStudy = (int)result1[0][0]; } ///////// com = new SqlCommand() { CommandText = "select * from Enrollment e JOIN Student s ON e.IdEnrollment=s.IdEnrollment where e.Semester=1 and e.IdStudy=@idStudy and IndexNumber=@indexNumber" }; com.Parameters.AddWithValue("idStudy", idStudy); com.Parameters.AddWithValue("indexNumber", studentRequest.IndexNumber); var result2 = dbservice.ExecuteSelect(com); if (result2.Count == 0) { //////// com = new SqlCommand() { CommandText = "select * from Student s where s.IndexNumber=@indexNumber" }; com.Parameters.AddWithValue("indexNumber", studentRequest.IndexNumber); if (dbservice.ExecuteSelect(com).Count == 0) { //////// com = new SqlCommand() { CommandText = "SELECT MAX(IdEnrollment) FROM Enrollment" }; int idEnrollment = ((int)dbservice.ExecuteSelect(com)[0][0]) + 1; var tran = dbservice.GetConnection().BeginTransaction(); //////// com = new SqlCommand() { CommandText = "INSERT INTO Enrollment(IdEnrollment, StartDate, IdStudy, Semester) VALUES (@idEnrollment, @startDate, @idStudy, @semester)" }; DateTime startDate = DateTime.Now; com.Parameters.AddWithValue("idEnrollment", idEnrollment); com.Parameters.AddWithValue("startDate", SqlDateTime.Parse(startDate.ToString("yyyy-MM-dd"))); com.Parameters.AddWithValue("idStudy", idStudy); com.Parameters.AddWithValue("semester", 1); dbservice.ExecuteInsert(com); ////////// com = new SqlCommand() { CommandText = "INSERT INTO dbo.Student(IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) VALUES (@indexNumber, @firstName, @lastName, @birthDate, @idEnrollment)" }; com.Parameters.AddWithValue("indexNumber", studentRequest.IndexNumber); com.Parameters.AddWithValue("firstName", studentRequest.FirstName); com.Parameters.AddWithValue("lastName", studentRequest.LastName); com.Parameters.AddWithValue("birthdate", studentRequest.Birthdate); com.Parameters.AddWithValue("idEnrollment", idEnrollment); dbservice.ExecuteInsert(com); Enrollment enrollment = new Enrollment(); enrollment.IdEnrollment = idEnrollment; enrollment.Semester = 1; enrollment.IdStudy = idStudy; enrollment.StartDate = startDate; tran.Commit(); return(Created("", enrollment)); } else { return(BadRequest("Indeks zajęty")); } } else { return(BadRequest("Taki wpis istnieje")); } }
public IActionResult EnrollStudent(StudentRequest studentRequest) { int idStudies; var command = new SqlCommand() { CommandText = "select s.IdStudy from Studies s where s.Name=@studies" }; command.Parameters.AddWithValue("studies", studentRequest.Studies); var queryResult = dbService.ExecuteSelect(command); if (queryResult.Count == 0) { return(BadRequest("No studies")); } else { idStudies = (int)queryResult[0][0]; } command = new SqlCommand() { CommandText = "select * from Enrollment e JOIN Student s ON e.IdEnrollment=s.IdEnrollment where e.Semester=1 and e.IdStudy=@idStudy and IndexNumber=@indexNumber" }; command.Parameters.AddWithValue("idStudies", idStudies); command.Parameters.AddWithValue("indexNumber", studentRequest.IndexNumber); queryResult = dbService.ExecuteSelect(command); if (queryResult.Count == 0) { command = new SqlCommand() { CommandText = "SELECT MAX(IdEnrollment) FROM Enrollment" }; int idEnrollment = ((int)dbService.ExecuteSelect(command)[0][0]) + 1; var transaction = dbService.GetConnection().BeginTransaction(); command = new SqlCommand() { CommandText = "INSERT INTO Enrollment(IdEnrollment, StartDate, IdStudy, Semester) VALUES(@idEnrollment, @startDate, @idStudy, @semester)" }; DateTime startDate = DateTime.Now; command.Parameters.AddWithValue("idEnrollment", idEnrollment); command.Parameters.AddWithValue("startDate", SqlDateTime.Parse(startDate.ToString("yyyy-MM-dd"))); command.Parameters.AddWithValue("idStudies", idStudies); command.Parameters.AddWithValue("semester", 1); dbService.ExecuteInsert(command); command = new SqlCommand() { CommandText = "INSERT INTO dbo.Student(IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) VALUES (@indexNumber, @firstName, @lastName, @birthDate, @idEnrollment)" }; command.Parameters.AddWithValue("indexNumber", studentRequest.IndexNumber); command.Parameters.AddWithValue("firstName", studentRequest.FirstName); command.Parameters.AddWithValue("lastName", studentRequest.LastName); command.Parameters.AddWithValue("birthDate", studentRequest.Birthdate); command.Parameters.AddWithValue("idEnrollment", idEnrollment); dbService.ExecuteInsert(command); Enrollment enrollment = new Enrollment(); enrollment.IdEnrollment = idEnrollment; enrollment.Semester = 1; enrollment.IdStudy = idStudies; enrollment.StartDate = startDate; transaction.Commit(); return(Created("", enrollment)); } else { return(BadRequest("Index unavailable")); } }