Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
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 {
private final Connection dbConnection;
protected Availability(Connection c) {
this.dbConnection = c;
}
protected String newEmployeeAvailability(int employeeID){
String newAvailability = "";
try {
Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
myStatement.executeUpdate("INSERT INTO TimeOff (employeeID) " +
"VALUES ('" + employeeID + "')");
ResultSet myRs = myStatement.executeQuery("select * from TimeOff 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;
}
System.out.println("Shift successfully added");
return newAvailability;
}
protected void removeEmployeeAvailability(int employeeID){
try {
Statement myStatement = dbConnection.createStatement();
myStatement.executeUpdate("DELETE FROM Availabilities WHERE (employeeID =" + employeeID + ")");
} catch (SQLException exception) {
exception.printStackTrace();
}
}
protected String editAvailability(int employeeID, int dayOfTheWeek, int startTime, int endTime){
String editedAvailability = "";
String dayOfTheWeekString = "";
try {
Statement myStatement = dbConnection.createStatement();
switch (employeeID) {
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);
editedAvailability = 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 editedAvailability;
}
return editedAvailability;
}
protected String allAvailabilities(){
StringBuilder response = new StringBuilder("allAvailabilities");
try {
Statement myStatement = dbConnection.createStatement();
ResultSet myRs = myStatement.executeQuery("SELECT * FROM shifts");
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();
}
}