Schedule.java

package com.example.scheduler_server;

import java.sql.*;

/*
    Name: Schedule
    Description: Responsible for sending queries and receiving responses from the database that deal with the
    Shifts table.
 */
public class Schedule {

    private final Connection dbConnection;

    protected Schedule(Connection c) {
        this.dbConnection = c;
    }

    /*
    Name: addShift
    Parameters:
        int ID: The employees unique ID.
        Date: The date as a string in the form of "yyyy-mm-dd".
        start: The employees shift start time.
        end: The employees shift end time.
    Description: Adds a new shift to the database, referencing the employees ID, and assigning a unique shift number.
    Return: none
     */
    protected String addShift(int ID, String date, int start, int end){
        String newShift = "";
        Date newDate = Date.valueOf(date);
        try {
            Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            myStatement.executeUpdate("INSERT INTO shifts (employee_id, full_date, start_time, end_time) " +
                    "VALUES ('" + ID +"', '"+ newDate +"', '"+ start +"', '"+ end +"')");
            ResultSet myRs = myStatement.executeQuery("select * from Shifts where employee_ID=" + ID +
                    " and full_date='" + newDate + "' and start_time=" + start + " and end_time=" + end + " and availability=false");
            if (myRs.last()) {
                newShift = myRs.getString("employee_id" ) + "," +
                        myRs.getString("full_date") + "," + myRs.getString("start_time") +
                        "," + myRs.getString("end_time") + "," + myRs.getString("shift_id") +
                        "," + myRs.getString("availability");
            }
        } catch (SQLException exception) {
            exception.printStackTrace();
            return newShift;
        }
        System.out.println("Shift successfully added");
        return newShift;
    }

    /*
    Name: removeShiftByID
    Parameters:
        int shiftID: The shift unique ID.
    Description: Removes the employees shift matching the shift date.
    Return: none
     */
    protected int removeShiftByID(int shiftID){
        try {
            Statement myStatement = dbConnection.createStatement();
            myStatement.executeUpdate("DELETE FROM shifts WHERE (shift_id =" + shiftID + ")");
        } catch (SQLException exception) {
            exception.printStackTrace();
            return -1;
        }
        return shiftID;
    }

    /*
    Name: editShift
    Parameters:
        int shift_id: The shift unique ID.
        String date: The date of the shift in the form "YYYY-MM-DD".
        String start: The start time of the shift in 24-hour time "HHMM".
        String end: The end time of the shift in 24-hour time "HHMM".
    Description: Edits the time and date of the of a shift if it has the same shift id
    Return: none
 */
    protected String editShift(Integer shiftID, String date, String start,String end, String available, int employeeID){
        String editedShift = "";
        Date newDate = Date.valueOf(date);
        System.out.println(newDate);
        try {
            Statement myStatement = dbConnection.createStatement();
            myStatement.executeUpdate("update shifts set full_date='" + newDate + "',start_time=" + start + ",end_time=" + end
                    + ",availability=" + available +",employee_ID=" + employeeID + " where shift_id=" + shiftID);
            ResultSet myRs = myStatement.executeQuery("select * from Shifts where shift_id=" + shiftID);
            editedShift = myRs.getString("employee_id" ) + "," + myRs.getString("full_date") +
                    "," + myRs.getString("start_time") + "," + myRs.getString("end_time") +
                    "," + myRs.getString("shift_id") + "," + myRs.getString("availability");
        } catch (SQLException exception) {
            exception.printStackTrace();
            return editedShift;
        }
        return editedShift;
    }

    /*
        Name: removeAllShiftsByID
        Parameters:
            int ID: Employee ID
        Description: Removes all shifts in the database for a specific employee
        Return: void
         */
    protected void removeAllShiftsByID(int ID) {
        try {
            Statement myStatement = dbConnection.createStatement();
            myStatement.executeUpdate("DELETE FROM shifts WHERE (employee_ID =" + ID + ")");
        } catch (SQLException exception) {
            System.out.println("Error removing all shifts by id.");
            exception.printStackTrace();
        }
    }

    /*
        Name: allShifts
        Parameters:
            None
        Description: Returns a list of all shifts in the database. Where each '/' split is a shift, and each '.' split
        is a detail of the shift.
        Return: String
         */
    protected String allShifts(){
        StringBuilder response = new StringBuilder("allShifts");
        try {
            Statement myStatement = dbConnection.createStatement();
            ResultSet myRs = myStatement.executeQuery("SELECT * FROM shifts");
            while (myRs.next()) {
                response.append("/").append(myRs.getString("employee_id"))
                        .append(",").append(myRs.getString("full_date"))
                        .append(",").append(myRs.getString("start_time"))
                        .append(",").append(myRs.getString("end_time"))
                        .append(",").append(myRs.getString("shift_id"))
                        .append(",").append(myRs.getString("availability"));
            }
        } catch (SQLException exception) {
            exception.printStackTrace();
        }
        return response.toString();
    }
}