Changeset 0a7426e

Timestamp:

01/12/25 17:15:36 (3 days ago)

Author:

Kristijan <kristijanzafirovski26@…>

Branches:

master

Children:

df7f390

Parents:

cd64b06

Message:

Added checking for changes - backend

Location:

backend/GlobeGuru-backend

Files:

• src/main/java/DatabaseUtil.java (modified) (11 diffs)
• src/main/java/Option.java (modified) (2 diffs)
• src/main/java/ScraperThread.java (modified) (6 diffs)
• target/classes/URLsJSON.json (modified) (1 diff)

backend/GlobeGuru-backend/src/main/java/DatabaseUtil.java

@@ -72 +72 @@
         }
     }
-
     public static boolean authenticateUser(String email, String password) throws SQLException {
         String sql = "SELECT password FROM users WHERE email = ?";
@@ -91 +90 @@
         return false;
     }
-
     public static boolean deleteUser(int userId) throws SQLException {
         String selectSql = "SELECT userId FROM users WHERE userId = ?";
@@ -118 +116 @@
         }
     }
-
     public static boolean userExists(String email) throws SQLException {
         String query = "SELECT COUNT(*) FROM users WHERE email = ?";
@@ -131 +128 @@
         return false;
     }
-
     public static boolean isAdmin(String email) throws SQLException {
         String selectSql = "SELECT isAdmin FROM users WHERE email = ?";
@@ -145 +141 @@
         return false;
     }
-
+    public static boolean saveFavoriteOption(int userId, int detailId) throws SQLException {
+        String sql = "INSERT INTO savedOptions (userId, detailId) VALUES (?, ?) ON CONFLICT DO NOTHING";
+        try (Connection conn = getConnection();
+             PreparedStatement stmt = conn.prepareStatement(sql)) {
+            stmt.setInt(1, userId);
+            stmt.setInt(2, detailId);
+            return stmt.executeUpdate() > 0;
+        }
+    }
+    public static boolean removeFavoriteOption(int userId, int detailId) throws SQLException {
+        String sql = "DELETE FROM savedOptions WHERE userId = ? AND detailId = ?";
+        try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) {
+            stmt.setInt(1, userId);
+            stmt.setInt(2, detailId);
+            return stmt.executeUpdate() > 0;
+        }
+    }
+    public static List<Option> getSavedTripsByUser(int userId) throws SQLException {
+        List<Option> savedTrips = new ArrayList<>();
+        String sql = "SELECT od.id AS detail_id, o.*, od.* FROM savedOptions so " +
+                "JOIN optionDetails od ON so.detailId = od.id " +
+                "JOIN options o ON od.optionId = o.id " +
+                "WHERE so.userId = ?";
+        try (Connection conn = getConnection();
+             PreparedStatement stmt = conn.prepareStatement(sql)) {
+            stmt.setInt(1, userId);
+            try (ResultSet rs = stmt.executeQuery()) {
+                while (rs.next()) {
+                    Option option = new Option();
+                    option.setId(rs.getInt("detail_id"));
+                    option.setLink(rs.getString("link"));
+                    option.setImgSrc(rs.getString("imgSrc"));
+                    option.setHotelName(rs.getString("hotelName"));
+                    option.setCountry(rs.getString("country"));
+                    option.setDateRange(rs.getString("dateRange"));
+                    option.setNumPeople(rs.getInt("numberOfPeople"));
+                    option.setType(rs.getString("type"));
+                    option.setBoard(rs.getString("board"));
+                    option.setAmenities(rs.getString("amenities"));
+                    option.setPrice(rs.getFloat("price"));
+                    savedTrips.add(option);
+                }
+            }
+        }
+        return savedTrips;
+    }
+    public static int getUserIdByEmail(String email) throws SQLException {
+        String sql = "SELECT id FROM users WHERE email = ?";
+        try (Connection conn = getConnection();
+             PreparedStatement stmt = conn.prepareStatement(sql)) {
+            stmt.setString(1, email);
+            try (ResultSet rs = stmt.executeQuery()) {
+                if (rs.next()) {
+                    return rs.getInt("id");
+                } else {
+                    throw new SQLException("User not found");
+                }
+            }
+        }
+    }
+
+
+
+    public static List<Option> poolOptionDetails(int id) throws SQLException{
+        String sql = "SELECT * FROM optionDetails WHERE optionId = ?";
+        List<Option> options = new ArrayList<>();
+        try(Connection conn = getConnection();
+            PreparedStatement stmt = conn.prepareStatement(sql)) {
+            stmt.setInt(1,id);
+            try(ResultSet rs = stmt.executeQuery()){
+                while (rs.next()){
+                    Option o = new Option();
+                    o.setAmenities(rs.getString("amenities"));
+                    o.setType(rs.getString("type"));
+                    o.setBoard(rs.getString("board"));
+                    o.setPrice(rs.getFloat("price"));
+                    o.setId(id);
+                    o.setDetail_id(rs.getInt("id"));
+                }
+            }
+
+        }
+        return options;
+    }
     public static List<Option> queryOptions(String destination, String dateQuery, int numPeople, boolean dateFlag) throws SQLException {
         List<Option> options = new ArrayList<>();
@@ -203 +282 @@
         return options;
     }
