IF EXISTS( SELECT * FROM SYS.SYSOBJECTS
				WHERE  NAME = 'P_ANALISE_VENDA_PRODUTOS_MENSAL_FILIAL' AND TYPE = N'P')
	BEGIN
		DROP PROCEDURE P_ANALISE_VENDA_PRODUTOS_MENSAL_FILIAL;
	END;
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO 


-- execute P_ANALISE_VENDA_PRODUTOS_MENSAL_FILIAL  1,2, 2013, 8
CREATE PROCEDURE [dbo].[P_ANALISE_VENDA_PRODUTOS_MENSAL_FILIAL] @CD_EMP    INT = 1
															  , @CD_FILIAL INT
															  , @ANO       INT = NULL
															  , @MES       INT = NULL
															  , @MES_ATUAL INT = 0
AS
	 BEGIN
		 IF @MES_ATUAL IS NULL
			OR @MES_ATUAL NOT IN(0, 1)
			 BEGIN
				 RAISERROR('Parâmetro @MES_ATUAL deve ter valor 0 (não) ou 1 (sim).', 16, 1);
				 RETURN -1;
			 END;
		 IF @ANO IS NOT NULL
			AND @MES IS NULL
			OR @ANO IS NULL
			AND @MES IS NOT NULL
			 BEGIN
				 RAISERROR('Parâmetros @MES e @ANO devem estar ambor preenchidos ou ambos em branco.', 16, 1);
				 RETURN -1;
			 END;
	
		 DECLARE @dtBase DATETIME
			   , @dtINI  DATETIME
			   , @dtFIM  DATETIME;
	
		 IF @ANO IS NULL --Mês atual: 1-1=0; mês anterior 0-1=-1
		 SELECT
				@dtBase = DATEADD(mm, @MES_ATUAL - 1, GETDATE())
			  , @ANO = YEAR(@dtBase)
			  , @MES = MONTH(@dtBase);
		 PRINT 'Processamento movimentação Mensal    '+CONVERT(CHAR(4), @ANO)+'/'+CONVERT(CHAR(5), @MES)+CONVERT(VARCHAR, GETDATE(), 108);
		 SET @dtINI = CONVERT(VARCHAR, (@ANO * 100) + @MES)+'01';
		 SET @dtFIM = DATEADD(DAY, -1, (DATEADD(MONTH, 1, @dtINI)));

		/*
		--Limpa os dados já existentes para mês (Atualização)
		IF EXISTS (SELECT TOP 1 CD_EMP FROM EST_PROD_CPL_ANALISE 
					WHERE 
							ANO=@ANO 
							AND MES=@MES
							AND CD_EMP=@CD_EMP
							AND CD_FILIAL = @CD_FILIAL)
		BEGIN
				DELETE a 
				FROM EST_PROD_CPL_ANALISE a
				WHERE 
					ANO=@ANO 
					AND MES=@MES 
					AND CD_EMP=@CD_EMP
					AND CD_FILIAL =@CD_FILIAL;
		END	  
		*/

		 BEGIN TRY
			 DROP TABLE #Tmp_EST_PROD_CPL_ANALISE;
		 END TRY
		 BEGIN CATCH
		 END CATCH;

		 CREATE TABLE #Tmp_EST_PROD_CPL_ANALISE(  CD_EMP            INT
												, CD_FILIAL         INT
												, CD_PROD           INT
												, MES               INT
												, ANO               INT
												, QTDE_TOTAL        MONEY
												, PART_QTDE         FLOAT
												, VLR_VD_TOTAL      MONEY
												, PART_VLR          FLOAT
												, VLR_MEDIA_ITEN    MONEY
												, VLR_TABELA        MONEY
												, VLR_OFERTA        MONEY
												, PERC_DESC         MONEY
												, ANO_MES           INT
												, QT_DIAS_FALTA     INT
												, QT_DIAS_CALCULO   INT
												, DT_CALCULO_DIARIO DATETIME
												, FLAG_PART_INDIC   INT
												);
		 PRINT 'insert 1 '+CONVERT(VARCHAR, GETDATE(), 108);
		
		--Produtos vendidos
		 ;WITH BASE
			  AS (
			  SELECT
					 CD_EMP
				   , CD_FILIAL
				   , CD_PROD
				   , YEAR(DT_VD) AS ANO
				   , MONTH(DT_VD) AS MES
				   , CONVERT(FLOAT, SUM(V_PDV_VD_IT.QT_IT - isnull(imp.qt_it,0))) AS QT_TOT
				   , CONVERT(FLOAT, SUM(TOT_IT)) AS VLR_TOT
				   , CASE
						 WHEN SUM(V_PDV_VD_IT.QT_IT) > 0 THEN SUM(TOT_IT) / SUM(V_PDV_VD_IT.QT_IT)
						 ELSE 0
					 END AS MEDIA
			  FROM
				  V_PDV_VD_IT
				  OUTER APPLY (SELECT SUM(IMP.QT_IT) AS QT_IT
						 FROM PDV_VD_DEV_EST_PROD_IMP IMP
						 WHERE IMP.CD_EMP = V_PDV_VD_IT.CD_EMP
						 AND IMP.CD_FILIAL = V_PDV_VD_IT.CD_FILIAL
						 AND IMP.CD_PROD = V_PDV_VD_IT.CD_PROD
						 AND IMP.CD_VD = V_PDV_VD_IT.CD_VD) IMP
			  WHERE  CD_EMP = @CD_EMP
					 AND CD_FILIAL = @CD_FILIAL
					 AND DT_VD BETWEEN @dtINI AND @dtFIM
			  GROUP BY
					   CD_EMP
					 , CD_FILIAL
					 , CD_PROD
					 , YEAR(DT_VD)
					 , MONTH(DT_VD))

			  INSERT INTO  #Tmp_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
				   , FLAG_PART_INDIC)
			  SELECT
					 b.CD_EMP
				   , b.CD_FILIAL
				   , b.CD_PROD
				   , b.MES
				   , b.ANO
				   , ISNULL(VLR_TABELA, 0)
				   , ISNULL(VLR_OFERTA, 0)
				   , PERC_DESC
				   , ISNULL(b.QT_TOT, 0) AS VLR_QTDE_TOTAL
				   , ISNULL(ROUND((b.QT_TOT / SUM(b.QT_TOT) OVER(PARTITION BY b.CD_FILIAL)) * 100, 8), 0) AS PERC_QTDE
				   , ISNULL(ROUND(b.VLR_TOT, 2), 0) AS VLR_VD_TOTAL
				   , ISNULL(ROUND((b.VLR_TOT / SUM(b.VLR_TOT) OVER(PARTITION BY b.CD_FILIAL)) * 100, 8), 0) AS PERC_VLR
				   , ISNULL(ROUND(b.MEDIA, 2), 0) AS VLR_MEDIA_ITEN
				   , ISNULL(p.FLAG_ANALISE_FALTAS, 0)
			  FROM
				  BASE 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
				  LEFT OUTER 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  pcl.CD_EMP = @CD_EMP
					 AND pcl.CD_FILIAL = @CD_FILIAL
					 AND b.ANO = @ANO
					 AND b.MES = @MES;
		
		 PRINT 'insert 2 '+CONVERT(VARCHAR, GETDATE(), 108);
		--Produtos não vendidos
		 INSERT INTO  #Tmp_EST_PROD_CPL_ANALISE(CD_EMP
													, CD_FILIAL
													, CD_PROD
													, MES
													, ANO
													, QTDE_TOTAL
													, PART_QTDE
													, PART_VLR
													, VLR_VD_TOTAL
													, VLR_TABELA
													, VLR_OFERTA
													, PERC_DESC
													, VLR_MEDIA_ITEN
													, 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 PART_VLR
			  , 0 VLR_VD_TOTAL
			  , ISNULL(r.VLR_TABELA, 0)
			  , ISNULL(r.VLR_OFERTA, 0)
			  , p.PERC_DESC
			  , 0 VLR_MEDIA_ITEN
			  , p.FLAG_ANALISE_FALTAS
		 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 OUTER 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
			 LEFT JOIN #Tmp_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 A.ANO = @ANO
													  AND A.MES = @MES
		 WHERE  PCL.CD_EMP = @CD_EMP
				AND pcl.CD_FILIAL = @CD_FILIAL
				AND pcl.TP_PROD IN(0, 8, 9)
				AND A.CD_EMP IS NULL;
		 PRINT 'CREATE 3 '+CONVERT(VARCHAR, GETDATE(), 108);

		 CREATE NONCLUSTERED INDEX IX_Tmp_EST_PROD_CPL_ANALISE ON #Tmp_EST_PROD_CPL_ANALISE (CD_EMP, CD_FILIAL, CD_PROD, ANO, MES);
		
		
