362 lines
8.7 KiB
Transact-SQL
362 lines
8.7 KiB
Transact-SQL
use [GestionCommerceInfo]
|
|
-- ================================================
|
|
-- Template generated from Template Explorer using:
|
|
-- Create Procedure (New Menu).SQL
|
|
--
|
|
-- Use the Specify Values for Template Parameters
|
|
-- command (Ctrl-Shift-M) to fill in the parameter
|
|
-- values below.
|
|
--
|
|
-- This block of comments will not be included in
|
|
-- the definition of the procedure.
|
|
-- ================================================
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
-- =============================================
|
|
-- Author: VAN DAMME Adrien
|
|
-- Create date:
|
|
-- Description:
|
|
-- =============================================
|
|
|
|
/*==========================================================================================================
|
|
*
|
|
* GESTION UTILISATEUR
|
|
*
|
|
*==========================================================================================================*/
|
|
IF OBJECT_ID ( 'AjoutUtilisateur', 'P' ) IS NOT NULL
|
|
DROP PROCEDURE AjoutUtilisateur;
|
|
GO
|
|
CREATE PROCEDURE AjoutUtilisateur
|
|
-- Add the parameters for the stored procedure here
|
|
@ID int OUTPUT,
|
|
@Nom varchar(50),
|
|
@Prenom varchar(50),
|
|
@Adresse varchar(50),
|
|
@NCompte varchar(50),
|
|
@DateDeNaisance datetime,
|
|
@email varchar(50),
|
|
@type int
|
|
AS
|
|
BEGIN
|
|
IF(@type IS NULL OR @email IS NULL)
|
|
RAISERROR('Type de compte et email requis !', 16,1);
|
|
ELSE
|
|
INSERT INTO Utilisateur(Nom,Prenom,Adresse,NCompte,DateDeNaisance,type,Actif,email)
|
|
VALUES(@Nom,@Prenom,@Adresse,@NCompte,@DateDeNaisance,@type,1,@email);
|
|
SET @ID=@@IDENTITY;
|
|
RETURN;
|
|
END
|
|
GO
|
|
IF OBJECT_ID ( 'ModifUtilisateur', 'P' ) IS NOT NULL
|
|
DROP PROCEDURE ModifUtilisateur;
|
|
GO
|
|
CREATE PROCEDURE ModifUtilisateur
|
|
-- Add the parameters for the stored procedure here
|
|
@ID int,
|
|
@Nom varchar(50),
|
|
@Prenom varchar(50),
|
|
@Adresse varchar(50),
|
|
@NCompte varchar(50),
|
|
@DateDeNaisance datetime,
|
|
@email varchar(50),
|
|
@type int,
|
|
@actif bit
|
|
AS
|
|
BEGIN
|
|
IF(@ID IS NULL)
|
|
RAISERROR('Identifiant requis !', 16,1);
|
|
ELSE IF(@type IS NULL OR @email IS NULL OR @actif IS NULL)
|
|
RAISERROR('Type de compte, email et actif requis !', 16,1);
|
|
ELSE
|
|
UPDATE Utilisateur SET Nom=@Nom,Prenom=@Prenom,Adresse=@Adresse,NCompte=@NCompte,DateDeNaisance=@DateDeNaisance,type=@type,Actif=@actif,email=@email
|
|
WHERE ID=@ID;
|
|
RETURN;
|
|
END
|
|
GO
|
|
IF OBJECT_ID ( 'SuprimerUtilisateur', 'P' ) IS NOT NULL
|
|
DROP PROCEDURE SuprimerUtilisateur;
|
|
GO
|
|
CREATE PROCEDURE SuprimerUtilisateur
|
|
-- Add the parameters for the stored procedure here
|
|
@ID int,
|
|
@definitivement bit
|
|
AS
|
|
BEGIN
|
|
IF(@ID IS NULL)
|
|
RAISERROR('Identifiant requis !', 16,1)
|
|
ELSE IF(@definitivement IS NULL OR @definitivement = 0)
|
|
BEGIN
|
|
UPDATE Utilisateur SET Actif=0 WHERE ID=@ID;
|
|
RETURN;
|
|
END
|
|
ELSE
|
|
DELETE FROM Utilisateur WHERE ID=@ID;
|
|
RETURN;
|
|
END
|
|
GO
|
|
IF OBJECT_ID ( 'ListUtilisateurs', 'P' ) IS NOT NULL
|
|
DROP PROCEDURE ListUtilisateurs;
|
|
GO
|
|
CREATE PROCEDURE ListUtilisateurs
|
|
-- Add the parameters for the stored procedure here
|
|
@ID int,
|
|
@TRI varchar(15)
|
|
AS
|
|
BEGIN
|
|
IF(@ID IS NULL OR @ID<=0)
|
|
BEGIN
|
|
IF(@TRI='NOM')
|
|
SELECT * FROM Utilisateur ORDER BY Nom
|
|
ELSE IF(@TRI='PRENOM')
|
|
SELECT * FROM Utilisateur ORDER BY Prenom
|
|
ELSE
|
|
SELECT * FROM Utilisateur
|
|
END
|
|
ELSE
|
|
SELECT * FROM Utilisateur WHERE ID=@ID;
|
|
END
|
|
GO
|
|
/*==========================================================================================================
|
|
*
|
|
* GESTION ARTICLE
|
|
*
|
|
*==========================================================================================================*/
|
|
IF OBJECT_ID ( 'AjoutArticle', 'P' ) IS NOT NULL
|
|
DROP PROCEDURE AjoutArticle;
|
|
GO
|
|
CREATE PROCEDURE AjoutArticle
|
|
-- Add the parameters for the stored procedure here
|
|
@ID int OUTPUT,
|
|
@Designation varchar(50),
|
|
@PrixHTVA money,
|
|
@Stock int,
|
|
@Visible bit,
|
|
@Actif bit
|
|
AS
|
|
BEGIN
|
|
IF(@Designation IS NULL OR @PrixHTVA IS NULL OR @Stock IS NULL OR @Visible IS NULL OR @Actif IS NULL)
|
|
RAISERROR('Tous les parametre sont requis !', 16,1)
|
|
ELSE
|
|
INSERT INTO Article(Designation,PrixHTVA,Stock,Visible,Actif)
|
|
VALUES(@Designation,@PrixHTVA,@Stock,@Visible,@Actif)
|
|
SET @ID=@@IDENTITY
|
|
RETURN
|
|
END
|
|
GO
|
|
IF OBJECT_ID ( 'ModifArticle', 'P' ) IS NOT NULL
|
|
DROP PROCEDURE ModifArticle;
|
|
GO
|
|
CREATE PROCEDURE ModifArticle
|
|
-- Add the parameters for the stored procedure here
|
|
@ID int,
|
|
@Designation varchar(50),
|
|
@PrixHTVA money,
|
|
@Stock int,
|
|
@Visible bit,
|
|
@Actif bit
|
|
AS
|
|
BEGIN
|
|
IF(@ID IS NULL)
|
|
RAISERROR('Identifiant requis !', 16,1)
|
|
ELSE IF(@Designation IS NULL OR @PrixHTVA IS NULL OR @Stock IS NULL OR @Visible IS NULL OR @Actif IS NULL)
|
|
RAISERROR('Tous les parametre sont requis !', 16,1)
|
|
ELSE
|
|
UPDATE Article SET Designation=@Designation,PrixHTVA=@PrixHTVA,Stock=@Stock,Visible=@Visible,Actif=@Actif WHERE ID=@ID
|
|
RETURN
|
|
END
|
|
GO
|
|
IF OBJECT_ID ( 'SuprimerArticle', 'P' ) IS NOT NULL
|
|
DROP PROCEDURE SuprimerArticle;
|
|
GO
|
|
CREATE PROCEDURE SuprimerArticle
|
|
-- Add the parameters for the stored procedure here
|
|
@ID int,
|
|
@definitivement bit
|
|
AS
|
|
BEGIN
|
|
IF(@ID IS NULL)
|
|
RAISERROR('Identifiant requis !', 16,1)
|
|
ELSE IF(@definitivement IS NULL OR @definitivement = 0)
|
|
BEGIN
|
|
UPDATE Article SET Actif=0 WHERE ID=@ID;
|
|
RETURN;
|
|
END
|
|
ELSE
|
|
DELETE FROM Article WHERE ID=@ID;
|
|
RETURN;
|
|
END
|
|
GO
|
|
IF OBJECT_ID ( 'ListArticle', 'P' ) IS NOT NULL
|
|
DROP PROCEDURE ListArticle;
|
|
GO
|
|
CREATE PROCEDURE ListArticle
|
|
-- Add the parameters for the stored procedure here
|
|
@ID int
|
|
AS
|
|
BEGIN
|
|
IF(@ID IS NULL OR @ID<=0)
|
|
BEGIN
|
|
SELECT * FROM Article
|
|
END
|
|
ELSE
|
|
SELECT * FROM Article WHERE ID=@ID;
|
|
END
|
|
GO
|
|
IF OBJECT_ID ( 'StockUnder', 'P' ) IS NOT NULL
|
|
DROP PROCEDURE StockUnder;
|
|
GO
|
|
CREATE PROCEDURE StockUnder
|
|
-- Add the parameters for the stored procedure here
|
|
@Stock int
|
|
AS
|
|
BEGIN
|
|
IF(@Stock IS NULL OR @Stock<0)
|
|
BEGIN
|
|
RAISERROR('Stock non nul ou non négatif !', 16,1)
|
|
END
|
|
ELSE
|
|
SELECT * FROM Article WHERE Stock<=@Stock;
|
|
END
|
|
GO
|
|
|
|
/*==========================================================================================================
|
|
*
|
|
* GESTION PANIER
|
|
*
|
|
*==========================================================================================================*/
|
|
|
|
IF OBJECT_ID ( 'CreerPanier', 'P' ) IS NOT NULL
|
|
DROP PROCEDURE CreerPanier;
|
|
GO
|
|
CREATE PROCEDURE CreerPanier
|
|
-- Add the parameters for the stored procedure here
|
|
@ID int OUTPUT,
|
|
@UserID int,
|
|
@Nom varchar(50),
|
|
@type int
|
|
AS
|
|
BEGIN
|
|
IF(@type IS NULL OR @UserID IS NULL)
|
|
RAISERROR('Type de compte et UserId requis !', 16,1);
|
|
ELSE
|
|
INSERT INTO Panier(UserId, Nom, Status, Type)
|
|
VALUES(@UserID, @Nom, 0, @type);
|
|
SET @ID=@@IDENTITY;
|
|
RETURN;
|
|
END
|
|
GO
|
|
IF OBJECT_ID ( 'ListArticlePanier', 'P' ) IS NOT NULL
|
|
DROP PROCEDURE ListArticlePanier;
|
|
GO
|
|
CREATE PROCEDURE ListArticlePanier
|
|
-- Add the parameters for the stored procedure here
|
|
@ID int
|
|
AS
|
|
BEGIN
|
|
IF(@ID is NULL)
|
|
RAISERROR('ID Requis !', 16,1);
|
|
ELSE
|
|
BEGIN
|
|
SELECT * FROM LSTArticle AS art JOIN Article ON ( art.ArticleID = Article.ID) WHERE art.PanierID = @ID;
|
|
END
|
|
END
|
|
GO
|
|
|
|
IF OBJECT_ID ( 'ListPanierUser', 'P' ) IS NOT NULL
|
|
DROP PROCEDURE ListPanierUser;
|
|
GO
|
|
CREATE PROCEDURE ListPanierUser
|
|
-- Add the parameters for the stored procedure here
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
IF(@UserID IS NULL)
|
|
SELECT * FROM Panier ORDER BY ID DESC;
|
|
ELSE
|
|
BEGIN
|
|
SELECT * FROM Panier WHERE UserID = @UserID ORDER BY ID DESC;
|
|
END
|
|
END
|
|
GO
|
|
|
|
|
|
IF OBJECT_ID ( 'LirePanier', 'P' ) IS NOT NULL
|
|
DROP PROCEDURE LirePanier;
|
|
GO
|
|
CREATE PROCEDURE LirePanier
|
|
-- Add the parameters for the stored procedure here
|
|
@ID int
|
|
AS
|
|
BEGIN
|
|
IF(@ID IS NULL)
|
|
RAISERROR('ID Requis !', 16,1);
|
|
ELSE
|
|
BEGIN
|
|
SELECT * FROM Panier WHERE ID = @ID;
|
|
END
|
|
END
|
|
GO
|
|
|
|
|
|
IF OBJECT_ID ( 'AjoutArticleDansPanier', 'P' ) IS NOT NULL
|
|
DROP PROCEDURE AjoutArticleDansPanier;
|
|
GO
|
|
CREATE PROCEDURE AjoutArticleDansPanier
|
|
-- Add the parameters for the stored procedure here
|
|
@ID int OUTPUT,
|
|
@IDArt int,
|
|
@IDPanier int,
|
|
@Quantite int,
|
|
@PrixHTVA money
|
|
AS
|
|
BEGIN
|
|
IF(@IDArt IS NULL OR @IDPanier IS NULL OR @Quantite IS NULL)
|
|
RAISERROR('Tout les parametre sont requis !', 16,1);
|
|
ELSE
|
|
INSERT INTO LSTArticle(PanierID, ArticleID, Quantite, PrixHTVA, TVA)
|
|
VALUES(@IDPanier, @IDArt, @Quantite, @PrixHTVA, 0);
|
|
SET @ID=@@IDENTITY;
|
|
RETURN;
|
|
END
|
|
GO
|
|
|
|
|
|
IF OBJECT_ID ( 'SuprimerArticlePanier', 'P' ) IS NOT NULL
|
|
DROP PROCEDURE SuprimerArticlePanier;
|
|
GO
|
|
CREATE PROCEDURE SuprimerArticlePanier
|
|
-- Add the parameters for the stored procedure here
|
|
@ID int
|
|
AS
|
|
BEGIN
|
|
IF(@ID is NULL)
|
|
RAISERROR('Tout les parametre sont requis !', 16,1);
|
|
ELSE
|
|
DELETE FROM LSTArticle WHERE ID=@ID;
|
|
END
|
|
GO
|
|
|
|
IF OBJECT_ID ( 'ModifPanier', 'P' ) IS NOT NULL
|
|
DROP PROCEDURE ModifPanier;
|
|
GO
|
|
CREATE PROCEDURE ModifPanier
|
|
-- Add the parameters for the stored procedure here
|
|
@ID int,
|
|
@userID int,
|
|
@nom varchar(50),
|
|
@Status int,
|
|
@type bit
|
|
AS
|
|
BEGIN
|
|
IF(@ID IS NULL)
|
|
RAISERROR('Identifiant requis !', 16,1)
|
|
ELSE IF(@userID IS NULL OR @nom IS NULL OR @Status IS NULL OR @type IS NULL)
|
|
RAISERROR('Tous les parametre sont requis !', 16,1)
|
|
ELSE
|
|
UPDATE Panier SET UserId=@UserID,Nom=@nom,Status=@Status,Type=@type WHERE ID=@ID
|
|
RETURN
|
|
END
|
|
GO
|