Newer
Older
package com.example.scheduler_server;
import java.sql.*;
/*
Name: TimeOff
Description: Responsible for sending queries and receiving responses from the database that deal with the
TimeOff table.
*/
public class TimeOff {
// Object that facilitates the connection to the database
private final Connection dbConnection;
/*
Name: TimeOff Constructor
Parameters:
Connection c: The connection to the database.
Description: Instantiates a TimeOff object and sets the global connection for the methods.
Return: TimeOff Object
*/
protected TimeOff(Connection c) {
this.dbConnection = c;
}
/*
Name: addTimeOff
Parameters:
int employeeID: The ID of an employee.
String startDate: The start date of the time off in "yyyy-mm-dd" format.
String endDate: The end date of the time off in "yyyy-mm-dd" format.
boolean approved: True if the time off is approved by a manager.
String reason: Why the time off was requested.
Description:
Return:
String: The details of the time off separated by commas.
*/
protected String addTimeOff(int employeeID, String startDate, String endDate, boolean approved, String reason){
String newTimeOff = "";
try {
Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
myStatement.executeUpdate("INSERT INTO TimeOff (employeeID, startDate, endDate, approved, reason) " +
"VALUES (" + employeeID +", '"+ startDate +"', '"+ endDate +"', "+ approved +", '" + reason + "')");
ResultSet myRs = myStatement.executeQuery("select * from TimeOff where employeeID=" + employeeID +
" and startDate='" + startDate + "' and endDate='" + endDate + "'");
if (myRs.last()) {
newTimeOff = myRs.getString("ID") + "," + myRs.getString("employeeID" ) + "," +
myRs.getString("startDate") + "," + myRs.getString("endDate") +
"," + myRs.getString("approved") + "," + myRs.getString("reason");
}
} catch (SQLException exception) {
exception.printStackTrace();
return newTimeOff;
}
return newTimeOff;
}
/*
Name: removeTimeOff
Parameters:
int timeOffID: The id of the time off request.
Description: Removes the time off request.
Return:
String: The time off id of the shift if it was removed, and -1 if there was a problem with the query.
*/
protected int removeTimeOff(int timeOffID){
try {
Statement myStatement = dbConnection.createStatement();
myStatement.executeUpdate("DELETE FROM TimeOff WHERE (ID =" + timeOffID + ")");
} catch (SQLException exception) {
exception.printStackTrace();
return -1;
}
return timeOffID;
}
/*
Name: setTimeOffApproval
Parameters:
int timeOffID: The id of the time off request.
String approved: "true" if its approved and "false" if it's not.
Description: Change the approval status of a request.
Return:
String: If the time off approval was successfully changed than it returns the id and the approval separated by
commas, or if there was an error with the query then it returns an empty string.
*/
protected String setTimeOffApproval(int timeOffID, String approved){
String editedTimeOff = "";
try {
Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
myStatement.executeUpdate("update TimeOff set approved=" + approved + " where ID=" + timeOffID);
ResultSet myRs = myStatement.executeQuery("select * from TimeOff where ID=" + timeOffID);
if (myRs.last()) {
editedTimeOff = myRs.getString("ID") + "," + myRs.getString("approved");
}
} catch (SQLException exception) {
exception.printStackTrace();
return editedTimeOff;
}
return editedTimeOff;
}
/*
Name: removeAllTimeOffByID
Parameters:
int employeeID: The id of an employee.
Description: Removes all time off request for a specified employee.
Return: void
*/
protected void removeAllTimeOffByID(int employeeID) {
try {
Statement myStatement = dbConnection.createStatement();
myStatement.executeUpdate("DELETE FROM TimeOff WHERE (employeeID =" + employeeID + ")");
} catch (SQLException exception) {
exception.printStackTrace();
}
}
/*
Name: allTimeOff
Parameters: none
Description: Gets all the time off request.
Return:
String: All the time off data where all the time offs are separated by slashes and each detail is separated by
commas.
*/
protected String allTimeOff(){
StringBuilder response = new StringBuilder("allTimeOff");
try {
Statement myStatement = dbConnection.createStatement();
ResultSet myRs = myStatement.executeQuery("SELECT * FROM TimeOff");
while (myRs.next()) {
response.append("/").append(myRs.getString("ID"))
.append(",").append(myRs.getString("employeeID"))
.append(",").append(myRs.getString("startDate"))
.append(",").append(myRs.getString("endDate"))
.append(",").append(myRs.getString("approved"))
.append(",").append(myRs.getString("reason"));
}
} catch (SQLException exception) {
exception.printStackTrace();
}
return response.toString();
}
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
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
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
// TimeOff testing suite
// This test suite must be started with an empty Employees and TimeOff tables.
public static void main(String[] args) {
try {
Connection dbConnection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ScheduleApp", "root", "password");
Staff staff = new Staff(dbConnection);
TimeOff timeOff = new TimeOff(dbConnection);
staff.addEmployee("John", "Doe", "true", "john.doe@email.com",
"1234567890", 20.0f); // Required to test TimeOff methods.
String queryString;
int queryInt;
int success = 0;
System.out.println("TimeOff Testing Suite:");
System.out.println("\nTimeOff Test 1: ");
System.out.println("Test Scenario: " + "Testing allTimeOff()");
System.out.println("Test Data: " + "none");
System.out.println("Expected Results: " + "allTimeOff");
queryString = timeOff.allTimeOff();
System.out.println("Actual Results: " + queryString);
if (queryString.equals("allTimeOff")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nTimeOff Test 2: ");
System.out.println("Test Scenario: " + "Testing addTimeOff()");
System.out.println("Test Data: " + "employeeID=1, startDate='2022-03-22', endDate='2022-03-25'," +
" approved=false, reason='Vacation'");
System.out.println("Expected Results: " + "1,1,2022-03-22,2022-03-25,0,Vacation");
queryString = timeOff.addTimeOff(1, "2022-03-22", "2022-03-25",
false, "Vacation");
System.out.println("Actual Results: " + queryString);
if (queryString.equals("1,1,2022-03-22,2022-03-25,0,Vacation")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nTimeOff Test 3: ");
System.out.println("Test Scenario: " + "Testing setTimeOffApproval()");
System.out.println("Test Data: " + "timeOffID=1, approved='true'");
System.out.println("Expected Results: " + "1,1");
queryString = timeOff.setTimeOffApproval(1, "true");
System.out.println("Actual Results: " + queryString);
if (queryString.equals("1,1")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nTimeOff Test 4: ");
System.out.println("Test Scenario: " + "Testing removeTimeOff()");
System.out.println("Test Data: " + "timeOffID=1");
System.out.println("Expected Results: " + "allTimeOff");
timeOff.removeTimeOff(1);
queryString = timeOff.allTimeOff();
System.out.println("Actual Results: " + queryString);
if (queryString.equals("allTimeOff")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
//Setup to properly test removeAllTimeOffByID()
timeOff.addTimeOff(1, "2022-03-22", "2022-03-25", false, "Vacation");
timeOff.addTimeOff(1 ,"2022-03-27", "2022-03-28", false, "SickDay");
System.out.println("\nTimeOff Test 5: ");
System.out.println("Test Scenario: " + "Testing allTimeOff() with time off requests in the table");
System.out.println("Test Data: " + "none");
System.out.println("Expected Results: " + "allTimeOff/2,1,2022-03-22,2022-03-25,0,Vacation" +
"/3,1,2022-03-27,2022-03-28,0,SickDay");
queryString = timeOff.allTimeOff();
System.out.println("Actual Results: " + queryString);
if (queryString.equals("allTimeOff/2,1,2022-03-22,2022-03-25,0,Vacation/3,1,2022-03-27,2022-03-28,0,SickDay")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nTimeOff Test 6: ");
System.out.println("Test Scenario: " + "Testing removeAllTimeOffByID()");
System.out.println("Test Data: " + "employeeID=1");
System.out.println("Expected Results: " + "allTimeOff");
timeOff.removeAllTimeOffByID(1);
queryString = timeOff.allTimeOff();
System.out.println("Actual Results: " + queryString);
if (queryString.equals("allTimeOff")){
success += 1;
System.out.println("Pass/Fail: Pass");
} else {
System.out.println("Pass/Fail: Fail");
}
System.out.println("\nTimeOff Unit Testing: Passed " + success + "/6.");
} catch (Exception exception) {
exception.printStackTrace();
}
}