/****** Object:  StoredProcedure [dbo].[P_ATUALIZA_NOTAS_PAINEL]    Script Date: 04/08/2014 09:57:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_ATUALIZA_NOTAS_PAINEL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_ATUALIZA_NOTAS_PAINEL]
GO


/*
=======================================================================================
'Nível Classificação.: INFORMAÇÃO RESTRITA
'Versão..............: 241108
'Job.................:  
'Nome................: P_ATUALIZA_NOTAS_PAINEL
'Objetivo............: Procedimento utilizado para atualizar o status da nota na tabela PDV_VD_NFCE com base no XML das tabelas PDV_VD_NFCE_ARQ_DANFE e PDV_VD_NFCE_ARQ_DANFE_CANCEL
'Tabelas.............: PDV_VD_NFCE, PDV_VD_NFCE_ARQ_DANFE , PDV_VD_NFCE_ARQ_DANFE_CANCEL
'Dt Criação..........: 01/11/2024
'Dt Ult Alteracão....: 10/12/2024
'Criado por..........: Marcos Barros
'Alterado por........: Lucas Bischoff
'=======================================================================================
*/

CREATE PROCEDURE [dbo].[P_ATUALIZA_NOTAS_PAINEL]
   @CD_VD INT = NULL
AS
BEGIN

	DECLARE @CD_NFCE INT, @CD_DANFE BIGINT, @CD_FILIAL INT , @CD_CX INT , @SERIE INT , @NR_NF INT , @COD_STAT INT, @CD_VD_CURS INT;
	
	IF (@CD_VD IS NOT NULL)
	print 'CODIGO CD_VD = ' + cast(@CD_VD as varchar);
	

    -- Lista notas que serão alteradas
    DECLARE cursorNotas CURSOR FOR
    WITH XMLNAMESPACES ('http://www.w3.org/2003/05/soap-envelope' AS env,
						'http://www.portalfiscal.inf.br/nfe/wsdl/NFeInutilizacao4' AS wsd,
						'http://www.portalfiscal.inf.br/nfe' AS nfe),
	DANFE_XML AS (
				  -- Busca notas autorizadas
				  SELECT 
							 D.CD_NFCE,
							 D.CD_DANFE,
							 C.CD_FILIAL,
							 C.CD_CX,
							 C.SERIE,
							 C.NR_NF,
							 TRY_CAST(CONVERT(VARCHAR(MAX),(D.XML_DANFE)) AS XML) AS XML_NOTA,
							 A.CD_VD
						 FROM 
							PDV_VD_NFCE_ARQ_DANFE (NOLOCK) D
							LEFT JOIN PDV_VD_PDV_VD_NFCE (NOLOCK) L ON D.CD_NFCE = L.CD_NFCE 
							LEFT JOIN PDV_VD (NOLOCK) A ON L.CD_EMP = A.CD_EMP AND L.CD_FILIAL = A.CD_FILIAL AND L.CD_VD = A.CD_VD
							LEFT JOIN PDV_VD_NFCE (NOLOCK) C ON C.CD_NFCE = L.CD_NFCE AND C.CD_FILIAL = L.CD_FILIAL
						 WHERE 
							 A.CD_EMP = 1 
							 AND A.FLAG_NFCE = 1 
							 AND ((@CD_VD IS NULL AND D.FLAG_TRIAGEM_NOTA = 0)
								OR (@CD_VD IS NOT NULL AND A.CD_VD = @CD_VD)
									)
						
						
							
				UNION ALL
				-- Busca notas canceladas
				SELECT 
							 DC.CD_NFCE,
							 DC.CD_DANFE,
							 C.CD_FILIAL,
							 C.CD_CX,
							 C.SERIE,
							 C.NR_NF,
							 TRY_CAST(CONVERT(VARCHAR(MAX),(DC.ARQUIVO_DANFE_CANCEL)) AS XML) AS XML_NOTA,
							 A.CD_VD
 
						 FROM 
							PDV_VD_NFCE_ARQ_DANFE_CANCEL (NOLOCK) DC
							LEFT JOIN PDV_VD_PDV_VD_NFCE (NOLOCK) L ON DC.CD_NFCE = L.CD_NFCE
							LEFT JOIN PDV_VD (NOLOCK) A ON L.CD_EMP = A.CD_EMP AND L.CD_FILIAL = A.CD_FILIAL AND L.CD_VD = A.CD_VD
							LEFT JOIN PDV_VD_NFCE (NOLOCK) C ON C.CD_NFCE = L.CD_NFCE AND C.CD_FILIAL = L.CD_FILIAL
						 WHERE 
							 A.CD_EMP = 1 
							 AND A.FLAG_NFCE = 1 
							 AND ((@CD_VD IS NULL AND DC.FLAG_TRIAGEM_NOTA = 0)
								OR (@CD_VD IS NOT NULL AND A.CD_VD = @CD_VD)
									)
						
					 )

				SELECT  
					CD_NFCE,
					CD_DANFE,
					CD_FILIAL,
					CD_CX,
					SERIE,
					NR_NF,
					COALESCE(
						XML_NOTA.value('(/nfe:procEventoNFe/nfe:retEvento/nfe:infEvento/nfe:cStat)[1]', 'int'), 
						XML_NOTA.value('(/nfe:retConsSitNFe/nfe:procEventoNFe/nfe:retEvento/nfe:infEvento/nfe:cStat)[1]', 'int'),
						XML_NOTA.value('(/nfe:ProcInutNFe/nfe:retInutNFe/nfe:infInut/nfe:cStat)[1]', 'int'),
						XML_NOTA.value('(/nfe:procInutNFe/nfe:retInutNFe/nfe:infInut/nfe:cStat)[1]', 'int'),
						XML_NOTA.value('(//nfe:retEnvEvento/nfe:retEvento/nfe:infEvento/nfe:cStat)[1]', 'int'),
						XML_NOTA.value('(/env:Envelope/env:Body/wsd:nfeResultMsg/nfe:retInutNFe/nfe:infInut/nfe:cStat)[1]', 'int'),
						XML_NOTA.value('(/*:infEvento/*:cStat)[1]', 'int'),
						XML_NOTA.value('(/nfe:nfeProc/nfe:protNFe/nfe:infProt/nfe:cStat)[1]', 'int'),
						0  
					) AS COD_STAT,
					CD_VD
				FROM 
					DANFE_XML;
				

    OPEN cursorNotas;


    FETCH NEXT FROM cursorNotas INTO @CD_NFCE, @CD_DANFE, @CD_FILIAL, @CD_CX, @SERIE, @NR_NF, @COD_STAT , @CD_VD_CURS

    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- Execução para atualizar o COD_STAT conforme xml
        UPDATE PDV_VD_NFCE   
			SET COD_STAT = CASE 
							 WHEN ISNUMERIC(COD_STAT) = 1 THEN
								CASE
									WHEN CAST(COD_STAT AS INT) < @COD_STAT THEN @COD_STAT 
									ELSE COD_STAT
								END
							 ELSE @COD_STAT
						   END
		WHERE CD_EMP = 1 AND CD_NFCE = @CD_NFCE AND CD_FILIAL = @CD_FILIAL AND CD_CX = @CD_CX AND SERIE = @SERIE AND NR_NF  = @NR_NF 

	    -- Altera FLAG_TRIAGEM_NOTA para processado
		UPDATE PDV_VD_NFCE_ARQ_DANFE SET FLAG_TRIAGEM_NOTA = 1 WHERE CD_NFCE = @CD_NFCE and CD_DANFE = @CD_DANFE;
		UPDATE PDV_VD_NFCE_ARQ_DANFE_CANCEL SET FLAG_TRIAGEM_NOTA = 1 WHERE CD_NFCE = @CD_NFCE and CD_DANFE = @CD_DANFE;

        -- Próximo registro
        FETCH NEXT FROM cursorNotas INTO  @CD_NFCE, @CD_DANFE, @CD_FILIAL, @CD_CX, @SERIE, @NR_NF, @COD_STAT , @CD_VD_CURS ;
    END


	-----------------------------------------------------------------------------------
    -- MERGE: Executa com base no CD_VD 
    -----------------------------------------------------------------------------------
	--PRINT '@CD_VD_CURS = ' + CAST( @CD_VD_CURS AS VARCHAR);

        MERGE dbo.TBL_PAINEL_NFCE_CACHE AS TARGET
        USING (
            SELECT *
            FROM dbo.V_PAINEL_NFCE_PDV_VENDAS
            WHERE CD_VD = @CD_VD_CURS
        ) AS SOURCE
        ON TARGET.CD_FILIAL = SOURCE.CD_FILIAL 
           AND TARGET.CD_VD = SOURCE.CD_VD

        WHEN MATCHED AND (
            TARGET.STATUS_XML         <> SOURCE.STATUS_XML OR
            TARGET.STS_VALID          <> SOURCE.STS_VALID OR
            TARGET.NR_PROT            <> SOURCE.NR_PROT OR
            TARGET.DS_MOTIVO          <> SOURCE.DS_MOTIVO OR
            TARGET.CD_NFCE            <> SOURCE.CD_NFCE OR
            TARGET.CHV_NFCE           <> SOURCE.CHV_NFCE OR
            TARGET.QTD_ENVIO          <> SOURCE.QTD_ENVIO OR
            TARGET.DT_ENVIO           <> SOURCE.DT_ENVIO OR
            TARGET.COD_STAT           <> SOURCE.COD_STAT OR
            TARGET.SERIE              <> SOURCE.SERIE OR
            TARGET.XML_DANFE          <> SOURCE.XML_DANFE OR
            TARGET.NR_PROT_CANCEL     <> SOURCE.NR_PROT_CANCEL OR
            TARGET.X_MOTIVO           <> SOURCE.X_MOTIVO OR
            TARGET.TIPO_NF            <> SOURCE.TIPO_NF OR
            TARGET.ST_VD              <> SOURCE.ST_VD OR
            TARGET.VLR_LIQ_VD         <> SOURCE.VLR_LIQ_VD
        )
        THEN UPDATE SET 
            STATUS_XML         = SOURCE.STATUS_XML,
            STS_VALID          = SOURCE.STS_VALID,
            NR_PROT            = SOURCE.NR_PROT,
            DS_MOTIVO          = SOURCE.DS_MOTIVO,
            CD_NFCE            = SOURCE.CD_NFCE,
            CHV_NFCE           = SOURCE.CHV_NFCE,
            QTD_ENVIO          = SOURCE.QTD_ENVIO,
            DT_ENVIO           = SOURCE.DT_ENVIO,
            COD_STAT           = SOURCE.COD_STAT,
            SERIE              = SOURCE.SERIE,
            XML_DANFE          = SOURCE.XML_DANFE,
            NR_PROT_CANCEL     = SOURCE.NR_PROT_CANCEL,
            X_MOTIVO           = SOURCE.X_MOTIVO,
            TIPO_NF            = SOURCE.TIPO_NF,
            ST_VD              = SOURCE.ST_VD,
            VLR_LIQ_VD         = SOURCE.VLR_LIQ_VD

        WHEN NOT MATCHED BY TARGET THEN
            INSERT (
                TP_EMIS, STATUS_XML, CD_EMP, CD_FILIAL, CD_VD, DT_VD, CD_NF, ST_VD, CD_VD_GESTAO,
                NR_ECF, HR_VD, CD_CX, VLR_VD, VLR_DINH, VLR_CARTAO, VLR_CHQS, VLR_DESC, VLR_LIQ_VD,
                HORALIMITE, DT_CAD, UF, AUT_NFCE, STS_VALID, NR_PROT, DS_MOTIVO, CD_NFCE, CHV_NFCE,
                QTD_ENVIO, DT_ENVIO, COD_STAT, SERIE, XML_DANFE, STS_PEND, url_danfe_nfce, CGC,
                NR_PROT_CANCEL, CHAVE_NFCE, X_MOTIVO, CD_NF_SERIE, TIPO_NF
            )
            VALUES (
                SOURCE.TP_EMIS, SOURCE.STATUS_XML, SOURCE.CD_EMP, SOURCE.CD_FILIAL, SOURCE.CD_VD, SOURCE.DT_VD, SOURCE.CD_NF, SOURCE.ST_VD, SOURCE.CD_VD_GESTAO,
                SOURCE.NR_ECF, SOURCE.HR_VD, SOURCE.CD_CX, SOURCE.VLR_VD, SOURCE.VLR_DINH, SOURCE.VLR_CARTAO, SOURCE.VLR_CHQS, SOURCE.VLR_DESC, SOURCE.VLR_LIQ_VD,
                SOURCE.HORALIMITE, SOURCE.DT_CAD, SOURCE.UF, SOURCE.AUT_NFCE, SOURCE.STS_VALID, SOURCE.NR_PROT, SOURCE.DS_MOTIVO, SOURCE.CD_NFCE, SOURCE.CHV_NFCE,
                SOURCE.QTD_ENVIO, SOURCE.DT_ENVIO, SOURCE.COD_STAT, SOURCE.SERIE, SOURCE.XML_DANFE, SOURCE.STS_PEND, SOURCE.url_danfe_nfce, SOURCE.CGC,
                SOURCE.NR_PROT_CANCEL, SOURCE.CHAVE_NFCE, SOURCE.X_MOTIVO, SOURCE.CD_NF_SERIE, SOURCE.TIPO_NF
            );



	
    -- Fechando e desalocando o cursor
    CLOSE cursorNotas;
    DEALLOCATE cursorNotas;



END;

