IF OBJECT_ID('UFD_RELA_PREVISAO_CNTS_RECEBER') IS NOT NULL
	BEGIN
		DROP FUNCTION UFD_RELA_PREVISAO_CNTS_RECEBER
	END
GO

CREATE FUNCTION  UFD_RELA_PREVISAO_CNTS_RECEBER (@CD_EMP INT,
												 @CD_FILIAL VARCHAR(2500),
												 @DT_INI DATETIME,
												 @DT_FIM DATETIME)

RETURNS @RS_RETORNO TABLE(	CD_FILIAL				INT,
							DT_MOV					DATETIME,	
							VLR_DINH				MONEY ,
							VLR_CHQ_VISTA			MONEY ,
							VLR_CHQ_PRAZO			MONEY ,
							VLR_CARTAO_VISTA		MONEY ,
							VLR_CARTAO_PRAZO		MONEY ,
							VLR_DUP					MONEY ,
							VLR_CONVENIO			MONEY ,
							VLR_DUP_GRUPO_ECO		MONEY)
AS
BEGIN

DECLARE @DATA	DATETIME;

/*******************************************************************************************************************/
/*										T A B E L A S  T E M P O R A R I A S									   */		
/*******************************************************************************************************************/

DECLARE @TBL_TEMP_BASE TABLE
							(
								CD_FILIAL		INT,
								DT_MOV			DATETIME 
							)


DECLARE @TBL_TEMP_VISTA TABLE
							(
								CD_FILIAL		INT,
								DT_MOV			DATETIME ,
								VLR_DINH		MONEY
							)

DECLARE @TBL_TEMP_CHQ_VISTA TABLE
							(
								CD_FILIAL		INT,
								DT_MOV			DATETIME ,
								VLR_CHQ_VISTA   MONEY
							)

DECLARE @TBL_TEMP_CHQ_PRAZO TABLE
							(
								CD_FILIAL		INT,
								DT_MOV			DATETIME ,
								VLR_CHQ_PRAZO   MONEY
							)

DECLARE @TBL_TEMP_CARTAO_VISTA TABLE
							(
								CD_FILIAL			INT,
								DT_MOV				DATETIME ,
								VLR_CARTAO_VISTA	MONEY
							)


DECLARE @TBL_TEMP_CARTAO_PRAZO TABLE
							(
								CD_FILIAL			INT,
								DT_MOV				DATETIME ,
								VLR_CARTAO			MONEY
							)



DECLARE @TBL_TEMP_DUPLICATA TABLE
							(
								CD_FILIAL			INT,
								CD_RC_DEB			INT,
								NR_DP				INT,
								DS_CONV				VARCHAR(2500),
								DT_MOV				DATETIME,
								VLR_DUP				MONEY,
								VLR_CONVENIO		MONEY,
								VLR_DUP_GRUPO_ECO   MONEY
							)


DECLARE @TBL_TEMP_FERIADO TABLE
							(
									DATA		DATETIME,
									DIAS		INT
							)


/*******************************************************************************************************************/
/*												F E R I A D O S													   */		
/*******************************************************************************************************************/

	INSERT INTO @TBL_TEMP_FERIADO
	SELECT 
		DT,
		CASE DATEPART(WEEKDAY,DT) WHEN 1 THEN
			1
		WHEN 2 THEN
			1
		WHEN 3 THEN
			1
		WHEN 4 THEN
			1
		WHEN 5 THEN
			1
		WHEN 6 THEN
			3
		WHEN 7 THEN
			2
		END AS DIAS_ADD

	FROM 
		GLB_FERIADO


/*******************************************************************************************************************/
/*										B A S E  T E M P O R A R I A											   */		
/*******************************************************************************************************************/

SET @DATA =@DT_INI;

	WHILE @DATA <= @DT_FIM
		BEGIN
			INSERT INTO @TBL_TEMP_BASE
			SELECT 
				CD_FILIAL,
				@DATA
			FROM 
				PRC_FILIAL
			WHERE
				CD_EMP=@CD_EMP
				AND CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL)) 
				
			SET @DATA = DATEADD(DAY,1,@DATA)
		END

/*******************************************************************************************************************/
/*										D I N H E I R O  A  V I S T A											   */		
/*******************************************************************************************************************/

SET @DATA =@DT_INI;

	WHILE @DATA <= @DT_FIM
		BEGIN
			INSERT INTO @TBL_TEMP_VISTA
			SELECT 
				CD_FILIAL,
				@DATA,
				SUM(VLR_DINH/2.0) VLR_DINH
			FROM 
				PDV_VD_FECHA_MOV
			WHERE
				CD_EMP=@CD_EMP
				AND CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL)) 
				AND DT_MOV BETWEEN DATEADD(DAY,-1,@DATA) AND @DATA
			GROUP BY
				CD_FILIAL

			SET @DATA = DATEADD(DAY,1,@DATA)
		END

/*******************************************************************************************************************/
/*										C H E Q U E  A  V I S T A												   */		
/*******************************************************************************************************************/

