IF EXISTS (SELECT 1 FROM sysobjects WHERE id = object_id(N'UFD_EST_PROD_TBL_DESC_QTDE_FAMILIA') AND xtype IN (N'FN', N'IF', N'TF'))
    DROP FUNCTION UFD_EST_PROD_TBL_DESC_QTDE_FAMILIA
GO


CREATE FUNCTION [dbo].[UFD_EST_PROD_TBL_DESC_QTDE_FAMILIA](@CD_FILIAL INT, @CD_CLI INT, @CD_FAMILIA INT, @QTDE INT, @CD_FRM_PGTO INT)
RETURNS @RETURN_REC 
TABLE(
	CD_TBL_DESC 		INT,
	TP_DESC 			INT,
	FLAG_DESC_FIX INT,
	PERC_DESC DECIMAL(10,2),
	PERC_DESC_MAX DECIMAL(10,2),
	TP_NIVEL INT,
	DS_TP_DESC VARCHAR(500),
	QTDE_PAG_AGR INT,
	MULTIPLOS INT
)
AS 
BEGIN

	DECLARE @vr_tp_cliente as table (tipo int);
	
	INSERT INTO @vr_tp_cliente (tipo)
	VALUES (0);
	
	DECLARE @vr_cli_conv int = 0;
	DECLARE @vr_cli_cred int = 0;
	DECLARE @cd_emp int = 1;
	DECLARE @var_desc_multiplo   int;
	DECLARE @var_trabalhacomdescontoformadepagamento 	int;

	DECLARE @temp_rs_tab_desc_qtde_familia AS TABLE
        (
            cd_tbl_desc         INT,
            tp_desc             INT,
            flag_desc_fix       INT,
            perc_desc           DECIMAL,
            perc_desc_max       DECIMAL,
            tp_nivel            INT,
            dt_ini              DATETIME,
            qtde_pag            INT,
            multiplos           INT,
            agregacao           INT
        );
		
	DECLARE @rs_tab_ativa AS TABLE
        (
            cd_emp      INT,
            cd_tbl_desc INT,
            tp_desc     INT
        );
	
	DECLARE @rs_tab_qtde_ativa AS TABLE
        (
            cd_emp              INT,
            cd_tbl_desc_qtde    INT,
            tp_desc             INT
        );
		
	-- verificando se cliente e um cliente conveniado
    set @vr_cli_conv = COALESCE((SELECT top 1 1 FROM rc_cli_conv_rc_cli WHERE @CD_CLI = cd_cli and sts_cred = 0 and sts_ativo = 0 ), 0);
    -- verificando se cliente e um cliente crediario
    set @vr_cli_cred = COALESCE(
                        (SELECT 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), 0);
    -- avaliando tipo de cliente
    IF @vr_cli_conv = 1 
	BEGIN
		INSERT INTO @vr_tp_cliente (tipo)
		VALUES (1),(3);
        -- set @vr_tp_cliente = ARRAY[0,1,3]; -- cliente eh convenio
		
        IF @vr_cli_cred = 1
		BEGIN
			INSERT INTO @vr_tp_cliente (tipo)
			VALUES (2);
            -- set @vr_tp_cliente = ARRAY[0,1,2,3]; -- cliente eh crediario e convenio
        END;
	END
    ELSE
	BEGIN
        IF @vr_cli_cred = 1 
		BEGIN
			INSERT INTO @vr_tp_cliente (tipo)
			VALUES (2),(3);
            -- @vr_tp_cliente = ARRAY[0,2,3]; -- cliente eh crediario
        END;
    END;
	
	-- TODAS AS TABELAS QUE NAO EXITE RECEITA
    INSERT INTO @rs_tab_ativa    
        SELECT  a.cd_emp,
                a.cd_tbl_desc,
                a.cd_tp_desconto
        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
        WHERE   @CD_EMP = a.cd_emp AND
                @CD_FILIAL = fil.cd_filial AND
                1 = a.sts_tbl_desc AND
                14 = a.cd_tp_desconto;
			

		 SET @var_trabalhacomdescontoformadepagamento = coalesce((select coalesce(valor,'0') from prc_filial_config where LOWER(cd_chave) = 'usa_desconto_pgto' and cd_filial = @CD_FILIAL), 0);
	
		 if @var_trabalhacomdescontoformadepagamento = 1 
		 BEGIN
			 
	
			 delete a
			 from @rs_tab_ativa a
			 where not exists (select b.cd_emp
			      				 from est_prod_tbl_desc_forma_pgto b  
				    			where b.cd_emp        = a.cd_emp
				    			  and b.cd_tbl_desc   = a.cd_tbl_desc   
				    			  and b.cd_forma_pgto = @CD_FRM_PGTO  
				    		   )
			 and exists (select b.cd_emp
			      				 from est_prod_tbl_desc_forma_pgto b  
				    			where b.cd_emp        = a.cd_emp
				    			  and b.cd_tbl_desc   = a.cd_tbl_desc    
				    		   )
	
		end ;
		
		
		IF EXISTS (SELECT 1 FROM @rs_tab_ativa) 
		BEGIN
        -------------------------------------------------------------------------------------------------------------------------------    
        --CONSULTANDO AS TBLS DE DESCONTO PARA VERIFICAR SE HA ALGUM DESCONTO NO NIVEL 2 - FAMILIAS    
        -------------------------------------------------------------------------------------------------------------------------------    
        INSERT INTO @temp_rs_tab_desc_qtde_familia     
            SELECT
                a.cd_tbl_desc AS cd_tbl_desc,
                a.cd_tp_desconto AS tp_desc,
                1 flag_desc_fix,
                b.perc_desc AS perc_desc,
                b.perc_desc_max AS perc_desc_max,
                2 AS tp_nivel,
                a.dt_ini,
                0,
                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_qtde_acima e ON
                        a.cd_emp = e.cd_emp AND
                        a.cd_tbl_desc = e.cd_tbl_desc
                    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 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 @rs_tab_ativa rs ON
                        a.cd_emp = rs.cd_emp AND
                        a.cd_tbl_desc = rs.cd_tbl_desc        
            WHERE   @cd_familia = b.cd_arv_merc_familia and
                    @cd_filial = fil.cd_filial and
                    @QTDE >= e.qtde_acima and
                    e.tp_cliente in ( select tipo from @vr_tp_cliente)
                    -- ORDER BY a.dt_ini  DESC, b.perc_desc DESC;
    END ;--IF EXISTS (SELECT 1 FROM rs_tab_ativa) THEN
	
    INSERT INTO @rs_tab_qtde_ativa    
                SELECT  a.cd_emp,
                        a.cd_tbl_desc_qtde,
                        a.cd_tp_desconto    
                FROM est_prod_tbl_desc_qtde a INNER JOIN
                     est_prod_tbl_desc_qtde_prc_filial fil ON
                        a.cd_emp = fil.cd_emp AND
                        a.cd_tbl_desc_qtde = fil.cd_tbl_desc_qtde 
                WHERE  @CD_EMP = a.cd_emp AND
                       @CD_FILIAL = fil.cd_filial AND
                       GETDATE() BETWEEN CONVERT(date,a.dt_ini) AND CONVERT(date,a.dt_fim) AND
                       a.sts_tbl_desc = 1 AND
                       a.cd_tp_desconto = 24 AND
                       a.qtde_leve <= @QTDE;
					   
    IF EXISTS (SELECT 1 FROM @rs_tab_qtde_ativa) 
	BEGIN
        INSERT INTO @temp_rs_tab_desc_qtde_familia     
                SELECT  cd_tbl_desc,
                        tp_desc,
                        flag_desc_fix,
                        round((1-(CAST(qtde_pag AS numeric)/@qtde))*100,2) perc_desc ,
                        round((1-(CAST(qtde_pag AS numeric)/@qtde))*100,2) perc_desc_max,
                        tp_nivel,
                        dt_ini,
                        case when agregacao = 1 then qtde_pag else 0 end qtde_pag, 0, 0 FROM (
																						SELECT
																								a.cd_tbl_desc_qtde AS cd_tbl_desc,
																								a.cd_tp_desconto AS tp_desc,
																								1 flag_desc_fix,
																								(abs(@qtde / a.qtde_leve) * a.qtde_pague) + (@qtde - (abs(@qtde / a.qtde_leve) * a.qtde_leve)) as qtde_pag,
																								2 AS tp_nivel,
																								a.dt_ini ,
																								a.agregacao
																						FROM est_prod_tbl_desc_qtde a    
																								INNER JOIN est_prod_tbl_desc_qtde_est_arv_merc_familia b ON
																									a.cd_emp = b.cd_emp AND
																									a.cd_tbl_desc_qtde = b.cd_tbl_desc_qtde    
																								INNER JOIN est_prod_tbl_desc_qtde_prc_filial fil ON
																									b.cd_emp = fil.cd_emp AND
																									b.cd_tbl_desc_qtde = fil.cd_tbl_desc_qtde    
																								INNER JOIN @rs_tab_qtde_ativa atv ON
																									a.cd_emp = atv.cd_emp AND
																									b.cd_tbl_desc_qtde = atv.cd_tbl_desc_qtde        
																						WHERE    @cd_familia = b.cd_arv_merc_familia and
																							fil.cd_filial = @cd_filial) AS TP
                    -- ORDER BY dt_ini  DESC,  perc_desc DESC;
    END;
	
    set @var_desc_multiplo   = (select top 1 multiplos 
								from @temp_rs_tab_desc_qtde_familia 
								where tp_desc = 14
								 ORDER BY dt_ini DESC, perc_desc DESC
							   );



