Posted on by admin

MSSQL – Função para pegar o Id dos Insumos de uma composição

Função para pegar o Id dos Insumos de uma composição

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114

 
USE [CORPORERM]
GO
/****** Object:  UserDefinedFunction [dbo].[GETIDCOMPOSICAO]    Script Date: 08/11/2015 09:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GETIDCOMPOSICAO] ( @CODCOLIGADA INT, @IDPRJ INT,@IDCMP INT )
RETURNS 
@TABELA_RETORNO TABLE (   
    CODCOLIGADA INT,
    IDPRJ INT,
    IDCMP INT,
	IDCMPFILHA INT,
	IDISM INT,
	QUANTIDADE NUMERIC(38,4),
	QUANTIDADE_ACUMULADA NUMERIC(38,4),
	IDCMP_PAI INT,
	IDCMP_RAIZ INT,
	NIVEL INT
)
AS
BEGIN
	DECLARE @v_CODCOLIGADA INT
	DECLARE @v_IDPRJ INT
	DECLARE @v_IDCMP INT
	DECLARE @v_IDISM INT
	DECLARE @v_IDCMPFILHA INT
	DECLARE @v_QUANTIDADE NUMERIC(38,4)
	DECLARE @v_QUANTIDADE_ACUMULADA NUMERIC(38,4)
	DECLARE @v_IDCMP_PAI INT
	DECLARE @NIVEL INT
	DECLARE @v_NIVEL INT
	DECLARE @CONTINUA INT
 
	DECLARE @tmpTable TABLE  (CODCOLIGADA INT,IDPRJ INT,IDCMP INT, IDISM INT, IDCMPFILHA INT,QUANTIDADE NUMERIC(38,4), QUANTIDADE_ACUMULADA NUMERIC(38,4), IDCMP_PAI INT, IDCMP_RAIZ INT, NIVEL INT)
	DECLARE @tmpTable2 TABLE (CODCOLIGADA INT,IDPRJ INT,IDCMP INT, IDISM INT, IDCMPFILHA INT,QUANTIDADE NUMERIC(38,4), QUANTIDADE_ACUMULADA NUMERIC(38,4), IDCMP_PAI INT, IDCMP_RAIZ INT, NIVEL INT)
 
	SET @NIVEL = 0
	SET @CONTINUA = 0
 
	--Alimenta a tabela com as informações iniciais
	insert into @tmpTable select CODCOLIGADA, IDPRJ, IDCMP, IDISM , IDCMPFILHA, QUANTIDADE/10000 , QUANTIDADE/10000 as QUANTIDADE_ACUMULADA, @IDCMP, @IDCMP, @NIVEL from MRECCMP where CODCOLIGADA = @CODCOLIGADA and IDPRJ = @IDPRJ and idcmp = @IDCMP -- and IDCMPFILHA = 620108
	-- Insere no retorno dos registros ok que não tem filhos
	insert into @TABELA_RETORNO select CODCOLIGADA, IDPRJ, IDCMP, IDCMPFILHA, IDISM, QUANTIDADE, QUANTIDADE as QUANTIDADE_ACUMULADA, @IDCMP, @IDCMP, @NIVEL as NIVEL from @tmpTable -- where IDCMPFILHA is null
	-- remove da tmptable os OK que já estão no retorno(que não tem filhos)
	delete from @tmpTable where IDCMPFILHA is null
 
	-------------------------------------------------------------------------------------
	--verifica se tem mais composições filhas
	SET @CONTINUA = (select COUNT(IDCMP) from @tmpTable where IDCMPFILHA is not null and idcmp = @IDCMP)
 
	WHILE( @CONTINUA > 0) BEGIN
		SET @NIVEL = @NIVEL + 1 
		DECLARE myCursor CURSOR FOR
			select CODCOLIGADA, IDPRJ, IDCMP, IDISM, IDCMPFILHA , IDCMP_PAI from @tmpTable where IDCMPFILHA is not null --pega os idcmp das filhas
		OPEN myCursor
		FETCH NEXT FROM myCursor INTO @v_CODCOLIGADA, @v_IDPRJ, @v_IDCMP, @v_IDISM, @v_IDCMPFILHA, @v_IDCMP_PAI
		WHILE (@@FETCH_STATUS = 0) 
		BEGIN
			insert into @tmpTable2 select CODCOLIGADA, IDPRJ, IDCMP, IDISM , IDCMPFILHA, QUANTIDADE/10000, QUANTIDADE/10000 as QUANTIDADE_ACUMULADA, @v_IDCMP, @IDCMP, @NIVEL as NIVEL from MRECCMP where CODCOLIGADA = @CODCOLIGADA and IDPRJ = @IDPRJ and idcmp = @v_IDCMPFILHA
			FETCH NEXT FROM myCursor INTO @v_CODCOLIGADA, @v_IDPRJ, @v_IDCMP, @v_IDISM, @v_IDCMPFILHA, @v_IDCMP_PAI
		END
		CLOSE myCursor
		DEALLOCATE myCursor
		delete from @tmpTable --Limpa temp table
		insert into @tmpTable select * from @tmpTable2 --pega os valores do nivel anterior e passa para temporaria
		delete from @tmpTable2 ----Limpa temp table do novel
		insert into @TABELA_RETORNO select CODCOLIGADA, IDPRJ, IDCMP, IDCMPFILHA, IDISM, QUANTIDADE, QUANTIDADE_ACUMULADA, IDCMP_PAI, @IDCMP, @NIVEL as NIVEL from @tmpTable --where IDCMPFILHA is null --insere no retorno apenas quem não tem próximo nivel
		delete from @tmpTable where IDCMPFILHA is null --limpa as inseridas no retorno
		SET @CONTINUA = (select COUNT(IDCMP) from @tmpTable where IDCMPFILHA is not null) --verifica se tem mais niveis
	END
 
	----Acumula as quantidades--------------------------------------------------------------------------------------------------------
	DECLARE @v_SOMA NUMERIC(38,4)
	--DECLARE @v_TESTE NUMERIC(38,4)
	DECLARE CUR_NIVEL CURSOR FOR --Seleciona os níveis no cursor CUR_NIVEL para acumular nivel por nivel gradualmente
		select distinct NIVEL from @TABELA_RETORNO where NIVEL > 0 order by NIVEL -- o nivel 0 não precisa ser acumulado é nescessário seguir os niveis na ordem crescente
	OPEN CUR_NIVEL
	FETCH NEXT FROM CUR_NIVEL INTO @v_NIVEL --Etapa um separa os níveis ACUMULA NIVEL A NIVEL 
		WHILE (@@FETCH_STATUS = 0) --controla o fim dos registros so cursor
	BEGIN
		DECLARE CUR_QUANT CURSOR FOR ---Seleciona todos os itens do nível na variável @v_NIVEL
			select CODCOLIGADA,IDPRJ,IDCMP,IDCMPFILHA,IDISM,QUANTIDADE,QUANTIDADE_ACUMULADA,IDCMP_PAI,NIVEL FROM @TABELA_RETORNO where NIVEL = @v_NIVEL
		OPEN CUR_QUANT
		FETCH NEXT FROM CUR_QUANT INTO @v_CODCOLIGADA, @v_IDPRJ, @v_IDCMP, @v_IDCMPFILHA, @v_IDISM, @v_QUANTIDADE, @v_QUANTIDADE_ACUMULADA, @v_IDCMP_PAI, @NIVEL
		WHILE (@@FETCH_STATUS = 0) --controla o fim dos registros so cursor
		BEGIN
			SET @v_SOMA = (select QUANTIDADE_ACUMULADA from  @TABELA_RETORNO where NIVEL = @v_NIVEL -1 and CODCOLIGADA = @v_CODCOLIGADA and IDPRJ = @v_IDPRJ and IDCMP = @v_IDCMP_PAI and  IDCMPFILHA = @v_IDCMP) -- (Pega a quantidade do pai e passa para varialvel soma)
			IF @v_SOMA IS NOT NULL BEGIN
				IF @v_IDISM is null BEGIN -- Quando a variavel @v_IDISM tiver valor é ultimo nível
					--SET @v_TESTE = (select QUANTIDADE_ACUMULADA from @TABELA_RETORNO                 where CODCOLIGADA = @v_CODCOLIGADA and IDPRJ = @v_IDPRJ and idcmp = @v_IDCMP and IDCMPFILHA = @v_IDCMPFILHA and IDISM is null AND NIVEL = @v_NIVEL)
					--Atualiza acumulado enquanto aida não for ultimo nível
					update @TABELA_RETORNO set QUANTIDADE_ACUMULADA = QUANTIDADE_ACUMULADA * @v_SOMA where CODCOLIGADA = @v_CODCOLIGADA and IDPRJ = @v_IDPRJ and idcmp = @v_IDCMP and IDCMPFILHA = @v_IDCMPFILHA and IDISM is null AND NIVEL = @v_NIVEL --pega o a qu de nivel anterios e multiplica pelo valor de nivel atual
				END ELSE BEGIN
					--Atualiza acumulado quanto for ultimo Nível
					update @TABELA_RETORNO set QUANTIDADE_ACUMULADA = QUANTIDADE_ACUMULADA * @v_SOMA where CODCOLIGADA = @v_CODCOLIGADA and IDPRJ = @v_IDPRJ and idcmp = @v_IDCMP and IDISM  = @v_IDISM AND NIVEL = @v_NIVEL --pega o a qu de nivel anterios e multiplica pelo valor de nivel atual
				END
			END
			--passa para o proximo registro se ouver podendo ser um insumo ou uma composição
			FETCH NEXT FROM CUR_QUANT INTO @v_CODCOLIGADA, @v_IDPRJ, @v_IDCMP, @v_IDCMPFILHA, @v_IDISM, @v_QUANTIDADE, @v_QUANTIDADE_ACUMULADA, @v_IDCMP_PAI, @NIVEL
		END
		CLOSE CUR_QUANT
		DEALLOCATE CUR_QUANT
		-- passa para o proximo nivel se ouver
		FETCH NEXT FROM CUR_NIVEL INTO @v_NIVEL 
	END
	CLOSE CUR_NIVEL
	DEALLOCATE CUR_NIVEL
	--tira as composições do retorno deixando apenas os isumos
	delete @TABELA_RETORNO where IDISM is null 
	RETURN
END