IF  EXISTS( SELECT  * FROM  SYS.OBJECTS 
	WHERE  NAME LIKE 'P_PDV_VD_TEF_PARC' AND TYPE = 'P' )
BEGIN
	DROP PROCEDURE P_PDV_VD_TEF_PARC
END

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
/*
=======================================================================================
'Job.................: Linx - TBD
'Autor..............: Ramam
'Função..............: 
'Objetivo............: Gerar parcelamento das vendas em cartao
'Tabelas.............: PDV_VD_TEF | PDV_VD_TEF_PARC |  RC_ADM_CARTAO
					   PDV_RECBTO_TEF_PARC | PDV_CUPOM_NAO_FISCAL | PDV_CUPOM_NAO_FISCAL_TEF | PDV_RECBTO_TEF
'Dt Modificação......: 15/01/2021
'Modificação.........:  
'=======================================================================================
*/
CREATE PROCEDURE P_PDV_VD_TEF_PARC (@Filial int=0, @DataMov Date=null)
AS
SET NOCOUNT ON;
/*****************************************************************************************************************************************************************************************/
 	IF  OBJECT_ID('TEMPDB.DBO.#TblParcelar') IS NOT NULL
	BEGIN
		 DROP TABLE #TblParcelar
	END 
	IF  OBJECT_ID('TEMPDB.DBO.#TblParcelas') IS NOT NULL
	BEGIN
		 DROP TABLE #TblParcelas
	END 

	CREATE TABLE #TblParcelar(CD_EMP		int
								,CD_FILIAL	int
								,CD_VD		int
								,CD_CTR		int
								,PARC		int
								,DT_VD_ADM	datetime
								,VL_CARTAO			money
								,VL_SAQUE_CARTAO	money
								,VL_TOTAL_DOACAO	money
								,NSU_SITEF			money
								,NR_COMPROVANTE	varchar(100)
								,QT_PARC_ADM	int
								,CD_ADM_CARTAO	int
								,TP_RATEIO		int
								,PERC_TAXA		money)

	CREATE TABLE #TblParcelas(CD_EMP		int
								,CD_FILIAL	int
								,CD_VD		int
								,CD_CTR		int
								,PARCELA	int
								,DT_VENCTO	datetime
								,VLR_PARCELA money
								,VLR_TAXA		Money)


	IF  OBJECT_ID('TEMPDB.DBO.#TblParcelarNaoFiscal') IS NOT NULL
	BEGIN
		 DROP TABLE #TblParcelarNaoFiscal
	END 
	IF  OBJECT_ID('TEMPDB.DBO.#TblParcelasNaoFiscal') IS NOT NULL
	BEGIN
		 DROP TABLE #TblParcelasNaoFiscal
	END 
	CREATE TABLE #TblParcelarNaoFiscal(CD_EMP		int
										,CD_FILIAL	int
										,CD_RECTO_TEF		int
										,QTDE_PARCELAS		int
										,DT_CUPOM	datetime
										,VL_CARTAO			money
										,VL_SAQUE_CARTAO	money 
										,QT_PARC_ADM	int
										,CD_ADM_CARTAO	int
										,TP_RATEIO		int
										,PERC_TAXA		money)


	CREATE TABLE #TblParcelasNaoFiscal(CD_EMP		int
								,CD_FILIAL	int
								,CD_RECTO_TEF		int
								,PARCELA	int
								,DT_VENCTO	datetime
								,VLR_PARCELA money
								,VLR_TAXA		Money)


