/****** Object:  StoredProcedure [dbo].[P_ANALISE_VENDA_PRODUTOS_FILIAL_30]    Script Date: 03/12/2014 10:20:11 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_ANALISE_VENDA_PRODUTOS_FILIAL_30]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_ANALISE_VENDA_PRODUTOS_FILIAL_30]
GO
GO
GO
--execute P_ANALISE_VENDA_PRODUTOS_FILIAL_30 1, 3,'20141225',10,100,100
CREATE PROCEDURE [dbo].[P_ANALISE_VENDA_PRODUTOS_FILIAL_30]
									@CD_EMP INT=1, 
									@CD_FILIAL INT, 
									@DT_ATUAL DATETIME =NULL,
									@QT_TOT_RD FLOAT,
									@VLR_TOT_RD FLOAT, 
									@MEDIA_RD FLOAT AS

SET NOCOUNT ON

PRINT ' Inicio P_ANALISE_VENDA_PRODUTOS_FILIAL_30 : ' + CONVERT(VARCHAR, GETDATE (),108)
--Calcular parmetros de datas
DECLARE @DT_INI DATETIME,
		@DT_FIM DATETIME,
		@AGORA  DATETIME,
		@TIPO_ANALISE_FALTAS INT

		
CREATE TABLE #RS_PROD_RD (CD_EMP INT, 
				  QT_TOT_RD MONEY,
				  VLR_TOT_RD FLOAT,
				  MEDIA_RD FLOAT)

CREATE TABLE #RS_PROD (CD_EMP INT, 
				  CD_FILIAL INT, 
				  CD_PROD INT, 
				  DT_MOV DATETIME,
				  QT_TOT FLOAT,
				  VLR_TOT FLOAT,
				  MEDIA MONEY)
				  		
SELECT	@DT_FIM = DBO.F_ONTEM(),  --Dia anterior
		@DT_INI = DATEADD(day, -29, @DT_FIM), --1 ms e 1 dia atrs
		@AGORA = convert(char(10),getdate(),112)



SET @TIPO_ANALISE_FALTAS=ISNULL((SELECT 
									VALOR AS ORC_PED_CP
								 FROM 
									PRC_EMP_CONFIG
								 WHERE 
									CD_EMP	= @CD_EMP 
								    AND	CD_CHAVE = 'TIPO_ANALISE_FALTA_PRODUTO'),0);

--Calculo do periodo de analise
IF @DT_ATUAL IS NULL SELECT @DT_ATUAL=dbo.F_ONTEM();

PRINT 'Dia do processamento ' + CONVERT(VARCHAR, @DT_ATUAL,112)
PRINT 'Deletando registro no dia do proc.: ' + CONVERT(VARCHAR, GETDATE (),108);

--Limpa os dados ja existentes para mes (Atualizacao)
IF EXISTS (SELECT TOP 1 CD_EMP FROM EST_PROD_CPL_ANALISE_30 
				WHERE 	CD_EMP = @CD_EMP
						AND DT_MOV = @DT_ATUAL
						AND CD_FILIAL = @CD_FILIAL)
begin
	DELETE A FROM 
		EST_PROD_CPL_ANALISE_30 A
	WHERE 
		A.CD_EMP = @CD_EMP
		AND A.DT_MOV = @DT_ATUAL
		AND A.CD_FILIAL = @CD_FILIAL;
end

PRINT 'Valor venda rede: ' + CONVERT(VARCHAR, GETDATE (),108)
--Produtos vendidos rede
INSERT INTO #RS_PROD_RD
SELECT	
	@CD_EMP,
	@QT_TOT_RD,
	@VLR_TOT_RD, 
	@MEDIA_RD

IF @TIPO_ANALISE_FALTAS=1
begin
PRINT 'Data de pesquisa: ' + convert(CHAR(10),@DT_INI,103) + ' a ' + convert(CHAR(10),@DT_FIM,103)
PRINT 'Valor venda lojas: ' + CONVERT(VARCHAR, GETDATE (),108)
--Produtos vendidos
INSERT INTO #RS_PROD
SELECT	
	i.CD_EMP, 
	i.CD_FILIAL, 
	i.CD_PROD, 
	@DT_ATUAL as DT_MOV,
	CONVERT(FLOAT,SUM(i.QT_IT)) AS QT_TOT, 
	CONVERT(FLOAT,SUM(i.TOT_IT - i.VLR_DESC_TOT_RAT)) AS VLR_TOT, 			
	CASE WHEN SUM(i.QT_IT) > 0 THEN 
		SUM(i.TOT_IT - i.VLR_DESC_TOT_RAT)/SUM(i.QT_IT) ELSE 0 END
	AS MEDIA		
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 
WHERE 
	i.DT_VD BETWEEN @DT_INI AND @DT_FIM	
	AND v.ST_VD = 0 
	AND i.ST_IT = 1
	AND i.CD_EMP=@CD_EMP
	AND i.CD_FILIAL = @CD_FILIAL
GROUP BY 
	i.CD_EMP, 
	i.CD_FILIAL, 
	i.CD_PROD
end

IF @TIPO_ANALISE_FALTAS=2
begin
PRINT 'Valor mediaF lojas: ' + CONVERT(VARCHAR, GETDATE (),108)
--Produtos vendidos
INSERT INTO #RS_PROD
SELECT	
	c.CD_EMP, 
	c.CD_FILIAL, 
	c.CD_PROD, 
	@DT_ATUAL as DT_MOV,
	CONVERT(FLOAT,SUM(c.MEDIAF)) AS QT_TOT, 
	CONVERT(FLOAT,SUM(pr.VLR_TABELA * c.MEDIAF)) AS VLR_TOT, 			
	CASE WHEN SUM(c.MEDIAF) > 0 THEN 
		SUM(pr.VLR_TABELA * c.MEDIAF)/SUM(c.MEDIAF)  
	ELSE 
	0 
	END AS MEDIA		
FROM 
	EST_PROD_CPL c 
	inner join EST_PROD_PRECO pr ON
	c.CD_EMP = pr.CD_EMP AND
	c.CD_FILIAL = pr.CD_FILIAL AND
	c.CD_PROD = pr.CD_PROD 
	LEFT JOIN V_PRC_FILIAL_RELACAO_DEPOSITOS R ON  
	c.CD_FILIAL = R.CD_FILIAL  
WHERE 
	c.CD_EMP=1 
	AND C.CD_EMP=@CD_EMP
	AND C.CD_FILIAL = @CD_FILIAL 
	AND R.CD_FILIAL IS NULL
GROUP BY
	c.CD_EMP,
	c.CD_FILIAL,
	c.CD_PROD
end

CREATE CLUSTERED INDEX IX_RsProdIdx ON #RS_PROD (CD_EMP,CD_FILIAL,CD_PROD)		
CREATE NONCLUSTERED INDEX IX_RsProdIdxlN ON #RS_PROD (CD_EMP,CD_PROD)		

PRINT 'Inserindo produtos movimentados.: ' + CONVERT(VARCHAR, GETDATE (),108)
INSERT INTO EST_PROD_CPL_ANALISE_30
		(CD_EMP, 
		CD_FILIAL, 
		CD_PROD, 
		DT_MOV, 
		VLR_TABELA, 
		VLR_OFERTA, 
		PERC_DESC, 
		QTDE_TOTAL, 
		PART_QTDE, 
		VLR_VD_TOTAL, 
		PART_VLR, 
		VLR_MEDIA_ITEN,
		FLAG_PART_INDIC)
	SELECT	
		b.CD_EMP, 
		b.CD_FILIAL, 
		b.CD_PROD, 
		DT_MOV,
		ISNULL(VLR_TABELA,0), 
		ISNULL(VLR_OFERTA,0), 
		PERC_DESC,
		b.QT_TOT AS VLR_QTDE_TOTAL,		
		(b.QT_TOT / br.QT_TOT_RD)*100 as PERC_QTDE,
		ROUND(b.VLR_TOT, 2) AS VLR_VD_TOTAL,	
		(b.VLR_TOT / br.VLR_TOT_RD) * 100.00 as PERC_VLR,
		ROUND(b.MEDIA, 2) AS VLR_MEDIA_ITEN,
		p.FLAG_ANALISE_FALTAS
	FROM #RS_PROD b 
		INNER JOIN EST_PROD p ON 
		b.CD_EMP=p.CD_EMP AND 
		b.CD_PROD=p.CD_PROD
		INNER JOIN EST_PROD_CPL pcl ON 
		b.CD_EMP=pcl.CD_EMP AND 
		b.CD_FILIAL=pcl.CD_FILIAL AND 
		b.CD_PROD=pcl.CD_PROD
		JOIN EST_PROD_PRECO c ON 
		b.CD_EMP=c.CD_EMP AND 
		b.CD_FILIAL=c.CD_FILIAL AND 
		b.CD_PROD=c.CD_PROD
		INNER JOIN #RS_PROD_RD br ON
		b.CD_EMP=br.CD_EMP 
	where
		pcl.CD_EMP=@CD_EMP
		AND pcl.CD_FILIAL = @CD_FILIAL 
		AND pcl.STS_PROD <> 1
		AND P.STS_PROD <> 1

	PRINT ' Fim P_ANALISE_VENDA_PRODUTOS_FILIAL_30 : ' + CONVERT(VARCHAR, GETDATE (),108)
		
DROP TABLE #RS_PROD;
DROP TABLE #RS_PROD_RD;

PRINT 'xxxxxxxxxxxxxxxxxxxxxxxxxx ' + CONVERT(VARCHAR, GETDATE (),108) + ' xxxxxxxxxxxxxxxxxxxxxxxxxx'  
PRINT ' '
PRINT ' '
PRINT ' '
GO
