/*public PromoteStudentResponse PromoteStudents(PromoteStudentRequest request)
* {
* PromoteStudentResponse response = new PromoteStudentResponse();
*
* using (var con = new SqlConnection(ConString))
* using (var com = new SqlCommand("PromoteStudents", con)
* {
* CommandType = CommandType.StoredProcedure
* })
* {
* com.Parameters.AddWithValue("@Studies", request.Studies);
* com.Parameters.AddWithValue("@semester", request.Semester);
*
* con.Open();
* com.ExecuteNonQuery();
* con.Close();
* response.message = "DONE";
*
* SqlCommand command = new SqlCommand();
* command.Connection = con;
* con.Open();
*
* int newSem = request.Semester + 1;
*
* command.CommandText = "SELECT IdStudy FROM Studies where Name=@name";
* command.Parameters.AddWithValue("name", request.Studies);
*
* var studs = command.ExecuteReader();
* if (!studs.Read())
* {
* response.message = "Podanych studiow nie ma w bazie";
* return response;
* }
* int idstudies = (int)studs["IdStudy"];
* studs.Close();
*
* Enrollment enrollment = new Enrollment();
*
* command.CommandText = "SELECT idEnrollment,idStudy,Semester,StartDate FROM Enrollment WHERE " +
* "idStudy=@idStudy AND Semester=@Semester";
* command.Parameters.AddWithValue("idStudy", idstudies);
* command.Parameters.AddWithValue("Semester", newSem);
*
* var enr = command.ExecuteReader();
* if (enr.Read())
* {
* enrollment.IdEnrollment = (int)enr["IdEnrollment"];
* enrollment.IdStudy = (int)enr["IdStudy"];
* enrollment.Semester = (int)enr["Semester"];
* enrollment.StartDate = (DateTime)enr["StartDate"];
* }
* enr.Close();
* response.enrollment = enrollment;
*
* return response;
* }
* }*/
public PromoteStudentResponse PromoteStudents(PromoteStudentRequest request)
{
PromoteStudentResponse response = new PromoteStudentResponse();
var db = new ModelsAuto.s16770Context();
var studies = db.Studies.Where(s => s.Name == request.Studies);
if (studies.Count() == 0)
{
response.message = "Podanych studiow nie ma w bazie";
return(response);
}
int idstudies = studies.First().IdStudy;
ModelsAuto.Enrollment enrollment = new ModelsAuto.Enrollment();
object[] parameters =
{
new SqlParameter("@Studies", request.Studies),
new SqlParameter("@Semester", request.Semester)
};
var promotion = db.Database.ExecuteSqlRaw("PromoteStudents @Studies, @Semester", parameters);
var enr = db.Enrollment.Where(e => e.IdStudy == idstudies)
.Where(e => e.Semester == request.Semester + 1);
int idEnr = enr.Single().IdEnrollment;
int idStu = enr.Single().IdStudy;
int sem = enr.Single().Semester;
DateTime sDate = enr.Single().StartDate;
enrollment.IdEnrollment = idEnr;
enrollment.IdStudy = idStu;
enrollment.Semester = sem;
enrollment.StartDate = sDate;
response.enrollment = enrollment;
return(response);
}
/*public EnrollStudentResponse EnrollStudent(EnrollStudentRequest request)
* {
* EnrollStudentResponse response = new EnrollStudentResponse();
*
* response.IndexNumber = request.IndexNumber;
* Enrollment e = new Enrollment();
*
* using (var con = new SqlConnection(ConString))
* using (var com = new SqlCommand())
* {
* com.Connection = con;
* con.Open();
*
* var transaction = con.BeginTransaction();
*
* try
* {
* com.CommandText = "SELECT IdStudy FROM Studies where Name=@name";
* com.Parameters.AddWithValue("name", request.Studies);
* com.Transaction = transaction;
*
* var studs = com.ExecuteReader();
* if (!studs.Read())
* {
* response.message = "Podanych studiow nie ma w bazie";
* return response;
* }
* int idstudies = (int)studs["IdStudy"];
* studs.Close();
*
* com.CommandText = "SELECT IdEnrollment FROM Enrollment WHERE IdEnrollment >= "
+ "(SELECT MAX(IdEnrollment) FROM Enrollment)";
+ var idenr = com.ExecuteReader();
+ if (!idenr.Read()){ }
+ int idEnroll = (int)idenr["IdEnrollment"] + 10;
+ idenr.Close();
+
+ com.CommandText = "SELECT idEnrollment, StartDate from Enrollment WHERE idStudy=@idStudy AND Semester=1" +
+ "ORDER BY StartDate";
+ com.Parameters.AddWithValue("idStudy", idstudies);
+
+ DateTime enrollDate;
+
+ var enrol = com.ExecuteReader();
+ if (!enrol.Read())
+ {
+ response.message = "Brak rozpoczetej rekrutacji";
+ enrollDate = DateTime.Now;
+ com.CommandText = "INSERT INTO Enrollment VALUES(@id, @Semester, @IdStud, @StartDate)";
+ com.Parameters.AddWithValue("id", idEnroll);
+ com.Parameters.AddWithValue("Semester", 1);
+ com.Parameters.AddWithValue("IdStud", idstudies);
+ com.Parameters.AddWithValue("StartDate", enrollDate);
+ enrol.Close();
+ com.ExecuteNonQuery();
+ }
+ else
+ {
+ idEnroll = (int)enrol["IdEnrollment"];
+ enrollDate = (DateTime)enrol["StartDate"];
+ enrol.Close();
+ }
+
+ e.IdEnrollment = idEnroll;
+ e.Semester = 1;
+ e.IdStudy = idstudies;
+ e.StartDate = enrollDate;
+
+ response.enrollment = e;
+
+ com.CommandText = "SELECT IndexNumber FROM Student WHERE IndexNumber=@indexNum";
+ com.Parameters.AddWithValue("indexNum", request.IndexNumber);
+
+ DateTime bDate = Convert.ToDateTime(request.Birthdate);
+ string formattedDate = bDate.ToString("yyyy-MM-dd HH:mm:ss.fff");
+
+ try
+ {
+ com.CommandText = "INSERT INTO Student(IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) VALUES " +
+ "(@index, @fName, @lName, @birthDate, @idEnrollment)";
+
+ com.Parameters.AddWithValue("index", request.IndexNumber);
+ com.Parameters.AddWithValue("fName", request.FirstName);
+ com.Parameters.AddWithValue("lName", request.LastName);
+ com.Parameters.AddWithValue("birthDate", formattedDate);
+ com.Parameters.AddWithValue("idEnrollment", idEnroll);
+
+ response.message = "DONE";
+ com.ExecuteNonQuery();
+
+ transaction.Commit();
+ }
+ catch(SqlException ex)
+ {
+ transaction.Rollback();
+ response.message = "Student o takim ID jest juz w bazie. " + ex.Message;
+ }
+ }
+ catch (SqlException exc)
+ {
+ transaction.Rollback();
+ response.message = exc.Message;
+ }
+ return response;
+ }
+ }*/
public EnrollStudentResponse EnrollStudent(EnrollStudentRequest request)
{
EnrollStudentResponse response = new EnrollStudentResponse();
ModelsAuto.Enrollment e = new ModelsAuto.Enrollment();
response.IndexNumber = request.IndexNumber;
var db = new ModelsAuto.s16770Context();
var studies = db.Studies.Where(s => s.Name == request.Studies);
if (studies.Count() == 0)
{
response.message = "Podanych studiow nie ma w bazie";
return(response);
}
int idstudies = studies.First().IdStudy;
var enrollment = db.Enrollment.Where(e => e.IdStudy == idstudies)
.Where(e => e.Semester == 1)
.OrderBy(e => e.StartDate);
DateTime enrollDate;
var newEnrollment = db.Enrollment.OrderBy(e => e.IdEnrollment).Last().IdEnrollment;
int newEnrollmentId = newEnrollment + 10;
if (enrollment.Count() == 0)
{
response.message = "Brak rozpoczetej rekrutacji";
enrollDate = DateTime.Now;
var enr = new ModelsAuto.Enrollment
{
IdEnrollment = newEnrollmentId,
Semester = 1,
IdStudy = idstudies,
StartDate = enrollDate
};
db.Enrollment.Add(enr);
db.SaveChanges();
}
else
{
newEnrollmentId = enrollment.Single().IdEnrollment;
enrollDate = (DateTime)enrollment.Single().StartDate;
}
e.IdEnrollment = newEnrollmentId;
e.Semester = 1;
e.IdStudy = idstudies;
e.StartDate = enrollDate;
response.enrollment = e;
DateTime bDate = Convert.ToDateTime(request.Birthdate);
//string formattedDate = bDate.ToString("yyyy-MM-dd HH:mm:ss.fff");
var student = new ModelsAuto.Student
{
IndexNumber = request.IndexNumber,
FirstName = request.FirstName,
LastName = request.LastName,
BirthDate = bDate,
IdEnrollment = newEnrollmentId
};
db.Student.Add(student);
try
{
db.SaveChanges();
}
catch (Exception ex)
{
response.message = "Student o takim ID jest juz w bazie. " + ex.Message;
}
response.message = "DONE";
return(response);
}
