# Projet BDD
[TOC]

## Dump

DROP SCHEMA IF EXISTS livraison CASCADE;
CREATE SCHEMA livraison;
SET SCHEMA 'livraison';

CREATE TABLE _Responsable_commerce(
    id_responsable    SERIAL         PRIMARY KEY,
    nom               VARCHAR(20)    NOT NULL,
    prenom            VARCHAR(20)    NOT NULL
);

CREATE TABLE _Vendeur(
    id_vendeur        SERIAL         PRIMARY KEY,
    numero_RCS        VARCHAR(15)    NOT NULL,
    nom               VARCHAR(20)    NOT NULL,
    adresse_activite  VARCHAR(30)    NOT NULL,
    ville             VARCHAR(20)    NOT NULL,
    id_responsable    INTEGER        NOT NULL,
    CONSTRAINT fk_vendeur_responsable_commerce
        FOREIGN KEY (id_responsable)
        REFERENCES _Responsable_commerce(id_responsable)
);

CREATE TABLE _Restaurant(
    id_vendeur        INTEGER         PRIMARY KEY,
    specialite        VARCHAR(20)    NOT NULL,
    CONSTRAINT fk_restaurant_vendeur
        FOREIGN KEY (id_vendeur)
        REFERENCES _Vendeur(id_vendeur)
);

CREATE TABLE _Magasin(
    id_vendeur            INTEGER         PRIMARY KEY,
    type                  VARCHAR(20)    NOT NULL,
    CONSTRAINT fk_magasin_vendeur
        FOREIGN KEY (id_vendeur)
        REFERENCES _Vendeur(id_vendeur)
);

CREATE TABLE _Produit(
    id_produit            SERIAL        	PRIMARY KEY,
    nom                   VARCHAR(20)   	NOT NULL,
    prix                  DOUBLE PRECISION  NOT NULL,
    stock                 INTEGER       	NOT NULL,
    id_vendeur            INTEGER       	NOT NULL,
    CONSTRAINT fk_produit_vendeur
        FOREIGN KEY (id_vendeur)
        REFERENCES _Vendeur(id_vendeur)
);

CREATE TABLE _Produit_magasin(
    id_produit            SERIAL        NOT NULL,
    id_vendeur            INTEGER       NOT NULL,
    barre_code            VARCHAR(15)   NOT NULL,
    photo                 VARCHAR(50)   NOT NULL,
    CONSTRAINT pk_produit_magasin
        PRIMARY KEY (id_produit, id_vendeur),
    CONSTRAINT fk_produit_magasin_produit 
        FOREIGN KEY (id_produit)
        REFERENCES _Produit(id_produit),
    CONSTRAINT fk_produit_magasin_vendeur
        FOREIGN KEY (id_vendeur)
        REFERENCES _Vendeur(id_vendeur)
);

CREATE TABLE _Produit_perissable(
    
    id_produit            SERIAL        NOT NULL,
    id_vendeur            INTEGER       NOT NULL,
    date_conso            DATE          NOT NULL,
    CONSTRAINT pk_produit_perissable
        PRIMARY KEY (id_produit, id_vendeur),
    CONSTRAINT fk_produit_magasin_produit 
        FOREIGN KEY (id_produit)
        REFERENCES _Produit(id_produit),
    CONSTRAINT fk_produit_magasin_vendeur
        FOREIGN KEY (id_vendeur)
        REFERENCES _Vendeur(id_vendeur)
);

CREATE TABLE _Client(
    id_client             SERIAL        PRIMARY KEY,
    nom                   VARCHAR(20)   NOT NULL,
    prenom                VARCHAR(20)   NOT NULL,
    nom_utilisateur       VARCHAR(20)   NOT NULL,
    mdp                   VARCHAR(20)   NOT NULL,
    adresse               VARCHAR(30)   NOT NULL,
    ville                 VARCHAR(20)   NOT NULL,
    num_telephone         VARCHAR(15)   NOT NULL
);

CREATE TABLE _Client_malade_covid(
    id_client             INTEGER,
    date_contamination    DATE,
    CONSTRAINT pk_client_malade_covid
        PRIMARY KEY (id_client, date_contamination),
    CONSTRAINT fk_client_malade
        FOREIGN KEY (id_client)
        REFERENCES _Client(id_client)
);

