IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'P_PDV_VD_CUPOMFISCAL_EMITE') AND type in (N'P', N'PC'))
begin
	DROP PROCEDURE P_PDV_VD_CUPOMFISCAL_EMITE
end
GO

/*
=======================================================================================
'Nível Classificação.: INFORMAÇÃO RESTRITA
'Versão..............: 20221025
'Job.................:  
'Nome................:  P_PDV_VD_CUPOMFISCAL_EMITE
'Objetivo............: 
'Tabelas.............: 
'Dt Criação..........: 
'Dt Ult Alteracão....: 2022-10-25
'Alterado por........: Jerfeson Barbosa
'Criado por..........: 
'=======================================================================================
FARMAGOI-34334 - 
 */

--P_PDV_VD_CUPOMFISCAL_EMITE 1 , 1, '2019-07-01', '2019-07-30'

CREATE PROCEDURE [dbo].[P_PDV_VD_CUPOMFISCAL_EMITE](@CD_EMP    INT, @CD_FILIAL INT, @DT_VD_INI DATETIME, @DT_VD_FIM DATETIME)
AS
SET NOCOUNT ON;   
-- :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
IF @DT_VD_INI > CONVERT(DATE, DATEADD(MONTH, -1, GETDATE()))
	begin 
		--SET @DT_VD_INI = CONVERT(DATE, DATEADD(MONTH, -1, GETDATE()));
		set @DT_VD_INI =dateadd(day,-10,getdate())
	end
IF @DT_VD_INI IS NULL
	BEGIN
		 SET @DT_VD_INI = CONVERT(DATE, DATEADD(MONTH, -1, GETDATE()));
    END;
-- :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
     CREATE TABLE #AUXILIAR(CD_EMP    INT
							, CD_FILIAL INT
							, CD_VD     INT
							, DT_VD     DATETIME
							, CD_NFCE   BIGINT
							, NR_PROT   VARCHAR(100)
							, DS_MOTIVO VARCHAR(1500));
     CREATE TABLE #RESULTADO(CONTINGENCIA INT
								, ERROS        INT
								, VALIDADOS    INT);
------------------------------------------------------------------------------------------------------------------------------
     DECLARE @Sql VARCHAR(8000);
     SET @Sql = ' INSERT INTO #AUXILIAR 
					SELECT
						   a.CD_EMP
						 , a.CD_FILIAL
						 , a.CD_VD
						 , a.DT_VD
						 , l.CD_NFCE
						 , '''' NR_PROT
						 , '''' DS_MOTIVO
					FROM
						PDV_VD A
						INNER JOIN PDV_VD_PDV_VD_NFCE L ON	   L.CD_EMP		= A.CD_EMP
														   AND L.CD_FILIAL	= A.CD_FILIAL
														   AND L.CD_VD		= A.CD_VD 
					WHERE  A.CD_EMP = '+CONVERT(VARCHAR, @cd_emp)+' AND A.CD_FILIAL = '+CONVERT(VARCHAR, @cd_Filial)+' AND A.DT_VD > '''+CONVERT(VARCHAR, @DT_VD_INI, 112)+'''
						   AND A.ST_VD = 0
						   AND A.FLAG_NFCE = 1 ';
     EXEC (@Sql);
     CREATE NONCLUSTERED INDEX #ix_#AUXILIAR_CD_NFCE ON #AUXILIAR(CD_NFCE);
	--CREATE NONCLUSTERED INDEX IX_PDV_VD_NFCE on  PDV_VD_NFCE (CD_NFCE)
	--	INCLUDE (NR_PROT,DS_MOTIVO) WITH(FILLFACTOR=90, DATA_COMPRESSION=PAGE, ONLINE=ON);
     SET @Sql = ' UPDATE l
       SET
           NR_PROT = c.NR_PROT
         , DS_MOTIVO = C.DS_MOTIVO
     FROM #AUXILIAR l
          INNER JOIN PDV_VD_NFCE C WITH (nolock) ON C.CD_NFCE = L.CD_NFCE
		WHERE  C.CD_EMP = '+CONVERT(VARCHAR, @cd_emp)+' AND C.CD_FILIAL = '+CONVERT(VARCHAR, @cd_Filial)  +' AND c.DT_VD > '''+CONVERT(VARCHAR, @DT_VD_INI, 112)+''''
	EXEC (@Sql);
------------------------------------------------------------------------------------------------------------------------------
     INSERT INTO #RESULTADO
            SELECT
                   COUNT(CD_VD) AS CONTINGENCIA
                 , 0 ERROS
                 , 0 VALIDADOS
            FROM
                 #AUXILIAR
            WHERE LEN(COALESCE(NR_PROT, '')) < 4
                  AND CHARINDEX(UPPER('contigencia'), UPPER(COALESCE(DS_MOTIVO, 'contigencia offline'))) > 0; 
------------------------------------------------------------------------------------------------------------------------------
     INSERT INTO #RESULTADO
            SELECT
                   0 CONTINGENCIA
                 , COUNT(CD_VD) AS ERROS
                 , 0 VALIDADOS
            FROM
                 #AUXILIAR
            WHERE LEN(COALESCE(NR_PROT, '')) < 4
                  AND CHARINDEX(UPPER('contigencia'), UPPER(DS_MOTIVO)) = 0;
------------------------------------------------------------------------------------------------------------------------------
     INSERT INTO #RESULTADO
            SELECT
                   0 CONTINGENCIA
                 , 0 ERROS
                 , COUNT(CD_VD) AS VALIDADOS
            FROM
                 #AUXILIAR
            WHERE DT_VD > @DT_VD_FIM
                  AND LEN(NR_PROT) > 4;
------------------------------------------------------------------------------------------------------------------------------
     SELECT
            COUNT(CD_VD) AS EMITIDOS
          , SUM(VLR_DINH) AS VLR_DINH
          , SUM(VLR_CARTAO) AS VLR_CARTAO
          , SUM(VLR_CHQS) AS VLR_CHEQUES
          , SUM(VLR_DESC) AS VLR_DESC
          , SUM(VLR_LIQ_VD) AS VLR_VEND
          ,(SELECT SUM(CONTINGENCIA) FROM #RESULTADO ) AS CONTIGENCIA
          ,(SELECT SUM(ERROS) FROM #RESULTADO ) AS ERRO
          ,(SELECT SUM(VALIDADOS) FROM #RESULTADO) AS VALIDADOS
     FROM
          PDV_VD
     WHERE CD_EMP = @CD_EMP
           AND CD_FILIAL = @CD_FILIAL
           AND DT_VD > @DT_VD_FIM
           AND FLAG_NFCE = 1
           AND ST_VD = 0  ;

	drop table #AUXILIAR
	drop table #RESULTADO  

GO