-
-
-
-    public static boolean saveFavoriteOption(int userId, int detailId) throws SQLException {
-        String sql = "INSERT INTO savedOptions (userId, detailId) VALUES (?, ?) ON CONFLICT DO NOTHING";
-        try (Connection conn = getConnection();
-             PreparedStatement stmt = conn.prepareStatement(sql)) {
-            stmt.setInt(1, userId);
-            stmt.setInt(2, detailId);
-            return stmt.executeUpdate() > 0;
-        }
-    }
-
-
-
-    public static boolean removeFavoriteOption(int userId, int detailId) throws SQLException {
-        String sql = "DELETE FROM savedOptions WHERE userId = ? AND detailId = ?";
-        try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) {
-            stmt.setInt(1, userId);
-            stmt.setInt(2, detailId);
-            return stmt.executeUpdate() > 0;
-        }
-    }
-
-
-    public static List<Option> getSavedTripsByUser(int userId) throws SQLException {
-        List<Option> savedTrips = new ArrayList<>();
-        String sql = "SELECT od.id AS detail_id, o.*, od.* FROM savedOptions so " +
-                "JOIN optionDetails od ON so.detailId = od.id " +
-                "JOIN options o ON od.optionId = o.id " +
-                "WHERE so.userId = ?";
-        try (Connection conn = getConnection();
-             PreparedStatement stmt = conn.prepareStatement(sql)) {
-            stmt.setInt(1, userId);
-            try (ResultSet rs = stmt.executeQuery()) {
-                while (rs.next()) {
-                    Option option = new Option();
-                    option.setId(rs.getInt("detail_id"));
-                    option.setLink(rs.getString("link"));
-                    option.setImgSrc(rs.getString("imgSrc"));
-                    option.setHotelName(rs.getString("hotelName"));
-                    option.setCountry(rs.getString("country"));
-                    option.setDateRange(rs.getString("dateRange"));
-                    option.setNumPeople(rs.getInt("numberOfPeople"));
-                    option.setType(rs.getString("type"));
-                    option.setBoard(rs.getString("board"));
-                    option.setAmenities(rs.getString("amenities"));
-                    option.setPrice(rs.getFloat("price"));
-                    savedTrips.add(option);
-                }
-            }
-        }
-        return savedTrips;
-    }
-
-
-
-    public static int getUserIdByEmail(String email) throws SQLException {
-        String sql = "SELECT id FROM users WHERE email = ?";
-        try (Connection conn = getConnection();
-             PreparedStatement stmt = conn.prepareStatement(sql)) {
-            stmt.setString(1, email);
-            try (ResultSet rs = stmt.executeQuery()) {
-                if (rs.next()) {
-                    return rs.getInt("id");
-                } else {
-                    throw new SQLException("User not found");
-                }
-            }
-        }
-    }
-
-    public static int getCurrentOptionsCount() throws SQLException {
-        String sql = "SELECT COUNT(*) AS optionsCount FROM options";
-        try (Connection conn = getConnection();
-             PreparedStatement stmt = conn.prepareStatement(sql);
-             ResultSet rs = stmt.executeQuery()) {
-            if (rs.next()) {
-                return rs.getInt("optionsCount");
-            } else {
-                return 0;
-            }
-        }
-    }
-
-    public static int getChangedOptionsCountSinceLastUpdate() throws SQLException, IOException {
-        LocalDateTime lastUpdateTime = Server.getLastUpdateTime();
-        if (lastUpdateTime == null) {
-            return 0;
-        }
-        String sql = "SELECT COUNT(*) AS changedOptionsCount FROM options WHERE lastModified > ?";
-        try (Connection conn = getConnection();
-             PreparedStatement stmt = conn.prepareStatement(sql)) {
-            stmt.setTimestamp(1, Timestamp.valueOf(lastUpdateTime));
-            try (ResultSet rs = stmt.executeQuery()) {
-                if (rs.next()) {
-                    return rs.getInt("changedOptionsCount");
-                } else {
-                    return 0;
-                }
-            }
-        }
-    }
-
     public static int saveOptionToDatabase(Option option) {
         String sql = "INSERT INTO options (link, imgSrc, hotelName, country, dateRange,numberOfPeople, isPriceChanged, newPrice) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
@@ -331 +306 @@
         return id;
     }
