link);
$i=0;
while($d = mysql_fetch_array($res))
{
$tabl_exo[$i]['id']=$d['id'];
$tabl_exo[$i]['nom']=$d['nom'];
$tabl_exo[$i]['description']=$d['description'];
$tabl_exo[$i]['niveau']=$d['niveau'];
$i++;
}
return $tabl_exo;
}
function getById($id) {
$strSQL = 'SELECT id, nom,description, exerciceFileName, corrigeFileName, niveau
FROM exercice
WHERE id='.$id;
$res = mysql_query($strSQL, $this->link);
$d = mysql_fetch_array($res);
return $d;
}
function getDuree($idExercice) {
$strSQL = 'SELECT duree.nom
FROM exercice, duree
WHERE exercice.duree=duree.id AND exercice.id='.$idExercice;
$res = mysql_query($strSQL, $this->link);
$d = mysql_fetch_array($res);
return $d['nom'];
}
/*
** Retourne le nb total d'exercice dans la base de donnees
*/
function getNbExo() {
$strSQL = "SELECT id FROM exercice";
$nb_exos = mysql_num_rows(mysql_query($strSQL));
return $nb_exos;
}
function getExerciceByTheme($id_theme)
{
$tab_result=array();
$req="select e.id as exercice_id, e.nom as exercice_nom ,m.nom as matiere_nom ,cl.nom as classe_nom, th.id as theme_id, th.nom as theme_nom
from matiere as m, classe as cl, theme as th
left join c_m_t_assoc as cmta on th.id= cmta.id_theme
left join exercice_theme_assoc as eta on cmta.id=eta.id_cmt_assoc
left join exercice as e on e.id=eta.id_exercice
where cmta.id_matiere=m.id
and cmta.id_classe=cl.id
and th.id='".$id_theme."'";
//echo $req."
";
$res=mysql_query($req,$this->link);
while($l=mysql_fetch_array($res))
{
array_push($tab_result,$l);
}
return $tab_result;
}
function getExerciceByNom($nom)
{
$tab_result=array();
$req="select e.id as exercice_id, e.nom as exercice_nom ,m.nom as matiere_nom ,cl.nom as classe_nom, th.id as theme_id, th.nom as theme_nom
from matiere as m, classe as cl, theme as th
left join c_m_t_assoc as cmta on th.id= cmta.id_theme
left join exercice_theme_assoc as eta on cmta.id=eta.id_cmt_assoc
left join exercice as e on e.id=eta.id_exercice
where cmta.id_matiere=m.id
and cmta.id_classe=cl.id
and e.nom like '%".$nom."%' ";
//echo $req."
";
$res=mysql_query($req,$this->link);
while($l=mysql_fetch_array($res))
{
array_push($tab_result,$l);
}
return $tab_result;
}
/*
** Retourne la liste des exercices associes aux themes $sqlTheme
** $sqlTheme est la liste des themes sous la forme d'une liste SQL clause IN:('1', '2', '4')
*/
function findExerciceByTheme($idClasse, $idMatiere, $arrTheme/*, $niveau = 3*/) {
$arrEx = array();
// Liste des sous-themes selectionnes
$sqlTheme = sqlBuildIN($arrTheme);
// Liste des sous-themes pour la classe/matiere/theme qui n'ont pas
// ete selectionnees
$cTheme = new theme();
$cTheme->link = $this->link;
$arrExcludeTheme = $cTheme->getThemeByClasseMatiereExcludeList($idClasse, $idMatiere, $arrTheme);
$sqlExcludeTheme = $cTheme->sqlBuildIN($arrExcludeTheme);
/*
// Dommage que mysql 4.1 n'est pas installe, autrement le pb se resoud par 1 seule requete, ci-dessous
if (count($arrExcludeTheme)) {
$strSQL = 'SELECT ex.id, COUNT(et.id_cmt_assoc) AS nbTh
FROM exercice ex, exercice_theme_assoc et
WHERE ex.id=et.id_exercice AND et.niveau LIKE "theme" AND et.id_cmt_assoc IN '.$sqlTheme.' AND ex.id NOT IN (
SELECT exercice_theme_assoc.id_exercice
FROM exercice_theme_assoc
WHERE exercice_theme_assoc.id_cmt_assoc IN '.$sqlExcludeTheme.')
AND ex.niveau <= '.$niveau.' GROUP BY ex.id ORDER BY nbTh, ex.niveau';
} else {
$strSQL = 'SELECT ex.id, COUNT(et.id_cmt_assoc) AS nbTh
FROM exercice ex, exercice_theme_assoc et
WHERE ex.id=et.id_exercice AND et.niveau LIKE "theme"
AND ex.niveau <= '.$niveau.' GROUP BY ex.id ORDER BY nbTh, ex.niveau';
}
*/
// Exercices a exclure
$arrExcludeEx = array();
$strSQL = 'SELECT DISTINCT ex.id
FROM exercice ex, exercice_theme_assoc et
WHERE ex.id=et.id_exercice AND et.id_cmt_assoc IN '.$sqlExcludeTheme.' AND et.niveau LIKE "theme"';// AND ex.niveau <= '.$niveau;
$res = mysql_query($strSQL, $this->link);
while ($d = mysql_fetch_array($res)) {
array_push($arrExcludeEx, $d['id']);
}
$strSQL = 'SELECT ex.id, COUNT(et.id_cmt_assoc) AS nbTh
FROM exercice ex, exercice_theme_assoc et
WHERE ex.id=et.id_exercice AND et.niveau LIKE "theme" AND et.id_cmt_assoc IN '.$sqlTheme;
if (count($arrExcludeEx)) {
$strSQL .= ' AND ex.id NOT IN ('.implode(',', $arrExcludeEx).')';
}
// AND ex.niveau <= '.$niveau.'
$strSQL .= ' GROUP BY ex.id ORDER BY nbTh, ex.niveau';
//echo $strSQL;
$res = mysql_query($strSQL, $this->link);
while ($d = mysql_fetch_array($res)) {
array_push($arrEx, $d);
}
return $arrEx;
}
/*
** Retourne la liste des exercices associes aux sous-themes $sqlTheme
** $sqlTheme doit etre sous la forme :('1', '2', '4')
*/
/*
function findExerciceBySousTheme($idClasse, $idMatiere, $idTheme, $arrTheme)
{
$arrEx = array();
// Liste des sous-themes selectionnes
$sqlTheme = sqlBuildIN($arrTheme);
// Liste des sous-themes pour la classe/matiere/theme qui n'ont pas
// ete selectionnees
$cTheme = new theme();
$cTheme->link = $this->link;
$arrExcludeTheme = $cTheme->getSousThemeByClasseMatiereThemeExcludeList($idClasse, $idMatiere, $idTheme, $arrTheme);
if (count($arrExcludeTheme)) {
$sqlExcludeTheme = $cTheme->sqlBuildIN($arrExcludeTheme);
}
// Recupere la liste des exercices devant etre exclus des exercices affiches
$arrExcludeEx = array();
if (count($arrExcludeTheme)) {
$strSQL = 'SELECT DISTINCT id_exercice
FROM exercice ex, exercice_theme_assoc et
WHERE ex.id = et.id_exercice AND et.niveau LIKE "sous-theme" AND et.id_cmt_assoc IN '.$sqlExcludeTheme;
// AND ex.niveau <='.$niveau;
$res = mysql_query($strSQL, $this->link);
while ($d = mysql_fetch_array($res)) {
array_push($arrExcludeEx, $d['id_exercice']);
}
}
$strSQL = 'SELECT ex.id, COUNT(et.id_cmt_assoc) AS nbTh
FROM exercice ex, exercice_theme_assoc et
WHERE ex.id=et.id_exercice AND et.niveau LIKE "sous-theme" AND et.id_cmt_assoc IN '.$sqlTheme;
if (count($arrExcludeEx)) {
$sqlEx = sqlBuildIN($arrExcludeEx);
$strSQL .= ' AND ex.id NOT IN '.$sqlEx;
}
// AND ex.niveau <= '.$niveau.'
$strSQL .= '
GROUP BY ex.id
ORDER BY nbTh, ex.niveau';
$res = mysql_query($strSQL, $this->link);
while ($d = mysql_fetch_array($res)) {
array_push($arrEx, $d);
}
return $arrEx;
}
*/
/*
** Retourne les infos sur un exercice par son id. Les infos retournees sont :
** la/les classe(s), la/les matiere(s), theme(s) et sous-theme(s)
** $arrExInfo[classe][nom] : nom de la classe
** $arrExInfo[classe][matiere][nom] : nom de la matiere
** $arrExInfo[classe][matiere][theme] = array : tableau de themes [nom] : nom du theme, [id] : id du theme
** $arrExInfo[classe][matiere][sous-theme] = array : si count([theme]) > 1: [nom], [id]
*/
function getInfoById($idExercice) {
$arrExInfo = array();
$strSQL = 'SELECT et.niveau FROM exercice_theme_assoc et WHERE id_exercice='.$idExercice.' GROUP BY id_exercice';
$res = mysql_query($strSQL, $this->link);
$d = mysql_fetch_array($res);
if ($d['niveau'] == 'theme'){
$strSQL = 'SELECT et.id_exercice AS idExercice, et.id_cmt_assoc AS idAssoc, t.nom as nomTheme, t.id AS idTheme,
c.id AS idClasse, c.nom AS nomClasse, m.id AS idMatiere, m.nom AS nomMatiere
FROM exercice_theme_assoc et, c_m_t_assoc cmt, theme t, classe c, matiere m
WHERE et.id_cmt_assoc=cmt.id AND cmt.id_theme=t.id AND cmt.id_classe=c.id AND cmt.id_matiere=m.id
AND et.id_exercice='.$idExercice.'
ORDER BY c.id, m.id, t.id';
$res = mysql_query($strSQL, $this->link);
while ($d = mysql_fetch_assoc($res)) {
array_push($arrExInfo, $d);
}
if (count($arrExInfo)) {
$arrExInfo['type'] = CONST_THEME;
}
} else {
// sous-theme
$strSQL = 'SELECT et.id_exercice AS idExercice, et.id_cmt_assoc AS idAssoc, c.id AS idClasse, c.nom AS nomClasse,
m.id AS idMatiere, m.nom AS nomMatiere, t.nom AS nomTheme, t.id AS idTheme, st.nom AS nomSousTheme, cmt.id AS cmtIdAssoc
FROM theme t, theme st, classe c, matiere m, c_m_t_assoc cmt, exercice_theme_assoc et
WHERE et.id_cmt_assoc=st.id AND cmt.id=st.id_cmt_assoc AND cmt.id_theme=t.id
AND et.id_exercice='.$idExercice.' AND cmt.id_matiere=m.id AND cmt.id_classe=c.id
ORDER BY c.id, m.id, t.id';
//echo $strSQL.'
';
$res = mysql_query($strSQL, $this->link);
while ($d = mysql_fetch_assoc($res)) {
array_push($arrExInfo, $d);
}
if (count($arrExInfo)) {
$arrExInfo['type'] = CONST_SOUS_THEME;
}
}
return $arrExInfo;
}
function getInfoAll() {
$arrExInfo = array();
$strSQL = 'SELECT e.nom, e.description,n.nom as nomNiveau, d.nom as nomDuree, et.id_exercice AS idExercice, et.id_cmt_assoc AS idAssoc, t.nom as nomTheme, t.id AS idTheme,
c.id AS idClasse, c.nom AS nomClasse, m.id AS idMatiere, m.nom AS nomMatiere
FROM exercice_theme_assoc et, c_m_t_assoc cmt, theme t, classe c, matiere m, exercice e, niveau n, duree d
WHERE et.id_cmt_assoc=cmt.id AND cmt.id_theme=t.id AND cmt.id_classe=c.id AND cmt.id_matiere=m.id AND e.id=et.id_exercice AND e.niveau=n.id AND e.duree=d.id
ORDER BY c.id, m.id, t.id';
$res = mysql_query($strSQL, $this->link);
while ($d = mysql_fetch_assoc($res)) {
array_push($arrExInfo, $d);
}
if (count($arrExInfo)) {
$arrExInfo['type'] = CONST_THEME;
}
return $arrExInfo;
}
function listeAleatoireParTheme($idTheme/*, $niveau*/) {
$arrEx = array();
$strSQL = 'SELECT id_theme FROM c_m_t_assoc WHERE id='.$idTheme;
$res = mysql_query($strSQL, $this->link);
$d = mysql_fetch_array($res);
$strSQL = 'SELECT DISTINCT ex.id
FROM exercice ex, exercice_theme_assoc et, c_m_t_assoc cmt, theme st
WHERE ex.id=et.id_exercice AND et.id_cmt_assoc=st.id AND st.id_cmt_assoc=cmt.id
AND cmt.id_theme='.$d['id_theme'].' ORDER BY rand()';
// AND ex.niveau <='.$niveau.'
$res = mysql_query($strSQL, $this->link);
while ($d = mysql_fetch_assoc($res)) {
array_push($arrEx, $d);
}
return $arrEx;
}
function listeAleatoirePlusDe30min(/*$niveau*/) {
$arrEx = array();
$strSQL = 'SELECT DISTINCT ex.id
FROM exercice ex, exercice_theme_assoc et, c_m_t_assoc cmt, theme st
WHERE ex.id=et.id_exercice AND et.id_cmt_assoc=st.id AND st.id_cmt_assoc=cmt.id ';
// $strSQL .= 'AND ex.niveau <='.$niveau.' ';
$strSQL .= 'AND ex.duree >= 3 ORDER BY rand()';
$res = mysql_query($strSQL, $this->link);
while ($d = mysql_fetch_assoc($res)) {
array_push($arrEx, $d);
}
return $arrEx;
}
/*
**
** FONCTIONS DE MAINTENANCE DES DONNEES
**
*/
function delete($idExercice) {
// Suppression du fichier
$storageLocation = DATA_STORAGE_ROOT_LOCATION.FOLDER_SEPARATOR.$idExercice;
$this->rm($storageLocation);
//Supprime la base de donnees
$strSQL = 'DELETE FROM exercice WHERE id='.$idExercice;
$msg= $strSQL."
";
$resA=mysql_query($strSQL, $this->link);
$strSQL = 'DELETE FROM exercice_theme_assoc WHERE id_exercice='.$idExercice;
$msg.= $strSQL."
";
$resB=mysql_query($strSQL, $this->link);
if($resA && $resB){$msg="L'exercice est supprimé.";}
else{$msg="Echec, contactez votre administrateur.";}
return $msg;
}
/*
** Ajout d'un exercice associe a un ou plusieurs themes OU un ou plusieurs sous-themes
*/
function addByTheme($nom, $duree, $niveau, $sourceFile, $corrigeFile, $themeOuSousTheme, $arrTheme) {
if (count($arrTheme) == 0) {
return $this->ERR_NB_THEME;
}
// Preparation de l'enregistrement
$exName = basename($sourceFile['name']);
$corrigeName = basename($corrigeFile['name']);
$strSQL = "INSERT INTO exercice (nom, exerciceFileName, corrigeFileName, niveau, duree)
VALUES ('".addslashes($nom)."', '".'enonce.'.GetExtensionName($sourceFile['name'])."', '".'corrige.'.GetExtensionName($corrigeFile['name'])."', '".$niveau."', '".$duree."')";
// echo $strSQL."
";
$res = mysql_query($strSQL, $this->link);
$idExercice = mysql_insert_id();
$storageLocation = DATA_STORAGE_ROOT_LOCATION.FOLDER_SEPARATOR.$idExercice;
if (is_dir($storageLocation)) {
mysql_query('DELETE FROM exercice WHERE id='.$idExercice, $this->link);
return $this->ERR_FOLDER_EXISTS;
}
if (mkdir($storageLocation, 0777) == false) {
mysql_query('DELETE FROM exercice WHERE id='.$idExercice, $this->link);
return $this->ERR_MKDIR;
}
// Copie des fichiers
if (move_uploaded_file($sourceFile['tmp_name'],
$storageLocation.FOLDER_SEPARATOR.'enonce.'.GetExtensionName($sourceFile['name'])) == false) {
$this->rm($storageLocation);
mysql_query('DELETE FROM exercice WHERE id='.$idExercice, $this->link);
return $this->ERR_EXERCICE_COPY;
}
if (move_uploaded_file($corrigeFile['tmp_name'],
$storageLocation.FOLDER_SEPARATOR.'corrige.'.GetExtensionName($corrigeFile['name'])) == false) {
$this->rm($storageLocation);
mysql_query('DELETE FROM exercice WHERE id='.$idExercice, $this->link);
return $this->ERR_CORRIGE_COPY;
}
// Enregistre les themes ou les sous-themes associes
for ($i = 0; $i < count($arrTheme); $i++) {
$strSQL = "INSERT INTO exercice_theme_assoc (id_exercice, id_cmt_assoc, niveau)
VALUES ('".$idExercice."', '".$arrTheme[$i]."', '".$themeOuSousTheme."')";
$res = mysql_query($strSQL, $this->link);
}
return $this->OK;
}
function updateByTheme($idExercice, $idThemeActuel, $idNewTheme) {
$strSQL = 'UPDATE exercice_theme_assoc
SET id_cmt_assoc='.$idNewTheme.'
WHERE id_exercice='.$idExercice.' AND id_cmt_assoc='.$idThemeActuel.' AND niveau="'.CONST_THEME.'"';
mysql_query($strSQL, $this->link);
}
/*
function updateSousTheme($idExercice, $idSousThemeActuel, $idNewSousTheme) {
$strSQL = 'UPDATE exercice_theme_assoc
SET id_cmt_assoc='.$idNewSousTheme.'
WHERE id_exercice='.$idExercice.' AND id_cmt_assoc='.$idSousThemeActuel.' AND niveau="'.CONST_SOUS_THEME.'"';
mysql_query($strSQL, $this->link);
}
*/
function updateDescriptifByExo($idExo, $descr)
{
$req="update exercice set nom='$descr' where id='$idExo'";
$res=mysql_query($req,$this->link);
if(!$res){$msg="Erreur
";}
else{$msg="L'exercice est modifié
";}
return $msg;
}
}
?>link);
while ($d = mysql_fetch_array($res)) {
array_push($arrNiv, $d);
}
return $arrNiv;
}
function getByID($id) {
$strSQL = 'SELECT nom FROM niveau WHERE id='.$id;
$res = mysql_query($strSQL, $this->link);
$d= mysql_fetch_array($res);
return $d['nom'];
}
}
?>