-
Notifications
You must be signed in to change notification settings - Fork 1
/
etablissement.sql
105 lines (95 loc) · 3.17 KB
/
etablissement.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
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'));
--Menu
SELECT 'dynamic' AS component,
CASE WHEN $group_id=1
THEN sqlpage.read_file_as_text('index.json')
ELSE sqlpage.read_file_as_text('menu.json')
END AS properties;
-- Sous Menu
select 'dynamic' as component, sqlpage.run_sql('menu_parametres.sql') as properties;
-- Sous Menu établissement
select
'button' as component,
'sm' as size,
'pill' as shape;
select
'Mon établissement' as title,
'building-community' as icon,
'green' as color,
'etab_carte.sql?id=' || user_info.etab as link
FROM etab JOIN user_info on user_info.etab=etab.id join login_session on user_info.username=login_session.username WHERE $group_id=1 and login_session.id = sqlpage.cookie('session') GROUP BY etab.id;
select
'Ajouter un établissement' as title,
'etab_ajout.sql' as link,
'square-rounded-plus' as icon,
$group_id<3 as disabled,
'green' as outline;
-- Message si droits insuffisants sur une page
SELECT 'alert' as component,
'Attention !' as title,
'Vous ne possédez pas les droits suffisants pour accéder à cette page.'
as description_md,
'alert-circle' as icon,
TRUE as important,
'orange' as color
WHERE $restriction IS NOT NULL;
--Fiches
SELECT 'card' as component;
SELECT
CASE WHEN type='Lycée' THEN'red'
WHEN type='Collège' THEN 'orange'
ELSE 'yellow'
END as color,
CASE WHEN type='Lycée'
THEN'building-community'
WHEN type='Collège' THEN 'building-community'
ELSE 'building-cottage'
END as icon,
type || ' ' || nom_etab AS title,
description as description,
CASE WHEN $group_id>3
THEN '[
![](./icons/pencil.svg)
](etab_edit.sql?id='||etab.id||')[
![](./icons/trash.svg)
](etab_delete.sql?id='||etab.id||')'
END as footer_md,
CASE WHEN $group_id=1 and user_info.etab<>etab.id
THEN ''
WHEN $group_id=1 and user_info.etab=etab.id
THEN 'etab_carte.sql?id=' || etab.id
WHEN $group_id>1
THEN 'etab_carte.sql?id=' || etab.id
END as link
FROM etab LEFT JOIN user_info on user_info.etab=etab.id group by etab.id;
--Carte
SELECT
'map' as component,
12 as zoom,
400 as height,
AVG(Lat) as latitude,
AVG(Lon) as longitude FROM etab WHERE type<>'---';
SELECT
type || ' ' || nom_etab AS title,
Lat AS latitude,
Lon AS longitude,
CASE WHEN type='Lycée' THEN'red'
WHEN type='Collège' THEN 'orange'
ELSE 'yellow'
END as color,
CASE WHEN type='Lycée'
THEN'building-community'
WHEN type='Collège' THEN 'building-community'
ELSE 'building-cottage'
END as icon,
CASE WHEN $group_id=1 and user_info.etab<>etab.id
THEN ''
WHEN $group_id=1 and user_info.etab=etab.id
THEN 'etab_carte.sql?id=' || etab.id
WHEN $group_id>1
THEN 'etab_carte.sql?id=' || etab.id
END as link
FROM etab LEFT JOIN user_info on user_info.etab=etab.id group by etab.id;