CREATE TABLE _Facteur(
    id_facteur            SERIAL        	PRIMARY KEY,
    nom                   VARCHAR(20)   	NOT NULL,
    prenom                VARCHAR(20)   	NOT NULL,
    date_recrutement      DATE          	NOT NULL,
    adresse               VARCHAR(30)   	NOT NULL,
    ville                 VARCHAR(20)   	NOT NULL,
    temperature           DOUBLE PRECISION  NOT NULL,
    id_vendeur            SERIAL        	NOT NULL,
    CONSTRAINT fk_facteur_vendeur 
        FOREIGN KEY (id_vendeur)
        REFERENCES _Vendeur(id_vendeur)
);

CREATE TABLE _Facteur_malade_covid(
    id_facteur             INTEGER,
    date_contamination     DATE,
    CONSTRAINT pk_facteur_malade_covid
        PRIMARY KEY (id_facteur, date_contamination)
);

CREATE TABLE _Itineraire(
    id_facteur             INTEGER       NOT NULL,
    adresse                VARCHAR(30)   NOT NULL,
    ville                  VARCHAR(20)   NOT NULL,
    date                   Date          NOT NULL,
	CONSTRAINT pk_itineraire
		PRIMARY KEY (id_facteur, adresse, ville, date),
    CONSTRAINT fk_itineraire_facteur
        FOREIGN KEY (id_facteur)
        REFERENCES _Facteur(id_facteur)
);

CREATE TABLE _Etat(
    code                   SERIAL        PRIMARY KEY,
    description            VARCHAR(10)   NOT NULL
);

CREATE TABLE _Commande(
    id_commande            SERIAL        	PRIMARY KEY,
    date                   DATE          	NOT NULL,
    etat                   INTEGER       	NOT NULL,
    prix_total             DOUBLE PRECISION DEFAULT 0,
    id_client              INTEGER       	NOT NULL,
    id_facteur             INTEGER       	NOT NULL,
    CONSTRAINT fk_commande_etat
        FOREIGN KEY (etat)
        REFERENCES _Etat(code),
    CONSTRAINT fk_commande_client
        FOREIGN KEY (id_client)
        REFERENCES _Client(id_client),
    CONSTRAINT fk_commande_facteur
        FOREIGN KEY (id_facteur)
        REFERENCES _Facteur(id_facteur)
);

CREATE TABLE _Commande_changement(
    id_commande            INTEGER       NOT NULL,
    date                   DATE          NOT NULL,
    ancien_etat            INTEGER       NOT NULL,
    nouvel_etat            INTEGER       NOT NULL,
    PRIMARY KEY(id_commande, date),
    CONSTRAINT fk_commande_changement_commande
        FOREIGN KEY (id_commande)
        REFERENCES _Commande(id_commande),
    CONSTRAINT fk_commande_changement_ancien_etat
        FOREIGN KEY (ancien_etat)
        REFERENCES _Etat(code),
    CONSTRAINT fk_commande_changement_nouvel_etat
        FOREIGN KEY (nouvel_etat)
        REFERENCES _Etat(code)
);

CREATE TABLE _Quantite_commandee(
    id_commande            INTEGER       NOT NULL,
    id_produit             INTEGER       NOT NULL,
    quantite               INTEGER       NOT NULL,
    PRIMARY KEY (id_commande,id_produit),
    CONSTRAINT fk_qauntite_comandee_commande
        FOREIGN KEY (id_commande)
        REFERENCES _Commande(id_commande),
    CONSTRAINT fk_qauntite_comandee_produit
        FOREIGN KEY(id_produit)
        REFERENCES _Produit(id_produit)
    
);

## Trigger
Calcul du prix total

CREATE OR REPLACE FUNCTION calcul_somme_commande()
RETURNS TRIGGER AS $$
DECLARE
  prixTotal          double precision;
BEGIN
	prixTotal = (SELECT SUM(_Quantite_commandee.quantite*_Produit.prix) as prixTotal FROM livraison._quantite_commandee NATURAL JOIN livraison._Produit
	WHERE _Quantite_commandee.id_commande = new.id_commande
	GROUP BY _Quantite_commandee.id_commande
	ORDER BY _Quantite_commandee.id_commande ASC);
	
	UPDATE livraison._Commande 
	SET prix_total = prixTotal
        WHERE id_commande= new.id_commande;

RETURN NULL;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER prixTotal_commande
    AFTER INSERT OR UPDATE ON livraison._Quantite_commandee
    FOR EACH ROW 
    EXECUTE PROCEDURE calcul_somme_commande();
    

Vérification Facteur malade

