housekeeper / has_activity

A simple way to grab recent activity or a columns sum on a given model from a table grouped by day, hour, or week with only 1 SQL query and giving the ability to pad the results for days/weeks/hours with no activity.

HasActivity v0.4.6

• Originally created by Cary Dunn
• Modified functionality and additional features by HouseKeeper
  • Added the ability to sum a column – giving you a total ammount from a date until now or between 2 dates
  • Added Between functionality so you can get activity or sums between 2 specified dates
  • Created a gem version of this plugin

A simple way to grab recent activity or a sum of values on a given model from a table grouped by day, hour,
or week with only 1 SQL query and giving the ability to pad the results for days/weeks/hours with no activity.

The plugin offers the ability to…

• Present as a Google Chart (line or bar)
• Scope your model to given :conditions
• Pad your results for days with no activity
• Present your data from a set date until present or between two dates

NOTE: MySQL only because of date functions use.

Example Google Chart Generated&

INSTALLATION

As a standard plugin

Run the following command in your projects root

ruby script/plugin install git://github.com/housekeeper/has_activity.git

As a RubyGem

Step one: Download the gem

Run the following commands

  gem sources -a http://gems.github.com (you only have to do this once)
  sudo gem install housekeeper-has_activity

Step two: configure rails

Add the following line inside Rails::Initializer.run do

  config.gem 'housekeeper-has_activity', :lib => 'has_activity', 
                                         :source => 'http://gems.github.com'

USAGE

  class Feed < ActiveRecord::Base
    # Defaults to created_at
    has_activity
    
    ...
  end

  class Feed < ActiveRecord::Base
    # Change the timestamp DB field to something custom
    has_activity :by => "published_at"
    
    ...
  end

  # Get all activity since 1 week ago grouped by day (default) 
  Feed.activity_since(1.week.ago)
  => [{:activity=>0, :offset=>7, :created_at=>Mon Mar 09 10:27:34 -0700 2009}, {:activity=>0, :offset=>6, :created_at=>Tue Mar 10 10:27:34 -0700 2009}, {:activity=>8, :offset=>5, :created_at=>Wed Mar 11 01:19:09 -0700 2009}, {:activity=>2, :offset=>4, :created_at=>Thu Mar 12 04:26:09 -0700 2009}, {:activity=>0, :offset=>3, :created_at=>Fri Mar 13 10:27:34 -0700 2009}, {:activity=>0, :offset=>2, :created_at=>Sat Mar 14 10:27:34 -0700 2009}, {:activity=>0, :offset=>1, :created_at=>Sun Mar 15 10:27:34 -0700 2009}, {:activity=>0, :offset=>0, :created_at=>Mon Mar 16 10:27:34 -0700 2009}]

SQL generated for previous statement…

