<?php
//============================================================+
// File name   : tce_functions_test.php
// Begin       : 2004-05-28
// Last Update : 2006-11-08
// 
// Description : Functions to handle test generation, status
//               and user access.
//
// Author: Nicola Asuni
//
// (c) Copyright:
//               Tecnick.com S.r.l.
//               Via Ugo Foscolo n.19
//               09045 Quartu Sant'Elena (CA)
//               ITALY
//               www.tecnick.com
//               info@tecnick.com
//
// License: GNU GENERAL PUBLIC LICENSE v.2
//          http://www.gnu.org/copyleft/gpl.html
//============================================================+

/**
 * Functions to handle test generation, status and user access.
 * @package com.tecnick.tcexam.shared
 * @author Nicola Asuni
 * @copyright Copyright &copy; 2004-2006, Tecnick.com S.r.l. - Via Ugo Foscolo n.19 - 09045 Quartu Sant'Elena (CA) - ITALY - www.tecnick.com - info@tecnick.com
 * @link www.tecnick.com
 * @since 2004-05-28
 */

/**
 * Returns an XHTML unordered list of user's enabled tests.
 * @return string containing an XHTML unordered list
 */
function F_getUserTests() {
	require_once('../config/tce_config.php');
	require_once('../../shared/code/tce_functions_tcecode.php');
	global $db, $l;
	
	$str = ""; //string to return
	
	// get current date-time
	$current_time = date(K_TIMESTAMP_FORMAT);
	
	$sql = "SELECT *
		FROM ".K_TABLE_TESTS."
		WHERE (
			test_id IN (
				SELECT tsubset_test_id
				FROM ".K_TABLE_TEST_SUBJSET."
			)
			AND (test_begin_time < '".$current_time."')
			AND (test_end_time > '".$current_time."')
		)
		ORDER BY test_name"; // select all active tests
	if($r = F_db_query($sql, $db)) {
		while($m = F_db_fetch_array($r)) { // for each active test
			// check user's authorization
			if (F_isValidTestUser($m['test_id'],$_SESSION['session_user_ip'], $m['test_ip_range'])) {
				// the user's IP is valid, check test status
				$test_status = F_checkTestStatus($_SESSION['session_user_id'], $m['test_id'], $m['test_duration_time']);
				if (($test_status < 4) OR (F_getBoolean($m['test_results_to_users']))) {
					$str .= "<li title=\"".F_tcecodeToTitle($m['test_description'])."\">";
					$str .= $m['test_name'];
					$str .= " [".F_testInfoLink($m['test_id'], $l['w_info'])."]";
				}
				
				// display various links by status case
				switch ($test_status) {
					case 0: { // 0 = the test generation process is started but not completed
						// print execute test link
						$str .= " [<a href=\"tce_test_execute.php?testid=".$m['test_id']."\" title=\"".$l['h_execute']."\">".$l['w_execute']."</a>]";
						break;
					}
					case 1: // 1 = the test has been successfully created
					case 2: // 2 = all questions have been displayed to the user
					case 3: { // 3 = all questions have been answered
						// continue test
						$str .= " [<a href=\"tce_test_execute.php?testid=".$m['test_id']."\" title=\"".$l['h_continue']."\">".$l['w_continue']."</a>]";
						break;
					}
					case 4: { // 4 = test locked (for timeout)
						if (F_getBoolean($m['test_results_to_users'])) {
							$str .= " [<a href=\"tce_test_results.php?testid=".$m['test_id']."\" title=\"".$l['h_result']."\">".$l['w_result']."</a>]";
						}
						break;
					}
				}
				
				if (($test_status < 4) OR (F_getBoolean($m['test_results_to_users']))) {
					$str .= "</li>";
				}
			}
		}
	}
	else {
		F_display_db_error();
	}
	
	if (strlen($str) > 0) {
		$str = $l['w_tests_available'].":<br />\n<ul>\n".$str."</ul>\n";
	}
	else {
		$str = $l['m_no_test_available'];
	}
	return $str;
}

/**
 * Check if user's IP is valid over test IP range
 * @param int $user_ip user's IP address.
 * @param int $test_ip test IP valid addresses. Various IP addresses may be separated using comma character. The asterisk character may be used to indicate "any number".
 * @return true if IP is valid, false otherwise
 */
function F_isValidIP($user_ip, $test_ip) {
	// remove unsupported chars
	$test_ip = preg_replace("/[^0-9,\.\*]+/si", "", $test_ip);
	$user_ip = preg_replace("/[^0-9\.]+/si", "", $user_ip);
	
	if ((strlen($user_ip) <= 0) OR (strlen($test_ip) <= 0)) {
		return false;
	}
	
	// build array of valid IPs
	$test_ip_array = explode(",", $test_ip);
	
	// split user IP in 4 parts
	$user_ip_part = explode(".", $user_ip, 4);
	
	// check user IP for each IP interval
	while (list($key, $IP) = each($test_ip_array)) {
		$temp_status = true;
 		$test_ip_part = explode(".", $IP, 4);
		for ($i = 0; $i < 4; $i++) {
			if ((strcmp($test_ip_part[$i], "*") != 0) AND ((int) $user_ip_part[$i] != (int) $test_ip_part[$i])) {
				$temp_status = false;
			}
		}
		if ($temp_status) {
			return true;
		}
	}
	return false;
}

/**
 * Check if user is authorized to execute the specified test
 * @param int $test_id ID of the selected test
 * @param int $user_ip user's IP address.
 * @param int $test_ip test IP valid addresses. Various IP addresses may be separated using comma character. The asterisk character may be used to indicate "any number".
 * @return true if is user is authorized, false otherwise
 */
