
IF OBJECT_ID('P_GERAR_ESTATISCA_DE_VENDAS_CLIENTE') IS NOT NULL
	BEGIN
		DROP PROCEDURE P_GERAR_ESTATISCA_DE_VENDAS_CLIENTE
	END
 GO 
  
/****** Object:  StoredProcedure [dbo].[P_GERAR_ESTATISCA_DE_VENDAS_CLIENTE]    Script Date: 30/07/2014 10:22:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO 
--EXEC P_GERAR_ESTATISCA_DE_VENDAS_CLIENTE
CREATE PROCEDURE [dbo].[P_GERAR_ESTATISCA_DE_VENDAS_CLIENTE] AS 
DECLARE	@dtInicioInsercao DATETIME, @dtFimInsercao DATETIME
SELECT	@dtInicioInsercao = CONVERT(CHAR(10), GETDATE()-25, 112)
SELECT	@dtFimInsercao = CONVERT(CHAR(10), GETDATE(), 112)
SET NOCOUNT ON 
CREATE TABLE #RsMovimento (CD_EMP INT,
						   CD_FILIAL INT,
						   CD_CLI INT,
						   DT_MOV DATETIME,
						   VLR_DESC MONEY,
						   VLR_MOV MONEY,
						   QTDE_MOV INT)

CREATE TABLE #RsMovimentoProd (CD_EMP INT,
							   CD_FILIAL INT,
							   CD_CLI INT,
							   CD_PROD INT,
							   QTDE_MOV MONEY,
							   VLR_MOV MONEY)						   	
	DELETE 
		P
	FROM
		RC_CLI_ESTATIS_PENDENTE P LEFT OUTER JOIN RC_CLI C ON
		P.CD_EMP = C.CD_EMP AND
		P.CD_CLI = C.CD_CLI
	WHERE
		C.CD_CLI IS NULL
	
	CREATE TABLE #RC_CLI_PROC (CD_EMP INT, CD_CLI INT)

	Print 'Buscando cliente pendente de processamento  ' + CONVERT(VARCHAR(8), GETDATE(), 114) 
	--Buscando clientes para processamento
	INSERT INTO #RC_CLI_PROC
	SELECT distinct 
		CD_EMP,
		CD_CLI
	FROM
		RC_CLI_ESTATIS_PENDENTE

	Print 'Deletando dados vencidos do diario ' + CONVERT(VARCHAR(8), GETDATE(), 114) 
--Deletando o diario 
	DELETE 
		e
	FROM 
		RC_CLI_ESTATIS_DESC_DIARIO e INNER JOIN #RC_CLI_PROC c ON
		e.CD_EMP = c.CD_EMP AND
		e.CD_CLI = c.CD_CLI 

	DELETE 
		from #RC_CLI_PROC
	where
		  CD_CLI< 0 or CD_CLI IS NULL

INSERT INTO #RsMovimento
SELECT
	EST_NF_SAI.CD_EMP,
	EST_NF_SAI.CD_FILIAL,
	EST_NF_SAI.CD_CLI,
	EST_NF_SAI.DT_EMI_NF,
	SUM(EST_NF_SAI.VLR_DESC) AS VLR_DESC,
	SUM(EST_NF_SAI.VLR_NF) AS VLR_NF,
	COUNT(EST_NF_SAI.CD_NF)
FROM
	EST_NF_SAI INNER JOIN #RC_CLI_PROC c ON
	EST_NF_SAI.CD_EMP = c.CD_EMP AND
	EST_NF_SAI.CD_CLI = c.CD_CLI
WHERE
	EST_NF_SAI.DT_EMI_NF > @dtInicioInsercao AND EST_NF_SAI.DT_EMI_NF <= @dtFimInsercao 
GROUP BY
	EST_NF_SAI.CD_EMP,
	EST_NF_SAI.CD_FILIAL,
	EST_NF_SAI.CD_CLI,
	EST_NF_SAI.DT_EMI_NF
UNION ALL
SELECT 
	PDV_VD.CD_EMP, 
	PDV_VD.CD_FILIAL, 
	PDV_VD.CD_CLI, 
	PDV_VD.DT_VD, 
	SUM(PDV_VD.VLR_DESC + PDV_VD.VLR_DESC_ITENS), 
	SUM(PDV_VD.VLR_LIQ_VD), 
	COUNT(PDV_VD.CD_CLI)
FROM 
	PDV_VD INNER JOIN #RC_CLI_PROC c ON
	PDV_VD.CD_EMP = c.CD_EMP AND
	PDV_VD.CD_CLI = c.CD_CLI
WHERE 
	PDV_VD.DT_VD > @dtInicioInsercao AND DT_VD <= @dtFimInsercao 
	AND PDV_VD.ST_VD=0
	AND PDV_VD.CD_EMP=1
GROUP BY 
	PDV_VD.CD_EMP, 
	PDV_VD.CD_FILIAL, 
	PDV_VD.CD_CLI, 
	PDV_VD.DT_VD 


	Print 'Gerando a estatistica diaria ' + CONVERT(VARCHAR(8), GETDATE(), 114) 
	INSERT INTO RC_CLI_ESTATIS_DESC_DIARIO
	SELECT 
		CD_EMP, 
		CD_FILIAL, 
		CD_CLI, 
		DT_MOV, 
		SUM(VLR_DESC), 
		SUM(VLR_MOV), 
		COUNT(QTDE_MOV)
	FROM 
		#RsMovimento
	GROUP BY 
		CD_EMP, 
		CD_FILIAL, 
		CD_CLI, 
		DT_MOV 

	Print 'Deletando dados da estatistica sintetica do cliente ' + CONVERT(VARCHAR(8), GETDATE(), 114) 

--Deletando quem esta na fila para processamento
	DELETE 
		e
	FROM 
		RC_CLI_ESTATIS_DESC e INNER JOIN #RC_CLI_PROC c ON
		e.CD_EMP = c.CD_EMP AND
		e.CD_CLI = c.CD_CLI 

	Print 'Gerando estatisca do cliente sintetica ' + CONVERT(VARCHAR(8), GETDATE(), 114) 
--Estatistica por cliente e filial
	INSERT INTO RC_CLI_ESTATIS_DESC
	SELECT	
		e.CD_EMP, 
		e.CD_FILIAL, 
		e.CD_CLI, 
		CASE WHEN ISNULL(SUM(e.VLR_TOTAL_VD),0) > 0 THEN
			100 * SUM(e.VLR_TOTAL_DESC) / SUM(e.VLR_TOTAL_VD)
		ELSE
			0
		END, 
		CASE WHEN  ISNULL(SUM(e.QT_VD),0) > 0 THEN
			SUM(e.VLR_TOTAL_VD) / SUM(e.QT_VD)
		ELSE
			0
		END, 
		SUM(e.QT_VD)
	FROM 
		RC_CLI_ESTATIS_DESC_DIARIO e INNER JOIN #RC_CLI_PROC c ON
		e.CD_EMP = c.CD_EMP AND
		e.CD_CLI = c.CD_CLI 
	WHERE 
		e.DT_VD > @dtInicioInsercao AND DT_VD <= @dtFimInsercao 
	GROUP BY 
		e.CD_EMP, 
		e.CD_FILIAL, 
		e.CD_CLI

	Print 'Deletando dados da estatistica de produto cliente ' + CONVERT(VARCHAR(8), GETDATE(), 114) 

--Deletando quem esta na fila para processamento
	DELETE 
		e
	FROM 
		RC_CLI_ESTATIS_EST_PROD e INNER JOIN #RC_CLI_PROC c ON
		e.CD_EMP = c.CD_EMP AND
		e.CD_CLI = c.CD_CLI 

DECLARE @CD_EMP INT
DECLARE @CD_CLI INT

	Print 'Gerando estatisca do cliente por produto ' + CONVERT(VARCHAR(8), GETDATE(), 114) 

--Estatistica por produto
set @dtInicioInsercao = CONVERT(CHAR(10), GETDATE()-90, 112)
DECLARE ClienteProceEsta CURSOR FAST_FORWARD FOR 
SELECT 
	CD_EMP, 
	CD_CLI     
FROM     
	#RC_CLI_PROC    
    
OPEN ClienteProceEsta    
FETCH NEXT FROM ClienteProceEsta INTO @CD_EMP, @CD_CLI    
    
WHILE @@FETCH_STATUS=0    
	Begin    
		print  'Cliente >> ' + convert(varchar(10), @CD_CLI)
		INSERT INTO #RsMovimentoProd
		SELECT
			TOP 10 EST_NF_SAI_IT.CD_EMP,
			EST_NF_SAI_IT.CD_FILIAL,
			EST_NF_SAI.CD_CLI,
			EST_NF_SAI_IT.CD_PROD,
			COUNT(EST_NF_SAI_IT.QT_IT) AS QT_IT,
			SUM(EST_NF_SAI_IT.VLR_TOT_IT - EST_NF_SAI_IT.VLR_DESC_IT) AS VLR_MOV
		FROM
			EST_NF_SAI INNER JOIN EST_NF_SAI_IT ON
			EST_NF_SAI.CD_EMP = EST_NF_SAI_IT.CD_EMP AND
			EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL AND
			EST_NF_SAI.CD_NF = EST_NF_SAI_IT.CD_NF 
			INNER JOIN EST_NF_SAI_VENDAS ON
			EST_NF_SAI.CD_EMP = EST_NF_SAI_VENDAS.CD_EMP AND
			EST_NF_SAI.CD_FILIAL = EST_NF_SAI_VENDAS.CD_FILIAL AND
			EST_NF_SAI.CD_NF = EST_NF_SAI_VENDAS.CD_NF 
			INNER JOIN RC_CLI  ON
			EST_NF_SAI.CD_EMP = RC_CLI.CD_EMP AND
			EST_NF_SAI.CD_CLI = RC_CLI.CD_CLI
			INNER JOIN EST_PROD P ON
			EST_NF_SAI_IT.CD_EMP = P.CD_EMP AND
			EST_NF_SAI_IT.CD_PROD = P.CD_PROD 
		WHERE
			EST_NF_SAI.CD_EMP =@CD_EMP   
			AND EST_NF_SAI.CD_CLI=@CD_CLI
			AND P.FLAG_NAO_APARECER_ESTATISTICA_COMPRA_CLIENTE = 0  
			AND EST_NF_SAI.STS_NF = 0   
			AND EST_NF_SAI.DT_EMI_NF BETWEEN @dtInicioInsercao AND @dtFimInsercao  
			AND RC_CLI.FLAG_NAO_APARECER_ESTATISTICA_COMPRA=0
		GROUP BY
			EST_NF_SAI_IT.CD_EMP,
			EST_NF_SAI_IT.CD_FILIAL,
			EST_NF_SAI.CD_CLI,
			EST_NF_SAI_IT.CD_PROD
		ORDER BY 
			QT_IT DESC

		INSERT INTO #RsMovimentoProd
			SELECT 
				TOP 10   
				v.CD_EMP,
				v.CD_FILIAL,
				V.CD_CLI,
				i.CD_PROD,   
				ISNULL(SUM(I.QT_IT),0) AS QTDE,   
				ISNULL(SUM( case when i.QT_IT > 0 then 
										I.TOT_IT / QT_IT else 0 end),0) AS VALOR   
			FROM   
				PDV_VD V INNER JOIN PDV_VD_IT I ON  
				V.CD_VD = I.CD_VD AND  
				V.CD_FILIAL = I.CD_FILIAL AND  
				V.CD_EMP = I.CD_EMP  AND  
				V.DT_VD = I.DT_VD 
				INNER JOIN EST_PROD P ON
				I.CD_EMP = P.CD_EMP AND
				I.CD_PROD = P.CD_PROD
				INNER JOIN RC_CLI  ON
				V.CD_EMP = RC_CLI.CD_EMP AND
				V.CD_CLI = RC_CLI.CD_CLI
			WHERE  
				i.CD_EMP =@CD_EMP   
				AND V.CD_CLI=@CD_CLI
				AND P.FLAG_NAO_APARECER_ESTATISTICA_COMPRA_CLIENTE = 0  
				AND v.ST_VD = 0   
				AND i.DT_VD BETWEEN @dtInicioInsercao AND @dtFimInsercao  
				AND RC_CLI.FLAG_NAO_APARECER_ESTATISTICA_COMPRA=0
			GROUP BY  
				v.CD_EMP,
				v.CD_FILIAL,
				i.CD_PROD,
				V.CD_CLI
			ORDER BY 
				QTDE DESC

		INSERT INTO RC_CLI_ESTATIS_EST_PROD
		SELECT 
			TOP 10   
			M.CD_EMP,
			M.CD_FILIAL,
			M.CD_CLI,
			M.CD_PROD,   
			ISNULL(SUM(M.QTDE_MOV),0) AS QTDE,   
			ISNULL(SUM( M.VLR_MOV),0) AS VALOR   
		FROM   
			#RsMovimentoProd M
		GROUP BY  
			M.CD_EMP,
			M.CD_FILIAL,
			M.CD_CLI,
			M.CD_PROD
		ORDER BY 
			QTDE DESC
		
		DELETE FROM #RsMovimentoProd

		--Proximo registro    
		FETCH NEXT FROM ClienteProceEsta INTO @CD_EMP, @CD_CLI     
	End    
    
--Fechando cursos    
CLOSE ClienteProceEsta;    
    
--Desalocando cursos da memoria    
DEALLOCATE ClienteProceEsta;    

	Print 'Deletando clientes na fila de processamento' + CONVERT(VARCHAR(8), GETDATE(), 114) 
--Deletando quem ja foi processado

DELETE
 cp
from
	#RC_CLI_PROC c inner join RC_CLI_ESTATIS_PENDENTE cp on
	c.cd_emp = cp.cd_emp and
	c.cd_cli = cp.cd_cli

DROP TABLE #RC_CLI_PROC;
DROP TABLE #RsMovimento;
DROP TABLE #RsMovimentoProd;