package com.example.scheduler_server;
import java.sql.*;
/*
Name: Schedule
Description: Responsible for sending queries and receiving responses from the database that deal with the
Shifts table.
*/
public class Schedule {
// Object that facilitates the connection to the database
private final Connection dbConnection;
/*
Name: Schedule Constructor
Parameters:
Connection c: The connection to the database.
Description: Instantiates a Schedule object and sets the global connection for the methods.
Return: Schedule Object
*/
protected Schedule(Connection c) {
this.dbConnection = c;
}
/*
Name: addShift
Parameters:
int ID: The employees unique ID.
Date: The date as a string in the form of "yyyy-mm-dd".
start: The employees shift start time.
end: The employees shift end time.
Description: Adds a new shift to the database, referencing the employees ID, and assigning a unique shift number.
Return: none
*/
protected String addShift(int ID, String date, int start, int end, String position){
String newShift = "";
Date newDate = Date.valueOf(date);
try {
Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
myStatement.executeUpdate("INSERT INTO shifts (employee_id, full_date, start_time, end_time, position) " +
"VALUES ('" + ID + "', '" + newDate + "', '" + start + "', '" + end + "', '" + position + "')");
ResultSet myRs = myStatement.executeQuery("select * from Shifts where employee_ID=" + ID +
" and full_date='" + newDate + "' and start_time=" + start + " and end_time=" + end + " and availability=false");
if (myRs.last()) {
newShift = myRs.getString("employee_id" ) + "," +
myRs.getString("full_date") + "," + myRs.getString("start_time") +
"," + myRs.getString("end_time") + "," + myRs.getString("shift_id") +
"," + myRs.getString("availability") + "," + myRs.getString("position") +
"," + myRs.getString("checkedIn");
}
} catch (SQLException exception) {
exception.printStackTrace();
return newShift;
}
return newShift;
}
/*
Name: removeShiftByID
Parameters:
int shiftID: The shift unique ID.
Description: Removes the employees shift matching the shift date.
Return: none
*/
protected int removeShiftByID(int shiftID){
try {
Statement myStatement = dbConnection.createStatement();
myStatement.executeUpdate("DELETE FROM shifts WHERE (shift_id =" + shiftID + ")");
} catch (SQLException exception) {
exception.printStackTrace();
return -1;
}
return shiftID;
}
/*
Name: editShift
Parameters:
int shift_id: The shift unique ID.
String date: The date of the shift in the form "YYYY-MM-DD".
String start: The start time of the shift in 24-hour time "HHMM".
String end: The end time of the shift in 24-hour time "HHMM".
Description: Edits the time and date of the of a shift if it has the same shift id
Return: none
*/
protected String editShift(Integer shiftID, String date, String start,String end, String available, int employeeID){
String editedShift = "";
Date newDate = Date.valueOf(date);
try {
Statement myStatement = dbConnection.createStatement();
myStatement.executeUpdate("update shifts set full_date='" + newDate + "',start_time=" + start + ",end_time=" + end
+ ",availability=" + available +",employee_ID=" + employeeID + " where shift_id=" + shiftID);
ResultSet myRs = myStatement.executeQuery("select * from Shifts where shift_id=" + shiftID);
myRs.next();
editedShift = myRs.getString("employee_id" ) + "," + myRs.getString("full_date") +
"," + myRs.getString("start_time") + "," + myRs.getString("end_time") +
"," + myRs.getString("shift_id") + "," + myRs.getString("availability") +
"," + myRs.getString("position") + "," + myRs.getString("checkedIn");
} catch (SQLException exception) {
exception.printStackTrace();
return editedShift;
}
return editedShift;
}
/*
Name: editShiftAvailability
Parameters:
int shiftID: The shift unique ID.
String available: The new availability of the shift. Either "true" or "false".
Description: Changes a shift's availability, so it can be picked up.
Return:
String: If the availability was changed it returns "editedShift", and if there was a problem changing it then
it returns an empty string.
*/
protected String editShiftAvailability(int shiftID, String available) {
try {
Statement myStatement = dbConnection.createStatement();
myStatement.executeUpdate("update Shifts set availability=" + available + " where shift_id=" + shiftID);
} catch (Exception exception) {
exception.printStackTrace();
return "";
}
return "editedShift";
}
/*
Name: removeAllShiftsByID
Parameters:
int ID: The id of the employee.
Description: Removes all shifts in the database for a specific employee
Return: void
*/
protected void removeAllShiftsByID(int ID) {
try {
Statement myStatement = dbConnection.createStatement();
myStatement.executeUpdate("DELETE FROM shifts WHERE (employee_ID =" + ID + ")");
} catch (SQLException exception) {
System.out.println("Error removing all shifts by id.");
exception.printStackTrace();
}
}
/*
Name: checkIn
Parameters:
int shiftID: The shift unique ID.
Description: Marks a shift as worked.
Return:
int: If the shift was successfully checked in then in it returns the shift's id, if not it -1.
*/
protected int checkIn(int shiftID) {
try {
Statement myStatement = dbConnection.createStatement();
myStatement.executeUpdate("update Shifts set checkedIn=true where shift_id=" + shiftID);
} catch (Exception exception) {
exception.printStackTrace();
return -1;
}
return shiftID;
}
/*
Name: allShifts
Parameters:
None
Description: Returns the data for all shifts in the database.
Return:
String: Each shift is separated by slashes and each detail separated by commas.
*/
protected String allShifts(){
StringBuilder response = new StringBuilder("allShifts");
try {
Statement myStatement = dbConnection.createStatement();
ResultSet myRs = myStatement.executeQuery("SELECT * FROM shifts");
while (myRs.next()) {
response.append("/").append(myRs.getString("employee_id"))
.append(",").append(myRs.getString("full_date"))
.append(",").append(myRs.getString("start_time"))
.append(",").append(myRs.getString("end_time"))
.append(",").append(myRs.getString("shift_id"))
.append(",").append(myRs.getString("availability"))
.append(",").append(myRs.getString("position"))
.append(",").append(myRs.getString("checkedIn"));
}
} catch (SQLException exception) {
exception.printStackTrace();
}
return response.toString();
}
// Schedule testing suite
// This test suite must be started while the Shifts, Employees, and Positions tables are empty.
public static void main(String[] args) {
try {
Connection dbConnection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ScheduleApp", "root", "password");
Schedule schedule = new Schedule(dbConnection);
Positions positions = new Positions(dbConnection);
Staff staff = new Staff(dbConnection);
staff.addEmployee("John", "Doe", "true", "john.doe@email.com",
"1234567890", 20.0f);
staff.addEmployee("Jane", "Doe", "true", "jane.doe@email.com",
"1234567891", 20.0f);
staff.addPosition(1, "cashier");
staff.addPosition(2, "cashier");
positions.addPosition("cashier", 10.00f); // Required to test position methods.
String queryString;
int queryInt;
int success = 0;
System.out.println("Schedule Testing Suite:");
System.out.println("\nSchedule Test 1: ");
System.out.println("Test Scenario: " + "Testing allShifts()");
System.out.println("Test Data: " + "none");
System.out.println("Expected Results: " + "allShifts");
queryString = schedule.allShifts();
System.out.println("Actual Results: " + queryString);
if (queryString.equals("allShifts")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nSchedule Test 2: ");
System.out.println("Test Scenario: " + "Testing addShift()");
System.out.println("Test Data: " + "ID=1, date='2022-03-22', start=900, end=1700, position='cashier");
System.out.println("Expected Results: " + "1,2022-03-22,900,1700,1,0,cashier,0");
queryString = schedule.addShift(1, "2022-03-22", 900, 1700, "cashier");
System.out.println("Actual Results: " + queryString);
if (queryString.equals("1,2022-03-22,900,1700,1,0,cashier,0")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nSchedule Test 3: ");
System.out.println("Test Scenario: " + "Testing editShift()");
System.out.println("Test Data: " + "shiftID=1, date='2022-03-23', start='1200', end='2100', " +
"available='true', employeeID=2");
System.out.println("Expected Results: " + "1,2022-03-23,1200,2100,1,1,cashier,0");
queryString = schedule.editShift(1, "2022-03-23", "1200", "2100", "true", 1);
System.out.println("Actual Results: " + queryString);
if (queryString.equals("1,2022-03-23,1200,2100,1,1,cashier,0")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nSchedule Test 4: ");
System.out.println("Test Scenario: " + "Testing editShiftAvailability()");
System.out.println("Test Data: " + "shiftID=1, available='false'");
System.out.println("Expected Results: " + "editedShift");
queryString = schedule.editShiftAvailability(1, "false");
System.out.println("Actual Results: " + queryString);
if (queryString.equals("editedShift")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nSchedule Test 5: ");
System.out.println("Test Scenario: " + "Testing checkIn()");
System.out.println("Test Data: " + "shiftID=1");
System.out.println("Expected Results: " + "1");
queryInt = schedule.checkIn(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("\nSchedule Test 6: ");
System.out.println("Test Scenario: " + "Testing removeShiftByID()");
System.out.println("Test Data: " + "shiftID=1");
System.out.println("Expected Results: " + "1");
queryInt = schedule.removeShiftByID(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");
}
//Setup to test removeAllShiftsByID()
schedule.addShift(1, "2022-03-24", 900, 1700, "cashier");
schedule.addShift(1, "2022-03-25", 900, 1700, "cashier");
System.out.println("\nSchedule Test 7: ");
System.out.println("Test Scenario: " + "Adding 2 shifts for 1 employee using addShift() for test 8");
System.out.println("Test Data: " + "none");
System.out.println("Expected Results: " + "allShifts/1,2022-03-24,900,1700,2,0,cashier,0/1,2022-03-25,900,1700,3,0,cashier,0");
queryString = schedule.allShifts();
System.out.println("Actual Results: " + queryString);
if (queryString.equals("allShifts/1,2022-03-24,900,1700,2,0,cashier,0/1,2022-03-25,900,1700,3,0,cashier,0")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nSchedule Test 8: ");
System.out.println("Test Scenario: " + "Testing removeAllShiftsByID()");
System.out.println("Test Data: " + "ID=1");
System.out.println("Expected Results: " + "allShifts");
schedule.removeAllShiftsByID(1);
queryString = schedule.allShifts();
System.out.println("Actual Results: " + queryString);
if (queryString.equals("allShifts")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nSchedule Unit Testing: Passed " + success + "/8.");
} catch (Exception exception) {
exception.printStackTrace();
}
}
}