function F_isValidTestUser($test_id, $user_ip, $test_ip) {
	require_once('../config/tce_config.php');
	global $db, $l;
	
	// check user's IP
	if (!F_isValidIP($user_ip, $test_ip)) {
		return false;
	}
	// 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=".$_SESSION['session_user_id']."
			LIMIT 1") > 0) {
		return true;
	}
	return false;
}
/**
 * Terminate user's test<br>
 * @param int $test_id test ID
 * @since 4.0.000 (2006-09-27)
 */
function F_terminateUserTest($testid) {
	require_once('../config/tce_config.php');
	global $db, $l;
	
	$sql = "UPDATE ".K_TABLE_TEST_USER."
		SET testuser_status=4 
		WHERE testuser_test_id=".$testid."
			AND testuser_user_id=".$_SESSION['session_user_id']."";
	if(!$r = F_db_query($sql, $db)) {
		F_display_db_error();
	}
}

/**
 * Check and returns specific test status for the specified user.<br>
 * @param int $user_id user ID
 * @param int $test_id test ID
 * @param int $duration test duration in seconds
 * @return test status: <ul><li>0 = the test generation process is started but not completed;</li><li>1 = the test has been successfully created;</li><li>2 = all questions have been displayed to the user;</li><li>3 = all questions have been answered;</li><li>4 = test locked (for timeout);</li></ul>
 */
function F_checkTestStatus($user_id, $test_id, $duration) {
	require_once('../config/tce_config.php');
	global $db, $l;
	
	// get current date-time
	$current_time = date(K_TIMESTAMP_FORMAT);
	
	$test_status = 0;
	
	// get current test status for the selected user
	$sql = "SELECT testuser_id,testuser_status,testuser_creation_time
		FROM ".K_TABLE_TEST_USER."
		WHERE testuser_test_id=".$test_id."
			AND testuser_user_id=".$user_id."";
	if($r = F_db_query($sql, $db)) {
		if($m = F_db_fetch_array($r)) {
			$testuser_id = $m['testuser_id'];
			$test_status = $m['testuser_status'];
			$endtime = date(K_TIMESTAMP_FORMAT, strtotime($m['testuser_creation_time']) + ($duration * K_SECONDS_IN_MINUTE));
			if (($test_status > 0) AND ($test_status < 4) AND ($current_time > $endtime)) {
				// update test mode to 4 = test locked (for timeout)
				$sqlu = "UPDATE ".K_TABLE_TEST_USER."
					SET testuser_status=4 
					WHERE testuser_id=".$testuser_id."";
				if(!$ru = F_db_query($sqlu, $db)) {
					F_display_db_error();
				} else {
					$test_status = 4;
				}
			} else {
				switch ($test_status) {
					case 0: { // 0 = the test generation process is started but not completed
						// delete incomplete test (also deletes test logs using database referential integrity)
						$sqld = "DELETE FROM ".K_TABLE_TEST_USER." 
							WHERE testuser_id=".$testuser_id."";
						if(!$rd = F_db_query($sqld, $db)) {
							F_display_db_error();
						}
						break;
					}
					case 1: { // 1 = the test has been successfully created
						//check if all questions were displayed
						if (F_count_rows(K_TABLE_TESTS_LOGS, "WHERE testlog_testuser_id=".$testuser_id." AND testlog_display_time IS NULL") == 0) {
							// update test status to 2 = all questions have been displayed to the user
							$sqlu = "UPDATE ".K_TABLE_TEST_USER."
								SET testuser_status=2 
								WHERE testuser_id=".$testuser_id."";
							if(!$ru = F_db_query($sqlu, $db)) {
								F_display_db_error();
							} else {
								$test_status = 2;
							}
						}
						break;
					}
					case 2: { // 2 = all questions have been displayed to the user
						//check if test has been completed in time
						if (F_count_rows(K_TABLE_TESTS_LOGS, "WHERE testlog_testuser_id=".$testuser_id." AND testlog_change_time IS NULL") == 0) {
							// update test mode to 3 = all questions have been answered
							$sqlu = "UPDATE ".K_TABLE_TEST_USER."
								SET testuser_status=3 
								WHERE testuser_id=".$testuser_id."";
							if(!$ru = F_db_query($sqlu, $db)) {
								F_display_db_error();
							} else {
								$test_status = 3;
							}
						}
						break;
					}
				} //end switch
			} //end else
		}
	}
	else {
		F_display_db_error();
	}
	return $test_status;
}

/**
 * Returns XHTML link to open test info popup.
 * @param int $test_id test ID
 * @param string $link_name link caption
 * return XHTML code
 */
function F_testInfoLink($test_id, $link_name="") {
	require_once('../config/tce_config.php');
	global $db, $l;
	
	$str = "";
	$onclickinfo = "infoTestWindow=window.open('tce_popup_test_info.php?testid=".$test_id."'";
	$onclickinfo .= ",'infoTestWindow','dependent";
	$onclickinfo .= ",height=".K_TEST_INFO_HEIGHT;
	$onclickinfo .= ",width=".K_TEST_INFO_WIDTH;
	$onclickinfo .= ",menubar=no,resizable=yes,scrollbars=yes,status=no,toolbar=no');";
	$onclickinfo .= "return false;";
	$str .= "<a href=\"tce_popup_test_info.php?testid=".$test_id."\" onclick=\"".$onclickinfo."\" title=\"".$l['m_new_window_link']."\">";
	if (strlen($link_name) > 0) {
		$str .= $link_name;
	}
	else {
		$str .= $l['w_info'];
	}
	$str .= "</a>";
	return $str;
}

/**
 * Returns an XHTML string containing specified test information.
 * @param int $test_id test ID
 * @param boolean $showip if true display enabled users' IP range
 * @return string containing an XHTML code
 */
