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

/*
=======================================================================================
'Job.................: Linx - Pendencias de entrada e saida
'Step................: 
'Função..............: 
'Objetivo............:  
'Tabelas.............: 
'Modificado..........: Jerfeson Barbosa
'Dt Modificação......: 17/11/2023
'Modificação.........: Tag #A001 Validar restringir o nao insert de cd_nf duplicado
'=======================================================================================
Tag #A001 - Alteração do codigo  
*/


--EXEC P_EST_PED_CP_ATUALIZA_STS_PED 1,34,null,null,0
CREATE PROCEDURE [dbo].[P_EST_PED_CP_ATUALIZA_STS_PED]
(@CD_EMP           INT, 
 @CD_FILIAL        INT, 
 @DT_INI           DATETIME = NULL, 
 @DT_FIM           DATETIME = NULL, 
 @ANALISAR_PERIODO INT      = 0
)
AS
    BEGIN
        SET NOCOUNT ON;
        IF @DT_INI IS NULL
            BEGIN
                SET @DT_INI = GETDATE() - 90;
            END;
        IF @DT_FIM IS NULL
            BEGIN
                SET @DT_FIM = GETDATE();
            END;
        /*******************************************************************************************************************/
        CREATE TABLE #RsPedidoAnalise
        (CD_EMP            INT, 
         CD_FILIAL         INT, 
         CD_PED            INT, 
         QT_PED_SLD_ATUAL  MONEY, 
         QT_PED            MONEY, 
         QT_NF_ENTRADA     MONEY, 
         QT_PED_SLD_CANCEL MONEY, 
         QT_PED_SLD        MONEY
        );
        /*******************************************************************************************************************/
        --Marcando danfe provisorio que possui vinculo com pre-entrada
        UPDATE EST_NF_ENT_DANFE_PROVISORIO_PENDENTE
          SET 
              PRE_ENTRADA = 1
        FROM EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT_PRE_ENTRADA
             INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_PENDENTE ON EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT_PRE_ENTRADA.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO_PENDENTE.CD_CTR
        WHERE PRE_ENTRADA = 0;
        -------------------------------------------------------------------------------------------------------------------
        INSERT INTO EST_PED_CP_PENDENCIA
               SELECT DISTINCT 
                      EST_PED_CP.CD_EMP, 
                      EST_PED_CP.CD_FILIAL, 
                      EST_PED_CP.CD_PED, 
                      @DT_INI, 
                      1
               FROM EST_PED_CP
                    LEFT OUTER JOIN EST_PED_CP_PENDENCIA ON EST_PED_CP.CD_EMP = EST_PED_CP_PENDENCIA.CD_EMP
                                                            AND EST_PED_CP.CD_FILIAL = EST_PED_CP_PENDENCIA.CD_FILIAL
                                                            AND EST_PED_CP.CD_PED = EST_PED_CP_PENDENCIA.CD_PED
               WHERE EST_PED_CP.STS_PED IN(0, 1) -- aberto e entrega parcial
                    AND EST_PED_CP_PENDENCIA.CD_PED IS NULL
                    AND EST_PED_CP.DT_EMI >= @DT_INI
                    AND EST_PED_CP.DT_EMI <= @DT_FIM
                    AND EST_PED_CP.CD_EMP = @CD_EMP
                    AND EST_PED_CP.CD_FILIAL = @CD_FILIAL;
        -------------------------------------------------------------------------------------------------------------------
        ----Print 'Analisando pedidos de compras pendentes que possui vinculo com danfe com entrada efetuada atraves de pre-entrada ' + convert(varchar,getdate(),108);	  
		DELETE B
        FROM EST_NF_ENT_PRE_ENTRADA A
            INNER JOIN EST_PED_CP_PENDENCIA B ON A.CD_EMP = B.CD_EMP
                                                    AND A.CD_FILIAL = B.CD_FILIAL
                                                    AND A.CD_PED = B.CD_PED
            INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT_PRE_ENTRADA C ON A.CD_EMP = C.CD_EMP
                                                                                AND A.CD_CTR_PRE_ENTRADA = C.CD_CTR_PRE_ENTRADA
            INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT D ON C.CD_EMP = D.CD_EMP
                                                                    AND C.CD_CTR = D.CD_CTR
            LEFT JOIN EST_NF_ENT_PED_CP E ON D.CD_EMP = E.CD_EMP
                                                AND D.CD_FILIAL = E.CD_FILIAL
                                                AND D.CD_NF = E.CD_NF
                                                AND e.CD_EMP = @CD_EMP
                                                AND e.CD_FILIAL = @CD_FILIAL
        WHERE E.CD_EMP IS NULL
                AND b.CD_EMP = @CD_EMP
                AND b.CD_FILIAL = @CD_FILIAL
		AND NOT EXISTS (SELECT 1 FROM EST_NF_ENT E
						WHERE E.CD_NF = D.CD_NF
						AND E.CD_FILIAL = B.CD_FILIAL)
        INSERT INTO EST_NF_ENT_PED_CP(CD_EMP, CD_FILIAL, CD_PED, CD_NF, DT_CAD)
        SELECT D.CD_EMP, 
                D.CD_FILIAL, 
                B.CD_PED, 
                D.CD_NF, 
                GETDATE() DT_CAD
        FROM EST_NF_ENT_PRE_ENTRADA A
            INNER JOIN EST_PED_CP_PENDENCIA B ON A.CD_EMP = B.CD_EMP
                                                    AND A.CD_FILIAL = B.CD_FILIAL
                                                    AND A.CD_PED = B.CD_PED
            INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT_PRE_ENTRADA C ON A.CD_EMP = C.CD_EMP
                                                                                AND A.CD_CTR_PRE_ENTRADA = C.CD_CTR_PRE_ENTRADA
            INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT D ON C.CD_EMP = D.CD_EMP
                                                                    AND C.CD_CTR = D.CD_CTR
            LEFT JOIN EST_NF_ENT_PED_CP E ON D.CD_EMP = E.CD_EMP
                                                AND D.CD_FILIAL = E.CD_FILIAL
                                                AND D.CD_NF = E.CD_NF
                                                AND e.CD_EMP = @CD_EMP
                                                AND e.CD_FILIAL = @CD_FILIAL
        WHERE E.CD_EMP IS NULL
                AND b.CD_EMP = @CD_EMP
                AND b.CD_FILIAL = @CD_FILIAL
				AND EXISTS (select 1 from est_ped_cp f where f.cd_ped = b.cd_ped 
							and f.cd_filial = d.cd_filial and f.cd_emp = d.cd_emp);
        -------------------------------------------------------------------------------------------------------------------
        IF NOT EXISTS( SELECT * FROM EST_PED_CP_PENDENCIA 
						WHERE 
								CD_EMP = @CD_EMP
							AND CD_FILIAL = @CD_FILIAL
							AND FLAG_ANALISAR_STS = 1)
            BEGIN
                RETURN;
            END
        ------------------------------------------------------------------------------------------------------------------- 
        ;WITH PedCp
             AS (SELECT --Buscando dados do pedido de compras
                 EST_PED_CP_IT.CD_EMP, 
                 EST_PED_CP_IT.CD_FILIAL, 
                 EST_PED_CP_IT.CD_PED, 
                 SUM(QT_IT_SLD) AS QT_PED_SLD_ATUAL
                 , --Ja esta convertido a embalagem 
                 SUM(EST_PED_CP_IT.QT_IT * CASE
                                               WHEN EST_PED_CP_IT.QT_EMB = 0
                                               THEN 1
                                               ELSE EST_PED_CP_IT.QT_EMB
                                           END) AS QT_PED
                 FROM EST_PED_CP_IT
                      INNER JOIN EST_PED_CP_PENDENCIA p ON EST_PED_CP_IT.CD_EMP = p.CD_EMP
                                                           AND EST_PED_CP_IT.CD_FILIAL = p.CD_FILIAL
                                                           AND EST_PED_CP_IT.CD_PED = p.CD_PED
                 WHERE EST_PED_CP_IT.CD_EMP = @CD_EMP
                       AND EST_PED_CP_IT.CD_FILIAL = @CD_FILIAL
                       AND p.FLAG_ANALISAR_STS = 1
                 GROUP BY EST_PED_CP_IT.CD_EMP, 
                          EST_PED_CP_IT.CD_FILIAL, 
                          EST_PED_CP_IT.CD_PED),
             NFEnt
             AS (SELECT     --Notas fiscais do pedido ja efetuadas
                 C.CD_EMP, 
                 C.CD_FILIAL, 
                 C.CD_PED, 
                 SUM(e.QT_IT) AS QT_NF_ENTRADA
                 FROM EST_NF_ENT_IT e
                      INNER JOIN EST_NF_ENT_PED_CP C ON e.CD_EMP = C.CD_EMP
                                                        AND e.CD_FILIAL = C.CD_FILIAL
                                                        AND e.CD_NF = C.CD_NF
                      INNER JOIN EST_PED_CP_PENDENCIA p ON C.CD_EMP = p.CD_EMP
                                                           AND C.CD_FILIAL = p.CD_FILIAL
                                                           AND C.CD_PED = p.CD_PED
                 WHERE e.CD_EMP = @CD_EMP
                       AND e.CD_FILIAL = @CD_FILIAL
                       AND p.FLAG_ANALISAR_STS = 1
                 GROUP BY C.CD_EMP, 
                          C.CD_FILIAL, 
                          C.CD_PED),
             CancelPed
             AS (SELECT  --Pedido saldo cancelamento
                 c.CD_EMP, 
                 c.CD_FILIAL, 
                 c.CD_PED, 
                 SUM(C.QT_PROD * i.QT_EMB) AS QT_PED_SLD_CANCEL
                 FROM EST_PED_CP_IT_CANCEL_SLD c
                      INNER JOIN EST_PED_CP_IT i ON c.CD_EMP = i.CD_EMP
                                                    AND c.CD_FILIAL = i.CD_FILIAL
                                                    AND c.CD_PED = i.CD_PED
                                                    AND c.CD_PROD = i.CD_PROD
                      INNER JOIN EST_PED_CP_PENDENCIA p ON C.CD_EMP = p.CD_EMP
                                                           AND C.CD_FILIAL = p.CD_FILIAL
                                                           AND C.CD_PED = p.CD_PED
                 WHERE c.CD_EMP = @CD_EMP
                       AND c.CD_FILIAL = @CD_FILIAL
                       AND p.FLAG_ANALISAR_STS = 1
                 GROUP BY c.CD_EMP, 
                          c.CD_FILIAL, 
                          c.CD_PED)
             INSERT INTO #RsPedidoAnalise
                    SELECT a.CD_EMP, 
                           a.CD_FILIAL, 
                           a.CD_PED, 
                           ISNULL(QT_PED_SLD_ATUAL,0) AS QT_PED_SLD_ATUAL, 
                           ISNULL(QT_PED,0) AS QT_PED, 
                           ISNULL(QT_NF_ENTRADA,0) AS QT_NF_ENTRADA, 
                           ISNULL(QT_PED_SLD_CANCEL,0) AS QT_PED_SLD_CANCEL,
                           CASE WHEN ISNULL(QT_PED,0) - ( ISNULL(QT_NF_ENTRADA,0) + ISNULL(QT_PED_SLD_CANCEL,0)) < 0
                               THEN 0
                               ELSE ISNULL(QT_PED,0) - (ISNULL(QT_NF_ENTRADA,0) + ISNULL(QT_PED_SLD_CANCEL,0))
                           END AS QT_PED_SLD
                    FROM PedCp a
                         LEFT JOIN NFEnt b ON A.CD_EMP = B.CD_EMP
                                              AND A.CD_FILIAL = B.CD_FILIAL
                                              AND A.CD_PED = B.CD_PED
                         LEFT JOIN CancelPed c ON A.CD_EMP = C.CD_EMP
                                                  AND A.CD_FILIAL = c.CD_FILIAL
                                                  AND A.CD_PED = c.CD_PED;
        -------------------------------------------------------------------------------------------------------------------
        --INSERT INTO #RsPedidoAnalise
        --SELECT
        --	CD_EMP
        --  , CD_FILIAL
        --  , CD_PED
        --  , SUM(QT_PED_SLD_ATUAL) AS QT_PED_SLD_ATUAL
        --  , SUM(QT_PED) AS QT_PED
        --  , SUM(QT_NF_ENTRADA) AS QT_NF_ENTRADA
        --  , SUM(QT_PED_SLD_CANCEL) AS QT_PED_SLD_CANCEL
        --  , CASE
        --		WHEN SUM(QT_PED) - (SUM(QT_NF_ENTRADA) + SUM(QT_PED_SLD_CANCEL)) < 0 THEN 0
        --		ELSE SUM(QT_PED) - (SUM(QT_NF_ENTRADA) + SUM(QT_PED_SLD_CANCEL))
        --	END AS QT_PED_SLD
        --FROM
        --(
        --	SELECT --Buscando dados do pedido de compras
        --		   EST_PED_CP_IT.CD_EMP
        --		 , EST_PED_CP_IT.CD_FILIAL
        --		 , EST_PED_CP_IT.CD_PED
        --		 , QT_IT_SLD AS QT_PED_SLD_ATUAL --Ja esta convertido a embalagem
        --		 , (EST_PED_CP_IT.QT_IT * CASE
        --									  WHEN EST_PED_CP_IT.QT_EMB = 0 THEN 1
        --									  ELSE EST_PED_CP_IT.QT_EMB
        --								  END) AS QT_PED
        --		 , 0 AS QT_NF_ENTRADA
        --		 , 0 AS QT_PED_SLD_CANCEL
        --	FROM
        --		EST_PED_CP_IT
        --		INNER JOIN EST_PED_CP_PENDENCIA p ON EST_PED_CP_IT.CD_EMP = p.CD_EMP
        --											 AND EST_PED_CP_IT.CD_FILIAL = p.CD_FILIAL
        --											 AND EST_PED_CP_IT.CD_PED = p.CD_PED
        --	WHERE   EST_PED_CP_IT.CD_EMP = @CD_EMP
        --			AND EST_PED_CP_IT.CD_FILIAL = @CD_FILIAL
        --			AND p.FLAG_ANALISAR_STS = 1
        --	UNION ALL
        --	SELECT     --Notas fiscais do pedido ja efetuadas
        --		   EST_NF_ENT_PED_CP.CD_EMP
        --		 , EST_NF_ENT_PED_CP.CD_FILIAL
        --		 , EST_NF_ENT_PED_CP.CD_PED
        --		 , 0 AS QT_PED_SLD_ATUAL
        --		 , 0 AS QT_PED
        --		 , EST_NF_ENT_IT.QT_IT AS QT_NF_ENTRADA
        --		 , 0 AS QT_PED_SLD_CANCEL
        --	FROM
        --		EST_NF_ENT_IT
        --		INNER JOIN EST_NF_ENT_PED_CP ON EST_NF_ENT_IT.CD_EMP = EST_NF_ENT_PED_CP.CD_EMP
        --										AND EST_NF_ENT_IT.CD_FILIAL = EST_NF_ENT_PED_CP.CD_FILIAL
        --										AND EST_NF_ENT_IT.CD_NF = EST_NF_ENT_PED_CP.CD_NF
        --		INNER JOIN EST_PED_CP_PENDENCIA p ON EST_NF_ENT_PED_CP.CD_EMP = p.CD_EMP
        --											 AND EST_NF_ENT_PED_CP.CD_FILIAL = p.CD_FILIAL
        --											 AND EST_NF_ENT_PED_CP.CD_PED = p.CD_PED
        --	WHERE   EST_NF_ENT_IT.CD_EMP = @CD_EMP
        --			AND EST_NF_ENT_IT.CD_FILIAL = @CD_FILIAL
        --			AND p.FLAG_ANALISAR_STS = 1
        --	UNION ALL
        --	SELECT  --Pedido saldo cancelamento
        --		   c.CD_EMP
        --		 , c.CD_FILIAL
        --		 , c.CD_PED
        --		 , 0 AS QT_PED_SLD_ATUAL
        --		 , 0 AS QT_PED
        --		 , 0 AS QT_NF_ENTRADA
        --		 , C.QT_PROD * i.QT_EMB AS QT_PED_SLD_CANCEL
        --	FROM
        --		EST_PED_CP_IT_CANCEL_SLD c
        --		INNER JOIN EST_PED_CP_IT i ON c.CD_EMP = i.CD_EMP
        --									  AND c.CD_FILIAL = i.CD_FILIAL
        --									  AND c.CD_PED = i.CD_PED
        --									  AND c.CD_PROD = i.CD_PROD
        --		INNER JOIN EST_PED_CP_PENDENCIA p ON C.CD_EMP = p.CD_EMP
        --											 AND C.CD_FILIAL = p.CD_FILIAL
        --											 AND C.CD_PED = p.CD_PED
        --	WHERE  c.CD_EMP = @CD_EMP
        --		   AND c.CD_FILIAL = @CD_FILIAL
        --		   AND p.FLAG_ANALISAR_STS = 1
        --) Rs
        --GROUP BY
        --	  CD_EMP
        --	, CD_FILIAL
        --	, CD_PED;
        /********************************************************************************************************************/
        IF @ANALISAR_PERIODO = 0
            BEGIN
                --Print 'Analisando pendencia de entada de pedidos de compras ' + convert(varchar,getdate(),108);	  
                UPDATE est_ped_cp
                  SET 
                      STS_PED = CASE WHEN QT_PED = QT_PED_SLD THEN 0
									 WHEN (QT_PED > QT_PED_SLD) AND QT_PED_SLD > 0 THEN 1
                                     ELSE 3
                                END
                FROM #RsPedidoAnalise A
                     INNER JOIN est_ped_cp ON A.cd_emp = est_ped_cp.cd_emp
                                              AND A.cd_filial = est_ped_cp.cd_filial
                                              AND A.cd_ped = est_ped_cp.cd_ped
                WHERE est_ped_cp.CD_EMP = @CD_EMP
                      AND est_ped_cp.CD_FILIAL = @CD_FILIAL;
                ----------------------------------------------------------------------------------------------------------------
                --Print 'Cancelando pedidos sem item ' + convert(varchar,getdate(),108);
                DELETE FROM a
                FROM EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP a
                     LEFT JOIN EST_PED_CP_IT I ON A.CD_EMP = I.CD_EMP
                                                  AND A.CD_PED = I.CD_PED
                                                  AND A.CD_FILIAL = I.CD_FILIAL
                WHERE A.CD_FILIAL = @CD_FILIAL
                      AND I.CD_EMP IS NULL
                      AND A.CD_EMP = @CD_EMP;
                UPDATE A
                  SET 
                      STS_PED = 2
                FROM EST_PED_CP A
                     LEFT JOIN EST_PED_CP_IT I ON A.CD_EMP = I.CD_EMP
                                                  AND A.CD_PED = I.CD_PED
                                                  AND A.CD_FILIAL = I.CD_FILIAL
                WHERE A.CD_FILIAL = @CD_FILIAL
                      AND I.CD_EMP IS NULL
                      AND A.CD_EMP = @CD_EMP
                      AND NOT A.STS_PED = 2
                      AND NOT dt_emi = CONVERT(DATE, GETDATE());
                ----------------------------------------------------------------------------------------------------------------
                --Print 'Deletando pendencia de entada de pedidos de compras cancelado/entrada total ' + convert(varchar,getdate(),108);	  
                DELETE FROM A
                FROM EST_PED_CP_PENDENCIA A
                     INNER JOIN EST_PED_CP C ON A.CD_EMP = C.CD_EMP
                                                AND A.CD_PED = C.CD_PED
                                                AND A.CD_FILIAL = C.CD_FILIAL
                WHERE C.CD_FILIAL = @CD_FILIAL
                      AND C.STS_PED IN(3, 2) -- 2 - CANCELADO  3- ENTREGA TOTAL
                AND C.CD_EMP = @CD_EMP;
                ----------------------------------------------------------------------------------------------------------------
                --Print 'Deletando pendencia de entada de Danfe com vinculo com nota fiscal de entrada ' + convert(varchar,getdate(),108);	  
                DELETE FROM D
                FROM EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT P
                     INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_PENDENTE D ON P.CD_CTR = D.CD_CTR
                WHERE p.CD_EMP = @CD_EMP
                      AND p.CD_FILIAL = @CD_FILIAL;
                ----------------------------------------------------------------------------------------------------------------
                --Print 'Retirando pendencia de analise de status de pedidos de compras ' + convert(varchar,getdate(),108);	  
                UPDATE EST_PED_CP_PENDENCIA
                  SET 
                      FLAG_ANALISAR_STS = 0
                FROM #RsPedidoAnalise A
                     INNER JOIN EST_PED_CP_PENDENCIA ON A.cd_emp = EST_PED_CP_PENDENCIA.cd_emp
                                                        AND A.cd_filial = EST_PED_CP_PENDENCIA.cd_filial
                                                        AND A.cd_ped = EST_PED_CP_PENDENCIA.cd_ped
                WHERE EST_PED_CP_PENDENCIA.CD_EMP = @CD_EMP
                      AND EST_PED_CP_PENDENCIA.CD_FILIAL = @CD_FILIAL;
                ----------------------------------------------------------------------------------------------------------------
                --Print 'Deletar pedido de compras que possui entrada total em pendencia ' + convert(varchar,getdate(),108);	  
                DELETE FROM EST_PED_CP_PENDENCIA
                FROM EST_PED_CP A
                     INNER JOIN EST_PED_CP_PENDENCIA ON A.CD_EMP = EST_PED_CP_PENDENCIA.CD_EMP
                                                        AND A.CD_FILIAL = EST_PED_CP_PENDENCIA.CD_FILIAL
                                                        AND A.CD_PED = EST_PED_CP_PENDENCIA.CD_PED
                WHERE A.STS_PED IN(3, 2)
                AND a.CD_EMP = @CD_EMP
                AND a.CD_FILIAL = @CD_FILIAL;
            END;
            ELSE
            BEGIN
                SELECT *
                FROM #RsPedidoAnalise
                ORDER BY cd_filial, 
                         cd_ped;
            END;
    END;

GO


