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

/*
=======================================================================================
'Job.................: Itec - Conciliacao cartao televendas
'Funo..............: P_TELE_ORC_PAG_CARTAO_PDV_VD_TEF
'Objetivo............: Inserir dados de cartao captados no televendas
'Tabelas.............: PDV_VD_TEF | PDV_VD | TELE_ORC_PAG_CARTAO
'Dt Modificao......: 07/06/2024
'Autor...............: Ramam Souza
'Modificao.........: Gerson Reis
'=======================================================================================
Tag #A001 - Alterao do codigo  
Tag #I001 - Incluso de novo codigo 
*/

CREATE PROCEDURE [dbo].[P_TELE_ORC_PAG_CARTAO_PDV_VD_TEF]
as
SET NOCOUNT ON;
DECLARE @ErrorMessage NVARCHAR(4000);  
DECLARE @ErrorSeverity INT;  
DECLARE @ErrorState INT;  
-----------------------------------------------------------------------------------------------------
	IF OBJECT_ID ('Tempdb.dbo.#Tmp_PDV_VD_TEF') IS NOT NULL
	BEGIN
		DROP TABLE #Tmp_PDV_VD_TEF 
	END
	create table #Tmp_PDV_VD_TEF (CD_EMP		INT,
									CD_FILIAL	INT,
									CD_VD		INT,
									CD_ADM_CARTAO INT,
									NR_AUTORIZACAO NVARCHAR(100),
									VL_CARTAO	MONEY,
									NSU_SITEF	BIGINT,
									NR_ECF_ADM	INT,
									CD_CX_ADM	INT,
									DT_VD_ADM	DATETIME,
									TP_ADM		INT,
									QTDE_PARCELAS	INT,
									CD_ORC_PAG_CARTAO	INT,
									VL_CRED	MONEY) 
	INSERT INTO #TMP_PDV_VD_TEF
	SELECT 
		  V.CD_EMP
		, V.CD_FILIAL
		, V.CD_VD 
		, c.CD_ADM_CARTAO --implementar CD_ADM_CARTAO Q VAI VIR DA TBL TELE_ORC_PAG_CARTAO
		, C.NR_AUTORIZACAO  
		, P.VLR_PAG VL_CARTAO
		, case when isnumeric(C.NSU)=1 AND C.NSU NOT LIKE '%,%' AND C.NSU NOT LIKE '%.%' then  COALESCE(try_convert(BIGINT,C.NSU),0) else 0 end    NSU_SITEF 
		, V.NR_ECF NR_ECF_ADM
		, V.CD_CX CD_CX_ADM
		, V.DT_VD DT_VD_ADM 
		, A.TP_ADM 
		, C.QTDE_PARC QTDE_PARCELAS 
		, PE.CD_ORC_PAG_CARTAO
		, ISNULL(CRED.VLR_PAG,0) as vl_cred
	FROM 
		TELE_ORC_PAG p
		JOIN TELE_ORC_PAG_CARTAO c on
		P.CD_ORC_PAG = C.CD_ORC_PAG
		JOIN TELE_ORC_PAG_CARTAO_PENDENTE Pe on
		c.CD_ORC_PAG_CARTAO = Pe.CD_ORC_PAG_CARTAO
		JOIN TELE_ORC O ON 
		P.CD_ORC = O.CD_ORC 
		--JOIN TELE_ORC_ETAPA_PDV_VD OV ON 
		--P.CD_ORC = OV.CD_ORC
		JOIN PDV_VD V ON 
		O.CD_ORC	= V.NR_PED_TELEVD AND
		O.CD_EMP	= V.CD_EMP AND
		O.CD_FILIAL = V.CD_FILIAL
		JOIN RC_ADM_CARTAO A ON 
		c.CD_ADM_CARTAO  = A.CD_ADM_CARTAO  
		LEFT JOIN RC_ADM_CARTAO_INTERFACEPDV I ON 
		A.CD_ADM_CARTAO = I.CD_ADM_CARTAO 
		OUTER APPLY (SELECT PAG2.CD_ORC, SUM(PAG2.VLR_PAG) AS VLR_PAG
					 FROM TELE_ORC_PAG_CREDIARIO OC
					 INNER JOIN TELE_ORC_PAG PAG2 ON PAG2.CD_ORC_PAG = OC.CD_ORC_PAG
					 WHERE PAG2.CD_ORC = P.CD_ORC
					 GROUP BY PAG2.CD_ORC) CRED /*FARMAGOI-49540*/
		;