-
     public static void saveOptionDetails(int id, String type, String board, String amenity, float price) {
         String sql = "INSERT INTO optionDetails (optionId, type, board, amenities, price) VALUES (?, ?, ?, ?, ?)";
@@ -347 +321 @@
         }
     }
-
     public static void dropOptions() throws SQLException {
         String sql = "DROP TABLE options";
@@ -358 +331 @@
         }
     }
+
+    public static void updateOptionDetails(int detail_id, String type, String board, String amenities, float price) {
+        String sql = "UPDATE optionDetails SET ";
+
+        List<String> updates = new ArrayList<>();
+        if (type != null && !type.isEmpty()) updates.add("type = '" + type + "'");
+        if (board != null && !board.isEmpty()) updates.add("board = '" + board + "'");
+        if (amenities != null && !amenities.isEmpty()) updates.add("amenities = '" + amenities + "'");
+        updates.add("price = " + price);
+
+        sql += String.join(", ", updates);
+        sql += " WHERE id = " + detail_id;
+
+        try (Connection conn = getConnection();
+             PreparedStatement stmt = conn.prepareStatement(sql)) {
+            stmt.executeUpdate();
+        } catch (SQLException e) {
+            e.printStackTrace();
+        }
+    }
+
     public static void updateOptionInDatabase(Option option) {
         String sql = "UPDATE options SET link = ?, imgSrc = ?, hotelName = ?, country = ?, price = ?, dateRange = ?, isPriceChanged = ?, newPrice = ? WHERE id = ?";
@@ -376 +370 @@
         }
     }
-
     public static Option findOption(Option option) {
         String sql = "SELECT * FROM options WHERE id = ?";
@@ -404 +397 @@
     }
 
-    public static List<Option> fetchAllOptions() {
-        List<Option> options = new ArrayList<>();
-        String sql = "SELECT * FROM options";
-
-        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:globe_guru.db");
+
+    public static int getCurrentOptionsCount() throws SQLException {
+        String sql = "SELECT COUNT(*) AS optionsCount FROM options";
+        try (Connection conn = getConnection();
              PreparedStatement stmt = conn.prepareStatement(sql);
              ResultSet rs = stmt.executeQuery()) {
-            while (rs.next()) {
-                Option option = new Option();
-                option.setId(rs.getInt("id"));
-                option.setLink(rs.getString("link"));
-                option.setImgSrc(rs.getString("imgSrc"));
-                option.setHotelName(rs.getString("hotelName"));
-                option.setCountry(rs.getString("country"));
-                option.setPrice(rs.getFloat("price"));
-                option.setDateRange(rs.getString("dateRange"));
-                option.setPriceChanged(rs.getBoolean("isPriceChanged"));
-                option.setNewPrice(rs.getInt("newPrice"));
-                option.setNumPeople(rs.getInt("numberOfPeople"));
-                options.add(option);
-            }
-
-        } catch (SQLException e) {
-            e.printStackTrace();
-        }
-        return options;
+            if (rs.next()) {
+                return rs.getInt("optionsCount");
+            } else {
+                return 0;
+            }
+        }
+    }
+    public static int getChangedOptionsCountSinceLastUpdate() throws SQLException, IOException {
+        LocalDateTime lastUpdateTime = Server.getLastUpdateTime();
+        if (lastUpdateTime == null) {
+            return 0;
+        }
+        String sql = "SELECT COUNT(*) AS changedOptionsCount FROM options WHERE lastModified > ?";
+        try (Connection conn = getConnection();
+             PreparedStatement stmt = conn.prepareStatement(sql)) {
+            stmt.setTimestamp(1, Timestamp.valueOf(lastUpdateTime));
+            try (ResultSet rs = stmt.executeQuery()) {
+                if (rs.next()) {
+                    return rs.getInt("changedOptionsCount");
+                } else {
+                    return 0;
+                }
+            }
+        }
     }
 

