mods/_standard/tracker/lib/tracker_stats.inc.php

   1 <?php
   2 /****************************************************************/
   3 /* ATutor                                                                                                               */
   4 /****************************************************************/
   5 /* Copyright (c) 2002-2010                                      */
   6 /* Inclusive Design Institute                                   */
   7 /* http://atutor.ca                                                                                             */
   8 /*                                                              */
   9 /* This program is free software. You can redistribute it and/or*/
  10 /* modify it under the terms of the GNU General Public License  */
  11 /* as published by the Free Software Foundation.                                */
  12 /****************************************************************/
  13 /////////////////////////////
  14 //Display the g_data bar chart for the member selected
  15 if (!defined('AT_INCLUDE_PATH')) { exit; }
  16
  17 // NOTE: this script should not be altered. its use will soon be deprecated.
  18
  19
  20 //get the summary data for all pages
  21
  22 //get the translations for the g numbers
  23 $to_cid = $_GET['to_cid'];
  24 $stats = $_GET['stats'];
  25 $g_id = $_GET['g_id'];
  26 $sql5 = "select * from ".TABLE_PREFIX."g_refs";
  27         $result = mysql_query($sql5, $db);
  28         $refs = array();
  29         while ($row= mysql_fetch_array($result)) {
  30                 $refs[$row['g_id']] = $row['reference'];
  31         }
  32
  33
  34 //get the g translation for non content pages
  35 $sql8= "select
  36                 G.g,
  37                 R.reference,
  38                 R.g_id
  39         from
  40                 ".TABLE_PREFIX."g_click_data G,
  41                 ".TABLE_PREFIX."g_refs R
  42         where
  43                 G.g = R.g_id
  44                 AND
  45                 course_id='$_SESSION[course_id]'";
  46
  47         if(!$result8 = mysql_query($sql8, $db)){
  48                 require(AT_INCLUDE_PATH.'footer.inc.php');
  49                 exit;
  50         }else{
  51
  52                 $title_refs = array();
  53                 while ($row= mysql_fetch_assoc($result8)) {
  54                         $title_refs2[$row['g']] = $row['reference'];
  55
  56                 }
  57         }
  58 //get the translations for the content id numbers
  59 $sql7 = "select
  60                         C.title,
  61                         C.content_id
  62
  63                 from
  64                         ".TABLE_PREFIX."content C
  65
  66                 where
  67                         course_id='$_SESSION[course_id]'";
  68         if(!$result7 = mysql_query($sql7, $db)){
  69                 require(AT_INCLUDE_PATH.'footer.inc.php');
  70                 exit;
  71         }
  72         $title_refs = array();
  73         while ($row= mysql_fetch_array($result7)) {
  74                 $title_refs[$row['content_id']] = $row['title'];
  75
  76         }
  77
  78 //get tools ATutor tools traffic
  79
  80 $sql9="SELECT
  81                         G.to_cid,
  82                         G.g,
  83                         R.g_id,
  84                         R.reference
  85
  86                 from
  87                         ".TABLE_PREFIX."g_click_data G,
  88                         ".TABLE_PREFIX."g_refs R
  89                 where
  90                         G.to_cid = 0
  91                         AND
  92                         course_id='$_SESSION[course_id]'";
  93         $title_tools = array();
  94         $result9 = mysql_query($sql9, $db);
  95         while ($row= mysql_fetch_array($result9)) {
  96                         if($row['g'] == $row['g_id']){
  97                                 $title_tools[$row['g_id']] = $row['reference'];
  98                                 $tool_grefs[$row['g_id']] = $row['g_id'];
  99                                 $gcount[$row['g_id']]++;
 100                         }
 101         }
 102 $sql10 = "select count(g) from ".TABLE_PREFIX."g_click_data where course_id='$_SESSION[course_id]' GROUP BY g";
 103 $result10 = mysql_query($sql10, $db);
 104 while($row=mysql_fetch_array($result10)){
 105         $thiscount[]=$row;
 106
 107 }
 108
 109 if($_GET['stats']="summary" && !$to_cid &&!$_GET['csv'] && !$_GET['g_id']){
 110
 111         $sql12= "select to_cid, g, AVG(duration) AS t, count(g) as c from ".TABLE_PREFIX."g_click_data where to_cid='0' AND course_id='$_SESSION[course_id]' GROUP BY g";
 112
 113         if($result12=mysql_query($sql12, $db)){
 114                 while($row=mysql_fetch_array($result12)){
 115                         if($row['g']){
 116                                 $nav_total = ($nav_total + $row['c']);
 117                         }
 118                         if($row['to_cid']==0){
 119                                 $that_time[$row['g']]= $row['t'];
 120                         }
 121                 }
 122         }else{
 123                 echo _AT('unknown_error');
 124         }
 125 ?>
 126         <br />
 127         <a name="show_pages"></a>
 128         <h3><?php  echo  _AT('tool_summary'); ?></h3>
 129         <table class="data static" rules="cols" summary="">
 130         <thead>
 131         <tr>
 132                 <th><?php echo _AT('at_tools');     ?></th>
 133                 <th><?php echo _AT('hit_count');    ?></th>
 134                 <th><?php echo _AT('avg_duration'); ?></th>
 135                 <th><?php echo _AT('details');      ?></th>
 136         </tr>
 137         </thead>
 138
 139         <tbody>
 140 <?php
 141                 //this array needs to be created from the database
 142                 //(eventually add new field to g_refs table called "timed" values true/false
 143                 $timed_tools=array(14=>14, 15=>15, 16=>16, 17=>17, 18=>18, 20=>20, 21=>21, 23=>23, 27=>27, 28=>28, 29=>29, 31=>31, 32=>32, 35=>35);
 144
 145                 foreach($title_tools as $key=>$value) {
 146                         $tool_names[$key] = $gcount[$key];
 147                 }
 148
 149                 if (is_array($tool_names)) {
 150                         arsort($tool_names);
 151
 152                         foreach($tool_names as $key=>$value) {
 153                                 echo '<tr>';
 154                                         echo '<td>' . _AT($title_tools[$key]) . '</td>';
 155                                         echo '<td><img src="images/bar.gif" height="12" width="' . ((($gcount[$key]/$nav_total)*100)*2) . '" alt="" />' . $value . '</td>';
 156
 157                                 $that_avgtime='';
 158                                 if($timed_tools[$key]==$key) {
 159                                         $that_avgtime=number_format((number_format($that_time[$key], 1  )/$gcount[$key]),1);
 160                                 }
 161
 162                                         echo '<td>';
 163                                         if($that_avgtime) {
 164                                                 echo $that_avgtime;
 165                                         } else {
 166                                                 echo _AT('na');
 167                                         }
 168                                         echo '</td>';
 169
 170                                         echo '<td><a href="' . $_SERVER['PHP_SELF'] . '?g_id=' . $key . '#show_pages">' . _AT('details') . '</a></td>';
 171                                 echo '</tr>';
 172                         }
 173                 }
 174 ?>
 175         </tbody>
 176         </table>
 177
 178         <br /><br />
 179
 180         <h3><?php  echo  _AT('page_stats'); ?></h3>
 181         <table class="data static" rules="cols" summary="">
 182         <thead>
 183         <tr>
 184                 <th><?php echo _AT('page_title');   ?></th>
 185                 <th><?php echo _AT('hit_count');    ?></th>
 186                 <th><?php echo _AT('avg_duration'); ?></th>
 187                 <th><?php echo _AT('details');      ?></th>
 188         </tr>
 189         </thead>
 190 <?php
 191         //get content page traffic
 192         $sql6 = "SELECT G.to_cid, count(*) AS pages, G.g
 193                 FROM ".TABLE_PREFIX."g_click_data G
 194                 WHERE G.to_cid <> 0     AND     course_id='$_SESSION[course_id]'
 195                 GROUP BY G.to_cid";
 196
 197         $result6 = mysql_query($sql6, $db);
 198
 199         if(!$result6) {
 200                 echo "query failed";
 201                 require(AT_INCLUDE_PATH.'footer.inc.php');
 202                 exit;
 203         }
 204
 205         $sql11 = "SELECT to_cid, AVG(duration) AS t FROM ".TABLE_PREFIX."g_click_data WHERE course_id='$_SESSION[course_id]' GROUP BY to_cid";
 206         $result11 = mysql_query($sql11, $db);
 207
 208         if ($result11) {
 209                 while($row = mysql_fetch_array($result11)) {
 210                         $this_time[$row['to_cid']]= $row['t'];
 211                 }
 212         }
 213
 214         else {
 215                 echo _AT('unknown_error');
 216         }
 217
 218         $max_bar_width='180';
 219         $result9 = mysql_query($sql6, $db);
 220
 221         while($row = mysql_fetch_array($result9)) {
 222                 $total_hits=($total_hits + $row["pages"]);
 223         }
 224         if($total_hits) {
 225                 $bar_factor = ($max_bar_width/$total_hits);
 226         }
 227
 228         if ($result6 = mysql_query($sql6, $db)) {
 229
 230                 echo '<tbody>';
 231
 232                 while($row = mysql_fetch_array($result6)) {
 233                         if($title_refs[$row['to_cid']] != '') {
 234                                 echo '<tr>';
 235                                         echo '<td>' . $title_refs[$row['to_cid']] . '</td>';
 236                                         echo '<td><img src="images/bar.gif" height="12" width="' . ($row["pages"]*$bar_factor) . '" alt="" />' . $row["pages"] . '</td>';
 237
 238                                         $this_avgtime=(number_format($this_time[$row['to_cid']], 1  )/$row["pages"]);
 239
 240                                         echo '<td>' . number_format($this_avgtime, 1) . '</td>';
 241                                         echo '<td><a href="' . $_SERVER['PHP_SELF'] . '?stats=details' . SEP . 'to_cid=' . $row['to_cid'] . '#show_pages">' . _AT('details') . '</a></td>';
 242                                 echo '</tr>';
 243
 244                         }
 245                 }
 246                 echo '<tbody>';
 247         }
 248
 249         echo '</table>';
 250 }  //end summary
 251
 252 //get the rawdata for a single page
 253 if(authenticate(AT_PRIV_ADMIN, AT_PRIV_RETURN)){
 254         $sql3="select
 255                 ".TABLE_PREFIX."content.title,
 256                 ".TABLE_PREFIX."content.content_id,
 257                 ".TABLE_PREFIX."g_click_data.member_id as m,
 258                 ".TABLE_PREFIX."g_click_data.to_cid,
 259                 ".TABLE_PREFIX."g_click_data.g,
 260                 ".TABLE_PREFIX."g_click_data.timestamp AS t
 261         from
 262                 ".TABLE_PREFIX."content,
 263                 ".TABLE_PREFIX."g_click_data
 264         where
 265                 ".TABLE_PREFIX."content.content_id=".TABLE_PREFIX."g_click_data.to_cid
 266                 AND
 267                 ".TABLE_PREFIX."g_click_data.to_cid=$to_cid
 268                 AND
 269                 ".TABLE_PREFIX."g_click_data.course_id=$_SESSION[course_id]";
 270
 271
 272
 273         $result3=mysql_query($sql3, $db);
 274         if($result3){
 275                 while($row=mysql_fetch_array($result3)){
 276                         $this_data[$row["t"]]= $row;
 277                         $this_user[$row["t"]]= $row['m'];
 278                 }
 279                 ksort($this_data);
 280                 $current = current($this_data);
 281                 $pre_time = $current[t];
 282
 283         }
 284
 285 }
 286
 287
 288 if($to_cid) {
 289         ?>
 290         <a name="show_pages"></a>
 291         <p>
 292                 [<a href="<?php echo $_SERVER['PHP_SELF'].'?stats=summary';?>#show_pages"><?php echo _AT('back_to_summary'); ?></a>]
 293         </p>
 294
 295         <h3><?php echo _AT('access_stats'); ?>: <?php echo $current['title']; ?></h3>
 296
 297
 298         <table class="data static" rules="cols" summary="">
 299         <thead>
 300         <tr>
 301                 <th scope="col"><?php echo _AT('access_method'); ?></th>
 302                 <th scope="col"><?php echo _AT('count'); ?></th>
 303         </tr>
 304         </thead>
 305
 306 <?php
 307         //get the number of clicks per g
 308         $sql2 = "select
 309                         g,
 310                         count(*) AS cnt
 311                 from
 312                         ".TABLE_PREFIX."g_click_data
 313                 where
 314                         to_cid=$to_cid
 315                         AND
 316                         course_id='$_SESSION[course_id]'
 317                 group by
 318                          g";
 319
 320         if($result2 = mysql_query($sql2, $db)){
 321                 echo '<tbody>';
 322                 while($row = mysql_fetch_array($result2)){
 323                         echo '<tr>';
 324                         echo '<td>';
 325                         foreach($refs AS $key => $value){
 326                                 if($key==$row["g"]){
 327                                         echo _AT($value);
 328                                 }
 329                         }
 330                         echo '</td>';
 331                         echo '<td><img src="images/bar.gif" height="12" width="' . ($row["cnt"]*2) . '" alt="" />' . $row["cnt"] . '</td>';
 332                         echo '</tr>';
 333                 }
 334
 335         }
 336         echo '</tbody>';
 337         echo '</table>';
 338         echo '<br />';
 339
 340         //////////////
 341         $sql4="select
 342                 ".TABLE_PREFIX."g_click_data.g,
 343                 ".TABLE_PREFIX."g_click_data.member_id AS m,
 344                 ".TABLE_PREFIX."g_click_data.to_cid,
 345                 ".TABLE_PREFIX."g_click_data.timestamp AS t
 346         from
 347                 ".TABLE_PREFIX."g_click_data
 348         where
 349                 ".TABLE_PREFIX."g_click_data.to_cid=0
 350                 AND
 351                 ".TABLE_PREFIX."g_click_data.to_cid=$to_cid
 352                 AND
 353                 ".TABLE_PREFIX."g_click_data.course_id=$_SESSION[course_id]
 354                 GROUP BY
 355                 m
 356                 ";
 357         $result4 = mysql_query($sql4, $db);
 358
 359         if($result4){
 360
 361                 if($this_data){
 362                         echo '<br />';
 363                         echo '<a name="show_pages"></a>';
 364                         echo '<h3>'._AT('pages_stats', $current["title"]).'</h3>';
 365
 366                         echo '<table class="data static" rules="cols" summary="">';
 367                         echo '<thead>';
 368                         echo '<tr>';
 369                                 echo '<th scope="col">' . _AT('access_method') . '</th>';
 370                                 echo '<th scope="col">' . _AT('duration_sec')  . '</th>';
 371                                 echo '<th scope="col">' . _AT('date')          . '</th>';
 372                                 echo '<th scope="col">' . _AT('student_id')    . '</th>';
 373                         echo '</tr>';
 374                         echo '<thead>';
 375                         echo '<tbody>';
 376                         foreach($this_data AS $key => $value){
 377                                 if(!$start_date){
 378                                         $start_date=$pre_time;
 379                                 }
 380                                 $diff = abs($value[t] - $pre_time);
 381                                 if ($diff > 60*45) {
 382                                         $end_date=$value[t];
 383                                         echo '<tr>';
 384                                         echo '<td>';
 385                                         if($start_date>0 && $start_date!=$pre_time){
 386                                                 echo _AT('session_start').' '.date("F j, Y,  g:i a", $start_date).' '._AT('session_end').' '.date("F j, Y,  g:i a", $pre_time).'     ('._AT('duration').':'.date('i \m\i\n s \s\e\c',($pre_time-$start_date)).')';
 387
 388                                                 echo '</td>';
 389                                                 echo '</tr>';
 390                                         }
 391                                         else if($value[g]==19) {
 392                                                 //don't do anything if its a logout
 393                                         }
 394                                         else {
 395                                                 echo _AT('invalid_session');
 396                                         }
 397                                         $start_date='';
 398                                 }
 399
 400                                 else {
 401                                         if (!$start_date) {
 402                                                 $start_date=$value[t];
 403                                         }
 404                                 }
 405                                 echo '<tr>';
 406                                 echo '<td>';
 407                                 $that_g=$refs[$value['g']];
 408                                 echo _AT($that_g);
 409                                 echo '</td>';
 410                                 echo '<td>';
 411
 412                                 if ($diff > 60*45) {
 413                                         echo _AT('na');
 414                                         $session_time='';
 415
 416                                 }else{
 417                                         $this_time=date('i.s', $diff);
 418                                         echo ' '.$this_time;
 419                                         $session_time=($session_time+$diff);
 420                                 }
 421                                 $remainder = $diff / 60;
 422                                 echo '</td>';
 423                                 echo '<td>';
 424                                 echo $that_date;
 425                                 echo '</td>';
 426                                 echo '<td>'.$this_user[$value['m']].'</td>';
 427                                 echo '</tr>';
 428                                 $that_date=date("M-j-y g:i:s:a", $value[t]);
 429                                 $that_title=$value[title]."&nbsp;";
 430                                 $pre_time = $value['t'];
 431                         }
 432                         echo '</tbody>';
 433                         echo '</table>';
 434                 }
 435         }
 436 }  /// end page detail
 437
 438 if($_GET['g_id']){
 439         $sql14 = "select member_id, login, first_name, last_name from ".TABLE_PREFIX."members";
 440         $result14=mysql_query($sql14, $db);
 441         while($row=mysql_fetch_array($result14)){
 442                 if($row['first_name'] && $row['last_name']){
 443                         $this_user[$row['member_id']]= $row['first_name'].' '. $row['last_name'];
 444                 }else{
 445                         $this_user[$row['member_id']]= $row['login'];
 446                 }
 447
 448         }
 449         $sql13 = "select *, timestamp as t from ".TABLE_PREFIX."g_click_data where to_cid='0' AND g='$_GET[g_id]' AND course_id='$_SESSION[course_id]'";
 450         $result13 = mysql_query($sql13, $db);
 451         echo '<a name="show_pages"></a>';
 452         echo '<h3>'._AT('tools_details').' ('._AT($title_refs2[$g_id]).')</h3>';
 453         echo '<p>[<a href="'.$_SERVER['PHP_SELF'].'?stats=summary#show_pages">'._AT('back_to_summary'),'</a>]</p>';
 454
 455         echo '<table class="data static" rules="cols" summary="">';
 456         echo '<thead>';
 457         echo '<tr>';
 458                 echo '<th scope="col">' . _AT('origin_page')  . '</th>';
 459                 echo '<th scope="col">' . _AT('duration_sec') . '</th>';
 460                 echo '<th scope="col">' . _AT('date')         . '</th>';
 461                 echo '<th scope="col">' . _AT('student_id')   . '</th>';
 462         echo '</tr>';
 463         echo '</thead>';
 464
 465         echo '<tbody>';
 466
 467         while ($row=mysql_fetch_array($result13)){
 468                 echo '<tr>';
 469                 if ($row['from_cid'] == 0) {
 470                         echo '<td>'._AT($title_refs2[$row['g']]).'</td>';
 471                 }
 472                 else if ($title_refs[$row['from_cid']] != '') {
 473                         echo '<td>'.$title_refs[$row['from_cid']].'</td>';
 474
 475                 }
 476
 477                 if ($title_refs[$row['from_cid']] != '' || $row['from_cid'] == 0) {
 478                         echo '<td>'.$row['duration'].'</td>';
 479                         echo '<td>'.date("M-j-y g:i:s:a",$row['t'] ).'</td>';
 480                         echo '<td>'.$this_user[$row['member_id']].'</td>';
 481                 }
 482                 echo '</tr>';
 483         }
 484         echo '</tbody>';
 485         echo '</table>';
 486 }
 487 ?>