<?php

/* Config File */

/* Constants */

//environment constants
@define('CLASSES_PATH', 'classes/');
@define('BACKUP_FOLDER', 'downloads/');
@define('IMAGE_FOLDER', 'images/qcm/');
@define('DB_EXT_FILE', 'sql');
@define('SETTINGS_FILE', 'settings.php');
@define('CHANGELOG_FILE', 'changelog');
@define('MAX_UPLOAD_SIZE', 10000000);
@define('MAX_IMAGE_WIDTH', 500);
@define('DOMAIN', str_replace('www.', '', $_SERVER['SERVER_NAME']));
@define('FILE_DATE_FORMAT', 'j.m.Y_H\hi');
@define('LAYOUT_DATE_FORMAT', 'd/m/Y - H\hi');

//version constants
@define('STABLE_VERSION', '2.2');
@define('CURRENT_VERSION_REV', 1);
@define('NEXT_VERSION', '2.3');
@define('CURRENT_VERSION', STABLE_VERSION.'.'.CURRENT_VERSION_REV);
@define('IS_BETA', CURRENT_VERSION_REV>0);

//remote actions constants
@define('ACCESS_POINT_DB', 'access_point');
@define('ACCESS_POINT_LAST_VERSION', ACCESS_POINT_DB.'_last_version');
@define('ACCESS_POINT_NEW_VERSION', ACCESS_POINT_DB.'_new_version');
@define('CREATE_NEW_BACKUP', 'create_backup');

//Cookie names constants
@define('EXPECTED_PAGE', 'backOnTrack');

//database constants
@define('QCM_TABLE', 'qcm');
@define('USER_TABLE', 'user');
@define('RESULT_TABLE', 'result');
@define('QUESTION_TABLE', 'question');
@define('ANSWER_TABLE', 'answer');
@define('CHANGELOG_TABLE', 'changelog');
@define('FEED_TABLE', 'feed');
@define('NEWS_TABLE', 'news');
@define('RIGHT_ANSWER', 'right_'.ANSWER_TABLE);
@define('DEFAULT_QCM_NAME', 'pas de nom');
@define('END_OF_QUERY', '/* -END OF QUERY- */');
@define('TIMEZONE', 'Europe/Paris');
@define('TEXT_ENC', 'UTF-8');
@define('DB_ENC', 'utf8mb4');

//display constants
@define('DEFAULT_LOGGED_PAGE', 'frontal');
@define('DEFAULT_QUESTION_INPUT', 'Tapez ici la question...');
@define('DEFAULT_ANSWER_INPUT', 'Tapez ici une reponse...');
@define('ADD_IMAGE_TEXT', 'Ajouter une image &agrave; la question');
@define('GRADE_CLASS_SEPARATOR', 'eme');
@define('DECIMAL_LIMIT', 2);
@define('OS_WINDOWS', 'win');
@define('OS_MAC', 'mac');
@define('OS_LINUX', 'linux');

/* Required Classes */

//require_once CLASSES_PATH.'qcm.php';
require_once CLASSES_PATH.'page_switch.php';
require_once CLASSES_PATH.'input_box.php';

/* Server & Admin Settings */

if(file_exists(SETTINGS_FILE))
{
	require_once SETTINGS_FILE;
}

/* Integrity Functions */

function getFiles($sFileName=false)
{
	$asFiles =array('account.php'		=> true,
					'account_admin.php'	=> true,
					'backUpCreator.php'	=> true,
					'frontal.php'		=> true, 
					'functions.js'		=> true,
					'index.php'			=> true, 
					'install.php'		=> true,
					'logon.php'			=> true,
					'qcmCreator.php'	=> true,
					'qcmProcess.php'	=> true,
					'qcmReader.php'		=> true,
					'qcmResults.php'	=> true,
					'register.php'		=> true,
					'rss.php'			=> true,
					'search.php'		=> true,
					'stats.php'			=> true,
					'statsAdmin.php'	=> true,
					'style.css'			=> true,
					'styleIe.css'		=> false,
					'version.php'		=> false,
					'test.php'			=> false);
	return (!$sFileName)?$asFiles:$asFiles[$sFileName];
}
function getPageDescription($sPageName=false)
{
	$asFiles =array('account'				=> 'Param&egrave;tres utilisateur',
					'account_admin'			=> 'Comptes &eacute;l&egrave;ves',
					'backUpCreator'			=> 'Gestion des sauvegardes',
					DEFAULT_LOGGED_PAGE		=> 'Accueil', 
					'index'					=> 'Index', 
					'install'				=> 'Param&egrave;tres d\'installation',
					'logon'					=> 'Connexion',
					'qcmCreator'			=> 'Formulaire de cr&eacute;ation de QCM',
					'qcmProcess'			=> 'Enregistrement du QCM dans la base de donn&eacute;es',
					'qcmReader'				=> 'Evaluation d\'un QCM',
					'qcmResults'			=> 'R&eacute;sultat du QCM',
					'register'				=> 'Cr&eacute;er un nouveau compte',
					'rss'					=> 'flux rss',
					'search'				=> 'Recherche',
					'stats'					=> 'Statistiques',
					'statsAdmin'			=> 'Statistiques [vue admin]',
					'version'				=> 'Logs et t&eacute;l&eacute;chargement',
					'test'					=> 'Tests Requ&ecirc;te SQL',
					'logout'				=> 'D&eacute;connexion',
					'download'				=> 'T&eacute;l&eacute;chargement de fichier',
					'remote'				=> 'Action &agrave; distance');
	return (!$sPageName)?$asFiles:$asFiles[$sPageName];
}
function checkIntegrity()
{
	foreach(getFiles() as $sFileName=>$bMandatory)
	{
		if($bMandatory && !file_exists($sFileName))
		{
			$asMissingFiles[] = 'Le fichier <strong>'.$sFileName.'</strong> est manquant';
		}
	}
	return isset($asMissingFiles)?$asMissingFiles:true;
}

/* Database Functions */

//Database construction functions
function connection()
{
	$asMissingFiles = checkIntegrity();
	if(is_array($asMissingFiles))
	{
		$sPlural = count($asMissingFiles)>1?'s':'';
		array_unshift($asMissingFiles, 'Fichier'.$sPlural.' manquant'.$sPlural, '');
		addMessage(getError($asMissingFiles));
		return false;
	}
	elseif(!defined('READY_TO_USE')) //settings.php ready to use
	{
		$_GET['page'] = 'install';
		return false;
	}
	$oConnection = mysqli_connect(DB_SERVER, DB_LOGIN, DB_PASS);
	if(!$oConnection)
	{
		addMessage(getError(array('La connexion &agrave; la base de donn&eacute;e a &eacute;chou&eacute;e', 'V&eacute;rifiez les param&egrave;tres dans '.SETTINGS_FILE, 'D&eacute;tails : '.mysqli_connect_error()), true));
	}
	elseif(!mysqli_select_db($oConnection, DB_NAME))
	{
		addMessage(getError(array('Impossible de s&eacute;lectionner la base de donn&eacute;es "'.DB_NAME.'"', 'Changez les constantes dans le fichier "'.SETTINGS_FILE.'" ou r&eacute;installer la base de donn&eacute;es'), true));
		$_GET['page'] = 'install';
	}
	return $oConnection;
}

function setContext()
{
	global $oConnection;
	
	//php settings
	date_default_timezone_set(TIMEZONE);
	ini_set('default_charset', TEXT_ENC);
	header('Content-Type: text/html; charset='.TEXT_ENC);
	mb_internal_encoding(TEXT_ENC);
	mb_http_output(TEXT_ENC);
	mb_http_input(TEXT_ENC);
	mb_language('uni');
	mb_regex_encoding(TEXT_ENC);
	
	//Characters encoding
	mysqli_set_charset($oConnection, DB_ENC);
	
	//Time zone
	$oNow = new DateTime();
	$iMins = $oNow->getOffset() / 60;
	$iSign = ($iMins < 0)?-1:1;
	$iMins = abs($iMins);
	$iHours = floor($iMins / 60);
	$iMins -= $iHours * 60;
	$sOffset = sprintf('%+d:%02d', $iHours*$iSign, $iMins);
	setQuery("SET time_zone='{$sOffset}';");
}