backend/GlobeGuru-backend/src/main/java/Option.java

@@ -3 +3 @@
 public class Option {
     private int id;
+    private int detail_id;
     private String hotelName;
     private String country;
@@ -24 +25 @@
     public void setDateRange(String dateRange) {
         this.dateRange = dateRange;
+    }
+
+    public int getDetail_id() {
+        return detail_id;
+    }
+
+    public void setDetail_id(int detail_id) {
+        this.detail_id = detail_id;
     }
 

backend/GlobeGuru-backend/src/main/java/ScraperThread.java

@@ -14 +14 @@
 import org.openqa.selenium.support.ui.WebDriverWait;
 
+import javax.xml.crypto.Data;
 import java.io.File;
 import java.io.IOException;
@@ -164 +165 @@
              }else continue;
 
-             DatabaseUtil.saveOptionDetails(option.getId(), type,board,amenity, price);
+             //Check for changes
+             int odId = checkForChanges(option.getId(), type, board,amenity,price);
+             if(odId != 0) { //true = changes found - update details
+                 DatabaseUtil.updateOptionDetails(odId,type,board,amenity,price);
+             }else{ //false = not found / no changes - save regular
+                 DatabaseUtil.saveOptionDetails(option.getId(), type, board, amenity, price);
+             }
          }
         }
@@ -194 +201 @@
                 }
                 System.out.println(type + board + price + amenities);
-                DatabaseUtil.saveOptionDetails(option.getId(), type, board, amenities.toString(), price);
-            }
-
-        }
+                int odId = checkForChanges(option.getId(), type, board,amenities.toString(),price);
+                if(odId != 0) { //true = changes found - update details
+                    DatabaseUtil.updateOptionDetails(odId,type,board,amenities.toString(),price);
+                }else{ //false = not found / no changes - save regular
+                    DatabaseUtil.saveOptionDetails(option.getId(), type, board, amenities.toString(), price);
+                }
+            }
+
+        }
+    }
+    private int checkForChanges(int id, String type, String board, String amenities, float price){ //return true for changes, false for no changes
+        try {
+            List<Option> pooled = DatabaseUtil.poolOptionDetails(id);
+            if (pooled.isEmpty()) { //not saved = no changes - save regular
+                return 0;
+            }else{ //got the options saved details
+                for(Option o : pooled){
+                    if(o.getType().equals(type) && o.getBoard().equals(board)){//for the room and board check amenity and price changes (Assumption type of room and board do not change)
+                        if((!o.getAmenities().equals(amenities)) || o.getPrice() != price){
+                            return o.getDetail_id(); //Change
+                        }
+                    }
+                }
+            }
+        }catch(SQLException e){
+            e.printStackTrace();
+        }
+        return 0; //no changes detected
     }
     private Option optionParser(String data, int numPeople){
@@ -218 +249 @@
             return null;
         }
-        //scrapeOptionInfo(created);
         return created;
     }
@@ -235 +265 @@
         Element countryElement = doc.selectFirst("l.ponuda-lokacija");
         created.setCountry(countryElement != null ? countryElement.text() : null);
-        //Element priceElement = doc.selectFirst("div.ponuda-cena");
         Element dateElement = doc.selectFirst("l.ponuda-opis.termin");
         created.setDateRange(dateElement != null ? dateElement.text() : null);
-        /*float price = Float.parseFloat(priceElement != null ? priceElement.text().replaceAll("[^\\d.]", "") : "0");
-        created.setPrice(price);*/
         return created;
     }
@@ -252 +279 @@
         String country = countryP.text().replaceAll("leto hoteli", "");
         created.setCountry(country);
-        /*Element priceElem = doc.selectFirst("span.hotel-price");
-        String priceText = priceElem.text();
-        float price = 0;
-        if(!priceText.isEmpty()) {
-            price = Float.parseFloat(priceText.replace("€", ""));
-        }
-        created.setPrice(price);*/
         String[] queryParams = link.split("[?&]");
         String startDateStr = null;

backend/GlobeGuru-backend/target/classes/URLsJSON.json

@@ -2 +2 @@
   "agencyurls":
           [
+            "https://magelantravel.mk/",
             "https://booking.escapetravel.mk/"
           ]