/**********************************************************************************************************************************/
--Atualiza os dados ja existentes
/**********************************************************************************************************************************/

		 PRINT ' update 4 '+CONVERT(VARCHAR, GETDATE(), 108);

		 UPDATE b
		   SET
			   QTDE_TOTAL = a.QTDE_TOTAL
			 , PART_QTDE = a.PART_QTDE
			 , PART_VLR = a.PART_VLR
			 , VLR_VD_TOTAL = a.VLR_VD_TOTAL
			 , VLR_TABELA = a.VLR_TABELA
			 , VLR_OFERTA = a.VLR_OFERTA
			 , PERC_DESC = a.PERC_DESC
			 , VLR_MEDIA_ITEN = a.VLR_MEDIA_ITEN
			 , FLAG_PART_INDIC = a.FLAG_PART_INDIC
		 FROM   #Tmp_EST_PROD_CPL_ANALISE a
				JOIN EST_PROD_CPL_ANALISE b ON a.CD_EMP = b.CD_EMP
											   AND a.CD_FILIAL = b.CD_FILIAL
											   AND a.CD_PROD = b.CD_PROD
											   AND A.ANO = b.ANO
											   AND A.MES = b.MES
											   
		where
			    b.CD_EMP = @CD_EMP
			AND b.CD_FILIAL = @CD_FILIAL
			AND b.ANO = @ANO
			AND b.MES = @MES;

