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.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; import org.springframework.util.Assert; 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 BaseJdbcService implements SubscriptionService { private final UserService userService; private final MessagesService messagesService; @Inject public SubscriptionServiceImpl(JdbcTemplate jdbcTemplate, UserService userService, MessagesService messagesService) { super(jdbcTemplate, null); Assert.notNull(userService); this.userService = userService; Assert.notNull(messagesService); this.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) { String tagsStr = StringUtils.arrayToCommaDelimitedString(tags.toArray()); List tagUsers = getJdbcTemplate().queryForList("SELECT suser_id FROM subscr_tags " + "WHERE tag_id IN (" + tagsStr + ") AND suser_id!=? " + " AND suser_id NOT IN (SELECT user_id FROM bl_users WHERE bl_user_id=?)" + " AND suser_id NOT IN (SELECT user_id FROM bl_tags WHERE tag_id IN (" + tagsStr + "))", 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; } }