IF EXISTS (SELECT * FROM SYS.procedures WHERE NAME = 'P_ATUALIZA_CONVENIO_EM_CARTAO')
	BEGIN
		DROP PROCEDURE P_ATUALIZA_CONVENIO_EM_CARTAO
	END

	
GO
-- EXEC P_ATUALIZA_CONVENIO_EM_CARTAO

CREATE  PROCEDURE [dbo].[P_ATUALIZA_CONVENIO_EM_CARTAO] AS 

SET NOCOUNT ON;

DECLARE @TABELA TABLE (
		TABELA VARCHAR(255),
		CHAVE1 INT,
		CHAVE2 INT,
		CHAVE3 INT,
		CHAVE4 INT,
		CHAVE5 INT)

CREATE TABLE #RcVdConv(CD_EMP INT,
					   CD_FILIAL  INT,
					   CD_CX INT,
					   CD_VD INT,
					   NR_ECF INT,
					   CD_CLI INT,
					   DT_VD DATETIME,
					   VLR_CONV MONEY,
					   CD_USU INT,
					   CD_CONV INT,
					   CD_CTR_TEF INT)

CREATE TABLE #RC_ADM_CARTAO_CONV(CD_EMP				INT, 
								 CD_ADM_CARTAO		INT, 
								 NM_OPERADORA_PDV	VARCHAR(50), 
								 COD_BANDEIRA		INT)
					   
DECLARE @CD_EMP INT
DECLARE @CD_FILIAL  INT
DECLARE @CD_CX INT
DECLARE @CD_VD INT
DECLARE @NR_ECF INT
DECLARE @CD_CLI INT
DECLARE @DT_VD DATETIME
DECLARE @VLR_CONV MONEY
DECLARE @CD_USU INT
DECLARE @CD_CONV INT
DECLARE @CD_CTR_TEF INT
DECLARE @CD_CTR INT
DECLARE @TP_GER_TEF	INT
DECLARE @CD_ADM_CARTAO_CONVCARD INT


---------------------------------------------------------------------------------------------------------------
--SELECIONANDO O TIPO DO GERENCIADO TEF
---------------------------------------------------------------------------------------------------------------
SET @TP_GER_TEF = ABS(ISNULL((
								SELECT 
									MAX(VALOR)
								FROM 
									PRC_FILIAL_CONFIG C
									INNER JOIN PDV_VD_TEF_CARTAO_CONV_PEND P ON 
										C.CD_EMP = P.CD_EMP
										AND C.CD_FILIAL = P.CD_FILIAL
								WHERE 
									CD_CHAVE = 'GERENCIADOR_TEF'),0))

---------------------------------------------------------------------------------------------------------------
--INSERINDO O CODIGO DE BANDEIRA E NOME DA OPERADORA NO PDV DEFINIDA NO CARTAO CONVCARD
---------------------------------------------------------------------------------------------------------------
INSERT INTO
	#RC_ADM_CARTAO_CONV (CD_EMP, CD_ADM_CARTAO, NM_OPERADORA_PDV, COD_BANDEIRA)						
SELECT 
	T.CD_EMP,
	T.CD_ADM_CARTAO,
	IT.NOME AS NM_OPERADORA_PDV,
	(CASE WHEN @TP_GER_TEF = 0 THEN RC_ADM_CARTAO_REDE_TEF.CD_REDE_TEF ELSE T.CD_BANDEIRA_SCOPE END) AS COD_BANDEIRA
FROM 
	RC_ADM_CARTAO T 
	LEFT JOIN RC_ADM_CARTAO_INTERFACEPDV IT ON
		IT.CD_ADM_CARTAO = T.CD_ADM_CARTAO
	LEFT JOIN RC_ADM_CARTAO_REDE_TEF ON
		T.CD_BANDEIRA_TEF = RC_ADM_CARTAO_REDE_TEF.CD_CTR_REDE_TEF
WHERE 
	T.TP_ADM = 4
	

---------------------------------------------------------------------------------------------------------------
--MAPEAR OS TEF QUE PODEM SER CARTAO CONVCARD DE ACORDO COM O COD_BANDEIRA OU NM_OPERADORA_PDV E QUE AINDA NAO FORAM MIGRADAS			
---------------------------------------------------------------------------------------------------------------
DECLARE 
	ProcVendaTefConvenio 
