· 4 years ago · Feb 09, 2021, 02:46 PM
1-- LA BASE TELLE QUE DONNEE DANS LE SUJET
2
3-- Création de la structure de la base de données
4CREATE TABLE IF NOT EXISTS fraisforfait (
5 id char(3) NOT NULL,
6 libelle char(20) DEFAULT NULL,
7 montant decimal(5,2) DEFAULT NULL,
8 PRIMARY KEY (id)
9) ENGINE=InnoDB;
10
11CREATE TABLE IF NOT EXISTS etat (
12 id char(2) NOT NULL,
13 libelle varchar(30) DEFAULT NULL,
14 PRIMARY KEY (id)
15) ENGINE=InnoDB;
16
17CREATE TABLE IF NOT EXISTS visiteur (
18 id char(4) NOT NULL,
19 nom char(30) DEFAULT NULL,
20 prenom char(30) DEFAULT NULL,
21 login char(20) DEFAULT NULL,
22 mdp char(20) DEFAULT NULL,
23 adresse char(30) DEFAULT NULL,
24 cp char(5) DEFAULT NULL,
25 ville char(30) DEFAULT NULL,
26 dateembauche date DEFAULT NULL,
27 PRIMARY KEY (id)
28) ENGINE=InnoDB;
29
30CREATE TABLE IF NOT EXISTS fichefrais (
31 idvisiteur char(4) NOT NULL,
32 mois char(6) NOT NULL,
33 nbjustificatifs int(11) DEFAULT NULL,
34 montantvalide decimal(10,2) DEFAULT NULL,
35 datemodif date DEFAULT NULL,
36 idetat char(2) DEFAULT 'CR',
37 PRIMARY KEY (idvisiteur,mois),
38 FOREIGN KEY (idetat) REFERENCES etat(id),
39 FOREIGN KEY (idvisiteur) REFERENCES visiteur(id)
40) ENGINE=InnoDB;
41
42CREATE TABLE IF NOT EXISTS lignefraisforfait (
43 idvisiteur char(4) NOT NULL,
44 mois char(6) NOT NULL,
45 idfraisforfait char(3) NOT NULL,
46 quantite int(11) DEFAULT NULL,
47 PRIMARY KEY (idvisiteur,mois,idfraisforfait),
48 FOREIGN KEY (idvisiteur, mois) REFERENCES fichefrais(idvisiteur, mois),
49 FOREIGN KEY (idfraisforfait) REFERENCES fraisforfait(id)
50) ENGINE=InnoDB;
51
52CREATE TABLE IF NOT EXISTS lignefraishorsforfait (
53 id int(11) NOT NULL auto_increment,
54 idvisiteur char(4) NOT NULL,
55 mois char(6) NOT NULL,
56 libelle varchar(100) DEFAULT NULL,
57 date date DEFAULT NULL,
58 montant decimal(10,2) DEFAULT NULL,
59 PRIMARY KEY (id),
60 FOREIGN KEY (idvisiteur, mois) REFERENCES fichefrais(idvisiteur, mois)
61) ENGINE=InnoDB;
62
63-- Alimentation des données paramètres
64INSERT INTO fraisforfait (id, libelle, montant) VALUES
65('ETP', 'Forfait Etape', 110.00),
66('KM', 'Frais Kilométrique', 0.62),
67('NUI', 'Nuitée Hôtel', 80.00),
68('REP', 'Repas Restaurant', 25.00);
69
70INSERT INTO etat (id, libelle) VALUES
71('RB', 'Remboursée'),
72('CL', 'Saisie clôturée'),
73('CR', 'Fiche créée, saisie en cours'),
74('VA', 'Validée et mise en paiement');
75
76-- Récupération des utilisateurs
77INSERT INTO visiteur (id, nom, prenom, login, mdp, adresse, cp, ville, dateembauche) VALUES
78('a131', 'Villechalane', 'Louis', 'lvillachane', 'jux7g', '8 rue des Charmes', '46000', 'Cahors', '2005-12-21'),
79('a17', 'Andre', 'David', 'dandre', 'oppg5', '1 rue Petit', '46200', 'Lalbenque', '1998-11-23'),
80('a55', 'Bedos', 'Christian', 'cbedos', 'gmhxd', '1 rue Peranud', '46250', 'Montcuq', '1995-01-12'),
81('a93', 'Tusseau', 'Louis', 'ltusseau', 'ktp3s', '22 rue des Ternes', '46123', 'Gramat', '2000-05-01'),
82('b13', 'Bentot', 'Pascal', 'pbentot', 'doyw1', '11 allée des Cerises', '46512', 'Bessines', '1992-07-09'),
83('b16', 'Bioret', 'Luc', 'lbioret', 'hrjfs', '1 Avenue gambetta', '46000', 'Cahors', '1998-05-11'),
84('b19', 'Bunisset', 'Francis', 'fbunisset', '4vbnd', '10 rue des Perles', '93100', 'Montreuil', '1987-10-21'),
85('b25', 'Bunisset', 'Denise', 'dbunisset', 's1y1r', '23 rue Manin', '75019', 'paris', '2010-12-05'),
86('b28', 'Cacheux', 'Bernard', 'bcacheux', 'uf7r3', '114 rue Blanche', '75017', 'Paris', '2009-11-12'),
87('b34', 'Cadic', 'Eric', 'ecadic', '6u8dc', '123 avenue de la République', '75011', 'Paris', '2008-09-23'),
88('b4', 'Charoze', 'Catherine', 'ccharoze', 'u817o', '100 rue Petit', '75019', 'Paris', '2005-11-12'),
89('b50', 'Clepkens', 'Christophe', 'cclepkens', 'bw1us', '12 allée des Anges', '93230', 'Romainville', '2003-08-11'),
90('b59', 'Cottin', 'Vincenne', 'vcottin', '2hoh9', '36 rue Des Roches', '93100', 'Monteuil', '2001-11-18'),
91('c14', 'Daburon', 'François', 'fdaburon', '7oqpv', '13 rue de Chanzy', '94000', 'Créteil', '2002-02-11'),
92('c3', 'De', 'Philippe', 'pde', 'gk9kx', '13 rue Barthes', '94000', 'Créteil', '2010-12-14'),
93('c54', 'Debelle', 'Michel', 'mdebelle', 'od5rt', '181 avenue Barbusse', '93210', 'Rosny', '2006-11-23'),
94('d13', 'Debelle', 'Jeanne', 'jdebelle', 'nvwqq', '134 allée des Joncs', '44000', 'Nantes', '2000-05-11'),
95('d51', 'Debroise', 'Michel', 'mdebroise', 'sghkb', '2 Bld Jourdain', '44000', 'Nantes', '2001-04-17'),
96('e22', 'Desmarquest', 'Nathalie', 'ndesmarquest', 'f1fob', '14 Place d Arc', '45000', 'Orléans', '2005-11-12'),
97('e24', 'Desnost', 'Pierre', 'pdesnost', '4k2o5', '16 avenue des Cèdres', '23200', 'Guéret', '2001-02-05'),
98('e39', 'Dudouit', 'Frédéric', 'fdudouit', '44im8', '18 rue de l église', '23120', 'GrandBourg', '2000-08-01'),
99('e49', 'Duncombe', 'Claude', 'cduncombe', 'qf77j', '19 rue de la tour', '23100', 'La souteraine', '1987-10-10'),
100('e5', 'Enault-Pascreau', 'Céline', 'cenault', 'y2qdu', '25 place de la gare', '23200', 'Gueret', '1995-09-01'),
101('e52', 'Eynde', 'Valérie', 'veynde', 'i7sn3', '3 Grand Place', '13015', 'Marseille', '1999-11-01'),
102('f21', 'Finck', 'Jacques', 'jfinck', 'mpb3t', '10 avenue du Prado', '13002', 'Marseille', '2001-11-10'),
103('f39', 'Frémont', 'Fernande', 'ffremont', 'xs5tq', '4 route de la mer', '13012', 'Allauh', '1998-10-01'),
104('f4', 'Gest', 'Alain', 'agest', 'dywvt', '30 avenue de la mer', '13025', 'Berre', '1985-11-01');
105
106
107-- LA BASE APRES MODIFICATION
108
109-- Copie de l'entièreté de la base de donnée vers des tables temporaires à des fins de sauvegarde
110CREATE TABLE IF NOT EXISTS fraisforfaitTemp LIKE fraisforfait;
111INSERT INTO fraisforfaitTemp SELECT * FROM fraisforfait;
112
113CREATE TABLE IF NOT EXISTS etatTemp LIKE etat;
114INSERT INTO etatTemp SELECT * FROM etat;
115
116CREATE TABLE IF NOT EXISTS visiteurTemp LIKE visiteur;
117INSERT INTO visiteurTemp SELECT * FROM visiteur;
118
119CREATE TABLE IF NOT EXISTS fichefraisTemp LIKE fichefrais;
120INSERT INTO fichefraisTemp SELECT * FROM fichefrais;
121
122CREATE TABLE IF NOT EXISTS lignefraisforfaitTemp LIKE lignefraisforfait;
123INSERT INTO lignefraisforfaitTemp SELECT * FROM lignefraisforfait;
124
125CREATE TABLE IF NOT EXISTS lignefraishorsforfaitTemp LIKE lignefraishorsforfait;
126INSERT INTO lignefraishorsforfaitTemp SELECT * FROM lignefraishorsforfait;
127
128-- Construction de la nouvelle base de donnée
129CREATE TABLE IF NOT EXISTS fraisForfait(
130 id char(3) NOT NULL,
131 libelle char(20)DEFAULT NULL,
132 montant decimal(5,2) DEFAULT NULL,
133 primary key (id)
134) ENGINE =InnoDB;
135
136CREATE TABLE IF NOT EXISTS etatNew (
137 id char(2) NOT NULL,
138 libelle varchar(30) DEFAULT NULL,
139 PRIMARY KEY (id)
140) ENGINE=InnoDB;
141
142CREATE TABLE IF NOT EXISTS user(
143 id int NOT NULL auto_increment,
144 nom char(30) DEFAULT NULL,
145 prenom char(30) DEFAULT NULL,
146 login char(20) DEFAULT NULL,
147 mdp char(20) DEFAULT NULL,
148 adresse char(30) DEFAULT NULL,
149 cp int(5) DEFAULT NULL,
150 ville char(30) DEFAULT NULL,
151 dateEmbauche date DEFAULT NULL,
152 idRole int NOT NULL,
153 PRIMARY KEY (id),
154 FOREIGN KEY (idRole) REFERENCES role(idRole)
155) ENGINE=InnoDB;
156
157CREATE TABLE IF NOT EXISTS role(
158 idRole int NOT NULL,
159 libelle char(20),
160 PRIMARY KEY (idRole)
161) ENGINE=InnoDB;
162CREATE TABLE IF NOT EXISTS ficheFrais (
163 idUser int NOT NULL,
164 mois char(6) NOT NULL,
165 nbJustificatifs int(11) DEFAULT NULL,
166 montantValide decimal(10,2) DEFAULT NULL,
167 dateModif date DEFAULT NULL,
168 idEtat char(2) DEFAULT 'CR',
169 PRIMARY KEY (idUser, mois),
170 FOREIGN KEY (idEtat) REFERENCES etat(id),
171 FOREIGN KEY (idUser) REFERENCES user(id)
172) ENGINE= InnoDB;
173
174CREATE TABLE IF NOT EXISTS ligneFraisForfait (
175 idUser int NOT NULL,
176 mois char(6) NOT NULL,
177 idFraisForfait int NOT NULL,
178 quantite int(11) DEFAULT NULL,
179 PRIMARY KEY (idUser, mois, idFraisForfait),
180 FOREIGN KEY (idUser, mois) REFERENCES ficheFrais(idUser, mois),
181 FOREIGN KEY (idFraisForfait) REFERENCES fraisForfait(id)
182) ENGINE=InnoDB;
183
184CREATE TABLE IF NOT EXISTS ligneFraisHorsForfait (
185 id int(11) NOT NULL auto_increment,
186 idUser int NOT NULL,
187 mois char(6) NOT NULL,
188 libelle varchar(100) DEFAULT NULL,
189 date date DEFAULT NULL,
190 montant decimal(10,2) DEFAULT NULL,
191 PRIMARY KEY (id),
192 FOREIGN KEY (idUser,mois) REFERENCES ficheFrais(idUser,mois)
193) ENGINE =InnoDB;
194
195
196-- Copie des données depuis les tables temporaires vers les nouvelles tables
197INSERT INTO fraisForfait SELECT * FROM fraisforfaitTemp;
198INSERT INTO etatNew SELECT * FROM etatTemp;
199INSERT INTO user SELECT nom, prenom, login, mdp, adresse, cp, ville, dateEmbauche FROM visiteurTemp;
200INSERT INTO role (idRole, libelle) VALUES ('1', 'visiteur'),('2','Comptable');
201INSERT INTO user (idRole) VALUES (1);
202
203-- LA PARTIE QUI ME POSE PROBLEME
204INSERT INTO ficheFrais SELECT mois, nbJustificatifs, montantvalide, datemodif, idetat FROM fichefraisTemp WHERE idvisiteur= (SELECT id FROM user WHERE nom, prenom, login, mdp, dateembauche FROM visiteurTemp = nom, prenom, login, mdp, dateEmbauche FROM user);
205INSERT INTO ligneFraisForfait SELECT mois, quantite FROM lignefraisforfaitTemp WHERE idvisiteur= (SELECT id FROM user where nom, prenom, login, mdp, dateEmbauche = nom,prenom,login,mdp,dateembauche FROM visiteurTemp);
206INSERT INTO ligneFraisHorsForfait SELECT * FROM lignefraishorsforfaitTemp WHERE idvisiteur = (SELECT idvisiteur FROM visiteurTemp WHERE nom, prenom, login, mdp, dateembauche = nom, prenom, login, mdp, dateembauche FROM user);
207
208
209-- TODO Must Drop all originals tables
210-- TODO Copy data into rebuildTables
211-- TODO Drop Temps