cgi-bin/solpeople/admin/stats-new.pl

   1 #!/usr/bin/perl -w
   2 use strict;
   3 use CXGN::Page;
   4 use CXGN::Login;
   5 use CXGN::Contact;
   6 use CXGN::People;
   7
   8 my $page=CXGN::Page->new("SGN Stats","johnathon");
   9 my $logged_in_person_id=CXGN::Login->new()->verify_session();
  10 my $logged_in_user=CXGN::People::Person->new($logged_in_person_id);
  11 my $logged_in_person_id=$logged_in_user->get_sp_person_id();
  12 my $logged_in_username=$logged_in_user->get_first_name()." ".$logged_in_user->get_last_name();
  13 my $logged_in_user_type=$logged_in_user->get_user_type();
  14 my $dbh = CXGN::DB::Connection->new("sgn_people");
  15 my $user_table;
  16 my @column_names_arr;
  17 my @table_columns = ("password", "last_access_time", "last_name");
  18 my ($time_to_check) = $page->get_encoded_arguments("hours");
  19 #print STDERR "Time: '$time_to_check'\n";
  20 if($logged_in_user_type eq 'curator'){
  21     find_recently_active_users();
  22     my $column_namesref = find_sp_person_column_names();
  23     foreach my $column(@$column_namesref){
  24         push @column_names_arr, @$column[0];
  25     }
  26
  27 }
  28
  29 else
  30 {
  31    $page->message_page('Sorry, but you are not authorized to view statistics.');
  32 }
  33
  34 #Finds the columns in the sp_person table.
  35 sub find_sp_person_column_names{
  36     my $dbh2 = CXGN::DB::Connection->new();
  37     my $sth = $dbh2->prepare("select column_name from information_schema.columns where table_schema = 'sgn_people' and table_name = 'sp_person'");
  38     $sth->execute();
  39     return $sth->fetchall_arrayref;
  40 }
  41
  42 #Change the epoch time into minutes
  43 sub epoch_to_minutes{
  44     my $people_logged_in = $_[0];
  45     for my $person(@$people_logged_in){
  46         $person->[2] = int($person->[2]/60);
  47     }
  48     return $people_logged_in;
  49 }
  50
  51 #Query the database for recently active users and then generate a table with their username, id, minutes since last action, and timestamp.
  52 sub find_recently_active_users{
  53     if($time_to_check <= 0){
  54         $time_to_check = 24;#THIS IS THE DEFAULT TIME
  55     }
  56     my $people_logged_in;
  57     $people_logged_in = $dbh->selectall_arrayref("select sp_person_id, username, extract(epoch from current_timestamp - last_access_time), last_access_time from sp_person where extract(epoch from current_timestamp - last_access_time) <= ? order by last_access_time DESC", undef, $time_to_check*60*60);
  58     $user_table = "<table align='left' style='width: 100%'><tr><th>ID:</th><th>Username:</th><th>Mins since last action:</th><th>Timestamp:</th></tr><tr><td>";
  59     $people_logged_in = epoch_to_minutes($people_logged_in);
  60     for my $row(@$people_logged_in){
  61         $user_table .="<tr><td align='center'>";
  62         for my $cell(@$row){
  63             $user_table .="$cell</td><td align='center'>";
  64         }
  65         $user_table .= "</tr>";
  66     }
  67     $user_table .="</table>";
  68 }
  69
  70
  71 #HTML Printing Start Here
  72 $page->header("SGN Stats", "SGN Stats");
  73 print <<html;
  74
  75 <script type='text/javascript' language='javascript'>
  76     function lookup_person(id){
  77         if(id > 0){
  78             if(window.XMLHttpRequest){
  79                 http_request = new XMLHttpRequest();
  80             }
  81             else if(window.ActiveXObject){
  82                 http_request = new ActiveXObject("Microsoft.XMLHTTP");
  83             }
  84             if (!http_request) {
  85                 alert('Giving up :( Cannot create an XMLHTTP instance');
  86             return false;
  87             }
  88             http_request.onreadystatechange = function() { writeData(http_request); };
  89             http_request.open('GET', 'get_person.pl?id='+id, true);
  90             http_request.send("id="+id);
  91         }
  92         function writeData(http_request){
  93             if(http_request.readyState == 4){
  94                 if(http_request.status == 200){
  95                     var table = document.getElementById("idnumbers");
  96                     table.innerHTML += http_request.responseText;
  97                 }
  98                 else{
  99                     alert("Error " + http_request.status);
 100             }
 101             }
 102         }
 103     }
 104 </script>
 105 <br />
 106 <form method="get" action="stats.pl" name="time" id="time"><label for="minutes">Time restraint (in hours): &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</label>
 107 <input type="text" size="5" name="hours" id="hours" value="$time_to_check" />
 108 <input type="submit" id="submit" value="Submit" /></form>
 109 <script type="text/javascript" language="javascript">document.time.hours.focus();</script>
 110 $user_table
 111 <table align='left' style='width:100%'><tr><th>ID</th><th>Username</th><th>$table_columns[0]</th><th>$table_columns[1]</th><th>$table_columns[2]</th></tr>
 112 <tr><td><input type='text' size='5' name='id1' id='id1' /></td></tr></table>
 113
 114 html
 115
 116 $page->footer();
 117
 118
 119 #<form id="lookup" name="lookup" action="javascript:lookup_person(document.lookup.number.value)">
 120 #<span id="request" name="request"
 121 #    style="cursor: pointer; text-decoration: underline"
 122 #    onclick="javascript:lookup_person(document.lookup.number.value)">
 123 #        Obtain user info on id number:
 124 #</span>
 125 #<input type="text" size="5" name="number" />
 126 #<input type="submit" id="sumbitnum" value="Submit" />
 127 #</form>