2 |
jpm |
1 |
#tables de la base de donnees
|
|
|
2 |
|
|
|
3 |
|
|
|
4 |
|
|
|
5 |
|
|
|
6 |
#table contenant les motifs d'absence
|
|
|
7 |
|
|
|
8 |
DROP TABLE IF EXISTS gestion_motif_absence;
|
|
|
9 |
|
|
|
10 |
CREATE TABLE gestion_motif_absence (
|
|
|
11 |
gma_id_motif TINYINT(3) UNSIGNED NOT NULL,
|
|
|
12 |
gma_libelle_motif VARCHAR(255) NOT NULL,
|
|
|
13 |
gma_type_rtt CHAR(1) NOT NULL,
|
|
|
14 |
PRIMARY KEY(gma_id_motif)
|
|
|
15 |
);
|
|
|
16 |
|
|
|
17 |
# table contenant le montant des deplacements, date du deplacement, les objets du trajet et la description du trajet
|
|
|
18 |
DROP TABLE IF EXISTS gestion_frais_kilometrique;
|
|
|
19 |
|
|
|
20 |
|
|
|
21 |
CREATE TABLE gestion_frais_kilometrique (
|
|
|
22 |
gfk_id_frais_kilometrique INTEGER(10) UNSIGNED NOT NULL,
|
|
|
23 |
gfk_taux_kilometre FLOAT NOT NULL,
|
|
|
24 |
PRIMARY KEY(gfk_id_frais_kilometrique)
|
|
|
25 |
);
|
|
|
26 |
|
|
|
27 |
#contient le statut des utilisateurs
|
|
|
28 |
|
|
|
29 |
DROP TABLE IF EXISTS gestion_statut;
|
|
|
30 |
|
|
|
31 |
CREATE TABLE gestion_statut (
|
|
|
32 |
gs_id_statut TINYINT(3) UNSIGNED NOT NULL,
|
|
|
33 |
gs_libelle_statut VARCHAR(255) NOT NULL,
|
|
|
34 |
PRIMARY KEY(gs_id_statut)
|
|
|
35 |
);
|
|
|
36 |
|
|
|
37 |
# contient le sidentifiant sdes notes de frais qui correspondent au numero du plan comptable, ainsi que le libelle du plan comptable
|
|
|
38 |
|
|
|
39 |
DROP TABLE IF EXISTS gestion_note_frais;
|
|
|
40 |
|
|
|
41 |
CREATE TABLE gestion_note_frais (
|
|
|
42 |
gnf_id_frais INTEGER(10) UNSIGNED NOT NULL,
|
|
|
43 |
gnf_libelle_frais VARCHAR(255) NOT NULL,
|
|
|
44 |
PRIMARY KEY(gnf_id_frais)
|
|
|
45 |
);
|
|
|
46 |
|
|
|
47 |
#stocke les differentes categorie sde projet existantes
|
|
|
48 |
DROP TABLE IF EXISTS gestion_categorie;
|
|
|
49 |
|
|
|
50 |
CREATE TABLE gestion_categorie (
|
|
|
51 |
gc_id_categorie INTEGER(10) UNSIGNED NOT NULL,
|
|
|
52 |
gc_libelle_categorie VARCHAR(255) NULL,
|
|
|
53 |
PRIMARY KEY(gc_id_categorie)
|
|
|
54 |
);
|
|
|
55 |
|
|
|
56 |
#contient les informations relatives a un projet
|
|
|
57 |
DROP TABLE IF EXISTS gestion_projet;
|
|
|
58 |
|
|
|
59 |
CREATE TABLE gestion_projet (
|
|
|
60 |
gp_id_projet INTEGER(10) UNSIGNED NOT NULL,
|
|
|
61 |
gc_id_categorie INTEGER(10) UNSIGNED NOT NULL,
|
|
|
62 |
gp_nom_projet VARCHAR(255) NOT NULL,
|
|
|
63 |
gp_description VARCHAR(255) NULL,
|
|
|
64 |
gp_date_debut DATE NULL,
|
|
|
65 |
gp_duree_prevue FLOAT NULL,
|
|
|
66 |
gp_avancement INTEGER(11) NULL,
|
|
|
67 |
PRIMARY KEY(gp_id_projet),
|
|
|
68 |
FOREIGN KEY(gc_id_categorie)
|
|
|
69 |
REFERENCES gestion_categorie(gc_id_categorie)
|
|
|
70 |
ON DELETE NO ACTION
|
|
|
71 |
ON UPDATE NO ACTION
|
|
|
72 |
);
|
|
|
73 |
|
|
|
74 |
#contient les informations relatives aux utilisateurs
|
|
|
75 |
|
|
|
76 |
DROP TABLE IF EXISTS gestion_utilisateur;
|
|
|
77 |
|
|
|
78 |
CREATE TABLE gestion_utilisateur (
|
|
|
79 |
gu_id_utilisateur INTEGER(10) UNSIGNED NOT NULL,
|
|
|
80 |
gs_id_statut TINYINT(3) UNSIGNED NOT NULL,
|
|
|
81 |
gu_nom VARCHAR(255) NOT NULL,
|
|
|
82 |
gu_prenom VARCHAR(255) NOT NULL,
|
|
|
83 |
gu_password VARCHAR(255) NOT NULL,
|
|
|
84 |
gu_email VARCHAR(255) NOT NULL,
|
|
|
85 |
gu_telephone INTEGER(10) UNSIGNED NULL,
|
|
|
86 |
gu_adresse VARCHAR(255) NULL,
|
|
|
87 |
gu_code_postal INTEGER(10) UNSIGNED NULL,
|
|
|
88 |
gu_ville VARCHAR(255) NULL,
|
|
|
89 |
gu_quota_heures_supp FLOAT NULL,
|
|
|
90 |
gu_conges_payes FLOAT NULL,
|
|
|
91 |
gu_temps_de_travail FLOAT NULL,
|
|
|
92 |
gu_admin TINYINT(3) UNSIGNED NULL,
|
|
|
93 |
gu_admin2 TINYINT(3) UNSIGNED NULL,/*indique si l'utilisateur doit figurer dans l'affichage des recapitulatifs*/
|
|
|
94 |
gu_notes VARCHAR(255) NULL,
|
|
|
95 |
PRIMARY KEY(gu_id_utilisateur),
|
|
|
96 |
FOREIGN KEY(gs_id_statut)
|
|
|
97 |
REFERENCES gestion_statut(gs_id_statut)
|
|
|
98 |
ON DELETE NO ACTION
|
|
|
99 |
ON UPDATE NO ACTION
|
|
|
100 |
);
|
|
|
101 |
|
|
|
102 |
#stocke les dates de trvail et leurs durees, ainsi que l'utilisateur et le projet concernes
|
|
|
103 |
|
|
|
104 |
DROP TABLE IF EXISTS gestion_travail;
|
|
|
105 |
CREATE TABLE gestion_travail (
|
|
|
106 |
gp_id_projet INTEGER(10) UNSIGNED NOT NULL,
|
|
|
107 |
gu_id_utilisateur INTEGER(10) UNSIGNED NOT NULL,
|
|
|
108 |
gt_date_travail DATE NOT NULL,
|
|
|
109 |
gt_duree_travail FLOAT NOT NULL,
|
|
|
110 |
PRIMARY KEY(gp_id_projet, gu_id_utilisateur,gt_date_travail),
|
|
|
111 |
FOREIGN KEY(gp_id_projet)
|
|
|
112 |
REFERENCES gestion_projet(gp_id_projet)
|
|
|
113 |
ON DELETE NO ACTION
|
|
|
114 |
ON UPDATE NO ACTION,
|
|
|
115 |
FOREIGN KEY(gu_id_utilisateur)
|
|
|
116 |
REFERENCES gestion_utilisateur(gu_id_utilisateur)
|
|
|
117 |
ON DELETE NO ACTION
|
|
|
118 |
ON UPDATE NO ACTION
|
|
|
119 |
);
|
|
|
120 |
|
|
|
121 |
#contient les dates de debut et de fin d'absence, ainsi que la date d'envoi de la lettre d'absence
|
|
|
122 |
DROP TABLE IF EXISTS gestion_absence;
|
|
|
123 |
|
|
|
124 |
|
|
|
125 |
CREATE TABLE gestion_absence (
|
|
|
126 |
gu_id_utilisateur INTEGER(10) UNSIGNED NOT NULL,
|
|
|
127 |
gma_id_motif TINYINT(3) UNSIGNED NOT NULL,
|
|
|
128 |
ga_date_debut DATE NOT NULL,
|
|
|
129 |
ga_date_fin DATE NOT NULL,
|
|
|
130 |
ga_date_envoi_lettre DATE NOT NULL,
|
|
|
131 |
PRIMARY KEY(gu_id_utilisateur, gma_id_motif, ga_date_debut),
|
|
|
132 |
FOREIGN KEY(gu_id_utilisateur)
|
|
|
133 |
REFERENCES gestion_utilisateur(gu_id_utilisateur)
|
|
|
134 |
ON DELETE NO ACTION
|
|
|
135 |
ON UPDATE NO ACTION,
|
|
|
136 |
FOREIGN KEY(gma_id_condition)
|
|
|
137 |
REFERENCES gestion_motif_absence(gma_id_motif)
|
|
|
138 |
ON DELETE NO ACTION
|
|
|
139 |
ON UPDATE NO ACTION
|
|
|
140 |
);
|
|
|
141 |
|
|
|
142 |
#fait le lien entre l'utilisateur et les frais kilometriques qu'il occasionne
|
|
|
143 |
DROP TABLE IF EXISTS gestion_composer_utilisateur_frais_kilometrique;
|
|
|
144 |
|
|
|
145 |
CREATE TABLE gestion_composer_utilisateur_frais_kilometrique (
|
|
|
146 |
gcufk_id_frais_kilometrique INTEGER(10) UNSIGNED NOT NULL,
|
|
|
147 |
gcufk_id_utilisateur INTEGER(10) UNSIGNED NOT NULL,
|
|
|
148 |
gcufk_date_frais DATE NOT NULL,
|
|
|
149 |
gcufk_nb_kilometre FLOAT NULL,
|
|
|
150 |
gcufk_objet VARCHAR(255) NULL,
|
|
|
151 |
gcufk_trajet VARCHAR(255) NULL,
|
|
|
152 |
gcufk_montant_total FLOAT NOT NULL,
|
|
|
153 |
PRIMARY KEY(gcufk_id_frais_kilometrique, gcufk_id_utilisateur, gcufk_date_frais),
|
|
|
154 |
FOREIGN KEY(gcufk_id_frais_kilometrique)
|
|
|
155 |
REFERENCES gestion_frais_kilometrique(gfk_id_frais_kilometrique)
|
|
|
156 |
ON DELETE NO ACTION
|
|
|
157 |
ON UPDATE NO ACTION,
|
|
|
158 |
FOREIGN KEY(gcufk_id_utilisateur)
|
|
|
159 |
REFERENCES gestion_utilisateur(gu_id_utilisateur)
|
|
|
160 |
ON DELETE NO ACTION
|
|
|
161 |
ON UPDATE NO ACTION
|
|
|
162 |
);
|
|
|
163 |
|
|
|
164 |
#stocke la date de depense et le montant de chaque depense
|
|
|
165 |
DROP TABLE IF EXISTS gestion_depense;
|
|
|
166 |
|
|
|
167 |
CREATE TABLE gestion_depense (
|
|
|
168 |
gu_id_utilisateur INTEGER(10) UNSIGNED NOT NULL,
|
|
|
169 |
gnf_id_frais INTEGER(10) UNSIGNED NOT NULL,
|
|
|
170 |
gd_date_depense DATE NOT NULL,
|
|
|
171 |
gd_montant_ht FLOAT NULL,
|
|
|
172 |
gd_montant_ttc FLOAT NOT NULL,
|
|
|
173 |
PRIMARY KEY(gu_id_utilisateur, gnf_id_frais, gd_date_depense),
|
|
|
174 |
FOREIGN KEY(gu_id_utilisateur)
|
|
|
175 |
REFERENCES gestion_utilisateur(gu_id_utilisateur)
|
|
|
176 |
ON DELETE NO ACTION
|
|
|
177 |
ON UPDATE NO ACTION,
|
|
|
178 |
FOREIGN KEY(gnf_id_frais)
|
|
|
179 |
REFERENCES gestion_note_frais(gnf_id_frais)
|
|
|
180 |
ON DELETE NO ACTION
|
|
|
181 |
ON UPDATE NO ACTION
|
|
|
182 |
);
|
|
|
183 |
|
|
|
184 |
# stocke la date de debut et la duree du temps prevu par l'utilisateur sur le projet
|
|
|
185 |
|
|
|
186 |
# DROP TABLE IF EXISTS gestion_prevision_projet;
|
|
|
187 |
|
|
|
188 |
# CREATE TABLE gestion_prevision_projet (
|
|
|
189 |
# gu_id_utilisateur INTEGER(10) UNSIGNED NOT NULL,
|
|
|
190 |
# gp_id_projet INTEGER(10) UNSIGNED NOT NULL,
|
|
|
191 |
# gpp_date_prevision DATE NOT NULL,
|
|
|
192 |
# gpp_duree_prevu FLOAT NOT NULL,
|
|
|
193 |
# PRIMARY KEY(gu_id_utilisateur, gp_id_projet, gpp_date_prevision),
|
|
|
194 |
# FOREIGN KEY(gu_id_utilisateur)
|
|
|
195 |
# REFERENCES gestion_utilisateur(gu_id_utilisateur)
|
|
|
196 |
# ON DELETE NO ACTION
|
|
|
197 |
# ON UPDATE NO ACTION,
|
|
|
198 |
# FOREIGN KEY(gp_id_projet)
|
|
|
199 |
# REFERENCES gestion_projet(gp_id_projet)
|
|
|
200 |
# ON DELETE NO ACTION
|
|
|
201 |
# ON UPDATE NO ACTION
|
|
|
202 |
# );
|
|
|
203 |
|
|
|
204 |
#stocke les preferences d'un utilisateur : utilisee pour l'affichage
|
|
|
205 |
DROP TABLE IF EXISTS gestion_preferences;
|
|
|
206 |
|
|
|
207 |
CREATE TABLE gestion_preferences (
|
|
|
208 |
gu_id_utilisateur INTEGER(10) UNSIGNED NOT NULL,
|
|
|
209 |
gp_id_projet INTEGER(10) UNSIGNED NOT NULL,
|
|
|
210 |
PRIMARY KEY(gu_id_utilisateur, gp_id_projet),
|
|
|
211 |
FOREIGN KEY(gu_id_utilisateur)
|
|
|
212 |
REFERENCES gestion_utilisateur(gu_id_utilisateur)
|
|
|
213 |
ON DELETE NO ACTION
|
|
|
214 |
ON UPDATE NO ACTION,
|
|
|
215 |
FOREIGN KEY(gp_id_projet)
|
|
|
216 |
REFERENCES gestion_projet(gp_id_projet)
|
|
|
217 |
ON DELETE NO ACTION
|
|
|
218 |
ON UPDATE NO ACTION
|
|
|
219 |
);
|
|
|
220 |
|
|
|
221 |
#stocke les differentes taches qui composent un projet
|
|
|
222 |
DROP TABLE IF EXISTS gestion_taches;
|
|
|
223 |
|
|
|
224 |
|
|
|
225 |
CREATE TABLE gestion_taches (
|
|
|
226 |
gt_id_tache INTEGER UNSIGNED NOT NULL,
|
|
|
227 |
gp_id_projet INTEGER(10) UNSIGNED NOT NULL,
|
|
|
228 |
gt_nom_tache VARCHAR(255) NOT NULL,
|
|
|
229 |
gt_description_tache VARCHAR(255) NULL,
|
|
|
230 |
gt_date_debut_tache DATE NULL,
|
|
|
231 |
gt_duree_prevue INTEGER UNSIGNED NULL,
|
|
|
232 |
gt_avancement INT NULL,
|
|
|
233 |
PRIMARY KEY(gt_id_tache),
|
|
|
234 |
FOREIGN KEY(gp_id_projet)
|
|
|
235 |
REFERENCES gestion_projet(gp_id_projet)
|
|
|
236 |
ON DELETE NO ACTION
|
|
|
237 |
ON UPDATE NO ACTION
|
|
|
238 |
);
|
|
|
239 |
|
|
|
240 |
#gestion des predecessers d'une tache afin de faire des diagrammes de gantt
|
|
|
241 |
|
|
|
242 |
Drop TABLE IF EXISTS gestion_predecesseurs;
|
|
|
243 |
|
|
|
244 |
CREATE TABLE gestion_predecesseurs (
|
|
|
245 |
gt_id_tache INTEGER UNSIGNED NOT NULL,
|
|
|
246 |
gpred_id_pred INTEGER UNSIGNED NOT NULL,
|
|
|
247 |
PRIMARY KEY(gt_id_tache, gpred_id_pred),
|
|
|
248 |
FOREIGN KEY(gt_id_tache)
|
|
|
249 |
REFERENCES gestion_taches(gt_id_tache)
|
|
|
250 |
ON DELETE NO ACTION
|
|
|
251 |
ON UPDATE NO ACTION,
|
|
|
252 |
FOREIGN KEY(gpred_id_pred)
|
|
|
253 |
REFERENCES gestion_taches(gt_id_tache)
|
|
|
254 |
ON DELETE NO ACTION
|
|
|
255 |
ON UPDATE NO ACTION
|
|
|
256 |
);
|
|
|
257 |
|
|
|
258 |
#stocke les previsions faites sur une tache
|
|
|
259 |
|
|
|
260 |
DROP TABLE IF EXISTS gestion_prevision_tache;
|
|
|
261 |
|
|
|
262 |
CREATE TABLE gestion_prevision_tache (
|
|
|
263 |
gu_id_utilisateur INTEGER(10) UNSIGNED NOT NULL,
|
|
|
264 |
gt_id_tache INTEGER UNSIGNED NOT NULL,
|
|
|
265 |
gpt_date_prevision DATE NOT NULL,
|
|
|
266 |
gpt_duree_prevision FLOAT NOT NULL,
|
|
|
267 |
PRIMARY KEY(gu_id_utilisateur, gt_id_tache, gpt_date_prevision),
|
|
|
268 |
FOREIGN KEY(gu_id_utilisateur)
|
|
|
269 |
REFERENCES gestion_utilisateur(gu_id_utilisateur)
|
|
|
270 |
ON DELETE NO ACTION
|
|
|
271 |
ON UPDATE NO ACTION,
|
|
|
272 |
FOREIGN KEY(gt_id_tache)
|
|
|
273 |
REFERENCES gestion_taches(gt_id_tache)
|
|
|
274 |
ON DELETE NO ACTION
|
|
|
275 |
ON UPDATE NO ACTION
|
|
|
276 |
);
|
|
|
277 |
|
|
|
278 |
|
|
|
279 |
|