IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_FECHA_PBM_CONTROLE]') 
					AND type in (N'P', N'PC'))
BEGIN
	DROP PROCEDURE [DBO].[P_EST_FECHA_PBM_CONTROLE]
END
GO
/*
=======================================================================================
'Job.................:  
'Função..............: 
'Objetivo............: Alterar codigo do produto 
'Tabelas.............: 
'Dt Modificação......: 04/05/2021
'Modificação.........: Tag #I001 Index tabela temporaria para remover Sort
'=======================================================================================
Tag #A001 - Alteração do codigo  
Tag #I001 - Inclusão de novo codigo 
*/
--EXEC P_EST_FECHA_PBM_CONTROLE 1, 9, 2018,4,0,0
CREATE PROCEDURE P_EST_FECHA_PBM_CONTROLE @CD_EMP INT,
										  @CD_FILIAL INT,
										  @ANO_PAR INT=0,
										  @MES_PAR INT=0,
										  @CD_PROD_PAR INT=0,
										  @FLAG_ANALISE INT=0
AS
begin

	DECLARE @DT_ATUAL DATETIME
	DECLARE @DT_PROC DATETIME
	SET NOCOUNT ON;
	DECLARE 
		@dtInicioMes DATETIME, 
		@dtInicioMesFechamento DATETIME, 
		@dtInicioMesAnterior DATETIME, 
		@dtDiaFinalMesFechamento DATETIME,
		@ano INT, 
		@mes VARCHAR(2),
		@anoAnt INT, 
		@mesAnt INT,
		@anoFecha INT, 
		@mesFecha INT,
		@QtdeMesesProc INT,
		@QtdeMesProc INT;
	
	CREATE TABLE #TmpNFEntPBM (CD_EMP INT, CD_FILIAL INT, CD_NF INT)


	IF @ANO_PAR IS NULL
	BEGIN
		SET @ANO_PAR=0
	END

	IF @ANO_PAR=0
	begin
		SET @DT_ATUAL= DATEADD(MONTH, -1, GETDATE())
		SET @ANO=YEAR(@DT_ATUAL)
		SET @MES=MONTH(@DT_ATUAL)
	end

	IF @ANO_PAR<>0
	begin
		SET @ANO=@ANO_PAR
		SET @MES=@MES_PAR
	end

	IF  LEN(@mes)=1 
		SET @mes='0' + @mes

	DELETE
		RC_CLI_CONV_PBM_CONTROLE_EST_PROD_FECHA_MENSAL
	FROM
		RC_CLI_CONV_PBM_CONTROLE_EST_PROD_FECHA_MENSAL INNER JOIN EST_PROD_RC_CLI_CONV ON
		RC_CLI_CONV_PBM_CONTROLE_EST_PROD_FECHA_MENSAL.CD_EMP = EST_PROD_RC_CLI_CONV.CD_EMP AND
		RC_CLI_CONV_PBM_CONTROLE_EST_PROD_FECHA_MENSAL.CD_PROD = EST_PROD_RC_CLI_CONV.CD_PROD 
	WHERE
		EST_PROD_RC_CLI_CONV.REPOSICAO ='N'	
	
	SELECT	@dtInicioMes = convert(char(8),CONVERT(CHAR(4), @ano) +  @mes + '01'),
			@QtdeMesesProc=DATEDIFF(MONTH,@dtInicioMes,GETDATE()) -1;

	CREATE TABLE #RsVendasPBMProc (CD_EMP INT,
							   CD_FILIAL INT,
							   CD_PROD INT,
							   QT_IT_VD MONEY,
							   QT_IT_ENT MONEY,
							   QT_IT_FECHA MONEY)
	
