Staff.java

package com.example.scheduler_server;

import java.sql.*;
import java.util.Locale;

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

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

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

    /*
    Name: addEmployee
    Parameters:
        String firstName: First name of the employee.
        String lastName: Last name of the employee.
        String isManager: "true" if the employee is a manager and "false" if the employee is not a manager.
        String email: The email of the employee.
        String phoneNumber: The phone number of the employee.
        float: wage: The wage of the employee.
    Description: Adds an employee by querying the database. Also, assigns a unique ID.
    Return:
        String newEmployee: If the employee was added successfully, then it returns their new details, else returns an empty string.
     */
    protected String addEmployee(String firstName, String lastName, String isManager, String email, String phoneNumber,
                                 float wage){
        System.out.println(firstName + " " + lastName + " " + isManager + " " + email + " " + phoneNumber + " " + wage);
        String newEmployee = "";
        try {
            Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            myStatement.executeUpdate("insert into Employees (first_name,last_name,isManager,email,phoneNumber,wage)" +
                    " values ('" + firstName + "','" + lastName + "'," + isManager + ",'" + email + "','" +
                    phoneNumber + "'," + wage + ")");
            ResultSet myRs = myStatement.executeQuery("select * from Employees where first_name='" + firstName
                    + "' and last_name='" + lastName + "'");
            if (myRs.last()) {
                newEmployee = myRs.getString("employee_ID") + "," + myRs.getString("first_name") +
                        "," + myRs.getString("last_name") + "," + myRs.getString("isManager") +
                        "," + myRs.getString("email") + "," + myRs.getString("phoneNumber") +
                        "," + myRs.getString("wage") + ", ";
            }
        } catch (Exception exception) {
            exception.printStackTrace();
            return newEmployee;
        }
        System.out.println("Employee Added");
        return newEmployee;
    }

    /*
    Name: editEmployee
    Parameters:
        int employeeID: The unique ID of the employee.
        String firstName: First name of the employee.
        String lastName: Last name of the employee.
        String isManager: "true" if the employee is a manager and "false" if the employee is not a manager.
        String email: The email of the employee.
        String phoneNumber: The phone number of the employee.
        float wage: The wage of the employee.
    Description: Edits the information of an existing employee.
    Return:
        String: If the employee exists and there were no error in the query, then the information of the
        updated employee is sent back as a string.
     */
    protected String editEmployee(int employeeID, String firstName, String lastName, String isManager, String email, String phoneNumber,
                                 float wage){
        String editedEmployee = "";
        try {
            Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            myStatement.executeUpdate("update Employees set first_name='" + firstName + "',last_name='"+
                    lastName + "',isManager=" + isManager + ",email='" + email + "',phoneNumber='" + phoneNumber +"',wage=" +
                    wage + " where employee_ID=" + employeeID);
            ResultSet myRs = myStatement.executeQuery("select * from Employees where employee_ID=" + employeeID);
            if (myRs.last()) {
                editedEmployee = myRs.getString("employee_ID") + "," + myRs.getString("first_name") +
                        "," + myRs.getString("last_name") + "," + myRs.getString("isManager") +
                        "," + myRs.getString("email") + "," + myRs.getString("phoneNumber") +
                        "," + myRs.getString("wage") + "," + myRs.getString("positions");
            }
        } catch (Exception exception) {
            exception.printStackTrace();
            return editedEmployee;
        }
        System.out.println("Employee Added");
        return editedEmployee;
    }

    /*
    Name: removeEmployee
    Parameters:
        int ID: The employees unique ID.
    Description: Removes the employee with the same ID by querying the database.
    Return:
        int: If the employee was successfully removed from the table then there old id is returned, if there was a
        problem with the query then -1 is returned.
     */
    protected int removeEmployee(int ID) {
        try {
            Statement myStatement = dbConnection.createStatement();
            myStatement.executeUpdate("delete from Employees where employee_id = " + ID);
        } catch (Exception exception) {
            exception.printStackTrace();
            return -1;
        }
        System.out.println("Employee Removed");
        return ID;
    }

    /*
    Name: addPosition
    Parameters:
        int ID: The employees unique ID.
        String position: A position from the table of positions.
    Description: The new position to add to the employees list of trained positions.
    Return:
        String: The id of the employee and the position that was added. If the methods fails then it will return an
        empty string.
     */
    protected String addPosition(int employeeID, String position) {
        try {
            System.out.println(employeeID + " " + position);
            Statement myStatement = dbConnection.createStatement();
            ResultSet myRs = myStatement.executeQuery("select exists(select *from Positions where position='" + position + "')");
            myRs.next();
            if (myRs.getString("exists(select *from Positions where position='" + position + "')").equals("0")) {
                return "";
            }

            myRs = myStatement.executeQuery("select positions from Employees where employee_ID=" + employeeID);
            myRs.next();
            if (myRs.getString("positions") == null) {
                myStatement.executeUpdate("update Employees set positions='" + position + "' where employee_ID = " + employeeID);
            } else {
                myStatement.executeUpdate("update Employees set positions=concat(positions,'." + position + "') where employee_ID = " + employeeID);
            }
        } catch (Exception exception) {
            exception.printStackTrace();
            return "";
        }
        return employeeID + "/" + position;
    }

    /*
    Name: removePosition
    Parameters:
        int ID: The employees unique ID.
        String position: A position from the table of positions.
    Description: To remove a position from the list of trained position.
    Return:
        String: The id of the employee and the position that was removed. If the methods fails then it will return an
        empty string.
     */
    protected String removePosition(int ID, String position) {
        String removedPosition = "";

        StringBuilder newPositions = new StringBuilder();
        try {
            Statement myStatement = dbConnection.createStatement();
            ResultSet myRs = myStatement.executeQuery("select positions from Employees where employee_ID=" + ID);
            myRs.next();
            for (String s : myRs.getString("positions").split("\\.")) {
                if (!s.equalsIgnoreCase(position)) {
                    if (newPositions.isEmpty()) {
                        newPositions.append(s);
                    } else {
                        newPositions.append(".").append(s);
                    }
                } else {
                    removedPosition = ID + "/" + position;
                }
            }
            if (newPositions.isEmpty()){
                myStatement.executeUpdate("update Employees set positions=null where employee_ID = " + ID);
            } else {
                myStatement.executeUpdate("update Employees set positions='" + newPositions + "' where employee_ID = " + ID);
            }
        } catch (Exception exception) {
            exception.printStackTrace();
            return removedPosition;
        }

        return removedPosition;
    }

    /*
    Name: removePositionAllEmployees
    Parameters:
        String position: A position from the table of positions.
    Description: To remove a position from all employees.
    Return: void
     */
    protected void removePositionAllEmployees(String position) {
        try {
            String positionLowerCase = position.toLowerCase(Locale.ROOT);
            Statement myStatement = dbConnection.createStatement();
            ResultSet myRs = myStatement.executeQuery("select * from Employees");
            while (myRs.next()) {
                removePosition(Integer.parseInt(myRs.getString("employee_ID")), positionLowerCase);
            }
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }

    /*
    Name: allEmployees
    Parameters: none
    Description: Gets all the data from all employees.
    Return:
        String: Each employee is separated by slashes and each detail separated by commas.
     */
    protected String allEmployees(){
        StringBuilder response = new StringBuilder("allEmployees");
        try {
            Statement myStatement = dbConnection.createStatement();
            ResultSet myRs = myStatement.executeQuery("select * from Employees");
            while (myRs.next()) {
                response.append("/").append(myRs.getString("employee_ID")).append(",")
                        .append(myRs.getString("first_name")).append(",")
                        .append(myRs.getString("last_name")).append(",")
                        .append(myRs.getString("isManager")).append(",")
                        .append(myRs.getString("email")).append(",")
                        .append(myRs.getString("phoneNumber")).append(",")
                        .append(myRs.getString("wage")).append(",")
                        .append(myRs.getString("positions"));
            }
        } catch (Exception exception) {
            exception.printStackTrace();
            return "Error retrieving employees from database";
        }
        return response.toString();
    }

    public static void main(String[] args) {
        /* This is for running test on the staff class although the cass for the DriverManager can be used for testing
        other classed.
        try {
            Staff staff = new Staff(DriverManager.getConnection("jdbc:mysql://localhost:3306/ScheduleApp", "root", "password"));
            staff.removePosition(1, "dishwasher");
        } catch (Exception exception) {
            exception.printStackTrace();
        }
         */

    }
}