Changeset 1c51912 for backend/GlobeGuru-backend

Timestamp:

01/10/25 19:07:51 (5 days ago)

Author:

Kristijan <kristijanzafirovski26@…>

Branches:

master

Children:

cd64b06

Parents:

53bad7e

Message:

Added details for magelan

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) (10 diffs)
• src/main/resources/URLsJSON.json (modified) (1 diff)
• target/classes/DatabaseUtil.class (modified) ( previous)
• target/classes/Option.class (modified) ( previous)
• target/classes/ScraperThread.class (modified) ( previous)
• target/classes/URLsJSON.json (modified) (1 diff)
• target/classes/lastUpdateTime.json (modified) (1 diff)

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

@@ -3 +3 @@
 import java.time.LocalDateTime;
 import java.util.ArrayList;
+import java.util.HashMap;
 import java.util.List;
+import java.util.Map;
 
 public class DatabaseUtil {
@@ -29 +31 @@
                              "hotelName TEXT, " +
                              "country TEXT, " +
-                             "price REAL, " +
                              "dateRange TEXT, " +
                              "numberOfPeople INTEGER, " +
@@ -35 +36 @@
                              "newPrice REAL DEFAULT 0)"
              );
-
              PreparedStatement stmt3 = conn.prepareStatement(
                      "CREATE TABLE IF NOT EXISTS savedOptions (" +
-                             "userId INTEGER, " +
-                             "optionId INTEGER, " +
-                             "FOREIGN KEY(userId) REFERENCES users(id), " +
-                             "FOREIGN KEY(optionId) REFERENCES options(id), " +
-                             "UNIQUE(userId, optionId))"
+                             "userId INTEGER," +
+                             "detailId INTEGER," +
+                             "FOREIGN KEY(userId) REFERENCES users(id)," +
+                             "FOREIGN KEY(detailId) REFERENCES optionDetails(id)," +
+                             "UNIQUE(userId, detailId))"
+             );
+             PreparedStatement stmt4 = conn.prepareStatement(
+                     "CREATE TABLE IF NOT EXISTS optionDetails (" +
+                             "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
+                             "optionId INTEGER NOT NULL, " +
+                             "type TEXT, " +
+                             "board TEXT, " +
+                             "amenities TEXT, " +
+                             "price REAL," +
+                             "FOREIGN KEY(optionId) REFERENCES options(id))"
              )) {
+
             stmt1.executeUpdate();
             stmt2.executeUpdate();
             stmt3.executeUpdate();
+            stmt4.executeUpdate();
         }
     }
@@ -136 +148 @@
     public static List<Option> queryOptions(String destination, String dateQuery, int numPeople, boolean dateFlag) throws SQLException {
         List<Option> options = new ArrayList<>();
-        String sql = "SELECT * FROM options WHERE (country LIKE ? OR hotelName LIKE ?)";
-        System.out.println(dateQuery);
+        String sql = "SELECT o.*, od.id AS detail_id, od.type, od.board, od.amenities, od.price AS detail_price " +
+                "FROM options o " +
+                "LEFT JOIN optionDetails od ON o.id = od.optionId " +
+                "WHERE (o.country LIKE ? OR o.hotelName LIKE ?)";
+
         if (dateQuery != null && !dateQuery.isEmpty() && !dateFlag) {
-            sql += (" AND dateRange = ?");
-        } //append date
-        if (dateFlag) {   //search only from dates
-            sql += " AND dateRange LIKE ?";
-        }
-        if(numPeople != 0) { //with number of people
-            sql += " AND numberOfPeople = ?";
-        }
-
-        System.out.println("Searching for dest:" + destination + "\n" + sql);
+            sql += " AND o.dateRange = ?";
+        }
+        if (dateFlag) {
+            sql += " AND o.dateRange LIKE ?";
+        }
+        if (numPeople != 0) {
+            sql += " AND o.numberOfPeople = ?";
+        }
+
         try (Connection conn = getConnection();
              PreparedStatement stmt = conn.prepareStatement(sql)) {
             stmt.setString(1, "%" + destination + "%");
             stmt.setString(2, "%" + destination + "%");
+            int paramIndex = 3;
             if (dateQuery != null && !dateQuery.isEmpty() && !dateFlag) {
-                stmt.setString(3, dateQuery);
+                stmt.setString(paramIndex++, dateQuery);
             }
             if (dateFlag) {
-                stmt.setString(3, dateQuery + "%");
-            }
-            if(numPeople != 0) {
-                stmt.setInt(4, numPeople);
-            }
+                stmt.setString(paramIndex++, dateQuery + "%");
+            }
+            if (numPeople != 0) {
+                stmt.setInt(paramIndex, numPeople);
+            }
+
             try (ResultSet rs = stmt.executeQuery()) {
                 while (rs.next()) {
                     Option option = new Option();
-                    option.setId(rs.getInt("id"));
+                    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.setPrice(rs.getFloat("price"));
                     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("detail_price"));
                     options.add(option);
                 }
             }
         }
