Blogs: related tags

5 files changed, 60 insertions, 2 deletions

diff --git a/src/java/com/juick/http/www/Blogs.java b/src/java/com/juick/http/www/Blogs.java
index 4af75cd3..527009a1 100644
--- a/src/java/com/juick/http/www/Blogs.java
+++ b/src/java/com/juick/http/www/Blogs.java
@@ -42,6 +42,24 @@ import javax.servlet.http.HttpServletResponse;
  */
 public class Blogs {
 
+    private int relatedTagsStartMID = 0;
+
+    public Blogs(Connection sql) {
+        PreparedStatement stmt = null;
+        ResultSet rs = null;
+        try {
+            stmt = sql.prepareStatement("SELECT MIN(message_id) FROM messages WHERE ts>DATE_ADD(NOW(),INTERVAL -6 MONTH)");
+            rs = stmt.executeQuery();
+            if (rs.first()) {
+                relatedTagsStartMID = rs.getInt(1);
+            }
+        } catch (SQLException e) {
+            System.err.println(e);
+        } finally {
+            Utils.finishSQL(rs, stmt);
+        }
+    }
+
     protected void doGet(Connection sql, Connection sqlSearch, HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
         String paramTagStrQuery = request.getParameter("tag");
         if (paramTagStrQuery != null) {
@@ -218,6 +236,13 @@ public class Blogs {
 
             out.println("<div id=\"rcol\" class=\"abs\">");
             PageTemplates.pageYandexAd(out, visitor == null ? 2 : 3); // разный ID для залогиненых и нет
+            if (paramTag > 0 && relatedTagsStartMID > 0) {
+                String related = getRelatedTags(sql, paramTag, 10);
+                if (!related.isEmpty()) {
+                    out.println("<h3>Related tags:</h3>");
+                    out.println("<ul id=\"reltags\">" + related + "</ul>");
+                }
+            }
             out.println("</div>");
 
             out.println("</div>"); // topwrapper
@@ -277,4 +302,30 @@ public class Blogs {
 
         return ret;
     }
+
+    private String getRelatedTags(Connection sql, int tag_id, int cnt) {
+        String ret = "";
+
+        PreparedStatement stmt = null;
+        ResultSet rs = null;
+        try {
+            stmt = sql.prepareStatement("SELECT tags.name,COUNT(message_id) AS cnt FROM messages_tags INNER JOIN tags ON messages_tags.tag_id=tags.tag_id WHERE messages_tags.message_id>" + relatedTagsStartMID + " AND messages_tags.message_id IN (SELECT message_id FROM messages_tags WHERE tag_id=?) AND messages_tags.tag_id NOT IN (SELECT tag_id FROM tags_ignore) GROUP BY messages_tags.tag_id HAVING cnt>1 ORDER BY cnt DESC LIMIT 1,?");
+            stmt.setInt(1, tag_id);
+            stmt.setInt(2, cnt);
+            rs = stmt.executeQuery();
+            rs.beforeFirst();
+            while (rs.next()) {
+                try {
+                    ret += "<li><a href=\"/tag/" + URLEncoder.encode(rs.getString(1), "UTF-8") + "\">" + Utils.encodeHTML(rs.getString(1)) + "</a></li>";
+                } catch (UnsupportedEncodingException e) {
+                }
+            }
+        } catch (SQLException e) {
+            System.err.println(e);
+        } finally {
+            Utils.finishSQL(rs, stmt);
+        }
+
+        return ret;
+    }
 }
diff --git a/src/java/com/juick/http/www/Main.java b/src/java/com/juick/http/www/Main.java
index e4b79962..226ac729 100644
--- a/src/java/com/juick/http/www/Main.java
+++ b/src/java/com/juick/http/www/Main.java
@@ -41,7 +41,7 @@ public class Main extends HttpServlet implements XmppListener {
     Connection sql;
     Connection sqlSearch;
     XmppConnection xmpp;
-    Blogs blogs = new Blogs();
+    Blogs blogs;
     RootRedirects rootRedirects = new RootRedirects();
     Login login = new Login();
     Help help = new Help();
@@ -65,6 +65,8 @@ public class Main extends HttpServlet implements XmppListener {
             xmpp.start();
              */
 
+            blogs = new Blogs(sql);
+
             PageTemplates.sape = new Sape(conf.getProperty("sape_user"), "juick.com", 2000, 3600);
         } catch (Exception e) {
             log(null, e);
diff --git a/src/java/com/juick/http/www/PageTemplates.java b/src/java/com/juick/http/www/PageTemplates.java
index 9e4eff98..5851a3ee 100644
--- a/src/java/com/juick/http/www/PageTemplates.java
+++ b/src/java/com/juick/http/www/PageTemplates.java
@@ -52,7 +52,7 @@ public class PageTemplates {
         out.println("<html>");
         out.println("<head>");
         out.println("  <title>" + title + "</title>");
-        out.println("  <link rel=\"stylesheet\" href=\"//static.juick.com/style3.2013010500.css\"/>");
+        out.println("  <link rel=\"stylesheet\" href=\"//static.juick.com/style3.2013010900.css\"/>");
         out.println("  <link rel=\"icon\" type=\"image/png\" href=\"//static.juick.com/favicon.png\"/>");
         out.println("  <script type=\"text/javascript\" src=\"//ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js\" defer=\"defer\"></script>");
         out.println("  <script type=\"text/javascript\" src=\"//ajax.googleapis.com/ajax/libs/swfobject/2.2/swfobject.js\" defer=\"defer\" async=\"async\"></script>");
diff --git a/src/java/com/juick/http/www/User.java b/src/java/com/juick/http/www/User.java
index 1d331956..8574eaab 100644
--- a/src/java/com/juick/http/www/User.java
+++ b/src/java/com/juick/http/www/User.java
@@ -127,6 +127,9 @@ public class User {
                 if (paramTag > 0) {
                     nextpage += "&amp;tag=" + URLEncoder.encode(paramTagStr, "UTF-8");
                 }
+                if (paramSearch != null) {
+                    nextpage += "&amp;search=" + URLEncoder.encode(paramSearch, "UTF-8");
+                }
                 out.println("<p class=\"page\"><a href=\"" + nextpage + "\">Older →</a></p>");
             }
             out.println("</div>");
diff --git a/web/style3.css b/web/style3.css
index adb686f0..2e8efe5e 100644
--- a/web/style3.css
+++ b/web/style3.css
@@ -96,6 +96,8 @@ blockquote { border-left: 1px dashed #CCC; margin: 10px 0 10px 10px; padding-lef
 /********/
 
 #rcol { width: 190px; top: 0; margin: 10px 0 0 810px; padding-top: 10px; overflow: hidden; font-size: smaller; }
+#rcol h3 { margin: 8px 0; }
+#reltags li { font-size: larger; margin: 2px 0 2px 10px; }
 
 /********/