tools/tests/results_all.php

   1 <?php
   2 /****************************************************************/
   3 /* ATutor                                                                                                               */
   4 /****************************************************************/
   5 /* Copyright (c) 2002-2008 by Greg Gay & Joel Kronenberg        */
   6 /* Adaptive Technology Resource Centre / University of Toronto  */
   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 // $Id$
  14 define('AT_INCLUDE_PATH', '../../include/');
  15 require(AT_INCLUDE_PATH.'vitals.inc.php');
  16
  17 authenticate(AT_PRIV_TESTS);
  18
  19 // Validate date
  20 function isValidDate($date)
  21 {
  22     if (preg_match("/^(\d{4})-(\d{2})-(\d{2})$/", $date, $matches))
  23     {
  24         if (checkdate($matches[2], $matches[3], $matches[1])) {
  25             return true;
  26         }
  27     }
  28
  29     return false;
  30 }
  31
  32 function display_test_info($row)
  33 {
  34                 global $random, $num_questions, $total_weight, $questions, $total_score, $table_content, $csv_content;
  35                 global $passscore, $passpercent;
  36                 global $q_sql, $db;
  37
  38                 $row['login'] = $row['login'] ? $row['login'] : '- '._AT('guest').' -';
  39                 $table_content .= '<tr>';
  40
  41                 if($anonymous == 1){
  42                                 $table_content .= '<td align="center">'._AT('anonymous').'</td>';
  43                                 $csv_content .= quote_csv(_AT('anonymous')).', ';
  44                 }else{
  45                                 $table_content .= '<td align="center">'.$row['login'].'</td>';
  46                                 $csv_content .= quote_csv($row['login']).', ';
  47                 }
  48                 $startend_date_format=_AT('startend_date_format');
  49                 $table_content .= '<td align="center">'.AT_date($startend_date_format, $row['date_taken'], AT_DATE_MYSQL_DATETIME).'</td>';
  50                 $csv_content .= quote_csv($row['date_taken']).', ';
  51
  52                 if ($passscore <> 0)
  53                 {
  54                         $table_content .= '<td align="center">'.$passscore.'</td>';
  55                         $csv_content .= $passscore.', ';
  56                 }
  57                 elseif ($passpercent <> 0)
  58                 {
  59                         $table_content .= '<td align="center">'.$passpercent.'%</td>';
  60                         $csv_content .= $passpercent . '%, ';
  61                 }
  62                 else
  63                 {
  64                         $table_content .= '<td align="center">'._AT('na').'</td>';
  65                         $csv_content .= _AT('na') . ', ';
  66                 }
  67
  68                 $table_content .= '<td align="center">'.$row['final_score'].'/'.$total_weight.'</td>';
  69                 $csv_content .= $row['final_score'].'/'.$total_weight;
  70
  71                 $total_score += $row['final_score'];
  72
  73                 $answers = array(); /* need this, because we dont know which order they were selected in */
  74
  75                 //get answers for this test result
  76                 $sql = "SELECT question_id, score FROM ".TABLE_PREFIX."tests_answers WHERE result_id=$row[result_id] AND question_id IN ($q_sql)";
  77                 $result2 = mysql_query($sql, $db);
  78                 if ($result2){
  79                         while ($row2 = mysql_fetch_assoc($result2)) {
  80                                 $answers[$row2['question_id']] = $row2['score'];
  81                         }
  82                 }
  83                 //print answers out for each question
  84                 for($i = 0; $i < $num_questions; $i++) {
  85                         $questions[$i]['score'] += $answers[$questions[$i]['question_id']];
  86                         $table_content .= '<td align="center">';
  87
  88                         if ($answers[$questions[$i]['question_id']] == '') {
  89                                 $table_content .= '<span style="color:#ccc;">-</span>';
  90                                 $csv_content .= ', -';
  91                         } else {
  92                                 $table_content .= $answers[$questions[$i]['question_id']];
  93                                 $csv_content .= ', '.$answers[$questions[$i]['question_id']];
  94
  95                                 if ($random) {
  96                                         $questions[$i]['count']++;
  97                                 }
  98                         }
  99                         $table_content .= '</td>';
 100                 }
 101
 102                 $table_content .= '</tr>';
 103
 104                 // append guest information into CSV content if the test is taken by a guest
 105                 if (substr($row['member_id'], 0, 2) == 'g_' || substr($row['member_id'], 0, 2) == 'G_')
 106                 {
 107                         $sql = "SELECT * FROM ".TABLE_PREFIX."guests WHERE guest_id='".$row['member_id']. "'";
 108                         $result3 = mysql_query($sql, $db);
 109                         $row3 = mysql_fetch_assoc($result3);
 110
 111                         $csv_content .= ', '.quote_csv($row3['name']) . ', '.quote_csv($row3['organization']). ', '.quote_csv($row3['location']). ', '.quote_csv($row3['role']). ', '.quote_csv($row3['focus']);
 112                 }
 113
 114                 $csv_content .= "\n";
 115 }
 116
 117 function quote_csv($line) {
 118         $line = str_replace('"', '""', $line);
 119         $line = str_replace("\n", '\n', $line);
 120         $line = str_replace("\r", '\r', $line);
 121         $line = str_replace("\x00", '\0', $line);
 122
 123         return '"'.$line.'"';
 124 }
 125
 126 $tid = intval($_REQUEST['tid']);
 127
 128 $_pages['tools/tests/results_all.php']['title_var']  = 'mark_statistics';
 129 $_pages['tools/tests/results_all.php']['parent'] = 'tools/tests/results_all_quest.php?tid='.$tid;
 130
 131 $_pages['tools/tests/results_all_quest.php?tid='.$tid]['title_var'] = 'question_statistics';
 132 $_pages['tools/tests/results_all_quest.php?tid='.$tid]['parent'] = 'tools/tests/index.php';
 133 $_pages['tools/tests/results_all_quest.php?tid='.$tid]['children'] = array('tools/tests/results_all.php');
 134
 135 if (isset($_POST['reset_filter'])) unset($_POST);
 136
 137 if (!isset($_POST['student_type'])) {
 138         $_POST['student_type'] = 'all';
 139 }
 140
 141 if (isset($_POST["start_date"])) $start_date = trim($_POST["start_date"]);
 142 if (isset($_POST["end_date"]))$end_date = trim($_POST["end_date"]);
 143
 144 if ($start_date != "" && !isValidDate($start_date)) {
 145         $msg->addError('START_DATE_INVALID');
 146 }
 147
 148 if ($end_date != "" && !isValidDate($end_date)) {
 149         $msg->addError('END_DATE_INVALID');
 150 }
 151
 152 $table_content = "";
 153 $csv_content = "";
 154
 155 require(AT_INCLUDE_PATH.'lib/test_result_functions.inc.php');
 156
 157 $sql    = "SELECT title, out_of, result_release, randomize_order, passscore, passpercent FROM ".TABLE_PREFIX."tests WHERE test_id=$tid";
 158 $result = mysql_query($sql, $db);
 159 $row = mysql_fetch_array($result);
 160 $out_of = $row['out_of'];
 161 $random = $row['randomize_order'];
 162 $passscore = $row['passscore'];
 163 $passpercent = $row['passpercent'];
 164 $test_title = str_replace (' ', '_', str_replace(array('"', '<', '>'), '', $row['title']));
 165
 166 $table_content .= '<h3>'.$row['title'].'</h3><br />';
 167
 168 $sql    = "SELECT TQ.*, TQA.* FROM ".TABLE_PREFIX."tests_questions TQ INNER JOIN ".TABLE_PREFIX."tests_questions_assoc TQA USING (question_id) WHERE TQ.course_id=$_SESSION[course_id] AND TQA.test_id=$tid ORDER BY TQA.ordering, TQA.question_id";
 169
 170 //$sql  = "SELECT * FROM ".TABLE_PREFIX."tests_questions Q WHERE Q.test_id=$tid AND Q.course_id=$_SESSION[course_id] ORDER BY ordering";
 171 $result = mysql_query($sql, $db);
 172 $questions = array();
 173 $total_weight = 0;
 174 $i = 0;
 175
 176 while ($row = mysql_fetch_assoc($result)) {
 177         $row['score']   = 0;
 178         $questions[$i]  = $row;
 179         $questions[$i]['count'] = 0;
 180         $q_sql .= $row['question_id'].',';
 181         $total_weight += $row['weight'];
 182         $i++;
 183 }
 184 $q_sql = substr($q_sql, 0, -1);
 185 $num_questions = count($questions);
 186
 187 //get all the marked tests for this test
 188 $sql    = "SELECT R.*, M.login FROM ".TABLE_PREFIX."tests_results R LEFT JOIN ".TABLE_PREFIX."members M USING (member_id) WHERE R.status=1 AND R.test_id=$tid AND R.final_score<>'' ";
 189
 190 if ($start_date)     $sql .= " AND R.date_taken >= '" . $start_date . "'";
 191 if ($end_date)     $sql .= " AND R.date_taken <= '" . $end_date . "'";
 192
 193 if ($_POST["user_type"] == 1) $sql .= " AND R.member_id not like 'G_%' AND R.member_id > 0 ";
 194 if ($_POST["user_type"] == 2) $sql .= " AND (R.member_id like 'G_%' OR R.member_id = 0) ";
 195
 196 $sql .= " ORDER BY M.login, R.date_taken";
 197
 198 $result = mysql_query($sql, $db);
 199 $num_results = mysql_num_rows($result);
 200 if ($row = mysql_fetch_assoc($result)) {
 201         $total_score = 0;
 202
 203         // generate table/csv header line
 204         $table_content .= '<table class="data static" summary="" style="width: 90%" rules="cols">';
 205         $table_content .= '<thead>';
 206         $table_content .= '<tr>';
 207         $table_content .= '<th scope="col">'._AT('login_name').'</th>';
 208         $table_content .= '<th scope="col">'._AT('date_taken').'</th>';
 209         $table_content .= '<th scope="col">'._AT('pass_score').'</th>';
 210         $table_content .= '<th scope="col">'._AT('mark').'</th>';
 211
 212         $csv_content .= quote_csv(_AT('login_name')).', ';
 213         $csv_content .= quote_csv(_AT('date_taken')).', ';
 214         $csv_content .= quote_csv(_AT('pass_score')).', ';
 215         $csv_content .= quote_csv(_AT('mark'));
 216
 217         for($i = 0; $i< $num_questions; $i++) {
 218                 $table_content .= '<th scope="col">Q'.($i+1).' /'.$questions[$i]['weight'].'</th>';
 219
 220                 $csv_content .= ', '.quote_csv('Q'.($i+1).'/'.$questions[$i]['weight']);
 221         }
 222         $table_content .= '</tr>';
 223         $table_content .= '</thead>';
 224         $table_content .= '<tbody>';
 225
 226         // if there's guest information to be exported into CSV, add header names
 227         while ($row = mysql_fetch_assoc($result))
 228         {
 229                 if (substr($row['member_id'], 0, 2) == 'g_' || substr($row['member_id'], 0, 2) == 'G_')
 230                 {
 231                         $csv_content .= ', '. quote_csv(_AT('guest_name'));
 232                         $csv_content .= ', '. quote_csv(_AT('organization'));
 233                         $csv_content .= ', '. quote_csv(_AT('location'));
 234                         $csv_content .= ', '. quote_csv(_AT('role'));
 235                         $csv_content .= ', '. quote_csv(_AT('focus'));
 236
 237                         break;
 238                 }
 239         }
 240         // reset $result for next loop
 241         mysql_data_seek($result, 0);
 242
 243         $csv_content .= "\n";
 244
 245         $sql2   = "SELECT anonymous FROM ".TABLE_PREFIX."tests WHERE test_id=$tid AND course_id=$_SESSION[course_id]";
 246         $result2        = mysql_query($sql2, $db);
 247         while($row2 =mysql_fetch_array($result2)){
 248                         $anonymous = $row2['anonymous'];
 249         }
 250
 251         while ($row = mysql_fetch_assoc($result))
 252         {
 253                 if ($random) {
 254                         $total_weight = get_random_outof($row['test_id'], $row['result_id']);
 255                 }
 256
 257                 // display passed student
 258                 if ($_POST['student_type'] == 'all' ||
 259                     $_POST['student_type'] == 'passed' &&
 260                     (($passscore<>0 && $row['final_score']>=$passscore) ||
 261                            ($passpercent<>0 && ($row['final_score']/$total_weight*100)>=$passpercent)))
 262                         display_test_info($row);
 263                 elseif ($_POST['student_type'] == 'all' ||
 264                         $_POST['student_type'] == 'failed' &&
 265                         (($passscore<>0 && $row['final_score']<$passscore) ||
 266                                ($passpercent<>0 && ($row['final_score']/$total_weight*100)<$passpercent)))
 267                         display_test_info($row);
 268                 elseif ($_POST['student_type'] == 'all')
 269                         display_test_info($row);
 270         }
 271
 272         $table_content .= '</tbody>';
 273
 274         $table_content .= '<tfoot>';
 275         $table_content .= '<tr>';
 276         $table_content .= '<td colspan="3" align="right"><strong>'._AT('average').':</strong></td>';
 277         $table_content .= '<td align="center"><strong>'.number_format($total_score/$num_results, 1).'</strong></td>';
 278
 279         for($i = 0; $i < $num_questions; $i++) {
 280                 $table_content .= '<td class="row1" align="center"><strong>';
 281                         if ($random) {
 282                                 $count = $questions[$i]['count'];
 283                         }
 284                         if ($questions[$i]['weight'] && $count) {
 285                                         $table_content .= number_format($questions[$i]['score']/$count, 1);
 286                         } else {
 287                                 $table_content .= '0.0';
 288                         }
 289                         $table_content .= '</strong></td>';
 290         }
 291         $table_content .= '</tr>';
 292
 293         $table_content .= '<tr>';
 294         $table_content .= '<td colspan="3">&nbsp;</td>';
 295         $table_content .= '<td align="center"><strong>';
 296         if ($total_weight) {
 297                 $table_content .= number_format($total_score/$num_results/$total_weight*100, 1).'%';
 298         }
 299         $table_content .= '</strong></td>';
 300
 301         for($i = 0; $i < $num_questions; $i++) {
 302                 $table_content .= '<td align="center"><strong>';
 303                         if ($random) {
 304                                 $count = $questions[$i]['count'];
 305                         }
 306
 307                         if ($questions[$i]['weight'] && $count) {
 308                                 $table_content .= number_format($questions[$i]['score']/$count/$questions[$i]['weight']*100, 1).'%';
 309                         } else {
 310                                 $table_content .= '00.0%';
 311                         }
 312                 $table_content .= '</strong></td>';
 313         }
 314         $table_content .= '</tr>';
 315         $table_content .= '</tfoot>';
 316 } else {
 317         $table_content .= '<em>'._AT('no_results_available').'</em>';
 318         $no_result_found = true;
 319 }
 320
 321 // header info has to be in front of any other output, so download
 322 // before display page
 323 if ($_POST['download']){
 324         if ($no_result_found)
 325         {
 326                 require (AT_INCLUDE_PATH.'header.inc.php');
 327                 $msg->printErrors('ITEM_NOT_FOUND');
 328                 require (AT_INCLUDE_PATH.'footer.inc.php');
 329                 exit;
 330         }
 331
 332         header('Content-Type: application/x-excel');
 333         header('Content-Disposition: inline; filename="'.$test_title.'.csv"');
 334         header('Expires: 0');
 335         header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
 336         header('Pragma: public');
 337
 338         echo $csv_content;
 339         exit;
 340 }
 341
 342 require(AT_INCLUDE_PATH.'header.inc.php');
 343
 344 $msg->printErrors();
 345
 346 ?>
 347 <script type='text/javascript' src='jscripts/calendar.js'></script>
 348
 349 <div class="input-form">
 350 <form method="post" action="<?php echo $_SERVER['PHP_SELF'] . '?tid='.$tid; ?>">
 351         <div class="row">
 352                 <label for="start_date"><?php echo _AT('start_date'); ?>(YYYY-MM-DD)</label>
 353                 <input id='start_date' name='start_date' type='text' value='<?php echo $start_date?>' />
 354                 <img src='images/calendar.gif' style="vertical-align: middle; cursor: pointer;" onclick="scwShow(scwID('start_date'),event);" />
 355
 356                 <label for="end_date"><?php echo _AT('end_date'); ?>(YYYY-MM-DD)</label>
 357                 <input id='end_date' name='end_date' type='text' value='<?php echo $end_date?>' />
 358                 <img src='images/calendar.gif' style="vertical-align: middle; cursor: pointer;" onclick="scwShow(scwID('end_date'),event);" />
 359         </div>
 360
 361         <div class="row">
 362                 <?php echo _AT('user_type'); ?><br />
 363                 <input type="radio" name="user_type" value="1" id="u0" <?php if ($_POST['user_type'] == 1) { echo 'checked="checked"'; } ?> /><label for="u0"><?php echo _AT('registered_members'); ?></label>
 364                 <input type="radio" name="user_type" value="2" id="u1" <?php if ($_POST['user_type'] == 2) { echo 'checked="checked"'; } ?> /><label for="u1"><?php echo _AT('guests'); ?></label>
 365                 <input type="radio" name="user_type" value="0" id="u2" <?php if (!isset($_POST['user_type']) || ($_POST['user_type'] != 1 && $_POST['user_type'] != 2)) { echo 'checked="checked"'; } ?> /><label for="u2"><?php echo _AT('all'); ?></label>
 366         </div>
 367
 368 <?php
 369 // display options for passed/failed students when pass score/percentage is defined
 370 if ($passscore <> 0 || $passpercent <> 0)
 371 {
 372 ?>
 373         <div class="row">
 374                 <?php echo _AT('students'); ?><br />
 375                 <input type="radio" name="student_type" value="all" id="all" <?php if ($_POST['student_type'] == 'all'){echo 'checked="true"';} ?> />
 376                 <label for="all" title="<?php echo _AT('all_students');  ?>"><?php echo _AT('all_students'); ?></label>
 377
 378                 <input type="radio" name="student_type" value="passed" id="passed" <?php if ($_POST['student_type'] == 'passed'){echo 'checked="true"';} ?> />
 379                 <label for="passed" title="<?php echo _AT('all_passed_students');  ?>"><?php echo _AT('all_passed_students'); ?></label>
 380
 381                 <input type="radio" name="student_type" value="failed" id="failed" <?php if ($_POST['student_type'] == 'failed'){echo 'checked="true"';} ?> />
 382                 <label for="failed" title="<?php echo _AT('all_failed_students');  ?>"><?php echo _AT('all_failed_students'); ?></label>
 383         </div>
 384 <?php
 385 }
 386 ?>
 387
 388         <div class="row buttons">
 389                 <input type="submit" name="filter" value="<?php echo _AT('filter'); ?>" />
 390                 <input type="submit" name="reset_filter" value="<?php echo _AT('reset_filter'); ?>" />
 391                 <input type="submit" name="download" value="<?php echo _AT('download_test_csv'); ?>" />
 392                 <input type="hidden" name="test_id" value="<?php echo $tid; ?>" />
 393         </div>
 394 </form>
 395 </div>
 396
 397
 398 <?php
 399 echo $table_content;
 400 ?>
 401
 402 </table>
 403
 404 <?php require(AT_INCLUDE_PATH.'footer.inc.php'); ?>