function F_printTestInfo($test_id, $showip=false) {
	require_once('../config/tce_config.php');
	require_once('../../shared/code/tce_functions_tcecode.php');
	global $db, $l;
	
	$str = ""; //string to return
	$boolval = Array($l['w_no'], $l['w_yes']);
	
	$sql = "SELECT * FROM ".K_TABLE_TESTS." WHERE test_id=".$test_id."";
	if($r = F_db_query($sql, $db)) {
		if($m = F_db_fetch_array($r)) {
			$str .= "<h1>".htmlspecialchars($m['test_name'], ENT_NOQUOTES, $l['a_meta_charset'])."</h1>\n";
			$str .= "<div class=\"tcecontentbox\">".F_decode_tcecode($m['test_description'])."<br /><br /></div>\n";
			$str .= "<div class=\"tceformbox\">\n";
			$str .= F_twoColRow($l['w_time_begin'], $l['h_time_begin'], $m['test_begin_time']);
			$str .= F_twoColRow($l['w_time_end'], $l['h_time_end'], $m['test_end_time']);
			$str .= F_twoColRow($l['w_test_time'], $l['h_test_time'], $m['test_duration_time']." ".$l['w_minutes']."");
			$str .= F_twoColRow($l['w_random_questions'], $l['h_random_questions'], $boolval[intval(F_getBoolean($m['test_random_questions']))]);
			$str .= F_twoColRow($l['w_results_to_users'], $l['h_results_to_users'], $boolval[intval(F_getBoolean($m['test_results_to_users']))]);
			$str .= F_twoColRow($l['w_score_right'], $l['h_score_right'], $m['test_score_right']);
			$str .= F_twoColRow($l['w_max_score'], $l['w_max_score'], $m['test_max_score']);
			if ($showip) {
				$str .= F_twoColRow($l['w_ip_range'], $l['h_ip_range'], $m['test_ip_range']);
			}
			$str .= "<br/>";
		}
	}
	else {
		F_display_db_error();
	}
	$str .= "</div>";
	return $str;
}

/**
 * Returns the test data.
 * @param int $test_id test ID.
 * @return array containing test data.
 */
function F_getTestData($test_id) {
		require_once('../config/tce_config.php');
	global $db, $l;
	
	$td = Array();
	$sql = "SELECT *
		FROM ".K_TABLE_TESTS."
		WHERE test_id=".$test_id."
		LIMIT 1";
	if($r = F_db_query($sql, $db)) {
		$td = F_db_fetch_array($r);
	}
	else {
		F_display_db_error();
	}
	return $td;
}

/**
 * Returns the test name.
 * @param int $test_id test ID.
 * @return string test name or empty string in case of error.
 */
function F_getTestName($test_id) {
	$td = F_getTestData($test_id);
	return $td['test_name'];
}

/**
 * Returns the basic score for right questions.
 * @param int $test_id test ID.
 * @return double basic score for each difficulty level of questions.
 */
function F_getTestBasicScore($test_id) {
	$td = F_getTestData($test_id);
	return $td['test_score_right'];
}

/**
 * Returns the test duration time in seconds.
 * @param int $test_id test ID
 * @return int test duration time in seconds
 */
function F_getTestDuration($test_id) {
		require_once('../config/tce_config.php');
	$td = F_getTestData($test_id);
	return ($td['test_duration_time'] * K_SECONDS_IN_MINUTE);
}

/**
 * Returns the user's test start time in seconds since UNIX epoch (1970-01-01 00:00:00).
 * @param int $testuser_id user's test ID
 * @return int start time in seconds
 */
function F_getTestStartTime($testuser_id) {
		require_once('../config/tce_config.php');
	global $db, $l;
	
	$starttime = 0;
	// select test control row (if any)
	$sql = "SELECT testuser_creation_time 
		FROM ".K_TABLE_TEST_USER." 
		WHERE testuser_id=".$testuser_id."";
	if($r = F_db_query($sql, $db)) {
		if($m = F_db_fetch_array($r)) {
			$starttime = strtotime($m['testuser_creation_time']);
		}
	}
	else {
		F_display_db_error();
	}
	return $starttime;
}

/**
 * Return a formatted XHTML row to display 2 columns data.<br>
 * See CSS classes:<ul>
 * <li>div.row span.label</li>
 * <li>div.row span.formw</li>
 * </ul>
 * @param string $label string to display on the left column
 * @param string $description string to display on the title attribute of the left column field
 * @param string $value string to display on the right column
 * @return string XHTML code
 */
function F_twoColRow($label="", $description="", $value="") {
	$str = "";
	$str .= "<div class=\"row\">";
	$str .= "<span class=\"label\">";
	$str .= "<span title=\"".$description."\">";
	$str .= $label.": ";
	$str .= "</span>";
	$str .= "</span>";
	$str .= "<span class=\"value\">";
	$str .= $value;
	$str .= "</span>";
	$str .= "</div>\n";
	return $str;
}

/**
 * Returns true if the current user is authorized to execute the selected test.<br>
 * Generates the test if it's not already generated.
 * @param int $test_id test ID.
 * @return true if user is authorized, false otherwise.
 */
function F_executeTest($test_id) {
		require_once('../config/tce_config.php');
	global $db, $l;
	
	// get current date-time
	$current_time = date(K_TIMESTAMP_FORMAT);
	
	// select the specified test checking if it's valid for the current time
	$sql = "SELECT test_id, test_ip_range, test_duration_time FROM ".K_TABLE_TESTS." 
		WHERE test_id=".$test_id."
			AND test_begin_time < '".$current_time."'
			AND test_end_time > '".$current_time."'";
	if($r = F_db_query($sql, $db)) {
		if($m = F_db_fetch_array($r)) {
			// check user's authorization
			if (F_isValidTestUser($m['test_id'],$_SESSION['session_user_ip'], $m['test_ip_range'])) {
				// the user's IP is valid, check test status
				$test_status = F_checkTestStatus($_SESSION['session_user_id'], $m['test_id'], $m['test_duration_time']);
				switch ($test_status) {
					case 0: { // 0 = test is not yet created
						// create new test session for the current user
						return F_createTest($test_id, $_SESSION['session_user_id']);
						break;
					}
					case 1: // 1 = the test has been successfully created
					case 2: // 2 = all questions have been displayed to the user
					case 3: { // 3 = all questions have been answered
						return true;
						break;
					}
					case 4: { // 4 = test locked (for timeout)
						return false;
						break;
					}
				}
			}
		}
	}
	else {
		F_display_db_error();
	}
	return false;
}

