/**
* 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;
}
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;
}
echo '<p><strong><a href="index.php" title="' . $l['h_index'] . '">' . $l['h_index'] . ' ></a></strong></p>' . K_NEWLINE;
echo '</div>' . K_NEWLINE;
require_once '../code/tce_page_footer.php';
exit;
}
}
} else {
F_display_db_error(false);
}
F_print_error('ERROR', 'USER VERIFICATION ERROR');
echo K_NEWLINE;
echo '<div class="container">' . K_NEWLINE;
echo '<strong><a href="index.php" title="' . $l['h_index'] . '">' . $l['h_index'] . ' ></a></strong>' . K_NEWLINE;
echo '</div>' . K_NEWLINE;
require_once '../code/tce_page_footer.php';
//============================================================+
// END OF FILE
//============================================================+
/**
* 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;
}
/**
* 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;
}
/**
* 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;
}
/**
* 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();
}
}
/**
* 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;
}
/**
* Export all question statistics of the selected test to XML.
* @author Nicola Asuni
* @since 2010-05-10
* @param $test_id (int) test ID
* @return XML data
*/
function F_xml_export_question_stats($test_id)
{
global $l, $db;
require_once '../config/tce_config.php';
require_once '../../shared/code/tce_authorization.php';
require_once '../code/tce_functions_auth_sql.php';
$boolean = array('false', 'true');
$type = array('single', 'multiple', 'text', 'ordering');
$xml = '';
// XML data to be returned
$xml .= '<' . '?xml version="1.0" encoding="UTF-8" ?' . '>' . K_NEWLINE;
$xml .= '<tcexamquestionstats version="' . K_TCEXAM_VERSION . '">' . K_NEWLINE;
$xml .= K_TAB . '<header';
$xml .= ' lang="' . K_USER_LANG . '"';
$xml .= ' date="' . date(K_TIMESTAMP_FORMAT) . '">' . K_NEWLINE;
$xml .= K_TAB . '</header>' . K_NEWLINE;
$xml .= K_TAB . '<body>' . K_NEWLINE;
// get test data
$testdata = F_getTestData($test_id);
// get total number of questions for the selected test
$num_questions = F_count_rows(K_TABLE_TESTS_LOGS . ', ' . K_TABLE_TEST_USER, 'WHERE testlog_testuser_id=testuser_id AND testuser_test_id=' . $test_id . '');
// output questions stats
$sqlr = 'SELECT
question_id,
COUNT(question_id) AS recurrence,
AVG(testlog_score) AS average_score,
AVG(testlog_change_time - testlog_display_time) AS average_time,
min(question_difficulty) AS question_difficulty
FROM ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_TEST_USER . ', ' . K_TABLE_QUESTIONS . '
WHERE testlog_testuser_id=testuser_id
AND testlog_question_id=question_id
AND testuser_test_id=' . $test_id . '
GROUP BY question_id
ORDER BY recurrence DESC,average_score DESC';
if ($rr = F_db_query($sqlr, $db)) {
while ($mr = F_db_fetch_array($rr)) {
$xml .= K_TAB . K_TAB . '<question>' . K_NEWLINE;
// get the question max score
$question_max_score = $testdata['test_score_right'] * $mr['question_difficulty'];
$qsttestdata = F_getQuestionTestStat($test_id, $mr['question_id']);
$xml .= K_TAB . K_TAB . K_TAB . '<id>' . $mr['question_id'] . '</id>' . K_NEWLINE;
$question_description = '';
$sqlrq = 'SELECT question_description FROM ' . K_TABLE_QUESTIONS . ' WHERE question_id=' . $mr['question_id'] . '';
if ($rrq = F_db_query($sqlrq, $db)) {
if ($mrq = F_db_fetch_array($rrq)) {
$question_description = $mrq['question_description'];
}
} else {
F_display_db_error();
}
$xml .= K_TAB . K_TAB . K_TAB . '<description>' . F_text_to_xml($question_description) . '</description>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<recurrence>' . $mr['recurrence'] . '</recurrence>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<recurrence_percent>' . F_formatXMLPercentage($mr['recurrence'] / $num_questions) . '</recurrence_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<points>' . number_format($mr['average_score'], 3, '.', '') . '</points>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<points_percent>' . F_formatXMLPercentage($mr['average_score'] / $question_max_score) . '</points_percent>' . K_NEWLINE;
if (stripos($mr['average_time'], ':') !== FALSE) {
// PostgreSQL returns formatted time, while MySQL returns the number of seconds
$mr['average_time'] = strtotime($mr['average_time']);
}
$xml .= K_TAB . K_TAB . K_TAB . '<time>' . date('i:s', $mr['average_time']) . '</time>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<correct>' . $qsttestdata['right'] . '</correct>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<correct_percent>' . F_formatXMLPercentage($qsttestdata['right'] / $qsttestdata['num']) . '</correct_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<wrong>' . $qsttestdata['wrong'] . '</wrong>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<wrong_percent>' . F_formatXMLPercentage($qsttestdata['wrong'] / $qsttestdata['num']) . '</wrong_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<unanswered>' . $qsttestdata['unanswered'] . '</unanswered>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<unanswered_percent>' . F_formatXMLPercentage($qsttestdata['unanswered'] / $qsttestdata['num']) . '</unanswered_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<undisplayed>' . $qsttestdata['undisplayed'] . '</undisplayed>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<undisplayed_percent>' . F_formatXMLPercentage($qsttestdata['undisplayed'] / $qsttestdata['num']) . '</undisplayed_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<unrated>' . $qsttestdata['unrated'] . '</unrated>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<unrated_percent>' . F_formatXMLPercentage($qsttestdata['unrated'] / $qsttestdata['num']) . '</unrated_percent>' . K_NEWLINE;
// answers statistics
$sqla = 'SELECT *
FROM ' . K_TABLE_ANSWERS . '
WHERE answer_question_id=' . $mr['question_id'] . '
ORDER BY answer_id';
if ($ra = F_db_query($sqla, $db)) {
while ($ma = F_db_fetch_array($ra)) {
$xml .= K_TAB . K_TAB . K_TAB . '<answer>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<id>' . $ma['answer_id'] . '</id>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<description>' . F_text_to_xml($ma['answer_description']) . '</description>' . K_NEWLINE;
$num_all_answers = F_count_rows(K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_ANSWERS . ', ' . K_TABLE_LOG_ANSWER . ' WHERE logansw_answer_id=answer_id AND logansw_testlog_id=testlog_id AND testlog_testuser_id=testuser_id AND testuser_test_id=' . $test_id . ' AND testlog_question_id=' . $mr['question_id'] . '');
$num_answers = F_count_rows(K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_ANSWERS . ', ' . K_TABLE_LOG_ANSWER . ' WHERE answer_id=' . $ma['answer_id'] . ' AND logansw_answer_id=answer_id AND logansw_testlog_id=testlog_id AND testlog_testuser_id=testuser_id AND testuser_test_id=' . $test_id . ' AND testlog_question_id=' . $mr['question_id'] . '');
$right_answers = F_count_rows(K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_ANSWERS . ', ' . K_TABLE_LOG_ANSWER . ' WHERE answer_id=' . $ma['answer_id'] . ' AND logansw_answer_id=answer_id AND logansw_testlog_id=testlog_id AND testlog_testuser_id=testuser_id AND testuser_test_id=' . $test_id . ' AND testlog_question_id=' . $mr['question_id'] . ' AND ((answer_isright=\'0\' AND logansw_selected=0) OR (answer_isright=\'1\' AND logansw_selected=1) OR (answer_position IS NOT NULL AND logansw_position IS NOT NULL AND answer_position=logansw_position))');
$wrong_answers = F_count_rows(K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_ANSWERS . ', ' . K_TABLE_LOG_ANSWER . ' WHERE answer_id=' . $ma['answer_id'] . ' AND logansw_answer_id=answer_id AND logansw_testlog_id=testlog_id AND testlog_testuser_id=testuser_id AND testuser_test_id=' . $test_id . ' AND testlog_question_id=' . $mr['question_id'] . ' AND ((answer_isright=\'0\' AND logansw_selected=1) OR (answer_isright=\'1\' AND logansw_selected=0) OR (answer_position IS NOT NULL AND answer_position!=logansw_position))');
$unanswered = F_count_rows(K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_ANSWERS . ', ' . K_TABLE_LOG_ANSWER . ' WHERE answer_id=' . $ma['answer_id'] . ' AND logansw_answer_id=answer_id AND logansw_testlog_id=testlog_id AND testlog_testuser_id=testuser_id AND testuser_test_id=' . $test_id . ' AND testlog_question_id=' . $mr['question_id'] . ' AND logansw_selected=-1');
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<recurrence>' . $num_answers . '</recurrence>' . K_NEWLINE;
$perc = 0;
if ($num_all_answers > 0) {
$perc = $num_answers / $num_all_answers;
}
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<recurrence_percent>' . F_formatXMLPercentage($perc) . '</recurrence_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<correct>' . $right_answers . '</correct>' . K_NEWLINE;
$perc = 0;
if ($num_answers > 0) {
$perc = $right_answers / $num_answers;
}
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<correct_percent>' . F_formatXMLPercentage($perc) . '</correct_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<wrong>' . $wrong_answers . '</wrong>' . K_NEWLINE;
$perc = 0;
if ($num_answers > 0) {
$perc = round($wrong_answers / $num_answers);
}
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<wrong_percent>' . F_formatXMLPercentage($perc) . '</wrong_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<unanswered>' . $unanswered . '</unanswered>' . K_NEWLINE;
$perc = 0;
if ($num_answers > 0) {
$perc = round($unanswered / $num_answers);
}
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<unanswered_percent>' . F_formatXMLPercentage($perc) . '</unanswered_percent>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '</answer>' . K_NEWLINE;
}
} else {
F_display_db_error();
}
$xml .= K_TAB . K_TAB . '</question>' . K_NEWLINE;
}
} else {
F_display_db_error();
}
$xml .= K_TAB . '</body>' . K_NEWLINE;
$xml .= '</tcexamquestionstats>' . K_NEWLINE;
return $xml;
}
/**
* 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;
}
/**
* Get a comma separated list of valid SSL certificates IDs for the selected test.
* @param $test_id (int) ID of the selected test
* @return string containing a comma separated list SSL certificates IDs.
*/
function F_getTestSSLCerts($test_id)
{
require_once '../config/tce_config.php';
global $db, $l;
$test_id = intval($test_id);
$ids = '0';
// select SSL certificates in this test
$sql = 'SELECT tstssl_ssl_id FROM ' . K_TABLE_TEST_SSLCERTS . ' WHERE tstssl_test_id=' . $test_id . ' ORDER BY tstssl_ssl_id';
if ($r = F_db_query($sql, $db)) {
while ($m = F_db_fetch_assoc($r)) {
$ids .= ',' . $m['tstssl_ssl_id'];
}
} else {
F_display_db_error();
}
return $ids;
}
/**
* print test details for the selected user
* @param $data (array) Testuser data array.
* @param $onlytext (boolean) If true print only text questions.
*/
public function printUserTestDetails($data, $onlytext = false)
{
require_once '../config/tce_config.php';
require_once '../../shared/code/tce_functions_test_stats.php';
require_once '../../shared/code/tce_functions_tcecode.php';
global $db, $l;
$testuser_id = intval($data['id']);
$qtype = array('S', 'M', 'T', 'O');
// question types
$num_column = 7;
$tce_data_cell_width = round($this->tce_page_width / $num_column, 2);
$tce_data_cell_width_third = round($tce_data_cell_width / 3, 2);
$tce_data_cell_width_half = round($tce_data_cell_width / 2, 2);
$numberfont = 'courier';
// display user questions
$sql = 'SELECT *
FROM ' . K_TABLE_QUESTIONS . ', ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_SUBJECTS . ', ' . K_TABLE_MODULES . '
WHERE question_id=testlog_question_id
AND testlog_testuser_id=' . $testuser_id . '
AND question_subject_id=subject_id
AND subject_module_id=module_id';
if ($onlytext) {
// display only TEXT questions
$sql .= ' AND question_type=3';
}
$sql .= ' ORDER BY testlog_id';
if ($r = F_db_query($sql, $db)) {
$this->SetFont(PDF_FONT_NAME_DATA, 'B', PDF_FONT_SIZE_DATA);
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, '#', 1, 0, 'C', 1);
$this->Cell($tce_data_cell_width, $this->tce_data_cell_height, $l['w_score'], 1, 0, 'C', 1);
$this->Cell($tce_data_cell_width, $this->tce_data_cell_height, $l['w_ip'], 1, 0, 'C', 1);
$this->Cell($tce_data_cell_width + $tce_data_cell_width_third, $this->tce_data_cell_height, $l['w_start'] . ' [' . $l['w_time_hhmmss'] . ']', 1, 0, 'C', 1);
$this->Cell($tce_data_cell_width + $tce_data_cell_width_third, $this->tce_data_cell_height, $l['w_end'] . ' [' . $l['w_time_hhmmss'] . ']', 1, 0, 'C', 1);
$this->Cell($tce_data_cell_width, $this->tce_data_cell_height, $l['w_time'] . ' [' . $l['w_time_mmss'] . ']', 1, 0, 'C', 1);
$this->Cell($tce_data_cell_width, $this->tce_data_cell_height, $l['w_reaction'] . ' [sec]', 1, 1, 'C', 1);
$this->Ln($this->tce_data_cell_height);
// print table rows
$this->SetFont(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA);
$itemcount = 1;
while ($m = F_db_fetch_array($r)) {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, $itemcount . ' ' . $qtype[$m['question_type'] - 1], 1, 0, 'R', 0);
$this->Cell($tce_data_cell_width, $this->tce_data_cell_height, $m['testlog_score'], 1, 0, 'C', 0);
$this->Cell($tce_data_cell_width, $this->tce_data_cell_height, getIpAsString($m['testlog_user_ip']), 1, 0, 'C', 0);
if (isset($m['testlog_display_time']) and strlen($m['testlog_display_time']) > 0) {
$display_time = substr($m['testlog_display_time'], 11, 8);
} else {
$display_time = '--:--:--';
}
if (isset($m['testlog_change_time']) and strlen($m['testlog_change_time']) > 0) {
$change_time = substr($m['testlog_change_time'], 11, 8);
} else {
$change_time = '--:--:--';
}
if (isset($m['testlog_display_time']) and isset($m['testlog_change_time'])) {
$diff_time = date('i:s', strtotime($m['testlog_change_time']) - strtotime($m['testlog_display_time']));
} else {
$diff_time = '--:--';
}
if (isset($m['testlog_reaction_time']) and strlen($m['testlog_reaction_time']) > 0) {
$reaction_time = $m['testlog_reaction_time'] / 1000;
} else {
$reaction_time = '';
}
$this->Cell($tce_data_cell_width + $tce_data_cell_width_third, $this->tce_data_cell_height, $display_time, 1, 0, 'C', 0);
$this->Cell($tce_data_cell_width + $tce_data_cell_width_third, $this->tce_data_cell_height, $change_time, 1, 0, 'C', 0);
$this->Cell($tce_data_cell_width, $this->tce_data_cell_height, $diff_time, 1, 0, 'C', 0);
$this->Cell($tce_data_cell_width, $this->tce_data_cell_height, $reaction_time, 1, 1, 'C', 0);
$this->writeHTMLCell(0, $this->tce_data_cell_height, PDF_MARGIN_LEFT + $tce_data_cell_width_third, $this->GetY(), F_decode_tcecode($m['question_description']), 1, 1);
if (K_ENABLE_QUESTION_EXPLANATION and !empty($m['question_explanation'])) {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, '', 0, 0, 'C', 0);
$this->SetFont('', 'BIU');
$this->Cell(0, $this->tce_data_cell_height, $l['w_explanation'], 'LTR', 1, '', 0, '', 0);
$this->SetFont('', '');
$this->writeHTMLCell(0, $this->tce_data_cell_height, PDF_MARGIN_LEFT + $tce_data_cell_width_third, $this->GetY(), F_decode_tcecode($m['question_explanation']), 'LRB', 1, '', '');
}
if ($m['question_type'] == 3) {
// free-text question - print user text answer
$this->writeHTMLCell(0, $this->tce_data_cell_height, PDF_MARGIN_LEFT + 2 * $tce_data_cell_width_third, $this->GetY(), F_decode_tcecode($m['testlog_answer_text']), 1, 1);
} else {
// display each answer option
$sqla = 'SELECT * FROM ' . K_TABLE_LOG_ANSWER . ', ' . K_TABLE_ANSWERS . ' WHERE logansw_answer_id=answer_id AND logansw_testlog_id=' . $m['testlog_id'] . ' ORDER BY logansw_order';
if ($ra = F_db_query($sqla, $db)) {
$idx = 0;
// count items
while ($ma = F_db_fetch_array($ra)) {
$posfill = 0;
$idx++;
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, '', 0, 0, 'C', 0);
if ($m['question_type'] == 4) {
if ($ma['logansw_position'] > 0) {
if ($ma['logansw_position'] == $ma['answer_position']) {
$posfill = 1;
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, $ma['logansw_position'], 1, 0, 'C', 1);
} else {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, $ma['logansw_position'], 1, 0, 'C', 0);
}
} else {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, ' ', 1, 0, 'C', 0);
}
} elseif ($ma['logansw_selected'] > 0) {
// selected
if (F_getBoolean($ma['answer_isright'])) {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, '+', 1, 0, 'C', 1);
} else {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, '-', 1, 0, 'C', 1);
}
} elseif ($m['question_type'] == 1) {
// MCSA
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, ' ', 1, 0, 'C', 0);
} else {
if ($ma['logansw_selected'] == 0) {
// unselected
if (F_getBoolean($ma['answer_isright'])) {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, '-', 1, 0, 'C', 0);
} else {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, '+', 1, 0, 'C', 0);
}
} else {
// no answer
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, ' ', 1, 0, 'C', 0);
}
}
if ($m['question_type'] == 4) {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, $ma['answer_position'], 1, 0, 'C', $posfill);
} elseif (F_getBoolean($ma['answer_isright'])) {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, $idx, 1, 0, 'C', 1);
} else {
$this->Cell($tce_data_cell_width_third, $this->tce_data_cell_height, $idx, 1, 0, 'C', 0);
}
$this->writeHTMLCell(0, $this->tce_data_cell_height, PDF_MARGIN_LEFT + $tce_data_cell_width, $this->GetY(), F_decode_tcecode($ma['answer_description']), 'LRTB', 1);
if (K_ENABLE_ANSWER_EXPLANATION and !empty($ma['answer_explanation'])) {
$this->Cell(3 * $tce_data_cell_width_third, $this->tce_data_cell_height, '', 0, 0, 'C', 0);
$this->SetFont('', 'BIU');
$this->Cell(0, $this->tce_data_cell_height, $l['w_explanation'], 'LTR', 1, '', 0, '', 0);
$this->SetFont('', '');
$this->writeHTMLCell(0, $this->tce_data_cell_height, PDF_MARGIN_LEFT + 3 * $tce_data_cell_width_third, $this->GetY(), F_decode_tcecode($ma['answer_explanation']), 'LRB', 1, '', '');
}
}
} else {
F_display_db_error();
}
}
// end multiple answers
if (strlen($m['testlog_comment']) > 0) {
// teacher / supervisor comment
$this->SetTextColor(255, 0, 0);
$this->writeHTMLCell(0, $this->tce_data_cell_height, PDF_MARGIN_LEFT + 2 * $tce_data_cell_width_third, $this->GetY(), F_decode_tcecode($m['testlog_comment']), 'LRTB', 1);
$this->SetTextColor(0, 0, 0);
}
$this->Ln($this->tce_data_cell_height);
$itemcount++;
}
} else {
F_display_db_error();
}
$stats = F_getTestStat($data['test']['test_id'], 0, $data['user_id'], 0, 0, $data['id']);
$this->printQuestionStats($stats['qstats'], 1);
}
/**
* Display user selection XHTML table (popup mode).
* @author Nicola Asuni
* @since 2012-04-14
* @param $order_field (string) Order by column name.
* @param $orderdir (int) Order direction.
* @param $firstrow (int) Number of first row to display.
* @param $rowsperpage (int) Number of rows per page.
* @param $andwhere (string) Additional SQL WHERE query conditions.
* @param $searchterms (string) Search terms.
* @param string $cid ID of the calling form field.
* @return false in case of empty database, true otherwise
*/
function F_show_select_test_popup($order_field, $orderdir, $firstrow, $rowsperpage, $andwhere = '', $searchterms = '', $cid = 0)
{
global $l, $db;
require_once '../config/tce_config.php';
require_once '../../shared/code/tce_functions_page.php';
require_once '../../shared/code/tce_functions_form.php';
$filter = 'cid=' . $cid;
if ($l['a_meta_dir'] == 'rtl') {
$txtalign = 'right';
$numalign = 'left';
} else {
$txtalign = 'left';
$numalign = 'right';
}
$order_field = F_escape_sql($db, $order_field);
$orderdir = intval($orderdir);
$firstrow = intval($firstrow);
$rowsperpage = intval($rowsperpage);
if (empty($order_field) or !in_array($order_field, array('test_name', 'test_description', 'test_begin_time', 'test_end_time', 'test_duration_time', 'test_ip_range', 'test_results_to_users', 'test_report_to_users', 'test_score_right', 'test_score_wrong', 'test_score_unanswered', 'test_max_score', 'test_user_id', 'test_score_threshold', 'test_random_questions_select', 'test_random_questions_order', 'test_questions_order_mode', 'test_random_answers_select', 'test_random_answers_order', 'test_answers_order_mode', 'test_comment_enabled', 'test_menu_enabled', 'test_noanswer_enabled', 'test_mcma_radio', 'test_repeatable', 'test_mcma_partial_score', 'test_logout_on_timeout'))) {
$order_field = 'test_begin_time DESC,test_name';
}
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_TESTS)) {
// if the table is void (no items) display message
F_print_error('MESSAGE', $l['m_databasempty']);
return FALSE;
}
$wherequery = '';
if (empty($wherequery)) {
$wherequery = ' WHERE';
} else {
$wherequery .= ' AND';
}
$wherequery .= ' (test_id>0)';
if ($_SESSION['session_user_level'] < K_AUTH_ADMINISTRATOR) {
$wherequery .= ' AND test_user_id IN (' . F_getAuthorizedUsers($_SESSION['session_user_id']) . ')';
}
if (!empty($andwhere)) {
$wherequery .= ' AND (' . $andwhere . ')';
}
$sql = 'SELECT * FROM ' . K_TABLE_TESTS . $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 . '';
}
if ($r = F_db_query($sql, $db)) {
if ($m = F_db_fetch_array($r)) {
// -- Table structure with links:
echo '<div class="container">';
echo '<table class="userselect" style="font-size:80%;">' . K_NEWLINE;
// table header
echo '<tr>' . K_NEWLINE;
if (strlen($searchterms) > 0) {
$filter .= '&searchterms=' . urlencode($searchterms);
}
echo F_select_table_header_element('test_begin_time', $nextorderdir, $l['w_time_begin'] . ' ' . $l['w_datetime_format'], $l['w_time_begin'], $order_field, $filter);
echo F_select_table_header_element('test_end_time', $nextorderdir, $l['w_time_end'] . ' ' . $l['w_datetime_format'], $l['w_time_end'], $order_field, $filter);
echo F_select_table_header_element('test_name', $nextorderdir, $l['h_test_name'], $l['w_name'], $order_field, $filter);
echo F_select_table_header_element('test_description', $nextorderdir, $l['h_test_description'], $l['w_description'], $order_field, $filter);
echo '</tr>' . K_NEWLINE;
$itemcount = 0;
do {
$itemcount++;
// on click the user ID will be returned on the calling form field
$jsaction = 'javascript:window.opener.document.getElementById(\'' . $cid . '\').value=' . $m['test_id'] . ';';
$jsaction .= 'window.opener.document.getElementById(\'' . $cid . '\').onchange();';
$jsaction .= 'window.close();';
echo '<tr>' . K_NEWLINE;
echo '<td style="text-align:' . $txtalign . ';"> ' . htmlspecialchars($m['test_begin_time'], ENT_NOQUOTES, $l['a_meta_charset']) . '</td>' . K_NEWLINE;
echo '<td style="text-align:' . $txtalign . ';"> ' . htmlspecialchars($m['test_end_time'], ENT_NOQUOTES, $l['a_meta_charset']) . '</td>' . K_NEWLINE;
echo '<td style="text-align:' . $txtalign . ';"> <a href="#" onclick="' . $jsaction . '" title="[' . $l['w_select'] . ']">' . htmlspecialchars($m['test_name'], ENT_NOQUOTES, $l['a_meta_charset']) . '</a></td>' . K_NEWLINE;
echo '<td style="text-align:' . $txtalign . ';"> ' . htmlspecialchars($m['test_description'], ENT_NOQUOTES, $l['a_meta_charset']) . '</td>' . K_NEWLINE;
echo '</tr>' . K_NEWLINE;
} while ($m = F_db_fetch_array($r));
echo '</table>' . K_NEWLINE;
echo '<input type="hidden" name="order_field" id="order_field" value="' . $order_field . '" />' . K_NEWLINE;
echo '<input type="hidden" name="orderdir" id="orderdir" value="' . $orderdir . '" />' . K_NEWLINE;
echo '<input type="hidden" name="firstrow" id="firstrow" value="' . $firstrow . '" />' . K_NEWLINE;
echo '<input type="hidden" name="rowsperpage" id="rowsperpage" value="' . $rowsperpage . '" />' . K_NEWLINE;
echo '<div class="row"><hr /></div>' . K_NEWLINE;
// ---------------------------------------------------------------
// -- page jumper (menu for successive pages)
if ($rowsperpage > 0) {
$sql = 'SELECT count(*) AS total FROM ' . K_TABLE_TESTS . '' . $wherequery . '';
if (!empty($order_field)) {
$param_array = '&order_field=' . urlencode($order_field) . '';
}
if (!empty($orderdir)) {
$param_array .= '&orderdir=' . $orderdir . '';
}
if (!empty($searchterms)) {
$param_array .= '&searchterms=' . urlencode($searchterms) . '';
}
$param_array .= '&submitted=1';
F_show_page_navigator($_SERVER['SCRIPT_NAME'], $sql, $firstrow, $rowsperpage, $param_array);
}
echo '</div>' . K_NEWLINE;
} else {
F_print_error('MESSAGE', $l['m_search_void']);
}
} else {
F_display_db_error();
}
return TRUE;
}
/**
* Export all questions of the selected subject to XML.
* @author Nicola Asuni
* @since 2006-03-06
* @param $module_id (int) module ID
* @param $subject_id (int) topic ID
* @param $expmode (int) export mode: 1 = selected topic; 2 = selected module; 3 = all modules.
* @return XML data
*/
function F_xml_export_questions($module_id, $subject_id, $expmode)
{
global $l, $db;
require_once '../config/tce_config.php';
require_once '../../shared/code/tce_authorization.php';
require_once '../../shared/code/tce_functions_auth_sql.php';
$module_id = intval($module_id);
$subject_id = intval($subject_id);
$expmode = intval($expmode);
$boolean = array('false', 'true');
$type = array('single', 'multiple', 'text', 'ordering');
$xml = '';
// XML data to be returned
$xml .= '<' . '?xml version="1.0" encoding="UTF-8" ?' . '>' . K_NEWLINE;
$xml .= '<tcexamquestions 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;
// ---- module
$andmodwhere = '';
if ($expmode < 3) {
$andmodwhere = 'module_id=' . $module_id . '';
}
$sqlm = F_select_modules_sql($andmodwhere);
if ($rm = F_db_query($sqlm, $db)) {
while ($mm = F_db_fetch_array($rm)) {
$xml .= K_TAB . K_TAB . '<module>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<name>';
$xml .= F_text_to_xml($mm['module_name']);
$xml .= '</name>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . '<enabled>';
$xml .= $boolean[intval(F_getBoolean($mm['module_enabled']))];
$xml .= '</enabled>' . K_NEWLINE;
// ---- topic
$where_sqls = 'subject_module_id=' . $mm['module_id'] . '';
if ($expmode < 2) {
$where_sqls .= ' AND subject_id=' . $subject_id . '';
}
$sqls = F_select_subjects_sql($where_sqls);
if ($rs = F_db_query($sqls, $db)) {
while ($ms = F_db_fetch_array($rs)) {
$xml .= K_TAB . K_TAB . K_TAB . '<subject>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<name>';
$xml .= F_text_to_xml($ms['subject_name']);
$xml .= '</name>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<description>';
$xml .= F_text_to_xml($ms['subject_description']);
$xml .= '</description>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<enabled>';
$xml .= $boolean[intval(F_getBoolean($ms['subject_enabled']))];
$xml .= '</enabled>' . K_NEWLINE;
// ---- questions
$sql = 'SELECT *
FROM ' . K_TABLE_QUESTIONS . '
WHERE question_subject_id=' . $ms['subject_id'] . '
ORDER BY question_enabled DESC, question_position, question_description';
if ($r = F_db_query($sql, $db)) {
while ($m = F_db_fetch_array($r)) {
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '<question>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . '<enabled>';
$xml .= $boolean[intval(F_getBoolean($m['question_enabled']))];
$xml .= '</enabled>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . '<type>';
$xml .= $type[$m['question_type'] - 1];
$xml .= '</type>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . '<difficulty>';
$xml .= $m['question_difficulty'];
$xml .= '</difficulty>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . '<position>';
$xml .= $m['question_position'];
$xml .= '</position>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . '<timer>';
$xml .= $m['question_timer'];
$xml .= '</timer>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . '<fullscreen>';
$xml .= $boolean[intval(F_getBoolean($m['question_fullscreen']))];
$xml .= '</fullscreen>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . '<inline_answers>';
$xml .= $boolean[intval(F_getBoolean($m['question_inline_answers']))];
$xml .= '</inline_answers>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . '<auto_next>';
$xml .= $boolean[intval(F_getBoolean($m['question_auto_next']))];
$xml .= '</auto_next>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . '<description>';
$xml .= F_text_to_xml($m['question_description']);
$xml .= '</description>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . '<explanation>';
$xml .= F_text_to_xml($m['question_explanation']);
$xml .= '</explanation>' . K_NEWLINE;
// display alternative answers
$sqla = 'SELECT *
FROM ' . K_TABLE_ANSWERS . '
WHERE answer_question_id=\'' . $m['question_id'] . '\'
ORDER BY answer_position,answer_isright DESC';
if ($ra = F_db_query($sqla, $db)) {
while ($ma = F_db_fetch_array($ra)) {
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . '<answer>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . '<enabled>';
$xml .= $boolean[intval(F_getBoolean($ma['answer_enabled']))];
$xml .= '</enabled>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . '<isright>';
$xml .= $boolean[intval(F_getBoolean($ma['answer_isright']))];
$xml .= '</isright>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . '<position>';
$xml .= $ma['answer_position'];
$xml .= '</position>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . '<keyboard_key>';
$xml .= $ma['answer_keyboard_key'];
$xml .= '</keyboard_key>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . '<description>';
$xml .= F_text_to_xml($ma['answer_description']);
$xml .= '</description>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . '<explanation>';
$xml .= F_text_to_xml($ma['answer_explanation']);
$xml .= '</explanation>' . K_NEWLINE;
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . '</answer>' . K_NEWLINE;
}
} else {
F_display_db_error();
}
$xml .= K_TAB . K_TAB . K_TAB . K_TAB . '</question>' . K_NEWLINE;
}
// end while for questions
} else {
F_display_db_error();
}
$xml .= K_TAB . K_TAB . K_TAB . '</subject>' . K_NEWLINE;
}
// end while for topics
} else {
F_display_db_error();
}
$xml .= K_TAB . K_TAB . '</module>' . K_NEWLINE;
}
// end while for module
} else {
F_display_db_error();
}
$xml .= K_TAB . '</body>' . K_NEWLINE;
$xml .= '</tcexamquestions>' . K_NEWLINE;
return $xml;
}
/**
* Display a list of selected questions.
* @author Nicola Asuni
* @since 2005-07-06
* @param $wherequery (string) question selection query
* @param $subject_module_id (string) module ID
* @param $subject_id (string) topic ID
* @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
* @param $hide_answers (boolean) if true hide answers
* @return false in case of empty database, true otherwise
*/
function F_show_select_questions($wherequery, $subject_module_id, $subject_id, $order_field, $orderdir, $firstrow, $rowsperpage, $hide_answers = false)
{
global $l, $db;
require_once '../config/tce_config.php';
require_once '../../shared/code/tce_functions_page.php';
$subject_module_id = intval($subject_module_id);
$subject_id = intval($subject_id);
$orderdir = intval($orderdir);
$firstrow = intval($firstrow);
$rowsperpage = intval($rowsperpage);
if (empty($order_field) or !in_array($order_field, array('question_id', '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', 'question_enabled DESC, question_position, CAST(question_description as varchar2(100))', 'question_enabled DESC, question_position, question_description'))) {
$order_field = 'question_description';
}
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_QUESTIONS)) {
//if the table is void (no items) display message
F_print_error('MESSAGE', $l['m_databasempty']);
return FALSE;
}
if (empty($wherequery)) {
$wherequery = 'WHERE question_subject_id=' . $subject_id . '';
} else {
$wherequery = F_escape_sql($db, $wherequery);
$wherequery .= ' AND question_subject_id=' . $subject_id . '';
}
$sql = 'SELECT *
FROM ' . K_TABLE_QUESTIONS . '
' . $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 . '';
}
if ($r = F_db_query($sql, $db)) {
$questlist = '';
$itemcount = $firstrow;
while ($m = F_db_fetch_array($r)) {
$itemcount++;
$questlist .= '<li>' . K_NEWLINE;
$questlist .= '<strong>' . $itemcount . '.</strong> ';
$questlist .= '<input type="checkbox" name="questionid' . $itemcount . '" id="questionid' . $itemcount . '" value="' . $m['question_id'] . '" title="' . $l['w_select'] . '"';
if (isset($_REQUEST['checkall']) and $_REQUEST['checkall'] == 1) {
$questlist .= ' checked="checked"';
}
$questlist .= ' />';
// display question description
if (F_getBoolean($m['question_enabled'])) {
$questlist .= '<acronym class="onbox" title="' . $l['w_enabled'] . '">+</acronym>';
} else {
$questlist .= '<acronym class="offbox" title="' . $l['w_disabled'] . '">-</acronym>';
}
switch ($m['question_type']) {
case 1:
$questlist .= ' <acronym class="offbox" title="' . $l['w_single_answer'] . '">S</acronym>';
break;
case 2:
$questlist .= ' <acronym class="offbox" title="' . $l['w_multiple_answers'] . '">M</acronym>';
break;
case 3:
$questlist .= ' <acronym class="offbox" title="' . $l['w_free_answer'] . '">T</acronym>';
break;
case 4:
$questlist .= ' <acronym class="offbox" title="' . $l['w_ordering_answer'] . '">O</acronym>';
break;
}
$questlist .= ' <acronym class="offbox" title="' . $l['h_question_difficulty'] . '">' . $m['question_difficulty'] . '</acronym>';
if ($m['question_position'] > 0) {
$questlist .= ' <acronym class="onbox" title="' . $l['h_position'] . '">' . intval($m['question_position']) . '</acronym>';
} else {
$questlist .= ' <acronym class="offbox" title="' . $l['h_position'] . '"> </acronym>';
}
if (F_getBoolean($m['question_fullscreen'])) {
$questlist .= ' <acronym class="onbox" title="' . $l['w_fullscreen'] . ': ' . $l['w_enabled'] . '">F</acronym>';
} else {
$questlist .= ' <acronym class="offbox" title="' . $l['w_fullscreen'] . ': ' . $l['w_disabled'] . '"> </acronym>';
}
if (F_getBoolean($m['question_inline_answers'])) {
$questlist .= ' <acronym class="onbox" title="' . $l['w_inline_answers'] . ': ' . $l['w_enabled'] . '">I</acronym>';
} else {
$questlist .= ' <acronym class="offbox" title="' . $l['w_inline_answers'] . ': ' . $l['w_disabled'] . '"> </acronym>';
}
if (F_getBoolean($m['question_auto_next'])) {
$questlist .= ' <acronym class="onbox" title="' . $l['w_auto_next'] . ': ' . $l['w_enabled'] . '">A</acronym>';
} else {
$questlist .= ' <acronym class="offbox" title="' . $l['w_auto_next'] . ': ' . $l['w_disabled'] . '"> </acronym>';
}
if ($m['question_timer'] > 0) {
$questlist .= ' <acronym class="onbox" title="' . $l['h_question_timer'] . '">' . intval($m['question_timer']) . '</acronym>';
} else {
$questlist .= ' <acronym class="offbox" title="' . $l['h_question_timer'] . '"> </acronym>';
}
$questlist .= ' <a href="tce_edit_question.php?subject_module_id=' . $subject_module_id . '&question_subject_id=' . $subject_id . '&question_id=' . $m['question_id'] . '" title="' . $l['t_questions_editor'] . ' [ID = ' . $m['question_id'] . ']" class="xmlbutton">' . $l['w_edit'] . '</a>';
$questlist .= '<br /><br />' . K_NEWLINE;
$questlist .= '<div class="paddingleft">' . F_decode_tcecode($m['question_description']) . '</div>' . K_NEWLINE;
if (K_ENABLE_QUESTION_EXPLANATION and !empty($m['question_explanation'])) {
$questlist .= '<div class="paddingleft"><br /><span class="explanation">' . $l['w_explanation'] . ':</span><br />' . F_decode_tcecode($m['question_explanation']) . '</div>' . K_NEWLINE;
}
if (!$hide_answers) {
// display alternative answers
$sqla = 'SELECT *
FROM ' . K_TABLE_ANSWERS . '
WHERE answer_question_id=\'' . $m['question_id'] . '\'
ORDER BY answer_enabled DESC,answer_position,answer_isright DESC';
if ($ra = F_db_query($sqla, $db)) {
$answlist = '';
while ($ma = F_db_fetch_array($ra)) {
$answlist .= '<li>';
if (F_getBoolean($ma['answer_enabled'])) {
$answlist .= '<acronym class="onbox" title="' . $l['w_enabled'] . '">+</acronym>';
} else {
$answlist .= '<acronym class="offbox" title="' . $l['w_disabled'] . '">-</acronym>';
}
if ($m['question_type'] != 4) {
if (F_getBoolean($ma['answer_isright'])) {
$answlist .= ' <acronym class="okbox" title="' . $l['h_answer_right'] . '">T</acronym>';
} else {
$answlist .= ' <acronym class="nobox" title="' . $l['h_answer_wrong'] . '">F</acronym>';
}
}
if ($ma['answer_position'] > 0) {
$answlist .= ' <acronym class="onbox" title="' . $l['h_position'] . '">' . intval($ma['answer_position']) . '</acronym>';
} else {
$answlist .= ' <acronym class="offbox" title="' . $l['h_position'] . '"> </acronym>';
}
if ($ma['answer_keyboard_key'] > 0) {
$answlist .= ' <acronym class="onbox" title="' . $l['h_answer_keyboard_key'] . '">' . F_text_to_xml(chr($ma['answer_keyboard_key'])) . '</acronym>';
} else {
$answlist .= ' <acronym class="offbox" title="' . $l['h_answer_keyboard_key'] . '"> </acronym>';
}
$answlist .= ' <a href="tce_edit_answer.php?subject_module_id=' . $subject_module_id . '&question_subject_id=' . $subject_id . '&answer_question_id=' . $m['question_id'] . '&answer_id=' . $ma['answer_id'] . '" title="' . $l['t_answers_editor'] . ' [ID = ' . $ma['answer_id'] . ']" class="xmlbutton">' . $l['w_edit'] . '</a>';
//$answlist .= " ";
//$answlist .= "".F_decode_tcecode($ma['answer_description'])."";
$answlist .= '<br /><br />' . K_NEWLINE;
$answlist .= '<div class="paddingleft">' . F_decode_tcecode($ma['answer_description']) . '</div>' . K_NEWLINE;
if (K_ENABLE_ANSWER_EXPLANATION and !empty($ma['answer_explanation'])) {
$answlist .= '<div class="paddingleft"><br /><span class="explanation">' . $l['w_explanation'] . ':</span><br />' . F_decode_tcecode($ma['answer_explanation']) . '</div>' . K_NEWLINE;
}
$answlist .= '</li>' . K_NEWLINE;
}
if (strlen($answlist) > 0) {
$questlist .= "<ol class=\"answer\">\n" . $answlist . "</ol><br /><br />\n";
}
} else {
F_display_db_error();
}
}
// end if hide_answers
$questlist .= '</li>' . K_NEWLINE;
}
if (strlen($questlist) > 0) {
// display the list
echo '<ul class="question">' . K_NEWLINE;
echo $questlist;
echo '</ul>' . K_NEWLINE;
echo '<div class="row"><hr /></div>' . K_NEWLINE;
// check/uncheck all options
echo '<span dir="' . $l['a_meta_dir'] . '">';
echo '<input type="radio" name="checkall" id="checkall1" value="1" onclick="document.getElementById(\'form_selectquestions\').submit()" />';
echo '<label for="checkall1">' . $l['w_check_all'] . '</label> ';
echo '<input type="radio" name="checkall" id="checkall0" value="0" onclick="document.getElementById(\'form_selectquestions\').submit()" />';
echo '<label for="checkall0">' . $l['w_uncheck_all'] . '</label>';
echo '</span>' . K_NEWLINE;
echo ' ';
if ($l['a_meta_dir'] == 'rtl') {
$arr = '←';
} else {
$arr = '→';
}
// action options
echo '<select name="menu_action" id="menu_action" size="0">' . K_NEWLINE;
echo '<option value="0" style="color:gray">' . $l['m_with_selected'] . '</option>' . K_NEWLINE;
echo '<option value="enable">' . $l['w_enable'] . '</option>' . K_NEWLINE;
echo '<option value="disable">' . $l['w_disable'] . '</option>' . K_NEWLINE;
echo '<option value="delete">' . $l['w_delete'] . '</option>' . K_NEWLINE;
echo '<option value="copy">' . $l['w_copy'] . ' ' . $arr . '</option>' . K_NEWLINE;
echo '<option value="move">' . $l['w_move'] . ' ' . $arr . '</option>' . K_NEWLINE;
echo '</select>' . K_NEWLINE;
// select new topic (for copy or move action)
echo '<select name="new_subject_id" id="new_subject_id" size="0" title="' . $l['h_subject'] . '">' . K_NEWLINE;
$sql = F_select_module_subjects_sql('module_enabled=\'1\' AND subject_enabled=\'1\'');
if ($r = F_db_query($sql, $db)) {
echo '<option value="0" style="color:gray">' . $l['w_subject'] . '</option>' . K_NEWLINE;
$prev_module_id = 0;
while ($m = F_db_fetch_array($r)) {
if ($m['module_id'] != $prev_module_id) {
$prev_module_id = $m['module_id'];
echo '<option value="0" style="color:gray;font-weight:bold;" disabled="disabled">* ' . htmlspecialchars($m['module_name'], ENT_NOQUOTES, $l['a_meta_charset']) . '</option>' . K_NEWLINE;
}
echo '<option value="' . $m['subject_id'] . '"> ' . htmlspecialchars($m['subject_name'], ENT_NOQUOTES, $l['a_meta_charset']) . '</option>' . K_NEWLINE;
}
} else {
echo '</select>' . K_NEWLINE;
F_display_db_error();
}
echo '</select>' . K_NEWLINE;
// submit button
F_submit_button("update", $l['w_update'], $l['h_update']);
}
// ---------------------------------------------------------------
// -- page jumper (menu for successive pages)
if ($rowsperpage > 0) {
$sql = 'SELECT count(*) AS total FROM ' . K_TABLE_QUESTIONS . ' ' . $wherequery . '';
if (!empty($order_field)) {
$param_array = '&order_field=' . urlencode($order_field) . '';
}
if (!empty($orderdir)) {
$param_array .= '&orderdir=' . $orderdir . '';
}
if (!empty($hide_answers)) {
$param_array .= '&hide_answers=' . intval($hide_answers) . '';
}
$param_array .= '&subject_module_id=' . $subject_module_id . '';
$param_array .= '&subject_id=' . $subject_id . '';
$param_array .= '&submitted=1';
F_show_page_navigator($_SERVER['SCRIPT_NAME'], $sql, $firstrow, $rowsperpage, $param_array);
}
} else {
F_display_db_error();
}
return TRUE;
}
/**
* 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;
}
/**
* 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;
}
/**
* 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;
}
/**
* 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;
}
/**
* 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
}
/**
* 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;
}
/**
* Returns an array containing groups IDs to which the specified user belongs
* @param $user_id (int) user ID
* @return array containing user's groups IDs
*/
function F_get_user_groups($user_id)
{
global $l, $db;
require_once '../config/tce_config.php';
$user_id = intval($user_id);
$groups = array();
$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;
}
/**
* 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();
}
}
}
}
/**
* 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;
}
/**
* 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;
}
}
}
}
/**
* 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;
}
/**
* 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();
}
}
//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';
}
}
}
echo '<div class="container">' . K_NEWLINE;
echo '<div class="tceformbox">' . K_NEWLINE;
echo '<form action="' . $_SERVER['SCRIPT_NAME'] . '" method="post" enctype="multipart/form-data" id="form_editor">' . K_NEWLINE;
echo F_connection_selector($sel_a_obj_id, $sel_b_obj_id, $sel_cbt_id);
echo getFormNoscriptSelect('selectrecord');
echo '<div class="row"><hr /></div>' . K_NEWLINE;
if (isset($cab_ids) and !empty($cab_ids)) {
echo getFormDescriptionLine($l['w_object'] . ' (A)', '', F_get_object_path($cab_a_obj_id, true));
/**
* 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;
}
}
/**
* 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();
}
}
/**
* 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;
}