CREATE OR REPLACE FUNCTION fn_insert_facteur()
RETURNS TRIGGER AS $$
    BEGIN
        IF NEW.temperature > 38.0 THEN
            INSERT INTO livraison._Facteur_malade_covid VALUES (NEW.id_facteur, NOW());
        END IF;
    
    RETURN NULL;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tg_insert_update_facteur
    AFTER INSERT OR UPDATE
    ON livraison._Facteur
    FOR EACH ROW
    EXECUTE PROCEDURE fn_insert_facteur();

## Insertion des données

INSERT INTO livraison._Responsable_commerce VALUES (DEFAULT, 'Aubert', 'Guillaume');
INSERT INTO livraison._Responsable_commerce VALUES (DEFAULT, 'Beauvilliers', 'Lelsy');
INSERT INTO livraison._Responsable_commerce VALUES (DEFAULT, 'Droz', 'Leonard');
INSERT INTO livraison._Responsable_commerce VALUES (DEFAULT, 'Bonnel', 'Gaetane');
INSERT INTO livraison._Responsable_commerce VALUES (DEFAULT, 'Deschanel', 'Olivia');

INSERT INTO livraison._Vendeur VALUES (DEFAULT, 11449234300022, 'La Hublais', '28 rue de rennes', 'Cesson-Sévigné', 3);
INSERT INTO livraison._Vendeur VALUES (DEFAULT, 45132133500510, 'Carrefour', 'La rigoudière', 'Cesson-Sévigné', 4);
INSERT INTO livraison._Vendeur VALUES (DEFAULT, 80311617700012, 'Poutinebros', '17 rue de penhoet', 'Rennes', 2);
INSERT INTO livraison._Vendeur VALUES (DEFAULT, 32800749700055, 'Biocoop scarabée', '132 rue Eugene Pottier', 'Rennes', 1);
INSERT INTO livraison._Vendeur VALUES (DEFAULT, 51936713000019, 'Ethnic food', '3 Place saint Anne', 'Rennes', 5);
INSERT INTO livraison._Vendeur VALUES (DEFAULT, 80241714700013, 'Pita pit', '8 rue de la visitation', 'Rennes', 5);

INSERT INTO livraison._Restaurant VALUES (1, 'Galettes et crêpes');
INSERT INTO livraison._Restaurant VALUES (3, 'Poutine');
INSERT INTO livraison._Restaurant VALUES (5, 'Hamburger');
INSERT INTO livraison._Restaurant VALUES (6, 'Healthy food');

INSERT INTO livraison._Magasin VALUES (2, 'Centre commercial');
INSERT INTO livraison._Magasin VALUES (4, 'Magasin bio');

INSERT INTO livraison._Produit VALUES (DEFAULT, 'La St Nectaire', 15.50, 60, 1);
INSERT INTO livraison._Produit VALUES (DEFAULT, 'Snickers', 9.50, 70, 1);
INSERT INTO livraison._Produit VALUES (DEFAULT, 'La savoyarde', 11.90, 60, 1);
INSERT INTO livraison._Produit VALUES (DEFAULT, 'Avocat', 2, 400, 2);
INSERT INTO livraison._Produit VALUES (DEFAULT, 'Papier toilette', 2.50, 103, 2);
INSERT INTO livraison._Produit VALUES (DEFAULT, 'Pâtes', 1.20, 100, 2);
INSERT INTO livraison._Produit VALUES (DEFAULT, 'Riz', 1.60, 90, 2);
INSERT INTO livraison._Produit VALUES (DEFAULT, 'Farine', 1.30, 75, 2);
INSERT INTO livraison._Produit VALUES (DEFAULT, 'India', 7.50, 60, 3);
INSERT INTO livraison._Produit VALUES (DEFAULT, 'Grizzli', 7.90, 40, 3);
INSERT INTO livraison._Produit VALUES (DEFAULT, 'Veggie', 7.50, 45, 3);
INSERT INTO livraison._Produit VALUES (DEFAULT, 'Reguliere', 6.90, 100, 3);
INSERT INTO livraison._Produit VALUES (DEFAULT, 'Lentille corail', 4, 80, 4);
INSERT INTO livraison._Produit VALUES (DEFAULT, 'Graine chia', 2.10, 200, 4);
INSERT INTO livraison._Produit VALUES (DEFAULT, 'Pain de sègle', 1.30, 70, 4);
INSERT INTO livraison._Produit VALUES (DEFAULT, 'California', 4.90, 60, 5);
INSERT INTO livraison._Produit VALUES (DEFAULT, 'Bombay', 4.60, 60, 5);
INSERT INTO livraison._Produit VALUES (DEFAULT, 'Pita poulet', 6.50, 50, 6);
INSERT INTO livraison._Produit VALUES (DEFAULT, 'Poke bawl', 10.50, 80, 6);

