3 function getCertificateProgress($member_id, $certify_id) {
7 $certificate = array();
10 // Fetch associated tests
12 $sql = 'SELECT '.TABLE_PREFIX.'certify_tests.*, '.TABLE_PREFIX.'tests.* FROM '.TABLE_PREFIX.'certify_tests ';
13 $sql .= 'INNER JOIN '.TABLE_PREFIX.'tests ON '.TABLE_PREFIX.'certify_tests.test_id = '.TABLE_PREFIX.'tests.test_id ';
14 $sql .= "WHERE ".TABLE_PREFIX."certify_tests.certify_id=".$certify_id;
15 $result = mysql_query($sql, $db) or die(mysql_error() . " - " . $sql);
17 $certificate['tests'] = array();
18 while( $row = mysql_fetch_assoc($result) ) {
19 $certificate['tests'][$row['test_id']] = array();
20 $certificate['tests'][$row['test_id']]['passscore'] = $row['passscore'];
21 $certificate['tests'][$row['test_id']]['passpercent'] = $row['passpercent'];
22 $certificate['tests'][$row['test_id']]['out_of'] = $row['out_of'];
23 $certificate['tests'][$row['test_id']]['final_score'] = 0;
25 // Convert percent scored tests to scores
26 if ($certificate['tests'][$row['test_id']]['passpercent'] > 0) {
27 $certificate['tests'][$row['test_id']]['passscore'] = $certificate['tests'][$row['test_id']]['out_of'] * $certificate['tests'][$row['test_id']]['passpercent'] / 100;
28 $certificate['tests'][$row['test_id']]['passpercent'] = 0;
33 // Calculate new scores for each test
36 SELECT '.TABLE_PREFIX.'certify_tests.test_id, '.TABLE_PREFIX.'tests_results.*
37 FROM '.TABLE_PREFIX.'certify_tests
38 RIGHT JOIN '.TABLE_PREFIX.'tests_results ON '.TABLE_PREFIX.'tests_results.test_id = '.TABLE_PREFIX.'certify_tests.test_id
39 WHERE '.TABLE_PREFIX.'tests_results.member_id = '.$member_id.'
40 AND '.TABLE_PREFIX.'certify_tests.certify_id = '.$certify_id.'
43 $result = mysql_query($sql, $db) or die(mysql_error() . "<br>" . $sql);
44 //echo '<code style="background-color:white;"><pre>'.$sql.'</pre></code>';
45 while( $row = mysql_fetch_assoc($result) ) {
46 if (!isset($certificate['tests'][$row['test_id']]['final_score']) || $certificate['tests'][$row['test_id']]['final_score'] < $row['final_score'])
47 $certificate['tests'][$row['test_id']]['final_score'] = $row['final_score'];
51 // Calculate new percentages for certificate.
54 $certificate['available_score'] = 0;
55 $certificate['achieved_score'] = 0;
57 if (isset($certificate['tests'])) {
58 foreach ($certificate['tests'] as $certify_testid => &$test) {
59 if ($test['final_score'] >= $test['passscore']) {
60 $certificate['achieved_score'] += $test['passscore'];
62 $certificate['achieved_score'] += $test['final_score'];
64 $certificate['available_score'] += $test['passscore'];
66 if (!isset($test['final_score'])) {
74 if ($certificate['available_score'] != 0) {
75 $certificate['progress'] = $certificate['achieved_score'] * 100 / $certificate['available_score'];
77 $certificate['progress'] = 0;
80 return $certificate['progress'];