/**
 * Checks if the current user is the right testlog_id owner.<br>
 * This function is used for security reasons.
 * @param int $test_id test ID
 * @param int $testlog_id test log ID
 * @return boolean TRUE in case of success, FALSE otherwise
 */
function F_isRightTestlogUser($test_id, $testlog_id) {
	require_once('../config/tce_config.php');
	global $db, $l;
	
	// check if the current user is the right testlog_id owner
	$sql = "SELECT testuser_user_id, testuser_test_id
		FROM ".K_TABLE_TEST_USER.", ".K_TABLE_TESTS_LOGS."
		WHERE testuser_id=testlog_testuser_id
			AND testlog_id=".$testlog_id."";
	if($r = F_db_query($sql, $db)) {
		if($m = F_db_fetch_array($r)) {
			if(($m['testuser_user_id'] != $_SESSION['session_user_id']) OR ($m['testuser_test_id'] != $test_id)) {
				return false;
			}
		} else {
			return false;
		}
	} else {
		F_display_db_error();
	}
	return true;
}

/**
 * Return an array containing answer_id field of selected answers.<br>
 * @param int $question_id question ID
 * @param boolean $checktype if true checks for answer_isright value on WHERE clause
 * @param int $type answer_isright value (0 = false, 1 = true)
 * @param int $limit maximum number of IDs to return
 * @param int $startindex array starting index (default = 0)
 * @param int $exclude_id ID of the answer to be excluded from selection (default=0)
 * @return array id of selected answers
 */
function F_selectAnswers($question_id, $checktype, $type, $limit, $startindex=0, $exclude_id=0) {
	require_once('../config/tce_config.php');
	global $db, $l;
	
	$answers_ids = array(); // stores answers IDs
	
	$sql = "SELECT answer_id
		FROM ".K_TABLE_ANSWERS."
		WHERE answer_question_id=".$question_id."";
	if ($checktype) {
		// single question
		$sql .= " AND answer_isright='".$type."'";
	}
	$sql .= " AND answer_enabled='1'";
	if ($exclude_id > 0) {
		$sql .= " AND answer_id<>".$exclude_id."";
	}
	$sql .= " ORDER BY RAND() LIMIT ".$limit."";
	
	if($r = F_db_query($sql, $db)) {
		while ($m = F_db_fetch_array($r)) {
			$answers_ids[$startindex++] = $m['answer_id'];
		}
	} else {
		F_display_db_error(false);
		F_db_query("ROLLBACK", $db); // rollback transaction
		return false;
	}
	return $answers_ids;
}

/**
 * Add specified answers on tce_tests_logs_answer table.
 * @param int $testlog_id testlog ID
 * @param array $answers_ids array of answer IDs to add
 * @return boolean true in case of success, false otherwise
 */
function F_addLogAnswers($testlog_id, $answers_ids) {
	require_once('../config/tce_config.php');
	global $db, $l;
	
	while (list($key, $answid) = each($answers_ids)) {
		$sqli = "INSERT INTO ".K_TABLE_LOG_ANSWER." (
			logansw_testlog_id,
			logansw_answer_id,
			logansw_selected,
			logansw_order
			) VALUES (
			".$testlog_id.",
			".$answid.",
			'0',
			".($key+1)."
			)";
		if(!$ri = F_db_query($sqli, $db)) {
			F_display_db_error(false);
			F_db_query("ROLLBACK", $db); // rollback transaction
			return false;
		}
	}
	return true;
}

/**
 * Returns the ID of the tce_tests_users table corresponding to a complete test of $test_id type.
 * @param int $test_id test ID
 * @return int testuser ID
 */
function F_getFirstTestUser($test_id) {
	require_once('../config/tce_config.php');
	global $db, $l;
	
	// check if this is the first test creation
	$firsttest = 0;
	$sql = "SELECT testuser_id
		FROM ".K_TABLE_TEST_USER."
		WHERE testuser_test_id=".$test_id."
			AND testuser_status>0
		LIMIT 1";
	if($r = F_db_query($sql, $db)) {
		if ($m = F_db_fetch_array($r)) {
			$firsttest = $m['testuser_id'];
		}
	} else {
		F_display_db_error(false);
	}
	return $firsttest;
}

/**
 * Creates a new tce_tests_logs table entry and returns inserted ID.
 * @param int $testuser_id ID of tce_tests_users
 * @param int $question_id question ID
 * @param int $score score for unanswered questions
 * @return int testlog ID
 */
function F_newTestLog($testuser_id, $question_id, $score) {
	require_once('../config/tce_config.php');
	global $db, $l;
	
	$sqll = "INSERT INTO ".K_TABLE_TESTS_LOGS." (
		testlog_testuser_id,
		testlog_question_id,
		testlog_score,
		testlog_creation_time
		) VALUES (
		".$testuser_id.",
		".$question_id.",
		".$score.",
		'".date(K_TIMESTAMP_FORMAT)."'
		)";
	if(!$rl = F_db_query($sqll, $db)) {
		F_display_db_error(false);
		F_db_query("ROLLBACK", $db); // rollback transaction
		return false;
	}
	// get inserted ID
	return F_db_insert_id($db, K_TABLE_TESTS_LOGS, 'testlog_id');
}

