/*public Enrollment GetEnrollment(string indexNumber)
* {
* _context = new APBDContext();
* var student = _context.Student
* .First(x => x.IndexNumber == indexNumber);
* var enrollment = _context.Enrollment
* .First(x => x.IdEnrollment == student.IdEnrollment);
* return enrollment;
* }*/
public Enrollment EnrollStudent(EnrollStudentRequests request)
{
_context = new APBDContext();
Studies study;
try
{
study = _context.Studies
.First(x => x.Name.Equals(request.Studies));
}
catch
{
return(null);
}
Enrollment enrollment;
try
{
enrollment = _context.Enrollment
.Where(x => x.IdStudy.Equals(study.IdStudy))
.First(x => x.Semester == 1);
} catch
{
var newEnrollment = new Enrollment
{
IdEnrollment = _context.Enrollment
.Max(x => x.IdEnrollment) + 1,
Semester = 1,
IdStudy = study.IdStudy,
StartDate = DateTime.Today
};
_context.Enrollment.Add(newEnrollment);
enrollment = newEnrollment;
}
var idEnrollment = enrollment.IdEnrollment;
var student = new Student
{
IndexNumber = request.IndexNumber,
FirstName = request.FirstName,
LastName = request.LastName,
BirthDate = DateTime.ParseExact(request.BirthDate, "dd.MM.yyyy", CultureInfo.InvariantCulture),
IdEnrollment = idEnrollment,
Password = "******"
};
_context.Student.Add(student);
_context.SaveChanges();
return(enrollment);
}
public IActionResult EnrollStudents(EnrollStudentRequests newStudent)
{
try
{
var enrollment = _service.EnrollStudent(newStudent);
var result = new ObjectResult(enrollment);
result.StatusCode = (int)HttpStatusCode.Created;
return(result);
}
catch (InvalidOperationException invalidOperation)
{
return(BadRequest(invalidOperation.Message));
}
catch (Exception exception)
{
var result = new StatusCodeResult((int)HttpStatusCode.InternalServerError);
return(result);
}
}
// [Authorize(Roles = "employee")]
public IActionResult EnrollStudent(EnrollStudentRequests request)
{
var response = _dbService.EnrollStudent(request);
return(response == null?Ok(BadRequest("Błędny parametr")) : Ok(response));
}
public EnrollStudentResponse EnrollStudent(EnrollStudentRequests newStudent)
{
using (var connection = new SqlConnection("Data Source=db-mssql.pjwstk.edu.pl;Initial Catalog=s16578;Integrated Security=True"))
using (var command = connection.CreateCommand())
using (var transaction = new TransactionScope())
{
connection.Open();
command.CommandText = "SELECT * FROM Student WHERE IndexNumber = @index";
command.Parameters.AddWithValue("@index", newStudent.Index);
using (var dataReader = command.ExecuteReader())
{
if (dataReader.Read())
{
throw new InvalidOperationException("Student with this index already exists");
}
}
int idStudy;
int idEnrollment;
command.CommandText = "SELECT IdStudy FROM Studies WHERE name = @name";
command.Parameters.AddWithValue("@name", newStudent.Studies);
using (var dataReader = command.ExecuteReader())
{
if (!dataReader.Read())
{
throw new InvalidOperationException("Studies does not exist");
}
idStudy = (int)dataReader["IdStudy"];
}
command.CommandText = "IF NOT EXISTS(SELECT * FROM Enrollment WHERE Semester = 1 AND IdStudy = @IdStudy) BEGIN " +
"DECLARE @MaxId INT = (SELECT MAX(IdEnrollment)+1 FROM Enrollment)+1 " +
"INSERT INTO Enrollment (IdEnrollment, Semester, IdStudy, StartDate) " +
"VALUES ( @MaxId, 1, 1, CURRENT_TIMESTAMP) END " +
"SELECT IdEnrollment FROM Enrollment WHERE Semester = 1 AND IdStudy = @IdStudy";
command.Parameters.AddWithValue("@IdStudy", idStudy);
using (var dataReader = command.ExecuteReader())
{
dataReader.Read();
idEnrollment = (int)dataReader.GetInt32(0);
}
command.CommandText = "INSERT INTO Student (IndexNumber, FirstName, LastName, BirthDate, IdEnrollment)" +
"VALUES (@index, @firstname, @lastname, @birthDate, @IdEnrollment)";
command.Parameters.AddWithValue("@firstname", newStudent.FirstName);
command.Parameters.AddWithValue("@lastname", newStudent.LastName);
command.Parameters.AddWithValue("@birthDate", newStudent.BirthDate);
command.Parameters.AddWithValue("@IdEnrollment", idEnrollment);
command.ExecuteNonQuery();
var insertedData = new EnrollStudentResponse();
command.CommandText =
"SELECT IdEnrollment, Semester, IdStudy, StartDate FROM Enrollment WHERE IdEnrollment = @IdEnrollment";
using (var dataReader = command.ExecuteReader())
{
if (dataReader.Read())
{
insertedData.IdEnrollment = dataReader.GetInt32(0);
insertedData.IdStudy = dataReader.GetInt32(1);
insertedData.Semester = dataReader.GetInt32(2);
insertedData.StartDate = dataReader.GetDateTime(3);
}
else
{
// nie wczytuje
}
}
transaction.Complete();
return(insertedData);
}
}
public IActionResult EnrollStudent(EnrollStudentRequests request)
{
var response = new EnrollStudentResponse();
using (var con = new SqlConnection("Data Source=db-mssql;Initial Catalog=s19183;Integrated Security=True"))
{
using (var com = new SqlCommand())
{
com.Connection = con;
com.CommandText = "SELECT * FROM Studies WHERE Name = @Name";
com.Parameters.AddWithValue("Name", request.studies);
con.Open();
var tran = con.BeginTransaction();
com.Transaction = tran;
var dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
tran.Rollback();
return(BadRequest("Wrong studies, try again."));
}
var idStudy = (int)dr["IdStudy"];
dr.Close();
com.CommandText = "SELECT * FROM Enrollment WHERE Semester = 1 AND IdStudy = @idStudy";
var IdEnrollment = (int)dr["IdEnrollment"] + 1;
com.Parameters.AddWithValue("IdStudy", idStudy);
dr = com.ExecuteReader();
if (dr.Read())
{
dr.Close();
com.CommandText = "SELECT MAX(idEnrollment) AS 'idEnrollment' FROM Enrollment";
dr = com.ExecuteReader();
dr.Close();
DateTime StartDate = DateTime.Now;
com.CommandText = "INSERT INTO Enrollment(IdEnrollment, Semester, IdStudy, StartDate) VALUES (@IdEnrollemnt, 1, @IdStudy, @StartDate)";
com.Parameters.AddWithValue("IdEnrollment", IdEnrollment);
com.Parameters.AddWithValue("StartDate", StartDate);
com.ExecuteNonQuery();
}
dr.Close();
com.CommandText = "SELECT * FROM Student WHERE IndexNumber=@IndexNumber";
com.Parameters.AddWithValue("IndexNumber", request.IndexNumber);
dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
com.CommandText = "INSERT INTO Student(IndexNumber, FirstName, LastName, Birthdate, IdEnrollment) VALUES (@IndexNumber, @FirstName, @LastName, @BirthDate, @IdEnrollment)";
com.Parameters.AddWithValue("FirstName", request.FirstName);
com.Parameters.AddWithValue("LastName", request.LastName);
com.Parameters.AddWithValue("BirthDate", request.BirthDate);
com.Parameters.AddWithValue("IdEnrollment", IdEnrollment);
com.ExecuteNonQuery();
dr.Close();
}
else
{
dr.Close();
tran.Rollback();
return(BadRequest("Error"));
}
tran.Commit();
}
return(Ok("Success"));
}
}
public Enrollment EnrollStudent(EnrollStudentRequests request)
{
Enrollment enrollment = null;
using (var connection = new SqlConnection(ConnectionString))
using (var command = new SqlCommand())
{
command.Connection = connection;
connection.Open();
var tran = connection.BeginTransaction();
command.Transaction = tran;
try
{
command.CommandText = $"select IdStudy from Studies where name=@name";
command.Parameters.Clear();
command.Parameters.AddWithValue("@name", request.Studies);
var dr = command.ExecuteReader();
int idStudy;
if (!dr.HasRows)
{
dr.Close();
tran.Rollback();
return(null);
}
else
{
dr.Read();
idStudy = Convert.ToInt32(dr["IdStudy"]);
dr.Close();
}
var idEnrollment = 0;
var startDate = DateTime.Today;
command.CommandText = $"select IdEnrollment from Enrollment where IdStudy=@idStudy and Semester=1";
command.Parameters.Clear();
command.Parameters.AddWithValue("@idStudy", idStudy);
dr = command.ExecuteReader();
if (!dr.HasRows)
{
dr.Close();
command.CommandText = $"select max(IdEnrollment) from Enrollment";
dr = command.ExecuteReader();
if (dr.Read())
{
idEnrollment = Convert.ToInt32(dr[0]);
idEnrollment++;
}
dr.Close();
command.CommandText = $"insert into Enrollment(IdEnrollment, Semester, IdStudy, StartDate) VALUES(@idEnrollment, @semester, @idStudy, @startDate)";
command.Parameters.Clear();
command.Parameters.AddWithValue("@idEnrollment", idEnrollment);
command.Parameters.AddWithValue("@semester", 1);
command.Parameters.AddWithValue("@idStudy", idStudy);
command.Parameters.AddWithValue("@startDate", startDate);
command.ExecuteNonQuery();
}
else
{
if (dr.Read())
{
idEnrollment = Convert.ToInt32(dr[0]);
}
dr.Close();
}
command.CommandText = $"select FirstName from Student where IndexNumber=@indexNumber";
command.Parameters.Clear();
command.Parameters.AddWithValue("@indexNumber", request.IndexNumber);
dr = command.ExecuteReader();
if (dr.HasRows)
{
dr.Close();
tran.Rollback();
return(null);
}
dr.Close();
command.CommandText = $"insert into Student(IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) VALUES(@indexNumber, @firstName, @lastName, @birthDate, @idEnrollment)";
command.Parameters.Clear();
command.Parameters.AddWithValue("@indexNumber", request.IndexNumber);
command.Parameters.AddWithValue("@firstName", request.FirstName);
command.Parameters.AddWithValue("@lastName", request.LastName);
command.Parameters.AddWithValue("@birthDate", DateTime.ParseExact(request.BirthDate, "dd.MM.yyyy", CultureInfo.InvariantCulture));
command.Parameters.AddWithValue("@idEnrollment", idEnrollment);
command.ExecuteNonQuery();
command.CommandText = $"select StartDate from Enrollment where IdEnrollment=@idEnrollment";
command.Parameters.Clear();
command.Parameters.AddWithValue("@idEnrollment", idEnrollment);
dr = command.ExecuteReader();
if (dr.Read())
{
startDate = (DateTime)dr["StartDate"];
}
dr.Close();
enrollment = new Enrollment
{
IdEnrollment = idEnrollment,
Semester = 1,
IdStudy = idStudy,
StartDate = startDate
};
tran.Commit();
} catch (SqlException exc)
{
Console.WriteLine(exc);
tran.Rollback();
}
}
return(enrollment);
}