/*****************************************************************************************************************************************************************************************/
-- Remove
/*****************************************************************************************************************************************************************************************/

		DELETE FROM P
		FROM 
			PDV_VD_TEF_PARC P
			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		
		where
				v.CD_FILIAL=@Filial
			AND V.DT_VD =@DATAMOV
			AND v.CD_EMP=1;


		DELETE FROM p
		FROM 
			PDV_CUPOM_NAO_FISCAL_TEF A
			INNER JOIN PDV_RECBTO_TEF B ON A.CD_EMP = B.CD_EMP
										AND A.CD_FILIAL = B.CD_FILIAL
									AND A.CD_RECTO_TEF = B.CD_RECTO_TEF
			INNER JOIN PDV_CUPOM_NAO_FISCAL n ON A.CD_EMP		= n.CD_EMP
												AND A.CD_FILIAL = n.CD_FILIAL
												AND A.CD_CTR	= n.CD_CTR
			JOIN RC_ADM_CARTAO C ON 
			B.CD_EMP	= 	C.CD_EMP	AND
			B.CD_ADM_CARTAO	= 	C.CD_ADM_CARTAO
			JOIN PDV_RECBTO_TEF_PARC p ON 
			b.CD_EMP	= 	P.CD_EMP	AND
			b.CD_FILIAL	= 	P.CD_FILIAL	AND
			b.CD_RECTO_TEF		= 	P.CD_RECTO_TEF 

		WHERE
			A.CD_EMP=1
			AND a.CD_FILIAL=@Filial
			AND n.DT_CUPOM =@DATAMOV


