Newer
Older
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);
Rafi Zereselasie (raz070)
committed
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();
}
Rafi Zereselasie (raz070)
committed
// Availability testing suite
// This test suite must be started while the Availability, and Employees tables are empty.
Rafi Zereselasie (raz070)
committed
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");
}
Rafi Zereselasie (raz070)
committed
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
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();
}
Rafi Zereselasie (raz070)
committed
}