A platform for community discussion. Free, open, simple.
# frozen_string_literal: true
module DiscourseSolved
# Builds the data for the "Support" section of the redesigned admin dashboard
# (registered via `register_admin_dashboard_section`). All metrics are scoped
# to "support categories" — categories where accepted answers are enabled — for
# the selected period, optionally narrowed to one or more categories.
class AdminDashboardSupport
DEFAULT_RANGE_DAYS = 30
AVAILABILITY_CACHE_KEY = "solved_admin_dashboard_support_available"
DATA_CACHE_DURATION = 30.minutes
# Response-time buckets, in seconds. Open-ended final bucket uses nil.
RESPONSE_TIME_BUCKETS = [
{ key: "lt_1h", max: 1.hour.to_i },
{ key: "1_4h", max: 4.hours.to_i },
{ key: "4_24h", max: 24.hours.to_i },
{ key: "gt_24h", max: nil },
].freeze
def self.available?
return false if !SiteSetting.solved_enabled
return true if SiteSetting.allow_solved_on_all_topics
Discourse
.cache
.fetch(AVAILABILITY_CACHE_KEY, expires_in: 5.minutes) do
DiscourseSolved::Categories::Types::Support.find_matches.exists?
end
end
def self.build(start_date:, end_date:, current_user: nil, category_ids: nil)
new(
start_date: start_date,
end_date: end_date,
current_user: current_user,
category_ids: category_ids,
).build
end
def initialize(start_date:, end_date:, current_user: nil, category_ids: nil)
@start_date = parse_date(start_date) || DEFAULT_RANGE_DAYS.days.ago.beginning_of_day
@end_date = parse_date(end_date)&.end_of_day || Time.zone.now.end_of_day
@current_user = current_user
@requested_category_ids =
Array(category_ids.is_a?(String) ? category_ids.split(",") : category_ids).map(&:to_i)
end
def build
Discourse
.cache
.fetch(cache_key, expires_in: DATA_CACHE_DURATION) do
{
category_options: category_options,
category_ids: selected_category_ids,
kpis: build_kpis,
topic_outcomes: topic_outcomes,
whos_answering: whos_answering,
response_time_distribution: response_time_distribution,
}
end
end
private
attr_reader :start_date, :end_date, :current_user, :requested_category_ids
def parse_date(value)
return nil if value.blank?
Time.zone.parse(value.to_s)&.beginning_of_day
rescue ArgumentError, TypeError
nil
end
# Length-matched window immediately preceding the selected period.
def prev_start_date
start_date - (end_date - start_date)
end
def prev_end_date
start_date
end
def guardian
@guardian ||= current_user&.guardian || Guardian.new
end
def support_categories
scope =
if SiteSetting.allow_solved_on_all_topics
Category.all
else
DiscourseSolved::Categories::Types::Support.find_matches
end
scope.secured(guardian)
end
def all_support_category_ids
@all_support_category_ids ||= support_categories.pluck(:id)
end
def selected_category_ids
requested_category_ids & all_support_category_ids
end
def effective_category_ids
selected_category_ids.presence || all_support_category_ids
end
def category_options
support_categories.order(:name).pluck(:id, :name).map { |id, name| { id: id, name: name } }
end
def cache_key
digest =
Digest::SHA1.hexdigest(
[
all_support_category_ids.sort,
selected_category_ids.sort,
start_date.to_i,
end_date.to_i,
].to_json,
)
"solved_admin_dashboard_support_data:#{digest}"
end
def build_kpis
current = outcomes_for(start_date, end_date)
previous = outcomes_for(prev_start_date, prev_end_date)
{
resolution_rate: {
value: resolution_rate(current),
previous_value: resolution_rate(previous),
report_type: "accepted_solutions",
report_query: resolution_report_query,
},
staff_involvement: {
value: staff_involvement(start_date, end_date),
previous_value: staff_involvement(prev_start_date, prev_end_date),
},
avg_first_reply: {
value: avg_first_reply_seconds(start_date, end_date),
previous_value: avg_first_reply_seconds(prev_start_date, prev_end_date),
},
}
end
def resolution_report_query
query = { start_date: start_date.to_date.iso8601, end_date: end_date.to_date.iso8601 }
if selected_category_ids.present?
query[:filters] = { category_ids: selected_category_ids.join(",") }
end
query
end
# Mutually-exclusive status counts for topics created in the window:
# resolved (has an accepted answer), else in_progress (has at least one
# reply), else unanswered.
def topic_outcomes
outcomes_for(start_date, end_date)
end
def outcomes_for(from, to)
(@outcomes_cache ||= {})[[from, to]] ||= compute_outcomes(from, to)
end
def compute_outcomes(from, to)
return { resolved: 0, in_progress: 0, unanswered: 0 } if effective_category_ids.empty?
row = DB.query(<<~SQL, params_for(from, to)).first
SELECT
COUNT(*) FILTER (WHERE solved) AS resolved,
COUNT(*) FILTER (WHERE NOT solved AND has_reply) AS in_progress,
COUNT(*) FILTER (WHERE NOT solved AND NOT has_reply) AS unanswered
FROM (
SELECT
(st.topic_id IS NOT NULL) AS solved,
EXISTS (
SELECT 1
FROM posts p
WHERE p.topic_id = t.id
AND p.post_number > 1
AND p.post_type = :post_type
AND p.deleted_at IS NULL
) AS has_reply
FROM topics t
LEFT JOIN discourse_solved_solved_topics st ON st.topic_id = t.id
WHERE t.category_id IN (:category_ids)
AND t.archetype = :archetype
AND t.deleted_at IS NULL
AND t.created_at >= :from
AND t.created_at <= :to
) topics_with_state
SQL
{
resolved: row.resolved.to_i,
in_progress: row.in_progress.to_i,
unanswered: row.unanswered.to_i,
}
end
def resolution_rate(outcomes)
total = outcomes.values.sum
return nil if total.zero?
(outcomes[:resolved].to_f / total * 100).round(1)
end
# Percentage of topics in the window whose first reply was authored by staff.
def staff_involvement(from, to)
return nil if effective_category_ids.empty?
total = outcomes_for(from, to).values.sum
return nil if total.zero?
staff_first = DB.query_single(<<~SQL, params_for(from, to)).first.to_i
SELECT COUNT(*)
FROM (
SELECT DISTINCT ON (p.topic_id) p.user_id
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.category_id IN (:category_ids)
AND t.archetype = :archetype
AND t.deleted_at IS NULL
AND t.created_at >= :from
AND t.created_at <= :to
AND p.post_number > 1
AND p.post_type = :post_type
AND p.deleted_at IS NULL
AND p.user_id <> t.user_id
ORDER BY p.topic_id, p.created_at ASC
) first_replies
JOIN users u ON u.id = first_replies.user_id
WHERE u.admin OR u.moderator
SQL
(staff_first.to_f / total * 100).round(1)
end
def first_reply_seconds(from, to)
(@first_reply_cache ||= {})[[from, to]] ||= compute_first_reply_seconds(from, to)
end
def compute_first_reply_seconds(from, to)
return [] if effective_category_ids.empty?
DB.query_single(<<~SQL, params_for(from, to))
SELECT EXTRACT(EPOCH FROM MIN(p.created_at) - t.created_at)::int AS seconds
FROM topics t
JOIN posts p ON p.topic_id = t.id
WHERE t.category_id IN (:category_ids)
AND t.archetype = :archetype
AND t.deleted_at IS NULL
AND t.created_at >= :from
AND t.created_at <= :to
AND p.post_number > 1
AND p.post_type = :post_type
AND p.deleted_at IS NULL
AND p.user_id <> t.user_id
GROUP BY t.id
HAVING EXTRACT(EPOCH FROM MIN(p.created_at) - t.created_at) > 0
SQL
end
def avg_first_reply_seconds(from, to)
seconds = first_reply_seconds(from, to)
return nil if seconds.empty?
(seconds.sum.to_f / seconds.size).round
end
def response_time_distribution
seconds = first_reply_seconds(start_date, end_date)
total = seconds.size
buckets =
RESPONSE_TIME_BUCKETS.map do |bucket|
min = previous_bucket_max(bucket)
count = seconds.count { |s| s >= min && (bucket[:max].nil? || s < bucket[:max]) }
{
key: bucket[:key],
count: count,
share: total.zero? ? 0.0 : (count.to_f / total * 100).round,
}
end
avg_now = avg_first_reply_seconds(start_date, end_date)
avg_prev = avg_first_reply_seconds(prev_start_date, prev_end_date)
trend =
if avg_now && avg_prev
{ direction: time_direction(avg_now, avg_prev), seconds: (avg_now - avg_prev).abs }
else
{ direction: "flat", seconds: 0 }
end
{ buckets: buckets, trend: trend }
end
def previous_bucket_max(bucket)
index = RESPONSE_TIME_BUCKETS.index(bucket)
index.zero? ? 0 : RESPONSE_TIME_BUCKETS[index - 1][:max]
end
def whos_answering
@whos_answering ||= compute_whos_answering
end
# Share of replies in the window by author group (staff, then trust level).
def compute_whos_answering
return { rows: [], total: 0 } if effective_category_ids.empty?
rows = DB.query(<<~SQL, params_for(start_date, end_date))
SELECT
CASE
WHEN u.admin OR u.moderator THEN 'staff'
WHEN u.trust_level >= 4 THEN 'leader'
WHEN u.trust_level = 3 THEN 'regular'
WHEN u.trust_level = 2 THEN 'member'
ELSE 'basic'
END AS bucket,
COUNT(*) AS count
FROM posts p
JOIN topics t ON t.id = p.topic_id
JOIN users u ON u.id = p.user_id
WHERE t.category_id IN (:category_ids)
AND t.archetype = :archetype
AND t.deleted_at IS NULL
AND p.created_at >= :from
AND p.created_at <= :to
AND p.post_number > 1
AND p.post_type = :post_type
AND p.deleted_at IS NULL
AND p.user_id <> t.user_id
AND u.id > 0
GROUP BY bucket
SQL
total = rows.sum(&:count)
{
rows:
rows.map do |row|
{
type: row.bucket,
count: row.count,
share: total.zero? ? 0.0 : (row.count.to_f / total * 100).round,
}
end,
total: total,
}
end
def params_for(from, to)
{
category_ids: effective_category_ids,
archetype: Archetype.default,
post_type: Post.types[:regular],
from: from,
to: to,
}
end
# For durations, lower is better, so "faster"/"slower" rather than up/down.
def time_direction(current, previous)
return "flat" if current.nil? || previous.nil? || current == previous
current < previous ? "faster" : "slower"
end
end
end