 
/****** Object:  UserDefinedFunction [dbo].[UFD_PDV_VD_RC_CLI]    Script Date: 01/21/2014 14:57:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_PDV_VD_RC_CLI]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_PDV_VD_RC_CLI]
GO 
GO
  
/****************************************************************************************************************
										M E L H O R E S  C L I E N T E S
*****************************************************************************************************************/


-- SELECT  * FROM UFD_PDV_VD_RC_CLI (1,'998','20130101','20130930',15 ,200 ,6)


CREATE  FUNCTION [dbo].[UFD_PDV_VD_RC_CLI] ( @CD_EMP			INT,
											 @FILIAL 		VARCHAR(500),
											 @DT_INI		DATETIME,
											 @DT_FIM		DATETIME,	
											 @FREQ			INT	,
											 @VLR_MIN		MONEY,
											 @LINHA			VARCHAR(500))
 

RETURNS		@RS_TEMP		TABLE (	CD_FILIAL			INT,
									CD_CLI				INT,
									NR_CARTAO			VARCHAR(50),
									NM_CLIENTE			VARCHAR(500),
									RZ_CLI				VARCHAR(500),
									END_CLI				VARCHAR(500),
									BAIRRO				VARCHAR(200),
									TELEFONE01			VARCHAR(50),
									TELEFONE02			VARCHAR(50),
									TELEFONE03			VARCHAR(50),
									TELEFONE04			VARCHAR(50),
									DT_VD       		DATETIME,
									NR_ECF              VARCHAR(10),
									CD_CX               INT ,
									DT_ULT_COMPRA		DATETIME,
									CD_PROD				INT,
									DS_PROD				VARCHAR(250),
									CD_LINHA			INT,
									DS_LINHA			VARCHAR(250),
									FORMA_PGTO			VARCHAR(50),
									VLR_VD				MONEY,
									VLR_LIQ_VD			MONEY,
									ITENS				MONEY,
									FREQ				MONEY,
									CD_MEDICO_REC       INT,
									NM_MEDICO           VARCHAR(250))

AS
BEGIN



/****************************************************************************************************************
										T A B E L A S   A U X I L I A R E S
*****************************************************************************************************************/
	DECLARE @RS_FILIAL AS TABLE(	CD_EMP			INT,
								CD_FILIAL		INT)
	INSERT INTO @RS_FILIAL 						
	SELECT 
		CD_EMP,
		CD_FILIAL
	FROM 
		PRC_FILIAL
	WHERE
		CD_EMP = @CD_EMP
		AND CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@FILIAL))
								
	DECLARE @RS_LINHAS AS TABLE (CD_EMP			INT,
								 CD_ARV_MERC_LINHA		INT,
								 DS_ARV_MERC_LINHA		VARCHAR(800))							
	
	INSERT INTO @RS_LINHAS 						
	SELECT 
		CD_EMP,
		CD_ARV_MERC_LINHA,
		DS_ARV_MERC_LINHA
	FROM 
		EST_ARV_MERC_LINHA
	WHERE
		CD_EMP = @CD_EMP									
		AND CD_ARV_MERC_LINHA IN (SELECT ID FROM UFD_PRM_IN_STRING(@LINHA))
						
	DECLARE @RS_FREQ AS TABLE(	CD_EMP			INT,
								CD_CLI			INT,
								FREQ			MONEY,
								DT_ULT_CP		DATETIME)

	DECLARE @RS_CLI AS TABLE(	CD_EMP				INT,
								CD_CLI				INT,
								NR_CARTAO			VARCHAR(50),
								NM_CLI				VARCHAR(500),
								RZ_CLI				VARCHAR(500),
								END_CLI				VARCHAR(500),
								BAIRRO				VARCHAR(100),
								TELEFONE01			VARCHAR(50),
								TELEFONE02			VARCHAR(50),
								TELEFONE03			VARCHAR(50),
								TELEFONE04			VARCHAR(50),
								DT_ULT_CP			DATETIME)	
	
	DECLARE @RS_VD AS TABLE(	CD_EMP				INT,	
								CD_FILIAL			INT,	
								CD_CLI				INT,
								CD_PROD				INT,
								DS_PROD				VARCHAR(250),
								CD_LINHA			INT,
								DS_LINHA			VARCHAR(250), 
								FORMA_PGTO			VARCHAR(50),
								VLR_VD				MONEY,
								VLR_LIQ_VD			MONEY,
								ITENS				MONEY,
								CD_MEDICO_REC       INT,
								NM_MEDICO           VARCHAR(250),
								DT_VD               DATETIME,
								NR_ECF              VARCHAR(10),
								CD_CX               INT)


