1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 package Utilities;
21
22 import java.sql.Connection;
23 import java.sql.PreparedStatement;
24 import java.sql.ResultSet;
25 import java.sql.SQLException;
26 import java.util.ArrayList;
27 import java.util.Objects;
28
29 public class Group {
30 private String name;
31 private String year;
32
33
34 private Utilities.Settable<Integer> id = new Utilities.Settable(-1, -1);
35 private Utilities.Settable<ArrayList<User>> groupUsers = new Utilities.Settable(null, null);
36
37 public Group(String name, String year) {
38 this.name = name;
39 this.year = year;
40 }
41
42 public Group(String name, String year, int id, ArrayList<User> uMan) {
43 this.name = name;
44 this.year = year;
45 this.id.set(id);
46 this.groupUsers.set(uMan);
47 }
48
49 public Group(int id, String year) {
50 this.id.orElseGetAndSet(() -> {
51 try (Connection con = DbInt.getConnection(year);
52 PreparedStatement prep = con.prepareStatement("SELECT * FROM groups WHERE ID=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
53 prep.setString(1, name);
54 try (ResultSet rs = prep.executeQuery()) {
55
56
57 while (rs.next()) {
58
59 this.name = rs.getString("Name");
60 }
61 }
62 } catch (SQLException e) {
63 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
64 }
65 return id;
66 });
67 groupUsers.orElseGetAndSet(() -> {
68 ArrayList<User> users = new ArrayList<>();
69 if (Objects.equals(name, "Ungrouped")) {
70 try (Connection con = DbInt.getConnection(year);
71 PreparedStatement prep = con.prepareStatement("SELECT * FROM users WHERE groupId IS NULL", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
72 try (ResultSet rs = prep.executeQuery()) {
73
74
75 while (rs.next()) {
76
77 users.add(new User(rs.getString("userName"), year));
78
79 }
80 }
81 } catch (SQLException e) {
82 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
83 }
84 }
85 try (Connection con = DbInt.getConnection(year);
86 PreparedStatement prep = con.prepareStatement("SELECT * FROM users WHERE groupId=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
87 prep.setInt(1, getID());
88
89 try (ResultSet rs = prep.executeQuery()) {
90
91
92 while (rs.next()) {
93
94 users.add(new User(rs.getString("userName"), year));
95
96 }
97 }
98 } catch (SQLException e) {
99 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
100 } catch (GroupNotFoundException e) {
101 LogToFile.log(e, Severity.WARNING, "Group not found. Please retry the action.");
102 }
103 return users;
104 });
105 this.year = year;
106 }
107
108 public static Iterable<Group> getGroups(String year) {
109 ArrayList<Group> groups = new ArrayList<>();
110 String name;
111
112
113 Integer id;
114 ArrayList<User> groupUsers = new ArrayList<>();
115 try (Connection con2 = DbInt.getConnection(year);
116 PreparedStatement prep2 = con2.prepareStatement("SELECT * FROM groups", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
117 try (ResultSet rs2 = prep2.executeQuery()) {
118
119
120 while (rs2.next()) {
121
122 name = rs2.getString("Name");
123 id = rs2.getInt("ID");
124 ArrayList<User> users = new ArrayList<>();
125 if (Objects.equals(name, "Ungrouped")) {
126 try (Connection con = DbInt.getConnection(year);
127 PreparedStatement prep = con.prepareStatement("SELECT * FROM users WHERE groupId IS NULL", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
128 try (ResultSet rs = prep.executeQuery()) {
129
130
131 while (rs.next()) {
132
133 users.add(new User(rs.getString("userName"), year));
134
135 }
136 }
137 } catch (SQLException e) {
138 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
139 }
140 }
141 try (Connection con = DbInt.getConnection(year);
142 PreparedStatement prep = con.prepareStatement("SELECT * FROM users WHERE groupId=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
143 prep.setInt(1, id);
144
145 try (ResultSet rs = prep.executeQuery()) {
146
147
148 while (rs.next()) {
149
150 users.add(new User(rs.getString("userName"), year));
151
152 }
153 }
154 } catch (SQLException e) {
155 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
156 } catch (GroupNotFoundException e) {
157 LogToFile.log(e, Severity.WARNING, "Group not found. Please retry the action.");
158 }
159
160 groups.add(new Group(name, year, id, users));
161 }
162 }
163 } catch (SQLException e) {
164 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
165 }
166
167
168 return groups;
169
170 }
171
172 public static ArrayList<Group> getGroupCollection(String year) {
173 ArrayList<Group> groups = new ArrayList<>();
174 try (Connection con = DbInt.getConnection(year);
175 PreparedStatement prep = con.prepareStatement("SELECT * FROM groups", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
176 ResultSet rs = prep.executeQuery()) {
177 while (rs.next()) {
178
179 groups.add(new Group(rs.getString("Name"), year));
180
181 }
182 } catch (SQLException e) {
183 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
184 }
185 return groups;
186
187 }
188 public Iterable<User> getUsers() {
189 return groupUsers.orElseGetAndSet(() -> {
190 ArrayList<User> users = new ArrayList<>();
191 if (Objects.equals(name, "Ungrouped")) {
192 try (Connection con = DbInt.getConnection(year);
193 PreparedStatement prep = con.prepareStatement("SELECT * FROM users WHERE groupId IS NULL", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
194 try (ResultSet rs = prep.executeQuery()) {
195
196
197 while (rs.next()) {
198
199 users.add(new User(rs.getString("userName"), year));
200
201 }
202 }
203 } catch (SQLException e) {
204 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
205 }
206 }
207 try (Connection con = DbInt.getConnection(year);
208 PreparedStatement prep = con.prepareStatement("SELECT * FROM users WHERE groupId=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
209 prep.setInt(1, getID());
210
211 try (ResultSet rs = prep.executeQuery()) {
212
213
214 while (rs.next()) {
215
216 users.add(new User(rs.getString("userName"), year));
217
218 }
219 }
220 } catch (SQLException e) {
221 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
222 } catch (GroupNotFoundException e) {
223 LogToFile.log(e, Severity.WARNING, "Group not found. Please retry the action.");
224 }
225 return users;
226 });
227
228 }
229
230 public void removeGroup() {
231 getUsers().forEach(user -> {
232 user.setGroupId(1);
233 user.updateYear(year);
234 });
235 try (Connection con = DbInt.getConnection(year);
236 PreparedStatement prep = con.prepareStatement("DELETE FROM groups WHERE ID=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
237
238 prep.setInt(1, getID());
239
240 prep.execute();
241 } catch (SQLException e) {
242 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
243 }
244 }
245 public String getName() {
246 return name;
247 }
248
249 public String getYear() {
250 return year;
251 }
252
253 public int getID() throws GroupNotFoundException {
254 return id.orElseGetAndSet(() -> {
255 int gID = -1;
256
257 try (Connection con = DbInt.getConnection(year);
258 PreparedStatement prep = con.prepareStatement("SELECT * FROM groups WHERE Name=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
259 prep.setString(1, name);
260 try (ResultSet rs = prep.executeQuery()) {
261
262
263 while (rs.next()) {
264
265 gID = rs.getInt("ID");
266
267 }
268 }
269 } catch (SQLException e) {
270 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
271 }
272 if (gID < 0) {
273 throw new GroupNotFoundException();
274 }
275 return gID;
276 });
277
278 }
279
280 public boolean equals(Object obj) {
281 if (obj == this) {
282 return true;
283 }
284 if (!(obj instanceof Group)) {
285 return false;
286 }
287 Group other = (Group) obj;
288 return (this.getID() == (other.getID())) && (this.getName().equals(other.getName()));
289 }
290
291 public int hashCode() {
292 return getName().hashCode();
293 }
294
295 public String toString() {
296 return name;
297 }
298
299 public class GroupNotFoundException extends RuntimeException {
300 public GroupNotFoundException() {
301 super();
302 }
303 }
304 }