From 379f1a8661da46040332923a5ccfb555656b0a8b Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Mon, 10 Apr 2023 22:53:40 +0300 Subject: Premium users --- .../com/juick/service/MessagesServiceImpl.java | 8 +-- src/main/java/com/juick/service/UserService.java | 7 ++- .../java/com/juick/service/UserServiceImpl.java | 57 ++++++++++++++-------- 3 files changed, 49 insertions(+), 23 deletions(-) (limited to 'src/main/java/com/juick/service') diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java index 083524b1..53f2407c 100644 --- a/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -83,6 +83,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ user.setName(Optional.ofNullable(rs.getString(5)).orElse(AnonymousUser.INSTANCE.getName())); user.setBanned(rs.getBoolean(6)); user.setUri(URI.create(Optional.ofNullable(rs.getString(22)).orElse(StringUtils.EMPTY))); + user.setPremium(rs.getInt("premium") > 0); msg.setUser(user); msg.setCreated(MessagesServiceImpl.this.getOffsetDateTime(rs, 7).toInstant()); msg.ReadOnly = rs.getBoolean(8); @@ -797,7 +798,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ COALESCE(messages.txt, '') txt, '' as q, messages.updated, 0 as to_uid, NULL as to_name, messages.updated_at, '' as m_user_uri, '' as to_uri, '' as msg_reply_uri, 0 as html, (1.*messages.replies - subscr_messages.last_read_rid) as unread, - (SELECT CASE WHEN EXISTS(SELECT * from subscr_messages where message_id=messages.message_id and suser_id=:uid) THEN 1 ELSE 0 END) subscribed + (SELECT CASE WHEN EXISTS(SELECT * from subscr_messages where message_id=messages.message_id and suser_id=:uid) THEN 1 ELSE 0 END) subscribed, + users.premium FROM messages INNER JOIN users ON messages.user_id=users.id LEFT JOIN subscr_messages ON messages.message_id=subscr_messages.message_id AND subscr_messages.suser_id=:uid LEFT JOIN favorites ON messages.message_id = favorites.message_id AND favorites.like_id=1 @@ -808,7 +810,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ messages.message_id, messages.user_id, users.nick, users.banned, messages.ts, messages.readonly, messages.privacy, messages.attach, messages.hidden, messages.repliesby, messages.txt, messages.updated, messages.replies, updated_at, - subscr_messages.last_read_rid"""; + subscr_messages.last_read_rid, users.premium"""; List msgs = getNamedParameterJdbcTemplate().query(query, new MapSqlParameterSource("ids", mids) .addValue("uid", uid), new MessageMapper()); @@ -867,7 +869,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "COALESCE(qw.user_id, m.user_id) as to_uid, COALESCE(qu.nick, mu.nick) as to_name, " + "replies.updated_at, replies.user_uri as uri, " + "qw.user_uri as to_uri, replies.reply_uri, replies.html, 0 as unread, " - + "0 as subscribed " + + "0 as subscribed, users.premium " + "FROM replies LEFT JOIN users " + "ON replies.user_id = users.id " + "LEFT JOIN replies qw ON replies.message_id = qw.message_id and replies.replyto = qw.reply_id " + "LEFT JOIN messages m on replies.message_id = m.message_id " diff --git a/src/main/java/com/juick/service/UserService.java b/src/main/java/com/juick/service/UserService.java index 05725b73..ec5beb13 100644 --- a/src/main/java/com/juick/service/UserService.java +++ b/src/main/java/com/juick/service/UserService.java @@ -152,7 +152,7 @@ public interface UserService { String getTelegramName(int uid); - Optional> getVkTokens(int uid); + List> getVkTokens(List uids); void deleteVKUser(Integer uid); @@ -166,6 +166,9 @@ public interface UserService { boolean createVKUser(long vkID, String loginhash, String token, String vkName, String vkLink); + boolean updateVkUser(long vkID, String token, String vkName, String vkLink); + boolean updateVkToken(long userId, String token); + String getFacebookNameByHash(String hash); String getTelegramNameByHash(String hash); @@ -197,4 +200,6 @@ public interface UserService { boolean addToken(Integer uid, String serviceType, String token); boolean deleteToken(String serviceType, String token); + + void setPremium(Integer uid, boolean isPremium); } diff --git a/src/main/java/com/juick/service/UserServiceImpl.java b/src/main/java/com/juick/service/UserServiceImpl.java index 932be19c..a7ea5c5f 100644 --- a/src/main/java/com/juick/service/UserServiceImpl.java +++ b/src/main/java/com/juick/service/UserServiceImpl.java @@ -65,6 +65,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { user.setSeen(seen.toInstant()); } user.setVerified(rs.getLong(6) > 0); + user.setPremium(rs.getInt(7) > 0); return user; } } @@ -113,7 +114,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { public Optional getUserByUID(final int uid) { var list = getJdbcTemplate().query(""" SELECT DISTINCT u.id, u.nick, u.passw, u.banned, u.last_seen, - COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified + COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified, premium FROM users u LEFT JOIN facebook f ON f.user_id = u.id LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id LEFT JOIN emails e ON e.user_id = u.id WHERE u.id = ?""", new UserMapper(), uid); @@ -127,7 +128,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { if (StringUtils.isNotBlank(username)) { var list = getJdbcTemplate().query(""" SELECT DISTINCT u.id, u.nick, u.passw, u.banned, u.last_seen, - COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified + COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified, premium FROM users u LEFT JOIN facebook f ON f.user_id = u.id LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id LEFT JOIN emails e ON e.user_id = u.id @@ -152,7 +153,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { try { List list = getJdbcTemplate().query( "SELECT DISTINCT u.id, u.nick, u.passw, u.banned, u.last_seen, " + - "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " + + "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified, premium " + "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " + "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " + "LEFT JOIN emails e ON e.user_id = u.id " + @@ -178,7 +179,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { if (StringUtils.isNotBlank(jid)) { List list = getJdbcTemplate().query( "SELECT DISTINCT u.id, u.nick, u.passw, u.banned, u.last_seen," + - "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " + + "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified, premium " + "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " + "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " + "LEFT JOIN emails e ON e.user_id = u.id " + @@ -200,7 +201,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { return getNamedParameterJdbcTemplate().query( "SELECT DISTINCT u.id, u.nick, u.passw, u.banned, u.last_seen," + - "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " + + "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified, premium " + "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " + "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " + "LEFT JOIN emails e ON e.user_id = u.id " + @@ -217,7 +218,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { return getNamedParameterJdbcTemplate().query( "SELECT DISTINCT u.id, u.nick, u.passw, u.banned, u.last_seen," + - "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " + + "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified, premium " + "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " + "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " + "LEFT JOIN emails e ON e.user_id = u.id " + @@ -252,7 +253,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { if (StringUtils.isNotBlank(hash)) { List list = getJdbcTemplate().query( "SELECT DISTINCT logins.user_id, u.nick, u.passw, u.banned, u.last_seen," + - "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " + + "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified, premium " + "FROM logins INNER JOIN users u ON logins.user_id = u.id " + "LEFT JOIN facebook f ON f.user_id = u.id " + "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " + @@ -290,7 +291,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { if (StringUtils.isNotBlank(username)) { List list = getJdbcTemplate().query( "SELECT DISTINCT u.id, u.nick, u.passw, u.banned, u.last_seen," + - "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " + + "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified, premium " + "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " + "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " + "LEFT JOIN emails e ON e.user_id = u.id " + @@ -719,7 +720,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { List list = getJdbcTemplate().query( """ SELECT DISTINCT u.id, u.nick, u.passw, u.banned, u.last_seen, - COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified + COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified, premium FROM users u LEFT JOIN facebook f ON f.user_id = u.id LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id LEFT JOIN emails e ON e.user_id = u.id @@ -732,14 +733,14 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { @Transactional(readOnly = true) @Override - public Optional> getVkTokens(final int uid) { - List>> list = getJdbcTemplate().query( - "SELECT vk_id, access_token FROM vk WHERE user_id = ? AND crosspost = 1", - (rs, num) -> Optional.of(Pair.of(rs.getString(1), rs.getString(2))), - uid); - - return list.isEmpty() ? - Optional.empty() : list.get(0); + public List> getVkTokens(List uids) { + return getNamedParameterJdbcTemplate().query( + """ + SELECT vk_id, access_token FROM vk WHERE crosspost = 1 AND access_token <> ''""" + + (uids.isEmpty() ? "" : " AND user_id IN (:uids)"), + new MapSqlParameterSource() + .addValue("uids", uids), + (rs, num) -> Pair.of(rs.getString(1), rs.getString(2))); } @Transactional @@ -753,7 +754,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { public Optional getUserByFacebookId(long facebookId) { List list = getJdbcTemplate().query( "SELECT DISTINCT u.id, u.nick, u.passw, u.banned, u.last_seen, " + - "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " + + "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified, premium " + "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " + "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " + "LEFT JOIN emails e ON e.user_id = u.id WHERE f.fb_id = ?", new UserMapper(), facebookId); @@ -789,6 +790,18 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { return getJdbcTemplate().update("INSERT INTO vk(vk_id,loginhash,access_token,vk_name,vk_link) VALUES (?,?,?,?,?)", vkID, loginhash, token, vkName, vkLink) > 0; } + @Transactional + @Override + public boolean updateVkUser(long vkID, String token, String vkName, String vkLink) { + return getJdbcTemplate().update("UPDATE vk SET access_token=?,vk_name=?,vk_link=? WHERE vk_id=?", + token, vkName, vkLink, vkID) > 0; + } + @Transactional + @Override + public boolean updateVkToken(long userId, String token) { + return getJdbcTemplate().update("UPDATE vk SET access_token=? WHERE user_id=?", + token, userId) > 0; + } @Transactional(readOnly = true) @Override @@ -864,7 +877,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { @Transactional(readOnly = true) @Override public boolean canDeleteTelegramUser(User user) { - return getEmails(user).size() > 0 || getFbCrossPostStatus(user.getUid()).isConnected() || getVkTokens(user.getUid()).isPresent(); + return getEmails(user).size() > 0 || getFbCrossPostStatus(user.getUid()).isConnected() || !getVkTokens(List.of(user.getUid())).isEmpty(); } private static class TokenMapper implements RowMapper { @@ -918,5 +931,11 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { public boolean deleteToken(final String serviceType, final String token) { return getJdbcTemplate().update("DELETE FROM user_services WHERE regid=? AND service_type=?", token, serviceType) > 0; } + + @Transactional + @Override + public void setPremium(final Integer uid, boolean isPremium) { + getJdbcTemplate().update("UPDATE users SET premium=? WHERE id=?", isPremium ? 1 : 0, uid); + } } -- cgit v1.2.3