-- Tag #I001	
	CREATE INDEX IX_#RsVendasPBMProc ON #RsVendasPBMProc (CD_EMP,CD_FILIAL,CD_PROD);

	SET @QtdeMesProc=0
	WHILE @QtdeMesProc <= @QtdeMesesProc
	BEGIN
		SELECT 
			@dtInicioMesFechamento= DATEADD(MONTH,@QtdeMesProc,@dtInicioMes),
			@dtDiaFinalMesFechamento = DATEADD(DAY, -1, DATEADD(month, 1, @dtInicioMesFechamento)),
			@dtInicioMesAnterior = DATEADD(DAY, -1, @dtInicioMesFechamento), 
			@anoAnt = DATEPART(year, @dtInicioMesAnterior), 
			@mesAnt=DATEPART(month, @dtInicioMesAnterior),
			@anoFecha=DATEPART(year, @dtInicioMesFechamento),
			@mesFecha =DATEPART(month, @dtInicioMesFechamento);

		PRINT 'Buscando dados ano\mes anterior '+ CONVERT( VARCHAR(10), @anoAnt) +' '+CONVERT(VARCHAR, @MesAnt, 112) + ' ' + convert(varchar,getdate(),108);          
	
		INSERT INTO #RsVendasPBMProc (CD_EMP,CD_FILIAL,CD_PROD,QT_IT_VD,QT_IT_ENT,QT_IT_FECHA)
		SELECT
			CD_EMP,
			CD_FILIAL,
			CD_PROD,
			0 AS QT_IT_VD,
			0 AS QT_IT_ENT,
			QT_SALDO AS QT_IT_FECHA
		FROM
			RC_CLI_CONV_PBM_CONTROLE_EST_PROD_FECHA_MENSAL
		WHERE
			ANO=@anoAnt
			AND MES=@mesAnt
			AND CD_EMP = @CD_EMP
			AND CD_FILIAL = @CD_FILIAL
			AND CD_PROD = CASE WHEN @CD_PROD_PAR=0 THEN CD_PROD ELSE @CD_PROD_PAR END

		PRINT 'Periodo analise '+ CONVERT(VARCHAR, @dtInicioMesFechamento, 112) +' a '+ CONVERT(VARCHAR, @dtDiaFinalMesFechamento, 112);          

		PRINT 'Gerando movimento de vendas '+  convert(varchar,getdate(),108);          

		INSERT INTO #RsVendasPBMProc (CD_EMP,CD_FILIAL,CD_PROD,QT_IT_VD,QT_IT_ENT,QT_IT_FECHA)
		SELECT
			PDV_VD_IT.CD_EMP,
			PDV_VD_IT.CD_FILIAL,
			PDV_VD_IT.CD_PROD,
			SUM(PDV_VD_IT.QT_IT) AS QT_IT_VD,
			0 AS QT_IT_ENT,
			0 AS QT_IT_FECHA
		FROM
			PDV_VD_IT INNER JOIN PDV_VD ON
			PDV_VD_IT.CD_EMP  = PDV_VD.CD_EMP AND
			PDV_VD_IT.CD_FILIAL = PDV_VD.CD_FILIAL AND
			PDV_VD_IT.CD_VD = PDV_VD.CD_VD 
			INNER JOIN PDV_VD_IT_VD_PBM ON
			PDV_VD_IT.CD_EMP= PDV_VD_IT_VD_PBM.CD_EMP AND
			PDV_VD_IT.CD_FILIAL = PDV_VD_IT_VD_PBM.CD_FILIAL AND
			PDV_VD_IT.CD_VD = PDV_VD_IT_VD_PBM.CD_VD AND
			PDV_VD_IT.CD_IT = PDV_VD_IT_VD_PBM.CD_IT 
			INNER JOIN RC_CLI_CONV_VINC_RC_CLI ON
			PDV_VD_IT_VD_PBM.CD_EMP = RC_CLI_CONV_VINC_RC_CLI.CD_EMP AND
			PDV_VD_IT_VD_PBM.CD_CLI_PBM = RC_CLI_CONV_VINC_RC_CLI.CD_CLI 
			INNER JOIN EST_PROD_RC_CLI_CONV ON
			PDV_VD_IT.CD_EMP = EST_PROD_RC_CLI_CONV.CD_EMP AND
			PDV_VD_IT.CD_PROD = EST_PROD_RC_CLI_CONV.CD_PROD 
			INNER JOIN RC_CLI_CONV_PBM ON
			RC_CLI_CONV_VINC_RC_CLI.CD_EMP = RC_CLI_CONV_PBM.CD_EMP AND
			RC_CLI_CONV_VINC_RC_CLI.CD_CONV = RC_CLI_CONV_PBM.CD_CONV AND
			PDV_VD_IT.DT_VD >= RC_CLI_CONV_PBM.DT_ATIVAR_CONTROLE
			LEFT OUTER JOIN PDV_VD_IT_VD_PBM_GLOSADA ON
			PDV_VD_IT.CD_EMP= PDV_VD_IT_VD_PBM_GLOSADA.CD_EMP AND
			PDV_VD_IT.CD_FILIAL = PDV_VD_IT_VD_PBM_GLOSADA.CD_FILIAL AND
			PDV_VD_IT.CD_VD = PDV_VD_IT_VD_PBM_GLOSADA.CD_VD AND
			PDV_VD_IT.CD_IT = PDV_VD_IT_VD_PBM_GLOSADA.CD_IT 
			LEFT OUTER JOIN PDV_VD_IT_VD_PBM_FINANCEIRO ON
			PDV_VD_IT.CD_EMP= PDV_VD_IT_VD_PBM_FINANCEIRO.CD_EMP AND
			PDV_VD_IT.CD_FILIAL = PDV_VD_IT_VD_PBM_FINANCEIRO.CD_FILIAL AND
			PDV_VD_IT.CD_VD = PDV_VD_IT_VD_PBM_FINANCEIRO.CD_VD AND
			PDV_VD_IT.CD_IT = PDV_VD_IT_VD_PBM_FINANCEIRO.CD_IT 
		
		WHERE
			PDV_VD_IT.CD_EMP =@CD_EMP
			AND PDV_VD_IT.ST_IT=1
			AND PDV_VD.ST_VD=0
			AND PDV_VD_IT.CD_FILIAL=@CD_FILIAL
			AND PDV_VD_IT.DT_VD BETWEEN @dtInicioMesFechamento AND @dtDiaFinalMesFechamento
			AND RC_CLI_CONV_PBM.FLAG_POSSUI_REPOSICAO_PRODUTOS=1
			AND PDV_VD_IT_VD_PBM_GLOSADA.CD_IT  IS NULL
			AND PDV_VD_IT_VD_PBM_FINANCEIRO.CD_IT IS NULL
			AND PDV_VD_IT.CD_PROD = CASE WHEN @CD_PROD_PAR=0 THEN PDV_VD_IT.CD_PROD ELSE @CD_PROD_PAR END
			AND EST_PROD_RC_CLI_CONV.REPOSICAO ='S'
		GROUP BY
			PDV_VD_IT.CD_EMP,
			PDV_VD_IT.CD_FILIAL,
			PDV_VD_IT.CD_PROD
		UNION ALL
		SELECT
			RC_CLI_CONV_PBM_CONTROLE_AJUSTES.CD_EMP,
			RC_CLI_CONV_PBM_CONTROLE_AJUSTES.CD_FILIAL,
			RC_CLI_CONV_PBM_CONTROLE_AJUSTES.CD_PROD,
			RC_CLI_CONV_PBM_CONTROLE_AJUSTES.QT_MOVIMENTO AS QT_IT_VD,
			0 AS QT_IT_ENT,
			0 AS QT_IT_FECHA
		FROM
			RC_CLI_CONV_PBM_CONTROLE_AJUSTES INNER JOIN GLB_USU ON
			RC_CLI_CONV_PBM_CONTROLE_AJUSTES.CD_USU = GLB_USU.CD_USU 
		WHERE
			RC_CLI_CONV_PBM_CONTROLE_AJUSTES.CD_EMP =@CD_EMP
			AND RC_CLI_CONV_PBM_CONTROLE_AJUSTES.CD_FILIAL=@CD_FILIAL
			AND RC_CLI_CONV_PBM_CONTROLE_AJUSTES.DT_MOVIMENTO BETWEEN @dtInicioMesFechamento AND @dtDiaFinalMesFechamento
			AND RC_CLI_CONV_PBM_CONTROLE_AJUSTES.TP_TRANSACAO=1	
		