INSERT INTO livraison._Produit_magasin VALUES (4, 2, 50123548795,'https://carrefour.fr/avocat');
INSERT INTO livraison._Produit_magasin VALUES (5, 2, 40265875204,'https://carrefour.fr/papier_toilette');
INSERT INTO livraison._Produit_magasin VALUES (6, 2, 85214522034,'https://carrefour.fr/pates');
INSERT INTO livraison._Produit_magasin VALUES (7, 2, 85412213898,'https://carrefour.fr/riz');
INSERT INTO livraison._Produit_magasin VALUES (8, 2, 32548755216,'https://carrefour.fr/farine');
INSERT INTO livraison._Produit_magasin VALUES (13, 4, 12255487503,'https://biocoop.fr/lentille_corail');
INSERT INTO livraison._Produit_magasin VALUES (14, 4, 25452236677,'https://biocoop.fr/graine_chia');
INSERT INTO livraison._Produit_magasin VALUES (15, 4, 45428874413,'https://biocoop.fr/pain_segle');

INSERT INTO livraison._Produit_perissable VALUES (4 ,2 ,'2020-07-03');
INSERT INTO livraison._Produit_perissable VALUES (6 ,2 ,'2021-02-18');
INSERT INTO livraison._Produit_perissable VALUES (7 ,2 ,'2021-03-30');
INSERT INTO livraison._Produit_perissable VALUES (8 ,2 ,'2021-05-06');
INSERT INTO livraison._Produit_perissable VALUES (13 ,4 ,'2021-04-10');
INSERT INTO livraison._Produit_perissable VALUES (14 ,4 ,'2022-10-12');
INSERT INTO livraison._Produit_perissable VALUES (15 ,4 ,'2020-07-03');

INSERT INTO livraison._Client VALUES (DEFAULT, 'Brunelle', 'David', 'David35', '195847df', '260 rue de Fougère', 'Rennes', '0771542695');
INSERT INTO livraison._Client VALUES (DEFAULT, 'Bourque', 'Hugo', 'Sop35', 'Hbour3514', '2 avenue Général Leclerc', 'Cesson-Sévigné', '0651235879');
INSERT INTO livraison._Client VALUES (DEFAULT, 'Lozé', 'Karine', 'Serwana', 'KarSer1458', '10 rue de Paris', 'Rennes', '0654893214');
INSERT INTO livraison._Client VALUES (DEFAULT, 'Massé', 'Anita', 'Ani_mas', 'LPfg35420', '5 avenue François Château', 'Rennes', '0254879631');
INSERT INTO livraison._Client VALUES (DEFAULT, 'Calvet', 'Aurélien', 'KenpA', 'kP85A', '18 avenue Chardonnet', 'Rennes', '0251416578');
INSERT INTO livraison._Client VALUES (DEFAULT, 'Naudé', 'Valentin', 'Vlad', 'ValNau12547', '8 rue de Rennes', 'Rennes', '0265879436');
INSERT INTO livraison._Client VALUES (DEFAULT, 'Dupont', 'Adeline', 'Pyrola', 'OsiBDD', '15 Boulevard de Sévigné', 'Cesson-Sévigné', '0145862530');

INSERT INTO livraison._Client_malade_covid VALUES (4, '2020-04-13');
INSERT INTO livraison._Client_malade_covid VALUES (5, '2020-04-10');
INSERT INTO livraison._Client_malade_covid VALUES (6, '2020-04-12');