/****************************************************************************************************************
								F R E Q U E N C I A   D E   C O M P R A
*****************************************************************************************************************/

	INSERT INTO @RS_FREQ
	SELECT 
		V.CD_EMP,
		V.CD_CLI, 
		COUNT(DISTINCT DT_VD) QTD,
		MAX(DT_VD) DT
	FROM 
		PDV_VD V
		INNER JOIN (SELECT 
							V.CD_EMP,
							V.CD_CLI,
							SUM(I.TOT_IT - I.VLR_DESC_TOT_RAT) TOT							
						FROM 
							PDV_VD V INNER JOIN PDV_VD_IT I ON
							V.CD_EMP = I.CD_EMP AND
							V.CD_FILIAL = I.CD_FILIAL AND
							V.CD_VD = I.CD_VD and
							v.DT_VD = I.DT_VD
							INNER JOIN @RS_FILIAL F ON 
							V.CD_EMP = F.CD_EMP AND
							V.CD_FILIAL = F.CD_FILIAL 
						WHERE 
						    V.CD_EMP = @CD_EMP
						    AND V.DT_VD BETWEEN @DT_INI AND @DT_FIM						 
							AND V.ST_VD = 0
							AND I.ST_IT = 1
						GROUP BY 
							V.CD_EMP , V.CD_CLI	
						HAVING
							SUM(I.TOT_IT - I.VLR_DESC_TOT_RAT) >= @VLR_MIN

						) M ON
		V.CD_CLI = M.CD_CLI AND 
		V.CD_EMP  = M.CD_EMP 
		INNER JOIN @RS_FILIAL F ON 
		V.CD_EMP = F.CD_EMP AND
		V.CD_FILIAL = F.CD_FILIAL 
	WHERE
		V.CD_EMP = @CD_EMP
 		AND V.DT_VD BETWEEN @DT_INI AND @DT_FIM
		AND V.CD_CLI NOT IN (SELECT CD_CLI FROM  RC_CLI WHERE CD_EMP = @CD_EMP AND CD_CLI > 0 AND CONS_FINAL =1)
		AND V.ST_VD = 0
		 	
	GROUP BY
		V.CD_EMP,
		V.CD_CLI
	HAVING 
		COUNT(DISTINCT DT_VD) +0 >= @FREQ
	 

/****************************************************************************************************************
									D A D O S  D O  C L I E N T E
*****************************************************************************************************************/

	INSERT INTO @RS_CLI
	SELECT DISTINCT
		C.CD_EMP,
		C.CD_CLI,
		M.NR_CARTAO,
		C.NM_FANT,
		C.RZ_CLI,
		C.END_CLI,
		C.BAIRRO,
		C.TEL TELEFONE01,
		C.TELEFONE1 TELEFONE02,
		C.TELEFONE2 TELEFONE03,
		C.TELEFONE3 TELEFONE04,	
		DT_ULT_CP
	FROM

		RC_CLI C
		INNER JOIN @RS_FREQ F ON 
		C.CD_CLI = F.CD_CLI AND
		C.CD_EMP = F.CD_EMP
		LEFT JOIN RC_CLI_FIDELI_CARTAO M ON
		C.CD_EMP = M.CD_EMP AND 
		C.CD_CLI = M.CD_CLI
	WHERE
		C.CD_EMP = @CD_EMP
	

