IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_GRAVA_LOTE_TRANSFERENCIA_RESSUPRIMENTO]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[P_GRAVA_LOTE_TRANSFERENCIA_RESSUPRIMENTO]
GO

CREATE PROCEDURE [dbo].[P_GRAVA_LOTE_TRANSFERENCIA_RESSUPRIMENTO]
AS
BEGIN

	SELECT DISTINCT T.CD_CTR, IT.CD_FILIAL, IT.CD_PROD, IT.QT_SOL
	INTO #ProcessamentoLotes --DROP TABLE #ProcessamentoLotes
	FROM EST_PROD_TRANSF T
	INNER JOIN EST_PROD_TRANSF_IT IT ON IT.CD_CTR = T.CD_CTR
	INNER JOIN EST_PROD P ON P.CD_PROD = IT.CD_PROD
	INNER JOIN PRC_FILIAL FIL ON FIL.CD_FILIAL = T.CD_FILIAL
	LEFT JOIN GLB_CID CID ON CID.CD_CID = FIL.CD_CID
	INNER JOIN INFORMA_LOTE_NFE ILN ON ILN.CD_FILIAL = T.CD_FILIAL
										AND ILN.NR_NCM = P.NR_NCM
										AND ILN.UF = CID.UF
										AND ILN.CD_PROD = IT.CD_PROD
	WHERE CONVERT(DATE,T.DT_CAD) = CONVERT(DATE,GETDATE())--'2024-04-16'
	AND IT.QT_SOL > 0
	AND NOT EXISTS (SELECT 1 FROM EST_PROD_TRANSF_IT_LOTE AS IT_LOTE
					WHERE IT_LOTE.CD_PROD = IT.CD_PROD
						  AND IT_LOTE.CD_CTR = T.CD_CTR
						  AND IT_LOTE.CD_FILIAL = IT.CD_FILIAL) /*Já não exista lote gravado para este item*/
	AND EXISTS (SELECT 1
				FROM PRC_FILIAL_CONFIG CFG
				WHERE CFG.CD_CHAVE = 'EXIGIR_CONTROLE_NFE_LOTE_NT_2021_004'
				AND CFG.CD_FILIAL = IT.CD_FILIAL
				AND CFG.VALOR = 1)

	-- Declare a variável que será usada para armazenar os resultados da consulta
	DECLARE @CD_CTR INT
	DECLARE @CD_FILIAL INT
	DECLARE @CD_PROD INT
	DECLARE @QT_SOL money

	-- Declare o cursor
	DECLARE cursor_processamento CURSOR FOR
											SELECT CD_CTR, CD_FILIAL, CD_PROD, QT_SOL
											FROM #ProcessamentoLotes

	-- Abra o cursor
	OPEN cursor_processamento

	-- Inicie o loop
	FETCH NEXT FROM cursor_processamento INTO @CD_CTR, @CD_FILIAL, @CD_PROD, @QT_SOL
	WHILE @@FETCH_STATUS = 0
	BEGIN

		DECLARE @Lotes AS TABLE (NR_LOTE nvarchar(100),
								 DT_VALID datetime,
								 DT_FABR smalldatetime,
								 QTDE_PROD money,
								 Ordem INT)

    
		INSERT INTO @Lotes
		SELECT LOTE.NR_LOTE, LOTE.DT_VALID, LOTE.DT_FABR, (LOTE.QTDE_PROD - ISNULL(LOTE_RESERVADO.QTDE_PROD,0)), ROW_NUMBER() OVER(ORDER BY LOTE.DT_VALID ASC, LOTE.QTDE_PROD ASC) AS Ordem
		FROM EST_PROD_CPL_LOTE AS LOTE
		OUTER APPLY (SELECT SUM(QTDE_PROD) AS QTDE_PROD
					 FROM EST_PROD_TRANSF_IT_LOTE IT_LOTE
					 INNER JOIN EST_PROD_TRANSF T ON T.CD_CTR = IT_LOTE.CD_CTR
													AND T.CD_FILIAL = IT_LOTE.CD_FILIAL
					 WHERE IT_LOTE.CD_EMP = LOTE.CD_EMP
					 AND IT_LOTE.CD_FILIAL = LOTE.CD_FILIAL
					 AND IT_LOTE.CD_PROD = LOTE.CD_PROD
					 AND CONVERT(DATE,T.DT_CAD) = CONVERT(DATE,GETDATE())) AS LOTE_RESERVADO
		WHERE LOTE.CD_EMP = 1
		AND LOTE.CD_FILIAL = @CD_FILIAL
		AND LOTE.CD_PROD = @CD_PROD
		AND LOTE.QTDE_PROD > 0
		AND LOTE.DT_VALID > GetDate()
		GROUP BY LOTE.NR_LOTE, LOTE.DT_VALID, LOTE.DT_FABR, LOTE.QTDE_PROD, ISNULL(LOTE_RESERVADO.QTDE_PROD,0)

		IF (ISNULL((SELECT SUM(QTDE_PROD) FROM @Lotes),0) >= @QT_SOL) 
		BEGIN
		
			DECLARE @NR_LOTE nvarchar(100)
			DECLARE @DT_VALID datetime
			DECLARE @DT_FABR smalldatetime
			DECLARE @QTDE_PROD money
			DECLARE @Ordem INT
			DECLARE @QT_SOL_INTERNO MONEY

			SET @QT_SOL_INTERNO = @QT_SOL;

			DECLARE cursorLotes CURSOR FOR
									SELECT NR_LOTE, DT_VALID, DT_FABR, QTDE_PROD, Ordem
									FROM @Lotes
									ORDER BY Ordem ASC

			OPEN cursorLotes

			FETCH NEXT FROM cursorLotes INTO @NR_LOTE, @DT_VALID, @DT_FABR, @QTDE_PROD, @Ordem

			WHILE @@FETCH_STATUS = 0
			BEGIN
			
				PRINT 'NR_LOTE: ' + @NR_LOTE + ', DT_VALID: ' + CONVERT(nvarchar, @DT_VALID, 120) + ', DT_FABR: ' + CONVERT(nvarchar, @DT_FABR, 120) + ', QTDE_PROD: ' + CONVERT(nvarchar, @QTDE_PROD) + ', Ordem: ' + CONVERT(nvarchar, @Ordem)

				IF @QT_SOL_INTERNO <= @QTDE_PROD
				BEGIN
					--GRAVAR LOTE com @QT_SOL_INTERNO;

					INSERT INTO EST_PROD_TRANSF_IT_LOTE (CD_EMP, CD_FILIAL, CD_CTR, CD_PROD, NR_LOTE, DT_VALID, QTDE_PROD, DT_FABR)
					VALUES (1, @CD_FILIAL, @CD_CTR, @CD_PROD, @NR_LOTE, @DT_VALID, @QT_SOL_INTERNO, @DT_FABR)

					BREAK; /*Encerrar o loop nos lotes*/
				END
				ELSE
				BEGIN
					--GRAVAR LOTE com @QTDE_PROD;

					INSERT INTO EST_PROD_TRANSF_IT_LOTE (CD_EMP, CD_FILIAL, CD_CTR, CD_PROD, NR_LOTE, DT_VALID, QTDE_PROD, DT_FABR)
					VALUES (1, @CD_FILIAL, @CD_CTR, @CD_PROD, @NR_LOTE, @DT_VALID, @QTDE_PROD, @DT_FABR)

					SET @QT_SOL_INTERNO = @QT_SOL_INTERNO - @QTDE_PROD; /*Continuar loop para alocar o restante nos proximos lotes*/
				END


				FETCH NEXT FROM cursorLotes INTO @NR_LOTE, @DT_VALID, @DT_FABR, @QTDE_PROD, @Ordem
			END

			CLOSE cursorLotes
			DEALLOCATE cursorLotes

		END

		DELETE FROM @Lotes;

		-- Busque o próximo registro
		FETCH NEXT FROM cursor_processamento INTO @CD_CTR, @CD_FILIAL, @CD_PROD, @QT_SOL
	END

	-- Feche o cursor e libere recursos
	CLOSE cursor_processamento
	DEALLOCATE cursor_processamento


	DROP TABLE #ProcessamentoLotes;
END

/*
SELECT *
FROM EST_PROD_TRANSF_IT_LOTE
*/


--DELETE FROM EST_PROD_TRANSF_IT_LOTE
--WHERE CD_CTR = 28872