IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = 'UFD_RC_ADM_CARTAO_VENDA_PARC')
	DROP FUNCTION UFD_RC_ADM_CARTAO_VENDA_PARC
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

GO

--SELECT * FROM [UFD_RC_ADM_CARTAO_VENDA_PARC](1,'3','3','2011-08-07', '2011-08-07')
CREATE FUNCTION [dbo].[UFD_RC_ADM_CARTAO_VENDA_PARC](	@CD_EMP 		INT
															, @CD_FILIAL   	VARCHAR(8000)
															, @CD_CLI		VARCHAR(8000)
															, @DT_INI 		DATETIME
															, @DT_FIM		DATETIME
															, @tpData		tinyint
															, @StsMov		tinyint
															, @IncluiDebito  tinyint)
RETURNS  @TblReturn    table   (CD_VD				INT
							, CD_CTR		INT
							, CD_FILIAL		INT
							, CD_CLI		INT
							, RZ_CLI		VARCHAR(255)
							, DT_VD_ADM		DATETIME
							, VLR_CARTAO	MONEY
							, TX_ADM		MONEY
							, QTDE_DIAS_VENCTO INT 
							, DT_VENCTO			DATETIME
							, VLR_RECBTO_BRUTO	MONEY
							, VLR_TAXA_ADM		MONEY
							, VLR_RECBTO_LIQUIDO MONEY
							, STS_FECHA TINYINT
							, PARC			TINYINT
							, QTDE_PARCELAS TINYINT
							, TP_CUPOM		TINYINT
							, TP_CARTAO		TINYINT)
AS
    BEGIN
	
	INSERT INTO @TblReturn    
	SELECT 
		   PDV_VD_TEF.CD_VD	
		 , PDV_VD_TEF.CD_CTR	  
		 , PDV_VD_TEF.CD_FILIAL
		 , RC_CLI.CD_CLI
		 , RC_CLI.RZ_CLI
		 , PDV_VD_TEF.DT_VD_ADM
		 , PARC.VLR_PARC_CARTAO AS VLR_CARTAO
		 , ISNULL(tax.PERC_TAXA, -1) AS TX_ADM
		 , DATEDIFF(DAY,  PDV_VD_TEF.DT_VD_ADM , PARC.DT_VENCTO )AS QTDE_DIAS_VENCTO
		 , PARC.DT_VENCTO AS DT_VENCTO
		 , PARC.VLR_PARC_CARTAO  AS VLR_RECBTO_BRUTO 
		 , PARC.VLR_PARC_CARTAO * (ISNULL(TAX.PERC_TAXA,0)/ 100) AS VLR_TAXA_ADM 
		 , PARC.VLR_PARC_CARTAO-PARC.VLR_TX AS VLR_RECBTO_LIQUIDO
		 , CASE WHEN RC_ADM_CARTAO_MOV_LOTE_PDV_VD.CD_VD IS NULL THEN 0 ELSE 1 END STS_FECHA
		 , PARC.PARC
		 , PDV_VD_TEF.QTDE_PARCELAS
		 , 0 TP_CUPOM
		 , CASE WHEN RC_ADM_CARTAO.TP_ADM IN(0, 2, 4) THEN 0 -- Credito
				WHEN RC_ADM_CARTAO.TP_ADM IN(1, 3)  THEN 1 else 2 end TP_CARTAO
	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 PDV_VD_TEF_PARC PARC ON PDV_VD_TEF.CD_EMP = PARC.CD_EMP
											AND PDV_VD_TEF.CD_FILIAL = PARC.CD_FILIAL
											AND PDV_VD_TEF.CD_VD = PARC.CD_VD
											AND PDV_VD_TEF.CD_CTR = PARC.CD_CTR
		 INNER JOIN RC_ADM_CARTAO_RC_CLI ON PDV_VD_TEF.CD_EMP = RC_ADM_CARTAO_RC_CLI.CD_EMP
											AND PDV_VD_TEF.CD_ADM_CARTAO = RC_ADM_CARTAO_RC_CLI.CD_ADM_CARTAO
		 INNER JOIN RC_ADM_CARTAO ON RC_ADM_CARTAO_RC_CLI.CD_EMP = RC_ADM_CARTAO.CD_EMP
									 AND RC_ADM_CARTAO_RC_CLI.CD_ADM_CARTAO = RC_ADM_CARTAO.CD_ADM_CARTAO
		 INNER JOIN RC_CLI ON RC_ADM_CARTAO_RC_CLI.CD_EMP = RC_CLI.CD_EMP
							  AND RC_ADM_CARTAO_RC_CLI.CD_CLI = RC_CLI.CD_CLI

		 LEFT JOIN V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS tax ON PDV_VD_TEF.CD_EMP = TAX.CD_EMP
														 AND PDV_VD_TEF.CD_FILIAL = TAX.CD_FILIAL
														 AND PDV_VD_TEF.CD_ADM_CARTAO = TAX.CD_ADM_CARTAO 

		 LEFT JOIN RC_ADM_CARTAO_MOV_LOTE_PDV_VD ON PDV_VD_TEF.CD_EMP		 = RC_ADM_CARTAO_MOV_LOTE_PDV_VD.CD_EMP
													AND PDV_VD_TEF.CD_FILIAL= RC_ADM_CARTAO_MOV_LOTE_PDV_VD.CD_FILIAL
													AND PDV_VD_TEF.CD_VD	= RC_ADM_CARTAO_MOV_LOTE_PDV_VD.CD_VD
													AND PDV_VD_TEF.CD_CTR	= RC_ADM_CARTAO_MOV_LOTE_PDV_VD.CD_CTR

	WHERE 
		PDV_VD_TEF.CD_EMP = @CD_EMP
		AND PDV_VD_TEF.CD_FILIAL IN(SELECT ID FROM  UFD_PRM_IN_STRING(@CD_FILIAL))
		AND RC_ADM_CARTAO_RC_CLI.CD_CLI IN ( SELECT  ID FROM  UFD_PRM_IN_STRING(@CD_CLI))
		AND case when @tpData =0 then PDV_VD_TEF.DT_VD_ADM 
				else parc.DT_VENCTO end BETWEEN @DT_INI AND @DT_FIM
		AND PDV_VD_TEF.VL_CARTAO > 0
		AND PDV_VD.ST_VD = 0
		AND ISNULL(PDV_VD.CANCEL_TEF, 0) <> 1 
		AND NOT EXISTS (SELECT  * FROM  PDV_VD_CANCEL_TEF
							WHERE PDV_VD_TEF.CD_EMP = PDV_VD_CANCEL_TEF.CD_EMP
								AND PDV_VD_TEF.CD_FILIAL = PDV_VD_CANCEL_TEF.CD_FILIAL
								AND PDV_VD_TEF.CD_VD = PDV_VD_CANCEL_TEF.CD_VD)
		and CASE WHEN RC_ADM_CARTAO_MOV_LOTE_PDV_VD.CD_VD IS NULL THEN 0 ELSE 1 END =@StsMov 
		AND CASE WHEN @INCLUIDEBITO = 0 THEN RC_ADM_CARTAO.TP_ADM  ELSE 0 END IN(0, 2, 4);

