require_once '../../shared/code/tce_functions_form.php';
require_once '../../shared/code/tce_functions_tcecode.php';
require_once '../../shared/code/tce_functions_auth_sql.php';
if (isset($selectcategory)) {
$changecategory = 1;
}
if (isset($testlog_id)) {
$testlog_id = intval($testlog_id);
}
if (!isset($testlog_comment)) {
$testlog_comment = '';
}
if (isset($_REQUEST['test_id']) and $_REQUEST['test_id'] > 0) {
$test_id = intval($_REQUEST['test_id']);
// check user's authorization
if (!F_isAuthorizedUser(K_TABLE_TESTS, 'test_id', $test_id, 'test_user_id')) {
F_print_error('ERROR', $l['m_authorization_denied']);
exit;
}
} else {
$test_id = 0;
}
switch ($menu_mode) {
case 'update':
// Update
if ($formstatus = F_check_form_fields()) {
if (isset($testlog_score) and isset($max_score)) {
// score cannot be greater than max_score
$testlog_score = floatval($testlog_score);
$max_score = floatval($max_score);
if ($testlog_score > $max_score) {
/**
* Add a new module if not exist.
* @private
*/
private function addModule()
{
global $l, $db;
require_once '../config/tce_config.php';
require_once '../code/tce_functions_auth_sql.php';
if (isset($this->level_data['module']['module_id']) and $this->level_data['module']['module_id'] > 0) {
return;
}
// check if this module already exist
$sql = 'SELECT module_id
FROM ' . K_TABLE_MODULES . '
WHERE module_name=\'' . $this->level_data['module']['module_name'] . '\'
LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
// get existing module ID
if (!F_isAuthorizedUser(K_TABLE_MODULES, 'module_id', $m['module_id'], 'module_user_id')) {
// unauthorized user
$this->level_data['module']['module_id'] = false;
} else {
$this->level_data['module']['module_id'] = $m['module_id'];
}
} else {
// insert new module
$sql = 'INSERT INTO ' . K_TABLE_MODULES . ' (
module_name,
module_enabled,
module_user_id
) VALUES (
\'' . $this->level_data['module']['module_name'] . '\',
\'' . $this->boolval[$this->level_data['module']['module_enabled']] . '\',
\'' . $_SESSION['session_user_id'] . '\'
)';
if (!($r = F_db_query($sql, $db))) {
F_display_db_error();
} else {
// get new module ID
$this->level_data['module']['module_id'] = F_db_insert_id($db, K_TABLE_MODULES, 'module_id');
}
}
} else {
F_display_db_error();
}
}
} else {
$subject_name = '';
}
if (isset($_REQUEST['subject_description'])) {
$subject_description = utrim($_REQUEST['subject_description']);
} else {
$subject_description = '';
}
if ($subject_id > 0) {
if ($changecategory == 0) {
$sql = 'SELECT subject_module_id FROM ' . K_TABLE_SUBJECTS . ' WHERE subject_id=' . $subject_id . ' LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
$subject_module_id = $m['subject_module_id'];
// check user's authorization for parent module
if (!F_isAuthorizedUser(K_TABLE_MODULES, 'module_id', $subject_module_id, 'module_user_id') and !F_isAuthorizedUser(K_TABLE_SUBJECTS, 'subject_id', $subject_id, 'subject_user_id')) {
F_print_error('ERROR', $l['m_authorization_denied']);
exit;
}
}
} else {
F_display_db_error();
}
}
} else {
$subject_id = 0;
}
switch ($menu_mode) {
case 'delete':
F_stripslashes_formfields();
// check if this record is used (test_log)
*/
require_once '../config/tce_config.php';
require_once '../../shared/code/tce_authorization.php';
require_once '../code/tce_functions_auth_sql.php';
require_once '../../shared/code/tce_functions_tcecode.php';
require_once '../../shared/config/tce_pdf.php';
require_once '../../shared/code/tcpdfex.php';
if (isset($_REQUEST['expmode']) and $_REQUEST['expmode'] > 0 and (isset($_REQUEST['module_id']) and $_REQUEST['module_id'] > 0) and (isset($_REQUEST['subject_id']) and $_REQUEST['subject_id'] > 0)) {
$expmode = intval($_REQUEST['expmode']);
$module_id = intval($_REQUEST['module_id']);
$subject_id = intval($_REQUEST['subject_id']);
} else {
exit;
}
// check user's authorization for module
if (!F_isAuthorizedUser(K_TABLE_MODULES, 'module_id', $module_id, 'module_user_id')) {
exit;
}
$show_answers = true;
if (isset($_REQUEST['hide_answers']) and $_REQUEST['hide_answers'] == 1) {
$show_answers = false;
}
$doc_title = unhtmlentities($l['t_questions_list']);
$doc_description = F_compact_string(unhtmlentities($l['hp_select_all_questions']));
$page_elements = 6;
$qtype = array('S', 'M', 'T', '0');
// question types
$qright = array(' ', '*');
// question types
// --- create pdf document
if ($l['a_meta_dir'] == 'rtl') {
/**
* Copy selected question to another topic
* @author Nicola Asuni
* @since 2008-11-26
* @param $question_id (int) question ID
* @param $new_subject_id (int) new subject ID
*/
function F_question_copy($question_id, $new_subject_id)
{
global $l, $db;
require_once '../config/tce_config.php';
$question_id = intval($question_id);
$new_subject_id = intval($new_subject_id);
// check authorization
$sql = 'SELECT subject_module_id FROM ' . K_TABLE_SUBJECTS . ' WHERE subject_id=' . $new_subject_id . ' LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
$subject_module_id = $m['subject_module_id'];
// check user's authorization for parent module
if (!F_isAuthorizedUser(K_TABLE_MODULES, 'module_id', $subject_module_id, 'module_user_id')) {
return;
}
}
} else {
F_display_db_error();
return;
}
$q = F_question_get_data($question_id);
if ($q !== false) {
if (K_DATABASE_TYPE == 'ORACLE') {
$chksql = 'dbms_lob.instr(question_description,\'' . F_escape_sql($db, $q['question_description']) . '\',1,1)>0';
} elseif (K_DATABASE_TYPE == 'MYSQL' and defined('K_MYSQL_QA_BIN_UNIQUITY') and K_MYSQL_QA_BIN_UNIQUITY) {
$chksql = 'question_description=\'' . F_escape_sql($db, $q['question_description']) . '\' COLLATE utf8_bin';
} else {
$chksql = 'question_description=\'' . F_escape_sql($db, $q['question_description']) . '\'';
}
if (F_check_unique(K_TABLE_QUESTIONS, $chksql . ' AND question_subject_id=' . $new_subject_id . '')) {
$sql = 'START TRANSACTION';
if (!($r = F_db_query($sql, $db))) {
F_display_db_error(false);
break;
}
// adjust questions ordering
if ($q['question_position'] > 0) {
$sql = 'UPDATE ' . K_TABLE_QUESTIONS . ' SET
question_position=question_position+1
WHERE question_subject_id=' . $new_subject_id . '
AND question_position>=' . $q['question_position'] . '';
if (!($r = F_db_query($sql, $db))) {
F_display_db_error(false);
F_db_query('ROLLBACK', $db);
// rollback transaction
}
}
$sql = 'INSERT INTO ' . K_TABLE_QUESTIONS . ' (
question_subject_id,
question_description,
question_explanation,
question_type,
question_difficulty,
question_enabled,
question_position,
question_timer,
question_fullscreen,
question_inline_answers,
question_auto_next
) VALUES (
' . $new_subject_id . ',
\'' . F_escape_sql($db, $q['question_description']) . '\',
\'' . F_escape_sql($db, $q['question_explanation']) . '\',
\'' . $q['question_type'] . '\',
\'' . $q['question_difficulty'] . '\',
\'' . $q['question_enabled'] . '\',
' . F_zero_to_null($q['question_position']) . ',
\'' . $q['question_timer'] . '\',
\'' . $q['question_fullscreen'] . '\',
\'' . $q['question_inline_answers'] . '\',
\'' . $q['question_auto_next'] . '\'
)';
if (!($r = F_db_query($sql, $db))) {
F_display_db_error(false);
} else {
$new_question_id = F_db_insert_id($db, K_TABLE_QUESTIONS, 'question_id');
}
// copy associated answers
$sql = 'SELECT *
FROM ' . K_TABLE_ANSWERS . '
WHERE answer_question_id=' . $question_id . '';
if ($r = F_db_query($sql, $db)) {
while ($m = F_db_fetch_array($r)) {
$sqli = 'INSERT INTO ' . K_TABLE_ANSWERS . ' (
answer_question_id,
answer_description,
answer_explanation,
answer_isright,
answer_enabled,
answer_position,
answer_keyboard_key
) VALUES (
' . $new_question_id . ',
\'' . F_escape_sql($db, $m['answer_description']) . '\',
\'' . F_escape_sql($db, $m['answer_explanation']) . '\',
\'' . $m['answer_isright'] . '\',
\'' . $m['answer_enabled'] . '\',
' . F_zero_to_null($m['answer_position']) . ',
' . F_empty_to_null($m['answer_keyboard_key']) . '
)';
if (!($ri = F_db_query($sqli, $db))) {
F_display_db_error(false);
F_db_query('ROLLBACK', $db);
// rollback transaction
}
}
} else {
F_display_db_error();
}
$sql = 'COMMIT';
if (!($r = F_db_query($sql, $db))) {
F_display_db_error(false);
break;
}
}
}
}
$question_explanation = '';
}
$qtype = array('S', 'M', 'T', 'O');
// question types
// check user's authorization
if (isset($_REQUEST['question_id']) and $_REQUEST['question_id'] > 0) {
$question_id = intval($_REQUEST['question_id']);
$sql = 'SELECT subject_module_id,question_subject_id
FROM ' . K_TABLE_SUBJECTS . ', ' . K_TABLE_QUESTIONS . '
WHERE subject_id=question_subject_id
AND question_id=' . $question_id . '
LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
// check user's authorization for parent module
if (!F_isAuthorizedUser(K_TABLE_MODULES, 'module_id', $m['subject_module_id'], 'module_user_id')) {
F_print_error('ERROR', $l['m_authorization_denied']);
exit;
}
}
} else {
F_display_db_error();
}
}
switch ($menu_mode) {
case 'delete':
F_stripslashes_formfields();
// check if this record is used (test_log)
if (!F_check_unique(K_TABLE_TESTS_LOGS, 'testlog_question_id=' . $question_id . '')) {
//this record will be only disabled and not deleted because it's used
$sql = 'UPDATE ' . K_TABLE_QUESTIONS . ' SET
$answer_explanation = '';
}
$qtype = array('S', 'M', 'T', 'O');
// question types
// check user's authorization
if ($answer_id > 0) {
$sql = 'SELECT subject_module_id,question_subject_id,answer_question_id
FROM ' . K_TABLE_SUBJECTS . ', ' . K_TABLE_QUESTIONS . ', ' . K_TABLE_ANSWERS . '
WHERE subject_id=question_subject_id
AND question_id=answer_question_id
AND answer_id=' . $answer_id . '
LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
// check user's authorization for parent module
if (!F_isAuthorizedUser(K_TABLE_MODULES, 'module_id', $m['subject_module_id'], 'module_user_id') and !F_isAuthorizedUser(K_TABLE_SUBJECTS, 'subject_id', $m['question_subject_id'], 'subject_user_id')) {
F_print_error('ERROR', $l['m_authorization_denied']);
exit;
}
}
} else {
F_display_db_error();
}
}
switch ($menu_mode) {
case 'delete':
F_stripslashes_formfields();
// check if this record is used (test_log)
if (!F_check_unique(K_TABLE_LOG_ANSWER, 'logansw_answer_id=' . $answer_id . '')) {
//this record will be only disabled and not deleted because it's used
$sql = 'UPDATE ' . K_TABLE_ANSWERS . ' SET
$ssl_enabled = F_getBoolean($ssl_enabled);
}
if (isset($ssl_name)) {
$ssl_name = utrim($ssl_name);
} else {
$ssl_name = '';
}
if (isset($ssl_user_id)) {
$ssl_user_id = intval($ssl_user_id);
} else {
$ssl_user_id = intval($_SESSION['session_user_id']);
}
if (isset($_REQUEST['ssl_id']) and $_REQUEST['ssl_id'] > 0) {
$ssl_id = intval($_REQUEST['ssl_id']);
// check user's authorization for this certificate
if (!F_isAuthorizedUser(K_TABLE_SSLCERTS, 'ssl_id', $ssl_id, 'ssl_user_id')) {
F_print_error('ERROR', $l['m_authorization_denied']);
exit;
}
} else {
$ssl_id = 0;
}
// extact hash and end date from uploaded file
$ssl_hash = '';
$ssl_end_date = '';
if (isset($_FILES['userfile']['name']) and !empty($_FILES['userfile']['name'])) {
require_once '../code/tce_functions_upload.php';
// upload file
$uploadedfile = F_upload_file('userfile', K_PATH_CACHE);
if ($uploadedfile !== false) {
$cert = file_get_contents(K_PATH_CACHE . $uploadedfile);
/**
* Returns raw 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 (int) start date ID - if greater than zero, filter stats for the specified starting date
* @param $enddate (int) end date ID - if greater than zero, filter stats for the specified ending date
* @param $testuser_id (int) test-user ID - if greater than zero, filter stats for the specified test-user.
* @param $data (array) Array of existing data to be merged with the current one.
* @param $pubmode (boolean) If true filter the results for the public interface.
* return $data array containing test statistics.
*/
function F_getRawTestStat($test_id, $group_id = 0, $user_id = 0, $startdate = 0, $enddate = 0, $testuser_id = 0, $data = array(), $pubmode = false)
{
require_once '../config/tce_config.php';
require_once '../../shared/code/tce_functions_authorization.php';
require_once '../../shared/code/tce_functions_test.php';
global $db, $l;
$test_id = intval($test_id);
$group_id = intval($group_id);
$user_id = intval($user_id);
$testuser_id = intval($testuser_id);
// query to calculate total number of questions
$sqltot = K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS_LOGS;
$sqltb = K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_ANSWERS . ', ' . K_TABLE_LOG_ANSWER;
$sqlm = K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_QUESTIONS . ', ' . K_TABLE_SUBJECTS . ', ' . K_TABLE_MODULES . '';
// apply filters
$sqlw = 'WHERE testlog_testuser_id=testuser_id';
$sqlansw = 'WHERE logansw_answer_id=answer_id AND logansw_testlog_id=testlog_id AND testlog_testuser_id=testuser_id';
if ($test_id > 0) {
$sqlw .= ' AND testuser_test_id=' . $test_id . '';
$sqlansw .= ' AND testuser_test_id=' . $test_id . '';
} elseif ($pubmode) {
$test_ids_results = F_getTestIDResults($test_id, $user_id);
$sqlw .= ' AND testuser_test_id IN (' . $test_ids_results . ')';
$sqlansw .= ' AND testuser_test_id IN (' . $test_ids_results . ')';
}
if ($user_id > 0) {
$sqltot .= ', ' . K_TABLE_USERS;
$sqltb .= ', ' . K_TABLE_USERS;
$sqlm .= ', ' . K_TABLE_USERS;
$sqlw .= ' AND testuser_user_id=user_id AND user_id=' . $user_id . '';
$sqlansw .= ' AND testuser_user_id=user_id AND user_id=' . $user_id . '';
if ($testuser_id > 0) {
$sqlw .= ' AND testuser_id=' . $testuser_id . '';
$sqlansw .= ' AND testuser_id=' . $testuser_id . '';
}
} elseif ($group_id > 0) {
$sqltot .= ', ' . K_TABLE_USERS . ', ' . K_TABLE_USERGROUP;
$sqltb .= ', ' . K_TABLE_USERS . ', ' . K_TABLE_USERGROUP;
$sqlm .= ', ' . K_TABLE_USERS . ', ' . K_TABLE_USERGROUP;
$sqlw .= ' AND testuser_user_id=user_id AND usrgrp_user_id=user_id AND usrgrp_group_id=' . $group_id . '';
$sqlansw .= ' AND testuser_user_id=user_id AND usrgrp_user_id=user_id AND usrgrp_group_id=' . $group_id . '';
}
if (!empty($startdate)) {
$startdate_time = strtotime($startdate);
$startdate = date(K_TIMESTAMP_FORMAT, $startdate_time);
$sqlw .= ' AND testuser_creation_time>=\'' . $startdate . '\'';
$sqlansw .= ' AND testuser_creation_time>=\'' . $startdate . '\'';
}
if (!empty($enddate)) {
$enddate_time = strtotime($enddate);
$enddate = date(K_TIMESTAMP_FORMAT, $enddate_time);
$sqlw .= ' AND testuser_creation_time<=\'' . $enddate . '\'';
$sqlansw .= ' AND testuser_creation_time<=\'' . $enddate . '\'';
}
// check if a specific test is selected or not
if ($test_id == 0) {
$test_ids = array();
$sqlt = 'SELECT testuser_test_id FROM ' . $sqltot . ' ' . $sqlw . ' GROUP BY testuser_test_id ORDER BY testuser_test_id';
if ($rt = F_db_query($sqlt, $db)) {
while ($mt = F_db_fetch_assoc($rt)) {
// check user's authorization
if (F_isAuthorizedUser(K_TABLE_TESTS, 'test_id', $mt['testuser_test_id'], 'test_user_id')) {
$test_ids[] = $mt['testuser_test_id'];
}
}
} else {
F_display_db_error();
}
foreach ($test_ids as $tid) {
// select test IDs
$data = F_getRawTestStat($tid, $group_id, $user_id, $startdate, $enddate, $testuser_id, $data);
}
return $data;
}
$testdata = F_getTestData($test_id);
// array to be returned
if (!isset($data['qstats'])) {
// total number of questions
$data['qstats'] = array('recurrence' => 0, 'recurrence_perc' => 0, 'average_score' => 0, 'average_score_perc' => 0, 'average_time' => 0, 'right' => 0, 'right_perc' => 0, 'wrong' => 0, 'wrong_perc' => 0, 'unanswered' => 0, 'unanswered_perc' => 0, 'undisplayed' => 0, 'undisplayed_perc' => 0, 'unrated' => 0, 'unrated_perc' => 0, 'qnum' => 0, 'module' => array());
}
$sql = 'SELECT
module_id,
subject_id,
question_id,
module_name,
subject_name,
subject_description,
question_description,';
if ($user_id > 0 and $testuser_id > 0) {
$sql .= ' testlog_score,
testlog_user_ip,
testlog_display_time,
testlog_change_time,
testlog_reaction_time,
testlog_answer_text,
question_type,
question_explanation,';
}
$sql .= ' COUNT(question_id) AS recurrence,
AVG(testlog_score) AS average_score,
AVG(testlog_change_time - testlog_display_time) AS average_time,
MIN(question_type) AS question_type,
MIN(question_difficulty) AS question_difficulty';
$sql .= ' FROM ' . $sqlm;
$sql .= ' WHERE testlog_testuser_id=testuser_id AND question_id=testlog_question_id AND subject_id=question_subject_id AND module_id=subject_module_id';
if ($test_id > 0) {
$sql .= ' AND testuser_test_id=' . $test_id . '';
}
if ($testuser_id > 0) {
$sql .= ' AND testuser_id=' . $testuser_id . '';
}
if ($user_id > 0) {
$sql .= ' AND testuser_user_id=user_id AND user_id=' . $user_id . '';
} elseif ($group_id > 0) {
$sql .= ' AND testuser_user_id=user_id AND usrgrp_user_id=user_id AND usrgrp_group_id=' . $group_id . '';
}
if (!empty($startdate)) {
$sql .= ' AND testuser_creation_time>=\'' . $startdate . '\'';
}
if (!empty($enddate)) {
$sql .= ' AND testuser_creation_time<=\'' . $enddate . '\'';
}
$sql .= ' GROUP BY module_id, subject_id, question_id, module_name, subject_name, subject_description, question_description';
if ($user_id > 0 and $testuser_id > 0) {
$sql .= ', testlog_score, testlog_user_ip, testlog_display_time, testlog_change_time, testlog_reaction_time, testlog_answer_text, question_type, question_explanation';
} else {
$sql .= ' ORDER BY module_name, subject_name, question_description';
}
if ($r = F_db_query($sql, $db)) {
while ($m = F_db_fetch_array($r)) {
if (!isset($data['qstats']['module']['\'' . $m['module_id'] . '\''])) {
$data['qstats']['module']['\'' . $m['module_id'] . '\''] = array('id' => $m['module_id'], 'name' => $m['module_name'], 'recurrence' => 0, 'recurrence_perc' => 0, 'average_score' => 0, 'average_score_perc' => 0, 'average_time' => 0, 'right' => 0, 'right_perc' => 0, 'wrong' => 0, 'wrong_perc' => 0, 'unanswered' => 0, 'unanswered_perc' => 0, 'undisplayed' => 0, 'undisplayed_perc' => 0, 'unrated' => 0, 'unrated_perc' => 0, 'qnum' => 0, 'subject' => array());
}
if (!isset($data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\''])) {
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\''] = array('id' => $m['subject_id'], 'name' => $m['subject_name'], 'description' => $m['subject_description'], 'recurrence' => 0, 'recurrence_perc' => 0, 'average_score' => 0, 'average_score_perc' => 0, 'average_time' => 0, 'right' => 0, 'right_perc' => 0, 'wrong' => 0, 'wrong_perc' => 0, 'unanswered' => 0, 'unanswered_perc' => 0, 'undisplayed' => 0, 'undisplayed_perc' => 0, 'unrated' => 0, 'unrated_perc' => 0, 'qnum' => 0, 'question' => array());
}
$question_max_score = $testdata['test_score_right'] * $m['question_difficulty'];
$question_half_score = $question_max_score / 2;
$qright = F_count_rows($sqltot, $sqlw . ' AND testlog_question_id=' . $m['question_id'] . ' AND testlog_score>' . $question_half_score . '');
$qwrong = F_count_rows($sqltot, $sqlw . ' AND testlog_question_id=' . $m['question_id'] . ' AND testlog_score<=' . $question_half_score . '');
$qunanswered = F_count_rows($sqltot, $sqlw . ' AND testlog_question_id=' . $m['question_id'] . ' AND testlog_change_time IS NULL');
$qundisplayed = F_count_rows($sqltot, $sqlw . ' AND testlog_question_id=' . $m['question_id'] . ' AND testlog_display_time IS NULL');
$qunrated = F_count_rows($sqltot, $sqlw . ' AND testlog_question_id=' . $m['question_id'] . ' AND testlog_score IS NULL');
if (stripos($m['average_time'], ':') !== FALSE) {
// PostgreSQL returns formatted time, while MySQL returns the number of seconds
$m['average_time'] = strtotime($m['average_time']);
}
$num_all_answers = F_count_rows($sqltb, $sqlansw . ' AND testlog_question_id=' . $m['question_id']);
if (!isset($data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\''])) {
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\''] = array('id' => $m['question_id'], 'description' => $m['question_description'], 'type' => $m['question_type'], 'difficulty' => $m['question_difficulty'], 'recurrence' => 0, 'recurrence_perc' => 0, 'average_score' => 0, 'average_score_perc' => 0, 'average_time' => 0, 'right' => 0, 'right_perc' => 0, 'wrong' => 0, 'wrong_perc' => 0, 'unanswered' => 0, 'unanswered_perc' => 0, 'undisplayed' => 0, 'undisplayed_perc' => 0, 'unrated' => 0, 'unrated_perc' => 0, 'qnum' => 0, 'anum' => 0, 'answer' => array());
}
// average score ratio
if ($question_max_score > 0) {
$average_score_perc = $m['average_score'] / $question_max_score;
} else {
$average_score_perc = 0;
}
// sum values for questions
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['qnum'] += 1;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['recurrence'] += $m['recurrence'];
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['average_score'] += $m['average_score'];
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['average_score_perc'] += $average_score_perc;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['average_time'] += $m['average_time'];
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['right'] += $qright;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['wrong'] += $qwrong;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['unanswered'] += $qunanswered;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['undisplayed'] += $qundisplayed;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['unrated'] += $qunrated;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['anum'] += $num_all_answers;
// sum values for subject
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['qnum'] += 1;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['recurrence'] += $m['recurrence'];
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['average_score'] += $m['average_score'];
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['average_score_perc'] += $average_score_perc;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['average_time'] += $m['average_time'];
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['right'] += $qright;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['wrong'] += $qwrong;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['unanswered'] += $qunanswered;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['undisplayed'] += $qundisplayed;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['unrated'] += $qunrated;
// sum values for module
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['qnum'] += 1;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['recurrence'] += $m['recurrence'];
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['average_score'] += $m['average_score'];
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['average_score_perc'] += $average_score_perc;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['average_time'] += $m['average_time'];
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['right'] += $qright;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['wrong'] += $qwrong;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['unanswered'] += $qunanswered;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['undisplayed'] += $qundisplayed;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['unrated'] += $qunrated;
// sum totals
$data['qstats']['qnum'] += 1;
$data['qstats']['recurrence'] += $m['recurrence'];
$data['qstats']['average_score'] += $m['average_score'];
$data['qstats']['average_score_perc'] += $average_score_perc;
$data['qstats']['average_time'] += $m['average_time'];
$data['qstats']['right'] += $qright;
$data['qstats']['wrong'] += $qwrong;
$data['qstats']['unanswered'] += $qunanswered;
$data['qstats']['undisplayed'] += $qundisplayed;
$data['qstats']['unrated'] += $qunrated;
// get answer statistics
$sqlaa = 'SELECT answer_id, answer_description, COUNT(answer_id) AS recurrence';
if ($user_id > 0 and $testuser_id > 0) {
$sqlaa .= ', logansw_position, logansw_selected, answer_isright, answer_position, answer_explanation';
}
$sqlaa .= ' FROM ' . $sqltb . '';
$sqlaw = ' WHERE testlog_testuser_id=testuser_id
AND logansw_testlog_id=testlog_id
AND answer_id=logansw_answer_id
AND answer_question_id=' . $m['question_id'] . '';
if ($test_id > 0) {
$sqlaw .= ' AND testuser_test_id=' . $test_id . '';
}
if ($user_id > 0) {
$sqlaw .= ' AND testuser_user_id=' . $user_id . '';
}
if ($testuser_id > 0) {
$sqlaw .= ' AND testuser_id=' . $testuser_id . '';
}
if ($user_id > 0) {
$sqlaw .= ' AND testuser_user_id=user_id AND user_id=' . $user_id . '';
} elseif ($group_id > 0) {
$sqlaw .= ' AND testuser_user_id=user_id AND usrgrp_user_id=user_id AND usrgrp_group_id=' . $group_id . '';
}
if (!empty($startdate)) {
$sql .= ' AND testuser_creation_time>=\'' . $startdate . '\'';
}
if (!empty($enddate)) {
$sql .= ' AND testuser_creation_time<=\'' . $enddate . '\'';
}
$sqlab = ' GROUP BY answer_id, answer_description';
if ($user_id > 0 and $testuser_id > 0) {
$sqlab .= ', logansw_position, logansw_selected, answer_isright, answer_position, answer_explanation';
}
$sqlab .= ' ORDER BY answer_description';
$sqla = $sqlaa . $sqlaw . $sqlab;
if ($ra = F_db_query($sqla, $db)) {
while ($ma = F_db_fetch_array($ra)) {
$aright = F_count_rows($sqltb, $sqlaw . ' AND answer_id=' . $ma['answer_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))');
$awrong = F_count_rows($sqltb, $sqlaw . ' AND answer_id=' . $ma['answer_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))');
$aunanswered = F_count_rows($sqltb, $sqlaw . ' AND answer_id=' . $ma['answer_id'] . ' AND logansw_selected=-1');
if (!isset($data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['answer']['\'' . $ma['answer_id'] . '\''])) {
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['answer']['\'' . $ma['answer_id'] . '\''] = array('id' => $ma['answer_id'], 'description' => $ma['answer_description'], 'recurrence' => 0, 'recurrence_perc' => 0, 'right' => 0, 'right_perc' => 0, 'wrong' => 0, 'wrong_perc' => 0, 'unanswered' => 0, 'unanswered_perc' => 0);
}
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['answer']['\'' . $ma['answer_id'] . '\'']['recurrence'] += $ma['recurrence'];
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['answer']['\'' . $ma['answer_id'] . '\'']['right'] += $aright;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['answer']['\'' . $ma['answer_id'] . '\'']['wrong'] += $awrong;
$data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['answer']['\'' . $ma['answer_id'] . '\'']['unanswered'] += $aunanswered;
}
} else {
F_display_db_error();
}
}
} else {
F_display_db_error();
}
return $data;
}
/**
* 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;
}
/**
* Sends email test reports to users.
* @author Nicola Asuni
* @since 2005-02-24
* @param $test_id (int) TEST ID
* @param $user_id (int) USER ID (0 means all users)
* @param $group_id (int) GROUP ID (0 means all groups)
* @param $mode (int) type of report to send: 0=detailed report; 1=summary report (without questions)
*/
function F_send_report_emails($test_id, $user_id = 0, $group_id = 0, $mode = 0)
{
global $l, $db;
require_once '../config/tce_config.php';
require_once '../../shared/code/tce_functions_test.php';
require_once '../../shared/code/tce_functions_test_stats.php';
require_once '../../shared/code/tce_class_mailer.php';
require_once 'tce_functions_user_select.php';
$test_id = intval($test_id);
$user_id = intval($user_id);
$group_id = intval($group_id);
$mode = intval($mode);
if (!F_isAuthorizedUser(K_TABLE_TESTS, 'test_id', $test_id, 'test_user_id')) {
return;
}
if (!F_isAuthorizedEditorForUser($user_id)) {
return;
}
if (!F_isAuthorizedEditorForGroup($group_id)) {
return;
}
// Instantiate C_mailer class
$mail = new C_mailer();
//Load default values
$mail->language = $l;
$mail->Priority = $emailcfg['Priority'];
$mail->ContentType = $emailcfg['ContentType'];
$mail->Encoding = $emailcfg['Encoding'];
$mail->WordWrap = $emailcfg['WordWrap'];
$mail->Mailer = $emailcfg['Mailer'];
$mail->Sendmail = $emailcfg['Sendmail'];
$mail->UseMSMailHeaders = $emailcfg['UseMSMailHeaders'];
$mail->Host = $emailcfg['Host'];
$mail->Port = $emailcfg['Port'];
$mail->Helo = $emailcfg['Helo'];
$mail->SMTPAuth = $emailcfg['SMTPAuth'];
$mail->SMTPSecure = $emailcfg['SMTPSecure'];
$mail->Username = $emailcfg['Username'];
$mail->Password = $emailcfg['Password'];
$mail->Timeout = $emailcfg['Timeout'];
$mail->SMTPDebug = $emailcfg['SMTPDebug'];
$mail->PluginDir = $emailcfg['PluginDir'];
$mail->Sender = $emailcfg['Sender'];
$mail->From = $emailcfg['From'];
$mail->FromName = $emailcfg['FromName'];
if ($emailcfg['Reply']) {
$mail->AddReplyTo($emailcfg['Reply'], $emailcfg['ReplyName']);
}
$mail->CharSet = $l['a_meta_charset'];
if (!$mail->CharSet) {
$mail->CharSet = $emailcfg['CharSet'];
}
$mail->Subject = $l['t_result_user'];
$mail->IsHTML(TRUE);
// Set message type to HTML.
$email_num = 0;
// count emails;
if ($user_id == 0) {
// for each user on selected test
$sql = 'SELECT user_id, user_name, user_email, user_firstname, user_lastname, testuser_creation_time
FROM ' . K_TABLE_TEST_USER . ', ' . K_TABLE_USERS . '
WHERE testuser_user_id=user_id
AND testuser_test_id=' . $test_id . '
AND testuser_status>0';
if ($group_id > 0) {
$sql .= ' AND testuser_user_id IN (SELECT usrgrp_user_id FROM ' . K_TABLE_USERGROUP . ' WHERE usrgrp_group_id=' . $group_id . ')';
}
} else {
// select only one test of one user
$sql = 'SELECT user_id, user_name, user_email, user_firstname, user_lastname, testuser_creation_time
FROM ' . K_TABLE_TEST_USER . ', ' . K_TABLE_USERS . '
WHERE testuser_user_id=user_id
AND testuser_user_id=' . $user_id . '
AND testuser_test_id=' . $test_id . '
AND testuser_status>0
LIMIT 1';
}
// get test data
$testdata = F_getTestData($test_id);
if ($r = F_db_query($sql, $db)) {
while ($m = F_db_fetch_array($r)) {
if (strlen($m['user_email']) > 3) {
// get user's test stats
$usrtestdata = F_getUserTestStat($test_id, $m['user_id']);
// set HTML header
$mail->Body = $emailcfg['MsgHeader'];
// compose alternate TEXT message
$mail->AltBody = '' . $l['t_result_user'] . ' [' . $m['testuser_creation_time'] . ']' . K_NEWLINE;
$mail->AltBody .= $l['w_test'] . ': ' . $testdata['test_name'] . K_NEWLINE;
$passmsg = '';
if ($testdata['test_score_threshold'] > 0) {
$mail->AltBody .= $l['w_test_score_threshold'] . ': ' . $testdata['test_score_threshold'];
if ($usrtestdata['score'] >= $testdata['test_score_threshold']) {
$passmsg = ' - ' . $l['w_passed'];
} else {
$passmsg = ' - ' . $l['w_not_passed'];
}
$mail->AltBody .= K_NEWLINE;
}
$mail->AltBody .= $l['w_score'] . ': ' . $usrtestdata['score'] . ' (' . round(100 * $usrtestdata['score'] / $usrtestdata['max_score']) . '%)' . $passmsg . K_NEWLINE;
$mail->AltBody .= $l['w_answers_right'] . ': ' . $usrtestdata['right'] . ' (' . round(100 * $usrtestdata['right'] / $usrtestdata['all']) . '%)' . K_NEWLINE;
$mail->AltBody .= $l['w_answers_wrong'] . ': ' . $usrtestdata['wrong'] . ' (' . round(100 * $usrtestdata['wrong'] / $usrtestdata['all']) . '%)' . K_NEWLINE;
$mail->AltBody .= $l['w_questions_unanswered'] . ': ' . $usrtestdata['unanswered'] . ' (' . round(100 * $usrtestdata['unanswered'] / $usrtestdata['all']) . '%)' . K_NEWLINE;
$mail->AltBody .= $l['w_questions_undisplayed'] . ': ' . $usrtestdata['undisplayed'] . ' (' . round(100 * $usrtestdata['undisplayed'] / $usrtestdata['all']) . '%)' . K_NEWLINE;
if ($mode == 0) {
// create PDF doc
$pdf_content = file_get_contents(K_PATH_HOST . K_PATH_TCEXAM . 'admin/code/tce_pdf_results.php?mode=3&testid=' . $test_id . '&groupid=0&userid=' . $m['user_id'] . '&email=' . md5(date('Y') . K_RANDOM_SECURITY . $test_id . $m['user_id']));
// attach doc
$doc_name = 'test_' . date('Ymd', strtotime($m['testuser_creation_time'])) . '_' . $test_id . '_' . $m['user_id'] . '.pdf';
$mail->AddStringAttachment($pdf_content, $doc_name, $emailcfg['AttachmentsEncoding'], 'application/octet-stream');
$mail->AltBody .= K_NEWLINE . $l['w_attachment'] . ': ' . $doc_name . K_NEWLINE;
}
// convert alternate text to HTML
$mail->Body .= str_replace(K_NEWLINE, '<br />' . K_NEWLINE, $mail->AltBody);
// add HTML footer
$mail->Body .= $emailcfg['MsgFooter'];
//--- Elaborate user Templates ---
$mail->Body = str_replace('#CHARSET#', $l['a_meta_charset'], $mail->Body);
$mail->Body = str_replace('#LANG#', $l['a_meta_language'], $mail->Body);
$mail->Body = str_replace('#LANGDIR#', $l['a_meta_dir'], $mail->Body);
$mail->Body = str_replace('#EMAIL#', $m['user_email'], $mail->Body);
$mail->Body = str_replace('#USERNAME#', htmlspecialchars($m['user_name'], ENT_NOQUOTES, $l['a_meta_charset']), $mail->Body);
$mail->Body = str_replace('#USERFIRSTNAME#', htmlspecialchars($m['user_firstname'], ENT_NOQUOTES, $l['a_meta_charset']), $mail->Body);
$mail->Body = str_replace('#USERLASTNAME#', htmlspecialchars($m['user_lastname'], ENT_NOQUOTES, $l['a_meta_charset']), $mail->Body);
// add a "To" address
$mail->AddAddress($m['user_email'], $m['user_name']);
$email_num++;
$progresslog = '' . $email_num . '. ' . $m['user_email'] . ' [' . $m['user_name'] . ']';
//output user data
if (!$mail->Send()) {
//send email to user
$progresslog .= ' [' . $l['t_error'] . ']';
//display error message
}
$mail->ClearAddresses();
// Clear all addresses for next loop
$mail->ClearAttachments();
// Clears all previously set filesystem, string, and binary attachments
} else {
$progresslog = '[' . $l['t_error'] . '] ' . $m['user_name'] . ': ' . $l['m_unknown_email'] . '';
//output user data
}
echo '' . $progresslog . '<br />' . K_NEWLINE;
//output processed emails
flush();
// force browser output
}
} else {
F_display_db_error(false);
}
$mail->ClearAddresses();
// Clear all addresses for next loop
$mail->ClearCustomHeaders();
// Clears all custom headers
$mail->ClearAllRecipients();
// Clears all recipients assigned in the TO, CC and BCC
$mail->ClearAttachments();
// Clears all previously set filesystem, string, and binary attachments
$mail->ClearReplyTos();
// Clears all recipients assigned in the ReplyTo array
return;
}
/**
* Import questions from TSV file (tab delimited text).
* The format of TSV is the same obtained by exporting data from TCExam interface.
* @param $tsvfile (string) TSV (tab delimited text) file name
* @return boolean TRUE in case of success, FALSE otherwise
*/
function F_TSVQuestionImporter($tsvfile)
{
global $l, $db;
require_once '../config/tce_config.php';
require_once '../../shared/code/tce_functions_auth_sql.php';
$qtype = array('S' => 1, 'M' => 2, 'T' => 3, 'O' => 4);
// get file content as array
$tsvrows = file($tsvfile, FILE_IGNORE_NEW_LINES);
// array of TSV lines
if ($tsvrows === FALSE) {
return FALSE;
}
$current_module_id = 0;
$current_subject_id = 0;
$current_question_id = 0;
$current_answer_id = 0;
$questionhash = array();
// for each row
while (list($item, $rowdata) = each($tsvrows)) {
// get user data into array
$qdata = explode("\t", $rowdata);
switch ($qdata[0]) {
case 'M':
// MODULE
$current_module_id = 0;
if (!isset($qdata[2]) or empty($qdata[2])) {
break;
}
$module_enabled = intval($qdata[1]);
$module_name = F_escape_sql($db, F_tsv_to_text($qdata[2]), false);
// check if this module already exist
$sql = 'SELECT module_id
FROM ' . K_TABLE_MODULES . '
WHERE module_name=\'' . $module_name . '\'
LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
// get existing module ID
if (!F_isAuthorizedUser(K_TABLE_MODULES, 'module_id', $m['module_id'], 'module_user_id')) {
// unauthorized user
$current_module_id = 0;
} else {
$current_module_id = $m['module_id'];
}
} else {
// insert new module
$sql = 'INSERT INTO ' . K_TABLE_MODULES . ' (
module_name,
module_enabled,
module_user_id
) VALUES (
\'' . $module_name . '\',
\'' . $module_enabled . '\',
\'' . $_SESSION['session_user_id'] . '\'
)';
if (!($r = F_db_query($sql, $db))) {
F_display_db_error();
} else {
// get new module ID
$current_module_id = F_db_insert_id($db, K_TABLE_MODULES, 'module_id');
}
}
} else {
F_display_db_error();
}
break;
case 'S':
// SUBJECT
$current_subject_id = 0;
if ($current_module_id == 0) {
return;
}
if (!isset($qdata[2]) or empty($qdata[2])) {
break;
}
$subject_enabled = intval($qdata[1]);
$subject_name = F_escape_sql($db, F_tsv_to_text($qdata[2]), false);
$subject_description = '';
if (isset($qdata[3])) {
$subject_description = F_empty_to_null(F_tsv_to_text($qdata[3]));
}
// check if this subject already exist
$sql = 'SELECT subject_id
FROM ' . K_TABLE_SUBJECTS . '
WHERE subject_name=\'' . $subject_name . '\'
AND subject_module_id=' . $current_module_id . '
LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
// get existing subject ID
$current_subject_id = $m['subject_id'];
} else {
// insert new subject
$sql = 'INSERT INTO ' . K_TABLE_SUBJECTS . ' (
subject_name,
subject_description,
subject_enabled,
subject_user_id,
subject_module_id
) VALUES (
\'' . $subject_name . '\',
' . $subject_description . ',
\'' . $subject_enabled . '\',
\'' . $_SESSION['session_user_id'] . '\',
' . $current_module_id . '
)';
if (!($r = F_db_query($sql, $db))) {
F_display_db_error();
} else {
// get new subject ID
$current_subject_id = F_db_insert_id($db, K_TABLE_SUBJECTS, 'subject_id');
}
}
} else {
F_display_db_error();
}
break;
case 'Q':
// QUESTION
$current_question_id = 0;
if ($current_module_id == 0 or $current_subject_id == 0) {
return;
}
if (!isset($qdata[5])) {
break;
}
$question_enabled = intval($qdata[1]);
$question_description = F_escape_sql($db, F_tsv_to_text($qdata[2]), false);
$question_explanation = F_empty_to_null(F_tsv_to_text($qdata[3]));
$question_type = $qtype[$qdata[4]];
$question_difficulty = intval($qdata[5]);
if (isset($qdata[6])) {
$question_position = F_zero_to_null($qdata[6]);
} else {
$question_position = F_zero_to_null(0);
}
if (isset($qdata[7])) {
$question_timer = intval($qdata[7]);
} else {
$question_timer = 0;
}
if (isset($qdata[8])) {
$question_fullscreen = intval($qdata[8]);
} else {
$question_fullscreen = 0;
}
if (isset($qdata[9])) {
$question_inline_answers = intval($qdata[9]);
} else {
$question_inline_answers = 0;
}
if (isset($qdata[10])) {
$question_auto_next = intval($qdata[10]);
} else {
$question_auto_next = 0;
}
// check if this question already exist
$sql = 'SELECT question_id
FROM ' . K_TABLE_QUESTIONS . '
WHERE ';
if (K_DATABASE_TYPE == 'ORACLE') {
$sql .= 'dbms_lob.instr(question_description,\'' . $question_description . '\',1,1)>0';
} elseif (K_DATABASE_TYPE == 'MYSQL' and K_MYSQL_QA_BIN_UNIQUITY) {
$sql .= 'question_description=\'' . $question_description . '\' COLLATE utf8_bin';
} else {
$sql .= 'question_description=\'' . $question_description . '\'';
}
$sql .= ' AND question_subject_id=' . $current_subject_id . ' LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
// get existing question ID
$current_question_id = $m['question_id'];
return;
}
} else {
F_display_db_error();
}
if (K_DATABASE_TYPE == 'MYSQL') {
// this section is to avoid the problems on MySQL string comparison
$maxkey = 240;
$strkeylimit = min($maxkey, strlen($question_description));
$stop = $maxkey / 3;
while (in_array(md5(strtolower(substr($current_subject_id . $question_description, 0, $strkeylimit))), $questionhash) and $stop > 0) {
// a similar question was already imported, so we change it a little bit to avoid duplicate keys
$question_description = '_' . $question_description;
$strkeylimit = min($maxkey, $strkeylimit + 1);
$stop--;
// variable used to avoid infinite loop
}
if ($stop == 0) {
F_print_error('ERROR', 'Unable to get unique question ID');
return;
}
}
$sql = 'START TRANSACTION';
if (!($r = F_db_query($sql, $db))) {
F_display_db_error();
}
// insert question
$sql = 'INSERT INTO ' . K_TABLE_QUESTIONS . ' (
question_subject_id,
question_description,
question_explanation,
question_type,
question_difficulty,
question_enabled,
question_position,
question_timer,
question_fullscreen,
question_inline_answers,
question_auto_next
) VALUES (
' . $current_subject_id . ',
\'' . $question_description . '\',
' . $question_explanation . ',
\'' . $question_type . '\',
\'' . $question_difficulty . '\',
\'' . $question_enabled . '\',
' . $question_position . ',
\'' . $question_timer . '\',
\'' . $question_fullscreen . '\',
\'' . $question_inline_answers . '\',
\'' . $question_auto_next . '\'
)';
if (!($r = F_db_query($sql, $db))) {
F_display_db_error(false);
} else {
// get new question ID
$current_question_id = F_db_insert_id($db, K_TABLE_QUESTIONS, 'question_id');
if (K_DATABASE_TYPE == 'MYSQL') {
$questionhash[] = md5(strtolower(substr($current_subject_id . $question_description, 0, $strkeylimit)));
}
}
$sql = 'COMMIT';
if (!($r = F_db_query($sql, $db))) {
F_display_db_error();
}
break;
case 'A':
// ANSWER
$current_answer_id = 0;
if ($current_module_id == 0 or $current_subject_id == 0 or $current_question_id == 0) {
return;
}
if (!isset($qdata[4])) {
break;
}
$answer_enabled = intval($qdata[1]);
$answer_description = F_escape_sql($db, F_tsv_to_text($qdata[2]), false);
$answer_explanation = F_empty_to_null(F_tsv_to_text($qdata[3]));
$answer_isright = intval($qdata[4]);
if (isset($qdata[5])) {
$answer_position = F_zero_to_null($qdata[5]);
} else {
$answer_position = F_zero_to_null(0);
}
if (isset($qdata[6])) {
$answer_keyboard_key = F_empty_to_null(F_tsv_to_text($qdata[6]));
} else {
$answer_keyboard_key = F_empty_to_null('');
}
// check if this answer already exist
$sql = 'SELECT answer_id
FROM ' . K_TABLE_ANSWERS . '
WHERE ';
if (K_DATABASE_TYPE == 'ORACLE') {
$sql .= 'dbms_lob.instr(answer_description, \'' . $answer_description . '\',1,1)>0';
} elseif (K_DATABASE_TYPE == 'MYSQL' and K_MYSQL_QA_BIN_UNIQUITY) {
$sql .= 'answer_description=\'' . $answer_description . '\' COLLATE utf8_bin';
} else {
$sql .= 'answer_description=\'' . $answer_description . '\'';
}
$sql .= ' AND answer_question_id=' . $current_question_id . ' LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
// get existing subject ID
$current_answer_id = $m['answer_id'];
} else {
$sql = 'START TRANSACTION';
if (!($r = F_db_query($sql, $db))) {
F_display_db_error();
}
$sql = 'INSERT INTO ' . K_TABLE_ANSWERS . ' (
answer_question_id,
answer_description,
answer_explanation,
answer_isright,
answer_enabled,
answer_position,
answer_keyboard_key
) VALUES (
' . $current_question_id . ',
\'' . $answer_description . '\',
' . $answer_explanation . ',
\'' . $answer_isright . '\',
\'' . $answer_enabled . '\',
' . $answer_position . ',
' . $answer_keyboard_key . '
)';
if (!($r = F_db_query($sql, $db))) {
F_display_db_error(false);
F_db_query('ROLLBACK', $db);
} else {
// get new answer ID
$current_answer_id = F_db_insert_id($db, K_TABLE_ANSWERS, 'answer_id');
}
$sql = 'COMMIT';
if (!($r = F_db_query($sql, $db))) {
F_display_db_error();
}
}
} else {
F_display_db_error();
}
break;
}
// end of switch
}
// end of while
return TRUE;
}
/**
* Sends email test reports to users.
* @author Nicola Asuni
* @since 2005-02-24
* @param $test_id (int) TEST ID
* @param $user_id (int) USER ID (0 means all users)
* @param $testuser_id (int) test-user ID - if greater than zero, filter stats for the specified test-user.
* @param $group_id (int) GROUP ID (0 means all groups)
* @param $startdate (int) start date ID - if greater than zero, filter stats for the specified starting date
* @param $enddate (int) end date ID - if greater than zero, filter stats for the specified ending date
* @param $mode (int) type of report to send: 0=detailed report; 1=summary report (without questions)
* @param $display_mode display (int) mode: 0 = disabled; 1 = minimum; 2 = module; 3 = subject; 4 = question; 5 = answer.
* @param $show_graph (boolean) If true display the score graph.
*/
function F_send_report_emails($test_id, $user_id = 0, $testuser_id = 0, $group_id = 0, $startdate = 0, $enddate = 0, $mode = 0, $display_mode = 1, $show_graph = false)
{
global $l, $db;
require_once '../config/tce_config.php';
require_once '../../shared/code/tce_functions_test.php';
require_once '../../shared/code/tce_functions_test_stats.php';
require_once '../../shared/code/tce_class_mailer.php';
require_once 'tce_functions_user_select.php';
$mode = intval($mode);
if ($test_id > 0) {
$test_id = intval($test_id);
if (!F_isAuthorizedUser(K_TABLE_TESTS, 'test_id', $test_id, 'test_user_id')) {
return;
}
} else {
$test_id = 0;
}
if ($user_id > 0) {
$user_id = intval($user_id);
} else {
$user_id = 0;
}
if ($testuser_id > 0) {
$testuser_id = intval($testuser_id);
} else {
$testuser_id = 0;
}
if ($group_id > 0) {
$group_id = intval($group_id);
} else {
$group_id = 0;
}
if (!empty($startdate)) {
$startdate_time = strtotime($startdate);
$startdate = date(K_TIMESTAMP_FORMAT, $startdate_time);
} else {
$startdate = '';
}
if (!empty($enddate)) {
$enddate_time = strtotime($enddate);
$enddate = date(K_TIMESTAMP_FORMAT, $enddate_time);
} else {
$enddate = '';
}
// Instantiate C_mailer class
$mail = new C_mailer();
//Load default values
$mail->language = $l;
$mail->Priority = $emailcfg['Priority'];
$mail->ContentType = $emailcfg['ContentType'];
$mail->Encoding = $emailcfg['Encoding'];
$mail->WordWrap = $emailcfg['WordWrap'];
$mail->Mailer = $emailcfg['Mailer'];
$mail->Sendmail = $emailcfg['Sendmail'];
$mail->UseMSMailHeaders = $emailcfg['UseMSMailHeaders'];
$mail->Host = $emailcfg['Host'];
$mail->Port = $emailcfg['Port'];
$mail->Helo = $emailcfg['Helo'];
$mail->SMTPAuth = $emailcfg['SMTPAuth'];
$mail->SMTPSecure = $emailcfg['SMTPSecure'];
$mail->Username = $emailcfg['Username'];
$mail->Password = $emailcfg['Password'];
$mail->Timeout = $emailcfg['Timeout'];
$mail->SMTPDebug = $emailcfg['SMTPDebug'];
$mail->PluginDir = $emailcfg['PluginDir'];
$mail->Sender = $emailcfg['Sender'];
$mail->From = $emailcfg['From'];
$mail->FromName = $emailcfg['FromName'];
if ($emailcfg['Reply']) {
$mail->AddReplyTo($emailcfg['Reply'], $emailcfg['ReplyName']);
}
$mail->CharSet = $l['a_meta_charset'];
if (!$mail->CharSet) {
$mail->CharSet = $emailcfg['CharSet'];
}
$mail->Subject = $l['t_result_user'];
$mail->IsHTML(TRUE);
// Set message type to HTML.
$email_num = 0;
// count emails;
// get all data
$data = F_getAllUsersTestStat($test_id, $group_id, $user_id, $startdate, $enddate, 'total_score', false, $display_mode);
foreach ($data['testuser'] as $tu) {
if (strlen($tu['user_email']) > 3) {
// set HTML header
$mail->Body = $emailcfg['MsgHeader'];
// compose alternate TEXT message
$mail->AltBody = '' . $l['t_result_user'] . ' [' . $tu['testuser_creation_time'] . ']' . K_NEWLINE;
$mail->AltBody .= $l['w_test'] . ': ' . $tu['test']['test_name'] . K_NEWLINE;
$passmsg = '';
if ($tu['test']['test_score_threshold'] > 0) {
$mail->AltBody .= $l['w_test_score_threshold'] . ': ' . $tu['test']['test_score_threshold'];
if ($tu['total_score'] >= $tu['test']['test_score_threshold']) {
$passmsg = ' - ' . $l['w_passed'];
} else {
$passmsg = ' - ' . $l['w_not_passed'];
}
$mail->AltBody .= K_NEWLINE;
}
$mail->AltBody .= $l['w_score'] . ': ' . F_formatFloat($tu['total_score']) . ' ' . F_formatPercentage($tu['total_score_perc'], false) . $passmsg . K_NEWLINE;
if ($display_mode > 0) {
$mail->AltBody .= $l['w_answers_right'] . ': ' . $tu['right'] . ' ' . F_formatPercentage($tu['right_perc'], false) . K_NEWLINE;
$mail->AltBody .= $l['w_answers_wrong'] . ': ' . $tu['wrong'] . ' ' . F_formatPercentage($tu['wrong_perc'], false) . K_NEWLINE;
$mail->AltBody .= $l['w_questions_unanswered'] . ': ' . $tu['unanswered'] . ' ' . F_formatPercentage($tu['unanswered_perc'], false) . K_NEWLINE;
$mail->AltBody .= $l['w_questions_undisplayed'] . ': ' . $tu['undisplayed'] . ' ' . F_formatPercentage($tu['undisplayed_perc'], false) . K_NEWLINE;
}
if ($mode == 0) {
$pdfkey = getPasswordHash(date('Y') . $tu['id'] . K_RANDOM_SECURITY . $tu['test']['test_id'] . date('m') . $tu['user_id']);
// create PDF doc
$mode = 3;
$pdf_content = file_get_contents(K_PATH_HOST . K_PATH_TCEXAM . 'admin/code/tce_pdf_results.php?mode=' . $mode . '&diplay_mode=' . $display_mode . '&show_graph=' . $show_graph . '&test_id=' . $tu['test']['test_id'] . '&user_id=' . $tu['user_id'] . '&testuser_id=' . $tu['id'] . '&email=' . $pdfkey);
// set PDF document file name
$doc_name = 'tcexam_report';
$doc_name .= '_' . $mode;
$doc_name .= '_0';
$doc_name .= '_' . $tu['test']['test_id'];
$doc_name .= '_0';
$doc_name .= '_' . $tu['user_id'];
$doc_name .= '_' . $tu['id'];
$doc_name .= '.pdf';
// attach document
$mail->AddStringAttachment($pdf_content, $doc_name, $emailcfg['AttachmentsEncoding'], 'application/octet-stream');
$mail->AltBody .= K_NEWLINE . $l['w_attachment'] . ': ' . $doc_name . K_NEWLINE;
}
// convert alternate text to HTML
$mail->Body .= str_replace(K_NEWLINE, '<br />' . K_NEWLINE, $mail->AltBody);
// add HTML footer
$mail->Body .= $emailcfg['MsgFooter'];
//--- Elaborate user Templates ---
$mail->Body = str_replace('#CHARSET#', $l['a_meta_charset'], $mail->Body);
$mail->Body = str_replace('#LANG#', $l['a_meta_language'], $mail->Body);
$mail->Body = str_replace('#LANGDIR#', $l['a_meta_dir'], $mail->Body);
$mail->Body = str_replace('#EMAIL#', $tu['user_email'], $mail->Body);
$mail->Body = str_replace('#USERNAME#', htmlspecialchars($tu['user_name'], ENT_NOQUOTES, $l['a_meta_charset']), $mail->Body);
$mail->Body = str_replace('#USERFIRSTNAME#', htmlspecialchars($tu['user_firstname'], ENT_NOQUOTES, $l['a_meta_charset']), $mail->Body);
$mail->Body = str_replace('#USERLASTNAME#', htmlspecialchars($tu['user_lastname'], ENT_NOQUOTES, $l['a_meta_charset']), $mail->Body);
// add a "To" address
$mail->AddAddress($tu['user_email'], $tu['user_name']);
$email_num++;
$progresslog = '' . $email_num . '. ' . $tu['user_email'] . ' [' . $tu['user_name'] . ']';
//output user data
if (!$mail->Send()) {
//send email to user
$progresslog .= ' [' . $l['t_error'] . ']';
//display error message
}
$mail->ClearAddresses();
// Clear all addresses for next loop
$mail->ClearAttachments();
// Clears all previously set filesystem, string, and binary attachments
} else {
$progresslog = '[' . $l['t_error'] . '] ' . $tu['user_name'] . ': ' . $l['m_unknown_email'] . '';
//output user data
}
echo $progresslog . '<br />' . K_NEWLINE;
//output processed emails
flush();
// force browser output
}
$mail->ClearAddresses();
// Clear all addresses for next loop
$mail->ClearCustomHeaders();
// Clears all custom headers
$mail->ClearAllRecipients();
// Clears all recipients assigned in the TO, CC and BCC
$mail->ClearAttachments();
// Clears all previously set filesystem, string, and binary attachments
$mail->ClearReplyTos();
// Clears all recipients assigned in the ReplyTo array
return;
}
