cafe-grader-web Commit - r506:c673d73fd0d3

cafe-grader-web

Commit `r506:c673d73fd0d3` public

parent | child

Description:

add cheat report prototype

Commit status:

[Not Reviewed]

References:

algo

Diff options:

| | | |

Comments:

0 Commit comments 0 Inline Comments

Unresolved TODOs:

There are no unresolved TODOs

Add another comment

r506:c673d73fd0d3 - Mon, 04 May 2015 03:36:46 - 4 files changed: 178 inserted, 2 deleted

app/views/report/cheat_report.html.haml created 644 ¶ +77

@@ -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]

db/migrate/20150503164846_change_userid_on_login.rb created 644 ¶ +9

@@ -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

app/controllers/report_controller.rb ¶ +90

                   end
                   @submissions = {}
                   User.find_each do |user|
                     @submissions[user.id] = { login: user.login, full_name: user.full_name, count: 0, sub: { } }
                   end
                   Submission.where("submitted_at >= ? AND submitted_at <= ?",@since_time,@until_time).find_each do |s|
                     if @submissions[s.user_id]
                       if not @submissions[s.user_id][:sub].has_key?(s.problem_id)
                         a = nil
                         begin
                           a = Problem.find(s.problem_id)
                         rescue
                           a = nil
                         end
                         @submissions[s.user_id][:sub][s.problem_id] =
                           { prob_name: (a ? a.full_name : '(NULL)'),
                             sub_ids: [s.id] }
                       else
                         @submissions[s.user_id][:sub][s.problem_id][:sub_ids] << s.id
                       end
                       @submissions[s.user_id][:count] += 1
                     end
                   end
                 end
                 def problem_hof
                   # gen problem list
                   @user = User.find(session[:user_id])
                   @problems = @user.available_problems
                   # get selected problems or the default
                   if params[:id]
                     begin
                       @problem = Problem.available.find(params[:id])
                     rescue
                       redirect_to action: :problem_hof
                       flash[:notice] = 'Error: submissions for that problem are not viewable.'
                       return
                     end
                   end
                   return unless @problem
                   @by_lang = {} #aggregrate by language
                   range =65
                   @histogram = { data: Array.new(range,0), summary: {} }
                   @summary = {count: 0, solve: 0, attempt: 0}
                   user = Hash.new(0)
                   Submission.where(problem_id: @problem.id).find_each do |sub|
                     #histogram
                     d = (DateTime.now.in_time_zone - sub.submitted_at) / 24 / 60 / 60
                     @histogram[:data][d.to_i] += 1 if d < range
                     next unless sub.points
                     @summary[:count] += 1
                     user[sub.user_id] = [user[sub.user_id], (sub.points >= @problem.full_score) ? 1 : 0].max
                     lang = Language.find_by_id(sub.language_id)
                     next unless lang
                     next unless sub.points >= @problem.full_score
                     #initialize
                     unless @by_lang.has_key?(lang.pretty_name)
                       @by_lang[lang.pretty_name] = {
                         runtime: { avail: false, value: 2**30-1 },
                         memory: { avail: false, value: 2**30-1 },
                         length: { avail: false, value: 2**30-1 },
                         first: { avail: false, value: DateTime.new(3000,1,1) }
                       }
                     end
                     if sub.max_runtime and sub.max_runtime < @by_lang[lang.pretty_name][:runtime][:value]
                       @by_lang[lang.pretty_name][:runtime] = { avail: true, user_id: sub.user_id, value: sub.max_runtime, sub_id: sub.id }
                     end
                     if sub.peak_memory and sub.peak_memory < @by_lang[lang.pretty_name][:memory][:value]
                       @by_lang[lang.pretty_name][:memory] = { avail: true, user_id: sub.user_id, value: sub.peak_memory, sub_id: sub.id }
                     end
                     if sub.submitted_at and sub.submitted_at < @by_lang[lang.pretty_name][:first][:value] and
                         !sub.user.admin?
                       @by_lang[lang.pretty_name][:first] = { avail: true, user_id: sub.user_id, value: sub.submitted_at, sub_id: sub.id }
                     end
                     if @by_lang[lang.pretty_name][:length][:value] > sub.effective_code_length
                       @by_lang[lang.pretty_name][:length] = { avail: true, user_id: sub.user_id, value: sub.effective_code_length, sub_id: sub.id }
                     end
                   end
                   #process user_id
                   @by_lang.each do |lang,prop|
                     prop.each do |k,v|
                       v[:user] = User.exists?(v[:user_id]) ? User.find(v[:user_id]).full_name : "(NULL)"
                     end
                   end
                   #sum into best
                   if @by_lang and @by_lang.first
                     @best = @by_lang.first[1].clone
                     @by_lang.each do |lang,prop|
                       if @best[:runtime][:value] >= prop[:runtime][:value]
                         @best[:runtime] = prop[:runtime]
                         @best[:runtime][:lang] = lang
                       end
                       if @best[:memory][:value] >= prop[:memory][:value]
                         @best[:memory] = prop[:memory]
                         @best[:memory][:lang] = lang
                       end
                       if @best[:length][:value] >= prop[:length][:value]
                         @best[:length] = prop[:length]
                         @best[:length][:lang] = lang
                       end
                       if @best[:first][:value] >= prop[:first][:value]
                         @best[:first] = prop[:first]
                         @best[:first][:lang] = lang
                       end
                     end
                   end
                   @histogram[:summary][:max] = [@histogram[:data].max,1].max
                   @summary[:attempt] = user.count
                   user.each_value { |v| @summary[:solve] += 1 if v == 1 }
                 end
                 def stuck #report struggling user,problem
                   # init
                   user,problem = nil
                   solve = true
                   tries = 0
                   @struggle = Array.new
                   record = {}
                   Submission.includes(:problem,:user).order(:problem_id,:user_id).find_each do |sub|
                     next unless sub.problem and sub.user
                     if user != sub.user_id or problem != sub.problem_id
                       @struggle << { user: record[:user], problem: record[:problem], tries: tries } unless solve
                       record = {user: sub.user, problem: sub.problem}
                       user,problem = sub.user_id, sub.problem_id
                       solve = false
                       tries = 0
                     end
                     if sub.points >= sub.problem.full_score
                       solve = true
                     else
                       tries += 1
                     end
                   end
                   @struggle.sort!{|a,b| b[:tries] <=> a[:tries] }
                   @struggle = @struggle[0..50]
                 end
                 def multiple_login
                   #user with multiple IP
                   raw = Submission.joins(:user).joins(:problem).where("problems.available != 0").group("login,ip_address").order(:login)
                   last,count = 0,0
                   first = 0
                   @users = []
                   raw.each do |r|
                     if last != r.user.login
                       count = 1
                       last = r.user.login
                       first = r
                     else
                       @users << first if count == 1
                       @users << r
                       count += 1
                     end
                   end
                   #IP with multiple user
                   raw = Submission.joins(:user).joins(:problem).where("problems.available != 0").group("login,ip_address").order(:ip_address)
                   last,count = 0,0
                   first = 0
                   @ip = []
                   raw.each do |r|
                     if last != r.ip_address
                       count = 1
                       last = r.ip_address
                       first = r
                     else
                       @ip << first if count == 1
                       @ip << r
                       count += 1
                     end
                   end
                 end
                 def cheat_report
