From ffb24e0d469aaafa35ad5f460a7bdf771382f0ff Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Wed, 16 Nov 2016 14:30:50 +0300 Subject: server-core -> juick-server --- .../com/juick/server/SubscriptionsQueries.java | 125 +++++++++++++++++++++ 1 file changed, 125 insertions(+) create mode 100644 juick-server/src/main/java/com/juick/server/SubscriptionsQueries.java (limited to 'juick-server/src/main/java/com/juick/server/SubscriptionsQueries.java') diff --git a/juick-server/src/main/java/com/juick/server/SubscriptionsQueries.java b/juick-server/src/main/java/com/juick/server/SubscriptionsQueries.java new file mode 100644 index 00000000..08d704d9 --- /dev/null +++ b/juick-server/src/main/java/com/juick/server/SubscriptionsQueries.java @@ -0,0 +1,125 @@ +/* + * To change this template, choose Tools | Templates + * and open the template in the editor. + */ +package com.juick.server; + +import com.juick.Tag; +import com.juick.User; +import com.juick.server.helpers.NotifyOpts; +import org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.util.StringUtils; + +import java.util.*; +import java.util.stream.Collectors; + +/** + * + * @author ugnich + */ +public class SubscriptionsQueries { + + public static List getJIDSubscribedToUser(JdbcTemplate sql, int uid, boolean friendsonly) { + if (friendsonly == false) { + return sql.queryForList("SELECT jids.jid FROM subscr_users INNER JOIN jids " + + "ON (subscr_users.user_id=? AND subscr_users.suser_id=jids.user_id) WHERE jids.active=1", + String.class, uid); + } else { + return sql.queryForList("SELECT jids.jid FROM subscr_users INNER JOIN jids " + + "ON (subscr_users.user_id=? AND subscr_users.suser_id=jids.user_id) WHERE jids.active=1 " + + "AND jids.user_id IN (SELECT wl_user_id FROM wl_users WHERE user_id=?)", String.class, uid, uid); + } + } + + public static List getSubscribedUsers(JdbcTemplate sql, int uid, int mid) { + User author = MessagesQueries.getMessageAuthor(sql, mid); + List userids = UserQueries.getUserReaders(sql, uid); + Set set = new HashSet<>(); + set.addAll(userids.stream().map(User::getUid).collect(Collectors.toList())); + List tags = MessagesQueries.getMessageTagsIDs(sql, mid); + if (tags.size() > 0) { + List tagUsers = sql.queryForList("SELECT suser_id FROM subscr_tags " + + "WHERE tag_id IN (" + StringUtils.arrayToCommaDelimitedString(tags.toArray()) + ") AND suser_id!=? " + + " AND suser_id NOT IN (SELECT user_id FROM bl_users WHERE bl_user_id=?)", Integer.class, uid, author.getUid()); + set.addAll(tagUsers); + } + return UserQueries.getUsersByID(sql, new ArrayList<>(set)); + } + + public static List getUsersSubscribedToComments(JdbcTemplate sql, int mid, int ignore_uid) { + List userids = sql.queryForList("SELECT suser_id FROM subscr_messages WHERE message_id=? AND suser_id!=?", + Integer.class, mid, ignore_uid); + if (userids.size() > 0) { + return UserQueries.getUsersByID(sql, userids); + } else { + return Collections.emptyList(); + } + } + + public static List getUsersSubscribedToUserRecommendations(JdbcTemplate sql, int uid, int mid, int muid) { + List tags = MessagesQueries.getMessageTagsIDs(sql, mid); + + String query = "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 = StringUtils.arrayToCommaDelimitedString(tags.toArray()); + 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 + "))"; + } + List userids = sql.queryForList(query, Integer.class); + return UserQueries.getUsersByID(sql, userids); + } + + public static boolean subscribeMessage(JdbcTemplate sql, int mid, int vuid) { + return sql.update("INSERT IGNORE INTO subscr_messages(suser_id,message_id) VALUES (" + vuid + "," + mid + ")") == 1; + } + public static boolean unSubscribeMessage(JdbcTemplate sql, int mid, int vuid) { + return sql.update("DELETE FROM subscr_messages WHERE message_id=? AND suser_id=?", + mid, vuid) > 0; + } + public static boolean subscribeUser(JdbcTemplate sql, User user, User toUser) { + return sql.update("INSERT IGNORE INTO subscr_users(user_id,suser_id) VALUES (?,?)", + toUser.getUid(), user.getUid()) == 1; + } + public static boolean unSubscribeUser(JdbcTemplate sql, User user, User fromUser) { + return sql.update("DELETE FROM subscr_users WHERE suser_id=? AND user_id=?", + user.getUid(), fromUser.getUid()) > 0; + } + public static boolean subscribeTag(JdbcTemplate sql, User user, Tag toTag) { + return sql.update("INSERT IGNORE INTO subscr_tags(tag_id,suser_id) VALUES (?,?)", + toTag.TID, user.getUid()) == 1; + } + public static boolean unSubscribeTag(JdbcTemplate sql, User user, Tag toTag) { + return sql.update("DELETE FROM subscr_tags WHERE tag_id=? AND suser_id=?", + toTag.TID, user.getUid()) > 0; + } + + public static NotifyOpts getNotifyOptions(JdbcTemplate sql, User user) { + try { + return sql.queryForObject("SELECT jnotify,subscr_notify,recommendations FROM useroptions WHERE user_id=?", + (rs, num) -> { + NotifyOpts options = new NotifyOpts(); + options.setRepliesEnabled(rs.getInt(1) > 0); + options.setSubscriptionsEnabled(rs.getInt(2) > 0); + options.setRecommendationsEnabled(rs.getInt(3) > 0); + return options; + }, user.getUid()); + } catch (EmptyResultDataAccessException e) { + return new NotifyOpts(); + } + } + + public static boolean setNotifyOptions(JdbcTemplate sql, User user, NotifyOpts options) { + return sql.update("UPDATE useroptions SET jnotify=? WHERE user_id=?", options.isRepliesEnabled() ? 1 : 0, + user.getUid()) > 0 && + sql.update("UPDATE useroptions SET subscr_notify=? WHERE user_id=?", options.isSubscriptionsEnabled() ? 1 : 0, + user.getUid()) > 0 && + sql.update("UPDATE useroptions SET recommendations=? WHERE user_id=?", options.isRecommendationsEnabled() ? 1 : 0, + user.getUid()) > 0; + } +} -- cgit v1.2.3