}
                $xml .= K_TAB . K_TAB . K_TAB . K_TAB . '</question>' . K_NEWLINE;
                $itemcount++;
            }
            // end questions
            $xml .= K_TAB . K_TAB . K_TAB . '</details>' . K_NEWLINE;
        } else {
            F_display_db_error();
        }
        $xml .= K_TAB . K_TAB . '</user>' . K_NEWLINE;
    }
} else {
    F_display_db_error();
}
// calculate statistics
$stats = F_getArrayStatistics($statsdata);
$excludestat = array('sum', 'variance');
$calcpercent = array('mean', 'median', 'mode', 'minimum', 'maximum', 'range', 'standard_deviation');
$xml .= K_TAB . K_TAB . '<teststatistics>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<passed>' . $passed . '</passed>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<passed_percent>' . round(100 * ($passed / $stats['number']['score'])) . '</passed_percent>' . K_NEWLINE;
foreach ($stats as $row => $columns) {
    if (!in_array($row, $excludestat)) {
        $xml .= K_TAB . K_TAB . K_TAB . '<' . $row . '>' . K_NEWLINE;
        $xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<score>' . round($columns['score'], 3) . '</score>' . K_NEWLINE;
        if (in_array($row, $calcpercent)) {
            $xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<score_percent>' . round(100 * ($columns['score'] / $usrtestdata['max_score'])) . '</score_percent>' . K_NEWLINE;
        }
        $xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<right>' . round($columns['right'], 3) . '</right>' . K_NEWLINE;
        if (in_array($row, $calcpercent)) {
            $xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<right_percent>' . round(100 * ($columns['right'] / $usrtestdata['all'])) . '</right_percent>' . K_NEWLINE;
/**
* Returns users statistic array for the selected test.
* @param $test_id (int) test ID.
* @param $group_id (int) group ID - if greater than zero, filter stats for the specified user group.
* @param $user_id (int) user ID - if greater than zero, filter stats for the specified user.
* @param $startdate (string) start date ID - if greater than zero, filter stats for the specified starting date
* @param $enddate (string) end date ID - if greater than zero, filter stats for the specified ending date
* @param $full_order_field (string) Ordering fields for SQL query.
* @param $pubmode (boolean) If true filter the results for the public interface.
* @param $stats (int) 2 = full stats; 1 = user stats; 0 = disabled stats;
* return $data array containing test statistics.
*/
function F_getAllUsersTestStat($test_id, $group_id = 0, $user_id = 0, $startdate = 0, $enddate = 0, $full_order_field = 'total_score', $pubmode = false, $stats = 2)
{
    require_once '../config/tce_config.php';
    require_once '../../shared/code/tce_functions_test.php';
    require_once '../../shared/code/tce_functions_statistics.php';
    global $db, $l;
    $test_id = intval($test_id);
    $group_id = intval($group_id);
    $user_id = intval($user_id);
    $data = array();
    $data['svgpoints'] = '';
    $data['testuser'] = array();
    $sqlr = 'SELECT
		testuser_id,
		testuser_test_id,
		testuser_creation_time,
		testuser_status,
		user_id,
		user_lastname,
		user_firstname,
		user_name,
		user_email,
		SUM(testlog_score) AS total_score,
		MAX(testlog_change_time) AS testuser_end_time
		FROM ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_TEST_USER . ', ' . K_TABLE_USERS . '';
    if ($group_id > 0) {
        $sqlr .= ',' . K_TABLE_USERGROUP . '';
    }
    $sqlr .= ' WHERE testlog_testuser_id=testuser_id AND testuser_user_id=user_id';
    if ($test_id > 0) {
        $sqlr .= ' AND testuser_test_id=' . $test_id . '';
    } elseif ($pubmode) {
        $sqlr .= ' AND testuser_test_id IN (' . F_getTestIDResults($test_id, $user_id) . ')';
    }
    if ($group_id > 0) {
        $sqlr .= ' AND usrgrp_user_id=user_id AND usrgrp_group_id=' . $group_id . '';
    }
    if ($user_id > 0) {
        $sqlr .= ' AND user_id=' . $user_id . '';
    }
    if (!empty($startdate)) {
        $startdate_time = strtotime($startdate);
        $startdate = date(K_TIMESTAMP_FORMAT, $startdate_time);
        $sqlr .= ' AND testuser_creation_time>=\'' . $startdate . '\'';
    }
    if (!empty($enddate)) {
        $enddate_time = strtotime($enddate);
        $enddate = date(K_TIMESTAMP_FORMAT, $enddate_time);
        $sqlr .= ' AND testuser_creation_time<=\'' . $enddate . '\'';
    }
    if ($stats > 1) {
        // get stats
        $data += F_getTestStat($test_id, $group_id, $user_id, $startdate, $enddate);
    }
    $sqlr .= ' GROUP BY testuser_id, testuser_test_id, testuser_creation_time, user_id, user_lastname, user_firstname, user_name, user_email, testuser_status
		ORDER BY ' . $full_order_field . '';
    if ($rr = F_db_query($sqlr, $db)) {
        $itemcount = 0;
        $passed = 0;
        $statsdata = array();
        $statsdata['score'] = array();
        $statsdata['right'] = array();
        $statsdata['wrong'] = array();
        $statsdata['unanswered'] = array();
        $statsdata['undisplayed'] = array();
        $statsdata['unrated'] = array();
        while ($mr = F_db_fetch_array($rr)) {
            $itemcount++;
            $usrtestdata = F_getUserTestStat($mr['testuser_test_id'], $mr['user_id'], $mr['testuser_id']);
            if ($stats > 0) {
                $teststat = F_getTestStat($mr['testuser_test_id'], $group_id, $mr['user_id'], $startdate, $enddate, $mr['testuser_id']);
            }
            $data['testuser']['\'' . $mr['testuser_id'] . '\''] = array();
            $data['testuser']['\'' . $mr['testuser_id'] . '\'']['test'] = $usrtestdata;
            $data['testuser']['\'' . $mr['testuser_id'] . '\'']['num'] = $itemcount;
            $data['testuser']['\'' . $mr['testuser_id'] . '\'']['id'] = $mr['testuser_id'];
            $data['testuser']['\'' . $mr['testuser_id'] . '\'']['user_id'] = $mr['user_id'];
            $halfscore = $usrtestdata['test_max_score'] / 2;
            $data['testuser']['\'' . $mr['testuser_id'] . '\'']['testuser_creation_time'] = $mr['testuser_creation_time'];
            $data['testuser']['\'' . $mr['testuser_id'] . '\'']['testuser_end_time'] = $mr['testuser_end_time'];
            if ($mr['testuser_end_time'] <= 0 or strtotime($mr['testuser_end_time']) < strtotime($mr['testuser_creation_time'])) {
                $time_diff = $usrtestdata['test_duration_time'] * K_SECONDS_IN_MINUTE;
            } else {
                $time_diff = strtotime($mr['testuser_end_time']) - strtotime($mr['testuser_creation_time']);
                //sec
            }
            $data['testuser']['\'' . $mr['testuser_id'] . '\'']['time_diff'] = gmdate('H:i:s', $time_diff);
            $passmsg = false;
            if ($usrtestdata['test_score_threshold'] > 0) {
                if ($usrtestdata['user_score'] >= $usrtestdata['test_score_threshold']) {
                    $passmsg = true;
                    $passed++;
                }
            } elseif ($usrtestdata['user_score'] > $halfscore) {
                $passmsg = true;
                $passed++;
            }
            if ($usrtestdata['test_max_score'] > 0) {
                $total_score_perc = round(100 * $mr['total_score'] / $usrtestdata['test_max_score']);
            } else {
                $total_score_perc = 0;
            }
            $data['testuser']['\'' . $mr['testuser_id'] . '\'']['passmsg'] = $passmsg;
            $data['testuser']['\'' . $mr['testuser_id'] . '\'']['user_name'] = $mr['user_name'];
            $data['testuser']['\'' . $mr['testuser_id'] . '\'']['user_email'] = $mr['user_email'];
            $data['testuser']['\'' . $mr['testuser_id'] . '\'']['user_lastname'] = $mr['user_lastname'];
            $data['testuser']['\'' . $mr['testuser_id'] . '\'']['user_firstname'] = $mr['user_firstname'];
            $data['testuser']['\'' . $mr['testuser_id'] . '\'']['total_score'] = $mr['total_score'];
            $data['testuser']['\'' . $mr['testuser_id'] . '\'']['total_score_perc'] = $total_score_perc;
            if ($stats > 0) {
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['right'] = $teststat['qstats']['right'];
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['right_perc'] = $teststat['qstats']['right_perc'];
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['wrong'] = $teststat['qstats']['wrong'];
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['wrong_perc'] = $teststat['qstats']['wrong_perc'];
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['unanswered'] = $teststat['qstats']['unanswered'];
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['unanswered_perc'] = $teststat['qstats']['unanswered_perc'];
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['undisplayed'] = $teststat['qstats']['undisplayed'];
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['undisplayed_perc'] = $teststat['qstats']['undisplayed_perc'];
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['unrated'] = $teststat['qstats']['unrated'];
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['unrated_perc'] = $teststat['qstats']['unrated_perc'];
            } else {
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['right'] = '';
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['right_perc'] = '';
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['wrong'] = '';
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['wrong_perc'] = '';
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['unanswered'] = '';
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['unanswered_perc'] = '';
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['undisplayed'] = '';
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['undisplayed_perc'] = '';
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['unrated'] = '';
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['unrated_perc'] = '';
            }
            if ($mr['testuser_status'] > 3) {
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['locked'] = true;
            } else {
                $data['testuser']['\'' . $mr['testuser_id'] . '\'']['locked'] = false;
            }
            // remaining user time in minutes
            $data['testuser']['\'' . $mr['testuser_id'] . '\'']['remaining_time'] = round((time() - strtotime($usrtestdata['user_test_start_time'])) / K_SECONDS_IN_MINUTE) - $usrtestdata['test_duration_time'];
            $data['testuser']['\'' . $mr['testuser_id'] . '\'']['user_comment'] = $usrtestdata['user_comment'];
            // SVG points
            $data['svgpoints'] .= 'x' . $data['testuser']['\'' . $mr['testuser_id'] . '\'']['total_score_perc'] . 'v' . $data['testuser']['\'' . $mr['testuser_id'] . '\'']['right_perc'];
            // collects data for descriptive statistics
            $statsdata['score'][] = $mr['total_score'];
            $statsdata['score_perc'][] = $total_score_perc;
            if ($stats > 0) {
                $statsdata['right'][] = $teststat['qstats']['right'];
                $statsdata['right_perc'][] = $teststat['qstats']['right_perc'];
                $statsdata['wrong'][] = $teststat['qstats']['wrong'];
                $statsdata['wrong_perc'][] = $teststat['qstats']['wrong_perc'];
                $statsdata['unanswered'][] = $teststat['qstats']['unanswered'];
                $statsdata['unanswered_perc'][] = $teststat['qstats']['unanswered_perc'];
                $statsdata['undisplayed'][] = $teststat['qstats']['undisplayed'];
                $statsdata['undisplayed_perc'][] = $teststat['qstats']['undisplayed_perc'];
                $statsdata['unrated'][] = $teststat['qstats']['unrated'];
                $statsdata['unrated_perc'][] = $teststat['qstats']['unrated_perc'];
            } else {
                $statsdata['right'][] = '';
                $statsdata['right_perc'][] = '';
                $statsdata['wrong'][] = '';
                $statsdata['wrong_perc'][] = '';
                $statsdata['unanswered'][] = '';
                $statsdata['unanswered_perc'][] = '';
                $statsdata['undisplayed'][] = '';
                $statsdata['undisplayed_perc'][] = '';
                $statsdata['unrated'][] = '';
                $statsdata['unrated_perc'][] = '';
            }
        }
    } else {
        F_display_db_error();
    }
    $data['passed'] = $passed;
    $passed_perc = 0;
    if ($itemcount > 0) {
        $passed_perc = round(100 * $passed / $itemcount);
    }
    $data['passed_perc'] = $passed_perc;
    $data['num_records'] = $itemcount;
    if ($itemcount > 0) {
        // calculate statistics
        $data['statistics'] = F_getArrayStatistics($statsdata);
    }
    return $data;
}
Example #3
0
/**
 * Export user results in XML format.
 * @param $user_id (int) user ID - if greater than zero, filter stats for the specified user.
 * @param $startdate (string) start date ID - if greater than zero, filter stats for the specified starting date
 * @param $enddate (string) end date ID - if greater than zero, filter stats for the specified ending date
 * @param $order_field (string) Ordering fields for SQL query.
 * @author Nicola Asuni
 * @return XML data
 */
function F_xml_export_user_results($user_id, $startdate, $enddate, $order_field)
{
    global $l, $db;
    require_once '../config/tce_config.php';
    // define symbols for answers list
    $qtype = array('S', 'M', 'T', 'O');
    // question types
    $type = array('single', 'multiple', 'text', 'ordering');
    $boolean = array('false', 'true');
    $xml = '';
    // XML data to be returned
    $xml .= '<' . '?xml version="1.0" encoding="UTF-8" ?' . '>' . K_NEWLINE;
    $xml .= '<tcexamuserresults version="' . K_TCEXAM_VERSION . '">' . K_NEWLINE;
    $xml .= K_TAB . '<header';
    $xml .= ' lang="' . K_USER_LANG . '"';
    $xml .= ' date="' . date(K_TIMESTAMP_FORMAT) . '">' . K_NEWLINE;
    $xml .= K_TAB . K_TAB . '<user_id>' . $user_id . '</user_id>' . K_NEWLINE;
    $sql = 'SELECT user_name, user_lastname, user_firstname FROM ' . K_TABLE_USERS . ' WHERE user_id=' . $user_id . '';
    if ($r = F_db_query($sql, $db)) {
        if ($m = F_db_fetch_array($r)) {
            $xml .= K_TAB . K_TAB . '<user_name>' . $m['user_name'] . '</user_name>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . '<user_lastname>' . $m['user_lastname'] . '</user_lastname>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . '<user_firstname>' . $m['user_firstname'] . '</user_firstname>' . K_NEWLINE;
        }
    } else {
        F_display_db_error();
    }
    $xml .= K_TAB . K_TAB . '<date_from>' . $startdate . '</date_from>' . K_NEWLINE;
    $xml .= K_TAB . K_TAB . '<date_to>' . $enddate . '</date_to>' . K_NEWLINE;
    $xml .= K_TAB . '</header>' . K_NEWLINE;
    $xml .= K_TAB . '<body>' . K_NEWLINE;
    $statsdata = array();
    $statsdata['score'] = array();
    $statsdata['right'] = array();
    $statsdata['wrong'] = array();
    $statsdata['unanswered'] = array();
    $statsdata['undisplayed'] = array();
    $statsdata['unrated'] = array();
    $sql = 'SELECT
			testuser_id,
			test_id,
			test_name,
			testuser_creation_time,
			testuser_status,
			SUM(testlog_score) AS total_score,
			MAX(testlog_change_time) AS testuser_end_time
		FROM ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS . '
		WHERE testuser_status>0
			AND testuser_creation_time>=\'' . F_escape_sql($db, $startdate) . '\'
			AND testuser_creation_time<=\'' . F_escape_sql($db, $enddate) . '\'
			AND testuser_user_id=' . $user_id . '
			AND testlog_testuser_id=testuser_id
			AND testuser_test_id=test_id';
    if ($_SESSION['session_user_level'] < K_AUTH_ADMINISTRATOR) {
        $sql .= ' AND test_user_id IN (' . F_getAuthorizedUsers($_SESSION['session_user_id']) . ')';
    }
    $sql .= ' GROUP BY testuser_id, test_id, test_name, testuser_creation_time, testuser_status ORDER BY ' . F_escape_sql($db, $order_field) . '';
    if ($r = F_db_query($sql, $db)) {
        $passed = 0;
        while ($m = F_db_fetch_array($r)) {
            $testuser_id = $m['testuser_id'];
            $usrtestdata = F_getUserTestStat($m['test_id'], $user_id);
            $halfscore = $usrtestdata['max_score'] / 2;
            $xml .= K_TAB . K_TAB . '<test id=\'' . $m['test_id'] . '\'>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . '<start_time>' . $m['testuser_creation_time'] . '</start_time>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . '<end_time>' . $m['testuser_end_time'] . '</end_time>' . K_NEWLINE;
            $time_diff = strtotime($m['testuser_end_time']) - strtotime($m['testuser_creation_time']);
            //sec
            $time_diff = gmdate('H:i:s', $time_diff);
            $xml .= K_TAB . K_TAB . K_TAB . '<time>' . $time_diff . '</time>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . '<name>' . F_text_to_xml($m['test_name']) . '</name>' . K_NEWLINE;
            if ($usrtestdata['score_threshold'] > 0) {
                if ($usrtestdata['score'] >= $usrtestdata['score_threshold']) {
                    $xml .= K_TAB . K_TAB . K_TAB . '<passed>true</passed>' . K_NEWLINE;
                    $passed++;
                } else {
                    $xml .= K_TAB . K_TAB . K_TAB . '<passed>false</passed>' . K_NEWLINE;
                }
            } elseif ($usrtestdata['score'] > $halfscore) {
                $passed++;
            }
            $xml .= K_TAB . K_TAB . K_TAB . '<score>' . round($m['total_score'], 3) . '</score>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . '<score_percent>' . round(100 * $usrtestdata['score'] / $usrtestdata['max_score']) . '</score_percent>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . '<right>' . $usrtestdata['right'] . '</right>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . '<right_percent>' . round(100 * $usrtestdata['right'] / $usrtestdata['all']) . '</right_percent>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . '<wrong>' . $usrtestdata['wrong'] . '</wrong>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . '<wrong_percent>' . round(100 * $usrtestdata['wrong'] / $usrtestdata['all']) . '</wrong_percent>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . '<unanswered>' . $usrtestdata['unanswered'] . '</unanswered>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . '<unanswered_percent>' . round(100 * $usrtestdata['unanswered'] / $usrtestdata['all']) . '</unanswered_percent>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . '<undisplayed>' . $usrtestdata['undisplayed'] . '</undisplayed>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . '<undisplayed_percent>' . round(100 * $usrtestdata['undisplayed'] / $usrtestdata['all']) . '</undisplayed_percent>' . K_NEWLINE;
            if ($m['testuser_status'] == 4) {
                $status = $l['w_locked'];
            } else {
                $status = $l['w_unlocked'];
            }
            $xml .= K_TAB . K_TAB . K_TAB . '<status>' . $status . '</status>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . '<comment>' . F_text_to_xml($usrtestdata['comment']) . '</comment>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . '</test>' . K_NEWLINE;
            // collects data for descriptive statistics
            $statsdata['score'][] = $m['total_score'] / $usrtestdata['max_score'];
            $statsdata['right'][] = $usrtestdata['right'] / $usrtestdata['all'];
            $statsdata['wrong'][] = $usrtestdata['wrong'] / $usrtestdata['all'];
            $statsdata['unanswered'][] = $usrtestdata['unanswered'] / $usrtestdata['all'];
            $statsdata['undisplayed'][] = $usrtestdata['undisplayed'] / $usrtestdata['all'];
            $statsdata['unrated'][] = $usrtestdata['unrated'] / $usrtestdata['all'];
        }
    } else {
        F_display_db_error();
    }
    // calculate statistics
    $stats = F_getArrayStatistics($statsdata);
    $excludestat = array('sum', 'variance');
    $calcpercent = array('mean', 'median', 'mode', 'minimum', 'maximum', 'range', 'standard_deviation');
    $xml .= K_TAB . K_TAB . '<teststatistics>' . K_NEWLINE;
    $xml .= K_TAB . K_TAB . K_TAB . '<passed>' . $passed . '</passed>' . K_NEWLINE;
    $passed_perc = 0;
    if ($itemcount > 0) {
        $passed_perc = $passed / $stats['number']['score'];
    }
    $xml .= K_TAB . K_TAB . K_TAB . '<passed_percent>' . round(100 * $passed_perc) . '</passed_percent>' . K_NEWLINE;
    foreach ($stats as $row => $columns) {
        if (!in_array($row, $excludestat)) {
            $xml .= K_TAB . K_TAB . K_TAB . '<' . $row . '>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<score>' . round($columns['score'], 3) . '</score>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<right>' . round($columns['right'], 3) . '</right>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<wrong>' . round($columns['wrong'], 3) . '</wrong>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<unanswered>' . round($columns['unanswered'], 3) . '</unanswered>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<undisplayed>' . round($columns['undisplayed'], 3) . '</undisplayed>' . K_NEWLINE;
            $xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<unrated>' . round($columns['unrated'], 3) . '</unrated>' . K_NEWLINE;
            if (in_array($row, $calcpercent)) {
                $xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<score_percent>' . round(100 * ($columns['score'] / $usrtestdata['max_score'])) . '</score_percent>' . K_NEWLINE;
                $xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<right_percent>' . round(100 * ($columns['right'] / $usrtestdata['all'])) . '</right_percent>' . K_NEWLINE;
                $xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<wrong_percent>' . round(100 * ($columns['wrong'] / $usrtestdata['all'])) . '</wrong_percent>' . K_NEWLINE;
                $xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<unanswered_percent>' . round(100 * ($columns['unanswered'] / $usrtestdata['all'])) . '</unanswered_percent>' . K_NEWLINE;
                $xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<undisplayed_percent>' . round(100 * ($columns['undisplayed'] / $usrtestdata['all'])) . '</undisplayed_percent>' . K_NEWLINE;
                $xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<unrated_percent>' . round(100 * ($columns['unrated'] / $usrtestdata['all'])) . '</unrated_percent>' . K_NEWLINE;
            }
            $xml .= K_TAB . K_TAB . K_TAB . '</' . $row . '>' . K_NEWLINE;
        }
    }
    $xml .= K_TAB . K_TAB . '</teststatistics>' . K_NEWLINE;
    $xml .= K_TAB . '</body>' . K_NEWLINE;
    $xml .= '</tcexamuserresults>' . K_NEWLINE;
    return $xml;
}
/**
 * Export all test results to CSV.
 * @author Nicola Asuni
 * @since 2006-03-30
 * @param $test_id (int) Test ID
 * @param $group_id (int) Group ID
 * @param $order_field (string) ORDER BY portion of the SQL query
 * @return CSV data
 */
function F_csv_export_result_allusers($test_id, $group_id = 0, $order_field = "")
{
    global $l, $db;
    require_once '../config/tce_config.php';
    require_once '../../shared/code/tce_authorization.php';
    require_once '../../shared/code/tce_functions_test_stats.php';
    require_once 'tce_functions_user_select.php';
    require_once '../code/tce_functions_statistics.php';
    $test_id = intval($test_id);
    $group_id = intval($group_id);
    $order_field = F_escape_sql($order_field);
    // check user's authorization
    if (!F_isAuthorizedUser(K_TABLE_TESTS, 'test_id', $test_id, 'test_user_id')) {
        return '';
    }
    if (!F_isAuthorizedEditorForGroup($group_id)) {
        return '';
    }
    // statistical data
    $statsdata = array();
    $statsdata['score'] = array();
    $statsdata['right'] = array();
    $statsdata['wrong'] = array();
    $statsdata['unanswered'] = array();
    $statsdata['undisplayed'] = array();
    $statsdata['unrated'] = array();
    $csv = '';
    // CSV data to be returned
    // general data
    $csv .= 'TCExam Results Summary' . K_NEWLINE . K_NEWLINE;
    $csv .= 'version' . K_TAB . K_TCEXAM_VERSION . K_NEWLINE;
    $csv .= 'lang' . K_TAB . K_USER_LANG . K_NEWLINE;
    $csv .= 'date' . K_TAB . date(K_TIMESTAMP_FORMAT) . K_NEWLINE;
    $csv .= 'test_id' . K_TAB . $test_id . K_NEWLINE;
    $csv .= 'group_id' . K_TAB . $group_id . K_NEWLINE;
    $csv .= K_NEWLINE . K_NEWLINE;
    // separator
    // print column names
    $csv .= '#';
    $csv .= K_TAB . $l['w_time_begin'];
    $csv .= K_TAB . $l['w_time_end'];
    $csv .= K_TAB . $l['w_time'];
    $csv .= K_TAB . $l['w_lastname'];
    $csv .= K_TAB . $l['w_firstname'];
    $csv .= K_TAB . $l['w_user'];
    $csv .= K_TAB . $l['w_passed'];
    $csv .= K_TAB . $l['w_score'];
    $csv .= K_TAB . $l['w_answers_right'];
    $csv .= K_TAB . $l['w_answers_wrong'];
    $csv .= K_TAB . $l['w_questions_unanswered'];
    $csv .= K_TAB . $l['w_questions_undisplayed'];
    $csv .= K_TAB . $l['w_questions_unrated'];
    $csv .= K_TAB . $l['w_comment'];
    $passed = 0;
    // output users stats
    $sqlr = 'SELECT
		testuser_id,
		testuser_creation_time,
		user_id,
		user_lastname,
		user_firstname,
		user_name,
		SUM(testlog_score) AS total_score,
		MAX(testlog_change_time) AS testuser_end_time
		FROM ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_TEST_USER . ', ' . K_TABLE_USERS . '
		WHERE testlog_testuser_id=testuser_id
			AND testuser_user_id=user_id
			AND testuser_test_id=' . $test_id . '';
    if ($group_id > 0) {
        $sqlr .= ' AND testuser_user_id IN (
				SELECT usrgrp_user_id
				FROM ' . K_TABLE_USERGROUP . '
				WHERE usrgrp_group_id=' . $group_id . '
			)';
    }
    if ($_SESSION['session_user_level'] < K_AUTH_ADMINISTRATOR) {
        $sqlr .= ' AND (user_level<' . $_SESSION['session_user_level'] . ' OR user_id=' . $_SESSION['session_user_id'] . ')';
    }
    $sqlr .= ' GROUP BY testuser_id, testuser_creation_time, user_id, user_lastname, user_firstname, user_name
		ORDER BY ' . $order_field . '';
    if ($rr = F_db_query($sqlr, $db)) {
        $itemcount = 0;
        while ($mr = F_db_fetch_array($rr)) {
            $itemcount++;
            $csv .= K_NEWLINE . $itemcount;
            $csv .= K_TAB . $mr['testuser_creation_time'];
            $csv .= K_TAB . $mr['testuser_end_time'];
            $time_diff = strtotime($mr['testuser_end_time']) - strtotime($mr['testuser_creation_time']);
            //sec
            $time_diff = gmdate('H:i:s', $time_diff);
            $csv .= K_TAB . $time_diff;
            $csv .= K_TAB . $mr['user_lastname'];
            $csv .= K_TAB . $mr['user_firstname'];
            $csv .= K_TAB . $mr['user_name'];
            $usrtestdata = F_getUserTestStat($test_id, $mr['user_id']);
            $halfscore = $usrtestdata['max_score'] / 2;
            if ($usrtestdata['score_threshold'] > 0) {
                if ($usrtestdata['score'] >= $usrtestdata['score_threshold']) {
                    $csv .= K_TAB . 'true';
                    $passed++;
                } else {
                    $csv .= K_TAB . 'false';
                }
            } else {
                $csv .= K_TAB;
                if ($usrtestdata['score'] > $halfscore) {
                    $passed++;
                }
            }
            $csv .= K_TAB . $mr['total_score'];
            $csv .= K_TAB . $usrtestdata['right'];
            $csv .= K_TAB . $usrtestdata['wrong'];
            $csv .= K_TAB . $usrtestdata['unanswered'];
            $csv .= K_TAB . $usrtestdata['undisplayed'];
            $csv .= K_TAB . $usrtestdata['unrated'];
            $csv .= K_TAB . F_compact_string(htmlspecialchars($usrtestdata['comment'], ENT_NOQUOTES, $l['a_meta_charset']));
            // collects data for descriptive statistics
            $statsdata['score'][] = $mr['total_score'] / $usrtestdata['max_score'];
            $statsdata['right'][] = $usrtestdata['right'] / $usrtestdata['all'];
            $statsdata['wrong'][] = $usrtestdata['wrong'] / $usrtestdata['all'];
            $statsdata['unanswered'][] = $usrtestdata['unanswered'] / $usrtestdata['all'];
            $statsdata['undisplayed'][] = $usrtestdata['undisplayed'] / $usrtestdata['all'];
            $statsdata['unrated'][] = $usrtestdata['unrated'] / $usrtestdata['all'];
        }
    } else {
        F_display_db_error();
    }
    $csv .= K_NEWLINE;
    // separator
    // calculate statistics
    $stats = F_getArrayStatistics($statsdata);
    $excludestat = array('sum', 'variance');
    $calcpercent = array('mean', 'median', 'mode', 'minimum', 'maximum', 'range', 'standard_deviation');
    $csv .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . 'passed_total' . K_TAB . $passed . K_NEWLINE;
    $csv .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . 'passed_percent [%]' . K_TAB . round(100 * ($passed / $itemcount)) . K_NEWLINE;
    $csv .= K_NEWLINE;
    // separator
    $csv .= $l['w_statistics'] . K_NEWLINE;
    // separator
    // headers
    $csv .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB;
    $csv .= $l['w_score'] . K_TAB;
    $csv .= $l['w_answers_right_th'] . K_TAB;
    $csv .= $l['w_answers_wrong_th'] . K_TAB;
    $csv .= $l['w_questions_unanswered_th'] . K_TAB;
    $csv .= $l['w_questions_undisplayed_th'] . K_TAB;
    $csv .= $l['w_questions_unrated'] . K_NEWLINE;
    foreach ($stats as $row => $columns) {
        if (!in_array($row, $excludestat)) {
            $csv .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . $l['w_' . $row] . K_TAB;
            $csv .= round($columns['score'], 3) . K_TAB;
            $csv .= round($columns['right'], 3) . K_TAB;
            $csv .= round($columns['wrong'], 3) . K_TAB;
            $csv .= round($columns['unanswered'], 3) . K_TAB;
            $csv .= round($columns['undisplayed'], 3) . K_TAB;
            $csv .= round($columns['unrated'], 3) . K_NEWLINE;
            if (in_array($row, $calcpercent)) {
                $csv .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . $row . ' [%]' . K_TAB;
                $csv .= round(100 * ($columns['score'] / $usrtestdata['max_score'])) . K_TAB;
                $csv .= round(100 * ($columns['right'] / $usrtestdata['all'])) . K_TAB;
                $csv .= round(100 * ($columns['wrong'] / $usrtestdata['all'])) . K_TAB;
                $csv .= round(100 * ($columns['unanswered'] / $usrtestdata['all'])) . K_TAB;
                $csv .= round(100 * ($columns['undisplayed'] / $usrtestdata['all'])) . K_TAB;
                $csv .= round(100 * ($columns['unrated'] / $usrtestdata['all'])) . K_NEWLINE;
            }
        }
    }
    return $csv;
}