Module: Tasks::Projects::YearInReviewHelper

Defined in:
app/helpers/tasks/projects/year_in_review_helper.rb

Overview

Helpers for the Year in Review task dashboard. Provides data gathering methods using raw SQL for performance.

Author:

  • Claude (>50% of code)

Constant Summary collapse

EXCLUDED_TABLE_PATTERNS =

Tables to exclude from the year in review report (indexing/cache tables).

[/cached|pinboard/i].freeze
YEAR_IN_REVIEW_TABLES =

Tables to include in the year in review report. Based on Project#MANIFEST plus User and Project, excluding indexing tables.

(Project::MANIFEST + %w{User Project}).uniq.reject { |t|
  EXCLUDED_TABLE_PATTERNS.any? { |pattern| t.match?(pattern) }
}.freeze
ADMIN_ONLY_TABLES =

Tables only shown in the admin (cross-project) version.

%w{User Project}.freeze

Instance Method Summary collapse

Instance Method Details

#available_years_for_review(project_id) ⇒ Array<Integer>

Returns available years for the dropdown selector.

Parameters:

  • project_id (Integer, nil) —

    if nil, queries across all projects

Returns:

  • (Array<Integer>) —

    years in descending order



73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
 
# File 'app/helpers/tasks/projects/year_in_review_helper.rb', line 73

def available_years_for_review(project_id)
  # Find the earliest created_at across project-scoped tables
  earliest_year = nil

  %w{Otu TaxonName CollectionObject}.each do |table_name|
    klass = table_name.safe_constantize
    next unless klass

    result = if klass.column_names.include?('project_id') && project_id.present?
      klass.where(project_id: project_id).minimum(:created_at)
    else
      klass.minimum(:created_at)
    end

    if result && (earliest_year.nil? || result.year < earliest_year)
      earliest_year = result.year
    end
  end

  earliest_year ||= Time.current.year
  (earliest_year..Time.current.year).to_a.reverse
end

#build_graph1_data(table_data) ⇒ Object (private)

Graph 1: Stacked bar graph with created and updated delta Sorted descending by sum of created + delta



224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
 
# File 'app/helpers/tasks/projects/year_in_review_helper.rb', line 224

def build_graph1_data(table_data)
  sorted = table_data.sort_by { |t| -(t[:created_count] + t[:updated_delta]) }

  {
    labels: sorted.map { |t| t[:display_name] },
    datasets: [
      {
        label: 'Created',
        data: sorted.map { |t| t[:created_count] },
        backgroundColor: sorted.map.with_index { |_, i| graph_color(i, 0.8) }
      },
      {
        label: 'Updated (delta)',
        data: sorted.map { |t| t[:updated_delta] },
        backgroundColor: sorted.map.with_index { |_, i| graph_color(i, 0.5) }
      }
    ]
  }
end

#build_graph2_data(table_data) ⇒ Object (private)

Graph 2: Percentage growth per table Sorted descending by percent_new



246
247
248
249
250
251
252
253
254
255
256
257
258
259
 
# File 'app/helpers/tasks/projects/year_in_review_helper.rb', line 246

def build_graph2_data(table_data)
  sorted = table_data.sort_by { |t| -t[:percent_new] }

  {
    labels: sorted.map { |t| t[:display_name] },
    datasets: [
      {
        label: '% New This Year',
        data: sorted.map { |t| t[:percent_new] },
        backgroundColor: sorted.map.with_index { |_, i| graph_color(i, 0.7) }
      }
    ]
  }
end

#build_graph3_data(table_data) ⇒ Object (private)

Graph 3: Scatter plot of normalized created/updated vs unique users X-axis: normalized created+updated count (0-1) Y-axis: normalized unique users count (0-1)



264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
 
# File 'app/helpers/tasks/projects/year_in_review_helper.rb', line 264

def build_graph3_data(table_data)
  return { datasets: [] } if table_data.empty?

  # Calculate totals for normalization
  max_records = table_data.map { |t| t[:created_count] + t[:updated_delta] }.max.to_f
  max_users = table_data.map { |t| t[:unique_created_by] + t[:unique_updated_by] }.max.to_f

  max_records = 1.0 if max_records == 0
  max_users = 1.0 if max_users == 0

  points = table_data.map.with_index do |t, i|
    record_total = t[:created_count] + t[:updated_delta]
    user_total = t[:unique_created_by] + t[:unique_updated_by]

    {
      x: (record_total / max_records).round(3),
      y: (user_total / max_users).round(3),
      label: t[:display_name]
    }
  end

  {
    datasets: [
      {
        label: 'Tables',
        data: points,
        backgroundColor: table_data.map.with_index { |_, i| graph_color(i, 0.7) },
        pointRadius: 8
      }
    ]
  }
