IF OBJECT_ID('P_EST_MOVIMENTO_COMPRAS') IS NOT NULL
	BEGIN
		DROP PROCEDURE P_EST_MOVIMENTO_COMPRAS
	END
GO
--EXEC P_EST_MOVIMENTO_COMPRAS 1,'2017-04-01'
CREATE PROCEDURE P_EST_MOVIMENTO_COMPRAS (@CD_EMP INT,  @DT_PROC DATETIME) AS 
SET NOCOUNT ON;
DECLARE @CD_CTR_DT_MOVIMENTO INT

Print 'buscando codificacao da data do movimento - Time ' + convert(varchar(20),getdate(),108);
SELECT 
	@CD_CTR_DT_MOVIMENTO=CD_CTR_DT_MOVIMENTO
FROM
	GLB_DT_MOVIMENTO
WHERE
	DT_MOVIMENTO=@DT_PROC

IF NOT @CD_CTR_DT_MOVIMENTO >0 
    RETURN;

Print 'Deletando movimento anterior - Time ' + convert(varchar(20),getdate(),108);
DELETE FROM 
	EST_MOVIMENTO_COMPRAS
WHERE
	CD_CTR_DT_MOVIMENTO=@CD_CTR_DT_MOVIMENTO
	AND CD_EMP = @CD_EMP

CREATE TABLE #RsComprasItens (CD_CTR_DT_MOVIMENTO INT,
							CD_EMP INT,
							CD_FILIAL INT,
							CD_PROD INT,
							CD_MOV_OP INT,
							VLR_COMPRAS MONEY,
							VLR_DESC MONEY,
							VLR_ICMS_SUBS MONEY,
							VLR_FRETE MONEY,
							VLR_IPI MONEY,
							VLR_OUTRAS_DESPESAS MONEY,
							VLR_ICMS_DESONERADO MONEY,
							VLR_COMPRAS_LIQ MONEY,
							TIPO_TRANSACAO INT,
							QTDE_ITENS MONEY,
							VLR_DESC_FINANCEIRO MONEY,
							TP_MOV INT)

/*************************************************************************************************************************************/
  --  Print 'Gerando dados de compras por item do dia - Time ' + convert(varchar(20),getdate(),108);
