/**
* 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).
* @param $overwrite (boolean) If true overwrites the previous answers on non-repeatable tests.
* @return boolean TRUE in case of success, FALSE otherwise.
*/
function F_importOMRTestData($user_id, $date, $omr_testdata, $omr_answers, $overwrite = false)
{
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. check if test is repeatable
$sqls = 'SELECT test_id FROM ' . K_TABLE_TESTS . ' WHERE test_id=' . $test_id . ' AND test_repeatable=\'1\' LIMIT 1';
if ($rs = F_db_query($sqls, $db)) {
if ($ms = F_db_fetch_array($rs)) {
// 1a. update previous test data if repeatable
$sqld = 'UPDATE ' . K_TABLE_TEST_USER . ' SET testuser_status=testuser_status+1 WHERE testuser_test_id=' . $test_id . ' AND testuser_user_id=' . $user_id . ' AND testuser_status>3';
if (!($rd = F_db_query($sqld, $db))) {
F_display_db_error();
}
} else {
if ($overwrite) {
// 1b. delete previous test data if not repeatable
$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();
}
} else {
// 1c. check if this data already exist
if (F_count_rows(K_TABLE_TEST_USER, 'WHERE testuser_test_id=' . $test_id . ' AND testuser_user_id=' . $user_id . '') > 0) {
return false;
}
}
}
} else {
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');
F_updateTestuserStat($date);
}
// 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;
$numselected = 0;
// count the number of MCSA selected answers
// for each answer 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) {
++$numselected;
if ($numselected == 1) {
$unanswered = false;
if ($answer_isright) {
$qscore = $question_right_score;
} else {
$qscore = $question_wrong_score;
}
} else {
// multiple answer selected
$unanswered = true;
$qscore = $question_unanswered_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;
}
/**
* Create user's test and returns TRUE on success.
* @param $test_id (int) test ID.
* @param $user_id (int) user ID.
* @return boolean TRUE in case of success, FALSE otherwise.
*/
function F_createTest($test_id, $user_id)
{
require_once '../config/tce_config.php';
require_once '../../shared/code/tce_functions_tcecode.php';
global $db, $l;
if (F_isTestOverLimits()) {
return false;
}
$test_id = intval($test_id);
$user_id = intval($user_id);
$firsttest = 0;
// id of the firts test of this type
// get test data
$testdata = F_getTestData($test_id);
$test_random_questions_select = F_getBoolean($testdata['test_random_questions_select']);
$test_random_questions_order = F_getBoolean($testdata['test_random_questions_order']);
$test_questions_order_mode = intval($testdata['test_questions_order_mode']);
$test_random_answers_select = F_getBoolean($testdata['test_random_answers_select']);
$test_random_answers_order = F_getBoolean($testdata['test_random_answers_order']);
$test_answers_order_mode = intval($testdata['test_answers_order_mode']);
$random_questions = ($test_random_questions_select or $test_random_questions_order);
$sql_answer_position = '';
if (!$test_random_answers_order and $test_answers_order_mode == 0) {
$sql_answer_position = ' AND answer_position>0';
}
$sql_questions_order_by = '';
switch ($test_questions_order_mode) {
case 0:
// position
$sql_questions_order_by = ' AND question_position>0 ORDER BY question_position';
break;
case 1:
// alphabetic
$sql_questions_order_by = ' ORDER BY question_description';
break;
case 2:
// ID
$sql_questions_order_by = ' ORDER BY question_id';
break;
case 3:
// type
$sql_questions_order_by = ' ORDER BY question_type';
break;
case 4:
// subject ID
$sql_questions_order_by = ' ORDER BY question_subject_id';
break;
}
// IDs of MCSA questions with more than one correct answer
$right_answers_mcsa_questions_ids = '';
// IDs of MCSA questions with more than one wrong answer
$wrong_answers_mcsa_questions_ids = array();
// IDs of MCMA questions with more than one answer
$answers_mcma_questions_ids = array();
// IDs of ORDER questions with more than one ordering answer
$answers_order_questions_ids = '';
// 1. create user's test entry
// ------------------------------
$date = date(K_TIMESTAMP_FORMAT);
$sql = 'INSERT INTO ' . K_TABLE_TEST_USER . ' (
testuser_test_id,
testuser_user_id,
testuser_status,
testuser_creation_time
) VALUES (
' . $test_id . ',
' . $user_id . ',
0,
\'' . $date . '\'
)';
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');
F_updateTestuserStat($date);
}
// get ID of first user's test (if exist)
$firsttest = F_getFirstTestUser($test_id);
// select questions
if ($test_random_questions_select or $firsttest == 0) {
// selected questions IDs
$selected_questions = '0';
// 2. for each set of subjects
// ------------------------------
$sql = 'SELECT *
FROM ' . K_TABLE_TEST_SUBJSET . '
WHERE tsubset_test_id=' . $test_id . '
ORDER BY tsubset_type, tsubset_difficulty, tsubset_answers DESC';
if ($r = F_db_query($sql, $db)) {
$questions_data = array();
while ($m = F_db_fetch_array($r)) {
// 3. select the subjects IDs
$selected_subjects = '0';
$sqlt = 'SELECT subjset_subject_id FROM ' . K_TABLE_SUBJECT_SET . ' WHERE subjset_tsubset_id=' . $m['tsubset_id'];
if ($rt = F_db_query($sqlt, $db)) {
while ($mt = F_db_fetch_array($rt)) {
$selected_subjects .= ',' . $mt['subjset_subject_id'];
}
}
// 4. select questions
// ------------------------------
$sqlq = 'SELECT question_id, question_type, question_difficulty, question_position
FROM ' . K_TABLE_QUESTIONS . '';
$sqlq .= ' WHERE question_subject_id IN (' . $selected_subjects . ')
AND question_difficulty=' . $m['tsubset_difficulty'] . '
AND question_enabled=\'1\'
AND question_id NOT IN (' . $selected_questions . ')';
if ($m['tsubset_type'] > 0) {
$sqlq .= ' AND question_type=' . $m['tsubset_type'];
}
if ($m['tsubset_type'] == 1) {
// (MCSA : Multiple Choice Single Answer) ----------
// get questions with the right number of answers
if (empty($right_answers_mcsa_questions_ids)) {
$right_answers_mcsa_questions_ids = '0';
$sqlt = 'SELECT DISTINCT answer_question_id FROM ' . K_TABLE_ANSWERS . ' WHERE answer_enabled=\'1\' AND answer_isright=\'1\'' . $sql_answer_position . '';
if ($rt = F_db_query($sqlt, $db)) {
while ($mt = F_db_fetch_array($rt)) {
$right_answers_mcsa_questions_ids .= ',' . $mt['answer_question_id'];
}
}
}
$sqlq .= ' AND question_id IN (' . $right_answers_mcsa_questions_ids . ')';
if ($m['tsubset_answers'] > 0) {
if (!isset($wrong_answers_mcsa_questions_ids['\'' . $m['tsubset_answers'] . '\''])) {
$wrong_answers_mcsa_questions_ids['\'' . $m['tsubset_answers'] . '\''] = '0';
$sqlt = 'SELECT answer_question_id FROM ' . K_TABLE_ANSWERS . ' WHERE answer_enabled=\'1\' AND answer_isright=\'0\'' . $sql_answer_position . ' GROUP BY answer_question_id HAVING (COUNT(answer_id)>=' . ($m['tsubset_answers'] - 1) . ')';
if ($rt = F_db_query($sqlt, $db)) {
while ($mt = F_db_fetch_array($rt)) {
$wrong_answers_mcsa_questions_ids['\'' . $m['tsubset_answers'] . '\''] .= ',' . $mt['answer_question_id'];
}
}
}
$sqlq .= ' AND question_id IN (' . $wrong_answers_mcsa_questions_ids['\'' . $m['tsubset_answers'] . '\''] . ')';
}
} elseif ($m['tsubset_type'] == 2) {
// (MCMA : Multiple Choice Multiple Answers) -------
// get questions with the right number of answers
if ($m['tsubset_answers'] > 0) {
if (!isset($answers_mcma_questions_ids['\'' . $m['tsubset_answers'] . '\''])) {
$answers_mcma_questions_ids['\'' . $m['tsubset_answers'] . '\''] = '0';
$sqlt = 'SELECT answer_question_id FROM ' . K_TABLE_ANSWERS . ' WHERE answer_enabled=\'1\'' . $sql_answer_position . ' GROUP BY answer_question_id HAVING (COUNT(answer_id)>=' . $m['tsubset_answers'] . ')';
if ($rt = F_db_query($sqlt, $db)) {
while ($mt = F_db_fetch_array($rt)) {
$answers_mcma_questions_ids['\'' . $m['tsubset_answers'] . '\''] .= ',' . $mt['answer_question_id'];
}
}
}
$sqlq .= ' AND question_id IN (' . $answers_mcma_questions_ids['\'' . $m['tsubset_answers'] . '\''] . ')';
}
} elseif ($m['tsubset_type'] == 4) {
// ORDERING ----------------------------------------
if (empty($answers_order_questions_ids)) {
$answers_order_questions_ids = '0';
$sqlt = 'SELECT answer_question_id FROM ' . K_TABLE_ANSWERS . ' WHERE answer_enabled=\'1\' AND answer_position>0 GROUP BY answer_question_id HAVING (COUNT(answer_id)>1)';
if ($rt = F_db_query($sqlt, $db)) {
while ($mt = F_db_fetch_array($rt)) {
$answers_order_questions_ids .= ',' . $mt['answer_question_id'];
}
}
}
$sqlq .= ' AND question_id IN (' . $answers_order_questions_ids . ')';
}
if ($random_questions) {
$sqlq .= ' ORDER BY RAND()';
} else {
$sqlq .= $sql_questions_order_by;
}
if (K_DATABASE_TYPE == 'ORACLE') {
$sqlq = 'SELECT * FROM (' . $sqlq . ') WHERE rownum <= ' . $m['tsubset_quantity'] . '';
} else {
$sqlq .= ' LIMIT ' . $m['tsubset_quantity'] . '';
}
if ($rq = F_db_query($sqlq, $db)) {
while ($mq = F_db_fetch_array($rq)) {
// store questions data
$tmp_data = array('id' => $mq['question_id'], 'type' => $mq['question_type'], 'answers' => $m['tsubset_answers'], 'score' => $testdata['test_score_unanswered'] * $mq['question_difficulty']);
if ($random_questions or $test_questions_order_mode != 0) {
$questions_data[] = $tmp_data;
} else {
$questions_data[$mq['question_position']] = $tmp_data;
}
$selected_questions .= ',' . $mq['question_id'] . '';
}
// end while select questions
} else {
F_display_db_error(false);
return false;
}
// --- end 3
}
// end while for each set of subjects
// 5. STORE QUESTIONS AND ANSWERS
// ------------------------------
if ($random_questions) {
shuffle($questions_data);
} else {
ksort($questions_data);
}
// add questions to database
$question_order = 0;
foreach ($questions_data as $key => $q) {
$question_order++;
$testlog_id = F_newTestLog($testuser_id, $q['id'], $q['score'], $question_order, $q['answers']);
// Add answers
if (!F_addQuestionAnswers($testlog_id, $q['id'], $q['type'], $q['answers'], $firsttest, $testdata)) {
return false;
}
}
} else {
F_display_db_error(false);
return false;
}
// --- end 2
} else {
// same questions for all test-takers
// ---------------------------------------
$sql = 'SELECT *
FROM ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_QUESTIONS . '
WHERE question_id=testlog_question_id
AND testlog_testuser_id=' . $firsttest . '';
if (F_getBoolean($testdata['test_random_questions_order'])) {
$sql .= ' ORDER BY RAND()';
} else {
$sql .= ' ORDER BY testlog_order';
}
if ($r = F_db_query($sql, $db)) {
$question_order = 0;
while ($m = F_db_fetch_array($r)) {
$question_order++;
// copy values to new user test
$question_unanswered_score = $testdata['test_score_unanswered'] * $m['question_difficulty'];
$testlog_id = F_newTestLog($testuser_id, $m['testlog_question_id'], $question_unanswered_score, $question_order, $m['testlog_num_answers']);
// Add answers
if (!F_addQuestionAnswers($testlog_id, $m['question_id'], $m['question_type'], $m['testlog_num_answers'], $firsttest, $testdata)) {
return false;
}
}
} else {
F_display_db_error(false);
return false;
}
}
// 6. update user's test status as 1 = the test has been successfully created
// ------------------------------
$sql = 'UPDATE ' . K_TABLE_TEST_USER . ' SET
testuser_status=1,
testuser_creation_time=\'' . date(K_TIMESTAMP_FORMAT) . '\'
WHERE testuser_id=' . $testuser_id . '';
if (!($r = F_db_query($sql, $db))) {
F_display_db_error(false);
return false;
}
return true;
}