/**
* {@inheritDoc} This method will delete the distance data from a MariaDB database. This method
* assumes the connection member is not null and open. Therefore, should be called through
* MariaDaoManager.
*/
@Override
public boolean delete(Distance distance) throws DataUpdateException, DataAccessException {
if (distance == null || distance.id < 0) {
return false;
}
try {
if (delete == null || delete.isClosed()) {
SqlBuilder builder =
new SqlBuilder("distance", StatementType.DELETE).addWhereClause("id=?");
delete = connection.prepareStatement(builder.build());
}
delete.setInt(1, distance.id);
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing DataAccessException!");
throw new DataAccessException(e);
}
try {
delete.executeUpdate();
return true;
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataUpdateException!");
throw new DataUpdateException(e);
}
}
/**
* {@inheritDoc} This method will get the distance data from a MariaDB database. This method
* assumes the connection member is not null and open. Therefore, should be called through
* MariaDaoManager.
*/
@Override
public Optional<Distance> getDistanceBetween(Classroom classroom1, Classroom classroom2)
throws DataAccessException {
Distance distance = null;
try {
if (selectTwoRooms == null || selectTwoRooms.isClosed()) {
SqlBuilder builder =
new SqlBuilder("distance", StatementType.SELECT)
.addColumns("id,distance")
.addWhereClause(
"(startRoomId=? AND endRoomId=?) OR (startRoomId=? AND endRoomId=?");
selectTwoRooms = connection.prepareStatement(builder.build());
}
selectTwoRooms.setInt(1, classroom1.id);
selectTwoRooms.setInt(4, classroom1.id);
selectTwoRooms.setInt(2, classroom2.id);
selectTwoRooms.setInt(3, classroom2.id);
ResultSet set = selectTwoRooms.executeQuery();
if (set.next()) {
distance = new Distance(set.getInt(1), classroom1, classroom2, set.getInt(2));
}
set.close();
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataAccessException!");
throw new DataAccessException(e);
}
return Optional.ofNullable(distance);
}
/**
* {@inheritDoc} This method will insert the distance data into a MariaDB database. This method
* assumes the connection member is not null and open. Therefore, should be called through
* MariaDaoManager.
*/
@Override
public int insert(Distance distance) throws DataUpdateException, DataAccessException {
int id = -1;
if (distance == null
|| distance.endRoom == null
|| distance.startRoom == null
|| distance.distance < 0) {
return id;
}
try {
if (insert == null || insert.isClosed()) {
SqlBuilder builder =
new SqlBuilder("distance", StatementType.INSERT)
.addColumns("startRoomId", "endRoomId", "distance")
.addValue("?,?,?");
insert = connection.prepareStatement(builder.build(), Statement.RETURN_GENERATED_KEYS);
}
insert.setInt(1, distance.startRoom.id);
insert.setInt(2, distance.endRoom.id);
insert.setInt(3, distance.distance);
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataAccessException!");
throw new DataUpdateException(e);
}
try {
insert.executeUpdate();
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataUpdateException!");
throw new DataUpdateException(e);
}
try {
ResultSet set = insert.getGeneratedKeys();
if (set.next()) {
id = set.getInt(1);
}
set.close();
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataAccessException!");
}
return id;
}
/** {@inheritDoc} */
@Override
public void close() {
try {
if (selectAll != null) selectAll.close();
if (selectAllRoomEnd != null) selectAllRoomEnd.close();
if (selectAllRoomStart != null) selectAllRoomStart.close();
if (selectId != null) selectId.close();
if (selectTwoRooms != null) selectTwoRooms.close();
} catch (SQLException e) {
Log.error(e);
}
}
/** {@inheritDoc} */
@Override
public boolean deleteAll() throws DataAccessException, DataUpdateException {
boolean success = false;
try {
if (delete == null || delete.isClosed()) {
delete = connection.prepareStatement("DELETE FROM distance;");
}
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataAccessException!");
throw new DataAccessException(e);
}
try {
delete.execute();
success = true;
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataUpdateException!");
throw new DataUpdateException(e);
}
return success;
}
/**
* {@inheritDoc} This method will update the distance data in a MariaDB database. This method
* assumes the connection member is not null and open. Therefore, should be called through
* MariaDaoManager.
*/
@Override
public boolean update(Distance distance) throws DataUpdateException, DataAccessException {
if (distance == null
|| distance.endRoom == null
|| distance.startRoom == null
|| distance.id < 0
|| distance.distance < 0) {
return false;
}
try {
if (update == null || update.isClosed()) {
SqlBuilder builder =
new SqlBuilder("distance", StatementType.UPDATE)
.addSetClauses("startRoomId=?", "endRoomId=?", "distance=?")
.addWhereClause("id=?");
update = connection.prepareStatement(builder.build());
}
update.setInt(1, distance.startRoom.id);
update.setInt(2, distance.endRoom.id);
update.setInt(3, distance.distance);
update.setInt(4, distance.id);
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing DataAccessException!");
throw new DataAccessException(e);
}
try {
update.executeUpdate();
return true;
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataUpdateException!");
throw new DataUpdateException(e);
}
}
/**
* {@inheritDoc} This method will get the distance data from a MariaDB database. This method
* assumes the connection member is not null and open. Therefore, should be called through
* MariaDaoManager.
*/
@Override
public List<Distance> getAll() throws DataAccessException {
ArrayList<Distance> distances = new ArrayList<>();
try {
if (selectAll == null || selectAll.isClosed()) {
SqlBuilder builder =
new SqlBuilder("distance", StatementType.SELECT)
.addColumns(
"distance.id",
"distance.startRoomId",
"distance.endRoomId",
"classroom1.roomName",
"classroom1.buildingId",
"building1.buildingName",
"classroom1.subjectId",
"subject1.subjectName",
"classroom2.roomName",
"classroom2.buildingId",
"building2.buildingName",
"classroom2.subjectId",
"subject2.subjectName",
"distance.distance")
.addJoinClause(
new JoinClause(
JoinType.INNER,
"classroom classroom1",
"classroom1.id=distance.startRoomId"))
.addJoinClause(
new JoinClause(
JoinType.INNER, "building building1", "building1.id=classroom1.buildingId"))
.addJoinClause(
new JoinClause(
JoinType.INNER, "subject subject1", "subject1.id=classroom1.subjectId"))
.addJoinClause(
new JoinClause(
JoinType.INNER, "classroom classroom2", "classroom2.id=distance.endRoomId"))
.addJoinClause(
new JoinClause(
JoinType.INNER, "building building2", "building2.id=classroom2.buildingId"))
.addJoinClause(
new JoinClause(
JoinType.INNER, "subject subject2", "subject2.id=classroom2.subjectId"));
selectAll = connection.prepareStatement(builder.build());
}
ResultSet set = selectAll.executeQuery();
while (set.next()) {
Subject subject1 = new Subject(set.getInt(7), set.getString(8));
Subject subject2 = new Subject(set.getInt(12), set.getString(13));
Building building1 = new Building(set.getInt(5), set.getString(6));
Building building2 = new Building(set.getInt(10), set.getString(11));
Classroom classroom1 = new Classroom(set.getInt(2), set.getString(4), building1, subject1);
Classroom classroom2 = new Classroom(set.getInt(3), set.getString(9), building2, subject2);
distances.add(new Distance(set.getInt(1), classroom1, classroom2, set.getInt(14)));
}
set.close();
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataAccessException!");
throw new DataAccessException(e);
}
return distances;
}
/**
* {@inheritDoc} This method will get the distance data from a MariaDB database. This method
* assumes the connection member is not null and open. Therefore, should be called through
* MariaDaoManager.
*/
@Override
public List<Distance> getAllDistancesFrom(Classroom classroom) throws DataAccessException {
ArrayList<Distance> distances = new ArrayList<>();
if (classroom == null || classroom.id < 0) {
return distances;
}
try {
if (selectAllRoomStart == null || selectAllRoomStart.isClosed()) {
SqlBuilder builder =
new SqlBuilder("distance", StatementType.SELECT)
.addColumns(
"distance.id",
"classroom.id",
"classroom.roomName",
"building.id",
"buildingName",
"subject.id",
"subject.subjectName",
"distance.distance")
.addJoinClause(
new JoinClause(JoinType.INNER, "classroom", "distance.endRoomId=classroom.id"))
.addJoinClause(
new JoinClause(JoinType.INNER, "building", "classroom.buildingId=building.id"))
.addJoinClause(
new JoinClause(JoinType.INNER, "subject", "classroom.subjectId=subject.id"))
.addWhereClause("distance.startRoomId=?");
selectAllRoomStart = connection.prepareStatement(builder.build());
}
selectAllRoomStart.setInt(1, classroom.id);
ResultSet startSet = selectAllRoomStart.executeQuery();
while (startSet.next()) {
Distance distance =
new Distance(
startSet.getInt(1),
classroom,
new Classroom(
startSet.getInt(2),
startSet.getString(3),
new Building(startSet.getInt(4), startSet.getString(5)),
new Subject(startSet.getInt(6), startSet.getString(7))),
startSet.getInt(8));
distances.add(distance);
}
startSet.close();
if (selectAllRoomEnd == null || selectAllRoomEnd.isClosed()) {
SqlBuilder builder =
new SqlBuilder("distance", StatementType.SELECT)
.addColumns(
"distance.id",
"classroom.id",
"classroom.roomName",
"building.id",
"buildingName",
"subject.id",
"subject.subjectName",
"distance.distance")
.addJoinClause(
new JoinClause(
JoinType.INNER, "classroom", "distance.startRoomId=classroom.id"))
.addJoinClause(
new JoinClause(JoinType.INNER, "building", "classroom.buildingId=building.id"))
.addJoinClause(
new JoinClause(JoinType.INNER, "subject", "classroom.subjectId=subject.id"))
.addWhereClause("distance.endRoomId=?");
selectAllRoomEnd = connection.prepareStatement(builder.build());
}
selectAllRoomEnd.setInt(1, classroom.id);
ResultSet endSet = selectAllRoomEnd.executeQuery();
while (endSet.next()) {
Distance distance =
new Distance(
endSet.getInt(1),
classroom,
new Classroom(
endSet.getInt(2),
endSet.getString(3),
new Building(endSet.getInt(4), endSet.getString(5)),
new Subject(endSet.getInt(6), endSet.getString(7))),
endSet.getInt(8));
distances.add(distance);
}
endSet.close();
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataAccessException!");
throw new DataAccessException(e);
}
return distances;
}
/**
* Gives each lesson a staff member and classroom on a per subject basis, taking into account the
* distances between each lesson for each member of staff.
*
* @return Nothing.
*/
@Override
public Void call() {
// get all subjects
List<Subject> subjects;
List<Distance> distances;
StringBuilder failedLog = new StringBuilder();
try {
subjects = daoManager.getSubjectDao().getAll();
} catch (DataAccessException e) {
updateMessage(
"Failed to access subject data due to the error ["
+ e
+ "]\n"
+ "Please restart the timetabler and send the logs to your system administrator if this occurs again.");
return null;
} catch (DataConnectionException e) {
DataExceptionHandler.handleJavaFx(e, null, true);
return null;
}
try {
distances = daoManager.getDistanceDao().getAll();
} catch (DataAccessException e) {
updateMessage(
"Failed to access distance data due to the error ["
+ e
+ "]\n"
+ "Please restart the timetabler and send the logs to your system administrator if this occurs again.");
return null;
} catch (DataConnectionException e) {
DataExceptionHandler.handleJavaFx(e, null, true);
return null;
}
try {
subjects.forEach(
subject -> {
updateMessage("Timetabling [" + subject.name + ']');
// for current subject find periods where multiple classes are taught during the same
// period for this subject
// The id of the top list is the id of the period as it is static data
List<Set<LessonPlan>> overloadedPeriod;
overloadedPeriod = findOverloadedPeriods(subject, 31);
Log.verbose("Found [" + overloadedPeriod.size() + "] overloaded periods");
List<LessonPlan> lessonPlans = null;
List<Staff> staff = null;
List<Classroom> classrooms = null;
try {
// get all lessonPlans for this subject
lessonPlans = daoManager.getLessonPlanDao().getAllBySubject(subject);
} catch (DataAccessException e) {
failedLog
.append("Could not access the lesson data!\nThe error was [")
.append(e)
.append(
"]\nPlease restart the timetabler and send the logs to your system administrator")
.append(" if this occurs again.");
Log.error(e);
throw new LambdaBreakException();
} catch (DataConnectionException e) {
DataExceptionHandler.handleJavaFx(e, null, true);
throw new LambdaBreakException();
}
try {
// get all staff who teach the subject
staff = daoManager.getStaffDao().getAllBySubject(subject);
} catch (DataAccessException e) {
failedLog
.append("Could not access the staff data!\nThe error was [")
.append(e)
.append(
"]\nPlease restart the timetabler and send the logs to your system administrator")
.append(" if this occurs again.");
Log.error(e);
throw new LambdaBreakException();
} catch (DataConnectionException e) {
DataExceptionHandler.handleJavaFx(e, null, true);
throw new LambdaBreakException();
}
try {
classrooms = daoManager.getClassroomDao().getAll();
} catch (DataConnectionException e) {
failedLog
.append("Could not access the classroom data!\nThe error was [")
.append(e)
.append(
"]\nPlease restart the timetabler and send the logs to your system administrator")
.append(" if this occurs again.");
Log.error(e);
throw new LambdaBreakException();
} catch (DataAccessException e) {
DataExceptionHandler.handleJavaFx(e, null, true);
throw new LambdaBreakException();
}
Log.verbose(
"Found ["
+ classrooms.size()
+ "] classrooms and ["
+ lessonPlans.size()
+ "] lessons and ["
+ staff.size()
+ "] staff");
// put staff into lessonPlans for this subject, avoiding teacher being scheduled twice
// for same period.
putStaffIntoLessonPlan(lessonPlans, staff, overloadedPeriod);
// Get the data needed for the final part of timetabling
List<Distance> subjectDistance =
distances
.parallelStream()
.filter(distance -> distance.startRoom.subject.equals(subject))
.collect(Collectors.toList());
List<Classroom> subjectRooms =
classrooms
.parallelStream()
.filter(classroom -> classroom.subject.equals(subject))
.collect(Collectors.toList());
// Put classrooms into lessonPlans for this subject.
// putLessonPlansIntoClassrooms(lessonPlans, subjectRooms, subjectDistance);
lessonPlans.forEach(
lessonPlan -> {
try {
daoManager.getLessonPlanDao().update(lessonPlan);
} catch (DataAccessException | DataUpdateException e) {
failedLog
.append("Could not update the lesson data!\nThe error was [")
.append(e)
.append(
"]\nPlease restart the timetabler and send the logs to your system administrator")
.append(" if this occurs again.");
Log.error(e);
throw new LambdaBreakException();
} catch (DataConnectionException e) {
DataExceptionHandler.handleJavaFx(e, null, true);
throw new LambdaBreakException();
}
});
});
} catch (LambdaBreakException e) {
if (failedLog.length() > 0) {
updateMessage(failedLog.toString());
}
return null;
}
updateMessage("Done");
return null;
}