Building Reports Accessible from the Internet

Although a lot of web reporting is textual, as in the previous example, it's also possible to do graphical reporting with Rails. To demonstrate, let's re-create the team performance report presented at the end of Chapter 4. However, instead of using Gruff to create reports, you will use a Flash charting application. The advantage of using a Flash solution is that it allows for interactivity. For example, you can create tool tips that report exact values when users move their mouse over an area of the graph.

First, let's create a Rails project for the application:

rails team_performance_web

--------------------------------------------  create
  create  app/controllers
  create  app/helpers
  create  app/models
  create  app/views/layouts
  create  config/environments
  create  components
  create  db
  create  doc
  create  lib
  create  lib/tasks
  create  log
  create  public/images
  create  public/javascripts
  ...
--------------------------------------------

For this example, you'll use a project called Open Flash Chart. It's open source, unlike many other Flash charting components, so you can use it on any size project without paying licensing fees. Obtain Open Flash Chart from http://teethgrinder.co.uk/open-flash-chart/. Unzip it into a temporary directory. Next, copy the open-flash-chart.swf file from the root of the ZIP file into a new directory into your Rails application: public/flash.

You'll also use the Flash Object plug-in, which helps you include Flash objects in your views. Install this plug-in using the following command:

ruby script/plugin install http://lipsiasoft.googlecode.com/svn/trunk/ flashobject_helper/

Now you can start generating your code .

Creating the Controller for the Graphical Report

Begin by generating a single controller, home, using the following command:

ruby script/generate controller home

--------------------------------------------
  exists  app/controllers/
  exists  app/helpers/
  create  app/views/home
  exists  test/functional/
  create  app/controllers/home_controller.rb
  create  test/functional/ home_controller_test.rb
  create  app/helpers/home_helper.rb
--------------------------------------------

For this example, you will use the players_4 database from Chapter 3, so you will need to edit your config/database.yml file to look something like Listing 5-9.

Listing 5-9. Database Configuration File for the Graphical Report (config/database.yml)

development:
  adapter: mysql
  database: players_4
  username: your_mysql_username_here
  password: your_mysql_password_here
  host: localhost

As in the previous example, you're creating only a development environment at this point, so you can safely ignore the other two database connection settings for testing and production.

Place the code shown in Listing 5-10 in config/routes.rb.

Listing 5-10. Application Routing Code (config/routes.rb)

ActionController::Routing::Routes.draw do |map|
  map.connect 'performance/:game_id/:player_id', 
              :controller=>'performance',
              :action=>'show'

map.connect 'performance/:game_id/:player_id.:format',
              :controller=>'performance',
              :action=>'show'

map.connect "/", :controller=>'home'

map.connect ':controller/:action/:id' map.connect ':controller/:action/:id.:format'
end

Remove the public/index.html file, as it overrides your routing for /. The code shown in Listing 5-11 goes in app/controllers/home_controller.rb.

Listing 5-11. Home Controller for the Web Report (app/controllers/home_controller.rb)

class HomeController < ApplicationController
  def index
    @available_players =Player.find(:all)
    @available_games = Game.find(:all)
  end
end

The code shown in Listing 5-12 goes in app/controllers/performance_controller.rb .

Listing 5-12. Web Performance Data Controller (app/controllers/performance_controller.rb)

class PerformanceController < ApplicationController
  def show
    @player = Player.find_by_id(params[:player_id])
    @game = Game.find_by_id(params[:game_id])

    @events = Event.find(:all,
                   :select=>'event, ' <<
                            'AVG(time)/1000 as average_time',
                   :group=>'events.event DESC',
                   :joins=>' INNER JOIN plays ON events.play_id=plays.id',
                   :conditions=>["plays.game_id = ? AND plays.player_id= ?",
                             @game.id, @player.id]
                       ).map { |event| 
                        {:event=>event.event,
                    :average_time=>event.average_time.to_i}
                       }

    respond_to do |format|
      format.html { render :layout=>false if request.xhr? }
      format.text { render :layout=>false }
      format.xml  { render :xml=>{'player'=>@player,
                           'game'=>@game,
                           'events'=>@events
                          }.to_xml(:root=>'player_performance_report',
                           :skip_types=>true) }
    end

  end
end

Now you can create the models for the report.