Repository navigation
Expand file tree
/
Copy pathDatabase.java
More file actions
281 lines (242 loc) · 8.81 KB
/
Copy pathDatabase.java
File metadata and controls
281 lines (242 loc) · 8.81 KB
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
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
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
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Database {
/**
* Establishes a connection to the mySQL database
*
* @return A Connection object storing the connection to the database
*/
public static Connection connect( ) {
Connection connection = null;
try {
connection = DriverManager
.getConnection( "jdbc:mysql://localhost:3306/HOURS","root", "chickens" );
} catch ( SQLException ex ) {
ex.printStackTrace();
System.out.println( "SQLException: " + ex.getMessage( ) );
System.out.println( "SQLState: " + ex.getSQLState( ) );
System.out.println( "VendorError: " + ex.getErrorCode( ) );
}
return connection;
}
/**
* Iterates through the database to determine the number of students in the
* database
*
* @return Number of students in the database
*/
public static int studentCount( ) {
int count = 0;
Statement state = null;
ResultSet rs = null;
try ( Connection con = connect( ) ) {
state = con.createStatement( );
rs = state.executeQuery( "SELECT * FROM hours" );
while ( rs.next( ) ) {
count++;
}
} catch ( SQLException ex ) {
System.out.println( "SQLException: " + ex.getMessage( ) );
System.out.println( "SQLState: " + ex.getSQLState( ) );
System.out.println( "VendorError: " + ex.getErrorCode( ) );
} finally {
if ( rs != null ) {
try {
rs.close( );
} catch ( SQLException e ) {
}
}
if ( state != null ) {
try {
state.close( );
} catch ( SQLException e ) {
}
}
}
return count;
}
public static String getName( String tag ) {
Statement state = null;
ResultSet rs = null;
String name = "";
try ( Connection con = connect( ) ) {
state = con.createStatement( );
rs = state.executeQuery( String.format( "SELECT first,last FROM hours WHERE tagId = '%s'", tag ) );
rs.next( );
name = rs.getString( 1 ) + " " + rs.getString( 2 );
} catch ( SQLException ex ) {
System.out.println( "SQLException: " + ex.getMessage( ) );
System.out.println( "SQLState: " + ex.getSQLState( ) );
System.out.println( "VendorError: " + ex.getErrorCode( ) );
} finally {
// Closes ResultSet
if ( rs != null ) {
try {
rs.close( );
} catch ( SQLException e ) {
}
}
// Closes Statement
if ( state != null ) {
try {
state.close( );
} catch ( SQLException e ) {
}
}
}
return name;
}
public static boolean getInOut( String tag ) {
Statement state = null;
ResultSet rs = null;
int inOut = 0;
try ( Connection con = connect( ) ) {
state = con.createStatement( );
rs = state.executeQuery( String.format( "SELECT status FROM hours WHERE tagId = '%s'", tag ) );
rs.next( );
inOut = rs.getInt( 1 );
} catch ( SQLException ex ) {
System.out.println( "SQLException: " + ex.getMessage( ) );
System.out.println( "SQLState: " + ex.getSQLState( ) );
System.out.println( "VendorError: " + ex.getErrorCode( ) );
} finally {
// Closes ResultSet
if ( rs != null ) {
try {
rs.close( );
} catch ( SQLException e ) {
}
}
// Closes Statement
if ( state != null ) {
try {
state.close( );
} catch ( SQLException e ) {
}
}
}
if ( inOut == 1 ) {
return true;
} else {
return false;
}
}
public static boolean logIn( String tag ) {
Statement state = null;
boolean success = false;
try ( Connection con = connect( ) ) {
state = con.createStatement( );
state.executeUpdate( String.format( "UPDATE hours SET status = 1 WHERE tagId = '%s'", tag ) );
state.executeUpdate( String.format( "UPDATE hours SET timeIn = NOW() WHERE tagId = '%s'", tag ) );
// log( getName( tag ), 1 );
success = true;
} catch ( SQLException ex ) {
System.out.println( "SQLException: " + ex.getMessage( ) );
System.out.println( "SQLState: " + ex.getSQLState( ) );
System.out.println( "VendorError: " + ex.getErrorCode( ) );
} finally {
// Closes Statement
if ( state != null ) {
try {
state.close( );
} catch ( SQLException e ) {
}
}
}
return success;
}
public static boolean logOut( String tag ) {
Statement state = null;
boolean success = false;
try ( Connection con = connect( ) ) {
state = con.createStatement( );
state.executeUpdate( String.format( "UPDATE hours SET status = 0 WHERE tagId = '%s'", tag ) );
state.executeUpdate( String.format( "UPDATE hours SET timeOut = NOW() WHERE tagId = '%s'", tag ) );
state.executeUpdate( String.format(
"UPDATE hours SET hoursToday = ADDTIME(hoursToday, TIMEDIFF(timeOut, timeIn)) WHERE tagId = '%s'",
tag ) );
state.executeUpdate( String.format(
"UPDATE hours SET hoursThisWeek = ADDTIME(hoursThisWeek, TIMEDIFF(timeOut, timeIn)) WHERE tagId = '%s'",
tag ) );
// log( getName( tag ), 0 );
success = true;
} catch ( SQLException ex ) {
System.out.println( "SQLException: " + ex.getMessage( ) );
System.out.println( "SQLState: " + ex.getSQLState( ) );
System.out.println( "VendorError: " + ex.getErrorCode( ) );
} finally {
// Closes Statement
if ( state != null ) {
try {
state.close( );
} catch ( SQLException e ) {
}
}
}
return success;
}
public static boolean logAllOut( ) {
return true;
}
public static boolean endWeek( ) {
return true;
}
public static void log( String name, int inOut ) {
try ( FileWriter logger = new FileWriter( "hoursLog.txt", true );
BufferedWriter writer = new BufferedWriter( logger );
PrintWriter out = new PrintWriter( writer ); ) {
if ( inOut == 1 ) {
out.printf( "LOG IN \t %s \t at %s", name, new Date( ).toString( ) );
} else {
out.printf( "LOG OUT \t %s \t at %s", name, new Date( ).toString( ) );
}
} catch ( IOException e ) {
Interface.print( "Log error" );
}
}
public static Student getStudent( int index ) {
Statement state = null;
ResultSet rs = null;
Student stu = null;
try ( Connection con = connect( ) ) {
state = con.createStatement( );
rs = state.executeQuery( "SELECT * FROM hours" );
for(int i = 0; i <= index; i++){
rs.next( );
}
String name = rs.getString( 2 ) + " " + rs.getString( 3 );
int status = rs.getInt( 4 );
String timeT = rs.getTime( 7 ).toString( );
String timeW = rs.getTime( 8 ).toString( );
stu = new Student(name, status, timeT, timeW);
} catch ( SQLException ex ) {
System.out.println( "SQLException: " + ex.getMessage( ) );
System.out.println( "SQLState: " + ex.getSQLState( ) );
System.out.println( "VendorError: " + ex.getErrorCode( ) );
} finally {
// Closes ResultSet
if ( rs != null ) {
try {
rs.close( );
} catch ( SQLException e ) {
}
}
// Closes Statement
if ( state != null ) {
try {
state.close( );
} catch ( SQLException e ) {
}
}
}
return stu;
}
}