· 7 years ago · Sep 06, 2018, 03:48 PM
1USE [LGPGest]
2/****** Object: Table [dbo].[Auth] Script Date: 06/09/2018 15:38:34 ******/
3SET ANSI_NULLS ON
4GO
5SET QUOTED_IDENTIFIER ON
6GO
7CREATE TABLE [dbo].[Auth](
8 [Login] [varchar](50) NOT NULL,
9 CONSTRAINT [PK_Auth] PRIMARY KEY CLUSTERED
10(
11 [Login] ASC
12)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
13) ON [PRIMARY]
14GO
15/****** Object: Table [dbo].[Caracteristicos] Script Date: 06/09/2018 15:38:35 ******/
16SET ANSI_NULLS ON
17GO
18SET QUOTED_IDENTIFIER ON
19GO
20CREATE TABLE [dbo].[Caracteristicos](
21 [ID_Caract] [int] NOT NULL,
22 [Cod_Tipo] [int] NOT NULL,
23 [Caracteristica] [varchar](50) NOT NULL,
24 CONSTRAINT [PK_Caracteristicos] PRIMARY KEY CLUSTERED
25(
26 [ID_Caract] ASC
27)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
28) ON [PRIMARY]
29GO
30/****** Object: Table [dbo].[Departamento] Script Date: 06/09/2018 15:38:35 ******/
31SET ANSI_NULLS ON
32GO
33SET QUOTED_IDENTIFIER ON
34GO
35CREATE TABLE [dbo].[Departamento](
36 [ID_Departamento] [int] NOT NULL,
37 [Nome] [varchar](50) NULL,
38 CONSTRAINT [PK_Departamento] PRIMARY KEY CLUSTERED
39(
40 [ID_Departamento] ASC
41)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
42) ON [PRIMARY]
43GO
44/****** Object: Table [dbo].[Equip_Caract] Script Date: 06/09/2018 15:38:35 ******/
45SET ANSI_NULLS ON
46GO
47SET QUOTED_IDENTIFIER ON
48GO
49CREATE TABLE [dbo].[Equip_Caract](
50 [ID_Equipamento] [int] NOT NULL,
51 [ID_Caracateristica] [int] NOT NULL,
52 [Valor] [varchar](50) NULL
53) ON [PRIMARY]
54GO
55/****** Object: Table [dbo].[Equip_Depart] Script Date: 06/09/2018 15:38:35 ******/
56SET ANSI_NULLS ON
57GO
58SET QUOTED_IDENTIFIER ON
59GO
60CREATE TABLE [dbo].[Equip_Depart](
61 [ID_Departamento] [int] NULL,
62 [ID_Equipamento] [int] NULL
63) ON [PRIMARY]
64GO
65/****** Object: Table [dbo].[Equipamento] Script Date: 06/09/2018 15:38:35 ******/
66SET ANSI_NULLS ON
67GO
68SET QUOTED_IDENTIFIER ON
69GO
70CREATE TABLE [dbo].[Equipamento](
71 [ID_Equipamento] [int] NOT NULL,
72 [Cod_Tipo] [int] NOT NULL,
73 [ID_Local] [int] NOT NULL,
74 [Estado] [varchar](50) NULL,
75 [ID_Departamento] [int] NULL,
76 [ID_Marca] [int] NULL,
77 [ID_Modelo] [int] NULL,
78 [Status] [varchar](50) NULL,
79 CONSTRAINT [PK_Equipamento] PRIMARY KEY CLUSTERED
80(
81 [ID_Equipamento] ASC
82)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
83) ON [PRIMARY]
84GO
85/****** Object: Table [dbo].[Func] Script Date: 06/09/2018 15:38:35 ******/
86SET ANSI_NULLS ON
87GO
88SET QUOTED_IDENTIFIER ON
89GO
90CREATE TABLE [dbo].[Func](
91 [ID_Departamento] [int] NOT NULL,
92 [Nome] [varchar](50) NULL,
93 [Site] [varchar](50) NULL,
94 CONSTRAINT [PK_Funcionario] PRIMARY KEY CLUSTERED
95(
96 [ID_Departamento] ASC
97)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
98) ON [PRIMARY]
99GO
100/****** Object: Table [dbo].[Historico] Script Date: 06/09/2018 15:38:35 ******/
101SET ANSI_NULLS ON
102GO
103SET QUOTED_IDENTIFIER ON
104GO
105CREATE TABLE [dbo].[Historico](
106 [ID_Registo] [int] NOT NULL,
107 [Descrição] [varchar](50) NULL,
108 [Movimento] [varchar](50) NOT NULL,
109 [Data] [varchar](50) NULL,
110 [Cod_Tipo] [int] NOT NULL,
111 CONSTRAINT [PK_Historico] PRIMARY KEY CLUSTERED
112(
113 [ID_Registo] ASC
114)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
115) ON [PRIMARY]
116GO
117/****** Object: Table [dbo].[ImgList] Script Date: 06/09/2018 15:38:35 ******/
118SET ANSI_NULLS ON
119GO
120SET QUOTED_IDENTIFIER ON
121GO
122CREATE TABLE [dbo].[ImgList](
123 [Cod_Tipo] [int] NOT NULL,
124 [ID_Img] [int] NOT NULL,
125 CONSTRAINT [PK_ImgList] PRIMARY KEY CLUSTERED
126(
127 [Cod_Tipo] ASC
128)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
129) ON [PRIMARY]
130GO
131/****** Object: Table [dbo].[Locais] Script Date: 06/09/2018 15:38:35 ******/
132SET ANSI_NULLS ON
133GO
134SET QUOTED_IDENTIFIER ON
135GO
136CREATE TABLE [dbo].[Locais](
137 [ID_Local] [int] NOT NULL,
138 [Login] [varchar](50) NULL,
139 [Nome] [varchar](50) NULL,
140 [Endereço] [varchar](50) NULL,
141 [Ref] [varchar](50) NULL,
142 [IXS] [varchar](50) NULL,
143 [Telefone] [int] NULL,
144 [Telemovel] [int] NULL,
145 [Pass] [varchar](50) NULL,
146 [Tipo] [nvarchar](50) NULL,
147 [DataAbre] [date] NULL,
148 [DataCria] [date] NULL,
149 [Site] [varchar](50) NULL,
150 [ID_Servico] [int] NULL,
151 [Estado] [varchar](50) NULL,
152 CONSTRAINT [PK_Loja] PRIMARY KEY CLUSTERED
153(
154 [ID_Local] ASC
155)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
156) ON [PRIMARY]
157GO
158/****** Object: Table [dbo].[Marcas] Script Date: 06/09/2018 15:38:35 ******/
159SET ANSI_NULLS ON
160GO
161SET QUOTED_IDENTIFIER ON
162GO
163CREATE TABLE [dbo].[Marcas](
164 [ID_Marca] [int] NOT NULL,
165 [Cod_Tipo] [int] NOT NULL,
166 [Marca] [varchar](50) NOT NULL,
167 [Status] [varchar](50) NULL,
168 CONSTRAINT [PK_Marcas] PRIMARY KEY CLUSTERED
169(
170 [ID_Marca] ASC
171)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
172) ON [PRIMARY]
173GO
174/****** Object: Table [dbo].[Modelos] Script Date: 06/09/2018 15:38:35 ******/
175SET ANSI_NULLS ON
176GO
177SET QUOTED_IDENTIFIER ON
178GO
179CREATE TABLE [dbo].[Modelos](
180 [ID_Modelo] [int] NOT NULL,
181 [ID_Marca] [int] NOT NULL,
182 [Modelo] [varchar](50) NOT NULL,
183 [Status] [varchar](50) NULL,
184 CONSTRAINT [PK_Modelos] PRIMARY KEY CLUSTERED
185(
186 [ID_Modelo] ASC
187)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
188) ON [PRIMARY]
189GO
190/****** Object: Table [dbo].[Operadora] Script Date: 06/09/2018 15:38:35 ******/
191SET ANSI_NULLS ON
192GO
193SET QUOTED_IDENTIFIER ON
194GO
195CREATE TABLE [dbo].[Operadora](
196 [ID_Operadora] [int] NOT NULL,
197 [Nome] [varchar](50) NOT NULL,
198 [Linha_de_Suporte] [varchar](50) NULL,
199 [Status] [varchar](50) NULL,
200 CONSTRAINT [PK_Operadora] PRIMARY KEY CLUSTERED
201(
202 [ID_Operadora] ASC
203)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
204) ON [PRIMARY]
205GO
206/****** Object: Table [dbo].[Servicos] Script Date: 06/09/2018 15:38:35 ******/
207SET ANSI_NULLS ON
208GO
209SET QUOTED_IDENTIFIER ON
210GO
211CREATE TABLE [dbo].[Servicos](
212 [ID_Servico] [int] NOT NULL,
213 [Hostname] [varchar](50) NULL,
214 [Instancia] [varchar](max) NULL,
215 [modelo] [varchar](50) NULL,
216 [Serial] [varchar](max) NULL,
217 [ID_Acesso] [varchar](50) NULL,
218 [ID_Acesso2] [varchar](50) NULL,
219 [Descricao] [varchar](50) NULL,
220 [Descricao2] [varchar](50) NULL,
221 [Debito] [varchar](50) NULL,
222 [Debito2] [varchar](50) NULL,
223 [VRF] [varchar](50) NULL,
224 [VLAN] [varchar](50) NULL,
225 [IPWAN] [varchar](50) NULL,
226 [Rede_Anunc] [varchar](50) NULL,
227 [Rede_Anunc2] [varchar](50) NULL,
228 [ID_Operadora] [int] NULL,
229 [Estado] [varchar](50) NULL,
230 CONSTRAINT [PK_Wan] PRIMARY KEY CLUSTERED
231(
232 [ID_Servico] ASC
233)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
234) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
235GO
236/****** Object: Table [dbo].[Tipo] Script Date: 06/09/2018 15:38:35 ******/
237SET ANSI_NULLS ON
238GO
239SET QUOTED_IDENTIFIER ON
240GO
241CREATE TABLE [dbo].[Tipo](
242 [Cod_Tipo] [int] NOT NULL,
243 [Tipo] [varchar](50) NOT NULL,
244 [Status] [varchar](50) NULL,
245 [ICO] [image] NULL,
246 CONSTRAINT [PK_Tipo] PRIMARY KEY CLUSTERED
247(
248 [Cod_Tipo] ASC
249)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
250) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
251GO
252/****** Object: Table [dbo].[Userstype] Script Date: 06/09/2018 15:38:35 ******/
253SET ANSI_NULLS ON
254GO
255SET QUOTED_IDENTIFIER ON
256GO
257CREATE TABLE [dbo].[Userstype](
258 [Username] [varchar](50) NOT NULL,
259 [Password] [varchar](50) NOT NULL,
260 [Tipo] [varchar](50) NOT NULL,
261 [Estado] [varchar](50) NULL,
262 CONSTRAINT [PK_Userstype] PRIMARY KEY CLUSTERED
263(
264 [Username] ASC
265)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
266) ON [PRIMARY]
267GO
268ALTER TABLE [dbo].[Caracteristicos] WITH CHECK ADD CONSTRAINT [FK_Caracteristicos_Tipo] FOREIGN KEY([Cod_Tipo])
269REFERENCES [dbo].[Tipo] ([Cod_Tipo])
270GO
271ALTER TABLE [dbo].[Caracteristicos] CHECK CONSTRAINT [FK_Caracteristicos_Tipo]
272GO
273ALTER TABLE [dbo].[Equip_Caract] WITH CHECK ADD CONSTRAINT [FK_Equip_Caract_Caracteristicos] FOREIGN KEY([ID_Caracateristica])
274REFERENCES [dbo].[Caracteristicos] ([ID_Caract])
275GO
276ALTER TABLE [dbo].[Equip_Caract] CHECK CONSTRAINT [FK_Equip_Caract_Caracteristicos]
277GO
278ALTER TABLE [dbo].[Equipamento] WITH CHECK ADD CONSTRAINT [FK_Equipamento_Departamento] FOREIGN KEY([ID_Departamento])
279REFERENCES [dbo].[Departamento] ([ID_Departamento])
280GO
281ALTER TABLE [dbo].[Equipamento] CHECK CONSTRAINT [FK_Equipamento_Departamento]
282GO
283ALTER TABLE [dbo].[Equipamento] WITH CHECK ADD CONSTRAINT [FK_Equipamento_Funcionario] FOREIGN KEY([ID_Departamento])
284REFERENCES [dbo].[Func] ([ID_Departamento])
285GO
286ALTER TABLE [dbo].[Equipamento] CHECK CONSTRAINT [FK_Equipamento_Funcionario]
287GO
288ALTER TABLE [dbo].[Equipamento] WITH CHECK ADD CONSTRAINT [FK_Equipamento_Locais] FOREIGN KEY([ID_Local])
289REFERENCES [dbo].[Locais] ([ID_Local])
290GO
291ALTER TABLE [dbo].[Equipamento] CHECK CONSTRAINT [FK_Equipamento_Locais]
292GO
293ALTER TABLE [dbo].[Equipamento] WITH CHECK ADD CONSTRAINT [FK_Equipamento_Marcas] FOREIGN KEY([ID_Marca])
294REFERENCES [dbo].[Marcas] ([ID_Marca])
295GO
296ALTER TABLE [dbo].[Equipamento] CHECK CONSTRAINT [FK_Equipamento_Marcas]
297GO
298ALTER TABLE [dbo].[Equipamento] WITH CHECK ADD CONSTRAINT [FK_Equipamento_Modelos] FOREIGN KEY([ID_Modelo])
299REFERENCES [dbo].[Modelos] ([ID_Modelo])
300GO
301ALTER TABLE [dbo].[Equipamento] CHECK CONSTRAINT [FK_Equipamento_Modelos]
302GO
303ALTER TABLE [dbo].[Equipamento] WITH CHECK ADD CONSTRAINT [FK_Equipamento_Tipo] FOREIGN KEY([Cod_Tipo])
304REFERENCES [dbo].[Tipo] ([Cod_Tipo])
305GO
306ALTER TABLE [dbo].[Equipamento] CHECK CONSTRAINT [FK_Equipamento_Tipo]
307GO
308ALTER TABLE [dbo].[Locais] WITH CHECK ADD CONSTRAINT [FK_Locais_Servicos] FOREIGN KEY([ID_Servico])
309REFERENCES [dbo].[Servicos] ([ID_Servico])
310GO
311ALTER TABLE [dbo].[Locais] CHECK CONSTRAINT [FK_Locais_Servicos]
312GO
313ALTER TABLE [dbo].[Locais] WITH CHECK ADD CONSTRAINT [FK_Loja_Auth] FOREIGN KEY([Login])
314REFERENCES [dbo].[Auth] ([Login])
315GO
316ALTER TABLE [dbo].[Locais] CHECK CONSTRAINT [FK_Loja_Auth]
317GO
318ALTER TABLE [dbo].[Marcas] WITH CHECK ADD CONSTRAINT [FK_Marcas_Tipo] FOREIGN KEY([Cod_Tipo])
319REFERENCES [dbo].[Tipo] ([Cod_Tipo])
320GO
321ALTER TABLE [dbo].[Marcas] CHECK CONSTRAINT [FK_Marcas_Tipo]
322GO
323ALTER TABLE [dbo].[Modelos] WITH CHECK ADD CONSTRAINT [FK_Modelos_Marcas] FOREIGN KEY([ID_Marca])
324REFERENCES [dbo].[Marcas] ([ID_Marca])
325GO
326ALTER TABLE [dbo].[Modelos] CHECK CONSTRAINT [FK_Modelos_Marcas]
327GO
328ALTER TABLE [dbo].[Servicos] WITH CHECK ADD CONSTRAINT [FK_Servicos_Operadora] FOREIGN KEY([ID_Operadora])
329REFERENCES [dbo].[Operadora] ([ID_Operadora])
330GO
331ALTER TABLE [dbo].[Servicos] CHECK CONSTRAINT [FK_Servicos_Operadora]
332GO
333/****** Object: StoredProcedure [dbo].[AddLinha] Script Date: 06/09/2018 15:38:35 ******/
334SET ANSI_NULLS ON
335GO
336SET QUOTED_IDENTIFIER ON
337GO
338CREATE proc [dbo].[AddLinha]
339@Linha varchar(50),
340@Nome varchar(50)
341
342as
343if not exists(Select Nome,Linha_de_Suporte from Operadora where Nome = @Nome and Linha_de_Suporte = @Linha)
344begin
345insert into Operadora(ID_Operadora,Nome,Linha_de_Suporte)
346values((select top 1 ID_Operadora+1 from Operadora order by ID_Operadora desc),@Nome,@Linha)
347
348insert into Historico
349values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),@Nome + ' - ' + @Linha ,'Foi Inserido a: ',SYSDATETIME(),0)
350end
351begin
352
353return -1
354end
355GO
356/****** Object: StoredProcedure [dbo].[AddLocal] Script Date: 06/09/2018 15:38:35 ******/
357SET ANSI_NULLS ON
358GO
359SET QUOTED_IDENTIFIER ON
360GO
361
362
363CREATE proc [dbo].[AddLocal]
364
365(@Login varchar(50),
366@Nome varchar(50),
367@Endereco varchar(50),
368@Ref varchar(50),
369@Telefone int,
370@Telemovel int,
371@Pass varchar(50),
372@Tipo varchar(50),
373@DataAbert date,
374@DataCria date,
375@Pais varchar(50))
376as
377declare @Id int, @LastID int
378
379Begin
380if not exists (select top 1 ID_Local+1 from Locais order by ID_Local desc)
381 Begin
382 insert into Locais values(1,@Login, @Nome,@Endereco,@Ref, Null, @Telefone, @Telemovel, @Pass, @Tipo, @DataAbert, @DataCria, @Pais, Null, 'Ativo')
383 End
384Else
385 Begin
386 set @Id = (select top 1 ID_Local+1 from Locais order by ID_Local desc)
387 insert into Locais values(@Id,@Login, @Nome,@Endereco,@Ref, Null, @Telefone, @Telemovel, @Pass, @Tipo, @DataAbert, @DataCria, @Pais, Null, 'Ativo')
388 End
389End
390if not exists (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
391 Begin
392 insert into Historico values(1, 'Local: '+@Nome, 'Foi criado a:',SYSDATETIME(), 1)
393 End
394Else
395 Begin
396 set @LastID = (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
397 insert into Historico values(@LastID, 'Local: '+@Nome, 'Foi criado a:',SYSDATETIME(), 1)
398 End
399GO
400/****** Object: StoredProcedure [dbo].[AddLoginLocal] Script Date: 06/09/2018 15:38:35 ******/
401SET ANSI_NULLS ON
402GO
403SET QUOTED_IDENTIFIER ON
404GO
405create proc [dbo].[AddLoginLocal]
406(@Login varchar(50))
407as
408 insert into Auth values(@Login)
409
410
411
412GO
413/****** Object: StoredProcedure [dbo].[AddLoginLocalModificar] Script Date: 06/09/2018 15:38:35 ******/
414SET ANSI_NULLS ON
415GO
416SET QUOTED_IDENTIFIER ON
417GO
418
419
420create proc [dbo].[AddLoginLocalModificar]
421(@Login varchar(50),
422@Nome varchar(50))
423as
424 if not exists (select Login from Auth where Login=@Login)
425 Begin
426 insert into Auth values(@Login)
427 End
428Else
429 Begin
430 update Locais set Login = @Login where Nome=@Nome
431 End
432
433GO
434/****** Object: StoredProcedure [dbo].[AddParamCarac] Script Date: 06/09/2018 15:38:35 ******/
435SET ANSI_NULLS ON
436GO
437SET QUOTED_IDENTIFIER ON
438GO
439CREATE proc [dbo].[AddParamCarac]
440@Type varchar(50),
441@carac varchar(50)
442
443
444as
445
446
447if not exists (select top 1 ID_Caract + 1 from Caracteristicos order by ID_Caract desc)
448 Begin
449 insert into Caracteristicos(ID_Caract,Cod_Tipo,Caracteristica)
450 VALUES(1,(select distinct Cod_Tipo from Tipo where Tipo = @Type),@carac)
451 End
452 else
453 if not exists(Select Cod_Tipo,Caracteristica from Caracteristicos where Caracteristica = @carac and Cod_Tipo = (select Cod_Tipo from Tipo where Tipo = @Type))
454begin
455 begin
456insert into Caracteristicos(ID_Caract,Cod_Tipo,Caracteristica)
457 VALUES((select top 1 ID_Caract+1 from Caracteristicos order by ID_Caract desc),(select distinct Cod_Tipo from Tipo where Tipo = @Type),@carac)
458 end
459 end
460 begin
461
462return -1
463end
464GO
465/****** Object: StoredProcedure [dbo].[AddServico] Script Date: 06/09/2018 15:38:35 ******/
466SET ANSI_NULLS ON
467GO
468SET QUOTED_IDENTIFIER ON
469GO
470CREATE proc [dbo].[AddServico]
471
472(@hostname varchar(50),
473@instancia varchar(50),
474@modelo varchar(50),
475@serial varchar(50),
476@idAcesso varchar(50),
477@idAcesso2 varchar(50),
478@descricao varchar(50),
479@descricao2 varchar(50),
480@debito varchar(50),
481@debito2 varchar(50),
482@VRF varchar(50),
483@VLAN varchar(50),
484@ipWan varchar(50),
485@redeAnunc varchar(50),
486@redeAnunc2 varchar(50),
487@idOperadora int,
488@local varchar(50))
489as
490declare @Id int, @LastID int
491
492
493Begin
494if not exists (select top 1 ID_Servico+1 from Servicos order by ID_Servico desc)
495 Begin
496 insert into Servicos values(1,@hostname, @instancia,@modelo ,@serial, @idAcesso, @idAcesso2, @descricao, @descricao2,@debito, @debito2, @VRF, @VLAN, @ipWan, @redeAnunc,@redeAnunc2, @idOperadora+1, 'Ativo' )
497 End
498Else
499 Begin
500 set @Id = (select top 1 ID_Servico+1 from Servicos order by ID_Servico desc)
501 insert into Servicos values(@Id,@hostname, @instancia,@modelo ,@serial, @idAcesso, @idAcesso2, @descricao, @descricao2,@debito, @debito2, @VRF, @VLAN, @ipWan, @redeAnunc,@redeAnunc2, @idOperadora+1, 'Ativo' )
502 End
503End
504if not exists (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
505 Begin
506 insert into Historico values(1, 'Servico de: '+@local,'Foi criado a:', SYSDATETIME(), 2)
507 End
508Else
509 Begin
510 set @LastID = (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
511 insert into Historico values(@LastID, 'Servico de: '+@local,'Foi criado a:', SYSDATETIME(), 2)
512 End
513
514GO
515/****** Object: StoredProcedure [dbo].[AddServicoLocal] Script Date: 06/09/2018 15:38:35 ******/
516SET ANSI_NULLS ON
517GO
518SET QUOTED_IDENTIFIER ON
519GO
520
521
522
523create proc [dbo].[AddServicoLocal]
524(@IXS varchar(50), @nome varchar(50))
525as
526declare @idServico int
527select top 1 @idServico = ID_Servico from Servicos order by ID_Servico desc
528update Locais set IXS=@IXS, ID_Servico = @idServico where ID_Local = (select ID_Local from Locais where Nome = @nome)
529
530
531
532GO
533/****** Object: StoredProcedure [dbo].[AddToHist] Script Date: 06/09/2018 15:38:35 ******/
534SET ANSI_NULLS ON
535GO
536SET QUOTED_IDENTIFIER ON
537GO
538CREATE PROC [dbo].[AddToHist]
539@Nome varchar(50),
540@Linha varchar(50)
541as
542insert into Historico
543values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),@Nome + ' - ' + @Linha,'Foi Desativado a: ',SYSDATETIME(),0)
544GO
545/****** Object: StoredProcedure [dbo].[AddType] Script Date: 06/09/2018 15:38:35 ******/
546SET ANSI_NULLS ON
547GO
548SET QUOTED_IDENTIFIER ON
549GO
550CREATE proc [dbo].[AddType]
551@Type varchar(50)
552as
553if not exists(Select TOP 1 Cod_Tipo FROM Tipo)
554begin
555insert into Tipo (Cod_Tipo,Tipo,Status)
556values((0),UPPER(@Type),'Ativo')
557
558END
559if not exists(Select Tipo from Tipo where Tipo = @Type)
560begin
561insert into Tipo (Cod_Tipo,Tipo,Status)
562values((select top 1 Cod_Tipo+1 from Tipo order by Cod_Tipo desc),UPPER(@Type),'Ativo')
563
564
565insert into Historico
566values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),upper(@Type),'Foi Inserido a: ',SYSDATETIME(),0)
567
568END
569
570begin
571
572return -1
573end
574GO
575/****** Object: StoredProcedure [dbo].[AddUSer] Script Date: 06/09/2018 15:38:35 ******/
576SET ANSI_NULLS ON
577GO
578SET QUOTED_IDENTIFIER ON
579GO
580CREATE proc [dbo].[AddUSer]
581
582(@username varchar(50),
583@pass varchar(50),
584@tipo varchar(50))
585as
586declare @LastID int
587begin
588 insert into Userstype values(@username,@pass, @tipo, 'Ativo' )
589end
590if not exists (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
591 Begin
592 insert into Historico values(1, 'User: '+@username, 'Foi criado a:',SYSDATETIME(), 3)
593 End
594Else
595 Begin
596 set @LastID = (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
597 insert into Historico values(@LastID, 'User: '+@username, 'Foi criado a:',SYSDATETIME(), 3)
598 End
599GO
600/****** Object: StoredProcedure [dbo].[ConsultEquipDetails] Script Date: 06/09/2018 15:38:35 ******/
601SET ANSI_NULLS ON
602GO
603SET QUOTED_IDENTIFIER ON
604GO
605CREATE proc [dbo].[ConsultEquipDetails]
606
607as
608SELECT *
609FROM [LGPGestTesteBKuserADmin].INFORMATION_SCHEMA.COLUMNS
610WHERE TABLE_NAME = N'Equipamento'
611GO
612/****** Object: StoredProcedure [dbo].[ConsultLastID] Script Date: 06/09/2018 15:38:35 ******/
613SET ANSI_NULLS ON
614GO
615SET QUOTED_IDENTIFIER ON
616GO
617CREATE proc [dbo].[ConsultLastID]
618
619
620
621as
622
623SELECT TOP 1 ID_Equipamento FROM Equipamento ORDER BY ID_Equipamento DESC
624
625GO
626/****** Object: StoredProcedure [dbo].[ConsultLastIDByTable] Script Date: 06/09/2018 15:38:35 ******/
627SET ANSI_NULLS ON
628GO
629SET QUOTED_IDENTIFIER ON
630GO
631create proc [dbo].[ConsultLastIDByTable]
632
633
634as
635
636SELECT TOP 1 * FROM Equipamento ORDER BY ID_Equipamento DESC
637GO
638/****** Object: StoredProcedure [dbo].[CountEquipByType] Script Date: 06/09/2018 15:38:35 ******/
639SET ANSI_NULLS ON
640GO
641SET QUOTED_IDENTIFIER ON
642GO
643CREATE proc [dbo].[CountEquipByType]
644@ID_Loja int,
645@Type varchar(50)
646
647as
648
649
650
651
652select count(*) from Equipamento
653inner join Tipo on Equipamento.Cod_Tipo = Tipo.Cod_Tipo
654where Tipo.Tipo = @Type and ID_Local = @ID_Loja and Equipamento.Status = 'Ativo'
655GO
656/****** Object: StoredProcedure [dbo].[CountMarca] Script Date: 06/09/2018 15:38:35 ******/
657SET ANSI_NULLS ON
658GO
659SET QUOTED_IDENTIFIER ON
660GO
661CREATE proc [dbo].[CountMarca]
662@marca varchar(50),
663@idtype int,
664@id_loja int
665as
666select count(*) from Equipamento
667inner join Marcas on Equipamento.ID_Marca = Marcas.ID_Marca
668 where Marca = @marca and ID_Local = @id_loja and Equipamento.Cod_Tipo = @idtype and Status = 'Ativo'
669GO
670/****** Object: StoredProcedure [dbo].[CountModel] Script Date: 06/09/2018 15:38:35 ******/
671SET ANSI_NULLS ON
672GO
673SET QUOTED_IDENTIFIER ON
674GO
675CREATE PROC [dbo].[CountModel]
676@Model varchar(50),
677@Marca varchar(50),
678@Type int,
679@id int
680as
681select COUNT(*) from Equipamento
682
683 where Equipamento.ID_Modelo = (select ID_Modelo from Modelos where Modelo = @Model) and
684ID_Marca = (select ID_Marca from Marcas where Marca = @Marca and Cod_Tipo = @Type) and ID_Local = @ID
685GO
686/****** Object: StoredProcedure [dbo].[DeacLinha] Script Date: 06/09/2018 15:38:35 ******/
687SET ANSI_NULLS ON
688GO
689SET QUOTED_IDENTIFIER ON
690GO
691CREATE proc [dbo].[DeacLinha]
692@Nome varchar(50),
693@Linha varchar(50)
694
695as
696update Operadora
697set Status = 'Inativo'
698where ID_Operadora = (select ID_Operadora from Operadora where Nome = @Nome )
699exec AddToHist @Nome,@Linha
700GO
701/****** Object: StoredProcedure [dbo].[DeactMarca] Script Date: 06/09/2018 15:38:35 ******/
702SET ANSI_NULLS ON
703GO
704SET QUOTED_IDENTIFIER ON
705GO
706create proc [dbo].[DeactMarca]
707
708@Type varchar(50),
709@Marca varchar(50)
710as
711declare @id int
712set @id = (select Cod_Tipo from Tipo where Tipo = @Type )
713
714update Marcas
715set Status = 'Inativo'
716where Cod_Tipo = @id and Marca = @Marca
717insert into Historico
718values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),upper(@Type),'Foi Eliminado a: ',SYSDATETIME(),0)
719GO
720/****** Object: StoredProcedure [dbo].[DeactModel] Script Date: 06/09/2018 15:38:35 ******/
721SET ANSI_NULLS ON
722GO
723SET QUOTED_IDENTIFIER ON
724GO
725create proc [dbo].[DeactModel]
726
727@Type varchar(50),
728@Model varchar(50),
729@Marca varchar(50)
730as
731declare @id int
732set @id = (select ID_Marca from Marcas where Marcas.Cod_Tipo = (Select Cod_Tipo from Tipo where Tipo = @Type) and Marca = @Marca)
733
734update Modelos
735set Status = 'Inativo'
736where ID_Marca = @id and Modelo = @Model
737insert into Historico
738values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),upper(@Type),'Foi Eliminado a: ',SYSDATETIME(),0)
739GO
740/****** Object: StoredProcedure [dbo].[DeactType] Script Date: 06/09/2018 15:38:35 ******/
741SET ANSI_NULLS ON
742GO
743SET QUOTED_IDENTIFIER ON
744GO
745CREATE proc [dbo].[DeactType]
746
747@Type varchar(50)
748as
749declare @id int
750set @id = (select Cod_Tipo from Tipo where Tipo = @Type )
751
752update Tipo
753set Tipo.Status = 'Inativo'
754where Cod_Tipo = @id
755insert into Historico
756values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),upper(@Type),'Foi Eliminado a: ',SYSDATETIME(),0)
757GO
758/****** Object: StoredProcedure [dbo].[DelCarac] Script Date: 06/09/2018 15:38:35 ******/
759SET ANSI_NULLS ON
760GO
761SET QUOTED_IDENTIFIER ON
762GO
763CREATE proc [dbo].[DelCarac]
764@type varchar(50),
765@Carac varchar(50)
766as
767declare @id int
768set @id= (select ID_Caract from Caracteristicos where Cod_Tipo = (select distinct Cod_Tipo from Tipo where Tipo = @type) and Caracteristica = @Carac )
769delete from Caracteristicos where Caracteristica = @Carac and ID_Caract = @id
770GO
771/****** Object: StoredProcedure [dbo].[DelEquipByID] Script Date: 06/09/2018 15:38:35 ******/
772SET ANSI_NULLS ON
773GO
774SET QUOTED_IDENTIFIER ON
775GO
776CREATE proc [dbo].[DelEquipByID]
777@ID int
778as
779declare @cod int
780declare @idequip varchar(50)
781declare @descript varchar(50)
782set @cod = (SELECT Cod_Tipo FROM Equipamento WHERE ID_Equipamento = @ID)
783set @descript = (Select Tipo from Tipo where Cod_Tipo = @cod)
784set @idequip = convert(varchar,@ID)
785update Equipamento
786set Status = 'Inactive'
787where ID_Equipamento = @ID
788
789insert into Historico
790values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),@idequip + ' - ' + @descript,'Foi Eliminado a: ',SYSDATETIME(),0)
791GO
792/****** Object: StoredProcedure [dbo].[DelEquipVals] Script Date: 06/09/2018 15:38:35 ******/
793SET ANSI_NULLS ON
794GO
795SET QUOTED_IDENTIFIER ON
796GO
797create proc [dbo].[DelEquipVals]
798@id int
799as
800
801delete from Equip_Caract
802where ID_Equipamento = @id
803GO
804/****** Object: StoredProcedure [dbo].[DeleteLocalByID] Script Date: 06/09/2018 15:38:35 ******/
805SET ANSI_NULLS ON
806GO
807SET QUOTED_IDENTIFIER ON
808GO
809
810CREATE
811pRoCeDuRe [dbo].[DeleteLocalByID]
812@id int,
813@login varchar(50)
814as
815declare @idServico int, @NomeLocal varchar(50), @LastID int, @localServico varchar(50)
816set @idServico = (select ID_Servico from Locais WHERE ID_Local = @id)
817set @localServico = (select Nome from Locais where ID_Servico=@idServico)
818update Equipamento set ID_Local = 91, Estado = 'Activo | Em Stock' where ID_Local = @id
819update Servicos set Estado = 'Inativo' where ID_Servico = @idServico
820update Locais set Estado= 'Inativo' where ID_Local = @id
821
822set @NomeLocal = (select Nome From Locais where ID_Local= @id)
823Begin
824if not exists (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
825 Begin
826 insert into Historico values(1, 'Local: '+@NomeLocal, 'Foi desativado a:',SYSDATETIME(), 1)
827 End
828Else
829 Begin
830 set @LastID = (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
831 insert into Historico values(@LastID,'Local: '+@NomeLocal, 'Foi desativado a:',SYSDATETIME(), 1)
832 End
833End
834set @LastID = (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
835insert into Historico values(@LastID, 'Servico de: '+@localServico,'Foi desativado a: ', SYSDATETIME(), 2)
836
837GO
838/****** Object: StoredProcedure [dbo].[DeleteLocalByID2] Script Date: 06/09/2018 15:38:35 ******/
839SET ANSI_NULLS ON
840GO
841SET QUOTED_IDENTIFIER ON
842GO
843CREATE
844pRoCeDuRe [dbo].[DeleteLocalByID2]
845@id int,
846@login varchar(50)
847as
848declare @NomeLocal varchar(50), @LastID int
849
850update Equipamento set ID_Local = 91, Estado = 'Activo | Em Stock' where ID_Local = @id
851
852update Locais set Estado= 'Inativo' where ID_Local = @id
853set @NomeLocal = (select Nome From Locais where ID_Local= @id)
854Begin
855if not exists (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
856 Begin
857 insert into Historico values(1, 'Local: '+@NomeLocal, 'Foi desativado a:',SYSDATETIME(), 1)
858 End
859Else
860 Begin
861 set @LastID = (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
862 insert into Historico values(@LastID, 'Local: '+@NomeLocal, 'Foi desativado a:',SYSDATETIME(), 1)
863 End
864End
865GO
866/****** Object: StoredProcedure [dbo].[DeleteServicoByID] Script Date: 06/09/2018 15:38:35 ******/
867SET ANSI_NULLS ON
868GO
869SET QUOTED_IDENTIFIER ON
870GO
871
872CREATE PROCEDURE [dbo].[DeleteServicoByID]
873(@id int)
874as
875declare @LastID int, @NomeLocal varchar(50)
876set @NomeLocal = (select Nome From Locais where ID_Servico=@id)
877 update Locais set IXS = null, ID_Servico = null where ID_Servico=@id
878 update Servicos set Estado = 'Desativado' where ID_Servico = @id
879 Begin
880if not exists (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
881 Begin
882 insert into Historico values(1, 'Servico do local: '+@NomeLocal,'Foi desativado a: ' ,SYSDATETIME(), 2)
883 End
884Else
885 Begin
886 set @LastID = (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
887 insert into Historico values(@LastID, 'Servico do local: '+@NomeLocal,'Foi desativado a: ' ,SYSDATETIME(), 2)
888 End
889End
890GO
891/****** Object: StoredProcedure [dbo].[DeleteServicoByLocal] Script Date: 06/09/2018 15:38:35 ******/
892SET ANSI_NULLS ON
893GO
894SET QUOTED_IDENTIFIER ON
895GO
896
897CREATE pRoCeDuRe [dbo].[DeleteServicoByLocal]
898@id int
899as
900declare @idServico int
901set @idServico = (select ID_Servico from Locais WHERE ID_Local = @id)
902delete from Servicos where ID_Servico = @idServico
903GO
904/****** Object: StoredProcedure [dbo].[DeleteType] Script Date: 06/09/2018 15:38:35 ******/
905SET ANSI_NULLS ON
906GO
907SET QUOTED_IDENTIFIER ON
908GO
909CREATE proc [dbo].[DeleteType]
910
911@Tipo varchar(50)
912as
913delete from Tipo
914where Cod_Tipo = (select Cod_Tipo from Tipo where Tipo = @Tipo)
915delete from Equipamento
916where Cod_Tipo = (select Cod_Tipo from Tipo where Tipo = @Tipo)
917
918insert into Historico
919values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),@Tipo,'Foi Eliminado a: ',SYSDATETIME(),0)
920GO
921/****** Object: StoredProcedure [dbo].[DeleteUserByName] Script Date: 06/09/2018 15:38:35 ******/
922SET ANSI_NULLS ON
923GO
924SET QUOTED_IDENTIFIER ON
925GO
926
927CREATE pRoCeDuRe [dbo].[DeleteUserByName]
928(@username varchar(50))
929as
930declare @LastID int
931 update Userstype set Estado = 'Inativo' where Username = @username
932 if not exists (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
933 Begin
934 insert into Historico values(1, 'User: '+@username, 'Foi desativado a:',SYSDATETIME(), 3)
935 End
936Else
937 Begin
938 set @LastID = (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
939 insert into Historico values(@LastID,'User: '+@username, 'Foi desativado a:',SYSDATETIME(), 3)
940 End
941GO
942/****** Object: StoredProcedure [dbo].[GetLastIDLocal] Script Date: 06/09/2018 15:38:35 ******/
943SET ANSI_NULLS ON
944GO
945SET QUOTED_IDENTIFIER ON
946GO
947create proc [dbo].[GetLastIDLocal]
948as
949SELECT TOP 1 ID_Local FROM Locais ORDER BY ID_Local DESC
950GO
951/****** Object: StoredProcedure [dbo].[InsertEquip] Script Date: 06/09/2018 15:38:35 ******/
952SET ANSI_NULLS ON
953GO
954SET QUOTED_IDENTIFIER ON
955GO
956CREATE proc [dbo].[InsertEquip]
957@Type VARCHAR(50),
958@Marca varchar(50),
959@Modelo varchar(50),
960@Local varchar(50),
961@Estado varchar(50)
962
963as
964declare @ID int, @Tipo int,@ID_Local int
965set @ID = (SELECT top 1 id_equipamento +1 from Equipamento order by ID_Equipamento desc)
966set @Tipo = (Select Cod_Tipo from Tipo where Tipo.Tipo LIKE @Type)
967set @ID_Local = (select ID_Local from Locais where Nome like @Local)
968
969if not exists (select top 1 ID_Equipamento+1 from Equipamento order by ID_Equipamento desc)
970 Begin
971 insert into Equipamento (ID_Equipamento,Cod_Tipo,ID_Marca,ID_Modelo,ID_Local,Estado,Status)
972
973values(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')
974end
975else
976begin
977insert into Equipamento (ID_Equipamento,Cod_Tipo,ID_Marca,ID_Modelo,ID_Local,Estado,Status)
978
979values(@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')
980end
981begin
982
983return -1
984end
985GO
986/****** Object: StoredProcedure [dbo].[InsertEquipCarac] Script Date: 06/09/2018 15:38:35 ******/
987SET ANSI_NULLS ON
988GO
989SET QUOTED_IDENTIFIER ON
990GO
991CREATE proc [dbo].[InsertEquipCarac]
992@Type varchar(50),
993@carac varchar(50),
994@Valor varchar(50)
995
996
997as
998declare @Ncarac int, @IDequip int
999set @Ncarac = (select distinct ID_Caract from Caracteristicos
1000
1001 where Caracteristica = @carac and Caracteristicos.Cod_Tipo = (select Cod_Tipo from Tipo where Tipo = @Type))
1002
1003
1004 set @IDequip= (SELECT top 1 id_equipamento from Equipamento order by ID_Equipamento desc)
1005
1006 if not exists(Select * from Equip_Caract where ID_Caracateristica = @Ncarac and ID_Equipamento = @IDequip and Valor = @Valor)
1007begin
1008insert into Equip_Caract(ID_Equipamento,ID_Caracateristica,Valor)
1009 VALUES(@idEquip,@Ncarac,@Valor)
1010 begin
1011
1012return -1
1013end
1014 END
1015GO
1016/****** Object: StoredProcedure [dbo].[insertIntoDepar] Script Date: 06/09/2018 15:38:35 ******/
1017SET ANSI_NULLS ON
1018GO
1019SET QUOTED_IDENTIFIER ON
1020GO
1021CREATE proc [dbo].[insertIntoDepar]
1022@idequip int,
1023@Depart varchar(50)
1024as
1025
1026insert into Equip_Depart(ID_Departamento,ID_Equipamento)
1027values((select ID_Departamento from Departamento where Nome = @Depart),(@idequip))
1028GO
1029/****** Object: StoredProcedure [dbo].[InsertIntoDepart] Script Date: 06/09/2018 15:38:35 ******/
1030SET ANSI_NULLS ON
1031GO
1032SET QUOTED_IDENTIFIER ON
1033GO
1034CREATE proc [dbo].[InsertIntoDepart]
1035@idDEPART int,
1036@Type VARCHAR(50),
1037@Marca varchar(50),
1038@Modelo varchar(50),
1039@Local varchar(50),
1040@Estado varchar(50)
1041as
1042declare @ID int, @Tipo int,@ID_Local int
1043set @ID = (SELECT top 1 id_equipamento +1 from Equipamento order by ID_Equipamento desc)
1044set @Tipo = (Select Cod_Tipo from Tipo where Tipo.Tipo LIKE @Type)
1045set @ID_Local = (select Nome from Departamento where ID_Departamento = @idDEPART)
1046insert into Equipamento (ID_Equipamento,Cod_Tipo,ID_Marca,ID_Modelo,ID_Departamento,Estado)
1047
1048values(@ID,@Tipo,@Marca,@Modelo,@ID_Local,@Estado)
1049
1050insert into Equip_Depart values(@idDEPART,(select top 1 ID_Equipamento+1 from Equipamento order by ID_Equipamento desc))
1051GO
1052/****** Object: StoredProcedure [dbo].[InsertMarca] Script Date: 06/09/2018 15:38:35 ******/
1053SET ANSI_NULLS ON
1054GO
1055SET QUOTED_IDENTIFIER ON
1056GO
1057CREATE proc [dbo].[InsertMarca]
1058
1059@Marca varchar(50),
1060@Tipo varchar(50)
1061
1062as
1063if not exists(Select TOP 1 Marca from Marcas)
1064begin
1065insert into Marcas values((1),(select distinct Cod_Tipo from Tipo where Tipo = @Tipo),@Marca,'Ativo')
1066END
1067if not exists(Select * from Marcas where Marca = @Marca and Cod_Tipo = (select distinct Cod_Tipo from Tipo where Tipo = @Tipo))
1068begin
1069insert 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')
1070
1071insert into Historico
1072values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),' Marca - '+@Marca,'Foi Inserido a: ',SYSDATETIME(),0)
1073end
1074else
1075begin
1076
1077return -1
1078end
1079GO
1080/****** Object: StoredProcedure [dbo].[InsertModel] Script Date: 06/09/2018 15:38:35 ******/
1081SET ANSI_NULLS ON
1082GO
1083SET QUOTED_IDENTIFIER ON
1084GO
1085CREATE proc [dbo].[InsertModel]
1086
1087@Modelo varchar(50),
1088@Marca varchar(50),
1089@Tipo varchar(50)
1090
1091as
1092DECLARE @COD_TYPO VARCHAR(50)
1093
1094SET @COD_TYPO = (SELECT COD_TIPO FROM Tipo WHERE Tipo = @Tipo)
1095
1096if not exists(Select TOP 1 Modelo FROM Modelos)
1097begin
1098insert into Modelos values((1),(select distinct ID_Marca from Marcas where Marca = @Marca AND Cod_Tipo = @COD_TYPO),@Modelo,'Ativo')
1099END
1100
1101if 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))
1102begin
1103
1104insert 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')
1105
1106insert into Historico
1107values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),' Modelo - ' + @Modelo ,'Foi Inserido a: ',SYSDATETIME(),0)
1108end
1109begin
1110
1111return -1
1112end
1113GO
1114/****** Object: StoredProcedure [dbo].[Linhas] Script Date: 06/09/2018 15:38:35 ******/
1115SET ANSI_NULLS ON
1116GO
1117SET QUOTED_IDENTIFIER ON
1118GO
1119create proc [dbo].[Linhas] @Nome varchar(50)
1120as
1121
1122select Linha_de_Suporte from dbo.Operadora where Nome = @Nome
1123GO
1124/****** Object: StoredProcedure [dbo].[LoadCaracteristicas] Script Date: 06/09/2018 15:38:35 ******/
1125SET ANSI_NULLS ON
1126GO
1127SET QUOTED_IDENTIFIER ON
1128GO
1129CREATE PROCEDURE [dbo].[LoadCaracteristicas]
1130@Type int
1131as
1132
1133select Caracteristica from Caracteristicos
1134inner join Tipo on Caracteristicos.Cod_Tipo = Tipo.Cod_Tipo
1135 where Tipo.Cod_Tipo = @Type and Tipo.Status = 'Ativo'
1136GO
1137/****** Object: StoredProcedure [dbo].[loaddepart] Script Date: 06/09/2018 15:38:35 ******/
1138SET ANSI_NULLS ON
1139GO
1140SET QUOTED_IDENTIFIER ON
1141GO
1142CREATE proc [dbo].[loaddepart]
1143as
1144select nome from Departamento
1145GO
1146/****** Object: StoredProcedure [dbo].[loaddepartEquip] Script Date: 06/09/2018 15:38:35 ******/
1147SET ANSI_NULLS ON
1148GO
1149SET QUOTED_IDENTIFIER ON
1150GO
1151create proc [dbo].[loaddepartEquip]
1152as
1153select * from Equip_Depart
1154GO
1155/****** Object: StoredProcedure [dbo].[LoadEquipLoja] Script Date: 06/09/2018 15:38:35 ******/
1156SET ANSI_NULLS ON
1157GO
1158SET QUOTED_IDENTIFIER ON
1159GO
1160CREATE proc [dbo].[LoadEquipLoja]
1161
1162@id int
1163
1164as
1165
1166declare @Type int,@Marca varchar(50),@Modelo varchar(50),@Estado varchar(50)
1167set @Type =(select Cod_Tipo from Equipamento where Equipamento.ID_Equipamento = @id)
1168set @Estado = (select Estado from Equipamento where Equipamento.ID_Equipamento = @id)
1169
1170select Nome,@Type as Cod, @Estado as status from dbo.Locais
1171inner join Equipamento on Locais.ID_Local=Equipamento.ID_Local
1172where Equipamento.ID_Equipamento= @id
1173GO
1174/****** Object: StoredProcedure [dbo].[LoadEquipPerType] Script Date: 06/09/2018 15:38:35 ******/
1175SET ANSI_NULLS ON
1176GO
1177SET QUOTED_IDENTIFIER ON
1178GO
1179CREATE proc [dbo].[LoadEquipPerType]
1180@ID_Loja int,
1181@Type varchar(50),
1182@model varchar(50)
1183as
1184
1185
1186
1187select * from Equipamento
1188inner join Tipo on Equipamento.Cod_Tipo = Tipo.Cod_Tipo
1189where Tipo.Tipo = @Type and Equipamento.Id_Modelo = (select ID_Modelo from Modelos where Modelo = @model ) and ID_Local = @ID_Loja
1190and Equipamento.Status = 'Ativo'
1191GO
1192/****** Object: StoredProcedure [dbo].[LoadEquipsHist] Script Date: 06/09/2018 15:38:35 ******/
1193SET ANSI_NULLS ON
1194GO
1195SET QUOTED_IDENTIFIER ON
1196GO
1197create PROCEDURE [dbo].[LoadEquipsHist]
1198as
1199select * from Historico where Cod_Tipo=0
1200GO
1201/****** Object: StoredProcedure [dbo].[LoadEquipTree_Carac] Script Date: 06/09/2018 15:38:35 ******/
1202SET ANSI_NULLS ON
1203GO
1204SET QUOTED_IDENTIFIER ON
1205GO
1206CREATE proc [dbo].[LoadEquipTree_Carac]
1207@Tipo varchar(50)
1208as
1209
1210select Caracteristica from Caracteristicos
1211INNER JOIN Tipo ON Caracteristicos.Cod_Tipo = Tipo.Cod_Tipo
1212where Tipo.Tipo=@Tipo and Tipo.Status = 'Ativo'
1213GO
1214/****** Object: StoredProcedure [dbo].[LoadEquipTree_CaracVal] Script Date: 06/09/2018 15:38:35 ******/
1215SET ANSI_NULLS ON
1216GO
1217SET QUOTED_IDENTIFIER ON
1218GO
1219CREATE proc [dbo].[LoadEquipTree_CaracVal]
1220
1221@id int,
1222@carac varchar(50)
1223as
1224
1225select distinct Valor,ID_Caracateristica from Equip_Caract
1226inner join Caracteristicos on Equip_Caract.ID_Caracateristica = Caracteristicos.ID_Caract
1227where ID_Equipamento = @id and Caracteristica = @carac
1228GO
1229/****** Object: StoredProcedure [dbo].[LoadEquipTree_Equips] Script Date: 06/09/2018 15:38:35 ******/
1230SET ANSI_NULLS ON
1231GO
1232SET QUOTED_IDENTIFIER ON
1233GO
1234CREATE proc [dbo].[LoadEquipTree_Equips]
1235@ID int
1236as
1237
1238select * from Equipamento
1239inner join Locais on Equipamento.ID_Local = Locais.ID_Local
1240where Locais.ID_Local = @ID
1241GO
1242/****** Object: StoredProcedure [dbo].[LoadEquipTree_Lojas] Script Date: 06/09/2018 15:38:35 ******/
1243SET ANSI_NULLS ON
1244GO
1245SET QUOTED_IDENTIFIER ON
1246GO
1247CREATE proc [dbo].[LoadEquipTree_Lojas]
1248
1249as
1250
1251select * from Locais
1252where Tipo = 'Loja' or Tipo = 'SEDE'
1253GO
1254/****** Object: StoredProcedure [dbo].[LoadHistorico] Script Date: 06/09/2018 15:38:35 ******/
1255SET ANSI_NULLS ON
1256GO
1257SET QUOTED_IDENTIFIER ON
1258GO
1259
1260CREATE PROCEDURE [dbo].[LoadHistorico]
1261as
1262select * from Historico
1263GO
1264/****** Object: StoredProcedure [dbo].[LoadImglist] Script Date: 06/09/2018 15:38:35 ******/
1265SET ANSI_NULLS ON
1266GO
1267SET QUOTED_IDENTIFIER ON
1268GO
1269create PROCEDURE [dbo].[LoadImglist]
1270@Type int
1271as
1272
1273select ID_Img from ImgList where Cod_Tipo = @Type
1274GO
1275/****** Object: StoredProcedure [dbo].[LoadLinhas] Script Date: 06/09/2018 15:38:35 ******/
1276SET ANSI_NULLS ON
1277GO
1278SET QUOTED_IDENTIFIER ON
1279GO
1280create proc [dbo].[LoadLinhas]
1281as
1282
1283select Nome from dbo.Operadora
1284GO
1285/****** Object: StoredProcedure [dbo].[LoadLocaisByID] Script Date: 06/09/2018 15:38:35 ******/
1286SET ANSI_NULLS ON
1287GO
1288SET QUOTED_IDENTIFIER ON
1289GO
1290create PROCEDURE [dbo].[LoadLocaisByID]
1291(@nome varchar(50))
1292as
1293select * from dbo.Locais where Nome = @nome
1294GO
1295/****** Object: StoredProcedure [dbo].[LoadLocaisHist] Script Date: 06/09/2018 15:38:35 ******/
1296SET ANSI_NULLS ON
1297GO
1298SET QUOTED_IDENTIFIER ON
1299GO
1300
1301CREATE PROCEDURE [dbo].[LoadLocaisHist]
1302as
1303select * from Historico where Cod_Tipo=1
1304GO
1305/****** Object: StoredProcedure [dbo].[LoadLojas] Script Date: 06/09/2018 15:38:35 ******/
1306SET ANSI_NULLS ON
1307GO
1308SET QUOTED_IDENTIFIER ON
1309GO
1310CREATE PROCEDURE [dbo].[LoadLojas]
1311as
1312select * from dbo.Locais WHERE Estado = 'Ativo'
1313GO
1314/****** Object: StoredProcedure [dbo].[LoadLojasByID] Script Date: 06/09/2018 15:38:35 ******/
1315SET ANSI_NULLS ON
1316GO
1317SET QUOTED_IDENTIFIER ON
1318GO
1319create PROCEDURE [dbo].[LoadLojasByID]
1320@id int
1321as
1322select * from dbo.Locais
1323where ID_Local = @id
1324GO
1325/****** Object: StoredProcedure [dbo].[LoadLojasName] Script Date: 06/09/2018 15:38:35 ******/
1326SET ANSI_NULLS ON
1327GO
1328SET QUOTED_IDENTIFIER ON
1329GO
1330CREATE PROCEDURE [dbo].[LoadLojasName]
1331as
1332select Nome from dbo.Locais
1333GO
1334/****** Object: StoredProcedure [dbo].[LoadMarca] Script Date: 06/09/2018 15:38:35 ******/
1335SET ANSI_NULLS ON
1336GO
1337SET QUOTED_IDENTIFIER ON
1338GO
1339CREATE proc [dbo].[LoadMarca]
1340@id_type int
1341as
1342select Marca from Marcas where Cod_Tipo = @id_type
1343and
1344Status = 'Ativo'
1345GO
1346/****** Object: StoredProcedure [dbo].[LoadModel] Script Date: 06/09/2018 15:38:35 ******/
1347SET ANSI_NULLS ON
1348GO
1349SET QUOTED_IDENTIFIER ON
1350GO
1351CREATE proc [dbo].[LoadModel]
1352
1353@marca varchar(50),
1354@type varchar(50)
1355
1356as
1357
1358declare @id_marca int, @CodTp int
1359set @CodTp = (select Cod_Tipo From Tipo where Tipo = @type )
1360set @id_marca = (select ID_Marca from Marcas where Marca = @marca and Cod_Tipo = @CodTp )
1361select distinct Modelo from Modelos where Modelos.ID_Marca = @id_marca and
1362Status = 'Ativo'
1363GO
1364/****** Object: StoredProcedure [dbo].[LoadOperadoras] Script Date: 06/09/2018 15:38:35 ******/
1365SET ANSI_NULLS ON
1366GO
1367SET QUOTED_IDENTIFIER ON
1368GO
1369create PROCEDURE [dbo].[LoadOperadoras]
1370as
1371select * from Operadora
1372GO
1373/****** Object: StoredProcedure [dbo].[LoadOperadorasName] Script Date: 06/09/2018 15:38:35 ******/
1374SET ANSI_NULLS ON
1375GO
1376SET QUOTED_IDENTIFIER ON
1377GO
1378create PROCEDURE [dbo].[LoadOperadorasName]
1379as
1380select Nome from Operadora
1381GO
1382/****** Object: StoredProcedure [dbo].[LoadServicos] Script Date: 06/09/2018 15:38:35 ******/
1383SET ANSI_NULLS ON
1384GO
1385SET QUOTED_IDENTIFIER ON
1386GO
1387CREATE PROCEDURE [dbo].[LoadServicos]
1388as
1389select * from Servicos where Estado = 'Ativo'
1390GO
1391/****** Object: StoredProcedure [dbo].[LoadServicosByID] Script Date: 06/09/2018 15:38:35 ******/
1392SET ANSI_NULLS ON
1393GO
1394SET QUOTED_IDENTIFIER ON
1395GO
1396create PROCEDURE [dbo].[LoadServicosByID]
1397@id int
1398as
1399select * from Servicos
1400where ID_Servico = @id
1401
1402GO
1403/****** Object: StoredProcedure [dbo].[LoadServicosByLocal] Script Date: 06/09/2018 15:38:35 ******/
1404SET ANSI_NULLS ON
1405GO
1406SET QUOTED_IDENTIFIER ON
1407GO
1408
1409CREATE PROCEDURE [dbo].[LoadServicosByLocal]
1410@nome varchar(50)
1411as
1412declare @idServico int
1413Begin
1414if not exists (select ID_Servico from Locais WHERE Nome = @nome)
1415 Begin
1416 set @idServico = null;
1417 End
1418Else
1419 Begin
1420 set @idServico = (select ID_Servico from Locais WHERE Nome = @nome)
1421 select * from Servicos where ID_Servico = @idServico
1422 End
1423End
1424GO
1425/****** Object: StoredProcedure [dbo].[LoadServicosHist] Script Date: 06/09/2018 15:38:35 ******/
1426SET ANSI_NULLS ON
1427GO
1428SET QUOTED_IDENTIFIER ON
1429GO
1430create PROCEDURE [dbo].[LoadServicosHist]
1431as
1432select * from Historico where Cod_Tipo=2
1433GO
1434/****** Object: StoredProcedure [dbo].[LoadTreeview] Script Date: 06/09/2018 15:38:35 ******/
1435SET ANSI_NULLS ON
1436GO
1437SET QUOTED_IDENTIFIER ON
1438GO
1439CREATE PROCEDURE [dbo].[LoadTreeview]
1440
1441as
1442
1443select Tipo from dbo.Tipo
1444
1445where Status = 'Ativo'
1446GO
1447/****** Object: StoredProcedure [dbo].[LoadUsers] Script Date: 06/09/2018 15:38:35 ******/
1448SET ANSI_NULLS ON
1449GO
1450SET QUOTED_IDENTIFIER ON
1451GO
1452
1453CREATE PROCEDURE [dbo].[LoadUsers]
1454as
1455select * from Userstype where Estado='Ativo'
1456GO
1457/****** Object: StoredProcedure [dbo].[LoadUsersHist] Script Date: 06/09/2018 15:38:35 ******/
1458SET ANSI_NULLS ON
1459GO
1460SET QUOTED_IDENTIFIER ON
1461GO
1462
1463
1464
1465create PROCEDURE [dbo].[LoadUsersHist]
1466as
1467select * from Historico where Cod_Tipo=3
1468
1469GO
1470/****** Object: StoredProcedure [dbo].[ResumoEquips] Script Date: 06/09/2018 15:38:35 ******/
1471SET ANSI_NULLS ON
1472GO
1473SET QUOTED_IDENTIFIER ON
1474GO
1475
1476create PROCEDURE [dbo].[ResumoEquips]
1477@id int
1478as
1479
1480select distinct COUNT(*) from Equipamento where ID_Local=@id
1481
1482
1483GO
1484/****** Object: StoredProcedure [dbo].[ResumoEquips2] Script Date: 06/09/2018 15:38:35 ******/
1485SET ANSI_NULLS ON
1486GO
1487SET QUOTED_IDENTIFIER ON
1488GO
1489
1490create PROCEDURE [dbo].[ResumoEquips2]
1491@id int
1492as
1493
1494declare @NumPC int, @NumPDT int, @NumPOS int,@NumPinpad int,@NumTablet int, @NumImpress int
1495set @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')
1496set @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')
1497set @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')
1498set @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')
1499set @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')
1500set @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')
1501
1502
1503select @NumPC, @NumPDT, @NumPOS,@NumPinpad,@NumTablet, @NumImpress
1504
1505
1506GO
1507/****** Object: StoredProcedure [dbo].[ResumoServicos] Script Date: 06/09/2018 15:38:35 ******/
1508SET ANSI_NULLS ON
1509GO
1510SET QUOTED_IDENTIFIER ON
1511GO
1512create PROCEDURE [dbo].[ResumoServicos]
1513@id int
1514as
1515select Nome from Locais where ID_Servico = @id
1516
1517
1518
1519GO
1520/****** Object: StoredProcedure [dbo].[SearchCaracVals] Script Date: 06/09/2018 15:38:35 ******/
1521SET ANSI_NULLS ON
1522GO
1523SET QUOTED_IDENTIFIER ON
1524GO
1525cReate PROCEDURE [dbo].[SearchCaracVals]
1526@id int
1527as
1528
1529select distinct Valor from dbo.Equip_Caract
1530where ID_Caracateristica = @id;
1531GO
1532/****** Object: StoredProcedure [dbo].[SearchEquipGeral] Script Date: 06/09/2018 15:38:35 ******/
1533SET ANSI_NULLS ON
1534GO
1535SET QUOTED_IDENTIFIER ON
1536GO
1537CREATE PROCEDURE [dbo].[SearchEquipGeral]
1538
1539@id int
1540as
1541declare @Local varchar(50)
1542
1543
1544
1545set @Local = (select Nome from Locais inner join Equipamento on Locais.ID_Local = Equipamento.ID_Local where ID_Equipamento = @id )
1546select ID_Equipamento ,@Local as NomeLoja,Marcas.Marca,Modelos.Modelo,Estado from Equipamento
1547
1548inner join Marcas on Equipamento.ID_Marca = Marcas.ID_Marca
1549inner join Modelos on Equipamento.ID_Modelo = Modelos.ID_Modelo
1550where ID_Equipamento = @id
1551GO
1552/****** Object: StoredProcedure [dbo].[SearchLocalByIndex] Script Date: 06/09/2018 15:38:35 ******/
1553SET ANSI_NULLS ON
1554GO
1555SET QUOTED_IDENTIFIER ON
1556GO
1557
1558create PROCEDURE [dbo].[SearchLocalByIndex]
1559@id int
1560as
1561
1562select Nome from Locais where ID_Local=@id
1563GO
1564/****** Object: StoredProcedure [dbo].[SearchLocalServicoByIndex] Script Date: 06/09/2018 15:38:35 ******/
1565SET ANSI_NULLS ON
1566GO
1567SET QUOTED_IDENTIFIER ON
1568GO
1569
1570
1571
1572create proc [dbo].[SearchLocalServicoByIndex]
1573@idServico int
1574as
1575 select Nome from Locais where ID_Servico=@idServico
1576
1577
1578GO
1579/****** Object: StoredProcedure [dbo].[SearchLojas] Script Date: 06/09/2018 15:38:35 ******/
1580SET ANSI_NULLS ON
1581GO
1582SET QUOTED_IDENTIFIER ON
1583GO
1584create PROCEDURE [dbo].[SearchLojas]
1585@Nome varchar(50)
1586as
1587
1588select Nome from dbo.Locais where Nome = @Nome
1589GO
1590/****** Object: StoredProcedure [dbo].[SearchLojas2] Script Date: 06/09/2018 15:38:35 ******/
1591SET ANSI_NULLS ON
1592GO
1593SET QUOTED_IDENTIFIER ON
1594GO
1595create PROCEDURE [dbo].[SearchLojas2]
1596@Name varchar(50)
1597as
1598
1599select * from dbo.Locais where Nome like '%'+@name+'%'or Login like '%'+@name+'%' and Estado = 'Ativo'
1600GO
1601/****** Object: StoredProcedure [dbo].[SearchServicos] Script Date: 06/09/2018 15:38:35 ******/
1602SET ANSI_NULLS ON
1603GO
1604SET QUOTED_IDENTIFIER ON
1605GO
1606
1607create PROCEDURE [dbo].[SearchServicos]
1608@nome varchar(50)
1609as
1610declare @idServico int
1611set @idServico= (select ID_Servico from Locais where Nome=@nome)
1612select * from Servicos where ID_Servico = @idServico
1613GO
1614/****** Object: StoredProcedure [dbo].[SearchType] Script Date: 06/09/2018 15:38:35 ******/
1615SET ANSI_NULLS ON
1616GO
1617SET QUOTED_IDENTIFIER ON
1618GO
1619CREATE PROCEDURE [dbo].[SearchType]
1620@Type varchar(50)
1621as
1622
1623select distinct Caracteristica,ID_Caract from dbo.Caracteristicos
1624INNER JOIN TIPO ON Caracteristicos.Cod_Tipo = Tipo.Cod_Tipo
1625where tIPO.Tipo LIKE @Type
1626GO
1627/****** Object: StoredProcedure [dbo].[UpdateEquipCarac] Script Date: 06/09/2018 15:38:35 ******/
1628SET ANSI_NULLS ON
1629GO
1630SET QUOTED_IDENTIFIER ON
1631GO
1632CREATE proc [dbo].[UpdateEquipCarac]
1633@idEquip int,
1634@carac varchar(50),
1635@Valor varchar(50)
1636
1637
1638as
1639declare @Ncarac int
1640
1641set @Ncarac = (select distinct ID_Caract from Caracteristicos
1642inner join Equip_Caract on
1643 Caracteristicos.ID_Caract = Equip_Caract.ID_Caracateristica
1644 where Caracteristica = @carac AND ID_Equipamento = @idEquip )
1645
1646
1647
1648 if not exists(select distinct ID_Caract from Caracteristicos
1649inner join Equip_Caract on
1650 Caracteristicos.ID_Caract = Equip_Caract.ID_Caracateristica
1651 where Caracteristica = @carac AND ID_Equipamento = @idEquip )
1652 begin
1653
1654 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)
1655 end
1656 else
1657 begin
1658
1659update Equip_Caract
1660set Valor = @Valor
1661
1662
1663
1664where ID_Equipamento = @idEquip and Equip_Caract.ID_Caracateristica = @Ncarac
1665
1666end
1667GO
1668/****** Object: StoredProcedure [dbo].[UpdateEquipGeral] Script Date: 06/09/2018 15:38:35 ******/
1669SET ANSI_NULLS ON
1670GO
1671SET QUOTED_IDENTIFIER ON
1672GO
1673CREATE proc [dbo].[UpdateEquipGeral]
1674@id int,
1675@Type varchar(50),
1676@Local varchar(50),
1677@Marca varchar(50),
1678@Modelo varchar(50),
1679@Estado varchar(50)
1680as
1681declare @Tipo int
1682set @Tipo = (Select Cod_Tipo from Tipo where Tipo.Tipo LIKE @Type)
1683update Equipamento
1684set ID_Local = (select ID_Local from Locais where Nome = @Local) ,
1685ID_Marca = (select distinct ID_Marca from Marcas where Marca = @Marca and Cod_Tipo = @Tipo),
1686ID_Modelo = (select distinct ID_Modelo from Modelos where Modelo = @Modelo),
1687Estado = @Estado
1688where ID_Equipamento = @id
1689exec AddToHist @id,@Type
1690GO
1691/****** Object: StoredProcedure [dbo].[UpdateLinha] Script Date: 06/09/2018 15:38:35 ******/
1692SET ANSI_NULLS ON
1693GO
1694SET QUOTED_IDENTIFIER ON
1695GO
1696CREATE proc [dbo].[UpdateLinha]
1697@Linha varchar(50),
1698@Nome varchar(50)
1699
1700as
1701
1702update Operadora
1703set Linha_de_Suporte = @Linha where Nome = @Nome
1704
1705
1706insert into Historico
1707values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),@Nome + ' - ' + @Linha ,'Foi Modificado a: ',SYSDATETIME(),0)
1708GO
1709/****** Object: StoredProcedure [dbo].[UpdateLocal] Script Date: 06/09/2018 15:38:35 ******/
1710SET ANSI_NULLS ON
1711GO
1712SET QUOTED_IDENTIFIER ON
1713GO
1714CREATE proc [dbo].[UpdateLocal]
1715
1716
1717@Login varchar(50),
1718@Nome varchar(50),
1719@Endereco varchar(50),
1720@Ref varchar(50),
1721@Telefone varchar(50),
1722@Telemovel varchar(50),
1723@Pass varchar(50),
1724@Tipo varchar(50),
1725@DataAbert date,
1726@DataCria date,
1727@Pais varchar(50),
1728@local varchar(50)
1729
1730as
1731declare @LastID int
1732 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
1733 if not exists (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
1734 Begin
1735 insert into Historico values(1, 'Local: '+@Nome,'Foi modificado a:' ,SYSDATETIME(), 1)
1736 End
1737Else
1738 Begin
1739 set @LastID = (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
1740 insert into Historico values(@LastID, 'Local: '+@Nome,'Foi modificado a:' ,SYSDATETIME(), 1)
1741 End
1742
1743
1744
1745
1746
1747GO
1748/****** Object: StoredProcedure [dbo].[UpdateLocalEquip] Script Date: 06/09/2018 15:38:35 ******/
1749SET ANSI_NULLS ON
1750GO
1751SET QUOTED_IDENTIFIER ON
1752GO
1753CREATE proc [dbo].[UpdateLocalEquip]
1754
1755
1756@id int,
1757@Nome varchar(50)
1758
1759as
1760
1761 update Equipamento set ID_Local = (select ID_Local from Locais where Nome = @Nome) where ID_Equipamento = @id
1762GO
1763/****** Object: StoredProcedure [dbo].[UpdateLoginLocal] Script Date: 06/09/2018 15:38:35 ******/
1764SET ANSI_NULLS ON
1765GO
1766SET QUOTED_IDENTIFIER ON
1767GO
1768
1769create proc [dbo].[UpdateLoginLocal]
1770@Login varchar(50),
1771@Local varchar(50)
1772as
1773declare @loginname varchar(50)
1774insert into Auth values(@Login)
1775
1776GO
1777/****** Object: StoredProcedure [dbo].[UpdateServico] Script Date: 06/09/2018 15:38:35 ******/
1778SET ANSI_NULLS ON
1779GO
1780SET QUOTED_IDENTIFIER ON
1781GO
1782
1783
1784
1785CREATE proc [dbo].[UpdateServico]
1786
1787
1788(@hostname varchar(50),
1789@instancia varchar(50),
1790@modelo varchar(50),
1791@serial varchar(50),
1792@idAcesso varchar(50),
1793@idAcesso2 varchar(50),
1794@descricao varchar(50),
1795@descricao2 varchar(50),
1796@debito varchar(50),
1797@debito2 varchar(50),
1798@vrf varchar(50),
1799@ipwan varchar(50),
1800@redeanunc varchar(50),
1801@redeanunc2 varchar(50),
1802@nome varchar(50),
1803@Id_Operadora int
1804)
1805as
1806declare @idServico int, @LastID int
1807set @idServico = (select ID_Servico from Locais WHERE Nome = @nome)
1808 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
1809 update Locais set IXS=@idAcesso where ID_Local = (select ID_Local from Locais where Nome = @nome)
1810 if not exists (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
1811 Begin
1812 insert into Historico values(1, 'Servico de: '+@nome,'Foi modificado a: ' ,SYSDATETIME(), 2)
1813 End
1814Else
1815 Begin
1816 set @LastID = (select top 1 ID_Registo+1 from Historico order by ID_Registo desc)
1817 insert into Historico values(@LastID, 'Servico de: '+@nome,'Foi modificado a: ' ,SYSDATETIME(), 2)
1818 End
1819GO
1820/****** Object: StoredProcedure [dbo].[UpdateType] Script Date: 06/09/2018 15:38:35 ******/
1821SET ANSI_NULLS ON
1822GO
1823SET QUOTED_IDENTIFIER ON
1824GO
1825CREATE proc [dbo].[UpdateType]
1826@Valor varchar(50),
1827@Tipo varchar(50)
1828as
1829update Tipo
1830set Tipo = @Valor
1831where Cod_Tipo = (select Cod_Tipo from Tipo where Tipo = @Tipo)
1832
1833insert into Historico
1834values((SELECT top 1 ID_Registo +1 from Historico order by ID_Registo desc),@Tipo,'Foi Modificado a: ',SYSDATETIME(),0)
1835GO
1836/****** Object: StoredProcedure [dbo].[VerificaUser] Script Date: 06/09/2018 15:38:35 ******/
1837SET ANSI_NULLS ON
1838GO
1839SET QUOTED_IDENTIFIER ON
1840GO
1841CREATE proc [dbo].[VerificaUser]
1842@user varchar(50),
1843@pass varchar(50)
1844as
1845
1846select distinct * from dbo.Userstype where Username = @user and dbo.Userstype.Password = @pass
1847GO
1848USE [master]
1849GO
1850ALTER DATABASE [LGPGest] SET READ_WRITE
1851GO