仪表盘报告 - 管理员活动

[SaraDev (Sara Devlaeminck)]

你好 @T_Disco,

@T_Disco

是否可以将“已批准帖子”和“已拒绝帖子”列添加到版主活动报告中？

是的，要将“已批准帖子”和“已拒绝帖子”的结果添加到版主活动报告中，我们可以利用 reviewables 和 reviewable_scores 表。具体来说，reviewable_scores 中的 status 字段可用于确定帖子是被批准（status = 1）还是被拒绝（status = 2）。

添加到报告中的效果如下：

包含已批准帖子和已拒绝帖子的版主活动

-- [params]
-- date :start_date = 2023-12-01
-- date :end_date = 2024-12-01
-- boolean :include_admins = false

WITH mods AS (
    SELECT
        id AS user_id,
        username_lower AS username,
        uploaded_avatar_id
    FROM users u
    WHERE ((u.moderator = true) OR (:include_admins AND u.admin = true))
    AND u.id > 0
),

time_read AS (
    SELECT SUM(uv.time_read) AS time_read,
        uv.user_id
    FROM mods m
    JOIN user_visits uv ON m.user_id = uv.user_id
    WHERE uv.visited_at >= :start_date
        AND uv.visited_at <= :end_date
    GROUP BY uv.user_id
),
      
flag_count AS (
    WITH period_actions AS (
        SELECT agreed_by_id,
        disagreed_by_id
        FROM post_actions
        WHERE post_action_type_id IN (3,4,8,6,7)
        AND created_at >= :start_date
        AND created_at <= :end_date
    ),

agreed_flags AS (
        SELECT pa.agreed_by_id AS user_id,
        COUNT(*) AS flag_count
        FROM mods m
        JOIN period_actions pa
        ON pa.agreed_by_id = m.user_id
        GROUP BY agreed_by_id
    ),

disagreed_flags AS (
        SELECT pa.disagreed_by_id AS user_id,
        COUNT(*) AS flag_count
        FROM mods m
        JOIN period_actions pa
        ON pa.disagreed_by_id = m.user_id
        GROUP BY disagreed_by_id
    )
    SELECT
    COALESCE(af.user_id, df.user_id) AS user_id,
    COALESCE(af.flag_count, 0) + COALESCE(df.flag_count, 0) AS flag_count
    FROM agreed_flags af
    FULL OUTER JOIN disagreed_flags df
    ON df.user_id = af.user_id
      ),
      
revision_count AS (
      SELECT pr.user_id,
      COUNT(*) AS revision_count
      FROM mods m
      JOIN post_revisions pr
      ON pr.user_id = m.user_id
      JOIN posts p
      ON p.id = pr.post_id
      WHERE pr.created_at >= :start_date
      AND pr.created_at <= :end_date
      AND p.user_id <> pr.user_id
      GROUP BY pr.user_id
),
      
topic_count AS (
      SELECT t.user_id,
        COUNT(*) AS topic_count
      FROM mods m
      JOIN topics t ON t.user_id = m.user_id
      WHERE t.archetype = 'regular'
          AND t.created_at >= :start_date
          AND t.created_at <= :end_date
      GROUP BY t.user_id
),
      
post_count AS (
      SELECT p.user_id,
         COUNT(*) AS post_count
      FROM mods m
      JOIN posts p ON p.user_id = m.user_id
      JOIN topics t ON t.id = p.topic_id
      WHERE t.archetype = 'regular'
          AND p.created_at >= :start_date
          AND p.created_at <= :end_date
      GROUP BY p.user_id
),
      
pm_count AS (
      SELECT p.user_id,
        COUNT(*) AS pm_count
      FROM mods m
      JOIN posts p ON p.user_id = m.user_id
      JOIN topics t ON t.id = p.topic_id
      WHERE t.archetype = 'private_message'
          AND p.created_at >= :start_date
          AND p.created_at <= :end_date
      GROUP BY p.user_id
),

reviewable_actions AS (
    SELECT
        rs.reviewed_by_id AS user_id,
        SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END) AS posts_approved,
        SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END) AS posts_rejected
    FROM mods m
    JOIN reviewable_scores rs ON rs.reviewed_by_id = m.user_id
    JOIN reviewables r ON r.id = rs.reviewable_id
    WHERE rs.reviewed_at >= :start_date
      AND rs.reviewed_at <= :end_date
    GROUP BY rs.reviewed_by_id
)

SELECT
    m.user_id,
    m.username,
    fc.flag_count as flags_reviewed,
    ROUND(((tr.time_read) / 3600.00),2) as time_reading_hours,
    tc.topic_count as topics_created,
    pmc.pm_count as PMs_created,
    pc.post_count as posts_created,
    rc.revision_count as revisions,
    ra.posts_approved,
    ra.posts_rejected
FROM mods m
LEFT JOIN time_read tr ON tr.user_id = m.user_id
LEFT JOIN flag_count fc ON fc.user_id = m.user_id
LEFT JOIN revision_count rc ON rc.user_id = m.user_id
LEFT JOIN topic_count tc ON tc.user_id = m.user_id
LEFT JOIN post_count pc ON pc.user_id = m.user_id
LEFT JOIN pm_count pmc ON pmc.user_id = m.user_id
LEFT JOIN reviewable_actions ra ON ra.user_id = m.user_id
ORDER BY m.username ASC

此报告的结果将如下所示：

user	username	flags_reviewed	time_reading_hours	topics_created	pms_created	posts_created	revisions	posts_approved	posts_rejected
1	moderator1	NULL	36.11	NULL	344	8	15	10	5
2	moderator2	46	104.52	2	271	466	363	7	3
3	moderator3	NULL	72.15	NULL	418	64	16	NULL	NULL