+            +     date_and_time = '%Y-%m-%d %H:%M'
+            +     begin
+            +       md = params[:since_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
+            +       @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)
+            +     rescue
+            +       @since_time = Time.zone.now
+            +     end
+            +     begin
+            +       md = params[:until_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
+            +       @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)
+            +     rescue
+            +       @until_time = Time.zone.now.ago( 90.minutes)
                   end
+            +     #multi login
+            +     @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")
+            +
+            +     st = <<-SQL
+            +   SELECT l2.*
+            +     FROM logins l2 INNER JOIN
+            +     (SELECT u.id,COUNT(DISTINCT ip_address) as count,u.login,u.full_name
+            +       FROM logins l
+            +       INNER JOIN users u ON l.user_id =  u.id
+            +       WHERE l.created_at >= '#{@since_time.in_time_zone("UTC")}' and l.created_at <= '#{@until_time.in_time_zone("UTC")}'
+            +       GROUP BY u.id
+            +       HAVING count > 1
+            +     ) ml ON l2.user_id = ml.id
+            +     WHERE l2.created_at >= '#{@since_time.in_time_zone("UTC")}' and l2.created_at <= '#{@until_time.in_time_zone("UTC")}'
+            + UNION
+            +   SELECT l2.*
+            +     FROM logins l2 INNER JOIN
+            +     (SELECT l.ip_address,COUNT(DISTINCT u.id) as count
+            +       FROM logins l
+            +       INNER JOIN users u ON l.user_id =  u.id
+            +       WHERE l.created_at >= '#{@since_time.in_time_zone("UTC")}' and l.created_at <= '#{@until_time.in_time_zone("UTC")}'
+            +       GROUP BY l.ip_address
+            +       HAVING count > 1
+            +     ) ml on ml.ip_address = l2.ip_address
+            +     INNER JOIN users u ON l2.user_id = u.id
+            +     WHERE l2.created_at >= '#{@since_time.in_time_zone("UTC")}' and l2.created_at <= '#{@until_time.in_time_zone("UTC")}'
+            + ORDER BY ip_address,created_at
+            +               SQL
+            +     @mld = Login.find_by_sql(st)
+            +
+            +     st = <<-SQL
+            +   SELECT s.id,s.user_id,s.ip_address,s.submitted_at,s.problem_id
+            +     FROM submissions s INNER JOIN
+            +     (SELECT u.id,COUNT(DISTINCT ip_address) as count,u.login,u.full_name
+            +       FROM logins l
+            +       INNER JOIN users u ON l.user_id =  u.id
+            +       WHERE l.created_at >= ? and l.created_at <= ?
+            +       GROUP BY u.id
+            +       HAVING count > 1
+            +     ) ml ON s.user_id = ml.id
+            +     WHERE s.submitted_at >= ? and s.submitted_at <= ?
+            + UNION
+            +   SELECT s.id,s.user_id,s.ip_address,s.submitted_at,s.problem_id
+            +     FROM submissions s INNER JOIN
+            +     (SELECT l.ip_address,COUNT(DISTINCT u.id) as count
+            +       FROM logins l
+            +       INNER JOIN users u ON l.user_id =  u.id
+            +       WHERE l.created_at >= ? and l.created_at <= ?
+            +       GROUP BY l.ip_address
+            +       HAVING count > 1
+            +     ) ml on ml.ip_address = s.ip_address
+            +     WHERE s.submitted_at >= ? and s.submitted_at <= ?
+            + ORDER BY ip_address,submitted_at
+            +             SQL
+            +     @subs = Submission.joins(:problem).find_by_sql([st,@since_time,@until_time,
+            +                                        @since_time,@until_time,
+            +                                        @since_time,@until_time,
+            +                                        @since_time,@until_time])
+            +
+            + #  st =
+            + #      " INNER JOIN" +
+            + #      "(SELECT u.id,l.ip_address,COUNT(DISTINCT ip_address) as count " +
+            + #      " FROM logins l INNER JOIN users u ON l.user_id = u.id "+
+            + #      " WHERE l.created_at >= '#{@since_time.in_time_zone("UTC")}' and l.created_at <= '#{@until_time.in_time_zone("UTC")}' " +
+            + #      " GROUP BY u.id" +
+            + #      " HAVING count > 1) ml "
+            + #  #ml detail
+            + #  @mld = Login.joins(st + "ON logins.user_id = ml.id").
+            + #    where("logins.created_at >= ? and logins.created_at <= ?",@since_time,@until_time).
+            + #    order("ip_address")
+            + #
+            + #  #submissions
+            + #  @subs =  Submission.joins(:problem).joins(st + "ON submissions.user_id = ml.id").
+            + #    where("submissions.submitted_at >= ? and submissions.submitted_at <= ?",@since_time,@until_time).
+            + #    order("submissions.ip_address")
                 end
