From 6a6b574cd010f01b4d26907707a28e70d3e384c6 Mon Sep 17 00:00:00 2001 From: Ugnich Anton Date: Wed, 9 Jan 2013 16:59:09 +0700 Subject: Blogs: related tags --- src/java/com/juick/http/www/Blogs.java | 51 ++++++++++++++++++++++++++ src/java/com/juick/http/www/Main.java | 4 +- src/java/com/juick/http/www/PageTemplates.java | 2 +- src/java/com/juick/http/www/User.java | 3 ++ web/style3.css | 2 + 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("
"); PageTemplates.pageYandexAd(out, visitor == null ? 2 : 3); // разный ID для залогиненых и нет + if (paramTag > 0 && relatedTagsStartMID > 0) { + String related = getRelatedTags(sql, paramTag, 10); + if (!related.isEmpty()) { + out.println("

Related tags:

"); + out.println(""); + } + } out.println("
"); out.println(""); // 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 += "
  • " + Utils.encodeHTML(rs.getString(1)) + "
    • "; + } 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(""); out.println(""); out.println(" " + title + ""); - out.println(" "); + out.println(" "); out.println(" "); out.println(" "); out.println(" "); 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 += "&tag=" + URLEncoder.encode(paramTagStr, "UTF-8"); } + if (paramSearch != null) { + nextpage += "&search=" + URLEncoder.encode(paramSearch, "UTF-8"); + } out.println("

    Older →

    "); } out.println(""); 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; } /********/ -- cgit v1.2.3