projects.mako.cc - selectricity-live/blob - vendor/plugins/sitealizer/lib/app/models/site_tracker.rb

   1 class SiteTracker < ActiveRecord::Base
   2   set_table_name 'sitealizer'
   3
   4   def self.find_browsers(params={})
   5     browsers = {}
   6     tmp = []
   7     r = find_by_sql("SELECT user_agent, COUNT(*) AS total FROM sitealizer WHERE created_on "+
   8                     "LIKE '#{params[:year]}-#{params[:month]}%' GROUP BY user_agent ORDER BY total DESC")
   9     r.each{|env|
  10       browser_info = Sitealizer::Parser::UserAgent.browser_info(env.user_agent)
  11       tmp << {:total => env.total, :browser => browser_info[:type], :version => browser_info[:version]}
  12     }
  13     tmp.each{|set| n = "#{set[:browser]}|#{set[:version]}"; browsers[n] = (browsers[n]||0) + set[:total].to_i}
  14     browsers = browsers.inject([]){|a,v|
  15       name = v[0].split("|")
  16       a << {:browser => {:type => name[0], :version => name[1]}, :total => v[1]}
  17     }
  18     return browsers.sort{|x,y| y[:total] <=> x[:total]}
  19   end
  20
  21   def self.find_platforms(params={})
  22     platforms = {}
  23     tmp = []
  24     r = find_by_sql("SELECT user_agent, COUNT(*) AS total FROM sitealizer WHERE created_on "+
  25                     "LIKE '#{params[:year]}-#{params[:month]}%' GROUP BY user_agent ORDER BY total DESC")
  26     r.each{|env|
  27       tmp << {:total => env.total, :name => Sitealizer::Parser::UserAgent.get_platform(env.user_agent)}
  28     }
  29     tmp.each{|set| platforms[set[:name]] = (platforms[set[:name]]||0) + set[:total].to_i}
  30     platforms = platforms.inject([]){|a,v| a << {:name => v[0], :total => v[1]}}
  31     return platforms.sort{|x,y| y[:total] <=> x[:total]}
  32   end
  33
  34   def self.find_robots(params={})
  35     robots = {}
  36     tmp = []
  37     r = find_by_sql("SELECT user_agent, COUNT(*) AS total FROM sitealizer WHERE created_on "+
  38                     "LIKE '#{params[:year]}-#{params[:month]}%' GROUP BY user_agent ORDER BY total DESC")
  39     r.each{|env|
  40       bot = Sitealizer::Parser::Robot.get_name(env.user_agent)
  41       tmp << {:total => env.total, :name => bot} if bot
  42     }
  43     tmp.each{|set| robots[set[:name]] = (robots[set[:name]]||0) + set[:total].to_i}
  44     robots = robots.inject([]){|a,v| a << {:name => v[0], :total => v[1]}}
  45     return robots.sort{|x,y| y[:total] <=> x[:total]}
  46   end
  47
  48   def self.find_search_terms(params={})
  49     terms = {}
  50     tmp = []
  51     r = find_by_sql("SELECT referer, COUNT(*) AS total FROM sitealizer WHERE created_on "+
  52                     "LIKE '#{params[:year]}-#{params[:month]}%' GROUP BY referer ORDER BY total DESC LIMIT 30")
  53     r.each{|env|
  54       term = Sitealizer::Parser::Keyword.get_terms(env.referer)
  55       tmp << {:total => env.total, :query => term} if term
  56     }
  57     tmp.each{|set| terms[set[:query]] = (terms[set[:query]]||0) + set[:total].to_i}
  58     terms = terms.inject([]){|a,v| a << {:query => v[0], :total => v[1]}}
  59     return terms.sort{|x,y| y[:total] <=> x[:total]}
  60   end
  61
  62   def self.find_domains(params={})
  63     domains = {}
  64     tmp = []
  65     r = find_by_sql("SELECT referer, COUNT(*) AS total FROM sitealizer WHERE created_on "+
  66                     "LIKE '#{params[:year]}-#{params[:month]}%' GROUP BY referer ORDER BY total DESC LIMIT 30")
  67     r.each{|env|
  68       domain = Sitealizer::Parser::Keyword.get_domain(env.referer, params[:host])
  69       tmp << {:total => env.total, :domain => domain} if domain
  70     }
  71     tmp.each{|set| domains[set[:domain]] = (domains[set[:domain]]||0) + set[:total].to_i}
  72     domains = domains.inject([]){|a,v| a << {:domain => v[0], :total => v[1]}}
  73     return domains.sort{|x,y| y[:total] <=> x[:total]}
  74   end
  75
  76   def self.find_referers(params={})
  77     find_by_sql("SELECT referer, COUNT(*) AS total " +
  78                 "FROM sitealizer WHERE created_on LIKE '#{params[:year]}-#{params[:month]}%' "+
  79                 "GROUP BY referer ORDER BY total DESC LIMIT 30")
  80   end
  81
  82   def self.find_page_urls(params={})
  83     find_by_sql("SELECT path, COUNT(*) AS total " +
  84                 "FROM sitealizer WHERE created_on LIKE '#{params[:year]}-#{params[:month]}%' "+
  85                 "GROUP BY path ORDER BY total DESC LIMIT 30")
  86   end
  87
  88   def self.find_hosts(params={})
  89     find_by_sql("SELECT ip, COUNT(*) AS total FROM sitealizer WHERE "+
  90                 "created_on LIKE '#{params[:year]}-#{params[:month]}%' "+
  91                 "GROUP BY ip ORDER BY total DESC LIMIT 30")
  92   end
  93
  94   def self.find_languages(params={})
  95     find_by_sql("SELECT language, COUNT(*) AS total FROM sitealizer WHERE "+
  96                 "created_on LIKE '#{params[:year]}-#{params[:month]}%'  "+
  97                 "GROUP BY language ORDER BY total DESC LIMIT 15")
  98   end
  99
 100   def self.count_hits(params={})
 101     count = !params[:count] ? "*" : params[:count]
 102     date  = !params[:date] ? "#{params[:year]}-#{params[:month]}%" : params[:date]
 103     count_by_sql("SELECT COUNT(#{count}) FROM sitealizer WHERE created_on LIKE '#{date}'")
 104   end
 105
 106   def self.find_monthly_hits(params={})
 107     dataset = []
 108     12.times{|month| month+=1
 109       created = "#{params[:year]}-#{sprintf("%02d",month)}-%"
 110       date = "#{params[:year]}-#{month}-1".to_time.strftime('%B %Y')
 111       date_short = "#{params[:year]}-#{month}-1".to_time.strftime('%b %Y')
 112       visits = count_by_sql("SELECT COUNT(DISTINCT ip) FROM sitealizer WHERE created_on LIKE '#{created}'")
 113       hits   = count_by_sql("SELECT COUNT(*) FROM sitealizer WHERE created_on LIKE '#{created}'")
 114       dataset << [date_short, visits, hits, date]
 115     }
 116     dataset
 117   end
 118
 119   def self.find_daily_hits(params={})
 120     dataset = []
 121     days_in_mon = Date.civil(params[:year].to_i, params[:month].to_i, -1).day
 122     days_in_mon.times{|day| day+=1
 123       created = "#{params[:year]}-#{params[:month]}-#{sprintf("%02d",day)}"
 124       date = "#{params[:year]}-#{params[:month]}-#{day}".to_time.strftime('%b %d %Y')
 125       visits = count_by_sql("SELECT COUNT(DISTINCT ip) FROM sitealizer WHERE created_on = '#{created}'")
 126       hits   = count_by_sql("SELECT COUNT(*) FROM sitealizer WHERE created_on = '#{created}'")
 127       dataset << [day, visits, hits, date]
 128     }
 129     dataset
 130   end
 131
 132   def self.find_hourly_hits(params={})
 133     dataset = []
 134     24.times{|hour|
 135       hour = sprintf("%02d",hour)
 136       visits = count_by_sql("SELECT COUNT(DISTINCT ip) FROM sitealizer "+
 137                             "WHERE created_at LIKE '#{params[:year]}-#{params[:month]}% #{hour}:%'")
 138       hits = count_by_sql("SELECT COUNT(*) FROM sitealizer WHERE "+
 139                           "created_at LIKE '#{params[:year]}-#{params[:month]}% #{hour}:%'")
 140       dataset << [hour.to_s, visits, hits]
 141     }
 142     dataset
 143   end
 144
 145 end