]> projects.mako.cc - selectricity-live/blob - vendor/plugins/sitealizer/lib/app/models/site_tracker.rb
Merge head
[selectricity-live] / 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

Benjamin Mako Hill || Want to submit a patch?