mods/_standard/tests/results_all.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 // $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, $anonymous)
  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['mods/_standard/tests/results_all.php']['title_var']  = 'mark_statistics';
 129 $_pages['mods/_standard/tests/results_all.php']['parent'] = 'mods/_standard/tests/results_all_quest.php?tid='.$tid;
 130
 131 $_pages['mods/_standard/tests/results_all_quest.php?tid='.$tid]['title_var'] = 'question_statistics';
 132 $_pages['mods/_standard/tests/results_all_quest.php?tid='.$tid]['parent'] = 'mods/_standard/tests/index.php';
 133 $_pages['mods/_standard/tests/results_all_quest.php?tid='.$tid]['children'] = array('mods/_standard/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.'../mods/_standard/tests/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         $row2 =mysql_fetch_array($result2);
 248         $anonymous = $row2['anonymous'];
 249
 250         while ($row = mysql_fetch_assoc($result))
 251         {
 252                 if ($random) {
 253                         $total_weight = get_random_outof($row['test_id'], $row['result_id']);
 254                 }
 255
 256                 // display passed student
 257                 if ($_POST['student_type'] == 'all' ||
 258                     $_POST['student_type'] == 'passed' &&
 259                     (($passscore<>0 && $row['final_score']>=$passscore) ||
 260                            ($passpercent<>0 && ($row['final_score']/$total_weight*100)>=$passpercent)))
 261                         display_test_info($row, $anonymous);
 262                 elseif ($_POST['student_type'] == 'all' ||
 263                         $_POST['student_type'] == 'failed' &&
 264                         (($passscore<>0 && $row['final_score']<$passscore) ||
 265                                ($passpercent<>0 && ($row['final_score']/$total_weight*100)<$passpercent)))
 266                         display_test_info($row, $anonymous);
 267                 elseif ($_POST['student_type'] == 'all')
 268                         display_test_info($row, $anonymous);
 269         }
 270
 271         $table_content .= '</tbody>';
 272
 273         $table_content .= '<tfoot>';
 274         $table_content .= '<tr>';
 275         $table_content .= '<td colspan="3" align="right"><strong>'._AT('average').':</strong></td>';
 276         $table_content .= '<td align="center"><strong>'.number_format($total_score/$num_results, 1).'</strong></td>';
 277
 278         for($i = 0; $i < $num_questions; $i++) {
 279                 $table_content .= '<td class="row1" align="center"><strong>';
 280                         if ($random) {
 281                                 $count = $questions[$i]['count'];
 282                         }
 283                         if ($questions[$i]['weight'] && $count) {
 284                                         $table_content .= number_format($questions[$i]['score']/$count, 1);
 285                         } else {
 286                                 $table_content .= '0.0';
 287                         }
 288                         $table_content .= '</strong></td>';
 289         }
 290         $table_content .= '</tr>';
 291
 292         $table_content .= '<tr>';
 293         $table_content .= '<td colspan="3">&nbsp;</td>';
 294         $table_content .= '<td align="center"><strong>';
 295         if ($total_weight) {
 296                 $table_content .= number_format($total_score/$num_results/$total_weight*100, 1).'%';
 297         }
 298         $table_content .= '</strong></td>';
 299
 300         for($i = 0; $i < $num_questions; $i++) {
 301                 $table_content .= '<td align="center"><strong>';
 302                         if ($random) {
 303                                 $count = $questions[$i]['count'];
 304                         }
 305
 306                         if ($questions[$i]['weight'] && $count) {
 307                                 $table_content .= number_format($questions[$i]['score']/$count/$questions[$i]['weight']*100, 1).'%';
 308                         } else {
 309                                 $table_content .= '00.0%';
 310                         }
 311                 $table_content .= '</strong></td>';
 312         }
 313         $table_content .= '</tr>';
 314         $table_content .= '</tfoot>';
 315 } else {
 316         $table_content .= '<strong>'._AT('no_results_available').'</strong>';
 317         $no_result_found = true;
 318 }
 319
 320 // header info has to be in front of any other output, so download
 321 // before display page
 322 if ($_POST['download']){
 323         if ($no_result_found)
 324         {
 325                 require (AT_INCLUDE_PATH.'header.inc.php');
 326                 $msg->printErrors('ITEM_NOT_FOUND');
 327                 require (AT_INCLUDE_PATH.'footer.inc.php');
 328                 exit;
 329         }
 330
 331         header('Content-Type: application/x-excel');
 332         header('Content-Disposition: inline; filename="'.$test_title.'.csv"');
 333         header('Expires: 0');
 334         header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
 335         header('Pragma: public');
 336
 337         echo $csv_content;
 338         exit;
 339 }
 340
 341 require(AT_INCLUDE_PATH.'header.inc.php');
 342
 343 $msg->printErrors();
 344
 345 ?>
 346 <script type='text/javascript' src='jscripts/lib/calendar.js'></script>
 347
 348 <div class="input-form">
 349 <form method="post" action="<?php echo $_SERVER['PHP_SELF'] . '?tid='.$tid; ?>">
 350         <div class="row">
 351                 <label for="start_date"><?php echo _AT('start_date'); ?>(YYYY-MM-DD)</label>
 352                 <input id='start_date' name='start_date' type='text' value='<?php echo $start_date?>' />
 353                 <img src='images/calendar.gif' style="vertical-align: middle; cursor: pointer;" onclick="scwShow(scwID('start_date'),event);" />
 354
 355                 <label for="end_date"><?php echo _AT('end_date'); ?>(YYYY-MM-DD)</label>
 356                 <input id='end_date' name='end_date' type='text' value='<?php echo $end_date?>' />
 357                 <img src='images/calendar.gif' style="vertical-align: middle; cursor: pointer;" onclick="scwShow(scwID('end_date'),event);" />
 358         </div>
 359
 360         <div class="row">
 361                 <?php echo _AT('user_type'); ?><br />
 362                 <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>
 363                 <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>
 364                 <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>
 365         </div>
 366
 367 <?php
 368 // display options for passed/failed students when pass score/percentage is defined
 369 if ($passscore <> 0 || $passpercent <> 0)
 370 {
 371 ?>
 372         <div class="row">
 373                 <?php echo _AT('students'); ?><br />
 374                 <input type="radio" name="student_type" value="all" id="all" <?php if ($_POST['student_type'] == 'all'){echo 'checked="true"';} ?> />
 375                 <label for="all" title="<?php echo _AT('all_students');  ?>"><?php echo _AT('all_students'); ?></label>
 376
 377                 <input type="radio" name="student_type" value="passed" id="passed" <?php if ($_POST['student_type'] == 'passed'){echo 'checked="true"';} ?> />
 378                 <label for="passed" title="<?php echo _AT('all_passed_students');  ?>"><?php echo _AT('all_passed_students'); ?></label>
 379
 380                 <input type="radio" name="student_type" value="failed" id="failed" <?php if ($_POST['student_type'] == 'failed'){echo 'checked="true"';} ?> />
 381                 <label for="failed" title="<?php echo _AT('all_failed_students');  ?>"><?php echo _AT('all_failed_students'); ?></label>
 382         </div>
 383 <?php
 384 }
 385 ?>
 386
 387         <div class="row buttons">
 388                 <input type="submit" name="filter" value="<?php echo _AT('filter'); ?>" />
 389                 <input type="submit" name="reset_filter" value="<?php echo _AT('reset_filter'); ?>" />
 390                 <input type="submit" name="download" value="<?php echo _AT('download_test_csv'); ?>" />
 391                 <input type="hidden" name="test_id" value="<?php echo $tid; ?>" />
 392         </div>
 393 </form>
 394 </div>
 395
 396
 397 <?php
 398 echo $table_content;
 399 ?>
 400
 401 </table>
 402
 403 <?php require(AT_INCLUDE_PATH.'footer.inc.php'); ?>