/**
 * Create user's test and returns TRUE on success.
 * @param int $test_id test ID.
 * @param int $user_id 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;
	
	$firsttest = 0; // id of the firts test of this type
	
	// get test data
	$testdata = F_getTestData($test_id);
	
	// 0. begin database transaction
	// Note: MySQL AUTOCOMMIT is set to 1 by default
	$sql = "START TRANSACTION";
	if(!$r = F_db_query($sql, $db)) {
		F_display_db_error(false);
		return false;
	}
	
	// 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);
		F_db_query("ROLLBACK", $db); // rollback transaction
		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);
	
	if (F_getBoolean($testdata['test_random_questions']) 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";
		if($r = F_db_query($sql, $db)) {
			while ($m = F_db_fetch_array($r)) {
				
				// 3. select questions
				// ------------------------------
				$sqlq = "SELECT question_id, question_type
					FROM ".K_TABLE_QUESTIONS."";
				if ($m['tsubset_type'] < 3) {
					$sqlq .= ", ".K_TABLE_ANSWERS."";
				}
				$sqlq .= " WHERE question_subject_id IN (
						SELECT subjset_subject_id
						FROM ".K_TABLE_SUBJECT_SET."
						WHERE subjset_tsubset_id=".$m['tsubset_id']."
						ORDER BY RAND()
					)
					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'] < 3) {
					// single question or multiple question
					// check if the selected question has enough answers
					$sqlq .= "  
						AND answer_question_id=question_id
						AND answer_enabled='1'
						AND answer_isright='1'
						AND question_id IN (
							SELECT answer_question_id
							FROM ".K_TABLE_ANSWERS."
							WHERE answer_enabled='1'";
					if ($m['tsubset_type'] == 1) {
						// single question
						$sqlq .= " AND answer_isright='0'";
					}
						$sqlq .= "  
							GROUP BY answer_question_id
							HAVING (COUNT(answer_id)>=".($m['tsubset_answers'] - 1).")
						)";
				}
				$sqlq .= " 
					GROUP BY question_id, question_type
					ORDER BY RAND() 
					LIMIT ".$m['tsubset_quantity']."";
				
				if($rq = F_db_query($sqlq, $db)) {
					while ($mq = F_db_fetch_array($rq)) {
						
						// 4. store questions
						// ------------------------------
						$testlog_id = F_newTestLog($testuser_id, $mq['question_id'], 0);
						
						$selected_questions .= ",".$mq['question_id']."";
						
						// 5. get answers
						// ------------------------------
						if ($mq['question_type'] < 3) {
							// multiple-choice question
							$answers_ids = array(); // to store answers IDs
							// select first random right answer
							$answers_ids += F_selectAnswers($mq['question_id'], true, 1, 1, 0);
							// select remaining answers
							$answers_ids += F_selectAnswers($mq['question_id'], ($m['tsubset_type'] == 1), 0, ($m['tsubset_answers'] - 1), 1, $answers_ids[0]);
							// randomizes the order of the answers
							shuffle($answers_ids);
							// add answers
							F_addLogAnswers($testlog_id, $answers_ids);
						} // -- end if multiple-choice question
						
					} // end while select questions
				} else {
					F_display_db_error(false);
					F_db_query("ROLLBACK", $db); // rollback transaction
					return false;
				} // --- end 3
				
			} // end while type of questions
		} else {
			F_display_db_error(false);
			F_db_query("ROLLBACK", $db); // rollback transaction
			return false;
		} // --- end 2
	} else {
		// questions must be the same on all tests
		// ------------------------------
		$sql = "SELECT *
			FROM ".K_TABLE_TESTS_LOGS."
			WHERE testlog_testuser_id=".$firsttest."
			ORDER BY testlog_id";
		if($r = F_db_query($sql, $db)) {
			while ($m = F_db_fetch_array($r)) {
				// copy values to new user test
				$testlog_id = F_newTestLog($testuser_id, $m['testlog_question_id'], 0);
				// copy answers entries
				$sqla = "SELECT *
					FROM ".K_TABLE_LOG_ANSWER."
					WHERE logansw_testlog_id=".$m['testlog_id']."
					ORDER BY logansw_order";
				$answers_ids = array(); // to store answers IDs
				$aswidx=1;
				if($ra = F_db_query($sqla, $db)) {
					while ($ma = F_db_fetch_array($ra)) {
						$answers_ids[$aswidx++] = $ma['logansw_answer_id'];
					}
					if (!empty($answers_ids)) {
						F_addLogAnswers($testlog_id, $answers_ids);
					}
				} else {
					F_display_db_error(false);
					F_db_query("ROLLBACK", $db); // rollback transaction
					return false;
				}
			}
		} else {
			F_display_db_error(false);
			F_db_query("ROLLBACK", $db); // rollback transaction
			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);
		F_db_query("ROLLBACK", $db); // rollback transaction
		return false;
	}
	
	// 7. commit database transaction
	// ------------------------------
	$sql = "COMMIT";
	if(!$r = F_db_query($sql, $db)) {
		F_display_db_error(false);
		return false;
	}
	
	return true;
}

/**
 * Updates question log data (register user's answers).
 * @param int $test_id test ID
 * @param int $testlog_id test log ID
 * @param int $answer_id answer ID
 * @param string $answer_text answer text
 * @return boolean TRUE in case of success, FALSE otherwise
 */