function getTables()
{
	return array(QCM_TABLE, USER_TABLE, RESULT_TABLE, QUESTION_TABLE, ANSWER_TABLE, CHANGELOG_TABLE, FEED_TABLE, NEWS_TABLE);
}
function getQcmTables()
{
	return array(QCM_TABLE, QUESTION_TABLE, ANSWER_TABLE, RESULT_TABLE);
}
function getChangeLogEnum()
{
	return array('bug fix', 'new feature', 'design');
}
function getTableColumns($sTable, $sColumnName=false)
{
	$asTableColumns = array('id_'.$sTable => "int(10) UNSIGNED NOT NULL auto_increment");
	switch($sTable)
	{
		case QCM_TABLE :
			$asTableColumns['id_'.USER_TABLE] = "int(10) UNSIGNED NOT NULL DEFAULT '0'";
			$asTableColumns[QCM_TABLE] = "varchar(255) NOT NULL DEFAULT '".DEFAULT_QCM_NAME."'";
			$asTableColumns['valid'] = "tinyint(1) DEFAULT '0'";
			break;
		case USER_TABLE :
			$asTableColumns[USER_TABLE.'_first_name'] = "varchar(20) NOT NULL";
			$asTableColumns[USER_TABLE.'_last_name'] = "varchar(20) NOT NULL";
			$asTableColumns['grade'] = "varchar(3)";
			$asTableColumns['class'] = "varchar(3)";
			$asTableColumns['pass'] = "varchar(100) NOT NULL";
			$asTableColumns['admin'] = "tinyint(1) DEFAULT '0'";
			break;
		case RESULT_TABLE :
			$asTableColumns['id_'.USER_TABLE] = "int(10) UNSIGNED NOT NULL";
			$asTableColumns['id_'.QCM_TABLE] = "int(10) UNSIGNED NOT NULL";
			$asTableColumns[RESULT_TABLE] = "float(5)";
			break;
		case QUESTION_TABLE :
			$asTableColumns['id_'.QCM_TABLE] = "int(10) UNSIGNED NOT NULL";
			$asTableColumns[QUESTION_TABLE] = "longtext NOT NULL";
			$asTableColumns['image'] = "varchar(100)";
			break;
		case ANSWER_TABLE :
			$asTableColumns['id_'.QUESTION_TABLE] = "int(10) UNSIGNED NOT NULL";
			$asTableColumns['id_'.QCM_TABLE] = "int(10) UNSIGNED NOT NULL";
			$asTableColumns[ANSWER_TABLE] = "longtext";
			$asTableColumns[RIGHT_ANSWER] = "tinyint(1) DEFAULT '0'";
			break;
		case CHANGELOG_TABLE :
			$asTableColumns[CHANGELOG_TABLE] = "longtext NOT NULL";
			$asTableColumns['version'] = "decimal(4, 1)";
			$asTableColumns['type'] = "enum(".encapsulate(getChangeLogEnum(), "'", "'", ", ").")";
			$asTableColumns['progress'] = "tinyint(1) default 0";
			break;
		case FEED_TABLE :
			$asTableColumns['author'] = "varchar(100) NOT NULL";
			$asTableColumns['action'] = "varchar(15) NOT NULL";
			$asTableColumns['source'] = "varchar(255) NOT NULL";
			$asTableColumns['type'] = "varchar(100) NOT NULL";
			$asTableColumns['old_value'] = "varchar(255)";
			$asTableColumns['new_value'] = "varchar(255)";
			$asTableColumns['id_ref'] = "int(10) UNSIGNED DEFAULT 0";
			break;
		case NEWS_TABLE:
			$asTableColumns['id_'.CHANGELOG_TABLE] = "int(10) UNSIGNED NOT NULL";
			$asTableColumns['id_'.USER_TABLE] = "int(10) UNSIGNED NOT NULL";
			break;
		default :
			return false;
	}
	$asTableColumns['led'] = "TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP";
	return (!$sColumnName)?$asTableColumns:$asTableColumns[$sColumnName];
}
function getTableConstraints($sTable)
{
	//primary key
	$asTableConstraints = array('PRIMARY' => "PRIMARY KEY (`id_$sTable`)");

	//other constraints
	//TODO foreign key
	switch($sTable)
	{
		case QCM_TABLE :
			break;
		case USER_TABLE :
			$asTableConstraints['UNIQUE'] = "UNIQUE KEY `user_first_and_last_name` (`".USER_TABLE."_first_name`, `".USER_TABLE."_last_name`)";
			break;
		case RESULT_TABLE :
			break;
		case QUESTION_TABLE :
			break;
		case ANSWER_TABLE :
			break;
		case CHANGELOG_TABLE :
			break;
		case FEED_TABLE :
			break;
	}
	return $asTableConstraints;
}
function getQuote($sTable, $sColumnName)
{
	$sDesc = getTableColumns($sTable,$sColumnName);
	$sType = substr($sDesc, 0, min(strpos($sDesc.'(', '('), strpos($sDesc.' ', ' ')));
	$asNumericTypes = array('int', 'tinyint', 'decimal', 'float');
	return in_array($sType, $asNumericTypes)?"":"'";
}
function getInstallQuery($sTable)
{
	$asTableColumns = getTableColumns($sTable);
	if(!$asTableColumns)
	{
		return false;
	}
	else
	{
		$sQuery = "\n".implodeAll($asTableColumns, "` ", "\n", "`", ",")."\n".implode(", \n", getTableConstraints($sTable));
		return "CREATE /* ".basename(__FILE__)." ".__LINE__." */ TABLE `{$sTable}` ({$sQuery})";
	}
}
function install(&$oConnection, $sDbName, $asAdminsInfo, $bDropDb=true)
{

	$bSuccess = false;
	if($bDropDb)
	{
		setQuery("DROP DATABASE IF EXISTS ".$sDbName);
	}
	if(!setQuery("CREATE /* ".basename(__FILE__)." ".__LINE__." */ DATABASE ".$sDbName, false)) //create database
	{
		addMessage(getError(array(	'Base de donn&eacute;es d&eacute;j&agrave; pr&eacute;sente', 
									'Choisissez un autre nom de base de donn&eacute;s ou supprimez la', 
									'D&eacute;tails : '.mysqli_error($oConnection)), true));
	}
	elseif(!mysqli_select_db($oConnection, $sDbName)) //select database
	{
		addMessage(getError(array('S&eacute;lection de la database "'.$sDbName.'" impossible', 'D&eacute;tails : '.mysqli_error($oConnection)), true));
	}
	else
	{
		//create tables
		$asTableNames = getTables();
		array_walk(array_map('getInstallQuery', $asTableNames), 'setQuery');
		
		//insert admin users and a test user
		foreach($asAdminsInfo as $asAdminInfo)
		{
			insertRow(USER_TABLE, $asAdminInfo);
		}
		
		//initial settings
		insertUpdateRow(USER_TABLE, 
						array(	USER_TABLE.'_last_name'=>'test',
								USER_TABLE.'_first_name'=>'test',
								'grade'=>'6',
								'class'=>'1',
								'pass'=>encryptPassword('test'),
								'admin'=>0), 
						array(USER_TABLE.'_last_name', USER_TABLE.'_first_name'));

						//TODO insert Qcm at once use a qcm class
		$iQcmId = insertRow(QCM_TABLE, array(1, 'Qcm test', 1));
		$iQuestionId = insertRow(QUESTION_TABLE, array($iQcmId, 'Quel est la couleur du cheval blanc d\'Henry 4 ?', 'qcm1question1.jpeg'));
		insertRow(ANSWER_TABLE, array($iQuestionId, $iQcmId, 'Rouge !', '0'));
		insertRow(ANSWER_TABLE, array($iQuestionId, $iQcmId, 'Vert', '0'));
		insertRow(ANSWER_TABLE, array($iQuestionId, $iQcmId, 'Bleu', '0'));
		insertRow(ANSWER_TABLE, array($iQuestionId, $iQcmId, 'Blanc', '1'));

		//insert changelog
		if(!setChangeLog())
		{
			addMessage(getWarning('Fichier de changelog introuvable'));
		}
		$bSuccess = true;
	}
	return $bSuccess;
}
function setChangeLog()
{
	$bChangeLogFileExists = file_exists(CHANGELOG_FILE);
	if($bChangeLogFileExists)
	{
		$sChangeLog = file_get_contents(CHANGELOG_FILE);
		$asOrder = array('led', 'version', 'type', CHANGELOG_TABLE, 'progress');
		$asChangeLogs = array_filter(explode("\n", str_replace("\r", '', $sChangeLog)));
		foreach($asChangeLogs as $sChangeLogRow)
		{
			$asChangeLogRow = explode("\t", $sChangeLogRow);
			$asChangeLogRow[] = 1;
			insertRow(CHANGELOG_TABLE, array_combine($asOrder, $asChangeLogRow));
		}
	}
	return $bChangeLogFileExists;
}
function publishChangeLog($iChangeLogId)
{
	$asAdmins = selectRows(array('select'=>array('id_'.USER_TABLE), 'from'=>USER_TABLE, 'constraint'=>array('admin'=>1)));
	foreach($asAdmins as $asInfo)
	{
		$iUserId = $asInfo['id_'.USER_TABLE];
		insertRow(NEWS_TABLE, array('id_'.USER_TABLE=>$iUserId, 'id_'.CHANGELOG_TABLE=>$iChangeLogId));
	}
	return !empty($asAdmins);
}
function getCleanPublication($iUserId)
{
	$asNews = selectRows(array('from'=>NEWS_TABLE, 'constraint'=>array('id_user'=>$iUserId)));
	$asNewsPanel = array(); 
	foreach($asNews as $asNews)
	{
		$iChangelogId = $asNews['id_'.CHANGELOG_TABLE];
		$asChangelog = selectRow(CHANGELOG_TABLE, array('id_'.CHANGELOG_TABLE=>$iChangelogId), array('changelog', 'type'));
		$asNewsPanel[] = getSuccess(array(	'Message personnel',
											getHtml(date(LAYOUT_DATE_FORMAT, strtotime($asNews['led'])), 'span', '', 'font-size:12px'), 
											'Le feedback n&#176;'.$iChangelogId.' ('.$asChangelog['type'].') a &eacute;t&eacute; trait&eacute; : ', 
											$asChangelog['changelog'].'.', 
											'-- L\'&eacute;quipe Admin'));
		deleteRow(NEWS_TABLE, $asNews['id_'.NEWS_TABLE]);
	}
	return implode("\n", $asNewsPanel);
}
function getTableColumnNames($sTableName, $iTableColumn=false, $bTableId=true, $bTableLed=true)
{
	$asTableNames = getTableColumns($sTableName);
	if(!$asTableNames)
	{
		return false;
	}
	if(!$bTableLed)
	{
		unset($asTableNames['led']);
	}
	if(!$bTableId)
	{
		unset($asTableNames['id_'.$sTableName]);
	}
	$aiTableColumnNames = array_keys($asTableNames);
	return ($iTableColumn!==false)?$aiTableColumnNames[$iTableColumn]:$aiTableColumnNames;
}
function getId($sTableName)
{
	return $sTableName.'.id_'.$sTableName;
}

