IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[P_GERAR_CONC_FINAN_ECONF_AUTOMATICO]') AND TYPE IN (N'P', N'PC'))
	DROP PROCEDURE [DBO].[P_GERAR_CONC_FINAN_ECONF_AUTOMATICO]
GO

CREATE PROCEDURE P_GERAR_CONC_FINAN_ECONF_AUTOMATICO
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE 
        @DataInicioDia DATETIME,
        @NextId INT;

    -- Data atual ās 00:00:00
    SET @DataInicioDia = CAST(GETDATE() AS DATE);

    BEGIN TRY
        BEGIN TRAN;

        /* =========================================================
           PARTE 1 - PDV_VD / NFC-e
        ========================================================= */

        CREATE TABLE #TMP_PDV_VD (
            CD_EMP INT,
            CD_FILIAL INT,
            CD_VD INT
        );

        INSERT INTO #TMP_PDV_VD (CD_EMP, CD_FILIAL, CD_VD)
        SELECT P.CD_EMP, P.CD_FILIAL, P.CD_VD
        FROM PDV_VD P
        INNER JOIN PDV_VD_PDV_VD_NFCE NFCE_VINC
            ON P.CD_EMP = NFCE_VINC.CD_EMP
           AND P.CD_FILIAL = NFCE_VINC.CD_FILIAL
           AND P.CD_VD = NFCE_VINC.CD_VD
        INNER JOIN PDV_VD_NFCE NFCE
            ON NFCE.CD_EMP = NFCE_VINC.CD_EMP
           AND NFCE.CD_FILIAL = NFCE_VINC.CD_FILIAL
           AND NFCE.CD_NFCE = NFCE_VINC.CD_NFCE
        INNER JOIN PRC_FILIAL_CONFIG
            ON PRC_FILIAL_CONFIG.CD_CHAVE = 'HABILITAR_ENVIO_ECONF_AUTOMATICO'
           AND PRC_FILIAL_CONFIG.VALOR = '1'
           AND PRC_FILIAL_CONFIG.CD_EMP = P.CD_EMP
           AND PRC_FILIAL_CONFIG.CD_FILIAL = P.CD_FILIAL
        WHERE P.ST_VD = 0
          AND NFCE.COD_STAT = 100
          AND NFCE.NR_PROT <> ''
          AND P.DT_VD >= @DataInicioDia
          AND NOT EXISTS (
                SELECT 1
                FROM EST_NFE_CONC_FINAN_ECONF_PDV_VD EV
                WHERE EV.CD_EMP    = P.CD_EMP
                  AND EV.CD_FILIAL = P.CD_FILIAL
                  AND EV.CD_VD     = P.CD_VD
          );

        DECLARE 
            @CD_EMP INT,
            @CD_FILIAL INT,
            @CD_VD INT;

        DECLARE CUR_PDV CURSOR FOR
            SELECT CD_EMP, CD_FILIAL, CD_VD
            FROM #TMP_PDV_VD;

        OPEN CUR_PDV;
        FETCH NEXT FROM CUR_PDV INTO @CD_EMP, @CD_FILIAL, @CD_VD;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @NextId = ISNULL(MAX(CD_CTR_CONC_FINAN_ECONF), 0) + 1
            FROM EST_NFE_CONC_FINAN_ECONF;

            INSERT INTO EST_NFE_CONC_FINAN_ECONF
            (
                CD_CTR_CONC_FINAN_ECONF,
                CD_USU,
                DT_HR_EVENTO
            )
            VALUES
            (
                @NextId,
                1,
                GETDATE()
            );

            INSERT INTO EST_NFE_CONC_FINAN_ECONF_PDV_VD
            (
                CD_CTR_CONC_FINAN_ECONF,
                CD_EMP,
                CD_FILIAL,
                CD_VD,
                NR_EVENTO
            )
            VALUES
            (
                @NextId,
                @CD_EMP,
                @CD_FILIAL,
                @CD_VD,
                1
            );

            FETCH NEXT FROM CUR_PDV INTO @CD_EMP, @CD_FILIAL, @CD_VD;
        END;

        CLOSE CUR_PDV;
        DEALLOCATE CUR_PDV;
        DROP TABLE #TMP_PDV_VD;

        /* =========================================================
           PARTE 2 - EST_NF_SAI / NFe
        ========================================================= */

        CREATE TABLE #TMP_NF_SAI (
            CD_EMP INT,
            CD_FILIAL INT,
            CD_NF INT
        );

        INSERT INTO #TMP_NF_SAI (CD_EMP, CD_FILIAL, CD_NF)
        SELECT P.CD_EMP, P.CD_FILIAL, P.CD_NF
        FROM EST_NF_SAI P
        INNER JOIN EST_NF_SAI_NFE NFE
            ON P.CD_EMP = NFE.CD_EMP
           AND P.CD_FILIAL = NFE.CD_FILIAL
           AND P.CD_NF = NFE.CD_NF
        INNER JOIN PRC_FILIAL_CONFIG
            ON PRC_FILIAL_CONFIG.CD_CHAVE = 'HABILITAR_ENVIO_ECONF_AUTOMATICO'
           AND PRC_FILIAL_CONFIG.VALOR = '1'
           AND PRC_FILIAL_CONFIG.CD_EMP = P.CD_EMP
           AND PRC_FILIAL_CONFIG.CD_FILIAL = P.CD_FILIAL
        WHERE P.STS_NF = 0
          AND NFE.C_STAT_NFE = 100
          AND NFE.NR_PROTOCOLO <> ''
          AND P.DT_EMI_NF >= @DataInicioDia
          AND NOT EXISTS (
                SELECT 1
                FROM EST_NFE_CONC_FINAN_ECONF_EST_NF_SAI EV
                WHERE EV.CD_EMP    = P.CD_EMP
                  AND EV.CD_FILIAL = P.CD_FILIAL
                  AND EV.CD_NF     = P.CD_NF
          );

        DECLARE 
            @CD_NF INT;

        DECLARE CUR_NF CURSOR FOR
            SELECT CD_EMP, CD_FILIAL, CD_NF
            FROM #TMP_NF_SAI;

        OPEN CUR_NF;
        FETCH NEXT FROM CUR_NF INTO @CD_EMP, @CD_FILIAL, @CD_NF;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @NextId = ISNULL(MAX(CD_CTR_CONC_FINAN_ECONF), 0) + 1
            FROM EST_NFE_CONC_FINAN_ECONF;

            INSERT INTO EST_NFE_CONC_FINAN_ECONF
            (
                CD_CTR_CONC_FINAN_ECONF,
                CD_USU,
                DT_HR_EVENTO
            )
            VALUES
            (
                @NextId,
                1,
                GETDATE()
            );

            INSERT INTO EST_NFE_CONC_FINAN_ECONF_EST_NF_SAI
            (
                CD_CTR_CONC_FINAN_ECONF,
                CD_EMP,
                CD_FILIAL,
                CD_NF,
                NR_EVENTO
            )
            VALUES
            (
                @NextId,
                @CD_EMP,
                @CD_FILIAL,
                @CD_NF,
                1
            );

            FETCH NEXT FROM CUR_NF INTO @CD_EMP, @CD_FILIAL, @CD_NF;
        END;

        CLOSE CUR_NF;
        DEALLOCATE CUR_NF;
        DROP TABLE #TMP_NF_SAI;

        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN;
        THROW;
    END CATCH
END;