# HG changeset patch
# User Nattee Niparnan <nattee@gmail.com>
# Date 2015-05-04 03:36:46
# Node ID c673d73fd0d3c31a4823a78ddcc584dbb8e9b4e8
# Parent  b253db246113aaa8001a936e48f1c633c183d21c

add cheat report prototype

diff --git a/app/controllers/report_controller.rb b/app/controllers/report_controller.rb
--- a/app/controllers/report_controller.rb
+++ b/app/controllers/report_controller.rb
@@ -253,6 +253,96 @@
   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
diff --git a/app/views/report/_date_range.html.haml b/app/views/report/_date_range.html.haml
--- a/app/views/report/_date_range.html.haml
+++ b/app/views/report/_date_range.html.haml
@@ -7,11 +7,11 @@
       %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
diff --git a/app/views/report/cheat_report.html.haml b/app/views/report/cheat_report.html.haml
new file mode 100644
--- /dev/null
+++ b/app/views/report/cheat_report.html.haml
@@ -0,0 +1,77 @@
+- content_for :header do
+  = stylesheet_link_tag 'tablesorter-theme.cafe'
+  = javascript_include_tag 'local_jquery'
+
+%script{:type=>"text/javascript"}
+  $(function () {
+  $('#since_datetime').datetimepicker({ showButtonPanel: true, dateFormat: "yy-mm-dd", controlType: "slider"} );
+  $('#until_datetime').datetimepicker({ showButtonPanel: true, dateFormat: "yy-mm-dd", controlType: "slider"} );
+  $('#my_table').tablesorter({widthFixed: true, widgets: ['zebra']});
+  $('#my_table2').tablesorter({widthFixed: true, widgets: ['zebra']});
+  $('#sub_table').tablesorter({widthFixed: true, widgets: ['zebra']});
+  });
+
+%h1 Login status
+
+=render partial: 'report_menu'
+=render partial: 'date_range', locals: {param_text: 'Login date range:', title: 'Query login stat in the range'  }
+
+%h2 Suspect
+
+%table.tablesorter-cafe#my_table
+  %thead
+    %tr
+      %th login
+      %th full name
+      %th login count
+  %tbody
+    - @ml.each do |l|
+      %tr{class: cycle('info-even','info-odd')}
+        %td= link_to l[:login], controller: 'users', action: 'profile', id: l[:id]
+        %td= l[:full_name]
+        %td= l[:count]
+
+
+%h2 Multiple Logins Report
+This section reports all logins record that have either multiple ip per login or multiple login per ip.
+
+%table.tablesorter-cafe#my_table2
+  %thead
+    %tr
+      %th login
+      %th full name
+      %th IP
+      %th time
+  %tbody
+    - @mld.each do |l|
+      %tr{class: cycle('info-even','info-odd')}
+        %td= link_to l.user[:login], controller: 'users', action: 'profile', id: l[:user_id]
+        %td= l.user[:full_name]
+        %td= l[:ip_address]
+        %td= l[:created_at]
+
+%h2 Multiple IP Submissions Report
+This section reports all submission records that have USER_ID matchs ID that logins on multiple IP
+and that have IP_ADDRESS that has multiple ID logins
+
+Be noted that when submission IP address is not available, this might exclude
+submissions that come from ID that login on multiple-login IP
+
+%table.tablesorter-cafe#sub_table
+  %thead
+    %tr
+      %th login
+      %th full name
+      %th IP
+      %th problem
+      %th Submission
+      %th time
+  %tbody
+    - @subs.each do |s|
+      %tr{class: cycle('info-even','info-odd')}
+        %td= link_to s.user[:login], controller: 'users', action: 'profile', id: s[:user_id]
+        %td= s.user[:full_name]
+        %td= s[:ip_address]
+        %td= s.problem.name
+        %td= link_to(s.id, controller: 'graders' , action: 'submission', id: s.id)
+        %td= s[:submitted_at]
diff --git a/db/migrate/20150503164846_change_userid_on_login.rb b/db/migrate/20150503164846_change_userid_on_login.rb
new file mode 100644
--- /dev/null
+++ b/db/migrate/20150503164846_change_userid_on_login.rb
@@ -0,0 +1,9 @@
+class ChangeUseridOnLogin < ActiveRecord::Migration
+  def up
+    change_column :logins, :user_id, :integer
+  end
+
+  def down
+    change_column :logins, :user_id, :string
+  end
+end