IF EXISTS
(
	SELECT *
	FROM sys.objects
	WHERE object_id = OBJECT_ID(N'P_CTB_CNT_DRE_CONSLD_VD_MES')
		  AND type IN(N'P', N'PC')
)
	DROP PROCEDURE P_CTB_CNT_DRE_CONSLD_VD_MES;
GO
GO
--EXEC P_CTB_CNT_DRE_CONSLD_VD_MES 1,0,2017,5
CREATE PROCEDURE P_CTB_CNT_DRE_CONSLD_VD_MES(@CD_EMP    INT = NULL
										   , @CD_FILIAL INT = NULL
										   , @ANO       INT = NULL
										   , @MES       INT = NULL)
AS
SET NOCOUNT ON;
	BEGIN TRY
		BEGIN
			--	DECLARE @CD_EMP INT
			--	DECLARE @CD_FILIAL INT
			--	DECLARE @ANO INT 
			--	DECLARE @MES INT
			--	SET @CD_EMP = 1
			--        SET @CD_FILIAL = NULL
			--	SET @ANO = NULL
			--	SET @MES = NULL

			DECLARE @DT_INI DATETIME;
			DECLARE @DT_FIM DATETIME; 
			DECLARE  @AGORA DATETIME;

			IF @CD_EMP IS NULL
				BEGIN
					SET @CD_EMP = 1;
				END;

			IF @ANO IS NULL
			   OR @MES IS NULL
				BEGIN
					SELECT @AGORA = DATEADD(MONTH, -1, GETDATE())
						 , @ANO = YEAR(@AGORA)
						 , @MES = MONTH(@AGORA);
				END;

			SET @DT_INI =
			(
				SELECT CONVERT( DATETIME, CAST(@ANO AS VARCHAR)+'-'+CAST(@MES AS VARCHAR)+'-01')
			);
			SET @DT_FIM = DATEADD(MONTH, 1, @DT_INI) - 1;


			CREATE TABLE #TBL_CARTAO
			(CD_EMP             INT
		   , CD_FILIAL          INT
		   , ANO_VD             INT
		   , MES_VD             INT
		   , VLR_CARTAO         MONEY
		   , VLR_TX_CARTAO      MONEY
		   , VLR_SAQUE_CARTAO   MONEY
		   , VLR_CARTAO_CRED    MONEY
		   , VLR_CARTAO_DEB     MONEY
		   , VLR_TX_CARTAO_CRED MONEY
		   , VLR_TX_CARTAO_DEB  MONEY
			);

			CREATE TABLE #TBL_DEV
			(CD_EMP        INT
		   , CD_FILIAL     INT
		   , ANO_DEV       INT
		   , MES_DEV       INT
		   , VLR_DEV       MONEY
		   , VLR_DEV_CUSTO MONEY
		   , VLR_COMIS_DEV MONEY
		   ,VLR_MARGEM_DEV MONEY
			);
								

			------------------------------------------------------------------------------------------------------------------------
			-- APAGANDO CONSOLIDAÇÃO DAS VENDAS NO MÊS, CASO EXISTA.								
			------------------------------------------------------------------------------------------------------------------------
			PRINT 'P_CTB_CNT_DRE_CONSLD_VD_MES - DELETE 1 '+CONVERT(VARCHAR, GETDATE(), 108);	
			DELETE FROM CTB_CNT_DRE_CONSLD_VD_MES
			WHERE CD_EMP = @CD_EMP
				  AND CD_FILIAL = (CASE
									   WHEN ISNULL(@CD_FILIAL, 0) > 0
									   THEN @CD_FILIAL
									   ELSE CD_FILIAL
								   END)
				  AND ANO_VD = @ANO
				  AND MES_VD = @MES;


			------------------------------------------------------------------------------------------------------------------------
	   -- GRAVANDO A CONSOLIDAÇÃO DAS VENDAS DO MÊS								
	   ------------------------------------------------------------------------------------------------------------------------
	   PRINT 'INSERT 2 '+CONVERT(VARCHAR, GETDATE(), 108);
	   INSERT INTO CTB_CNT_DRE_CONSLD_VD_MES
	   (CD_EMP		   , CD_FILIAL		   , ANO_VD		   , MES_VD		   , VLR_PMC
		  , VLR_LIQ_VD		   , VLR_VD		   , MARGEM_BRUTA		   , VLR_VD_CUSTO		   , VLR_DESC_ITENS
		  , VLR_DESC_OFERTA		   , VLR_DESC_RATEIO		   , VLR_DESC_VERBA		   , VLR_DINH
		  , VLR_CHQS		   , VLR_CARTAO		   , VLR_CARTAO_CRED		   , VLR_CARTAO_DEB
		  , VLR_TX_CARTAO		   , VLR_TX_CARTAO_CRED		   , VLR_TX_CARTAO_DEB		   , VLR_TICKETS
		  , VLR_DUP		   , VLR_CONV		   , VLR_TROCO_CHQ		   , VLR_COMIS		   , VLR_IMPOSTO
		  ,VLR_VD_FRETE		, VLR_VD_OUTROS		,VLR_VD_SEGURO)	
		SELECT
			V.CD_EMP,
			V.CD_FILIAL,
			D.ANO_MOVIMENTO,
			D.MES_MOVIMENTO,
			SUM(p.VLR_PMC) AS VLR_PMC,
			SUM(p.VLR_VD_LIQ) AS VLR_LIQ_VD,
			SUM(p.VLR_VD) AS VLR_VD,
			SUM(p.VLR_VD_LIQ - p.VLR_CUSTO_MD) AS MARGEM_BRUTA,
			SUM(p.VLR_CUSTO_MD) AS VLR_VD_CUSTO,
			SUM(p.VLR_DESC_ITENS) AS VLR_DESC_ITENS,
			SUM(p.VLR_DESC_OFERTA) AS VLR_DESC_OFERTA,
			SUM(p.VLR_DESC_RATEIO)  AS VLR_DESC_RATEIO,
			SUM(p.VLR_DESC_VERBA) AS VLR_DESC_VERBA,
			SUM(p.VLR_DINHEIRO) AS VLR_DINHEIRO,
			SUM(p.VLR_CHEQUES) AS VLR_CHEQUES,
			0 AS VLR_CARTAO,
			0 AS VLR_CARTAO_CRED,
			0 AS VLR_CARTAO_DEB,
			0 AS VLR_TX_CARTAO,
			0 AS VLR_TX_CARTAO_CRED,
			0 AS VLR_TX_CARTAO_DEB,
			SUM(p.VLR_TICKETS) AS VLR_TICKETS,
			SUM(p.VLR_DUPLICATA) AS VLR_DUP,
			SUM(p.VLR_CONV) AS VLR_CONV,
			SUM(p.VLR_CHEQUE_TROCO) AS VLR_TROCO_CHQ,
			SUM(p.VLR_COMISSAO_FABRIC+p.VLR_COMISSAO) AS VLR_COMIS,
			SUM(p.VLR_ICMS+p.VLR_PIS_COFINS) AS VLR_IMPOSTO,
			SUM(ISNULL(p.VLR_PROD_FRETE,0)) AS VLR_PROD_FRETE,
			SUM(ISNULL(p.VLR_OUTROS_DESPESAS,0)) AS VLR_OUTROS_DESPESAS,
			SUM(ISNULL(p.VLR_SEGURO,0)) AS VLR_SEGURO
		FROM
			EST_MOVIMENTO_VENDAS_PRODUTOS P INNER JOIN EST_MOVIMENTO_VENDAS V ON
			P.CD_CTR_VENDAS = V.CD_CTR_VENDAS 
			INNER JOIN GLB_DT_MOVIMENTO D ON
			V.CD_CTR_DT_MOVIMENTO = D.CD_CTR_DT_MOVIMENTO
			INNER JOIN V_BI_MOVIMENTO_TIPO T ON
			V.TIPO_VENDA = T.TIPOMOVIMENTO
		WHERE
			V.CD_EMP =@CD_EMP
			AND V.CD_FILIAL =  (CASE WHEN ISNULL(@CD_FILIAL, 0) > 0
												THEN @CD_FILIAL
												ELSE V.CD_FILIAL
											END)
			AND D.ANO_MOVIMENTO = YEAR(@DT_INI)
			AND D.MES_MOVIMENTO = MONTH(@DT_INI)
			AND V.TIPO_VENDA IN (0,2,10)
		GROUP BY
			V.CD_EMP,
			V.CD_FILIAL,
			D.ANO_MOVIMENTO,
			D.MES_MOVIMENTO

    ------------------------------------------------------------------------------------------------------------------------
    -- CONSULTANDO DEVOLUÇÕES								
    ------------------------------------------------------------------------------------------------------------------------
		  PRINT 'INSERT 3 '+CONVERT(VARCHAR, GETDATE(), 108);
			 INSERT INTO #TBL_DEV
			 SELECT
				    V.CD_EMP,
				    V.CD_FILIAL,
				    D.ANO_MOVIMENTO,
				    D.MES_MOVIMENTO,
				    SUM(VLR_VD_LIQ)*-1 AS VLR_LIQ_VD,
				    SUM(VLR_CUSTO_MD)*-1 AS VLR_VD_CUSTO,
				    SUM(p.VLR_COMISSAO_FABRIC+p.VLR_COMISSAO)*-1 AS VLR_COMIS,
				    SUM(p.VLR_VD_LIQ - p.VLR_CUSTO_MD)*-1 AS MARGEM_BRUTA
			 FROM
				    EST_MOVIMENTO_VENDAS_PRODUTOS P INNER JOIN EST_MOVIMENTO_VENDAS V ON
				    P.CD_CTR_VENDAS = V.CD_CTR_VENDAS 
				    INNER JOIN GLB_DT_MOVIMENTO D ON
				    V.CD_CTR_DT_MOVIMENTO = D.CD_CTR_DT_MOVIMENTO
				    INNER JOIN V_BI_MOVIMENTO_TIPO T ON
				    V.TIPO_VENDA = T.TIPOMOVIMENTO
			 WHERE
				    V.CD_EMP =@CD_EMP
				    AND V.CD_FILIAL =  (CASE WHEN ISNULL(@CD_FILIAL, 0) > 0 THEN @CD_FILIAL 
								    ELSE V.CD_FILIAL END)
				    AND D.ANO_MOVIMENTO = YEAR(@DT_INI)
				    AND D.MES_MOVIMENTO = MONTH(@DT_INI)
				    AND V.TIPO_VENDA in (3,11)
			 GROUP BY
				    V.CD_EMP,
				    V.CD_FILIAL,
				    D.ANO_MOVIMENTO,
				    D.MES_MOVIMENTO;
    ------------------------------------------------------------------------------------------------------------------------
    -- GRAVANDO DEVOLUÇÕES NA TABELA DE CONSOLIDAÇÃO
    ------------------------------------------------------------------------------------------------------------------------
			PRINT 'UPDATE 4 '+CONVERT(VARCHAR, GETDATE(), 108);
			UPDATE DRE
			  SET
				  VLR_DEV = ROUND(D.VLR_DEV, 2)
				, VLR_DEV_CUSTO = ROUND(D.VLR_DEV_CUSTO, 2)
				, VLR_COMIS_DEV = ROUND(D.VLR_COMIS_DEV, 2)
				,MARGEM_BRUTA_DEV=VLR_MARGEM_DEV
			FROM 
				CTB_CNT_DRE_CONSLD_VD_MES DRE
				INNER JOIN #TBL_DEV D ON 
				DRE.CD_EMP = D.CD_EMP AND 
				DRE.CD_FILIAL = D.CD_FILIAL AND 
				DRE.ANO_VD = D.ANO_DEV AND 
				DRE.MES_VD = D.MES_DEV;
	
    ------------------------------------------------------------------------------------------------------------------------
    -- CONSULTANDO RECEBIMENTOS EM CARTÕES NO MÊS								
    ------------------------------------------------------------------------------------------------------------------------
			PRINT 'INSERT 5 '+CONVERT(VARCHAR, GETDATE(), 108);
			INSERT INTO #TBL_CARTAO
			(CD_EMP , CD_FILIAL , ANO_VD , MES_VD, VLR_CARTAO		   , VLR_TX_CARTAO 
				, VLR_SAQUE_CARTAO, VLR_CARTAO_CRED, VLR_CARTAO_DEB, VLR_TX_CARTAO_CRED, VLR_TX_CARTAO_DEB)
				   SELECT
						CD_EMP
						, CD_FILIAL
						, ANO_VD
						, MES_VD
						, SUM(VLR_CARTAO)
						, SUM(VLR_TX_CARTAO)
						, SUM(VLR_SAQUE_CARTAO)
						, SUM(VLR_CARTAO_CRED)
						, SUM(VLR_CARTAO_DEB)
						, SUM(VLR_TX_CARTAO_CRED)
						, SUM(VLR_TX_CARTAO_DEB)
					FROM
				   (SELECT 
						PDV_VD.CD_EMP
						, PDV_VD.CD_FILIAL
						, YEAR(PDV_VD.DT_VD) AS ANO_VD
						, MONTH(PDV_VD.DT_VD) AS MES_VD
						, PDV_VD_TEF.VL_CARTAO AS VLR_CARTAO
						, ((PDV_VD_TEF.VL_CARTAO * ISNULL(V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.PERC_TAXA, 0)) / 100.00) AS VLR_TX_CARTAO
						, ((PDV_VD_TEF.VL_SAQUE_CARTAO - ((PDV_VD_TEF.VL_SAQUE_CARTAO * ISNULL(V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.PERC_TAXA, 0)))) / 100.00) AS VLR_SAQUE_CARTAO
						, (CASE WHEN RC_ADM_CARTAO.TP_ADM IN(0, 2) THEN 
								PDV_VD_TEF.VL_CARTAO
							ELSE 
								0
							 END) VLR_CARTAO_CRED
						, (CASE WHEN RC_ADM_CARTAO.TP_ADM IN(1, 3) THEN 
								PDV_VD_TEF.VL_CARTAO
							ELSE 
								0
							  END) VLR_CARTAO_DEB
						, (CASE WHEN RC_ADM_CARTAO.TP_ADM IN(0, 2) THEN
								(PDV_VD_TEF.VL_CARTAO * ISNULL(V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.PERC_TAXA, 0)) / 100.00
							ELSE 
								0
							END) VLR_TX_CARTAO_CRED
						, (CASE
								  WHEN RC_ADM_CARTAO.TP_ADM IN(1, 3)
								  THEN(PDV_VD_TEF.VL_CARTAO * ISNULL(V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.PERC_TAXA, 0)) / 100.00
								  ELSE 0
							  END) VLR_TX_CARTAO_DEB
				   FROM PDV_VD
						INNER JOIN PDV_VD_TEF ON 
						PDV_VD.CD_EMP = PDV_VD_TEF.CD_EMP AND 
						PDV_VD.CD_FILIAL = PDV_VD_TEF.CD_FILIAL AND 
						PDV_VD.CD_VD = PDV_VD_TEF.CD_VD
						INNER JOIN RC_ADM_CARTAO ON 
						PDV_VD_TEF.CD_EMP = RC_ADM_CARTAO.CD_EMP AND 
						PDV_VD_TEF.CD_ADM_CARTAO = RC_ADM_CARTAO.CD_ADM_CARTAO
						LEFT OUTER JOIN V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS ON 
						PDV_VD.CD_EMP = V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.CD_EMP AND 
						PDV_VD.CD_FILIAL = V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.CD_FILIAL AND 
						PDV_VD_TEF.CD_ADM_CARTAO = V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.CD_ADM_CARTAO
				   WHERE 
					PDV_VD.CD_EMP = @CD_EMP
					AND PDV_VD.CD_FILIAL = (CASE WHEN ISNULL(@CD_FILIAL, 0) > 0 THEN 
												@CD_FILIAL
											ELSE 
												PDV_VD.CD_FILIAL
											END)
						 AND PDV_VD.DT_VD BETWEEN @DT_INI AND @DT_FIM
						 AND PDV_VD_TEF.VL_CARTAO > 0
						 AND PDV_VD.ST_VD = 0
						 AND ISNULL(PDV_VD_TEF.CANCEL_TEF, 0) <> 1
						 AND NOT EXISTS ( SELECT  CANCEL.CD_EMP
										  FROM  PDV_VD_CANCEL_TEF CANCEL
									   WHERE 
										CANCEL.CD_EMP = PDV_VD.CD_EMP
										AND CANCEL.CD_FILIAL = PDV_VD.CD_FILIAL
										AND CANCEL.CD_VD = PDV_VD.CD_VD )
				UNION ALL
				   SELECT PDV_CUPOM_NAO_FISCAL.CD_EMP
						, PDV_CUPOM_NAO_FISCAL.CD_FILIAL
						, YEAR(PDV_CUPOM_NAO_FISCAL.DT_CUPOM) AS ANO_VD
						, MONTH(PDV_CUPOM_NAO_FISCAL.DT_CUPOM) AS MES_VD
						, (PDV_VD_TEF.VL_CARTAO) AS VLR_CARTAO
						, ((PDV_VD_TEF.VL_CARTAO * ISNULL(V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.PERC_TAXA, 0)) / 100.00) AS VLR_TX_CARTAO
						, ((PDV_VD_TEF.VL_SAQUE_CARTAO - ((PDV_VD_TEF.VL_SAQUE_CARTAO * ISNULL(V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.PERC_TAXA, 0)))) / 100.00) AS VLR_SAQUE_CARTAO
						, (CASE WHEN RC_ADM_CARTAO.TP_ADM IN(0, 2) THEN 
								PDV_VD_TEF.VL_CARTAO
							ELSE 
								0
							END) VLR_CARTAO_CRED
						, (CASE WHEN RC_ADM_CARTAO.TP_ADM IN(1, 3) THEN 
								PDV_VD_TEF.VL_CARTAO
							ELSE 
								0
							END) VLR_CARTAO_DEB
						, (CASE WHEN RC_ADM_CARTAO.TP_ADM IN(0, 2) THEN
								(PDV_VD_TEF.VL_CARTAO * ISNULL(V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.PERC_TAXA, 0)) / 100.00
							ELSE 
								0
							END) VLR_TX_CARTAO_CRED
						, (CASE WHEN RC_ADM_CARTAO.TP_ADM IN(1, 3) THEN
								(PDV_VD_TEF.VL_CARTAO * ISNULL(V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.PERC_TAXA, 0)) / 100.00
							ELSE 
								0
							END) VLR_TX_CARTAO_DEB
				   FROM 
					PDV_CUPOM_NAO_FISCAL INNER JOIN PDV_CUPOM_NAO_FISCAL_TEF ON 
					PDV_CUPOM_NAO_FISCAL.CD_EMP = PDV_CUPOM_NAO_FISCAL_TEF.CD_EMP AND 
					PDV_CUPOM_NAO_FISCAL.CD_FILIAL = PDV_CUPOM_NAO_FISCAL_TEF.CD_FILIAL AND 
					PDV_CUPOM_NAO_FISCAL.CD_CTR = PDV_CUPOM_NAO_FISCAL_TEF.CD_CTR				
					INNER JOIN PDV_RECBTO_TEF PDV_VD_TEF ON 
					PDV_VD_TEF.CD_EMP = PDV_CUPOM_NAO_FISCAL_TEF.CD_EMP AND
					PDV_VD_TEF.CD_FILIAL = PDV_CUPOM_NAO_FISCAL_TEF.CD_FILIAL AND
					PDV_VD_TEF.CD_RECTO_TEF = PDV_CUPOM_NAO_FISCAL_TEF.CD_RECTO_TEF
					INNER JOIN RC_ADM_CARTAO ON 
					PDV_VD_TEF.CD_EMP = RC_ADM_CARTAO.CD_EMP AND 
					PDV_VD_TEF.CD_ADM_CARTAO = RC_ADM_CARTAO.CD_ADM_CARTAO
					LEFT OUTER JOIN V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS ON 
					PDV_CUPOM_NAO_FISCAL.CD_EMP = V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.CD_EMP AND 
					PDV_CUPOM_NAO_FISCAL.CD_FILIAL = V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.CD_FILIAL AND 
					PDV_VD_TEF.CD_ADM_CARTAO = V_RC_ADM_CARTAO_PRC_GRP_ECON_TAXAS.CD_ADM_CARTAO
				   WHERE PDV_CUPOM_NAO_FISCAL.CD_EMP = @CD_EMP
						 AND PDV_CUPOM_NAO_FISCAL.CD_FILIAL = (CASE WHEN ISNULL(@CD_FILIAL, 0) > 0 THEN 
																	@CD_FILIAL
																ELSE 
																	PDV_CUPOM_NAO_FISCAL.CD_FILIAL
																END)
						 AND PDV_CUPOM_NAO_FISCAL.DT_CUPOM BETWEEN @DT_INI AND @DT_FIM
						 AND PDV_VD_TEF.VL_CARTAO > 0
						 AND PDV_CUPOM_NAO_FISCAL.ST_CUPOM = 0) RS
					GROUP BY
						 CD_EMP
						, CD_FILIAL
						, ANO_VD
						, MES_VD
			------------------------------------------------------------------------------------------------------------------------
			-- GRAVANDO RECEBIMENTO EM CARTÕES NA TABELA DE CONSOLIDAÇÃO
			------------------------------------------------------------------------------------------------------------------------		
			PRINT 'UPDATE 6 '+CONVERT(VARCHAR, GETDATE(), 108);
			UPDATE DRE
			  SET
				  VLR_CARTAO = ROUND(D.VLR_CARTAO, 2)
				, VLR_CARTAO_CRED = ROUND(D.VLR_CARTAO_CRED, 2)
				, VLR_CARTAO_DEB = ROUND(D.VLR_CARTAO_DEB, 2)
				, VLR_TX_CARTAO = ROUND(D.VLR_TX_CARTAO, 2)
				, VLR_TX_CARTAO_CRED = ROUND(D.VLR_TX_CARTAO_CRED, 2)
				, VLR_TX_CARTAO_DEB = ROUND(D.VLR_TX_CARTAO_DEB, 2)
			FROM CTB_CNT_DRE_CONSLD_VD_MES DRE
				 INNER JOIN #TBL_CARTAO D ON DRE.CD_EMP = D.CD_EMP
											 AND DRE.CD_FILIAL = D.CD_FILIAL
											 AND DRE.ANO_VD = D.ANO_VD
											 AND DRE.MES_VD = D.MES_VD;
		END;
	END TRY
	BEGIN CATCH

		INSERT INTO ADM_ERRO
		(numero
	   , severidade
	   , estado
	   , rotina
	   , linha
	   , mensagem
	   , dtErro
		)
			   SELECT ERROR_NUMBER() AS numero
					, ERROR_SEVERITY() AS severidade
					, ERROR_STATE() AS estado
					, ERROR_PROCEDURE() AS rotina
					, ERROR_LINE() AS linha
					, ERROR_MESSAGE() AS mensagem
					, GETDATE() AS dtErro;
	END CATCH;

PRINT 'P_CTB_CNT_DRE_CONSLD_VD_MES FINALIZADA!';