IF OBJECT_ID('P_INCLUI_PONTOS_FIDEL') IS NOT NULL
BEGIN
    DROP PROCEDURE P_INCLUI_PONTOS_FIDEL;
END;
GO

--EXECUTE P_INCLUI_PONTOS_FIDEL 0,0,0,0, '11/01/2008','12/31/2008'
CREATE PROCEDURE [dbo].[P_INCLUI_PONTOS_FIDEL] (@CD_EMP	    INT      = 1,
											    @CD_VD     INT       = 0,
											    @CD_FILIAL INT       = 0,
											    @CD_IT     INT       = 0,
											    @DT_INI    DATETIME  = NULL,
											    @DT_FIM    DATETIME  = NULL
											   )
AS
SET NOCOUNT ON;
  
  
	IF EXISTS (SELECT * FROM PRC_EMP_CONFIG WHERE CD_EMP = 1 AND CD_CHAVE = 'FLAG_IGNORA_CONTROLE_CREDITO_DEBITO_PONTOS_FIDEL' AND VALOR = 1)
	BEGIN
		RETURN;
	END

  
  BEGIN TRY
        BEGIN

	

            DECLARE @CD_EMP_RS INT;
            DECLARE @CD_FILIAL_RS INT;
            DECLARE @CD_VD_RS INT;
            DECLARE @CD_IT_RS INT;
            DECLARE @QT_TOT_PT_RS MONEY;
            DECLARE @QT_PT_RS MONEY;
		  
		  if @DT_INI =  null
		  begin
			 set @DT_INI =getdate()-10
		  end
		  if @DT_FIM =  null
		  begin
			 set @DT_FIM =getdate()
		  end
		  Create Table #TmpPontuacao(  CD_CLI INT, CD_FILIAL INT, CD_VD INT, CD_IT INT, QTDE_PONTOS_GANHOS money, QTDE_PONTO_FIDEL money)

		  if @DT_INI is null
		  begin

			 INSERT INTO #TMPPONTUACAO
                SELECT  DISTINCT
				B.CD_CLI,
                    A.CD_FILIAL,
                    A.CD_VD,
                    A.CD_IT,
                    ROUND((A.TOT_IT / CPL.QTDE_PONTO_FIDEL), 2) QTDE_PONTOS_GANHOS,
                    CPL.QTDE_PONTO_FIDEL
                FROM 
				 PDV_VD_IT A
                     INNER JOIN PDV_VD B ON 
				 A.CD_EMP = B.CD_EMP AND 
				 A.CD_FILIAL = B.CD_FILIAL AND 
				 A.CD_VD = B.CD_VD AND 
				 A.DT_VD = B.DT_VD
				  
                     INNER JOIN EST_PROD_CPL CPL ON 
				 A.CD_EMP	   = CPL.CD_EMP AND 
				 A.CD_FILIAL = CPL.CD_FILIAL AND 
				 A.CD_PROD   = CPL.CD_PROD 

				 LEFT JOIN  PDV_VD_IT_FIDELI_CARTAO C ON
				 A.CD_EMP	   = C.CD_EMP AND 
				 A.CD_FILIAL = C.CD_FILIAL AND 
				 A.CD_VD	   = C.CD_VD AND 
				 A.CD_IT	   = C.CD_IT
				 
				 INNER JOIN RC_CLI_FIDELI_CARTAO FICA ON
				 FICA.CD_EMP = B.CD_EMP AND
				 FICA.CD_CLI = B.CD_CLI

                WHERE
					A.CD_EMP=1				
					AND (CASE
							  WHEN @CD_FILIAL = 0
							  THEN B.CD_FILIAL
							  ELSE @CD_FILIAL
						  END) = B.CD_FILIAL
					AND (CASE WHEN @CD_VD = 0 THEN B.CD_VD
							  ELSE @CD_VD END) = B.CD_VD   
					AND A.DT_VD BETWEEN GETDATE()-5  AND GETDATE() 
					AND B.ST_VD = 0
					AND CPL.QTDE_PONTO_FIDEL > 0    
					AND FICA.DT_CAD < B.DT_VD					 
					AND C.CD_EMP IS NULL
					AND A.ST_IT = 1;

				DELETE FROM B
				FROM
				    #TmpPontuacao B
				    INNER JOIN RC_CLI r ON  
				    b.CD_CLI = r.CD_CLI
				WHERE
				 r.CD_EMP=1
				AND (r.TP_CONS_FINAL = 1 
					   OR r.CONS_FINAL= 1);


		  end 
		  else
		  begin
			  insert into #TmpPontuacao
			  SELECT DISTINCT
				B.CD_CLI,  
                    a.CD_FILIAL,
                    a.CD_VD,
                    a.CD_IT,
                    ROUND((a.TOT_IT / cpl.QTDE_PONTO_FIDEL), 2),
                    cpl.QTDE_PONTO_FIDEL
                FROM 
				 PDV_VD_IT A
                     INNER JOIN PDV_VD b ON 
				 a.CD_EMP = b.CD_EMP AND 
				 a.CD_FILIAL = b.CD_FILIAL AND 
				 a.CD_VD = b.CD_VD AND 
				 a.DT_VD = b.DT_VD
				  
                     INNER JOIN EST_PROD_CPL cpl ON 
				 a.CD_EMP = cpl.CD_EMP AND 
				 a.CD_FILIAL = cpl.CD_FILIAL AND 
				 a.CD_PROD = cpl.CD_PROD
                      

				 left join  PDV_VD_IT_FIDELI_CARTAO c on 
				 a.CD_EMP	   = c.CD_EMP AND 
				 a.CD_FILIAL = c.CD_FILIAL AND 
				 a.CD_VD	   = c.CD_VD AND 
				 a.CD_IT	   = c.CD_IT
				 
				 INNER JOIN RC_CLI_FIDELI_CARTAO FICA ON
				 FICA.CD_EMP = B.CD_EMP AND
				 FICA.CD_CLI = B.CD_CLI

                WHERE
		
				a.CD_EMP=1 			 
                    AND (CASE
                              WHEN @CD_FILIAL = 0
                              THEN b.CD_FILIAL
                              ELSE @CD_FILIAL
                          END) = b.CD_FILIAL

                     AND (CASE WHEN @CD_VD = 0 THEN b.CD_VD
                              ELSE @CD_VD END) = b.CD_VD 
				 AND  a.DT_VD BETWEEN @DT_INI  AND @DT_FIM 
                     AND b.ST_VD = 0
                     AND c.cd_emp is null 
				  AND cpl.QTDE_PONTO_FIDEL > 0                     
				AND FICA.DT_CAD < B.DT_VD
				AND A.ST_IT = 1 
				 ; 

				DELETE FROM B
				FROM
				    #TmpPontuacao B
				    INNER JOIN RC_CLI r ON  
				    b.CD_CLI = r.CD_CLI
				WHERE
				 r.CD_EMP=1
				AND (r.TP_CONS_FINAL = 1 
					   OR r.CONS_FINAL= 1);
		  end
		  ------------------------------------------------------------------------------------------------------------------------
		  -- Gravando a pontuação de vendas
		  ------------------------------------------------------------------------------------------------------------------------
		  PRINT 'P_INCLUI_PONTOS_FIDEL - SELECT 1 '+CONVERT(VARCHAR, GETDATE(), 108);
        
	       DECLARE CurItens --Nome do cursor
			 CURSOR FAST_FORWARD READ_ONLY FOR 
			 select
			    1 CD_EMP,
                    CD_FILIAL,
                    CD_VD,
                    CD_IT,
                    QTDE_PONTOS_GANHOS,
                    QTDE_PONTO_FIDEL
			 FROM #TmpPontuacao;
		

		--Abrindo cursor
            OPEN CurItens;
		--Atribuindo valores do select nas variáveis
            FETCH NEXT FROM CurItens INTO @CD_EMP_RS, @CD_FILIAL_RS, @CD_VD_RS, @CD_IT_RS, @QT_TOT_PT_RS, @QT_PT_RS;

		--Iniciando laço
            WHILE @@FETCH_STATUS = 0
                BEGIN
                    PRINT 'INSERT 2 '+CONVERT(VARCHAR, GETDATE(), 108);
                    INSERT INTO PDV_VD_IT_FIDELI_CARTAO (CD_EMP, CD_FILIAL, CD_VD, CD_IT, 
												QTDE_PONTO_FIDEL, ENVIO_FIDEL, VLR_PONTO_IT )
                    VALUES
                    (@CD_EMP_RS, @CD_FILIAL_RS, @CD_VD_RS, @CD_IT_RS, @QT_PT_RS, 0, @QT_TOT_PT_RS);

			--Próxima linha do cursor
                    FETCH NEXT FROM CurItens INTO @CD_EMP_RS, @CD_FILIAL_RS, @CD_VD_RS, @CD_IT_RS, @QT_TOT_PT_RS, @QT_PT_RS;
                END;
            CLOSE CurItens;
            DEALLOCATE CurItens;
        END;
    END TRY
    BEGIN CATCH
	   
	   
	   DECLARE @ErrorMessage VARCHAR(4000)=ERROR_MESSAGE();  
	   DECLARE @ErrorSeverity INT=ERROR_SEVERITY();  
	   DECLARE @ErrorState INT=ERROR_STATE();  


	   INSERT INTO ADM_ERRO (numero, severidade, estado, rotina, linha, mensagem, dtErro )
        SELECT ERROR_NUMBER() AS numero,
                @ErrorSeverity AS severidade,
                @ErrorState AS estado,
                ERROR_PROCEDURE() AS rotina,
                ERROR_LINE() AS linha,
                @ErrorMessage AS mensagem,
                GETDATE() AS dtErro;
	   
	   RAISERROR (@ErrorMessage, 
			    @ErrorSeverity,
			    @ErrorState );

    END CATCH;
    
    
    PRINT 'P_INCLUI_PONTOS_FIDEL FINALIZADA!';