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, 102 insertions, 1 deletions

diff --git a/app/controllers/public_body_controller.rb b/app/controllers/public_body_controller.rb
index 374866eda..2c8d83f6f 100644
--- a/app/controllers/public_body_controller.rb
+++ b/app/controllers/public_body_controller.rb
@@ -6,6 +6,7 @@
 # Email: hello@mysociety.org; WWW: http://www.mysociety.org/
 
 require 'fastercsv'
+require 'confidence_intervals'
 
 class PublicBodyController < ApplicationController
     # XXX tidy this up with better error messages, and a more standard infrastructure for the redirect to canonical URL
@@ -149,6 +150,107 @@ class PublicBodyController < ApplicationController
                   :disposition =>'attachment', :encoding => 'utf8')
     end
 
+    def statistics
+        per_graph = 10
+        minimum_requests = 1
+        total_column = 'info_requests_count'
+        graphs =
+            [[total_column,
+              [{
+                   :title => 'Public bodies with the most requests',
+                   :y_axis => 'Number of requests',
+                   :highest => true}]],
+             ['info_requests_successful_count',
+              [{
+                   :title => 'Public bodies with the most successful requests',
+                   :y_axis => 'Percentage of total requests',
+                   :highest => true},
+               {
+                   :title => 'Public bodies with the fewest successful requests',
+                   :y_axis => 'Percentage of total requests',
+                   :highest => false}]],
+             ['info_requests_overdue_count',
+              [{
+                   :title => 'Public bodies with most overdue requests',
+                   :y_axis => 'Percentage of requests that are overdue',
+                   :highest => true}]],
+             ['info_requests_not_held_count',
+              [{
+                   :title => 'Public bodies that most frequently replied with "Not Held"',
+                   :y_axis => 'Percentage of total requests',
+                   :highest => true}]]]
+
+        @graph_list = []
+
+        graphs.each do |column, graphs_properties|
+            graphs_properties.each do |graph_properties|
+                ordering = "y_value"
+                reverse = false
+                percentages = (column != total_column)
+                if graph_properties[:highest]
+                    ordering = "y_value DESC"
+                    reverse = true
+                end
+                y_value_column = total_column
+                if percentages
+                    y_value_column = "(cast(#{column} as float) / #{total_column})"
+                end
+                where_clause = "#{total_column} >= #{minimum_requests}"
+                public_bodies = PublicBody.select("*, #{y_value_column} AS y_value").order(ordering).where(where_clause).limit(per_graph)
+                public_bodies.reverse! if reverse
+
+                x_values = public_bodies.each_with_index.map { |public_body, index| index }
+                y_values = public_bodies.map { |pb| pb.y_value.to_f }
+                cis_below = nil
+                cis_above = nil
+
+                if percentages
+                    original_values = public_bodies.map { |pb| pb.send(column) }
+                    original_totals = public_bodies.map { |pb| pb.send(total_column) }
+                    # Calculate confidence intervals:
+                    cis_below = []
+                    cis_above = []
+                    original_totals.each_with_index.map { |total, i|
+                        lower_ci, higher_ci = ci_bounds original_values[i], total, 0.05
+                        cis_below.push(y_values[i] - lower_ci)
+                        cis_above.push(higher_ci - y_values[i])
+                    }
+                    # Turn the y values and confidence interval into
+                    # percentages:
+                    [y_values, cis_below, cis_above].each { |l|
+                        l.map! { |v| 100 * v }
+                    }
+                end
+
+                y_max = y_values.max
+                if percentages
+                    y_max = 100
+                end
+
+                graph_id = "#{column}-"
+                graph_id += graph_properties[:highest] ? 'highest' : 'lowest'
+
+                @graph_list.push({
+                    'id' => graph_id,
+                    'errorbars' => percentages,
+                    'title' => graph_properties[:title],
+                    'x_values' => x_values,
+                    'y_values' => y_values,
+                    'cis_below' => cis_below,
+                    'cis_above' => cis_above,
+                    'x_axis' => 'Public Bodies',
+                    'x_ticks' => public_bodies.each_with_index.map { |pb, i| [i, pb.name] },
+                    'y_axis' => graph_properties[:y_axis],
+                    'y_max' => y_max})
+            end
+        end
+
+        respond_to do |format|
+            format.html { render :template => "public_body/statistics" }
+            format.json { render :json => @graph_list }
+        end
+    end
+
     # Type ahead search
     def search_typeahead
         # Since acts_as_xapian doesn't support the Partial match flag, we work around it
@@ -158,4 +260,3 @@ class PublicBodyController < ApplicationController
         render :partial => "public_body/search_ahead"
     end
 end
-