mods/_standard/gradebook/lib/gradebook.inc.php

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