INSERT INTO livraison._Facteur VALUES (DEFAULT, 'Neri', 'Denis', '2020-04-17', '10 rue du Plateau Dogon', 'Rennes', 36.5, 3);
INSERT INTO livraison._Facteur VALUES (DEFAULT, 'Rouanet', 'Sylvie', '2020-04-18', '20 rue Rossini', 'Rennes', 36.3, 3);
INSERT INTO livraison._Facteur VALUES (DEFAULT, 'Duchamp', 'Violette', '2020-04-20', '3 rue de Chateaugiron', 'Rennes', 37, 3);
INSERT INTO livraison._Facteur VALUES (DEFAULT, 'Raoult', 'Stéphane', '2020-04-02', '5 Allée Mozart', 'Rennes', 36, 4);
INSERT INTO livraison._Facteur VALUES (DEFAULT, 'Auch', 'Laurette', '2020-03-28', '20 rue des Pins', 'Cesson-Sévigné', 38, 5);
INSERT INTO livraison._Facteur VALUES (DEFAULT, 'Milhaud', 'Clémence', '2020-03-28', '4 rue du Muguet', 'Cesson-Sévigné', 37.5, 5);
INSERT INTO livraison._Facteur VALUES (DEFAULT, 'Duret', 'Dylan', '2020-03-25', '9 rue Michelet', 'Rennes', 36.1, 2);
INSERT INTO livraison._Facteur VALUES (DEFAULT, 'Leblanc', 'Milo', '2020-03-29', '15 rue du thabor', 'Rennes', 36.8, 2);
INSERT INTO livraison._Facteur VALUES (DEFAULT, 'Durand', 'Thibaut', '2020-04-01', '17 rue Lavoisier', 'Rennes', 36.7, 2);
INSERT INTO livraison._Facteur VALUES (DEFAULT, 'Abbadie', 'Inès', '2020-04-04', '6 rue le Dantec', 'Rennes', 37.5, 2);
INSERT INTO livraison._Facteur VALUES (DEFAULT, 'Renou', 'Lucie', '2020-04-06', '14 rue des Landelles', 'Rennes', 38.2, 1);
INSERT INTO livraison._Facteur VALUES (DEFAULT, 'Gustave', 'Lucas', '2020-04-08', '20 rue de Bray', 'Rennes', 36, 1);
INSERT INTO livraison._Facteur VALUES (DEFAULT, 'Millet', 'Jacques', '2020-03-24', '5 rue du Bignon', 'Rennes', 36.4, 6);


INSERT INTO livraison._Itineraire VALUES (1, '260 rue de Fougère', 'Rennes', '2020-04-20');
INSERT INTO livraison._Itineraire VALUES (1, '10 rue de Paris', 'Rennes', '2020-04-18');
INSERT INTO livraison._Itineraire VALUES (1, '15 Boulevard de Sévigné', 'Cesson-Sévigné', '2020-04-22');
INSERT INTO livraison._Itineraire VALUES (2, '8 rue de Rennes', 'Rennes', '2020-04-28');
INSERT INTO livraison._Itineraire VALUES (3, '2 avenue Général Leclerc', 'Cesson-Sévigné', '2020-04-29');
INSERT INTO livraison._Itineraire VALUES (3, '15 Boulevard de Sévigné', 'Cesson-Sévigné', '2020-04-25');
INSERT INTO livraison._Itineraire VALUES (5, '10 rue de Paris', 'Rennes', '2020-04-04');
INSERT INTO livraison._Itineraire VALUES (5, '18 avenue Chardonnet', 'Rennes', '2020-04-10');
INSERT INTO livraison._Itineraire VALUES (5, '8 rue de Rennes', 'Rennes', '2020-04-12');
INSERT INTO livraison._Itineraire VALUES (6, '2 avenue Général Leclerc', 'Cesson-Sévigné', '2020-04-03');
INSERT INTO livraison._Itineraire VALUES (7, '260 rue de Fougère', 'Rennes', '2020-03-27');
INSERT INTO livraison._Itineraire VALUES (8, '5 avenue François Château', 'Rennes', '2020-04-08');
INSERT INTO livraison._Itineraire VALUES (8, '10 rue de Paris', 'Rennes', '2020-04-07');
INSERT INTO livraison._Itineraire VALUES (11, '5 avenue François Château', 'Rennes', '2020-04-10');
INSERT INTO livraison._Itineraire VALUES (11, '18 avenue Chardonnet', 'Rennes', '2020-04-11');
INSERT INTO livraison._Itineraire VALUES (12, '2 avenue Général Leclerc', 'Cesson-Sévigné', '2020-04-09');
INSERT INTO livraison._Itineraire VALUES (13, '5 avenue François Château', 'Rennes', '2020-04-15');

INSERT INTO livraison._Etat VALUES (DEFAULT, 'Validée');
INSERT INTO livraison._Etat VALUES (DEFAULT, 'Transit');
INSERT INTO livraison._Etat VALUES (DEFAULT, 'Terminée');
INSERT INTO livraison._Etat VALUES (DEFAULT, 'Annulée');

