From 3d7f60c1c0b48dad2f58bffe3352d1c5c1b93f00 Mon Sep 17 00:00:00 2001
From: Vitaly Takmazov
Date: Thu, 1 Dec 2016 13:56:53 +0300
Subject: juick-api: Tags controller from perl, refactoring

---
 .../java/com/juick/service/TagServiceImpl.java     | 68 ++++++++++++++++++----
 1 file changed, 56 insertions(+), 12 deletions(-)

(limited to 'juick-server/src/main/java/com/juick/service/TagServiceImpl.java')

diff --git a/juick-server/src/main/java/com/juick/service/TagServiceImpl.java b/juick-server/src/main/java/com/juick/service/TagServiceImpl.java
index 8f420ba9..23b8d2a9 100644
--- a/juick-server/src/main/java/com/juick/service/TagServiceImpl.java
+++ b/juick-server/src/main/java/com/juick/service/TagServiceImpl.java
@@ -1,11 +1,13 @@
 package com.juick.service;
 
 import com.juick.Tag;
+import com.juick.server.helpers.TagStats;
 import org.apache.commons.collections4.CollectionUtils;
 import org.apache.commons.lang3.ArrayUtils;
 import org.apache.commons.lang3.StringEscapeUtils;
 import org.apache.commons.lang3.StringUtils;
 import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.core.RowMapper;
 import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
 import org.springframework.jdbc.support.GeneratedKeyHolder;
 import org.springframework.jdbc.support.KeyHolder;
@@ -14,9 +16,7 @@ import org.springframework.transaction.annotation.Transactional;
 import org.springframework.util.Assert;
 
 import javax.inject.Inject;
-import java.sql.PreparedStatement;
-import java.sql.Statement;
-import java.sql.Types;
+import java.sql.*;
 import java.util.ArrayList;
 import java.util.Collection;
 import java.util.Collections;
@@ -132,19 +132,27 @@ public class TagServiceImpl extends BaseJdbcService implements TagService {
         return holder.getKey().intValue();
     }
 
+    private class TagStatsMapper implements RowMapper<TagStats> {
+
+        @Override
+        public TagStats mapRow(ResultSet rs, int rowNum) throws SQLException {
+            Tag t = new Tag(StringEscapeUtils.unescapeHtml4(rs.getString(1)));
+            TagStats s = new TagStats();
+            s.setTag(t);
+            s.setUsageCount(rs.getInt(2));
+            return s;
+        }
+    }
+
     @Transactional(readOnly = true)
     @Override
-    public List<com.juick.Tag> getUserTagsAll(final int uid) {
+    public List<TagStats> getUserTagStats(final int uid) {
         return getJdbcTemplate().query(
                 "SELECT tags.name,COUNT(messages.message_id) " +
                         "FROM (messages INNER JOIN messages_tags ON (messages.user_id=? " +
                         "AND messages.message_id=messages_tags.message_id)) " +
                         "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id ORDER BY tags.name ASC",
-                (rs, rowNum) -> {
-                    Tag t = new Tag(StringEscapeUtils.unescapeHtml4(rs.getString(1)));
-                    t.UsageCnt = rs.getInt(2);
-                    return t;
-                },
+                new TagStatsMapper(),
                 uid);
     }
 
@@ -167,13 +175,24 @@ public class TagServiceImpl extends BaseJdbcService implements TagService {
                 .collect(Collectors.toList());
     }
 
+    @Override
+    public List<TagStats> getTagStats() {
+        return getJdbcTemplate().query(
+                "SELECT tags.name,COUNT(DISTINCT messages.user_id) AS cnt " +
+                        "FROM (messages INNER JOIN messages_tags ON (messages.ts>TIMESTAMPADD(DAY,-3,NOW()) " +
+                        "AND messages.message_id=messages_tags.message_id)) " +
+                        "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id " +
+                        "WHERE tags.tag_id NOT IN (SELECT tag_id FROM tags_ignore) " +
+                        "GROUP BY tags.tag_id ORDER BY cnt DESC LIMIT 20", new TagStatsMapper());
+    }
+
     @Transactional
     @Override
     public List<Tag> updateTags(final int mid, final Collection<Tag> newTags) {
-        List<Tag> currentTags = messagesService.getMessageTags(mid);
+        List<TagStats> currentTags = getMessageTags(mid);
 
         if (CollectionUtils.isEmpty(newTags))
-            return currentTags;
+            return currentTags.stream().map(TagStats::getTag).collect(Collectors.toList());
 
         List<Integer> ids = new ArrayList<>(newTags.size());
         List<Object[]> params = new ArrayList<>(newTags.size());
@@ -198,7 +217,7 @@ public class TagServiceImpl extends BaseJdbcService implements TagService {
                             .addValue("mid", mid)
                             .addValue("ids", ids));
 
-        return messagesService.getMessageTags(mid);
+        return getMessageTags(mid).stream().map(TagStats::getTag).collect(Collectors.toList());
     }
 
     @Override
@@ -219,4 +238,29 @@ public class TagServiceImpl extends BaseJdbcService implements TagService {
         }
         return Collections.emptyList();
     }
+
+    @Transactional(readOnly = true)
+    @Override
+    public List<TagStats> getMessageTags(final int mid) {
+        return getJdbcTemplate().query(
+                "SELECT tags.tag_id,synonym_id,name,stat_messages FROM tags " +
+                        "INNER JOIN messages_tags ON (messages_tags.message_id = ? AND messages_tags.tag_id = tags.tag_id)",
+                (rs, num) -> {
+                    com.juick.Tag t = new com.juick.Tag(StringEscapeUtils.unescapeHtml4(rs.getString(3)));
+                    t.TID = rs.getInt(1);
+                    t.SynonymID = rs.getInt(2);
+                    TagStats s = new TagStats();
+                    s.setTag(t);
+                    s.setUsageCount(rs.getInt(4));
+                    return s;
+                }, mid);
+    }
+
+    @Transactional(readOnly = true)
+    @Override
+    public List<Integer> getMessageTagsIDs(final int mid) {
+        return getJdbcTemplate().queryForList(
+                "SELECT tag_id FROM messages_tags WHERE message_id = ?",
+                Integer.class, mid);
+    }
 }
-- 
cgit v1.2.3