USE [master]
GO
/****** Object:  Database [dbInventari]    Script Date: 17/12/2018 7:44:20 ******/
CREATE DATABASE [dbInventari]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'dbInventari', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\dbInventari.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'dbInventari_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\dbInventari_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [dbInventari] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [dbInventari].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [dbInventari] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [dbInventari] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [dbInventari] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [dbInventari] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [dbInventari] SET ARITHABORT OFF 
GO
ALTER DATABASE [dbInventari] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [dbInventari] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [dbInventari] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [dbInventari] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [dbInventari] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [dbInventari] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [dbInventari] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [dbInventari] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [dbInventari] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [dbInventari] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [dbInventari] SET  DISABLE_BROKER 
GO
ALTER DATABASE [dbInventari] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [dbInventari] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [dbInventari] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [dbInventari] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [dbInventari] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [dbInventari] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [dbInventari] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [dbInventari] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [dbInventari] SET  MULTI_USER 
GO
ALTER DATABASE [dbInventari] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [dbInventari] SET DB_CHAINING OFF 
GO
ALTER DATABASE [dbInventari] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [dbInventari] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
USE [dbInventari]
GO
/****** Object:  UserDefinedDataType [dbo].[EstatRef]    Script Date: 17/12/2018 7:44:20 ******/
CREATE TYPE [dbo].[EstatRef] FROM [varchar](7) NULL
GO
/****** Object:  UserDefinedDataType [dbo].[EstatUsuari]    Script Date: 17/12/2018 7:44:20 ******/
CREATE TYPE [dbo].[EstatUsuari] FROM [varchar](10) NOT NULL
GO
/****** Object:  UserDefinedDataType [dbo].[TipPort]    Script Date: 17/12/2018 7:44:20 ******/
CREATE TYPE [dbo].[TipPort] FROM [varchar](5) NOT NULL
GO
/****** Object:  StoredProcedure [dbo].[a_boqswitch]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[a_boqswitch]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idRef varchar(12),
	@modul char(1),
	@port int,
	@valid bit,
	@tipPort TipPort,
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idRef='' 
		SET @sortida='El identificador de referencia no pot ser blanc';
	ELSE IF NOT EXISTS (SELECT idRef FROM Referencia WHERE idRef=@idRef)
		SET @sortida='La referencia '+@idRef+' no existeix';
	ELSE IF EXISTS (SELECT * FROM BoqSwitch WHERE idRef=@idRef AND modul=@modul and port=@port)
		SET @sortida='El switch '+@idRef+'-'+@modul+'-'+str(@port,2,0)+' ja existeix';
	ELSE
		BEGIN
		-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
		BEGIN TRY
			INSERT INTO BoqSwitch
				VALUES (@idRef,@modul, @port, @valid, @tipPort);
			--Un cop insertat
			SET @sortida='Registre insertat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
		--registrar auditoria
		DECLARE @resultat varchar(250);
		SET @sortida=@idRef+'/'+@modul+'/'+str(@port,2,0)+'-'+@sortida;
		exec ins_auditoria @idUsuGest,@control,'a_boqswitch',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[a_edifici]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[a_edifici]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idEdifici varchar(8),
	@nom varchar(30),
	@adreca varchar(50),
	@poblacio varchar(30),
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idEdifici=''
		SET @sortida='El identificador d"edifici no pot ser blanc';
	ELSE IF @idEdifici=(SELECT @idEdifici FROM Edifici WHERE idEdifici=@idEdifici)
		SET @sortida='L"Edifici '+@idEdifici+' ja existeix';
	ELSE
		BEGIN
		-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
		BEGIN TRY
			INSERT INTO Edifici
				VALUES (@idEdifici, @nom,@adreca,@poblacio);	
			SET @sortida='Registre insertat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
		--registrar auditoria
		DECLARE @resultat varchar(250);
		SET @sortida=@idEdifici+'-'+@sortida;
		exec ins_auditoria @idUsuGest,@control,'a_edifici',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[a_entmat]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[a_entmat]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@dateEntMat date,
	@numEntMat varchar(15),
	@numFact varchar(15),
	@dataFact date,
	@numReg varchar(15),
	@dataReg date,
	@idProveidor varchar(8),
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF NOT EXISTS (SELECT idProveidor FROM Proveidor WHERE idProveidor=@idProveidor)
		SET @sortida='El proveïdor '+@idProveidor+' no existeix';
	ELSE
		BEGIN
		-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
		BEGIN TRY
			INSERT INTO EntMaterial
				VALUES (@dateEntMat,@numEntMat,@numFact,@dataFact,@numReg,@dataReg,@idProveidor);
			--Un cop insertat
			SET @sortida='Registre insertat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
		--registrar auditoria
		DECLARE @resultat varchar(250);
		SET @sortida=@idProveidor+'/'+@numEntMat+'-'+@sortida;
		exec ins_auditoria @idUsuGest,@control,'a_entmat',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[a_famreferencia]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[a_famreferencia]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idFamRef varchar(15),
	@descFamRef varchar(50),
	@etiqueta varchar(12),
	@esOrdinador bit,
	@esSwitch bit,
	@esTelefon bit,
	@esPeriferic bit,
	@esFungible bit,
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idFamRef=''
		SET @sortida='El identificador de familia no pot ser blanc';
	--Comprovem el format de l'etiqueta
	ELSE IF (SELECT dbo.valida_etiqueta(@etiqueta))=1
		SET @sortida='El format de l"etiqueta ha de ser "XX000/AA"';
	ELSE IF @idFamRef=(SELECT @idFamRef FROM FamReferencia WHERE idFamRef=@idFamRef)
		SET @sortida='La familia '+@idFamRef+' ja existeix';
	ELSE
		BEGIN
		-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
		BEGIN TRY
			INSERT INTO FamReferencia 
				VALUES (@idFamRef, @descFamRef,@etiqueta,@esOrdinador,@esSwitch,@esTelefon,@esPeriferic,@esFungible);	
			SET @sortida='Registre insertat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
		--registrar auditoria
		DECLARE @resultat varchar(250);
		SET @sortida=@idFamRef+'-'+@sortida;
		exec ins_auditoria @idUsuGest,@control,'a_famreferencia',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[a_linentmat]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[a_linentmat]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@comLinMat int,
	@idRef varchar(12),
	@qtt int,
	@preuSiva decimal(6,2),
	@tipGar varchar(15),
	@mesosGar int,
	@dataGar date,
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF NOT EXISTS (SELECT idRef FROM Referencia WHERE idRef=@idRef)
		SET @sortida='La referencia '+@idRef+' no existeix';
	ELSE IF NOT EXISTS (SELECT idEntMat FROM EntMaterial WHERE idEntMat=@comLinMat)
		SET @sortida='L"entrada '+str(@comLinMat,10)+' no existeix';
	ELSE
		BEGIN
		-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
		BEGIN TRY
			INSERT INTO LinEntMat
				VALUES (@comLinMat,@idRef,@qtt,@preuSiva,@tipGar,@mesosGar,@dataGar);
			--Un cop insertat
			SET @sortida='Registre insertat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
		--registrar auditoria
		DECLARE @resultat varchar(250);
		SET @sortida=str(@comLinMat,10)+'/'+@idRef+'-'+@sortida;
		exec ins_auditoria @idUsuGest,@control,'a_linentmat',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[a_ordinador]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[a_ordinador]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idRef varchar(12),
	@processador varchar(20),
	@gbRam decimal(6,2),
	@gbDisc decimal(6,2),
	@tDdur varchar(8),
	@wol bit,
	@periferics varchar(50),
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idRef='' 
		SET @sortida='El identificador de referencia no pot ser blanc';
	ELSE IF NOT EXISTS (SELECT idRef FROM Referencia WHERE idRef=@idRef)
		SET @sortida='La referencia '+@idRef+' no existeix';
	ELSE IF EXISTS (SELECT * FROM Ordinador WHERE idRef=@idRef)
		SET @sortida='L"Ordinador'+@idRef+' ja existeix';
	ELSE
		BEGIN
		-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
		BEGIN TRY
			INSERT INTO Ordinador
				VALUES (@idRef,@processador, @gbRam, @gbDisc, @tDdur, @wol, @periferics);
			--Un cop insertat
			SET @sortida='Registre insertat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
		--registrar auditoria
		DECLARE @resultat varchar(250);
		SET @sortida=@idRef+'-'+@sortida;
		exec ins_auditoria @idUsuGest,@control,'a_ordinador',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[a_planta]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[a_planta]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idEdifici varchar(8),
	@idPlanta varchar(8),
	@planta int,
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idEdifici='' or @idPlanta=''
		SET @sortida='El identificador d"edifici i planta no poden ser blanc';
	ELSE IF NOT EXISTS (SELECT * FROM Edifici WHERE idEdifici=@idEdifici)
		SET @sortida='L"Edifici '+@idEdifici+' no existeix';
	ELSE IF EXISTS (SELECT * FROM Planta WHERE idEdifici=@idEdifici AND idPlanta=@idPlanta)
		SET @sortida='L"Edifici - Planta'+@idEdifici+' - '+@idPlanta+' ja existeix';
	ELSE
		BEGIN
		-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
		BEGIN TRY
			INSERT INTO Planta
				VALUES (@idEdifici, @idPlanta, @planta);
			SET @sortida='Registre insertat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
		--registrar auditoria
		DECLARE @resultat varchar(250);
		SET @sortida=@idEdifici+'/'+@idPlanta+'-'+@sortida;
		exec ins_auditoria @idUsuGest,@control,'a_planta',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[a_referencia]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[a_referencia]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idRef varchar(12),
	@nSerie varchar(40),
	@idFamRef varchar(15),
	@descRef varchar(50),
	@marca varchar(20),
	@model varchar(30),
	@pNum varchar(20),
	@estat EstatRef,
	@mac varchar(12),
	@extensio varchar(9),
	@observacions varchar(100),
	@ubiDriv varchar(10),
	@guardat varchar(10),
	@ip varchar(15),
	@dataCompra date,
	@nomProv varchar(50),
	@dataGar date,
	@qtt int,
	@pulzPant decimal(5,2),
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idRef='' or @idFamRef=''
		SET @sortida='El identificador de referencia i familia no poden ser blanc';
	ELSE IF NOT EXISTS (SELECT * FROM FamReferencia WHERE idFamRef=@idFamRef)
		SET @sortida='La familia '+@idFamRef+' no existeix';
	ELSE IF @ubiDriv<>'' AND @ubiDriv<>NULL AND NOT EXISTS (SELECT * FROM Ubicacio WHERE idUbicacio=@ubiDriv)
		SET @sortida='La ubicació del driver '+@ubiDriv+' no existeix';
	ELSE IF @guardat<>'' AND @guardat<>NULL AND NOT EXISTS (SELECT * FROM Ubicacio WHERE idUbicacio=@guardat)
		SET @sortida='La ubicació de la referència '+@guardat+' no existeix';
	ELSE IF EXISTS (SELECT * FROM Referencia WHERE idRef=@idRef)
		SET @sortida='La referencia'+@idRef+' ja existeix';
	ELSE
		BEGIN
		-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
		BEGIN TRY
			INSERT INTO Referencia
				VALUES (@idRef,@nSerie,@idFamRef,@descRef,@marca,@model,@pNum,default,@mac,@extensio,@observacions,
				@ubiDriv,@guardat,@ip,@dataCompra,@nomProv,@dataGar,@qtt,@pulzPant);
			--Un cop insertat
			SET @sortida='Registre insertat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
		--registrar auditoria
		DECLARE @resultat varchar(250);
		SET @sortida=@idRef+'-'+@sortida;
		exec ins_auditoria @idUsuGest,@control,'a_referencia',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[a_usuari]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[a_usuari]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idUsuari varchar(15),
	@nom varchar(30),
	@cognoms varchar(50),
	@dni varchar(9),
	@dataAlta date,
	@dataBaixa date,
	@estatUsu EstatUsuari,
	@carrec varchar(30),
	@carrec2 varchar(30),
	@bustia varchar(30),
	@bustia2 varchar(30),
	@tipUsu varchar(8),
	@deptAssig varchar(8),
	@deptAssig2 varchar(8),	
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idUsuari=NULL or @idUsuari=''
		SET @sortida='L"usuari no pot ser blanc';
	ELSE IF ISNULL(@dataAlta,'')=''
		SET @sortida='La data d"alta no pot ser blanc';	
	ELSE IF (@dataBaixa<>NULL or @dataBaixa='') and @dataBaixa<@dataAlta
		SET @sortida='La data de baixa no pot ser inferior a la data d"alta';	
	ELSE IF NOT EXISTS (SELECT idtipUsu FROM TipusUsuari WHERE idTipUsu=@tipUsu)
		SET @sortida='El tipus d"usuari '+@tipUsu+' no existeix';
	ELSE IF ISNULL(@bustia,'')<>'' and NOT EXISTS (SELECT idBustia FROM Bustia WHERE idBustia=@bustia)
		SET @sortida='La bustia '+@bustia+' no existeix';
	ELSE IF ISNULL(@bustia2,'')<>'' and NOT EXISTS (SELECT idBustia FROM Bustia WHERE idBustia=@bustia2)
		SET @sortida='La bustia2 '+@bustia2+' no existeix';
	ELSE IF ISNULL(@deptAssig,'')<>'' and NOT EXISTS (SELECT idDepartament FROM Departament WHERE idDepartament=@deptAssig)
		SET @sortida='El departament '+@deptAssig+' no existeix';
	ELSE IF ISNULL(@deptAssig2,'')<>'' and NOT EXISTS (SELECT idDepartament FROM Departament WHERE idDepartament=@deptAssig2)
		SET @sortida='El departament2 '+@deptAssig2+' no existeix';
	ELSE IF EXISTS (SELECT idUsuari FROM Usuari WHERE idUsuari=@idUsuari)
		SET @sortida='L"usuari '+@idUsuari+' ja existeix';
	ELSE
		BEGIN
		-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
		BEGIN TRY
			INSERT INTO Usuari
				VALUES (@idUsuari, @nom, @cognoms,@dni, @dataAlta, @dataBaixa, @estatUsu, @carrec, @carrec2, 
				@bustia, @bustia2, @tipUsu, @deptAssig, @deptAssig2);
			--Un cop insertat
			SET @sortida='Registre insertat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
		--registrar auditoria
		DECLARE @resultat varchar(250);
		SET @sortida=@idUsuari+'-'+@sortida;
		exec ins_auditoria @idUsuGest,@control,'a_usuari',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[a_usugestio]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[a_usugestio]
	-- Paràmetres d'entrada
	@idUsu varchar(15),
	@nom varchar(50),
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	IF @idUsu=''
		SET @sortida='El identificador usuari no pot ser blanc';
	ELSE	
		BEGIN
		IF @idUsu=(SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsu)
			SET @sortida='L"usuari '+@idUsu+' ja existeix';
		ELSE
			-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
			BEGIN
			BEGIN TRY
				INSERT INTO UsuGestio (idUsuGest, nom)
					VALUES (@idUsu, @nom);	
				SET @sortida='Registre insertat correctament';
			END TRY
			BEGIN CATCH
				SET @sortida=(SELECT ERROR_MESSAGE());				
				RAISERROR (@sortida,-1,-1);
			END CATCH
			END
		END
END

GO
/****** Object:  StoredProcedure [dbo].[b_edifici]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[b_edifici]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idEdifici varchar(8),
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idEdifici=''
		SET @sortida='El identificador d"edifici no pot ser blanc';
	ELSE IF NOT EXISTS (SELECT @idEdifici FROM Edifici WHERE idEdifici=@idEdifici)
		SET @sortida='L"Edifici '+@idEdifici+' no existeix';
	ELSE
		-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
		BEGIN
		BEGIN TRY
			DELETE FROM Edifici WHERE idEdifici=@idEdifici;
			SET @sortida='Registre eliminat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
	--registrar auditoria
	DECLARE @resultat varchar(250);
	SET @sortida=@idEdifici+'-'+@sortida;
	exec ins_auditoria @idUsuGest,@control,'b_edifici',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[b_entmat]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[b_entmat]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idEntMat int,
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idEntMat='' OR @idEntMat=NULL 
		SET @sortida='El número d"entrada no poden ser blanc';
	ELSE IF NOT EXISTS (SELECT * FROM EntMaterial WHERE idEntMat=@idEntMat)
		SET @sortida='L"entrada '+str(@idEntMat,10)+' no existeix';
	ELSE
		BEGIN
		BEGIN TRY
			DELETE FROM EntMaterial
			WHERE idEntMat=@idEntMat;
			SET @sortida='Registre eliminat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
	--registrar auditoria
	DECLARE @resultat varchar(250);
		SET @sortida=str(@idEntMat,10)+'-'+@sortida;
		exec ins_auditoria @idUsuGest,@control,'b_entmat',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[b_famreferencia]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[b_famreferencia]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idFamRef varchar(15),
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idFamRef=''
		SET @sortida='El identificador de familia no pot ser blanc';
	ELSE IF NOT EXISTS (SELECT @idFamRef FROM FamReferencia WHERE idFamRef=@idFamRef)
		SET @sortida='La familia '+@idFamRef+' no existeix';
	ELSE IF @idFamRef='Ordinador' or @idFamRef='Switch'
		SET @sortida='Les families "Ordinador" i "Switch" no es poden eliminar';
	ELSE
		-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
		BEGIN
		BEGIN TRY
			DELETE FROM FamReferencia WHERE idFamRef=@idFamRef;
			SET @sortida='Registre eliminat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
	--registrar auditoria
	DECLARE @resultat varchar(250);
	SET @sortida=@idFamRef+'-'+@sortida;
	exec ins_auditoria @idUsuGest,@control,'b_famreferencia',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[b_linentmat]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[b_linentmat]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@comLinMat int,
	@idLinEntMat int,
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF NOT EXISTS (SELECT idEntMat FROM EntMaterial WHERE idEntMat=@comLinMat)
		SET @sortida='L"entrada '+str(@comLinMat,10)+' no existeix';
	ELSE IF NOT EXISTS (SELECT * FROM LinEntMat WHERE comLinMat=@comLinMat and idLinEntMat=@idLinEntMat)
		SET @sortida='La linia d"entrada '+@comLinMat+'/'+str(@idLinEntMat,5)+' no existeix';	
	ELSE
		BEGIN
		BEGIN TRY
			DELETE FROM LinEntMat
			WHERE comLinMat=@comLinMat and idLinEntMat=@idLinEntMat;
			SET @sortida='Registre eliminat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
	--registrar auditoria
	DECLARE @resultat varchar(250);
		SET @sortida=str(@comLinMat,10)+'/'+str(@idLinEntMat)+'-'+@sortida;
		exec ins_auditoria @idUsuGest,@control,'b_linentmat',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[b_planta]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[b_planta]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idEdifici varchar(8),
	@idPlanta varchar(8),
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idEdifici='' or @idPlanta=''
		SET @sortida='El identificador d"edifici i planta no poden ser blanc';
	ELSE IF NOT EXISTS (SELECT * FROM Edifici WHERE idEdifici=@idEdifici)
		SET @sortida='L"Edifici '+@idEdifici+' no existeix';
	ELSE IF NOT EXISTS (SELECT * FROM Planta WHERE idEdifici=@idEdifici AND idPlanta=@idPlanta)
		SET @sortida='L"Edifici - Planta'+@idEdifici+' - '+@idPlanta+' no existeix';
	ELSE
		-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
		BEGIN
		BEGIN TRY
			DELETE FROM Planta WHERE idEdifici=@idEdifici AND idPlanta=@idPlanta;
			SET @sortida='Registre eliminat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
	--registrar auditoria
	DECLARE @resultat varchar(250);
	SET @sortida=@idEdifici+'/'+@idPlanta+'-'+@sortida;
	exec ins_auditoria @idUsuGest,@control,'b_planta',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[b_usuari]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[b_usuari]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idUsuari varchar(15),
	@dataBaixa date,
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idUsuari=NULL or @idUsuari=''
		SET @sortida='L"usuari no pot ser blanc';
	ELSE IF ISNULL(@dataBaixa,'')<>'' and @dataBaixa<(SELECT dataAlta FROM Usuari WHERE idUsuari=@idUsuari)
		SET @sortida='La data de baixa no pot ser inferior a la data d"alta';		
	ELSE IF NOT EXISTS (SELECT idUsuari FROM Usuari WHERE idUsuari=@idUsuari)
		SET @sortida='L"usuari '+@idUsuari+' no existeix';
	ELSE
		BEGIN
		BEGIN TRY
			DELETE FROM Usuari
			WHERE idUsuari=@idUsuari;
			SET @sortida='Registre eliminat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
	--registrar auditoria
	DECLARE @resultat varchar(250);
		SET @sortida=@idUsuari+'-'+@sortida;
		exec ins_auditoria @idUsuGest,@control,'b_usuari',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[b_usugestio]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[b_usugestio]
	-- Paràmetres d'entrada
	@idUsu varchar(15),
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	IF @idUsu=(SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsu)
	-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
			BEGIN
			BEGIN TRY
				DELETE FROM UsuGestio WHERE idUsuGest=@idUsu;
				SET @sortida='Registre eliminat correctament';
			END TRY
			BEGIN CATCH
				SET @sortida=(SELECT ERROR_MESSAGE());				
				RAISERROR (@sortida,-1,-1);
			END CATCH	
			END
	ELSE
		SET @sortida='L"usuari '+@idUsu+' no existeix';			
END

GO
/****** Object:  StoredProcedure [dbo].[ins_auditoria]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[ins_auditoria]
	-- Paràmetres d'entrada
	@idUsu varchar(15),
	@tipusLog char(1),
	@procediment varchar(50),
	@descripcio varchar(100),
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
	BEGIN TRY
		INSERT INTO Auditoria
		VALUES (getdate(),@tipusLog,@procediment,@descripcio,@idUsu);
		SET @sortida='Registre auditoria insertat correctament';
	END TRY
	BEGIN CATCH
		SET @sortida=(SELECT ERROR_MESSAGE());				
		RAISERROR (@sortida,-1,-1);
	END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[m_boqswitch]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[m_boqswitch]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idRef varchar(12),
	@modul char(1),
	@port int,
	@valid bit,
	@tipPort TipPort,
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idRef='' 
		SET @sortida='El identificador de referencia no pot ser blanc';
	ELSE IF NOT EXISTS (SELECT idRef FROM Referencia WHERE idRef=@idRef)
		SET @sortida='La referencia '+@idRef+' no existeix';
	ELSE IF NOT EXISTS (SELECT * FROM BoqSwitch WHERE idRef=@idRef AND modul=@modul and port=@port)
		SET @sortida='El switch '+@idRef+'-'+@modul+'-'+str(@port,2,0)+' no existeix';
			-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
	ELSE
		BEGIN
		BEGIN TRY
			UPDATE BoqSwitch
			SET valid=@valid,
			tipPort=@tipPort
			WHERE idRef=@idRef AND modul=@modul and port=@port;
			SET @sortida='Registre modificat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
	--registrar auditoria
	DECLARE @resultat varchar(250);
		SET @sortida=@idRef+'/'+@modul+'/'+str(@port,2,0)+'-'+@sortida;
		exec ins_auditoria @idUsuGest,@control,'m_boqswitch',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[m_edifici]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[m_edifici]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idEdifici varchar(8),
	@nom varchar(30),
	@adreca varchar(50),
	@poblacio varchar(30),
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idEdifici=''
		SET @sortida='El identificador d"edifici no pot ser blanc';
	ELSE IF NOT EXISTS (SELECT @idEdifici FROM Edifici WHERE idEdifici=@idEdifici)
		SET @sortida='L"Edifici '+@idEdifici+' no existeix';
	ELSE
		-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
		BEGIN
		BEGIN TRY
			UPDATE Edifici 
			SET nom=@nom,
			adreca=@adreca,
			poblacio=@poblacio
			WHERE idEdifici=@idEdifici;
			SET @sortida='Registre modificat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
	--registrar auditoria
	DECLARE @resultat varchar(250);
	SET @sortida=@idEdifici+'-'+@sortida;
	exec ins_auditoria @idUsuGest,@control,'m_edifici',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[m_entmat]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[m_entmat]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idEntMat int,
	@dateEntMat date,
	@numEntMat varchar(15),
	@numFact varchar(15),
	@dataFact date,
	@numReg varchar(15),
	@dataReg date,
	@idProveidor varchar(8),
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idEntMat='' OR @idEntMat=NULL 
		SET @sortida='El número d"entrada no poden ser blanc';
	ELSE IF NOT EXISTS (SELECT idProveidor FROM Proveidor WHERE idProveidor=@idProveidor)
		SET @sortida='El proveïdor '+@idProveidor+' no existeix';
	ELSE IF NOT EXISTS (SELECT * FROM EntMaterial WHERE idEntMat=@idEntMat)
		SET @sortida='L"entrada '+str(@idEntMat,10)+' no existeix';
	ELSE
		BEGIN
		BEGIN TRY
			UPDATE EntMaterial
			SET dateEntMat=@dateEntMat,
			numEntMat=@numEntMat,
			numFact=@numFact,
			dataFact=@dataFact,
			numReg=@numReg,
			dataReg=@dataReg,
			idProveidor=@idProveidor
			WHERE idEntMat=@idEntMat;
			SET @sortida='Registre modificat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
	--registrar auditoria
	DECLARE @resultat varchar(250);
		SET @sortida=str(@idEntMat,10)+'-'+@sortida;
		exec ins_auditoria @idUsuGest,@control,'m_entmat',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[m_famreferencia]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[m_famreferencia]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idFamRef varchar(15),
	@descFamRef varchar(50),
	@etiqueta varchar(12),
	@esOrdinador bit,
	@esSwitch bit,
	@esTelefon bit,
	@esPeriferic bit,
	@esFungible bit,
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idFamRef=''
		SET @sortida='El identificador de familia no pot ser blanc';
	--Comprovem el format de l'etiqueta
	ELSE IF (SELECT dbo.valida_etiqueta(@etiqueta))=1
		SET @sortida='El format de l"etiqueta ha de ser "XX000/AA"';
	ELSE IF NOT EXISTS (SELECT @idFamRef FROM FamReferencia WHERE idFamRef=@idFamRef)
		SET @sortida='La familia '+@idFamRef+' no existeix';
	ELSE IF @idFamRef='Ordinador' or @idFamRef='Switch'
		SET @sortida='Les families "Ordinador" i "Switch" no es poden modificar';
	ELSE
		-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
		BEGIN
		BEGIN TRY
			UPDATE FamReferencia 
			SET descFamRef=@descFamRef,
			etiqueta=@etiqueta,
			esOrdinador=@esOrdinador,
			esSwitch=@esSwitch,
			esTelefon=@esTelefon,
			esPeriferic=@esPeriferic,
			esFungible=@esFungible
			WHERE idFamRef=@idFamRef;
			SET @sortida='Registre modificat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
	--registrar auditoria
	DECLARE @resultat varchar(250);
	SET @sortida=@idFamRef+'-'+@sortida;
	exec ins_auditoria @idUsuGest,@control,'m_famreferencia',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[m_linentmat]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[m_linentmat]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@comLinMat int,
	@idLinEntMat int,
	@idRef varchar(12),
	@qtt int,
	@preuSiva decimal(6,2),
	@tipGar varchar(15),
	@mesosGar int,
	@dataGar date,
		-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF NOT EXISTS (SELECT idRef FROM Referencia WHERE idRef=@idRef)
		SET @sortida='La referencia '+@idRef+' no existeix';
	ELSE IF NOT EXISTS (SELECT idEntMat FROM EntMaterial WHERE idEntMat=@comLinMat)
		SET @sortida='L"entrada '+str(@comLinMat,10)+' no existeix';
	ELSE IF NOT EXISTS (SELECT * FROM LinEntMat WHERE comLinMat=@comLinMat and idLinEntMat=@idLinEntMat)
		SET @sortida='La linia d"entrada '+str(@comLinMat,15)+'/'+str(@idLinEntMat,5)+' no existeix';	
	ELSE
		BEGIN
		BEGIN TRY
			UPDATE LinEntMat
			SET idRef=@idRef,
			qtt=@qtt,
			preuSiva=@preuSiva,
			tipGar=@tipGar,
			mesosGar=@mesosGar,
			dataGar=@dataGar
			WHERE comLinMat=@comLinMat and idLinEntMat=@idLinEntMat;
			SET @sortida='Registre modificat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
	--registrar auditoria
	DECLARE @resultat varchar(250);
		SET @sortida=str(@comLinMat,10)+'/'+str(@idLinEntMat)+'-'+@sortida;
		exec ins_auditoria @idUsuGest,@control,'m_linentmat',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[m_ordinador]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[m_ordinador]
	-- Paràmetres d'entrada
@idUsuGest varchar(15),
	@idRef varchar(12),
	@processador varchar(20),
	@gbRam decimal(6,2),
	@gbDisc decimal(6,2),
	@tDdur varchar(8),
	@wol bit,
	@periferics varchar(50),
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idRef='' 
		SET @sortida='El identificador de referencia no pot ser blanc';
	ELSE IF NOT EXISTS (SELECT idRef FROM Referencia WHERE idRef=@idRef)
		SET @sortida='La referencia '+@idRef+' no existeix';
	ELSE IF NOT EXISTS (SELECT * FROM Ordinador WHERE idRef=@idRef)
		SET @sortida='L"Ordinador '+@idRef+' no existeix';
	ELSE
			-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
		BEGIN
		BEGIN TRY
			UPDATE Ordinador
			SET processador=@processador,
			gbRam=@gbRam,
			gbDisc=@gbDisc,
			tDdur=@tDdur,
			wol=@wol,
			periferics=@periferics
			WHERE idRef=@idRef;
			SET @sortida='Registre modificat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
	--registrar auditoria
	DECLARE @resultat varchar(250);
		SET @sortida=@idRef+'-'+@sortida;
		exec ins_auditoria @idUsuGest,@control,'m_ordinador',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[m_planta]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[m_planta]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idEdifici varchar(8),
	@idPlanta varchar(8),
	@planta int,
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idEdifici='' or @idPlanta=''
		SET @sortida='El identificador d"edifici i planta no poden ser blanc';
	ELSE IF NOT EXISTS (SELECT * FROM Edifici WHERE idEdifici=@idEdifici)
		SET @sortida='L"Edifici '+@idEdifici+' no existeix';
	ELSE IF NOT EXISTS (SELECT * FROM Planta WHERE idEdifici=@idEdifici AND idPlanta=@idPlanta)
		SET @sortida='L"Edifici - Planta'+@idEdifici+' - '+@idPlanta+' no existeix';
	ELSE
		-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
		BEGIN
		BEGIN TRY
			UPDATE Planta
			SET planta=@planta
			WHERE idEdifici=@idEdifici AND idPlanta=@idPlanta;
			SET @sortida='Registre modificat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
	--registrar auditoria
	DECLARE @resultat varchar(250);
	SET @sortida=@idEdifici+'/'+@idPlanta+'-'+@sortida;
	exec ins_auditoria @idUsuGest,@control,'m_planta',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[m_referencia]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[m_referencia]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idRef varchar(12),
	@nSerie varchar(40),
	@idFamRef varchar(15),
	@descRef varchar(50),
	@marca varchar(20),
	@model varchar(30),
	@pNum varchar(20),
	@estat EstatRef,
	@mac varchar(12),
	@extensio varchar(9),
	@observacions varchar(100),
	@ubiDriv varchar(10),
	@guardat varchar(10),
	@ip varchar(15),
	@dataCompra date,
	@nomProv varchar(50),
	@dataGar date,
	@qtt int,
	@pulzPant decimal(5,2),
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idRef='' or @idFamRef=''
		SET @sortida='El identificador de referencia i familia no poden ser blanc';
	ELSE IF NOT EXISTS (SELECT * FROM FamReferencia WHERE idFamRef=@idFamRef)
		SET @sortida='La familia '+@idFamRef+' no existeix';
	ELSE IF @ubiDriv<>'' AND @ubiDriv<>NULL AND NOT EXISTS (SELECT * FROM Ubicacio WHERE idUbicacio=@ubiDriv)
		SET @sortida='La ubicació del driver '+@ubiDriv+' no existeix';
	ELSE IF @guardat<>'' AND @guardat<>NULL AND NOT EXISTS (SELECT * FROM Ubicacio WHERE idUbicacio=@guardat)
		SET @sortida='La ubicació de la referència '+@guardat+' no existeix';
	ELSE IF NOT EXISTS (SELECT * FROM Referencia WHERE idRef=@idRef)
		SET @sortida='La referencia'+@idRef+' no existeix';
	ELSE IF @idFamRef<>(SELECT idFamRef FROM Referencia wherE idRef=@idRef)
		SET @sortida='No es pot modificar la familia de la referencia'+@idRef;	
	ELSE
		-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
		BEGIN
		BEGIN TRY
			UPDATE Referencia
			SET nSerie=@nSerie,
			idFamRef=@idFamRef,
			descRef=@descRef,
			marca=@marca,
			model=@model,
			pNum=@pNum,
			estat=@estat,
			mac=@mac,
			extensio=@extensio,
			observacions=@observacions,
			ubiDriv=@ubiDriv,
			guardat=@guardat,
			ip=@ip,
			dataCompra=@dataCompra,
			nomProv=@nomProv,
			dataGar=@dataGar,
			qtt=@qtt,
			pulzPant=@pulzPant
			WHERE idRef=@idRef;
			SET @sortida='Registre modificat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
	--registrar auditoria
	DECLARE @resultat varchar(250);
		SET @sortida=@idRef+'-'+@sortida;
		exec ins_auditoria @idUsuGest,@control,'m_referencia',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[m_usuari]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[m_usuari]
	-- Paràmetres d'entrada
	@idUsuGest varchar(15),
	@idUsuari varchar(15),
	@nom varchar(30),
	@cognoms varchar(50),
	@dni varchar(9),
	@dataAlta date,
	@dataBaixa date,
	@estatUsu EstatUsuari,
	@carrec varchar(30),
	@carrec2 varchar(30),
	@bustia varchar(30),
	@bustia2 varchar(30),
	@tipUsu varchar(8),
	@deptAssig varchar(8),
	@deptAssig2 varchar(8),	
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	--Fem les validacions

	DECLARE @control char(1)='E';
	
	IF NOT EXISTS (SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsuGest)
		SET @sortida='Aquest usuari de gestió no existeix';
	ELSE IF @idUsuari=NULL or @idUsuari=''
		SET @sortida='L"usuari no pot ser blanc';
	ELSE IF ISNULL(@dataAlta,'')=''
		SET @sortida='La data d"alta no pot ser blanc';	
	ELSE IF ISNULL(@dataBaixa,'')<>'' and @dataBaixa<@dataAlta
		SET @sortida='La data de baixa no pot ser inferior a la data d"alta';		
	ELSE IF NOT EXISTS (SELECT idtipUsu FROM TipusUsuari WHERE idTipUsu=@tipUsu)
		SET @sortida='El tipus d"usuari '+@tipUsu+' no existeix';
	ELSE IF ISNULL(@bustia,'')<>'' and NOT EXISTS (SELECT idBustia FROM Bustia WHERE idBustia=@bustia)
		SET @sortida='La bustia '+@bustia+' no existeix';
	ELSE IF ISNULL(@bustia2,'')<>'' and NOT EXISTS (SELECT idBustia FROM Bustia WHERE idBustia=@bustia2)
		SET @sortida='La bustia2 '+@bustia2+' no existeix';
	ELSE IF ISNULL(@deptAssig,'')<>'' and NOT EXISTS (SELECT idDepartament FROM Departament WHERE idDepartament=@deptAssig)
		SET @sortida='El departament '+@deptAssig+' no existeix';
	ELSE IF ISNULL(@deptAssig2,'')<>'' and NOT EXISTS (SELECT idDepartament FROM Departament WHERE idDepartament=@deptAssig2)
		SET @sortida='El departament2 '+@deptAssig2+' no existeix';
	ELSE IF NOT EXISTS (SELECT idUsuari FROM Usuari WHERE idUsuari=@idUsuari)
		SET @sortida='L"usuari '+@idUsuari+' no existeix';
	ELSE
		BEGIN
		BEGIN TRY
			UPDATE Usuari
			SET nom=@nom, 
			cognoms=@cognoms,
			dni=@dni, 
			dataAlta=@dataAlta, 
			dataBaixa=@dataBaixa, 
			estatUsu=@estatUsu, 
			carrec=@carrec, 
			carrec2=@carrec2, 
			bustia=@bustia, 
			bustia2=@bustia2, 
			tipUsu=@tipUsu, 
			deptAssig=@deptAssig, 
			deptAssig2=@deptAssig2
			WHERE idUsuari=@idUsuari;
			SET @sortida='Registre modificat correctament';
			SET @control='R';
		END TRY
		BEGIN CATCH
			SET @sortida=(SELECT ERROR_MESSAGE());				
			RAISERROR (@sortida,-1,-1);
		END CATCH
		END
	--registrar auditoria
	DECLARE @resultat varchar(250);
		SET @sortida=@idUsuari+'-'+@sortida;
		exec ins_auditoria @idUsuGest,@control,'m_usuari',@sortida, @resultat OUTPUT;
END

GO
/****** Object:  StoredProcedure [dbo].[m_usugestio]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem el procediment
CREATE PROCEDURE [dbo].[m_usugestio]
	-- Paràmetres d'entrada
	@idUsu varchar(15),
	@nom varchar(50),
	-- Paràmetre de sortida
	@sortida varchar(250) OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	IF @idUsu=(SELECT idUsuGest FROM UsuGestio WHERE idUsuGest=@idUsu)
			-- afegim la inserció en un try-catch per si surtis algun error no controlat en el procediment.
			BEGIN
			BEGIN TRY
				UPDATE UsuGestio SET nom=@nom WHERE idUsuGest=@idUsu;
				SET @sortida='Registre modificat correctament';
			END TRY
			BEGIN CATCH
				SET @sortida=(SELECT ERROR_MESSAGE());				
				RAISERROR (@sortida,-1,-1);
			END CATCH
			END
	ELSE
		SET @sortida='L"usuari '+@idUsu+' no existeix';
		
END

GO
/****** Object:  UserDefinedFunction [dbo].[retorna_etiqueta]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem una funció que retorna el següent número de referencia segons el format de l'etiqueta de la familia
CREATE FUNCTION [dbo].[retorna_etiqueta] (@familia varchar(15)) 
RETURNS varchar(12) -- Indiquem el tipus de retorn
AS
BEGIN
 DECLARE @eti varchar(12)='';
 
 --Busquem el format de l'etiqueta de la familia;
 SET @eti=(SELECT etiqueta FROM FamReferencia WHERE idFamRef=@familia);
 
 --Recollim el número i l'augmentem en 1 unitat.
	IF (len(@eti)<8)
		SET @eti='';
	ELSE
	BEGIN
		-- Retorn ISNUMERIC 1=true, 0=false
		IF (SELECT ISNUMERIC(SUBSTRING(@eti,3,3)))=1
		 BEGIN
		 DECLARE @num int =SUBSTRING(@eti,3,3);
		 SET @num=@num+1;
		 SET @eti=SUBSTRING(@eti,1,2)+right('000'+ltrim(rtrim(str(@num,3,0))),3)+'/'+SUBSTRING(@eti,7,2);
		 END
		ELSE
		 SET @eti='';
	END
 RETURN (@eti) 
END

GO
/****** Object:  UserDefinedFunction [dbo].[valida_etiqueta]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creem una funció que verifica si l'entrada té el format correcte
CREATE FUNCTION [dbo].[valida_etiqueta] (@etiqueta varchar(12)) 
RETURNS bit -- Indiquem el tipus de retorn
AS
BEGIN
 DECLARE @resultat bit=0;
 
 --Comprovem el format de l'etiqueta
	IF (len(@etiqueta)<8)
		SET @resultat=1;
	ELSE
		BEGIN
		-- Retorn ISNUMERIC 1=true, 0=false
		DECLARE @prefix int=(SELECT ISNUMERIC(SUBSTRING(@etiqueta,1,2)));
		DECLARE @codi int=(SELECT ISNUMERIC(SUBSTRING(@etiqueta,3,3))); 
		DECLARE @any int=(SELECT ISNUMERIC(SUBSTRING(@etiqueta,7,2)));
		IF @prefix=1 OR @codi=0 OR @any=0
			SET @resultat=1;
	END

 RETURN (@resultat) 
END

GO
/****** Object:  Table [dbo].[AssigBus]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AssigBus](
	[idBustia] [varchar](30) NOT NULL,
	[idUsu] [varchar](15) NOT NULL,
	[dataAssig] [date] NULL,
	[permis] [varchar](30) NULL,
 CONSTRAINT [PK_AssigBus] PRIMARY KEY CLUSTERED 
(
	[idBustia] ASC,
	[idUsu] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[AssigProg]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AssigProg](
	[idPuntTreb] [varchar](9) NOT NULL,
	[idRef] [varchar](12) NOT NULL,
	[dataAssig] [date] NULL,
 CONSTRAINT [PK_AssigProg] PRIMARY KEY CLUSTERED 
(
	[idPuntTreb] ASC,
	[idRef] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[AssigUsu]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AssigUsu](
	[idPuntTreb] [varchar](9) NOT NULL,
	[idUsu] [varchar](15) NOT NULL,
	[perfil] [varchar](50) NULL,
	[esPpal] [bit] NOT NULL,
	[dataAssig] [date] NULL,
 CONSTRAINT [PK_AssigUsu] PRIMARY KEY CLUSTERED 
(
	[idPuntTreb] ASC,
	[idUsu] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Auditoria]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Auditoria](
	[idLog] [int] IDENTITY(1,1) NOT NULL,
	[datalog] [datetime] NOT NULL,
	[tipusLog] [char](1) NOT NULL,
	[procediment] [varchar](50) NULL,
	[descripcio] [varchar](100) NULL,
	[idUsuGest] [varchar](15) NOT NULL,
 CONSTRAINT [PK_Auditoria] PRIMARY KEY CLUSTERED 
(
	[idLog] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[BoqSwitch]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BoqSwitch](
	[idRef] [varchar](12) NOT NULL,
	[modul] [char](1) NOT NULL,
	[port] [int] NOT NULL,
	[valid] [bit] NOT NULL,
	[tipPort] [dbo].[TipPort] NOT NULL,
 CONSTRAINT [PK_BoqSwitch] PRIMARY KEY CLUSTERED 
(
	[idRef] ASC,
	[modul] ASC,
	[port] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Bustia]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Bustia](
	[idBustia] [varchar](30) NOT NULL,
	[nom] [varchar](30) NULL,
	[redirec] [varchar](30) NULL,
	[idDept] [varchar](8) NULL,
 CONSTRAINT [PK_Bustia] PRIMARY KEY CLUSTERED 
(
	[idBustia] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Departament]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Departament](
	[idDepartament] [varchar](8) NOT NULL,
	[nom] [varchar](30) NOT NULL,
	[idPlanta] [varchar](8) NULL,
	[idEdifici] [varchar](8) NULL,
 CONSTRAINT [PK_Departament] PRIMARY KEY CLUSTERED 
(
	[idDepartament] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Edifici]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Edifici](
	[idEdifici] [varchar](8) NOT NULL,
	[nom] [varchar](30) NULL,
	[adreca] [varchar](50) NULL,
	[poblacio] [varchar](30) NULL,
 CONSTRAINT [PK_Edifici] PRIMARY KEY CLUSTERED 
(
	[idEdifici] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[EntMaterial]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EntMaterial](
	[idEntMat] [int] IDENTITY(1,1) NOT NULL,
	[dateEntMat] [date] NOT NULL,
	[numEntMat] [varchar](15) NOT NULL,
	[numFact] [varchar](15) NULL,
	[dataFact] [date] NULL,
	[numReg] [varchar](15) NULL,
	[dataReg] [date] NULL,
	[idProveidor] [varchar](8) NULL,
 CONSTRAINT [PK_EntMaterial] PRIMARY KEY CLUSTERED 
(
	[idEntMat] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[FamReferencia]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FamReferencia](
	[idFamRef] [varchar](15) NOT NULL,
	[descFamRef] [varchar](50) NULL,
	[etiqueta] [varchar](12) NULL,
	[esOrdinador] [bit] NOT NULL,
	[esSwitch] [bit] NOT NULL,
	[esTelefon] [bit] NOT NULL,
	[esPeriferic] [bit] NOT NULL,
	[esFungible] [bit] NOT NULL,
 CONSTRAINT [PK_FamReferencia] PRIMARY KEY CLUSTERED 
(
	[idFamRef] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Finestreta]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Finestreta](
	[idEdifici] [varchar](8) NOT NULL,
	[idPlanta] [varchar](8) NOT NULL,
	[idFinestreta] [varchar](8) NOT NULL,
	[idSwitch] [varchar](12) NULL,
	[modul] [char](1) NULL,
	[port] [int] NULL,
	[idRef] [varchar](12) NULL,
 CONSTRAINT [PK_Finestreta] PRIMARY KEY CLUSTERED 
(
	[idEdifici] ASC,
	[idPlanta] ASC,
	[idFinestreta] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[HistAssigProg]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HistAssigProg](
	[idHistPuntTreb] [int] NOT NULL,
	[idRef] [varchar](12) NOT NULL,
	[dataAssig] [date] NULL,
 CONSTRAINT [PK_HistAssigProg] PRIMARY KEY CLUSTERED 
(
	[idHistPuntTreb] ASC,
	[idRef] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[HistAssigUsu]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HistAssigUsu](
	[idHistPuntTreb] [int] NOT NULL,
	[idUsu] [varchar](15) NOT NULL,
	[perfil] [varchar](50) NULL,
	[esPpal] [bit] NULL,
	[dataAssig] [date] NULL,
 CONSTRAINT [PK_HistAssigUsu] PRIMARY KEY CLUSTERED 
(
	[idHistPuntTreb] ASC,
	[idUsu] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[HistBus]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HistBus](
	[idHistUsu] [int] NOT NULL,
	[bustia] [varchar](30) NOT NULL,
	[dataAssig] [date] NULL,
	[permis] [varchar](30) NULL,
 CONSTRAINT [PK_HistBus] PRIMARY KEY CLUSTERED 
(
	[idHistUsu] ASC,
	[bustia] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[HistPuntTreb]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HistPuntTreb](
	[idHistPuntTreb] [int] IDENTITY(1,1) NOT NULL,
	[idPuntTreb] [varchar](9) NULL,
	[dataAccio] [datetime] NOT NULL,
	[accio] [char](1) NULL,
	[nom] [varchar](30) NULL,
	[dataAlta] [date] NULL,
	[estat] [bit] NULL,
	[observacions] [varchar](150) NULL,
	[usb] [bit] NULL,
	[nomDep] [varchar](30) NULL,
	[progNLlic] [varchar](12) NULL,
	[ubiPuntTreb] [varchar](8) NULL,
	[ordinador] [varchar](12) NULL,
	[reinstalat] [date] NULL,
	[gbRam] [decimal](6, 2) NULL,
	[gbDisc] [decimal](6, 2) NULL,
	[tDdur] [varchar](8) NULL,
	[sOpe] [varchar](12) NULL,
	[office] [varchar](12) NULL,
	[monitor] [varchar](12) NULL,
	[monitor2] [varchar](12) NULL,
	[teclat] [varchar](12) NULL,
	[ratoli] [varchar](12) NULL,
	[telf] [varchar](12) NULL,
	[mobil] [varchar](12) NULL,
	[tauleta] [varchar](12) NULL,
	[periferics] [varchar](12) NULL,
	[impressora] [varchar](12) NULL,
	[escaner] [varchar](12) NULL,
	[wol] [bit] NULL,
	[altPer] [varchar](12) NULL,
	[altPer2] [varchar](12) NULL,
	[pulzPant] [decimal](6, 2) NULL,
 CONSTRAINT [PK_HistPuntTreb] PRIMARY KEY CLUSTERED 
(
	[idHistPuntTreb] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[HistUsu]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HistUsu](
	[idHistUsu] [int] IDENTITY(1,1) NOT NULL,
	[idUsu] [varchar](15) NOT NULL,
	[dataAccio] [datetime] NOT NULL,
	[accio] [char](1) NULL,
	[nom] [varchar](30) NULL,
	[cognoms] [varchar](30) NULL,
	[dni] [varchar](9) NULL,
	[dataAlta] [date] NULL,
	[dataBaixa] [date] NULL,
	[estatUsu] [dbo].[EstatUsuari] NULL,
	[carrec] [varchar](30) NULL,
	[carrec2] [varchar](30) NULL,
	[bustia] [varchar](30) NULL,
	[bustia2] [varchar](30) NULL,
	[nomTipUsu] [varchar](30) NULL,
	[nomDeptAssig] [varchar](30) NULL,
	[nomDeptAssig2] [varchar](30) NULL,
 CONSTRAINT [PK_HistUsu] PRIMARY KEY CLUSTERED 
(
	[idHistUsu] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Intervencio]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Intervencio](
	[idIntervencio] [int] IDENTITY(1,1) NOT NULL,
	[dataInt] [date] NOT NULL,
	[nTiquet] [varchar](8) NULL,
	[refAvariada] [varchar](12) NOT NULL,
	[refRecanvi] [varchar](12) NULL,
	[descripcio] [varchar](150) NULL,
 CONSTRAINT [PK_Intervencio] PRIMARY KEY CLUSTERED 
(
	[idIntervencio] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[LinEntMat]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LinEntMat](
	[comLinMat] [int] NOT NULL,
	[idLinEntMat] [int] IDENTITY(1,1) NOT NULL,
	[idRef] [varchar](12) NOT NULL,
	[qtt] [int] NULL,
	[preuSiva] [decimal](6, 2) NULL,
	[tipGar] [varchar](15) NULL,
	[mesosGar] [int] NULL,
	[dataGar] [date] NULL,
 CONSTRAINT [PK_LinEntMat] PRIMARY KEY CLUSTERED 
(
	[comLinMat] ASC,
	[idLinEntMat] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[MovInventari]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MovInventari](
	[idMovInv] [int] IDENTITY(1,1) NOT NULL,
	[data] [date] NULL,
	[qtt] [int] NULL,
	[idRef] [varchar](12) NOT NULL,
 CONSTRAINT [PK_MovInventari] PRIMARY KEY CLUSTERED 
(
	[idMovInv] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Ordinador]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Ordinador](
	[idRef] [varchar](12) NOT NULL,
	[processador] [varchar](20) NULL,
	[gbRam] [decimal](6, 2) NULL,
	[gbDisc] [decimal](6, 2) NULL,
	[tDdur] [varchar](8) NULL,
	[wol] [bit] NULL,
	[periferics] [varchar](50) NULL,
 CONSTRAINT [PK_Ordinador] PRIMARY KEY CLUSTERED 
(
	[idRef] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Planta]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Planta](
	[idEdifici] [varchar](8) NOT NULL,
	[idPlanta] [varchar](8) NOT NULL,
	[planta] [int] NOT NULL,
 CONSTRAINT [PK_Planta] PRIMARY KEY CLUSTERED 
(
	[idEdifici] ASC,
	[idPlanta] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Proveidor]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Proveidor](
	[idProveidor] [varchar](8) NOT NULL,
	[nomComer] [varchar](50) NULL,
	[raoSocial] [varchar](50) NULL,
	[cif] [varchar](9) NULL,
	[telefon] [varchar](9) NULL,
	[email] [varchar](30) NULL,
 CONSTRAINT [PK_Proveidor] PRIMARY KEY CLUSTERED 
(
	[idProveidor] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[PuntTreball]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PuntTreball](
	[idPuntTreb] [varchar](9) NOT NULL,
	[nom] [varchar](30) NULL,
	[dataAlta] [date] NULL,
	[estat] [bit] NOT NULL,
	[observacions] [varchar](100) NULL,
	[usb] [bit] NULL,
	[progNLlic] [varchar](12) NULL,
	[ubiPuntTreb] [varchar](8) NOT NULL,
	[ordinador] [varchar](12) NOT NULL,
	[sOpe] [varchar](12) NULL,
	[office] [varchar](12) NULL,
	[monitor] [varchar](12) NULL,
	[monitor2] [varchar](12) NULL,
	[teclat] [varchar](12) NULL,
	[ratoli] [varchar](12) NULL,
	[telf] [varchar](12) NULL,
	[mobil] [varchar](12) NULL,
	[tauleta] [varchar](12) NULL,
	[impressora] [varchar](12) NULL,
	[escaner] [varchar](12) NULL,
	[wol] [bit] NULL,
	[altPer] [varchar](12) NULL,
	[altPer2] [varchar](12) NULL,
	[reinstalat] [date] NULL,
 CONSTRAINT [PK_PuntTreball] PRIMARY KEY CLUSTERED 
(
	[idPuntTreb] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Referencia]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Referencia](
	[idRef] [varchar](12) NOT NULL,
	[nSerie] [varchar](40) NULL,
	[idFamRef] [varchar](15) NOT NULL,
	[descRef] [varchar](50) NULL,
	[marca] [varchar](20) NULL,
	[model] [varchar](30) NULL,
	[pNum] [varchar](20) NULL,
	[estat] [dbo].[EstatRef] NOT NULL,
	[mac] [varchar](12) NULL,
	[extensio] [varchar](9) NULL,
	[observacions] [varchar](100) NULL,
	[ubiDriv] [varchar](10) NULL,
	[guardat] [varchar](10) NULL,
	[ip] [varchar](15) NULL,
	[dataCompra] [date] NULL,
	[nomProv] [varchar](50) NULL,
	[dataGar] [date] NULL,
	[qtt] [int] NULL,
	[pulzPant] [decimal](5, 2) NULL,
 CONSTRAINT [PK_Referencia] PRIMARY KEY CLUSTERED 
(
	[idRef] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[TipusUsuari]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TipusUsuari](
	[idTipUsu] [varchar](8) NOT NULL,
	[descripcio] [varchar](30) NULL,
 CONSTRAINT [PK_TipusUsuari] PRIMARY KEY CLUSTERED 
(
	[idTipUsu] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Ubicacio]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Ubicacio](
	[idUbicacio] [varchar](10) NOT NULL,
	[descUbic] [varchar](50) NULL,
 CONSTRAINT [PK_Ubicacio] PRIMARY KEY CLUSTERED 
(
	[idUbicacio] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Usuari]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Usuari](
	[idUsuari] [varchar](15) NOT NULL,
	[nom] [varchar](30) NULL,
	[cognoms] [varchar](50) NULL,
	[dni] [varchar](9) NULL,
	[dataAlta] [date] NOT NULL,
	[dataBaixa] [date] NULL,
	[estatUsu] [dbo].[EstatUsuari] NULL,
	[carrec] [varchar](30) NULL,
	[carrec2] [varchar](30) NULL,
	[bustia] [varchar](30) NULL,
	[bustia2] [varchar](30) NULL,
	[tipUsu] [varchar](8) NOT NULL,
	[deptAssig] [varchar](8) NOT NULL,
	[deptAssig2] [varchar](8) NULL,
 CONSTRAINT [PK_Usuari] PRIMARY KEY CLUSTERED 
(
	[idUsuari] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[UsuGestio]    Script Date: 17/12/2018 7:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UsuGestio](
	[idUsuGest] [varchar](15) NOT NULL,
	[nom] [varchar](50) NULL,
 CONSTRAINT [PK_UsuGestio] PRIMARY KEY CLUSTERED 
(
	[idUsuGest] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Index [Data_Auditoria]    Script Date: 17/12/2018 7:44:20 ******/