INSERT INTO livraison._Commande VALUES (DEFAULT, '2020-04-22', 1,DEFAULT, 7, 1);
INSERT INTO livraison._Commande VALUES (DEFAULT, '2020-04-03', 4,DEFAULT, 2, 6);
INSERT INTO livraison._Commande VALUES (DEFAULT, '2020-04-20', 1,DEFAULT, 1, 1);
INSERT INTO livraison._Commande VALUES (DEFAULT, '2020-04-18', 2,DEFAULT, 7, 1);
INSERT INTO livraison._Commande VALUES (DEFAULT, '2020-04-28', 4,DEFAULT, 6, 2);
INSERT INTO livraison._Commande VALUES (DEFAULT, '2020-04-25', 1,DEFAULT, 7, 3);
INSERT INTO livraison._Commande VALUES (DEFAULT, '2020-04-04', 3,DEFAULT, 3, 5);
INSERT INTO livraison._Commande VALUES (DEFAULT, '2020-04-10', 2,DEFAULT, 5, 5);
INSERT INTO livraison._Commande VALUES (DEFAULT, '2020-04-12', 1,DEFAULT, 6, 5);
INSERT INTO livraison._Commande VALUES (DEFAULT, '2020-03-27', 4,DEFAULT, 1, 7);
INSERT INTO livraison._Commande VALUES (DEFAULT, '2020-04-08', 1,DEFAULT, 4, 8);
INSERT INTO livraison._Commande VALUES (DEFAULT, '2020-04-07', 4,DEFAULT, 3, 8);
INSERT INTO livraison._Commande VALUES (DEFAULT, '2020-04-13', 2,DEFAULT, 4, 11);
INSERT INTO livraison._Commande VALUES (DEFAULT, '2020-04-09', 3,DEFAULT, 2, 12);
INSERT INTO livraison._Commande VALUES (DEFAULT, '2020-04-11', 4,DEFAULT, 5, 11);
INSERT INTO livraison._Commande VALUES (DEFAULT, '2020-04-29', 2,DEFAULT, 2, 7);
INSERT INTO livraison._Commande VALUES (DEFAULT, '2020-04-15', 3,DEFAULT, 4, 13);

INSERT INTO livraison._Commande_changement VALUES (2, '2020-04-04', 1, 2);
INSERT INTO livraison._Commande_changement VALUES (2, '2020-04-05', 2, 4);
INSERT INTO livraison._Commande_changement VALUES (5, '2020-04-22', 1, 2);
INSERT INTO livraison._Commande_changement VALUES (5, '2020-04-28', 2, 4);
INSERT INTO livraison._Commande_changement VALUES (7, '2020-04-05', 1, 2);
INSERT INTO livraison._Commande_changement VALUES (7, '2020-04-06', 2, 3);
INSERT INTO livraison._Commande_changement VALUES (10, '2020-03-29', 1, 2);
INSERT INTO livraison._Commande_changement VALUES (10, '2020-03-30', 2, 4);
INSERT INTO livraison._Commande_changement VALUES (12, '2020-04-08', 1, 4);
INSERT INTO livraison._Commande_changement VALUES (12, '2020-04-09', 2, 4);
INSERT INTO livraison._Commande_changement VALUES (14, '2020-04-10', 1, 2);
INSERT INTO livraison._Commande_changement VALUES (14, '2020-04-11', 2, 3);
INSERT INTO livraison._Commande_changement VALUES (15, '2020-04-11', 1, 4);
INSERT INTO livraison._Commande_changement VALUES (17, '2020-04-16', 1, 2);
INSERT INTO livraison._Commande_changement VALUES (17, '2020-04-17', 2, 3);

