おこづかい帳アプリを作る(5)

今日やること

統計表示ページを作る

今日勉強すること

ActiveRecrodで集計関数を使う
ActiveRecrodでSQLを実行する

Controllerの追加

統計表示の処理は既存のEntriesControllerに追加すべきなのか、新しいControllerを用意すべきなのかよくわからないのですが、とりあえずControllerを追加します。Statsという名前にしておきます。

script/generate controller Stats index month category

month:年ごとの月別合計
category:年ごとの分類別合計
monthcategory:年ごとの分類別月別合計

です。

集計関数を使うためには、ActiveRecord::Calculationsを使います。今回はsumのみですが。

月別合計と分類別合計

StatsController#monthとcategoryは以下のようにしました。

  def month
    @year = get_year
    @results = Entry.sum(:amount, :group => "strftime('%Y/%m', occurred_at)",
              :conditions => ["occurred_at >= ? AND occurred_at < ?", Date.new(@year), Date.new(@year + 1)])
  end

  def category
    @year = get_year
    @results = Entry.sum(:amount, :group => :category_id,
              :conditions => ["occurred_at >= ? AND occurred_at < ?", Date.new(@year), Date.new(@year + 1)])
  end

  private
  
  def get_year
    # 年が未指定の場合は今年のデータ
    year = params[:id].to_i
    year ||= Date.today.year
  end

:groupでGROUP BY句を指定しますが、そのままSQLとして使われる様なのでSQLiteの日付関数を使っています。これだと、以下のようなSQLが実行されます。

SELECT 
  sum("entries".amount) AS sum_amount, strftime('%Y/%m', occurred_at) AS strftime_y_m_occurred_at 
FROM 
  "entries" 
WHERE 
  (occurred_at >= '2008-01-01' AND occurred_at < '2009-01-01') 
GROUP BY strftime('%Y/%m', occurred_at)

結果として、["2008/08",2000]という形の配列の配列が得られます。これをテンプレートで以下の様に表示しています。

<!-- app/views/stats/month.html.erb -->
<h1><%= h("#{@year}年 月別合計") %></h1>
<table>
  <tr>
    <th>month</th>
    <th>amount</th>
  </tr>
<% 1.upto(12) do |month| %>
  <tr>
    <td><%=h("#{month}月") %></td>
    <td><%= format_result(month) %></td>
  </tr>
<% end %>
</table>

データが存在しない月も出力するように1から12までループさせています。

<!-- app/views/stats/category.html.erb -->
<h1><%= h("#{@year}年 分類別合計")%></h1>
<table>
  <tr>
    <th>category</th>
    <th>amount</th>
  </tr>
<% for cat in Category.find(:all, :order => "position ASC") %>
  <tr>
    <td><%=h(cat.name) %></td>
    <td><%= format_result_by_category(cat.id) %></td>
  </tr>
<% end %>
</table>
月別と同様に、データの有無にかかわらずすべての分類について結果を表示します。

ヘルパーメソッドです。

  def format_result_by_month(month)
    format_result(Date.new(@year, month).strftime("%Y/%m"))
  end
  
  def format_result_by_category(category_id)
    format_result(category_id)
  end  
  
  def format_result(key)
    result = @results.assoc(key)    
    format_amount result ? result[1] : 0
  end

分類別月別合計

クロス集計的な表示をしてみます。以下、Controllerです。

  def monthcategory
    sql = <<-SQL
    SELECT
      category_id
      , SUM(case strftime('%m', occurred_at) when "01" then amount else 0 end) as m1
      , SUM(case strftime('%m', occurred_at) when "02" then amount else 0 end) as m2
      , SUM(case strftime('%m', occurred_at) when "03" then amount else 0 end) as m3
      , SUM(case strftime('%m', occurred_at) when "04" then amount else 0 end) as m4
      , SUM(case strftime('%m', occurred_at) when "05" then amount else 0 end) as m5
      , SUM(case strftime('%m', occurred_at) when "06" then amount else 0 end) as m6
      , SUM(case strftime('%m', occurred_at) when "07" then amount else 0 end) as m7
      , SUM(case strftime('%m', occurred_at) when "08" then amount else 0 end) as m8
      , SUM(case strftime('%m', occurred_at) when "09" then amount else 0 end) as m9
      , SUM(case strftime('%m', occurred_at) when "10" then amount else 0 end) as m10
      , SUM(case strftime('%m', occurred_at) when "11" then amount else 0 end) as m11
      , SUM(case strftime('%m', occurred_at) when "12" then amount else 0 end) as m12     
    FROM
      entries
    WHERE
      occurred_at >= ? AND occurred_at < ?
    GROUP BY
      category_id    
    SQL
    @year = get_year
    @results = Entry.find_by_sql([sql, Date.new(@year), Date.new(@year + 1)])    
  end

ここでは、ActiveRecordのfind_by_sqlを使って、ヒアドキュメントのところで記述したSQLを実行します。結果は、Entryの配列ですが、SELECTで指定したEntryクラスには存在しない月別の合計値のフィールド(m1,m2,...,m12)が動的に追加されます。これをヘルパーメソッドで呼び出しています。

<!-- app/views/stats/monthcategory.html.erb -->
<h1><%= h("#{@year}年 分類別月別合計")%></h1>
<table>
  <tr>
    <th>category</th>
	<% 1.upto(12) do |i|%>
    <th><%= h("#{i}月")%></th>
	<% end %>
  </tr>
<% for cat in Category.find(:all, :order => "position ASC") %>
  <tr>
    <td><%=h(cat.name) %></td>
   	<%= write_month_category(cat) %>
  </tr>
<% end %>
</table>

  def write_month_category(cat)
    for r in @results
      # この辺はもっとすっきり書けるはず...
      if r.category_id == cat.id
        result = r
        break
      end
    end
    html = ''
    1.upto(12) do |m|
      html << "<td>"
      # ここで月別合計値を取得
      html << (result ? result.send("m#{m}") : "0")
      html << "</td>\n"
    end
    return html
  end

こんな感じで表示されます。

なんとなく、アプリケーションらしくなってきました。勉強用なので表示や操作性は気にせずやってきたのですが、そろそろなんとかしたいところです。あとは、グラフを表示してみたり、ログイン画面をつけたりとか。