SELECT
 published_at AS timestamp,
 COUNT(*) AS activity_count,
 DATEDIFF(now(), published_at) as days_ago
 FROM feeds
 WHERE 1=1 AND published_at > '2009-03-09 17:27:33'
 GROUP BY days_ago
 ORDER BY published_at ASC

  # Get all activity since 1 day ago grouped by hour scoped to a certain user
  Feed.activity_since(1.week.ago, :conditions => ["user_id = ?", current_user.id], :group_by => :hour)
  => [{:activity=>0, :offset=>24, :created_at=>Sun Mar 15 10:29:47 -0700 2009}, {:activity=>0, :offset=>23, :created_at=>Sun Mar 15 11:29:47 -0700 2009}, {:activity=>0, :offset=>22, :created_at=>Sun Mar 15 12:29:47 -0700 2009}, {:activity=>0, :offset=>21, :created_at=>Sun Mar 15 13:29:47 -0700 2009}, {:activity=>0, :offset=>20, :created_at=>Sun Mar 15 14:29:47 -0700 2009}, {:activity=>0, :offset=>19, :created_at=>Sun Mar 15 15:29:47 -0700 2009}, {:activity=>0, :offset=>18, :created_at=>Sun Mar 15 16:29:47 -0700 2009}, {:activity=>0, :offset=>17, :created_at=>Sun Mar 15 17:29:47 -0700 2009}, {:activity=>0, :offset=>16, :created_at=>Sun Mar 15 18:29:47 -0700 2009}, {:activity=>0, :offset=>15, :created_at=>Sun Mar 15 19:29:47 -0700 2009}, {:activity=>0, :offset=>14, :created_at=>Sun Mar 15 20:29:47 -0700 2009}, {:activity=>0, :offset=>13, :created_at=>Sun Mar 15 21:29:47 -0700 2009}, {:activity=>0, :offset=>12, :created_at=>Sun Mar 15 22:29:47 -0700 2009}, {:activity=>0, :offset=>11, :created_at=>Sun Mar 15 23:29:47 -0700 2009}, {:activity=>0, :offset=>10, :created_at=>Mon Mar 16 00:29:47 -0700 2009}, {:activity=>0, :offset=>9, :created_at=>Mon Mar 16 01:29:47 -0700 2009}, {:activity=>0, :offset=>8, :created_at=>Mon Mar 16 02:29:47 -0700 2009}, {:activity=>0, :offset=>7, :created_at=>Mon Mar 16 03:29:47 -0700 2009}, {:activity=>0, :offset=>6, :created_at=>Mon Mar 16 04:29:47 -0700 2009}, {:activity=>0, :offset=>5, :created_at=>Mon Mar 16 05:29:47 -0700 2009}, {:activity=>0, :offset=>4, :created_at=>Mon Mar 16 06:29:47 -0700 2009}, {:activity=>0, :offset=>3, :created_at=>Mon Mar 16 07:29:47 -0700 2009}, {:activity=>0, :offset=>2, :created_at=>Mon Mar 16 08:29:47 -0700 2009}, {:activity=>0, :offset=>1, :created_at=>Mon Mar 16 09:29:47 -0700 2009}, {:activity=>0, :offset=>0, :created_at=>Mon Mar 16 10:29:47 -0700 2009}]\

SQL generated for previous statement…

SELECT
 published_at AS timestamp,
 COUNT(*) AS activity_count,
 ((((YEAR(now()) - YEAR(published_at))*365)+(DAYOFYEAR(now())-DAYOFYEAR(published_at)))*24)+(HOUR(now())-HOUR(published_at)) as hours_ago,
 CONCAT(YEAR(published_at), CONCAT(DAYOFYEAR(published_at), HOUR(published_at))) AS unique_hour
 FROM feeds
 WHERE user_id = 1 AND published_at > '2009-03-15 17:29:47'
 GROUP BY unique_hour
 ORDER BY published_at ASC

  # Get all activity since 2 months ago grouped by week and order it from current week to oldest week
  Feed.activity_since(2.months.ago, :group_by => :week, :order => :desc)
  => [{:activity=>0, :offset=>0, :created_at=>Mon Mar 16 10:34:28 -0700 2009}, {:activity=>0, :offset=>1, :created_at=>Mon Mar 09 10:34:28 -0700 2009}, {:activity=>0, :offset=>2, :created_at=>Mon Mar 02 10:34:28 -0800 2009}, {:activity=>0, :offset=>3, :created_at=>Mon Feb 23 10:34:28 -0800 2009}, {:activity=>0, :offset=>4, :created_at=>Mon Feb 16 10:34:28 -0800 2009}, {:activity=>0, :offset=>5, :created_at=>Mon Feb 09 10:34:28 -0800 2009}, {:activity=>0, :offset=>6, :created_at=>Mon Feb 02 10:34:28 -0800 2009}, {:activity=>0, :offset=>7, :created_at=>Mon Jan 26 10:34:28 -0800 2009}, {:activity=>0, :offset=>8, :created_at=>Mon Jan 19 10:34:28 -0800 2009}]

