· 8 years ago · Dec 08, 2017, 12:24 PM
1DROP SCHEMA IF EXISTS projet CASCADE;
2CREATE SCHEMA projet;
3
4CREATE TABLE projet.utilisateurs(
5 id_utilisateur SERIAL PRIMARY KEY,
6 nom CHARACTER VARYING(50) NOT NULL CHECK (nom <>''),
7 prenom CHARACTER VARYING(50) NOT NULL CHECK (prenom<>''),
8 email CHARACTER VARYING(128) NOT NULL CHECK (email<>''),
9 nom_utilisateur CHARACTER VARYING(30) NOT NULL UNIQUE CHECK (nom_utilisateur<>''),
10 mdp CHARACTER VARYING(256) NOT NULL CHECK (mdp<>''),
11 sel VARCHAR(256) NOT NULL CHECK (sel <>'') DEFAULT 'SALT',
12 nombre_avis INTEGER NOT NULL CHECK (nombre_avis>=0) DEFAULT 0,
13 sum_avis INTEGER NOT NULL CHECK (sum_avis>=0) DEFAULT 0,
14 etat VARCHAR(20) NOT NULL CHECK (etat IN('Actif','Suspendu','Supprimé')) DEFAULT 'Actif',
15 derniere_evaluation INTEGER NULL CHECK (derniere_evaluation>=1 AND derniere_evaluation<=5),
16 nombre_objets_achete INTEGER NOT NULL DEFAULT 0,
17 nombre_objets_vendu INTEGER NOT NULL DEFAULT 0
18);
19
20CREATE TABLE projet.objets(
21 id_objet SERIAL PRIMARY KEY,
22 description CHARACTER VARYING(128) NOT NULL CHECK (description<>''),
23 prix_depart DOUBLE PRECISION NOT NULL CHECK (prix_depart >0),
24 date_depart TIMESTAMP NOT NULL DEFAULT now(),
25 date_expiration TIMESTAMP NOT NULL CHECK (date_expiration>now()) DEFAULT now() + INTERVAL '8' minute,
26 proprietaire INTEGER NOT NULL REFERENCES projet.utilisateurs (id_utilisateur),
27 nombre_encheres INTEGER NULL CHECK (nombre_encheres>=0) DEFAULT 0,
28 etat VARCHAR(20) NOT NULL CHECK (etat IN('En vente','Vendu','Annulé','Périmé')) DEFAULT 'En vente'
29);
30
31CREATE TABLE projet.encheres(
32 id_enchere SERIAL PRIMARY KEY,
33 encherisseur INTEGER NOT NULL REFERENCES projet.utilisateurs (id_utilisateur),
34 objet INTEGER NOT NULL REFERENCES projet.objets (id_objet),
35 montant DOUBLE PRECISION NOT NULL CHECK (montant>0),
36 statut VARCHAR(20) NOT NULL CHECK (statut IN('Enchère remportée','Enchère perdue','Enchère annulée','Meilleur enchère','Enchère perdante')) DEFAULT 'Meilleur enchère',
37 date_enchere TIMESTAMP NOT NULL DEFAULT now()
38);
39
40CREATE TABLE projet.transactions(
41 id_transaction SERIAL PRIMARY KEY,
42 enchere INTEGER NOT NULL REFERENCES projet.encheres (id_enchere),
43 date_transaction TIMESTAMP NOT NULL DEFAULT now()
44);
45
46CREATE TABLE projet.evaluations(
47 id_evaluation SERIAL PRIMARY KEY,
48 u_evaluateur INTEGER NOT NULL REFERENCES projet.utilisateurs (id_utilisateur),
49 u_evalue INTEGER NOT NULL REFERENCES projet.utilisateurs (id_utilisateur),
50 id_transaction INTEGER NOT NULL REFERENCES projet.transactions (id_transaction),
51 type_evaluation VARCHAR(10) NOT NULL CHECK (type_evaluation IN ('Acheteur','Vendeur')) DEFAULT 'Acheteur',
52 evaluation INTEGER NOT NULL CHECK (evaluation>=0 AND evaluation<=5),
53 commentaire VARCHAR(125) NULL,
54 date_evaluation TIMESTAMP NOT NULL DEFAULT now()
55);
56
57-- Functions()
58-- AJOUTER UN utilisateur
59
60CREATE OR REPLACE FUNCTION projet.ajouter_utilisateur(CHARACTER VARYING(50),CHARACTER VARYING(50),CHARACTER VARYING(128),CHARACTER VARYING(30),CHARACTER VARYING(256),CHARACTER VARYING(256)) RETURNS INTEGER AS $$
61DECLARE
62nom_s ALIAS FOR $1;
63prenom_s ALIAS FOR $2;
64email_s ALIAS FOR $3;
65nom_utilisateur_s ALIAS FOR $4;
66mdp_s ALIAS FOR $5;
67sel_s ALIAS FOR $6;
68id INTEGER:=0;
69BEGIN
70 IF EXISTS(SELECT * FROM projet.utilisateurs u WHERE u.nom_utilisateur=nom_utilisateur_s) THEN
71 RAISE 'Nom utilisateur est déjà utilisé !';
72 END IF;
73 IF EXISTS(SELECT * FROM projet.utilisateurs u WHERE u.email=email_s) THEN
74 RAISE 'Email déjà utilisé !';
75 END IF;
76 INSERT INTO projet.utilisateurs VALUES (DEFAULT,nom_s,prenom_s,email_s,nom_utilisateur_s,mdp_s,sel_s,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT)
77 RETURNING id_utilisateur INTO id;
78 RETURN id;
79END;
80$$ LANGUAGE plpgsql;
81
82
83-- AJOUTER UN OBJET
84CREATE OR REPLACE FUNCTION projet.ajouter_objet(CHARACTER VARYING(128),DOUBLE PRECISION,TIMESTAMP,INTEGER) RETURNS INTEGER AS $$
85DECLARE
86description_s ALIAS FOR $1;
87prix_depart_s ALIAS FOR $2;
88date_expiration_s ALIAS FOR $3;
89proprietaire_s ALIAS FOR $4;
90--date_s TIMESTAMP;
91id INTEGER:=0;
92BEGIN
93 IF ((SELECT u.etat FROM projet.utilisateurs u WHERE u.id_utilisateur=proprietaire_s)!='Actif') THEN
94 RAISE 'Le compte doit etre Actif !';
95 END IF;
96 IF date_expiration_s IS NULL THEN
97 INSERT INTO projet.objets VALUES (DEFAULT,description_s,prix_depart_s,DEFAULT,DEFAULT,proprietaire_s,DEFAULT,DEFAULT)
98 RETURNING id_objet INTO id;
99 ELSE
100 INSERT INTO projet.objets VALUES (DEFAULT,description_s,prix_depart_s,DEFAULT,date_expiration_s,proprietaire_s,DEFAULT,DEFAULT)
101 RETURNING id_objet INTO id;
102 END IF;
103
104 RETURN id;
105END;
106$$ LANGUAGE plpgsql;
107
108-- AJOUTER UNE enchere
109CREATE OR REPLACE FUNCTION projet.ajouter_enchere(INTEGER,INTEGER,DOUBLE PRECISION) RETURNS INTEGER AS $$
110DECLARE
111encherisseur_s ALIAS FOR $1;
112objet_s ALIAS FOR $2;
113montant_s ALIAS FOR $3;
114id INTEGER:=0;
115BEGIN
116
117 UPDATE projet.encheres
118 SET statut='Enchère perdante'
119 WHERE objet=objet_s AND statut='Meilleur enchère';
120
121 UPDATE projet.objets
122 SET nombre_encheres=nombre_encheres+1
123 WHERE id_objet=objet_s;
124
125 INSERT INTO projet.encheres VALUES (DEFAULT,encherisseur_s,objet_s,montant_s,DEFAULT,DEFAULT)
126 RETURNING id_enchere INTO id;
127 RETURN id;
128END;
129$$LANGUAGE plpgsql;
130
131CREATE OR REPLACE FUNCTION projet.trigger1() RETURNS TRIGGER AS $$
132BEGIN
133 IF ((SELECT u.etat FROM projet.utilisateurs u, projet.objets o WHERE u.id_utilisateur=o.proprietaire AND o.id_objet=NEW.objet)='Suspendu')
134 THEN RAISE 'Le proprietaire doit etre Actif pour pouvoir faire une enchère !';
135 END IF;
136
137 IF ((SELECT o.proprietaire FROM projet.objets o WHERE o.id_objet=NEW.objet)=NEW.encherisseur)
138 THEN RAISE 'Le proprietaire ne peut pas faire une enchere sur son propre objet !';
139 END IF;
140
141 IF ((SELECT o.etat FROM projet.objets o WHERE o.id_objet=NEW.objet)!='En vente')
142 THEN RAISE 'Objet vendu ou annulé !';
143 END IF;
144
145 IF NOW() > (SELECT o.date_expiration FROM projet.objets o WHERE o.id_objet = NEW.objet)
146 THEN RAISE 'Objet périmé !';
147 END IF;
148
149 IF ((SELECT o.prix_depart FROM projet.objets o WHERE o.id_objet=NEW.objet)>NEW.montant)
150 THEN RAISE 'Le montant de lenchere doit etre strictement supérieur du prix de depart !';
151 END IF;
152
153 IF ((SELECT max(e.montant) FROM projet.encheres e WHERE e.statut='Enchère perdante' AND e.objet=NEW.objet)>=NEW.montant)
154 THEN RAISE 'Le montant de lenchere doit etre strictement supérieur à la meilleur enchere !';
155 END IF;
156
157 RETURN NEW ;
158END;
159$$LANGUAGE plpgsql;
160
161CREATE TRIGGER trigger_enchere AFTER INSERT ON projet.encheres
162FOR EACH ROW EXECUTE PROCEDURE projet.trigger1();
163
164CREATE OR REPLACE FUNCTION projet.ajouter_transaction(INTEGER) RETURNS INTEGER AS $$
165DECLARE
166 enchere_s ALIAS FOR $1;
167 id INTEGER:=0;
168BEGIN
169
170 IF ((SELECT e.statut FROM projet.encheres e WHERE e.id_enchere=enchere_s)!='Meilleur enchère')
171 THEN RAISE 'Seule la meilleur enchère peut etre accepté !';
172 END IF;
173
174 IF ((SELECT o.etat FROM projet.objets o WHERE o.id_objet=(SELECT e.objet FROM projet.encheres e WHERE e.id_enchere=enchere_s))!='En vente')
175 THEN RAISE 'Objet doit etre en vente pour effectuer une transaction !';
176 END IF;
177
178 IF ((SELECT o.date_expiration FROM projet.objets o WHERE o.id_objet=(SELECT e.objet FROM projet.encheres e WHERE e.id_enchere=enchere_s))<now())
179 THEN RAISE 'l Objet est périmé !';
180 END IF;
181
182 IF ((SELECT u.etat FROM projet.utilisateurs u WHERE u.id_utilisateur=
183 (SELECT o.proprietaire FROM projet.objets o WHERE o.id_objet=
184 (SELECT e.objet FROM projet.encheres e WHERE e.id_enchere=enchere_s)))!='Actif')
185 THEN RAISE 'Le proprietaire doit avoir le compte actif pour beneficier de la transaction';
186 END IF;
187
188 IF ((SELECT u.etat FROM projet.utilisateurs u WHERE u.id_utilisateur=(SELECT e.encherisseur FROM projet.encheres e WHERE e.id_enchere=enchere_s))!='Actif')
189 THEN RAISE 'L Acheteur doit avoir le compte actif pour pouvoir effectuer la transaction';
190 END IF;
191
192 UPDATE projet.encheres
193 SET statut='Enchère remportée'
194 WHERE id_enchere=enchere_s;
195
196 UPDATE projet.encheres
197 SET statut='Enchère perdue'
198 WHERE statut='Enchère perdante' AND objet=(SELECT e.objet FROM projet.encheres e WHERE e.id_enchere=enchere_s);
199
200 UPDATE projet.objets
201 SET etat='Vendu'
202 WHERE id_objet=(SELECT e.objet FROM projet.encheres e WHERE e.id_enchere=enchere_s);
203
204 UPDATE projet.utilisateurs
205 SET nombre_objets_achete=nombre_objets_achete+1
206 WHERE id_utilisateur=(SELECT e.encherisseur FROM projet.encheres e WHERE e.id_enchere=enchere_s);
207
208 UPDATE projet.utilisateurs
209 SET nombre_objets_vendu=nombre_objets_vendu+1
210 WHERE id_utilisateur=(SELECT o.proprietaire FROM projet.objets o,projet.encheres e WHERE o.id_objet=e.objet AND e.id_enchere=enchere_s);
211
212 INSERT INTO projet.transactions VALUES(DEFAULT,enchere_s,DEFAULT)
213 RETURNING id_transaction INTO id;
214 RETURN id;
215END;
216$$LANGUAGE plpgsql;
217
218CREATE OR REPLACE FUNCTION projet.ajouter_evaluation(INTEGER, INTEGER, INTEGER, VARCHAR(125)) RETURNS INTEGER AS $$
219DECLARE
220u_evaluateur_s ALIAS FOR $1;
221id_transaction_s ALIAS FOR $2;
222evaluation_s ALIAS FOR $3;
223commentaire_s ALIAS FOR $4;
224
225u_evalue_s INTEGER;
226enchere INTEGER;
227derniere_evaluation INTEGER;
228type_evaluation_s VARCHAR(15):='Vendeur';
229
230id INTEGER:=0;
231BEGIN
232
233 IF ((SELECT u.etat FROM projet.utilisateurs u WHERE u.id_utilisateur=u_evaluateur_s)!='Actif')
234 THEN RAISE 'L evaluateur doit etre Actif pour faire une evaluation !';
235 END IF;
236
237 IF ((SELECT e.u_evaluateur FROM projet.evaluations e WHERE e.id_transaction=id_transaction_s)=u_evaluateur_s)
238 THEN RAISE 'Vous avez déjà laissé une évaluation pour cette transaction !';
239 END IF;
240
241 SELECT t.enchere FROM projet.transactions t WHERE t.id_transaction=id_transaction_s INTO enchere;
242
243 IF ((SELECT e.encherisseur FROM projet.encheres e WHERE e.id_enchere=enchere)=u_evaluateur_s)
244 THEN type_evaluation_s='Acheteur';
245 SELECT o.proprietaire FROM projet.objets o WHERE o.id_objet=(SELECT e.objet FROM projet.encheres e WHERE e.id_enchere=(SELECT t.enchere FROM projet.transactions t WHERE t.id_transaction=id_transaction_s))INTO u_evalue_s;
246 ELSE
247 SELECT e.encherisseur FROM projet.encheres e WHERE e.id_enchere=enchere INTO u_evalue_s;
248 END IF;
249
250 IF ((SELECT u.derniere_evaluation FROM projet.utilisateurs u WHERE u.id_utilisateur=u_evalue_s)=1 AND evaluation_s=1)
251 THEN UPDATE projet.utilisateurs SET etat='Suspendu' WHERE id_utilisateur=u_evalue_s;
252 END IF;
253
254 UPDATE projet.utilisateurs
255 SET nombre_avis=nombre_avis+1
256 WHERE id_utilisateur=u_evalue_s;
257
258 UPDATE projet.utilisateurs
259 SET sum_avis=sum_avis+evaluation_s
260 WHERE id_utilisateur=u_evalue_s;
261
262 UPDATE projet.utilisateurs
263 SET derniere_evaluation=evaluation_s
264 WHERE id_utilisateur=u_evalue_s;
265
266 IF commentaire_s IS NULL
267 THEN INSERT INTO projet.evaluations VALUES (DEFAULT, u_evaluateur_s, u_evalue_s, id_transaction_s, type_evaluation_s, evaluation_s, DEFAULT, DEFAULT)
268 RETURNING id_evaluation INTO id;
269 ELSE INSERT INTO projet.evaluations VALUES (DEFAULT, u_evaluateur_s, u_evalue_s, id_transaction_s, type_evaluation_s, evaluation_s, commentaire_s, DEFAULT)
270 RETURNING id_evaluation INTO id;
271 END IF;
272
273 RETURN id;
274END;
275$$LANGUAGE plpgsql;
276
277CREATE OR REPLACE FUNCTION projet.modifier_objet(INTEGER,INTEGER,VARCHAR(125),TIMESTAMP,DOUBLE PRECISION) RETURNS INTEGER AS $$
278DECLARE
279 id_objet_s ALIAS FOR $1;
280 id_utilisateur_s ALIAS FOR $2;
281 description_s ALIAS FOR $3;
282 date_expiration_s ALIAS FOR $4;
283 prix_depart_s ALIAS FOR $5;
284BEGIN
285 IF ((SELECT o.nombre_encheres FROM projet.objets o WHERE o.id_objet=id_objet_s)>0)
286 THEN RAISE 'L Objet doit avoir aucune enchère pour pouvoir etre modifié !';
287 END IF;
288
289 IF ((SELECT o.proprietaire FROM projet.objets o WHERE o.id_objet=id_objet_s)!=id_utilisateur_s)
290 THEN RAISE 'Seul le proprietaire peut modifier l objet !';
291 END IF;
292
293 IF (date_expiration_s IS NULL )
294 THEN date_expiration_s=now()+INTERVAL '15' DAY;
295 END IF;
296
297 IF (description_s IS NULL)
298 THEN SELECT o.description FROM projet.objets o WHERE o.id_objet=id_objet_s INTO description_s;
299 END IF;
300
301 IF (prix_depart_s =0)
302 THEN select o.prix_depart FROM projet.objets o WHERE o.id_objet=id_objet_s INTO prix_depart_s;
303 END IF;
304
305 UPDATE projet.objets
306 SET description=description_s
307 WHERE id_objet=id_objet_s;
308
309 UPDATE projet.objets
310 SET date_expiration=date_expiration_s
311 WHERE id_objet=id_objet_s;
312
313 UPDATE projet.objets
314 SET prix_depart=prix_depart_s
315 WHERE id_objet=id_objet_s;
316
317 RETURN id_objet_s;
318END;
319$$LANGUAGE plpgsql;
320
321CREATE OR REPLACE FUNCTION projet.modifier_compte_suspendu(INTEGER) RETURNS INTEGER AS $$
322DECLARE
323 id_utilisateur_s ALIAS FOR $1;
324BEGIN
325 IF ((SELECT u.etat FROM projet.utilisateurs u WHERE u.id_utilisateur=id_utilisateur_s)!='Suspendu')
326 THEN RAISE 'L utilisateur doit etre suspendu pour etre acitivé !';
327 END IF;
328
329 UPDATE projet.utilisateurs
330 SET etat='Actif'
331 WHERE id_utilisateur=id_utilisateur_s;
332
333 RETURN id_utilisateur_s;
334END
335$$LANGUAGE plpgsql;
336
337CREATE OR REPLACE FUNCTION projet.supprimer_compte(INTEGER) RETURNS INTEGER AS $$
338DECLARE
339 id_utilisateur_s ALIAS FOR $1;
340BEGIN
341 IF ((SELECT u.etat FROM projet.utilisateurs u WHERE u.id_utilisateur=id_utilisateur_s)!='Suspendu')
342 THEN RAISE 'Le compte doit etre Suspendu pour etre supprimé !';
343 END IF;
344
345 UPDATE projet.utilisateurs
346 SET etat='Supprimé'
347 WHERE id_utilisateur=id_utilisateur_s;
348
349 RETURN id_utilisateur_s;
350END;
351$$LANGUAGE plpgsql;
352
353CREATE OR REPLACE FUNCTION projet.trigger2() RETURNS TRIGGER AS $$
354BEGIN
355 IF ((SELECT u.etat FROM projet.utilisateurs u WHERE u.id_utilisateur=NEW.id_utilisateur)='Supprimé' OR (SELECT u.etat FROM projet.utilisateurs u WHERE u.id_utilisateur=NEW.id_utilisateur)='Suspendu')
356 THEN
357 UPDATE projet.objets
358 SET etat='Annulé'
359 WHERE proprietaire=NEW.id_utilisateur AND etat='En vente';
360
361 UPDATE projet.encheres
362 SET statut='Enchère annulée'
363 WHERE encherisseur=NEW.id_utilisateur AND (statut='Meilleur enchère' OR statut='Enchère perdante');
364 ELSE
365 IF ((SELECT u.etat FROM projet.utilisateurs u WHERE u.id_utilisateur=NEW.id_utilisateur)='Actif')
366 THEN
367 UPDATE projet.objets
368 SET etat='En vente'
369 WHERE proprietaire=NEW.id_utilisateur AND etat='Annulé';
370
371 -- UPDATE projet.encheres
372 -- SET statut='Enchère perdante'
373 -- WHERE encherisseur=NEW.id_utilisateur AND statut='Annulé';
374 END IF;
375 END IF;
376 RETURN NEW ;
377END
378$$LANGUAGE plpgsql;
379
380CREATE TRIGGER trigger_update_objets_apres_modification_etat AFTER UPDATE ON projet.utilisateurs
381FOR EACH ROW EXECUTE PROCEDURE projet.trigger2();
382
383CREATE FUNCTION projet.check_utilisateur(VARCHAR(256),VARCHAR(256)) RETURNS INTEGER AS $$
384DECLARE
385 nom_utilisateur_s ALIAS FOR $1;
386 mdp_s ALIAS FOR $2;
387 code_error INTEGER:=0;
388BEGIN
389
390 IF NOT EXISTS (SELECT u.nom_utilisateur FROM projet.utilisateurs u WHERE u.nom_utilisateur=nom_utilisateur_s)
391 THEN RAISE 'Erreur 01 : Utilisateur inconnu !';
392 END IF;
393
394 IF ((SELECT u.mdp FROM projet.utilisateurs u WHERE u.nom_utilisateur=nom_utilisateur_s)!=mdp_s)
395 THEN RAISE 'Erreur 02 : Mauvais mot de passe !';
396 END IF;
397
398 RETURN code_error;
399END
400$$LANGUAGE plpgsql;
401-- VIEWS
402
403
404-- Consulter tous les objets en cours de vente;
405-- pour chacun des objets il a accès au descriptif,
406-- prix de départ et au temps restant
407CREATE VIEW projet.consulter_objetsEnVente AS
408 (SELECT u.id_utilisateur AS "id vendeur",u.nom_utilisateur AS "vendeur",o.id_objet AS "id objet",o.description AS "Description",o.prix_depart AS "Prix de départ",0 AS "Meilleur enchère",o.nombre_encheres AS "Nombre d'enchères",o.date_expiration-o.date_depart AS "Temps restant"
409 FROM projet.objets o, projet.utilisateurs u
410 WHERE o.proprietaire=u.id_utilisateur AND o.etat='En vente' AND o.date_expiration>now() AND o.nombre_encheres=0
411 GROUP BY u.id_utilisateur,o.id_objet,u.nom_utilisateur)
412
413 UNION
414
415 (SELECT u.id_utilisateur AS "id vendeur",u.nom_utilisateur AS "Vendeur",o.id_objet AS "id objet",o.description AS "Description",o.prix_depart AS "Prix de départ",max(e.montant) AS "Meilleur enchère",o.nombre_encheres AS "Nombre d'enchères",o.date_expiration-o.date_depart AS "Temps restant"
416 FROM projet.objets o, projet.encheres e, projet.utilisateurs u
417 WHERE o.proprietaire=u.id_utilisateur AND o.etat='En vente' AND o.date_expiration>now() AND o.id_objet=e.objet
418 GROUP BY u.id_utilisateur,o.id_objet,u.nom_utilisateur)
419 ORDER BY 8
420;
421
422CREATE VIEW projet.consulter_utilisateur AS
423 (SELECT u.id_utilisateur AS "id", u.nom_utilisateur AS "nom",u.prenom AS "prenom", u.email AS "email",u.nom_utilisateur AS "utilisateur", u.sel AS "sel"
424 FROM projet.utilisateurs u)
425;
426
427CREATE OR REPLACE VIEW projet.consulter_utilisateur_admin AS
428 (SELECT u.id_utilisateur AS "id",u.nom_utilisateur AS "utilisateur", u.nombre_avis AS "Nb avis", (u.sum_avis/u.nombre_avis) AS "Moyenne" , u.etat AS "Etat",u.nombre_objets_achete,u.nombre_objets_vendu
429 FROM projet.utilisateurs u
430 WHERE u.nombre_avis>0) UNION
431 (SELECT u.id_utilisateur AS "id",u.nom_utilisateur AS "utilisateur", u.nombre_avis AS "Nb avis", 0 AS "Moyenne" , u.etat AS "Etat",u.nombre_objets_achete,u.nombre_objets_vendu
432 FROM projet.utilisateurs u
433 WHERE u.nombre_avis=0)
434 ORDER BY 1;
435;
436
437CREATE OR REPLACE VIEW projet.consulter_nombre_objets_achete AS
438 SELECT u.id_utilisateur AS "id_utilisateur",u.nom_utilisateur AS "nom_utilisateur", count(t.id_transaction) AS "Objets achetes",t.date_transaction AS "date"
439 FROM projet.transactions t,projet.encheres e,projet.utilisateurs u
440 WHERE u.id_utilisateur=e.encherisseur AND e.id_enchere =t.enchere
441 GROUP BY 1,2,4
442;
443
444
445CREATE OR REPLACE VIEW projet.consulter_nombre_objets_vendu AS
446 SELECT u.id_utilisateur AS "id_utilisateur",u.nom_utilisateur AS "nom_utilisateur", count(t.id_transaction) AS "Objets vendus",t.date_transaction AS "date"
447 FROM projet.transactions t,projet.encheres e,projet.utilisateurs u,projet.objets o
448 WHERE u.id_utilisateur=o.proprietaire AND o.id_objet=e.objet AND e.id_enchere =t.enchere
449 GROUP BY 1,2,4
450;
451--SELECT sel from projet.consulter_utilisateur WHERE utilisateur=jsako15
452-- Consulter tous les objets en cours de vente;
453-- pour chacun des objets il accès aux différentes enchères déjà proposées pour cet objets
454CREATE VIEW projet.consulter_encheres_objet_en_vente AS
455 SELECT e.id_enchere AS "id_enchere",e.objet AS "id_objet", u.nom_utilisateur AS "Enchérisseur",e.montant AS "Montant", e.statut AS "Statut"
456 FROM projet.encheres e, projet.utilisateurs u, projet.objets o
457 WHERE u.id_utilisateur=e.encherisseur AND o.etat='En vente' AND o.id_objet=e.objet
458 ORDER BY e.montant DESC
459;
460
461CREATE VIEW projet.consulter_encheres_objet AS
462 SELECT e.id_enchere AS "id_enchere",e.objet AS "id_objet", u.nom_utilisateur AS "Enchérisseur",e.montant AS "Montant", e.statut AS "Statut"
463 FROM projet.encheres e, projet.utilisateurs u, projet.objets o
464 WHERE u.id_utilisateur=e.encherisseur AND o.id_objet=e.objet
465 ORDER BY e.montant DESC
466;
467
468CREATE OR REPLACE VIEW projet.consulter_encheres_utilisateur AS
469 SELECT e.id_enchere AS "id_enchere",e.objet AS "id_objet",u.id_utilisateur AS "id_utilisateur", u.nom_utilisateur AS "Enchérisseur",e.montant AS "Montant", e.statut AS "Statut", e.date_enchere AS "Date",o.description AS "Description"
470 FROM projet.encheres e, projet.utilisateurs u, projet.objets o
471 WHERE u.id_utilisateur=e.encherisseur AND o.id_objet=e.objet AND (e.date_enchere > (now() - INTERVAL '15' DAY))
472;
473
474CREATE OR REPLACE VIEW projet.consulter_toutes_encheres_utilisateur AS
475 SELECT e.id_enchere AS "id_enchere",e.objet AS "id_objet",u.id_utilisateur AS "id_utilisateur", u.nom_utilisateur AS "Enchérisseur",e.montant AS "Montant", e.statut AS "Statut", e.date_enchere AS "date_e",o.description AS "Description"
476 FROM projet.encheres e, projet.utilisateurs u, projet.objets o
477 WHERE u.id_utilisateur=e.encherisseur AND o.id_objet=e.objet
478 ORDER BY e.date_enchere DESC
479;
480
481
482CREATE VIEW projet.consulter_objets AS
483 SELECT o.proprietaire AS "proprietaire",o.id_objet AS "id_objet",o.description AS "Description",o.prix_depart AS "Prix de départ",o.nombre_encheres AS "Nombre d'enchères",o.date_expiration-o.date_depart AS "Temps restant",o.etat AS "Etat"
484 FROM projet.objets o
485;
486
487-- Consulter tous les objets en cours de vente;
488-- pour chacun des objets il a accès aux évaluations
489-- du vendeur ainsi qu'à la moyenne de ses évaluations
490CREATE VIEW projet.consulter_evaluations_recu AS
491 SELECT e.id_evaluation AS "id_evaluation",e.u_evalue AS "id_evalue",u.nom_utilisateur AS "evalue",e.u_evaluateur AS "id_evaluateur", u2.nom_utilisateur AS "Evaluateur" ,o.description AS "Description", e.evaluation AS "Evaluation", e.commentaire AS "Commentaire" , (u.sum_avis/u.nombre_avis) AS "Moyenne évaluation"
492 FROM projet.evaluations e, projet.utilisateurs u, projet.utilisateurs u2, projet.transactions t, projet.encheres en, projet.objets o
493 WHERE e.u_evalue=u.id_utilisateur AND e.u_evaluateur = u2.id_utilisateur AND e.id_transaction=t.id_transaction AND t.enchere=en.id_enchere AND en.objet=o.id_objet;
494;
495
496-- Consulter les transactions qu'il a effectué
497-- Consulter les objets qu’il a déjà achetés ou vendus.
498-- De plus, pour chacun de ces objets, il pourra voir
499-- le login de l’utilisateur avec qui il a fait la
500-- transaction et aussi consulter la liste de ses enchères.
501CREATE VIEW projet.consulter_transactions AS
502 SELECT DISTINCT t.id_transaction AS "transaction",t.enchere AS "id_enchere",t.date_transaction AS "date_transaction",u.id_utilisateur AS "id_acheteur", u.nom_utilisateur as "acheteur",u2.id_utilisateur AS "id_vendeur",u2.nom_utilisateur AS "vendeur", o.description AS "description", o.prix_depart "prix_départ",e.montant AS "meilleur_enchere"
503 FROM projet.transactions t, projet.utilisateurs u , projet.utilisateurs u2, projet.objets o, projet.encheres e
504 WHERE t.enchere=e.id_enchere AND e.encherisseur=u.id_utilisateur AND e.objet=o.id_objet AND u2.id_utilisateur=o.proprietaire
505;
506
507SELECT projet.ajouter_utilisateur('Damas','Christophe','cdamas@ipl.be','Damas','$2a$10$9fIG2byECTEms6oYRbsW7eLShXBEe4GriGWEp2w7a1va0Ic.xcUwK','$2a$10$9fIG2byECTEms6oYRbsW7e');
508SELECT projet.ajouter_utilisateur('Ferneeuw','Ferneeuw','Ferneeuw@ipl.be','Ferneeuw','$2a$10$YOv2nsnboJu68DDrLUoh3eD2/xYV9Jgygs1KpikjOIZy1Z/Q5ye.C','$2a$10$YOv2nsnboJu68DDrLUoh3e');
509SELECT projet.ajouter_utilisateur('Khaddam','Khaddam','Khaddam@ipl.be','Khaddam','$2a$10$jxbp8IYVw0NMYcBagOEMe.ZmjDQV0diE3d1k4QzK7CziIazn8eJzC','$2a$10$jxbp8IYVw0NMYcBagOEMe.');
510
511-- GRANT CONNECT ON DATABASE projet TO ljacob15;
512-- GRANT USAGE ON SCHEMA projet TO ljacob15;
513-- GRANT SELECT ON utilisateurs,objets,encheres,transactions,evaluations TO ljacob15;
514-- GRANT INSERT ON TABLE utilisateurs,objets,encheres,transactions,evaluations TO ljacob15;
515-- GRANT UPDATE ON TABLE utilisateurs,objets,encheres,transactions,evaluations TO ljacob15;
516-- GRANT DELETE ON TABLE utilisateurs,objets,encheres,transactions,evaluations TO ljacob15;
517
518GRANT CONNECT ON DATABASE dbljacob15 TO jsako15;
519GRANT USAGE ON SCHEMA projet TO jsako15;
520GRANT SELECT ON TABLE projet.utilisateurs,projet.objets,projet.encheres,projet.transactions,projet.evaluations TO jsako15;
521GRANT INSERT ON TABLE projet.utilisateurs,projet.objets,projet.encheres,projet.transactions,projet.evaluations TO jsako15;
522GRANT UPDATE ON TABLE projet.utilisateurs,projet.objets,projet.encheres,projet.evaluations TO jsako15;
523
524GRANT UPDATE ON projet.utilisateurs_id_utilisateur_seq TO jsako15;
525GRANT UPDATE ON projet.objets_id_objet_seq TO jsako15;
526GRANT UPDATE ON projet.encheres_id_enchere_seq TO jsako15;
527GRANT UPDATE ON projet.transactions_id_transaction_seq TO jsako15;
528GRANT UPDATE ON projet.evaluations_id_evaluation_seq TO jsako15;
529
530GRANT ALL ON projet.consulter_objetsEnVente TO jsako15;
531GRANT ALL ON projet.consulter_utilisateur TO jsako15;
532GRANT ALL ON projet.consulter_nombre_objets_achete TO jsako15;
533GRANT ALL ON projet.consulter_nombre_objets_vendu TO jsako15;
534GRANT ALL ON projet.consulter_encheres_objet_en_vente TO jsako15;
535GRANT ALL ON projet.consulter_encheres_objet TO jsako15;
536GRANT ALL ON projet.consulter_encheres_utilisateur TO jsako15;
537GRANT ALL ON projet.consulter_toutes_encheres_utilisateur TO jsako15;
538GRANT ALL ON projet.consulter_objets TO jsako15;
539GRANT ALL ON projet.consulter_evaluations_recu TO jsako15;
540GRANT ALL ON projet.consulter_transactions TO jsako15;