/**
* 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;
}
$sqlt = 'SELECT testuser_user_id FROM ' . K_TABLE_TEST_USER . ' WHERE testuser_test_id=' . $test_id . ' AND testuser_id=' . $testuser_id . '';
if ($rt = F_db_query($sqlt, $db)) {
if ($mt = F_db_fetch_assoc($rt)) {
$checkid = $mt['testuser_user_id'];
}
} else {
F_display_db_error();
}
if ($user_id != $checkid) {
header('Location: index.php');
//redirect browser to public main page
exit;
}
// get user's test stats
$userdata = F_getUserData($user_id);
$teststat = F_getTestStat($test_id, 0, $user_id, 0, 0, $testuser_id);
$teststat['testinfo'] = F_getUserTestStat($test_id, $user_id, $testuser_id);
$test_id = $teststat['testinfo']['test_id'];
if (!F_getBoolean($teststat['testinfo']['test_results_to_users'])) {
header('Location: index.php');
//redirect browser to public main page
exit;
}
//lock user's test
F_lockUserTest($test_id, $_SESSION['session_user_id']);
echo '<div class="container">' . K_NEWLINE;
echo '<div class="tceformbox">' . K_NEWLINE;
$usr_all = htmlspecialchars($userdata['user_lastname'] . ' ' . $userdata['user_firstname'] . ' - ' . $userdata['user_name'] . '', ENT_NOQUOTES, $l['a_meta_charset']);
echo getFormDescriptionLine($l['w_user'] . ':', $l['w_user'], $usr_all);
$test_all = '<strong>' . htmlspecialchars($teststat['testinfo']['test_name'], ENT_NOQUOTES, $l['a_meta_charset']) . '</strong><br />' . K_NEWLINE;
$test_all .= htmlspecialchars($teststat['testinfo']['test_description'], ENT_NOQUOTES, $l['a_meta_charset']);
/**
* print test details for the selected user
* @param $data (array) Testuser data array.
* @param $onlytext (boolean) If true print only text questions.
*/
public function printUserTestDetails($data, $onlytext = false)
{
require_once '../config/tce_config.php';
require_once '../../shared/code/tce_functions_test_stats.php';
require_once '../../shared/code/tce_functions_tcecode.php';
global $db, $l;
$testuser_id = intval($data['id']);
$qtype = array('S', 'M', 'T', 'O');
// question types
$num_column = 7;
$tce_data_cell_width = round($this->tce_page_width / $num_column, 2);
$tce_data_cell_width_third = round($tce_data_cell_width / 3, 2);
$tce_data_cell_width_half = round($tce_data_cell_width / 2, 2);
$numberfont = 'courier';
// display user questions
$sql = 'SELECT *
FROM ' . K_TABLE_QUESTIONS . ', ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_SUBJECTS . ', ' . K_TABLE_MODULES . '
WHERE question_id=testlog_question_id
AND testlog_testuser_id=' . $testuser_id . '
AND question_subject_id=subject_id
AND subject_module_id=module_id';
if ($onlytext) {
// display only TEXT questions
$sql .= ' AND question_type=3';
}
$sql .= ' ORDER BY testlog_id';
if ($r = F_db_query($sql, $db)) {
$this->SetFont(PDF_FONT_NAME_DATA, 'B', PDF_FONT_SIZE_DATA);
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, '#', 1, 0, 'C', 1);
$this->Cell($tce_data_cell_width, $this->tce_data_cell_height, $l['w_score'], 1, 0, 'C', 1);
$this->Cell($tce_data_cell_width, $this->tce_data_cell_height, $l['w_ip'], 1, 0, 'C', 1);
$this->Cell($tce_data_cell_width + $tce_data_cell_width_third, $this->tce_data_cell_height, $l['w_start'] . ' [' . $l['w_time_hhmmss'] . ']', 1, 0, 'C', 1);
$this->Cell($tce_data_cell_width + $tce_data_cell_width_third, $this->tce_data_cell_height, $l['w_end'] . ' [' . $l['w_time_hhmmss'] . ']', 1, 0, 'C', 1);
$this->Cell($tce_data_cell_width, $this->tce_data_cell_height, $l['w_time'] . ' [' . $l['w_time_mmss'] . ']', 1, 0, 'C', 1);
$this->Cell($tce_data_cell_width, $this->tce_data_cell_height, $l['w_reaction'] . ' [sec]', 1, 1, 'C', 1);
$this->Ln($this->tce_data_cell_height);
// print table rows
$this->SetFont(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA);
$itemcount = 1;
while ($m = F_db_fetch_array($r)) {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, $itemcount . ' ' . $qtype[$m['question_type'] - 1], 1, 0, 'R', 0);
$this->Cell($tce_data_cell_width, $this->tce_data_cell_height, $m['testlog_score'], 1, 0, 'C', 0);
$this->Cell($tce_data_cell_width, $this->tce_data_cell_height, getIpAsString($m['testlog_user_ip']), 1, 0, 'C', 0);
if (isset($m['testlog_display_time']) and strlen($m['testlog_display_time']) > 0) {
$display_time = substr($m['testlog_display_time'], 11, 8);
} else {
$display_time = '--:--:--';
}
if (isset($m['testlog_change_time']) and strlen($m['testlog_change_time']) > 0) {
$change_time = substr($m['testlog_change_time'], 11, 8);
} else {
$change_time = '--:--:--';
}
if (isset($m['testlog_display_time']) and isset($m['testlog_change_time'])) {
$diff_time = date('i:s', strtotime($m['testlog_change_time']) - strtotime($m['testlog_display_time']));
} else {
$diff_time = '--:--';
}
if (isset($m['testlog_reaction_time']) and strlen($m['testlog_reaction_time']) > 0) {
$reaction_time = $m['testlog_reaction_time'] / 1000;
} else {
$reaction_time = '';
}
$this->Cell($tce_data_cell_width + $tce_data_cell_width_third, $this->tce_data_cell_height, $display_time, 1, 0, 'C', 0);
$this->Cell($tce_data_cell_width + $tce_data_cell_width_third, $this->tce_data_cell_height, $change_time, 1, 0, 'C', 0);
$this->Cell($tce_data_cell_width, $this->tce_data_cell_height, $diff_time, 1, 0, 'C', 0);
$this->Cell($tce_data_cell_width, $this->tce_data_cell_height, $reaction_time, 1, 1, 'C', 0);
$this->writeHTMLCell(0, $this->tce_data_cell_height, PDF_MARGIN_LEFT + $tce_data_cell_width_third, $this->GetY(), F_decode_tcecode($m['question_description']), 1, 1);
if (K_ENABLE_QUESTION_EXPLANATION and !empty($m['question_explanation'])) {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, '', 0, 0, 'C', 0);
$this->SetFont('', 'BIU');
$this->Cell(0, $this->tce_data_cell_height, $l['w_explanation'], 'LTR', 1, '', 0, '', 0);
$this->SetFont('', '');
$this->writeHTMLCell(0, $this->tce_data_cell_height, PDF_MARGIN_LEFT + $tce_data_cell_width_third, $this->GetY(), F_decode_tcecode($m['question_explanation']), 'LRB', 1, '', '');
}
if ($m['question_type'] == 3) {
// free-text question - print user text answer
$this->writeHTMLCell(0, $this->tce_data_cell_height, PDF_MARGIN_LEFT + 2 * $tce_data_cell_width_third, $this->GetY(), F_decode_tcecode($m['testlog_answer_text']), 1, 1);
} else {
// display each answer option
$sqla = 'SELECT * FROM ' . K_TABLE_LOG_ANSWER . ', ' . K_TABLE_ANSWERS . ' WHERE logansw_answer_id=answer_id AND logansw_testlog_id=' . $m['testlog_id'] . ' ORDER BY logansw_order';
if ($ra = F_db_query($sqla, $db)) {
$idx = 0;
// count items
while ($ma = F_db_fetch_array($ra)) {
$posfill = 0;
$idx++;
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, '', 0, 0, 'C', 0);
if ($m['question_type'] == 4) {
if ($ma['logansw_position'] > 0) {
if ($ma['logansw_position'] == $ma['answer_position']) {
$posfill = 1;
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, $ma['logansw_position'], 1, 0, 'C', 1);
} else {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, $ma['logansw_position'], 1, 0, 'C', 0);
}
} else {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, ' ', 1, 0, 'C', 0);
}
} elseif ($ma['logansw_selected'] > 0) {
// selected
if (F_getBoolean($ma['answer_isright'])) {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, '+', 1, 0, 'C', 1);
} else {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, '-', 1, 0, 'C', 1);
}
} elseif ($m['question_type'] == 1) {
// MCSA
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, ' ', 1, 0, 'C', 0);
} else {
if ($ma['logansw_selected'] == 0) {
// unselected
if (F_getBoolean($ma['answer_isright'])) {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, '-', 1, 0, 'C', 0);
} else {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, '+', 1, 0, 'C', 0);
}
} else {
// no answer
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, ' ', 1, 0, 'C', 0);
}
}
if ($m['question_type'] == 4) {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, $ma['answer_position'], 1, 0, 'C', $posfill);
} elseif (F_getBoolean($ma['answer_isright'])) {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, $idx, 1, 0, 'C', 1);
} else {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, $idx, 1, 0, 'C', 0);
}
$this->writeHTMLCell(0, $this->tce_data_cell_height, PDF_MARGIN_LEFT + $tce_data_cell_width, $this->GetY(), F_decode_tcecode($ma['answer_description']), 'LRTB', 1);
if (K_ENABLE_ANSWER_EXPLANATION and !empty($ma['answer_explanation'])) {
$this->Cell(3 * $tce_data_cell_width_third, $this->tce_data_cell_height, '', 0, 0, 'C', 0);
$this->SetFont('', 'BIU');
$this->Cell(0, $this->tce_data_cell_height, $l['w_explanation'], 'LTR', 1, '', 0, '', 0);
$this->SetFont('', '');
$this->writeHTMLCell(0, $this->tce_data_cell_height, PDF_MARGIN_LEFT + 3 * $tce_data_cell_width_third, $this->GetY(), F_decode_tcecode($ma['answer_explanation']), 'LRB', 1, '', '');
}
}
} else {
F_display_db_error();
}
}
// end multiple answers
if (strlen($m['testlog_comment']) > 0) {
// teacher / supervisor comment
$this->SetTextColor(255, 0, 0);
$this->writeHTMLCell(0, $this->tce_data_cell_height, PDF_MARGIN_LEFT + 2 * $tce_data_cell_width_third, $this->GetY(), F_decode_tcecode($m['testlog_comment']), 'LRTB', 1);
$this->SetTextColor(0, 0, 0);
}
$this->Ln($this->tce_data_cell_height);
$itemcount++;
}
} else {
F_display_db_error();
}
$stats = F_getTestStat($data['test']['test_id'], 0, $data['user_id'], 0, 0, $data['id']);
$this->printQuestionStats($stats['qstats'], 1);
}