gistfile1.rb

class Outlay < ActiveRecord::Base

  def self.budget(scopes = {}, pivot_value = nil)
    pivot_value ||= "top_10_competitors"

    projection_value = {}.tap do |projection|
      pvs = Outlay.pivot_values(pivot_value, scopes)
      pvs.each do |pivot|
        projection[pivot] = {}
        pivot_scopes = ({(pivot_value=="top_10_competitors" ? "contractor" : pivot_value) => pivot}.merge(scopes))

        projection[pivot] = Outlay.where(pivot_scopes).sum(:outlay_amount)
      end

      projection['All others'] = Outlay.where("contractor NOT IN ?", pvs)
    end
  end

  def self.pivot_values(key, scopes)
    v = case(key)
    when "top_10_competitors"
      scopes["contractor"] ? (scopes["contractor"] & self.contractor) : self.contractor
    when "all_competitors"
      raise "Not yet implemented"     
    when "custom_competitors"
      raise "Not yet implemented"     
    else
      raise "Unknown value to pivot on (#{key.inspect})"
    end

    scopes.delete(key)
    v
  end

  def self.contractor
    @@contractors ||= distinct(:contractor)
  end

  private

  def self.distinct(column_name)  
    connection.select_values("SELECT DISTINCT(#{column_name}) FROM outlays")
  end

end