INSERT INTO livraison._Quantite_commandee VALUES (1, 9, 2);
INSERT INTO livraison._Quantite_commandee VALUES (1, 10, 1);
INSERT INTO livraison._Quantite_commandee VALUES (2, 16, 3);
INSERT INTO livraison._Quantite_commandee VALUES (3, 11, 1);
INSERT INTO livraison._Quantite_commandee VALUES (4, 12, 2);
INSERT INTO livraison._Quantite_commandee VALUES (4, 10, 2);
INSERT INTO livraison._Quantite_commandee VALUES (5, 9, 1);
INSERT INTO livraison._Quantite_commandee VALUES (5, 10, 1);
INSERT INTO livraison._Quantite_commandee VALUES (5, 11, 1);
INSERT INTO livraison._Quantite_commandee VALUES (6, 12, 2);
INSERT INTO livraison._Quantite_commandee VALUES (6, 11, 1);
INSERT INTO livraison._Quantite_commandee VALUES (7, 16, 3);
INSERT INTO livraison._Quantite_commandee VALUES (8, 16, 4);
INSERT INTO livraison._Quantite_commandee VALUES (8, 17, 4);
INSERT INTO livraison._Quantite_commandee VALUES (9, 17, 2);
INSERT INTO livraison._Quantite_commandee VALUES (10, 4, 2);
INSERT INTO livraison._Quantite_commandee VALUES (10, 5, 1);
INSERT INTO livraison._Quantite_commandee VALUES (10, 6, 3);
INSERT INTO livraison._Quantite_commandee VALUES (10, 8, 2);
INSERT INTO livraison._Quantite_commandee VALUES (11, 5, 2);
INSERT INTO livraison._Quantite_commandee VALUES (11, 6, 2);
INSERT INTO livraison._Quantite_commandee VALUES (12, 4, 2);
INSERT INTO livraison._Quantite_commandee VALUES (13, 1, 1);
INSERT INTO livraison._Quantite_commandee VALUES (13, 2, 2);
INSERT INTO livraison._Quantite_commandee VALUES (13, 3, 1);
INSERT INTO livraison._Quantite_commandee VALUES (14, 1, 2);
INSERT INTO livraison._Quantite_commandee VALUES (14, 2, 4);
INSERT INTO livraison._Quantite_commandee VALUES (14, 3, 1);
INSERT INTO livraison._Quantite_commandee VALUES (15, 1, 1);
INSERT INTO livraison._Quantite_commandee VALUES (16, 6, 2);
INSERT INTO livraison._Quantite_commandee VALUES (17, 18, 2);
INSERT INTO livraison._Quantite_commandee VALUES (17, 19, 3);

## VIEWS

CREATE VIEW malade_covid as

    SELECT nom, prenom 
    FROM livraison._Facteur NATURAl 
    JOIN livraison._Facteur_malade_covid

    UNION

    SELECT nom, prenom 
    FROM livraison._Client NATURAL 
    JOIN livraison._Client_malade_covid; 
    
    
CREATE VIEW profil_client AS

    SELECT nom, prenom, nom_utilisateur, adresse, ville, num_telephone 
    FROM livraison._Client;
    
    
CREATE VIEW produits_restaurant AS

    SELECT _Vendeur.nom as restaurant, _Restaurant.specialite, _Produit.nom as produit, _Produit.prix 
    FROM livraison._Produit NATURAL 
        JOIN livraison._Restaurant INNER 
        JOIN livraison._Vendeur 
            ON livraison._Restaurant.id_vendeur = livraison._Vendeur.id_vendeur;
            
            
CREATE VIEW produits_magasin AS

    SELECT _Vendeur.nom as magasin, _Magasin.type, _Produit.nom as produit, _Produit.prix
    FROM livraison._Produit NATURAL 
        JOIN livraison._Magasin INNER
        JOIN livraison._Vendeur 
            ON livraison._Magasin.id_vendeur = livraison._Vendeur.id_vendeur;
            

CREATE VIEW produit_vendeur AS
SELECT _Vendeur.id_vendeur,_Vendeur.nom, _Produit.nom as produit, _Produit.prix 
    FROM livraison._Produit NATURAL 
        JOIN livraison._Restaurant INNER 
        JOIN livraison._Vendeur 
            ON livraison._Restaurant.id_vendeur = livraison._Vendeur.id_vendeur		
UNION

SELECT _Vendeur.id_vendeur,_Vendeur.nom, _Produit.nom as produit, _Produit.prix
    FROM livraison._Produit NATURAL 
        JOIN livraison._Magasin INNER
        JOIN livraison._Vendeur 
            ON livraison._Magasin.id_vendeur = livraison._Vendeur.id_vendeur;
            


CREATE VIEW commande_client_produit as
SELECT _Commande.id_commande,_Client.nom, _Client.prenom, _Client.nom_utilisateur, _Produit.nom as produit, _Quantite_commandee.quantite, _commande.prix_total 
        FROM livraison._CLient natural 
            JOIN livraison._Commande INNER 
            JOIN livraison._quantite_commandee 
                ON livraison._Commande.id_commande = livraison._quantite_commandee.id_commande
            INNER JOIN livraison._Produit 
                on livraison._quantite_commandee.id_produit = livraison._Produit.id_produit;
                
                
CREATE VIEW client_malade_commande_facteur AS
SELECT _client_malade_covid.id_client, _client_malade_covid.date_contamination, _Commande.id_facteur, _Commande.date as dateLivraison 
        FROM livraison._Commande NATURAL JOIN livraison._client_malade_covid;

## Procédure

Recherche mot_clé

CREATE OR REPLACE FUNCTION show_produit_vendeur (idV integer, mot text)	
RETURNS RECORD AS $$
DECLARE 
  ret RECORD;