function F_updateQuestionLog($test_id, $testlog_id, $answer_id=0, $answer_text="") {
	require_once('../config/tce_config.php');
	global $db, $l;
	
	$question_type = 3; // question type
	$question_difficulty = 1; // question difficulty
	$oldtext = ""; // old text answer
	$answer_changed = false; // true when answer change
	$answer_score = 0; // answer total score
	$num_answers = 0; // counts alternative answers
	$num_selections = 0; // count selected items
	
	// get question information
	$sql = "SELECT testlog_answer_text,question_type,question_difficulty
		FROM ".K_TABLE_TESTS_LOGS.",".K_TABLE_QUESTIONS."
		WHERE testlog_question_id=question_id
			AND testlog_id=".$testlog_id."
		LIMIT 1";
	if($r = F_db_query($sql, $db)) {
		if ($m = F_db_fetch_array($r)) {
			// get previous answer text
			$oldtext = $m['testlog_answer_text'];
			$question_type = $m['question_type'];
			$question_difficulty = $m['question_difficulty'];
		}
	} else {
		F_display_db_error();
		return false;
	}
	
	if ($question_type < 3) {
		// calculate question score
		$question_score = F_getTestBasicScore($test_id) * $question_difficulty;
		
		// multiple choice question
		$sql = "SELECT *
			FROM ".K_TABLE_LOG_ANSWER.",".K_TABLE_ANSWERS."
			WHERE logansw_answer_id=answer_id
				AND logansw_testlog_id=".$testlog_id."
			ORDER BY logansw_order";
		if($r = F_db_query($sql, $db)) {
			while ($m = F_db_fetch_array($r)) {
				$num_answers++;
				// update each answer
				$sqlu = "UPDATE ".K_TABLE_LOG_ANSWER." SET";
				
				if ((($question_type == 1) AND ($answer_id == $m['logansw_answer_id'])) 
					OR (($question_type == 2) AND isset($answer_id[$m['logansw_answer_id']]) AND ($answer_id[$m['logansw_answer_id']] > 0))) {
					$sqlu .= " logansw_selected='1'";
					if (!F_getBoolean($m['logansw_selected'])) {
						$answer_changed = true;
					}
					if (F_getBoolean($m['answer_isright'])) {
						$answer_score += $question_score;
					}
					$num_selections++;
				} else {
					$sqlu .= " logansw_selected='0'";
					if (F_getBoolean($m['logansw_selected'])) {
						$answer_changed = true;
					}
					if (($question_type == 2) AND (!F_getBoolean($m['answer_isright']))) {
						$answer_score += $question_score;
					}
				}
				$sqlu .= "
					WHERE logansw_testlog_id=".$testlog_id."
						AND logansw_answer_id=".$m['logansw_answer_id']."";
				if(!$ru = F_db_query($sqlu, $db)) {
					F_display_db_error();
					return false;
				}
			}
			if ($question_type == 2) {
				// normalize score for multiple-choice questions
				$answer_score = round(($answer_score / $num_answers), 3);
			}
		} else {
			F_display_db_error();
			return false;
		}
	}
	
	// update log on answer change
	if ($answer_changed OR ($oldtext != $answer_text)) {
		if (strlen($answer_text) > 0) {
			// free text answers must be scored manually
			$answer_score = "NULL";
		}
		$change_time = "";
		if (($num_selections > 0) OR (strlen($answer_text) > 0)) {
			$change_time = date(K_TIMESTAMP_FORMAT);
		}
		$sqlu = "UPDATE ".K_TABLE_TESTS_LOGS." SET";
		$sqlu .= " testlog_answer_text=".F_empty_to_null($answer_text).",";
		$sqlu .= " testlog_score=".$answer_score.",";
		$sqlu .= " testlog_change_time=".F_empty_to_null($change_time).",";
		$sqlu .= " testlog_user_ip='".$_SERVER['REMOTE_ADDR']."'";
		$sqlu .= " WHERE testlog_id=".$testlog_id."";
		if(!$ru = F_db_query($sqlu, $db)) {
			F_display_db_error();
			return false;
		}
	}
	
	return true;
}

/**
 * Returns a formatted XHTML form code to handle the specified question.<br>
 * Form fields names are: answer_text, answer_id<br>
 * CSS classes:<ul>
 * <li>div.tcecontentbox</li>
 * <li>div.rowl</li>
 * <li>textarea.answertext</li>
 * </ul>
 * @param int $test_id test ID
 * @param int $testlog_id test log ID
 * @param string $formname form name (form ID)
 * @return string XHTML code
 */
