From d03e8b19d346563afec5fb2b6b9c9dcea68040e0 Mon Sep 17 00:00:00 2001 From: Alexander Alexeev Date: Mon, 14 Nov 2016 15:56:32 +0700 Subject: jdbc services --- .../com/juick/service/SubscriptionServiceImpl.java | 177 ++++++++++++++++++++- 1 file changed, 176 insertions(+), 1 deletion(-) (limited to 'server-core/src/main/java/com/juick/service/SubscriptionServiceImpl.java') diff --git a/server-core/src/main/java/com/juick/service/SubscriptionServiceImpl.java b/server-core/src/main/java/com/juick/service/SubscriptionServiceImpl.java index 811f5af0..57d52cbf 100644 --- a/server-core/src/main/java/com/juick/service/SubscriptionServiceImpl.java +++ b/server-core/src/main/java/com/juick/service/SubscriptionServiceImpl.java @@ -1,10 +1,185 @@ package com.juick.service; +import com.juick.Tag; +import com.juick.User; +import com.juick.server.helpers.NotifyOpts; +import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.stereotype.Repository; +import org.springframework.transaction.annotation.Transactional; +import org.springframework.util.StringUtils; + +import javax.inject.Inject; +import java.util.*; +import java.util.stream.Collectors; /** * Created by aalexeev on 11/13/16. */ @Repository -public class SubscriptionServiceImpl extends BaseJdbcDao implements SubscriptionService { +public class SubscriptionServiceImpl extends BaseJdbcService implements SubscriptionService { + @Inject + UserService userService; + @Inject + MessagesService messagesService; + + @Transactional(readOnly = true) + @Override + public List getJIDSubscribedToUser(final int uid, final boolean friendsonly) { + if (friendsonly == false) { + return getJdbcTemplate().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 getJdbcTemplate().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); + } + } + + @Transactional(readOnly = true) + @Override + public List getSubscribedUsers(final int uid, final int mid) { + User author = messagesService.getMessageAuthor(mid); + + List userids = userService.getUserReaders(uid); + + Set set = new HashSet<>(); + set.addAll(userids.stream().map(User::getUID).collect(Collectors.toList())); + List tags = messagesService.getMessageTagsIDs(mid); + if (tags.size() > 0) { + List tagUsers = getJdbcTemplate().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 userService.getUsersByID(new ArrayList<>(set)); + } + + @Transactional(readOnly = true) + @Override + public List getUsersSubscribedToComments(final int mid, final int ignore_uid) { + List userids = getJdbcTemplate().queryForList( + "SELECT suser_id FROM subscr_messages WHERE message_id=? AND suser_id!=?", + Integer.class, + mid, + ignore_uid); + if (userids.size() > 0) { + return userService.getUsersByID(userids); + } else { + return Collections.emptyList(); + } + } + + @Transactional(readOnly = true) + @Override + public List getUsersSubscribedToUserRecommendations(final int uid, final int mid, final int muid) { + List tags = messagesService.getMessageTagsIDs(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 = getJdbcTemplate().queryForList(query, Integer.class); + + return userService.getUsersByID(userids); + } + + @Transactional + @Override + public boolean subscribeMessage(final int mid, final int vuid) { + return getJdbcTemplate().update( + "INSERT IGNORE INTO subscr_messages(suser_id,message_id) VALUES (" + vuid + "," + mid + ")") == 1; + } + + @Transactional + @Override + public boolean unSubscribeMessage(final int mid, final int vuid) { + return getJdbcTemplate().update( + "DELETE FROM subscr_messages WHERE message_id=? AND suser_id=?", + mid, vuid) > 0; + } + + @Transactional + @Override + public boolean subscribeUser(final User user, final User toUser) { + return getJdbcTemplate().update( + "INSERT IGNORE INTO subscr_users(user_id,suser_id) VALUES (?,?)", + toUser.getUID(), user.getUID()) == 1; + } + + @Transactional + @Override + public boolean unSubscribeUser(final User user, final User fromUser) { + return getJdbcTemplate().update( + "DELETE FROM subscr_users WHERE suser_id=? AND user_id=?", + user.getUID(), fromUser.getUID()) > 0; + } + + @Transactional + @Override + public boolean subscribeTag(final User user, final Tag toTag) { + return getJdbcTemplate().update( + "INSERT IGNORE INTO subscr_tags(tag_id,suser_id) VALUES (?,?)", + toTag.TID, user.getUID()) == 1; + } + + @Transactional + @Override + public boolean unSubscribeTag(final User user, final Tag toTag) { + return getJdbcTemplate().update( + "DELETE FROM subscr_tags WHERE tag_id=? AND suser_id=?", + toTag.TID, user.getUID()) > 0; + } + + @Transactional(readOnly = true) + @Override + public NotifyOpts getNotifyOptions(final User user) { + try { + return getJdbcTemplate().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(); + } + } + + @Transactional + @Override + public boolean setNotifyOptions(final User user, final NotifyOpts options) { + return getJdbcTemplate().update( + "UPDATE useroptions SET jnotify=? WHERE user_id=?", options.isRepliesEnabled() ? 1 : 0, + user.getUID()) > 0 && + getJdbcTemplate().update( + "UPDATE useroptions SET subscr_notify=? WHERE user_id=?", options.isSubscriptionsEnabled() ? 1 : 0, + user.getUID()) > 0 && + getJdbcTemplate().update( + "UPDATE useroptions SET recommendations=? WHERE user_id=?", options.isRecommendationsEnabled() ? 1 : 0, + user.getUID()) > 0; + } } -- cgit v1.2.3