/*****************************************************************************************************************************************************************************************/
-- Parcela cupom fiscal
/*****************************************************************************************************************************************************************************************/

	Insert Into #TblParcelar					
	select  
		  t.CD_EMP
		, t.CD_FILIAL
		, t.CD_VD
		, t.CD_CTR
		, T.QTDE_PARCELAS PARC
		, T.DT_VD_ADM
		, ISNULL (T.VL_CARTAO ,0)		VL_CARTAO
		, ISNULL (T.VL_SAQUE_CARTAO,0)	VL_SAQUE_CARTAO
		, ISNULL (T.VL_TOTAL_DOACAO ,0)  VL_TOTAL_DOACAO
		, T.NSU_SITEF NSU_SITEF
		, T.NR_COMPROVANTE NR_COMPROVANTE
		, a.PARC QT_PARC_ADM
		, A.CD_ADM_CARTAO
		, A.TP_RATEIO
		, ISNULL(tax.PERC_TAXA,0) PERC_TAXA
	from 
		PDV_VD_TEF t
		JOIN 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 RC_ADM_CARTAO A ON 
		T.CD_EMP	= 	A.CD_EMP	AND
		T.CD_ADM_CARTAO	= 	A.CD_ADM_CARTAO

		LEFT JOIN V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS tax ON t.CD_EMP = TAX.CD_EMP
														 AND t.CD_FILIAL = TAX.CD_FILIAL
														AND t.CD_ADM_CARTAO = TAX.CD_ADM_CARTAO
	
		LEFT JOIN PDV_VD_TEF_PARC p ON 
		T.CD_EMP	= 	P.CD_EMP	AND
		T.CD_FILIAL	= 	P.CD_FILIAL	AND
		T.CD_VD		= 	P.CD_VD		AND
		T.CD_CTR	= 	P.CD_CTR	
	WHERE
		P.CD_EMP IS NULL
		AND T.QTDE_PARCELAS > 0
		AND T.CD_FILIAL=@Filial
		AND V.DT_VD =@DATAMOV
		AND T.CD_EMP=1;
		 
	DELETE from #TblParcelar WHERE VL_CARTAO=0;

	DECLARE @CD_EMP	INT,
		@CD_FILIAL	INT,
		@CD_VD		INT,
		@CD_CTR		INT,
		@CD_RECTO_TEF	INT,
		@QT_PARC	INT,
		@VLR_PARC	MONEY, 
		@PARCELA	INT,
		@DT_VENDA   DATE
		,@Taxa      money; 
		
	--RC_ADM_CARTAO_VENCTO
	--Insere a primeira parcela de todos de uma vez para evitar 1 loop
	INSERT INTO #TblParcelas 
	select 
		 CD_EMP	
		,CD_FILIAL
		,CD_VD	
		,CD_CTR	
		,1 Parcela
		,DATEADD(DAY,30, DT_VD_ADM	)   
		,case when TP_RATEIO=0 then round ((VL_CARTAO/PARC),2) - (VL_CARTAO - round ((VL_CARTAO/PARC),2) * PARC)
				else round ((VL_CARTAO/PARC),2) end  VlrParcela
		,case when TP_RATEIO=0 then round ((VL_CARTAO/PARC),2) - (VL_CARTAO - round ((VL_CARTAO/PARC),2) * PARC)
				else round ((VL_CARTAO/PARC),2) end  * (Perc_Taxa/100.00)
	from  #TblParcelar 
	 
	 --Insere a segunda parcela de todos de uma vez para evitar 1 loop
	INSERT INTO #TblParcelas 
	select 
		 CD_EMP	
		,CD_FILIAL
		,CD_VD	
		,CD_CTR	
		,2 Parcela
		,DATEADD(DAY,60, DT_VD_ADM	)  
		,round ((VL_CARTAO/PARC),2)  
		,round ((VL_CARTAO/PARC),2)  * (Perc_Taxa/100.00)
	from  #TblParcelar  

	declare c_Parcelamento CURSOR FAST_FORWARD  READ_ONLY FOR
	select 
		CD_EMP	
		,CD_FILIAL,CD_VD	
		,CD_CTR	
		,PARC
		,DT_VD_ADM
		,round ((VL_CARTAO/PARC),2) VLR_PARCELA   
		,(Perc_Taxa/100.00) Taxa
	from  #TblParcelar 
	where PARC > 2;

	OPEN c_Parcelamento
	FETCH NEXT FROM c_Parcelamento INTO @CD_EMP, @CD_FILIAL, @CD_VD, @CD_CTR, @QT_PARC,@DT_VENDA, @VLR_PARC,@TAXA   

	WHILE @@FETCH_STATUS = 0
	BEGIN
		 
		 SET @PARCELA =3 

		 WHILE @PARCELA <= @QT_PARC
		 BEGIN 
			INSERT INTO #TblParcelas 
			SELECT   @CD_EMP
					,@CD_FILIAL	
					,@CD_VD
					,@CD_CTR
					,@PARCELA
					,DATEADD(DAY, (30*@PARCELA), @DT_VENDA	) 
					,@VLR_PARC	
					,@VLR_PARC*@TAXA
			SET @PARCELA = @PARCELA+1;
			 
		 END

		FETCH NEXT FROM c_Parcelamento INTO @CD_EMP, @CD_FILIAL, @CD_VD, @CD_CTR, @QT_PARC,@DT_VENDA, @VLR_PARC,@TAXA  
	END
	  
	CLOSE c_Parcelamento;
	DEALLOCATE c_Parcelamento; 

	--Abate diferenca rateio das ultimas
	update P 
	set VLR_PARCELA= VLR_PARCELA - (VL_CARTAO - round ((VL_CARTAO/PARC),2) * PARC) 
	FROM 
		#TblParcelas P
		join #TblParcelar 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		
		AND p.PARCELA	=t.PARC 
	where
		T.CD_EMP	=1
		AND T.TP_RATEIO=1;
		 
		 --GLB_FERIADO

	INSERT INTO PDV_VD_TEF_PARC (CD_EMP,CD_FILIAL,CD_VD,CD_CTR,PARC,DT_VENCTO
									,VLR_TX,VLR_PARC_CARTAO,VLR_PARC_SAQUE_CARTAO,VLR_PARC_TOTAL_DOACAO
									,NSU_SITEF,NR_COMPROVANTE,ID_TRANSACAO,VLR_LIQ_LJ,DT_PREV_PAG_LJ )

	SELECT 
		A.CD_EMP
		,A.CD_FILIAL
		,A.CD_VD
		,A.CD_CTR
		,A.PARCELA
		,A.DT_VENCTO
		,A.VLR_TAXA
		,A.VLR_PARCELA VLR_PARC_CARTAO
		,0 VLR_PARC_SAQUE_CARTAO
		,0 VLR_PARC_TOTAL_DOACAO
		,T.NSU_SITEF
		,T.NR_COMPROVANTE
		,NULL ID_TRANSACAO
		,NULL VLR_LIQ_LJ
		,NULL DT_PREV_PAG_LJ 
	FROM   #TblParcelas a
			join #TblParcelar t on 
			A.CD_EMP	=t.CD_EMP		
		AND A.CD_FILIAL =t.CD_FILIAL
		AND A.CD_VD		=t.CD_VD			
		AND A.CD_CTR	=t.CD_CTR 

