IF EXISTS (SELECT * FROM SYS.PROCEDURES WHERE NAME = 'P_CG_FND_VERBA_MOVIMENTACAO_USO_CONTRATO_VERBA_FIXA')
BEGIN
	DROP PROCEDURE P_CG_FND_VERBA_MOVIMENTACAO_USO_CONTRATO_VERBA_FIXA;
END

GO

CREATE PROCEDURE P_CG_FND_VERBA_MOVIMENTACAO_USO_CONTRATO_VERBA_FIXA (@CD_EMP INT, @CD_FND_VERBA INT)
AS BEGIN


SET NOCOUNT ON

DECLARE @CD_FILIAL			INT
DECLARE @CD_VD				INT
DECLARE @CD_IT				INT
DECLARE @QT_IT_VENDA		MONEY
DECLARE @CD_FND_VERBA_USO	INT
DECLARE @CD_PROD			INT
DECLARE @VLR_IT				MONEY
DECLARE @DT_VD				DATETIME
DECLARE @VLR_SALDO			MONEY
DECLARE @CD_RECEITA			INT
DECLARE @VLR_IT_USO			MONEY
DECLARE @LIMITE_VERBA		MONEY


SET @CD_RECEITA = 0
SET @CD_RECEITA = (SELECT COALESCE(VALOR, '0') FROM PRC_EMP_CONFIG WHERE CD_EMP = @CD_EMP AND CD_CHAVE = 'CD_RECEITA_VERBA_GERENCIAL')

IF @CD_RECEITA = 0
BEGIN
	RAISERROR ('P_CG_FND_VERBA_MOVIMENTACAO_USO_CONTRATO_VERBA_FIXA - CODIGO DA RECEITA PARA LANCAMENTO DO CONTAS A RECEBER NAO ENCONTRADO.',16, 1);
	ROLLBACK TRANSACTION;
	RETURN;
END

CREATE TABLE #CG_FND_VERBA_USO_PROD_LIMITE (
	CD_EMP INT,
	CD_FND_VERBA_USO INT,
	CD_PROD INT,
	VLR_IT MONEY,
	LIMITE_VERBA MONEY)

CREATE TABLE #CG_FND_VERBA_USO_PDV_VD_IT_TEMP (
	CD_EMP INT, 
	CD_FND_VERBA_USO INT,
	CD_FILIAL INT,
	CD_VD INT,
	CD_IT INT,
	VLR_USO MONEY,
	CD_PROD INT)

INSERT INTO
	#CG_FND_VERBA_USO_PROD_LIMITE (CD_EMP, CD_FND_VERBA_USO, CD_PROD, VLR_IT, LIMITE_VERBA)
SELECT
	CG_FND_VERBA_USO_PROD.CD_EMP,
	CG_FND_VERBA_USO_PROD.CD_FND_VERBA_USO,
	CG_FND_VERBA_USO_PROD.CD_PROD,
	CG_FND_VERBA_USO_PROD.VLR_IT,
	CG_FND_VERBA_USO_PROD.LIMITE_VERBA - COALESCE(VERBA_USADA.VLR_USO, 0) AS LIMITE_VERBA
FROM
	CG_FND_VERBA VERBA
	INNER JOIN CG_FND_VERBA_USO_CG_FND_VERBA ON
		VERBA.CD_EMP = CG_FND_VERBA_USO_CG_FND_VERBA.CD_EMP
		AND VERBA.CD_FND_VERBA = CG_FND_VERBA_USO_CG_FND_VERBA.CD_FND_VERBA
	INNER JOIN CG_FND_VERBA_USO_PROD ON
		CG_FND_VERBA_USO_CG_FND_VERBA.CD_EMP = CG_FND_VERBA_USO_PROD.CD_EMP
		AND CG_FND_VERBA_USO_CG_FND_VERBA.CD_FND_VERBA_USO = CG_FND_VERBA_USO_PROD.CD_FND_VERBA_USO
	LEFT JOIN (
		SELECT
			CD_EMP,
			CD_FND_VERBA_USO,
			CD_PROD,
			SUM(VLR_USO) AS VLR_USO
		FROM
			CG_FND_VERBA_USO_PDV_VD_IT
		GROUP BY
			CD_EMP,
			CD_FND_VERBA_USO,
			CD_PROD) VERBA_USADA ON
		CG_FND_VERBA_USO_PROD.CD_EMP = VERBA_USADA.CD_EMP
		AND CG_FND_VERBA_USO_PROD.CD_FND_VERBA_USO = VERBA_USADA.CD_FND_VERBA_USO
		AND CG_FND_VERBA_USO_PROD.CD_PROD = VERBA_USADA.CD_PROD