SQL generated for previous statement…

  SELECT
   published_at AS timestamp,
   COUNT(*) AS activity_count,
   ((YEAR(now()) - YEAR(published_at))*52)+(WEEK(now())-WEEK(published_at)) as weeks_ago,
   YEARWEEK(published_at) AS unique_week
   FROM feeds
   WHERE 1=1 AND published_at > '2009-01-16 17:34:28'
   GROUP BY unique_week
   ORDER BY published_at ASC

  # Get Usd Net Commission in July ago grouped by day and order it in ascending order by the days commission was earnt on for account_id 1
  AccountPnl.activity_between(july_month.beginning_of_month,
                              july_month.end_of_month, 
                              :group_by => :day, 
                              :padding => false, 
                              :sum_on => "usd_net_commission",
                              :conditions => ["crm_account_id=?", 1], 
                              :order => :asc)

SQL generated for previous statement…

SELECT
 earnt_on AS timestamp,
 COUNT(*) AS activity_count, SUM(usd_net_commission) as sum,
 DATEDIFF('2009-07-31 23:59:59', earnt_on) as days_ago
 FROM crm_daily_account_pnls 
 WHERE crm_account_id=16 AND earnt_on
 BETWEEN '2009-07-01 00:00:00' AND '2009-07-31 23:59:59' 
 GROUP BY days_ago 
 ORDER BY earnt_on ASC

CONVERT DATA INTO GOOGLE CHART

Feed.activity_since(6.months.ago, :group_by => :week, :order => :desc).to_activity_gchart
=> "http://chart.apis.google.com/chart?cht=bvs&chs=200x50&chd=t:0,10,15,3,8,4,0,3,2,3,0,11,4,23,14,0,0,0,13,0,4,8,19,10,12,21,1&chco=336699&chbh=a,2&chds=0,23&chf=bg,s,EFEFEF&"

Feed.activity_since(6.months.ago, :group_by => :week, :order => :desc).to_activity_gchart
  AccountPnl.activity_between(july_month.beginning_of_month,
                              july_month.end_of_month, 
                              :group_by   => :day, 
                              :padding    => false, 
                              :sum_on     => "usd_net_commission",
                              :conditions => ["crm_account_id=?", 1], 
                              :order      => :asc).to_activity_gchart( :type => :line, 
                                                                       :column => :sum,
                                                                       :size => "100x45", 
                                                                       :bgcolor => "f0ffdc", 
                                                                       :chart_color => "D1EAFF", 
                                                                       :area_color => "DFEBFF",
                                                                       :line_color => "c7c7c7", 
                                                                       :line_width => "4"))
=> "http://chart.apis.google.com/chart?chs=100x45&cht=ls&chco=c7c7c7&chm=B,DFEBFF,0,0,0&chd=t:<data>&chds=0,0&chf=bg,s,f0ffdc&"

OTHER OPTIONS

  # Grabs a hash of the activity since <time ago> grouped by <hour/day/week>
  # 
  #   * :conditions
  #       same as the standard Rails finder. Used to scope your activity to a particular user, etc.
  #   * :padding
  #       true/false
  #   * :group_by
  #       :hour, :day, :week
  # 
  def activity_since(since=1.week.ago, options={})

  # Returns a URL to a simple google chart the represents the activity returned by the plugin
  # 
  #   * :type => :bar/:line
  #   * :size => "200x50"
  #   * :bgcolor => "EFEFEF"
  #   * :chart_color => "336699"
  #   * :area_color => "DFEBFF" (only for :line)
  #   * :line_color => "0077CC" (only for :line)
  #   * :line_width => "2" (only for :line)
  # 
  def to_activity_gchart(options={})

INDEX THE TIMESTAMP YOU USE

Obviously there is not “one index fits all” but you should include an index on at least the timestamp that fits the conditions you are using.
The same applies when performing SUM on a column.

add_index :model, [:created_at]

NOTES

• Using sum and creating Bar Charts is not fully tested – HouseKeeper
• Some work still needs to be done on the queries and a tidy up of has_activity.rb

Copyright © 2009 Cary Dunn <[email protected]>, HouseKeeper <[email protected]>, released under the MIT license