-        System.out.println("Found " + options.size());
+
+        System.out.println("Found " + options.size() + " options");
         return options;
     }
 
-    public static boolean saveFavoriteOption(int userId, int optionId) throws SQLException {
-        String sql = "INSERT INTO savedOptions (userId, optionId) VALUES (?, ?) ON CONFLICT DO NOTHING";
+
+    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, optionId);
+            stmt.setInt(2, detailId);
             return stmt.executeUpdate() > 0;
         }
@@ -192 +213 @@
 
 
-    public static boolean removeFavoriteOption(int userId, int optionId) throws SQLException {
-        String sql = "DELETE FROM savedOptions WHERE userId = ? AND optionId = ?";
+
+    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, optionId);
+            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 options.* FROM savedOptions JOIN options ON savedOptions.optionId = options.id WHERE savedOptions.userId = ?";
+        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)) {
@@ -210 +236 @@
                 while (rs.next()) {
                     Option option = new Option();
-                    option.setId(rs.getInt("id"));
+                    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"));
-                    option.setDateRange(rs.getString("dateRange"));
-                    option.setPriceChanged(rs.getBoolean("isPriceChanged"));
-                    option.setNewPrice(rs.getInt("newPrice"));
                     savedTrips.add(option);
                 }
@@ -225 +253 @@
         return savedTrips;
     }
+
 
 
@@ -274 +303 @@
     }
 
-    public static void saveOptionToDatabase(Option option) {
-        String sql = "INSERT INTO options (link, imgSrc, hotelName, country, price, dateRange,numberOfPeople, isPriceChanged, newPrice) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
+    public static int saveOptionToDatabase(Option option) {
+        String sql = "INSERT INTO options (link, imgSrc, hotelName, country, dateRange,numberOfPeople, isPriceChanged, newPrice) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
+        int id = 0;
         try (Connection conn = DriverManager.getConnection("jdbc:sqlite:globe_guru.db");
              PreparedStatement stmt = conn.prepareStatement(sql)) {
@@ -282 +312 @@
             stmt.setString(3, option.getHotelName());
             stmt.setString(4, option.getCountry());
-            stmt.setFloat(5, option.getPrice());
-            stmt.setString(6, option.getDateRange());
-            stmt.setInt(7,option.getNumPeople());
-            stmt.setBoolean(8, option.isPriceChanged());
-            stmt.setFloat(9, option.getNewPrice());
+            stmt.setString(5, option.getDateRange());
+            stmt.setInt(6,option.getNumPeople());
+            stmt.setBoolean(7, option.isPriceChanged());
+            stmt.setFloat(8, option.getNewPrice());
+            stmt.executeUpdate();
+            try(ResultSet genKey = stmt.getGeneratedKeys()){
+                if(genKey.next()){
+                    id = genKey.getInt(1);
+                }
+            }
+        } catch (SQLException e) {
+            e.printStackTrace();
+        }
+        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 (?, ?, ?, ?, ?)";
+
+        try (Connection conn = getConnection();
+             PreparedStatement stmt = conn.prepareStatement(sql)) {
+            stmt.setInt(1, id);
+            stmt.setString(2, type);
+            stmt.setString(3, board);
+            stmt.setString(4, amenity);
+            stmt.setFloat(5, price);
             stmt.executeUpdate();
         } catch (SQLException e) {
@@ -292 +343 @@
         }
     }
-
 
     public static void dropOptions() throws SQLException {
@@ -350 +400 @@
     }
 
+    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");
+             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;
+    }
+
 }

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

@@ -8 +8 @@
     private String link;
     private String imgSrc;
+    private String type;
+    private String board;
+    private String amenities;
+
     private int numPeople;
     //Price changing
@@ -26 +30 @@
     }
 
+    public String getType() {
+        return type;
+    }
+
+    public void setType(String type) {
+        this.type = type;
+    }
+
+    public String getBoard() {
+        return board;
+    }
+
+    public void setBoard(String board) {
+        this.board = board;
+    }
+
+    public String getAmenities() {
+        return amenities;
+    }
+
+    public void setAmenities(String amenities) {
+        this.amenities = amenities;
+    }
+
     public boolean isEmpty(){
-        return (hotelName == null || country == null || price == 0 || link == null || imgSrc == null);
+        return (hotelName == null || country == null || link == null || imgSrc == null);
     }
     public String getHotelName() {

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

@@ -39 +39 @@
     }
 
-    private WebDriver driver;
+    public WebDriver driver;
 
     private void initializeWebDriver() {
@@ -70 +70 @@
                 wait.until(ExpectedConditions.presenceOfElementLocated(By.cssSelector("div.sodrzina")));
                 break;
-            default:
-                System.out.println("URL not recognized for waiting condition.");
-                // Handle other URLs if needed
         }
 
