public Enrollment PromoteStudents(PostPromotion p)
{
if (!(_context.Studies.Any(st => st.Name == p.Studies)))
{
return(null);
}
var study = _context.Studies.Where(stu => stu.Name == p.Studies).FirstOrDefault();
int idStudy = study.IdStudy;
if (!(_context.Enrollment.Any(st => st.Semester == p.Semester && st.IdStudy == idStudy)))
{
return(null);
}
//
if (_context.Enrollment.Any(en => en.IdStudy == idStudy && en.Semester == p.Semester + 1))
{
var newEnroll = _context.Enrollment.Where(en => en.IdStudy == idStudy && en.Semester == p.Semester + 1).FirstOrDefault();
var oldEnroll = _context.Enrollment.Where(en => en.IdStudy == idStudy && en.Semester == p.Semester).FirstOrDefault();
var oldStud = _context.Student.Where(st => st.IdEnrollment == oldEnroll.IdEnrollment);
var newStud = _context.Student.Where(st => st.IdEnrollment == newEnroll.IdEnrollment);
var set = newStud.Union(oldStud);
newEnroll.Student = set.ToHashSet();
_context.Attach(newEnroll);
_context.Entry(newEnroll).State = EntityState.Modified;
_context.SaveChanges();
}
else
{
var oldEnroll = _context.Enrollment.Where(en => en.IdStudy == idStudy && en.Semester == p.Semester).FirstOrDefault();
var oldStud = _context.Student.Where(st => st.IdEnrollment == oldEnroll.IdEnrollment);
var newEnroll = new Enrollment()
{
IdEnrollment = _context.Enrollment.Select(en => en.IdEnrollment).Max() + 1,
Semester = p.Semester + 1,
StartDate = DateTime.Now.Date,
Student = oldStud.ToHashSet()
};
study.Enrollment.Add(newEnroll);
_context.Add(newEnroll);
_context.Attach(study);
_context.Entry(study).State = EntityState.Modified;
_context.SaveChanges();
}
//_context.Database.ExecuteSqlRaw("exec PromoteStudents @nam, @sem", parameters: new { p.Studies, p.Semester });
var result = _context.Enrollment.Where(st => st.Semester == p.Semester + 1 && st.IdStudy == idStudy).FirstOrDefault();
return(result);
}
public IActionResult PostPromotion(PostPromotion req)
{
Enrollment en = _service.PostPromotion(req);
if (en == null)
{
return(BadRequest("Semestr i studia nie istnieja"));
}
else
{
return(Created("", en));
}
}
public Enrollment PostPromotion(PostPromotion req)
{
using (SqlConnection con = new SqlConnection(ConnString))
using (SqlCommand com = new SqlCommand())
{
com.Connection = con;
con.Open();
var trans = con.BeginTransaction();
com.Transaction = trans;
//try
//{
com.CommandText = "select IdEnrollment from Enrollment join Studies on Enrollment.IdStudy = Studies.IdStudy where Semester = @sem and Name = @nam";
com.Parameters.AddWithValue("sem", req.Semester);
com.Parameters.AddWithValue("nam", req.Studies);
var dr = com.ExecuteReader();
if (!dr.Read())
{
trans.Rollback();
return(null);
}
dr.Close();
//int enrollid = (int)dr["IdEnrollment"];
com.CommandText = "exec PromoteStudents @nam2, @sem2";
com.Parameters.AddWithValue("sem2", req.Semester);
com.Parameters.AddWithValue("nam2", req.Studies);
com.ExecuteNonQuery();
var en = new Enrollment();
com.CommandText = "select * from Enrollment join Studies on Enrollment.IdStudy = Studies.IdStudy where Semester = @sem3 +1 and Name = @nam3";
com.Parameters.AddWithValue("sem3", req.Semester);
com.Parameters.AddWithValue("nam3", req.Studies);
dr = com.ExecuteReader();
if (dr.Read())
{
en.IdEnrollment = (int)dr["IdEnrollment"];
en.Semester = (int)dr["Semester"];
en.IdStudy = (int)dr["IdStudy"];
en.StartDate = dr["StartDate"].ToString();
}
dr.Close();
trans.Commit();
return(en);
//}
//catch (SqlException ex)
//{
// trans.Rollback();
// return BadRequest(ex.Message);
//}
}
}