cafe-grader-web

Commit r721:93040f45d604 public

parent | child
Description:
Merge pull request #21 from nattee/master block report/current_score from normal user
Commit status:
[Not Reviewed]
References:
merge default
Diff options:
Raw Diff | Patch Diff | Download Diff | Ignore whitespace | Increase context
Comments:
0 Commit comments 0 Inline Comments
Unresolved TODOs:
There are no unresolved TODOs
Side by Side Unified
Expand All Files Collapse All Files Wide Mode Diff
Add another comment
Browse Files

r721:93040f45d604 - - 1 file changed: 1 inserted, 1 deleted

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