 IF  EXISTS (SELECT * FROM sys.objects 
            WHERE object_id = OBJECT_ID(N'[dbo].[UFD_EST_PROD_TBL_DESC_QTDE]') 
            AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

/****** Object:  UserDefinedFunction [dbo].[UFD_DESCRICAO_DESC]    Script Date: 05/02/2020 ******/
DROP FUNCTION [dbo].[UFD_EST_PROD_TBL_DESC_QTDE]
GO

CREATE FUNCTION UFD_EST_PROD_TBL_DESC_QTDE(@CD_EMP INT, @CD_FILIAL INT, @CD_CLI INT, @CD_PROD INT, @QTDE_PRODUTO DECIMAL, @IS_RECEITA INT, @CD_FRM_PGTO INT)

RETURNS @RETURN_REC TABLE(CD_TBL_DESC 		INT,
						  TP_DESC 			INT,
						  FLAG_DESC_FIX 	INT,
						  PERC_DESC			MONEY,
						  PERC_DESC_MAX		MONEY,
						  TP_NIVEL			INT,
						  AGREGACAO         INT)
AS 
BEGIN

-- VERSÃO 23/09/2020

	-- SELECT * FROM UFD_EST_PROD_TBL_DESC_QTDE(1, 1, 1,1,3, 0)

	DECLARE @TP_CLIENTE VARCHAR(20)
	DECLARE @CLI_CONV   BIT
	DECLARE @CLI_CRED   BIT
	DECLARE @CLI_FIDEL  BIT
	DECLARE @Hoje       DATE
	DECLARE @QTDE_ACIMA MONEY=0
	DECLARE @MULTIPLO   INT=0
	DECLARE @AGREGACAO  INT=0
	DECLARE @TRABALHACOMDESCONTOFORMADEPAGAMENTO INT = 0 

	SET @Hoje = convert(date,GETDATE());

	--vr_cli_conv = 0; -- 0 false 1 true
	--vr_cli_cred = 0; -- 0 false 1 true

	SET @TP_CLIENTE = '0' -- 0 COMUM, 1 CONVENIO, 2 CREDIARIO , 3 CREDIARIO E CONVNEIO, 4 FIDELIDADE
	SET @CLI_CONV   = 0   -- 0 false 1 true
	SET @CLI_CRED   = 0   -- 0 false 1 true
	SET @CLI_FIDEL  = 0   -- 0 FALSE 1 TRUE

	DECLARE @RS_TMP_DESC TABLE(
		CD_TBL_DESC 		INT,
		TP_DESC 			INT,
		FLAG_DESC_FIX 		INT,
		PERC_DESC			MONEY,
		PERC_DESC_MAX		MONEY,
		TP_NIVEL			INT,
		DT_INI              DATETIME,
		AGREGACAO           INT
	)

	-------------------------------------------------------------------------------------------------------------------------------    
	--DECLARANDO TABELA DE ARVORE MERCADOLOGICA DO PRODUTO    
	-------------------------------------------------------------------------------------------------------------------------------    
	DECLARE @RS_TAB_ATIVA TABLE ( CD_EMP      INT,    
								  CD_TBL_DESC INT,    
								  TP_DESC     INT,
								  QTDE_ACIMA  MONEY,
								  MULTIPLO	  INT,
								  AGREGACAO   INT)    

	-------------------------------------------------------------------------------------------------------------------------------    
	--DECLARANDO TABELA DE ARVORE MERCADOLOGICA DO PRODUTO    
	-------------------------------------------------------------------------------------------------------------------------------    
	DECLARE @EST_PROD_ARV_MERC TABLE (  CD_EMP               INT,    
										CD_PROD              INT,    
										CD_ARV_MERC_CATEG    INT,    
										CD_ARV_MERC_LINHA    INT,    
										CD_MC                INT,    
										CD_ARV_MERC_FAMILIA  INT,            
										CD_FABRIC            INT)    


	-------------------------------------------------------------------------------------------------------------------------------    
	--BUSCANDO ARVORE MERC. DO PRODUTO    
	-------------------------------------------------------------------------------------------------------------------------------     
	INSERT INTO @EST_PROD_ARV_MERC    			
		SELECT     
			ARV.CD_EMP     ,    
			ARV.CD_PROD     ,    
			ARV.CD_ARV_MERC_CATEG  ,    
			ARV.CD_ARV_MERC_LINHA  ,    
			ARV.CD_MC     ,    
			ARV.CD_ARV_MERC_FAMILIA  ,            
			P.CD_FABRIC     
		FROM EST_PROD_EST_ARV_MERCADOLOGICA ARV 
			 INNER JOIN EST_PROD P ON ARV.CD_EMP  = P.CD_EMP    
								  AND ARV.CD_PROD = P.CD_PROD    
		WHERE P.CD_EMP = @CD_EMP     
		  AND P.CD_PROD = @CD_PROD 


	-------------------------------------------------------------------------------------------------------------------------------    
	--EXISTE ARVORE MERCADOLOGICA    
	-------------------------------------------------------------------------------------------------------------------------------     
	IF EXISTS(SELECT 1 FROM @EST_PROD_ARV_MERC)
	BEGIN


		SET @TRABALHACOMDESCONTOFORMADEPAGAMENTO = ISNULL (( select valor from prc_filial_config where cd_chave = 'USA_DESCONTO_PGTO' and cd_filial = @CD_FILIAL), 0)

	

		IF EXISTS(SELECT COUNT(1) FROM RC_CLI_CONV_RC_CLI WHERE CD_EMP = 1 AND CD_CLI = @CD_CLI AND STS_CRED = 1 AND STS_ATIVO = 1)
			SET @CLI_CONV   = 1
			
		IF EXISTS(SELECT COUNT(1)
					FROM RC_CLI C 
				         INNER JOIN RC_CLI_CREDIARIO CR ON C.CD_CLI = CR.CD_CLI
                   WHERE @CD_CLI = C.CD_CLI 
					 AND C.FLAG_CLIENTE_CREDIARIO = 0 
					 AND CR.STS_CRED = 0 
					 AND COALESCE(CR.CD_MOT_BLOQ_CLI, 0) = 0)

			SET @CLI_CRED   = 1

		IF EXISTS (SELECT COALESCE(FLAG_CLI_FIDEL,0) FROM RC_CLI WHERE CD_EMP = @CD_EMP AND CD_CLI = @CD_CLI)		
			SET @CLI_FIDEL  = 1


	IF @CLI_FIDEL  = 1
		IF @CLI_CONV = 1
			BEGIN
				SET @TP_CLIENTE = '0,1,3,4'
				IF @CLI_CRED = 1
					SET @TP_CLIENTE = '0,1,2,3,4'
			END
		ELSE 
		
		IF @CLI_CRED = 1
			SET @TP_CLIENTE = '0,2,3,4'
		ELSE 	
			SET @TP_CLIENTE = '0,4'
	else 	
		
		IF @CLI_CONV = 1
			BEGIN
				SET @TP_CLIENTE = '0,1,3'
					IF @CLI_CRED = 1
						SET @TP_CLIENTE = '0,1,2,3'
			END
		ELSE
			IF @CLI_CRED = 1
				SET @TP_CLIENTE = '0,2,3'





		INSERT INTO @RS_TAB_ATIVA    
			SELECT  A.CD_EMP,    
					A.CD_TBL_DESC,    
					A.CD_TP_DESCONTO,
					E.QTDE_ACIMA,
					A.FLAG_APLICA_DESCONTO_QTDE_MULTIPLO,
					A.FLAG_AGREGAR_DESC_QTDE_AOS_DESCONTOS_EXISTENTES
			FROM EST_PROD_TBL_DESC A 
				 INNER JOIN EST_PROD_TBL_DESC_PRC_FILIAL FIL ON A.CD_EMP = FIL.CD_EMP 
															AND A.CD_TBL_DESC = FIL.CD_TBL_DESC 
				 INNER JOIN EST_PROD_TBL_DESC_QTDE_ACIMA E ON A.CD_EMP = E.CD_EMP 
														  AND A.CD_TBL_DESC = E.CD_TBL_DESC  
			WHERE @CD_EMP    = A.CD_EMP 
			  AND @CD_FILIAL = FIL.CD_FILIAL 
			  AND @Hoje BETWEEN A.DT_INI AND A.DT_FIM 
			  AND 1          = A.STS_TBL_DESC 
			  AND 14         = A.CD_TP_DESCONTO 
			  AND 0          = A.FLAG_DESC_RECEITA 
			  AND @QTDE_PRODUTO >= E.QTDE_ACIMA 
			  AND E.TP_CLIENTE IN (SELECT ID FROM UFD_PRM_IN_STRING(@TP_CLIENTE)  );


		IF @IS_RECEITA > 0
		BEGIN
			INSERT INTO @RS_TAB_ATIVA    
			SELECT  A.CD_EMP,    
					A.CD_TBL_DESC,    
					A.CD_TP_DESCONTO,
					E.QTDE_ACIMA,
					A.FLAG_APLICA_DESCONTO_QTDE_MULTIPLO,
					A.FLAG_AGREGAR_DESC_QTDE_AOS_DESCONTOS_EXISTENTES
			FROM EST_PROD_TBL_DESC A 
			     INNER JOIN EST_PROD_TBL_DESC_PRC_FILIAL FIL ON A.CD_EMP = FIL.CD_EMP 
														    AND A.CD_TBL_DESC = FIL.CD_TBL_DESC 
				 INNER JOIN EST_PROD_TBL_DESC_QTDE_ACIMA E ON A.CD_EMP = E.CD_EMP 
														  AND A.CD_TBL_DESC = E.CD_TBL_DESC  
			WHERE @CD_EMP    = A.CD_EMP 
			  AND @CD_FILIAL = FIL.CD_FILIAL 
			  AND @Hoje BETWEEN A.DT_INI AND A.DT_FIM 
			  AND 1          = A.STS_TBL_DESC 
			  AND 14         = A.CD_TP_DESCONTO 
			  AND 1          = A.FLAG_DESC_RECEITA 
			  AND @QTDE_PRODUTO >= E.QTDE_ACIMA 
			  AND E.TP_CLIENTE IN (SELECT ID FROM UFD_PRM_IN_STRING(@TP_CLIENTE)  );
		END

		 

	  IF @TRABALHACOMDESCONTOFORMADEPAGAMENTO = 1  
	   BEGIN   
			 
			DELETE FROM RS_TAB_ATIVA    
			  from @RS_TAB_ATIVA RS_TAB_ATIVA
			 WHERE NOT EXISTS (SELECT B.CD_EMP
			      				 FROM EST_PROD_TBL_DESC_FORMA_PGTO B  
				    			WHERE B.CD_EMP        = RS_TAB_ATIVA.CD_EMP
				    			  AND B.CD_TBL_DESC   = RS_TAB_ATIVA.CD_TBL_DESC   
				    			  AND B.CD_FORMA_PGTO = @CD_FRM_PGTO  
				    			    ) ;
	
		END 




		-------------------------------------------------------------------------------------------------------------------------------    
		--EXISTE TABELA ATIVA  
		-------------------------------------------------------------------------------------------------------------------------------     

		IF EXISTS(SELECT 1 FROM @RS_TAB_ATIVA)
		BEGIN
			-------------------------------------------------------------------------------------------------------------------------------    
			--CONSULTANDO AS TBLS DE DESCONTO PARA VERIFICAR SE HA ALGUM DESCONTO NO NIVEL 1 - PRODUTOS    
			-------------------------------------------------------------------------------------------------------------------------------    
			INSERT INTO @RS_TMP_DESC     
				SELECT    
						A.CD_TBL_DESC    AS CD_TBL_DESC,    
						A.CD_TP_DESCONTO AS TP_DESC,    
						A.FLAG_DESC_FIX,    
						B.PERC_DESC      AS PERC_DESC,    
						B.PERC_DESC_MAX  AS PERC_DESC_MAX,    
						1			     AS TP_NIVEL,
					    A.DT_INI,
						TBA.AGREGACAO           
				FROM EST_PROD_TBL_DESC A 
						INNER JOIN EST_PROD_TBL_DESC_EST_PROD B ON    
							A.CD_EMP = B.CD_EMP AND    
							A.CD_TBL_DESC = B.CD_TBL_DESC    
						INNER JOIN @RS_TAB_ATIVA TBA ON    
							A.CD_EMP = TBA.CD_EMP AND    
							A.CD_TBL_DESC = TBA.CD_TBL_DESC     
						INNER JOIN @EST_PROD_ARV_MERC ARV ON    
							B.CD_EMP = ARV.CD_EMP AND    
							B.CD_PROD = ARV.CD_PROD  
			
			-------------------------------------------------------------------------------------------------------------------------------    
			--CONSULTANDO AS TBLS DE DESCONTO PARA VERIFICAR SE HA ALGUM DESCONTO NO NIVEL 2 - FAMILIAS    
			-------------------------------------------------------------------------------------------------------------------------------    				
			INSERT INTO @RS_TMP_DESC     
				SELECT    
						A.CD_TBL_DESC    AS CD_TBL_DESC,    
						A.CD_TP_DESCONTO AS TP_DESC,    
						A.FLAG_DESC_FIX,    
						B.PERC_DESC      AS PERC_DESC,    
						B.PERC_DESC_MAX  AS PERC_DESC_MAX,    
						2				  AS TP_NIVEL,
					    A.DT_INI,
						TBA.AGREGACAO    
				FROM EST_PROD_TBL_DESC A
						INNER JOIN EST_PROD_TBL_DESC_EST_ARV_MERC_FAMILIA B ON    
							A.CD_EMP = B.CD_EMP AND    
							A.CD_TBL_DESC = B.CD_TBL_DESC    
						INNER JOIN @RS_TAB_ATIVA TBA ON    
							A.CD_EMP = TBA.CD_EMP AND    
							A.CD_TBL_DESC = TBA.CD_TBL_DESC     
						INNER JOIN @EST_PROD_ARV_MERC ARV ON    
							B.CD_EMP = ARV.CD_EMP AND    
							B.CD_ARV_MERC_FAMILIA = ARV.CD_ARV_MERC_FAMILIA 
			-------------------------------------------------------------------------------------------------------------------------------        
			--CONSULTANDO AS TBLS DE DESCONTO PARA VERIFICAR SE HA ALGUM DESCONTO NO NIVEL 3 - MARCAS    
			-------------------------------------------------------------------------------------------------------------------------------    				
			INSERT INTO @RS_TMP_DESC     
				SELECT    
						A.CD_TBL_DESC    AS CD_TBL_DESC,    
						A.CD_TP_DESCONTO AS TP_DESC,    
						A.FLAG_DESC_FIX,    
						B.PERC_DESC      AS PERC_DESC,    
						B.PERC_DESC_MAX  AS PERC_DESC_MAX,    
						3                AS TP_NIVEL,
					    A.DT_INI,
						TBA.AGREGACAO      
				FROM EST_PROD_TBL_DESC A
					 INNER JOIN EST_PROD_TBL_DESC_EST_MC B ON A.CD_EMP = B.CD_EMP 
														  AND A.CD_TBL_DESC = B.CD_TBL_DESC    
					 INNER JOIN @RS_TAB_ATIVA TBA ON A.CD_EMP = TBA.CD_EMP 
												 AND A.CD_TBL_DESC = TBA.CD_TBL_DESC   
                     INNER JOIN @EST_PROD_ARV_MERC ARV ON B.CD_EMP = ARV.CD_EMP 
													  AND B.CD_MC = ARV.CD_MC 
			-------------------------------------------------------------------------------------------------------------------------------    
			--CONSULTANDO AS TBLS DE DESCONTO PARA VERIFICAR SE HA ALGUM DESCONTO NO NIVEL 4 - FABRICANTES    
			-------------------------------------------------------------------------------------------------------------------------------    
			INSERT INTO @RS_TMP_DESC     
				SELECT   
						A.CD_TBL_DESC    AS CD_TBL_DESC,    
						A.CD_TP_DESCONTO AS TP_DESC,    
						A.FLAG_DESC_FIX,    
						B.PERC_DESC      AS PERC_DESC,    
						B.PERC_DESC_MAX  AS PERC_DESC_MAX,   
						4			     AS TP_NIVEL,
					    A.DT_INI,
						TBA.AGREGACAO     
				FROM EST_PROD_TBL_DESC A
						INNER JOIN EST_PROD_TBL_DESC_EST_PROD_FABRIC B ON A.CD_EMP = B.CD_EMP 
																	  AND A.CD_TBL_DESC = B.CD_TBL_DESC 
						INNER JOIN @RS_TAB_ATIVA TBA ON B.CD_EMP = TBA.CD_EMP 
													AND B.CD_TBL_DESC = TBA.CD_TBL_DESC 
						INNER JOIN @EST_PROD_ARV_MERC ARV ON B.CD_EMP = ARV.CD_EMP 
														 AND B.CD_FABRIC = ARV.CD_FABRIC
			------------------------------------------------------------------------------------------------------------------------------    
			--CONSULTANDO AS TBLS DE DESCONTO PARA VERIFICAR SE HA ALGUM DESCONTO NO NIVEL 5 - CATEGORIAS    
			-------------------------------------------------------------------------------------------------------------------------------    
			INSERT INTO @RS_TMP_DESC     
				SELECT   
						A.CD_TBL_DESC    AS CD_TBL_DESC,    
						A.CD_TP_DESCONTO AS TP_DESC,    
						A.FLAG_DESC_FIX,    
						B.PERC_DESC      AS PERC_DESC,    
						B.PERC_DESC_MAX  AS PERC_DESC_MAX,   
						5                AS TP_NIVEL,
					    A.DT_INI,
						TBA.AGREGACAO    
				FROM EST_PROD_TBL_DESC A 
						INNER JOIN EST_PROD_TBL_DESC_EST_ARV_MERC_CATEGORIA B ON A.CD_EMP = B.CD_EMP 
																			 AND A.CD_TBL_DESC = B.CD_TBL_DESC 
						INNER JOIN @RS_TAB_ATIVA TBA ON A.CD_EMP = TBA.CD_EMP 
													AND B.CD_TBL_DESC = TBA.CD_TBL_DESC   
						INNER JOIN @EST_PROD_ARV_MERC ARV ON B.CD_EMP = ARV.CD_EMP 
														 AND B.CD_ARV_MERC_CATEG = ARV.CD_ARV_MERC_CATEG
			
					
							
			-------------------------------------------------------------------------------------------------------------------------------    
			--CONSULTANDO AS TBLS DE DESCONTO PARA VERIFICAR SE HA ALGUM DESCONTO NO NIVEL 6 - LINHAS    
			-------------------------------------------------------------------------------------------------------------------------------  

			INSERT INTO @RS_TMP_DESC     
				SELECT   
						A.CD_TBL_DESC    AS CD_TBL_DESC,    
						A.CD_TP_DESCONTO AS TP_DESC,    
						A.FLAG_DESC_FIX,    
						B.PERC_DESC      AS PERC_DESC,    
						B.PERC_DESC_MAX  AS PERC_DESC_MAX,   
						6                AS TP_NIVEL,
					    A.DT_INI,
						TBA.AGREGACAO  
				FROM EST_PROD_TBL_DESC A
						INNER JOIN EST_PROD_TBL_DESC_EST_ARV_MERC_LINHA B ON A.CD_EMP = B.CD_EMP 
																		 AND A.CD_TBL_DESC = B.CD_TBL_DESC    
						INNER JOIN @RS_TAB_ATIVA TBA ON A.CD_EMP = TBA.CD_EMP 
													AND B.CD_TBL_DESC = TBA.CD_TBL_DESC 
						INNER JOIN @EST_PROD_ARV_MERC ARV ON B.CD_EMP = ARV.CD_EMP	
														 AND B.CD_ARV_MERC_LINHA = ARV.CD_ARV_MERC_LINHA
						
		END
	END

	
	SELECT @QTDE_ACIMA = QTDE_ACIMA,
		   @MULTIPLO   = MULTIPLO
      FROM @RS_TAB_ATIVA
     WHERE CD_TBL_DESC = (SELECT TOP 1 CD_TBL_DESC 
						    FROM  @RS_TMP_DESC  
						   ORDER BY TP_NIVEL  ASC,
									PERC_DESC DESC,  
									DT_INI    DESC)


	IF @MULTIPLO= 1
	BEGIN
		    INSERT INTO @RETURN_REC
			SELECT TOP 1
					     CD_TBL_DESC
					   , TP_DESC
					   , 1 FLAG_DESC_FIX
					   , (((@QTDE_PRODUTO - (@QTDE_PRODUTO % @QTDE_ACIMA))) * PERC_DESC) / (@QTDE_PRODUTO) PERC_DESC_MULT
					   , (((@QTDE_PRODUTO - (@QTDE_PRODUTO % @QTDE_ACIMA))) * PERC_DESC_MAX) / (@QTDE_PRODUTO) PERC_DESC_MAX_MULT
					   , TP_NIVEL
					   , AGREGACAO
			  FROM @RS_TMP_DESC
			 ORDER BY  TP_NIVEL ASC,
					   PERC_DESC DESC, 
					   DT_INI DESC;
			
	END 
	ELSE 
		BEGIN
			  INSERT INTO @RETURN_REC
			  SELECT TOP 1 CD_TBL_DESC, 
			             TP_DESC, 
			  		     1 FLAG_DESC_FIX, 
			  		     PERC_DESC, 
			  		     PERC_DESC_MAX, 
			  		     TP_NIVEL,
			  		     AGREGACAO
			    FROM @RS_TMP_DESC 
			   ORDER BY  TP_NIVEL ASC,
					     PERC_DESC DESC, 
					     DT_INI DESC;
			   
		END 

	
	RETURN
END