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 {

    private final Connection dbConnection;

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

    protected String addTimeOff(int employeeID, String startDate, String endDate, boolean approved, String reason){
        String newTimeOff = "";
        Date sD = Date.valueOf(startDate);
        Date eD = Date.valueOf(endDate);
        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;
    }

    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;
    }

    protected String setTimeOffApproval(int ID, String approved){
        String editedTimeOff = "";
        System.out.println(ID + " and " + approved);
        try {
            Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            myStatement.executeUpdate("update TimeOff set approved=" + approved + " where ID=" + ID);
            ResultSet myRs = myStatement.executeQuery("select * from TimeOff where ID=" + ID);
            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;
    }

    protected void removeAllTimeOffByID(int employeeID) {
        try {
            Statement myStatement = dbConnection.createStatement();
            myStatement.executeUpdate("DELETE FROM TimeOff WHERE (employeeID =" + employeeID + ")");
        } catch (SQLException exception) {
            exception.printStackTrace();
        }
    }

    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();
    }
}