1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
| class Application_Model_DbTable_Etudiant extends Zend_Db_Table_Abstract
{
protected $_name = 'th_etudiant_etu';
protected $_primary = 'etu_id_candidat';
/**
* Récupère toutes les infos relatives à un étudiant
* @param Integer $iduser : Identifiant de l'étudiant
*/
public function infosEtudiant($iduser)
{
$sql = "
SELECT p.prs_nom, p.prs_prenom, p.prs_adrel, p.prs_telephone,
e.etu_date_naissance, e.etu_adresse, e.etu_code_postal,
c.cmn_id AS id_commune_residence,
c.cmn_nom AS commune_residence,
etb.etb_id, etb.etb_nom, etb.etb_adresse, etb.etb_code_postal, etb.etb_telephone,
c1.cmn_id AS id_commune_etablissement,
c1.cmn_nom AS nom_commune_etablissement,
c1.cmn_nom AS commune_etablissement,
te.te_id, te.te_libelle,
ccr.ccr_libelle,
dsc.dsc_nom
FROM th_etudiant_etu AS e
INNER JOIN te_personne_prs AS p ON p.prs_id = e.etu_id_candidat
INNER JOIN te_concours_ccr AS ccr ON ccr.ccr_id = e.etu_id_concours
INNER JOIN te_discipline_dsc AS dsc ON dsc.dsc_id = ccr.ccr_id_discipline
LEFT OUTER JOIN tr_commune_cmn AS c ON c.cmn_id = e.etu_id_commune_residence
LEFT OUTER JOIN te_etablissement_etb As etb ON etb.etb_id = e.etu_id_etablissement_origine
LEFT OUTER JOIN tr_commune_cmn AS c1 ON c1.cmn_id = etb.etb_id_commune
LEFT OUTER JOIN tr_type_etablissement_te AS te ON te.te_id = etb.etb_id_type_etablissement
WHERE e.etu_id_candidat = ?
";
return $this->_db->fetchRow($sql, (int) $iduser);
} // Fin public function infosEtudiant($login)
/**
* Donne le stage auquel est éventuellement inscrit l'étudiant
* @param String $iduser : Identifiant de l'étudiant connecté
* @return Zend_Db_Table_Row_Abstract : Le stage ou NULL
*/
public function inscritStage($iduser)
{
$sql = "
SELECT p.prs_nom, p.prs_prenom,
ssn.ssn_id, ssn_code, ssn.ssn_libelle,
DATE_FORMAT(ssn.ssn_date_debut, '%d/%m/%Y') AS ssn_date_debut,
DATE_FORMAT(ssn.ssn_date_fin, '%d/%m/%Y') AS ssn_date_fin,
e.etb_nom, c.cmn_nom, d.dpt_numero
FROM te_personne_prs AS p
LEFT OUTER JOIN tj_cnd_inscrire_ssn_cis AS cis ON cis_id_candidat = p.prs_id
LEFT OUTER JOIN te_session_ssn AS ssn ON ssn.ssn_id = cis.cis_id_session
INNER JOIN th_stage_stg AS stg ON stg.stg_id_session = ssn.ssn_id
INNER JOIN te_etablissement_etb AS e ON e.etb_id = stg.stg_id_etablissement
INNER JOIN tr_commune_cmn AS c ON c.cmn_id = e.etb_id_commune
INNER JOIN tr_departement_dpt AS d ON d.dpt_id = c.cmn_id_departement
WHERE p.prs_id = ?
";
return $this->_db->fetchRow($sql, (int) $iduser);
} // Fin public function inscritStage($login)
/**
* Vérifie si un étudiant existe dans la BDD
* @param String $nom
* @param String $prenom
* @param Date $datenaissance
*/
public function identifierEtudiant($nom, $prenom, $datenaissance)
{
$sql = "
SELECT u.uti_login
FROM th_etudiant_etu AS e
INNER JOIN th_utilisateur_uti AS u ON u.uti_id_personne = e.etu_id_candidat
INNER JOIN te_personne_prs AS p ON p.prs_id = u.uti_id_personne
WHERE p.prs_nom = :nom
AND p.prs_prenom = :prenom
AND e.etu_date_naissance = :datenaissance
";
return $this->_db->fetchRow($sql,
array( 'nom'=>$nom,
'prenom'=>$prenom,
'datenaissance'=>$datenaissance
)
);
} // Fin public function identifierEtudiant($nom, $prenom, $datenaissance)
/**
* Inscrit un étudiant à un stage
* @param Integer $iduser : Identifiant de l'étudiant
* @param integer $idsession : Identifiant de la session de stage
*/
public function inscrire($iduser, $idsession)
{
$sql = "
INSERT INTO tj_cnd_inscrire_ssn_cis (cis_id_candidat, cis_id_session)
VALUES
(
:iduser,
:session
)
";
$stmt = $this->_db->query($sql, array( 'iduser' => (int) $iduser,
'session' => (int) $idsession
)
);
}
/**
* Supprime l'inscription d'un étudiant à un stage
* @param $iduser : Identifiant de l'étudiant
*/
public function desinscrire($iduser)
{
$sql = "
DELETE c
FROM tj_cnd_inscrire_ssn_cis AS c
WHERE cis_id_candidat = :iduser
";
$stmt = $this->_db->query($sql, array('iduser' => (int) $iduser));
}
} |
Résultat de requête avec join
Répondre avec citation
Envoyé par 5h4rk
Partager