SET @DATA =@DT_INI;

	WHILE @DATA <= @DT_FIM
		BEGIN
			INSERT INTO @TBL_TEMP_CHQ_VISTA
			SELECT 
				CD_FILIAL,
				@DATA,
				VLR_CHQ_VISTA VLR_CHQ_VISTA
			FROM 
				PDV_VD_FECHA_MOV
			WHERE
				CD_EMP=@CD_EMP
				AND CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL)) 
				AND DT_MOV = DATEADD(DAY,-3,@DATA) 
		
			SET @DATA = DATEADD(DAY,1,@DATA)
		END

/*******************************************************************************************************************/
/*										C H E Q U E   A  P R A Z O												   */
/*******************************************************************************************************************/


	INSERT INTO @TBL_TEMP_CHQ_PRAZO
	SELECT 
		CD_FILIAL,
		DT_VENCTO,
		SUM(VL_CHQ) VLR_CHQS_PRAZO
	FROM 
		PDV_VD_CHQS
	WHERE
		CD_EMP=@CD_EMP
		AND CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL)) 
		AND DT_EMI <> DT_VENCTO 
		AND DT_EMI  < = @DT_INI
		AND DT_VENCTO BETWEEN @DT_INI AND @DT_FIM

	GROUP BY 
		CD_FILIAL,
		DT_VENCTO



/*******************************************************************************************************************/
/*										C A R T A O  A  V I S T A												   */		
/*******************************************************************************************************************/


SET @DATA =@DT_INI;

	WHILE @DATA <= @DT_FIM
		BEGIN
			INSERT INTO @TBL_TEMP_CARTAO_VISTA
			SELECT 
				V.CD_FILIAL,
				@DATA,
				SUM((V.VLR_CARTAO - (V.VLR_CARTAO * R.TX_ADM / 100.0)))  VLR_LIQ_CARTAO
			FROM 
				PDV_VD V
				INNER JOIN PDV_VD_TEF C ON
				V.CD_EMP = C.CD_EMP AND
				V.CD_FILIAL = C.CD_FILIAL AND
				V.CD_VD = C.CD_VD
				INNER JOIN RC_ADM_CARTAO R ON
				R.CD_EMP = C.CD_EMP AND
				R.CD_ADM_CARTAO = C.CD_ADM_CARTAO
			WHERE 
				V.CD_EMP=@CD_EMP
				AND V.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL)) 
				AND V.DT_VD = DATEADD(DAY,-1,@DATA) 
				AND C.CD_ADM_CARTAO > 0
				AND V.ST_VD = 0
				AND R.TP_ADM IN (1,3)
				AND NOT EXISTS (SELECT CD_FILIAL FROM PDV_VD_CANCEL_TEF			
									WHERE 
										V.CD_VD = PDV_VD_CANCEL_TEF.CD_VD AND 
										V.CD_FILIAL = PDV_VD_CANCEL_TEF.CD_FILIAL AND 
										V.CD_EMP = PDV_VD_CANCEL_TEF.CD_EMP)
			GROUP BY
				V.CD_FILIAL
			SET @DATA = DATEADD(DAY,1,@DATA)
		END



/*******************************************************************************************************************/
/*										C A R T A O  A  P R A Z O 												   */		
/*******************************************************************************************************************/
SET @DATA =@DT_INI;

	WHILE @DATA <= @DT_FIM
		BEGIN

			INSERT INTO @TBL_TEMP_CARTAO_PRAZO
			SELECT 
				V.CD_FILIAL,
				@DATA,
				SUM(((V.VLR_CARTAO/R.PARC) - ((V.VLR_CARTAO/R.PARC) * R.TX_ADM / 100.0) ))  VLR_LIQ_CARTAO
			FROM 
				PDV_VD V
				INNER JOIN PDV_VD_TEF C ON
				V.CD_EMP = C.CD_EMP AND
				V.CD_FILIAL = C.CD_FILIAL AND
				V.CD_VD = C.CD_VD
				INNER JOIN RC_ADM_CARTAO R ON
				R.CD_EMP = C.CD_EMP AND
				R.CD_ADM_CARTAO = C.CD_ADM_CARTAO
			WHERE 
				V.CD_EMP=@CD_EMP
				AND V.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL)) 
				AND V.DT_VD = DATEADD(MONTH,-R.PARC,@DATA)
				AND C.CD_ADM_CARTAO > 0
				AND V.ST_VD = 0
				AND R.TP_ADM IN (0,2)
				AND NOT EXISTS (SELECT CD_FILIAL FROM PDV_VD_CANCEL_TEF			
									WHERE 
										V.CD_VD = PDV_VD_CANCEL_TEF.CD_VD AND 
										V.CD_FILIAL = PDV_VD_CANCEL_TEF.CD_FILIAL AND 
										V.CD_EMP = PDV_VD_CANCEL_TEF.CD_EMP)
			GROUP BY
				V.CD_FILIAL
			SET @DATA = DATEADD(DAY,1,@DATA)
		END



/*******************************************************************************************************************/
/*										D U P L I C A T A S		 												   */		
/*******************************************************************************************************************/

