db_GESTION

CREATE DATABASE gestion_Des_ventes_PFE
---------------------------------------------------------------------------------------------------------------------------------------------------
USE gestion_Des_ventes_PFE
---------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE CATEGORIE
(
    ID_CAT INT IDENTITY (1,1),
    NOM_CAT VARCHAR(50),
    DES_CAT VARCHAR(50),
    CONSTRAINT PK_CATEGORIE PRIMARY KEY(ID_CAT)
)
---------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE PRODUIT
(
    ID_PRODUIT INT IDENTITY (1,1),
    ID_CAT INT,
    NOM_PRODUIT VARCHAR(40),
    QTE_STOCK INT,
    PRIX VARCHAR(25),
    FOUR VARCHAR(20),
    img_prod TEXT,
    CONSTRAINT PK_PRODUIT PRIMARY KEY(ID_PRODUIT),
    CONSTRAINT FK_CATEGORIE FOREIGN KEY(ID_CAT) REFERENCES CATEGORIE(ID_CAT) ON DELETE cascade ON UPDATE cascade
)
---------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE CLIENT
(
    ID_CLIENT INT IDENTITY(1, 1),
    NOM_CLIENT VARCHAR(30),
    PRN_CLIENT VARCHAR(30),
    TEL_CLIENT VARCHAR(20),
    EMAIL VARCHAR(50),
    COUNTRY VARCHAR(50),
    VILLE VARCHAR(50),
    ADRESSE VARCHAR(50)
    CONSTRAINT PK_CLIENT PRIMARY KEY(ID_CLIENT)
)
---------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE DETAIL_CMD
(
    ID_CMD INT IDENTITY(1, 1),
    DATE_CMD DATE,
    NOM_VENDEUR VARCHAR(75),
    DESCRIPTION_CMD VARCHAR(250),
    ID_PRODUIT INT,
    ID_CLIENT INT,
    QUANTITE INT,
    PRICE MONEY,
    DISCOUNT FLOAT,
    AMOUNT VARCHAR(50),
    TOTAL_AMOUNT VARCHAR(50),
    CONSTRAINT PK_PRODUIT_COMMANDE PRIMARY KEY(ID_CMD),
    CONSTRAINT FK_PRODUIT FOREIGN KEY(ID_PRODUIT) REFERENCES PRODUIT(ID_PRODUIT) ON UPDATE cascade ON DELETE cascade,
    CONSTRAINT FK_CLIENTID FOREIGN KEY(ID_CLIENT) REFERENCES CLIENT(ID_CLIENT) ON UPDATE cascade ON DELETE cascade,
)
---------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE DEVIS (
ID_DEVIS INT IDENTITY(1,1) PRIMARY KEY,
ID_CLIENT INT,
DATE_DEVIS DATE,
ID_PRODUIT INT,
Quantité INT,
PRIX FLOAT,
DISCOUNT FLOAT,
MONTANT_TOTAL FLOAT,
NOM_BUYER VARCHAR(30)
CONSTRAINT FK_ID_CLIENT1 FOREIGN KEY (ID_CLIENT) REFERENCES CLIENT(ID_CLIENT),
CONSTRAINT FK_ID_PRODUIT1 FOREIGN KEY (ID_PRODUIT) REFERENCES PRODUIT(ID_PRODUIT)
)
---------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE LIVRAISON(
ID_LIVRAISON INT IDENTITY(1,1) PRIMARY KEY,
ID_CLIENT INT,
NOM_VENDEUR VARCHAR(20),
CLIENT_PAYS VARCHAR(20),
CLIENT_VILLE VARCHAR(20),
CLIENT_ADRESSE VARCHAR(70),
MODE_LIVRAISON VARCHAR(20),
LIVRASON_CHARGE FLOAT,
LIV_DES VARCHAR(70),
CONSTRAINT FK_ID_CLIENT2 FOREIGN KEY (ID_CLIENT) REFERENCES CLIENT(ID_CLIENT)
)
---------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE USERS
(
    NOM VARCHAR(20),
    PRENOM VARCHAR(30),
    EMAIL VARCHAR(30),
    USERNAME VARCHAR(50),
    PSW VARCHAR(50),
    USERTYPE VARCHAR(50) CHECK (USERTYPE IN ('admin','modérateur','utilisateur')),
    CONSTRAINT PK_USERS PRIMARY KEY(USERNAME)
)
---------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE GRADATION
(
    ID_GRADE INT IDENTITY(1,1),
    NOM_GRADE VARCHAR(20),
    CONSTRAINT PK_ID_GRADE PRIMARY KEY(ID_GRADE)
)


/*
CREATE OR ALTER TRIGGER AUTOQUANTITY
ON DETAIL_CMD
FOR INSERT
AS BEGIN
DECLARE @QTECMD INT
SELECT @QTECMD = INSERTED.QUANTITE from INSERTED
IF EXISTS(SELECT * FROM INSERTED I,PRODUIT P WHERE I.ID_PRODUIT = P.ID_PRODUIT AND I.QUANTITE > P.QTE_STOCK)
BEGIN
RAISERROR('La quantité de Commande n''est pas suffisante!!',16,1)
ROLLBACK
END
ELSE
BEGIN
UPDATE PRODUIT SET QTE_STOCK = QTE_STOCK - @QTECMD
WHERE ID_PRODUIT = (SELECT INSERTED.ID_PRODUIT FROM INSERTED,PRODUIT WHERE INSERTED.ID_PRODUIT = PRODUIT.ID_PRODUIT)
END
END
*/