@@ -92 +89 @@
                             Option existingOption = DatabaseUtil.findOption(option);
                             if (existingOption != null) {
-                                if (existingOption.equals(option) || existingOption.getPrice() != option.getPrice()) {
+                                if (existingOption.equals(option)) {
                                     option.setPriceChanged(true);
                                     option.setNewPrice(option.getPrice());
@@ -112 +109 @@
                 if (parentDiv != null) {
                     childDivs = parentDiv.select("div.destinacija");
-                    System.out.println(childDivs.size());
                     childDivs.removeIf(div -> div.attr("style").contains("display:none") || div.attr("style").contains("display: none"));
                     System.out.println("Filtered childDivs size: " + childDivs.size());
@@ -119 +115 @@
                         Option newOption = optionParser(data,numPeople);
                         if (newOption != null) {
-                            Option existingOption = DatabaseUtil.findOption(newOption);
-                            if (existingOption != null) {
-                                if (existingOption.equals(newOption) || existingOption.getPrice() != newOption.getPrice()) {
-                                    newOption.setPriceChanged(true);
-                                    newOption.setNewPrice(newOption.getPrice());
-                                }
-                                DatabaseUtil.updateOptionInDatabase(newOption);
-                            } else if (optionSet.add(newOption)) {
+                            if (optionSet.add(newOption)) {
                                 uniqueOptions.add(newOption);
-                                DatabaseUtil.saveOptionToDatabase(newOption);
+
+                                newOption.setId(DatabaseUtil.saveOptionToDatabase(newOption));
+                                scrapeOptionInfo(newOption);
                                 System.out.println("Parsed " + newOption);
                             }
@@ -142 +133 @@
         }
     }
-
-
-
-    private Option optionParser(String data, int numPeople) {
+    private void scrapeOptionInfo(Option option) {
+        String url = option.getLink();
+        if(url.contains("magelantravel.mk")) {
+            System.out.println("Scraping info for " + option.getHotelName());
+         String[] dates = option.getDateRange().split(" - ");
+         url += "&checkin=" + dates[0] + "&checkout=" + dates[1] + "&adult=" + option.getNumPeople();
+
+         driver.get(url);
+         try { Thread.sleep(5000); } catch (InterruptedException e) { e.printStackTrace(); } //data fetch
+         String pageSource = driver.getPageSource();
+         Document doc = Jsoup.parse(pageSource);
+         Elements roomOptions = doc.select(".tblroom > tbody > tr");
+         for (Element roomOption : roomOptions) {
+             String type = roomOption.select("a.tblroom-type").text();
+
+             String board = roomOption.select(".rezervacija-objekt").text();
+             if(board.length() > 2){
+                 board = board.substring(0,2);
+             }
+             if(board.isEmpty() || type.isEmpty()){
+                 continue;
+             }
+             Elements amenityElement = roomOption.select(".objekt-opis");
+             String amenity = (amenityElement != null ? amenityElement.text() : "");
+             System.out.println(amenity + " " + board + " " + type );
+             String priceText = roomOption.select(".tbl-cena").text().replace("€", "").trim();
+             float price;
+             if (!priceText.isEmpty()) {
+                 price = Float.parseFloat(priceText);
+             }else continue;
+
+             DatabaseUtil.saveOptionDetails(option.getId(), type,board,amenity, price);
+         }
+        }
+    }
+    private Option optionParser(String data, int numPeople){
         Document doc = Jsoup.parse(data);
         Option created = new Option();
@@ -162 +185 @@
         }
         if (created.isEmpty()) {
-            System.out.println(created);
             return null;
         }
+        //scrapeOptionInfo(created);
         return created;
     }
@@ -181 +204 @@
         Element countryElement = doc.selectFirst("l.ponuda-lokacija");
         created.setCountry(countryElement != null ? countryElement.text() : null);
-        Element priceElement = doc.selectFirst("div.ponuda-cena");
+        //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);
+        /*float price = Float.parseFloat(priceElement != null ? priceElement.text().replaceAll("[^\\d.]", "") : "0");
+        created.setPrice(price);*/
         return created;
     }
@@ -198 +221 @@
         String country = countryP.text().replaceAll("leto hoteli", "");
         created.setCountry(country);
-        Element priceElem = doc.selectFirst("span.hotel-price");
+        /*Element priceElem = doc.selectFirst("span.hotel-price");
         String priceText = priceElem.text();
         float price = 0;
@@ -204 +227 @@
             price = Float.parseFloat(priceText.replace("€", ""));
         }
-        created.setPrice(price);
+        created.setPrice(price);*/
         String[] queryParams = link.split("[?&]");
         String startDateStr = null;

backend/GlobeGuru-backend/src/main/resources/URLsJSON.json

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

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

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

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

@@ -1 +1 @@
 {
-  "lastUpdateTime" : "2025-01-09T19:56:28.178946100"
+  "lastUpdateTime" : "2025-01-10T18:31:22.019011800"
 }