package com.example.scheduler_server;
import java.sql.*;
/*
Name: Availabilities
Description: Responsible for sending queries and receiving responses from the database that deal with the
Availability table.
*/
public class Availability {
// Object that facilitates the connection to the database
private final Connection dbConnection;
/*
Name: Availability Constructor
Parameters:
Connection c: The connection to the database.
Description: Instantiates an Availability object and sets the global connection for the methods.
Return: Availability Object
*/
protected Availability(Connection c) {
this.dbConnection = c;
}
/*
Name: newEmployeeAvailability
Parameters:
int employeeID: The employees unique ID.
Description: Creates an availability for an employee.
Return:
String: The availability of the new employee. Where the details are separated by commas.
*/
protected String newEmployeeAvailability(int employeeID){
String newAvailability = "";
try {
Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
myStatement.executeUpdate("insert into Availabilities (employeeID) " +
"values ('" + employeeID + "')");
ResultSet myRs = myStatement.executeQuery("select * from availabilities where employeeID=" + employeeID);
if (myRs.last()) {
newAvailability = myRs.getString("employeeID") +
"," + myRs.getString("sundayStart") + "," + myRs.getString("sundayEnd") +
"," + myRs.getString("mondayStart") + "," + myRs.getString("mondayEnd") +
"," + myRs.getString("tuesdayStart") + "," + myRs.getString("tuesdayEnd") +
"," + myRs.getString("wednesdayStart") + "," + myRs.getString("wednesdayEnd") +
"," + myRs.getString("thursdayStart") + "," + myRs.getString("thursdayEnd") +
"," + myRs.getString("fridayStart") + "," + myRs.getString("fridayEnd") +
"," + myRs.getString("saturdayStart") + "," + myRs.getString("saturdayEnd");
}
} catch (SQLException exception) {
exception.printStackTrace();
return newAvailability;
}
return newAvailability;
}
/*
Name: removeEmployeeAvailability
Parameters:
int employeeID: The employees unique ID.
Description: Removes the availability for an employee.
Return: void
*/
protected void removeEmployeeAvailability(int employeeID){
try {
Statement myStatement = dbConnection.createStatement();
myStatement.executeUpdate("delete from Availabilities where (employeeID =" + employeeID + ")");
} catch (SQLException exception) {
exception.printStackTrace();
}
}
/*
Name: editAvailability
Parameters:
int employeeID: The employees unique ID.
int dayOfTheWeek: The day of the week, where 0=sunday, 1=monday,..., 6=saturday.
int startTime: Start time represented by 24 hour time. Example: 1330 (1:30-PM).
int endTime: End time represented by 24 hour time. Example: 1330 (1:30-PM).
Description: Changes the availability of an employee for a given day.
Return:
String: The employee's id, day of the week, start time, and end time, separated by commas.
*/
protected String editAvailability(int employeeID, int dayOfTheWeek, int startTime, int endTime){
String editedAvailability = "";
String dayOfTheWeekString = "";
try {
Statement myStatement = dbConnection.createStatement();
switch (dayOfTheWeek) {
case 0 -> dayOfTheWeekString = "sunday";
case 1 -> dayOfTheWeekString = "monday";
case 2 -> dayOfTheWeekString = "tuesday";
case 3 -> dayOfTheWeekString = "wednesday";
case 4 -> dayOfTheWeekString = "thursday";
case 5 -> dayOfTheWeekString = "friday";
case 6 -> dayOfTheWeekString = "saturday";
}
if (dayOfTheWeekString.isEmpty()) {
return editedAvailability;
}
myStatement.executeUpdate("update Availabilities set " + dayOfTheWeekString + "Start=" + startTime +
", " + dayOfTheWeekString + "End=" + endTime + " where employeeID=" + employeeID);
ResultSet myRs = myStatement.executeQuery("select * from Availabilities where employeeID=" + employeeID);
myRs.next();
editedAvailability = myRs.getString("employeeID" ) + "," + dayOfTheWeek + "," +
myRs.getString(dayOfTheWeekString+"Start") +
"," + myRs.getString(dayOfTheWeekString+"End");
} catch (SQLException exception) {
exception.printStackTrace();
return editedAvailability;
}
return editedAvailability;
}
/*
Name: allAvailability
Parameters: none
Description: Gets the availability of each employee.
Return:
String: All the availabilities of each employee, where each employee's availability is separated by slashes, and
each time is separated by commas.
*/
protected String allAvailabilities(){
StringBuilder response = new StringBuilder("allAvailabilities");
try {
Statement myStatement = dbConnection.createStatement();
ResultSet myRs = myStatement.executeQuery("select * from Availabilities");
while (myRs.next()) {
response.append("/").append(myRs.getString("employeeID"))
.append(",").append(myRs.getString("sundayStart"))
.append(",").append(myRs.getString("sundayEnd"))
.append(",").append(myRs.getString("mondayStart"))
.append(",").append(myRs.getString("mondayEnd"))
.append(",").append(myRs.getString("tuesdayStart"))
.append(",").append(myRs.getString("tuesdayEnd"))
.append(",").append(myRs.getString("wednesdayStart"))
.append(",").append(myRs.getString("wednesdayEnd"))
.append(",").append(myRs.getString("thursdayStart"))
.append(",").append(myRs.getString("thursdayEnd"))
.append(",").append(myRs.getString("fridayStart"))
.append(",").append(myRs.getString("fridayEnd"))
.append(",").append(myRs.getString("saturdayStart"))
.append(",").append(myRs.getString("saturdayEnd"));
}
} catch (SQLException exception) {
exception.printStackTrace();
}
return response.toString();
}
// Availability testing suite
// This test suite must be started while the Availability, and Employees tables are empty.
public static void main(String[] args) {
try {
Connection dbConnection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ScheduleApp", "root", "password");
Availability availability = new Availability(dbConnection);
Staff staff = new Staff(dbConnection);
staff.addEmployee("John", "Doe", "true", "john.doe@email.com",
"1234567890", 20.0f);
String queryString;
int success = 0;
System.out.println("Availability Testing Suite:");
System.out.println("\nAvailability Test 1: ");
System.out.println("Test Scenario: " + "Testing allAvailabilities() while table is empty");
System.out.println("Test Data: " + "none");
System.out.println("Expected Results: " + "allAvailabilities");
queryString = availability.allAvailabilities();
System.out.println("Actual Results: " + queryString);
if (queryString.equals("allAvailabilities")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nAvailability Test 2: ");
System.out.println("Test Scenario: " + "Testing newEmployeeAvailability()");
System.out.println("Test Data: " + "employeeID=1");
System.out.println("Expected Results: " + "1,0,2330,0,2330,0,2330,0,2330,0,2330,0,2330,0,2330");
queryString = availability.newEmployeeAvailability(1);
System.out.println("Actual Results: " + queryString);
if (queryString.equals("1,0,2330,0,2330,0,2330,0,2330,0,2330,0,2330,0,2330")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nAvailability Test 3: ");
System.out.println("Test Scenario: " + "Testing editAvailability()");
System.out.println("Test Data: " + "employeeID=1, dayOfTheWee=0, startTime=900, endTime=2100");
System.out.println("Expected Results: " + "1,0,900,2100");
queryString = availability.editAvailability(1, 0,900,2100);
System.out.println("Actual Results: " + queryString);
if (queryString.equals("1,0,900,2100")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nAvailability Test 4: ");
System.out.println("Test Scenario: " + "Testing removeEmployeeAvailability()");
System.out.println("Test Data: " + "employeeID=1");
System.out.println("Expected Results: " + "allAvailabilities");
availability.removeEmployeeAvailability(1);
queryString = availability.allAvailabilities();
System.out.println("Actual Results: " + queryString);
if (queryString.equals("allAvailabilities")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nAvailability Unit Testing: Passed " + success + "/4.");
} catch (Exception exception) {
exception.printStackTrace();
}
}
}