TimeOff.java

package com.example.scheduler_server;

import java.sql.*;

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

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

    /*
    Name: TimeOff Constructor
    Parameters:
        Connection c: The connection to the database.
    Description: Instantiates a TimeOff object and sets the global connection for the methods.
    Return: TimeOff Object
     */
    protected TimeOff(Connection c) {
        this.dbConnection = c;
    }

    /*
    Name: addTimeOff
    Parameters:
        int employeeID: The ID of an employee.
        String startDate: The start date of the time off in "yyyy-mm-dd" format.
        String endDate: The end date of the time off in "yyyy-mm-dd" format.
        boolean approved: True if the time off is approved by a manager.
        String reason: Why the time off was requested.
    Description:
    Return:
        String: The details of the time off separated by commas.
     */
    protected String addTimeOff(int employeeID, String startDate, String endDate, boolean approved, String reason){
        String newTimeOff = "";
        try {
            Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            myStatement.executeUpdate("INSERT INTO TimeOff (employeeID, startDate, endDate, approved, reason) " +
                    "VALUES (" + employeeID +", '"+ startDate +"', '"+ endDate +"', "+ approved +", '" + reason + "')");
            ResultSet myRs = myStatement.executeQuery("select * from TimeOff where employeeID=" + employeeID +
                    " and startDate='" + startDate + "' and endDate='" + endDate + "'");
            if (myRs.last()) {
                newTimeOff = myRs.getString("ID") + "," + myRs.getString("employeeID" ) + "," +
                        myRs.getString("startDate") + "," + myRs.getString("endDate") +
                        "," + myRs.getString("approved") + "," + myRs.getString("reason");
            }
        } catch (SQLException exception) {
            exception.printStackTrace();
            return newTimeOff;
        }
        System.out.println("Shift successfully added");
        return newTimeOff;
    }

    /*
    Name: removeTimeOff
    Parameters:
        int timeOffID: The id of the time off request.
    Description: Removes the time off request.
    Return:
        String: The time off id of the shift if it was removed, and -1 if there was a problem with the query.
     */
    protected int removeTimeOff(int timeOffID){
        try {
            Statement myStatement = dbConnection.createStatement();
            myStatement.executeUpdate("DELETE FROM TimeOff WHERE (ID =" + timeOffID + ")");
        } catch (SQLException exception) {
            exception.printStackTrace();
            return -1;
        }
        return timeOffID;
    }

    /*
    Name: setTimeOffApproval
    Parameters:
        int timeOffID: The id of the time off request.
        String approved: "true" if its approved and "false" if it's not.
    Description: Change the approval status of a request.
    Return:
        String: If the time off approval was successfully changed than it returns the id and the approval separated by
        commas, or if there was an error with the query then it returns an empty string.
     */
    protected String setTimeOffApproval(int timeOffID, String approved){
        String editedTimeOff = "";
        System.out.println(timeOffID + " and " + approved);
        try {
            Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            myStatement.executeUpdate("update TimeOff set approved=" + approved + " where ID=" + timeOffID);
            ResultSet myRs = myStatement.executeQuery("select * from TimeOff where ID=" + timeOffID);
            if (myRs.last()) {
                editedTimeOff = myRs.getString("ID") + "," + myRs.getString("approved");
            }
        } catch (SQLException exception) {
            exception.printStackTrace();
            return editedTimeOff;
        }
        System.out.println("Shift successfully added");
        return editedTimeOff;
    }

    /*
    Name: removeAllTimeOffByID
    Parameters:
        int employeeID: The id of an employee.
    Description: Removes all time off request for a specified employee.
    Return: void
     */
    protected void removeAllTimeOffByID(int employeeID) {
        try {
            Statement myStatement = dbConnection.createStatement();
            myStatement.executeUpdate("DELETE FROM TimeOff WHERE (employeeID =" + employeeID + ")");
        } catch (SQLException exception) {
            exception.printStackTrace();
        }
    }

    /*
    Name: allTimeOff
    Parameters: none
    Description: Gets all the time off request.
    Return:
        String: All the time off data where all the time offs are separated by slashes and each detail is separated by
        commas.
     */
    protected String allTimeOff(){
        StringBuilder response = new StringBuilder("allTimeOff");
        try {
            Statement myStatement = dbConnection.createStatement();
            ResultSet myRs = myStatement.executeQuery("SELECT * FROM TimeOff");
            while (myRs.next()) {
                response.append("/").append(myRs.getString("ID"))
                        .append(",").append(myRs.getString("employeeID"))
                        .append(",").append(myRs.getString("startDate"))
                        .append(",").append(myRs.getString("endDate"))
                        .append(",").append(myRs.getString("approved"))
                        .append(",").append(myRs.getString("reason"));
            }
        } catch (SQLException exception) {
            exception.printStackTrace();
        }
        return response.toString();
    }
}