function F_questionForm($test_id, $testlog_id, $formname) {
		require_once('../config/tce_config.php');
	require_once('../../shared/code/tce_functions_tcecode.php');
	global $db, $l, $examtime;
	
	$user_id = $_SESSION['session_user_id'];
	
	$str = "";
	
	if (!isset($test_id) OR ($test_id == 0)) {
		return;
	}
	
	// select question for the first time
	if (!isset($testlog_id) OR ($testlog_id == 0)) {
		//select first question
		$sql = "SELECT testlog_id
			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."
			ORDER BY testlog_id
			LIMIT 1";
		if($r = F_db_query($sql, $db)) {
			if($m = F_db_fetch_array($r)) {
				$testlog_id = $m['testlog_id'];
			} else {
				return;
			}
		} else {
			F_display_db_error();
		}	
	}
	
	// build selection query for question to display
	$sql = "SELECT question_description, question_type, testlog_id, testlog_testuser_id, testlog_answer_text, testlog_display_time
			FROM ".K_TABLE_QUESTIONS.",".K_TABLE_TESTS_LOGS." 
			WHERE question_id=testlog_question_id
				AND testlog_id=".$testlog_id."";
	if($r = F_db_query($sql, $db)) {
		if($m = F_db_fetch_array($r)) {
			
			$str .= "<input type=\"hidden\" name=\"testid\" id=\"testid\" value=\"".$test_id."\" />\n";
			$str .= "<input type=\"hidden\" name=\"testlogid\" id=\"testlogid\" value=\"".$testlog_id."\" />\n";
			$str .= "<input type=\"hidden\" name=\"testuser_id\" id=\"testuser_id\" value=\"".$m['testlog_testuser_id']."\" />\n";
			
			// store time information for interactive timer
			$examtime = F_getTestStartTime($m['testlog_testuser_id']) + F_getTestDuration($test_id);
			$str .= "<input type=\"hidden\" name=\"examtime\" id=\"examtime\" value=\"".$examtime."\" />\n";
			
			$str .= "<a name=\"questionsection\" id=\"questionsection\"></a>\n";
			
			$str .= "<div class=\"tcecontentbox\">\n"; //fieldset
			
			//$str .= "<legend>\n";
			//$str .= $l['w_question'];
			//$str .= "</legend>";
			
			// display question description
			if ($m['question_type'] == 3) {
				$str .= "<label for=\"answertext\">";
			}
			$str .= F_decode_tcecode($m['question_description'])."\n";
			
			if ($m['question_type'] == 3) {
				$str .= "</label>";
			}
			
			$str .= "<div class=\"row\">\n";
			$str .= "<hr/>\n";
			$str .= "</div>\n";
			
			$str .= "<div class=\"rowl\">\n";
			
			if ($m['question_type'] == 3) {
				// free text question
				$str .= "<textarea cols=\"".K_ANSWER_TEXTAREA_COLS."\" rows=\"".K_ANSWER_TEXTAREA_ROWS."\" name=\"answertext\" id=\"answertext\" class=\"answertext\">";
				$str .= $m['testlog_answer_text'];
				$str .= "</textarea>\n";
			}
			else {
				// multiple-choice question
				$checked = false;
				$str .= "<ol class=\"answer\">\n";
				
				// display answer options
				$sqla = "SELECT answer_id, answer_description, logansw_selected
					FROM ".K_TABLE_ANSWERS.",".K_TABLE_LOG_ANSWER."
					WHERE logansw_answer_id=answer_id
						AND logansw_testlog_id=".$testlog_id."
					ORDER BY logansw_order
				";
				if($ra = F_db_query($sqla, $db)) {
					while($ma = F_db_fetch_array($ra)) {
						$str .= "<li>";
						
						if ($m['question_type'] == 1) {
							// single-answer question
							$str .= "<input type=\"radio\" name=\"answerid\" id=\"answerid_".$ma['answer_id']."\" value=\"".$ma['answer_id']."\"";
						} else {
							// multiple-answer question
							$str .= "<input type=\"checkbox\" name=\"answerid[".$ma['answer_id']."]\" id=\"answerid_".$ma['answer_id']."\" value=\"".$ma['answer_id']."\"";
						}
						if (F_getBoolean($ma['logansw_selected'])) {
							$str .= " checked=\"checked\"";
							$checked = true;
						}
						$str .= " />&nbsp;";
						
						$str .= "<label for=\"answerid_".$ma['answer_id']."\">";
						$str .= F_decode_tcecode($ma['answer_description']);
						$str .= "</label>";
						$str .= "</li>\n";
					}
				}
				else {
					F_display_db_error();
				}
				
				if ($m['question_type'] == 1) {
					// display default "unanswered" option
					$str .= "<li>";
					$str .= "<input type=\"radio\" name=\"answerid\" id=\"answerid_0\" value=\"0\"";
					if (!$checked) {
						$str .= " checked=\"checked\"";
					}
					$str .= " />&nbsp;";
					
					$str .= "<label for=\"answerid_0\">";
					$str .= $l['m_unanswered'];
					$str .= "</label>";
					$str .= "</li>\n";
				}
				
				$str .= "</ol>\n";
			} // end multiple answers
			
			$str .= "</div>\n";
			
			$str .= "</div>\n"; //fieldset
			
			// display questions menu
			$str .= F_questionsMenu($test_id, $m['testlog_testuser_id'], $testlog_id, $formname);
		}
		
		if (empty($m['testlog_display_time'])) {
			// mark test as displayed:
			$sqlu = "UPDATE ".K_TABLE_TESTS_LOGS." 
				SET testlog_display_time='".date(K_TIMESTAMP_FORMAT)."' 
				WHERE testlog_id=".$testlog_id."";
			if(!$ru = F_db_query($sqlu, $db)) {
				F_display_db_error();
			}
		}
	}
	else {
		F_display_db_error();
	}
	
	return $str;
}

/**
 * Return a formatted XHTML ordered list containing test questions menu.<br>
 * @param int $test_id test ID
 * @param int $testuser_id user's test ID
 * @param string $formname form name (form ID)
 * @return string XHTML code
 */
