COUNT(question_id) AS recurrence,
AVG(testlog_score) AS average_score,
AVG(testlog_change_time - testlog_display_time) AS average_time,
min(question_difficulty) AS question_difficulty
FROM ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_TEST_USER . ', ' . K_TABLE_QUESTIONS . '
WHERE testlog_testuser_id=testuser_id
AND testlog_question_id=question_id
AND testuser_test_id=' . $test_id . '
GROUP BY question_id
ORDER BY ' . $full_order_field . '';
if ($rr = F_db_query($sqlr, $db)) {
$itemcount = 1;
while ($mr = F_db_fetch_array($rr)) {
// get the question max score
$question_max_score = $testdata['test_score_right'] * $mr['question_difficulty'];
$qsttestdata = F_getQuestionTestStat($test_id, $mr['question_id']);
$pdf->SetFont($numberfont, 'B', 6);
$pdf->Cell(2 * $data_cell_width_third, $data_cell_height, $itemcount, 1, 0, 'R', 0);
$pdf->Cell($data_cell_width, $data_cell_height, $mr['recurrence'] . ' ' . F_formatPdfPercentage($mr['recurrence'] / $num_questions), 1, 0, 'R', 0);
$pdf->Cell($data_cell_width, $data_cell_height, number_format($mr['average_score'], 3, '.', '') . ' ' . F_formatPdfPercentage($mr['average_score'] / $question_max_score), 1, 0, 'R', 0);
if (stripos($mr['average_time'], ':') !== FALSE) {
// PostgreSQL returns formatted time, while MySQL returns the number of seconds
$mr['average_time'] = strtotime($mr['average_time']);
}
$pdf->Cell(4 * $data_cell_width_third, $data_cell_height, date('i:s', $mr['average_time']), 1, 0, 'R', 0);
$pdf->Cell($data_cell_width, $data_cell_height, $qsttestdata['right'] . ' ' . F_formatPdfPercentage($qsttestdata['right'] / $qsttestdata['num']), 1, 0, 'R', 0);
$pdf->Cell($data_cell_width, $data_cell_height, $qsttestdata['wrong'] . ' ' . F_formatPdfPercentage($qsttestdata['wrong'] / $qsttestdata['num']), 1, 0, 'R', 0);
$pdf->Cell($data_cell_width, $data_cell_height, $qsttestdata['unanswered'] . ' ' . F_formatPdfPercentage($qsttestdata['unanswered'] / $qsttestdata['num']), 1, 0, 'R', 0);
$pdf->Cell($data_cell_width, $data_cell_height, $qsttestdata['undisplayed'] . ' ' . F_formatPdfPercentage($qsttestdata['undisplayed'] / $qsttestdata['num']), 1, 0, 'R', 0);
$pdf->Cell($data_cell_width, $data_cell_height, $qsttestdata['unrated'] . ' ' . F_formatPdfPercentage($qsttestdata['unrated'] / $qsttestdata['num']), 1, 1, 'R', 0);
$pdf->SetFont(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA);
/**
* Export all question statistics of the selected test to XML.
* @author Nicola Asuni
* @since 2010-05-10
* @param $test_id (int) test ID
* @return XML data
*/
function F_xml_export_question_stats($test_id)
{
global $l, $db;
require_once '../config/tce_config.php';
require_once '../../shared/code/tce_authorization.php';
require_once '../code/tce_functions_auth_sql.php';
$boolean = array('false', 'true');
$type = array('single', 'multiple', 'text', 'ordering');
$xml = '';
// XML data to be returned
$xml .= '<' . '?xml version="1.0" encoding="UTF-8" ?' . '>' . K_NEWLINE;
$xml .= '<tcexamquestionstats 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 . '</header>' . K_NEWLINE;
$xml .= K_TAB . '<body>' . K_NEWLINE;
// get test data
$testdata = F_getTestData($test_id);
// get total number of questions for the selected test
$num_questions = F_count_rows(K_TABLE_TESTS_LOGS . ', ' . K_TABLE_TEST_USER, 'WHERE testlog_testuser_id=testuser_id AND testuser_test_id=' . $test_id . '');
// output questions stats
$sqlr = 'SELECT
question_id,
COUNT(question_id) AS recurrence,
AVG(testlog_score) AS average_score,
AVG(testlog_change_time - testlog_display_time) AS average_time,
min(question_difficulty) AS question_difficulty
FROM ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_TEST_USER . ', ' . K_TABLE_QUESTIONS . '
WHERE testlog_testuser_id=testuser_id
AND testlog_question_id=question_id
AND testuser_test_id=' . $test_id . '
GROUP BY question_id
ORDER BY recurrence DESC,average_score DESC';
if ($rr = F_db_query($sqlr, $db)) {
while ($mr = F_db_fetch_array($rr)) {
$xml .= K_TAB . K_TAB . '<question>' . K_NEWLINE;
// get the question max score
$question_max_score = $testdata['test_score_right'] * $mr['question_difficulty'];
$qsttestdata = F_getQuestionTestStat($test_id, $mr['question_id']);
$xml .= K_TAB . K_TAB . K_TAB . '<id>' . $mr['question_id'] . '</id>' . K_NEWLINE;
$question_description = '';
$sqlrq = 'SELECT question_description FROM ' . K_TABLE_QUESTIONS . ' WHERE question_id=' . $mr['question_id'] . '';
if ($rrq = F_db_query($sqlrq, $db)) {
if ($mrq = F_db_fetch_array($rrq)) {
$question_description = $mrq['question_description'];
}
} else {
F_display_db_error();
}
$xml .= K_TAB . K_TAB . K_TAB . '<description>' . F_text_to_xml($question_description) . '</description>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<recurrence>' . $mr['recurrence'] . '</recurrence>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<recurrence_percent>' . F_formatXMLPercentage($mr['recurrence'] / $num_questions) . '</recurrence_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<points>' . number_format($mr['average_score'], 3, '.', '') . '</points>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<points_percent>' . F_formatXMLPercentage($mr['average_score'] / $question_max_score) . '</points_percent>' . K_NEWLINE;
if (stripos($mr['average_time'], ':') !== FALSE) {
// PostgreSQL returns formatted time, while MySQL returns the number of seconds
$mr['average_time'] = strtotime($mr['average_time']);
}
$xml .= K_TAB . K_TAB . K_TAB . '<time>' . date('i:s', $mr['average_time']) . '</time>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<correct>' . $qsttestdata['right'] . '</correct>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<correct_percent>' . F_formatXMLPercentage($qsttestdata['right'] / $qsttestdata['num']) . '</correct_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<wrong>' . $qsttestdata['wrong'] . '</wrong>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<wrong_percent>' . F_formatXMLPercentage($qsttestdata['wrong'] / $qsttestdata['num']) . '</wrong_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<unanswered>' . $qsttestdata['unanswered'] . '</unanswered>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<unanswered_percent>' . F_formatXMLPercentage($qsttestdata['unanswered'] / $qsttestdata['num']) . '</unanswered_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<undisplayed>' . $qsttestdata['undisplayed'] . '</undisplayed>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<undisplayed_percent>' . F_formatXMLPercentage($qsttestdata['undisplayed'] / $qsttestdata['num']) . '</undisplayed_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<unrated>' . $qsttestdata['unrated'] . '</unrated>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<unrated_percent>' . F_formatXMLPercentage($qsttestdata['unrated'] / $qsttestdata['num']) . '</unrated_percent>' . K_NEWLINE;
// answers statistics
$sqla = 'SELECT *
FROM ' . K_TABLE_ANSWERS . '
WHERE answer_question_id=' . $mr['question_id'] . '
ORDER BY answer_id';
if ($ra = F_db_query($sqla, $db)) {
while ($ma = F_db_fetch_array($ra)) {
$xml .= K_TAB . K_TAB . K_TAB . '<answer>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<id>' . $ma['answer_id'] . '</id>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<description>' . F_text_to_xml($ma['answer_description']) . '</description>' . K_NEWLINE;
$num_all_answers = F_count_rows(K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_ANSWERS . ', ' . K_TABLE_LOG_ANSWER . ' WHERE logansw_answer_id=answer_id AND logansw_testlog_id=testlog_id AND testlog_testuser_id=testuser_id AND testuser_test_id=' . $test_id . ' AND testlog_question_id=' . $mr['question_id'] . '');
$num_answers = F_count_rows(K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_ANSWERS . ', ' . K_TABLE_LOG_ANSWER . ' WHERE answer_id=' . $ma['answer_id'] . ' AND logansw_answer_id=answer_id AND logansw_testlog_id=testlog_id AND testlog_testuser_id=testuser_id AND testuser_test_id=' . $test_id . ' AND testlog_question_id=' . $mr['question_id'] . '');
$right_answers = F_count_rows(K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_ANSWERS . ', ' . K_TABLE_LOG_ANSWER . ' WHERE answer_id=' . $ma['answer_id'] . ' AND logansw_answer_id=answer_id AND logansw_testlog_id=testlog_id AND testlog_testuser_id=testuser_id AND testuser_test_id=' . $test_id . ' AND testlog_question_id=' . $mr['question_id'] . ' AND ((answer_isright=\'0\' AND logansw_selected=0) OR (answer_isright=\'1\' AND logansw_selected=1) OR (answer_position IS NOT NULL AND logansw_position IS NOT NULL AND answer_position=logansw_position))');
$wrong_answers = F_count_rows(K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_ANSWERS . ', ' . K_TABLE_LOG_ANSWER . ' WHERE answer_id=' . $ma['answer_id'] . ' AND logansw_answer_id=answer_id AND logansw_testlog_id=testlog_id AND testlog_testuser_id=testuser_id AND testuser_test_id=' . $test_id . ' AND testlog_question_id=' . $mr['question_id'] . ' AND ((answer_isright=\'0\' AND logansw_selected=1) OR (answer_isright=\'1\' AND logansw_selected=0) OR (answer_position IS NOT NULL AND answer_position!=logansw_position))');
$unanswered = F_count_rows(K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_ANSWERS . ', ' . K_TABLE_LOG_ANSWER . ' WHERE answer_id=' . $ma['answer_id'] . ' AND logansw_answer_id=answer_id AND logansw_testlog_id=testlog_id AND testlog_testuser_id=testuser_id AND testuser_test_id=' . $test_id . ' AND testlog_question_id=' . $mr['question_id'] . ' AND logansw_selected=-1');
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<recurrence>' . $num_answers . '</recurrence>' . K_NEWLINE;
$perc = 0;
if ($num_all_answers > 0) {
$perc = $num_answers / $num_all_answers;
}
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<recurrence_percent>' . F_formatXMLPercentage($perc) . '</recurrence_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<correct>' . $right_answers . '</correct>' . K_NEWLINE;
$perc = 0;
if ($num_answers > 0) {
$perc = $right_answers / $num_answers;
}
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<correct_percent>' . F_formatXMLPercentage($perc) . '</correct_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<wrong>' . $wrong_answers . '</wrong>' . K_NEWLINE;
$perc = 0;
if ($num_answers > 0) {
$perc = round($wrong_answers / $num_answers);
}
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<wrong_percent>' . F_formatXMLPercentage($perc) . '</wrong_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<unanswered>' . $unanswered . '</unanswered>' . K_NEWLINE;
$perc = 0;
if ($num_answers > 0) {
$perc = round($unanswered / $num_answers);
}
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<unanswered_percent>' . F_formatXMLPercentage($perc) . '</unanswered_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '</answer>' . K_NEWLINE;
}
} else {
F_display_db_error();
}
$xml .= K_TAB . K_TAB . '</question>' . K_NEWLINE;
}
} else {
F_display_db_error();
}
$xml .= K_TAB . '</body>' . K_NEWLINE;
$xml .= '</tcexamquestionstats>' . K_NEWLINE;
return $xml;
}
