Description:
add authorization for show max score
Commit status:
[Not Reviewed]
References:
Comments:
0 Commit comments 0 Inline Comments
Unresolved TODOs:
There are no unresolved TODOs
Add another comment

r595:ad86e593f5b0 - - 1 file changed: 1 inserted, 1 deleted

@@ -1,387 +1,387
1 class ReportController < ApplicationController
1 class ReportController < ApplicationController
2
2
3 - before_filter :admin_authorization, only: [:login_stat,:submission_stat, :stuck, :cheat_report, :cheat_scruntinize]
3 + before_filter :admin_authorization, only: [:login_stat,:submission_stat, :stuck, :cheat_report, :cheat_scruntinize, :show_max_score]
4
4
5 before_filter(only: [:problem_hof]) { |c|
5 before_filter(only: [:problem_hof]) { |c|
6 return false unless authenticate
6 return false unless authenticate
7
7
8 if GraderConfiguration["right.user_view_submission"]
8 if GraderConfiguration["right.user_view_submission"]
9 return true;
9 return true;
10 end
10 end
11
11
12 admin_authorization
12 admin_authorization
13 }
13 }
14
14
15 def max_score
15 def max_score
16 end
16 end
17
17
18 def show_max_score
18 def show_max_score
19 #process parameters
19 #process parameters
20 #problems
20 #problems
21 @problems = []
21 @problems = []
22 params[:problem_id].each do |id|
22 params[:problem_id].each do |id|
23 next unless id.strip != ""
23 next unless id.strip != ""
24 @problems << Problem.find(id.to_i)
24 @problems << Problem.find(id.to_i)
25 end
25 end
26
26
27 #users
27 #users
28 @users = if params[:user] == "all" then
28 @users = if params[:user] == "all" then
29 User.find(:all, :include => [:contests, :contest_stat])
29 User.find(:all, :include => [:contests, :contest_stat])
30 else
30 else
31 User.includes(:contests).includes(:contest_stat).where(enabled: true)
31 User.includes(:contests).includes(:contest_stat).where(enabled: true)
32 end
32 end
33
33
34 #set up range from param
34 #set up range from param
35 since_id = params.fetch(:min_id, 0).to_i
35 since_id = params.fetch(:min_id, 0).to_i
36 until_id = params.fetch(:max_id, 0).to_i
36 until_id = params.fetch(:max_id, 0).to_i
37
37
38 #get data
38 #get data
39 @scorearray = Array.new
39 @scorearray = Array.new
40 @users.each do |u|
40 @users.each do |u|
41 ustat = Array.new
41 ustat = Array.new
42 ustat[0] = u
42 ustat[0] = u
43 @problems.each do |p|
43 @problems.each do |p|
44 max_points = 0
44 max_points = 0
45 Submission.find_in_range_by_user_and_problem(u.id,p.id,since_id,until_id).each do |sub|
45 Submission.find_in_range_by_user_and_problem(u.id,p.id,since_id,until_id).each do |sub|
46 max_points = sub.points if sub and sub.points and (sub.points > max_points)
46 max_points = sub.points if sub and sub.points and (sub.points > max_points)
47 end
47 end
48 ustat << [(max_points.to_f*100/p.full_score).round, (max_points>=p.full_score)]
48 ustat << [(max_points.to_f*100/p.full_score).round, (max_points>=p.full_score)]
49 end
49 end
50 @scorearray << ustat
50 @scorearray << ustat
51 end
51 end
52
52
53 if params[:commit] == 'download csv' then
53 if params[:commit] == 'download csv' then
54 csv = gen_csv_from_scorearray(@scorearray,@problems)
54 csv = gen_csv_from_scorearray(@scorearray,@problems)
55 send_data csv, filename: 'max_score.csv'
55 send_data csv, filename: 'max_score.csv'
56 else
56 else
57 #render template: 'user_admin/user_stat'
57 #render template: 'user_admin/user_stat'
58 render 'max_score'
58 render 'max_score'
59 end
59 end
60
60
61 end
61 end
62
62
63 def score
63 def score
64 if params[:commit] == 'download csv'
64 if params[:commit] == 'download csv'
65 @problems = Problem.all
65 @problems = Problem.all
66 else
66 else
67 @problems = Problem.find_available_problems
67 @problems = Problem.find_available_problems
68 end
68 end
69 @users = User.includes(:contests, :contest_stat).where(enabled: true) #find(:all, :include => [:contests, :contest_stat]).where(enabled: true)
69 @users = User.includes(:contests, :contest_stat).where(enabled: true) #find(:all, :include => [:contests, :contest_stat]).where(enabled: true)
70 @scorearray = Array.new
70 @scorearray = Array.new
71 @users.each do |u|
71 @users.each do |u|
72 ustat = Array.new
72 ustat = Array.new
73 ustat[0] = u
73 ustat[0] = u
74 @problems.each do |p|
74 @problems.each do |p|
75 sub = Submission.find_last_by_user_and_problem(u.id,p.id)
75 sub = Submission.find_last_by_user_and_problem(u.id,p.id)
76 if (sub!=nil) and (sub.points!=nil) and p and p.full_score
76 if (sub!=nil) and (sub.points!=nil) and p and p.full_score
77 ustat << [(sub.points.to_f*100/p.full_score).round, (sub.points>=p.full_score)]
77 ustat << [(sub.points.to_f*100/p.full_score).round, (sub.points>=p.full_score)]
78 else
78 else
79 ustat << [0,false]
79 ustat << [0,false]
80 end
80 end
81 end
81 end
82 @scorearray << ustat
82 @scorearray << ustat
83 end
83 end
84 if params[:commit] == 'download csv' then
84 if params[:commit] == 'download csv' then
85 csv = gen_csv_from_scorearray(@scorearray,@problems)
85 csv = gen_csv_from_scorearray(@scorearray,@problems)
86 send_data csv, filename: 'last_score.csv'
86 send_data csv, filename: 'last_score.csv'
87 else
87 else
88 render template: 'user_admin/user_stat'
88 render template: 'user_admin/user_stat'
89 end
89 end
90
90
91 end
91 end
92
92
93 def login_stat
93 def login_stat
94 @logins = Array.new
94 @logins = Array.new
95
95
96 date_and_time = '%Y-%m-%d %H:%M'
96 date_and_time = '%Y-%m-%d %H:%M'
97 begin
97 begin
98 md = params[:since_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
98 md = params[:since_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
99 @since_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i)
99 @since_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i)
100 rescue
100 rescue
101 @since_time = DateTime.new(1000,1,1)
101 @since_time = DateTime.new(1000,1,1)
102 end
102 end
103 begin
103 begin
104 md = params[:until_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
104 md = params[:until_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
105 @until_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i)
105 @until_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i)
106 rescue
106 rescue
107 @until_time = DateTime.new(3000,1,1)
107 @until_time = DateTime.new(3000,1,1)
108 end
108 end
109
109
110 User.all.each do |user|
110 User.all.each do |user|
111 @logins << { id: user.id,
111 @logins << { id: user.id,
112 login: user.login,
112 login: user.login,
113 full_name: user.full_name,
113 full_name: user.full_name,
114 count: Login.where("user_id = ? AND created_at >= ? AND created_at <= ?",
114 count: Login.where("user_id = ? AND created_at >= ? AND created_at <= ?",
115 user.id,@since_time,@until_time)
115 user.id,@since_time,@until_time)
116 .count(:id),
116 .count(:id),
117 min: Login.where("user_id = ? AND created_at >= ? AND created_at <= ?",
117 min: Login.where("user_id = ? AND created_at >= ? AND created_at <= ?",
118 user.id,@since_time,@until_time)
118 user.id,@since_time,@until_time)
119 .minimum(:created_at),
119 .minimum(:created_at),
120 max: Login.where("user_id = ? AND created_at >= ? AND created_at <= ?",
120 max: Login.where("user_id = ? AND created_at >= ? AND created_at <= ?",
121 user.id,@since_time,@until_time)
121 user.id,@since_time,@until_time)
122 .maximum(:created_at),
122 .maximum(:created_at),
123 ip: Login.where("user_id = ? AND created_at >= ? AND created_at <= ?",
123 ip: Login.where("user_id = ? AND created_at >= ? AND created_at <= ?",
124 user.id,@since_time,@until_time)
124 user.id,@since_time,@until_time)
125 .select(:ip_address).uniq
125 .select(:ip_address).uniq
126
126
127 }
127 }
128 end
128 end
129 end
129 end
130
130
131 def submission_stat
131 def submission_stat
132
132
133 date_and_time = '%Y-%m-%d %H:%M'
133 date_and_time = '%Y-%m-%d %H:%M'
134 begin
134 begin
135 @since_time = DateTime.strptime(params[:since_datetime],date_and_time)
135 @since_time = DateTime.strptime(params[:since_datetime],date_and_time)
136 rescue
136 rescue
137 @since_time = DateTime.new(1000,1,1)
137 @since_time = DateTime.new(1000,1,1)
138 end
138 end
139 begin
139 begin
140 @until_time = DateTime.strptime(params[:until_datetime],date_and_time)
140 @until_time = DateTime.strptime(params[:until_datetime],date_and_time)
141 rescue
141 rescue
142 @until_time = DateTime.new(3000,1,1)
142 @until_time = DateTime.new(3000,1,1)
143 end
143 end
144
144
145 @submissions = {}
145 @submissions = {}
146
146
147 User.find_each do |user|
147 User.find_each do |user|
148 @submissions[user.id] = { login: user.login, full_name: user.full_name, count: 0, sub: { } }
148 @submissions[user.id] = { login: user.login, full_name: user.full_name, count: 0, sub: { } }
149 end
149 end
150
150
151 Submission.where("submitted_at >= ? AND submitted_at <= ?",@since_time,@until_time).find_each do |s|
151 Submission.where("submitted_at >= ? AND submitted_at <= ?",@since_time,@until_time).find_each do |s|
152 if @submissions[s.user_id]
152 if @submissions[s.user_id]
153 if not @submissions[s.user_id][:sub].has_key?(s.problem_id)
153 if not @submissions[s.user_id][:sub].has_key?(s.problem_id)
154 a = Problem.find_by_id(s.problem_id)
154 a = Problem.find_by_id(s.problem_id)
155 @submissions[s.user_id][:sub][s.problem_id] =
155 @submissions[s.user_id][:sub][s.problem_id] =
156 { prob_name: (a ? a.full_name : '(NULL)'),
156 { prob_name: (a ? a.full_name : '(NULL)'),
157 sub_ids: [s.id] }
157 sub_ids: [s.id] }
158 else
158 else
159 @submissions[s.user_id][:sub][s.problem_id][:sub_ids] << s.id
159 @submissions[s.user_id][:sub][s.problem_id][:sub_ids] << s.id
160 end
160 end
161 @submissions[s.user_id][:count] += 1
161 @submissions[s.user_id][:count] += 1
162 end
162 end
163 end
163 end
164 end
164 end
165
165
166 def problem_hof
166 def problem_hof
167 # gen problem list
167 # gen problem list
168 @user = User.find(session[:user_id])
168 @user = User.find(session[:user_id])
169 @problems = @user.available_problems
169 @problems = @user.available_problems
170
170
171 # get selected problems or the default
171 # get selected problems or the default
172 if params[:id]
172 if params[:id]
173 begin
173 begin
174 @problem = Problem.available.find(params[:id])
174 @problem = Problem.available.find(params[:id])
175 rescue
175 rescue
176 redirect_to action: :problem_hof
176 redirect_to action: :problem_hof
177 flash[:notice] = 'Error: submissions for that problem are not viewable.'
177 flash[:notice] = 'Error: submissions for that problem are not viewable.'
178 return
178 return
179 end
179 end
180 end
180 end
181
181
182 return unless @problem
182 return unless @problem
183
183
184 @by_lang = {} #aggregrate by language
184 @by_lang = {} #aggregrate by language
185
185
186 range =65
186 range =65
187 @histogram = { data: Array.new(range,0), summary: {} }
187 @histogram = { data: Array.new(range,0), summary: {} }
188 @summary = {count: 0, solve: 0, attempt: 0}
188 @summary = {count: 0, solve: 0, attempt: 0}
189 user = Hash.new(0)
189 user = Hash.new(0)
190 Submission.where(problem_id: @problem.id).find_each do |sub|
190 Submission.where(problem_id: @problem.id).find_each do |sub|
191 #histogram
191 #histogram
192 d = (DateTime.now.in_time_zone - sub.submitted_at) / 24 / 60 / 60
192 d = (DateTime.now.in_time_zone - sub.submitted_at) / 24 / 60 / 60
193 @histogram[:data][d.to_i] += 1 if d < range
193 @histogram[:data][d.to_i] += 1 if d < range
194
194
195 next unless sub.points
195 next unless sub.points
196 @summary[:count] += 1
196 @summary[:count] += 1
197 user[sub.user_id] = [user[sub.user_id], (sub.points >= @problem.full_score) ? 1 : 0].max
197 user[sub.user_id] = [user[sub.user_id], (sub.points >= @problem.full_score) ? 1 : 0].max
198
198
199 lang = Language.find_by_id(sub.language_id)
199 lang = Language.find_by_id(sub.language_id)
200 next unless lang
200 next unless lang
201 next unless sub.points >= @problem.full_score
201 next unless sub.points >= @problem.full_score
202
202
203 #initialize
203 #initialize
204 unless @by_lang.has_key?(lang.pretty_name)
204 unless @by_lang.has_key?(lang.pretty_name)
205 @by_lang[lang.pretty_name] = {
205 @by_lang[lang.pretty_name] = {
206 runtime: { avail: false, value: 2**30-1 },
206 runtime: { avail: false, value: 2**30-1 },
207 memory: { avail: false, value: 2**30-1 },
207 memory: { avail: false, value: 2**30-1 },
208 length: { avail: false, value: 2**30-1 },
208 length: { avail: false, value: 2**30-1 },
209 first: { avail: false, value: DateTime.new(3000,1,1) }
209 first: { avail: false, value: DateTime.new(3000,1,1) }
210 }
210 }
211 end
211 end
212
212
213 if sub.max_runtime and sub.max_runtime < @by_lang[lang.pretty_name][:runtime][:value]
213 if sub.max_runtime and sub.max_runtime < @by_lang[lang.pretty_name][:runtime][:value]
214 @by_lang[lang.pretty_name][:runtime] = { avail: true, user_id: sub.user_id, value: sub.max_runtime, sub_id: sub.id }
214 @by_lang[lang.pretty_name][:runtime] = { avail: true, user_id: sub.user_id, value: sub.max_runtime, sub_id: sub.id }
215 end
215 end
216
216
217 if sub.peak_memory and sub.peak_memory < @by_lang[lang.pretty_name][:memory][:value]
217 if sub.peak_memory and sub.peak_memory < @by_lang[lang.pretty_name][:memory][:value]
218 @by_lang[lang.pretty_name][:memory] = { avail: true, user_id: sub.user_id, value: sub.peak_memory, sub_id: sub.id }
218 @by_lang[lang.pretty_name][:memory] = { avail: true, user_id: sub.user_id, value: sub.peak_memory, sub_id: sub.id }
219 end
219 end
220
220
221 if sub.submitted_at and sub.submitted_at < @by_lang[lang.pretty_name][:first][:value] and
221 if sub.submitted_at and sub.submitted_at < @by_lang[lang.pretty_name][:first][:value] and
222 !sub.user.admin?
222 !sub.user.admin?
223 @by_lang[lang.pretty_name][:first] = { avail: true, user_id: sub.user_id, value: sub.submitted_at, sub_id: sub.id }
223 @by_lang[lang.pretty_name][:first] = { avail: true, user_id: sub.user_id, value: sub.submitted_at, sub_id: sub.id }
224 end
224 end
225
225
226 if @by_lang[lang.pretty_name][:length][:value] > sub.effective_code_length
226 if @by_lang[lang.pretty_name][:length][:value] > sub.effective_code_length
227 @by_lang[lang.pretty_name][:length] = { avail: true, user_id: sub.user_id, value: sub.effective_code_length, sub_id: sub.id }
227 @by_lang[lang.pretty_name][:length] = { avail: true, user_id: sub.user_id, value: sub.effective_code_length, sub_id: sub.id }
228 end
228 end
229 end
229 end
230
230
231 #process user_id
231 #process user_id
232 @by_lang.each do |lang,prop|
232 @by_lang.each do |lang,prop|
233 prop.each do |k,v|
233 prop.each do |k,v|
234 v[:user] = User.exists?(v[:user_id]) ? User.find(v[:user_id]).full_name : "(NULL)"
234 v[:user] = User.exists?(v[:user_id]) ? User.find(v[:user_id]).full_name : "(NULL)"
235 end
235 end
236 end
236 end
237
237
238 #sum into best
238 #sum into best
239 if @by_lang and @by_lang.first
239 if @by_lang and @by_lang.first
240 @best = @by_lang.first[1].clone
240 @best = @by_lang.first[1].clone
241 @by_lang.each do |lang,prop|
241 @by_lang.each do |lang,prop|
242 if @best[:runtime][:value] >= prop[:runtime][:value]
242 if @best[:runtime][:value] >= prop[:runtime][:value]
243 @best[:runtime] = prop[:runtime]
243 @best[:runtime] = prop[:runtime]
244 @best[:runtime][:lang] = lang
244 @best[:runtime][:lang] = lang
245 end
245 end
246 if @best[:memory][:value] >= prop[:memory][:value]
246 if @best[:memory][:value] >= prop[:memory][:value]
247 @best[:memory] = prop[:memory]
247 @best[:memory] = prop[:memory]
248 @best[:memory][:lang] = lang
248 @best[:memory][:lang] = lang
249 end
249 end
250 if @best[:length][:value] >= prop[:length][:value]
250 if @best[:length][:value] >= prop[:length][:value]
251 @best[:length] = prop[:length]
251 @best[:length] = prop[:length]
252 @best[:length][:lang] = lang
252 @best[:length][:lang] = lang
253 end
253 end
254 if @best[:first][:value] >= prop[:first][:value]
254 if @best[:first][:value] >= prop[:first][:value]
255 @best[:first] = prop[:first]
255 @best[:first] = prop[:first]
256 @best[:first][:lang] = lang
256 @best[:first][:lang] = lang
257 end
257 end
258 end
258 end
259 end
259 end
260
260
261 @histogram[:summary][:max] = [@histogram[:data].max,1].max
261 @histogram[:summary][:max] = [@histogram[:data].max,1].max
262 @summary[:attempt] = user.count
262 @summary[:attempt] = user.count
263 user.each_value { |v| @summary[:solve] += 1 if v == 1 }
263 user.each_value { |v| @summary[:solve] += 1 if v == 1 }
264 end
264 end
265
265
266 def stuck #report struggling user,problem
266 def stuck #report struggling user,problem
267 # init
267 # init
268 user,problem = nil
268 user,problem = nil
269 solve = true
269 solve = true
270 tries = 0
270 tries = 0
271 @struggle = Array.new
271 @struggle = Array.new
272 record = {}
272 record = {}
273 Submission.includes(:problem,:user).order(:problem_id,:user_id).find_each do |sub|
273 Submission.includes(:problem,:user).order(:problem_id,:user_id).find_each do |sub|
274 next unless sub.problem and sub.user
274 next unless sub.problem and sub.user
275 if user != sub.user_id or problem != sub.problem_id
275 if user != sub.user_id or problem != sub.problem_id
276 @struggle << { user: record[:user], problem: record[:problem], tries: tries } unless solve
276 @struggle << { user: record[:user], problem: record[:problem], tries: tries } unless solve
277 record = {user: sub.user, problem: sub.problem}
277 record = {user: sub.user, problem: sub.problem}
278 user,problem = sub.user_id, sub.problem_id
278 user,problem = sub.user_id, sub.problem_id
279 solve = false
279 solve = false
280 tries = 0
280 tries = 0
281 end
281 end
282 if sub.points >= sub.problem.full_score
282 if sub.points >= sub.problem.full_score
283 solve = true
283 solve = true
284 else
284 else
285 tries += 1
285 tries += 1
286 end
286 end
287 end
287 end
288 @struggle.sort!{|a,b| b[:tries] <=> a[:tries] }
288 @struggle.sort!{|a,b| b[:tries] <=> a[:tries] }
289 @struggle = @struggle[0..50]
289 @struggle = @struggle[0..50]
290 end
290 end
291
291
292
292
293 def multiple_login
293 def multiple_login
294 #user with multiple IP
294 #user with multiple IP
295 raw = Submission.joins(:user).joins(:problem).where("problems.available != 0").group("login,ip_address").order(:login)
295 raw = Submission.joins(:user).joins(:problem).where("problems.available != 0").group("login,ip_address").order(:login)
296 last,count = 0,0
296 last,count = 0,0
297 first = 0
297 first = 0
298 @users = []
298 @users = []
299 raw.each do |r|
299 raw.each do |r|
300 if last != r.user.login
300 if last != r.user.login
301 count = 1
301 count = 1
302 last = r.user.login
302 last = r.user.login
303 first = r
303 first = r
304 else
304 else
305 @users << first if count == 1
305 @users << first if count == 1
306 @users << r
306 @users << r
307 count += 1
307 count += 1
308 end
308 end
309 end
309 end
310
310
311 #IP with multiple user
311 #IP with multiple user
312 raw = Submission.joins(:user).joins(:problem).where("problems.available != 0").group("login,ip_address").order(:ip_address)
312 raw = Submission.joins(:user).joins(:problem).where("problems.available != 0").group("login,ip_address").order(:ip_address)
313 last,count = 0,0
313 last,count = 0,0
314 first = 0
314 first = 0
315 @ip = []
315 @ip = []
316 raw.each do |r|
316 raw.each do |r|
317 if last != r.ip_address
317 if last != r.ip_address
318 count = 1
318 count = 1
319 last = r.ip_address
319 last = r.ip_address
320 first = r
320 first = r
321 else
321 else
322 @ip << first if count == 1
322 @ip << first if count == 1
323 @ip << r
323 @ip << r
324 count += 1
324 count += 1
325 end
325 end
326 end
326 end
327 end
327 end
328
328
329 def cheat_report
329 def cheat_report
330 date_and_time = '%Y-%m-%d %H:%M'
330 date_and_time = '%Y-%m-%d %H:%M'
331 begin
331 begin
332 md = params[:since_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
332 md = params[:since_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
333 @since_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i)
333 @since_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i)
334 rescue
334 rescue
335 @since_time = Time.zone.now.ago( 90.minutes)
335 @since_time = Time.zone.now.ago( 90.minutes)
336 end
336 end
337 begin
337 begin
338 md = params[:until_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
338 md = params[:until_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
339 @until_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i)
339 @until_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i)
340 rescue
340 rescue
341 @until_time = Time.zone.now
341 @until_time = Time.zone.now
342 end
342 end
343
343
344 #multi login
344 #multi login
345 @ml = Login.joins(:user).where("logins.created_at >= ? and logins.created_at <= ?",@since_time,@until_time).select('users.login,count(distinct ip_address) as count,users.full_name').group("users.id").having("count > 1")
345 @ml = Login.joins(:user).where("logins.created_at >= ? and logins.created_at <= ?",@since_time,@until_time).select('users.login,count(distinct ip_address) as count,users.full_name').group("users.id").having("count > 1")
346
346
347 st = <<-SQL
347 st = <<-SQL
348 SELECT l2.*
348 SELECT l2.*
349 FROM logins l2 INNER JOIN
349 FROM logins l2 INNER JOIN
350 (SELECT u.id,COUNT(DISTINCT ip_address) as count,u.login,u.full_name
350 (SELECT u.id,COUNT(DISTINCT ip_address) as count,u.login,u.full_name
351 FROM logins l
351 FROM logins l
352 INNER JOIN users u ON l.user_id = u.id
352 INNER JOIN users u ON l.user_id = u.id
353 WHERE l.created_at >= '#{@since_time.in_time_zone("UTC")}' and l.created_at <= '#{@until_time.in_time_zone("UTC")}'
353 WHERE l.created_at >= '#{@since_time.in_time_zone("UTC")}' and l.created_at <= '#{@until_time.in_time_zone("UTC")}'
354 GROUP BY u.id
354 GROUP BY u.id
355 HAVING count > 1
355 HAVING count > 1
356 ) ml ON l2.user_id = ml.id
356 ) ml ON l2.user_id = ml.id
357 WHERE l2.created_at >= '#{@since_time.in_time_zone("UTC")}' and l2.created_at <= '#{@until_time.in_time_zone("UTC")}'
357 WHERE l2.created_at >= '#{@since_time.in_time_zone("UTC")}' and l2.created_at <= '#{@until_time.in_time_zone("UTC")}'
358 UNION
358 UNION
359 SELECT l2.*
359 SELECT l2.*
360 FROM logins l2 INNER JOIN
360 FROM logins l2 INNER JOIN
361 (SELECT l.ip_address,COUNT(DISTINCT u.id) as count
361 (SELECT l.ip_address,COUNT(DISTINCT u.id) as count
362 FROM logins l
362 FROM logins l
363 INNER JOIN users u ON l.user_id = u.id
363 INNER JOIN users u ON l.user_id = u.id
364 WHERE l.created_at >= '#{@since_time.in_time_zone("UTC")}' and l.created_at <= '#{@until_time.in_time_zone("UTC")}'
364 WHERE l.created_at >= '#{@since_time.in_time_zone("UTC")}' and l.created_at <= '#{@until_time.in_time_zone("UTC")}'
365 GROUP BY l.ip_address
365 GROUP BY l.ip_address
366 HAVING count > 1
366 HAVING count > 1
367 ) ml on ml.ip_address = l2.ip_address
367 ) ml on ml.ip_address = l2.ip_address
368 INNER JOIN users u ON l2.user_id = u.id
368 INNER JOIN users u ON l2.user_id = u.id
369 WHERE l2.created_at >= '#{@since_time.in_time_zone("UTC")}' and l2.created_at <= '#{@until_time.in_time_zone("UTC")}'
369 WHERE l2.created_at >= '#{@since_time.in_time_zone("UTC")}' and l2.created_at <= '#{@until_time.in_time_zone("UTC")}'
370 ORDER BY ip_address,created_at
370 ORDER BY ip_address,created_at
371 SQL
371 SQL
372 @mld = Login.find_by_sql(st)
372 @mld = Login.find_by_sql(st)
373
373
374 st = <<-SQL
374 st = <<-SQL
375 SELECT s.id,s.user_id,s.ip_address,s.submitted_at,s.problem_id
375 SELECT s.id,s.user_id,s.ip_address,s.submitted_at,s.problem_id
376 FROM submissions s INNER JOIN
376 FROM submissions s INNER JOIN
377 (SELECT u.id,COUNT(DISTINCT ip_address) as count,u.login,u.full_name
377 (SELECT u.id,COUNT(DISTINCT ip_address) as count,u.login,u.full_name
378 FROM logins l
378 FROM logins l
379 INNER JOIN users u ON l.user_id = u.id
379 INNER JOIN users u ON l.user_id = u.id
380 WHERE l.created_at >= ? and l.created_at <= ?
380 WHERE l.created_at >= ? and l.created_at <= ?
381 GROUP BY u.id
381 GROUP BY u.id
382 HAVING count > 1
382 HAVING count > 1
383 ) ml ON s.user_id = ml.id
383 ) ml ON s.user_id = ml.id
384 WHERE s.submitted_at >= ? and s.submitted_at <= ?
384 WHERE s.submitted_at >= ? and s.submitted_at <= ?
385 UNION
385 UNION
386 SELECT s.id,s.user_id,s.ip_address,s.submitted_at,s.problem_id
386 SELECT s.id,s.user_id,s.ip_address,s.submitted_at,s.problem_id
387 FROM submissions s INNER JOIN
387 FROM submissions s INNER JOIN
You need to be logged in to leave comments. Login now