INSERT INTO @TblReturn    
	SELECT 
		  TEF.CD_CTR	
		, TEF.CD_RECTO_TEF	  
		, TEF.CD_FILIAL
		, TEF.CD_CLI
		, TEF.RZ_CLI
		, TEF.DT_CUPOM
		, TEF.VLR_CARTAO
		, ISNULL(TAX.PERC_TAXA, -1)
		, DATEDIFF(DAY, TEF.DT_CUPOM, PARC.DT_VENCTO) AS QTDE_DIAS_VENCTO
		, PARC.DT_VENCTO AS DT_VENCTO
		,  (PARC.VLR_PARC_CARTAO) AS VLR_RECBTO_BRUTO
		,  (PARC.VLR_PARC_CARTAO) * (ISNULL(TAX.PERC_TAXA, 0) / 100 ) AS VLR_TAXA_ADM
		,  (PARC.VLR_PARC_CARTAO) - ( (PARC.VLR_PARC_CARTAO) * (ISNULL(TAX.PERC_TAXA, 0) / 100 ))  AS VLR_RECBTO_LIQUIDO
		, CASE WHEN RC_ADM_CARTAO_MOV_LOTE_PDV_RECBTO_TEF.CD_RECTO_TEF IS NULL THEN 0 ELSE 1 END STS_FECHA
		, PARC.PARCELA
		, TEF.QT_PARC
		, 1 TP_CUPOM
		, CASE WHEN TEF.TP_ADM IN(0, 2, 4) THEN 0 -- Credito
				WHEN TEF.TP_ADM IN(1, 3)  THEN 1 else 2 end TP_CARTAO
	FROM 
		V_CUPOMNAOFISCAL_TEF TEF
		LEFT JOIN V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS TAX ON TEF.CD_EMP = TAX.CD_EMP
		AND TEF.CD_FILIAL	= TAX.CD_FILIAL
		AND TEF.CD_CONV		= TAX.CD_ADM_CARTAO
		INNER JOIN PDV_RECBTO_TEF_PARC PARC ON TEF.CD_EMP = PARC.CD_EMP
		AND TEF.CD_FILIAL = PARC.CD_FILIAL
		AND TEF.CD_RECTO_TEF = PARC.CD_RECTO_TEF
		LEFT JOIN RC_ADM_CARTAO_MOV_LOTE_PDV_RECBTO_TEF ON 
			TEF.CD_EMP		 = RC_ADM_CARTAO_MOV_LOTE_PDV_RECBTO_TEF.CD_EMP
		AND TEF.CD_FILIAL	 = RC_ADM_CARTAO_MOV_LOTE_PDV_RECBTO_TEF.CD_FILIAL
		AND TEF.CD_RECTO_TEF = RC_ADM_CARTAO_MOV_LOTE_PDV_RECBTO_TEF.CD_RECTO_TEF 
	WHERE 
		TEF.CD_EMP = @CD_EMP
		AND TEF.CD_FILIAL IN( SELECT ID FROM  UFD_PRM_IN_STRING(@CD_FILIAL) )
		AND TEF.CD_CLI IN (SELECT  ID FROM  UFD_PRM_IN_STRING(@CD_CLI))
		AND CASE WHEN @tpData = 0 THEN DT_CUPOM ELSE parc.DT_VENCTO END BETWEEN @DT_INI AND @DT_FIM
		AND TEF.VLR_CARTAO > 0
		AND TEF.ST_CUPOM = 0 
		AND CASE WHEN RC_ADM_CARTAO_MOV_LOTE_PDV_RECBTO_TEF.CD_RECTO_TEF IS NULL THEN 0 ELSE 1 END = @StsMov  
		AND CASE WHEN @INCLUIDEBITO = 0 THEN TEF.TP_ADM  ELSE 0 END IN(0, 2, 4);

		return
END

GO


