| New file |
|---|
| 0,0 → 1,279 |
|---|
| #tables de la base de donnees |
| |
| |
| |
| |
| #table contenant les motifs d'absence |
| |
| DROP TABLE IF EXISTS gestion_motif_absence; |
| |
| CREATE TABLE gestion_motif_absence ( |
| gma_id_motif TINYINT(3) UNSIGNED NOT NULL, |
| gma_libelle_motif VARCHAR(255) NOT NULL, |
| gma_type_rtt CHAR(1) NOT NULL, |
| PRIMARY KEY(gma_id_motif) |
| ); |
| |
| # table contenant le montant des deplacements, date du deplacement, les objets du trajet et la description du trajet |
| DROP TABLE IF EXISTS gestion_frais_kilometrique; |
| |
| |
| CREATE TABLE gestion_frais_kilometrique ( |
| gfk_id_frais_kilometrique INTEGER(10) UNSIGNED NOT NULL, |
| gfk_taux_kilometre FLOAT NOT NULL, |
| PRIMARY KEY(gfk_id_frais_kilometrique) |
| ); |
| |
| #contient le statut des utilisateurs |
| |
| DROP TABLE IF EXISTS gestion_statut; |
| |
| CREATE TABLE gestion_statut ( |
| gs_id_statut TINYINT(3) UNSIGNED NOT NULL, |
| gs_libelle_statut VARCHAR(255) NOT NULL, |
| PRIMARY KEY(gs_id_statut) |
| ); |
| |
| # contient le sidentifiant sdes notes de frais qui correspondent au numero du plan comptable, ainsi que le libelle du plan comptable |
| |
| DROP TABLE IF EXISTS gestion_note_frais; |
| |
| CREATE TABLE gestion_note_frais ( |
| gnf_id_frais INTEGER(10) UNSIGNED NOT NULL, |
| gnf_libelle_frais VARCHAR(255) NOT NULL, |
| PRIMARY KEY(gnf_id_frais) |
| ); |
| |
| #stocke les differentes categorie sde projet existantes |
| DROP TABLE IF EXISTS gestion_categorie; |
| |
| CREATE TABLE gestion_categorie ( |
| gc_id_categorie INTEGER(10) UNSIGNED NOT NULL, |
| gc_libelle_categorie VARCHAR(255) NULL, |
| PRIMARY KEY(gc_id_categorie) |
| ); |
| |
| #contient les informations relatives a un projet |
| DROP TABLE IF EXISTS gestion_projet; |
| |
| CREATE TABLE gestion_projet ( |
| gp_id_projet INTEGER(10) UNSIGNED NOT NULL, |
| gc_id_categorie INTEGER(10) UNSIGNED NOT NULL, |
| gp_nom_projet VARCHAR(255) NOT NULL, |
| gp_description VARCHAR(255) NULL, |
| gp_date_debut DATE NULL, |
| gp_duree_prevue FLOAT NULL, |
| gp_avancement INTEGER(11) NULL, |
| PRIMARY KEY(gp_id_projet), |
| FOREIGN KEY(gc_id_categorie) |
| REFERENCES gestion_categorie(gc_id_categorie) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION |
| ); |
| |
| #contient les informations relatives aux utilisateurs |
| |
| DROP TABLE IF EXISTS gestion_utilisateur; |
| |
| CREATE TABLE gestion_utilisateur ( |
| gu_id_utilisateur INTEGER(10) UNSIGNED NOT NULL, |
| gs_id_statut TINYINT(3) UNSIGNED NOT NULL, |
| gu_nom VARCHAR(255) NOT NULL, |
| gu_prenom VARCHAR(255) NOT NULL, |
| gu_password VARCHAR(255) NOT NULL, |
| gu_email VARCHAR(255) NOT NULL, |
| gu_telephone INTEGER(10) UNSIGNED NULL, |
| gu_adresse VARCHAR(255) NULL, |
| gu_code_postal INTEGER(10) UNSIGNED NULL, |
| gu_ville VARCHAR(255) NULL, |
| gu_quota_heures_supp FLOAT NULL, |
| gu_conges_payes FLOAT NULL, |
| gu_temps_de_travail FLOAT NULL, |
| gu_admin TINYINT(3) UNSIGNED NULL, |
| gu_admin2 TINYINT(3) UNSIGNED NULL,/*indique si l'utilisateur doit figurer dans l'affichage des recapitulatifs*/ |
| gu_notes VARCHAR(255) NULL, |
| PRIMARY KEY(gu_id_utilisateur), |
| FOREIGN KEY(gs_id_statut) |
| REFERENCES gestion_statut(gs_id_statut) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION |
| ); |
| |
| #stocke les dates de trvail et leurs durees, ainsi que l'utilisateur et le projet concernes |
| |
| DROP TABLE IF EXISTS gestion_travail; |
| CREATE TABLE gestion_travail ( |
| gp_id_projet INTEGER(10) UNSIGNED NOT NULL, |
| gu_id_utilisateur INTEGER(10) UNSIGNED NOT NULL, |
| gt_date_travail DATE NOT NULL, |
| gt_duree_travail FLOAT NOT NULL, |
| PRIMARY KEY(gp_id_projet, gu_id_utilisateur,gt_date_travail), |
| FOREIGN KEY(gp_id_projet) |
| REFERENCES gestion_projet(gp_id_projet) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION, |
| FOREIGN KEY(gu_id_utilisateur) |
| REFERENCES gestion_utilisateur(gu_id_utilisateur) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION |
| ); |
| |
| #contient les dates de debut et de fin d'absence, ainsi que la date d'envoi de la lettre d'absence |
| DROP TABLE IF EXISTS gestion_absence; |
| |
| |
| CREATE TABLE gestion_absence ( |
| gu_id_utilisateur INTEGER(10) UNSIGNED NOT NULL, |
| gma_id_motif TINYINT(3) UNSIGNED NOT NULL, |
| ga_date_debut DATE NOT NULL, |
| ga_date_fin DATE NOT NULL, |
| ga_date_envoi_lettre DATE NOT NULL, |
| PRIMARY KEY(gu_id_utilisateur, gma_id_motif, ga_date_debut), |
| FOREIGN KEY(gu_id_utilisateur) |
| REFERENCES gestion_utilisateur(gu_id_utilisateur) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION, |
| FOREIGN KEY(gma_id_condition) |
| REFERENCES gestion_motif_absence(gma_id_motif) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION |
| ); |
| |
| #fait le lien entre l'utilisateur et les frais kilometriques qu'il occasionne |
| DROP TABLE IF EXISTS gestion_composer_utilisateur_frais_kilometrique; |
| |
| CREATE TABLE gestion_composer_utilisateur_frais_kilometrique ( |
| gcufk_id_frais_kilometrique INTEGER(10) UNSIGNED NOT NULL, |
| gcufk_id_utilisateur INTEGER(10) UNSIGNED NOT NULL, |
| gcufk_date_frais DATE NOT NULL, |
| gcufk_nb_kilometre FLOAT NULL, |
| gcufk_objet VARCHAR(255) NULL, |
| gcufk_trajet VARCHAR(255) NULL, |
| gcufk_montant_total FLOAT NOT NULL, |
| PRIMARY KEY(gcufk_id_frais_kilometrique, gcufk_id_utilisateur, gcufk_date_frais), |
| FOREIGN KEY(gcufk_id_frais_kilometrique) |
| REFERENCES gestion_frais_kilometrique(gfk_id_frais_kilometrique) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION, |
| FOREIGN KEY(gcufk_id_utilisateur) |
| REFERENCES gestion_utilisateur(gu_id_utilisateur) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION |
| ); |
| |
| #stocke la date de depense et le montant de chaque depense |
| DROP TABLE IF EXISTS gestion_depense; |
| |
| CREATE TABLE gestion_depense ( |
| gu_id_utilisateur INTEGER(10) UNSIGNED NOT NULL, |
| gnf_id_frais INTEGER(10) UNSIGNED NOT NULL, |
| gd_date_depense DATE NOT NULL, |
| gd_montant_ht FLOAT NULL, |
| gd_montant_ttc FLOAT NOT NULL, |
| PRIMARY KEY(gu_id_utilisateur, gnf_id_frais, gd_date_depense), |
| FOREIGN KEY(gu_id_utilisateur) |
| REFERENCES gestion_utilisateur(gu_id_utilisateur) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION, |
| FOREIGN KEY(gnf_id_frais) |
| REFERENCES gestion_note_frais(gnf_id_frais) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION |
| ); |
| |
| # stocke la date de debut et la duree du temps prevu par l'utilisateur sur le projet |
| |
| # DROP TABLE IF EXISTS gestion_prevision_projet; |
| |
| # CREATE TABLE gestion_prevision_projet ( |
| # gu_id_utilisateur INTEGER(10) UNSIGNED NOT NULL, |
| # gp_id_projet INTEGER(10) UNSIGNED NOT NULL, |
| # gpp_date_prevision DATE NOT NULL, |
| # gpp_duree_prevu FLOAT NOT NULL, |
| # PRIMARY KEY(gu_id_utilisateur, gp_id_projet, gpp_date_prevision), |
| # FOREIGN KEY(gu_id_utilisateur) |
| # REFERENCES gestion_utilisateur(gu_id_utilisateur) |
| # ON DELETE NO ACTION |
| # ON UPDATE NO ACTION, |
| # FOREIGN KEY(gp_id_projet) |
| # REFERENCES gestion_projet(gp_id_projet) |
| # ON DELETE NO ACTION |
| # ON UPDATE NO ACTION |
| # ); |
| |
| #stocke les preferences d'un utilisateur : utilisee pour l'affichage |
| DROP TABLE IF EXISTS gestion_preferences; |
| |
| CREATE TABLE gestion_preferences ( |
| gu_id_utilisateur INTEGER(10) UNSIGNED NOT NULL, |
| gp_id_projet INTEGER(10) UNSIGNED NOT NULL, |
| PRIMARY KEY(gu_id_utilisateur, gp_id_projet), |
| FOREIGN KEY(gu_id_utilisateur) |
| REFERENCES gestion_utilisateur(gu_id_utilisateur) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION, |
| FOREIGN KEY(gp_id_projet) |
| REFERENCES gestion_projet(gp_id_projet) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION |
| ); |
| |
| #stocke les differentes taches qui composent un projet |
| DROP TABLE IF EXISTS gestion_taches; |
| |
| |
| CREATE TABLE gestion_taches ( |
| gt_id_tache INTEGER UNSIGNED NOT NULL, |
| gp_id_projet INTEGER(10) UNSIGNED NOT NULL, |
| gt_nom_tache VARCHAR(255) NOT NULL, |
| gt_description_tache VARCHAR(255) NULL, |
| gt_date_debut_tache DATE NULL, |
| gt_duree_prevue INTEGER UNSIGNED NULL, |
| gt_avancement INT NULL, |
| PRIMARY KEY(gt_id_tache), |
| FOREIGN KEY(gp_id_projet) |
| REFERENCES gestion_projet(gp_id_projet) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION |
| ); |
| |
| #gestion des predecessers d'une tache afin de faire des diagrammes de gantt |
| |
| Drop TABLE IF EXISTS gestion_predecesseurs; |
| |
| CREATE TABLE gestion_predecesseurs ( |
| gt_id_tache INTEGER UNSIGNED NOT NULL, |
| gpred_id_pred INTEGER UNSIGNED NOT NULL, |
| PRIMARY KEY(gt_id_tache, gpred_id_pred), |
| FOREIGN KEY(gt_id_tache) |
| REFERENCES gestion_taches(gt_id_tache) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION, |
| FOREIGN KEY(gpred_id_pred) |
| REFERENCES gestion_taches(gt_id_tache) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION |
| ); |
| |
| #stocke les previsions faites sur une tache |
| |
| DROP TABLE IF EXISTS gestion_prevision_tache; |
| |
| CREATE TABLE gestion_prevision_tache ( |
| gu_id_utilisateur INTEGER(10) UNSIGNED NOT NULL, |
| gt_id_tache INTEGER UNSIGNED NOT NULL, |
| gpt_date_prevision DATE NOT NULL, |
| gpt_duree_prevision FLOAT NOT NULL, |
| PRIMARY KEY(gu_id_utilisateur, gt_id_tache, gpt_date_prevision), |
| FOREIGN KEY(gu_id_utilisateur) |
| REFERENCES gestion_utilisateur(gu_id_utilisateur) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION, |
| FOREIGN KEY(gt_id_tache) |
| REFERENCES gestion_taches(gt_id_tache) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION |
| ); |
| |
| |
| |