Add a info_requests_visible_classified_count column to PublicBody

This counts only those info requests that have prominence 'normal'
(i.e. are not hidden) and are not 'awaiting_description' (i.e. that
they have had some basic status classification).

1 files changed, 15 insertions, 0 deletions

diff --git a/db/migrate/20131101155844_add_stats_denominator.rb b/db/migrate/20131101155844_add_stats_denominator.rb
new file mode 100644
index 000000000..7df4c8200
--- /dev/null
+++ b/db/migrate/20131101155844_add_stats_denominator.rb
@@ -0,0 +1,15 @@
+class AddStatsDenominator < ActiveRecord::Migration
+  def up
+    add_column :public_bodies, :info_requests_visible_classified_count, :integer
+    PublicBody.connection.execute("UPDATE public_bodies
+                                     SET info_requests_visible_classified_count =
+                                       (SELECT COUNT(*) FROM info_requests
+                                          WHERE awaiting_description = FALSE AND
+                                                prominence = 'normal' AND
+                                                public_body_id = public_bodies.id);")
+  end
+
+  def down
+    remove_column :public_bodies, :info_requests_visible_classified_count
+  end
+end