CURSOR FAST_FORWARD FOR 
SELECT
	PDV_TEF.CD_EMP,
	PDV_TEF.CD_FILIAL,
	PDV_TEF.CD_VD,
	PDV_TEF.CD_CTR,
	I.CD_ADM_CARTAO
FROM 
	PDV_VD_TEF PDV_TEF 
	INNER JOIN PDV_VD_TEF_CARTAO_CONV_PEND PEND ON
		PDV_TEF.CD_EMP 			= PEND.CD_EMP
		AND PDV_TEF.CD_FILIAL 	= PEND.CD_FILIAL
		AND PDV_TEF.CD_VD 		= PEND.CD_VD
		AND PDV_TEF.CD_CTR 		= PEND.CD_CTR
	INNER JOIN #RC_ADM_CARTAO_CONV I  ON
		I.COD_BANDEIRA = PDV_TEF.COD_BANDEIRA AND
		I.NM_OPERADORA_PDV COLLATE SQL_Latin1_General_CP1_CI_AS = PDV_TEF.NM_OPERADORA_PDV COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE 
	PDV_TEF.VL_CARTAO > 0
		
OPEN ProcVendaTefConvenio;
FETCH NEXT FROM ProcVendaTefConvenio INTO @CD_EMP,@CD_FILIAL,@CD_VD,@CD_CTR_TEF,@CD_ADM_CARTAO_CONVCARD
	
WHILE @@FETCH_STATUS = 0
BEGIN
	
	--Atualizando bloqueio dos produtos nas lojas
	UPDATE 
		PDV_TEF 
	SET 
		CD_ADM_CARTAO = @CD_ADM_CARTAO_CONVCARD
	FROM 
		PDV_VD_TEF PDV_TEF 
	WHERE 
		CD_EMP=@CD_EMP
		AND CD_FILIAL=@CD_FILIAL
		AND CD_VD=@CD_VD
		AND CD_CTR=@CD_CTR_TEF	

	FETCH NEXT FROM ProcVendaTefConvenio INTO @CD_EMP,@CD_FILIAL,@CD_VD,@CD_CTR_TEF,@CD_ADM_CARTAO_CONVCARD
END;

CLOSE ProcVendaTefConvenio;
DEALLOCATE ProcVendaTefConvenio;


---------------------------------------------------------------------------------------------------------------
--ELIMINANDO AS PENDENCIAS QUE NAO POSSUI O COD_BANDEIRA OU NM_OPERADORA_PDV IGUAL O DO CARTAO CONVCARD			
---------------------------------------------------------------------------------------------------------------
DELETE
	P
FROM 
	PDV_VD_TEF_CARTAO_CONV_PEND P
	INNER JOIN PDV_VD_TEF T ON
		P.CD_EMP		= T.CD_EMP
		AND P.CD_FILIAL	= T.CD_FILIAL
		AND P.CD_VD		= T.CD_VD
		AND P.CD_CTR	= T.CD_CTR
	INNER JOIN PDV_VD V ON
		P.CD_EMP		= V.CD_EMP
		AND P.CD_FILIAL	= V.CD_FILIAL
		AND P.CD_VD		= V.CD_VD
	INNER JOIN RC_CLI_CONV_RC_CLI ON
		V.CD_EMP		= RC_CLI_CONV_RC_CLI.CD_EMP
		AND V.CD_CLI	= RC_CLI_CONV_RC_CLI.CD_CLI
WHERE 
	NOT EXISTS (
		SELECT
			*
		FROM
			RC_ADM_CARTAO_RC_CLI_CONV
			INNER JOIN RC_ADM_CARTAO ON
				RC_ADM_CARTAO_RC_CLI_CONV.CD_EMP = RC_ADM_CARTAO.CD_EMP
				AND RC_ADM_CARTAO_RC_CLI_CONV.CD_ADM_CARTAO = RC_ADM_CARTAO.CD_ADM_CARTAO
		WHERE
			RC_ADM_CARTAO_RC_CLI_CONV.CD_EMP = T.CD_EMP
			AND RC_ADM_CARTAO_RC_CLI_CONV.CD_ADM_CARTAO = T.CD_ADM_CARTAO
			AND RC_ADM_CARTAO_RC_CLI_CONV.CD_CONV = RC_CLI_CONV_RC_CLI.CD_CONV
			AND RC_ADM_CARTAO.TP_ADM = 4)