//Back up functions
function getMaxIncrementedValue($sTable)
{
	return selectValue($sTable, "MAX(".getId($sTable).")");
}
function createBackUp($bDropDb)
{
	$sFileName = DB_NAME.'_'.date(FILE_DATE_FORMAT);
	
	$sQuery  = "/* ID ".$sFileName." */";
	$sQuery .= "\n\n"."SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\";".END_OF_QUERY;
	$sQuery .= $bDropDb?"\n\n"."DROP DATABASE `".DB_NAME."`;".END_OF_QUERY:"";
	$sQuery .= "\n\n"."CREATE DATABASE `".DB_NAME."` DEFAULT CHARACTER SET ".DB_ENC.";".END_OF_QUERY;
	$sQuery .= "\n\n"."USE `".DB_NAME."`;".END_OF_QUERY;
	
	foreach(getTables() as $sTable)
	{
		$sQuery .= "\n\n".getUniqueValue("SHOW CREATE TABLE ".$sTable, 'Create Table').";".END_OF_QUERY;
		$oTable = getQuery("SELECT * FROM `{$sTable}`");
		if(mysqli_num_rows($oTable) > 0)
		{
			$iColumnNb = mysqli_num_fields($oTable);
			$sQuery .= "\n\n"."INSERT INTO `{$sTable}` \n(";
			
			$asFields = array();
			for($iFieldNb=0; $iFieldNb<$iColumnNb; $iFieldNb++) $asFields[] = mysqli_fetch_field_direct($oTable, $iFieldNb)->name;
			$sQuery .= encapsulate($asFields, "`", false, ", ");
			
			$sQuery .= ") \nVALUES\n";

			$asValues = array();
			while ($asTableRow = mysqli_fetch_array($oTable, MYSQLI_ASSOC))
			{
				cleanSql($asTableRow);
				$asValues[] = "(".encapsulate($asTableRow, "'", false, ", ").")";
			}
			$sQuery .= implode(",\n", $asValues).";".END_OF_QUERY;
		}
	}
	$sFilePath = BACKUP_FOLDER.$sFileName.'.'.DB_EXT_FILE;
	$bResult = file_put_contents($sFilePath, $sQuery);
	if($bResult)
	{
		addMessage(getSuccess('Back up ajout&eacute;'));
		addFeed('CREATE', DB_NAME, 'database', $sFilePath);
	}
	else
	{
		addMessage(getError(array(	'Impossible de cr&eacute;er un nouveau fichier de sauvegarde', 
									'V&eacute;rifier les droits d\'&eacute;criture du dossier '.BACKUP_FOLDER)));
	}
	return $bResult;
}
function restoreBackUp($sBackFilePath, &$oConnection)
{
	$bResult = false;
	if(checkBackup($sBackFilePath))
	{
		$sFileContents = file_get_contents($sBackFilePath);
		mysqli_close($oConnection);
		$oConnection = mysqli_connect(DB_SERVER, DB_LOGIN, DB_PASS);
		setContext($oConnection);
		$asQueries = array_filter(explode(END_OF_QUERY, str_replace(array("\r\n", "\n"), '', $sFileContents)));
		foreach($asQueries as $sQuery)
		{
			setQuery($sQuery);
		}
		mysqli_select_db($oConnection, DB_NAME);
		addFeed('RESTORE', DB_NAME, 'database', $sBackFilePath);
		addMessage(getSuccess(array('Base restor&eacute;e', 'Fichier charg&eacute; : '.$sBackFilePath, getHtml('Pour des raisons de s&eacute;curit&eacute;, vous allez &ecirc;tre d&eacute;connect&eacute;', 'strong'))));
		logMeOut();
		$bResult = true;
	}
	return $bResult;
}
function checkBackup($sFilePath)
{
	$bValidBackup = true;
	$sFileContents = file_get_contents($sFilePath);
	foreach(getTables() as $sTable)
	{
		//Check if table exists
		if(getUniqueValue("SHOW TABLES LIKE '$sTable'")!='')
		{
			$sCreateTableQuery = getUniqueValue("SHOW CREATE TABLE ".$sTable, 'Create Table');
			$sCreateTableQuery = substr($sCreateTableQuery, 0, strpos($sCreateTableQuery,'ENGINE'));
			if(strpos($sFileContents, $sCreateTableQuery)===false)
			{
				$iStart = strpos($sFileContents, 'CREATE TABLE `'.$sTable.'`');
				if($iStart===false)
				{
					file_put_contents($sFilePath, $sFileContents."\n".$sCreateTableQuery.";", FILE_APPEND);
					addMessage(getWarning(array('La sauvegarde ne comprends pas la table "'.$sTable.'"', 'Celle-ci a &eacute;t&eacute; ajout&eacute;e')));
				}
				else
				{
					$sStart = substr($sFileContents, $iStart);
					$sBackupTableCreation = substr($sStart, 0, strpos($sStart, 'ENGINE'));
					addMessage(getError(array('La base de donn&eacute;e n\'est pas compatible', 'Erreur sur la table "'.$sTable.'"', 'Config'=>$sCreateTableQuery, 'Backup'=>$sBackupTableCreation)));
					$bValidBackup = false;
				}
			}
		}
	}
	return $bValidBackup;
}
function uploadBackUp($asFile, $bRestoreBackUp, &$oConnection)
{
	$bResult = false;
	if(uploadFile($asFile, BACKUP_FOLDER, array(DB_EXT_FILE)))
	{
		$sFilePath = BACKUP_FOLDER.$asFile['name'];
		if(checkBackup($sFilePath))
		{
			addMessage(getSuccess('Upload termin&eacute;'));
			addFeed('UPLOAD', DB_NAME, 'file', $asFile['name']);
			$bResult = true;
			if($bRestoreBackUp)
			{
				$bResult = restoreBackUp($sFilePath, $oConnection);
			}
		}
		else
		{
			unlink($sFilePath);
		}
	}
	return $bResult;
}
function getKeyRing($asAuth=array())
{
	switch(count($asAuth))
	{
		case 3:
			$sLastName = $asAuth['lastName'];
			$sFirstName = $asAuth['firstName'];
			$sPass = $asAuth['pass'];
			break;
		case 0:
			$sLastName = $_SESSION['lastName'];
			$sFirstName = $_SESSION['firstName'];
			$sPass = selectValue(USER_TABLE, 'pass', array('id_'.USER_TABLE=>$_SESSION['user'], 'admin'=>1));
			break;
		default:
			return false;
	}
	return encodeUrl(serialize(array('lastname'=>$sLastName, 'firstname'=>$sFirstName, 'pass'=>$sPass)));
}
function syncBackUp($sUrl, $bInternalLink, $sAuth, $bRestoreBackUp, &$oConnection)
{
	$bResult = false;
	$sDomain = $sUrl;
	if($bInternalLink)
	{
		//authorization
		$sUrl .= '&auth='.$sAuth;
		$sFileName = 'sync_'.date(FILE_DATE_FORMAT).'.'.DB_EXT_FILE;
	}
	else
	{
		$sFileName = basename($sUrl);
	}

	$sFilePath = BACKUP_FOLDER.$sFileName;
	$sContent = file_get_contents($sUrl);
	$iFileTimeStamp = getTimeStampFromBackUp($sContent);
	if(!isset($sContent) || $sContent=='')
	{
		addMessage(getError(array('Fichier introuvable sur le point d\'acc&egrave;s', 'url : '.$sUrl)));
	}
	elseif($iFileTimeStamp==0)
	{
		addMessage(getError('Fichier incompatible (date illisible)'));
	}
	elseif(!file_put_contents($sFilePath, $sContent))
	{
		addMessage(getError(array('Impossible de cr&eacute;er un nouveau fichier de sauvegarde', 'V&eacute;rifier les droits d\'&eacute;criture du dossier '.BACKUP_FOLDER)));
	}
	else
	{
		$bResult = true;
		addFeed('UPLOAD', $sDomain, 'sync', $sFileName);
		addMessage(getSuccess(array('Back up ajout&eacute;', 'fichier : '.$sFileName, 'Date de la sauvegarde : '.date(LAYOUT_DATE_FORMAT, $iFileTimeStamp))));
	}

	if($bResult && $bRestoreBackUp)
	{
		$iServerLastBackTimeStamp = getLastBackUp('timeStamp');
		if($iServerLastBackTimeStamp > $iFileTimeStamp)
		{
			$bResult = false;
			addMessage(getError(array(	'Sauvegarde trop ancienne',
										'Date de la sauvegarde du point d\'acc&egrave;s : '.date(LAYOUT_DATE_FORMAT, $iFileTimeStamp),
										'Derni&egrave;re sauvegarde disponible sur le serveur : '.date(LAYOUT_DATE_FORMAT, $iServerLastBackTimeStamp))));
		}
		else
		{
			$bResult = restoreBackUp($sFilePath, $oConnection);
		}
	}
	return $bResult;
}
function resetDatabase(&$oConnection)
{
	$asAdminsInfo = selectRows(array('select'=>getTableColumnNames(USER_TABLE, false, false, false), 'from'=>USER_TABLE, 'constraint'=>array('admin'=>1)));

	if(!$asAdminsInfo)
	{
		relocate(getError('Aucun administrateur trouv&eacute; dans la base de donn&eacute;es'), 'install');
	}
	
	$bResult = install($oConnection, DB_NAME, $asAdminsInfo);
	if($bResult)
	{
		addFeed('RESET', DB_NAME, 'database');
		addMessage(getSuccess(array('Base de donn&eacute;es r&eacute;initialis&eacute;e', getHtml('Pour des raisons de s&eacute;curit&eacute;, vous allez &ecirc;tre d&eacute;connect&eacute;', 'strong'))));
		logMeOut();
		return true;
	}
	return $bResult;
}
function uploadFile($asFileInfo, $sDestFolder, $asAuthorizedFileExt)
{
	$bResult = false;
	$sFileExt = getExtension($asFileInfo['name']);
	if(in_array($sFileExt, $asAuthorizedFileExt))
	{
		if($asFileInfo['size']<MAX_UPLOAD_SIZE)
		{
			echo $sDestFolder.$asFileInfo['name'];
			if(!move_uploaded_file($asFileInfo['tmp_name'], $sDestFolder.$asFileInfo['name']))
			{
				addMessage(getError(array('Impossible de copier le fichier', 'V&eacute;rifier les droits sur les dossiers  : '.$sDestFolder)));
			}
			else
			{
				$bResult = true;
			}
		}
		else
		{
			addMessage(getError(array('Fichier trop volumineux', 'Passez par le ftp.')));
		}
	}
	else
	{
		addMessage(getError(array(	'Type de fichier non autoris&eacute; ('.$sFileExt.')',
									'Seuls les fichiers de type '.getHtml(implode(', ', $asAuthorizedFileExt), 'strong').' peuvent &ecirc;tre ajout&eacute; &agrave; la base')));
	}
	return $bResult;
}

//Database access & write functions
function cleanSql(&$oData)
{
	global $oConnection;
	
	if(!is_array($oData))
	{
		$oData = mysqli_real_escape_string($oConnection, $oData);
	}
	elseif(count($oData)>0)
	{
		$asKeys = array();
		$asValues = array();
		foreach($oData as $sKey=>$sValue)
		{
			$asKeys[] = mysqli_real_escape_string($oConnection, $sKey);
			$asValues[] = mysqli_real_escape_string($oConnection, $sValue);
		}
		$oData = array_combine($asKeys, $asValues);
	}
}
function getQuery($sQuery, $bDieOnError=true, $sTypeQuery=__FUNCTION__)
{
	global $oConnection;

	$oResult = mysqli_query($oConnection, $sQuery);
	if(!$oResult)
	{
		$sError = getError(array("Requ&ecirc;te {$sTypeQuery}", 'mysql : '.mysqli_error($oConnection), 'query : '.$sQuery), true);
		feedback($sError);
		if($bDieOnError)
		{
			relocate($sError);
		}
		return false;
	}
	else
	{
		return $oResult;
	}
}
function getArrayQuery($sQuery, $bStringOnly=false, $sTypeQuery=__FUNCTION__)
{
	$asResult = array();
	$oResult = getQuery($sQuery, true, $sTypeQuery);
	while($asCurrentRow = mysqli_fetch_array($oResult))
	{
		if($bStringOnly)
		{
			$asCurrentRow = arrayKeyFilter($asCurrentRow, 'is_string');
		}
		$asResult[] = $asCurrentRow;
	}
	return $asResult;
}
function getUniqueQuery($sQuery, $asColumnName=array(), $bStringOnly=false, $sTypeQuery=__FUNCTION__)
{
	$oResult = getQuery($sQuery, true, $sTypeQuery);
	$asResult = array();
	while($asCurrentRow = mysqli_fetch_array($oResult))
	{
		if($bStringOnly)
		{
			$asCurrentRow = arrayKeyFilter($asCurrentRow, 'is_string');
		}
		$asResult[] = $asCurrentRow;
	}
	$iCount = count($asResult);
	
	switch(count($asResult))
	{
		case 0 :
			return false;
		case ($iCount > 1) :
			addMessage(getWarning(array('Erreur de taille pour la requ&ecirc;te :', $sQuery, 'taille = '.$iCount)));
		case ($iCount > 0) :
			$asResult = array_shift($asResult);
			return (count($asColumnName)>0)?array_intersect_key($asResult, array_flip($asColumnName)):$asResult;
	}
}
function getUniqueValue($sQuery, $sColumnName=0, $sTypeQuery=__FUNCTION__)
{
	$asResult = getUniqueQuery($sQuery, array($sColumnName), false, $sTypeQuery);
	return array_shift($asResult);
}
function setQuery($sQuery, $bDieOnError=true, $sTypeQuery=__FUNCTION__)
{
	return getQuery($sQuery, $bDieOnError, $sTypeQuery);
}

