2 /************************************************************************/
4 /************************************************************************/
5 /* Copyright (c) 2002-2010 */
6 /* Inclusive Design Institute */
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 /************************************************************************/
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:
21 // [3] => Excellent - Inadequate
22 // [grade_scale_id] => scale_value_max - scale_value_min (Value Explanation)
24 define ('USE_HIGHER_GRADE', 1);
25 define ('USE_LOWER_GRADE', 2);
26 define ('NOT_OVERWRITE', 3);
27 define ('OVERWRITE', 4);
29 function get_grade_scales_array($member_id = 0)
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());
36 $preset_grade_scales = array();
37 while ($row = mysql_fetch_assoc($result))
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'];
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'];
49 $preset_grade_scales[$row["grade_scale_id"]] = $max_value . " - ". $min_value;
52 return $preset_grade_scales;
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.
58 function print_grade_scale_selectbox($selected_grade_scale_id = 0, $id_name="selected_grade_scale_id")
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>
64 // preset grade scales
65 $preset_scales = get_grade_scales_array();
67 if (count($preset_scales) > 0)
70 <optgroup label="<?php echo _AT('presets'); ?>">
73 foreach ($preset_scales as $id=>$preset)
75 echo '<option value="'.$id.'" ';
76 if ($selected_grade_scale_id == $id) echo 'selected="selected"';
77 echo '>'.$preset.'</option>'."\n\r";
79 echo ' </optgroup>'."\n\r";
83 $custom_scales = get_grade_scales_array($_SESSION["member_id"]);
85 if (count($custom_scales) > 0)
87 echo ' <optgroup label="'. _AT('custom').'">'."\n\r";
88 foreach ($custom_scales as $id=>$custom)
90 echo '<option value="'.$id.'" ';
91 if ($selected_grade_scale_id == $id) echo 'selected="selected"';
92 echo '>'.$custom.'</option>'."\n\r";
94 echo ' </optgroup>'."\n\r";
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='')
110 $score = trim($score);
111 $out_of = trim($out_of);
113 if ($out_of == '') $default_mark = $score;
114 else $default_mark = $score ." / " . $out_of;
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;
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());
124 if (mysql_num_rows($result_grade) == 0)
125 $mark = $default_mark;
128 // check if $score is already the grade. If it is, return $score
129 while ($row_grade = mysql_fetch_assoc($result_grade))
131 if ($row_grade['scale_value'] == $score) return $score;
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;
139 mysql_data_seek($result_grade, 0);
140 while ($row_grade = mysql_fetch_assoc($result_grade))
142 if ($mark_in_percentage <= $row_grade['percentage_to'] && $mark_in_percentage >= $row_grade['percentage_from'])
143 $mark = $row_grade['scale_value'];
148 // in case grade definition does not cover all scores
149 if ($mark == '') $mark = $default_mark;
154 function get_member_grade($test_id, $member_id, $grade_scale_id)
158 require_once(AT_INCLUDE_PATH.'../mods/_standard/tests/lib/test_result_functions.inc.php');
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);
167 if (mysql_num_rows($result) > 0 && $row["final_score"] <> "")
169 if ($row['random']) {
170 $out_of = get_random_outof($test_id, $row['result_id']);
172 $out_of = $row['out_of'];
174 $grade = get_mark_by_grade($grade_scale_id, $row["final_score"], $out_of);
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
185 // [member_id1] => [num_takes1]
186 // [member_id2] => [num_takes2]
189 function get_studs_take_more_than_once($course_id, $test_id)
193 $rtn_array = array();
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());
198 while ($row = mysql_fetch_assoc($result))
199 $rtn_array[$row["member_id"]. " " . $row["last_name"]] = $row["num"];
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")
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"];
219 if ($grade_scale_id == 0) // compare raw scores
221 // retrieve raw score
222 $grade1 = trim(str_replace("%", "", $grade1));
223 $grade2 = trim(str_replace("%", "", $grade2));
225 if ($grade1 > $grade2) return 1;
226 else if ($grade1 < $grade2) return -1;
231 $grade1 = get_mark_by_grade($grade_scale_id, $grade1);
232 $grade2 = get_mark_by_grade($grade_scale_id, $grade2);
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))
240 $grades[] = $row_grade["scale_value"];
243 if (!in_array($grade1, $grades) || !in_array($grade2, $grades))
244 return -1; // uncomparable
247 $grade1_key = array_search($grade1, $grades);
248 $grade2_key = array_search($grade2, $grades);
250 if ($grade1_key > $grade2_key)
252 $higher_grade = $grade2;
253 $lower_grade = $grade1;
255 else if ($grade1_key < $grade2_key)
257 $higher_grade = $grade1;
258 $lower_grade = $grade2;
260 else $higher_grade = $lower_grade = $grade1;
264 if ($mode == "higher") return $higher_grade;
265 else return $lower_grade;
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
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)
279 // [gradebook_test_id] => 4
280 // [solve_conflict] => 0
282 // return: an array of processed student/grade/error info
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)
290 // [gradebook_test_id] => 4
291 // [solve_conflict] => 0
292 // [error] => "Student not exists"
293 // [has_conflict] => 1
295 function check_user_info($record)
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'])));
305 if (empty($record['remove'])) {
306 $record['remove'] = FALSE;
309 if ($record['member_id'] == '')
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());
314 if (mysql_num_rows($result) == 0)
315 $record['error'] = _AT("student_not_exists");
318 $row = mysql_fetch_assoc($result);
319 $record['member_id'] = $row["member_id"];
323 if ($record['error'] == "" && $record['member_id'] > 0)
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());
328 if (mysql_num_rows($result) > 0 && $record['solve_conflict'] == 0)
330 $row = mysql_fetch_assoc($result);
331 $record['error'] = _AT("grade_already_exists", $row["grade"]);
332 $record['conflict'] = 1;
335 if (mysql_num_rows($result) > 0 && $record['solve_conflict'] > 0)
337 $row = mysql_fetch_assoc($result);
339 if ($record['solve_conflict'] == USE_HIGHER_GRADE || $record['solve_conflict'] == USE_LOWER_GRADE)
341 if ($record['solve_conflict'] == USE_HIGHER_GRADE)
342 $grade = compare_grades($record['grade'], $row['grade'], $record['gradebook_test_id'], "higher");
344 if ($record['solve_conflict'] == USE_LOWER_GRADE)
345 $grade = compare_grades($record['grade'], $row['grade'], $record['gradebook_test_id'], "lower");
349 $record["error"] = _AT("grades_uncomparable");
350 $record['conflict'] = 1;
353 $record['grade'] = $grade;
356 if ($record['solve_conflict'] == NOT_OVERWRITE) $record['grade'] = $row['grade'];
357 if ($record['solve_conflict'] == OVERWRITE) $record['grade'] = $record['grade'];
361 if ($record['remove']) {
363 $record['error'] = '';
370 function update_gradebook_external_test($students, $gradebook_test_id)
374 foreach($students as $student)
376 if (!$student['remove'])
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"];
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"];
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());
394 $updated_list .= '<li>' . $student['fname'] . ', '. $student['lname']. ': '. $grade. '</li>';
400 $feedback = array('GRADEBOOK_UPDATED', $updated_list);
401 $msg->addFeedback($feedback);
405 // return median value of the given array
406 function median($grade_array)
408 $oe_value = count($grade_array);
410 if ($oe_value % 2 == 0 )
416 $median = $grade_array[(count($grade_array)/2)];
418 $median= $grade_array[(count($grade_array)/2)-1];
423 // return class average of the given test_id
424 function get_class_avg($gradebook_test_id)
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);
432 if ($row["id"]<>0) // internal atutor test
434 require_once (AT_INCLUDE_PATH.'../mods/_standard/tests/lib/test_result_functions.inc.php');
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);
440 if ($row_test['out_of'] == 0 || $row_test['result_release']==AT_RELEASE_NEVER)
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());
447 $total_final_score = 0;
449 while ($row_marks = mysql_fetch_assoc($result_marks))
451 if ($row_marks['final_score'] == '' ) continue;
454 $total_final_score += $row_marks["final_score"];
456 if ($row_test['random'])
457 $total_out_of += get_random_outof($row_marks['test_id'], $row_marks['result_id']);
459 $total_out_of += $row_test['out_of'];
462 if ($num_students > 0)
464 $avg_final_score = round($total_final_score / $num_students);
465 $avg_out_of = round($total_out_of / $num_students);
468 if ($avg_final_score <> "")
469 $avg_grade = get_mark_by_grade($row["grade_scale_id"], $avg_final_score, $avg_out_of);
473 else // external test
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());
478 $grade_array = array();
479 while ($row_grades = mysql_fetch_assoc($result_grades))
480 $grade_array[] = $row_grades["grade"];
482 $avg_grade = median($grade_array);
485 if ($avg_grade == "") return _AT("na");
486 else return $avg_grade;