mods/gradebook/lib/gradebook.inc.php

   1 <?php
   2 /************************************************************************/
   3 /* ATutor                                                                                                                               */
   4 /************************************************************************/
   5 /* Copyright (c) 2002-2008 by Greg Gay, Joel Kronenberg & Heidi Hazelton*/
   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: gradebook.inc.php 7208 2008-05-28 16:07:24Z cindy $
  14
  15 // input: member_id
  16 // return: array of grade scales created by member_id, member_id is set to 0 by default, which is to get preset grade scales
  17 // returned array format:
  18 //Array
  19 //(
  20 //    [1] => A+ - E
  21 //    [2] => Pass - Fail
  22 //    [3] => Excellent - Inadequate
  23 //    [grade_scale_id] => scale_value_max - scale_value_min     (Value Explanation)
  24 //)
  25 define ('USE_HIGHER_GRADE', 1);
  26 define ('USE_LOWER_GRADE', 2);
  27 define ('NOT_OVERWRITE', 3);
  28 define ('OVERWRITE', 4);
  29
  30 function get_grade_scales_array($member_id = 0)
  31 {
  32         global $db;
  33
  34         $sql = "SELECT d.grade_scale_id, MIN(percentage_to) min, MAX(percentage_to) max FROM ".TABLE_PREFIX."grade_scales_detail d, ".TABLE_PREFIX."grade_scales g WHERE d.grade_scale_id = g.grade_scale_id AND g.member_id = ". $member_id ." GROUP BY d.grade_scale_id";
  35         $result = mysql_query($sql, $db) or die(mysql_error());
  36
  37         $preset_grade_scales = array();
  38         while ($row = mysql_fetch_assoc($result))
  39         {
  40                 $sql_min = "SELECT scale_value FROM ".TABLE_PREFIX."grade_scales_detail WHERE grade_scale_id=".$row["grade_scale_id"]." AND percentage_to=".$row["min"];
  41                 $result_min = mysql_query($sql_min, $db) or die(mysql_error());
  42                 $row_min = mysql_fetch_assoc($result_min);
  43                 $min_value = $row_min['scale_value'];
  44
  45                 $sql_max = "SELECT scale_value FROM ".TABLE_PREFIX."grade_scales_detail WHERE grade_scale_id=".$row["grade_scale_id"]." AND percentage_to=".$row["max"];
  46                 $result_max = mysql_query($sql_max, $db) or die(mysql_error());
  47                 $row_max = mysql_fetch_assoc($result_max);
  48                 $max_value = $row_max['scale_value'];
  49
  50                 $preset_grade_scales[$row["grade_scale_id"]] = $max_value . " - ". $min_value;
  51         }
  52
  53         return $preset_grade_scales;
  54 }
  55
  56 // generate html of dropdown list box on preset grade scales and grade scales created by current member Id
  57 // parameter: $selected_grade_scale_id: the grade_scale_id that need to set to be selected.
  58 // return: html text
  59 function print_grade_scale_selectbox($selected_grade_scale_id = 0, $id_name="selected_grade_scale_id")
  60 {
  61 ?>
  62                 <select name="selected_grade_scale_id" id="<?php echo $id_name; ?>">
  63                         <option value="0" <?php if ($selected_grade_scale_id  == 0) { echo 'selected="selected"'; } ?>><?php echo _AT('none'); ?></option>
  64                 <?php
  65                         // preset grade scales
  66                         $preset_scales = get_grade_scales_array();
  67
  68                         if (count($preset_scales) > 0)
  69                         {
  70                 ?>
  71                         <optgroup label="<?php echo _AT('presets'); ?>">
  72                 <?php
  73                                 //presets
  74                                 foreach ($preset_scales as $id=>$preset)
  75                                 {
  76                                         echo '<option value="'.$id.'" ';
  77                                         if ($selected_grade_scale_id  == $id) echo 'selected="selected"';
  78                                         echo '>'.$preset.'</option>'."\n\r";
  79                                 }
  80                                 echo '                  </optgroup>'."\n\r";
  81                         }
  82
  83                         //previously used
  84                         $custom_scales = get_grade_scales_array($_SESSION["member_id"]);
  85
  86                         if (count($custom_scales) > 0)
  87                         {
  88                                 echo '                  <optgroup label="'. _AT('custom').'">'."\n\r";
  89                                 foreach ($custom_scales as $id=>$custom)
  90                                 {
  91                                         echo '<option value="'.$id.'" ';
  92                                         if ($selected_grade_scale_id  == $id) echo 'selected="selected"';
  93                                         echo '>'.$custom.'</option>'."\n\r";
  94                                 }
  95                                 echo '                  </optgroup>'."\n\r";
  96                         }
  97                 ?>
  98                 </select>
  99
 100 <?php
 101 }
 102
 103 // This function returns grade based on grade scale
 104 // Note: $score can be one of: grade itself, percentage, raw final score.
 105 // If $score is raw final score, $out_of has to be provided, otherwise,
 106 // don't have to provide $out_of
 107 function get_mark_by_grade($grade_scale_id, $score, $out_of='')
 108 {
 109         global $db;
 110
 111         $score = trim($score);
 112         $out_of = trim($out_of);
 113
 114         if ($out_of == '') $default_mark = $score;
 115         else $default_mark = $score ." / " . $out_of;
 116
 117         // if $grade_scale_id is 0 or not given, return $score itself.
 118         if ($grade_scale_id == 0 || $grade_scale_id == '')
 119                 $mark = $default_mark;
 120         else // raw score
 121         {
 122                 $sql_grade = "SELECT * from ".TABLE_PREFIX."grade_scales_detail WHERE grade_scale_id = ". $grade_scale_id. " ORDER BY percentage_to DESC";
 123                 $result_grade   = mysql_query($sql_grade, $db) or die(mysql_error());
 124
 125                 if (mysql_num_rows($result_grade) == 0)
 126                         $mark = $default_mark;
 127                 else
 128                 {
 129                         // check if $score is already the grade. If it is, return $score
 130                         while ($row_grade = mysql_fetch_assoc($result_grade))
 131                         {
 132                                 if ($row_grade['scale_value'] == $score) return $score;
 133                         }
 134
 135                         if (substr($score, -1) == '%') // percentage
 136                                 $mark_in_percentage = substr($score, 0, -1);
 137                         else if ($out_of <> '' && $out_of <> 0)  // raw final score
 138                                 $mark_in_percentage = $score / $out_of * 100;
 139
 140                         mysql_data_seek($result_grade, 0);
 141                         while ($row_grade = mysql_fetch_assoc($result_grade))
 142                         {
 143                                 if ($mark_in_percentage <= $row_grade['percentage_to'] && $mark_in_percentage >= $row_grade['percentage_from'])
 144                                         $mark = $row_grade['scale_value'];
 145                         }
 146                 }
 147         }
 148
 149         // in case grade definition does not cover all scores
 150         if ($mark == '') $mark = $default_mark;
 151
 152         return $mark;
 153 }
 154
 155 function get_member_grade($test_id, $member_id, $grade_scale_id)
 156 {
 157         global $db;
 158
 159         require_once(AT_INCLUDE_PATH.'lib/test_result_functions.inc.php');
 160
 161         $grade = "";
 162
 163         // find out final_score, out_of
 164         $sql = "SELECT t.random, t.out_of, r.result_id, r.final_score FROM ".TABLE_PREFIX."tests t, ".TABLE_PREFIX."tests_results r WHERE t.test_id=".$test_id." AND t.test_id=r.test_id AND r.member_id=".$member_id;
 165         $result = mysql_query($sql, $db) or die(mysql_error());
 166         $row = mysql_fetch_assoc($result);
 167
 168         if (mysql_num_rows($result) > 0 && $row["final_score"] <> "")
 169         {
 170                 if ($row['random']) {
 171                         $out_of = get_random_outof($test_id, $row['result_id']);
 172                 } else {
 173                         $out_of = $row['out_of'];
 174                 }
 175                 $grade = get_mark_by_grade($grade_scale_id, $row["final_score"], $out_of);
 176         }
 177
 178         return $grade;
 179 }
 180
 181 // Return array of students in the given course who take the given test more than once
 182 // Parameter: $member_id, $test_id
 183 // Return: an empty array or
 184 //Array
 185 //(
 186 //    [member_id1] => [num_takes1]
 187 //    [member_id2] => [num_takes2]
 188 //    ...
 189 //)
 190 function get_studs_take_more_than_once($course_id, $test_id)
 191 {
 192         global $db;
 193
 194         $rtn_array = array();
 195
 196         $sql = "SELECT m.first_name, m.last_name, count(result_id) num FROM ".TABLE_PREFIX."members m, ".TABLE_PREFIX."course_enrollment e, ".TABLE_PREFIX."tests_results t WHERE m.member_id = e.member_id AND e.course_id = ".$course_id." AND e.approved='y' AND e.role <> 'Instructor' AND e.member_id=t.member_id AND t.test_id=".$test_id." GROUP BY m.first_name, m.last_name having count(*) > 1";
 197         $result = mysql_query($sql, $db) or die(mysql_error());
 198
 199         while ($row = mysql_fetch_assoc($result))
 200                 $rtn_array[$row["first_name"]. " " . $row["last_name"]] = $row["num"];
 201
 202         return $rtn_array;
 203 }
 204
 205 // compare grades
 206 // parameters: 2 grades to compare, grade_scale_id, "higher"/"lower": return higher or lower grade
 207 //             grade can be percentage like 70% or grade defined in grade_scale_id, like "A", "B"...
 208 // return: higher or lower grade depending on 4th parameter
 209 //         or, -1 if grades are comparable
 210 function compare_grades($grade1, $grade2, $gradebook_test_id, $mode = "higher")
 211 {
 212         global $db;
 213
 214         // get grade scale id
 215         $sql = "SELECT grade_scale_id FROM ".TABLE_PREFIX."gradebook_tests WHERE gradebook_test_id = ".$gradebook_test_id;
 216         $result = mysql_query($sql, $db) or die(mysql_error());
 217         $row = mysql_fetch_assoc($result);
 218         $grade_scale_id = $row["grade_scale_id"];
 219
 220         if ($grade_scale_id == 0) // compare raw scores
 221         {
 222                 // retrieve raw score
 223                 $grade1 = trim(str_replace("%", "", $grade1));
 224                 $grade2 = trim(str_replace("%", "", $grade2));
 225
 226                 if ($grade1 > $grade2) return 1;
 227                 else if ($grade1 < $grade2) return -1;
 228                 else return 0;
 229         }
 230         else
 231         {
 232                 $grade1 = get_mark_by_grade($grade_scale_id, $grade1);
 233                 $grade2 = get_mark_by_grade($grade_scale_id, $grade2);
 234
 235                 $grades = array();
 236
 237                 $sql_grade = "SELECT scale_value from ".TABLE_PREFIX."grade_scales_detail WHERE grade_scale_id = ". $grade_scale_id. " ORDER BY percentage_to DESC";
 238                 $result_grade   = mysql_query($sql_grade, $db) or die(mysql_error());
 239                 while ($row_grade = mysql_fetch_assoc($result_grade))
 240                 {
 241                         $grades[] = $row_grade["scale_value"];
 242                 }
 243
 244                 if (!in_array($grade1, $grades) || !in_array($grade2, $grades))
 245                         return -1; // uncomparable
 246                 else
 247                 {
 248                         $grade1_key = array_search($grade1, $grades);
 249                         $grade2_key = array_search($grade2, $grades);
 250
 251                         if ($grade1_key > $grade2_key)
 252                         {
 253                                 $higher_grade = $grade2;
 254                                 $lower_grade = $grade1;
 255                         }
 256                         else if ($grade1_key < $grade2_key)
 257                         {
 258                                 $higher_grade = $grade1;
 259                                 $lower_grade = $grade2;
 260                         }
 261                         else $higher_grade = $lower_grade = $grade1;
 262                 }
 263         }
 264
 265         if ($mode == "higher") return $higher_grade;
 266         else return $lower_grade;
 267 }
 268
 269 // check imported students and grades:
 270 // 1. if the student exists in the class, if not, report error
 271 // 2. if the grade already exists, if it is, report conflict
 272 // parameter: an array of student/grade info
 273 // Array
 274 //(
 275 //    [member_id] => 1
 276 //    [fname] => angelo  (could be empty if [member_id] is given)
 277 //    [lname] => yuan    (could be empty if [member_id] is given)
 278 //    [email] => angelo@hotmail.com   (could be empty if [member_id] is given)
 279 //    [grade] => 70%
 280 //    [gradebook_test_id] => 4
 281 //    [solve_conflict] => 0
 282 //)
 283 // return: an array of processed student/grade/error info
 284 // Array
 285 //(
 286 //    [member_id] => 1
 287 //    [fname] => angelo  (could be empty if [member_id] is given)
 288 //    [lname] => yuan    (could be empty if [member_id] is given)
 289 //    [email] => angelo@hotmail.com  (could be empty if [member_id] is given)
 290 //    [grade] => 70%
 291 //    [gradebook_test_id] => 4
 292 //    [solve_conflict] => 0
 293 //    [error] => "Student not exists"
 294 //    [has_conflict] => 1
 295 //)
 296 function check_user_info($record)
 297 {
 298         global $db;
 299
 300         $record['fname'] = htmlspecialchars(stripslashes(trim($record['fname'])));
 301         $record['lname'] = htmlspecialchars(stripslashes(trim($record['lname'])));
 302         $record['member_id'] = htmlspecialchars(stripslashes(trim($record['member_id'])));
 303         $record['email'] = htmlspecialchars(stripslashes(trim($record['email'])));
 304         $record['grade'] = htmlspecialchars(stripslashes(trim($record['grade'])));
 305
 306         if (empty($record['remove'])) {
 307                 $record['remove'] = FALSE;
 308         }
 309
 310         if ($record['member_id'] == '')
 311         {
 312                 $sql = "SELECT * FROM ".TABLE_PREFIX."members m, ".TABLE_PREFIX."course_enrollment e WHERE m.first_name='".$record['fname']."' AND m.last_name='".$record['lname']."' AND m.email='".$record['email']."' AND m.member_id = e.member_id AND e.course_id=".$_SESSION["course_id"]." AND e.approved='y' AND e.role<>'Instructor'";
 313                 $result = mysql_query($sql, $db) or die(mysql_error());
 314
 315                 if (mysql_num_rows($result) == 0)
 316                         $record['error'] = _AT("student_not_exists");
 317                 else
 318                 {
 319                         $row = mysql_fetch_assoc($result);
 320                         $record['member_id'] = $row["member_id"];
 321                 }
 322         }
 323
 324         if ($record['error'] == "" && $record['member_id'] > 0)
 325         {
 326                 $sql = "SELECT grade FROM ".TABLE_PREFIX."gradebook_detail WHERE gradebook_test_id=".$record['gradebook_test_id']. " AND member_id=".$record["member_id"];
 327                 $result = mysql_query($sql, $db) or die(mysql_error());
 328
 329                 if (mysql_num_rows($result) > 0 && $record['solve_conflict'] == 0)
 330                 {
 331                         $row = mysql_fetch_assoc($result);
 332                         $record['error'] = _AT("grade_already_exists", $row["grade"]);
 333                         $record['conflict'] = 1;
 334                 }
 335
 336                 if (mysql_num_rows($result) > 0 && $record['solve_conflict'] > 0)
 337                 {
 338                         $row = mysql_fetch_assoc($result);
 339
 340                         if ($record['solve_conflict'] == USE_HIGHER_GRADE || $record['solve_conflict'] == USE_LOWER_GRADE)
 341                         {
 342                                 if ($record['solve_conflict'] == USE_HIGHER_GRADE)
 343                                         $grade = compare_grades($record['grade'], $row['grade'], $record['gradebook_test_id'], "higher");
 344
 345                                 if ($record['solve_conflict'] == USE_LOWER_GRADE)
 346                                         $grade = compare_grades($record['grade'], $row['grade'], $record['gradebook_test_id'], "lower");
 347
 348                                 if ($grade == -1)
 349                                 {
 350                                         $record["error"] = _AT("grades_uncomparable");
 351                                         $record['conflict'] = 1;
 352                                 }
 353                                 else
 354                                         $record['grade'] = $grade;
 355                         }
 356
 357                         if ($record['solve_conflict'] == NOT_OVERWRITE) $record['grade'] = $row['grade'];
 358                         if ($record['solve_conflict'] == OVERWRITE) $record['grade'] = $record['grade'];
 359                 }
 360         }
 361
 362         if ($record['remove']) {
 363                 //unset errors
 364                 $record['error'] = '';
 365         }
 366
 367         return $record;
 368 }
 369
 370 // update gradebook
 371 function update_gradebook_external_test($students, $gradebook_test_id)
 372 {
 373         global $db, $msg;
 374
 375         foreach($students as $student)
 376         {
 377                 if (!$student['remove'])
 378                 {
 379                         // retrieve member id
 380                         $sql = "SELECT member_id FROM ".TABLE_PREFIX."members m WHERE m.first_name='".$student['fname']."' AND m.last_name='".$student['lname']."' AND m.email='".$student['email']."'";
 381                         $result = mysql_query($sql, $db) or die(mysql_error());
 382                         $row = mysql_fetch_assoc($result);
 383                         $member_id = $row["member_id"];
 384
 385                         // retrieve grade scale id
 386                         $sql = "SELECT grade_scale_id FROM ".TABLE_PREFIX."gradebook_tests WHERE gradebook_test_id=".$gradebook_test_id;
 387                         $result = mysql_query($sql, $db) or die(mysql_error());
 388                         $row = mysql_fetch_assoc($result);
 389                         $grade_scale_id = $row["grade_scale_id"];
 390
 391                         $grade = get_mark_by_grade($grade_scale_id, $student["grade"]);
 392                         $sql = "REPLACE INTO ".TABLE_PREFIX."gradebook_detail(gradebook_test_id, member_id, grade) VALUES(".$gradebook_test_id.", ".$member_id.", '".$grade."')";
 393                         $result = mysql_query($sql, $db) or die(mysql_error());
 394
 395                         $updated_list .= '<li>' . $student['fname'] . ', '. $student['lname']. ': '. $grade. '</li>';
 396                 }
 397         }
 398
 399         if ($updated_list)
 400         {
 401         $feedback = array('GRADEBOOK_UPDATED', $updated_list);
 402         $msg->addFeedback($feedback);
 403         }
 404 }
 405
 406 // return median value of the given array
 407 function median($grade_array)
 408 {
 409         $oe_value = count($grade_array);
 410
 411         if ($oe_value % 2 == 0 )
 412                 $position = 1;
 413         else
 414                 $position = 2;
 415
 416         if ($position == 2 )
 417                 $median = $grade_array[(count($grade_array)/2)];
 418         else
 419                 $median= $grade_array[(count($grade_array)/2)-1];
 420
 421         return $median;
 422 }
 423
 424 // return class average of the given test_id
 425 function get_class_avg($gradebook_test_id)
 426 {
 427         global $db;
 428
 429         $sql = "SELECT * FROM ".TABLE_PREFIX."gradebook_tests WHERE gradebook_test_id=".$gradebook_test_id;
 430         $result = mysql_query($sql, $db) or die(mysql_error());
 431         $row = mysql_fetch_assoc($result);
 432
 433         if ($row["id"]<>0)  // internal atutor test
 434         {
 435                 require_once (AT_INCLUDE_PATH.'lib/test_result_functions.inc.php');
 436
 437                 $sql_test = "SELECT * FROM ".TABLE_PREFIX."tests WHERE test_id=".$row["id"];
 438                 $result_test = mysql_query($sql_test, $db) or die(mysql_error());
 439                 $row_test = mysql_fetch_assoc($result_test);
 440
 441                 if ($row_test['out_of'] == 0 || $row_test['result_release']==AT_RELEASE_NEVER)
 442                         return _AT("na");
 443
 444                 $sql_marks = "SELECT * FROM ".TABLE_PREFIX."tests_results WHERE test_id=".$row["id"]. " AND status=1";
 445                 $result_marks = mysql_query($sql_marks, $db) or die(mysql_error());
 446
 447                 $num_students = 0;
 448                 $total_final_score = 0;
 449                 $total_out_of = 0;
 450                 while ($row_marks = mysql_fetch_assoc($result_marks))
 451                 {
 452                         if ($row_marks['final_score'] == '' ) continue;
 453
 454                         $num_students++;
 455                         $total_final_score += $row_marks["final_score"];
 456
 457                         if ($row_test['random'])
 458                                 $total_out_of += get_random_outof($row_marks['test_id'], $row_marks['result_id']);
 459                         else
 460                                 $total_out_of += $row_test['out_of'];
 461                 }
 462
 463                 if ($num_students > 0)
 464                 {
 465                         $avg_final_score = round($total_final_score / $num_students);
 466                         $avg_out_of = round($total_out_of / $num_students);
 467                 }
 468
 469                 if ($avg_final_score <> "")
 470                         $avg_grade = get_mark_by_grade($row["grade_scale_id"], $avg_final_score, $avg_out_of);
 471                 else
 472                         $avg_grade = "";
 473         }
 474         else  // external test
 475         {
 476                 $sql_grades = "SELECT * FROM ".TABLE_PREFIX."gradebook_detail WHERE gradebook_test_id=".$gradebook_test_id." ORDER BY grade";
 477                 $result_grades = mysql_query($sql_grades, $db) or die(mysql_error());
 478
 479                 $grade_array = array();
 480                 while ($row_grades = mysql_fetch_assoc($result_grades))
 481                         $grade_array[] = $row_grades["grade"];
 482
 483                 $avg_grade = median($grade_array);
 484         }
 485
 486         if ($avg_grade == "") return _AT("na");
 487         else return $avg_grade;
 488 }
 489 ?>