/**
 * 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;
    $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);
    // 1. create user's test entry
    // ------------------------------
    $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(K_TIMESTAMP_FORMAT) . '\'
		)';
    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');
    }
    // get ID of first user's test (if exist)
    $firsttest = F_getFirstTestUser($test_id);
    // select questions
    if (F_getBoolean($testdata['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 questions
                // ------------------------------
                $sqlq = 'SELECT question_id, question_type, question_difficulty, question_position
					FROM ' . K_TABLE_QUESTIONS . '';
                $sqlq .= ' WHERE question_subject_id IN (
						SELECT subjset_subject_id
						FROM ' . K_TABLE_SUBJECT_SET . '
						WHERE subjset_tsubset_id=' . $m['tsubset_id'] . '';
                $sqlq .= ' )
					AND question_type=' . $m['tsubset_type'] . '
					AND question_difficulty=' . $m['tsubset_difficulty'] . '
					AND question_enabled=\'1\'
					AND question_id NOT IN (' . $selected_questions . ')';
                if ($m['tsubset_type'] == 1) {
                    // single question (MCSA)
                    // get questions with the right number of answers
                    $sqlq .= '
						AND question_id IN (
							SELECT answer_question_id
							FROM ' . K_TABLE_ANSWERS . '
							WHERE answer_enabled=\'1\'
								AND answer_isright=\'1\'';
                    if (!F_getBoolean($testdata['test_random_answers_order'])) {
                        $sqlq .= ' AND answer_position>0';
                    }
                    $sqlq .= ' GROUP BY answer_question_id
							HAVING (COUNT(answer_id)>0)
							)';
                    $sqlq .= '
						AND question_id IN (
							SELECT answer_question_id
							FROM ' . K_TABLE_ANSWERS . '
							WHERE answer_enabled=\'1\'
								AND answer_isright=\'0\'';
                    if (!F_getBoolean($testdata['test_random_answers_order'])) {
                        $sqlq .= ' AND answer_position>0';
                    }
                    $sqlq .= ' GROUP BY answer_question_id
							HAVING (COUNT(answer_id)>=' . ($m['tsubset_answers'] - 1) . ')
							)';
                } elseif ($m['tsubset_type'] == 2) {
                    // multiple question (MCMA)
                    // get questions with the right number of answers
                    $sqlq .= '
						AND question_id IN (
							SELECT answer_question_id
							FROM ' . K_TABLE_ANSWERS . '
							WHERE answer_enabled=\'1\'';
                    if (!F_getBoolean($testdata['test_random_answers_order'])) {
                        $sqlq .= ' AND answer_position>0';
                    }
                    $sqlq .= ' GROUP BY answer_question_id
							HAVING (COUNT(answer_id)>=' . $m['tsubset_answers'] . ')
							)';
                } elseif ($m['tsubset_type'] == 4) {
                    // ordering question
                    // get questions with the right number of answers
                    $sqlq .= '
						AND question_id IN (
							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 (F_getBoolean($testdata['test_random_questions_select']) or F_getBoolean($testdata['test_random_questions_order'])) {
                    $sqlq .= ' ORDER BY RAND()';
                } else {
                    $sqlq .= ' AND question_position>0 ORDER BY question_position';
                }
                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 (F_getBoolean($testdata['test_random_questions_select']) or F_getBoolean($testdata['test_random_questions_order'])) {
                            $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
            // 4. STORE QUESTIONS AND ANSWERS
            // ------------------------------
            /*
            if ((!F_getBoolean($testdata['test_random_questions_select'])) AND (!F_getBoolean($testdata['test_random_questions_order']))) {
            	// order questions
            	ksort($questions_data);
            } else {
            	shuffle($questions_data);
            }
            */
            // order questions
            //·ÇËæ»ú¡¢·Ç˳Ðò
            if (!F_getBoolean($testdata['test_random_questions_select']) and !F_getBoolean($testdata['test_random_questions_order'])) {
                ksort($questions_data);
            }
            //Ëæ»ú¡¢Ë³Ðò
            if (F_getBoolean($testdata['test_random_questions_select']) and F_getBoolean($testdata['test_random_questions_order'])) {
                ksort($questions_data);
            } else {
                shuffle($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;
}
Example #2
0
/**
 * 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;
}