/**
* Returns a user select box
* @param $label (string) Field label.
* @param $user_id (int) selected user ID.
* @param $fieldname (string) field name.
* @return array containing user's groups IDs
*/
function F_get_user_selectbox($label, $user_id = 0, $fieldname = 'user_id')
{
global $l, $db;
require_once '../config/tce_config.php';
$out = '';
$out .= '<div class="row">' . K_NEWLINE;
$out .= '<span class="label">' . K_NEWLINE;
$out .= '<label for="' . $fieldname . '">' . $label . '</label>' . K_NEWLINE;
$out .= '</span>' . K_NEWLINE;
$out .= '<span class="formw">' . K_NEWLINE;
$out .= '<select name="' . $fieldname . '" id="' . $fieldname . '" size="0">' . K_NEWLINE;
$out .= '<option value="0"';
if ($user_id == 0) {
$out .= ' selected="selected"';
}
$out .= '></option>' . K_NEWLINE;
$sql = 'SELECT user_id, user_lastname, user_firstname, user_name FROM ' . K_TABLE_USERS . ' WHERE (user_id>1)';
if ($_SESSION['session_user_level'] < K_AUTH_ADMINISTRATOR) {
// filter for level
$sql .= ' AND ((user_level<' . $_SESSION['session_user_level'] . ') OR (user_id=' . $_SESSION['session_user_id'] . '))';
// filter for groups
$sql .= ' AND user_id IN (SELECT tb.usrgrp_user_id
FROM ' . K_TABLE_USERGROUP . ' AS ta, ' . K_TABLE_USERGROUP . ' AS tb
WHERE ta.usrgrp_group_id=tb.usrgrp_group_id
AND ta.usrgrp_user_id=' . intval($_SESSION['session_user_id']) . '
AND tb.usrgrp_user_id=user_id)';
}
$sql .= ' ORDER BY user_lastname, user_firstname, user_name';
if ($r = F_db_query($sql, $db)) {
$countitem = 1;
while ($m = F_db_fetch_array($r)) {
$out .= '<option value="' . $m['user_id'] . '"';
if ($m['user_id'] == $user_id) {
$out .= ' selected="selected"';
}
$out .= '>' . $countitem . '. ' . htmlspecialchars($m['user_lastname'] . ' ' . $m['user_firstname'] . ' - ' . $m['user_name'] . '', ENT_NOQUOTES, $l['a_meta_charset']) . '</option>' . K_NEWLINE;
$countitem++;
}
} else {
$out .= '</select></span></div>' . K_NEWLINE;
F_display_db_error();
}
$out .= '</select>' . K_NEWLINE;
$out .= '</span>' . K_NEWLINE;
$out .= '</div>' . K_NEWLINE;
return $out;
}
$thispage_description = '';
require_once '../code/tce_page_header.php';
$sql = 'SELECT *
FROM ' . K_TABLE_USERS . '
WHERE (user_verifycode=\'' . F_escape_sql($verifycode) . '\'
AND user_id=\'' . $userid . '\'
AND user_email=\'' . F_escape_sql($email) . '\')
LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
// update user level
$sqlu = 'UPDATE ' . K_TABLE_USERS . ' SET
user_level=\'1\',
user_verifycode=NULL
WHERE user_id=' . $userid . '';
if (!($ru = F_db_query($sqlu, $db))) {
F_display_db_error(false);
} else {
F_print_error('MESSAGE', $l['m_user_registration_ok']);
echo K_NEWLINE;
echo '<div class="container">' . K_NEWLINE;
if (K_OTP_LOGIN) {
require_once '../../shared/tcpdf/tcpdf_barcodes_2d.php';
$host = preg_replace('/[h][t][t][p][s]?[:][\\/][\\/]/', '', K_PATH_HOST);
$qrcode = new TCPDF2DBarcode('otpauth://totp/' . $m['user_name'] . '@' . $host . '?secret=' . $m['user_otpkey'], 'QRCODE,H');
echo '<p>' . $l['m_otp_qrcode'] . '</p>' . K_NEWLINE;
echo '<h2>' . $m['user_otpkey'] . '</h2>' . K_NEWLINE;
echo '<div style="margin:40px 40px 40px 40px;">' . K_NEWLINE;
echo $qrcode->getBarcodeHTML(6, 6, 'black');
echo '</div>' . K_NEWLINE;
}
/**
* Export all users to CSV grouped by users' groups.
* @author Nicola Asuni
* @since 2006-03-30
* @return CSV data
*/
function F_csv_export_users()
{
global $l, $db;
require_once '../config/tce_config.php';
$csv = '';
// CSV data to be returned
// print column names
$csv .= 'user_id';
$csv .= K_TAB . 'user_name';
$csv .= K_TAB . 'user_password';
$csv .= K_TAB . 'user_email';
$csv .= K_TAB . 'user_regdate';
$csv .= K_TAB . 'user_ip';
$csv .= K_TAB . 'user_firstname';
$csv .= K_TAB . 'user_lastname';
$csv .= K_TAB . 'user_birthdate';
$csv .= K_TAB . 'user_birthplace';
$csv .= K_TAB . 'user_regnumber';
$csv .= K_TAB . 'user_ssn';
$csv .= K_TAB . 'user_level';
$csv .= K_TAB . 'user_verifycode';
$csv .= K_TAB . 'user_groups';
$sql = 'SELECT * FROM ' . K_TABLE_USERS . ' WHERE (user_id>1)';
if ($_SESSION['session_user_level'] < K_AUTH_ADMINISTRATOR) {
// filter for level
$sql .= ' AND ((user_level<' . $_SESSION['session_user_level'] . ') OR (user_id=' . $_SESSION['session_user_id'] . '))';
// filter for groups
$sql .= ' AND user_id IN (SELECT tb.usrgrp_user_id
FROM ' . K_TABLE_USERGROUP . ' AS ta, ' . K_TABLE_USERGROUP . ' AS tb
WHERE ta.usrgrp_group_id=tb.usrgrp_group_id
AND ta.usrgrp_user_id=' . intval($_SESSION['session_user_id']) . '
AND tb.usrgrp_user_id=user_id)';
}
$sql .= ' ORDER BY user_lastname,user_firstname,user_name';
if ($r = F_db_query($sql, $db)) {
while ($m = F_db_fetch_array($r)) {
$csv .= K_NEWLINE . $m['user_id'];
$csv .= K_TAB . $m['user_name'];
$csv .= K_TAB;
// password cannot be exported because is encrypted
$csv .= K_TAB . $m['user_email'];
$csv .= K_TAB . $m['user_regdate'];
$csv .= K_TAB . $m['user_ip'];
$csv .= K_TAB . $m['user_firstname'];
$csv .= K_TAB . $m['user_lastname'];
$csv .= K_TAB . substr($m['user_birthdate'], 0, 10);
$csv .= K_TAB . $m['user_birthplace'];
$csv .= K_TAB . $m['user_regnumber'];
$csv .= K_TAB . $m['user_ssn'];
$csv .= K_TAB . $m['user_level'];
$csv .= K_TAB . $m['user_verifycode'];
$csv .= K_TAB;
$grp = '';
// comma separated list of user's groups
$sqlg = 'SELECT *
FROM ' . K_TABLE_GROUPS . ', ' . K_TABLE_USERGROUP . '
WHERE usrgrp_group_id=group_id
AND usrgrp_user_id=' . $m['user_id'] . '
ORDER BY group_name';
if ($rg = F_db_query($sqlg, $db)) {
while ($mg = F_db_fetch_array($rg)) {
$grp .= $mg['group_name'] . ',';
}
} else {
F_display_db_error();
}
if (!empty($grp)) {
// add user's groups removing last comma
$csv .= substr($grp, 0, -1);
}
}
} else {
F_display_db_error();
}
return $csv;
}
/**
* Returns test data structure for selected user:
* <ul>
* <li>$data['all'] = total number of questions</li>
* <li>$data['right'] = number of right answers for multiple-choice questions (score > 50% max points)</li>
* <li>$data['wrong'] = number of wrong answers for multiple-choice questions (score <= 50% max points)</li>
* <li>$data['textright'] = number of right answers for free-text questions (score > 50% max points)</li>
* <li>$data['textwrong'] = number of wrong answers for free-text questions (score <= 50% max points)</li>
* <li>$data['unanswered'] = total number of unanswered questions</li>
* <li>$data['undisplayed'] = total number of undisplayed questions</li>
* <li>$data['basic_score'] = basic points for each difficulty level of questions</li>
* <li>$data['max_score'] = maximum test score</li>
* <li>$data['score'] = user's score</li>
* <li>$data['comment'] = user's test comment</li>
* <li>$data['time'] = user's test start time</li>
* </ul>
* @param $test_id (int) test ID
* @param $user_id (int) user's test ID
* return array $data
*/
function F_getUserTestStat($test_id, $user_id)
{
require_once '../config/tce_config.php';
global $db, $l;
$test_id = intval($test_id);
$user_id = intval($user_id);
$data = array();
// get test default scores
$sql = 'SELECT test_score_right, test_max_score, test_score_threshold
FROM ' . K_TABLE_TESTS . '
WHERE test_id=' . $test_id . '';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
$data['basic_score'] = $m['test_score_right'];
$data['max_score'] = $m['test_max_score'];
$data['score_threshold'] = $m['test_score_threshold'];
}
} else {
F_display_db_error();
}
// total number of questions
$data['all'] = F_count_rows(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 . ' AND testuser_user_id=' . $user_id . '');
// number of right answers
$data['right'] = F_count_rows(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 . ' AND testuser_user_id=' . $user_id . ' AND testlog_score>((question_difficulty*' . $data['basic_score'] . ')/2)');
// number of wrong answers
$data['wrong'] = F_count_rows(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 . ' AND testuser_user_id=' . $user_id . ' AND testlog_score<=((question_difficulty*' . $data['basic_score'] . ')/2)');
// total number of unanswered questions
$data['unanswered'] = F_count_rows(K_TABLE_TESTS_LOGS . ', ' . K_TABLE_TEST_USER, 'WHERE testlog_testuser_id=testuser_id AND testuser_test_id=' . $test_id . ' AND testuser_user_id=' . $user_id . ' AND testlog_change_time IS NULL');
// total number of undisplayed questions
$data['undisplayed'] = F_count_rows(K_TABLE_TESTS_LOGS . ', ' . K_TABLE_TEST_USER, 'WHERE testlog_testuser_id=testuser_id AND testuser_test_id=' . $test_id . ' AND testuser_user_id=' . $user_id . ' AND testlog_display_time IS NULL');
// number of free-text unrated questions
$data['unrated'] = F_count_rows(K_TABLE_TESTS_LOGS . ', ' . K_TABLE_TEST_USER, 'WHERE testlog_testuser_id=testuser_id AND testuser_test_id=' . $test_id . ' AND testuser_user_id=' . $user_id . ' AND testlog_score IS NULL');
// get user's score
$sql = 'SELECT SUM(testlog_score) AS total_score
FROM ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_TEST_USER . '
WHERE testlog_testuser_id=testuser_id
AND testuser_user_id=' . $user_id . '
AND testuser_test_id=' . $test_id . '
GROUP BY testuser_id';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
$data['score'] = $m['total_score'];
}
} else {
F_display_db_error();
}
// get user's test comment
$data['comment'] = '';
$sql = 'SELECT testuser_comment, testuser_creation_time
FROM ' . K_TABLE_TEST_USER . '
WHERE testuser_user_id=' . $user_id . '
AND testuser_test_id=' . $test_id . '
LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
$data['comment'] = $m['testuser_comment'];
$data['time'] = $m['testuser_creation_time'];
}
} else {
F_display_db_error();
}
$sql = 'SELECT testuser_id, testuser_creation_time, testuser_status, MAX(testlog_change_time) AS test_end_time
FROM ' . K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS_LOGS . '
WHERE testlog_testuser_id=testuser_id
AND testuser_test_id=' . $test_id . '
AND testuser_user_id=' . $user_id . '
AND testuser_status>0
GROUP BY testuser_id, testuser_creation_time, testuser_status
LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
$data['test_start_time'] = $m['testuser_creation_time'];
$data['test_end_time'] = $m['test_end_time'];
$data['testuser_id'] = $m['testuser_id'];
$data['testuser_status'] = $m['testuser_status'];
}
} else {
F_display_db_error();
}
return $data;
}
$pdf->Ln();
$pdf->writeHTMLCell(0, $data_cell_height, PDF_MARGIN_LEFT + $data_cell_width_third, $pdf->GetY(), F_decode_tcecode($mq['question_description']), 1, 1, '', '');
if (K_ENABLE_QUESTION_EXPLANATION and !empty($mq['question_explanation'])) {
$pdf->Cell($data_cell_width_third, $data_cell_height, '', 0, 0, 'C', 0);
$pdf->SetFont('', 'BIU');
$pdf->Cell(0, $data_cell_height, $l['w_explanation'], 'LTR', 1, '', 0, '', 0);
$pdf->SetFont('', '');
$pdf->writeHTMLCell(0, $data_cell_height, PDF_MARGIN_LEFT + $data_cell_width_third, $pdf->GetY(), F_decode_tcecode($mq['question_explanation']), 'LRB', 1, '', '');
}
if ($show_answers) {
// display alternative answers
$sqla = 'SELECT *
FROM ' . K_TABLE_ANSWERS . '
WHERE answer_question_id=\'' . $mq['question_id'] . '\'
ORDER BY answer_position,answer_isright DESC';
if ($ra = F_db_query($sqla, $db)) {
$idx = 0;
// count items
while ($ma = F_db_fetch_array($ra)) {
$idx++;
$answer_disabled = intval(!F_getBoolean($ma['answer_enabled']));
$answer_isright = intval(F_getBoolean($ma['answer_isright']));
$pdf->Cell($data_cell_width_third, $data_cell_height, '', 0, 0, 'C', 0);
$pdf->Cell($data_cell_width_third, $data_cell_height, $idx, 1, 0, 'C', $answer_disabled);
if ($mq['question_type'] != 4) {
$pdf->Cell($data_cell_width_third / 2, $data_cell_height, $qright[$answer_isright], 1, 0, 'C', $answer_disabled);
} else {
$pdf->Cell($data_cell_width_third / 2, $data_cell_height, '', 1, 0, 'C', $answer_disabled);
}
if ($ma['answer_position'] > 0) {
$pdf->Cell($data_cell_width_third, $data_cell_height, $ma['answer_position'], 1, 0, 'C', $answer_disabled);
/**
* Return true if the file is used on question or answer descriptions
* @author Nicola Asuni
* @param $file (string) the fiel to search
* @return true if the file is used, false otherwise
*/
function F_isUsedMediaFile($file)
{
global $l, $db;
require_once '../config/tce_config.php';
// remove cache root from file path
$file = substr($file, strlen(K_PATH_CACHE));
// search on questions
$sql = 'SELECT question_id FROM ' . K_TABLE_QUESTIONS . ' WHERE question_description LIKE \'%' . $file . '[/object%\' OR question_explanation LIKE \'%' . $file . '[/object%\' LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
return true;
}
} else {
F_display_db_error();
}
// search on answers
$sql = 'SELECT answer_id FROM ' . K_TABLE_ANSWERS . ' WHERE answer_description LIKE \'%' . $file . '[/object%\' OR answer_explanation LIKE \'%' . $file . '[/object%\' LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
return true;
}
} else {
F_display_db_error();
}
return false;
}
if (isset($_POST['xuser_otpcode']) and !empty($_POST['xuser_otpcode']) and ($_POST['xuser_otpcode'] == F_getOTP($m['user_otpkey'], $mtime) or $_POST['xuser_otpcode'] == F_getOTP($m['user_otpkey'], $mtime - 30) or $_POST['xuser_otpcode'] == F_getOTP($m['user_otpkey'], $mtime + 30))) {
// check if this OTP token has been alredy used
$sqlt = 'SELECT cpsession_id FROM ' . K_TABLE_SESSIONS . ' WHERE cpsession_id=\'' . $_POST['xuser_otpcode'] . '\' LIMIT 1';
if ($rt = F_db_query($sqlt, $db)) {
if (!F_db_fetch_array($rt)) {
// Store this token on the session table to mark it as invalid for 5 minute (300 seconds)
$sqltu = 'INSERT INTO ' . K_TABLE_SESSIONS . ' (
cpsession_id,
cpsession_expiry,
cpsession_data
) VALUES (
\'' . $_POST['xuser_otpcode'] . '\',
\'' . date(K_TIMESTAMP_FORMAT, time() + 300) . '\',
\'300\'
)';
if (!F_db_query($sqltu, $db)) {
F_display_db_error();
}
$otp = true;
}
}
}
}
if (!K_OTP_LOGIN or $otp) {
// sets some user's session data
$_SESSION['session_user_id'] = $m['user_id'];
$_SESSION['session_user_name'] = $m['user_name'];
$_SESSION['session_user_ip'] = getNormalizedIP($_SERVER['REMOTE_ADDR']);
$_SESSION['session_user_level'] = $m['user_level'];
$_SESSION['session_user_firstname'] = urlencode($m['user_firstname']);
$_SESSION['session_user_lastname'] = urlencode($m['user_lastname']);
/**
* Clone the specified object, including child objects
* @param $source_obj_id (int) Source parent object ID.
* @param $target_obj_id (int) Target parent object ID.
*/
function F_clone_child_objects($source_obj_id, $target_obj_id)
{
global $l, $db;
require_once '../config/tce_config.php';
$sql = 'SELECT * FROM ' . K_TABLE_OBJECTS . ', ' . K_TABLE_OBJECTS_MAP . ' WHERE omp_child_obj_id=obj_id AND omp_parent_obj_id=' . $source_obj_id . '';
if ($r = F_db_query($sql, $db)) {
while ($m = F_db_fetch_array($r)) {
// create new object
$sqli = 'INSERT INTO ' . K_TABLE_OBJECTS . ' (
obj_obt_id,
obj_name,
obj_description,
obj_label,
obj_tag,
obj_mnf_id,
obj_owner_id,
obj_tenant_id
) VALUES (
' . $m['obj_obt_id'] . ',
\'' . $m['obj_name'] . '\',
' . F_empty_to_null($m['obj_description']) . ',
' . F_empty_to_null($m['obj_label']) . ',
' . F_empty_to_null($m['obj_tag']) . ',
' . F_empty_to_null($m['obj_mnf_id']) . ',
' . F_empty_to_null($m['obj_owner_id']) . ',
' . F_empty_to_null($m['obj_tenant_id']) . '
)';
if (!($ri = F_db_query($sqli, $db))) {
F_display_db_error(false);
} else {
$child_obj_id = F_db_insert_id($db, K_TABLE_OBJECTS, 'obj_id');
// add new object as child
$sqli = 'INSERT INTO ' . K_TABLE_OBJECTS_MAP . ' (
omp_parent_obj_id,
omp_child_obj_id
) VALUES (
' . $target_obj_id . ',
' . $child_obj_id . '
)';
if (!($ri = F_db_query($sqli, $db))) {
F_display_db_error(false);
}
F_clone_child_objects($m['obj_id'], $child_obj_id);
}
}
} else {
F_display_db_error();
}
}
} else {
// all question types
$subjlist .= '*';
}
$subjlist .= '</acronym> ';
$subjlist .= '<acronym class="offbox" title="' . $l['h_question_difficulty'] . '">' . $m['tsubset_difficulty'] . '</acronym> ';
$subjlist .= '<acronym class="offbox" title="' . $l['h_num_answers'] . '">' . $m['tsubset_answers'] . '</acronym> ';
$subjlist .= ' <a href="' . $_SERVER['SCRIPT_NAME'] . '?menu_mode=deletesubject&test_id=' . $test_id . '&tsubset_id=' . $m['tsubset_id'] . '" title="' . $l['h_delete'] . '" class="deletebutton">' . $l['w_delete'] . '</a>';
$subjlist .= '</li>' . K_NEWLINE;
// update test_max_score
$test_max_score_new += $test_score_right * $m['tsubset_difficulty'] * $m['tsubset_quantity'];
if (isset($test_max_score) and $test_max_score_new != $test_max_score) {
$test_max_score = $test_max_score_new;
// update max score on test table
$sqlup = 'UPDATE ' . K_TABLE_TESTS . ' SET test_max_score=' . $test_max_score . ' WHERE test_id=' . $test_id . '';
if (!($rup = F_db_query($sqlup, $db))) {
F_display_db_error(false);
}
}
}
if (strlen($subjlist) > 0) {
echo '<ul>' . K_NEWLINE . $subjlist . '</ul>' . K_NEWLINE;
}
} else {
F_display_db_error();
}
echo ' ' . K_NEWLINE;
echo $l['w_max_score'] . ': ' . $test_max_score_new;
echo '<input type="hidden" name="test_max_score" id="test_max_score" value="' . $test_max_score_new . '" />';
echo '</div>' . K_NEWLINE;
echo '<br /><br />' . K_NEWLINE;
/**
* Sync user groups with the ones specified on the configuration file for alternate authentication.
* @param $usrid (int) ID of the user to update.
* @param $grpids (mixed) Group ID or comma separated list of group IDs (0=all available groups).
* @author Nicola Asuni
* @since 2012-09-11
*/
function F_syncUserGroups($usrid, $grpids)
{
global $l, $db;
require_once '../config/tce_config.php';
$usrid = intval($usrid);
// select new group IDs
$newgrps = array();
if (is_string($grpids)) {
// comma separated list of group IDs
$newgrps = explode(',', $grpids);
array_walk($newgrps, 'intval');
$newgrps = array_unique($newgrps, SORT_NUMERIC);
} elseif ($grpids == 0) {
// all available groups
$sqlg = 'SELECT group_id FROM ' . K_TABLE_GROUPS . '';
if ($rg = F_db_query($sqlg, $db)) {
while ($mg = F_db_fetch_array($rg)) {
$newgrps[] = $mg['group_id'];
}
} else {
F_display_db_error();
}
} elseif ($grpids > 0) {
// single default group
$newgrps[] = intval($grpids);
}
if (empty($newgrps)) {
return;
}
// select existing group IDs
$usrgrps = array();
$sqlu = 'SELECT usrgrp_group_id FROM ' . K_TABLE_USERGROUP . ' WHERE usrgrp_user_id=' . $usrid . '';
if ($ru = F_db_query($sqlu, $db)) {
while ($mu = F_db_fetch_array($ru)) {
$usrgrps[] = $mu['usrgrp_group_id'];
}
} else {
F_display_db_error();
}
// extract missing groups
$diffgrps = array_values(array_diff($newgrps, $usrgrps));
// add missing groups
foreach ($diffgrps as $grpid) {
if ($grpid > 0) {
// add user to default user groups
$sql = 'INSERT INTO ' . K_TABLE_USERGROUP . ' (
usrgrp_user_id,
usrgrp_group_id
) VALUES (
\'' . $usrid . '\',
\'' . $grpid . '\'
)';
if (!($r = F_db_query($sql, $db))) {
F_display_db_error();
}
}
}
}
/**
* 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;
}
}
}
}
/**
* Garbage collector.<br>
* Deletes expired sessions.<br>
* NOTE: while time() function returns a 32 bit integer, it works fine until year 2038.
* @return int number of deleted sessions.
*/
function F_session_gc()
{
global $db;
$expiry_time = date(K_TIMESTAMP_FORMAT);
$sql = 'DELETE FROM ' . K_TABLE_SESSIONS . ' WHERE cpsession_expiry<=\'' . $expiry_time . '\'';
if (!($r = F_db_query($sql, $db))) {
return FALSE;
}
return F_db_affected_rows($db, $r);
}
/**
* Import user's test data from OMR.
* @param $user_id (int) user ID.
* @param $date (string) date-time field.
* @param $omr_testdata (array) Array containing test data.
* @param $omr_answers (array) Array containing test answers (from OMR).
* @return boolean TRUE in case of success, FALSE otherwise.
*/
function F_importOMRTestData($user_id, $date, $omr_testdata, $omr_answers)
{
require_once '../config/tce_config.php';
require_once '../../shared/code/tce_functions_test.php';
global $db, $l;
// check arrays
if (count($omr_testdata) > count($omr_answers) + 1) {
// arrays must contain the same amount of questions
return false;
}
$test_id = intval($omr_testdata[0]);
$user_id = intval($user_id);
$time = strtotime($date);
$date = date(K_TIMESTAMP_FORMAT, $time);
$dateanswers = date(K_TIMESTAMP_FORMAT, $time + 1);
// check user's group
if (F_count_rows(K_TABLE_USERGROUP . ', ' . K_TABLE_TEST_GROUPS . ' WHERE usrgrp_group_id=tstgrp_group_id AND tstgrp_test_id=' . $test_id . ' AND usrgrp_user_id=' . $user_id . ' LIMIT 1') == 0) {
return false;
}
// get test data
$testdata = F_getTestData($test_id);
// 1. delete previous test data
$sqld = 'DELETE FROM ' . K_TABLE_TEST_USER . ' WHERE testuser_test_id=' . $test_id . ' AND testuser_user_id=' . $user_id . '';
if (!($rd = F_db_query($sqld, $db))) {
F_display_db_error();
}
// 2. create new user's test entry
// ------------------------------
$sql = 'INSERT INTO ' . K_TABLE_TEST_USER . ' (
testuser_test_id,
testuser_user_id,
testuser_status,
testuser_creation_time,
testuser_comment
) VALUES (
' . $test_id . ',
' . $user_id . ',
4,
\'' . $date . '\',
\'OMR\'
)';
if (!($r = F_db_query($sql, $db))) {
F_display_db_error(false);
return false;
} else {
// get inserted ID
$testuser_id = F_db_insert_id($db, K_TABLE_TEST_USER, 'testuser_id');
}
// 3. create test log entries
$num_questions = count($omr_testdata) - 1;
// for each question on array
for ($q = 1; $q <= $num_questions; ++$q) {
$question_id = intval($omr_testdata[$q][0]);
$num_answers = count($omr_testdata[$q][1]);
// get question data
$sqlq = 'SELECT question_type, question_difficulty FROM ' . K_TABLE_QUESTIONS . ' WHERE question_id=' . $question_id . ' LIMIT 1';
if ($rq = F_db_query($sqlq, $db)) {
if ($mq = F_db_fetch_array($rq)) {
// question scores
$question_right_score = $testdata['test_score_right'] * $mq['question_difficulty'];
$question_wrong_score = $testdata['test_score_wrong'] * $mq['question_difficulty'];
$question_unanswered_score = $testdata['test_score_unanswered'] * $mq['question_difficulty'];
// add question
$sqll = 'INSERT INTO ' . K_TABLE_TESTS_LOGS . ' (
testlog_testuser_id,
testlog_question_id,
testlog_score,
testlog_creation_time,
testlog_display_time,
testlog_reaction_time,
testlog_order,
testlog_num_answers
) VALUES (
' . $testuser_id . ',
' . $question_id . ',
' . $question_unanswered_score . ',
\'' . $date . '\',
\'' . $date . '\',
1,
' . $q . ',
' . $num_answers . '
)';
if (!($rl = F_db_query($sqll, $db))) {
F_display_db_error(false);
return false;
}
$testlog_id = F_db_insert_id($db, K_TABLE_TESTS_LOGS, 'testlog_id');
// set initial question score
if ($mq['question_type'] == 1) {
// MCSA
$qscore = $question_unanswered_score;
} else {
// MCMA
$qscore = 0;
}
$unanswered = true;
// for each question on array
for ($a = 1; $a <= $num_answers; ++$a) {
$answer_id = intval($omr_testdata[$q][1][$a]);
if (isset($omr_answers[$q][$a])) {
$answer_selected = $omr_answers[$q][$a];
//-1, 0, 1
} else {
$answer_selected = -1;
}
// add answer
$sqli = 'INSERT INTO ' . K_TABLE_LOG_ANSWER . ' (
logansw_testlog_id,
logansw_answer_id,
logansw_selected,
logansw_order
) VALUES (
' . $testlog_id . ',
' . $answer_id . ',
' . $answer_selected . ',
' . $a . '
)';
if (!($ri = F_db_query($sqli, $db))) {
F_display_db_error(false);
return false;
}
// calculate question score
if ($mq['question_type'] < 3) {
// MCSA or MCMA
// check if the answer is right
$answer_isright = false;
$sqla = 'SELECT answer_isright FROM ' . K_TABLE_ANSWERS . ' WHERE answer_id=' . $answer_id . ' LIMIT 1';
if ($ra = F_db_query($sqla, $db)) {
if ($ma = F_db_fetch_array($ra)) {
$answer_isright = F_getBoolean($ma['answer_isright']);
switch ($mq['question_type']) {
case 1:
// MCSA - Multiple Choice Single Answer
if ($answer_selected == 1) {
$unanswered = false;
if ($answer_isright) {
$qscore = $question_right_score;
} else {
$qscore = $question_wrong_score;
}
}
break;
case 2:
// MCMA - Multiple Choice Multiple Answer
if ($answer_selected == -1) {
$qscore += $question_unanswered_score;
} elseif ($answer_selected == 0) {
$unanswered = false;
if ($answer_isright) {
$qscore += $question_wrong_score;
} else {
$qscore += $question_right_score;
}
} elseif ($answer_selected == 1) {
$unanswered = false;
if ($answer_isright) {
$qscore += $question_right_score;
} else {
$qscore += $question_wrong_score;
}
}
break;
}
}
} else {
F_display_db_error(false);
return false;
}
}
}
// end for each answer
if ($mq['question_type'] == 2) {
// MCMA
// normalize score
if (F_getBoolean($testdata['test_mcma_partial_score'])) {
// use partial scoring for MCMA and ORDER questions
$qscore = round($qscore / $num_answers, 3);
} else {
// all-or-nothing points
if ($qscore >= $question_right_score * $num_answers) {
// right
$qscore = $question_right_score;
} elseif ($qscore == $question_unanswered_score * $num_answers) {
// unanswered
$qscore = $question_unanswered_score;
} else {
// wrong
$qscore = $question_wrong_score;
}
}
}
if ($unanswered) {
$change_time = '';
} else {
$change_time = $dateanswers;
}
// update question score
$sqll = 'UPDATE ' . K_TABLE_TESTS_LOGS . ' SET
testlog_score=' . $qscore . ',
testlog_change_time=' . F_empty_to_null($change_time) . ',
testlog_reaction_time=1000
WHERE testlog_id=' . $testlog_id . '';
if (!($rl = F_db_query($sqll, $db))) {
F_display_db_error();
return false;
}
}
} else {
F_display_db_error(false);
return false;
}
}
// end for each question
return true;
}
} else {
header('Location: index.php');
//redirect browser to public main page
exit;
}
if (isset($_REQUEST['test_id']) and $_REQUEST['test_id'] > 0) {
$test_id = intval($_REQUEST['test_id']);
} else {
header('Location: index.php');
//redirect browser to public main page
exit;
}
// security check
$checkid = 0;
$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);
/**
* Updates user's test comment.<br>
* @param $test_id (int) test ID
* @param $testcomment (string) user comment.
* @return string XHTML code
* @since 4.0.000 (2006-10-01)
*/
function F_updateTestComment($test_id, $testcomment)
{
require_once '../config/tce_config.php';
global $db, $l;
$test_id = intval($test_id);
$testcomment = F_escape_sql($testcomment);
$user_id = intval($_SESSION['session_user_id']);
$sql = 'UPDATE ' . K_TABLE_TEST_USER . '
SET testuser_comment=\'' . $testcomment . '\'
WHERE testuser_test_id=' . $test_id . '
AND testuser_user_id=' . $user_id . '';
if (!($r = F_db_query($sql, $db))) {
F_display_db_error();
}
}
/**
* Sets the end element handler function for the XML parser parser.end_element_handler.
* @param $parser (resource) The first parameter, parser, is a reference to the XML parser calling the handler.
* @param $name (string) The second parameter, name, contains the name of the element for which this handler is called. If case-folding is in effect for this parser, the element name will be in uppercase letters.
* @private
*/
private function endElementHandler($parser, $name)
{
global $l, $db;
require_once '../config/tce_config.php';
require_once 'tce_functions_user_select.php';
switch (strtolower($name)) {
case 'name':
case 'password':
case 'email':
case 'regdate':
case 'ip':
case 'firstname':
case 'lastname':
case 'birthdate':
case 'birthplace':
case 'regnumber':
case 'ssn':
case 'level':
case 'verifycode':
$this->current_data = F_escape_sql(F_xml_to_text($this->current_data));
$this->user_data[$this->current_element] = $this->current_data;
$this->current_element = '';
$this->current_data = '';
break;
case 'group':
$group_name = F_escape_sql(F_xml_to_text($this->current_data));
// check if group already exist
$sql = 'SELECT group_id
FROM ' . K_TABLE_GROUPS . '
WHERE group_name=\'' . $group_name . '\'
LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
// the group has been already added
$this->group_data[] = $m['group_id'];
} else {
// add new group
$sqli = 'INSERT INTO ' . K_TABLE_GROUPS . ' (
group_name
) VALUES (
\'' . $group_name . '\'
)';
if (!($ri = F_db_query($sqli, $db))) {
F_display_db_error(false);
} else {
$this->group_data[] = F_db_insert_id($db, K_TABLE_GROUPS, 'group_id');
}
}
} else {
F_display_db_error();
}
break;
case 'user':
// insert users
if (!empty($this->user_data['user_name'])) {
if (empty($this->user_data['user_regdate'])) {
$this->user_data['user_regdate'] = date(K_TIMESTAMP_FORMAT);
}
if (empty($this->user_data['user_ip'])) {
$this->user_data['user_ip'] = getNormalizedIP($_SERVER['REMOTE_ADDR']);
}
if (!isset($this->user_data['user_level']) or strlen($this->user_data['user_level']) == 0) {
$this->user_data['user_level'] = 1;
}
if ($_SESSION['session_user_level'] < K_AUTH_ADMINISTRATOR) {
// you cannot edit a user with a level equal or higher than yours
$this->user_data['user_level'] = min(max(0, $_SESSION['session_user_level'] - 1), $this->user_data['user_level']);
// non-administrator can access only to his/her groups
if (empty($this->group_data)) {
break;
}
$common_groups = array_intersect(F_get_user_groups($_SESSION['session_user_id']), $this->group_data);
if (empty($common_groups)) {
break;
}
}
// check if user already exist
$sql = 'SELECT user_id,user_level
FROM ' . K_TABLE_USERS . '
WHERE user_name=\'' . $this->user_data['user_name'] . '\'
OR user_regnumber=\'' . $this->user_data['user_regnumber'] . '\'
OR user_ssn=\'' . $this->user_data['user_ssn'] . '\'
LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
// the user has been already added
$user_id = $m['user_id'];
if ($_SESSION['session_user_level'] >= K_AUTH_ADMINISTRATOR or $_SESSION['session_user_level'] > $m['user_level']) {
//update user data
$sqlu = 'UPDATE ' . K_TABLE_USERS . ' SET
user_regdate=\'' . $this->user_data['user_regdate'] . '\',
user_ip=\'' . $this->user_data['user_ip'] . '\',
user_name=\'' . $this->user_data['user_name'] . '\',
user_email=' . F_empty_to_null($this->user_data['user_email']) . ',';
// update password only if it is specified
if (!empty($this->user_data['user_password'])) {
$sqlu .= ' user_password=\'' . md5($this->user_data['user_password']) . '\',';
}
$sqlu .= '
user_regnumber=' . F_empty_to_null($this->user_data['user_regnumber']) . ',
user_firstname=' . F_empty_to_null($this->user_data['user_firstname']) . ',
user_lastname=' . F_empty_to_null($this->user_data['user_lastname']) . ',
user_birthdate=' . F_empty_to_null($this->user_data['user_birthdate']) . ',
user_birthplace=' . F_empty_to_null($this->user_data['user_birthplace']) . ',
user_ssn=' . F_empty_to_null($this->user_data['user_ssn']) . ',
user_level=\'' . $this->user_data['user_level'] . '\',
user_verifycode=' . F_empty_to_null($this->user_data['user_verifycode']) . '
WHERE user_id=' . $user_id . '';
if (!($ru = F_db_query($sqlu, $db))) {
F_display_db_error(false);
return FALSE;
}
} else {
// no user is updated, so empty groups
$this->group_data = array();
}
} else {
// add new user
$sqlu = 'INSERT INTO ' . K_TABLE_USERS . ' (
user_regdate,
user_ip,
user_name,
user_email,
user_password,
user_regnumber,
user_firstname,
user_lastname,
user_birthdate,
user_birthplace,
user_ssn,
user_level,
user_verifycode
) VALUES (
' . F_empty_to_null($this->user_data['user_regdate']) . ',
\'' . $this->user_data['user_ip'] . '\',
\'' . $this->user_data['user_name'] . '\',
' . F_empty_to_null($this->user_data['user_email']) . ',
\'' . md5($this->user_data['user_password']) . '\',
' . F_empty_to_null($this->user_data['user_regnumber']) . ',
' . F_empty_to_null($this->user_data['user_firstname']) . ',
' . F_empty_to_null($this->user_data['user_lastname']) . ',
' . F_empty_to_null($this->user_data['user_birthdate']) . ',
' . F_empty_to_null($this->user_data['user_birthplace']) . ',
' . F_empty_to_null($this->user_data['user_ssn']) . ',
\'' . $this->user_data['user_level'] . '\',
' . F_empty_to_null($this->user_data['user_verifycode']) . '
)';
if (!($ru = F_db_query($sqlu, $db))) {
F_display_db_error(false);
return FALSE;
} else {
$user_id = F_db_insert_id($db, K_TABLE_USERS, 'user_id');
}
}
} else {
F_display_db_error(false);
return FALSE;
}
// user's groups
if (!empty($this->group_data)) {
while (list($key, $group_id) = each($this->group_data)) {
// check if user-group already exist
$sqls = 'SELECT *
FROM ' . K_TABLE_USERGROUP . '
WHERE usrgrp_group_id=\'' . $group_id . '\'
AND usrgrp_user_id=\'' . $user_id . '\'
LIMIT 1';
if ($rs = F_db_query($sqls, $db)) {
if (!($ms = F_db_fetch_array($rs))) {
// associate group to user
$sqlg = 'INSERT INTO ' . K_TABLE_USERGROUP . ' (
usrgrp_user_id,
usrgrp_group_id
) VALUES (
' . $user_id . ',
' . $group_id . '
)';
if (!($rg = F_db_query($sqlg, $db))) {
F_display_db_error(false);
return FALSE;
}
}
} else {
F_display_db_error(false);
return FALSE;
}
}
}
}
break;
default:
break;
}
}
/**
* Display Pages navigation index.
* @param $script_name (string) url of the calling page
* @param $sql (string) sql used to select records
* @param $firstrow (int) first row number
* @param $rowsperpage (int) number of max rows per page
* @param $param_array (string) parameters to pass on url via GET
* @return mixed the number of pages in case of success, FALSE otherwise
*/
function F_show_page_navigator($script_name, $sql, $firstrow, $rowsperpage, $param_array)
{
global $l, $db;
require_once '../config/tce_config.php';
$max_pages = 4;
// max pages to display on page selector
$indexbar = '';
// string for selection page html code
$firstrow = intval($firstrow);
$rowsperpage = intval($rowsperpage);
if (!$sql or $rowsperpage < 1) {
return FALSE;
}
if (!($r = F_db_query($sql, $db))) {
F_display_db_error();
}
// build base url for all links
$baseaddress = $script_name;
if (empty($param_array)) {
$baseaddress .= '?';
} else {
$param_array = substr($param_array, 5);
// remove first "&"
$baseaddress .= '?' . $param_array . '&';
}
$count_rows = preg_match('/GROUP BY/i', $sql);
//check if query contain a "GROUP BY"
$all_updates = F_db_num_rows($r);
if ($all_updates == 1 and !$count_rows) {
list($all_updates) = F_db_fetch_array($r);
}
if (!$all_updates) {
//no records
F_print_error('MESSAGE', $l['m_search_void']);
} else {
if ($all_updates > $rowsperpage) {
$indexbar .= '<div class="pageselector">' . $l['w_page'] . ': ';
$page_range = $max_pages * $rowsperpage;
if ($firstrow <= $page_range) {
$page_range = 2 * $page_range - $firstrow + $rowsperpage;
} elseif ($firstrow >= $all_updates - $page_range) {
$page_range = 2 * $page_range - ($all_updates - 2 * $rowsperpage - $firstrow);
}
if ($firstrow >= $rowsperpage) {
$indexbar .= '<a href="' . $baseaddress . 'firstrow=0">1</a> | ';
$indexbar .= '<a href="' . $baseaddress . 'firstrow=' . ($firstrow - $rowsperpage) . '" title="' . $l['w_previous'] . '"><</a> | ';
} else {
$indexbar .= '1 | < | ';
}
$count = 2;
$x = 0;
for ($x = $rowsperpage; $x < $all_updates - $rowsperpage; $x += $rowsperpage) {
if ($x >= $firstrow - $page_range and $x <= $firstrow + $page_range) {
if ($x == $firstrow) {
$indexbar .= $count . ' | ';
} else {
$indexbar .= '<a href="' . $baseaddress . 'firstrow=' . $x . '" title="' . $count . '">' . $count . '</a> | ';
}
}
$count++;
}
if ($firstrow + $rowsperpage < $all_updates) {
$indexbar .= '<a href="' . $baseaddress . 'firstrow=' . ($firstrow + $rowsperpage) . '" title="' . $l['w_next'] . '">></a> | ';
$indexbar .= '<a href="' . $baseaddress . 'firstrow=' . $x . '" title="' . $count . '">' . $count . '</a>';
} else {
$indexbar .= '> | ' . $count;
}
$indexbar .= '</div>';
}
}
echo $indexbar;
// display the page selector
return $all_updates;
//return number of records found
}
/**
* Return the IDs of groups asociated to the selected user.
* @param $user_id (int) User ID.
* @return (array) Array of group IDs.
*/
function F_getUserGroups($user_id)
{
global $l, $db;
require_once '../config/tce_config.php';
$groups = array();
// check if user is an administrator
$sql = 'SELECT usrgrp_group_id FROM ' . K_TABLE_USERGROUP . ' WHERE usrgrp_user_id=' . $user_id . '';
if ($r = F_db_query($sql, $db)) {
while ($m = F_db_fetch_array($r)) {
$groups[] = $m['usrgrp_group_id'];
}
} else {
F_display_db_error();
}
return $groups;
}
/**
* Return the user ID from registration number.
* @param $regnumber (int) user registration number.
* @return (int) User ID or 0 in case of error.
* @since 11.3.005 (2012-07-31)
*/
function F_getUIDfromRegnum($regnum)
{
global $l, $db;
require_once '../config/tce_config.php';
$sql = 'SELECT user_id FROM ' . K_TABLE_USERS . ' WHERE user_regnumber=\'' . F_escape_sql($db, $regnum) . '\' LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
return $m['user_id'];
}
}
return 0;
}
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 (isset($group_id) and $group_id > 0) {
$sqlr .= ',' . K_TABLE_USERGROUP . '';
}
$sqlr .= ' WHERE testlog_testuser_id=testuser_id
AND testuser_user_id=user_id
AND testuser_test_id=' . $test_id . '';
if (isset($group_id) and $group_id > 0) {
$sqlr .= ' AND usrgrp_user_id=user_id
AND usrgrp_group_id=' . $group_id . '';
}
$sqlr .= ' GROUP BY testuser_id, testuser_creation_time, user_id, user_lastname, user_firstname, user_name, 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($test_id, $mr['user_id']);
$halfscore = $usrtestdata['max_score'] / 2;
echo '<tr>';
echo '<td>';
/**
* Check if specified fields are unique on table.
* @param $table (string) table name
* @param $where (string) SQL where clause
* @param $fieldname (mixed) name of table column to check
* @param $fieldid (mixed) ID of table row to check
* @return bool true if unique, false otherwise
*/
function F_check_unique($table, $where, $fieldname = FALSE, $fieldid = FALSE)
{
require_once '../config/tce_config.php';
global $l, $db;
$sqlc = 'SELECT * FROM ' . $table . ' WHERE ' . $where . ' LIMIT 1';
if ($rc = F_db_query($sqlc, $db)) {
if ($fieldname === FALSE and $fieldid === FALSE and F_count_rows($table, 'WHERE ' . $where) > 0) {
return FALSE;
}
if ($mc = F_db_fetch_array($rc)) {
if ($mc[$fieldname] == $fieldid) {
return TRUE;
// the values are unchanged
}
} else {
// the new values are not yet present on table
return TRUE;
}
} else {
F_display_db_error();
}
// another table row contains the same values
return FALSE;
}
/**
* Export user results in XML format.
* @param $user_id (int) user ID - if greater than zero, filter stats for the specified user.
* @param $startdate (string) start date ID - if greater than zero, filter stats for the specified starting date
* @param $enddate (string) end date ID - if greater than zero, filter stats for the specified ending date
* @param $order_field (string) Ordering fields for SQL query.
* @author Nicola Asuni
* @return XML data
*/
function F_xml_export_user_results($user_id, $startdate, $enddate, $order_field)
{
global $l, $db;
require_once '../config/tce_config.php';
// define symbols for answers list
$qtype = array('S', 'M', 'T', 'O');
// question types
$type = array('single', 'multiple', 'text', 'ordering');
$boolean = array('false', 'true');
$xml = '';
// XML data to be returned
$xml .= '<' . '?xml version="1.0" encoding="UTF-8" ?' . '>' . K_NEWLINE;
$xml .= '<tcexamuserresults version="' . K_TCEXAM_VERSION . '">' . K_NEWLINE;
$xml .= K_TAB . '<header';
$xml .= ' lang="' . K_USER_LANG . '"';
$xml .= ' date="' . date(K_TIMESTAMP_FORMAT) . '">' . K_NEWLINE;
$xml .= K_TAB . K_TAB . '<user_id>' . $user_id . '</user_id>' . K_NEWLINE;
$sql = 'SELECT user_name, user_lastname, user_firstname FROM ' . K_TABLE_USERS . ' WHERE user_id=' . $user_id . '';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
$xml .= K_TAB . K_TAB . '<user_name>' . $m['user_name'] . '</user_name>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . '<user_lastname>' . $m['user_lastname'] . '</user_lastname>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . '<user_firstname>' . $m['user_firstname'] . '</user_firstname>' . K_NEWLINE;
}
} else {
F_display_db_error();
}
$xml .= K_TAB . K_TAB . '<date_from>' . $startdate . '</date_from>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . '<date_to>' . $enddate . '</date_to>' . K_NEWLINE;
$xml .= K_TAB . '</header>' . K_NEWLINE;
$xml .= K_TAB . '<body>' . K_NEWLINE;
$statsdata = array();
$statsdata['score'] = array();
$statsdata['right'] = array();
$statsdata['wrong'] = array();
$statsdata['unanswered'] = array();
$statsdata['undisplayed'] = array();
$statsdata['unrated'] = array();
$sql = 'SELECT
testuser_id,
test_id,
test_name,
testuser_creation_time,
testuser_status,
SUM(testlog_score) AS total_score,
MAX(testlog_change_time) AS testuser_end_time
FROM ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS . '
WHERE testuser_status>0
AND testuser_creation_time>=\'' . F_escape_sql($db, $startdate) . '\'
AND testuser_creation_time<=\'' . F_escape_sql($db, $enddate) . '\'
AND testuser_user_id=' . $user_id . '
AND testlog_testuser_id=testuser_id
AND testuser_test_id=test_id';
if ($_SESSION['session_user_level'] < K_AUTH_ADMINISTRATOR) {
$sql .= ' AND test_user_id IN (' . F_getAuthorizedUsers($_SESSION['session_user_id']) . ')';
}
$sql .= ' GROUP BY testuser_id, test_id, test_name, testuser_creation_time, testuser_status ORDER BY ' . F_escape_sql($db, $order_field) . '';
if ($r = F_db_query($sql, $db)) {
$passed = 0;
while ($m = F_db_fetch_array($r)) {
$testuser_id = $m['testuser_id'];
$usrtestdata = F_getUserTestStat($m['test_id'], $user_id);
$halfscore = $usrtestdata['max_score'] / 2;
$xml .= K_TAB . K_TAB . '<test id=\'' . $m['test_id'] . '\'>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<start_time>' . $m['testuser_creation_time'] . '</start_time>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<end_time>' . $m['testuser_end_time'] . '</end_time>' . K_NEWLINE;
$time_diff = strtotime($m['testuser_end_time']) - strtotime($m['testuser_creation_time']);
//sec
$time_diff = gmdate('H:i:s', $time_diff);
$xml .= K_TAB . K_TAB . K_TAB . '<time>' . $time_diff . '</time>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<name>' . F_text_to_xml($m['test_name']) . '</name>' . K_NEWLINE;
if ($usrtestdata['score_threshold'] > 0) {
if ($usrtestdata['score'] >= $usrtestdata['score_threshold']) {
$xml .= K_TAB . K_TAB . K_TAB . '<passed>true</passed>' . K_NEWLINE;
$passed++;
} else {
$xml .= K_TAB . K_TAB . K_TAB . '<passed>false</passed>' . K_NEWLINE;
}
} elseif ($usrtestdata['score'] > $halfscore) {
$passed++;
}
$xml .= K_TAB . K_TAB . K_TAB . '<score>' . round($m['total_score'], 3) . '</score>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<score_percent>' . round(100 * $usrtestdata['score'] / $usrtestdata['max_score']) . '</score_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<right>' . $usrtestdata['right'] . '</right>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<right_percent>' . round(100 * $usrtestdata['right'] / $usrtestdata['all']) . '</right_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<wrong>' . $usrtestdata['wrong'] . '</wrong>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<wrong_percent>' . round(100 * $usrtestdata['wrong'] / $usrtestdata['all']) . '</wrong_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<unanswered>' . $usrtestdata['unanswered'] . '</unanswered>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<unanswered_percent>' . round(100 * $usrtestdata['unanswered'] / $usrtestdata['all']) . '</unanswered_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<undisplayed>' . $usrtestdata['undisplayed'] . '</undisplayed>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<undisplayed_percent>' . round(100 * $usrtestdata['undisplayed'] / $usrtestdata['all']) . '</undisplayed_percent>' . K_NEWLINE;
if ($m['testuser_status'] == 4) {
$status = $l['w_locked'];
} else {
$status = $l['w_unlocked'];
}
$xml .= K_TAB . K_TAB . K_TAB . '<status>' . $status . '</status>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<comment>' . F_text_to_xml($usrtestdata['comment']) . '</comment>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . '</test>' . K_NEWLINE;
// collects data for descriptive statistics
$statsdata['score'][] = $m['total_score'] / $usrtestdata['max_score'];
$statsdata['right'][] = $usrtestdata['right'] / $usrtestdata['all'];
$statsdata['wrong'][] = $usrtestdata['wrong'] / $usrtestdata['all'];
$statsdata['unanswered'][] = $usrtestdata['unanswered'] / $usrtestdata['all'];
$statsdata['undisplayed'][] = $usrtestdata['undisplayed'] / $usrtestdata['all'];
$statsdata['unrated'][] = $usrtestdata['unrated'] / $usrtestdata['all'];
}
} else {
F_display_db_error();
}
// calculate statistics
$stats = F_getArrayStatistics($statsdata);
$excludestat = array('sum', 'variance');
$calcpercent = array('mean', 'median', 'mode', 'minimum', 'maximum', 'range', 'standard_deviation');
$xml .= K_TAB . K_TAB . '<teststatistics>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<passed>' . $passed . '</passed>' . K_NEWLINE;
$passed_perc = 0;
if ($itemcount > 0) {
$passed_perc = $passed / $stats['number']['score'];
}
$xml .= K_TAB . K_TAB . K_TAB . '<passed_percent>' . round(100 * $passed_perc) . '</passed_percent>' . K_NEWLINE;
foreach ($stats as $row => $columns) {
if (!in_array($row, $excludestat)) {
$xml .= K_TAB . K_TAB . K_TAB . '<' . $row . '>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<score>' . round($columns['score'], 3) . '</score>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<right>' . round($columns['right'], 3) . '</right>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<wrong>' . round($columns['wrong'], 3) . '</wrong>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<unanswered>' . round($columns['unanswered'], 3) . '</unanswered>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<undisplayed>' . round($columns['undisplayed'], 3) . '</undisplayed>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<unrated>' . round($columns['unrated'], 3) . '</unrated>' . K_NEWLINE;
if (in_array($row, $calcpercent)) {
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<score_percent>' . round(100 * ($columns['score'] / $usrtestdata['max_score'])) . '</score_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<right_percent>' . round(100 * ($columns['right'] / $usrtestdata['all'])) . '</right_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<wrong_percent>' . round(100 * ($columns['wrong'] / $usrtestdata['all'])) . '</wrong_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<unanswered_percent>' . round(100 * ($columns['unanswered'] / $usrtestdata['all'])) . '</unanswered_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<undisplayed_percent>' . round(100 * ($columns['undisplayed'] / $usrtestdata['all'])) . '</undisplayed_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<unrated_percent>' . round(100 * ($columns['unrated'] / $usrtestdata['all'])) . '</unrated_percent>' . K_NEWLINE;
}
$xml .= K_TAB . K_TAB . K_TAB . '</' . $row . '>' . K_NEWLINE;
}
}
$xml .= K_TAB . K_TAB . '</teststatistics>' . K_NEWLINE;
$xml .= K_TAB . '</body>' . K_NEWLINE;
$xml .= '</tcexamuserresults>' . K_NEWLINE;
return $xml;
}
/**
* Display online users.
* @author Nicola Asuni
* @since 2001-10-18
* @param $wherequery (string) users selection query
* @param $order_field (string) order by column name
* @param $orderdir (int) oreder direction
* @param $firstrow (int) number of first row to display
* @param $rowsperpage (int) number of rows per page
* @return false in case of empty database, true otherwise
*/
function F_list_online_users($wherequery, $order_field, $orderdir, $firstrow, $rowsperpage)
{
global $l, $db;
require_once '../config/tce_config.php';
require_once '../../shared/code/tce_functions_page.php';
require_once 'tce_functions_user_select.php';
//initialize variables
$orderdir = intval($orderdir);
$firstrow = intval($firstrow);
$rowsperpage = intval($rowsperpage);
// order fields for SQL query
if (empty($order_field) or !in_array($order_field, array('cpsession_id', 'cpsession_data'))) {
$order_field = 'cpsession_expiry';
}
if ($orderdir == 0) {
$nextorderdir = 1;
$full_order_field = $order_field;
} else {
$nextorderdir = 0;
$full_order_field = $order_field . ' DESC';
}
if (!F_count_rows(K_TABLE_SESSIONS)) {
//if the table is void (no items) display message
echo '<h2>' . $l['m_databasempty'] . '</h2>';
return FALSE;
}
if (empty($wherequery)) {
$sql = 'SELECT * FROM ' . K_TABLE_SESSIONS . ' ORDER BY ' . $full_order_field . '';
} else {
$wherequery = F_escape_sql($db, $wherequery);
$sql = 'SELECT * FROM ' . K_TABLE_SESSIONS . ' ' . $wherequery . ' ORDER BY ' . $full_order_field . '';
}
if (K_DATABASE_TYPE == 'ORACLE') {
$sql = 'SELECT * FROM (' . $sql . ') WHERE rownum BETWEEN ' . $firstrow . ' AND ' . ($firstrow + $rowsperpage) . '';
} else {
$sql .= ' LIMIT ' . $rowsperpage . ' OFFSET ' . $firstrow . '';
}
echo '<div class="container">' . K_NEWLINE;
echo '<table class="userselect">' . K_NEWLINE;
echo '<tr>' . K_NEWLINE;
echo '<th>' . $l['w_user'] . '</th>' . K_NEWLINE;
echo '<th>' . $l['w_level'] . '</th>' . K_NEWLINE;
echo '<th>' . $l['w_ip'] . '</th>' . K_NEWLINE;
echo '</tr>' . K_NEWLINE;
if ($r = F_db_query($sql, $db)) {
while ($m = F_db_fetch_array($r)) {
$this_session = F_session_string_to_array($m['cpsession_data']);
echo '<tr>';
echo '<td align="left">';
$user_str = '';
if ($this_session['session_user_lastname']) {
$user_str .= urldecode($this_session['session_user_lastname']) . ', ';
}
if ($this_session['session_user_firstname']) {
$user_str .= urldecode($this_session['session_user_firstname']) . '';
}
$user_str .= ' (' . urldecode($this_session['session_user_name']) . ')';
if (F_isAuthorizedEditorForUser($this_session['session_user_id'])) {
echo '<a href="tce_edit_user.php?user_id=' . $this_session['session_user_id'] . '">' . $user_str . '</a>';
} else {
echo $user_str;
}
echo '</td>';
echo '<td>' . $this_session['session_user_level'] . '</td>';
echo '<td>' . $this_session['session_user_ip'] . '</td>';
echo '</tr>' . K_NEWLINE;
}
} else {
F_display_db_error();
}
echo '</table>' . K_NEWLINE;
// --- ------------------------------------------------------
// --- page jump
if ($rowsperpage > 0) {
$sql = 'SELECT count(*) AS total FROM ' . K_TABLE_SESSIONS . ' ' . $wherequery . '';
if (!empty($order_field)) {
$param_array = '&order_field=' . urlencode($order_field) . '';
}
if (!empty($orderdir)) {
$param_array .= '&orderdir=' . $orderdir . '';
}
$param_array .= '&submitted=1';
F_show_page_navigator($_SERVER['SCRIPT_NAME'], $sql, $firstrow, $rowsperpage, $param_array);
}
echo '<div class="pagehelp">' . $l['hp_online_users'] . '</div>' . K_NEWLINE;
echo '</div>' . K_NEWLINE;
return TRUE;
}
/**
* Create new database. Existing database will be dropped.
* Oracle databases must be created manually (create the tcexam user and set the database name equal to user name)
* @param $host (string) Database server path. It can also include a port number. e.g. "hostname:port" or a path to a local socket e.g. ":/path/to/socket" for the localhost. Note: Whenever you specify "localhost" or "localhost:port" as server, the MySQL client library will override this and try to connect to a local socket (named pipe on Windows). If you want to use TCP/IP, use "127.0.0.1" instead of "localhost". If the MySQL client library tries to connect to the wrong local socket, you should set the correct path as mysql.default_host in your PHP configuration and leave the server field blank.
* @param $dbtype (string) database type ('MYSQL' or 'POSTGREQL')
* @param $host (string) database host
* @param $port (string) database port
* @param $user (string) Name of the user that owns the server process.
* @param $password (string) Password of the user that owns the server process.
* @param $database (string) Database name.
* @param $table_prefix (string) prefix for tables
* @param $drop (boolean) if true drop existing database
* @param $create (boolean) if true creates new database
* @return database link identifier on success, FALSE otherwise.
*/
function F_create_database($dbtype, $host, $port, $user, $password, $database, $table_prefix, $drop, $create)
{
// open default connection
if ($drop or $create) {
if ($db = @F_db_connect($host, $port, $user, $password)) {
if ($dbtype == 'ORACLE') {
if ($drop) {
$table_prefix = strtoupper($table_prefix);
// DROP sequences
$sql = 'select \'DROP SEQUENCE \'||sequence_name||\'\' from user_sequences where sequence_name like \'' . $table_prefix . '%\'';
if ($r = @F_db_query($sql, $db)) {
while ($m = @F_db_fetch_array($r)) {
@F_db_query($m[0], $db);
}
}
// DROP triggers
$sql = 'select \'DROP TRIGGER \'||trigger_name||\'\' from user_triggers where trigger_name like \'' . $table_prefix . '%\'';
if ($r = @F_db_query($sql, $db)) {
while ($m = @F_db_fetch_array($r)) {
@F_db_query($m[0], $db);
}
}
// DROP tables
$sql = 'select \'DROP TABLE \'||table_name||\' CASCADE CONSTRAINTS\' from user_tables where table_name like \'' . $table_prefix . '%\'';
if ($r = @F_db_query($sql, $db)) {
while ($m = @F_db_fetch_array($r)) {
@F_db_query($m[0], $db);
}
}
} else {
echo '<span style="color:#000080">[SKIP DROP]</span> ';
}
// Note: Oracle Database automatically creates a schema when you create a user,
// so you have to create a tcexam user before calling this.
} else {
if ($drop) {
// DROP existing database (if exist)
@F_db_query('DROP DATABASE ' . $database . '', $db);
} else {
echo '<span style="color:#000080">[SKIP DROP]</span> ';
}
if ($create) {
// create database
$sql = 'CREATE DATABASE ' . $database . '';
if ($dbtype == 'MYSQL') {
$sql .= ' CHARACTER SET utf8 COLLATE utf8_unicode_ci';
} elseif ($dbtype == 'POSTGRESQL') {
$sql .= ' ENCODING=\'UNICODE\'';
}
if (!($r = @F_db_query($sql, $db))) {
return FALSE;
}
} else {
echo '<span style="color:#000080">[SKIP CREATE]</span> ';
}
}
@F_db_close($db);
} else {
return FALSE;
}
} else {
echo '<span style="color:#000080">[SKIP DROP AND CREATE]</span> ';
}
if ($db = @F_db_connect($host, $port, $user, $password, $database)) {
return $db;
} else {
return FALSE;
}
}
/**
* Returns a comma separated string of ID of the users that belong to the same groups.
* @author Nicola Asuni
* @since 2006-03-11
* @param $user_id (int) user ID
* @return string
*/
function F_getAuthorizedUsers($user_id)
{
global $l, $db;
require_once '../config/tce_config.php';
$str = '';
// string to return
$user_id = intval($user_id);
$sql = 'SELECT tb.usrgrp_user_id
FROM ' . K_TABLE_USERGROUP . ' AS ta, ' . K_TABLE_USERGROUP . ' AS tb
WHERE ta.usrgrp_group_id=tb.usrgrp_group_id
AND ta.usrgrp_user_id=' . $user_id . '';
if ($r = F_db_query($sql, $db)) {
while ($m = F_db_fetch_array($r)) {
$str .= $m[0] . ',';
}
} else {
F_display_db_error();
}
// add the user
$str .= $user_id;
return $str;
}
F_display_db_error();
}
}
echo '</span>' . K_NEWLINE;
echo '</div>' . K_NEWLINE;
echo '<div class="row">' . K_NEWLINE;
echo '<span class="label">' . K_NEWLINE;
echo '<label>' . $l['w_groups'] . '</label>' . K_NEWLINE;
echo '</span>' . K_NEWLINE;
echo '<span class="formw">' . K_NEWLINE;
$sqlg = 'SELECT *
FROM ' . K_TABLE_GROUPS . ', ' . K_TABLE_USERGROUP . '
WHERE usrgrp_group_id=group_id
AND usrgrp_user_id=' . $module_user_id . '
ORDER BY group_name';
if ($rg = F_db_query($sqlg, $db)) {
echo '<span style="font-style:italic;color#333333;font-size:small;">';
while ($mg = F_db_fetch_array($rg)) {
echo ' ยท ' . $mg['group_name'] . '';
}
echo '</span>';
} else {
F_display_db_error();
}
echo '</span>' . K_NEWLINE;
echo '</div>' . K_NEWLINE;
echo getFormRowCheckBox('module_enabled', $l['w_enabled'], $l['h_enabled'], '', 1, $module_enabled, false, '');
echo '<div class="row">' . K_NEWLINE;
// show buttons by case
if (isset($module_id) and $module_id > 0) {
echo '<span style="background-color:#999999;">';
break;
case 'clear':
// Clear form fields
$cbt_name = '';
$cab_color = 'd3d3d3';
break;
default:
break;
}
//end of switch
// --- Initialize variables
if ($formstatus) {
if ($menu_mode != 'clear') {
if (isset($cab_ids) and !empty($cab_ids)) {
$sql = 'SELECT * FROM ' . K_TABLE_CABLES . ', ' . K_TABLE_CABLE_TYPES . ' WHERE cab_cbt_id=cbt_id AND cab_a_obj_id=' . $cab_a_obj_id . ' AND cab_b_obj_id=' . $cab_b_obj_id . ' AND cab_cbt_id=' . $cab_cbt_id . ' LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
$cbt_name = $m['cbt_name'];
$cab_color = $m['cab_color'];
} else {
$cbt_name = '';
$cab_color = 'd3d3d3';
}
} else {
F_display_db_error();
}
} else {
$cbt_name = '';
$cab_color = 'd3d3d3';
}
}
/**
* Export all users to XML grouped by users' groups.
* @author Nicola Asuni
* @since 2006-03-17
* @return XML data
*/
function F_xml_export_users()
{
global $l, $db;
require_once '../config/tce_config.php';
$boolean = array('false', 'true');
$xml = '';
// XML data to be returned
$xml .= '<' . '?xml version="1.0" encoding="UTF-8" ?' . '>' . K_NEWLINE;
$xml .= '<tcexamusers 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;
// select users
$sqla = 'SELECT * FROM ' . K_TABLE_USERS . ' WHERE (user_id>1)';
if ($_SESSION['session_user_level'] < K_AUTH_ADMINISTRATOR) {
// filter for level
$sqla .= ' AND ((user_level<' . $_SESSION['session_user_level'] . ') OR (user_id=' . $_SESSION['session_user_id'] . '))';
// filter for groups
$sqla .= ' AND user_id IN (SELECT tb.usrgrp_user_id
FROM ' . K_TABLE_USERGROUP . ' AS ta, ' . K_TABLE_USERGROUP . ' AS tb
WHERE ta.usrgrp_group_id=tb.usrgrp_group_id
AND ta.usrgrp_user_id=' . intval($_SESSION['session_user_id']) . '
AND tb.usrgrp_user_id=user_id)';
}
$sqla .= ' ORDER BY user_lastname,user_firstname,user_name';
if ($ra = F_db_query($sqla, $db)) {
while ($ma = F_db_fetch_array($ra)) {
$xml .= K_TAB . K_TAB . K_TAB . '<user id="' . $ma['user_id'] . '">' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<name>';
$xml .= F_text_to_xml($ma['user_name']);
$xml .= '</name>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<password>';
// password cannot be exported because is encrypted
//$xml .= $ma['user_password'];
$xml .= '</password>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<email>';
$xml .= $ma['user_email'];
$xml .= '</email>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<regdate>';
$xml .= $ma['user_regdate'];
$xml .= '</regdate>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<ip>';
$xml .= $ma['user_ip'];
$xml .= '</ip>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<firstname>';
$xml .= F_text_to_xml($ma['user_firstname']);
$xml .= '</firstname>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<lastname>';
$xml .= F_text_to_xml($ma['user_lastname']);
$xml .= '</lastname>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<birthdate>';
$xml .= substr($ma['user_birthdate'], 0, 10);
$xml .= '</birthdate>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<birthplace>';
$xml .= F_text_to_xml($ma['user_birthplace']);
$xml .= '</birthplace>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<regnumber>';
$xml .= F_text_to_xml($ma['user_regnumber']);
$xml .= '</regnumber>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<ssn>';
$xml .= F_text_to_xml($ma['user_ssn']);
$xml .= '</ssn>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<level>';
$xml .= $ma['user_level'];
$xml .= '</level>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<verifycode>';
$xml .= $ma['user_verifycode'];
$xml .= '</verifycode>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<otpkey>';
$xml .= $ma['user_otpkey'];
$xml .= '</otpkey>' . K_NEWLINE;
// add user's groups
$sqlg = 'SELECT *
FROM ' . K_TABLE_GROUPS . ', ' . K_TABLE_USERGROUP . '
WHERE usrgrp_group_id=group_id
AND usrgrp_user_id=' . $ma['user_id'] . '
ORDER BY group_name';
if ($rg = F_db_query($sqlg, $db)) {
while ($mg = F_db_fetch_array($rg)) {
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<group id="' . $mg['group_id'] . '">';
$xml .= $mg['group_name'];
$xml .= '</group>' . K_NEWLINE;
}
} else {
F_display_db_error();
}
$xml .= K_TAB . K_TAB . K_TAB . '</user>' . K_NEWLINE;
}
} else {
F_display_db_error();
}
$xml .= K_TAB . '</body>' . K_NEWLINE;
$xml .= '</tcexamusers>' . K_NEWLINE;
return $xml;
}
/**
* Add a new answer if not exist.
* @private
*/
private function addAnswer()
{
global $l, $db;
require_once '../config/tce_config.php';
if ($this->level_data['module']['module_id'] === false) {
return;
}
if ($this->level_data['subject']['subject_id'] === false) {
return;
}
if (isset($this->level_data['answer']['answer_id']) and $this->level_data['answer']['answer_id'] > 0) {
return;
}
// 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, \'' . $this->level_data['answer']['answer_description'] . '\',1,1)>0';
} else {
$sql .= 'answer_description=\'' . $this->level_data['answer']['answer_description'] . '\'';
}
$sql .= ' AND answer_question_id=' . $this->level_data['question']['question_id'] . ' LIMIT 1';
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
// get existing subject ID
$this->level_data['answer']['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 (
' . $this->level_data['question']['question_id'] . ',
\'' . $this->level_data['answer']['answer_description'] . '\',
' . F_empty_to_null($this->level_data['answer']['answer_explanation']) . ',
\'' . $this->boolval[$this->level_data['answer']['answer_isright']] . '\',
\'' . $this->boolval[$this->level_data['answer']['answer_enabled']] . '\',
' . F_zero_to_null($this->level_data['answer']['answer_position']) . ',
' . F_empty_to_null($this->level_data['answer']['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
$this->level_data['answer']['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();
}
}
/**
* Returns a comma separated string of test IDs with test_results_to_users enabled
* @param $test_id (int) Test ID.
* @return string
*/
function F_getTestIDs($test_id, $user_id, $filter = 'test_results_to_users')
{
global $l, $db;
require_once '../config/tce_config.php';
$str = '0';
// string to return
$test_id = intval($test_id);
$user_id = intval($user_id);
$sql = 'SELECT test_id FROM ' . K_TABLE_TESTS . ' WHERE test_id IN (SELECT DISTINCT testuser_test_id FROM ' . K_TABLE_TEST_USER . ' WHERE testuser_user_id=' . intval($user_id) . ' AND testuser_status>0) AND ' . $filter . '=1';
if ($r = F_db_query($sql, $db)) {
while ($m = F_db_fetch_assoc($r)) {
$str .= ',' . $m['test_id'];
}
} else {
F_display_db_error();
}
return $str;
}