BEGIN
	SELECT * FROM produit_vendeur WHERE id_vendeur = idV AND produit LIKE CONCAT('%',mot,'%') INTO ret;
RETURN ret;
END;
$$ LANGUAGE plpgsql;

annulation d'une commande

CREATE OR REPLACE FUNCTION annuler_commande (idC integer)	
RETURNS text AS $$
DECLARE
	dateC Date;
	etat_ancien integer;
BEGIN
	SELECT etat FROM livraison._Commande WHERE id_commande = $1 into etat_ancien;

	UPDATE livraison._Commande SET etat = 4 
	WHERE id_commande = $1;
	
	SELECT date FROM livraison._Commande WHERE id_commande = $1 into dateC;
	
	INSERT INTO livraison._commande_changement VALUES ($1,dateC,etat_ancien,4);
	
	DELETE FROM livraison._quantite_commandee WHERE id_commande = $1;
	
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

chiffre d'affaire d'un vendeur entre 2 dates

CREATE OR REPLACE FUNCTION calcul_chiffre_affaire (date1 date, date2 date, idV integer)	
RETURNS DOUBLE PRECISION AS $$
DECLARE
	ca DOUBLE PRECISION;
BEGIN

    SELECT SUM(quantite*prix) from livraison._produit NATURAL JOIN livraison._quantite_commandee INNER JOIN livraison._commande
    ON livraison._quantite_commandee.id_commande = livraison._commande.id_commande 
    WHERE id_vendeur=$3 AND date>$1 AND date < $2 into ca;
	
RETURN ca;
END;
$$ LANGUAGE plpgsql;

Traçage

CREATE OR REPLACE FUNCTION tracage_client (idC integer)
RETURNS RECORD AS $$
DECLARE
	facteur integer;
	dateL	DATE;
	res     RECORD;
BEGIN

    SELECT id_facteur FROM client_malade_commande_facteur 
		WHERE id_client = $1 AND dateLivraison > date_contamination into facteur;
		
	SELECT dateLivraison FROM client_malade_commande_facteur 
		WHERE id_client = $1 into dateL;
	
	IF facteur IS NOT null AND dateL IS NOT null THEN
		SELECT _Client.nom, _Client.prenom FROM livraison._client NATURAL JOIN livraison._Commande
			WHERE id_facteur = facteur AND date >= dateL into res;
	END IF;
	
	
RETURN res;
END;
$$ LANGUAGE plpgsql

Date rencontre facteur malade facteur

CREATE OR REPLACE FUNCTION show_vendeur_facteur_malade ()	
RETURNS SETOF refcursor AS 
$BODY$ DECLARE 
  refv refcursor;
BEGIN
	OPEN refv FOR 
	SELECT _vendeur.nom as Vendeur, _facteur.prenom as facteur, _itineraire.adresse, _itineraire.date FROM livraison._itineraire NATURAL JOIN livraison._facteur_malade_covid 
INNER JOIN livraison._facteur ON livraison._facteur.id_facteur = livraison._facteur_malade_covid.id_facteur
INNER JOIN livraison._vendeur ON livraison._vendeur.id_vendeur = livraison._facteur.id_vendeur; RETURN NEXT refv;

END;
$BODY$ LANGUAGE plpgsql;

Transaction

	
-- Start a transaction
   BEGIN;
 
    SELECT show_vendeur_facteur_malade ();
 
   FETCH ALL IN "<unnamed portal 1>";
 
   COMMIT;

Vendeur ayant rencontrer un facteur qui a livré un client malade

CREATE OR REPLACE FUNCTION show_vendeur_client_malade (id_cli integer)	
RETURNS SETOF refcursor AS 
$BODY$ DECLARE 
  ref2 refcursor;
BEGIN
	OPEN ref2 FOR 
	SELECT DISTINCT _vendeur.id_vendeur, _Vendeur.nom FROM livraison._facteur NATURAL JOIN livraison._commande 
        INNER JOIN livraison._vendeur ON livraison._vendeur.id_vendeur = livraison._facteur.id_vendeur
        INNER JOIN livraison._client ON livraison._client.id_client = livraison._commande.id_client
        INNER JOIN livraison._client_malade_covid ON livraison._client_malade_covid.id_client = livraison._client.id_client 
            WHERE _client_malade_covid.id_client = $1; RETURN NEXT ref2;

END;
$BODY$ LANGUAGE plpgsql;

Transaction

	
-- Start a transaction
   BEGIN;
 
    SELECT show_vendeur_client_malade  (4);

    FETCH ALL IN "<unnamed portal 2>";
    
    COMMIT;