Add a page with experimental statistics on public bodies

The statistics on the status of the requests to a particular
public body are too slow to calculate on-the-fly, so this
commit adds:

 * Extra columns on public_bodies to store counts of
   the successful, not held, and overdue request counts
   for each public body.

 * A rake task which should be run periodically to update
   the overdue request count column.

If Javascript is not available, the summary statistics are
shown as tables.  If Javascript is available, graphs are
drawn with Flot.

1 files changed, 26 insertions, 0 deletions

diff --git a/db/migrate/20130816150110_add_statistics_to_public_body.rb b/db/migrate/20130816150110_add_statistics_to_public_body.rb
new file mode 100644
index 000000000..fb3a67e83
--- /dev/null
+++ b/db/migrate/20130816150110_add_statistics_to_public_body.rb
@@ -0,0 +1,26 @@
+class AddStatisticsToPublicBody < ActiveRecord::Migration
+  def self.up
+    add_column :public_bodies, :info_requests_successful_count, :integer
+    add_column :public_bodies, :info_requests_not_held_count, :integer
+    add_column :public_bodies, :info_requests_overdue_count, :integer
+    # We need to set the :info_requests_successful_count and
+    # :info_requests_not_held_count columns, since they will
+    # subsequently will be updated in after_save /
+    # after_destroy. :info_requests_overdue_count, however will be set
+    # from a periodically run rake task.
+    PublicBody.connection.execute("UPDATE public_bodies
+                                     SET info_requests_not_held_count = (SELECT COUNT(*) FROM info_requests
+                                                                         WHERE described_state = 'not_held' AND
+                                                                               public_body_id = public_bodies.id);")
+    PublicBody.connection.execute("UPDATE public_bodies
+                                     SET info_requests_successful_count = (SELECT COUNT(*) FROM info_requests
+                                                                           WHERE described_state IN ('successful', 'partially_successful') AND
+                                                                                 public_body_id = public_bodies.id);")
+  end
+
+  def self.down
+    remove_column :public_bodies, :info_requests_successful_count
+    remove_column :public_bodies, :info_requests_not_held_count
+    remove_column :public_bodies, :info_requests_overdue_count
+  end
+end