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