
/****** Object:  UserDefinedFunction [dbo].[UFD_RC_ADM_CARTAO_VENDA_CREDITO]    Script Date: 19/01/2016 15:42:50 ******/
DROP FUNCTION [dbo].[UFD_RC_ADM_CARTAO_VENDA_CREDITO]
GO

/****** Object:  UserDefinedFunction [dbo].[UFD_RC_ADM_CARTAO_VENDA_CREDITO]    Script Date: 19/01/2016 15:42:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--SELECT * FROM UFD_RC_ADM_CARTAO_VENDA_CREDITO(1,'3','3','2011-08-07', '2011-08-07')
CREATE FUNCTION [dbo].[UFD_RC_ADM_CARTAO_VENDA_CREDITO](
																@CD_EMP 		INT,
																@CD_FILIAL   	VARCHAR(3000),
																@CD_CLI			VARCHAR(3000),
																@DT_INI 		DATETIME,
																@DT_FIM			DATETIME)
RETURNS TABLE
AS
RETURN
SELECT     
	PDV_VD_TEF.CD_FILIAL,
	RC_CLI.CD_CLI, 
	RC_CLI.RZ_CLI, 
	PDV_VD_TEF.DT_VD_ADM,
	SUM(PDV_VD_TEF.VL_CARTAO) AS VLR_CARTAO, 
	ISNULL(V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.PERC_TAXA,-1) AS TX_ADM, 
	ISNULL(RC_ADM_CARTAO_VENCTO.QTDE_DIAS,0) AS QTDE_DIAS_VENCTO,
	DATEADD(day,ISNULL(RC_ADM_CARTAO_VENCTO.QTDE_DIAS,0),PDV_VD_TEF.DT_VD_ADM) AS DT_VENCTO,
	(SUM(PDV_VD_TEF.VL_CARTAO) / RC_ADM_CARTAO.PARC) AS VLR_RECBTO_BRUTO, 
	((SUM(PDV_VD_TEF.VL_CARTAO)/ RC_ADM_CARTAO.PARC) * ISNULL(V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.PERC_TAXA,-1)) / 100 AS VLR_TAXA_ADM, 
	(SUM(PDV_VD_TEF.VL_CARTAO)/ RC_ADM_CARTAO.PARC) - (((SUM(PDV_VD_TEF.VL_CARTAO)/ RC_ADM_CARTAO.PARC) * ISNULL(V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.PERC_TAXA,-1)) / 100 ) AS VLR_RECBTO_LIQUIDO,
	CASE WHEN RC_ADM_CARTAO_MOV_LOTE_PDV_VD.CD_VD IS NULL THEN 0 ELSE 1 END  STS_FECHA
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_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 ON
	PDV_VD_TEF.CD_EMP = V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.CD_EMP AND
	PDV_VD_TEF.CD_FILIAL = V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.CD_FILIAL AND
	PDV_VD_TEF.CD_ADM_CARTAO = V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.CD_ADM_CARTAO
	LEFT OUTER JOIN RC_ADM_CARTAO_VENCTO ON 
	RC_ADM_CARTAO.CD_EMP = RC_ADM_CARTAO_VENCTO.CD_EMP AND 
    RC_ADM_CARTAO.CD_ADM_CARTAO = RC_ADM_CARTAO_VENCTO.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    
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 PDV_VD_TEF.DT_VD_ADM 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 RC_ADM_CARTAO.TP_ADM IN (0,2,4)
	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)
GROUP BY 
	RC_CLI.CD_CLI, 
	RC_CLI.RZ_CLI, 
	V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.PERC_TAXA, 
	PDV_VD_TEF.DT_VD_ADM,
	RC_ADM_CARTAO_VENCTO.QTDE_DIAS,
	RC_ADM_CARTAO.PARC,
	PDV_VD_TEF.CD_FILIAL,
	RC_ADM_CARTAO_MOV_LOTE_PDV_VD.CD_VD

UNION ALL


SELECT  
	TEF.CD_FILIAL,
	TEF.CD_CLI,
	TEF.RZ_CLI,
	TEF.DT_CUPOM,
	TEF.VLR_CARTAO,
	ISNULL(TAX.PERC_TAXA,-1),
	ISNULL(RC_ADM_CARTAO_VENCTO.QTDE_DIAS,0) AS QTDE_DIAS_VENCTO,
	DATEADD(DAY,ISNULL(RC_ADM_CARTAO_VENCTO.QTDE_DIAS,0),TEF.DT_CUPOM) AS DT_VENCTO,
	(SUM(TEF.VLR_CARTAO) / TEF.PARC) AS VLR_RECBTO_BRUTO,
	((SUM(TEF.VLR_CARTAO)/ TEF.PARC) * ISNULL(TAX.PERC_TAXA,-1)) / 100 AS VLR_TAXA_ADM ,
	(SUM(TEF.VLR_CARTAO)/ TEF.PARC) - (((SUM(TEF.VLR_CARTAO)/ TEF.PARC) * ISNULL(TAX.PERC_TAXA,-1)) / 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

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

	LEFT OUTER JOIN RC_ADM_CARTAO_VENCTO ON 
	TEF.CD_EMP = RC_ADM_CARTAO_VENCTO.CD_EMP AND 
    TEF.CD_CONV = RC_ADM_CARTAO_VENCTO.CD_ADM_CARTAO

	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 DT_CUPOM BETWEEN @DT_INI AND @DT_FIM
	AND TEF.VLR_CARTAO > 0
	AND TEF.ST_CUPOM = 0 
	AND TEF.TP_ADM IN (0,2,4)

GROUP BY 
	TEF.CD_FILIAL,
	TEF.CD_CLI,
	TEF.RZ_CLI,
	TEF.DT_CUPOM,
	TEF.VLR_CARTAO,
	TAX.PERC_TAXA,
	RC_ADM_CARTAO_VENCTO.QTDE_DIAS,
	TEF.PARC,
	TEF.CD_CTR,
	RC_ADM_CARTAO_MOV_LOTE_PDV_RECBTO_TEF.CD_RECTO_TEF    


GO