end

#build_graph4_data(table_data) ⇒ Object (private)

Graph 4: Normalized average update delta (time between creation and update) Sorted descending



299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
 
# File 'app/helpers/tasks/projects/year_in_review_helper.rb', line 299

def build_graph4_data(table_data)
  # Filter to tables with non-zero delta
  with_delta = table_data.select { |t| t[:avg_update_delta_days] > 0 }
  return { labels: [], datasets: [] } if with_delta.empty?

  max_delta = with_delta.map { |t| t[:avg_update_delta_days] }.max.to_f
  max_delta = 1.0 if max_delta == 0

  sorted = with_delta.sort_by { |t| -t[:avg_update_delta_days] }

  {
    labels: sorted.map { |t| t[:display_name] },
    datasets: [
      {
        label: 'Avg Days Between Create/Update (normalized)',
        data: sorted.map { |t| (t[:avg_update_delta_days] / max_delta).round(3) },
        backgroundColor: sorted.map.with_index { |_, i| graph_color(i, 0.7) },
        raw_values: sorted.map { |t| t[:avg_update_delta_days] }
      }
    ]
  }
end

#calculate_avg_update_delta(table_name:, start_date:, end_date:, project_clause:) ⇒ Object (private)

Calculate average days between creation and update for records created in the target year



202
203
204
205
206
207
208
209
210
211
212
213
214
 
# File 'app/helpers/tasks/projects/year_in_review_helper.rb', line 202

def calculate_avg_update_delta(table_name:, start_date:, end_date:, project_clause:)
  result = ActiveRecord::Base.connection.execute(<<~SQL)
    SELECT AVG(EXTRACT(EPOCH FROM (updated_at - created_at)) / 86400.0) as avg_days
    FROM #{table_name}
    WHERE created_at >= '#{start_date}'
      AND created_at <= '#{end_date} 23:59:59'
      AND updated_at > created_at
      #{project_clause}
  SQL

  avg = result.first&.fetch('avg_days', nil)
  avg.nil? ? 0.0 : avg.to_f.round(2)
end

#calculate_table_stats(klass:, table_name:, start_date:, end_date:, project_id:) ⇒ Object (private)

Calculate all statistics for a single table

Parameters:

  • project_id (Integer, nil) —

    if nil, queries across all projects



116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
 
# File 'app/helpers/tasks/projects/year_in_review_helper.rb', line 116

def calculate_table_stats(klass:, table_name:, start_date:, end_date:, project_id:)
  is_project_scoped = table_is_project_scoped?(klass)
  has_housekeeping = table_has_housekeeping_users?(klass)

  # Only add project clause if project_id is provided and table has project_id column
  project_clause = (is_project_scoped && project_id.present?) ? "AND project_id = #{project_id.to_i}" : ""

  # Total created in target year
  created_count = execute_count_sql(<<~SQL)
    SELECT COUNT(*)
    FROM #{table_name}
    WHERE created_at >= '#{start_date}'
      AND created_at <= '#{end_date} 23:59:59'
      #{project_clause}
  SQL

  # Total updated in target year
  updated_count = execute_count_sql(<<~SQL)
    SELECT COUNT(*)
    FROM #{table_name}
    WHERE updated_at >= '#{start_date}'
      AND updated_at <= '#{end_date} 23:59:59'
      #{project_clause}
  SQL

  # Delta: updated - created (records updated but not created this year)
  updated_delta = [updated_count - created_count, 0].max

  # All time count (up to and including target year)
  all_time_count = execute_count_sql(<<~SQL)
    SELECT COUNT(*)
    FROM #{table_name}
    WHERE created_at <= '#{end_date} 23:59:59'
      #{project_clause}
  SQL

  # Percentage new vs all time
  percent_new = all_time_count > 0 ? (created_count.to_f / all_time_count * 100).round(2) : 0.0

  # Unique created_by_id and updated_by_id counts
  unique_created_by = 0
  unique_updated_by = 0

  if has_housekeeping
    unique_created_by = execute_count_sql(<<~SQL)
      SELECT COUNT(DISTINCT created_by_id)
      FROM #{table_name}
      WHERE created_at >= '#{start_date}'
        AND created_at <= '#{end_date} 23:59:59'
        #{project_clause}
    SQL

    unique_updated_by = execute_count_sql(<<~SQL)
      SELECT COUNT(DISTINCT updated_by_id)
      FROM #{table_name}
      WHERE updated_at >= '#{start_date}'
        AND updated_at <= '#{end_date} 23:59:59'
        #{project_clause}
    SQL
  end

  # Average time between creation and update (in days)
  avg_update_delta_days = calculate_avg_update_delta(
    table_name: table_name,
    start_date: start_date,
    end_date: end_date,
    project_clause: project_clause
  )

  {
    table_name: table_name,
    display_name: klass.name.underscore.humanize.pluralize,
    model_name: klass.name,
    created_count: created_count,
    updated_count: updated_count,
    updated_delta: updated_delta,
    all_time_count: all_time_count,
    percent_new: percent_new,
    unique_created_by: unique_created_by,
    unique_updated_by: unique_updated_by,
    avg_update_delta_days: avg_update_delta_days,
    is_project_scoped: is_project_scoped
  }