/*************************************************************************************************************************************/
   BEGIN 

    WITH FILIAL_CTE AS (
    SELECT 
	   CD_EMP,
	   CD_FILIAL
    FROM  PRC_FILIAL 
    WHERE
	   CD_EMP=1
	   AND STS_FILIAL =0)
    
    
    	 

		  INSERT INTO #RsComprasItens
		  SELECT
			 @CD_CTR_DT_MOVIMENTO CD_CTR_DT_MOVIMENTO
			 ,EST_NF_ENT_IT.CD_EMP
			 ,EST_NF_ENT_IT.CD_FILIAL
			 ,EST_NF_ENT_IT.CD_PROD
			 ,GLB_MOV_OP.CD_MOV_OP
			 ,(CONVERT(FLOAT,EST_NF_ENT_IT.VLR_TOT_IT)/CONVERT(FLOAT,CASE WHEN EST_NF_ENT_IT.QT_IT=0 THEN 1 ELSE EST_NF_ENT_IT.QT_IT END)
				* EST_NF_ENT_IT.QT_IT)	    VLR_COMPRAS
			 ,EST_NF_ENT_IT.VLR_DESC		    VLR_DESC_IT
			 ,EST_NF_ENT_IT.VLR_ICMS_SUBS      VLR_ICMS_SUBS_IT
			 ,ISNULL(EST_NF_ENT_IT.VLR_FRETE_IT,0) AS VLR_FRETE_IT
			 ,EST_NF_ENT_IT.VLR_IPI VLR_IPI
			 ,ISNULL(EST_NF_ENT_IT.VLR_OUTRAS_DESPESAS, 0)     AS VLR_OUTRAS_DESPESAS
			 ,ISNULL(EST_NF_ENT_IT.VLR_ICMS_DESONERADO, 0)	VLR_ICMS_DESONERADO

			 ,((CONVERT(FLOAT,EST_NF_ENT_IT.VLR_TOT_IT)/CONVERT(FLOAT,CASE WHEN EST_NF_ENT_IT.QT_IT=0 THEN 1 ELSE EST_NF_ENT_IT.QT_IT END)
				* EST_NF_ENT_IT.QT_IT)  - ISNULL(EST_NF_ENT_IT.VLR_DESC,0) )
							 + ISNULL(EST_NF_ENT_IT.VLR_ICMS_SUBS,0)
							 + ISNULL(EST_NF_ENT_IT.VLR_IPI,0)
							 + ISNULL(EST_NF_ENT_IT.VLR_FRETE_IT, 0)
							 + ISNULL(EST_NF_ENT_IT.VLR_OUTRAS_DESPESAS,0) 
							 - ISNULL(EST_NF_ENT_IT.VLR_ICMS_DESONERADO, 0) AS VLR_COMPRAS_LIQ

		  
			 ,CASE	WHEN GLB_MOV_OP.TP_MOV =0		 AND GLB_MOV_OP.TP_MOV_OP_TRANSA=0 THEN 4
				    WHEN GLB_MOV_OP.TP_MOV =11		 AND GLB_MOV_OP.TP_MOV_OP_TRANSA=0 THEN 5
				    WHEN GLB_MOV_OP.TP_MOV =10		 AND GLB_MOV_OP.TP_MOV_OP_TRANSA=0 THEN 6
				    WHEN GLB_MOV_OP.TP_MOV IN (3,4)	 AND GLB_MOV_OP.TP_MOV_OP_TRANSA=0	THEN 7
				    WHEN GLB_MOV_OP.TP_MOV IN (45,16,47)	 AND GLB_MOV_OP.TP_MOV_OP_TRANSA=0	THEN 8
				    WHEN GLB_MOV_OP.TP_MOV =23		 AND GLB_MOV_OP.TP_MOV_OP_TRANSA=0	THEN 9
				ELSE 999
			 END AS TIPO_TRANSACAO 
			 ,EST_NF_ENT_IT.QT_IT 
			 ,ROUND(ISNULL(( CASE WHEN ISNULL(DBO.EST_NF_ENT.FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0 THEN 
								( EST_NF_ENT_IT.VLR_TOT_IT + EST_NF_ENT_IT.VLR_FRETE_IT )
							ELSE 
								( EST_NF_ENT_IT.VLR_TOT_IT
													 + EST_NF_ENT_IT.VLR_ICMS_SUBS
													 + EST_NF_ENT_IT.VLR_IPI
													 + EST_NF_ENT_IT.VLR_FRETE_IT ) - EST_NF_ENT_IT.VLR_DESC
										END ) * ( EST_NF_ENT_IT.PERC_DESC_FINAN / 100 ), 0), 2)  AS VLR_DESC_FINANCEIRO,
		   GLB_MOV_OP.TP_MOV  AS TP_MOV	
		  FROM
			 EST_NF_ENT 
			 INNER JOIN DBO.EST_NF_ENT_IT ON
			 EST_NF_ENT.CD_EMP = DBO.EST_NF_ENT_IT.CD_EMP AND
			 EST_NF_ENT.CD_FILIAL = DBO.EST_NF_ENT_IT.CD_FILIAL AND
			 EST_NF_ENT.CD_NF = DBO.EST_NF_ENT_IT.CD_NF AND
			 EST_NF_ENT.DT_ENT = DBO.EST_NF_ENT_IT.DT_ENT 
			 INNER JOIN FILIAL_CTE cte on 
			 EST_NF_ENT.CD_EMP = cte.CD_EMP AND
			 EST_NF_ENT.CD_FILIAL = cte.CD_FILIAL  
			 INNER JOIN DBO.GLB_MOV_OP ON
			 EST_NF_ENT.CD_NT_MOV = DBO.GLB_MOV_OP.CD_MOV_OP 
		  WHERE
			 EST_NF_ENT_IT.CD_EMP =1
			 AND EST_NF_ENT_IT.DT_ENT =@DT_PROC 
 
		END
 

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

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

Print 'Gravando compras do dia - Time ' + convert(varchar(20),getdate(),108);
INSERT INTO EST_MOVIMENTO_COMPRAS
    (CD_CTR_DT_MOVIMENTO,CD_EMP,CD_FILIAL
	   ,CD_MOV_OP,TIPO_TRANSACAO)
SELECT
	CD_CTR_DT_MOVIMENTO,
	CD_EMP,
	CD_FILIAL,	
	CD_MOV_OP,
	TIPO_TRANSACAO
FROM
	#RsComprasItens
GROUP BY
	CD_CTR_DT_MOVIMENTO,
	CD_FILIAL,
	CD_EMP,
	CD_MOV_OP,
	TIPO_TRANSACAO

Print 'Gravando compras por produtos - Time ' + convert(varchar(20),getdate(),108);
INSERT INTO EST_MOVIMENTO_COMPRAS_PRODUTOS
(CD_CTR_COMPRAS,CD_EMP,CD_PROD,CD_MOV_OP,VLR_COMPRAS,VLR_DESC,VLR_ICMS_SUBS,
    VLR_FRETE,VLR_IPI,VLR_OUTRAS_DESPESAS,VLR_ICMS_DESONERADO,VLR_COMPRAS_LIQ,
	QTDE_ITENS,VLR_DESC_FINANCEIRO,TP_MOV )
SELECT
	EST_MOVIMENTO_COMPRAS.CD_CTR_COMPRAS,
	i.CD_EMP,
	i.CD_PROD,
	i.CD_MOV_OP,
	SUM(i.VLR_COMPRAS) AS VLR_COMPRAS,
	SUM(i.VLR_DESC) AS VLR_DESC,
	SUM(i.VLR_ICMS_SUBS) AS VLR_ICMS_SUBS,
	SUM(i.VLR_FRETE) AS VLR_FRETE,
	SUM(i.VLR_IPI) AS VLR_IPI,
	SUM(i.VLR_OUTRAS_DESPESAS) AS VLR_OUTRAS_DESPESAS,
	SUM(i.VLR_ICMS_DESONERADO) AS VLR_ICMS_DESONERADO,
	SUM(i.VLR_COMPRAS_LIQ) AS VLR_COMPRAS_LIQ,
	SUM(i.QTDE_ITENS) AS QTDE_ITENS,
	SUM(i.VLR_DESC_FINANCEIRO) AS VLR_DESC_FINAN,
	i.TP_MOV
FROM
	#RsComprasItens i inner join EST_MOVIMENTO_COMPRAS on
	i.CD_CTR_DT_MOVIMENTO=EST_MOVIMENTO_COMPRAS.CD_CTR_DT_MOVIMENTO AND
	i.CD_EMP = EST_MOVIMENTO_COMPRAS.CD_EMP AND
	i.CD_FILIAL = EST_MOVIMENTO_COMPRAS.CD_FILIAL AND
	i.CD_MOV_OP = EST_MOVIMENTO_COMPRAS.CD_MOV_OP
GROUP BY
	EST_MOVIMENTO_COMPRAS.CD_CTR_COMPRAS,
	i.CD_EMP,
	I.CD_PROD,
	i.CD_MOV_OP,
	i.TP_MOV


DROP TABLE #RsComprasItens;

Print 'Gravando movimento ultima entrada compras de produtos - Time ' + convert(varchar(20),getdate(),108);
EXEC P_EST_PROD_ENT_GRP_ECON_GRAVAR @CD_EMP,@DT_PROC,@DT_PROC