IF EXISTS (SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[P_ANALISE_VENDA_PRODUTOS_MENSAL_DEPOSITO]')
		AND type IN (N'P', N'PC' )
    )
    DROP PROCEDURE [dbo].[P_ANALISE_VENDA_PRODUTOS_MENSAL_DEPOSITO];
GO
GO
--EXECUTE [P_ANALISE_VENDA_PRODUTOS_MENSAL_DEPOSITO] 1,2011,9,1,1
CREATE PROCEDURE [dbo].[P_ANALISE_VENDA_PRODUTOS_MENSAL_DEPOSITO] @CD_EMP      INT = 1
												    , @ANO         INT = NULL
												    , @MES         INT = NULL
												    , @MES_ATUAL   INT = NULL
												    , @CD_CTR_DEPO INT = 1
AS
	    BEGIN
		   SET NOCOUNT ON;
		   DECLARE @DTBASE         DATETIME
			    , @DT_INI         DATETIME
			    , @DT_FIM         DATETIME
			    , @DT_INI_PER     DATETIME
			    , @DT_FIM_PER     DATETIME
			    , @CD_FILIAL_DEPO INT;
		   IF @MES_ATUAL IS NULL
			  BEGIN
				 SELECT
					   @DTBASE = DATEADD(MONTH, -1, GETDATE());
			  END;
		   IF @MES_ATUAL = 1 --M?s atual para fechamento diario
			  BEGIN
				 SELECT
					   @DTBASE = GETDATE();
				 SET @DT_INI = DBO.F_INICIO_MES(@DTBASE);
				 SET @DT_FIM = DBO.F_FINAL_MES(@DTBASE);
			  END;
		   IF @ANO IS NULL
			  BEGIN
				 SELECT
					   @ANO = YEAR(@DTBASE);
			  END;
		   IF @MES IS NULL
			  BEGIN
				 SELECT
					   @MES = MONTH(@DTBASE);
			  END;
		   IF @DT_INI IS NULL
			  BEGIN
				 SET @DT_INI = CONVERT(DATETIME, CONVERT(VARCHAR, @ANO)+RIGHT('00'+CONVERT(VARCHAR, @MES), 2)+'01');
			  END;
		   IF @DT_FIM IS NULL
			  BEGIN
				 SET @DT_FIM = DBO.F_FINAL_MES(@DT_INI);
			  END;

			if @DTBASE is null 
			  set @DTBASE =@DT_FIM 

    DECLARE @TIPO_ANALISE_FALTAS TINYINT;
    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);

						  
		   SET @DT_INI_PER = @DT_INI ;
		   SET @DT_FIM_PER = @DT_FIM;

		  IF @TIPO_ANALISE_FALTAS = 1
		  BEGIN 		
		  --Produtos participacao 30
    			 SET @DT_INI_PER = DATEADD(DAY, -30, @DTBASE);
			 SET @DT_FIM_PER = @DTBASE;

		  END;


		   SET @CD_FILIAL_DEPO =( SELECT TOP 1 CD_FILIAL FROM PRC_FILIAL_DEPOSITO WHERE CD_CTR_DEPO = @CD_CTR_DEPO );
		   PRINT 'Incio da rotina  - Hora : '+CONVERT(VARCHAR, GETDATE(), 108)+' Filial '+CONVERT(VARCHAR, @CD_FILIAL_DEPO);
		   PRINT 'Data Base : '+CONVERT(VARCHAR, @DTBASE)
		   PRINT 'Codigo Controle Deposito : '+CONVERT(VARCHAR, @CD_CTR_DEPO);
		   PRINT 'Periodo de analise de vendas : '+CONVERT(VARCHAR, @DT_INI, 112)+' a '+CONVERT(VARCHAR, @DT_FIM, 112);
	
	--Limpa os dados j? existentes para m?s (Atualiza??o)
		   IF EXISTS
				    (
					   SELECT
							*
					   FROM EST_PROD_CPL_ANALISE
					   WHERE CD_FILIAL = @CD_FILIAL_DEPO
						    AND CD_EMP = @CD_EMP
						    AND ANO = @ANO
						    AND MES = @MES
				    )
			  BEGIN
				 DELETE FROM EST_PROD_CPL_ANALISE
				 WHERE
					  CD_FILIAL = @CD_FILIAL_DEPO
					  AND CD_EMP = @CD_EMP
					  AND ANO = @ANO
					  AND MES = @MES;
				 PRINT 'Limpou registros antigos ';
			  END;
		   PRINT 'Processamento movimentacao mensal: '+CONVERT(CHAR(4), @ANO)+'/'+CONVERT(CHAR(5), @MES)+' Hora : '+CONVERT(VARCHAR, GETDATE(), 108);
		   CREATE TABLE #TBL_BASE
						  (CD_EMP    INT
						  , CD_FILIAL INT
						  , CD_PROD   INT
						  , ANO       INT
						  , MES       INT
						  , QT_TOT    MONEY
						  , VLR_TOT   MONEY
						  , MEDIA     MONEY
						  );
		   CREATE TABLE #TBL_BASE_PART
					   (CD_EMP    INT
					   , CD_FILIAL INT
					   , CD_PROD   INT
					   , QT_TOT_PART    MONEY
					   , VLR_TOT_PART   MONEY
					   );
		   
		--Produtos vendidos
		   INSERT INTO #TBL_BASE
		   SELECT
				I.CD_EMP
			   , @CD_FILIAL_DEPO CD_FILIAL
			   , I.CD_PROD
			   , @ANO AS ANO
			   , @MES AS MES
			   , CONVERT(MONEY, SUM(I.QT_IT - isnull(imp.qt_it,0))) AS QT_TOT
			   , CONVERT(MONEY, SUM(I.TOT_IT)) AS VLR_TOT
			   , CONVERT(MONEY,
					   CASE
						  WHEN SUM(I.QT_IT) > 0
						  THEN SUM(I.TOT_IT) / SUM(I.QT_IT)
						  ELSE 0
					   END) AS MEDIA
		   FROM
			   V_PDV_VD_IT I
			   INNER JOIN V_PRC_FILIAL_DEPOSITO D
			   ON I.CD_EMP = D.CD_EMP
				 AND I.CD_FILIAL = D.CD_FILIAL
			   OUTER APPLY (SELECT SUM(IMP.QT_IT) AS QT_IT
						 FROM PDV_VD_DEV_EST_PROD_IMP IMP
						 WHERE IMP.CD_EMP = I.CD_EMP
						 AND IMP.CD_FILIAL = I.CD_FILIAL
						 AND IMP.CD_PROD = I.CD_PROD
						 AND IMP.CD_VD = I.CD_VD) IMP
		   WHERE I.CD_EMP = 1
			    AND D.CD_CTR_DEPO = @CD_CTR_DEPO
			    AND DT_VD BETWEEN @DT_INI  AND @DT_FIM 
		   GROUP BY
				  I.CD_EMP
				, I.CD_PROD;

		  --Participacao dos produtos no periodo							
		  INSERT INTO #TBL_BASE_PART
		  SELECT
				I.CD_EMP
				, @CD_FILIAL_DEPO CD_FILIAL
				, I.CD_PROD
				, CONVERT(MONEY, SUM(I.QT_IT - isnull(imp.qt_it,0))) AS QT_TOT
				, CONVERT(MONEY, SUM(I.TOT_IT)) AS VLR_TOT
		  FROM
				V_PDV_VD_IT I
				INNER JOIN V_PRC_FILIAL_DEPOSITO D
				ON I.CD_EMP = D.CD_EMP
				AND I.CD_FILIAL = D.CD_FILIAL
				OUTER APPLY (SELECT SUM(IMP.QT_IT) AS QT_IT
						 FROM PDV_VD_DEV_EST_PROD_IMP IMP
						 WHERE IMP.CD_EMP = I.CD_EMP
						 AND IMP.CD_FILIAL = I.CD_FILIAL
						 AND IMP.CD_PROD = I.CD_PROD
						 AND IMP.CD_VD = I.CD_VD) IMP
		  WHERE I.CD_EMP = 1
				AND D.CD_CTR_DEPO = @CD_CTR_DEPO
				AND DT_VD BETWEEN @DT_INI_PER AND @DT_FIM_PER
		  GROUP BY
				    I.CD_EMP
				, I.CD_PROD;
			
			 


		   INSERT INTO EST_PROD_CPL_ANALISE(
				CD_EMP
			   , CD_FILIAL
			   , CD_PROD
			   , MES
			   , ANO
			   , VLR_TABELA
			   , VLR_OFERTA
			   , PERC_DESC
			   , QTDE_TOTAL
			   , PART_QTDE
			   , VLR_VD_TOTAL
			   , PART_VLR
			   , VLR_MEDIA_ITEN
			   , DT_CALCULO_DIARIO
			   , FLAG_PART_INDIC)
		   SELECT
				b.CD_EMP
			   , b.CD_FILIAL
			   , b.CD_PROD
			   , b.MES
			   , b.ANO
			   , ISNULL(C.VLR_TABELA, 0) VLR_TABELA
			   , ISNULL(C.VLR_OFERTA, 0) VLR_TABELA
			   , p.PERC_DESC
			   , ISNULL(b.QT_TOT, 0) AS VLR_QTDE_TOTAL

			   , ROUND((Part.QT_TOT_PART / CONVERT(FLOAT, lj.QT_TOT_VD_FILIAL)) * 100, 8) AS PERC_QTDE

			   , ISNULL(ROUND(b.VLR_TOT, 2), 0) AS VLR_VD_TOTAL

			   , ROUND((Part.VLR_TOT_PART / CONVERT(FLOAT, lj.VLR_TOT_VD_FILIAL)) * 100, 8) AS PERC_VLR

			   , ROUND(b.MEDIA, 2) AS VLR_MEDIA_ITEN
			   , getdate()
			   , p.FLAG_ANALISE_FALTAS
		   FROM
			   #TBL_BASE b
			   INNER JOIN EST_PROD p ON 
			   b.CD_EMP  = p.CD_EMP AND 
			   b.CD_PROD = p.CD_PROD

			   INNER JOIN #TBL_BASE_PART Part ON 
			   b.CD_EMP	  = part.CD_EMP AND 
			   b.CD_FILIAL	  = part.CD_FILIAL AND 
			   b.CD_PROD	  = part.CD_PROD

			   INNER JOIN( SELECT CD_FILIAL
						   , SUM(QT_TOT_PART) QT_TOT_VD_FILIAL
						   , SUM(VLR_TOT_PART) VLR_TOT_VD_FILIAL
					   FROM #TBL_BASE_PART b
					   GROUP BY CD_FILIAL
				    ) LJ ON b.CD_FILIAL = LJ.CD_FILIAL


			   LEFT 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
		   WHERE b.CD_EMP = @CD_EMP;



		   PRINT 'Carrega os produtos nao vendidos  -  Hora : '+CONVERT(VARCHAR, GETDATE(), 108);
		   INSERT INTO EST_PROD_CPL_ANALISE(
				CD_EMP
			   , CD_FILIAL
			   , CD_PROD
			   , MES
			   , ANO
			   , QTDE_TOTAL
			   , PART_QTDE
			   , VLR_VD_TOTAL
			   , PART_VLR
			   , VLR_MEDIA_ITEN
			   , VLR_TABELA
			   , VLR_OFERTA
			   , PERC_DESC
			   , QT_DIAS_FALTA
			   , QT_DIAS_CALCULO
			   , DT_CALCULO_DIARIO
			   , FLAG_PART_INDIC)
		   SELECT
				pcl.CD_EMP
			   , pcl.CD_FILIAL
			   , pcl.CD_PROD
			   , @MES AS MES
			   , @ANO AS ANO
			   , 0 QTDE_TOTAL
			   , 0 PART_QTDE
			   , 0 VLR_VD_TOTAL
			   , 0 PART_VLR
			   , 0 VLR_MEDIA_ITEN
			   , isnull(r.VLR_TABELA, 0)
			   , isnull(r.VLR_OFERTA, 0)
			   , isnull(p.PERC_DESC, 0)
			   , 0 QT_DIAS_FALTA
			   , 0 QT_DIAS_CALCULO
			   , NULL DT_CALCULO_DIARIO
			   , isnull(p.FLAG_ANALISE_FALTAS, 0)
		   FROM
			   EST_PROD p
			   INNER JOIN EST_PROD_CPL pcl
			   ON p.CD_EMP = pcl.CD_EMP
				 AND p.CD_PROD = pcl.CD_PROD
			   LEFT JOIN EST_PROD_CPL_ANALISE A
			   ON pcl.CD_EMP = A.CD_EMP
				 AND pcl.CD_FILIAL = A.CD_FILIAL
				 AND pcl.CD_PROD = A.CD_PROD
				 AND ANO = @ANO
				 AND MES = @MES
			   LEFT JOIN EST_PROD_PRECO r
			   ON pcl.CD_EMP = r.CD_EMP
				 AND pcl.CD_FILIAL = r.CD_FILIAL
				 AND pcl.CD_PROD = r.CD_PROD
		   WHERE PCL.CD_EMP = @CD_EMP
			    AND pcl.CD_FILIAL = @CD_FILIAL_DEPO
			    AND A.CD_EMP IS NULL
			    AND p.STS_PROD = 0;
		   PRINT 'Fim da rotina  -  Hora : '+CONVERT(VARCHAR, GETDATE(), 108);
	    END;