/*****************************************************************************************************************************************************/

		PRINT 'Gerando movimento de entrada '+  convert(varchar,getdate(),108);
	
		INSERT INTO #TmpNFEntPBM
		SELECT
			CD_EMP,
			CD_FILIAL,
			CD_NF
		FROM EST_NF_ENT
		WHERE
				EST_NF_ENT.CD_EMP =@CD_EMP
			AND EST_NF_ENT.CD_FILIAL=@CD_FILIAL
			AND EST_NF_ENT.DT_ENT BETWEEN @dtInicioMesFechamento AND @dtDiaFinalMesFechamento
			AND EST_NF_ENT.FLAG_REPOSICAO_PBM=1

		/*
		INSERT INTO #TmpNFEntPBM
		SELECT
			E.CD_EMP,
			E.CD_FILIAL,
			E.CD_NF
		FROM 
			EST_NF_ENT
			INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT ON
				EST_NF_ENT_DANFE_PROVISORIO.CD_EMP     = EST_NF_ENT.CD_EMP
				AND EST_NF_ENT_DANFE_PROVISORIO.CD_FILIAL  = EST_NF_ENT.CD_FILIAL
				AND EST_NF_ENT_DANFE_PROVISORIO.CD_NF      = EST_NF_ENT.CD_NF 

			 JOIN V_EST_NF_ENT_DANFE_PROVISORIO_REPOSICAO_PBM REPOSICAO_PBM ON
					REPOSICAO_PBM.CD_EMP = EST_NF_ENT_DANFE_PROVISORIO.CD_EMP
					AND REPOSICAO_PBM.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO.CD_CTR

			LEFT JOIN  #TmpNFEntPBM D ON
					EST_NF_ENT.CD_EMP     = D.CD_EMP
				AND EST_NF_ENT.CD_FILIAL  = D.CD_FILIAL
				AND EST_NF_ENT.CD_NF      = D.CD_NF 

		WHERE
				EST_NF_ENT_IT.CD_EMP =@CD_EMP
			AND EST_NF_ENT_IT.CD_FILIAL=@CD_FILIAL
			AND EST_NF_ENT_IT.DT_ENT BETWEEN @dtInicioMesFechamento AND @dtDiaFinalMesFechamento 
			AND EST_NF_ENT.FLAG_REPOSICAO_PBM=1
			AND REPOSICAO_PBM.PERC_ATEND = 100
			AND D.CD_EMP IS NULL;
			*/
		--Vinculadas pelo usuario
		INSERT INTO #TmpNFEntPBM
		SELECT 
			e.CD_EMP, 
			e.CD_FILIAL, 
			e.CD_NF
		FROM 
			EST_NF_ENT
			INNER JOIN EST_NF_ENT_PBM  e ON
					EST_NF_ENT.CD_EMP     = E.CD_EMP
				AND EST_NF_ENT.CD_FILIAL  = E.CD_FILIAL
				AND EST_NF_ENT.CD_NF      = E.CD_NF 
			LEFT JOIN #TmpNFEntPBM D ON 
						E.CD_EMP	= D.CD_EMP
					AND E.CD_FILIAL = D.CD_FILIAL
					AND E.CD_NF		= D.CD_NF 
		WHERE
				EST_NF_ENT.CD_EMP =@CD_EMP
			AND EST_NF_ENT.CD_FILIAL=@CD_FILIAL
			AND EST_NF_ENT.DT_ENT BETWEEN @dtInicioMesFechamento AND @dtDiaFinalMesFechamento 
			AND D.CD_EMP IS NULL;

		INSERT INTO #RsVendasPBMProc (CD_EMP,CD_FILIAL,CD_PROD,QT_IT_VD,QT_IT_ENT,QT_IT_FECHA)
		SELECT
			   EST_NF_ENT_IT.CD_EMP,
			   EST_NF_ENT_IT.CD_FILIAL,
			   EST_NF_ENT_IT.CD_PROD,
			   0 AS QT_IT_VD,
			   SUM(EST_NF_ENT_IT.QT_IT) AS QT_IT_ENT,
			   0 AS QT_IT_FECHA
		  FROM EST_NF_ENT 
			   INNER JOIN EST_NF_ENT_IT ON EST_NF_ENT.CD_EMP = EST_NF_ENT_IT.CD_EMP 
									   AND EST_NF_ENT.CD_FILIAL = EST_NF_ENT_IT.CD_FILIAL 
									   AND EST_NF_ENT.CD_NF = EST_NF_ENT_IT.CD_NF 
			   INNER JOIN (SELECT
								  c.CD_EMP
								 ,c.CD_PROD
								 ,PBM.DT_ATIVAR_CONTROLE
							 FROM EST_PROD_RC_CLI_CONV c 
								  INNER JOIN RC_CLI_CONV_PBM pbm ON C.CD_EMP = pbm.CD_EMP 
																AND C.CD_CONV = pbm.CD_CONV
							WHERE pbm.FLAG_POSSUI_REPOSICAO_PRODUTOS = 1
							GROUP  BY c.CD_EMP
									 ,c.CD_PROD
									 ,PBM.DT_ATIVAR_CONTROLE) P ON EST_NF_ENT_IT.CD_PROD = P.CD_PROD 
															   AND EST_NF_ENT_IT.CD_EMP  = P.CD_EMP 
															   AND EST_NF_ENT_IT.DT_ENT >= P.DT_ATIVAR_CONTROLE
			   LEFT OUTER JOIN  EST_NF_ENT_IT_PBM_GLOSADA ON EST_NF_ENT_IT.CD_EMP    = EST_NF_ENT_IT_PBM_GLOSADA.CD_EMP 
														 AND EST_NF_ENT_IT.CD_FILIAL = EST_NF_ENT_IT_PBM_GLOSADA.CD_FILIAL 
														 AND EST_NF_ENT_IT.CD_NF     = EST_NF_ENT_IT_PBM_GLOSADA.CD_NF 
														 AND EST_NF_ENT_IT.CD_PROD   = EST_NF_ENT_IT_PBM_GLOSADA.CD_PROD 
		 		JOIN #TmpNFEntPBM D ON 
							EST_NF_ENT.CD_EMP	= D.CD_EMP
						AND EST_NF_ENT.CD_FILIAL = D.CD_FILIAL
						AND EST_NF_ENT.CD_NF		= D.CD_NF 
		 
		 WHERE EST_NF_ENT_IT.CD_EMP =@CD_EMP
		   AND EST_NF_ENT_IT.CD_FILIAL=@CD_FILIAL
		   AND EST_NF_ENT_IT.DT_ENT BETWEEN @dtInicioMesFechamento AND @dtDiaFinalMesFechamento
		   AND EST_NF_ENT_IT_PBM_GLOSADA.CD_NF  IS NULL
		   AND EST_NF_ENT_IT.CD_PROD = CASE WHEN @CD_PROD_PAR=0 THEN EST_NF_ENT_IT.CD_PROD 
											ELSE @CD_PROD_PAR 
										END
       
		GROUP BY
			EST_NF_ENT_IT.CD_EMP,
			EST_NF_ENT_IT.CD_FILIAL,
			EST_NF_ENT_IT.CD_PROD

		UNION ALL
		SELECT
			RC_CLI_CONV_PBM_CONTROLE_AJUSTES.CD_EMP,
			RC_CLI_CONV_PBM_CONTROLE_AJUSTES.CD_FILIAL,
			RC_CLI_CONV_PBM_CONTROLE_AJUSTES.CD_PROD,
			0 AS QT_IT_VD,
			RC_CLI_CONV_PBM_CONTROLE_AJUSTES.QT_MOVIMENTO AS QT_IT_ENT,
			0 AS QT_IT_FECHA
		FROM
			RC_CLI_CONV_PBM_CONTROLE_AJUSTES INNER JOIN GLB_USU ON
			RC_CLI_CONV_PBM_CONTROLE_AJUSTES.CD_USU = GLB_USU.CD_USU 
		WHERE
			RC_CLI_CONV_PBM_CONTROLE_AJUSTES.CD_EMP =@CD_EMP
			AND RC_CLI_CONV_PBM_CONTROLE_AJUSTES.CD_FILIAL=@CD_FILIAL
			AND RC_CLI_CONV_PBM_CONTROLE_AJUSTES.DT_MOVIMENTO BETWEEN @dtInicioMesFechamento AND @dtDiaFinalMesFechamento
			AND RC_CLI_CONV_PBM_CONTROLE_AJUSTES.TP_TRANSACAO=0;

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

		PRINT 'Gravando cadastro de produtos para controle de estoque ' +  convert(varchar,getdate(),108);   
		INSERT INTO RC_CLI_CONV_PBM_CONTROLE_EST_PROD	(CD_EMP,CD_FILIAL,CD_PROD,DT_CAD,QT_SALDO)
		SELECT
			DISTINCT
			p.CD_EMP,
			p.CD_FILIAL,
			p.CD_PROD,
			CONVERT(CHAR(10),GETDATE(),112),
			0
		FROM
			#RsVendasPBMProc p LEFT OUTER JOIN RC_CLI_CONV_PBM_CONTROLE_EST_PROD c ON
			p.CD_EMP = c.CD_EMP AND
			p.CD_FILIAL = c.CD_FILIAL AND
			p.CD_PROD = c.CD_PROD 
		WHERE
			C.CD_PROD IS NULL

		PRINT 'Deletando fechamento do mes de processamento ' +  convert(varchar,getdate(),108);   
		DELETE
		FROM
			RC_CLI_CONV_PBM_CONTROLE_EST_PROD_FECHA_MENSAL
		WHERE
			ANO=@anoFecha
			AND MES=@mesFecha
			AND CD_EMP = @CD_EMP
			AND CD_FILIAL = @CD_FILIAL
	
		IF @FLAG_ANALISE=0
		BEGIN
			PRINT 'Gravando resultado do fechamento ' +  convert(varchar,getdate(),108);   
			INSERT INTO  RC_CLI_CONV_PBM_CONTROLE_EST_PROD_FECHA_MENSAL
			SELECT
				a.CD_EMP,
				a.CD_FILIAL,
				a.CD_PROD,
				@anoFecha,
				@mesFecha,
				sum((a.QT_IT_FECHA + a.QT_IT_VD) - a.QT_IT_ENT),
				GETDATE()
			FROM
				#RsVendasPBMProc a
				join    RC_CLI_CONV_PBM_CONTROLE_EST_PROD p on --Garantir fk
				a.CD_EMP	= p.CD_EMP AND
				a.CD_FILIAL	= p.CD_FILIAL AND
				a.CD_PROD	= p.CD_PROD
				
			GROUP BY
				a.CD_EMP,
				a.CD_FILIAL,
				a.CD_PROD
		END
		ELSE
		BEGIN
			SELECT
				*
			FROM
				#RsVendasPBMProc 
		END	

		SELECT @QtdeMesProc = @QtdeMesProc + 1

		TRUNCATE TABLE #RsVendasPBMProc
	END
	 
	DROP TABLE #RsVendasPBMProc
	 
end