function selectRows($asInfo)
{
	$sAttributes = array('select'=>"SELECT", 'from'=>"FROM", 'constraint'=>"WHERE", 'groupBy'=>"GROUP BY", 'orderBy'=>"ORDER BY");
	$asRowSeparators = array('select'=>", ", 'from'=>"", 'constraint'=>" AND ", 'groupBy'=>", ", 'orderBy'=>", ");
	$asOperators = array('constraint'=>" = ", 'orderBy'=>" ");
	
	$sQuery = "/* ".basename(__FILE__)." ".__LINE__." */";
	foreach($sAttributes as $sStatement => $sKeyWord)
	{
		$asSelection = array_key_exists($sStatement, $asInfo)?$asInfo[$sStatement]:array();
		if(!is_array($asSelection))
		{
			$asSelection = array($asSelection);
		}
		
		//if provided values
		if(count($asSelection)>0)
		{
			cleanSql($asSelection);
			$sQuery .= " ".$sKeyWord." ";
			
			//in case of double value input
			if(array_key_exists($sStatement, $asOperators))
			{
				if($sStatement=='constraint' && array_key_exists('constOpe', $asInfo))
				{
					$asOperators[$sStatement] = $asInfo['constOpe'];
				}
				$sQuery .= implodeAll($asSelection, $asOperators[$sStatement], $asRowSeparators[$sStatement]);
			}
			else
			{
				$sQuery .= implode($asRowSeparators[$sStatement], $asSelection);
			}
		}
		//default value for select
		elseif($sStatement=='select')
		{
			$sQuery .= " ".$sKeyWord." * ";
		}
	}
	return getArrayQuery($sQuery, true);
}
function selectRow($sTableName, $asConstraints, $asColumnNames=array(), $bStringOnly=false)
{
	cleanSql($sTableName);
	cleanSql($asConstraints);
	
	$asQueryValues = array();
	foreach($asConstraints as $oColumnName =>$oValue)
	{
		//check for column names
		if(is_numeric($oColumnName))
		{
			$oColumnName = getTableColumnNames($sTableName, $oColumnName);
		}
		$sQuote = getQuote($sTableName, $oColumnName);

		$asQueryValues[$oColumnName] = $oColumnName." = ".$sQuote.$oValue.$sQuote;
	}
	$sConstraint = (count($asQueryValues)>0)?"WHERE ".implode(" AND ", $asQueryValues):"";
	
	//requested columns
	$sColumns = (count($asColumnNames)>0)?implode(", ", $asColumnNames):"*";
	
	$sQuery = 	"SELECT /* ".basename(__FILE__)." ".__LINE__." */ $sColumns
				FROM $sTableName
				$sConstraint";
	
	return getUniqueQuery($sQuery, array(), $bStringOnly);
}
function selectValue($sTableName, $sColumnName, $asConstraints=array())
{
	if(is_numeric($asConstraints))
	{
		$asConstraints = array('id_'.$sTableName=>$asConstraints);
	}

	$asResult = selectRow($sTableName, $asConstraints, array($sColumnName));
	if(!$asResult || !isset($asResult[$sColumnName]))
	{
		return false;
	}
	else
	{
		return $asResult[$sColumnName];
	}
}
function insertUpdateRow($sTableName, $asData, $asKeys=array())
{
	$sTableIdName = 'id_'.$sTableName;
	
	//check for data in the db
	if($asKeys==array())
	{
		$asKeys[] = $sTableIdName;
	}
	$asValues = array_intersect_key($asData, array_flip($asKeys));
	$iTableId = selectValue($sTableName, $sTableIdName, $asValues);
	
	//insert
	if(!$iTableId)
	{
		$iTableId = insertRow($sTableName, $asData);
	}
	//Update
	else
	{
		if(array_key_exists($sTableIdName, $asData))
		{
			unset($asData[$sTableIdName]);
		}
		$iTableId = updateRow($sTableName, $iTableId, $asData);
	}
	return $iTableId;
}
function insertRow($sTableName, $asData)
{
	cleanSql($sTableName);
	cleanSql($asData);
	
	$asQueryValues = array();
	foreach($asData as $oColumnName =>$oValue)
	{
		//check for column names
		if(is_numeric($oColumnName))
		{
			$oColumnName = getTableColumnNames($sTableName, $oColumnName, false);
		}
		$sQuote = getQuote($sTableName, $oColumnName);

		$asQueryValues[$oColumnName] = $sQuote.$oValue.$sQuote;
	}
	$sQuery = 	"INSERT /* ".basename(__FILE__)." ".__LINE__." */
				INTO ".$sTableName." (`".implode("`, `", array_keys($asQueryValues))."`) 
				VALUES (".implode(", ", $asQueryValues).")";

	setQuery($sQuery);
	return getMaxIncrementedValue($sTableName);
}
function updateRow($sTableName, $iTableId, $asData)
{
	cleanSql($sTableName);
	cleanSql($iTableId);
	cleanSql($asData);

	$asSets = array();
	foreach($asData as $oColumnName=>$oColumnValue)
	{
		//check for column names
		if(is_numeric($oColumnName))
		{
			$oColumnName = getTableColumnNames($sTableName, $oColumnName, false);
		}
		$sQuote = getQuote($sTableName, $oColumnName);
		$asSets[] = "`$oColumnName` = ".$sQuote.$oColumnValue.$sQuote;
	}
	$sQuery = 	"UPDATE /* ".basename(__FILE__)." ".__LINE__." */ $sTableName
				SET ".implode(", ", $asSets)." 
				WHERE ".getId($sTableName)." = ".$iTableId." LIMIT 1";
	setQuery($sQuery);
	return $iTableId;
}
function deleteRow($sTableName, $iTableId)
{
	cleanSql($sTableName);
	cleanSql($iTableId);

	//linked tables
	switch($sTableName)
	{
		case is_array($sTableName):
			$asTables = $sTableName;
			break;
		case USER_TABLE :
			$asTables = array(USER_TABLE, RESULT_TABLE);
			break;
		case QCM_TABLE :
			$asTables = getQcmTables();
			break;
		case QUESTION_TABLE :
			$asTables = array(QUESTION_TABLE, ANSWER_TABLE);
			break;
		case is_string($sTableName) :
			$asTables = array($sTableName);
			break;
		default:
			$asTables = array();
	}
	foreach($asTables as $sTable)
	{
		setQuery("DELETE /* ".basename(__FILE__)." ".__LINE__." */ FROM ".$sTable." WHERE id_".$sTableName." = ".$iTableId);
	}
}
function setQcmValidity($iQcmId, $bValid)
{
	$sQcmName = getTextFromId(QCM_TABLE, $iQcmId);
	updateRow(QCM_TABLE, $iQcmId, array('valid'=>$bValid?'1':'0'));
	addFeed($bValid?'ACTIVATE':'DESACTIVATE', $iQcmId, QCM_TABLE, $sQcmName);
}
function getListe($asInfo)
{
	//TODO remplacer par selectRows
	$sAttributes = array('select'=>"", 'from'=>"FROM", 'constraint'=>"WHERE", 'groupBy'=>"GROUP BY", 'orderBy'=>"ORDER BY");
	$sQuery =	"SELECT /* ".basename(__FILE__)." ".__LINE__." */";
	foreach($sAttributes as $sStatement => $sKeyWord)
	{
		$sSeparator = ($sStatement=='constraint')?" AND ":", ";
		if(array_key_exists($sStatement, $asInfo))
		{
			$sQuery .= " ".$sKeyWord." ".(is_array($asInfo[$sStatement])?implode($sSeparator, $asInfo[$sStatement]):$asInfo[$sStatement]);
		}
		elseif($sStatement=='select')
		{
			$sQuery .= " ".$sKeyWord." * ";
		}
	}

	return getArrayQuery($sQuery, true);
}
function arrayKeyFilter($asArray, $sCallBack)
{
	$asValidKeys = array_flip(array_filter(array_keys($asArray), $sCallBack));
	return array_intersect_key($asArray, $asValidKeys);
}

//feed functions
function addFeed($sAction, $sSource, $sType, $sOldValue='', $sNewValue='' , $iRefId=0, $sAuthor='')
{
	if($sAuthor=='')
	{
		$sAuthor = isset($_SESSION['user'])?$_SESSION['firstName'].' '.$_SESSION['lastName']:'bot';
	}
	return insertRow(FEED_TABLE, array($sAuthor, $sAction, $sSource, $sType, substr($sOldValue, 0, 254), substr($sNewValue, 0, 254), $iRefId));
}
function cleanRss($oText)
{
	$asForbiddenChars = array('&', '<', '>', '"', '\'');
	$asReplacementCode = array( '&amp;', '&lt;', '&gt;', '&quot;', '&apos;');
	if(!is_array($oText))
	{
		return str_replace($asForbiddenChars, $asReplacementCode, $oText);
	}
	elseif(count($oText)>0)
	{
		$oTextKeys = array_map('cleanRss', array_keys($oText));
		$oTextValues = array_map('cleanRss', $oText);
		return array_combine($oTextKeys, $oTextValues);
	}
}

/**
 * Get one or more qcm infos (questions + answers)
 *
 * Output architecture :
 *
 *	Array
 *	(
 *		[id_qcm] => Array
 *			(
 *				['qcm'] => qcm_name
 *				['id_user'] => author
 *				[QUESTION_TABLE] => Array
 *					(
 *						[id_question] => Array
 *							(
 *								[QUESTION_TABLE] => question_title
 *								[ANSWER_TABLE] => Array
 *									(
 *										[answer_id] => Array
 *											(
 *												[ANSWER_TABLE] => answer_title
 *												['right_answer'] => right_answer
 *											)
 *										[answer_id] => ...
 *									)
 *							)
 *						[id_question] => ...
 *					)
 *			)
 *		[id_qcm] => ...
 *	)
 *
 * @param	array	$oQcmIds	liste of qcm to get
 */
