cafe-grader-web

Commit r507:5af12f2a9a30 public

parent | child
Description:
add cheat detail
Commit status:
[Not Reviewed]
References:
algo
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

r507:5af12f2a9a30 - - 2 files changed: 52 inserted, 20 deleted

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