USE [master]
GO
/****** Object:  Database [GestionCommerceInfo]    Script Date: 23-05-18 17:13:54 ******/
CREATE DATABASE [GestionCommerceInfo]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'GestionCommerceInfo', FILENAME = N'C:\Users\Adrien\GestionCommerceInfo.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'GestionCommerceInfo_log', FILENAME = N'C:\Users\Adrien\GestionCommerceInfo_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [GestionCommerceInfo] SET COMPATIBILITY_LEVEL = 130
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [GestionCommerceInfo].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [GestionCommerceInfo] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET ARITHABORT OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [GestionCommerceInfo] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [GestionCommerceInfo] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET  DISABLE_BROKER 
GO
ALTER DATABASE [GestionCommerceInfo] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [GestionCommerceInfo] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [GestionCommerceInfo] SET  MULTI_USER 
GO
ALTER DATABASE [GestionCommerceInfo] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [GestionCommerceInfo] SET DB_CHAINING OFF 
GO
ALTER DATABASE [GestionCommerceInfo] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [GestionCommerceInfo] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [GestionCommerceInfo] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [GestionCommerceInfo] SET QUERY_STORE = OFF
GO
USE [GestionCommerceInfo]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
GO
USE [GestionCommerceInfo]
GO
/****** Object:  Table [dbo].[Article]    Script Date: 23-05-18 17:13:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Article](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Designation] [varchar](50) NOT NULL,
	[Stock] [int] NOT NULL,
	[Visible] [bit] NOT NULL,
	[Actif] [bit] NOT NULL,
	[PrixHTVA] [money] NULL,
 CONSTRAINT [PK_Article] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[LSTArticle]    Script Date: 23-05-18 17:13:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LSTArticle](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[PanierID] [int] NOT NULL,
	[ArticleID] [int] NOT NULL,
	[Quantite] [int] NOT NULL,
	[PrixHTVA] [money] NULL,
	[TVA] [int] NULL,
 CONSTRAINT [PK_Commande] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Panier]    Script Date: 23-05-18 17:13:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Panier](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UserId] [int] NOT NULL,
	[Nom] [varchar](50) NULL,
	[Status] [int] NULL,
	[DateAchat] [datetime] NULL,
	[Type] [int] NOT NULL,
 CONSTRAINT [PK_Panier] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Utilisateur]    Script Date: 23-05-18 17:13:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Utilisateur](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Nom] [varchar](50) NULL,
	[Prenom] [varchar](50) NULL,
	[Adresse] [varchar](50) NULL,
	[NCompte] [varchar](50) NULL,
	[DateDeNaisance] [datetime] NULL,
	[type] [int] NOT NULL,
	[Actif] [bit] NOT NULL,
	[email] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Utilisateur] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LSTArticle]  WITH CHECK ADD  CONSTRAINT [FK_LSTArticle_Article] FOREIGN KEY([ArticleID])
REFERENCES [dbo].[Article] ([ID])
GO
ALTER TABLE [dbo].[LSTArticle] CHECK CONSTRAINT [FK_LSTArticle_Article]
GO
ALTER TABLE [dbo].[LSTArticle]  WITH CHECK ADD  CONSTRAINT [RELPANIER_Commande_Panier] FOREIGN KEY([PanierID])
REFERENCES [dbo].[Panier] ([ID])
GO
ALTER TABLE [dbo].[LSTArticle] CHECK CONSTRAINT [RELPANIER_Commande_Panier]
GO
ALTER TABLE [dbo].[Panier]  WITH CHECK ADD  CONSTRAINT [RELUSERID_Panier_Utilisateur] FOREIGN KEY([UserId])
REFERENCES [dbo].[Utilisateur] ([ID])
GO
ALTER TABLE [dbo].[Panier] CHECK CONSTRAINT [RELUSERID_Panier_Utilisateur]
GO
/****** Object:  StoredProcedure [dbo].[AjoutArticle]    Script Date: 23-05-18 17:13:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AjoutArticle]
	-- Add the parameters for the stored procedure here
	@ID int OUTPUT,
	@Designation text,
	@PrixHTVA float,
	@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
/****** Object:  StoredProcedure [dbo].[AjoutArticleDansPanier]    Script Date: 23-05-18 17:13:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AjoutArticleDansPanier]
	-- Add the parameters for the stored procedure here
  @IDArt int,
  @IDPanier int,
  @Quantite int
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)
					VALUES(@IDPanier, @IDArt, @Quantite);
END
GO
/****** Object:  StoredProcedure [dbo].[AjoutUtilisateur]    Script Date: 23-05-18 17:13:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AjoutUtilisateur]
	-- Add the parameters for the stored procedure here
	@ID int OUTPUT,
	@Nom varchar(50),
	@Prenom varchar(50),
	@Adresse text,
	@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
/****** Object:  StoredProcedure [dbo].[CreerPanier]    Script Date: 23-05-18 17:13:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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
/****** Object:  StoredProcedure [dbo].[ListArticle]    Script Date: 23-05-18 17:13:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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
/****** Object:  StoredProcedure [dbo].[ListArticlePanier]    Script Date: 23-05-18 17:13:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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
/****** Object:  StoredProcedure [dbo].[ListPanierUser]    Script Date: 23-05-18 17:13:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ListPanierUser]
	-- 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 UserID = @ID;
END
END
GO
/****** Object:  StoredProcedure [dbo].[ListUtilisateurs]    Script Date: 23-05-18 17:13:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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
/****** Object:  StoredProcedure [dbo].[ModifArticle]    Script Date: 23-05-18 17:13:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ModifArticle]
	-- Add the parameters for the stored procedure here
	@ID int,
	@Designation text,
	@PrixHTVA float,
	@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
/****** Object:  StoredProcedure [dbo].[ModifUtilisateur]    Script Date: 23-05-18 17:13:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ModifUtilisateur]
	-- Add the parameters for the stored procedure here
	@ID int,
	@Nom varchar(50),
	@Prenom varchar(50),
	@Adresse text,
	@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
/****** Object:  StoredProcedure [dbo].[SuprimerArticle]    Script Date: 23-05-18 17:13:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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
/****** Object:  StoredProcedure [dbo].[SuprimerArticlePanier]    Script Date: 23-05-18 17:13:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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
/****** Object:  StoredProcedure [dbo].[SuprimerUtilisateur]    Script Date: 23-05-18 17:13:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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
USE [master]
GO
ALTER DATABASE [GestionCommerceInfo] SET  READ_WRITE 
GO