+            +
+            +
+            + end

app/views/report/_date_range.html.haml ¶ +2 -2

               = form_tag({session: :url })  do
                 .submitbox
                   %table
                     %tr
                       %td{colspan: 6, style: 'font-weight: bold'}= title
                     %tr
                       %td{style: 'width: 120px; font-weight: bold'}= param_text
                       %td{align: 'right'} since:
-            -         %td= text_field_tag 'since_datetime'
+            +         %td= text_field_tag 'since_datetime', @since_time
                     %tr
                       %td
                       %td{align: 'right'} until:
-            -         %td= text_field_tag 'until_datetime'
+            +         %td= text_field_tag 'until_datetime', @until_time
                     %tr
                       %td
                       %td
                       %td Blank mean no condition
                     %tr
                       %td
                       %td
                       %td= submit_tag 'query'

Write
Preview

You need to be logged in to leave comments. Login now

	Site-wide shortcuts
/	Use quick search box
g h	Goto home page
g g	Goto my private gists page
g G	Goto my public gists page
n r	New repository page
n g	New gist page

	Repositories
g s	Goto summary page
g c	Goto changelog page
g f	Goto files page
g F	Goto files page with file search activated
g p	Goto pull requests page
g o	Goto repository settings
g O	Goto repository permissions settings

Sign in to your account

Author

r506:c673d73fd0d3 - Mon, 04 May 2015 03:36:46 - 4 files changed: 178 inserted, 2 deleted

Sign in to your account

Commit r506:c673d73fd0d3 public

Author

r506:c673d73fd0d3 - Mon, 04 May 2015 03:36:46 - 4 files changed: 178 inserted, 2 deleted

Commit `r506:c673d73fd0d3` public