cafe-grader-web

Commit r506:c673d73fd0d3 public

parent | child
Description:
add cheat report prototype
Commit status:
[Not Reviewed]
References:
algo
Diff options:
Raw Diff | Patch Diff | Download Diff | Show 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

r506:c673d73fd0d3 - - 4 files changed: 178 inserted, 2 deleted

Show file before | Show file after | Raw diff | Download diff | Show whitespace Increase context Show commentsHide comments
@@ -0,0 +1,77
1 + - content_for :header do
2 + = stylesheet_link_tag 'tablesorter-theme.cafe'
3 + = javascript_include_tag 'local_jquery'
4 +
5 + %script{:type=>"text/javascript"}
6 + $(function () {
7 + $('#since_datetime').datetimepicker({ showButtonPanel: true, dateFormat: "yy-mm-dd", controlType: "slider"} );
8 + $('#until_datetime').datetimepicker({ showButtonPanel: true, dateFormat: "yy-mm-dd", controlType: "slider"} );
9 + $('#my_table').tablesorter({widthFixed: true, widgets: ['zebra']});
10 + $('#my_table2').tablesorter({widthFixed: true, widgets: ['zebra']});
11 + $('#sub_table').tablesorter({widthFixed: true, widgets: ['zebra']});
12 + });
13 +
14 + %h1 Login status
15 +
16 + =render partial: 'report_menu'
17 + =render partial: 'date_range', locals: {param_text: 'Login date range:', title: 'Query login stat in the range' }
18 +
19 + %h2 Suspect
20 +
21 + %table.tablesorter-cafe#my_table
22 + %thead
23 + %tr
24 + %th login
25 + %th full name
26 + %th login count
27 + %tbody
28 + - @ml.each do |l|
29 + %tr{class: cycle('info-even','info-odd')}
30 + %td= link_to l[:login], controller: 'users', action: 'profile', id: l[:id]
31 + %td= l[:full_name]
32 + %td= l[:count]
33 +
34 +
35 + %h2 Multiple Logins Report
36 + This section reports all logins record that have either multiple ip per login or multiple login per ip.
37 +
38 + %table.tablesorter-cafe#my_table2
39 + %thead
40 + %tr
41 + %th login
42 + %th full name
43 + %th IP
44 + %th time
45 + %tbody
46 + - @mld.each do |l|
47 + %tr{class: cycle('info-even','info-odd')}
48 + %td= link_to l.user[:login], controller: 'users', action: 'profile', id: l[:user_id]
49 + %td= l.user[:full_name]
50 + %td= l[:ip_address]
51 + %td= l[:created_at]
52 +
53 + %h2 Multiple IP Submissions Report
54 + This section reports all submission records that have USER_ID matchs ID that logins on multiple IP
55 + and that have IP_ADDRESS that has multiple ID logins
56 +
57 + Be noted that when submission IP address is not available, this might exclude
58 + submissions that come from ID that login on multiple-login IP
59 +
60 + %table.tablesorter-cafe#sub_table
61 + %thead
62 + %tr
63 + %th login
64 + %th full name
65 + %th IP
66 + %th problem
67 + %th Submission
68 + %th time
69 + %tbody
70 + - @subs.each do |s|
71 + %tr{class: cycle('info-even','info-odd')}
72 + %td= link_to s.user[:login], controller: 'users', action: 'profile', id: s[:user_id]
73 + %td= s.user[:full_name]
74 + %td= s[:ip_address]
75 + %td= s.problem.name
76 + %td= link_to(s.id, controller: 'graders' , action: 'submission', id: s.id)
77 + %td= s[:submitted_at]
Show file before | Show file after | Raw diff | Download diff | Show whitespace Increase context Show commentsHide comments
@@ -0,0 +1,9
1 + class ChangeUseridOnLogin < ActiveRecord::Migration
2 + def up
3 + change_column :logins, :user_id, :integer
4 + end
5 +
6 + def down
7 + change_column :logins, :user_id, :string
8 + end
9 + end
Show file before | Show file after | Raw diff | Download diff | Show whitespace Increase context Show commentsHide comments
@@ -64,195 +64,285
64 64 end
65 65
66 66 @submissions = {}
67 67
68 68 User.find_each do |user|
69 69 @submissions[user.id] = { login: user.login, full_name: user.full_name, count: 0, sub: { } }
70 70 end
71 71
72 72 Submission.where("submitted_at >= ? AND submitted_at <= ?",@since_time,@until_time).find_each do |s|
73 73 if @submissions[s.user_id]
74 74 if not @submissions[s.user_id][:sub].has_key?(s.problem_id)
75 75 a = nil
76 76 begin
77 77 a = Problem.find(s.problem_id)
78 78 rescue
79 79 a = nil
80 80 end
81 81 @submissions[s.user_id][:sub][s.problem_id] =
82 82 { prob_name: (a ? a.full_name : '(NULL)'),
83 83 sub_ids: [s.id] }
84 84 else
85 85 @submissions[s.user_id][:sub][s.problem_id][:sub_ids] << s.id
86 86 end
87 87 @submissions[s.user_id][:count] += 1
88 88 end
89 89 end
90 90 end
91 91
92 92 def problem_hof
93 93 # gen problem list
94 94 @user = User.find(session[:user_id])
95 95 @problems = @user.available_problems
96 96
97 97 # get selected problems or the default
98 98 if params[:id]
99 99 begin
100 100 @problem = Problem.available.find(params[:id])
101 101 rescue
102 102 redirect_to action: :problem_hof
103 103 flash[:notice] = 'Error: submissions for that problem are not viewable.'
104 104 return
105 105 end
106 106 end
107 107
108 108 return unless @problem
109 109
110 110 @by_lang = {} #aggregrate by language
111 111
112 112 range =65
113 113 @histogram = { data: Array.new(range,0), summary: {} }
114 114 @summary = {count: 0, solve: 0, attempt: 0}
115 115 user = Hash.new(0)
116 116 Submission.where(problem_id: @problem.id).find_each do |sub|
117 117 #histogram
118 118 d = (DateTime.now.in_time_zone - sub.submitted_at) / 24 / 60 / 60
119 119 @histogram[:data][d.to_i] += 1 if d < range
120 120
121 121 next unless sub.points
122 122 @summary[:count] += 1
123 123 user[sub.user_id] = [user[sub.user_id], (sub.points >= @problem.full_score) ? 1 : 0].max
124 124
125 125 lang = Language.find_by_id(sub.language_id)
126 126 next unless lang
127 127 next unless sub.points >= @problem.full_score
128 128
129 129 #initialize
130 130 unless @by_lang.has_key?(lang.pretty_name)
131 131 @by_lang[lang.pretty_name] = {
132 132 runtime: { avail: false, value: 2**30-1 },
133 133 memory: { avail: false, value: 2**30-1 },
134 134 length: { avail: false, value: 2**30-1 },
135 135 first: { avail: false, value: DateTime.new(3000,1,1) }
136 136 }
137 137 end
138 138
139 139 if sub.max_runtime and sub.max_runtime < @by_lang[lang.pretty_name][:runtime][:value]
140 140 @by_lang[lang.pretty_name][:runtime] = { avail: true, user_id: sub.user_id, value: sub.max_runtime, sub_id: sub.id }
141 141 end
142 142
143 143 if sub.peak_memory and sub.peak_memory < @by_lang[lang.pretty_name][:memory][:value]
144 144 @by_lang[lang.pretty_name][:memory] = { avail: true, user_id: sub.user_id, value: sub.peak_memory, sub_id: sub.id }
145 145 end
146 146
147 147 if sub.submitted_at and sub.submitted_at < @by_lang[lang.pretty_name][:first][:value] and
148 148 !sub.user.admin?
149 149 @by_lang[lang.pretty_name][:first] = { avail: true, user_id: sub.user_id, value: sub.submitted_at, sub_id: sub.id }
150 150 end
151 151
152 152 if @by_lang[lang.pretty_name][:length][:value] > sub.effective_code_length
153 153 @by_lang[lang.pretty_name][:length] = { avail: true, user_id: sub.user_id, value: sub.effective_code_length, sub_id: sub.id }
154 154 end
155 155 end
156 156
157 157 #process user_id
158 158 @by_lang.each do |lang,prop|
159 159 prop.each do |k,v|
160 160 v[:user] = User.exists?(v[:user_id]) ? User.find(v[:user_id]).full_name : "(NULL)"
161 161 end
162 162 end
163 163
164 164 #sum into best
165 165 if @by_lang and @by_lang.first
166 166 @best = @by_lang.first[1].clone
167 167 @by_lang.each do |lang,prop|
168 168 if @best[:runtime][:value] >= prop[:runtime][:value]
169 169 @best[:runtime] = prop[:runtime]
170 170 @best[:runtime][:lang] = lang
171 171 end
172 172 if @best[:memory][:value] >= prop[:memory][:value]
173 173 @best[:memory] = prop[:memory]
174 174 @best[:memory][:lang] = lang
175 175 end
176 176 if @best[:length][:value] >= prop[:length][:value]
177 177 @best[:length] = prop[:length]
178 178 @best[:length][:lang] = lang
179 179 end
180 180 if @best[:first][:value] >= prop[:first][:value]
181 181 @best[:first] = prop[:first]
182 182 @best[:first][:lang] = lang
183 183 end
184 184 end
185 185 end
186 186
187 187 @histogram[:summary][:max] = [@histogram[:data].max,1].max
188 188 @summary[:attempt] = user.count
189 189 user.each_value { |v| @summary[:solve] += 1 if v == 1 }
190 190 end
191 191
192 192 def stuck #report struggling user,problem
193 193 # init
194 194 user,problem = nil
195 195 solve = true
196 196 tries = 0
197 197 @struggle = Array.new
198 198 record = {}
199 199 Submission.includes(:problem,:user).order(:problem_id,:user_id).find_each do |sub|
200 200 next unless sub.problem and sub.user
201 201 if user != sub.user_id or problem != sub.problem_id
202 202 @struggle << { user: record[:user], problem: record[:problem], tries: tries } unless solve
203 203 record = {user: sub.user, problem: sub.problem}
204 204 user,problem = sub.user_id, sub.problem_id
205 205 solve = false
206 206 tries = 0
207 207 end
208 208 if sub.points >= sub.problem.full_score
209 209 solve = true
210 210 else
211 211 tries += 1
212 212 end
213 213 end
214 214 @struggle.sort!{|a,b| b[:tries] <=> a[:tries] }
215 215 @struggle = @struggle[0..50]
216 216 end
217 217
218 218
219 219 def multiple_login
220 220 #user with multiple IP
221 221 raw = Submission.joins(:user).joins(:problem).where("problems.available != 0").group("login,ip_address").order(:login)
222 222 last,count = 0,0
223 223 first = 0
224 224 @users = []
225 225 raw.each do |r|
226 226 if last != r.user.login
227 227 count = 1
228 228 last = r.user.login
229 229 first = r
230 230 else
231 231 @users << first if count == 1
232 232 @users << r
233 233 count += 1
234 234 end
235 235 end
236 236
237 237 #IP with multiple user
238 238 raw = Submission.joins(:user).joins(:problem).where("problems.available != 0").group("login,ip_address").order(:ip_address)
239 239 last,count = 0,0
240 240 first = 0
241 241 @ip = []
242 242 raw.each do |r|
243 243 if last != r.ip_address
244 244 count = 1
245 245 last = r.ip_address
246 246 first = r
247 247 else
248 248 @ip << first if count == 1
249 249 @ip << r
250 250 count += 1
251 251 end
252 252 end
253 253 end
254 254
255 255 def cheat_report
256 + date_and_time = '%Y-%m-%d %H:%M'
257 + begin
258 + 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 + rescue
261 + @since_time = Time.zone.now
262 + end
263 + begin
264 + 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 + rescue
267 + @until_time = Time.zone.now.ago( 90.minutes)
256 268 end
257 269
270 + #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 +
273 + st = <<-SQL
274 + SELECT l2.*
275 + FROM logins l2 INNER JOIN
276 + (SELECT u.id,COUNT(DISTINCT ip_address) as count,u.login,u.full_name
277 + FROM logins l
278 + 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 + GROUP BY u.id
281 + HAVING count > 1
282 + ) 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 + UNION
285 + SELECT l2.*
286 + FROM logins l2 INNER JOIN
287 + (SELECT l.ip_address,COUNT(DISTINCT u.id) as count
288 + FROM logins l
289 + 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 + GROUP BY l.ip_address
292 + HAVING count > 1
293 + ) ml on ml.ip_address = l2.ip_address
294 + 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 + ORDER BY ip_address,created_at
297 + SQL
298 + @mld = Login.find_by_sql(st)
299 +
300 + st = <<-SQL
301 + SELECT s.id,s.user_id,s.ip_address,s.submitted_at,s.problem_id
302 + FROM submissions s INNER JOIN
303 + (SELECT u.id,COUNT(DISTINCT ip_address) as count,u.login,u.full_name
304 + FROM logins l
305 + INNER JOIN users u ON l.user_id = u.id
306 + WHERE l.created_at >= ? and l.created_at <= ?
307 + GROUP BY u.id
308 + HAVING count > 1
309 + ) ml ON s.user_id = ml.id
310 + WHERE s.submitted_at >= ? and s.submitted_at <= ?
311 + UNION
312 + SELECT s.id,s.user_id,s.ip_address,s.submitted_at,s.problem_id
313 + FROM submissions s INNER JOIN
314 + (SELECT l.ip_address,COUNT(DISTINCT u.id) as count
315 + FROM logins l
316 + INNER JOIN users u ON l.user_id = u.id
317 + WHERE l.created_at >= ? and l.created_at <= ?
318 + GROUP BY l.ip_address
319 + HAVING count > 1
320 + ) ml on ml.ip_address = s.ip_address
321 + WHERE s.submitted_at >= ? and s.submitted_at <= ?
322 + ORDER BY ip_address,submitted_at
323 + SQL
324 + @subs = Submission.joins(:problem).find_by_sql([st,@since_time,@until_time,
325 + @since_time,@until_time,
326 + @since_time,@until_time,
327 + @since_time,@until_time])
328 +
329 + # st =
330 + # " INNER JOIN" +
331 + # "(SELECT u.id,l.ip_address,COUNT(DISTINCT ip_address) as count " +
332 + # " FROM logins l INNER JOIN users u ON l.user_id = u.id "+
333 + # " WHERE l.created_at >= '#{@since_time.in_time_zone("UTC")}' and l.created_at <= '#{@until_time.in_time_zone("UTC")}' " +
334 + # " GROUP BY u.id" +
335 + # " HAVING count > 1) ml "
336 + # #ml detail
337 + # @mld = Login.joins(st + "ON logins.user_id = ml.id").
338 + # where("logins.created_at >= ? and logins.created_at <= ?",@since_time,@until_time).
339 + # order("ip_address")
340 + #
341 + # #submissions
342 + # @subs = Submission.joins(:problem).joins(st + "ON submissions.user_id = ml.id").
343 + # where("submissions.submitted_at >= ? and submissions.submitted_at <= ?",@since_time,@until_time).
344 + # order("submissions.ip_address")
258 345 end
346 +
347 +
348 + end
Show file before | Show file after | Raw diff | Download diff | Show whitespace Increase context Show commentsHide comments
@@ -1,23 +1,23
1 1
2 2 = form_tag({session: :url }) do
3 3 .submitbox
4 4 %table
5 5 %tr
6 6 %td{colspan: 6, style: 'font-weight: bold'}= title
7 7 %tr
8 8 %td{style: 'width: 120px; font-weight: bold'}= param_text
9 9 %td{align: 'right'} since:
10 - %td= text_field_tag 'since_datetime'
10 + %td= text_field_tag 'since_datetime', @since_time
11 11 %tr
12 12 %td
13 13 %td{align: 'right'} until:
14 - %td= text_field_tag 'until_datetime'
14 + %td= text_field_tag 'until_datetime', @until_time
15 15 %tr
16 16 %td
17 17 %td
18 18 %td Blank mean no condition
19 19 %tr
20 20 %td
21 21 %td
22 22 %td= submit_tag 'query'
23 23
You need to be logged in to leave comments. Login now