/*****************************************************************************************************************************************************************************************/
-- Parcela cupom nao fiscal
/*****************************************************************************************************************************************************************************************/
	
	
	Insert Into #TblParcelarNaoFiscal	 
		SELECT 
			  B.CD_EMP
			, B.CD_FILIAL
			, B.CD_RECTO_TEF
			, B.QTDE_PARCELAS  
			, n.DT_CUPOM  
			, B.VL_CARTAO
			, B.VL_SAQUE_CARTAO
			, C.PARC QT_PARC_ADM
			, C.CD_ADM_CARTAO
			, C.TP_RATEIO 
			, TAX.PERC_TAXA
		FROM 
			PDV_CUPOM_NAO_FISCAL_TEF A
			INNER JOIN PDV_RECBTO_TEF B ON A.CD_EMP = B.CD_EMP
										AND A.CD_FILIAL = B.CD_FILIAL
                                    AND A.CD_RECTO_TEF = B.CD_RECTO_TEF
			INNER JOIN PDV_CUPOM_NAO_FISCAL n ON A.CD_EMP		= n.CD_EMP
												AND A.CD_FILIAL = n.CD_FILIAL
												AND A.CD_CTR	= n.CD_CTR
			JOIN RC_ADM_CARTAO C ON 
			B.CD_EMP	= 	C.CD_EMP	AND
			B.CD_ADM_CARTAO	= 	C.CD_ADM_CARTAO
			
			LEFT JOIN V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS tax ON B.CD_EMP = TAX.CD_EMP
														 AND B.CD_FILIAL = TAX.CD_FILIAL
														AND B.CD_ADM_CARTAO = TAX.CD_ADM_CARTAO

			LEFT JOIN PDV_RECBTO_TEF_PARC p ON 
			b.CD_EMP	= 	P.CD_EMP	AND
			b.CD_FILIAL	= 	P.CD_FILIAL	AND
			b.CD_RECTO_TEF		= 	P.CD_RECTO_TEF 

		WHERE
			P.CD_EMP IS NULL
			AND b.QTDE_PARCELAS > 0
			AND a.CD_FILIAL=@Filial
			AND n.DT_CUPOM =@DATAMOV
			AND b.CD_EMP=1;


		DELETE from #TblParcelarNaoFiscal WHERE VL_CARTAO=0;
	   
	--Insere a primeira parcela de todos de uma vez para evitar 1 loop
	INSERT INTO #TblParcelasNaoFiscal 
	select 
		 CD_EMP	
		,CD_FILIAL
		,CD_RECTO_TEF	
		,1 Parcela
		,DATEADD(DAY,30, DT_CUPOM )   
		,case when TP_RATEIO=0 then round ((VL_CARTAO/QTDE_PARCELAS),2) - (VL_CARTAO - round ((VL_CARTAO/QTDE_PARCELAS),2) * QTDE_PARCELAS)
				else round ((VL_CARTAO/QTDE_PARCELAS),2) end  VlrParcela
		,case when TP_RATEIO=0 then round ((VL_CARTAO/QTDE_PARCELAS),2) - (VL_CARTAO - round ((VL_CARTAO/QTDE_PARCELAS),2) * QTDE_PARCELAS)
				else round ((VL_CARTAO/QTDE_PARCELAS),2) end * (Perc_taxa /100.000)
	from  #TblParcelarNaoFiscal 
	 
	 --Insere a segunda parcela de todos de uma vez para evitar 1 loop
	INSERT INTO #TblParcelasNaoFiscal 
	select 
		 CD_EMP	
		,CD_FILIAL 
		,CD_RECTO_TEF	
		,2 Parcela
		,DATEADD(DAY,60, DT_CUPOM)   
		,round ((VL_CARTAO/QTDE_PARCELAS),2) 
		,round ((VL_CARTAO/QTDE_PARCELAS),2)  *(Perc_taxa /100.000)
	from  #TblParcelarNaoFiscal  

	declare c_ParcNaoFiscal CURSOR FAST_FORWARD  READ_ONLY FOR
	SELECT 
		CD_EMP	
		,CD_FILIAL 	
		,CD_RECTO_TEF	
		,QTDE_PARCELAS
		,DT_CUPOM
		,ROUND ((VL_CARTAO/QTDE_PARCELAS),2) VLR_PARCELA   
		,(Perc_Taxa/100.00) Taxa
	FROM  #tblparcelarnaofiscal 
	WHERE QTDE_PARCELAS > 2;
	 
	
	OPEN c_ParcNaoFiscal
	FETCH NEXT FROM c_ParcNaoFiscal INTO @CD_EMP, @CD_FILIAL,  @CD_RECTO_TEF, @QT_PARC,@DT_VENDA, @VLR_PARC ,@TAXA  

	WHILE @@FETCH_STATUS = 0
	BEGIN
		 
		 SET @PARCELA =3 

		 WHILE @PARCELA <= @QT_PARC
		 BEGIN 
			INSERT INTO #TblParcelas 
			SELECT   @CD_EMP
					,@CD_FILIAL	 
					,@CD_RECTO_TEF
					,@PARCELA
					,DATEADD(DAY, (30*@PARCELA), @DT_VENDA	) 
					,@VLR_PARC	
					,@VLR_PARC*@TAXA
			SET @PARCELA = @PARCELA+1;
			 
		 END

		FETCH NEXT FROM c_ParcNaoFiscal INTO @CD_EMP, @CD_FILIAL,  @CD_RECTO_TEF, @QT_PARC,@DT_VENDA, @VLR_PARC  ,@TAXA 
	END
	  
	CLOSE c_ParcNaoFiscal;
	DEALLOCATE c_ParcNaoFiscal; 

	--Abate diferenca rateio das ultimas
	update P 
	set VLR_PARCELA= VLR_PARCELA - (VL_CARTAO - round ((VL_CARTAO/QTDE_PARCELAS),2) * QTDE_PARCELAS) 
	FROM 
		#TblParcelasnaofiscal P
		join #tblparcelarnaofiscal t on 
			p.CD_EMP	=t.CD_EMP		
		AND p.CD_FILIAL =t.CD_FILIAL 	
		AND p.CD_RECTO_TEF	=t.CD_RECTO_TEF		
		AND p.PARCELA		=t.QTDE_PARCELAS 
	where
		T.CD_EMP	=1
		AND T.TP_RATEIO=1;
		   
	insert into PDV_RECBTO_TEF_PARC (CD_EMP, CD_FILIAL, CD_RECTO_TEF, PARCELA, 
										DT_VENCTO, VLR_PARC_CARTAO, VLR_PARC_SAQUE_CARTAO)
	select CD_EMP, CD_FILIAL, CD_RECTO_TEF, PARCELA, 
										DT_VENCTO, VLR_PARCELA, 0 VLR_PARC_SAQUE_CARTAO
	from #TblParcelasNaoFiscal;
/*****************************************************************************************************************************************************************************************/

	Select 1 Sucesso	;