CREATE NONCLUSTERED INDEX [Data_Auditoria] ON [dbo].[Auditoria]
(
	[datalog] ASC,
	[idLog] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [Punt_HistPuntTreb]    Script Date: 17/12/2018 7:44:20 ******/
CREATE NONCLUSTERED INDEX [Punt_HistPuntTreb] ON [dbo].[HistPuntTreb]
(
	[idPuntTreb] ASC,
	[dataAccio] ASC,
	[idHistPuntTreb] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [Usu_HistUsu]    Script Date: 17/12/2018 7:44:20 ******/
CREATE NONCLUSTERED INDEX [Usu_HistUsu] ON [dbo].[HistUsu]
(
	[idUsu] ASC,
	[dataAccio] ASC,
	[idHistUsu] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [Ref_Intervencio]    Script Date: 17/12/2018 7:44:20 ******/
CREATE NONCLUSTERED INDEX [Ref_Intervencio] ON [dbo].[Intervencio]
(
	[refAvariada] ASC,
	[dataInt] ASC,
	[idIntervencio] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [Ref_MovInventari]    Script Date: 17/12/2018 7:44:20 ******/
CREATE NONCLUSTERED INDEX [Ref_MovInventari] ON [dbo].[MovInventari]
(
	[idRef] ASC,
	[data] ASC,
	[idMovInv] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [Fam_Referencia]    Script Date: 17/12/2018 7:44:20 ******/
CREATE NONCLUSTERED INDEX [Fam_Referencia] ON [dbo].[Referencia]
(
	[idFamRef] ASC,
	[idRef] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AssigUsu] ADD  CONSTRAINT [DF_AssigUsu_esPpal]  DEFAULT ((0)) FOR [esPpal]
GO
ALTER TABLE [dbo].[BoqSwitch] ADD  CONSTRAINT [DF_BoqSwitch_valid]  DEFAULT ((1)) FOR [valid]
GO
ALTER TABLE [dbo].[BoqSwitch] ADD  CONSTRAINT [DF_BoqSwitch_tipPort]  DEFAULT ('RJ-45') FOR [tipPort]
GO
ALTER TABLE [dbo].[FamReferencia] ADD  CONSTRAINT [DF_FamReferencia_esOrdinador]  DEFAULT ((0)) FOR [esOrdinador]
GO
ALTER TABLE [dbo].[FamReferencia] ADD  CONSTRAINT [DF_FamReferencia_esSwitch]  DEFAULT ((0)) FOR [esSwitch]
GO
ALTER TABLE [dbo].[FamReferencia] ADD  CONSTRAINT [DF_FamReferencia_esTelefon]  DEFAULT ((0)) FOR [esTelefon]
GO
ALTER TABLE [dbo].[FamReferencia] ADD  CONSTRAINT [DF_FamReferencia_esPeriferic]  DEFAULT ((0)) FOR [esPeriferic]
GO
ALTER TABLE [dbo].[FamReferencia] ADD  CONSTRAINT [DF_FamReferencia_esFungible]  DEFAULT ((0)) FOR [esFungible]
GO
ALTER TABLE [dbo].[Ordinador] ADD  CONSTRAINT [DF_Ordinador_wol]  DEFAULT ((0)) FOR [wol]
GO
ALTER TABLE [dbo].[PuntTreball] ADD  CONSTRAINT [DF_PuntTreball_estat]  DEFAULT ((0)) FOR [estat]
GO
ALTER TABLE [dbo].[PuntTreball] ADD  CONSTRAINT [DF_PuntTreball_usb]  DEFAULT ((0)) FOR [usb]
GO
ALTER TABLE [dbo].[Referencia] ADD  CONSTRAINT [DF_Referencia_estat]  DEFAULT ('Pendent') FOR [estat]
GO
ALTER TABLE [dbo].[AssigBus]  WITH CHECK ADD  CONSTRAINT [FK_AssigBus_Bustia] FOREIGN KEY([idBustia])
REFERENCES [dbo].[Bustia] ([idBustia])
GO
ALTER TABLE [dbo].[AssigBus] CHECK CONSTRAINT [FK_AssigBus_Bustia]
GO
ALTER TABLE [dbo].[AssigBus]  WITH CHECK ADD  CONSTRAINT [FK_AssigBus_Usuari] FOREIGN KEY([idUsu])
REFERENCES [dbo].[Usuari] ([idUsuari])
GO
ALTER TABLE [dbo].[AssigBus] CHECK CONSTRAINT [FK_AssigBus_Usuari]
GO
ALTER TABLE [dbo].[AssigProg]  WITH CHECK ADD  CONSTRAINT [FK_AssigProg_PuntTreball] FOREIGN KEY([idPuntTreb])
REFERENCES [dbo].[PuntTreball] ([idPuntTreb])
GO
ALTER TABLE [dbo].[AssigProg] CHECK CONSTRAINT [FK_AssigProg_PuntTreball]
GO
ALTER TABLE [dbo].[AssigProg]  WITH CHECK ADD  CONSTRAINT [FK_AssigProg_Referencia] FOREIGN KEY([idRef])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[AssigProg] CHECK CONSTRAINT [FK_AssigProg_Referencia]
GO
ALTER TABLE [dbo].[AssigUsu]  WITH CHECK ADD  CONSTRAINT [FK_AssigUsu_PuntTreball] FOREIGN KEY([idPuntTreb])
REFERENCES [dbo].[PuntTreball] ([idPuntTreb])
GO
ALTER TABLE [dbo].[AssigUsu] CHECK CONSTRAINT [FK_AssigUsu_PuntTreball]
GO
ALTER TABLE [dbo].[AssigUsu]  WITH CHECK ADD  CONSTRAINT [FK_AssigUsu_Usuari] FOREIGN KEY([idUsu])
REFERENCES [dbo].[Usuari] ([idUsuari])
GO
ALTER TABLE [dbo].[AssigUsu] CHECK CONSTRAINT [FK_AssigUsu_Usuari]
GO
ALTER TABLE [dbo].[Auditoria]  WITH CHECK ADD  CONSTRAINT [FK_Auditoria_UsuGestio] FOREIGN KEY([idUsuGest])
REFERENCES [dbo].[UsuGestio] ([idUsuGest])
GO
ALTER TABLE [dbo].[Auditoria] CHECK CONSTRAINT [FK_Auditoria_UsuGestio]
GO
ALTER TABLE [dbo].[BoqSwitch]  WITH CHECK ADD  CONSTRAINT [FK_BoqSwitch_Referencia] FOREIGN KEY([idRef])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[BoqSwitch] CHECK CONSTRAINT [FK_BoqSwitch_Referencia]
GO
ALTER TABLE [dbo].[Bustia]  WITH CHECK ADD  CONSTRAINT [FK_Bustia_Departament] FOREIGN KEY([idDept])
REFERENCES [dbo].[Departament] ([idDepartament])
GO
ALTER TABLE [dbo].[Bustia] CHECK CONSTRAINT [FK_Bustia_Departament]
GO
ALTER TABLE [dbo].[Departament]  WITH CHECK ADD  CONSTRAINT [FK_Departament_Planta] FOREIGN KEY([idEdifici], [idPlanta])
REFERENCES [dbo].[Planta] ([idEdifici], [idPlanta])
GO
ALTER TABLE [dbo].[Departament] CHECK CONSTRAINT [FK_Departament_Planta]
GO
ALTER TABLE [dbo].[EntMaterial]  WITH CHECK ADD  CONSTRAINT [FK_EntMaterial_Proveidor] FOREIGN KEY([idProveidor])
REFERENCES [dbo].[Proveidor] ([idProveidor])
GO
ALTER TABLE [dbo].[EntMaterial] CHECK CONSTRAINT [FK_EntMaterial_Proveidor]
GO
ALTER TABLE [dbo].[Finestreta]  WITH CHECK ADD  CONSTRAINT [FK_Finestreta_BoqSwitch] FOREIGN KEY([idSwitch], [modul], [port])
REFERENCES [dbo].[BoqSwitch] ([idRef], [modul], [port])
GO
ALTER TABLE [dbo].[Finestreta] CHECK CONSTRAINT [FK_Finestreta_BoqSwitch]
GO
ALTER TABLE [dbo].[Finestreta]  WITH CHECK ADD  CONSTRAINT [FK_Finestreta_Planta] FOREIGN KEY([idEdifici], [idPlanta])
REFERENCES [dbo].[Planta] ([idEdifici], [idPlanta])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Finestreta] CHECK CONSTRAINT [FK_Finestreta_Planta]
GO
ALTER TABLE [dbo].[Finestreta]  WITH CHECK ADD  CONSTRAINT [FK_Finestreta_Referencia] FOREIGN KEY([idRef])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[Finestreta] CHECK CONSTRAINT [FK_Finestreta_Referencia]
GO
ALTER TABLE [dbo].[HistAssigProg]  WITH NOCHECK ADD  CONSTRAINT [FK_HistAssigProg_HistPuntTreb] FOREIGN KEY([idHistPuntTreb])
REFERENCES [dbo].[HistPuntTreb] ([idHistPuntTreb])
ON UPDATE CASCADE
ON DELETE CASCADE
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[HistAssigProg] CHECK CONSTRAINT [FK_HistAssigProg_HistPuntTreb]
GO
ALTER TABLE [dbo].[HistAssigUsu]  WITH CHECK ADD  CONSTRAINT [FK_HistAssigUsu_HistPuntTreb] FOREIGN KEY([idHistPuntTreb])
REFERENCES [dbo].[HistPuntTreb] ([idHistPuntTreb])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[HistAssigUsu] CHECK CONSTRAINT [FK_HistAssigUsu_HistPuntTreb]
GO
ALTER TABLE [dbo].[HistBus]  WITH CHECK ADD  CONSTRAINT [FK_HistBus_HistUsu] FOREIGN KEY([idHistUsu])
REFERENCES [dbo].[HistUsu] ([idHistUsu])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[HistBus] CHECK CONSTRAINT [FK_HistBus_HistUsu]
GO
ALTER TABLE [dbo].[Intervencio]  WITH CHECK ADD  CONSTRAINT [FK_Intervencio_RefAvariada] FOREIGN KEY([refAvariada])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[Intervencio] CHECK CONSTRAINT [FK_Intervencio_RefAvariada]
GO
ALTER TABLE [dbo].[Intervencio]  WITH CHECK ADD  CONSTRAINT [FK_Intervencio_RefRecanvi] FOREIGN KEY([refRecanvi])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[Intervencio] CHECK CONSTRAINT [FK_Intervencio_RefRecanvi]
GO
ALTER TABLE [dbo].[LinEntMat]  WITH CHECK ADD  CONSTRAINT [FK_LinEntMat_EntMaterial] FOREIGN KEY([comLinMat])
REFERENCES [dbo].[EntMaterial] ([idEntMat])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[LinEntMat] CHECK CONSTRAINT [FK_LinEntMat_EntMaterial]
GO
ALTER TABLE [dbo].[LinEntMat]  WITH CHECK ADD  CONSTRAINT [FK_LinEntMat_Referencia] FOREIGN KEY([idRef])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[LinEntMat] CHECK CONSTRAINT [FK_LinEntMat_Referencia]
GO
ALTER TABLE [dbo].[MovInventari]  WITH CHECK ADD  CONSTRAINT [FK_MovInventari_Referencia] FOREIGN KEY([idRef])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[MovInventari] CHECK CONSTRAINT [FK_MovInventari_Referencia]
GO
ALTER TABLE [dbo].[Ordinador]  WITH CHECK ADD  CONSTRAINT [FK_Ordinador_Referencia] FOREIGN KEY([idRef])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[Ordinador] CHECK CONSTRAINT [FK_Ordinador_Referencia]
GO
ALTER TABLE [dbo].[Planta]  WITH CHECK ADD  CONSTRAINT [FK_Planta_Edifici] FOREIGN KEY([idEdifici])
REFERENCES [dbo].[Edifici] ([idEdifici])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Planta] CHECK CONSTRAINT [FK_Planta_Edifici]
GO
ALTER TABLE [dbo].[PuntTreball]  WITH CHECK ADD  CONSTRAINT [FK_PuntTreball_Departament] FOREIGN KEY([ubiPuntTreb])
REFERENCES [dbo].[Departament] ([idDepartament])
GO
ALTER TABLE [dbo].[PuntTreball] CHECK CONSTRAINT [FK_PuntTreball_Departament]
GO
ALTER TABLE [dbo].[PuntTreball]  WITH CHECK ADD  CONSTRAINT [FK_PuntTreball_RefAltPer] FOREIGN KEY([altPer])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[PuntTreball] CHECK CONSTRAINT [FK_PuntTreball_RefAltPer]
GO
ALTER TABLE [dbo].[PuntTreball]  WITH CHECK ADD  CONSTRAINT [FK_PuntTreball_RefAltPer2] FOREIGN KEY([altPer2])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[PuntTreball] CHECK CONSTRAINT [FK_PuntTreball_RefAltPer2]
GO
ALTER TABLE [dbo].[PuntTreball]  WITH CHECK ADD  CONSTRAINT [FK_PuntTreball_RefEsc] FOREIGN KEY([escaner])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[PuntTreball] CHECK CONSTRAINT [FK_PuntTreball_RefEsc]
GO
ALTER TABLE [dbo].[PuntTreball]  WITH CHECK ADD  CONSTRAINT [FK_PuntTreball_RefImp] FOREIGN KEY([impressora])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[PuntTreball] CHECK CONSTRAINT [FK_PuntTreball_RefImp]
GO
ALTER TABLE [dbo].[PuntTreball]  WITH CHECK ADD  CONSTRAINT [FK_PuntTreball_RefMob] FOREIGN KEY([mobil])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[PuntTreball] CHECK CONSTRAINT [FK_PuntTreball_RefMob]
GO
ALTER TABLE [dbo].[PuntTreball]  WITH CHECK ADD  CONSTRAINT [FK_PuntTreball_RefMon] FOREIGN KEY([monitor])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[PuntTreball] CHECK CONSTRAINT [FK_PuntTreball_RefMon]
GO
ALTER TABLE [dbo].[PuntTreball]  WITH CHECK ADD  CONSTRAINT [FK_PuntTreball_RefMon2] FOREIGN KEY([monitor2])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[PuntTreball] CHECK CONSTRAINT [FK_PuntTreball_RefMon2]
GO
ALTER TABLE [dbo].[PuntTreball]  WITH CHECK ADD  CONSTRAINT [FK_PuntTreball_RefOff] FOREIGN KEY([office])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[PuntTreball] CHECK CONSTRAINT [FK_PuntTreball_RefOff]
GO
ALTER TABLE [dbo].[PuntTreball]  WITH CHECK ADD  CONSTRAINT [FK_PuntTreball_RefOrd] FOREIGN KEY([ordinador])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[PuntTreball] CHECK CONSTRAINT [FK_PuntTreball_RefOrd]
GO
ALTER TABLE [dbo].[PuntTreball]  WITH CHECK ADD  CONSTRAINT [FK_PuntTreball_RefRat] FOREIGN KEY([ratoli])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[PuntTreball] CHECK CONSTRAINT [FK_PuntTreball_RefRat]
GO
ALTER TABLE [dbo].[PuntTreball]  WITH CHECK ADD  CONSTRAINT [FK_PuntTreball_RefSOpe] FOREIGN KEY([sOpe])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[PuntTreball] CHECK CONSTRAINT [FK_PuntTreball_RefSOpe]
GO
ALTER TABLE [dbo].[PuntTreball]  WITH CHECK ADD  CONSTRAINT [FK_PuntTreball_RefTau] FOREIGN KEY([tauleta])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[PuntTreball] CHECK CONSTRAINT [FK_PuntTreball_RefTau]
GO
ALTER TABLE [dbo].[PuntTreball]  WITH CHECK ADD  CONSTRAINT [FK_PuntTreball_RefTec] FOREIGN KEY([teclat])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[PuntTreball] CHECK CONSTRAINT [FK_PuntTreball_RefTec]
GO
ALTER TABLE [dbo].[PuntTreball]  WITH CHECK ADD  CONSTRAINT [FK_PuntTreball_RefTelf] FOREIGN KEY([telf])
REFERENCES [dbo].[Referencia] ([idRef])
GO
ALTER TABLE [dbo].[PuntTreball] CHECK CONSTRAINT [FK_PuntTreball_RefTelf]
GO
ALTER TABLE [dbo].[Referencia]  WITH CHECK ADD  CONSTRAINT [FK_Referencia_FamReferencia] FOREIGN KEY([idFamRef])
REFERENCES [dbo].[FamReferencia] ([idFamRef])
GO
ALTER TABLE [dbo].[Referencia] CHECK CONSTRAINT [FK_Referencia_FamReferencia]
GO
ALTER TABLE [dbo].[Referencia]  WITH CHECK ADD  CONSTRAINT [FK_Referencia_UbiDriver] FOREIGN KEY([ubiDriv])
REFERENCES [dbo].[Ubicacio] ([idUbicacio])
GO
ALTER TABLE [dbo].[Referencia] CHECK CONSTRAINT [FK_Referencia_UbiDriver]
GO
ALTER TABLE [dbo].[Referencia]  WITH CHECK ADD  CONSTRAINT [FK_Referencia_UbiGuardat] FOREIGN KEY([guardat])
REFERENCES [dbo].[Ubicacio] ([idUbicacio])
GO
ALTER TABLE [dbo].[Referencia] CHECK CONSTRAINT [FK_Referencia_UbiGuardat]
GO
ALTER TABLE [dbo].[Usuari]  WITH CHECK ADD  CONSTRAINT [FK_Usuari_Bustia] FOREIGN KEY([bustia])
REFERENCES [dbo].[Bustia] ([idBustia])
GO
ALTER TABLE [dbo].[Usuari] CHECK CONSTRAINT [FK_Usuari_Bustia]
GO
ALTER TABLE [dbo].[Usuari]  WITH CHECK ADD  CONSTRAINT [FK_Usuari_Bustia2] FOREIGN KEY([bustia2])
REFERENCES [dbo].[Bustia] ([idBustia])
GO
ALTER TABLE [dbo].[Usuari] CHECK CONSTRAINT [FK_Usuari_Bustia2]
GO
ALTER TABLE [dbo].[Usuari]  WITH CHECK ADD  CONSTRAINT [FK_Usuari_Departament] FOREIGN KEY([deptAssig])
REFERENCES [dbo].[Departament] ([idDepartament])
GO
ALTER TABLE [dbo].[Usuari] CHECK CONSTRAINT [FK_Usuari_Departament]
GO
ALTER TABLE [dbo].[Usuari]  WITH CHECK ADD  CONSTRAINT [FK_Usuari_Departament2] FOREIGN KEY([deptAssig2])
REFERENCES [dbo].[Departament] ([idDepartament])
GO
ALTER TABLE [dbo].[Usuari] CHECK CONSTRAINT [FK_Usuari_Departament2]
GO
ALTER TABLE [dbo].[Usuari]  WITH CHECK ADD  CONSTRAINT [FK_Usuari_TipusUsuari] FOREIGN KEY([tipUsu])
REFERENCES [dbo].[TipusUsuari] ([idTipUsu])
GO
ALTER TABLE [dbo].[Usuari] CHECK CONSTRAINT [FK_Usuari_TipusUsuari]
GO
ALTER TABLE [dbo].[Auditoria]  WITH CHECK ADD  CONSTRAINT [CK_Auditoria] CHECK  (([tipusLog]='E' OR [tipusLog]='R'))
GO
ALTER TABLE [dbo].[Auditoria] CHECK CONSTRAINT [CK_Auditoria]
GO
ALTER TABLE [dbo].[HistPuntTreb]  WITH CHECK ADD  CONSTRAINT [CK_HistPuntTreb] CHECK  (([accio]='A' OR [accio]='B' OR [accio]='M'))
GO
ALTER TABLE [dbo].[HistPuntTreb] CHECK CONSTRAINT [CK_HistPuntTreb]
GO
ALTER TABLE [dbo].[HistUsu]  WITH CHECK ADD  CONSTRAINT [CK_HistUsu] CHECK  (([accio]='A' OR [accio]='B' OR [accio]='M'))
GO
ALTER TABLE [dbo].[HistUsu] CHECK CONSTRAINT [CK_HistUsu]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Error/Registre' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Auditoria', @level2type=N'CONSTRAINT',@level2name=N'CK_Auditoria'
GO
USE [master]
GO
ALTER DATABASE [dbInventari] SET  READ_WRITE 
GO
