public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand("PromoteStudents", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@Semester", request.Semester));
command.Parameters.Add(new SqlParameter("@Studies", request.Studies));
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
var response = new PromoteStudentsResponse()
{
IdEnrollment = int.Parse(reader["IdEnrollment"].ToString()),
Semester = int.Parse(reader["Semester"].ToString()),
Study = reader["Name"].ToString(),
StartDate = DateTime.Parse(reader["StartDate"].ToString())
};
reader.Close();
return(response);
}
reader.Close();
}
return(null);
}
public IActionResult PromoteStudent(PromoteStudentsRequest request)
{
var enrollment = _dbService.GetLatestEnrollment(request.Studies, request.Semester);
if (enrollment == null)
{
return(NotFound("Enrollment does not exist"));
}
Enrollment newEnrollment;
try
{
newEnrollment = _dbService.PromoteStudents(enrollment.Course, enrollment.Semester);
}
catch (Exception e)
{
return(BadRequest(e.Message));
}
var response = new PromoteStudentsResponse
{
IdEnrollment = newEnrollment.IdEnrollment,
Course = request.Studies,
Semester = newEnrollment.Semester,
};
return(Created("", response));
}
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request)
{
PromoteStudentsResponse response = null;
using (SqlConnection con = new SqlConnection(ConString))
using (SqlCommand com = new SqlCommand())
{
com.Connection = con;
com.CommandType = System.Data.CommandType.StoredProcedure;
com.CommandText = "PromoteStudents";
com.Parameters.AddWithValue("@Studies", request.Studies);
com.Parameters.AddWithValue("@Semester", request.Semester);
com.Parameters.Add("@IdEnrollment", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.Output;
com.Parameters.Add("@IdStudies", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.Output;
com.Parameters.Add("@StartDate", System.Data.SqlDbType.Date).Direction = System.Data.ParameterDirection.Output;
con.Open();
com.ExecuteNonQuery();
response = new PromoteStudentsResponse();
response.IdEnrollment = (int)com.Parameters["@IdEnrollment"].Value;
response.Semester = request.Semester + 1;
response.IdStudy = (int)com.Parameters["@IdStudies"].Value;
response.StartDate = (DateTime)com.Parameters["@StartDate"].Value;
}
return(response);
}
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request)
{
var study = _context.Studies.FirstOrDefault(s => s.Name == request.Name);
if (study == null)
{
throw new StudiesNotFound();
}
var idStudy = _context.Studies.FirstOrDefault(s => s.Name == request.Name).IdStudy;
var enrollment = _context.Enrollment.Where(en => en.Semester == request.Semester &&
en.IdStudy == idStudy)
.FirstOrDefault();
if (enrollment == null)
{
throw new NoStudentsToPromote();
}
var enrollments = _context.Enrollment.Where(enr => enr.Semester == request.Semester)
.ToList();
enrollments.ForEach(e => e.Semester = request.Semester + 1);
var response = new PromoteStudentsResponse()
{
Semester = request.Semester + 1,
Name = request.Name
};
_context.SaveChanges();
return(response);
}
public PromoteStudentsResponse PromoteStudents(PromoteStudentRequest request)
{
PromoteStudentsResponse response = new PromoteStudentsResponse();
List <Student> listOfSt = new List <Student>();
var studies = _dbContext.Studies
.Where(s => s.Name.Equals(request.Studies))
.Single();
var OldEnrollment = _dbContext.Enrollment
.Where(e => e.IdStudy == studies.IdStudy && e.Semester == request.Semester)
.Single();
var enrollment = _dbContext.Enrollment
.Where(e => e.IdStudy == studies.IdStudy && e.Semester == request.Semester + 1)
.SingleOrDefault();
int idEnrollment;
if (enrollment == null)
{
idEnrollment = _dbContext.Enrollment.Count() + 1;
var enr = new Enrollment
{
IdEnrollment = idEnrollment,
Semester = request.Semester + 1,
IdStudy = studies.IdStudy,
StartDate = DateTime.Now
};
_dbContext.Enrollment.Add(enr);
_dbContext.SaveChanges();
}
else
{
idEnrollment = enrollment.IdEnrollment;
}
var students = _dbContext.Student
.Where(s => s.IdEnrollment == OldEnrollment.IdEnrollment)
.ToList();
foreach (Student s in students)
{
s.IdEnrollment = idEnrollment;
_dbContext.SaveChanges();
var s1 = new Student
{
IndexNumber = s.IndexNumber,
FirstName = s.FirstName,
LastName = s.LastName,
BirthDate = s.BirthDate,
IdEnrollment = idEnrollment,
};
listOfSt.Add(s1);
}
response.Students = listOfSt;
return(response);
}
public PromoteStudentsResponse PromoteStudent(PromoteStudentsRequest request)
{
var response = new PromoteStudentsResponse();
using (var con = new SqlConnection(ConString))
{
using (var com = new SqlCommand())
{
com.Connection = con;
con.Open();
var transaction = con.BeginTransaction();
com.Transaction = transaction;
//1. Sprawdzam czy w tabeli enrollment istnieje wpis o podanej wartości Studies i Semester, W przeciwnym razie zwracam kod 404 Not Found
com.CommandText = "SELECT * FROM Enrollment" +
" INNER JOIN Studies" +
" ON Studies.IdStudy = Enrollment.IdStudy" +
" WHERE Enrollment.Semester = @semester" +
" AND Studies.Name = @studies";
com.Parameters.AddWithValue("semester", request.Semester);
com.Parameters.AddWithValue("studies", request.Studies);
var dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
response.Status = 404;
response.Message = "Nie istnieje wpis Studies i Semester o podanej wartości";
return(response);
}
dr.Close();
// Jeżeli wszystko poszło dobrze uruchamiam procedurę składową
com.CommandText = "promoteStudents";
com.CommandType = CommandType.StoredProcedure;
dr = com.ExecuteReader();
if (dr.Read())
{
var enrollment = new Enrollment();
enrollment.IdEnrollment = (int)dr["IdEnrollment"];
enrollment.Semester = (int)dr["Semester"];
enrollment.IdStudy = (int)dr["IdStudy"];
enrollment.StartDate = (DateTime)dr["StartDate"];
response.enrollment = enrollment;
response.Status = 201;
//response.Message = "Ok";
dr.Close();
}
}
}
return(response);
}
public IActionResult PromoteStudent(PromoteStudentsRequest promotion)
{
// _service.PromoteStudent(promotion);
PromoteStudentsResponse resp = new PromoteStudentsResponse();
return(CreatedAtRoute(new RouteValues(), _service.PromoteStudent(promotion)));
}
public Response PromoteStudents(PromoteStudentsRequest request)
{
using (var con = new SqlConnection(ConString))
using (var com = new SqlCommand())
{
com.Connection = con;
con.Open();
var tran = con.BeginTransaction();
com.Transaction = tran;
try
{
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())
{
dr.Close();
tran.Rollback();
return(new Response("404", "Wpis dla " +
request.Studies + " semestr " + request.Semester +
" nie istnieje", null));
}
dr.Close();
com.Parameters.Clear();
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "dbo.PromoteStudents";
//kod tworzący procedurę PromoteStudents poniżej
com.Parameters.Add(new SqlParameter("@Studies", request.Studies));
com.Parameters.Add(new SqlParameter("@Semester", request.Semester));
com.ExecuteNonQuery();
tran.Commit();
var enrollment = new PromoteStudentsResponse();
enrollment.Studies = request.Studies;
enrollment.Semester = (int)request.Semester + 1;
return(new Response("201", "Promocja studentow ukonczona", enrollment));
}
catch (SqlException exc)
{
tran.Rollback();
}
}
return(new Response("400", null, null));
}
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request)
{
using (var con = new SqlConnection("Data Source=db-mssql;Initial Catalog=s16985;Integrated Security=True"))
using (var com = new SqlCommand())
{
var response = new PromoteStudentsResponse();
Object idEnrollment;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
var trans = con.BeginTransaction();
com.Connection = con;
com.Transaction = trans;
try
{
com.Parameters.Clear();
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "PromoteStudents";
com.Parameters.AddWithValue("Studies", request.Studies);
com.Parameters.AddWithValue("Semester", request.Semester);
var returnValue = com.Parameters.Add("@ReturnValue", SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
com.ExecuteNonQuery();
if ((int)returnValue.Value == 0)
{
response.Message = "Podany semestr nie istnieje!";
return(response);
}
else
{
idEnrollment = returnValue.Value;
}
}
catch (SqlException e)
{
trans.Rollback();
response.Message = e.Message;
return(response);
}
trans.Commit();
response.IdEnrollment = (int)idEnrollment;
response.Message = "Ok";
return(response);
}
}
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request)
{
var response = new PromoteStudentsResponse();
if (!_context
.Enrollment
.Where(e => e.IdStudyNavigation.Name == request.Studies && e.Semester == request.Semester)
.Any())
{
response.Message = "NO_SUCH_ENROLLMENT";
return(response);
}
var oldEnrollment = _context
.Enrollment
.Where(e => e.IdStudyNavigation.Name == request.Studies && e.Semester == request.Semester)
.Include(e => e.Student)
.Single();
Enrollment newEnrollment = null;
if (_context
.Enrollment
.Where(e => e.IdStudyNavigation.Name == request.Studies && e.Semester == request.Semester + 1)
.Any())
{
newEnrollment = _context
.Enrollment
.Where(e => e.IdStudyNavigation.Name == request.Studies && e.Semester == request.Semester + 1)
.Single();
}
if (newEnrollment == null)
{
newEnrollment = new Enrollment
{
Semester = request.Semester + 1,
IdStudyNavigation = _context.Studies.Where(e => e.Name == request.Studies).Single(),
StartDate = new DateTime(),
};
}
foreach (Student student in oldEnrollment.Student)
{
student.IdEnrollmentNavigation = newEnrollment;
}
_context.SaveChanges();
response.IdEnrollment = newEnrollment.IdEnrollment;
response.Message = "OK";
return(response);
}
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request)
{
var response = new PromoteStudentsResponse();
response.setStatus(400, "Error");
var _studies = _context.Studies.Where(s => s.Name == request.Studies).FirstOrDefault();
if (_studies == null)
{
response.setStatus(404, "ERROR: studies do not exist");
return(response);
}
;
var _enrollment = _context.Enrollment
.Where(a => a.IdStudy == _studies.IdStudy && a.Semester == request.Semester)
.OrderByDescending(s => s.StartDate).FirstOrDefault();
if (_enrollment == null)
{
response.setStatus(404, "ERROR: semester and studies do not exist in database");
return(response);
}
;
Console.WriteLine("here:" + _enrollment.IdEnrollment);
var _enrollment_next = _context.Enrollment.Where(p => p.Semester == _enrollment.Semester + 1).FirstOrDefault();
if (_enrollment_next == null)
{
_enrollment_next = new Enrollment()
{
IdEnrollment = _context.Enrollment.Max(p => p.IdEnrollment) + 1,
Semester = _enrollment.Semester + 1,
IdStudy = _enrollment.IdStudy,
StartDate = DateTime.Now.Date
};
_context.Add(_enrollment_next);
_context.SaveChanges();
}
var _students = _context.Student.Where(s => s.IdEnrollment == _enrollment.IdEnrollment);
foreach (var s in _students)
{
s.IdEnrollment = _enrollment_next.IdEnrollment;
}
_context.SaveChanges();
response.Semester = _enrollment_next.Semester;
response.StartDate = _enrollment_next.StartDate.ToString();
response.StudiesName = _studies.Name;
response.setStatus(201, "Students are promoted on the next semester");
return(response);
}
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request)
{
var response = new PromoteStudentsResponse();
response.setStatus(400, "Unknown Error"); // domyślnie - błąd
var _studies = _context.Studies.Where(s => s.Name == request.Studies).FirstOrDefault();
if (_studies == null)
{
response.setStatus(404, "ERROR: nie znaleziono studiów");
return(response);
}
;
var _enrollment = _context.Enrollment
.Where(a => a.IdStudy == _studies.IdStudy && a.Semester == request.Semester)
.OrderByDescending(s => s.StartDate).FirstOrDefault();
if (_enrollment == null)
{
response.setStatus(404, "ERROR: nie znaleziono semestru i studiów");
return(response);
}
;
Console.WriteLine("tutaj:" + _enrollment.IdEnrollment);
var _enrollment_next = _context.Enrollment.Where(p => p.Semester == _enrollment.Semester + 1).FirstOrDefault();
if (_enrollment_next == null)
{
_enrollment_next = new Enrollment()
{
IdEnrollment = _context.Enrollment.Max(p => p.IdEnrollment) + 1,
Semester = _enrollment.Semester + 1,
IdStudy = _enrollment.IdStudy,
StartDate = DateTime.Now.Date
};
_context.Add(_enrollment_next);
_context.SaveChanges();
}
var _students = _context.Student.Where(s => s.IdEnrollment == _enrollment.IdEnrollment);
foreach (var s in _students)
{
s.IdEnrollment = _enrollment_next.IdEnrollment;
}
_context.SaveChanges();
response.Semester = _enrollment_next.Semester;
response.StartDate = _enrollment_next.StartDate.ToString();
response.StudiesName = _studies.Name;
response.setStatus(201, "Studenci zostali promowani na następny semestr");
return(response);
}
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request)
{
var db = new s18985Context();
var response = new PromoteStudentsResponse();
var t = db.Studies.Where(x => x.Name == request.Studies).Select(x => x.IdStudy).First();
var e = db.Enrollment.Where(x => x.IdStudy == t && x.Semester == request.Semester).FirstOrDefault();
if (e == null)
{
return(null);
}
var studs = db.Student.Where(x => x.IdEnrollment == e.IdEnrollment).ToList();
int nextSemester = request.Semester + 1;
e = db.Enrollment.Where(x => x.IdStudy == t && x.Semester == nextSemester).FirstOrDefault();
int f;
if (e == null)
{
var max = (db.Enrollment.Max(x => x.IdEnrollment)) + 1;
var enroll = new Enrollment
{
IdEnrollment = max,
Semester = nextSemester,
IdStudy = t,
StartDate = DateTime.Now
};
db.Enrollment.Add(enroll);
f = max;
}
else
{
f = e.IdEnrollment;
}
foreach (var s in studs)
{
s.IdEnrollment = f;
db.Attach(s);
db.Entry(s).Property("IdEnrollment").IsModified = true;
db.SaveChanges();
}
response.IdEnrollment = f;
response.Semester = nextSemester;
response.Studies = request.Studies;
response.StartDate = DateTime.Now;
return(response);
}
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request)
{
var response = new PromoteStudentsResponse();
response.setStatus(400, "Unknown Error"); // domyślnie - błąd
using (var connection = new SqlConnection(CONN_STR))
{
connection.Open();
var command = connection.CreateCommand();
var transaction = connection.BeginTransaction();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = "select e.IdEnrollment from dbo.Enrollment e inner join dbo.Studies s on e.IdStudy = s.IdStudy where s.Name = @studyName and e.Semester = @semesterNumber";
command.Parameters.AddWithValue("studyName", request.Studies);
command.Parameters.AddWithValue("semesterNumber", request.Semester);
var reader = command.ExecuteReader();
if (!reader.Read())
{
reader.Close();
response.setStatus(404, "ERROR: nie znaleziono semestru i/lub studiów");
transaction.Rollback();
}
else
{
reader.Close();
using (SqlConnection conn = new SqlConnection(CONN_STR))
{
conn.Open();
SqlCommand cmd = new SqlCommand("Promote", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@studies", request.Studies));
cmd.Parameters.Add(new SqlParameter("@semester", request.Semester));
cmd.ExecuteReader();
conn.Close();
}
response.Semester = request.Semester + 1;
response.StartDate = DateTime.Now.ToString("dd.MM.yyyy");
response.StudiesName = request.Studies;
transaction.Commit();
response.setStatus(201, "Studenci zostali promowani na następny semestr");
}
}
return(response);
}
public IActionResult PromoteStudents(PromoteStudentsRequest request)
{
PromoteStudentsResponse resp = _service.PromoteStudents(request);
if (resp == null)
{
return(BadRequest("404 Not Found"));
}
else
{
return(Ok(resp));
}
}
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request)
{
var response = new PromoteStudentsResponse();
DateTime date = DateTime.Today;
int success;
const string ConString = "Data Source=db-mssql;Initial Catalog=s19391;Integrated Security=True";
using (var con = new SqlConnection(ConString))
using (var com = new SqlCommand())
{
com.Connection = con;
con.Open();
var tran = con.BeginTransaction();
com.CommandText = "execute PromoteStudents @study, @semester";
com.Parameters.AddWithValue("study", request.Studies);
com.Parameters.AddWithValue("semester", request.Semester);
com.Transaction = tran;
success = com.ExecuteNonQuery();
var nextSemestr = request.Semester + 1;
com.CommandText = "select IdEnrollment from enrollment where semester = @nextSemester and idstudy = (select idstudy from studies where name = @study)";
com.Parameters.AddWithValue("nextSemester", nextSemestr);
com.Transaction = tran;
var dr = com.ExecuteScalar();
response.IdEnrollment = (int)dr;
com.CommandText = "select StartDate from enrollment where semester = @nextSemester and idstudy = (select idstudy from studies where name = @study)";
com.Transaction = tran;
dr = com.ExecuteScalar();
response.StartDate = (DateTime)dr;
tran.Commit();
response.Studies = request.Studies;
response.Semester = request.Semester + 1;
if (success > 0)
{
return(response);
}
else
{
return(null);
}
}
}
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request)
{
PromoteStudentsResponse response;
DateTime now = DateTime.Now;
var checkStudies = _context.Studies
.Where(s => s.Name == request.Studies)
.FirstOrDefault();
if (checkStudies == null)
{
throw new ArgumentException("Podane studia nie istnieja");
}
var studies = _context.Enrollment
.Join(_context.Studies,
enr => enr.IdStudy,
stu => stu.IdStudy,
(enr, stu) => new
{
enr,
stu
})
.Where(s => s.stu.Name == request.Studies &&
s.enr.Semester == request.Semester)
.FirstOrDefault();
if (studies == default)
{
throw new ArgumentException("Wpis z podanym semestrem nie istnieje w bazie");
}
var result = _context.Enrollment
.FromSqlRaw("EXEC [dbo].[PromoteStudents] {0}, {1}", request.Studies, request.Semester)
.AsEnumerable()
.FirstOrDefault();
if (result != null)
{
response = new PromoteStudentsResponse()
{
IdEnrollment = result.IdEnrollment,
IdStudy = result.IdStudy,
Semester = result.Semester,
StartDate = result.StartDate
};
return(response);
}
return(null);
}
//zastosowanie SQLConnection oraz SQLCommand
/*
* public Enrollment PromoteStudents(int semester, string studiesName)
* {
* var enrollment = new Enrollment();
* using (var connection = new SqlConnection("Data Source=10.1.1.36; Initial Catalog=s8346;Integrated Security=True"))
* {
* using (var command = new SqlCommand())
* {
* command.Connection = connection;
* connection.Open();
*
* command.CommandText = "SELECT semester FROM Enrollment inner join Studies on Enrollment.IdStudy=Studies.idStudy where Enrollment.Semester = @semester AND Studies.name = (SELECT name from Studies where Name = @name)";
* command.Parameters.AddWithValue("name", studiesName);
* command.Parameters.AddWithValue("semester", semester);
*
* var SqlDataReader = command.ExecuteReader();
*
* if (!SqlDataReader.Read())
* {
* //return NotFound();
* }
* SqlDataReader.Close();
* command.Parameters.Clear();
*
* command.CommandText = "PromoteStudents";
* command.CommandType = System.Data.CommandType.StoredProcedure;
* command.Parameters.AddWithValue("Studies", studiesName);
* command.Parameters.AddWithValue("Semester", semester);
*
* SqlDataReader = command.ExecuteReader();
* if (SqlDataReader.Read())
* {
* enrollment.IdEnrollment = int.Parse(SqlDataReader["IdEnrollment"].ToString());
* enrollment.IdStudy = int.Parse(SqlDataReader["IdStudy"].ToString());
* enrollment.Semester = int.Parse(SqlDataReader["Semester"].ToString());
* enrollment.StartDate = DateTime.Parse(SqlDataReader["StartDate"].ToString());
* }
* }
* }
* return enrollment;
* }
* }*/
//zastosowanie EF
public PromoteStudentsResponse PromoteStudents(int semester, string studies)
{
using (var transaction = _dbContext.Database.BeginTransaction())
{
int idStudy = _dbContext.Studies
.Where(st => st.Name == studies)
.Select(st => st.IdStudy).SingleOrDefault();
Enrollment enrollment = _dbContext.Enrollment.FirstOrDefault(e => e.IdStudy == idStudy && e.Semester == semester);
if (enrollment == null)
{
return(null);
}
int oldIdEnrollment = enrollment.IdEnrollment;
enrollment = _dbContext.Enrollment.FirstOrDefault(e => e.IdStudy == idStudy && e.Semester == semester + 1);
if (enrollment == null)
{
int maxId = _dbContext.Enrollment.Max(e => e.IdEnrollment);
enrollment = new Enrollment();
enrollment.IdEnrollment = maxId + 1;
enrollment.Semester = semester + 1;
enrollment.IdStudy = idStudy;
enrollment.StartDate = DateTime.Now;
_dbContext.Enrollment.Add(enrollment);
_dbContext.SaveChanges();
}
var students = _dbContext.Student.Where(s => s.IdEnrollment == oldIdEnrollment).ToList();
foreach (Student student in students)
{
student.IdEnrollment = enrollment.IdEnrollment;
}
_dbContext.SaveChanges();
transaction.Commit();
var response = new PromoteStudentsResponse()
{
Study = studies,
NewIdStudy = enrollment.IdStudy,
NewSemester = enrollment.Semester
};
return(response);
}
}
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request)
{
var psr = new PromoteStudentsResponse();
using (var con = new SqlConnection(Program.GetConnectionString()))
using (var com = new SqlCommand())
{
com.Connection = con;
con.Open();
var tran = con.BeginTransaction();
try
{
com.CommandText = "EXEC PROMOTESTUDENTS @STUDIES = @studies, @SEMESTER = @semester";
com.Parameters.AddWithValue("studies", request.Studies);
com.Parameters.AddWithValue("semester", request.Semester);
com.Transaction = tran;
var dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
tran.Rollback();
throw new Exception("Procedura nie powiodła się");
}
else
{
psr.IdEnrollment = (int)dr["IdEnrollment"];
psr.IdStudy = (int)dr["IdStudy"];
psr.Semester = (int)dr["Semester"];
psr.StartDate = (DateTime)dr["StartDate"];
}
dr.Close();
tran.Commit();
}
catch (SqlException exc)
{
tran.Rollback();
throw new Exception("Operacja nie przebiegła pomyślnie");
}
return(psr);
}
}
public IActionResult PromoteStudent(PromoteStudentsRequest req)
{
var arePromoted = _service.PromoteStudents(req);
if (!arePromoted)
{
return(StatusCode(500));
}
var response = new PromoteStudentsResponse()
{
Semester = req.Semester + 1,
Studies = req.Studies
};
return(Ok(response));
}
public PromoteStudentsResponse PromoteStudents(PromoteStudentRequest req)
{
Enrollment respEnrollment = new Enrollment();
var resp = new PromoteStudentsResponse();
SqlCommand com = new SqlCommand();
using (SqlConnection con = new SqlConnection(SqlConn))
{
con.Open();
var transaction = con.BeginTransaction();
com.Connection = con;
com.Transaction = transaction;
com.CommandText = "select e.IdEnrollment from dbo.Enrollment e inner join dbo.Studies s on e.IdStudy = s.IdStudy where s.Name = @studyName and e.Semester = @semesterNumber";
com.Parameters.AddWithValue("@semester", req.Semester);
com.Parameters.AddWithValue("@studies", req.Studies);
var dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
return(null);
}
else
{
dr.Close();
using (SqlConnection conn = new SqlConnection(SqlConn))
{
conn.Open();
SqlCommand cmd = new SqlCommand("Promote", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@studies", req.Studies));
cmd.Parameters.Add(new SqlParameter("@semester", req.Semester));
cmd.ExecuteReader();
conn.Close();
}
resp.Semester = req.Semester + 1;
resp.StartDate = DateTime.Now.ToString("dd.MM.yyyy");
resp.StudiesName = req.Studies;
transaction.Commit();
}
}
return(resp);
}
public PromoteStudentsResponse PromoteStudents(PromoteStudentRequest request)
{
String test = "0 ";
var result = new PromoteStudentsResponse();
//check if Enrollment exists
using (var client = new SqlConnection(CONNECTION_STRING))
using (var command = new SqlCommand("PromoteStudents", client)
{
CommandType = System.Data.CommandType.StoredProcedure
})
{
test += "1 ";
client.Open();
var tran = client.BeginTransaction();
command.Transaction = tran;
command.Parameters.AddWithValue("Semester", request.Semester);
command.Parameters.AddWithValue("StudiesName", request.Studies);
var reader = command.ExecuteReader();
try
{
test += "2 ";
//create response
if (reader.Read())
{
result.IdEnrollment = int.Parse(reader["IdEnrollment"].ToString());
result.IdStudy = int.Parse(reader["IdStudy"].ToString());
result.Semester = int.Parse(reader["Semester"].ToString());
result.StartDate = DateTime.Parse(reader["StartDate"].ToString());
}
test += "3 ";
tran.Commit();
client.Close();
}catch (Exception e)
{
test += "Roll " + e;
reader.Close();
tran.Rollback();
return(null);
}
}
test += "end ";
return(result);
}
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request)
{
if (request.Studies == null || request.Semester == 0)
{
throw new ArgumentNullException("Incorrect request");
}
PromoteStudentsResponse response = null;
Enrollment respEnrollment = new Enrollment();
using (SqlConnection conn = new SqlConnection(SqlConn))
{
conn.Open();
SqlCommand cmd = new SqlCommand("PromoteStudents", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@studies", request.Studies));
cmd.Parameters.Add(new SqlParameter("@semester", request.Semester));
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (!dr.Read())
{
cmd.Dispose();
throw new ArgumentException("Nothing to be read by SqlDataReader");
}
response = new PromoteStudentsResponse();
response.Enrollment = respEnrollment;
respEnrollment.IdEnrollment = (int)dr["IdEnrollment"];
respEnrollment.Semester = (int)dr["Semester"];
respEnrollment.IdStudy = (int)dr["IdStudy"];
respEnrollment.StartDate = dr["StartDate"].ToString();
}
cmd.Dispose();
}
return(response);
}
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request)
{
PromoteStudentsResponse response;
using (SqlConnection con = new SqlConnection(ConString))
using (SqlCommand com = new SqlCommand())
{
com.Connection = con;
con.Open();
var tran = con.BeginTransaction();
com.Transaction = tran;
try
{
com.CommandText = "SELECT IdStudy FROM Studies WHERE name=@name";
com.Parameters.AddWithValue("name", request.Studies);
var dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
tran.Rollback();
throw new ArgumentException("Podane studia nie istnieja");
}
dr.Close();
com.CommandText = "SELECT IdEnrollment FROM Enrollment, Studies " +
"WHERE Enrollment.IdStudy = Studies.IdStudy " +
"AND Studies.Name = @name " +
"AND Enrollment.Semester = @semester ";
com.Parameters.AddWithValue("semester", request.Semester);
com.Transaction = tran;
dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
tran.Rollback();
throw new ArgumentException("Wpis z podanym semestrem nie istnieje w bazie");
}
dr.Close();
com.Parameters.Clear();
com.CommandText = "EXEC [dbo].[PromoteStudents] @studies, @semester";
com.Parameters.AddWithValue("studies", request.Studies);
com.Parameters.AddWithValue("semester", request.Semester);
com.ExecuteNonQuery();
com.CommandText = "SELECT * FROM enrollment " +
"WHERE idStudy = (SELECT idStudy FROM Studies WHERE name = @studiesName) " +
"AND semester = @newSemester ";
com.Parameters.AddWithValue("studiesName", request.Studies);
com.Parameters.AddWithValue("newSemester", request.Semester + 1);
dr = com.ExecuteReader();
if (dr.Read())
{
response = new PromoteStudentsResponse()
{
IdEnrollment = (int)dr["IdEnrollment"],
IdStudy = (int)dr["IdStudy"],
Semester = (int)dr["Semester"],
StartDate = DateTime.Now
};
dr.Close();
tran.Commit();
return(response);
}
else
{
throw new ArgumentException("Problem przy tworzeniu odpowiedzi");
}
}
catch (SqlException exc)
{
tran.Rollback();
throw new ArgumentException(exc.Message);
}
}
}
public PromoteStudentsResponse PromoteStudent(PromoteStudentsRequest promotion)
{
var pr = new PromoteStudentsRequest();
pr.Studies = promotion.Studies;
pr.Semester = promotion.Semester;
using (var client = new SqlConnection(ConString))
using (var com = new SqlCommand())
{
com.Connection = client;
client.Open();
var tran = client.BeginTransaction();
com.Transaction = tran;
PromoteStudentsResponse resp = new PromoteStudentsResponse();
com.CommandText = "EXEC PromoteStudents '" + pr.Studies + "', " + pr.Semester + " WITH RESULT SETS(( IdEnrollment INT,IDStudy INT,Semester INT,StartDate DateTime)) ";
/*used procedure:
* CREATE PROCEDURE PromoteStudents @Studies NVARCHAR(100), @Semester INT
* AS
* BEGIN
* DECLARE @IdStudies INT = (SELECT IdStudy FROM Studies WHERE NAme=@Studies);
* IF @IdStudies IS NULL
* BEGIN
* RETURN;
*
* END
*
* DECLARE @StartDate DATETIME = (SELECT MAX(startdate) FROM enrollment where IdStudy=@IdStudies and Semester =@Semester)
*
* DECLARE @IdEnrollment INT = (select IdEnrollment from enrollment where IdStudy=@IdStudies and Semester =@Semester and startdate = @StartDate)
*
* IF @IdEnrollment IS NULL
* BEGIN
* RETURN;
*
* END
*
* DECLARE @NextSemester INT = (select IdEnrollment from enrollment where IdStudy=@IdStudies and Semester =@semester+1)
* IF @NextSemester IS NULL
* BEGIN
* DECLARE @Max INT = (SELECT MAX(IdEnrollment) FROM enrollment)
* DECLARE @NextId INT = @Max +1
* insert into enrollment values(@NextId, @semester+1, @IdStudies, CURRENT_TIMESTAMP)
* UPDATE student SET IdEnrollment=@NextId WHERE IdEnrollment=@IdEnrollment
* SELECT * FROM enrollment WHERE IdEnrollment=@NextId;
* END
* ELSE
*
* UPDATE student SET IdEnrollment=@NextSemester WHERE IdEnrollment=@IdEnrollment
* SELECT * FROM enrollment WHERE IdEnrollment=@NextSemester;
* RETURN;
* END;
*
*/
var dr = com.ExecuteReader();
while (dr.Read())
{
resp.IdEnrollment = (int)dr["IdEnrollment"];
resp.idStudy = (int)dr["IDStudy"];
resp.Semester = (int)dr["Semester"];
resp.StartDate = (DateTime)dr["StartDate"];
}
dr.Close();
tran.Commit();
return(resp);
}
}
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request)
{
using (var connection = new SqlConnection(sqlConnectionString))
using (var command = new SqlCommand())
{
var response = new PromoteStudentsResponse();
Object idEnrollment;
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
var transaction = connection.BeginTransaction();
command.Connection = connection;
command.Transaction = transaction;
try
{
// 1. Sprawdzamy czy istnieje odpowiedni wpis w tabeli Enrollment
// UWAGA!!! Funkcjonalność przeniesiona do procedury składowanej
//command.CommandText = "" +
// "SELECT IdEnrollment FROM Enrollment e" +
// " JOIN Studies s ON e.IdStudy = s.IdStudy" +
// " WHERE s.Name=@Name AND e.Semester=@Semester";
//command.Parameters.AddWithValue("Name", request.Studies);
//command.Parameters.AddWithValue("Semester", request.Semester);
//idEnrollment = command.ExecuteScalar();
//if(idEnrollment == null)
//{
// response.Message = "There is no such studies in database";
// return response;
//}
// 2. Wywołanie procedury składowanej
command.Parameters.Clear();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "PromoteStudents";
command.Parameters.AddWithValue("Studies", request.Studies);
command.Parameters.AddWithValue("Semester", request.Semester);
var returnValue = command.Parameters.Add("@ReturnValue", SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
command.ExecuteNonQuery();
if ((int)returnValue.Value == 0)
{
response.Message = "There is no such semester in database";
return(response);
}
else
{
idEnrollment = returnValue.Value;
}
}
catch (SqlException e)
{
transaction.Rollback();
response.Message = e.Message;
return(response);
}
transaction.Commit();
response.IdEnrollment = (int)idEnrollment;
response.Message = "Ok";
return(response);
}
}
[Route("api/enrollments/promotions")] //zmień na student
public IActionResult PromoteStudents(PromoteStudentsRequest request)
{
var db = new _2019SBDContext();
// var res = db.Student.ToList();
var res = db.Studies
.Where((d) => d.Name == request.Studies).FirstOrDefault();
if (res == null)
{
return(BadRequest());
}
var res2 = db.Enrollment
.Where((d) => d.IdStudy == res.IdStudy && d.Semester == request.Semester).FirstOrDefault();
if (res == null)
{
return(BadRequest());
}
var res3 = db.Enrollment
.Where((d) => d.IdStudy == res.IdStudy && d.Semester == request.Semester + 1).FirstOrDefault();
PromoteStudentsResponse response = new PromoteStudentsResponse();
if (res3 == null)
{
//dodaj enrollment
var res4 = db.Enrollment
.OrderByDescending(d => d.IdEnrollment) //rosnąco czy malejąco?
.FirstOrDefault();
int maxIdEnrollment = res4.IdEnrollment;
response.StartDate = DateTime.Today;
var e = new Enrollment()
{
IdEnrollment = maxIdEnrollment + 1,
StartDate = response.StartDate,
Semester = request.Semester + 1,
IdStudy = res.IdStudy
};
db.Enrollment.Add(e);
response.IdEnrollment = maxIdEnrollment + 1;
}
else
{
response.IdEnrollment = res3.IdEnrollment;
}
// db.SaveChanges();
//teraz trzeba wszystkich studentów zrolować
var res5 = db.Student
.Where((d) => d.IdEnrollment == res2.IdEnrollment).ToList();
foreach (Student s in res5)
{
s.IdEnrollment = response.IdEnrollment;
}
db.SaveChanges();
return(Ok(response));
}