end

#execute_count_sql(sql) ⇒ Object (private)

Execute a count SQL query and return the integer result



217
218
219
220
 
# File 'app/helpers/tasks/projects/year_in_review_helper.rb', line 217

def execute_count_sql(sql)
  result = ActiveRecord::Base.connection.execute(sql)
  result.first['count'].to_i
end

#graph_color(index, alpha = 1.0) ⇒ Object (private)

Generate a color for graph elements based on index Uses HSL color space for visually distinct colors



324
325
326
327
 
# File 'app/helpers/tasks/projects/year_in_review_helper.rb', line 324

def graph_color(index, alpha = 1.0)
  hue = (index * 137.508) % 360  # Golden angle approximation for good distribution
  "hsla(#{hue.round}, 70%, 50%, #{alpha})"
end

#table_has_housekeeping_users?(klass) ⇒ Boolean (private)

Check if a table has housekeeping user columns

Returns:

  • (Boolean) 


110
111
112
 
# File 'app/helpers/tasks/projects/year_in_review_helper.rb', line 110

def table_has_housekeeping_users?(klass)
  klass.column_names.include?('created_by_id') && klass.column_names.include?('updated_by_id')
end

#table_has_timestamps?(table_name) ⇒ Boolean (private)

Check if a table has created_at and updated_at columns

Returns:

  • (Boolean) 


99
100
101
102
 
# File 'app/helpers/tasks/projects/year_in_review_helper.rb', line 99

def table_has_timestamps?(table_name)
  columns = ActiveRecord::Base.connection.columns(table_name).map(&:name)
  columns.include?('created_at') && columns.include?('updated_at')
end

#table_is_project_scoped?(klass) ⇒ Boolean (private)

Check if a table is project-scoped

Returns:

  • (Boolean) 


105
106
107
 
# File 'app/helpers/tasks/projects/year_in_review_helper.rb', line 105

def table_is_project_scoped?(klass)
  klass.column_names.include?('project_id')
end

#year_in_review_data(year, project_id) ⇒ Hash

Returns all data for the year in review dashboard.

Parameters:

  • year (Integer) —

    the calendar year to report on

  • project_id (Integer) —

    the project to scope data to (nil for non-project tables)

Returns:

  • (Hash) —

    structured data for all graphs



24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
 
# File 'app/helpers/tasks/projects/year_in_review_helper.rb', line 24

def year_in_review_data(year, project_id)
  start_date = Date.new(year, 1, 1)
  end_date = Date.new(year, 12, 31)

  table_data = []

  YEAR_IN_REVIEW_TABLES.each do |table_name|
    # Skip admin-only tables when scoped to a project
    next if project_id.present? && ADMIN_ONLY_TABLES.include?(table_name)

    klass = table_name.safe_constantize
    next unless klass
    next unless klass.respond_to?(:table_name)

    actual_table_name = klass.table_name
    next unless table_has_timestamps?(actual_table_name)

    data = calculate_table_stats(
      klass: klass,
      table_name: actual_table_name,
      start_date: start_date,
      end_date: end_date,
      project_id: project_id
    )

    # Exclude tables with zero created and zero updated delta
    next if data[:created_count] == 0 && data[:updated_delta] == 0

    table_data << data
  end

  {
    metadata: {
      year: year,
      project_id: project_id,
      generated_at: Time.current
    },
    tables: table_data,
    graph1: build_graph1_data(table_data),
    graph2: build_graph2_data(table_data),
    graph3: build_graph3_data(table_data),
    graph4: build_graph4_data(table_data)
  }
end