USE [LGPGest]
/****** Object:  Table [dbo].[Auth]    Script Date: 06/09/2018 15:38:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Auth](
	[Login] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Auth] PRIMARY KEY CLUSTERED
(
	[Login] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Caracteristicos]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Caracteristicos](
	[ID_Caract] [int] NOT NULL,
	[Cod_Tipo] [int] NOT NULL,
	[Caracteristica] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Caracteristicos] PRIMARY KEY CLUSTERED
(
	[ID_Caract] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Departamento]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Departamento](
	[ID_Departamento] [int] NOT NULL,
	[Nome] [varchar](50) NULL,
 CONSTRAINT [PK_Departamento] PRIMARY KEY CLUSTERED
(
	[ID_Departamento] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Equip_Caract]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Equip_Caract](
	[ID_Equipamento] [int] NOT NULL,
	[ID_Caracateristica] [int] NOT NULL,
	[Valor] [varchar](50) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Equip_Depart]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Equip_Depart](
	[ID_Departamento] [int] NULL,
	[ID_Equipamento] [int] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Equipamento]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Equipamento](
	[ID_Equipamento] [int] NOT NULL,
	[Cod_Tipo] [int] NOT NULL,
	[ID_Local] [int] NOT NULL,
	[Estado] [varchar](50) NULL,
	[ID_Departamento] [int] NULL,
	[ID_Marca] [int] NULL,
	[ID_Modelo] [int] NULL,
	[Status] [varchar](50) NULL,
 CONSTRAINT [PK_Equipamento] PRIMARY KEY CLUSTERED
(
	[ID_Equipamento] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Func]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Func](
	[ID_Departamento] [int] NOT NULL,
	[Nome] [varchar](50) NULL,
	[Site] [varchar](50) NULL,
 CONSTRAINT [PK_Funcionario] PRIMARY KEY CLUSTERED
(
	[ID_Departamento] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Historico]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Historico](
	[ID_Registo] [int] NOT NULL,
	[Descrição] [varchar](50) NULL,
	[Movimento] [varchar](50) NOT NULL,
	[Data] [varchar](50) NULL,
	[Cod_Tipo] [int] NOT NULL,
 CONSTRAINT [PK_Historico] PRIMARY KEY CLUSTERED
(
	[ID_Registo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ImgList]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ImgList](
	[Cod_Tipo] [int] NOT NULL,
	[ID_Img] [int] NOT NULL,
 CONSTRAINT [PK_ImgList] PRIMARY KEY CLUSTERED
(
	[Cod_Tipo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Locais]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Locais](
	[ID_Local] [int] NOT NULL,
	[Login] [varchar](50) NULL,
	[Nome] [varchar](50) NULL,
	[Endereço] [varchar](50) NULL,
	[Ref] [varchar](50) NULL,
	[IXS] [varchar](50) NULL,
	[Telefone] [int] NULL,
	[Telemovel] [int] NULL,
	[Pass] [varchar](50) NULL,
	[Tipo] [nvarchar](50) NULL,
	[DataAbre] [date] NULL,
	[DataCria] [date] NULL,
	[Site] [varchar](50) NULL,
	[ID_Servico] [int] NULL,
	[Estado] [varchar](50) NULL,
 CONSTRAINT [PK_Loja] PRIMARY KEY CLUSTERED
(
	[ID_Local] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Marcas]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Marcas](
	[ID_Marca] [int] NOT NULL,
	[Cod_Tipo] [int] NOT NULL,
	[Marca] [varchar](50) NOT NULL,
	[Status] [varchar](50) NULL,
 CONSTRAINT [PK_Marcas] PRIMARY KEY CLUSTERED
(
	[ID_Marca] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Modelos]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Modelos](
	[ID_Modelo] [int] NOT NULL,
	[ID_Marca] [int] NOT NULL,
	[Modelo] [varchar](50) NOT NULL,
	[Status] [varchar](50) NULL,
 CONSTRAINT [PK_Modelos] PRIMARY KEY CLUSTERED
(
	[ID_Modelo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Operadora]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Operadora](
	[ID_Operadora] [int] NOT NULL,
	[Nome] [varchar](50) NOT NULL,
	[Linha_de_Suporte] [varchar](50) NULL,
	[Status] [varchar](50) NULL,
 CONSTRAINT [PK_Operadora] PRIMARY KEY CLUSTERED
(
	[ID_Operadora] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Servicos]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Servicos](
	[ID_Servico] [int] NOT NULL,
	[Hostname] [varchar](50) NULL,
	[Instancia] [varchar](max) NULL,
	[modelo] [varchar](50) NULL,
	[Serial] [varchar](max) NULL,
	[ID_Acesso] [varchar](50) NULL,
	[ID_Acesso2] [varchar](50) NULL,
	[Descricao] [varchar](50) NULL,
	[Descricao2] [varchar](50) NULL,
	[Debito] [varchar](50) NULL,
	[Debito2] [varchar](50) NULL,
	[VRF] [varchar](50) NULL,
	[VLAN] [varchar](50) NULL,
	[IPWAN] [varchar](50) NULL,
	[Rede_Anunc] [varchar](50) NULL,
	[Rede_Anunc2] [varchar](50) NULL,
	[ID_Operadora] [int] NULL,
	[Estado] [varchar](50) NULL,
 CONSTRAINT [PK_Wan] PRIMARY KEY CLUSTERED
(
	[ID_Servico] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Tipo]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tipo](
	[Cod_Tipo] [int] NOT NULL,
	[Tipo] [varchar](50) NOT NULL,
	[Status] [varchar](50) NULL,
	[ICO] [image] NULL,
 CONSTRAINT [PK_Tipo] PRIMARY KEY CLUSTERED
(
	[Cod_Tipo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Userstype]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Userstype](
	[Username] [varchar](50) NOT NULL,
	[Password] [varchar](50) NOT NULL,
	[Tipo] [varchar](50) NOT NULL,
	[Estado] [varchar](50) NULL,
 CONSTRAINT [PK_Userstype] PRIMARY KEY CLUSTERED
(
	[Username] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Caracteristicos]  WITH CHECK ADD  CONSTRAINT [FK_Caracteristicos_Tipo] FOREIGN KEY([Cod_Tipo])
REFERENCES [dbo].[Tipo] ([Cod_Tipo])
GO
ALTER TABLE [dbo].[Caracteristicos] CHECK CONSTRAINT [FK_Caracteristicos_Tipo]
GO
ALTER TABLE [dbo].[Equip_Caract]  WITH CHECK ADD  CONSTRAINT [FK_Equip_Caract_Caracteristicos] FOREIGN KEY([ID_Caracateristica])
REFERENCES [dbo].[Caracteristicos] ([ID_Caract])
GO
ALTER TABLE [dbo].[Equip_Caract] CHECK CONSTRAINT [FK_Equip_Caract_Caracteristicos]
GO
ALTER TABLE [dbo].[Equipamento]  WITH CHECK ADD  CONSTRAINT [FK_Equipamento_Departamento] FOREIGN KEY([ID_Departamento])
REFERENCES [dbo].[Departamento] ([ID_Departamento])
GO
ALTER TABLE [dbo].[Equipamento] CHECK CONSTRAINT [FK_Equipamento_Departamento]
GO
ALTER TABLE [dbo].[Equipamento]  WITH CHECK ADD  CONSTRAINT [FK_Equipamento_Funcionario] FOREIGN KEY([ID_Departamento])
REFERENCES [dbo].[Func] ([ID_Departamento])
GO
ALTER TABLE [dbo].[Equipamento] CHECK CONSTRAINT [FK_Equipamento_Funcionario]
GO
ALTER TABLE [dbo].[Equipamento]  WITH CHECK ADD  CONSTRAINT [FK_Equipamento_Locais] FOREIGN KEY([ID_Local])
REFERENCES [dbo].[Locais] ([ID_Local])
GO
ALTER TABLE [dbo].[Equipamento] CHECK CONSTRAINT [FK_Equipamento_Locais]
GO
ALTER TABLE [dbo].[Equipamento]  WITH CHECK ADD  CONSTRAINT [FK_Equipamento_Marcas] FOREIGN KEY([ID_Marca])
REFERENCES [dbo].[Marcas] ([ID_Marca])
GO
ALTER TABLE [dbo].[Equipamento] CHECK CONSTRAINT [FK_Equipamento_Marcas]
GO
ALTER TABLE [dbo].[Equipamento]  WITH CHECK ADD  CONSTRAINT [FK_Equipamento_Modelos] FOREIGN KEY([ID_Modelo])
REFERENCES [dbo].[Modelos] ([ID_Modelo])
GO
ALTER TABLE [dbo].[Equipamento] CHECK CONSTRAINT [FK_Equipamento_Modelos]
GO
ALTER TABLE [dbo].[Equipamento]  WITH CHECK ADD  CONSTRAINT [FK_Equipamento_Tipo] FOREIGN KEY([Cod_Tipo])
REFERENCES [dbo].[Tipo] ([Cod_Tipo])
GO
ALTER TABLE [dbo].[Equipamento] CHECK CONSTRAINT [FK_Equipamento_Tipo]
GO
ALTER TABLE [dbo].[Locais]  WITH CHECK ADD  CONSTRAINT [FK_Locais_Servicos] FOREIGN KEY([ID_Servico])
REFERENCES [dbo].[Servicos] ([ID_Servico])
GO
ALTER TABLE [dbo].[Locais] CHECK CONSTRAINT [FK_Locais_Servicos]
GO
ALTER TABLE [dbo].[Locais]  WITH CHECK ADD  CONSTRAINT [FK_Loja_Auth] FOREIGN KEY([Login])
REFERENCES [dbo].[Auth] ([Login])
GO
ALTER TABLE [dbo].[Locais] CHECK CONSTRAINT [FK_Loja_Auth]
GO
ALTER TABLE [dbo].[Marcas]  WITH CHECK ADD  CONSTRAINT [FK_Marcas_Tipo] FOREIGN KEY([Cod_Tipo])
REFERENCES [dbo].[Tipo] ([Cod_Tipo])
GO
ALTER TABLE [dbo].[Marcas] CHECK CONSTRAINT [FK_Marcas_Tipo]
GO
ALTER TABLE [dbo].[Modelos]  WITH CHECK ADD  CONSTRAINT [FK_Modelos_Marcas] FOREIGN KEY([ID_Marca])
REFERENCES [dbo].[Marcas] ([ID_Marca])
GO
ALTER TABLE [dbo].[Modelos] CHECK CONSTRAINT [FK_Modelos_Marcas]
GO
ALTER TABLE [dbo].[Servicos]  WITH CHECK ADD  CONSTRAINT [FK_Servicos_Operadora] FOREIGN KEY([ID_Operadora])
REFERENCES [dbo].[Operadora] ([ID_Operadora])
GO
ALTER TABLE [dbo].[Servicos] CHECK CONSTRAINT [FK_Servicos_Operadora]
GO
/****** Object:  StoredProcedure [dbo].[AddLinha]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[AddLinha]
@Linha varchar(50),
@Nome varchar(50)

as
if not exists(Select Nome,Linha_de_Suporte from Operadora where Nome = @Nome and Linha_de_Suporte = @Linha)
begin
insert into Operadora(ID_Operadora,Nome,Linha_de_Suporte)
values((select top 1 ID_Operadora+1 from Operadora order by ID_Operadora desc),@Nome,@Linha)

insert into Historico
values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),@Nome + ' - ' + @Linha ,'Foi Inserido a: ',SYSDATETIME(),0)
end
begin

return -1
end
GO
/****** Object:  StoredProcedure [dbo].[AddLocal]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE proc [dbo].[AddLocal]

(@Login varchar(50),
@Nome varchar(50),
@Endereco varchar(50),
@Ref varchar(50),
@Telefone int,
@Telemovel int,
@Pass varchar(50),
@Tipo varchar(50),
@DataAbert date,
@DataCria date,
@Pais varchar(50))
as
declare @Id int, @LastID int

Begin
if not exists (select top  1 ID_Local+1 from Locais order by ID_Local desc)
    Begin
     insert into Locais values(1,@Login, @Nome,@Endereco,@Ref, Null, @Telefone, @Telemovel, @Pass, @Tipo, @DataAbert, @DataCria, @Pais, Null, 'Ativo')
    End
Else
    Begin
	       set @Id = (select top  1 ID_Local+1 from Locais order by ID_Local desc)
           insert into Locais values(@Id,@Login, @Nome,@Endereco,@Ref, Null, @Telefone, @Telemovel, @Pass, @Tipo, @DataAbert, @DataCria, @Pais, Null, 'Ativo')
    End
End
if not exists (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
    Begin
            insert into Historico values(1, 'Local: '+@Nome, 'Foi criado a:',SYSDATETIME(), 1)
    End
Else
    Begin
	       set @LastID = (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
           insert into Historico values(@LastID, 'Local: '+@Nome, 'Foi criado a:',SYSDATETIME(), 1)
    End
GO
/****** Object:  StoredProcedure [dbo].[AddLoginLocal]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[AddLoginLocal]
(@Login varchar(50))
as
 insert into Auth values(@Login)



GO
/****** Object:  StoredProcedure [dbo].[AddLoginLocalModificar]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


create proc [dbo].[AddLoginLocalModificar]
(@Login varchar(50),
@Nome varchar(50))
as
 if not exists (select Login from Auth where Login=@Login)
    Begin
      insert into Auth values(@Login)
    End
Else
    Begin
	        update Locais set Login = @Login  where Nome=@Nome
    End

GO
/****** Object:  StoredProcedure [dbo].[AddParamCarac]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[AddParamCarac]
@Type varchar(50),
@carac varchar(50)


as


if not exists (select top 1 ID_Caract + 1 from Caracteristicos order by ID_Caract desc)
    Begin
     insert into Caracteristicos(ID_Caract,Cod_Tipo,Caracteristica)
 VALUES(1,(select distinct Cod_Tipo from Tipo where Tipo = @Type),@carac)
    End
	else
	if not exists(Select Cod_Tipo,Caracteristica from Caracteristicos where Caracteristica = @carac and Cod_Tipo = (select Cod_Tipo from Tipo where Tipo = @Type))
begin
	begin
insert into Caracteristicos(ID_Caract,Cod_Tipo,Caracteristica)
 VALUES((select top 1 ID_Caract+1 from Caracteristicos order by ID_Caract desc),(select distinct Cod_Tipo from Tipo where Tipo = @Type),@carac)
 end
 end
 begin

return -1
end
GO
/****** Object:  StoredProcedure [dbo].[AddServico]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[AddServico]

(@hostname varchar(50),
@instancia varchar(50),
@modelo varchar(50),
@serial varchar(50),
@idAcesso varchar(50),
@idAcesso2 varchar(50),
@descricao varchar(50),
@descricao2 varchar(50),
@debito varchar(50),
@debito2 varchar(50),
@VRF varchar(50),
@VLAN varchar(50),
@ipWan varchar(50),
@redeAnunc varchar(50),
@redeAnunc2 varchar(50),
@idOperadora int,
@local varchar(50))
as
declare @Id int, @LastID int


Begin
if not exists (select top  1 ID_Servico+1 from Servicos order by ID_Servico desc)
    Begin
            insert into Servicos values(1,@hostname, @instancia,@modelo ,@serial, @idAcesso, @idAcesso2, @descricao, @descricao2,@debito, @debito2, @VRF, @VLAN, @ipWan, @redeAnunc,@redeAnunc2, @idOperadora+1, 'Ativo' )
    End
Else
    Begin
	       set @Id = (select top  1 ID_Servico+1 from Servicos order by ID_Servico desc)
           insert into Servicos values(@Id,@hostname, @instancia,@modelo ,@serial, @idAcesso, @idAcesso2, @descricao, @descricao2,@debito, @debito2, @VRF, @VLAN, @ipWan, @redeAnunc,@redeAnunc2, @idOperadora+1, 'Ativo' )
    End
End
if not exists (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
    Begin
            insert into Historico values(1, 'Servico de: '+@local,'Foi criado a:', SYSDATETIME(), 2)
    End
Else
    Begin
	       set @LastID = (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
           insert into Historico values(@LastID, 'Servico de: '+@local,'Foi criado a:', SYSDATETIME(), 2)
    End

GO
/****** Object:  StoredProcedure [dbo].[AddServicoLocal]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



create proc [dbo].[AddServicoLocal]
(@IXS varchar(50),  @nome varchar(50))
as
declare @idServico int
select top  1 @idServico = ID_Servico  from Servicos order by ID_Servico desc
update Locais set IXS=@IXS, ID_Servico = @idServico where ID_Local = (select ID_Local from Locais where Nome = @nome)



GO
/****** Object:  StoredProcedure [dbo].[AddToHist]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[AddToHist]
@Nome varchar(50),
@Linha varchar(50)
as
insert into Historico
values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),@Nome + ' - ' + @Linha,'Foi Desativado a: ',SYSDATETIME(),0)
GO
/****** Object:  StoredProcedure [dbo].[AddType]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[AddType]
@Type varchar(50)
as
if not exists(Select TOP 1 Cod_Tipo FROM Tipo)
begin
insert into Tipo (Cod_Tipo,Tipo,Status)
values((0),UPPER(@Type),'Ativo')

END
if not exists(Select Tipo from Tipo where Tipo = @Type)
begin
insert into Tipo (Cod_Tipo,Tipo,Status)
values((select top 1 Cod_Tipo+1 from Tipo order by Cod_Tipo desc),UPPER(@Type),'Ativo')


insert into Historico
values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),upper(@Type),'Foi Inserido a: ',SYSDATETIME(),0)

END

begin

return -1
end
GO
/****** Object:  StoredProcedure [dbo].[AddUSer]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[AddUSer]

(@username varchar(50),
@pass varchar(50),
@tipo varchar(50))
as
declare @LastID int
begin
     insert into Userstype values(@username,@pass, @tipo, 'Ativo' )
end
if not exists (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
    Begin
            insert into Historico values(1, 'User: '+@username, 'Foi criado a:',SYSDATETIME(), 3)
    End
Else
    Begin
	       set @LastID = (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
           insert into Historico values(@LastID, 'User: '+@username, 'Foi criado a:',SYSDATETIME(), 3)
    End
GO
/****** Object:  StoredProcedure [dbo].[ConsultEquipDetails]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[ConsultEquipDetails]

as
SELECT *
FROM [LGPGestTesteBKuserADmin].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Equipamento'
GO
/****** Object:  StoredProcedure [dbo].[ConsultLastID]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  proc [dbo].[ConsultLastID]



as

SELECT TOP 1 ID_Equipamento FROM Equipamento ORDER BY ID_Equipamento DESC

GO
/****** Object:  StoredProcedure [dbo].[ConsultLastIDByTable]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[ConsultLastIDByTable]


as

SELECT TOP 1 * FROM Equipamento ORDER BY ID_Equipamento DESC
GO
/****** Object:  StoredProcedure [dbo].[CountEquipByType]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[CountEquipByType]
@ID_Loja int,
@Type varchar(50)

as




select count(*) from Equipamento
inner join Tipo on Equipamento.Cod_Tipo = Tipo.Cod_Tipo
where Tipo.Tipo =  @Type and ID_Local = @ID_Loja and Equipamento.Status = 'Ativo'
GO
/****** Object:  StoredProcedure [dbo].[CountMarca]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[CountMarca]
@marca varchar(50),
@idtype int,
@id_loja int
as
select count(*) from Equipamento
inner join Marcas on Equipamento.ID_Marca = Marcas.ID_Marca
 where Marca = @marca and ID_Local = @id_loja and Equipamento.Cod_Tipo = @idtype and Status = 'Ativo'
GO
/****** Object:  StoredProcedure [dbo].[CountModel]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[CountModel]
@Model varchar(50),
@Marca varchar(50),
@Type int,
@id int
as
select COUNT(*) from Equipamento

 where Equipamento.ID_Modelo = (select ID_Modelo from Modelos where Modelo = @Model) and
ID_Marca = (select ID_Marca from Marcas where Marca = @Marca and Cod_Tipo = @Type) and ID_Local = @ID
GO
/****** Object:  StoredProcedure [dbo].[DeacLinha]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[DeacLinha]
@Nome varchar(50),
@Linha varchar(50)

as
update Operadora
set Status = 'Inativo'
where ID_Operadora = (select ID_Operadora from Operadora where Nome = @Nome )
exec AddToHist @Nome,@Linha
GO
/****** Object:  StoredProcedure [dbo].[DeactMarca]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[DeactMarca]

@Type varchar(50),
@Marca varchar(50)
as
declare @id int
set @id = (select Cod_Tipo from Tipo where Tipo = @Type )

update Marcas
set Status = 'Inativo'
where Cod_Tipo = @id and Marca = @Marca
insert into Historico
values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),upper(@Type),'Foi Eliminado a: ',SYSDATETIME(),0)
GO
/****** Object:  StoredProcedure [dbo].[DeactModel]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[DeactModel]

@Type varchar(50),
@Model varchar(50),
@Marca varchar(50)
as
declare @id int
set @id = (select ID_Marca from Marcas where Marcas.Cod_Tipo = (Select Cod_Tipo from Tipo where Tipo = @Type) and Marca = @Marca)

update Modelos
set Status = 'Inativo'
where ID_Marca = @id and Modelo = @Model
insert into Historico
values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),upper(@Type),'Foi Eliminado a: ',SYSDATETIME(),0)
GO
/****** Object:  StoredProcedure [dbo].[DeactType]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[DeactType]

@Type varchar(50)
as
declare @id int
set @id = (select Cod_Tipo from Tipo where Tipo = @Type )

update Tipo
set Tipo.Status = 'Inativo'
where Cod_Tipo = @id
insert into Historico
values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),upper(@Type),'Foi Eliminado a: ',SYSDATETIME(),0)
GO
/****** Object:  StoredProcedure [dbo].[DelCarac]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[DelCarac]
@type varchar(50),
@Carac varchar(50)
as
declare @id int
set @id= (select ID_Caract from Caracteristicos where Cod_Tipo = (select distinct Cod_Tipo from Tipo where Tipo = @type) and Caracteristica = @Carac )
delete from Caracteristicos where Caracteristica = @Carac and ID_Caract = @id
GO
/****** Object:  StoredProcedure [dbo].[DelEquipByID]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[DelEquipByID]
@ID int
as
declare @cod int
declare @idequip varchar(50)
declare @descript varchar(50)
set @cod = (SELECT Cod_Tipo FROM Equipamento WHERE ID_Equipamento = @ID)
set @descript = (Select Tipo from Tipo where Cod_Tipo = @cod)
set @idequip = convert(varchar,@ID)
update Equipamento
set Status = 'Inactive'
where ID_Equipamento = @ID

insert into Historico
values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),@idequip + ' - ' + @descript,'Foi Eliminado a: ',SYSDATETIME(),0)
GO
/****** Object:  StoredProcedure [dbo].[DelEquipVals]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[DelEquipVals]
@id int
as

delete from Equip_Caract
where ID_Equipamento = @id
GO
/****** Object:  StoredProcedure [dbo].[DeleteLocalByID]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE
pRoCeDuRe [dbo].[DeleteLocalByID]
@id int,
@login varchar(50)
as
declare @idServico int, @NomeLocal varchar(50), @LastID int, @localServico varchar(50)
set @idServico = (select ID_Servico from Locais WHERE ID_Local = @id)
set @localServico = (select Nome from Locais where ID_Servico=@idServico)
update Equipamento set ID_Local = 91, Estado = 'Activo | Em Stock' where ID_Local = @id
update Servicos set Estado = 'Inativo' where ID_Servico = @idServico
update Locais set Estado= 'Inativo' where ID_Local = @id

set @NomeLocal = (select Nome From Locais where ID_Local= @id)
Begin
if not exists (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
    Begin
            insert into Historico values(1, 'Local: '+@NomeLocal, 'Foi desativado a:',SYSDATETIME(), 1)
    End
Else
    Begin
	       set @LastID = (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
           insert into Historico values(@LastID,'Local: '+@NomeLocal, 'Foi desativado a:',SYSDATETIME(), 1)
    End
End
set @LastID = (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
insert into Historico values(@LastID, 'Servico de: '+@localServico,'Foi desativado a: ', SYSDATETIME(), 2)

GO
/****** Object:  StoredProcedure [dbo].[DeleteLocalByID2]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE
pRoCeDuRe [dbo].[DeleteLocalByID2]
@id int,
@login varchar(50)
as
declare @NomeLocal varchar(50), @LastID int

update Equipamento set ID_Local = 91, Estado = 'Activo | Em Stock' where ID_Local = @id

update Locais set Estado= 'Inativo' where ID_Local = @id
set @NomeLocal = (select Nome From Locais where ID_Local= @id)
Begin
if not exists (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
    Begin
            insert into Historico values(1, 'Local: '+@NomeLocal, 'Foi desativado a:',SYSDATETIME(), 1)
    End
Else
    Begin
	       set @LastID = (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
           insert into Historico values(@LastID, 'Local: '+@NomeLocal, 'Foi desativado a:',SYSDATETIME(), 1)
    End
End
GO
/****** Object:  StoredProcedure [dbo].[DeleteServicoByID]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[DeleteServicoByID]
(@id int)
as
declare @LastID int, @NomeLocal varchar(50)
set @NomeLocal = (select Nome From Locais where ID_Servico=@id)
 update Locais set IXS = null, ID_Servico = null  where ID_Servico=@id
 update Servicos set Estado = 'Desativado' where ID_Servico = @id
 Begin
if not exists (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
    Begin
            insert into Historico values(1, 'Servico do local: '+@NomeLocal,'Foi desativado a: ' ,SYSDATETIME(), 2)
    End
Else
    Begin
	       set @LastID = (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
           insert into Historico values(@LastID, 'Servico do local: '+@NomeLocal,'Foi desativado a: ' ,SYSDATETIME(), 2)
    End
End
GO
/****** Object:  StoredProcedure [dbo].[DeleteServicoByLocal]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE pRoCeDuRe [dbo].[DeleteServicoByLocal]
@id int
as
declare @idServico int
set @idServico = (select ID_Servico from Locais WHERE ID_Local = @id)
delete from Servicos where ID_Servico = @idServico
GO
/****** Object:  StoredProcedure [dbo].[DeleteType]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[DeleteType]

@Tipo varchar(50)
as
delete from Tipo
where Cod_Tipo = (select Cod_Tipo from Tipo where Tipo = @Tipo)
delete from Equipamento
where Cod_Tipo = (select Cod_Tipo from Tipo where Tipo = @Tipo)

insert into Historico
values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),@Tipo,'Foi Eliminado a: ',SYSDATETIME(),0)
GO
/****** Object:  StoredProcedure [dbo].[DeleteUserByName]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE pRoCeDuRe [dbo].[DeleteUserByName]
(@username varchar(50))
as
declare @LastID int
 update Userstype set Estado = 'Inativo' where Username = @username
 if not exists (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
    Begin
            insert into Historico values(1, 'User: '+@username, 'Foi desativado a:',SYSDATETIME(), 3)
    End
Else
    Begin
	       set @LastID = (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
           insert into Historico values(@LastID,'User: '+@username, 'Foi desativado a:',SYSDATETIME(), 3)
    End
GO
/****** Object:  StoredProcedure [dbo].[GetLastIDLocal]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[GetLastIDLocal]
as
SELECT TOP 1 ID_Local FROM Locais ORDER BY ID_Local DESC
GO
/****** Object:  StoredProcedure [dbo].[InsertEquip]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[InsertEquip]
@Type VARCHAR(50),
@Marca varchar(50),
@Modelo varchar(50),
@Local varchar(50),
@Estado varchar(50)

as
declare  @ID int, @Tipo int,@ID_Local int
set @ID = (SELECT top 1 id_equipamento +1 from Equipamento order by ID_Equipamento desc)
set @Tipo = (Select Cod_Tipo from Tipo where Tipo.Tipo LIKE @Type)
set @ID_Local = (select ID_Local from Locais where Nome like @Local)

if not exists (select top  1 ID_Equipamento+1 from Equipamento order by ID_Equipamento desc)
    Begin
	insert into Equipamento (ID_Equipamento,Cod_Tipo,ID_Marca,ID_Modelo,ID_Local,Estado,Status)

values(1,@Tipo,(select distinct ID_Marca from Marcas where Marca = @Marca and Cod_Tipo = @Tipo),(select distinct ID_Modelo from Modelos where Modelo = @Modelo),@ID_Local,@Estado,'Ativo')
end
else
begin
insert into Equipamento (ID_Equipamento,Cod_Tipo,ID_Marca,ID_Modelo,ID_Local,Estado,Status)

values(@ID,@Tipo,(select distinct ID_Marca from Marcas where Marca = @Marca and Cod_Tipo = @Tipo),(select distinct ID_Modelo from Modelos where Modelo = @Modelo),@ID_Local,@Estado,'Ativo')
end
begin

return -1
end
GO
/****** Object:  StoredProcedure [dbo].[InsertEquipCarac]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[InsertEquipCarac]
@Type varchar(50),
@carac varchar(50),
@Valor varchar(50)


as
declare @Ncarac int, @IDequip int
set @Ncarac = (select distinct ID_Caract from Caracteristicos

  where Caracteristica =  @carac and Caracteristicos.Cod_Tipo = (select Cod_Tipo from Tipo where Tipo = @Type))


  set @IDequip= (SELECT top 1 id_equipamento from Equipamento order by ID_Equipamento desc)

  if not exists(Select * from Equip_Caract where ID_Caracateristica = @Ncarac and ID_Equipamento = @IDequip and Valor = @Valor)
begin
insert into Equip_Caract(ID_Equipamento,ID_Caracateristica,Valor)
 VALUES(@idEquip,@Ncarac,@Valor)
 begin

return -1
end
 END
GO
/****** Object:  StoredProcedure [dbo].[insertIntoDepar]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[insertIntoDepar]
@idequip int,
@Depart varchar(50)
as

insert into Equip_Depart(ID_Departamento,ID_Equipamento)
values((select ID_Departamento from Departamento where Nome = @Depart),(@idequip))
GO
/****** Object:  StoredProcedure [dbo].[InsertIntoDepart]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[InsertIntoDepart]
@idDEPART int,
@Type VARCHAR(50),
@Marca varchar(50),
@Modelo varchar(50),
@Local varchar(50),
@Estado varchar(50)
as
declare  @ID int, @Tipo int,@ID_Local int
set @ID = (SELECT top 1 id_equipamento +1 from Equipamento order by ID_Equipamento desc)
set @Tipo = (Select Cod_Tipo from Tipo where Tipo.Tipo LIKE @Type)
set @ID_Local = (select Nome from Departamento where ID_Departamento = @idDEPART)
insert into Equipamento (ID_Equipamento,Cod_Tipo,ID_Marca,ID_Modelo,ID_Departamento,Estado)

values(@ID,@Tipo,@Marca,@Modelo,@ID_Local,@Estado)

insert into Equip_Depart values(@idDEPART,(select top 1 ID_Equipamento+1 from Equipamento order by ID_Equipamento desc))
GO
/****** Object:  StoredProcedure [dbo].[InsertMarca]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[InsertMarca]

@Marca varchar(50),
@Tipo varchar(50)

as
if not exists(Select TOP 1 Marca from Marcas)
begin
insert into Marcas values((1),(select distinct Cod_Tipo from Tipo where Tipo = @Tipo),@Marca,'Ativo')
END
if not exists(Select * from Marcas where Marca = @Marca and Cod_Tipo = (select distinct Cod_Tipo from Tipo where Tipo = @Tipo))
begin
insert into Marcas values((select top 1 ID_Marca+1 from Marcas order by ID_Marca desc),(select distinct Cod_Tipo from Tipo where Tipo = @Tipo),@Marca,'Ativo')

insert into Historico
values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),' Marca - '+@Marca,'Foi Inserido a: ',SYSDATETIME(),0)
end
else
begin

return -1
end
GO
/****** Object:  StoredProcedure [dbo].[InsertModel]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[InsertModel]

@Modelo varchar(50),
@Marca varchar(50),
@Tipo varchar(50)

as
DECLARE @COD_TYPO VARCHAR(50)

SET @COD_TYPO = (SELECT COD_TIPO FROM Tipo WHERE Tipo = @Tipo)

if not exists(Select TOP 1 Modelo FROM Modelos)
begin
insert into Modelos values((1),(select distinct ID_Marca from Marcas where Marca = @Marca AND Cod_Tipo = @COD_TYPO),@Modelo,'Ativo')
END

if not exists(Select * from Modelos where Modelo = @Modelo and ID_Marca = (select distinct ID_Marca from Marcas where Marca = @Marca AND Cod_Tipo = @COD_TYPO))
begin

insert into Modelos values((select top 1 ID_Modelo+1 from Modelos order by ID_Modelo desc),(select distinct ID_Marca from Marcas where Marca = @Marca AND Cod_Tipo = @COD_TYPO),@Modelo,'Ativo')

insert into Historico
values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),' Modelo - ' + @Modelo ,'Foi Inserido a: ',SYSDATETIME(),0)
end
begin

return -1
end
GO
/****** Object:  StoredProcedure [dbo].[Linhas]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[Linhas] @Nome varchar(50)
as

select Linha_de_Suporte from dbo.Operadora where Nome = @Nome
GO
/****** Object:  StoredProcedure [dbo].[LoadCaracteristicas]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LoadCaracteristicas]
@Type int
as

select Caracteristica  from Caracteristicos
inner join Tipo on Caracteristicos.Cod_Tipo  = Tipo.Cod_Tipo
 where Tipo.Cod_Tipo = @Type and Tipo.Status = 'Ativo'
GO
/****** Object:  StoredProcedure [dbo].[loaddepart]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[loaddepart]
as
select nome from Departamento
GO
/****** Object:  StoredProcedure [dbo].[loaddepartEquip]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[loaddepartEquip]
as
select * from Equip_Depart
GO
/****** Object:  StoredProcedure [dbo].[LoadEquipLoja]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[LoadEquipLoja]

@id int

as

declare @Type int,@Marca varchar(50),@Modelo varchar(50),@Estado varchar(50)
set @Type =(select Cod_Tipo from Equipamento where Equipamento.ID_Equipamento = @id)
set @Estado = (select Estado from Equipamento where Equipamento.ID_Equipamento = @id)

select  Nome,@Type as Cod, @Estado as status from dbo.Locais
inner join Equipamento on Locais.ID_Local=Equipamento.ID_Local
where Equipamento.ID_Equipamento= @id
GO
/****** Object:  StoredProcedure [dbo].[LoadEquipPerType]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[LoadEquipPerType]
@ID_Loja int,
@Type varchar(50),
@model varchar(50)
as



select * from Equipamento
inner join Tipo on Equipamento.Cod_Tipo = Tipo.Cod_Tipo
where Tipo.Tipo =  @Type and Equipamento.Id_Modelo = (select ID_Modelo from Modelos where Modelo = @model ) and ID_Local = @ID_Loja
and Equipamento.Status = 'Ativo'
GO
/****** Object:  StoredProcedure [dbo].[LoadEquipsHist]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[LoadEquipsHist]
as
select * from Historico where Cod_Tipo=0
GO
/****** Object:  StoredProcedure [dbo].[LoadEquipTree_Carac]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[LoadEquipTree_Carac]
@Tipo varchar(50)
as

select Caracteristica from Caracteristicos
INNER JOIN Tipo ON Caracteristicos.Cod_Tipo = Tipo.Cod_Tipo
where Tipo.Tipo=@Tipo and Tipo.Status = 'Ativo'
GO
/****** Object:  StoredProcedure [dbo].[LoadEquipTree_CaracVal]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[LoadEquipTree_CaracVal]

@id int,
@carac varchar(50)
as

select distinct Valor,ID_Caracateristica from Equip_Caract
inner join Caracteristicos on Equip_Caract.ID_Caracateristica = Caracteristicos.ID_Caract
where ID_Equipamento = @id and Caracteristica = @carac
GO
/****** Object:  StoredProcedure [dbo].[LoadEquipTree_Equips]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[LoadEquipTree_Equips]
@ID int
as

select * from Equipamento
inner join Locais on Equipamento.ID_Local = Locais.ID_Local
where Locais.ID_Local = @ID
GO
/****** Object:  StoredProcedure [dbo].[LoadEquipTree_Lojas]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[LoadEquipTree_Lojas]

as

select * from Locais
where Tipo = 'Loja' or Tipo = 'SEDE'
GO
/****** Object:  StoredProcedure [dbo].[LoadHistorico]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[LoadHistorico]
as
select * from Historico
GO
/****** Object:  StoredProcedure [dbo].[LoadImglist]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[LoadImglist]
@Type int
as

select ID_Img from ImgList where Cod_Tipo = @Type
GO
/****** Object:  StoredProcedure [dbo].[LoadLinhas]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[LoadLinhas]
as

select Nome from dbo.Operadora
GO
/****** Object:  StoredProcedure [dbo].[LoadLocaisByID]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[LoadLocaisByID]
(@nome varchar(50))
as
select * from dbo.Locais where Nome = @nome
GO
/****** Object:  StoredProcedure [dbo].[LoadLocaisHist]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[LoadLocaisHist]
as
select * from Historico where Cod_Tipo=1
GO
/****** Object:  StoredProcedure [dbo].[LoadLojas]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LoadLojas]
as
select * from dbo.Locais  WHERE Estado = 'Ativo'
GO
/****** Object:  StoredProcedure [dbo].[LoadLojasByID]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[LoadLojasByID]
@id int
as
select * from dbo.Locais
where ID_Local = @id
GO
/****** Object:  StoredProcedure [dbo].[LoadLojasName]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LoadLojasName]
as
select Nome from dbo.Locais
GO
/****** Object:  StoredProcedure [dbo].[LoadMarca]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[LoadMarca]
@id_type int
as
select Marca from Marcas where Cod_Tipo = @id_type
and
Status = 'Ativo'
GO
/****** Object:  StoredProcedure [dbo].[LoadModel]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[LoadModel]

@marca varchar(50),
@type varchar(50)

as

declare @id_marca int, @CodTp int
set @CodTp = (select Cod_Tipo From Tipo where Tipo = @type )
set @id_marca = (select ID_Marca from Marcas where Marca = @marca and Cod_Tipo = @CodTp )
select distinct Modelo from Modelos where Modelos.ID_Marca = @id_marca and
Status = 'Ativo'
GO
/****** Object:  StoredProcedure [dbo].[LoadOperadoras]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[LoadOperadoras]
as
select * from Operadora
GO
/****** Object:  StoredProcedure [dbo].[LoadOperadorasName]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[LoadOperadorasName]
as
select Nome from Operadora
GO
/****** Object:  StoredProcedure [dbo].[LoadServicos]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LoadServicos]
as
select * from Servicos where Estado = 'Ativo'
GO
/****** Object:  StoredProcedure [dbo].[LoadServicosByID]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[LoadServicosByID]
@id int
as
select * from Servicos
where ID_Servico = @id

GO
/****** Object:  StoredProcedure [dbo].[LoadServicosByLocal]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE [dbo].[LoadServicosByLocal]
@nome varchar(50)
as
declare @idServico int
Begin
if not exists (select ID_Servico from Locais WHERE Nome = @nome)
    Begin
            set @idServico = null;
    End
Else
    Begin
	       set @idServico = (select ID_Servico from Locais WHERE Nome = @nome)
           select * from Servicos where ID_Servico = @idServico
    End
End
GO
/****** Object:  StoredProcedure [dbo].[LoadServicosHist]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[LoadServicosHist]
as
select * from Historico where Cod_Tipo=2
GO
/****** Object:  StoredProcedure [dbo].[LoadTreeview]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LoadTreeview]

as

select Tipo from dbo.Tipo

where Status = 'Ativo'
GO
/****** Object:  StoredProcedure [dbo].[LoadUsers]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[LoadUsers]
as
select * from Userstype where Estado='Ativo'
GO
/****** Object:  StoredProcedure [dbo].[LoadUsersHist]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



create PROCEDURE [dbo].[LoadUsersHist]
as
select * from Historico where Cod_Tipo=3

GO
/****** Object:  StoredProcedure [dbo].[ResumoEquips]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[ResumoEquips]
@id int
as

select distinct COUNT(*) from Equipamento where ID_Local=@id


GO
/****** Object:  StoredProcedure [dbo].[ResumoEquips2]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[ResumoEquips2]
@id int
as

declare @NumPC int, @NumPDT int, @NumPOS int,@NumPinpad int,@NumTablet int, @NumImpress int
set @NumPC = (select COUNT(ID_Equipamento) as PC from Equipamento inner join Tipo on ID_Local=@id and Equipamento.Cod_Tipo=Tipo.Cod_Tipo where Tipo ='PC' or Tipo='pc' or Tipo='Pc')
set @NumPDT = (select COUNT(ID_Equipamento) as PDT from Equipamento inner join Tipo on ID_Local=@id and Equipamento.Cod_Tipo=Tipo.Cod_Tipo where Tipo ='PDT' or Tipo='pdt' or Tipo='Pdt')
set @NumPOS = (select COUNT(ID_Equipamento) as POS from Equipamento inner join Tipo on ID_Local=@id and Equipamento.Cod_Tipo=Tipo.Cod_Tipo where Tipo ='POS' or Tipo='pos' or Tipo='Pos')
set @NumPinpad = (select COUNT(ID_Equipamento) as POS from Equipamento inner join Tipo on ID_Local=@id and Equipamento.Cod_Tipo=Tipo.Cod_Tipo where Tipo ='PINPAD' or Tipo='pinpad' or Tipo='Pinpad')
set @NumTablet = (select COUNT(ID_Equipamento) as POS from Equipamento inner join Tipo on ID_Local=@id and Equipamento.Cod_Tipo=Tipo.Cod_Tipo where Tipo ='TABLET' or Tipo='tablet' or Tipo='Tablet')
set @NumImpress= (select COUNT(ID_Equipamento) as POS from Equipamento inner join Tipo on ID_Local=@id and Equipamento.Cod_Tipo=Tipo.Cod_Tipo where Tipo ='IMPRESSORA' or Tipo='impressora' or Tipo='Impressora')


select @NumPC, @NumPDT, @NumPOS,@NumPinpad,@NumTablet, @NumImpress


GO
/****** Object:  StoredProcedure [dbo].[ResumoServicos]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[ResumoServicos]
@id int
as
select Nome from Locais where ID_Servico = @id



GO
/****** Object:  StoredProcedure [dbo].[SearchCaracVals]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
cReate PROCEDURE [dbo].[SearchCaracVals]
@id int
as

select distinct Valor from dbo.Equip_Caract
where ID_Caracateristica = @id;
GO
/****** Object:  StoredProcedure [dbo].[SearchEquipGeral]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SearchEquipGeral]

@id int
as
declare  @Local varchar(50)



set @Local  = (select Nome from Locais inner join Equipamento on Locais.ID_Local = Equipamento.ID_Local where ID_Equipamento = @id )
select ID_Equipamento ,@Local as NomeLoja,Marcas.Marca,Modelos.Modelo,Estado from Equipamento

inner join Marcas on Equipamento.ID_Marca = Marcas.ID_Marca
inner join Modelos on Equipamento.ID_Modelo = Modelos.ID_Modelo
where ID_Equipamento = @id
GO
/****** Object:  StoredProcedure [dbo].[SearchLocalByIndex]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[SearchLocalByIndex]
@id int
as

select Nome from Locais where ID_Local=@id
GO
/****** Object:  StoredProcedure [dbo].[SearchLocalServicoByIndex]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



create proc [dbo].[SearchLocalServicoByIndex]
@idServico int
as
 select Nome from Locais where ID_Servico=@idServico


GO
/****** Object:  StoredProcedure [dbo].[SearchLojas]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[SearchLojas]
@Nome varchar(50)
as

select Nome from dbo.Locais where Nome = @Nome
GO
/****** Object:  StoredProcedure [dbo].[SearchLojas2]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[SearchLojas2]
@Name varchar(50)
as

select * from dbo.Locais where Nome like '%'+@name+'%'or Login like '%'+@name+'%' and Estado = 'Ativo'
GO
/****** Object:  StoredProcedure [dbo].[SearchServicos]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[SearchServicos]
@nome varchar(50)
as
declare @idServico int
set @idServico= (select ID_Servico from Locais where Nome=@nome)
select * from Servicos where ID_Servico = @idServico
GO
/****** Object:  StoredProcedure [dbo].[SearchType]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SearchType]
@Type varchar(50)
as

select distinct Caracteristica,ID_Caract from dbo.Caracteristicos
INNER JOIN TIPO ON Caracteristicos.Cod_Tipo = Tipo.Cod_Tipo
where tIPO.Tipo LIKE @Type
GO
/****** Object:  StoredProcedure [dbo].[UpdateEquipCarac]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[UpdateEquipCarac]
@idEquip int,
@carac varchar(50),
@Valor varchar(50)


as
declare @Ncarac int

set @Ncarac = (select distinct ID_Caract from Caracteristicos
inner join Equip_Caract on
 Caracteristicos.ID_Caract = Equip_Caract.ID_Caracateristica
  where Caracteristica = @carac AND ID_Equipamento = @idEquip  )



 if not exists(select distinct ID_Caract from Caracteristicos
inner join Equip_Caract on
 Caracteristicos.ID_Caract = Equip_Caract.ID_Caracateristica
  where Caracteristica = @carac AND ID_Equipamento = @idEquip )
  begin

  insert into Equip_Caract(ID_Caracateristica,ID_Equipamento,Valor) values((select distinct ID_Caract from Caracteristicos where Caracteristica = @carac and Cod_Tipo = (select Cod_Tipo from Equipamento where ID_Equipamento = @idEquip))  ,@idEquip,@Valor)
  end
  else
  begin

update Equip_Caract
set Valor = @Valor



where ID_Equipamento = @idEquip and Equip_Caract.ID_Caracateristica = @Ncarac

end
GO
/****** Object:  StoredProcedure [dbo].[UpdateEquipGeral]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[UpdateEquipGeral]
@id int,
@Type varchar(50),
@Local varchar(50),
@Marca varchar(50),
@Modelo varchar(50),
@Estado varchar(50)
as
declare @Tipo int
set @Tipo = (Select Cod_Tipo from Tipo where Tipo.Tipo LIKE @Type)
update Equipamento
set ID_Local = (select ID_Local from Locais where Nome = @Local) ,
ID_Marca = (select distinct ID_Marca from Marcas where Marca = @Marca and Cod_Tipo = @Tipo),
ID_Modelo = (select distinct ID_Modelo from Modelos where Modelo = @Modelo),
Estado = @Estado
where ID_Equipamento = @id
exec AddToHist @id,@Type
GO
/****** Object:  StoredProcedure [dbo].[UpdateLinha]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[UpdateLinha]
@Linha varchar(50),
@Nome varchar(50)

as

update Operadora
set Linha_de_Suporte = @Linha where Nome = @Nome


insert into Historico
values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),@Nome + ' - ' + @Linha ,'Foi Modificado a: ',SYSDATETIME(),0)
GO
/****** Object:  StoredProcedure [dbo].[UpdateLocal]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[UpdateLocal]


@Login varchar(50),
@Nome varchar(50),
@Endereco varchar(50),
@Ref varchar(50),
@Telefone varchar(50),
@Telemovel varchar(50),
@Pass varchar(50),
@Tipo varchar(50),
@DataAbert date,
@DataCria date,
@Pais varchar(50),
@local varchar(50)

as
declare @LastID int
 update Locais set Login=@Login, Nome=@Nome,Endereço=@Endereco,Ref=@Ref, Telefone=@Telefone, telemovel=@Telemovel, Pass=@Pass, Tipo=@Tipo, DataAbre=@DataAbert,DataCria= @DataCria, Site=@Pais, ID_Servico=Null where Nome = @local
 if not exists (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
    Begin
            insert into Historico values(1, 'Local: '+@Nome,'Foi modificado a:' ,SYSDATETIME(), 1)
    End
Else
    Begin
	       set @LastID = (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
           insert into Historico values(@LastID, 'Local: '+@Nome,'Foi modificado a:' ,SYSDATETIME(), 1)
    End





GO
/****** Object:  StoredProcedure [dbo].[UpdateLocalEquip]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[UpdateLocalEquip]


@id int,
@Nome varchar(50)

as

 update Equipamento set ID_Local = (select ID_Local from Locais where Nome = @Nome) where ID_Equipamento = @id
GO
/****** Object:  StoredProcedure [dbo].[UpdateLoginLocal]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create proc [dbo].[UpdateLoginLocal]
@Login varchar(50),
@Local varchar(50)
as
declare @loginname varchar(50)
insert into Auth values(@Login)

GO
/****** Object:  StoredProcedure [dbo].[UpdateServico]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE proc [dbo].[UpdateServico]


(@hostname varchar(50),
@instancia varchar(50),
@modelo varchar(50),
@serial varchar(50),
@idAcesso varchar(50),
@idAcesso2 varchar(50),
@descricao varchar(50),
@descricao2 varchar(50),
@debito varchar(50),
@debito2 varchar(50),
@vrf varchar(50),
@ipwan varchar(50),
@redeanunc varchar(50),
@redeanunc2 varchar(50),
@nome varchar(50),
@Id_Operadora int
)
as
declare @idServico int, @LastID int
set @idServico = (select ID_Servico from Locais WHERE Nome = @nome)
     update Servicos set Hostname=@hostname, Instancia=@instancia, modelo=@modelo, Serial=@serial, ID_Acesso=@idAcesso, ID_Acesso2=@idAcesso2, Descricao=@descricao, Descricao2=@descricao2,Debito=@debito, Debito2=@debito2,VRF=@vrf, IPWAN = @ipwan, Rede_Anunc=@redeanunc,Rede_Anunc2=@redeanunc2, ID_Operadora=@Id_Operadora+1  where ID_Servico = @idServico
	 update Locais set IXS=@idAcesso where ID_Local = (select ID_Local from Locais where Nome = @nome)
	 if not exists (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
    Begin
            insert into Historico values(1, 'Servico de: '+@nome,'Foi modificado a: ' ,SYSDATETIME(), 2)
    End
Else
    Begin
	       set @LastID = (select top  1 ID_Registo+1 from Historico order by ID_Registo desc)
           insert into Historico values(@LastID, 'Servico de: '+@nome,'Foi modificado a: ' ,SYSDATETIME(), 2)
    End
GO
/****** Object:  StoredProcedure [dbo].[UpdateType]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[UpdateType]
@Valor varchar(50),
@Tipo varchar(50)
as
update Tipo
set Tipo = @Valor
where Cod_Tipo = (select Cod_Tipo from Tipo where Tipo = @Tipo)

insert into Historico
values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),@Tipo,'Foi Modificado a: ',SYSDATETIME(),0)
GO
/****** Object:  StoredProcedure [dbo].[VerificaUser]    Script Date: 06/09/2018 15:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[VerificaUser]
@user varchar(50),
@pass varchar(50)
as

select distinct * from dbo.Userstype where Username = @user and dbo.Userstype.Password = @pass
GO
USE [master]
GO
ALTER DATABASE [LGPGest] SET  READ_WRITE
GO