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