DELETE
	P
FROM 
	PDV_VD_TEF_CARTAO_CONV_PEND P
	INNER JOIN PDV_VD_RC_VD_CONV ON
		P.CD_EMP 		= PDV_VD_RC_VD_CONV.CD_EMP AND
		P.CD_FILIAL 	= PDV_VD_RC_VD_CONV.CD_FILIAL AND
		P.CD_VD 		= PDV_VD_RC_VD_CONV.CD_VD


-----------------------------------------------------------------------------
--CONVERTENDO CONVCARD EM CONVENIO
-----------------------------------------------------------------------------
INSERT INTO 
	#RcVdConv
SELECT
	PDV_VD.CD_EMP,
	PDV_VD.CD_FILIAL,
	PDV_VD.CD_CX,
	PDV_VD.CD_VD,
	PDV_VD.NR_ECF,
	PDV_VD.CD_CLI,
	PDV_VD.DT_VD,
	PDV_VD_TEF.VL_CARTAO,
	PDV_VD.CD_USU,
	PDV_VD.CD_CONV,
	PDV_VD_TEF.CD_CTR
FROM
	PDV_VD 
	INNER JOIN PDV_VD_TEF ON
		PDV_VD.CD_EMP 		= PDV_VD_TEF.CD_EMP AND
		PDV_VD.CD_FILIAL 	= PDV_VD_TEF.CD_FILIAL AND
		PDV_VD.CD_VD 		= PDV_VD_TEF.CD_VD 	
	INNER JOIN RC_ADM_CARTAO ON 
		RC_ADM_CARTAO.CD_EMP 			= PDV_VD_TEF.CD_EMP AND
		RC_ADM_CARTAO.CD_ADM_CARTAO 	= PDV_VD_TEF.CD_ADM_CARTAO 
	INNER JOIN PDV_VD_TEF_CARTAO_CONV_PEND PEND ON 
		PDV_VD_TEF.CD_EMP 		= PEND.CD_EMP AND
		PDV_VD_TEF.CD_FILIAL 	= PEND.CD_FILIAL AND
		PDV_VD_TEF.CD_VD 		= PEND.CD_VD AND
		PDV_VD_TEF.CD_CTR 		= PEND.CD_CTR
	INNER JOIN RC_CLI_CONV_RC_CLI ON
		PDV_VD.CD_EMP = RC_CLI_CONV_RC_CLI.CD_EMP
		AND PDV_VD.CD_CLI = RC_CLI_CONV_RC_CLI.CD_CLI
	LEFT OUTER JOIN PDV_VD_RC_VD_CONV ON
		PDV_VD.CD_EMP 		= PDV_VD_RC_VD_CONV.CD_EMP AND
		PDV_VD.CD_FILIAL 	= PDV_VD_RC_VD_CONV.CD_FILIAL AND
		PDV_VD.CD_VD 		= PDV_VD_RC_VD_CONV.CD_VD
WHERE
	RC_ADM_CARTAO.TP_ADM = 4
	AND PDV_VD_RC_VD_CONV.CD_VD  IS NULL
	AND EXISTS (
		SELECT
			*
		FROM
			RC_ADM_CARTAO_RC_CLI_CONV
		WHERE
			CD_EMP = PDV_VD_TEF.CD_EMP
			AND CD_ADM_CARTAO = PDV_VD_TEF.CD_ADM_CARTAO
			AND CD_CONV = RC_CLI_CONV_RC_CLI.CD_CONV)

DECLARE
	vendasconv 
cursor 
	fast_forward for		
select 	
	PDV_VD.CD_EMP,
	PDV_VD.CD_FILIAL,
	PDV_VD.CD_CX,
	PDV_VD.CD_VD,
	PDV_VD.NR_ECF,
	PDV_VD.CD_CLI,
	PDV_VD.DT_VD,
	PDV_VD.VLR_CONV ,
	PDV_VD.CD_USU,
	PDV_VD.CD_CONV,
	PDV_VD.CD_CTR_TEF
from 
	#RcVdConv as pdv_vd