/**********************************************************************************************************************************/ 
--insere novos registros
/**********************************************************************************************************************************/

		 PRINT ' Insert 5 '+CONVERT(VARCHAR, GETDATE(), 108);
		 INSERT INTO                     EST_PROD_CPL_ANALISE(
				CD_EMP
			  , CD_FILIAL
			  , CD_PROD
			  , MES
			  , ANO
			  , QTDE_TOTAL
			  , PART_QTDE
			  , PART_VLR
			  , VLR_VD_TOTAL
			  , VLR_TABELA
			  , VLR_OFERTA
			  , PERC_DESC
			  , VLR_MEDIA_ITEN
			  , FLAG_PART_INDIC)
		 SELECT
				a.CD_EMP
			  , a.CD_FILIAL
			  , a.CD_PROD
			  , a.MES
			  , a.ANO
			  , a.QTDE_TOTAL
			  , a.PART_QTDE
			  , a.PART_VLR
			  , a.VLR_VD_TOTAL
			  , a.VLR_TABELA
			  , a.VLR_OFERTA
			  , a.PERC_DESC
			  , a.VLR_MEDIA_ITEN
			  , a.FLAG_PART_INDIC
		 FROM
			 #Tmp_EST_PROD_CPL_ANALISE a
			 LEFT JOIN EST_PROD_CPL_ANALISE b ON a.CD_EMP = b.CD_EMP
												 AND a.CD_FILIAL = b.CD_FILIAL
												 AND a.CD_PROD = b.CD_PROD
												 AND A.ANO = b.ANO
												 AND A.MES = b.MES
		 WHERE  b.cd_emp IS NULL;
	 END;

/**********************************************************************************************************************************/

GO


