
IF EXISTS
(
    SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[P_ANALISE_VENDA_PRODUTOS_MENSAL]')
          AND type IN(N'P', N'PC')
)
BEGIN
	DROP PROCEDURE [dbo].[P_ANALISE_VENDA_PRODUTOS_MENSAL];
END;
GO

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

--execute P_ANALISE_VENDA_PRODUTOS_MENSAL 1, 2020, 10  
CREATE PROCEDURE [dbo].[P_ANALISE_VENDA_PRODUTOS_MENSAL] @CD_EMP    INT = 1,
                                                         @ANO       INT = NULL,
                                                         @MES       INT = NULL,
                                                         @MES_ATUAL INT = 0
AS
     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;
     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);  
--Limpa os dados já existentes para mês (Atualização)  
     IF EXISTS
     (
         SELECT *
         FROM EST_PROD_CPL_ANALISE
         WHERE ANO = @ANO
               AND MES = @MES
     )
         DELETE a
         FROM EST_PROD_CPL_ANALISE a
         WHERE ANO = @ANO
               AND MES = @MES
               AND NOT EXISTS
         (
             SELECT CD_FILIAL
             FROM PRC_FILIAL
             WHERE FILIAL_MTZ = 1
                   AND CD_FILIAL = a.CD_FILIAL
         );
     PRINT 'Processamento movimentação diária dia  '+CONVERT(CHAR(4), @ANO)+'/'+CONVERT(CHAR(5), @MES);  
--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 YEAR(DT_VD) = @ANO
                AND MONTH(DT_VD) = @MES
				 AND NOT EXISTS
         (
             SELECT CD_FILIAL
             FROM PRC_FILIAL
             WHERE FILIAL_MTZ = 1
                   AND CD_FILIAL = V_PDV_VD_IT.CD_FILIAL
         )
          GROUP BY CD_EMP,
                   CD_FILIAL,
                   CD_PROD,
                   YEAR(DT_VD),
                   MONTH(DT_VD))
          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,
           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,
                        b.QT_TOT AS VLR_QTDE_TOTAL,
                        ROUND((b.QT_TOT / SUM(b.QT_TOT) OVER(PARTITION BY b.CD_FILIAL)) * 100, 8) AS PERC_QTDE,
                        ROUND(b.VLR_TOT, 2) AS VLR_VD_TOTAL,
                        ROUND((b.VLR_TOT / SUM(b.VLR_TOT) OVER(PARTITION BY b.CD_FILIAL)) * 100, 8) AS PERC_VLR,
                        ROUND(b.MEDIA, 2) AS VLR_MEDIA_ITEN,
                        p.FLAG_ANALISE_FALTAS
                 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 b.CD_EMP = @CD_EMP
                       AND b.ANO = @ANO
                       AND b.MES = @MES;  
  
--Produtos não vendidos  
     INSERT INTO EST_PROD_CPL_ANALISE
     (CD_EMP,
      CD_FILIAL,
      CD_PROD,
      MES,
      ANO,
      VLR_TABELA,
      VLR_OFERTA,
      PERC_DESC,
      FLAG_PART_INDIC,
      QTDE_TOTAL,
      VLR_VD_TOTAL,
      PART_QTDE,
      PART_VLR,
      VLR_MEDIA_ITEN
     )
            SELECT pcl.CD_EMP,
                   pcl.CD_FILIAL,
                   pcl.CD_PROD,
                   @MES AS MES,
                   @ANO AS ANO,
                   ISNULL(r.VLR_TABELA, 0),
                   ISNULL(r.VLR_OFERTA, 0),
                   p.PERC_DESC,
                   p.FLAG_ANALISE_FALTAS,
                   0,
                   0,
                   0,
                   0,
                   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 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
            WHERE p.STS_PROD = 0
              AND pcl.TP_PROD IN(0, 8, 9)
              AND NOT EXISTS (
							  SELECT CD_FILIAL
								FROM PRC_FILIAL
							   WHERE FILIAL_MTZ = 1
								 AND CD_FILIAL = pcl.CD_FILIAL
							 ) 

			 AND NOT EXISTS (
							  SELECT *
							    FROM EST_PROD_CPL_ANALISE
							   WHERE pcl.CD_EMP = CD_EMP
							  	 AND pcl.CD_FILIAL = CD_FILIAL
							  	 AND pcl.CD_PROD = CD_PROD
							  	 AND ANO = @ANO
							  	 AND MES = @MES
							);
GO