OPEN vendasconv   
FETCH NEXT FROM vendasconv INTO @CD_EMP,@CD_FILIAL,@CD_CX,@CD_VD,@NR_ECF,@CD_CLI,@DT_VD,@VLR_CONV,@CD_USU,@CD_CONV,@CD_CTR_TEF 

WHILE @@FETCH_STATUS = 0   
BEGIN   

	PRINT CONCAT('Filial: ', @CD_FILIAL,' - Venda: ', @CD_VD, ' - Caixa: ', @CD_CX, ' - NrEcf: ', @NR_ECF);

	DELETE FROM  @TABELA;

	INSERT INTO
		@TABELA
	EXEC 
		P_GET_NEXT_KEY_TABLE 'RC_VD_CONV', 1;

	SET @CD_CTR = (SELECT TOP 1 CHAVE2 FROM @TABELA);

	BEGIN TRY 
		BEGIN TRAN;

		INSERT INTO 
			RC_VD_CONV (CD_EMP, CD_CTR, CD_FILIAL,CD_CX, NR_ECF,CD_CLI,
						DT_VD,	DT_FECHA,VLR_CONV,ST_FECHVD_CONV,
						CD_USU,	DT_CAD,DT_ATUAL,TP_LANC, NR_AUTORIZACAO,
						PARC, DT_GLOSA,TP_CANCEL, CD_CONV,
						HR_VD, FLAG_CONV_AFASTADO,ORIG_CONS,QTD_DIAS_PRORROG)
		SELECT 
			@CD_EMP,
			@CD_CTR,
			@CD_FILIAL,
			@CD_CX,
			@NR_ECF,
			@CD_CLI,
			@DT_VD,
			@DT_VD,
			@VLR_CONV,
			0	ST_FECHVD_CONV,
			@CD_USU,
			CONVERT(CHAR(10),GETDATE(),112),
			CONVERT(CHAR(10),GETDATE(),112),
			0 ST_FECHVD_CONV,
			'' NR_AUTORIZACAO,
			1	PARC,
			null DT_GLOSA,
			0 TP_CANCEL,	
			@CD_CONV CD_CONV,
			GETDATE() HR_VD,
			0 FLAG_CONV_AFASTADO,
			0	ORIG_CONS, 
			0 QTD_DIAS_PRORROG

			UPDATE
				PDV_VD
			SET 
				VLR_CONV = @VLR_CONV,
				VLR_CARTAO = (CASE WHEN VLR_CARTAO >= @VLR_CONV THEN (VLR_CARTAO - @VLR_CONV) ELSE VLR_CARTAO END)
			WHERE
				CD_EMP=@CD_EMP AND
				CD_FILIAL=@CD_FILIAL AND
				CD_VD=@CD_VD 

			UPDATE
				PDV_VD_TEF
			SET 				
				VL_CARTAO = 0
			WHERE
				CD_EMP=@CD_EMP AND
				CD_FILIAL=@CD_FILIAL AND
				CD_VD=@CD_VD AND
				CD_CTR=@CD_CTR_TEF

			INSERT INTO	
				PDV_VD_RC_VD_CONV (CD_EMP,CD_FILIAL,CD_VD,CD_CTR)
			SELECT 
				@CD_EMP, @CD_FILIAL, @CD_VD, @CD_CTR
			
			DELETE FROM 
				PDV_VD_TEF_CARTAO_CONV_PEND
			WHERE 
				CD_EMP=@CD_EMP AND
				CD_FILIAL=@CD_FILIAL AND
				CD_VD=@CD_VD AND
				CD_CTR=@CD_CTR_TEF

			COMMIT;
		END TRY 
		
		BEGIN CATCH
			ROLLBACK;
			PRINT CONCAT('Erro: ', ERROR_NUMBER(), ' - Mensagem: ', ERROR_MESSAGE())
		END CATCH 

	FETCH NEXT FROM vendasconv INTO @CD_EMP,@CD_FILIAL,@CD_CX,@CD_VD,@NR_ECF,@CD_CLI,@DT_VD,@VLR_CONV,@CD_USU,@CD_CONV,@CD_CTR_TEF 

END 

CLOSE vendasconv   
DEALLOCATE vendasconv

DROP TABLE #RcVdConv
DROP TABLE #RC_ADM_CARTAO_CONV
