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

		@@ -253,6 +253,96
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)
	268	+ end
	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")
256	345	end
257	346
	347	+
258	348	end

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

                     %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

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