WHERE
	VERBA.CD_EMP = @CD_EMP
	AND VERBA.CD_FND_VERBA = @CD_FND_VERBA
	

DECLARE 
	CURSOR_VENDAS
CURSOR FAST_FORWARD FOR
SELECT
	PDV_VD_IT.CD_FILIAL,
	PDV_VD_IT.CD_VD,
	PDV_VD_IT.CD_IT,
	PDV_VD_IT.QT_IT AS QT_IT_VENDA,
	CG_FND_VERBA_USO.CD_FND_VERBA_USO,
	PDV_VD_IT.CD_PROD,
	CG_FND_VERBA_USO_PROD.VLR_IT,
	PDV_VD_IT.DT_VD,
	SALDO.VLR_SALDO
FROM
	CG_FND_VERBA VERBA
	INNER JOIN CG_FND_VERBA_USO_CG_FND_VERBA ON
		VERBA.CD_EMP = CG_FND_VERBA_USO_CG_FND_VERBA.CD_EMP
		AND VERBA.CD_FND_VERBA = CG_FND_VERBA_USO_CG_FND_VERBA.CD_FND_VERBA

	INNER JOIN CG_FND_VERBA_USO ON
		CG_FND_VERBA_USO_CG_FND_VERBA.CD_EMP = CG_FND_VERBA_USO.CD_EMP
		AND CG_FND_VERBA_USO_CG_FND_VERBA.CD_FND_VERBA_USO = CG_FND_VERBA_USO.CD_FND_VERBA_USO

	INNER JOIN #CG_FND_VERBA_USO_PROD_LIMITE CG_FND_VERBA_USO_PROD ON
		CG_FND_VERBA_USO_CG_FND_VERBA.CD_EMP = CG_FND_VERBA_USO_PROD.CD_EMP
		AND CG_FND_VERBA_USO_CG_FND_VERBA.CD_FND_VERBA_USO = CG_FND_VERBA_USO_PROD.CD_FND_VERBA_USO

	INNER JOIN CG_FND_VERBA_USO_PRC_FILIAL ON
		CG_FND_VERBA_USO_PROD.CD_EMP = CG_FND_VERBA_USO_PRC_FILIAL.CD_EMP
		AND CG_FND_VERBA_USO_PROD.CD_FND_VERBA_USO = CG_FND_VERBA_USO_PRC_FILIAL.CD_FND_VERBA_USO

	INNER JOIN PDV_VD_IT ON
		CG_FND_VERBA_USO_PRC_FILIAL.CD_EMP = PDV_VD_IT.CD_EMP
		AND CG_FND_VERBA_USO_PRC_FILIAL.CD_FILIAL = PDV_VD_IT.CD_FILIAL
		AND CG_FND_VERBA_USO_PROD.CD_PROD = PDV_VD_IT.CD_PROD

	INNER JOIN PDV_VD  ON
		PDV_VD_IT.CD_EMP = PDV_VD.CD_EMP
		AND PDV_VD_IT.CD_FILIAL = PDV_VD.CD_FILIAL
		AND PDV_VD_IT.CD_VD = PDV_VD.CD_VD

	LEFT JOIN CG_FND_VERBA_USO_PDV_VD_IT ON
		PDV_VD_IT.CD_EMP = CG_FND_VERBA_USO_PDV_VD_IT.CD_EMP
		AND PDV_VD_IT.CD_FILIAL = CG_FND_VERBA_USO_PDV_VD_IT.CD_FILIAL
		AND PDV_VD_IT.CD_VD = CG_FND_VERBA_USO_PDV_VD_IT.CD_VD
		AND PDV_VD_IT.CD_IT = CG_FND_VERBA_USO_PDV_VD_IT.CD_IT
		AND CG_FND_VERBA_USO.CD_FND_VERBA_USO = CG_FND_VERBA_USO_PDV_VD_IT.CD_FND_VERBA_USO

	INNER JOIN UFD_CG_FND_VERBA_RETORNA_VLR (@CD_EMP, @CD_FND_VERBA) SALDO ON
		VERBA.CD_EMP = SALDO.CD_EMP
		AND VERBA.CD_FND_VERBA = SALDO.CD_FND_VERBA