-----------------------------------------------------------------------------------------------------
--Remove da pendencia cartoes com movimento j lancado
-----------------------------------------------------------------------------------------------------
		SELECT
			t.CD_EMP		
			, t.CD_FILIAL		
			, t.CD_VD			
			, t.CD_ORC_PAG_CARTAO
			into #TblRemover
		FROM 
			#TMP_PDV_VD_TEF  T 
			join RC_ADM_CARTAO_MOV_LOTE_PDV_VD  v on 
			t.CD_EMP		= v.CD_EMP AND
			t.CD_FILIAL		= v.CD_FILIAL AND
			t.CD_VD			= v.CD_VD
			JOIN TELE_ORC_PAG_CARTAO_PENDENTE Pe on
			t.CD_ORC_PAG_CARTAO = Pe.CD_ORC_PAG_CARTAO
		WHERE
			v.CD_EMP=1 
		delete from PE
		FROM 
			#TblRemover  T  
			JOIN TELE_ORC_PAG_CARTAO_PENDENTE Pe on
			t.CD_ORC_PAG_CARTAO = Pe.CD_ORC_PAG_CARTAO
		WHERE
			T.CD_EMP=1 
		delete from V
		FROM 
			#TblRemover  T  
			join #TMP_PDV_VD_TEF  v on 
			t.CD_EMP		= v.CD_EMP AND
			t.CD_FILIAL		= v.CD_FILIAL AND
			t.CD_VD			= v.CD_VD
		WHERE
			v.CD_EMP=1 ;
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
	BEGIN TRY
		/*Deletar as conciliaes do registro anterior*/
		INSERT INTO CONCILIACAO_CARTOES_FINAL_CONTROLE
		SELECT 'DELETADO', FINAL.CD_CTR
							,FINAL.CD_EMP
							,FINAL.CD_FILIAL
							,FINAL.CD_CX
							,FINAL.NR_ECF
							,FINAL.DT_VD
							,FINAL.VLR_VENDA
							,FINAL.COD_SIT
							,FINAL.IDT_REDE
							,FINAL.DESCR_REDE
							,FINAL.IDT_PRODUTO
							,FINAL.DESCR_PRODUTO
							,FINAL.DESCR_TRN
							,FINAL.IDT_BANDEIRA
							,FINAL.DESCR_BANDEIRA
							,FINAL.NUM_PARCELAS
							,FINAL.CODIGO_RESP
							,FINAL.ESTADO_TRN
							,FINAL.DATA_LANC
							,FINAL.DATAFISCAL
							,FINAL.COD_AUTORIZ
							,FINAL.ST_VENDA
							,FINAL.NSU_SITEF
							,FINAL.TP_OP_AUTOMATICO
							,FINAL.CD_CTR_PDV_VD_TEF
							,FINAL.CD_VD
							,FINAL.IDT_TRANSACAO
							,FINAL.CD_CTR_CONCILIADOR
							,FINAL.CD_RECTO_TEF
							,FINAL.CD_CTR_PDV_CUPOM_NAO_FISCAL_TEF
							,FINAL.CD_CTR_ID_QR_LINX
		FROM 
			#TMP_PDV_VD_TEF  T
			JOIN PDV_VD_TEF f ON 
			T.CD_EMP	 = F.CD_EMP AND
			T.CD_FILIAL =F.CD_FILIAL AND
			T.CD_VD		=F.CD_VD
			JOIN CONCILIACAO_CARTOES_FINAL FINAL ON FINAL.CD_CTR_PDV_VD_TEF = F.CD_CTR
													AND FINAL.CD_VD = F.CD_VD
													AND FINAL.CD_FILIAL = F.CD_FILIAL
													AND FINAL.CD_EMP = F.CD_EMP
		WHERE
			F.CD_EMP=1
		DELETE FINAL FROM 
			#TMP_PDV_VD_TEF  T
			JOIN PDV_VD_TEF f ON 
			T.CD_EMP	 = F.CD_EMP AND
			T.CD_FILIAL =F.CD_FILIAL AND
			T.CD_VD		=F.CD_VD
			JOIN CONCILIACAO_CARTOES_FINAL FINAL ON FINAL.CD_CTR_PDV_VD_TEF = F.CD_CTR
													AND FINAL.CD_VD = F.CD_VD
													AND FINAL.CD_FILIAL = F.CD_FILIAL
													AND FINAL.CD_EMP = F.CD_EMP
		WHERE
			F.CD_EMP=1
	 /*FIM - Deletar as conciliaes do registro anterior*/
		DELETE FROM f
		FROM 
			#TMP_PDV_VD_TEF  T
			JOIN PDV_VD_TEF f ON 
			T.CD_EMP	 = F.CD_EMP AND
			T.CD_FILIAL =F.CD_FILIAL AND
			T.CD_VD		=F.CD_VD
		WHERE
			F.CD_EMP=1
			AND ISNULL(T.VL_CRED,0) = 0 
		-- Insere registros do TEF
		INSERT INTO  PDV_VD_TEF(CD_EMP,CD_FILIAL,CD_VD, CD_TRN_TEF,CD_ADM_CARTAO,NR_AUTORIZACAO
									,COD_REDE,COD_MODALIDADE,COD_BANDEIRA,VL_CARTAO,NSU_SITEF,VL_SAQUE_CARTAO,DT_CONSD
									,NR_ECF_ADM,CD_CX_ADM,DT_VD_ADM,CANCEL_TEF,CONC_CARTAO,TP_ADM,NM_OPERADORA_PDV
									,QTDE_PARCELAS,TEF_GATEWAY,MODALIDADE,nr_comprovante,NR_ESTABELECIMENTO,VL_TOTAL_DOACAO
									,IDT_TRANSACAO,T_BAND)
		SELECT 
			  t.CD_EMP
			, t.CD_FILIAL
			, t.CD_VD 
			, 0 CD_TRN_TEF
			, t.CD_ADM_CARTAO 
			, t.NR_AUTORIZACAO 
			, 0 COD_REDE
			, 0 COD_MODALIDADE
			, 0 COD_BANDEIRA
			, t.VL_CARTAO
			, t.NSU_SITEF
			, 0 VL_SAQUE_CARTAO
			, GETDATE() DT_CONSD
			, t.NR_ECF_ADM
			, t.CD_CX_ADM
			, t.DT_VD_ADM
			, 0 CANCEL_TEF
			, 0 CONC_CARTAO
			, t.TP_ADM
			, '' NM_OPERADORA_PDV
			, t.QTDE_PARCELAS
			, '' TEF_GATEWAY
			, '' MODALIDADE
			, '' NR_COMPROVANTE
			, '' NR_ESTABELECIMENTO
			, 0 VL_TOTAL_DOACAO
			, null IDT_TRANSACAO
			, null T_BAND 
		FROM 
			#TMP_PDV_VD_TEF t
		--Troca forma de recebimento de dinheiro para cartao.
		UPDATE V
		SET 
			VLR_CARTAO = T.VL_CARTAO,
			VLR_DINH   =  VLR_LIQ_VD - (t.VL_CARTAO + VLR_CHQS+VLR_TICKETS+VLR_DUP+VLR_CONV) --Atualiza o vlr dinheiro
		FROM 
			PDV_VD v
			JOIN (SELECT CD_EMP, CD_VD, CD_FILIAL, SUM(t.VL_CARTAO + t.VL_CRED) VL_CARTAO FROM
						#TMP_PDV_VD_TEF T GROUP BY CD_EMP, CD_VD, CD_FILIAL ) T ON 
			V.CD_EMP = T.CD_EMP AND
			V.CD_VD = T.CD_VD AND
			V.CD_FILIAL = T.CD_FILIAL ;
		--Remove da pendencia
		DELETE FROM PE
		FROM
			#TMP_PDV_VD_TEF c
			JOIN TELE_ORC_PAG_CARTAO_PENDENTE Pe on
			c.CD_ORC_PAG_CARTAO = Pe.CD_ORC_PAG_CARTAO
	END TRY
	BEGIN CATCH
		SELECT   
			@ErrorMessage = ERROR_MESSAGE(),  
			@ErrorSeverity = ERROR_SEVERITY(),  
			@ErrorState = ERROR_STATE();  
		RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);  
	END CATCH
