IEnumerable <Models.Student> IDbService.GetStudents()
{
var Db = new s19110Context();
var dbStudents = Db.Student.Include(st => st.IdEnrollmentNavigation).Include(st => st.IdEnrollmentNavigation.IdStudyNavigation).ToList();
var modelStudents = new List <Models.Student>();
foreach (var student in dbStudents)
{
var toAdd = new Models.Student()
{
FirstName = student.FirstName,
LastName = student.LastName,
IndexNumber = student.IndexNumber,
BirthDate = student.BirthDate
};
if (student.IdEnrollmentNavigation != null)
{
toAdd.Studies = student.IdEnrollmentNavigation.IdStudyNavigation.Name;
toAdd.Semester = student.IdEnrollmentNavigation.Semester;
}
modelStudents.Add(toAdd);
}
return(modelStudents);
}
public PromoteStudentResponse PromoteStudents(int semester, string studies)
{
var db = new s19110Context();
var enrollment = db.Enrollment.Include(en => en.IdStudyNavigation).Where(en => en.Semester == semester && en.IdStudyNavigation.Name.Equals(studies)).FirstOrDefault();
if (enrollment == null)
{
throw new ArgumentException("Nie ma takiego wpisu");
}
var par1 = new SqlParameter("@Studies", studies);
var par2 = new SqlParameter("@semester", semester);
db.Database.ExecuteSqlCommand("EXEC promoteStudents @Studies, @semester", par1, par2);
return(new PromoteStudentResponse
{
Semester = semester + 1,
Studies = studies
});
//Zawartość procedury -- nie tworzę jej za każdym razem przy uruchamianiu tej metody
/*Create Procedure PromoteStudents @Studies nvarchar(100), @Semester INT
* AS BEGIN
* Declare @IndexNumberCurs nvarchar(100), @NameCurs nvarchar(100), @SemesterCurs int, @StudyIdCurs int;
* Declare Studenci cursor for (Select IndexNumber, Name, Semester, Studies.IdStudy From Student
* Join Enrollment on Student.IdEnrollment = Enrollment.IdEnrollment join Studies on Studies.IdStudy = Enrollment.IdStudy
* where Name = @Studies and Semester = @Semester );
*
* Open studenci;
* Fetch next From studenci
* into @IndexNumberCurs, @NameCurs, @SemesterCurs, @StudyIdCurs;
*
* WHILE @@FETCH_STATUS = 0
* BEGIN
* Declare @newEnrollmentId int = -1;
* Select @newEnrollmentId = IdEnrollment from Enrollment Join Studies on Studies.IdStudy = Enrollment.IdStudy where Name = @NameCurs AND Semester=@SemesterCurs+1;
*
*
* IF @newEnrollmentId = -1
* BEGIN
* Insert Into Enrollment(IdEnrollment,Semester,IdStudy,StartDate) values ((Select Max(IdEnrollment)+1 From Enrollment), @SemesterCurs+1,@StudyIdCurs,SYSDATETIME());
* update Student set IdEnrollment =( Select MAX(IdEnrollment) FROM Enrollment )where IndexNumber=@IndexNumberCurs;
* END
* ELSE
* update Student set IdEnrollment = @newEnrollmentId where IndexNumber=@IndexNumberCurs;
* Fetch next From studenci
* into @IndexNumberCurs, @NameCurs, @SemesterCurs, @StudyIdCurs;
* END
*
* Close studenci;
* Deallocate studenci;
* END */
}
public NewModels.Enrollment GetEnrollment(String idStudenta)
{
var Db = new s19110Context();
var student = Db.Student.Where(st => st.IndexNumber.Equals(idStudenta)).FirstOrDefault();
if (student == null)
{
throw new StudentNotFoundException("Nie ma takiego studenta");
}
return(Db.Enrollment.Where(e => e.IdEnrollment.Equals(student.IdEnrollment)).FirstOrDefault());
}
public void DeleteStudent(string IndexNumber)
{
var Db = new s19110Context();
var deletedRoles = Db.StudentRoles.Where(sr => sr.IndexNumber.Equals(IndexNumber)).ToList();
var deleted = new NewModels.Student()
{
IndexNumber = IndexNumber
};
foreach (var sr in deletedRoles)
{
Db.Remove(sr);
}
Db.SaveChanges(); //Muszę jakoś obejść więzy spójności przy rolach z poprzednich zajęć
Db.Remove(deleted);
Db.SaveChanges();
}
Models.Student IDbService.GetStudent(string IndexNumber)
{
var Db = new s19110Context();
var student = Db.Student.Where(st => st.IndexNumber.Equals(IndexNumber)).FirstOrDefault();
if (student == null)
{
throw new StudentNotFoundException("Nie ma takiego studenta");
}
return(new Models.Student()
{
FirstName = student.FirstName,
LastName = student.LastName,
IndexNumber = student.IndexNumber,
BirthDate = student.BirthDate,
Semester = student.IdEnrollmentNavigation.Semester,
Studies = student.IdEnrollmentNavigation.IdStudyNavigation.Name
});
}
//Korzystam z innego modelu studenta, bo nie chcę, żeby można było podawać sól itp.
public void ModifyStudent(Models.Student newData)
{
var Db = new s19110Context();
var toUpdate = Db.Student.Where(st => st.IndexNumber == newData.IndexNumber).FirstOrDefault();
if (toUpdate == null)
{
throw new StudentNotFoundException("Nie ma takiego studenta");
}
toUpdate.FirstName = newData.FirstName != null ? newData.FirstName : toUpdate.FirstName;
toUpdate.LastName = newData.LastName != null ? newData.LastName : toUpdate.LastName;
toUpdate.BirthDate = newData.BirthDate.Equals(null) ? newData.BirthDate : toUpdate.BirthDate;
if (newData.Studies != null && newData.Semester != 0)
{
var enrollment = Db.Enrollment.Where(en => en.IdStudyNavigation.Name.Equals(newData.Studies) && en.Semester == newData.Semester).FirstOrDefault();
if (enrollment != null)
{
toUpdate.IdEnrollment = enrollment.IdEnrollment;
toUpdate.IdEnrollmentNavigation = enrollment;
}
else
{
var study = Db.Studies.Where(st => st.Name.Equals(newData.Studies)).FirstOrDefault();
if (study == null)
{
throw new ArgumentException("Podany typ studiów nie istnieje");
}
var newEnrollment = new NewModels.Enrollment()
{
IdStudy = study.IdStudy,
IdStudyNavigation = study,
Semester = newData.Semester
};
Db.Enrollment.Add(newEnrollment);
toUpdate.IdEnrollmentNavigation = newEnrollment;
}
}
Db.SaveChanges();
}
public EnrollStudentResponse EnrollStudent(EnrollStudentRequest request)
{
var db = new s19110Context();
//1. Czy studia instnieją?
var study = db.Studies.Where(st => st.Name.Equals(request.Studies)).FirstOrDefault();
if (study == null)
{
throw new ArgumentException("Podane studia nie instnieją");
}
//3. Sprawdzenie czy student o takim indeksie już istnieje
var student = db.Student.Where(st => st.IndexNumber == request.IndexNumber).FirstOrDefault();
if (student != null)
{
throw new ArgumentException("Student o podanym indeksie już znajduje się w bazie danych");
}
//3. Szukanie w tabeli Enrollment
var enrollment = db.Enrollment.Where(en => en.IdStudy == study.IdStudy && en.Semester == 1).FirstOrDefault();
var toRespond = new EnrollStudentResponse();
toRespond.LastName = request.LastName;
var addedStudent = new NewModels.Student()
{
IndexNumber = request.IndexNumber,
FirstName = request.FirstName,
LastName = request.LastName,
BirthDate = request.BirthDate
};
if (enrollment == null)
{
//Wstawianie nowego zapisu do bazy danych
//Używam zagnieżdżonego selecta zamiast Identity, bo nie korzystałem z niego od początku
var addedEnrollment = new NewModels.Enrollment()
{
IdEnrollment = db.Enrollment.Max(en => en.IdEnrollment),
Semester = 1,
StartDate = DateTime.Now,
IdStudy = study.IdStudy,
IdStudyNavigation = study
};
db.Enrollment.Add(addedEnrollment);
addedStudent.IdEnrollment = addedEnrollment.IdEnrollment;
addedStudent.IdEnrollmentNavigation = addedEnrollment;
toRespond.Semester = addedEnrollment.Semester;
toRespond.StartDate = addedEnrollment.StartDate;
}
else
{
addedStudent.IdEnrollment = enrollment.IdEnrollment;
addedStudent.IdEnrollmentNavigation = enrollment;
toRespond.Semester = enrollment.Semester;
toRespond.StartDate = enrollment.StartDate;
}
//4. Dodanie studenta
db.Student.Add(addedStudent);
db.SaveChanges();
return(toRespond);
}
public IEnumerable <NewModels.Student> GetStudents()
{
var Db = new s19110Context();
return(Db.Student.ToList());
}
