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){
String newEmployee = "";
try {
Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
if (email.equals("empty")) {
myStatement.executeUpdate("insert into Employees (first_name,last_name,isManager,email,phoneNumber,wage)" +
" values ('" + firstName + "','" + lastName + "'," + isManager + ", null,'" +
phoneNumber + "'," + wage + ")");
} else {
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;
}
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;
}
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;
}
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 {
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();
if (myRs.getString("positions") == null) {
return removedPosition;
}
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 String 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();
return "";
}
return position;
}
/*
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();
}
// Staff testing suite
// This test suite must be started while the staff table is empty.
public static void main(String[] args) {
try {
Connection dbConnection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ScheduleApp", "root", "password");
Staff staff = new Staff(dbConnection);
Positions positions = new Positions(dbConnection);
positions.addPosition("dishwasher", 10.00f); // Required to test position methods.
positions.addPosition("cashier", 10.00f); // Required to test position methods.
String queryString;
int queryInt;
int success = 0;
System.out.println("Staff Test 1: ");
System.out.println("Test Scenario: " + "Testing allEmployees()");
System.out.println("Test Data: " + "none");
System.out.println("Expected Results: " + "allEmployees");
queryString = staff.allEmployees();
System.out.println("Actual Results: " + queryString);
if (queryString.equals("allEmployees")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nStaff Test 2: ");
System.out.println("Test Scenario: " + "Testing addEmployee()");
System.out.println("Test Data: " + "firstName='John', lastName='Doe', isManager='true', " +
"email='john.doe@email.com', phoneNumber='1234567890', wage=11.81f");
System.out.println("Expected Results: " + "1,John,Doe,1,john.doe@email.com,1234567890,11.81, ");
queryString = staff.addEmployee("John", "Doe", "true", "john.doe@email.com",
"1234567890", 11.81f);
System.out.println("Actual Results: " + queryString);
if (queryString.equals("1,John,Doe,1,john.doe@email.com,1234567890,11.81, ")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nStaff Test 3: ");
System.out.println("Test Scenario: " + "Testing editEmployee()");
System.out.println("Test Data: " + "firstName='Jonathan', lastName='Deer', isManager='false', " +
"email='jonathan.deer@email.com', phoneNumber='0987654321', wage=15.00f");
System.out.println("Expected Results: " + "1,Jonathan,Deer,0,jonathan.deer@email.com,0987654321,15.0,null");
queryString = staff.editEmployee(1,"Jonathan", "Deer", "false", "jonathan.deer@email.com",
"0987654321", 15.00f);
System.out.println("Actual Results: " + queryString);
if (queryString.equals("1,Jonathan,Deer,0,jonathan.deer@email.com,0987654321,15.0,null")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nStaff Test 4: ");
System.out.println("Test Scenario: " + "Testing addPosition()");
System.out.println("Test Data: " + "employeeID='1', position='dishwasher'");
System.out.println("Expected Results: " + "1/dishwasher");
queryString = staff.addPosition(1, "dishwasher");
System.out.println("Actual Results: " + queryString);
if (queryString.equals("1/dishwasher")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nStaff Test 5: ");
System.out.println("Test Scenario: " + "Testing removePosition()");
System.out.println("Test Data: " + "employeeID='1', position='dishwasher'");
System.out.println("Expected Results: " + "1/dishwasher");
queryString = staff.removePosition(1, "dishwasher");
System.out.println("Actual Results: " + queryString);
if (queryString.equals("1/dishwasher")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
//Setup to test removePositionAllEmployees() properly
staff.addEmployee("Jane", "Doe", "false", "jane.doe@email.com",
"1234567890", 10.0f);
staff.addPosition(2, "cashier");
staff.addPosition(1, "cashier");
System.out.println("\nStaff Test 6: ");
System.out.println("Test Scenario: " + "Testing removePositionAllEmployees() removes the the position from" +
"all employees.");
System.out.println("Test Data: " + "position='cashier'");
System.out.println("Expected Results: " + "allEmployees/1,Jonathan,Deer,0,jonathan.deer@email.com,0987654321," +
"15.0,null/2,Jane,Doe,0,jane.doe@email.com,1234567890,10.0,null");
staff.removePositionAllEmployees("cashier");
queryString = staff.allEmployees();
System.out.println("Actual Results: " + queryString);
if (queryString.equals("allEmployees/1,Jonathan,Deer,0,jonathan.deer@email.com,0987654321,15.0,null/2,Jane," +
"Doe,0,jane.doe@email.com,1234567890,10.0,null")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nStaff Test 7: ");
System.out.println("Test Scenario: " + "Testing removeEmployee()");
System.out.println("Test Data: " + "employeeID='1'");
System.out.println("Expected Results: " + "1");
queryInt = staff.removeEmployee(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("\nStaff Unit Testing: Passed " + success + "/7.");
} catch (Exception exception) {
System.out.println("Exception: Tests failed");
}
}
}