-
Notifications
You must be signed in to change notification settings - Fork 3
/
aesh_suivi.sql
197 lines (182 loc) · 7.88 KB
/
aesh_suivi.sql
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
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
SELECT 'redirect' AS component,
'signin.sql?error' AS link
WHERE NOT EXISTS (SELECT 1 FROM login_session WHERE id=sqlpage.cookie('session'));
SET group_id = (SELECT user_info.groupe FROM login_session join user_info on user_info.username=login_session.username WHERE id = sqlpage.cookie('session'));
SELECT 'redirect' AS component,
'etablissement.sql?restriction' AS link
WHERE $group_id<'2';
--Menu
SELECT 'dynamic' AS component, sqlpage.read_file_as_text('menu.json') AS properties;
-- Menu spécifique AESH : modifier
select
'button' as component,
'sm' as size,
'pill' as shape;
select
'Modifier' as title,
'pencil' as icon,
'orange' as outline,
$group_id<3 as disabled,
'aesh_edit.sql?id='||$id||'&username='||username as link FROM aesh WHERE aesh.id = $id;
select
'Planning' as title,
'clock-share' as icon,
'red' as outline,
$group_id<3 as disabled,
'upload_edt_form.sql?id='||$id as link FROM aesh where aesh.id=$id;
-- écrire le nom de l'AESH dans le titre de la page
SELECT
'datagrid' as component;
SELECT
'AESH'||' - '||'Quotité : ' || quotite || ' h' as title,
aesh_name||' '||aesh_firstname as description, 'orange' as color, 1 as active
FROM aesh WHERE aesh.id = $id;
SELECT
CASE WHEN $group_id>2
THEN tel_aesh
ELSE 'numéro masqué'
END as title,
courriel_aesh as description
FROM aesh WHERE aesh.id = $id;
--Onglets
SET tab=coalesce($tab,'Profils');
select 'tab' as component,
TRUE as center;
select 'Profils' as title, 'briefcase' as icon, 1 as active, 'aesh_suivi.sql?id='||$id||'&tab=Profils' as link, CASE WHEN $tab='Profils' THEN 'orange' ELSE 'green' END as color;
select 'Détails' as title, 'user-plus' as icon, 0 as active, 'aesh_suivi.sql?id='||$id||'&tab=Détails' as link, CASE WHEN $tab='Détails' THEN 'orange' ELSE 'green' END as color;
select 'Liste' as title, 'user-plus' as icon, 1 as active, 'aesh_suivi.sql?id='||$id||'&tab=Liste' as link, CASE WHEN $tab='Liste' THEN 'orange' ELSE 'green' END as color;
select 'Graphique' as title, 'chart-bar' as icon, 1 as active, 'aesh_suivi.sql?id='||$id||'&tab=Graphique' as link, CASE WHEN $tab='Graphique' THEN 'orange' ELSE 'green' END as color;
select 'Carte' as title, 'map-question' as icon, 1 as active, 'aesh_suivi.sql?id='||$id||'&tab=Carte' as link, CASE WHEN $tab='Carte' THEN 'orange' ELSE 'green' END as color;
select 'Emploi du temps' as title, 'briefcase' as icon, 1 as active, 'aesh_suivi.sql?id='||$id||'&tab=EDT' as link, CASE WHEN $tab='EDT' THEN 'orange' ELSE 'green' END as color;
-- Résumé de suivis des élèves
SELECT 'card' as component,
4 as columns
WHERE $tab='Profils';
SELECT
eleve.nom || ' '|| eleve.prenom || ' (' || eleve.classe || ') ' AS title,
'green' as color,
CASE WHEN EXISTS (SELECT eleve.id FROM image WHERE eleve.id=image.eleve_id)
THEN image_url
ELSE './icons/profil.png'
END as top_image,
CASE WHEN EXISTS (SELECT eleve.id FROM amenag WHERE eleve.id=amenag.eleve_id)
THEN 'Mission de l''AESH : '|| suivi.mission
ELSE 'non saisi'
END as description,
group_concat(DISTINCT dispositif.dispo) as footer,
'[
](notification.sql?id='||eleve.id||'&tab=Profil) [
](notification.sql?id='||eleve.id||'&tab=Suivi)' as footer_md,
'notification.sql?id='||eleve.id||'&tab=Profil' as link
FROM eleve INNER JOIN affectation on eleve.id=affectation.eleve_id LEFT JOIN amenag on amenag.eleve_id=eleve.id JOIN dispositif on dispositif.id=affectation.dispositif_id JOIN etab on eleve.etab_id=etab.id JOIN suivi on suivi.eleve_id=eleve.id LEFT JOIN image on eleve.id=image.eleve_id JOIN aesh on suivi.aesh_id=aesh.id WHERE aesh_id=$id and $tab='Profils' GROUP BY eleve.id ORDER BY eleve.nom ASC;
-- Fiche détaillée
SELECT 'table' as component
WHERE $tab='Détails';
SELECT
eleve.nom||' '||eleve.prenom as élève,
CASE WHEN EXISTS (SELECT eleve.id FROM amenag WHERE eleve.id=amenag.eleve_id)
THEN amenag.info
ELSE 'non saisi'
END AS commentaires,
CASE WHEN EXISTS (SELECT eleve.id FROM amenag WHERE eleve.id=amenag.eleve_id)
THEN amenag.amenagements
ELSE 'non saisi'
END AS Aménagements,
CASE WHEN EXISTS (SELECT eleve.id FROM amenag WHERE eleve.id=amenag.eleve_id)
THEN amenag.objectifs
ELSE 'non saisi'
END as Objectifs
FROM eleve INNER JOIN affectation on eleve.id=affectation.eleve_id LEFT JOIN amenag on amenag.eleve_id=eleve.id JOIN dispositif on dispositif.id=affectation.dispositif_id JOIN etab on eleve.etab_id=etab.id JOIN suivi on suivi.eleve_id=eleve.id JOIN aesh on suivi.aesh_id=aesh.id WHERE aesh_id=$id and $tab='Détails' GROUP BY eleve.id ORDER BY eleve.nom ASC;
-- Liste des suivis
SELECT
'text' as component,
'Suivis' as contents
WHERE $tab='Liste';
SELECT 'table' as component,
'Élève' as markdown,
1 as sort,
1 as search
WHERE $tab='Liste';
SELECT
eleve.nom as Nom,
eleve.prenom as Prénom,
suivi.temps as Temps,
CASE
WHEN ind=1 THEN 'individuel'
WHEN mut=2 THEN 'mutualisé'
END AS Suivi,
eleve.classe AS Classe,
etab.type as Niveau,
etab.nom_etab AS Établissement,
'[
](notification.sql?id='||eleve.id||'&tab=Profil "Dossier élève")' as Élève
FROM eleve INNER JOIN suivi on suivi.eleve_id=eleve.id FULL JOIN etab on eleve.etab_id = etab.id LEFT JOIN aesh on suivi.aesh_id=aesh.id WHERE aesh_id = $id and $tab='Liste';
--Graphique
SELECT
'chart' as component,
'bar' as type,
TRUE as stacked,
1 as labels,
1 as horizontal,
'Répartition du suivi' as title,
'green' as color,
'orange' as color,
'red' as color,
'indigo' as color,
'yellow' as color,
'purple' as color,
35 as xmax,
7 as xticks,
1 as toolbar
WHERE $tab='Graphique';
SELECT
eleve.nom ||' '||eleve.prenom ||' ('||eleve.classe||')' as series,
'Missions' as x,
coalesce(sum((suivi.temps*1.00)/mut),0) as value
FROM eleve INNER JOIN etab on eleve.etab_id = etab.id JOIN suivi on suivi.eleve_id=eleve.id JOIN aesh on suivi.aesh_id=aesh.id WHERE aesh_id = $id and $tab='Graphique' GROUP BY eleve.nom;
SELECT
'ULIS' as series,
'Missions' as x,
coalesce(tps_ULIS,0) as value
FROM aesh WHERE aesh.id=$id and tps_ULIS<>0 and $tab='Graphique';
SELECT
'Activités' as series,
'Missions' as x,
coalesce(tps_mission,0) as value
FROM aesh WHERE aesh.id=$id and tps_mission<>0 and $tab='Graphique';
SELECT
'Synthèse(s)' as series,
'Missions' as x,
coalesce(tps_synthese,0) as value
FROM aesh WHERE aesh.id=$id and tps_synthese<>0 and $tab='Graphique';
SELECT
'Quotité' as series,
'Quotité' as x,
coalesce(quotite,0) as value
FROM aesh WHERE aesh.id=$id and $tab='Graphique';
--Carte
SET AVG_Lat = (SELECT AVG(Lat) FROM etab JOIN eleve on eleve.etab_id = etab.id join suivi on suivi.eleve_id=eleve.id join aesh on suivi.aesh_id=aesh.id WHERE aesh_id = $id);
SET AVG_Lon = (SELECT AVG(Lon) FROM etab JOIN eleve on eleve.etab_id = etab.id join suivi on suivi.eleve_id=eleve.id join aesh on suivi.aesh_id=aesh.id WHERE aesh_id = $id);
SELECT
'map' as component,
12 as zoom,
400 as height,
$AVG_Lat as latitude,
$AVG_Lon as longitude
where $tab='Carte';
SELECT
type || ' ' || nom_etab as title,
Lat AS latitude,
Lon AS longitude,
'etab_notif.sql?id=' || etab.id as link
FROM etab JOIN eleve on eleve.etab_id = etab.id join suivi on suivi.eleve_id=eleve.id join aesh on suivi.aesh_id=aesh.id WHERE aesh_id = $id and $tab='Carte';
select
'card' as component,
1 as columns;
select
edt_url as top_image,
'mis en ligne, le '||strftime('%d/%m/%Y à %Hh%M', created_at) as description
FROM edt WHERE aesh_id = $id and $tab='EDT';