function F_questionsMenu($test_id, $testuser_id, $testlog_id=0, $formname) {
	require_once('../config/tce_config.php');
	require_once('../../shared/code/tce_functions_tcecode.php');
	global $db, $l;
	
	$str = "";
	$testlog_id_prev = 0; // previous question ID
	$testlog_id_next = 0; // next question ID
	$testlog_id_last = 0; // temp variable
	
	$testdata = F_getTestData($test_id);
	
	$sql = "SELECT question_description, question_difficulty, testlog_id, testlog_answer_text, testlog_display_time, testlog_change_time
		FROM ".K_TABLE_QUESTIONS.", ".K_TABLE_TESTS_LOGS." 
		WHERE question_id=testlog_question_id 
			AND testlog_testuser_id=".$testuser_id."
		ORDER BY testlog_id";
	if($r = F_db_query($sql, $db)) {
		while($m = F_db_fetch_array($r)) {
			
			if ($m['testlog_id'] != $testlog_id) {
				$str .= "<li>";
				$str .= "<input type=\"submit\" name=\"jumpquestion_".$m['testlog_id']."\" id=\"jumpquestion_".$m['testlog_id']."\" value=\"&gt;\" title=\"".F_tcecodeToTitle($m['question_description'])."\" /> ";
				if ($testlog_id_last == $testlog_id) {
					$testlog_id_next = $m['testlog_id'];
				}
			}
			else {
				$str .= "<li class=\"selected\">";
				$str .= "<input type=\"button\" name=\"jumpquestion_".$m['testlog_id']."\" id=\"jumpquestion_".$m['testlog_id']."\" value=\"&gt;\" title=\"".F_tcecodeToTitle($m['question_description'])."\" disabled=\"disabled\"/> ";
				$testlog_id_prev = $testlog_id_last;
			}
			// display mark when the current question has been displayed
			$str .= "<acronym";
			if (!empty($m['testlog_display_time'])) {
				$str .= " class=\"onbox\"";
				$str .= " title=\"".$l['h_question_displayed']."\">+";
			} else {
				$str .= " class=\"offbox\"";
				$str .= " title=\"".$l['h_question_not_displayed']."\">-";
			}
			$str .= "</acronym>";
			
			$str .= "&nbsp;";
			
			// show mark when the current question has been answered
			$str .= "<acronym";
			if (!empty($m['testlog_change_time'])) {
				$str .= " class=\"onbox\"";
				$str .= " title=\"".$l['h_question_answered']."\">+";
			} else {
				$str .= " class=\"offbox\"";
				$str .= " title=\"".$l['h_question_not_answered']."\">-";
			}
			$str .= "</acronym>";
			
			$str .= "&nbsp;";
			
			
			// show question score
			$n_question_score = $testdata['test_score_right'] * $m['question_difficulty'];
			$str .= "<acronym class=\"offbox\" title=\"".$l['w_max_score'].": ".$n_question_score."\">";
			$str .= sprintf("% 5.1f", $n_question_score);
			$str .= "</acronym>";
			
			$str .= "&nbsp;";
			
			if ($testlog_id == 0) {
				$testlog_id = $m['testlog_id'];
				$testlog_id_last = $testlog_id;
			}
			
			$testlog_id_last = $m['testlog_id'];
			$str .= F_tcecodeToLine($m['question_description']);
			$str .= "</li>\n";
		}
	}
	else {
		F_display_db_error();
	}
	
	// build quick navigator links (previous - next)
	$navlink = "";
	
	if (($testlog_id_prev > 0) AND ($testlog_id_prev < $testlog_id)) {
		$navlink .= "<input type=\"submit\" name=\"prevquestion\" id=\"prevquestion\" value=\"&lt; ".$l['w_previous']."\" title=\"".$l['w_previous']."\" />";
	}
	else {
		$navlink .= "<input type=\"button\" name=\"prevquestion\" id=\"prevquestion\" value=\"&lt; ".$l['w_previous']."\" title=\"".$l['w_previous']."\" disabled=\"disabled\" />";
	}
	$navlink .= "<input type=\"submit\" name=\"confirmanswer\" id=\"confirmanswer\" value=\"".$l['w_confirm']."\" />";
	if ($testlog_id_next > 0) {
		$navlink .= "<input type=\"submit\" name=\"nextquestion\" id=\"nextquestion\" value=\"".$l['w_next']." &gt;\" title=\"".$l['w_next']."\" />\n";
	}
	else {
		$navlink .= "<input type=\"button\" name=\"nextquestion\" id=\"nextquestion\" value=\"".$l['w_next']." &gt;\" title=\"".$l['w_next']."\" disabled=\"disabled\" />\n";
	}
	
	$navlink .= "<input type=\"hidden\" name=\"prevquestionid\" id=\"prevquestionid\" value=\"".$testlog_id_prev."\" />\n";
	$navlink .= "<input type=\"hidden\" name=\"nextquestionid\" id=\"nextquestionid\" value=\"".$testlog_id_next."\" />\n";
	
	$navlink = "<div class=\"navlink\">".$navlink."</div>\n";
	
	$rstr = "";
	
	if (strlen($str) > 0) {
		$rstr .= "<br />\n";
		$rstr .= $navlink;
		$rstr .= "<br />\n";
		$rstr .= "<a name=\"questionssection\" id=\"questionssection\"></a>\n";
		$rstr .= "<div class=\"tcecontentbox\">\n"; //fieldset
		//$rstr .= "<legend>";
		$rstr .= $l['w_questions'];
		//$rstr .= "</legend>\n";
		$rstr .= "<ol class=\"qlist\">\n".$str."</ol>\n";
		$rstr .= "</div>\n"; //fieldset
		$rstr .= "<br />\n";
	}
	
	return $rstr;
}

/**
 * Display a textarea for user's comment.<br>
 * @param int $testid test ID
 * @return string XHTML code
 * @since 4.0.000 (2006-10-01)
 */
function F_testComment($testid) {
	require_once('../config/tce_config.php');
	global $db, $l;
	
	$str = "";
	// user's comment
	if (K_TEST_COMMENT) {
		// get user's test comment
		$comment = "";
		$sql = "SELECT testuser_comment
		FROM ".K_TABLE_TEST_USER."
		WHERE testuser_user_id=".$_SESSION['session_user_id']."
			AND testuser_test_id=".$testid."
		LIMIT 1";
		if($r = F_db_query($sql, $db)) {
			if($m = F_db_fetch_array($r)) {
				$comment = $m['testuser_comment'];
			}
		} else {
			F_display_db_error();
		}
		$str .= "<label for=\"testcomment\">".$l['w_comment']."</label><br />";
		$str .= "<textarea cols=\"".K_ANSWER_TEXTAREA_COLS."\" rows=\"4\" name=\"testcomment\" id=\"testcomment\" class=\"answertext\" title=\"".$l['h_testcomment']."\">".$comment."</textarea><br />\n";
	}
	return $str;
}

/**
 * Updates user's test comment.<br>
 * @param int $testid test ID
 * @param string $comment user's comment.
 * @return string XHTML code
 * @since 4.0.000 (2006-10-01)
 */
function F_updateTestComment($testid, $testcomment) {
	require_once('../config/tce_config.php');
	global $db, $l;
	
	$sql = "UPDATE ".K_TABLE_TEST_USER."
		SET testuser_comment='".$testcomment."'
		WHERE testuser_test_id=".$testid."
			AND testuser_user_id=".$_SESSION['session_user_id']."";
	if(!$r = F_db_query($sql, $db)) {
		F_display_db_error();
	}
}

//============================================================+
// END OF FILE                                                 
//============================================================+
?>
