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 {

    // Object that facilitates the connection to the database
    private final Connection dbConnection;

    /*
    Name: Schedule Constructor
    Parameters:
        Connection c: The connection to the database.
    Description: Instantiates a Schedule object and sets the global connection for the methods.
    Return: Schedule Object
     */
    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 position){
        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, position) " +
                    "VALUES ('" + ID + "', '" + newDate + "', '" + start + "', '" + end + "', '" + position + "')");
            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") + "," + myRs.getString("position") +
                        "," + myRs.getString("checkedIn");
            }
        } 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);
            myRs.next();
            editedShift = myRs.getString("employee_id" ) + "," + myRs.getString("full_date") +
                    "," + myRs.getString("start_time") + "," + myRs.getString("end_time") +
                    "," + myRs.getString("shift_id") + "," + myRs.getString("availability") +
                    "," + myRs.getString("position") + "," + myRs.getString("checkedIn");
        } catch (SQLException exception) {
            exception.printStackTrace();
            return editedShift;
        }
        return editedShift;
    }

    /*
    Name: editShiftAvailability
    Parameters:
        int shiftID: The shift unique ID.
        String available: The new availability of the shift. Either "true" or "false".
    Description: Changes a shift's availability, so it can be picked up.
    Return:
        String: If the availability was changed it returns "editedShift", and if there was a problem changing it then
        it returns an empty string.
     */
    protected String editShiftAvailability(int shiftID, String available) {
        try {
            Statement myStatement = dbConnection.createStatement();
            myStatement.executeUpdate("update Shifts set availability=" + available + " where shift_id=" + shiftID);
        } catch (Exception exception) {
            exception.printStackTrace();
            return "";
        }
        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: checkIn
    Parameters:
        int shiftID: The shift unique ID.
    Description: Marks a shift as worked.
    Return:
        int: If the shift was successfully checked in then in it returns the shift's id, if not it -1.
     */
    protected int checkIn(int shiftID) {
        try {
            Statement myStatement = dbConnection.createStatement();
            myStatement.executeUpdate("update Shifts set checkedIn=true where shift_id=" + shiftID);
        } catch (Exception exception) {
            exception.printStackTrace();
            return -1;
        }
        return shiftID;
    }

    /*
    Name: allShifts
    Parameters:
        None
    Description: Returns the data for all shifts in the database.
    Return:
        String: Each shift is separated by slashes and each detail separated by commas.
     */
    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"))
                        .append(",").append(myRs.getString("position"))
                        .append(",").append(myRs.getString("checkedIn"));
            }
        } catch (SQLException exception) {
            exception.printStackTrace();
        }
        return response.toString();
    }
}