WHERE
	CG_FND_VERBA_USO_PDV_VD_IT.CD_EMP IS NULL
	AND VERBA.CD_EMP = @CD_EMP
	AND VERBA.CD_FND_VERBA = @CD_FND_VERBA
	AND PDV_VD_IT.DT_VD BETWEEN CG_FND_VERBA_USO.DT_INI_APURACAO AND CG_FND_VERBA_USO.DT_FIM_APURACAO
	AND SALDO.VLR_SALDO > 0
	AND CG_FND_VERBA_USO_PROD.LIMITE_VERBA > 0
	AND PDV_VD_IT.ST_IT = 1
	AND PDV_VD.ST_VD = 0
ORDER BY
	PDV_VD_IT.CD_EMP ASC,
	PDV_VD_IT.CD_FILIAL ASC,
	PDV_VD_IT.CD_VD ASC,
	PDV_VD_IT.CD_PROD ASC,
	PDV_VD_IT.TOT_IT ASC,
	PDV_VD_IT.VLR_IT ASC,
	PDV_VD_IT.TX_DESC DESC;

OPEN CURSOR_VENDAS

FETCH NEXT FROM CURSOR_VENDAS INTO @CD_FILIAL, @CD_VD, @CD_IT, @QT_IT_VENDA, @CD_FND_VERBA_USO, @CD_PROD, @VLR_IT, @DT_VD, @VLR_SALDO

