/* public EnrollStudentResponse EnrollStudent(EnrollStudentRequest request)
* {
* var idStudy = -1;
* var nextIdEnrollment = -1;
*
* var startDate = DateTime.Now;
* var semester = 1;
*
* using (var connection = new SqlConnection(sqlConnecionStr))
* using (var command = new SqlCommand())
* {
* command.Connection = connection;
* connection.Open();
* var transaction = connection.BeginTransaction();
* command.Transaction = transaction;
*
* try
* {
* //1. Check wether studies with given name exist
* command.CommandText = "SELECT IdStudy from Studies where Name = @name" + ";";
* command.Parameters.AddWithValue("@name", request.Studies);
* var dr = command.ExecuteReader();
* if (!dr.Read())
* {
* dr.Close();
* transaction.Rollback();
* throw new Exception("Studia o podanej nazwie nie istnieją!");
* }
* idStudy = (int)dr["IdStudy"];
* dr.Close();
*
* //2. Check wether indexNumber is unique (it's a PK in Student)
* command.CommandText = "SELECT IndexNumber from Student where IndexNumber = @number" + ";";
* command.Parameters.AddWithValue("@number", request.IndexNumber);
* var dr2 = command.ExecuteReader();
* if (dr2.Read())
* {
* dr2.Close();
* transaction.Rollback();
* throw new Exception("Student o podanym nr indeksu juz istnieje!");
* }
* dr2.Close();
*
*
* //3. Check latest IdEnrollment
* command.CommandText = "SELECT Max(IdEnrollment) as max from Enrollment;";
* var dr3 = command.ExecuteReader();
* if (!dr3.Read())
* {
* dr3.Close();
* transaction.Rollback();
* throw new Exception("Problem with fetching id enrollemnt");
* }
* var maxIdEnrollment = (int)dr3["max"];
* nextIdEnrollment = ++maxIdEnrollment;
* dr3.Close();
*
*
* //4. Create new Enrollment for student
* command.CommandText = "INSERT INTO Enrollment (IdEnrollment, Semester, IdStudy, StartDate) "
+ "VALUES(@nextId, @semester, @idStudy, CAST(@date AS DATETIME));";
+ command.Parameters.AddWithValue("@nextId", nextIdEnrollment);
+ command.Parameters.AddWithValue("@semester", semester);
+ command.Parameters.AddWithValue("@idStudy", idStudy);
+ command.Parameters.AddWithValue("@date", startDate);
+
+ command.ExecuteNonQuery();
+
+ //5. Add student to Studyies
+ command.CommandText = "INSERT INTO Student (IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) "
+ "VALUES(@indexNumber, @studentFirstName, @studnetLastName, CAST(@bdate AS DATE), @idEnrollment)";
+ command.Parameters.AddWithValue("@indexNumber", request.IndexNumber);
+ command.Parameters.AddWithValue("@studentFirstName", request.FirstName);
+ command.Parameters.AddWithValue("@studnetLastName", request.LastName);
+ command.Parameters.AddWithValue("@bdate", request.BirthDate);
+ command.Parameters.AddWithValue("@idEnrollment", nextIdEnrollment);
+
+ command.ExecuteNonQuery();
+
+ transaction.Commit();
+ }
+ catch (SqlException exc)
+ {
+ transaction.Rollback();
+ Console.WriteLine(exc.Message);
+ throw new Exception("SqlException " + exc.Message);
+ }
+ }
+
+ var response = new EnrollStudentResponse();
+ response.IdEnrollment = nextIdEnrollment;
+ response.Semester = semester;
+ response.IdStudy = idStudy;
+ response.StartDate = startDate;
+
+ return response;
+ }*/
public EnrollStudentResponse EnrollStudent(EnrollStudentRequest request)
{
var idStudy = -1;
var semester = 1;
var startDate = DateTime.Now;
//1. Check wether studies with given name exist
var studiesWithNameRetrived = _dbcontext.Studies.Where(s => s.Name.Equals(request.Studies)).FirstOrDefault();
if (studiesWithNameRetrived == null)
{
throw new Exception("Studia o podanej nazwie nie istnieją!");
}
idStudy = studiesWithNameRetrived.IdStudy;
//2. Check wether indexNumber is unique (it's a PK in Student)
var studentWithNewIndexNumber = _dbcontext.Student.Where(s => s.IndexNumber.Equals(request.IndexNumber)).FirstOrDefault();
if (studentWithNewIndexNumber != null)
{
throw new Exception("Student o podanym nr indeksu juz istnieje!");
}
//3. Check latest IdEnrollment
var maxIdEnrollment = _dbcontext.Enrollment.Max(e => e.IdEnrollment);
var nextIdEnrollment = ++maxIdEnrollment;
//4. Create new Enrollment for student
var newEnrollemnt = new Enrollment();
newEnrollemnt.IdEnrollment = nextIdEnrollment;
newEnrollemnt.Semester = 1;
newEnrollemnt.IdStudy = idStudy;
newEnrollemnt.StartDate = startDate;
_dbcontext.Add(newEnrollemnt);
_dbcontext.SaveChanges();
//5. Add student to Studyies
var newStudent = new Student();
newStudent.IndexNumber = request.IndexNumber;
newStudent.FirstName = request.FirstName;
newStudent.LastName = request.LastName;
newStudent.BirthDate = request.BirthDate;
newStudent.IdEnrollment = nextIdEnrollment;
_dbcontext.Add(newStudent);
_dbcontext.SaveChanges();
var response = new EnrollStudentResponse();
response.IdEnrollment = nextIdEnrollment;
response.Semester = semester;
response.IdStudy = idStudy;
response.StartDate = startDate;
return(response);
}
public Student UpdateStudent(Student student)
{
if (String.IsNullOrWhiteSpace(student.IndexNumber))
{
throw new InvalidArgumentException("Nie podano numeru indexu studenta do zmiany");
}
var db = new apbdContext();
db.Attach(student);
if (student.FirstName != null && student.FirstName.Trim().Length > 0)
{
db.Entry(student).Property("FirstName").IsModified = true;
}
if (student.LastName != null && student.LastName.Trim().Length > 0)
{
db.Entry(student).Property("LastName").IsModified = true;
}
if (student.BirthDate != null)
{
db.Entry(student).Property("BirthDate").IsModified = true;
}
db.SaveChanges();
return(GetStudent(student.IndexNumber));
}
Student IDbService.CreateStudent([FromBody] EnrollStudentRequest request)
{
var idEnroll = _studentContext.Enrollment.Include(e => e.IdStudyNavigation)
.Where(study => study.IdStudyNavigation.Name == request.Studies && study.Semester == 1)
.Select(id => id.IdEnrolment).FirstOrDefault();
var student = new Student
{
IndexNumber = request.IndexNumber,
FirstName = request.FirstName,
LastName = request.LastName,
BirthDate = request.BirthDate,
IdEnrollment = idEnroll
};
_studentContext.Student.Add(student);
_studentContext.SaveChanges();
return(student);
}
public Enrollment EnrollStudentToStudies(StudentEnrollment enrollments)
{
if (!IsValidEnrollments(enrollments))
{
throw new InvalidArgumentException("Brakujące dane wejściowe");
}
var db = new apbdContext();
var salt = PasswordService.CreateSalt();
var s = new Student
{
FirstName = enrollments.FirstName,
LastName = enrollments.LastName,
BirthDate = enrollments.BirthDate,
IndexNumber = enrollments.IndexNumber,
Salt = salt,
Password = PasswordService.Create(enrollments.Password, salt)
};
var st = db.Studies.Where(s => s.Name == enrollments.Studies).Single();
Enrollment en;
en = db.Enrollment.Where(en => en.IdStudy == st.IdStudy && en.Semester == 1).Single();
if (en == null)
{
var newEnrollment = new Enrollment
{
Semester = 1,
IdStudy = st.IdStudy,
StartDate = new DateTime()
};
db.Enrollment.Add(newEnrollment);
db.SaveChanges();
en = newEnrollment;
}
s.IdEnrollmentNavigation = en;
db.Student.Add(s);
db.SaveChanges();
return(en);
}
public void DeleteStudent(string index)
{
var db = new apbdContext();
var s = new Student
{
IndexNumber = index
};
db.Attach(s);
db.Remove(s);
db.SaveChanges();
}
public Student UpdateStudent(StudentDto studentDto, string id)
{
var existingStudent = (Student)_dbcontext.Student.FirstOrDefault <Student>();
if (studentDto.FirstName != null && studentDto.FirstName.Equals(""))
{
existingStudent.FirstName = studentDto.FirstName;
}
if (studentDto.LastName != null && studentDto.LastName.Equals(""))
{
existingStudent.LastName = studentDto.LastName;
}
if (studentDto.BirthDate != null && studentDto.BirthDate.Equals(""))
{
existingStudent.BirthDate = studentDto.BirthDate;
}
if (studentDto.IdEnrollment.Equals(""))
{
existingStudent.IdEnrollment = studentDto.IdEnrollment;
}
_dbcontext.SaveChanges();
return(existingStudent);
}