SET @DATA =@DT_INI;

	WHILE @DATA <= @DT_FIM
		BEGIN
			INSERT INTO @TBL_TEMP_DUPLICATA					
			SELECT DISTINCT
				D.CD_FILIAL,
				D.CD_RC_DEB,	
				D.NR_DP,
				CASE WHEN C.CD_CLI IS NULL THEN
					CLI.RZ_CLI
				ELSE
					CASE WHEN ISNULL(cv.CD_CONV,0) > 0  THEN 
						C.DS_CONV
					ELSE
						CLI.RZ_CLI
					END
				END AS CLI,
				@DATA,
				CASE WHEN C.CD_CLI IS NULL THEN
					0
				ELSE 
					D.VLR_DP
				END AS VLR_DP,
				CASE WHEN C.CD_CLI IS NULL AND ISNULL(cv.CD_CONV,0) > 0 THEN
					D.VLR_DP - ISNULL(i.VLR_IMP,0)
				ELSE 
					0
				END AS VLR_CONV,				
				CASE WHEN f.CD_CLI > 0 AND ISNULL(cv.CD_CONV,0) = 0 THEN
					D.VLR_DP - ISNULL(i.VLR_IMP,0)
				ELSE 
					0
				END AS VLR_DUP_GRUPO_ECO
			FROM 
				RC_DEB D
				INNER JOIN RC_CLI CLI ON
				D.CD_EMP = CLI.CD_EMP AND
				D.CD_CLI = CLI.CD_CLI 
				LEFT JOIN PRC_FILIAL_RC_CLI F ON
				D.CD_EMP = F.CD_EMP AND
				D.CD_CLI = F.CD_CLI 
				LEFT JOIN RC_CLI_CONV C ON
				D.CD_EMP = C.CD_EMP AND
				D.CD_CLI = C.CD_CLI 
				LEFT OUTER JOIN V_RC_DEB_IMPOSTO  i ON 
				D.CD_EMP = i.CD_EMP AND 
				D.CD_FILIAL = i.CD_FILIAL AND 
				D.CD_RC_DEB = i.CD_RC_DEB 						
				LEFT OUTER JOIN RC_DEB_RC_CLI_CONV  cv ON 
				D.CD_EMP = cv.CD_EMP AND 
				D.CD_FILIAL = cv.CD_FILIAL AND 
				D.CD_RC_DEB = cv.CD_RC_DEB 		
			WHERE
				D.CD_EMP=@CD_EMP
				AND D.CD_FILIAL  IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
				AND DT_VENCTO = @DATA;

			SET @DATA = DATEADD(DAY,1,@DATA)
		END


/*******************************************************************************************************************/
/*******************************************************************************************************************/
/*******************************************************************************************************************/

	INSERT INTO @RS_RETORNO
	SELECT 
		B.CD_FILIAL,
		B.DT_MOV,	
		ISNULL(SUM (D.VLR_DINH),0) VLR_DINH,
		ISNULL(SUM (C.VLR_CHQ_VISTA),0) VLR_CHQ_VISTA,
		ISNULL(SUM (CP.VLR_CHQ_PRAZO),0) VLR_CHQ_PRAZO,
		ISNULL(SUM (CAV.VLR_CARTAO_VISTA),0) VLR_CARTAO_VISTA,
		ISNULL(SUM (CAP.VLR_CARTAO),0) VLR_CARTAO_PRAZO,
		ISNULL(SUM (DUP.VLR_DUP),0) VLR_DUP,
		ISNULL(SUM (DUP.VLR_CONVENIO),0) VLR_CONVENIO,
		ISNULL(SUM (DUP.VLR_DUP_GRUPO_ECO),0) VLR_DUP_GRUPO_ECO
	FROM 
		
		@TBL_TEMP_BASE B
		LEFT JOIN @TBL_TEMP_VISTA D ON
		B.CD_FILIAL = D.CD_FILIAL  AND 
		B.DT_MOV = D.DT_MOV 
		LEFT JOIN @TBL_TEMP_CHQ_VISTA C ON
		B.CD_FILIAL = C.CD_FILIAL  AND 
		B.DT_MOV = C.DT_MOV 
		LEFT JOIN @TBL_TEMP_CHQ_PRAZO CP ON
		B.CD_FILIAL = CP.CD_FILIAL  AND 
		B.DT_MOV = CP.DT_MOV 
		LEFT JOIN @TBL_TEMP_CARTAO_VISTA CAV ON
		B.CD_FILIAL = CAV.CD_FILIAL  AND 
		B.DT_MOV = CAV.DT_MOV 
		LEFT JOIN @TBL_TEMP_CARTAO_PRAZO CAP ON
		B.CD_FILIAL = CAP.CD_FILIAL  AND 
		B.DT_MOV = CAP.DT_MOV 
		LEFT JOIN @TBL_TEMP_DUPLICATA DUP ON
		B.CD_FILIAL = DUP.CD_FILIAL  AND 
		B.DT_MOV = DUP.DT_MOV 

	GROUP BY 
		B.CD_FILIAL,
		B.DT_MOV;	
RETURN
END