function getQcm($oQcmIds=false)
{
	$sQcmConstraint = "";
	$bOneQcm = false;
	if(is_numeric($oQcmIds))
	{
		$bOneQcm = true;
		$sQcmConstraint = " WHERE id_".QCM_TABLE." = ".$oQcmIds;
	}
	elseif(is_array($oQcmIds))
	{
		$sQcmConstraint = " WHERE id_".QCM_TABLE." IN(".implode(',', $oQcmIds);
	}
	$oQcm = getQuery(	"SELECT /* ".basename(__FILE__)." ".__LINE__." */ ".getId(QCM_TABLE).", ".QCM_TABLE.", id_".USER_TABLE."
						FROM ".QCM_TABLE.
	$sQcmConstraint."
						ORDER BY ".getId(QCM_TABLE));
	while($asQcms = mysqli_fetch_array($oQcm))
	{
		$asResults[$asQcms['id_'.QCM_TABLE]][QCM_TABLE] = $asQcms[QCM_TABLE];
		$asResults[$asQcms['id_'.QCM_TABLE]]['id_'.USER_TABLE] = $asQcms['id_'.USER_TABLE];
		$oQuestion = getQuery(	"SELECT /* ".basename(__FILE__)." ".__LINE__." */ ".getId(QUESTION_TABLE).", ".QUESTION_TABLE.", image
								FROM ".QUESTION_TABLE.
		$sQcmConstraint."
								ORDER BY ".getId(QUESTION_TABLE));
		while($asQuestion = mysqli_fetch_array($oQuestion))
		{
			$asResults[$asQcms['id_'.QCM_TABLE]][QUESTION_TABLE][$asQuestion['id_'.QUESTION_TABLE]][QUESTION_TABLE] = $asQuestion[QUESTION_TABLE];
			$asResults[$asQcms['id_'.QCM_TABLE]][QUESTION_TABLE][$asQuestion['id_'.QUESTION_TABLE]]['image'] = $asQuestion['image'];
			$oAnswer = getQuery("SELECT /* ".basename(__FILE__)." ".__LINE__." */ ".getId(ANSWER_TABLE).", ".ANSWER_TABLE.", ".RIGHT_ANSWER."
								FROM ".ANSWER_TABLE.
			$sQcmConstraint."
								".(($sQcmConstraint!='')?"AND":"WHERE")." id_".QUESTION_TABLE." = ".$asQuestion['id_'.QUESTION_TABLE]."
								ORDER BY ".getId(ANSWER_TABLE));
			while($asAnswer = mysqli_fetch_array($oAnswer))
			{
				$asResults	[$asQcms['id_'.QCM_TABLE]]
				[QUESTION_TABLE]
				[$asQuestion['id_'.QUESTION_TABLE]]
				[ANSWER_TABLE]
				[$asAnswer['id_'.ANSWER_TABLE]] = array(ANSWER_TABLE => $asAnswer[ANSWER_TABLE], RIGHT_ANSWER=>$asAnswer[RIGHT_ANSWER]);
			}
		}
	}
	return $bOneQcm?$asResults[$oQcmIds]:$asResults ;
}
function getQcmName($iQcmId)
{
	return selectValue(QCM_TABLE, QCM_TABLE, $iQcmId);
}
function getNbQuestions($iQcmId)
{
	return selectValue(QUESTION_TABLE, 'COUNT(1)', array('id_qcm'=>$iQcmId));
}
function getUserFromLogin($sLastName, $sFirstName)
{
	if($sLastName!='' && $sFirstName!='')
	{
		return selectRow(USER_TABLE, array('user_last_name'=>strtolower($sLastName), 'user_first_name'=>strtolower($sFirstName)));
	}
	else
	{
		return false;
	}
}
function getTextFromId($sTableName, $iTableId)
{
	$sTextColumnName = $sTableName;
	$sAdditionalInfo = '';
	switch($sTableName)
	{
		case USER_TABLE:
			$sAdditionalInfo = selectValue($sTableName, USER_TABLE.'_first_name', $iTableId).' ';
			$sTextColumnName = USER_TABLE.'_last_name';
			break;
		case FEED_TABLE:
			$sTextColumnName = 'type';
	}
	return $sAdditionalInfo.selectValue($sTableName, $sTextColumnName, $iTableId);
}
function getTimeStampFromFile($sFileName)
{
	return getTimeStampFromBackUp(file_get_contents($sFileName));
}
function getTimeStampFromBackUp($sBackUp)
{
	preg_match('/\/\* ID (?P<date>\S+) \*\//', $sBackUp, $asMatches);
	if(!array_key_exists('date', $asMatches))
	{
		return 0;
	}
	$asData = explode('_', str_replace('.'.DB_EXT_FILE, '', basename($asMatches['date'])));
	if(count($asData)!=3)
	{
		return 0;
	}
	list($sDbName, $sDate, $sTime) = $asData;
	$asDate = explode('.', $sDate);
	$asTime = explode('h', $sTime);
	if(count($asDate)!=3 || count($asTime)!=2)
	{
		return 0;
	}
	return strtotime($asDate[2].'-'.$asDate[1].'-'.$asDate[0].' '.$asTime[0].':'.$asTime[1].':00');
}
function getLastBackUp($sInfo)
{
	foreach(glob(BACKUP_FOLDER.'*.'.DB_EXT_FILE) as $sFileName)
	{
		$asTimeStamp[getTimeStampFromFile($sFileName)] = $sFileName;
	}
	if(isset($asTimeStamp))
	{
		ksort($asTimeStamp);
		switch($sInfo)
		{
			case 'timeStamp':
				return array_pop(array_keys($asTimeStamp));
			case 'fileName':
				return basename(array_pop($asTimeStamp));
			case 'file':
				return file_get_contents(array_pop($asTimeStamp));
			default:
				return getWarning('Info '.$Info.' inconnue');
		}
	}
	else
	{
		return false;
	}
}

//Results related
function checkPreviousResults($iUserId, $iQcmId)
{
	$iResultId = selectValue(RESULT_TABLE, 'id_'.RESULT_TABLE, array('id_'.USER_TABLE=>$iUserId, 'id_'.QCM_TABLE=>$iQcmId));
	return ($iResultId !== false);
}
function addResult($iUserId, $iQcmId, $iMark)
{
	insertRow(RESULT_TABLE, array($iUserId, $iQcmId, $iMark));
}
function getSQLResultsConstraints($aiInfoSelect)
{
	$asConstraintColumns = array('grade' => false, 'class' => false, USER_TABLE => true, QCM_TABLE => true);
	$sConstraints = array('noAdmin' => USER_TABLE.".admin = 0");
	foreach($asConstraintColumns as $sConstraintColumn => $bIsTable)
	{
		if(array_key_exists($sConstraintColumn, $aiInfoSelect) && $aiInfoSelect[$sConstraintColumn]>0)
		{
			$aiConstraintIds = $aiInfoSelect[$sConstraintColumn];
			$sConstraints[$sConstraintColumn] = ($bIsTable?getId($sConstraintColumn):$sConstraintColumn).((is_array($aiConstraintIds))?" IN (".implode(',', $aiConstraintIds).")":" = ".$aiConstraintIds);
		}
	}
	return "WHERE ".implode(" AND ", $sConstraints);
}
function getResults($aiInfoSelect)
{
	//TODO class Qcm
	if(isset($aiInfoSelect['outputOrder']))
	{
		$asOutputOrder = $aiInfoSelect['outputOrder'];
	}
	else
	{
		$asOutputOrder = array('grade', 'class', 'id_'.USER_TABLE, 'id_'.QCM_TABLE);
	}
	$oResults = getQuery(	"SELECT /* ".basename(__FILE__)." ".__LINE__." */ grade, class, ".USER_TABLE."_last_name, ".USER_TABLE."_first_name, ".getId(USER_TABLE).", ".RESULT_TABLE.".id_".QCM_TABLE.", ".QCM_TABLE.", result
							FROM ".RESULT_TABLE."
	 						LEFT JOIN ".USER_TABLE." USING(id_".USER_TABLE.")
	 						LEFT JOIN ".QCM_TABLE." ON(".RESULT_TABLE.".id_".QCM_TABLE." = ".getId(QCM_TABLE).")
							".getSQLResultsConstraints($aiInfoSelect)."
							ORDER BY ".QCM_TABLE.", grade, class, ".USER_TABLE."_last_name");

	while($asCurrentResult = mysqli_fetch_array($oResults))
	{
		$asResults	[$asCurrentResult[$asOutputOrder[0]]]
		[$asCurrentResult[$asOutputOrder[1]]]
		[$asCurrentResult[$asOutputOrder[2]]]
		[$asCurrentResult[$asOutputOrder[3]]] = array(	QCM_TABLE => $asCurrentResult[QCM_TABLE],
		USER_TABLE.'_first_name' => $asCurrentResult[USER_TABLE.'_first_name'],
		USER_TABLE.'_last_name' => $asCurrentResult[USER_TABLE.'_last_name'],
		RESULT_TABLE => $asCurrentResult['result']);
	}
	return isset($asResults)?$asResults:false;
}
function getResultsAverage($iOutOf, $aiInfoSelect)
{
	$iAvg = getUniqueValue(	"SELECT /* ".basename(__FILE__)." ".__LINE__." */ AVG(result) as resultAvg
							FROM ".RESULT_TABLE."
 							LEFT JOIN ".USER_TABLE." USING(id_".USER_TABLE.")
 	 						LEFT JOIN ".QCM_TABLE." ON(".RESULT_TABLE.".id_".QCM_TABLE." = ".getId(QCM_TABLE).")
							".getSQLResultsConstraints($aiInfoSelect), 'resultAvg');
	return roundMark($iOutOf, $iAvg);
}
function shuffle_assoc($asArray)
{
	$aiArrayKeys = array_keys($asArray);
	shuffle($aiArrayKeys);
	foreach($aiArrayKeys as $iKey)
	{
		$asShuffledArray[$iKey] = $asArray[$iKey];
	}
	return $asShuffledArray;
}
function roundMark($iOutOf, $iMark)
{
	return round($iMark*$iOutOf, DECIMAL_LIMIT);
}

/* QCM Builders */

//HTML question builders
function getQuestionPattern()
{
	$sQuestionId = '#new#'.QUESTION_TABLE.'#questionId#';
	return array
	(
		'html' =>	'<tr>
						<td width="60%">
							<textarea rows="5" id="'.$sQuestionId.'" name="'.$sQuestionId.'" class="spaceRight" onfocus="emptyBox(\''.$sQuestionId.'\');" onclick="addQuestion(#questionId#);">#questionValue#</textarea>
							<a href="#" id="'.$sQuestionId.'Delete" class="deleteQuestion" title="Supprimer cette question" onclick="deleteQuestion(\''.$sQuestionId.'\');return false;">X</a>
							#image#
						</td>
						<td width="30%" id="'.QUESTION_TABLE.'#questionId#answerpanel">#answersValue#</td>
						<td width="10%" id="'.QUESTION_TABLE.'#questionId#rightanswerpanel">#rightAnswersValue#</td>
					</tr>',
		'variables' => array('#questionNum#', '#new#', '#questionId#', '#questionValue#', '#answersValue#', '#rightAnswersValue#', '#image#'),
		'defaultValues' => array('#questionId#', 'new', '#questionId#', DEFAULT_QUESTION_INPUT, getInputHtml('Answer'), getInputHtml('RightAnswer'), getInputHtml('Image'))
	);
}
function getAnswerPattern()
{
	$sAnswerId = '#new#'.QUESTION_TABLE.'#questionId#'.ANSWER_TABLE.'#answerId#';
	return array
	(
		'html' => '<div class="answerBox"><input type="text" id="'.$sAnswerId.'" name="'.$sAnswerId.'" class="spaceRight" onfocus="addAnswer(#questionId#, #answerId#);emptyBox(\''.$sAnswerId.'\');" value="#answerValue#" /><a href="#" id="'.$sAnswerId.'Delete" class="deleteAnswer" title="Supprimer cette r&eacute;ponse" onclick="deleteAnswer(\''.$sAnswerId.'\');return false;">X</a></div>',
		'variables' => array('#questionId#', '#answerId#', '#new#', '#answerValue#'),
		'defaultValues' => array('#questionId#', '#answerId#', 'new', DEFAULT_ANSWER_INPUT)
	);
}
function getRightAnswerPattern()
{
	$sRightAnswerId = '#new#'.QUESTION_TABLE.'#questionId#'.ANSWER_TABLE.'#rightAnswerId#'.RIGHT_ANSWER.'#rightAnswerId#';
	return array
	(
		'html' => '<div class="answerBox"><input type="checkbox" class="centered" id="'.$sRightAnswerId.'" name="'.$sRightAnswerId.'"#rightAnswerChecked# /></div>',
		'variables' => array('#questionId#', '#rightAnswerId#', '#new#', '#rightAnswerChecked#'),
		'defaultValues' => array('#questionId#', '#answerId#', 'new', '')
	);
}
function getCurrentAnswerPattern()
{
	return array
	(
		'html' => '<input type="hidden" name="question#questionId#currentanswer" value="1" />', 
		'variables' => array('#questionId#', '#answerNumber#'),
		'defaultValues' => array('!+questionId+!', '1')
	);
}
function getImagePattern()
{
	$sQuestionId = '#new#'.QUESTION_TABLE.'#questionId#';
	return array
	(
		'html' => '#image#<p><span id="'.$sQuestionId.'imageText" class="text">#imageText# : </span><input type="file" name="'.$sQuestionId.'image" class="button" /></p>', 
		'variables' => array('#image#', '#imageText#', '#new#', '#questionId#'),
		'defaultValues' => array('', ADD_IMAGE_TEXT, 'new', '#questionId#')
	);
}

function getInputHtml($sType, $oData=false)
{
	$asHtml = call_user_func('get'.$sType.'Pattern');
	$sInput = str_replace
	(
	$asHtml['variables'],
	(!$oData)?$asHtml['defaultValues']:$oData,
	$asHtml['html']
	);
	return (!$oData)?printJsString($sInput):$sInput;
}
function printJsString($sText)
{
	return str_replace(array("\'", "'", "!", chr(10), chr(13), chr(9)),array("'", "\'", "'", '', '', ''), $sText);
}
function checkQcm($iQcmId)
{
	$asQcm = getQcm($iQcmId);
	$asErrors = array();

	//no qcm name
	if($asQcm[QCM_TABLE] == DEFAULT_QCM_NAME)
	{
		$asErrors[] = getWarning('Le QCM n\'a aucun nom');
	}

	if(isset($asQcm[QUESTION_TABLE]) && count($asQcm[QUESTION_TABLE])>0)
	{
		$iQuestionNum = 0;
		foreach($asQcm[QUESTION_TABLE] as $iQuestionId => $asAnswers)
		{
			$iQuestionNum++;
			$iNbAnswers = 0;
			$iNbRightAnswers = 0;

			//empty question
			if($asAnswers[QUESTION_TABLE]=='' || $asAnswers[QUESTION_TABLE]==DEFAULT_QUESTION_INPUT)
			{
				$asErrors[] = 'Question '.$iQuestionNum.' : vide';
			}
			if(isset($asAnswers[ANSWER_TABLE]) && count($asAnswers[ANSWER_TABLE])>0)
			{
				foreach($asAnswers[ANSWER_TABLE] as $iAnswerId => $asAnswer)
				{
					$iNbRightAnswers += $asAnswer[RIGHT_ANSWER];
						
					//No answer and a valid answer checked
					if($asAnswer[ANSWER_TABLE]=='' && $asAnswer[RIGHT_ANSWER]>0)
					{
						$asErrors[] =	'Question '.$iQuestionNum.', r&eacute;ponse '.$iNbAnswers.' : 
										Aucune r&eacute;ponse n\'a &eacute;t&eacute; &eacutecrite, 
										mais une bonne r&eacute;ponse a &eacute;t&eacute; coch&eacute;e';
					}
				}
			}
			else
			{
				//No answer for a defined question
				$asErrors[] = 'Question '.$iQuestionNum.' : Aucune r&eacute;ponse possible n\'a &eacute;t&eacute; ajout&eacute;e';
			}

			//No right answer
			if($iNbRightAnswers==0)
			{
				$asErrors[] = 'Question '.$iQuestionNum.' : Aucune bonne r&eacute;ponse n\'a &eacute;t&eacute; coch&eacute;e';
			}
		}
	}
	else
	{
		//No question
		$asErrors[] = 'Aucune question n\'a &eacute;t&eacute; &eacute;crite';
	}
	
	$bResult = true;
	if(count($asErrors)>0)
	{
		array_unshift($asErrors, 'QCM incomplet', 'les &eacute;l&eacute;ments suivants comportent une erreur :');
		addMessage(getError($asErrors));
		$bResult = false;
	}
	return $bResult;
}

//SQL Questions builders
function parseQuestion($sKey)
{
	$asTables = array(QUESTION_TABLE, ANSWER_TABLE, RIGHT_ANSWER);
	if(strpos($sKey, QUESTION_TABLE) === false)
	{
		return false;
	}
	else
	{
		$aiParsedKey = array();
		$iCurrentIndex = -1;
		for($i=0;$i<strlen($sKey);$i++)
		{
			if(is_numeric($sKey[$i]))
			{
				if(!$bSameValue)
				{
					$iCurrentIndex++;
				}
				$aiParsedKey[$asTables[$iCurrentIndex]] = isset($aiParsedKey[$asTables[$iCurrentIndex]])?$aiParsedKey[$asTables[$iCurrentIndex]].$sKey[$i]:$sKey[$i];
				$bSameValue = true;
			}
			else
			{
				$bSameValue = false;
			}
		}
		$aiParsedKey = array_filter($aiParsedKey);
		return (empty($aiParsedKey) || count($aiParsedKey)>2)?false:$aiParsedKey;
	}
}

/* Security Functions */

//Authorizations
function pageAccessManagement($sRequestedPage)
{
	//check on logon
	$bGrantedAccess = false;
	$bConnected = checkSession();
	$asAuth = isset($_GET['auth'])?unserialize(decodeUrl($_GET['auth'])):array();
	if(isAuthorizedPage($sRequestedPage))
	{
		$bGrantedAccess = true;
	}
	elseif($sRequestedPage=='download' || $sRequestedPage=='remote')
	{
		$bResult = false;
		if($sRequestedPage=='download' && $bConnected)
		{
			$sFileName = isset($_POST['generate'])?generateCsv($_POST['file'], $_POST['content']):$_REQUEST['file'];
			$bResult = download($sFileName, array('csv', 'sql', 'zip', 'gz'));	
		}
		elseif($sRequestedPage=='remote' && is_array($asAuth) && count($asAuth)>0 && checkExternalAccess($asAuth['lastname'], $asAuth['firstname'], $asAuth['pass']))
		{
			$bResult = remote($_GET['action']);
		}
		die($bResult?'':getCleanMessage());
	}
	elseif(isset($_SESSION['user']))
	{
		if(!$bConnected || $sRequestedPage == 'logout')
		{
			addMessage(logMeOut());
		}
		elseif(isAdminOnlyPage($sRequestedPage) && !isAdmin()) //stop displaying page (requires admin status)
		{
			relocate(getError('Zone r&eacute;serv&eacute;e aux administrateurs'));
		}
		else
		{
			$bGrantedAccess = true;
		}
	}
	elseif(isset($_GET['register']))
	{
		$bGrantedAccess = ($_GET['register'] == 1)?register($_POST):logMeIn($_POST['login'], $_POST['firstName'], $_POST['pass']);
		if($bGrantedAccess && isset($_COOKIE[EXPECTED_PAGE]))
		{
			$sRequestedPage = $_COOKIE[EXPECTED_PAGE];
			setcookie(EXPECTED_PAGE, '', time()-3600);
		}
	}
	elseif($sRequestedPage!='logout')
	{
		setcookie(EXPECTED_PAGE, $sRequestedPage, time()+3600);
	}
	
	//Choose corresponding page
	if($bGrantedAccess)
	{
		if($sRequestedPage!='logon' && array_key_exists($sRequestedPage, getPageDescription()))
		{
			$sPage = $sRequestedPage;
		}
		else
		{
			$sPage = DEFAULT_LOGGED_PAGE;
		}
	} 
	else
	{
		$sPage = 'logon';
	}
	return $sPage;
}
function logMeIn($sLastName, $sFirstName, $sPass)
{
	$asUser = getUserFromLogin($sLastName, $sFirstName);
	if(!$asUser)
	{
		addMessage(getError('Utilisateur inconnu'));
	}
	elseif(checkPassword($sPass, $asUser['pass']))
	{
		setSession($asUser);
		addMessage(getSuccess('Connexion r&eacute;ussie ! Bienvenue '.ucwords($_SESSION['firstName'].' '.$_SESSION['lastName'])));
		//addFeed('CONNECT', $asUser['id_'.USER_TABLE], USER_TABLE, $asUser[USER_TABLE.'_first_name'].' '.$asUser[USER_TABLE.'_last_name']);
		return true;
	}
	else
	{
		addMessage(getError('Mot de passe incorrect'));
	}
	return false;
}
function checkExternalAccess($sLastName, $sFirstName, $sPass)
{
	$asUser = getUserFromLogin($sLastName, $sFirstName);
	return ($asUser && ($sPass == $asUser['pass']) && $asUser['admin']);
}
function logMeOut($bRelocate=true)
{
	//carry messages through sessions
	$sMessages = getCleanMessage();
	
	//destroy session
	$_SESSION = array();
	$bResult = session_destroy();

	//start new session and redirect towards the log-in panel
	session_start();
	if($bRelocate)
	{
		relocate($sMessages.($bResult?getSuccess('Vous &ecirc;tes d&eacute;connect&eacute;'):getError(array('Echec de la d&eacute;connexion', 'Veuillez fermer le navigateur et l\'ouvrir de nouveau'))), 'logon');
	}
	return $bResult;
}
function relocate($oMessage, $sPage=DEFAULT_LOGGED_PAGE, $asVar=array())
{
	//clean buffer
	ob_end_clean();

	//set user action result message
	if(!isset($_SESSION))
	{
		session_start();
	}
	addMessage($oMessage);

	//load new page with specific variables
	$asVar['page'] = $sPage;
	header('Location:index.php?'.implodeAll($asVar, '=', '&'));
	die();
}
function register($asData)
{
	$sLastName = strtolower($asData['login']);
	$sFirstName = strtolower($asData['firstname']);
	$sPass = $asData['pass'];
	$bExistingUser = getUserFromLogin($sLastName, $sFirstName);
	
	$sErrorMessage = '';
	if($sFirstName=='' || $sLastName=='' || $sPass=="" || $asData['pass2']=='')
	{
		$sErrorMessage = 'Tous les champs ne sont pas remplis';
	}
	elseif(htmlspecialchars($sLastName, ENT_QUOTES)!=$sLastName || htmlspecialchars($sFirstName, ENT_QUOTES)!=$sFirstName)
	{
		$sErrorMessage = 'Les caract&egrav;res HTML sont interdits dans les noms et pr&eacute;noms';
	}
	elseif($sPass != $asData['pass2'])
	{
		$sErrorMessage = 'Les mots de passes ne sont pas les m&ecirc;mes';
	}
	elseif($bExistingUser)
	{
		$sErrorMessage = 'Il existe d&eacute;j&agrave; un compte ayant ce nom et ce pr&eacute;nom dans la base de donn&eacute;es';
	}

	if($sErrorMessage != '')
	{
		addMessage(getError($sErrorMessage));
	}
	else
	{
		$iUserId = insertRow(USER_TABLE, array($sFirstName, $sLastName, $asData['grade'], $asData['class'], encryptPassword($sPass), 0));
		addMessage(getSuccess('F&eacute;licitation '.$sFirstName.' '.$sLastName.' ! Votre compte est cr&eacute;&eacute;'));
		addFeed('CREATE', $iUserId, USER_TABLE, $sFirstName.' '.$sLastName, '', 0, $sFirstName.' '.$sLastName);
		return logMeIn($sLastName, $sFirstName, $sPass);
	}
	return false;
}
function isAdmin($iUserId=false)
{
	if(!checkSession()) //not connected or wrong id
	{
		$bIsAdmin = false;
	}
	else
	{
		$iUserId = ($iUserId===false)?$_SESSION['user']:$iUserId;
		$bIsAdmin = selectValue(USER_TABLE, 'admin', $iUserId);
	}
	return $bIsAdmin;
}
function isAuthorizedPage($sPage)
{
	$asAuthorizedPages = array('install', 'register', 'qcmProcess', 'qcmResults', 'rss');
	return in_array($sPage,$asAuthorizedPages);
}
function isAdminOnlyPage($sPage)
{
	$asAdminOnlyPages = array('account_admin', 'statsAdmin', 'test', 'version', 'backUpCreator', 'buildCsv', 'download', 'search');
	return in_array($sPage, $asAdminOnlyPages);
}
function setSession($asUser)
{
	if(!is_array($asUser))
	{
		$asUser = selectRow(USER_TABLE, array($asUser));
	}
	$_SESSION['user'] = $asUser['id_user'];
	$_SESSION['lastName'] = $asUser['user_last_name'];
	$_SESSION['firstName'] = $asUser['user_first_name'];
	$_SESSION['grade'] = $asUser['grade'];
	$_SESSION['class'] = $asUser['class'];
	$_SESSION['sessionId'] = getSessionId();
}
function checkSession()
{
	return 	array_key_exists('user', $_SESSION)
			&& is_numeric($_SESSION['user']) 
			&& array_key_exists('sessionId', $_SESSION) 
			&& $_SESSION['sessionId'] == getSessionId();
}
function getSessionId()
{
	return encryptPassword($_SERVER['HTTP_USER_AGENT'].$_SERVER['REMOTE_ADDR']);
}
function checkOrigin($sPrevPage, $asExtraCheck=array())
{
	$asUrlParts = parse_url(array_key_exists('HTTP_REFERER', $_SERVER)?$_SERVER['HTTP_REFERER']:'');
	parse_str(array_key_exists('query', $asUrlParts)?$asUrlParts['query']:'', $asUrlVariables);

	$bExtraCheck = true;
	$asExtraCheck['page'] = $sPrevPage;
	foreach($asExtraCheck as $sGetVar=>$sGetValue)
	{
		if(!isset($asUrlVariables[$sGetVar]) || $asUrlVariables[$sGetVar]!=$sGetValue)
		{
			$bExtraCheck = false;
			break;
		}
	}

	return $bExtraCheck;
}

//encoding functions
function encryptPassword($sPass)
{
	return md5(shuffleText($sPass));
}
function checkPassword($sClearPass,$sEncodedPass)
{
	return encryptPassword($sClearPass) == $sEncodedPass;
}
function encodeUrl($sUrl)
{
	return base64_encode(serialize(explode("\n", shuffleText($sUrl))));
}
function decodeUrl($sEncodedUrl)
{
	return shuffleText(implode("\n", unserialize(base64_decode($sEncodedUrl))));
}
function shuffleText($sText)
{
/*
	$sRandomText = <<< RANDTEXT
					let's_mess%a&bit;with~it,!just§for¨the^sake*of-it
RANDTEXT;
*/
	$sRandomText = "let's_mess%a&bit;with~it,!just§for¨the^sake*of-it";
	for($iIndex=0; $iIndex < strlen($sText); $iIndex++)
	{
		$sText[$iIndex] = $sRandomText[$iIndex%strlen($sRandomText)] ^ $sText[$iIndex];
	}
	return $sText;
}
function cleanPost(&$asData)
{
	//get rid of magic quotes
	if(function_exists('get_magic_quotes_gpc') && get_magic_quotes_gpc())
	{
	    cleanData($asData, 'stripslashes');
	}
}
function cleanData(&$oData, $sCleaningFunc)
{
	if(!is_array($oData))
	{
		$oData = call_user_func($sCleaningFunc, $oData);
	}
	elseif(count($oData)>0)
	{
		$asKeys = array_map($sCleaningFunc, array_keys($oData));
		$asValues = array_map($sCleaningFunc, $oData);
		$oData = array_combine($asKeys, $asValues);
	}
}

/* HTML Display Functions */

//Global application messages
function addMessage($sMessage)
{
	if(!isset($_SESSION['message']))
	{
		$_SESSION['message'] = '';
	}
	$_SESSION['message'] .= $sMessage;
}
function getCleanMessage()
{
	$sMessage = '';
	if(isset($_SESSION['message']))
	{
		$sMessage = $_SESSION['message'];
		unset($_SESSION['message']);
	}
	return $sMessage;
}

//Error / Success handling
function getError($oMessage=false, $bSqlError=false, $bDie=false)
{
	if(!$oMessage)
	{
		$oMessage = 'Une erreur inconnue est apparue';
	}
	$sErrorMessage = getParagraph($oMessage, 'error', 'Erreur '.($bSqlError?'SQL ':'').'!');
	if($bDie)
	{
		echo $sErrorMessage;
		die('die() called by '.__FUNCTION__);
	}
	else
	{
		return $sErrorMessage;
	}
}
function getWarning($sText)
{
	return getParagraph($sText, 'warning', 'Attention :');
}
function getSuccess($sText)
{
	return getParagraph($sText, 'success');
}
function getNotice($sText)
{
	return getParagraph($sText, 'notice');
}

//compute HTML
function getImage($sSrc, $sAlt='', $sClass='', $sStyle='', $asExtraAttr=array())
{
	$sAlt = ($sAlt=='')?basename($sSrc):$sAlt;
	$asExtraAttr = array_merge(array('src'=>$sSrc, 'alt'=>$sAlt), $asExtraAttr);
	return getHtml('', 'img', $sClass, $sStyle, $asExtraAttr, true);
}
function getA($sLink, $oText, $sTitle='', $sClass='', $sStyle='', $asExtraAttr=array())
{
	$asExtraAttr = array_merge(array('href'=>$sLink, 'title'=>$sTitle), $asExtraAttr);
	return getHtml($oText, 'a', $sClass, $sStyle, $asExtraAttr);
}
function getParagraph($oText, $sClass='', $sTitle='')
{
	$sComments = '';
	if(is_array($oText))
	{
		$sFirstLine = array_shift($oText);
		$iLine=0;
		foreach($oText as $sCommentTitle=>$sLine)
		{
			if(!is_numeric($sCommentTitle))
			{
				$sCommentTitle = getHtml($sCommentTitle, 'span', '', 'font-weight:bold;').' : ';
			}
			else
			{
				$sCommentTitle = '';
			}
			$sComments .= "\n".getHtml($sCommentTitle.$sLine, 'p', ($iLine==0)?'notice':'');
			$iLine++;
		}
	}
	else
	{
		$sFirstLine = $oText;
	}
	$sTitle = getHtml($sTitle.' '.$sFirstLine, 'p', $sClass);
	return getHtml($sTitle.$sComments, 'div', $sClass.' rounded');
}
function getHtml($oText, $sTag, $sClass='', $sStyle='', $asExtraAttr=array(), $bAutoClose=false, $sInter='')
{
	if($sClass!='')
	{
		$asExtraAttr['class'] = $sClass;
	}
	if($sStyle!='')
	{
		$asExtraAttr['style'] = $sStyle;
	}
	$sHtmlAttr = '';
	foreach($asExtraAttr as $sAttrName=>$sAttrValue)
	{
		$sHtmlAttr .= ' '.$sAttrName.'="'.$sAttrValue.'"';
	}
	if($bAutoClose)
	{
		return encapsulate('', "\n".'<'.$sTag.$sHtmlAttr, ' />');
	}
	else
	{
		return encapsulate($oText, "\n".'<'.$sTag.$sHtmlAttr.'>', '</'.$sTag.'>', $sInter);
	}
}
function encapsulate($oText, $sPre='', $sPost=false, $sInter='')
{
	if($sPost===false)
	{
		$sPost = $sPre;
	}
	if(is_array($oText))
	{
		$oText = implode($sPost.$sInter.$sPre, $oText);
	}
	return $sPre.$oText.$sPost;
}
function implodeAll($asText, $asKeyValueSeparator='',  $sRowSeparator='', $sKeyPre='', $sValuePost=false)
{
	if($sValuePost===false)
	{
		$sValuePost = $sKeyPre;
	}
	$asCombinedText = array();
	
	//if unique value for key value separator
	if(!is_array($asKeyValueSeparator))
	{
		$asKeyValueSeparator = array_combine(array_keys($asText), array_fill(0, count($asText), $asKeyValueSeparator));
	}
	
	foreach($asText as $sKey=>$sValue)
	{
		$asCombinedText[] = $sKeyPre.$sKey.$asKeyValueSeparator[$sKey].$sValue.$sValuePost;
	}
	return implode($sRowSeparator, $asCombinedText);
}
function getSelect($asListe, $sOptionIdKey, $sOptionDescKey='', $asAddedValues=array(), $sSelectedValue='', $sFormName='')
{
	$asOptions = array();
	$asOptionsHtml='';
	$sSelectName = str_replace('id_', '', $sOptionIdKey);
	if($sOptionDescKey=='')
	{
		$sOptionDescKey = $sOptionIdKey;
	}
	foreach($asListe as $asListValues)
	{
		$asOptions[$asListValues[$sOptionIdKey]] = $asListValues[$sOptionDescKey];
	}
	if(count($asAddedValues)>0)
	{
		$asOptions += $asAddedValues;
	}
	ksort($asOptions);
	foreach($asOptions as $sOptionId=>$sOptionDesc)
	{
		$asOptionsHtml .= "\n".'<option value="'.$sOptionId.'"'.($sOptionId==$sSelectedValue?' selected':'').'>'.$sOptionDesc.'</option>';
	}
	$sAutoSubmit = ($sFormName!='')?' onchange="document.forms[\''.$sFormName.'\'].submit();"':'';
	return '<select name="'.$sSelectName.'"'.$sAutoSubmit.'>'.$asOptionsHtml.'</select>';
}
function getMenu($sRequestedPage, $bAdmin)
{
	$sDisplayMenu = '';
	switch($sRequestedPage)
	{
		case 'logon':
			$sCurrentPage = DEFAULT_LOGGED_PAGE;
			break;
		case 'qcmProcess':
			$sCurrentPage = 'qcmCreator';
			break;
		default:
			$sCurrentPage = $sRequestedPage;
	}
	$asMenuItems = array('Accueil' => DEFAULT_LOGGED_PAGE, 'Cr&eacute;er un nouveau QCM' => 'qcmCreator', 'Compte' => 'account', 'Notes' => 'stats');
	if(isset($_SESSION['user']))
	{
		if($bAdmin)
		{
			$asMenuItems['Back-up'] = 'backUpCreator';
			$asMenuItems['Notes'] = 'statsAdmin';
			$asMenuItems['Compte'] = 'account_admin';
		}
		$asMenuItems['D&eacute;connexion'] = 'logout';
	}
	foreach ($asMenuItems as $sMenuItem => $sItemLink)
	{
		$sClass = 'top-left-rounded top-right-rounded'.(($sCurrentPage == $sItemLink)?' current':'');
		$sDisplayMenu .= getA('?page='.$sItemLink, $sMenuItem, getPageDescription($sItemLink) , $sClass)."\n";
	}
	return $sDisplayMenu;
}
function getClasses($aiClass=array(), $sFormName='')
{
	$iUserGrade = isset($aiClass['grade'])?$aiClass['grade']:'';
	$iUserClass = isset($aiClass['class'])?$aiClass['class']:'';
	$sAutoSubmit = ($sFormName!='')?' onchange="document.forms[\''.$sFormName.'\'].submit();"':'';
	$sSelects = '<select name="grade"'.$sAutoSubmit.'>';
	for($iGrade=MIN_GRADE;$iGrade>=MAX_GRADE;$iGrade--)
	{
		$sSelects .= '<option value="'.$iGrade.'"'.(($iGrade==$iUserGrade)?' SELECTED ':'').'>'.$iGrade.'</option>';
	}
	$sSelects .= '</select> &egrave;me <select name="class">';
	for($iClass=1;$iClass<=MAX_CLASS;$iClass++)
	{
		$sSelects .= '<option value="'.$iClass.'"'.(($iClass==$iUserClass)?' SELECTED ':'').'>'.$iClass.'</option>';
	}
	return $sSelects.'</select>';
}
function getChangelogTypeSelect($iChangeLogId=false, $sTypeSelected='')
{
	if(!$iChangeLogId)
	{
		$sStyle = '';
		$iChangeLogId = '0';
		$sChangelogAction = 'addChangelog';
	}
	else
	{
		$sStyle = 'class="nude" ';
		$sChangelogAction = 'modifyChangelog';
	}
	$sSelectType = '<select name="'.$iChangeLogId.'type" '.$sStyle.'onclick="setFormAction('.$iChangeLogId.', \''.$sChangelogAction.'\');">';
	foreach(getChangeLogEnum() as $sType)
	{
		$sSelectType .= '<option value="'.$sType.'"'.($sTypeSelected==$sType?' SELECTED':'').'>'.ucwords($sType).'</option>';
	}
	return $sSelectType.'</select>';
}
function getOutOfForm($iOutOf=20, $bSelectOnly=true, $sAction='', $sText='Note')
{
	$sFormStart = $sFormEnd = $sClass = $sOnChange = '';
	if(!$bSelectOnly)
	{
		if($sAction=='')
		{
			$sAction = $_SERVER['REQUEST_URI'];
		}
		$sFormStart = '<form action="'.$sAction.'" method="post" name="outof">'.$sText.' sur ';
		$sFormEnd = '</form>';
		$sClass = ' class="mergeWithTh"';
		$sOnChange = ' onchange="outof.submit();"';
	}
	foreach(array(5, 10, 20, 100) as $iOptionId)
	{
		$sSeleted = ($iOptionId==$iOutOf)?' selected':'';
		$asHtmlOptions[] = '<option value="'.$iOptionId.'"'.$sSeleted.'>'.$iOptionId.'</option>';
	}
	$sHtmlSelect = '<select'.$sClass.$sOnChange.' name="outofvalue">'.implode($asHtmlOptions).'</select>';
	return $sFormStart.$sHtmlSelect.$sFormEnd;
}

/* User Environment Functions */

function getElapsedTime($sStartTime)
{
	list($iCurrentMilli, $iCurrentSeconde, $iStartMilli, $iStartSeconde) = explode(' ', microtime().' '.$sStartTime);
	return round($iCurrentSeconde + $iCurrentMilli -($iStartSeconde + $iStartMilli), 4);
}
function getOS()
{
	$sUserAgent = strtolower($_SERVER['HTTP_USER_AGENT']);
	if(strpos($sUserAgent, 'mac')!==false || strpos($sUserAgent, 'apple')!==false)
	{
		$sUserOS = OS_MAC;
	}
	elseif(strpos($sUserAgent, 'windows')!==false)
	{
		$sUserOS = OS_WINDOWS;
	}
	else
	{
		$sUserOS = OS_LINUX;
	}
	return $sUserOS;
}
function getBrowser()
{
	$sUserAgent = strtolower($_SERVER['HTTP_USER_AGENT']);
	$asBrowsers = array('msie', 'firefox', 'opera', 'safari', 'chrome');
	foreach($asBrowsers as $sBrowser)
	{
		if(strpos($sUserAgent, $sBrowser)!==false)
		{
			break;
		}
	}
	return $sBrowser;
}
function feedback($sMessage)
{
	if(FEEDBACK_MAIL != '')
	{
		$sHtmlMessage = getHtml('Erreur SQL', 'h2').'<br />'.getHtml('User Id:'.$_SESSION['user'].' at '.date('r'), 'em').'<br />'.$sMessage;
		$sPlainMessage = strip_tags(str_replace('<br />', "\n", $sMessage));

		$iBoundary = uniqid("HTMLEMAIL");
		$sHeaders = 'From: feedback@'.DOMAIN."\r\n".
					'MIME-Version: 1.0'."\r\n".
					'Content-Type: multipart/alternative;'.
					'boundary = '.$iBoundary."\r\n\r\n".
					'MIME encoded Message'.
					'--'.$iBoundary."\r\n".
                	'Content-Type: text/plain; charset=ISO-8859-1'."\r\n".
                	'Content-Transfer-Encoding: base64'."\r\n\r\n".
					chunk_split(base64_encode($sPlainMessage)).
					'--'.$iBoundary."\r\n".
                	'Content-Type: text/html; charset=ISO-8859-1'."\r\n".
                	'Content-Transfer-Encoding: base64'."\r\n\r\n".
					chunk_split(base64_encode($sHtmlMessage));

		@mail(FEEDBACK_MAIL, 'SQL Error', '', $sHeaders);
	}
}

/* File Functions */

function remote($iActionId)
{
	if($iActionId==CREATE_NEW_BACKUP || $iActionId==ACCESS_POINT_NEW_VERSION)
	{
		createBackUp(true);
	}
	if($iActionId==ACCESS_POINT_LAST_VERSION || $iActionId==ACCESS_POINT_NEW_VERSION)
	{
		return download(encodeUrl(getLastBackUp('fileName')), array('sql'));
	}
}
function download($sEncodedFilePath, $asAuthExt)
{
	if($sEncodedFilePath=='' || count($asAuthExt)==0)
	{
		relocate(getError('Informations incompl&egrave;tes sur le fichier'));
	}
	$sFileName = basename(decodeUrl($sEncodedFilePath));
	$sFilePath = BACKUP_FOLDER.$sFileName;
	$sFileFullPath = dirname($_SERVER['SCRIPT_FILENAME'])."/".$sFilePath;

	if(!in_array(getExtension($sFileName), $asAuthExt))
	{
		relocate(getError('Fichier interdit : '.$sFileName));
	}
	elseif(!file_exists($sFilePath))
	{
		relocate(getError('Fichier introuvable : '.$sFileName));
	}
	else
	{
		//get mime type
		if(class_exists('finfo'))
		{
			$oFileInfo = new finfo(FILEINFO_MIME);
			$sMimetype = $oFileInfo->file($sFileFullPath);
		}
		else
		{
			$sMimetype = 'application/force-download';
		}

		//set headers
		header('Pragma: public');
		header('Expires: 0');
		header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
		header('Cache-Control: public');
		header('Content-Description: File Transfer');
		header('Content-Type: '.$sMimetype);
		header('Content-Disposition: attachment; filename='.$sFileName);
		header('Content-Transfer-Encoding: binary');
		header('Content-Length: '.filesize($sFilePath));

		// download
		if ($oFile = @fopen($sFilePath, 'rb'))
		{
			while(!feof($oFile))
			{
				print(fread($oFile, 1024*8));
				flush();
				if(connection_status() != 0)
				{
					@fclose($oFile);
				}
			}
			@fclose($oFile);
		}
	}
	return true;
}
function generateCsv($sFile, $sContent)
{
	if($sFile=='' || $sContent=='')
	{
		relocate(getError('Informations incompl&egrave;tes sur le fichier'));
	}
	else
	{
		$asCsvContent = unserialize(urldecode($sContent));
		$sCsvFileName = $sFile.'_'.date(FILE_DATE_FORMAT).'.csv';
		$oCsvFile = fopen(BACKUP_FOLDER.$sCsvFileName, 'w');
		if(!$oCsvFile)
		{
			relocate(getError(array('Impossible d\'&eacute;crire dans le dossier : '.BACKUP_FOLDER, 'V&eacute;rifiez les droits d\'&eacute;criture')));
		}
		else
		{
			foreach($asCsvContent as $asCsvLine)
			{
				fputs($oCsvFile, implode('|', $asCsvLine)."\n");
			}
			fclose($oCsvFile);
			return encodeUrl($sCsvFileName);
		}
	}
}
function resizeRenameImage($sFilePath, $sNewFileName)
{
	list($iWidth, $iHeight) = getimagesize($sFilePath);

	$sExt = getExtension($sFilePath);
	$sNewFilePath = IMAGE_FOLDER.$sNewFileName.'.'.$sExt;

	if($iWidth>MAX_IMAGE_WIDTH)
	{
		//new sizes
		$iResizedWidth = MAX_IMAGE_WIDTH;
		$iResizedHeight = ($iResizedWidth / $iWidth) * $iHeight;

		//create image from source
		$oSource = call_user_func('imagecreatefrom'.$sExt, $sFilePath);

		//Resize
		$oThumb = imagecreatetruecolor($iResizedWidth, $iResizedHeight);
		imagecopyresized($oThumb, $oSource, 0, 0, 0, 0, $iResizedWidth, $iResizedHeight, $iWidth, $iHeight);

		//Save
		call_user_func_array('image'.$sExt, array($oThumb, $sNewFilePath));
	}
	else
	{
		rename($sFilePath, $sNewFilePath);
	}
	unlink($sFilePath);
}
function getExtension($sFileName)
{
	$sFileInfo = pathinfo($sFileName);
	$sExt = strtolower($sFileInfo['extension']);
	return $sExt=='jpg'?'jpeg':$sExt;
}

/* Debug Functions */

function pre($sText, $sTitle='Test', $bDie=false, $bLog=false)
{
	if($bLog)
	{
		file_put_contents('log', ($sTitle!=''?$sTitle." :\n":'').print_r($sText, true)."\n\n");
	}
	echo '<fieldset class="rounded"><legend class="rounded">'.$sTitle.'</legend><pre>'.print_r($sText, true).'</pre></fieldset>';
	if($bDie)
	{
		die('[die() called by the test function '.__FUNCTION__.'()]');
	}
}
?>