/****** Object:  StoredProcedure [dbo].[P_ANALISE_VENDA_PRODUTOS_FILIAL_30_EXECUTE_ALL]    Script Date: 03/12/2014 09:44:32 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_ANALISE_VENDA_PRODUTOS_FILIAL_30_EXECUTE_ALL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_ANALISE_VENDA_PRODUTOS_FILIAL_30_EXECUTE_ALL]
GO
GO
GO
--EXECUTE P_ANALISE_VENDA_PRODUTOS_FILIAL_30_EXECUTE_ALL 1,3,'20090131',2
-- EXECUTE P_ANALISE_VENDA_PRODUTOS_FILIAL_30_EXECUTE_ALL
CREATE PROCEDURE [dbo].[P_ANALISE_VENDA_PRODUTOS_FILIAL_30_EXECUTE_ALL] 
														@CD_EMP INT=1, 
														@QT_MESES INT=1, 
														@DT_PROC DATETIME=NULL AS
IF @DT_PROC IS NULL
	BEGIN
		SET @DT_PROC=DBO.F_ONTEM(); 
	END
	
DECLARE @CD_FILIAL INT
DECLARE @TIPO_ANALISE_FALTAS INT
DECLARE @QT_TOT_RD FLOAT
DECLARE @VLR_TOT_RD FLOAT
DECLARE @MEDIA_RD FLOAT
DECLARE @DT_INI DATETIME
DECLARE @DT_FIM DATETIME


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);

SELECT	@DT_FIM =@DT_PROC,  --Dia anterior
		@DT_INI = DATEADD(day, -29, @DT_FIM) --1 ms e 1 dia atrs

PRINT 'Inicio' + CONVERT(VARCHAR, GETDATE (),108)

IF @TIPO_ANALISE_FALTAS=1 
begin
	PRINT 'Processamento do analise diaria 30 para todas as filiais ' 
	PRINT 'Tipo analise diaria 30 dias corrente' 
end
IF @TIPO_ANALISE_FALTAS=2 
	PRINT 'Tipo analise diaria MediaF'

--Ultimos 30 dias
IF @TIPO_ANALISE_FALTAS=1 
	begin 
		SELECT	
			@QT_TOT_RD  = CONVERT(FLOAT,SUM(i.QT_IT)), 
			@VLR_TOT_RD = CONVERT(FLOAT,SUM(i.TOT_IT - i.VLR_DESC_TOT_RAT)), 			
			@MEDIA_RD = CASE WHEN SUM(i.QT_IT) > 0 THEN 
							SUM(i.TOT_IT - i.VLR_DESC_TOT_RAT)/SUM(i.QT_IT) 
						ELSE 
							0 
						END
		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
		GROUP BY 
			v.CD_EMP

PRINT	'Vendas R$' + convert(varchar(200), @QT_TOT_RD)  
PRINT	'Itens ' + CONVERT(VARCHAR(200), @VLR_TOT_RD) 
PRINT	'Media ' + CONVERT(VARCHAR(200), @MEDIA_RD) 
end

--MediaF
IF @TIPO_ANALISE_FALTAS=2 
	begin 
		SELECT	
			@QT_TOT_RD  = CONVERT(FLOAT,SUM(c.MEDIAF)), 
			@VLR_TOT_RD = CONVERT(FLOAT,SUM(pr.VLR_TABELA * c.MEDIAF)), 			
			@MEDIA_RD = CASE WHEN SUM(c.MEDIAF) > 0 THEN 
							SUM(pr.VLR_TABELA * c.MEDIAF)/SUM(c.MEDIAF) 
						ELSE 
							0 
						END
		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 R.CD_FILIAL IS NULL

	    PRINT	'MediaF R$' + convert(varchar(200), @QT_TOT_RD) 
	    PRINT	'Itens ' + CONVERT(VARCHAR(200), @VLR_TOT_RD) 
	    PRINT	'Media ' + CONVERT(VARCHAR(200), @MEDIA_RD) 
  end
	
DECLARE filiais CURSOR FAST_FORWARD FOR 
		SELECT CD_FILIAL FROM PRC_FILIAL
				WHERE NOT CD_FILIAL IN ( SELECT CD_FILIAL FROM PRC_FILIAL_DEPOSITO)
				AND STS_FILIAL=0;

OPEN filiais
FETCH NEXT FROM filiais INTO @CD_FILIAL
WHILE @@FETCH_STATUS=0
  begin
	PRINT	'  Analise filial ' + convert(varchar(5), @CD_FILIAL) + 
			' (' + CONVERT(VARCHAR(8), GETDATE(), 114) + ')...'
	IF @TIPO_ANALISE_FALTAS IN (1,2)
		begin 
			EXECUTE P_ANALISE_VENDA_PRODUTOS_FILIAL_30 1,@CD_FILIAL,@DT_PROC,@QT_TOT_RD,@VLR_TOT_RD,@MEDIA_RD
		end 
	
	FETCH NEXT FROM filiais INTO @CD_FILIAL
  end
PRINT 'Concluido.'
CLOSE filiais
DEALLOCATE filiais
GO


