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
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 {
private final Connection dbConnection;
protected TimeOff(Connection c) {
this.dbConnection = c;
}
protected String addTimeOff(int employeeID, String startDate, String endDate, boolean approved, String reason){
String newTimeOff = "";
Date sD = Date.valueOf(startDate);
Date eD = Date.valueOf(endDate);
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 +"', '"+ sD +"', '"+ eD +"', '"+ approved +"', '" + reason + "')");
ResultSet myRs = myStatement.executeQuery("select * from TimeOff where employeeID=" + employeeID +
" and startDate='" + sD + "' and endDate=" + eD);
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;
}
System.out.println("Shift successfully added");
return newTimeOff;
}
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;
}
protected String setTimeOffApproval(int ID, boolean 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=" + ID);
ResultSet myRs = myStatement.executeQuery("select * from TimeOff where ID=" + ID);
if (myRs.last()) {
editedTimeOff = myRs.getString("ID") + "," + myRs.getString("approved");
}
} catch (SQLException exception) {
exception.printStackTrace();
return editedTimeOff;
}
System.out.println("Shift successfully added");
return editedTimeOff;
}
protected void removeAllTimeOffByID(int employeeID) {
try {
Statement myStatement = dbConnection.createStatement();
myStatement.executeUpdate("DELETE FROM TimeOff WHERE (employeeID =" + employeeID + ")");
} catch (SQLException exception) {
exception.printStackTrace();
}
}
protected String allTimeOff(){
StringBuilder response = new StringBuilder("allTimeOff");
try {
Statement myStatement = dbConnection.createStatement();
ResultSet myRs = myStatement.executeQuery("SELECT * FROM shifts");
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();
}
}