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