if @var_desc_multiplo = 1
BEGIN 
    -------------------------------------------------------------------------------------------------------------------------------
    -- retornando desconto limite (resultado da funcao)
    -------------------------------------------------------------------------------------------------------------------------------
		INSERT INTO @RETURN_REC
         select TOP 1 a.cd_tbl_desc,
               a.tp_desc,
               a.flag_desc_fix,
               (((cast(@QTDE as integer ) - (cast(@QTDE as integer ) % e.qtde_acima))) * a.perc_desc) / (cast(@QTDE as integer )) perc_desc_mult,
               (((cast(@QTDE as integer ) - (cast(@QTDE as integer ) % e.qtde_acima))) * a.perc_desc_max) / (cast(@QTDE as integer )) perc_desc_max_mult,
               a.tp_nivel,
               dbo.ufd_descricao_desc(tp_desc,a.cd_tbl_desc,2),
               a.agregacao,
               a.multiplos
         FROM @temp_rs_tab_desc_qtde_familia a
               inner join est_prod_tbl_desc_qtde_acima e on a.cd_tbl_desc = e.cd_tbl_desc
         where a.multiplos  = 1
         ORDER BY perc_desc DESC;
END
ELSE 
BEGIN
		INSERT INTO @RETURN_REC
        SELECT  TOP 1 cd_tbl_desc,
                tp_desc,
                flag_desc_fix,
                perc_desc,
                perc_desc_max,
                tp_nivel,
                dbo.ufd_descricao_desc(tp_desc,a.cd_tbl_desc,2),
                qtde_pag,
                COALESCE(multiplos,0) AS multiplos
          FROM @temp_rs_tab_desc_qtde_familia a
         where COALESCE(a.multiplos,0)  = 0
         ORDER BY perc_desc DESC;
END;

	RETURN
END
GO

