From 3406753be46b637c0afee894cb12ed1bc5de9d29 Mon Sep 17 00:00:00 2001 From: Ugnich Anton Date: Wed, 23 Apr 2014 21:27:46 +0700 Subject: Recommendations (getJIDSubscribedToUserRecommendations) --- src/com/juick/server/MessagesQueries.java | 8 ++++++ src/com/juick/server/SubscriptionsQueries.java | 36 ++++++++++++++++++++++++++ src/com/juick/server/TagQueries.java | 2 +- 3 files changed, 45 insertions(+), 1 deletion(-) (limited to 'src/com') diff --git a/src/com/juick/server/MessagesQueries.java b/src/com/juick/server/MessagesQueries.java index 550da56d..d02985ee 100644 --- a/src/com/juick/server/MessagesQueries.java +++ b/src/com/juick/server/MessagesQueries.java @@ -138,6 +138,14 @@ public class MessagesQueries { return rid; } + public static boolean recommendMessage(Connection sql, int mid, int vuid) { + boolean res = SQLHelpers.execute(sql, "INSERT IGNORE INTO favorites(user_id,message_id) VALUES (" + vuid + "," + mid + ")") == 1; + if (res) { + SQLHelpers.executeInt(sql, "UPDATE messages SET likes=likes+1 WHERE message_id=?", mid); + } + return res; + } + public static boolean canViewThread(Connection sql, int mid, int uid) { int privacy = 0; diff --git a/src/com/juick/server/SubscriptionsQueries.java b/src/com/juick/server/SubscriptionsQueries.java index ac465948..d0f5f308 100644 --- a/src/com/juick/server/SubscriptionsQueries.java +++ b/src/com/juick/server/SubscriptionsQueries.java @@ -126,6 +126,42 @@ public class SubscriptionsQueries { return jids; } + public static ArrayList getJIDSubscribedToUserRecommendations(Connection sql, int uid, int mid, int muid) { + ArrayList jids = new ArrayList(); + + ArrayList tags = MessagesQueries.getMessageTagsIDs(sql, mid); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + String query = "SELECT jid FROM jids WHERE active=1 AND user_id!=" + uid; + query += " AND user_id IN (SELECT suser_id FROM subscr_users WHERE user_id=" + uid + ")"; + query += " AND user_id NOT IN (SELECT user_id FROM bl_users WHERE bl_user_id=" + muid + ")"; + query += " AND user_id NOT IN (SELECT suser_id FROM subscr_users WHERE user_id=" + muid + ")"; + query += " AND user_id NOT IN (SELECT suser_id FROM subscr_messages WHERE message_id=" + mid + ")"; + query += " AND user_id NOT IN (SELECT user_id FROM favorites WHERE message_id=" + mid + ")"; + query += " AND user_id NOT IN (SELECT subscr_users.suser_id FROM subscr_users INNER JOIN favorites ON (favorites.message_id=" + mid + " AND subscr_users.user_id=favorites.user_id AND favorites.user_id!=" + uid + "))"; + if (!tags.isEmpty()) { + String tagsStr = Utils.convertArrayInt2String(tags); + query += " AND user_id NOT IN (SELECT suser_id FROM subscr_tags WHERE tag_id IN (" + tagsStr + "))"; + query += " AND user_id NOT IN (SELECT user_id FROM bl_tags WHERE tag_id IN (" + tagsStr + "))"; + } + + stmt = sql.prepareStatement(query); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + jids.add(rs.getString(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return jids; + } + public static boolean subscribeMessage(Connection sql, int mid, int vuid) { return SQLHelpers.execute(sql, "INSERT IGNORE INTO subscr_messages(suser_id,message_id) VALUES (" + vuid + "," + mid + ")") == 1; } diff --git a/src/com/juick/server/TagQueries.java b/src/com/juick/server/TagQueries.java index e4d94121..4f0639f9 100644 --- a/src/com/juick/server/TagQueries.java +++ b/src/com/juick/server/TagQueries.java @@ -86,7 +86,7 @@ public class TagQueries { for (int i = 0; i < tags.length; i++) { boolean found = false; for (int n = 0; n < ret.size(); n++) { - if (tags[i].equals(ret.get(n).Name)) { + if (tags[i].equalsIgnoreCase(ret.get(n).Name)) { found = true; break; } -- cgit v1.2.3