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;
        }
        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);
        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: The id of the employee.
    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();
    }

    // Schedule testing suite
    // This test suite must be started while the Shifts, Employees, and Positions tables are empty.
    public static void main(String[] args) {
        try {
            Connection dbConnection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ScheduleApp", "root", "password");
            Schedule schedule = new Schedule(dbConnection);
            Positions positions = new Positions(dbConnection);
            Staff staff = new Staff(dbConnection);
            staff.addEmployee("John", "Doe", "true", "john.doe@email.com",
                    "1234567890", 20.0f);
            staff.addEmployee("Jane", "Doe", "true", "jane.doe@email.com",
                    "1234567891", 20.0f);
            staff.addPosition(1, "cashier");
            staff.addPosition(2, "cashier");
            positions.addPosition("cashier", 10.00f); // Required to test position methods.
            String queryString;
            int queryInt;
            int success = 0;
            System.out.println("Schedule Testing Suite:");

            System.out.println("\nSchedule Test 1: ");
            System.out.println("Test Scenario: " + "Testing allShifts()");
            System.out.println("Test Data: " + "none");
            System.out.println("Expected Results: " + "allShifts");
            queryString = schedule.allShifts();
            System.out.println("Actual Results: " + queryString);
            if (queryString.equals("allShifts")){
                success += 1;
                System.out.println("Pass/Fail: Pass");
            } else {
                System.out.println("Pass/Fail: Fail");
            }

            System.out.println("\nSchedule Test 2: ");
            System.out.println("Test Scenario: " + "Testing addShift()");
            System.out.println("Test Data: " + "ID=1, date='2022-03-22', start=900, end=1700, position='cashier");
            System.out.println("Expected Results: " + "1,2022-03-22,900,1700,1,0,cashier,0");
            queryString = schedule.addShift(1, "2022-03-22", 900, 1700, "cashier");
            System.out.println("Actual Results: " + queryString);
            if (queryString.equals("1,2022-03-22,900,1700,1,0,cashier,0")){
                success += 1;
                System.out.println("Pass/Fail: Pass");
            } else {
                System.out.println("Pass/Fail: Fail");
            }

            System.out.println("\nSchedule Test 3: ");
            System.out.println("Test Scenario: " + "Testing editShift()");
            System.out.println("Test Data: " + "shiftID=1, date='2022-03-23', start='1200', end='2100', " +
                    "available='true', employeeID=2");
            System.out.println("Expected Results: " + "1,2022-03-23,1200,2100,1,1,cashier,0");
            queryString = schedule.editShift(1, "2022-03-23", "1200", "2100", "true", 1);
            System.out.println("Actual Results: " + queryString);
            if (queryString.equals("1,2022-03-23,1200,2100,1,1,cashier,0")){
                success += 1;
                System.out.println("Pass/Fail: Pass");
            } else {
                System.out.println("Pass/Fail: Fail");
            }

            System.out.println("\nSchedule Test 4: ");
            System.out.println("Test Scenario: " + "Testing editShiftAvailability()");
            System.out.println("Test Data: " + "shiftID=1, available='false'");
            System.out.println("Expected Results: " + "editedShift");
            queryString = schedule.editShiftAvailability(1, "false");
            System.out.println("Actual Results: " + queryString);
            if (queryString.equals("editedShift")){
                success += 1;
                System.out.println("Pass/Fail: Pass");
            } else {
                System.out.println("Pass/Fail: Fail");
            }

            System.out.println("\nSchedule Test 5: ");
            System.out.println("Test Scenario: " + "Testing checkIn()");
            System.out.println("Test Data: " + "shiftID=1");
            System.out.println("Expected Results: " + "1");
            queryInt = schedule.checkIn(1);
            System.out.println("Actual Results: " + queryInt);
            if (queryInt == 1){
                success += 1;
                System.out.println("Pass/Fail: Pass");
            } else {
                System.out.println("Pass/Fail: Fail");
            }

            System.out.println("\nSchedule Test 6: ");
            System.out.println("Test Scenario: " + "Testing removeShiftByID()");
            System.out.println("Test Data: " + "shiftID=1");
            System.out.println("Expected Results: " + "1");
            queryInt = schedule.removeShiftByID(1);
            System.out.println("Actual Results: " + queryInt);
            if (queryInt == 1){
                success += 1;
                System.out.println("Pass/Fail: Pass");
            } else {
                System.out.println("Pass/Fail: Fail");
            }

            //Setup to test removeAllShiftsByID()
            schedule.addShift(1, "2022-03-24", 900, 1700, "cashier");
            schedule.addShift(1, "2022-03-25", 900, 1700, "cashier");

            System.out.println("\nSchedule Test 7: ");
            System.out.println("Test Scenario: " + "Adding 2 shifts for 1 employee using addShift() for test 8");
            System.out.println("Test Data: " + "none");
            System.out.println("Expected Results: " + "allShifts/1,2022-03-24,900,1700,2,0,cashier,0/1,2022-03-25,900,1700,3,0,cashier,0");
            queryString = schedule.allShifts();
            System.out.println("Actual Results: " + queryString);
            if (queryString.equals("allShifts/1,2022-03-24,900,1700,2,0,cashier,0/1,2022-03-25,900,1700,3,0,cashier,0")){
                success += 1;
                System.out.println("Pass/Fail: Pass");
            } else {
                System.out.println("Pass/Fail: Fail");
            }

            System.out.println("\nSchedule Test 8: ");
            System.out.println("Test Scenario: " + "Testing removeAllShiftsByID()");
            System.out.println("Test Data: " + "ID=1");
            System.out.println("Expected Results: " + "allShifts");
            schedule.removeAllShiftsByID(1);
            queryString = schedule.allShifts();
            System.out.println("Actual Results: " + queryString);
            if (queryString.equals("allShifts")){
                success += 1;
                System.out.println("Pass/Fail: Pass");
            } else {
                System.out.println("Pass/Fail: Fail");
            }

            System.out.println("\nSchedule Unit Testing: Passed " + success + "/8.");
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }
}