cafe-grader-web

Commit r859:d7fa5bf1aeba public

parent | child
Description:
fig bugs in login report
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

r859:d7fa5bf1aeba - - 2 files changed: 7 inserted, 6 deleted

Show file before | Show file after | Raw diff | Download diff | Ignore whitespace Increase context Show commentsHide comments
@@ -1,264 +1,262
1 1 require 'ipaddr'
2 2 require "securerandom"
3 3
4 4 class ApplicationController < ActionController::Base
5 5 protect_from_forgery
6 6
7 7 before_action :current_user
8 8 before_action :nav_announcement
9 9 before_action :unique_visitor_id
10 10
11 11 SINGLE_USER_MODE_CONF_KEY = 'system.single_user_mode'
12 12 MULTIPLE_IP_LOGIN_CONF_KEY = 'right.multiple_ip_login'
13 13 WHITELIST_IGNORE_CONF_KEY = 'right.whitelist_ignore'
14 14 WHITELIST_IP_CONF_KEY = 'right.whitelist_ip'
15 15
16 16 #report and redirect for unauthorized activities
17 17 def unauthorized_redirect(notice = 'You are not authorized to view the page you requested')
18 18 flash[:notice] = notice
19 19 redirect_to login_main_path
20 20 end
21 21
22 22 # Returns the current logged-in user (if any).
23 23 def current_user
24 24 return nil unless session[:user_id]
25 25 @current_user ||= User.find(session[:user_id])
26 26 end
27 27
28 28 def nav_announcement
29 29 @nav_announcement = Announcement.where(on_nav_bar: true)
30 30 end
31 31
32 32 def admin_authorization
33 33 return false unless check_valid_login
34 34 user = User.includes(:roles).find(session[:user_id])
35 35 unless user.admin?
36 36 unauthorized_redirect
37 37 return false
38 38 end
39 39 return true
40 40 end
41 41
42 42 #admin always count as every roles
43 43 def role_authorization(roles)
44 44 return false unless check_valid_login
45 45 user = User.find(session[:user_id])
46 46 return true if user.admin?
47 47 roles.each do |r|
48 48 return true if user.has_role?(r)
49 49 end
50 50 unauthorized_redirect
51 51 end
52 52
53 53 def authorization_by_roles(allowed_roles)
54 54 return false unless check_valid_login
55 55 unless @current_user.roles.detect { |role| allowed_roles.member?(role.name) }
56 56 unauthorized_redirect
57 57 return false
58 58 end
59 59 end
60 60
61 61 def testcase_authorization
62 62 #admin always has privileged
63 63 if @current_user.admin?
64 64 return true
65 65 end
66 66
67 67 unauthorized_redirect unless GraderConfiguration["right.view_testcase"]
68 68 end
69 69
70 70 def unique_visitor_id
71 71 unless cookies.encrypted[:uuid]
72 72 value = SecureRandom.uuid
73 73 cookies.encrypted[:uuid] = { value: value, expires: 20.year }
74 74 end
75 - puts "encrypt " + cookies.encrypted[:uuid]
76 - puts cookies[:uuid]
77 75 end
78 76
79 77 protected
80 78
81 79 #redirect to root (and also force logout)
82 80 #if the user is not logged_in or the system is in "ADMIN ONLY" mode
83 81 def check_valid_login
84 82 #check if logged in
85 83 unless session[:user_id]
86 84 if GraderConfiguration[SINGLE_USER_MODE_CONF_KEY]
87 85 unauthorized_redirect('You need to login but you cannot log in at this time')
88 86 else
89 87 unauthorized_redirect('You need to login')
90 88 end
91 89 return false
92 90 end
93 91
94 92 # check if run in single user mode
95 93 if GraderConfiguration[SINGLE_USER_MODE_CONF_KEY]
96 94 if @current_user==nil || (!@current_user.admin?)
97 95 unauthorized_redirect('You cannot log in at this time')
98 96 return false
99 97 end
100 98 end
101 99
102 100 # check if the user is enabled
103 101 unless @current_user.enabled? || @current_user.admin?
104 102 unauthorized_redirect 'Your account is disabled'
105 103 return false
106 104 end
107 105
108 106 # check if user ip is allowed
109 107 unless @current_user.admin? || GraderConfiguration[WHITELIST_IGNORE_CONF_KEY]
110 108 unless is_request_ip_allowed?
111 109 unauthorized_redirect 'Your IP is not allowed to login at this time.'
112 110 return false
113 111 end
114 112 end
115 113
116 114 if GraderConfiguration.multicontests?
117 115 return true if @current_user.admin?
118 116 begin
119 117 if @current_user.contest_stat(true).forced_logout
120 118 flash[:notice] = 'You have been automatically logged out.'
121 119 redirect_to :controller => 'main', :action => 'index'
122 120 end
123 121 rescue
124 122 end
125 123 end
126 124 return true
127 125 end
128 126
129 127 #redirect to root (and also force logout)
130 128 #if the user use different ip from the previous connection
131 129 # only applicable when MULTIPLE_IP_LOGIN options is false only
132 130 def authenticate_by_ip_address
133 131 #this assume that we have already authenticate normally
134 132 unless GraderConfiguration[MULTIPLE_IP_LOGIN_CONF_KEY]
135 133 user = User.find(session[:user_id])
136 134 if (!user.admin? && user.last_ip && user.last_ip != request.remote_ip)
137 135 flash[:notice] = "You cannot use the system from #{request.remote_ip}. Your last ip is #{user.last_ip}"
138 136 redirect_to :controller => 'main', :action => 'login'
139 137 return false
140 138 end
141 139 unless user.last_ip
142 140 user.last_ip = request.remote_ip
143 141 user.save
144 142 end
145 143 end
146 144 return true
147 145 end
148 146
149 147 def authorization
150 148 return false unless check_valid_login
151 149 user = User.find(session[:user_id])
152 150 unless user.roles.detect { |role|
153 151 role.rights.detect{ |right|
154 152 right.controller == self.class.controller_name and
155 153 (right.action == 'all' || right.action == action_name)
156 154 }
157 155 }
158 156 flash[:notice] = 'You are not authorized to view the page you requested'
159 157 #request.env['HTTP_REFERER'] ? (redirect_to :back) : (redirect_to :controller => 'login')
160 158 redirect_to :controller => 'main', :action => 'login'
161 159 return false
162 160 end
163 161 end
164 162
165 163 def verify_time_limit
166 164 return true if session[:user_id]==nil
167 165 user = User.find(session[:user_id], :include => :site)
168 166 return true if user==nil || user.site == nil
169 167 if user.contest_finished?
170 168 flash[:notice] = 'Error: the contest you are participating is over.'
171 169 redirect_to :back
172 170 return false
173 171 end
174 172 return true
175 173 end
176 174
177 175 def is_request_ip_allowed?
178 176 unless GraderConfiguration[WHITELIST_IGNORE_CONF_KEY]
179 177 user_ip = IPAddr.new(request.remote_ip)
180 178 allowed = GraderConfiguration[WHITELIST_IP_CONF_KEY] || ''
181 179
182 180 allowed.delete(' ').split(',').each do |ips|
183 181 allow_ips = IPAddr.new(ips)
184 182 if allow_ips.include?(user_ip)
185 183 return true
186 184 end
187 185 end
188 186 return false
189 187 end
190 188 return true
191 189 end
192 190
193 191 #function for datatable ajax query
194 192 #return record,total_count,filter_count
195 193 def process_query_record(record,
196 194 total_count: nil,
197 195 select: '',
198 196 global_search: [],
199 197 no_search: false,
200 198 force_order: '',
201 199 date_filter: '', date_param_since: 'date_since',date_param_until: 'date_until',
202 200 hard_limit: nil)
203 201 arel_table = record.model.arel_table
204 202
205 203 if !no_search && params['search']
206 204 global_value = record.model.sanitize_sql(params['search']['value'].strip.downcase)
207 205 if !global_value.blank?
208 206 global_value.split.each do |value|
209 207 global_where = global_search.map{|f| "LOWER(#{f}) like '%#{value}%'"}.join(' OR ')
210 208 record = record.where(global_where)
211 209 end
212 210 end
213 211
214 212 params['columns'].each do |i, col|
215 213 if !col['search']['value'].blank?
216 214 record = record.where(arel_table[col['name']].lower.matches("%#{col['search']['value'].strip.downcase}%"))
217 215 end
218 216 end
219 217 end
220 218
221 219 if !date_filter.blank?
222 220 param_since = params[date_param_since]
223 221 param_until = params[date_param_until]
224 222 date_since = Time.zone.parse( param_since ) || Time.new(1,1,1) rescue Time.new(1,1,1)
225 223 date_until = Time.zone.parse( param_until ) || Time.zone.now() rescue Time.zone.now()
226 224 date_range = date_since..(date_until.end_of_day)
227 225 record = record.where(date_filter.to_sym => date_range)
228 226 end
229 227
230 228 if force_order.blank?
231 229 if params['order']
232 230 params['order'].each do |i, o|
233 231 colName = params['columns'][o['column']]['name']
234 232 colName = "#{record.model.table_name}.#{colName}" if colName.upcase == 'ID'
235 233 record = record.order("#{colName} #{o['dir'].casecmp('desc') != 0 ? 'ASC' : 'DESC'}") unless colName.blank?
236 234 end
237 235 end
238 236 else
239 237 record = record.order(force_order)
240 238 end
241 239
242 240 filterCount = record.count(record.model.primary_key)
243 241 # if .group() is used, filterCount might be like {id_1: count_1, id_2: count_2, ...}
244 242 # so we should count the result again..
245 243 if filterCount.is_a? Hash
246 244 filterCount = filterCount.count
247 245 end
248 246
249 247
250 248 record = record.offset(params['start'] || 0)
251 249 record = record.limit(hard_limit)
252 250 if (params['length'])
253 251 limit = params['length'].to_i
254 252 limit == hard_limit if (hard_limit && hard_limit < limit)
255 253 record = record.limit(limit)
256 254 end
257 255 if (!select.blank?)
258 256 record = record.select(select)
259 257 end
260 258
261 259 return record, total_count || record.model.count, filterCount
262 260 end
263 261
264 262 end
Show file before | Show file after | Raw diff | Download diff | Ignore whitespace Increase context Show commentsHide comments
@@ -1,549 +1,552
1 1 require 'csv'
2 2
3 3 class ReportController < ApplicationController
4 4
5 5 before_action :check_valid_login
6 6
7 7 before_action :admin_authorization, only: [:login_stat,:submission, :submission_query,
8 8 :login, :login_detail_query, :login_summary_query,
9 9 :stuck, :cheat_report, :cheat_scrutinize, :show_max_score, :current_score]
10 10
11 11 before_action(only: [:problem_hof]) { |c|
12 12 return false unless check_valid_login
13 13
14 14 admin_authorization unless GraderConfiguration["right.user_view_submission"]
15 15 }
16 16
17 17 def max_score
18 18 end
19 19
20 20 def current_score
21 21 @problems = Problem.available_problems
22 22 if params[:group_id] && params[:users] == 'group'
23 23 @group = Group.find(params[:group_id])
24 24 @users = @group.users.where(enabled: true)
25 25 else
26 26 @users = User.includes(:contests).includes(:contest_stat).where(enabled: true)
27 27 end
28 28 @scorearray = calculate_max_score(@problems, @users,0,0,true)
29 29
30 30 #rencer accordingly
31 31 if params[:button] == 'download' then
32 32 csv = gen_csv_from_scorearray(@scorearray,@problems)
33 33 send_data csv, filename: 'max_score.csv'
34 34 else
35 35 #render template: 'user_admin/user_stat'
36 36 render 'current_score'
37 37 end
38 38 end
39 39
40 40 def show_max_score
41 41 #process parameters
42 42 #problems
43 43 @problems = []
44 44 if params[:problem_id]
45 45 params[:problem_id].each do |id|
46 46 next unless id.strip != ""
47 47 pid = Problem.find_by_id(id.to_i)
48 48 @problems << pid if pid
49 49 end
50 50 end
51 51
52 52 #users
53 53 @users = if params[:users] == "group" then
54 54 Group.find(params[:group_id]).users.all
55 55 elsif params[:users] == 'enabled'
56 56 User.includes(:contests).includes(:contest_stat).where(enabled: true)
57 57 else
58 58 User.includes(:contests).includes(:contest_stat)
59 59 end
60 60
61 61 #set up range from param
62 62 @since_id = params.fetch(:from_id, 0).to_i
63 63 @until_id = params.fetch(:to_id, 0).to_i
64 64 @since_id = nil if @since_id == 0
65 65 @until_id = nil if @until_id == 0
66 66
67 67 #calculate the routine
68 68 @scorearray = calculate_max_score(@problems, @users, @since_id, @until_id)
69 69
70 70 #rencer accordingly
71 71 if params[:button] == 'download' then
72 72 csv = gen_csv_from_scorearray(@scorearray,@problems)
73 73 send_data csv, filename: 'max_score.csv'
74 74 else
75 75 #render template: 'user_admin/user_stat'
76 76 render 'max_score'
77 77 end
78 78
79 79 end
80 80
81 81 def score
82 82 if params[:commit] == 'download csv'
83 83 @problems = Problem.all
84 84 else
85 85 @problems = Problem.available_problems
86 86 end
87 87 @users = User.includes(:contests, :contest_stat).where(enabled: true)
88 88 @scorearray = Array.new
89 89 @users.each do |u|
90 90 ustat = Array.new
91 91 ustat[0] = u
92 92 @problems.each do |p|
93 93 sub = Submission.find_last_by_user_and_problem(u.id,p.id)
94 94 if (sub!=nil) and (sub.points!=nil) and p and p.full_score
95 95 ustat << [(sub.points.to_f*100/p.full_score).round, (sub.points>=p.full_score)]
96 96 else
97 97 ustat << [0,false]
98 98 end
99 99 end
100 100 @scorearray << ustat
101 101 end
102 102 if params[:commit] == 'download csv' then
103 103 csv = gen_csv_from_scorearray(@scorearray,@problems)
104 104 send_data csv, filename: 'last_score.csv'
105 105 else
106 106 render template: 'user_admin/user_stat'
107 107 end
108 108
109 109 end
110 110
111 111 def login
112 112 end
113 113
114 114 def login_summary_query
115 115 @users = Array.new
116 116
117 117 date_and_time = '%Y-%m-%d %H:%M'
118 118 begin
119 119 md = params[:since_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
120 120 @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)
121 121 rescue
122 - @since_time = DateTime.new(1000,1,1)
122 + @since_time = Time.zone.now
123 123 end
124 + puts @since_time
124 125 begin
125 126 md = params[:until_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
126 127 @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)
127 128 rescue
128 129 @until_time = DateTime.new(3000,1,1)
129 130 end
130 131
131 132 record = User
132 133 .left_outer_joins(:logins).group('users.id')
133 134 .where("logins.created_at >= ? AND logins.created_at <= ?",@since_time, @until_time)
134 135 case params[:users]
135 136 when 'enabled'
136 137 record = record.where(enabled: true)
137 138 when 'group'
138 139 record = record.joins(:groups).where(groups: {id: params[:groups]}) if params[:groups]
139 140 end
140 141
141 142 record = record.pluck("users.id,users.login,users.full_name,count(logins.created_at),min(logins.created_at),max(logins.created_at)")
142 143 record.each do |user|
143 144 x = Login.where("user_id = ? AND created_at >= ? AND created_at <= ?",
144 145 user[0],@since_time,@until_time)
145 146 .pluck(:ip_address).uniq
147 + puts user[4]
148 + puts user[5]
146 149 @users << { id: user[0],
147 150 login: user[1],
148 151 full_name: user[2],
149 152 count: user[3],
150 - min: user[4],
151 - max: user[5],
153 + min: user[4].in_time_zone,
154 + max: user[5].in_time_zone,
152 155 ip: x
153 156 }
154 157 end
155 158 end
156 159
157 160 def login_detail_query
158 161 @logins = Array.new
159 162
160 163 date_and_time = '%Y-%m-%d %H:%M'
161 164 begin
162 165 md = params[:since_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
163 166 @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)
164 167 rescue
165 - @since_time = DateTime.new(1000,1,1)
168 + @since_time = Time.zone.now
166 169 end
167 170 begin
168 171 md = params[:until_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
169 172 @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)
170 173 rescue
171 174 @until_time = DateTime.new(3000,1,1)
172 175 end
173 176
174 177 @logins = Login.includes(:user).where("logins.created_at >= ? AND logins.created_at <= ?",@since_time, @until_time)
175 178 case params[:users]
176 179 when 'enabled'
177 180 @logins = @logins.where(users: {enabled: true})
178 181 when 'group'
179 182 @logins = @logins.joins(user: :groups).where(user: {groups: {id: params[:groups]}}) if params[:groups]
180 183 end
181 184 end
182 185
183 186 def submission
184 187 end
185 188
186 189 def submission_query
187 190 @submissions = Submission
188 191 .includes(:problem).includes(:user).includes(:language)
189 192
190 193 case params[:users]
191 194 when 'enabled'
192 195 @submissions = @submissions.where(users: {enabled: true})
193 196 when 'group'
194 197 @submissions = @submissions.joins(user: :groups).where(user: {groups: {id: params[:groups]}}) if params[:groups]
195 198 end
196 199
197 200 case params[:problems]
198 201 when 'enabled'
199 202 @submissions = @submissions.where(problems: {available: true})
200 203 when 'selected'
201 204 @submissions = @submissions.where(problem_id: params[:problem_id])
202 205 end
203 206
204 207 #set default
205 208 params[:since_datetime] = Date.today.to_s if params[:since_datetime].blank?
206 209
207 210 @submissions, @recordsTotal, @recordsFiltered = process_query_record( @submissions,
208 211 global_search: ['user.login','user.full_name','problem.name','problem.full_name','points'],
209 212 date_filter: 'submitted_at',
210 213 date_param_since: 'since_datetime',
211 214 date_param_until: 'until_datetime',
212 215 hard_limit: 100_000
213 216 )
214 217 end
215 218
216 219 def login
217 220 end
218 221
219 222 def problem_hof
220 223 # gen problem list
221 224 @user = User.find(session[:user_id])
222 225 @problems = @user.available_problems
223 226
224 227 # get selected problems or the default
225 228 if params[:id]
226 229 begin
227 230 @problem = Problem.available.find(params[:id])
228 231 rescue
229 232 redirect_to action: :problem_hof
230 233 flash[:notice] = 'Error: submissions for that problem are not viewable.'
231 234 return
232 235 end
233 236 end
234 237
235 238 return unless @problem
236 239
237 240 #model submisssion
238 241 @model_subs = Submission.where(problem: @problem,tag: Submission.tags[:model])
239 242
240 243
241 244 #calculate best submission
242 245 @by_lang = {} #aggregrate by language
243 246
244 247 range =65
245 248 #@histogram = { data: Array.new(range,0), summary: {} }
246 249 @summary = {count: 0, solve: 0, attempt: 0}
247 250 user = Hash.new(0)
248 251 Submission.where(problem_id: @problem.id).find_each do |sub|
249 252 #histogram
250 253 d = (DateTime.now.in_time_zone - sub.submitted_at) / 24 / 60 / 60
251 254 #@histogram[:data][d.to_i] += 1 if d < range
252 255
253 256 next unless sub.points
254 257 @summary[:count] += 1
255 258 user[sub.user_id] = [user[sub.user_id], (sub.points >= @problem.full_score) ? 1 : 0].max
256 259
257 260 lang = Language.find_by_id(sub.language_id)
258 261 next unless lang
259 262 next unless sub.points >= @problem.full_score
260 263
261 264 #initialize
262 265 unless @by_lang.has_key?(lang.pretty_name)
263 266 @by_lang[lang.pretty_name] = {
264 267 runtime: { avail: false, value: 2**30-1 },
265 268 memory: { avail: false, value: 2**30-1 },
266 269 length: { avail: false, value: 2**30-1 },
267 270 first: { avail: false, value: DateTime.new(3000,1,1) }
268 271 }
269 272 end
270 273
271 274 if sub.max_runtime and sub.max_runtime < @by_lang[lang.pretty_name][:runtime][:value]
272 275 @by_lang[lang.pretty_name][:runtime] = { avail: true, user_id: sub.user_id, value: sub.max_runtime, sub_id: sub.id }
273 276 end
274 277
275 278 if sub.peak_memory and sub.peak_memory < @by_lang[lang.pretty_name][:memory][:value]
276 279 @by_lang[lang.pretty_name][:memory] = { avail: true, user_id: sub.user_id, value: sub.peak_memory, sub_id: sub.id }
277 280 end
278 281
279 282 if sub.submitted_at and sub.submitted_at < @by_lang[lang.pretty_name][:first][:value] and sub.user and
280 283 !sub.user.admin?
281 284 @by_lang[lang.pretty_name][:first] = { avail: true, user_id: sub.user_id, value: sub.submitted_at, sub_id: sub.id }
282 285 end
283 286
284 287 if @by_lang[lang.pretty_name][:length][:value] > sub.effective_code_length
285 288 @by_lang[lang.pretty_name][:length] = { avail: true, user_id: sub.user_id, value: sub.effective_code_length, sub_id: sub.id }
286 289 end
287 290 end
288 291
289 292 #process user_id
290 293 @by_lang.each do |lang,prop|
291 294 prop.each do |k,v|
292 295 v[:user] = User.exists?(v[:user_id]) ? User.find(v[:user_id]).full_name : "(NULL)"
293 296 end
294 297 end
295 298
296 299 #sum into best
297 300 if @by_lang and @by_lang.first
298 301 @best = @by_lang.first[1].clone
299 302 @by_lang.each do |lang,prop|
300 303 if @best[:runtime][:value] >= prop[:runtime][:value]
301 304 @best[:runtime] = prop[:runtime]
302 305 @best[:runtime][:lang] = lang
303 306 end
304 307 if @best[:memory][:value] >= prop[:memory][:value]
305 308 @best[:memory] = prop[:memory]
306 309 @best[:memory][:lang] = lang
307 310 end
308 311 if @best[:length][:value] >= prop[:length][:value]
309 312 @best[:length] = prop[:length]
310 313 @best[:length][:lang] = lang
311 314 end
312 315 if @best[:first][:value] >= prop[:first][:value]
313 316 @best[:first] = prop[:first]
314 317 @best[:first][:lang] = lang
315 318 end
316 319 end
317 320 end
318 321
319 322 #@histogram[:summary][:max] = [@histogram[:data].max,1].max
320 323 @summary[:attempt] = user.count
321 324 user.each_value { |v| @summary[:solve] += 1 if v == 1 }
322 325
323 326
324 327 #for new graph
325 328 @chart_dataset = @problem.get_jschart_history.to_json.html_safe
326 329 end
327 330
328 331 def stuck #report struggling user,problem
329 332 # init
330 333 user,problem = nil
331 334 solve = true
332 335 tries = 0
333 336 @struggle = Array.new
334 337 record = {}
335 338 Submission.includes(:problem,:user).order(:problem_id,:user_id).find_each do |sub|
336 339 next unless sub.problem and sub.user
337 340 if user != sub.user_id or problem != sub.problem_id
338 341 @struggle << { user: record[:user], problem: record[:problem], tries: tries } unless solve
339 342 record = {user: sub.user, problem: sub.problem}
340 343 user,problem = sub.user_id, sub.problem_id
341 344 solve = false
342 345 tries = 0
343 346 end
344 347 if sub.points >= sub.problem.full_score
345 348 solve = true
346 349 else
347 350 tries += 1
348 351 end
349 352 end
350 353 @struggle.sort!{|a,b| b[:tries] <=> a[:tries] }
351 354 @struggle = @struggle[0..50]
352 355 end
353 356
354 357
355 358 def multiple_login
356 359 #user with multiple IP
357 360 raw = Submission.joins(:user).joins(:problem).where("problems.available != 0").group("login,ip_address").order(:login)
358 361 last,count = 0,0
359 362 first = 0
360 363 @users = []
361 364 raw.each do |r|
362 365 if last != r.user.login
363 366 count = 1
364 367 last = r.user.login
365 368 first = r
366 369 else
367 370 @users << first if count == 1
368 371 @users << r
369 372 count += 1
370 373 end
371 374 end
372 375
373 376 #IP with multiple user
374 377 raw = Submission.joins(:user).joins(:problem).where("problems.available != 0").group("login,ip_address").order(:ip_address)
375 378 last,count = 0,0
376 379 first = 0
377 380 @ip = []
378 381 raw.each do |r|
379 382 if last != r.ip_address
380 383 count = 1
381 384 last = r.ip_address
382 385 first = r
383 386 else
384 387 @ip << first if count == 1
385 388 @ip << r
386 389 count += 1
387 390 end
388 391 end
389 392 end
390 393
391 394 def cheat_report
392 395 date_and_time = '%Y-%m-%d %H:%M'
393 396 begin
394 397 md = params[:since_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
395 398 @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)
396 399 rescue
397 400 @since_time = Time.zone.now.ago( 90.minutes)
398 401 end
399 402 begin
400 403 md = params[:until_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
401 404 @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)
402 405 rescue
403 406 @until_time = Time.zone.now
404 407 end
405 408
406 409 #multi login
407 410 @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")
408 411
409 412 st = <<-SQL
410 413 SELECT l2.*
411 414 FROM logins l2 INNER JOIN
412 415 (SELECT u.id,COUNT(DISTINCT ip_address) as count,u.login,u.full_name
413 416 FROM logins l
414 417 INNER JOIN users u ON l.user_id = u.id
415 418 WHERE l.created_at >= '#{@since_time.in_time_zone("UTC")}' and l.created_at <= '#{@until_time.in_time_zone("UTC")}'
416 419 GROUP BY u.id
417 420 HAVING count > 1
418 421 ) ml ON l2.user_id = ml.id
419 422 WHERE l2.created_at >= '#{@since_time.in_time_zone("UTC")}' and l2.created_at <= '#{@until_time.in_time_zone("UTC")}'
420 423 UNION
421 424 SELECT l2.*
422 425 FROM logins l2 INNER JOIN
423 426 (SELECT l.ip_address,COUNT(DISTINCT u.id) as count
424 427 FROM logins l
425 428 INNER JOIN users u ON l.user_id = u.id
426 429 WHERE l.created_at >= '#{@since_time.in_time_zone("UTC")}' and l.created_at <= '#{@until_time.in_time_zone("UTC")}'
427 430 GROUP BY l.ip_address
428 431 HAVING count > 1
429 432 ) ml on ml.ip_address = l2.ip_address
430 433 INNER JOIN users u ON l2.user_id = u.id
431 434 WHERE l2.created_at >= '#{@since_time.in_time_zone("UTC")}' and l2.created_at <= '#{@until_time.in_time_zone("UTC")}'
432 435 ORDER BY ip_address,created_at
433 436 SQL
434 437 @mld = Login.find_by_sql(st)
435 438
436 439 st = <<-SQL
437 440 SELECT s.id,s.user_id,s.ip_address,s.submitted_at,s.problem_id
438 441 FROM submissions s INNER JOIN
439 442 (SELECT u.id,COUNT(DISTINCT ip_address) as count,u.login,u.full_name
440 443 FROM logins l
441 444 INNER JOIN users u ON l.user_id = u.id
442 445 WHERE l.created_at >= ? and l.created_at <= ?
443 446 GROUP BY u.id
444 447 HAVING count > 1
445 448 ) ml ON s.user_id = ml.id
446 449 WHERE s.submitted_at >= ? and s.submitted_at <= ?
447 450 UNION
448 451 SELECT s.id,s.user_id,s.ip_address,s.submitted_at,s.problem_id
449 452 FROM submissions s INNER JOIN
450 453 (SELECT l.ip_address,COUNT(DISTINCT u.id) as count
451 454 FROM logins l
452 455 INNER JOIN users u ON l.user_id = u.id
453 456 WHERE l.created_at >= ? and l.created_at <= ?
454 457 GROUP BY l.ip_address
455 458 HAVING count > 1
456 459 ) ml on ml.ip_address = s.ip_address
457 460 WHERE s.submitted_at >= ? and s.submitted_at <= ?
458 461 ORDER BY ip_address,submitted_at
459 462 SQL
460 463 @subs = Submission.joins(:problem).find_by_sql([st,@since_time,@until_time,
461 464 @since_time,@until_time,
462 465 @since_time,@until_time,
463 466 @since_time,@until_time])
464 467
465 468 end
466 469
467 470 def cheat_scrutinize
468 471 #convert date & time
469 472 date_and_time = '%Y-%m-%d %H:%M'
470 473 begin
471 474 md = params[:since_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
472 475 @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)
473 476 rescue
474 477 @since_time = Time.zone.now.ago( 90.minutes)
475 478 end
476 479 begin
477 480 md = params[:until_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
478 481 @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)
479 482 rescue
480 483 @until_time = Time.zone.now
481 484 end
482 485
483 486 #convert sid
484 487 @sid = params[:SID].split(/[,\s]/) if params[:SID]
485 488 unless @sid and @sid.size > 0
486 489 return
487 490 redirect_to actoin: :cheat_scrutinize
488 491 flash[:notice] = 'Please enter at least 1 student id'
489 492 end
490 493 mark = Array.new(@sid.size,'?')
491 494 condition = "(u.login = " + mark.join(' OR u.login = ') + ')'
492 495
493 496 @st = <<-SQL
494 497 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
495 498 FROM logins l INNER JOIN users u on l.user_id = u.id
496 499 WHERE l.created_at >= ? AND l.created_at <= ? AND #{condition}
497 500 UNION
498 501 SELECT s.submitted_at,s.id,u.login,u.full_name,s.ip_address,s.problem_id,s.points,s.user_id
499 502 FROM submissions s INNER JOIN users u ON s.user_id = u.id
500 503 WHERE s.submitted_at >= ? AND s.submitted_at <= ? AND #{condition}
501 504 ORDER BY submitted_at
502 505 SQL
503 506
504 507 p = [@st,@since_time,@until_time] + @sid + [@since_time,@until_time] + @sid
505 508 @logs = Submission.joins(:problem).find_by_sql(p)
506 509
507 510
508 511
509 512
510 513
511 514 end
512 515
513 516 protected
514 517
515 518 def calculate_max_score(problems, users,since_id,until_id, get_last_score = false)
516 519 #scorearray[i] = user #i's user stat where i is the index (not id)
517 520 scorearray = Array.new
518 521 users.each do |u|
519 522 ustat = Array.new
520 523 ustat[0] = u
521 524 problems.each do |p|
522 525 unless get_last_score
523 526 #get max score
524 527 max_points = 0
525 528 Submission.find_in_range_by_user_and_problem(u.id,p.id,since_id,until_id).each do |sub|
526 529 max_points = sub.points if sub and sub.points and (sub.points > max_points)
527 530 end
528 531 ustat << [(max_points.to_f*100/p.full_score).round, (max_points>=p.full_score)]
529 532 else
530 533 #get latest score
531 534 sub = Submission.find_last_by_user_and_problem(u.id,p.id)
532 535 if (sub!=nil) and (sub.points!=nil) and p and p.full_score
533 536 ustat << [(sub.points.to_f*100/p.full_score).round, (sub.points>=p.full_score)]
534 537 else
535 538 ustat << [0,false]
536 539 end
537 540 end
538 541 end
539 542 scorearray << ustat
540 543 end
541 544 return scorearray
542 545 end
543 546
544 547 def gen_csv_from_scorearray(scorearray,problem)
545 548 CSV.generate do |csv|
546 549 #add header
547 550 header = ['User','Name', 'Activated?', 'Logged in', 'Contest']
548 551 problem.each { |p| header << p.name }
549 552 header += ['Total','Passed']
You need to be logged in to leave comments. Login now