1 files changed, 189 insertions, 0 deletions
diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/RegistrationDatabaseDAO.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/RegistrationDatabaseDAO.java
new file mode 100644
index 0000000..b624056
--- /dev/null
+++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/RegistrationDatabaseDAO.java
@@ -0,0 +1,189 @@
+package at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dao;
+
+import at.ac.tuwien.sepm.assignment.groupphase.exception.ElementNotFoundException;
+import at.ac.tuwien.sepm.assignment.groupphase.exception.PersistenceException;
+import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Employee;
+import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Employee.EducationLevel;
+import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Registration;
+import at.ac.tuwien.sepm.assignment.groupphase.util.JDBCConnectionManager;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.time.Instant;
+import java.time.LocalDate;
+import java.time.OffsetDateTime;
+import java.time.ZoneId;
+import java.util.ArrayList;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Set;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.stereotype.Repository;
+
+@Repository
+public class RegistrationDatabaseDAO implements RegistrationDAO {
+
+    private JDBCConnectionManager jdbcConnectionManager;
+    private EmployeeDAO employeePersistence;
+
+    @Autowired
+    public RegistrationDatabaseDAO(
+            JDBCConnectionManager jdbcConnectionManager, EmployeeDAO employeePersistence) {
+        this.jdbcConnectionManager = jdbcConnectionManager;
+        this.employeePersistence = employeePersistence;
+    }
+
+    private long getVehicleVersionId(long vehicleId) throws PersistenceException {
+        String sqlGetVehicleVersionId = "SELECT * FROM vehicle WHERE id = ?";
+        try (PreparedStatement stmt =
+                jdbcConnectionManager.getConnection().prepareStatement(sqlGetVehicleVersionId)) {
+            stmt.setLong(1, vehicleId);
+            try (ResultSet rs = stmt.executeQuery()) {
+                if (rs.next()) {
+                    return rs.getLong("version");
+                } else {
+                    throw new PersistenceException("vehicle id not found");
+                }
+            }
+        } catch (SQLException e) {
+            throw new PersistenceException(e);
+        }
+    }
+
+    private long getEmployeeVersionId(long employeeId) throws PersistenceException {
+        String sqlGetEmployeeVersionId = "SELECT * FROM employee WHERE id = ?";
+        try (PreparedStatement stmt =
+                jdbcConnectionManager.getConnection().prepareStatement(sqlGetEmployeeVersionId)) {
+            stmt.setLong(1, employeeId);
+            try (ResultSet rs = stmt.executeQuery()) {
+                if (rs.next()) {
+                    return rs.getLong("version");
+                } else {
+                    throw new PersistenceException("employee id not found");
+                }
+            }
+        } catch (SQLException e) {
+            throw new PersistenceException(e);
+        }
+    }
+
+    @Override
+    public Set<Long> add(long vehicleId, Set<Registration> registrations)
+            throws PersistenceException {
+        String sql =
+                "INSERT INTO Registration (vehicleId, employeeId, start, end, active) VALUES (?,?,?,?,?)";
+        String sql2 = "UPDATE Vehicle SET status = 'FREI_WACHE' WHERE id = ?;";
+
+        Set<Long> vehicleIds = new HashSet<>();
+
+        try {
+            Connection con = jdbcConnectionManager.getConnection();
+            con.setAutoCommit(false);
+
+            try (PreparedStatement pstmt =
+                    con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
+
+                // vehicleId is a Vehicle.id as it comes from GUI => fetch VehicleVersion.id
+                pstmt.setLong(1, getVehicleVersionId(vehicleId));
+
+                for (Registration r : registrations) {
+                    pstmt.setLong(2, getEmployeeVersionId(r.employee().id()));
+                    pstmt.setObject(3, OffsetDateTime.ofInstant(r.start(), ZoneId.systemDefault()));
+                    pstmt.setObject(4, OffsetDateTime.ofInstant(r.end(), ZoneId.systemDefault()));
+                    pstmt.setBoolean(5, true);
+                    pstmt.addBatch();
+                }
+
+                pstmt.executeBatch();
+
+                try (ResultSet rs = pstmt.getGeneratedKeys()) {
+                    while (rs.next()) vehicleIds.add(rs.getLong(1));
+                }
+            }
+
+            try (PreparedStatement pstmt = con.prepareStatement(sql2)) {
+                pstmt.setLong(1, vehicleId);
+                if (pstmt.executeUpdate() != 1) {
+                    con.rollback();
+                    throw new PersistenceException("Failed to persist registration");
+                }
+            }
+
+            con.commit();
+            return vehicleIds;
+        } catch (SQLException e) {
+            jdbcConnectionManager.rollbackConnection();
+            throw new PersistenceException(e);
+        }
+    }
+
+    @Override
+    public void remove(long id) throws ElementNotFoundException, PersistenceException {
+        String sql = "UPDATE Registration SET active = 0, end = ? WHERE id = ?";
+
+        try {
+            Connection con = jdbcConnectionManager.getConnection();
+
+            try (PreparedStatement pstmt = con.prepareStatement(sql)) {
+                pstmt.setObject(1, OffsetDateTime.ofInstant(Instant.now(), ZoneId.systemDefault()));
+                pstmt.setLong(2, id);
+
+                if (pstmt.executeUpdate() != 1)
+                    throw new ElementNotFoundException("No such registrationId exists");
+            }
+
+        } catch (SQLException e) {
+            throw new PersistenceException(e);
+        }
+    }
+
+    protected List<Registration> list(long vehicleId) throws PersistenceException {
+
+        String sql =
+                "SELECT * FROM Registration r "
+                        + "JOIN EmployeeVersion ev ON ev.id = r.employeeId "
+                        + "JOIN VehicleVersion vv ON vv.id = r.vehicleId "
+                        + "WHERE r.vehicleId = ?";
+
+        try (PreparedStatement stmt = jdbcConnectionManager.getConnection().prepareStatement(sql)) {
+
+            List<Registration> registrationList = new ArrayList<>();
+            stmt.setLong(1, vehicleId); // is vehicle version id!
+            ResultSet rs = stmt.executeQuery();
+            while (rs.next()) {
+
+                Employee employee =
+                        Employee.builder()
+                                .id(rs.getLong("EmployeeVersion.id"))
+                                .name(rs.getString("EmployeeVersion.name"))
+                                .birthday(rs.getObject("EmployeeVersion.birthday", LocalDate.class))
+                                .educationLevel(
+                                        EducationLevel.valueOf(
+                                                rs.getString("EmployeeVersion.educationLevel")))
+                                .isDriver(rs.getBoolean("EmployeeVersion.isDriver"))
+                                .isPilot(rs.getBoolean("EmployeeVersion.isPilot"))
+                                .build();
+
+                Registration registration =
+                        Registration.builder()
+                                .id(rs.getLong("Registration.id"))
+                                .start(
+                                        (rs.getObject("Registration.start", OffsetDateTime.class))
+                                                .toInstant())
+                                .end(
+                                        (rs.getObject("Registration.end", OffsetDateTime.class))
+                                                .toInstant())
+                                .employee(employee)
+                                .build();
+
+                registrationList.add(registration);
+            }
+
+            return registrationList;
+        } catch (SQLException e) {
+            throw new PersistenceException(e);
+        }
+    }
+}