· 6 years ago · Aug 06, 2019, 04:02 PM
1USE db_ApoiosVinculos
2GO
3
4
5USE db_ApoiosVinculos
6go
7
8SET NOCOUNT ON
9
10drop table #TBL_Resultado
11drop table #TBL_Vinculos
12drop table #TMP_Pergunta1
13
14DECLARE @IdVinculo INT
15DECLARE @IdPergunta INT
16DECLARE @I INT=1
17DECLARE @NumeroFornecedor INT
18DECLARE @NomeFornecedor VARCHAR(255)
19DECLARE @NomeFornecedorForn VARCHAR(255)
20DECLARE @IdVinculoControle INT
21DECLARE @msg varchar(500)
22DECLARE @ContTot INT
23
24DROP TABLE #TMP_USUARIO
25--Montar tabelas temporárias
26--DECLARE #TBL_Vinculos AS TABLE
27--(
28-- IdVinculo INT
29--)
30
31CREATE TABLE #TBL_Vinculos
32(
33 IdVinculo INT
34)
35DECLARE @TBL_Perguntas AS TABLE
36(
37 IdPergunta INT
38)
39DECLARE @TBL_VinculoRespsota AS TABLE
40(
41 IdVinculo INT
42)
43
44
45SELECT * INTO #TMP_USUARIO FROM Usuario
46CREATE INDEX i1 ON #TMP_USUARIO (Login);
47
48CREATE TABLE #TBL_Resultado
49(
50 IdResposta INT
51, Nome VARCHAR(255)
52, Login VARCHAR(255)
53, DRT VARCHAR(255)
54, CRM VARCHAR(255)
55, Diretor VARCHAR(255)
56, Area VARCHAR(255)
57, Gestor VARCHAR(255)
58, Cargo VARCHAR(255)
59, AtividadePesquisa VARCHAR(255)
60, AtividadeEnsino VARCHAR(255)
61, GrupoMedico VARCHAR(255)
62, CamaraTecnica VARCHAR(255)
63, Comissao VARCHAR(255)
64, IdVinculo VARCHAR(255)
65, IdPergunta VARCHAR(255)
66, Descricao VARCHAR(255)
67, Resposta VARCHAR(255)
68, RespostaTexto VARCHAR(255)
69, NumeroFornecedor INT
70)
71
72CREATE INDEX IX_Id ON #TBL_Resultado(IdVinculo);
73CREATE INDEX IX_Id1 ON #TBL_Resultado(IdPergunta);
74
75--Inserte na tabela #TBL_Vinculos os Vinculos
76 INSERT INTO #TBL_Vinculos (IdVinculo)
77 select DISTINCT TOP 100
78 R.IdVinculo
79 from
80 Respostas R
81INNER JOIN Perguntas P on p.IdPergunta=r.IdPergunta
82LEFT JOIN RespostasPossiveis RP on RP.IdPergunta=p.IdPergunta and rp.IdRespostaPossivel=R.IdRespostaPossivel
83INNER JOIN ItensDeclarados I on I.Id_Vinculo = R.IdVinculo
84INNER JOIN Declaracao D on D.Id=I.Id_Declaracao
85INNER JOIN #TMP_USUARIO U on D.Usuario=U.Login
86WHERE IdVinculo =1181
87
88ORDER BY R.IdVinculo desc
89
90SET @ContTot=@@ROWCOUNT
91
92WHILE EXISTS (SELECT TOP 1 * FROM #TBL_Vinculos)
93BEGIN
94 SELECT TOP 1 @IdVinculo=IdVinculo FROM #TBL_Vinculos
95 SET @ContTot=@ContTot-1
96 --SELECT @msg = 'Processando Vínculo: ' + CONVERT(VARCHAR(100),@IdVinculo) + ' --- Faltantes: ' + CONVERT(VARCHAR(100),@ContTot)
97 --RAISERROR(@msg, 0, 1) WITH NOWAIT
98
99
100 DELETE FROM @TBL_Perguntas
101
102 INSERT INTO @TBL_Perguntas (IdPergunta)
103 SELECT DISTINCT IdPergunta FROM Respostas WHERE IdVinculo=@IdVinculo
104
105 WHILE EXISTS (SELECT TOP 1 * FROM @TBL_Perguntas )
106 BEGIN
107
108 SELECT TOP 1 @IdPergunta=IdPergunta FROM @TBL_Perguntas
109
110
111 --Prepara para receber as respostas
112 INSERT INTO #TBL_Resultado
113 (
114 IdResposta
115 , Nome
116 , Login
117 , DRT
118 , CRM
119 , Diretor
120 , Area
121 , Gestor
122 , Cargo
123 , AtividadePesquisa
124 , AtividadeEnsino
125 , GrupoMedico
126 , CamaraTecnica
127 , Comissao
128 , IdVinculo
129 , IdPergunta
130 , Descricao
131 , Resposta
132 , RespostaTexto
133 , NumeroFornecedor
134 )
135 SELECT
136 R.IdResposta,
137 U.Nome,
138 U.Login,
139 U.DRT,
140 u.CRM,
141 U.Diretor,
142 U.Area,
143 u.Gestor,
144 u.Cargo,
145 U.AtividadePesquisa,
146 U.AtividadeEnsino,
147 U.GrupoMedico,
148 u.CamaraTecnica,
149 u.Comissao,
150 R.IdVinculo,
151 P.IdPergunta,
152 P.Descricao,
153 RP.Resposta,
154 R.RespostaTexto,
155 R.NumeroFornecedor
156 from
157 Respostas R
158 INNER JOIN Perguntas P on p.IdPergunta=r.IdPergunta
159 LEFT JOIN RespostasPossiveis RP on RP.IdPergunta=p.IdPergunta and rp.IdRespostaPossivel=R.IdRespostaPossivel
160 INNER JOIN ItensDeclarados I on I.Id_Vinculo = R.IdVinculo
161 INNER JOIN Declaracao D on D.Id=I.Id_Declaracao
162 INNER JOIN #TMP_USUARIO U on D.Usuario=U.Login
163 WHERE IdVinculo=@IdVinculo
164 AND R.IdPergunta=@IdPergunta
165
166
167 DELETE @TBL_Perguntas WHERE IdPergunta=@IdPergunta
168 END
169
170 --Cria Regra para validar fornecedor
171 SET @NumeroFornecedor=NULL
172 SET @NomeFornecedor=NULL
173
174 SELECT @NumeroFornecedor=NumeroFornecedor,@NomeFornecedor=RespostaTexto FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo AND NumeroFornecedor IS NOT NULL
175
176 IF @NumeroFornecedor IS NOT NULL
177 BEGIN
178 SELECT @NomeFornecedorForn=NomeFornecedor FROM Fornecedor WHERE NumeroFornecedor=@NumeroFornecedor
179
180 IF LTRIM(RTRIM(UPPER(@NomeFornecedorForn)))<>LTRIM(RTRIM(UPPER(@NomeFornecedor)))
181 BEGIN
182 UPDATE #TBL_Resultado SET NumeroFornecedor=NULL WHERE IdVinculo=@IdVinculo
183 END
184 END
185
186 --Faz uma pesquisa para verificar as arvores de respostas corretas
187
188 INSERT INTO @TBL_VinculoRespsota(IdVinculo)
189 SELECT DISTINCT IdVinculo FROM #TBL_Resultado
190
191 WHILE EXISTS (SELECT TOP 1 * FROM @TBL_VinculoRespsota)
192 BEGIN
193
194 SELECT TOP 1 @IdVinculoControle=IdVinculo FROM @TBL_VinculoRespsota
195
196
197 DECLARE @IdPergunta_Tabela INT=1
198 DECLARE @IdRespostaPossivel INT
199 DECLARE @IdProximaPergunta INT=1
200 DECLARE @Resposta VARCHAR(255)
201 DECLARE @Pergunta VARCHAR(255)
202 DECLARE @Continua INT=1
203 DECLARE @Cont INT=0
204 DECLARE @TotRespostas INT
205 DECLARE @bArvoreCompleta INT=0
206 DECLARE @IdRespostaTable INT
207 DECLARE @Identificador INT=0
208 DECLARE @TBL_RespostasOK TABLE
209 (
210 IdResposta INT,
211 Identificador INT
212 )
213
214 SELECT * INTO #TMP_Pergunta1 FROM Respostas WHERE IdVinculo=@IdVinculoControle AND IdPergunta=1
215
216 WHILE EXISTS (SELECT TOP 1 * FROM #TMP_Pergunta1)
217 BEGIN
218 SET @Identificador=@Identificador+1
219 SELECT TOP 1 @IdRespostaPossivel=IdRespostaPossivel,@IdPergunta_Tabela=IdPergunta,@IdRespostaTable=IdResposta FROM #TMP_Pergunta1
220 SET @Cont=0
221 SET @Continua=1
222 WHILE @Continua=1
223 BEGIN
224
225 IF @Cont<>0
226 SELECT @IdRespostaPossivel=IdRespostaPossivel,@IdRespostaTable=IdResposta FROM Respostas WHERE IdVinculo=@IdVinculoControle AND IdPergunta=@IdProximaPergunta
227
228 --Pega resposta que informei
229 SELECT @Resposta=Resposta FROM RespostasPossiveis WHERE IdRespostaPossivel=@IdRespostaPossivel
230 SELECT @Pergunta=Descricao FROM Perguntas where IdPergunta=@IdProximaPergunta
231
232 SELECT @IdProximaPergunta=IdProximaPergunta FROM RespostasPossiveis WHERE IdPergunta=@IdProximaPergunta AND Resposta=@Resposta
233
234 --Pergunta onde as respostas não são opções
235 IF @@ROWCOUNT=0
236 BEGIN
237 SELECT @IdProximaPergunta=IdProximaPergunta FROM Perguntas WHERE IdPergunta=@IdProximaPergunta
238 END
239
240 --Valida se tem essa resposta
241 SELECT @TotRespostas=COUNT(*) FROM Respostas WHERE IdVinculo=@IdVinculoControle AND IdPergunta=@IdProximaPergunta
242
243 SET @Cont=1
244
245 IF @IdProximaPergunta=9
246 BEGIN
247 SET @Continua=0
248 SET @bArvoreCompleta=1
249 TRUNCATE TABLE #TMP_Pergunta1
250 END
251 IF @TotRespostas=0
252 BEGIN
253 SET @Continua=0
254 SET @bArvoreCompleta=0
255 END
256
257 INSERT INTO @TBL_RespostasOK (IdResposta,Identificador) VALUES (@IdRespostaTable,@Identificador)
258
259
260 END
261
262
263 DELETE #TMP_Pergunta1 WHERE IdRespostaPossivel=@IdRespostaPossivel
264
265
266 END
267
268
269 DROP TABLE #TMP_Pergunta1
270
271 --
272 DELETE #TBL_Resultado WHERE IdVinculo=@IdVinculoControle AND IdResposta NOT IN (SELECT IdResposta FROM @TBL_RespostasOK)
273
274 DELETE @TBL_VinculoRespsota WHERE IdVinculo=@IdVinculoControle
275
276 END
277
278
279 IF EXISTS (SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,0,'Grupo',NULL,(SELECT CONVERT(VARCHAR(MAX),Grupo) as Resposta FROM Fornecedor WHERE NumeroFornecedor =R.NumeroFornecedor),NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo AND NumeroFornecedor IS NOT NULL)
280 BEGIN
281
282 INSERT INTO #TBL_Resultado
283 (
284 IdResposta
285 , Nome
286 , Login
287 , DRT
288 , CRM
289 , Diretor
290 , Area
291 , Gestor
292 , Cargo
293 , AtividadePesquisa
294 , AtividadeEnsino
295 , GrupoMedico
296 , CamaraTecnica
297 , Comissao
298 , IdVinculo
299 , IdPergunta
300 , Descricao
301 , Resposta
302 , RespostaTexto
303 , NumeroFornecedor
304 )
305
306
307 SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,999,'Grupo',NULL,(SELECT CONVERT(VARCHAR(MAX),Grupo) as Resposta FROM Fornecedor WHERE NumeroFornecedor =R.NumeroFornecedor),NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo AND NumeroFornecedor IS NOT NULL
308 UNION ALL
309 SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,999,'NumeroFornecedor',NULL,(SELECT CONVERT(VARCHAR(MAX),NumeroFornecedor) as Resposta FROM Fornecedor WHERE NumeroFornecedor =R.NumeroFornecedor),NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo AND NumeroFornecedor IS NOT NULL
310 UNION ALL
311 SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,999,'NomeFornecedor',NULL,(SELECT CONVERT(VARCHAR(MAX),NomeFornecedor) as Resposta FROM Fornecedor WHERE NumeroFornecedor =R.NumeroFornecedor),NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo AND NumeroFornecedor IS NOT NULL
312 UNION ALL
313 SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,999,'CNPJ',NULL,(SELECT CONVERT(VARCHAR(MAX),CNPJ) as Resposta FROM Fornecedor WHERE NumeroFornecedor =R.NumeroFornecedor),NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo AND NumeroFornecedor IS NOT NULL
314 UNION ALL
315 SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,999,'StatusFornecedor',NULL,(SELECT CONVERT(VARCHAR(MAX),StatusFornecedor) as Resposta FROM Fornecedor WHERE NumeroFornecedor =R.NumeroFornecedor),NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo AND NumeroFornecedor IS NOT NULL
316 UNION ALL
317 SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,999,'CodigoFornecimento',NULL,(SELECT CONVERT(VARCHAR(MAX),CodigoFornecimento) as Resposta FROM Fornecedor WHERE NumeroFornecedor =R.NumeroFornecedor),NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo AND NumeroFornecedor IS NOT NULL
318 UNION ALL
319 SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,999,'Fornecimento',NULL,(SELECT CONVERT(VARCHAR(MAX),Fornecimento) as Resposta FROM Fornecedor WHERE NumeroFornecedor =R.NumeroFornecedor),NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo AND NumeroFornecedor IS NOT NULL
320 UNION ALL
321 SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,999,'ABC',NULL,(SELECT CONVERT(VARCHAR(MAX),ABC) as Resposta FROM Fornecedor WHERE NumeroFornecedor =R.NumeroFornecedor),NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo AND NumeroFornecedor IS NOT NULL
322 UNION ALL
323 SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,999,'GrupoADMTesouraria',NULL,(SELECT CONVERT(VARCHAR(MAX),GrupoADMTesouraria) as Resposta FROM Fornecedor WHERE NumeroFornecedor =R.NumeroFornecedor),NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo AND NumeroFornecedor IS NOT NULL
324 END
325
326 ELSE
327
328 BEGIN
329 INSERT INTO #TBL_Resultado
330 (
331 IdResposta
332 , Nome
333 , Login
334 , DRT
335 , CRM
336 , Diretor
337 , Area
338 , Gestor
339 , Cargo
340 , AtividadePesquisa
341 , AtividadeEnsino
342 , GrupoMedico
343 , CamaraTecnica
344 , Comissao
345 , IdVinculo
346 , IdPergunta
347 , Descricao
348 , Resposta
349 , RespostaTexto
350 , NumeroFornecedor
351 )
352 SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,999,'Grupo',NULL, NULL,NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo
353 UNION ALL
354 SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,999,'NumeroFornecedor',NULL,NULL,NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo
355 UNION ALL
356 SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,999,'NomeFornecedor',NULL,NULL,NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo
357 UNION ALL
358 SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,999,'CNPJ',NULL,NULL,NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo
359 UNION ALL
360 SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,999,'StatusFornecedor',NULL,NULL,NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo
361 UNION ALL
362 SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,999,'CodigoFornecimento',NULL,NULL,NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo
363 UNION ALL
364 SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,999,'Fornecimento',NULL,NULL,NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo
365 UNION ALL
366 SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,999,'ABC',NULL,NULL,NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo
367 UNION ALL
368 SELECT TOP 1 IdResposta,Nome,Login,DRT,CRM,Diretor,Area,Gestor,Cargo,AtividadePesquisa,AtividadeEnsino,GrupoMedico,CamaraTecnica,Comissao,IdVinculo,999,'GrupoADMTesouraria',NULL,NULL,NULL FROM #TBL_Resultado AS R WHERE IdVinculo=@IdVinculo
369 END
370
371 DELETE #TBL_Vinculos WHERE IdVinculo=@IdVinculo
372END
373SELECT * FROM #TBL_Resultado ORDER BY IdVinculo, IdPergunta