<?php
//============================================================+
// File name   : tce_functions_auth_sql.php
// Begin       : 2006-03-11
// Last Update : 2006-09-29
// 
// Description : Functions to select topics.
//
// 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 select topics.
 * @package com.tecnick.tcexam.admin
 * @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 2006-03-11
 */

/**
 * Returns a SQL string to select subjects accounting for user authorizations.
 * @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 2006-03-12
 * @param $andwhere additional WHERE statements (e.g.: "subject_enabled='1'")
 * @return string sql statement
 */
function F_select_subjects_sql($andwhere="") {
	global $l;
	require_once('../config/tce_config.php');
	
	if ($_SESSION['session_user_level'] >= K_AUTH_ADMINISTRATOR) {
		$sql = "SELECT * FROM ".K_TABLE_SUBJECTS."";
		if (!empty($andwhere)) {
			$sql .= " WHERE ".$andwhere;
		}
		$sql .= " ORDER BY subject_name";
	} else {
		$sql = "SELECT *
			FROM ".K_TABLE_SUBJECTS."
			WHERE subject_user_id IN (".F_getAuthorizedUsers($_SESSION['session_user_id']).")";
			if (!empty($andwhere)) {
				$sql .= " AND ".$andwhere;
			}
			$sql .= " ORDER BY subject_name";
	}
	return $sql;
}

/**
 * Returns a SQL string to select tests accounting for user authorizations.
 * @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 2006-03-12
 * @return string sql statement
 */
function F_select_tests_sql() {
	global $l;
	require_once('../config/tce_config.php');
	
	if ($_SESSION['session_user_level'] >= K_AUTH_ADMINISTRATOR) {
		$sql = "SELECT * 
			FROM ".K_TABLE_TESTS."
			ORDER BY test_begin_time DESC, test_name";
	} else {
		$sql = "SELECT * 
			FROM ".K_TABLE_TESTS.",".K_TABLE_USERS."
			WHERE test_user_id IN (".F_getAuthorizedUsers($_SESSION['session_user_id']).")
			ORDER BY test_begin_time DESC, test_name";
	}
	return $sql;
}

/**
 * Returns a SQL string to select executed user tests accounting for user authorizations.
 * @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 2006-03-12
 * @return string sql statement
 */
function F_select_executed_tests_sql() {
	global $l;
	require_once('../config/tce_config.php');
	
	if ($_SESSION['session_user_level'] >= K_AUTH_ADMINISTRATOR) {
		$sql = "SELECT DISTINCT *
			FROM ".K_TABLE_TESTS.", ".K_TABLE_TEST_USER." 
			WHERE test_id=testuser_test_id
				AND testuser_status>0
			ORDER BY test_begin_time DESC, test_name";
	} else {
		$sql = "SELECT DISTINCT * 
			FROM ".K_TABLE_TESTS.", ".K_TABLE_TEST_USER.", ".K_TABLE_USERS."
			WHERE test_id=testuser_test_id
				AND test_user_id=user_id 
				AND testuser_status>0
				AND test_user_id IN (".F_getAuthorizedUsers($_SESSION['session_user_id']).")
			ORDER BY test_begin_time DESC, test_name";
	}
	return $sql;
}

/**
 * Returns a SQL string to select executed tests accounting for user authorizations.
 * @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 2006-06-26
 * @return string sql statement
 */
function F_select_executed_distinct_tests_sql() {
	global $l;
	require_once('../config/tce_config.php');
	
	if ($_SESSION['session_user_level'] >= K_AUTH_ADMINISTRATOR) {
		$sql = "SELECT *
			FROM ".K_TABLE_TESTS."
			WHERE test_id IN (
				SELECT testuser_test_id
				FROM ".K_TABLE_TEST_USER." 
				WHERE testuser_status>0
			)
			ORDER BY test_begin_time DESC, test_name";
	} else {
		$sql = "SELECT * 
			FROM ".K_TABLE_TESTS."
			WHERE test_id IN (
				SELECT testuser_test_id
				FROM ".K_TABLE_TEST_USER.", ".K_TABLE_USERS."
				WHERE test_user_id=user_id 
					AND testuser_status>0
					AND test_user_id IN (".F_getAuthorizedUsers($_SESSION['session_user_id']).")
			)
			ORDER BY test_begin_time DESC, test_name";
	}
	return $sql;
}

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