public Enrollment promoteStudents(EnrollPromoteRequest promoteRequest)
{
var study = _dbContext.Studies.Where(s => s.Name == promoteRequest.studies).FirstOrDefault();
var enrollment = _dbContext.Enrollment.Where(e => e.Semester == promoteRequest.semester)
.Where(e => e.IdStudy == study.IdStudy).First();
var newEnrollment = _dbContext.Enrollment.Where(e => e.Semester == promoteRequest.semester + 1)
.Where(e => e.IdStudy == study.IdStudy).First();
if (newEnrollment == null)
{
var maxIdEnrollment = _dbContext.Enrollment.Max(e => e.IdEnrollment);
_dbContext.Enrollment.Add(new Enrollment
{
IdEnrollment = maxIdEnrollment + 1,
IdStudy = study.IdStudy,
Semester = enrollment.Semester + 1,
StartDate = DateTime.Now
});
newEnrollment = _dbContext.Enrollment.Where(e => e.Semester == promoteRequest.semester + 1)
.Where(e => e.IdStudy == study.IdStudy).First();
}
var students = _dbContext.Student.Where(s => s.IdEnrollment == enrollment.IdEnrollment);
foreach (var s in students)
{
s.IdEnrollment = newEnrollment.IdEnrollment;
}
return(newEnrollment);
}
public ServicePromoteResult PromoteStudents(EnrollPromoteRequest model)
{
ServicePromoteResult result = new ServicePromoteResult
{
Success = true,
Message = string.Empty
};
using (var con = new SqlConnection(ConnectionString))
using (var cmd = new SqlCommand())
{
cmd.Connection = con;
con.Open();
try
{
cmd.CommandText = "select * from enrollment where IdStudy = (select IdStudy from studies where Name = @studyName) and Semester = @semester";
cmd.Parameters.AddWithValue("studyName", model.Studies);
cmd.Parameters.AddWithValue("semester", model.Semester);
var dr = cmd.ExecuteReader();
if (!dr.Read())
{
dr.Close();
result.Message = "Taki enrollment nie istnieje";
result.Success = false;
return(result);
}
dr.Close();
cmd.Parameters.Clear();
cmd.CommandText = "PromoteStudents1";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@studiesName", model.Studies);
cmd.Parameters.AddWithValue("@oldSemester", model.Semester);
cmd.ExecuteNonQuery();
result.Model = new EnrollPromoteResponse
{
Semester = model.Semester + 1,
Studies = model.Studies
};
}
catch (SqlException exc)
{
Console.WriteLine(exc.Message);
result.Message = "Błąd wewnętrzny.";
result.Success = false;
}
}
return(result);
}
public EnrollmentResponse PromoteStudents(EnrollPromoteRequest enrollPromoteRequest)
{
using (SqlConnection con =
new SqlConnection("Data Source=db-mssql;Initial Catalog=s19267;Integrated Security=True"))
using (SqlCommand com = new SqlCommand())
{
com.Connection = con;
con.Open();
var tran = con.BeginTransaction();
com.Transaction = tran;
//idStudy
com.CommandText = "select IdStudy from Studies where name=@studiesName";
com.Parameters.AddWithValue("studiesName", enrollPromoteRequest.studies);
var dr = com.ExecuteReader();
if (!dr.Read())
{
tran.Rollback();
return(null);
}
int idStudies = (int)dr["IdStudy"];
dr.Close();
//Enrollments
com.CommandText = "select IdEnrollment from Enrollment where Semester=@semester and IdStudy=@idStudy";
com.Parameters.AddWithValue("semester", enrollPromoteRequest.semester);
com.Parameters.AddWithValue("idStudy", idStudies);
dr = com.ExecuteReader();
if (!dr.Read())
{
tran.Rollback();
return(null);
}
dr.Close();
com.CommandText = " Exec promoteStudents @studiesName, @semester";
dr = com.ExecuteReader();
if (dr.Read())
{
EnrollmentResponse response = new EnrollmentResponse
{
Semester = (int)dr["Semester"],
IdEnrollment = (int)dr["IdEnrollment"],
IdStudy = (int)dr["IdStudy"],
StartDate = dr["StartDate"].ToString()
};
dr.Close();
tran.Commit();
return(response);
}
return(null);
}
}
public IActionResult PromoteStudent(EnrollPromoteRequest request)
{
try
{
var response = _service.PromoteStudent(request);
return(Created("", response));
} catch (Exception ex)
{
return(NotFound(ex));
}
}
public IActionResult Promote(EnrollPromoteRequest enrollPromoteRequest)
{
EnrollmentResponse enrollmentResponse = _studentsDbService.PromoteStudents(enrollPromoteRequest);
if (enrollmentResponse == null)
{
return(BadRequest("studia nie istnieją"));
}
else
{
return(CreatedAtAction("createStudent", enrollmentResponse));
}
}
public IActionResult EnrollPromote(EnrollPromoteRequest request)
{
EnrollPromoteResponse output = _service.Promote(request);
if (output != null)
{
return(Ok(output));
}
else
{
return(BadRequest("Bad request!!!"));
}
}
public IActionResult EnrollStudent(EnrollPromoteRequest request)
{
if (ModelState.IsValid == false)
{
IEnumerable <ModelError> allErrors = ModelState.Values.SelectMany(v => v.Errors);
return(BadRequest(allErrors));
}
var result = _service.PromoteStudents(request);
if (result.Success == false)
{
return(BadRequest(result.Message));
}
return(StatusCode(201, result.Model));
}
public EnrollPromoteResponse Promote(EnrollPromoteRequest request)
{
EnrollPromoteResponse response = null;
using (var connection = new SqlConnection("Data Source=db-mssql;Initial Catalog=s19088;Integrated Security=True"))
using (var command = new SqlCommand())
{
command.Connection = connection;
connection.Open();
var transaction = connection.BeginTransaction();
command.Transaction = transaction;
try
{
command.CommandText = "Execute PromoteStudents @nazwa, @semestr";
command.Parameters.AddWithValue("nazwa", request.StudiesName);
command.Parameters.AddWithValue("semestr", request.Semester);
var dr = command.ExecuteReader();//tu tak samo jak w poprzedniej metodzie
if (!dr.Read())
{
transaction.Rollback();
return(response);
}
int semestr = (int)dr["Semester"];
int idStudy = (int)dr["IdStudy"];
int IdEnrollment = (int)dr["IdEnrollment"];
System.DateTime date = (System.DateTime)dr["StartDate"];
transaction.Commit();
response = new EnrollPromoteResponse();
response.Semester = semestr;
response.StudiesName = request.StudiesName;
}
catch (SqlException ex)
{
transaction.Rollback();
}
return(response);
}
}
public IActionResult Promotions(EnrollPromoteRequest request)
{
int semestr = 0, idStudy = 0, IdEnrollment = 0;
System.DateTime date = System.DateTime.Now;
using (var connection = new SqlConnection("Data Source=db-mssql;Initial Catalog=s18705;Integrated Security=True"))
using (var command = new SqlCommand())
{
command.Connection = connection;
connection.Open();
var transaction = connection.BeginTransaction();
try
{
command.CommandText = "Execute PromoteStudents @nazwa, @semestr";
command.Parameters.AddWithValue("nazwa", request.StudiesName);
command.Parameters.AddWithValue("semestr", request.Semester);
var dr = command.ExecuteReader();
if (!dr.Read())
{
transaction.Rollback();
return NotFound();
}
semestr = (int)dr["Semester"];
idStudy = (int)dr["IdStudy"];
IdEnrollment = (int)dr["IdEnrollment"];
date = (System.DateTime)dr["StartDate"];
transaction.Commit();
}
catch (SqlException ex)
{
transaction.Rollback();
}
}
string output = "Semestr: " + semestr + ", idStudy: " + idStudy + ", IdEnrollment: " + IdEnrollment + ", Data: " + date;
return Ok(output);
}
public async Task <IActionResult> EnrollStudent(EnrollPromoteRequest request)
{
if (ModelState.IsValid == false)
{
IEnumerable <ModelError> allErrors = ModelState.Values.SelectMany(v => v.Errors);
return(BadRequest(allErrors));
}
var studies = await Context.Studies.FirstOrDefaultAsync(stu => stu.Name == request.Studies);
var enrolmentOld = await Context.Enrollment.FirstOrDefaultAsync(en => en.IdStudy == studies.IdStudy && en.Semester == request.Semester);
var enrollmentNew = await Context.Enrollment.FirstOrDefaultAsync(en => en.IdStudy == studies.IdStudy && en.Semester == request.Semester + 1);
if (enrollmentNew == null)
{
enrollmentNew = new Enrollment
{
Semester = request.Semester + 1,
IdStudy = Context.Studies.First(stu => stu.Name == request.Studies).IdStudy,
StartDate = DateTime.Now,
};
Context.Enrollment.Add(enrollmentNew);
}
var students = await Context.Student.Where(stud => stud.IdEnrollment == enrolmentOld.IdEnrollment).ToArrayAsync();
foreach (var student in students)
{
student.IdEnrollment = enrollmentNew.IdEnrollment;
}
Context.Student.UpdateRange(students);
if (await Context.SaveChangesAsync() == 0)
{
return(StatusCode(500, "Błąd podczas zapisu danych do bazy."));
}
return(StatusCode(201, enrollmentNew));
}
public EnrollPromoteResponse PromoteStudent(EnrollPromoteRequest request)
{
var response = new EnrollPromoteResponse();
using (var con = new SqlConnection(connection))
{
using (var com = new SqlCommand())
{
con.Open();
com.Connection = con;
com.CommandText = "SELECT * FROM Enrollment JOIN Studies ON enrollment.IdStudy=studies.IdStudy WHERE Name = @name AND Semester=@semester";
com.Parameters.AddWithValue("@name", request.Studies);
com.Parameters.AddWithValue("@semester", request.Semester);
var dr = com.ExecuteReader();
if (!dr.Read())
{
throw new NotImplementedException("Nie znaleziono danych");
}
dr.Close();
com.CommandType = System.Data.CommandType.StoredProcedure;
com.CommandText = "PromoteStudents";
com.Parameters.Clear();
com.Parameters.AddWithValue("@Studies", request.Studies);
com.Parameters.AddWithValue("@Semester", request.Semester);
com.ExecuteNonQuery();
}
}
response.Studies = request.Studies;
response.Semester = request.Semester + 1;
return(response);
}