/****************************************************************************************************************
												V E N D A S
*****************************************************************************************************************/

	INSERT INTO @RS_VD
	SELECT
		V.CD_EMP,
		V.CD_FILIAL,
		V.CD_CLI,
		V.CD_PROD,
		P.DS_PROD,
		A.CD_ARV_MERC_LINHA,
		L.DS_ARV_MERC_LINHA,
        
		CASE WHEN  V.VLR_DINH  > V.VLR_CHQS AND   V.VLR_DINH  >  V.VLR_CARTAO   AND V.VLR_DINH  >  V.VLR_TICKETS    AND V.VLR_DINH  >  V.VLR_DUP  AND  V.VLR_DINH  >   V.VLR_CONV THEN
				'DINHEIRO'
			WHEN   V.VLR_CHQS >  V.VLR_DINH  AND  V.VLR_CHQS >  V.VLR_CARTAO   AND V.VLR_CHQS  >  V.VLR_TICKETS    AND V.VLR_CHQS  >  V.VLR_DUP  AND  V.VLR_CHQS  >   V.VLR_CONV THEN
				'CHEQUE'
			WHEN  V.VLR_CARTAO > V.VLR_CHQS  AND  V.VLR_CARTAO > V.VLR_DINH   AND V.VLR_CARTAO >  V.VLR_TICKETS    AND  V.VLR_CARTAO >  V.VLR_DUP  AND  V.VLR_CARTAO >   V.VLR_CONV THEN
				'CARTAO'
			WHEN  V.VLR_TICKETS  > V.VLR_CHQS AND   V.VLR_TICKETS  >  V.VLR_CARTAO   AND V.VLR_TICKETS  >  V.VLR_DINH AND V.VLR_TICKETS  >  V.VLR_DUP  AND V.VLR_TICKETS  >  V.VLR_CONV THEN
				'TICKET'
			WHEN  V.VLR_DUP  > V.VLR_CHQS AND   V.VLR_DUP  >  V.VLR_CARTAO   AND V.VLR_DUP  >  V.VLR_TICKETS    AND V.VLR_DUP  >  V.VLR_DINH  AND  V.VLR_DUP  >   V.VLR_CONV THEN
				'DUPLICATA'
			WHEN  V.VLR_CONV  > V.VLR_CHQS AND   V.VLR_CONV  >  V.VLR_CARTAO   AND V.VLR_CONV  >  V.VLR_TICKETS    AND V.VLR_CONV  >  V.VLR_DUP  AND  V.VLR_CONV  >   V.VLR_DINH THEN
				'CONVENIO'
			ELSE
				'NAO DEFINIDO'

		END AS FORMA_PGTO,

		SUM	(V.TOT_IT_BRUTO) VLR_VD,
		SUM	(V.TOT_IT) VLR_VD_LIQ,
		SUM	(V.QT_IT) ITENS,
		R.CD_MEDICO_REC,
		M.NM_MEDICO,
		V.DT_VD,
		V.NR_ECF,
		V.CD_CX 
		
	FROM
		V_PDV_VD_IT V
		
		INNER JOIN EST_PROD P ON
		V.CD_EMP = P.CD_EMP AND
		V.CD_PROD = P.CD_PROD
 		
		INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA A ON
		P.CD_EMP = A.CD_EMP AND
		P.CD_PROD = A.CD_PROD
 		
 		INNER JOIN @RS_LINHAS L ON 
		A.CD_EMP		    = L.CD_EMP AND 
		A.CD_ARV_MERC_LINHA = L.CD_ARV_MERC_LINHA
		
		INNER JOIN @RS_FREQ F ON 
		V.CD_EMP = F.CD_EMP 
		AND V.CD_CLI = F.CD_CLI   
		
		
		LEFT JOIN PDV_VD_IT_REC R ON 
 		R.CD_EMP    = V.CD_EMP AND
 		R.CD_FILIAL = V.CD_FILIAL AND
 		R.CD_VD = V.CD_VD  AND
		R.CD_IT = V.CD_IT
		
		INNER JOIN @RS_FILIAL FL ON 
		V.CD_EMP = FL.CD_EMP AND
		V.CD_FILIAL = FL.CD_FILIAL 
		
        LEFT JOIN GLB_MEDICOS M ON 
    	R.CD_EMP = M.CD_EMP AND
    	R.CD_MEDICO_REC = M.CD_CTR

	WHERE
	    V.CD_EMP = @CD_EMP 
		AND V.DT_VD BETWEEN @DT_INI AND @DT_FIM 
	GROUP BY
		V.CD_EMP,
		V.CD_FILIAL,
		V.CD_CLI,
		V.CD_VD,
		V.CD_PROD,
		P.DS_PROD,
		A.CD_ARV_MERC_LINHA,
		L.DS_ARV_MERC_LINHA,
		V.VLR_CONV,
		V.VLR_CHQS ,
		V.VLR_CARTAO,
		V.VLR_TICKETS,
		V.VLR_DUP,
		V.VLR_DINH,
		R.CD_MEDICO_REC,
		M.NM_MEDICO,
		V.DT_VD	,
		V.NR_ECF,
		V.CD_CX		

/****************************************************************************************************************
												R E T O R N O
*****************************************************************************************************************/
	INSERT INTO @RS_TEMP		
	SELECT
		V.CD_FILIAL,
		V.CD_CLI,
		C.NR_CARTAO,
		C.NM_CLI,
		C.RZ_CLI,
		C.END_CLI,
		C.BAIRRO,
		C.TELEFONE01,
		C.TELEFONE02,
		C.TELEFONE03,
		C.TELEFONE04,
		V.DT_VD,
		V.NR_ECF,
		V.CD_CX,
		C.DT_ULT_CP,
		V.CD_PROD,
		V.DS_PROD,
		V.CD_LINHA,
		V.DS_LINHA,
		V.FORMA_PGTO,
		V.VLR_VD,
		V.VLR_LIQ_VD,
		V.ITENS,
		F.FREQ,
		V.CD_MEDICO_REC,
		V.NM_MEDICO
	FROM
		@RS_VD V
		INNER JOIN @RS_CLI C ON
		V.CD_EMP  = C.CD_EMP
		AND V.CD_CLI = C.CD_CLI	
		 
		INNER JOIN @RS_FREQ F ON
		V.CD_EMP  = F.CD_EMP 
		AND V.CD_CLI = F.CD_CLI  

 
/****************************************************************************************************************
												F    I    M 
*****************************************************************************************************************/

	RETURN
END
