| [c164f8f] | 1 | import java.io.IOException;
|
|---|
| 2 | import java.sql.*;
|
|---|
| 3 | import java.time.LocalDateTime;
|
|---|
| 4 | import java.util.ArrayList;
|
|---|
| [1c51912] | 5 | import java.util.HashMap;
|
|---|
| [c164f8f] | 6 | import java.util.List;
|
|---|
| [1c51912] | 7 | import java.util.Map;
|
|---|
| [c164f8f] | 8 |
|
|---|
| 9 | public class DatabaseUtil {
|
|---|
| 10 |
|
|---|
| 11 | private static final String DB_URL = "jdbc:sqlite:globe_guru.db";
|
|---|
| 12 |
|
|---|
| 13 | public static Connection getConnection() throws SQLException {
|
|---|
| 14 | return DriverManager.getConnection(DB_URL);
|
|---|
| 15 | }
|
|---|
| 16 | public static void initializeDatabase() throws SQLException {
|
|---|
| 17 | try (Connection conn = getConnection();
|
|---|
| 18 | PreparedStatement stmt1 = conn.prepareStatement(
|
|---|
| 19 | "CREATE TABLE IF NOT EXISTS users (" +
|
|---|
| 20 | "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
|
|---|
| 21 | "username TEXT NOT NULL UNIQUE, " +
|
|---|
| 22 | "email TEXT NOT NULL UNIQUE, " +
|
|---|
| 23 | "password TEXT, " +
|
|---|
| 24 | "isAdmin BOOLEAN NOT NULL DEFAULT FALSE)"
|
|---|
| 25 | );
|
|---|
| 26 | PreparedStatement stmt2 = conn.prepareStatement(
|
|---|
| 27 | "CREATE TABLE IF NOT EXISTS options (" +
|
|---|
| 28 | "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
|
|---|
| 29 | "link TEXT, " +
|
|---|
| 30 | "imgSrc TEXT, " +
|
|---|
| 31 | "hotelName TEXT, " +
|
|---|
| 32 | "country TEXT, " +
|
|---|
| 33 | "dateRange TEXT, " +
|
|---|
| [53bad7e] | 34 | "numberOfPeople INTEGER, " +
|
|---|
| [c164f8f] | 35 | "isPriceChanged BOOLEAN DEFAULT 0, " +
|
|---|
| 36 | "newPrice REAL DEFAULT 0)"
|
|---|
| 37 | );
|
|---|
| 38 | PreparedStatement stmt3 = conn.prepareStatement(
|
|---|
| 39 | "CREATE TABLE IF NOT EXISTS savedOptions (" +
|
|---|
| [1c51912] | 40 | "userId INTEGER," +
|
|---|
| 41 | "detailId INTEGER," +
|
|---|
| 42 | "FOREIGN KEY(userId) REFERENCES users(id)," +
|
|---|
| 43 | "FOREIGN KEY(detailId) REFERENCES optionDetails(id)," +
|
|---|
| 44 | "UNIQUE(userId, detailId))"
|
|---|
| 45 | );
|
|---|
| 46 | PreparedStatement stmt4 = conn.prepareStatement(
|
|---|
| 47 | "CREATE TABLE IF NOT EXISTS optionDetails (" +
|
|---|
| 48 | "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
|
|---|
| 49 | "optionId INTEGER NOT NULL, " +
|
|---|
| 50 | "type TEXT, " +
|
|---|
| 51 | "board TEXT, " +
|
|---|
| 52 | "amenities TEXT, " +
|
|---|
| 53 | "price REAL," +
|
|---|
| 54 | "FOREIGN KEY(optionId) REFERENCES options(id))"
|
|---|
| [df7f390] | 55 | );
|
|---|
| 56 | PreparedStatement stmt5 = conn.prepareStatement(
|
|---|
| 57 | "CREATE TABLE IF NOT EXISTS changeLog(" +
|
|---|
| 58 | "id INTEGER PRIMARY KEY AUTOINCREMENT," +
|
|---|
| 59 | "detail_id INTEGER NOT NULL," +
|
|---|
| 60 | "attribute TEXT NOT NULL," +
|
|---|
| 61 | "oldValue TEXT," +
|
|---|
| 62 | "newValue TEXT," +
|
|---|
| 63 | "FOREIGN KEY(detail_id) REFERENCES optionDetails(id))"
|
|---|
| [c164f8f] | 64 | )) {
|
|---|
| [1c51912] | 65 |
|
|---|
| [c164f8f] | 66 | stmt1.executeUpdate();
|
|---|
| 67 | stmt2.executeUpdate();
|
|---|
| 68 | stmt3.executeUpdate();
|
|---|
| [1c51912] | 69 | stmt4.executeUpdate();
|
|---|
| [df7f390] | 70 | stmt5.executeUpdate();
|
|---|
| [c164f8f] | 71 | }
|
|---|
| 72 | }
|
|---|
| 73 |
|
|---|
| 74 | public static boolean registerUser(String username, String email, String password) throws SQLException {
|
|---|
| 75 | String sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
|
|---|
| 76 | try (Connection conn = getConnection();
|
|---|
| 77 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 78 | stmt.setString(1, username);
|
|---|
| 79 | stmt.setString(2, email);
|
|---|
| [53bad7e] | 80 | stmt.setString(3, password);
|
|---|
| [c164f8f] | 81 | return stmt.executeUpdate() > 0;
|
|---|
| 82 | }
|
|---|
| 83 | }
|
|---|
| 84 | public static boolean authenticateUser(String email, String password) throws SQLException {
|
|---|
| 85 | String sql = "SELECT password FROM users WHERE email = ?";
|
|---|
| 86 | try (Connection conn = getConnection();
|
|---|
| 87 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 88 | stmt.setString(1, email);
|
|---|
| 89 | try (ResultSet rs = stmt.executeQuery()) {
|
|---|
| 90 | if (rs.next()) {
|
|---|
| 91 | String storedPassword = rs.getString("password");
|
|---|
| 92 | if (password == null) {
|
|---|
| [53bad7e] | 93 | // Google login
|
|---|
| [c164f8f] | 94 | return storedPassword == null;
|
|---|
| 95 | }
|
|---|
| [53bad7e] | 96 | return password.equals(storedPassword);
|
|---|
| [c164f8f] | 97 | }
|
|---|
| 98 | }
|
|---|
| 99 | }
|
|---|
| 100 | return false;
|
|---|
| 101 | }
|
|---|
| 102 | public static boolean deleteUser(int userId) throws SQLException {
|
|---|
| 103 | String selectSql = "SELECT userId FROM users WHERE userId = ?";
|
|---|
| 104 | String deleteSql = "DELETE FROM users WHERE userId = ?";
|
|---|
| 105 | String deleteFavoritesSql = "DELETE FROM savedOptions WHERE userId = ?";
|
|---|
| 106 |
|
|---|
| 107 | try (Connection conn = getConnection();
|
|---|
| 108 | PreparedStatement selectStmt = conn.prepareStatement(selectSql);
|
|---|
| 109 | PreparedStatement deleteStmt = conn.prepareStatement(deleteSql);
|
|---|
| 110 | PreparedStatement deleteFavoritesStmt = conn.prepareStatement(deleteFavoritesSql)) {
|
|---|
| 111 |
|
|---|
| 112 | selectStmt.setInt(1, userId);
|
|---|
| 113 | try (ResultSet rs = selectStmt.executeQuery()) {
|
|---|
| 114 | if (rs.next()) {
|
|---|
| 115 | deleteStmt.setInt(1, userId);
|
|---|
| 116 | int rowsAffected = deleteStmt.executeUpdate();
|
|---|
| 117 |
|
|---|
| 118 | deleteFavoritesStmt.setInt(1, userId);
|
|---|
| 119 | deleteFavoritesStmt.executeUpdate();
|
|---|
| 120 |
|
|---|
| 121 | return rowsAffected > 0;
|
|---|
| 122 | } else {
|
|---|
| 123 | return false;
|
|---|
| 124 | }
|
|---|
| 125 | }
|
|---|
| 126 | }
|
|---|
| 127 | }
|
|---|
| 128 | public static boolean userExists(String email) throws SQLException {
|
|---|
| 129 | String query = "SELECT COUNT(*) FROM users WHERE email = ?";
|
|---|
| 130 | try (Connection connection = getConnection();
|
|---|
| 131 | PreparedStatement statement = connection.prepareStatement(query)) {
|
|---|
| 132 | statement.setString(1, email);
|
|---|
| 133 | ResultSet resultSet = statement.executeQuery();
|
|---|
| 134 | if (resultSet.next()) {
|
|---|
| 135 | return resultSet.getInt(1) > 0;
|
|---|
| 136 | }
|
|---|
| 137 | }
|
|---|
| 138 | return false;
|
|---|
| 139 | }
|
|---|
| 140 | public static boolean isAdmin(String email) throws SQLException {
|
|---|
| 141 | String selectSql = "SELECT isAdmin FROM users WHERE email = ?";
|
|---|
| 142 | try (Connection conn = getConnection();
|
|---|
| 143 | PreparedStatement stmt = conn.prepareStatement(selectSql)) {
|
|---|
| 144 | stmt.setString(1, email);
|
|---|
| 145 | try (ResultSet rs = stmt.executeQuery()) {
|
|---|
| 146 | if (rs.next()) {
|
|---|
| 147 | return rs.getBoolean("isAdmin");
|
|---|
| 148 | }
|
|---|
| 149 | }
|
|---|
| 150 | }
|
|---|
| 151 | return false;
|
|---|
| 152 | }
|
|---|
| [1c51912] | 153 | public static boolean saveFavoriteOption(int userId, int detailId) throws SQLException {
|
|---|
| 154 | String sql = "INSERT INTO savedOptions (userId, detailId) VALUES (?, ?) ON CONFLICT DO NOTHING";
|
|---|
| [c164f8f] | 155 | try (Connection conn = getConnection();
|
|---|
| 156 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 157 | stmt.setInt(1, userId);
|
|---|
| [1c51912] | 158 | stmt.setInt(2, detailId);
|
|---|
| [c164f8f] | 159 | return stmt.executeUpdate() > 0;
|
|---|
| 160 | }
|
|---|
| 161 | }
|
|---|
| [1c51912] | 162 | public static boolean removeFavoriteOption(int userId, int detailId) throws SQLException {
|
|---|
| 163 | String sql = "DELETE FROM savedOptions WHERE userId = ? AND detailId = ?";
|
|---|
| [c164f8f] | 164 | try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 165 | stmt.setInt(1, userId);
|
|---|
| [1c51912] | 166 | stmt.setInt(2, detailId);
|
|---|
| [c164f8f] | 167 | return stmt.executeUpdate() > 0;
|
|---|
| 168 | }
|
|---|
| 169 | }
|
|---|
| 170 | public static List<Option> getSavedTripsByUser(int userId) throws SQLException {
|
|---|
| 171 | List<Option> savedTrips = new ArrayList<>();
|
|---|
| [1c51912] | 172 | String sql = "SELECT od.id AS detail_id, o.*, od.* FROM savedOptions so " +
|
|---|
| 173 | "JOIN optionDetails od ON so.detailId = od.id " +
|
|---|
| 174 | "JOIN options o ON od.optionId = o.id " +
|
|---|
| 175 | "WHERE so.userId = ?";
|
|---|
| [c164f8f] | 176 | try (Connection conn = getConnection();
|
|---|
| 177 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 178 | stmt.setInt(1, userId);
|
|---|
| 179 | try (ResultSet rs = stmt.executeQuery()) {
|
|---|
| 180 | while (rs.next()) {
|
|---|
| 181 | Option option = new Option();
|
|---|
| [1c51912] | 182 | option.setId(rs.getInt("detail_id"));
|
|---|
| [c164f8f] | 183 | option.setLink(rs.getString("link"));
|
|---|
| 184 | option.setImgSrc(rs.getString("imgSrc"));
|
|---|
| 185 | option.setHotelName(rs.getString("hotelName"));
|
|---|
| 186 | option.setCountry(rs.getString("country"));
|
|---|
| 187 | option.setDateRange(rs.getString("dateRange"));
|
|---|
| [1c51912] | 188 | option.setNumPeople(rs.getInt("numberOfPeople"));
|
|---|
| 189 | option.setType(rs.getString("type"));
|
|---|
| 190 | option.setBoard(rs.getString("board"));
|
|---|
| 191 | option.setAmenities(rs.getString("amenities"));
|
|---|
| 192 | option.setPrice(rs.getFloat("price"));
|
|---|
| [c164f8f] | 193 | savedTrips.add(option);
|
|---|
| 194 | }
|
|---|
| 195 | }
|
|---|
| 196 | }
|
|---|
| 197 | return savedTrips;
|
|---|
| 198 | }
|
|---|
| 199 | public static int getUserIdByEmail(String email) throws SQLException {
|
|---|
| 200 | String sql = "SELECT id FROM users WHERE email = ?";
|
|---|
| 201 | try (Connection conn = getConnection();
|
|---|
| 202 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 203 | stmt.setString(1, email);
|
|---|
| 204 | try (ResultSet rs = stmt.executeQuery()) {
|
|---|
| 205 | if (rs.next()) {
|
|---|
| 206 | return rs.getInt("id");
|
|---|
| 207 | } else {
|
|---|
| 208 | throw new SQLException("User not found");
|
|---|
| 209 | }
|
|---|
| 210 | }
|
|---|
| 211 | }
|
|---|
| 212 | }
|
|---|
| [0a7426e] | 213 | public static List<Option> poolOptionDetails(int id) throws SQLException{
|
|---|
| 214 | String sql = "SELECT * FROM optionDetails WHERE optionId = ?";
|
|---|
| 215 | List<Option> options = new ArrayList<>();
|
|---|
| 216 | try(Connection conn = getConnection();
|
|---|
| 217 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 218 | stmt.setInt(1,id);
|
|---|
| 219 | try(ResultSet rs = stmt.executeQuery()){
|
|---|
| 220 | while (rs.next()){
|
|---|
| 221 | Option o = new Option();
|
|---|
| 222 | o.setAmenities(rs.getString("amenities"));
|
|---|
| 223 | o.setType(rs.getString("type"));
|
|---|
| 224 | o.setBoard(rs.getString("board"));
|
|---|
| 225 | o.setPrice(rs.getFloat("price"));
|
|---|
| 226 | o.setId(id);
|
|---|
| 227 | o.setDetail_id(rs.getInt("id"));
|
|---|
| 228 | }
|
|---|
| [c164f8f] | 229 | }
|
|---|
| [0a7426e] | 230 |
|
|---|
| [c164f8f] | 231 | }
|
|---|
| [0a7426e] | 232 | return options;
|
|---|
| [c164f8f] | 233 | }
|
|---|
| [0a7426e] | 234 | public static List<Option> queryOptions(String destination, String dateQuery, int numPeople, boolean dateFlag) throws SQLException {
|
|---|
| 235 | List<Option> options = new ArrayList<>();
|
|---|
| 236 | String sql = "SELECT o.*, od.id AS detail_id, od.type, od.board, od.amenities, od.price AS detail_price " +
|
|---|
| 237 | "FROM options o " +
|
|---|
| 238 | "LEFT JOIN optionDetails od ON o.id = od.optionId " +
|
|---|
| 239 | "WHERE (o.country LIKE ? OR o.hotelName LIKE ?) " +
|
|---|
| 240 | "AND od.type IS NOT NULL " +
|
|---|
| 241 | "AND od.board IS NOT NULL " +
|
|---|
| 242 | "AND od.price > 0";
|
|---|
| [c164f8f] | 243 |
|
|---|
| [0a7426e] | 244 | if (dateQuery != null && !dateQuery.isEmpty() && !dateFlag) {
|
|---|
| 245 | sql += " AND o.dateRange = ?";
|
|---|
| [c164f8f] | 246 | }
|
|---|
| [0a7426e] | 247 | if (dateFlag) {
|
|---|
| 248 | sql += " AND o.dateRange LIKE ?";
|
|---|
| 249 | }
|
|---|
| 250 | if (numPeople != 0) {
|
|---|
| 251 | sql += " AND o.numberOfPeople = ?";
|
|---|
| 252 | }
|
|---|
| 253 |
|
|---|
| [c164f8f] | 254 | try (Connection conn = getConnection();
|
|---|
| 255 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| [0a7426e] | 256 | stmt.setString(1, "%" + destination + "%");
|
|---|
| 257 | stmt.setString(2, "%" + destination + "%");
|
|---|
| 258 | int paramIndex = 3;
|
|---|
| 259 | if (dateQuery != null && !dateQuery.isEmpty() && !dateFlag) {
|
|---|
| 260 | stmt.setString(paramIndex++, dateQuery);
|
|---|
| 261 | }
|
|---|
| 262 | if (dateFlag) {
|
|---|
| 263 | stmt.setString(paramIndex++, dateQuery + "%");
|
|---|
| 264 | }
|
|---|
| 265 | if (numPeople != 0) {
|
|---|
| 266 | stmt.setInt(paramIndex, numPeople);
|
|---|
| 267 | }
|
|---|
| 268 |
|
|---|
| [c164f8f] | 269 | try (ResultSet rs = stmt.executeQuery()) {
|
|---|
| [0a7426e] | 270 | while (rs.next()) {
|
|---|
| 271 | Option option = new Option();
|
|---|
| 272 | option.setId(rs.getInt("detail_id"));
|
|---|
| 273 | option.setLink(rs.getString("link"));
|
|---|
| 274 | option.setImgSrc(rs.getString("imgSrc"));
|
|---|
| 275 | option.setHotelName(rs.getString("hotelName"));
|
|---|
| 276 | option.setCountry(rs.getString("country"));
|
|---|
| 277 | option.setDateRange(rs.getString("dateRange"));
|
|---|
| 278 | option.setNumPeople(rs.getInt("numberOfPeople"));
|
|---|
| 279 | option.setType(rs.getString("type"));
|
|---|
| 280 | option.setBoard(rs.getString("board"));
|
|---|
| 281 | option.setAmenities(rs.getString("amenities"));
|
|---|
| 282 | option.setPrice(rs.getFloat("detail_price"));
|
|---|
| 283 | options.add(option);
|
|---|
| [c164f8f] | 284 | }
|
|---|
| 285 | }
|
|---|
| 286 | }
|
|---|
| 287 |
|
|---|
| [0a7426e] | 288 | System.out.println("Found " + options.size() + " options");
|
|---|
| 289 | return options;
|
|---|
| 290 | }
|
|---|
| [1c51912] | 291 | public static int saveOptionToDatabase(Option option) {
|
|---|
| [df7f390] | 292 | String sql = "INSERT INTO options (link, imgSrc, hotelName, country, dateRange,numberOfPeople) VALUES (?, ?, ?, ?, ?, ?)";
|
|---|
| [1c51912] | 293 | int id = 0;
|
|---|
| [c164f8f] | 294 | try (Connection conn = DriverManager.getConnection("jdbc:sqlite:globe_guru.db");
|
|---|
| 295 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 296 | stmt.setString(1, option.getLink());
|
|---|
| 297 | stmt.setString(2, option.getImgSrc());
|
|---|
| 298 | stmt.setString(3, option.getHotelName());
|
|---|
| 299 | stmt.setString(4, option.getCountry());
|
|---|
| [1c51912] | 300 | stmt.setString(5, option.getDateRange());
|
|---|
| 301 | stmt.setInt(6,option.getNumPeople());
|
|---|
| [c164f8f] | 302 | stmt.executeUpdate();
|
|---|
| [1c51912] | 303 | try(ResultSet genKey = stmt.getGeneratedKeys()){
|
|---|
| 304 | if(genKey.next()){
|
|---|
| 305 | id = genKey.getInt(1);
|
|---|
| 306 | }
|
|---|
| 307 | }
|
|---|
| [c164f8f] | 308 | } catch (SQLException e) {
|
|---|
| 309 | e.printStackTrace();
|
|---|
| 310 | }
|
|---|
| [1c51912] | 311 | return id;
|
|---|
| [c164f8f] | 312 | }
|
|---|
| [1c51912] | 313 | public static void saveOptionDetails(int id, String type, String board, String amenity, float price) {
|
|---|
| 314 | String sql = "INSERT INTO optionDetails (optionId, type, board, amenities, price) VALUES (?, ?, ?, ?, ?)";
|
|---|
| 315 |
|
|---|
| 316 | try (Connection conn = getConnection();
|
|---|
| 317 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 318 | stmt.setInt(1, id);
|
|---|
| 319 | stmt.setString(2, type);
|
|---|
| 320 | stmt.setString(3, board);
|
|---|
| 321 | stmt.setString(4, amenity);
|
|---|
| 322 | stmt.setFloat(5, price);
|
|---|
| 323 | stmt.executeUpdate();
|
|---|
| 324 | } catch (SQLException e) {
|
|---|
| 325 | e.printStackTrace();
|
|---|
| 326 | }
|
|---|
| 327 | }
|
|---|
| [c164f8f] | 328 | public static void dropOptions() throws SQLException {
|
|---|
| 329 | String sql = "DROP TABLE options";
|
|---|
| 330 | try (
|
|---|
| 331 | Connection conn = getConnection();
|
|---|
| 332 | PreparedStatement stmt = conn.prepareStatement(sql)){
|
|---|
| 333 | stmt.executeUpdate();
|
|---|
| 334 | initializeDatabase();
|
|---|
| 335 |
|
|---|
| 336 | }
|
|---|
| 337 | }
|
|---|
| [0a7426e] | 338 | public static void updateOptionDetails(int detail_id, String type, String board, String amenities, float price) {
|
|---|
| [df7f390] | 339 | try (Connection conn = getConnection()) {
|
|---|
| 340 | String selectSql = "SELECT * FROM optionDetails WHERE id = ?";
|
|---|
| 341 | PreparedStatement selectStmt = conn.prepareStatement(selectSql);
|
|---|
| 342 | selectStmt.setInt(1, detail_id);
|
|---|
| 343 | ResultSet rs = selectStmt.executeQuery();
|
|---|
| [0a7426e] | 344 |
|
|---|
| [df7f390] | 345 | if (rs.next()) {
|
|---|
| 346 | String oldType = rs.getString("type");
|
|---|
| 347 | String oldBoard = rs.getString("board");
|
|---|
| 348 | String oldAmenities = rs.getString("amenities");
|
|---|
| 349 | float oldPrice = rs.getFloat("price");
|
|---|
| [0a7426e] | 350 |
|
|---|
| [df7f390] | 351 | insertChangeLog(detail_id, "type", oldType, type);
|
|---|
| 352 | insertChangeLog(detail_id, "board", oldBoard, board);
|
|---|
| 353 | insertChangeLog(detail_id, "amenities", oldAmenities, amenities);
|
|---|
| 354 | insertChangeLog(detail_id, "price", String.valueOf(oldPrice), String.valueOf(price));
|
|---|
| 355 | }
|
|---|
| [0a7426e] | 356 |
|
|---|
| [df7f390] | 357 | String sql = "UPDATE optionDetails SET ";
|
|---|
| 358 | List<String> updates = new ArrayList<>();
|
|---|
| 359 | if (type != null && !type.isEmpty()) updates.add("type = '" + type + "'");
|
|---|
| 360 | if (board != null && !board.isEmpty()) updates.add("board = '" + board + "'");
|
|---|
| 361 | if (amenities != null && !amenities.isEmpty()) updates.add("amenities = '" + amenities + "'");
|
|---|
| 362 | if (price > 0) updates.add("price = " + price);
|
|---|
| [0a7426e] | 363 |
|
|---|
| [df7f390] | 364 | sql += String.join(", ", updates);
|
|---|
| 365 | sql += " WHERE id = " + detail_id;
|
|---|
| 366 |
|
|---|
| 367 | try (PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 368 | stmt.executeUpdate();
|
|---|
| 369 | }
|
|---|
| [c164f8f] | 370 | } catch (SQLException e) {
|
|---|
| 371 | e.printStackTrace();
|
|---|
| 372 | }
|
|---|
| 373 | }
|
|---|
| [df7f390] | 374 | private static void insertChangeLog(int detail_id, String attribute, String oldValue, String newValue) {
|
|---|
| 375 | if (!oldValue.equals(newValue)) {
|
|---|
| 376 | String sql = "INSERT INTO changeLog (detail_id, attribute, oldValue, newValue) VALUES (?, ?, ?, ?)";
|
|---|
| 377 | try (Connection conn = getConnection();
|
|---|
| 378 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 379 | stmt.setInt(1, detail_id);
|
|---|
| 380 | stmt.setString(2, attribute);
|
|---|
| 381 | stmt.setString(3, oldValue);
|
|---|
| 382 | stmt.setString(4, newValue);
|
|---|
| 383 | stmt.executeUpdate();
|
|---|
| 384 | } catch (SQLException e) {
|
|---|
| 385 | e.printStackTrace();
|
|---|
| [c164f8f] | 386 | }
|
|---|
| 387 | }
|
|---|
| 388 | }
|
|---|
| [0a7426e] | 389 | public static int getCurrentOptionsCount() throws SQLException {
|
|---|
| 390 | String sql = "SELECT COUNT(*) AS optionsCount FROM options";
|
|---|
| 391 | try (Connection conn = getConnection();
|
|---|
| [1c51912] | 392 | PreparedStatement stmt = conn.prepareStatement(sql);
|
|---|
| 393 | ResultSet rs = stmt.executeQuery()) {
|
|---|
| [0a7426e] | 394 | if (rs.next()) {
|
|---|
| 395 | return rs.getInt("optionsCount");
|
|---|
| 396 | } else {
|
|---|
| 397 | return 0;
|
|---|
| 398 | }
|
|---|
| 399 | }
|
|---|
| 400 | }
|
|---|
| 401 | public static int getChangedOptionsCountSinceLastUpdate() throws SQLException, IOException {
|
|---|
| 402 | LocalDateTime lastUpdateTime = Server.getLastUpdateTime();
|
|---|
| 403 | if (lastUpdateTime == null) {
|
|---|
| 404 | return 0;
|
|---|
| 405 | }
|
|---|
| 406 | String sql = "SELECT COUNT(*) AS changedOptionsCount FROM options WHERE lastModified > ?";
|
|---|
| 407 | try (Connection conn = getConnection();
|
|---|
| 408 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 409 | stmt.setTimestamp(1, Timestamp.valueOf(lastUpdateTime));
|
|---|
| 410 | try (ResultSet rs = stmt.executeQuery()) {
|
|---|
| 411 | if (rs.next()) {
|
|---|
| 412 | return rs.getInt("changedOptionsCount");
|
|---|
| 413 | } else {
|
|---|
| 414 | return 0;
|
|---|
| 415 | }
|
|---|
| [1c51912] | 416 | }
|
|---|
| 417 | }
|
|---|
| 418 | }
|
|---|
| 419 |
|
|---|
| [c164f8f] | 420 | }
|
|---|
