WIP: Add scheduler output to database

src/main/java/nl/tudelft/ewi/tam/repositories/SchedulerRepository.java

 package nl.tudelft.ewi.tam.repositories;
 
+import edu.umd.cs.findbugs.annotations.SuppressFBWarnings;
 import nl.tudelft.ewi.tam.JdbcDaoImpl;
 import nl.tudelft.ewi.tam.beans.StudentScheduleInformation;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.jdbc.core.BeanPropertyRowMapper;
+import org.springframework.jdbc.core.PreparedStatementCreator;
 import org.springframework.stereotype.Repository;
 
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import java.sql.Statement;
 import java.sql.Types;
+import java.util.Date;
 import java.util.List;
 
 /**
@@ -23,6 +30,35 @@ public class SchedulerRepository {
     private transient JdbcDaoImpl jdbcDaoImpl;
 
     /**
+     * The {@link String} instance that, for a student who is assigned to a lab in a given timeslot,
+     * gets the courses of that lab that he is assigned to.
+     */
+    private static final String USER_CE_ID_BY_LAB =
+            "SELECT LabSesCourse.*  FROM "
+            + "(SELECT LabSessions.* FROM "
+            + "(SELECT ce_id FROM LabSession LS WHERE LS.lab_id = ? AND date = ? AND slot_id = ?) LabSessions "
+            + "JOIN CourseEdition ON LabSessions.ce_id = CourseEdition.id) LabSesCourse "
+            + "JOIN CourseAssignment CA ON LabSesCourse.ce_id = CA.ce_id WHERE CA.netid = ? AND CA.schedule_id = ?;";
+
+    /**
+     * The {@link String} instance create a new course assignment.
+     */
+    private static final String CREATE_COURSE_ASS =
+            "INSERT INTO CourseAssignment (netid, ce_id, schedule_id) VALUES (?, ?, ?);";
+
+    /**
+     * The {@link String} instance create a new course assignment.
+     */
+    private static final String CREATE_LAB_ASS =
+            "INSERT INTO LabAssignment (netid, schedule_id, ce_id, slot_id, date, lab_id) VALUES (?, ?, ?, ?, ?, ?);";
+
+    /**
+     * The {@link String} instance create a new course assignment.
+     */
+    private static final String CREATE_SCHEDULE =
+            "INSERT INTO Schedule (year, quarter, final) VALUES (?, ?, ?)";
+
+    /**
      * The {@link String} instance to get the courseRank, validatorRank and experience of the given netid
      * in the given year/quarter.
      */
@@ -51,4 +87,71 @@ public class SchedulerRepository {
         return jdbcDaoImpl.queryForList(GET_STUDENT_DATA, arguments, argumentTypes,
                 new BeanPropertyRowMapper<>(StudentScheduleInformation.class));
     }
+
+    /**
+     * Create a new schedule with the given year, quarter and final status, returning the ID of the generated schedule.
+     *
+     * @param year          the year of the schedule
+     * @param quarter       the quarter of the schedule
+     * @param finalSchedule whether the schedule is final or not
+     * @return the ID of the generated schedule
+     */
+    @SuppressWarnings("checkstyle:magicnumber") //Indexes of fields in prepared SQL query.
+    public int createSchedule(final int year, final int quarter, final boolean finalSchedule) {
+        return jdbcDaoImpl.update(new PreparedStatementCreator() { //Can't lambda since we must suppressFB
+            @Override
+            @SuppressWarnings("PMD.AccessorMethodGeneration") //PMD doesn't like that this isn't a lambda
+            @SuppressFBWarnings("OBL_UNSATISFIED_OBLIGATION_EXCEPTION_EDGE") //False positive; spring closes this for us
+            public PreparedStatement createPreparedStatement(final Connection connection) throws SQLException {
+                final PreparedStatement preparedStatement =
+                        connection.prepareStatement(CREATE_SCHEDULE, Statement.RETURN_GENERATED_KEYS);
+                // These match the order of the '?'s in the used SQL query (CREATE_SCHEDULE)
+                preparedStatement.setInt(1, year);
+                preparedStatement.setInt(2, quarter);
+                preparedStatement.setBoolean(3, finalSchedule);
+                return preparedStatement;
+            }
+        }).intValue();
+    }
+
+    /**
+     * Insert a new course assignment into the database.
+     *
+     * @param updateList the {@link List} of {netid, coursecode, scheduleId} combinations to create
+     * @return whether the insertion has succeeded
+     */
+    public int[] addCourseAssignments(final List<Object[]> updateList) {
+        final int[] argumentTypes = {Types.VARCHAR, Types.INTEGER, Types.INTEGER};
+        return jdbcDaoImpl.batchUpdate(CREATE_COURSE_ASS, updateList, argumentTypes);
+    }
+
+    /**
+     * Insert a new lab assignment into the database.
+     *
+     * @param updateList the {@link List} of {netid, scheduleId, courseCode, slotid, date, labid} combinations to create
+     * @return whether the insertion has succeeded
+     */
+    public int[] addLabAssignments(final List<Object[]> updateList) {
+        final int[] argumentTypes =
+                {Types.VARCHAR, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.DATE, Types.INTEGER};
+        return jdbcDaoImpl.batchUpdate(CREATE_LAB_ASS, updateList, argumentTypes);
+    }
+
+    /**
+     * Get the courses that the given student is a TA of in the given schedule, that are part of the given LabSession.
+     *
+     * @param scheduleId the schedule to check student assignment of
+     * @param labId      the ID of the lab of the session
+     * @param slotId     the timeslot ID of the session
+     * @param date       the date of the session
+     * @param netid      the netid of the student
+     * @return a list of {@link Integer} reprsenting the courseIDs
+     */
+    public List<Integer> getCoursesOfLab(final int scheduleId, final int labId, final int slotId, final Date date,
+                                         final String netid) {
+        final Object[] arguments = {labId, date, slotId, netid, scheduleId};
+        final int[] argumentTypes = {Types.INTEGER, Types.DATE, Types.INTEGER, Types.VARCHAR, Types.INTEGER};
+        return jdbcDaoImpl.queryForList(USER_CE_ID_BY_LAB, arguments, argumentTypes,
+                (resultSet, rowNum) -> resultSet.getInt(1));
+    }
 }