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; }