WHILE @@FETCH_STATUS = 0
BEGIN
	
	SET @LIMITE_VERBA = COALESCE((SELECT LIMITE_VERBA FROM #CG_FND_VERBA_USO_PROD_LIMITE WHERE CD_EMP = @CD_EMP AND CD_FND_VERBA_USO = @CD_FND_VERBA_USO AND CD_PROD = @CD_PROD), 0)

	IF  @LIMITE_VERBA > 0
	BEGIN
		
		SET @VLR_IT_USO = CASE WHEN @VLR_IT * @QT_IT_VENDA > @LIMITE_VERBA THEN @LIMITE_VERBA ELSE @VLR_IT * @QT_IT_VENDA END

		INSERT INTO
			#CG_FND_VERBA_USO_PDV_VD_IT_TEMP (CD_EMP, CD_FND_VERBA_USO, CD_FILIAL, CD_VD, CD_IT, VLR_USO, CD_PROD)
		VALUES
			(@CD_EMP, @CD_FND_VERBA_USO, @CD_FILIAL, @CD_VD, @CD_IT, @VLR_IT_USO, @CD_PROD)

		UPDATE
			PDV_VD_IT
		SET
			VLR_RECOMPOSICAO_MARGEM = COALESCE(VLR_RECOMPOSICAO_MARGEM, 0) + @VLR_IT_USO
		WHERE
			CD_EMP = @CD_EMP
			AND CD_FILIAL = @CD_FILIAL 
			AND CD_VD = @CD_VD 
			AND CD_IT = @CD_IT
			
		UPDATE
			#CG_FND_VERBA_USO_PROD_LIMITE
		SET
			LIMITE_VERBA = LIMITE_VERBA - @VLR_IT_USO
		WHERE
			CD_EMP = @CD_EMP
			AND CD_FND_VERBA_USO = @CD_FND_VERBA_USO 
			AND CD_PROD = @CD_PROD
	END


	FETCH NEXT FROM CURSOR_VENDAS INTO @CD_FILIAL, @CD_VD, @CD_IT, @QT_IT_VENDA, @CD_FND_VERBA_USO, @CD_PROD, @VLR_IT, @DT_VD, @VLR_SALDO
END


IF EXISTS (SELECT 1 FROM #CG_FND_VERBA_USO_PDV_VD_IT_TEMP)
BEGIN
	DECLARE @VLR_VERBA MONEY
	DECLARE @CD_FND_VERBA_SAQUE INT
	DECLARE @VLR_SLD_ANT MONEY

	SET @VLR_VERBA = 0;

	SELECT @VLR_VERBA = SUM(VLR_USO) FROM #CG_FND_VERBA_USO_PDV_VD_IT_TEMP
	SELECT @VLR_SLD_ANT = COALESCE(SUM(VLR_SAQUE),0) FROM CG_FND_VERBA_SAQUE WHERE CD_EMP = @CD_EMP AND CD_FND_VERBA = CD_FND_VERBA
	SELECT @CD_FND_VERBA_SAQUE = COALESCE(MAX(CD_FND_VERBA_SAQUE), 0) + 1 FROM CG_FND_VERBA_SAQUE WHERE CD_EMP = @CD_EMP AND CD_FND_VERBA = @CD_FND_VERBA
	
	PRINT 'Gravando saque ' +  (CONVERT(VARCHAR(8), GETDATE(),108));	
	INSERT INTO
		CG_FND_VERBA_SAQUE (CD_EMP, CD_FND_VERBA, CD_FND_VERBA_SAQUE, CD_DESP_REC, HIST_SAQ, DT_SAQUE, STATUS, VLR_SAQUE, SALDO_ANT, DT_CAD, CD_USU)
	VALUES
		(@CD_EMP, @CD_FND_VERBA, @CD_FND_VERBA_SAQUE, @CD_RECEITA, 'ALOCACAO DE VERBA NO CONTRATO: ' + CAST(@CD_FND_VERBA AS VARCHAR), CONVERT(DATE, GETDATE()), 0, @VLR_VERBA, @VLR_SLD_ANT, CONVERT(DATE, GETDATE()), 1)


	PRINT 'Gravando verba nos itens da venda ' +  (CONVERT(VARCHAR(8), GETDATE(),108));
	INSERT INTO
		CG_FND_VERBA_USO_PDV_VD_IT(CD_EMP, CD_FND_VERBA_USO, CD_FILIAL, CD_VD, CD_IT, VLR_USO, CD_PROD)
	SELECT
		CD_EMP,
		CD_FND_VERBA_USO,
		CD_FILIAL,
		CD_VD,
		CD_IT,
		VLR_USO,
		CD_PROD 
	FROM
		#CG_FND_VERBA_USO_PDV_VD_IT_TEMP
		
	PRINT 'Vinculo da venda com o saque ' +  (CONVERT(VARCHAR(8), GETDATE(),108));	
	INSERT INTO
		CG_FND_VERBA_SAQUE_CG_FND_VERBA_USO_PDV_VD_IT(CD_EMP, CD_FND_VERBA_USO, CD_FILIAL, CD_VD, CD_IT, CD_FND_VERBA_SAQUE, CD_FND_VERBA)
	SELECT
		CD_EMP,
		CD_FND_VERBA_USO,
		CD_FILIAL,
		CD_VD,
		CD_IT,
		@CD_FND_VERBA_SAQUE,
		@CD_FND_VERBA
	FROM
		#CG_FND_VERBA_USO_PDV_VD_IT_TEMP
END

CLOSE CURSOR_VENDAS
DEALLOCATE CURSOR_VENDAS

DROP TABLE #CG_FND_VERBA_USO_PROD_LIMITE
DROP TABLE #CG_FND_VERBA_USO_PDV_VD_IT_TEMP


END