IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_PROD_CPL_EXTRATO_LINHA]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_PROD_CPL_EXTRATO_LINHA]
GO

--EXEC P_EST_PROD_CPL_EXTRATO_LINHA 1,998,'2019-11-01','2020-01-13','10',35,0
--Versao 2523
CREATE PROCEDURE [dbo].[P_EST_PROD_CPL_EXTRATO_LINHA](@CD_EMP					INT ,		-- Codigo da Empresa
														 @CD_FILIAL				INT,		-- Codigo da Filial
														 @DT_INI				DATETIME,
														 @DT_FINAL				DATETIME,
														 @CD_LINHA				VARCHAR(MAX),
														 @CD_PROD				INT=0,
														 @FLAG_TIPO_CONSULTA	INT=0)

AS
BEGIN  
SET NOCOUNT ON 
CREATE TABLE #RS_MOV_RET (CD_PROD    INT, 
						DT_MOV	  DATETIME,
						DS_PROD   VARCHAR(300), 
						QT_INI	  MONEY, 
						QT_SAI	  MONEY,
						QT_ENT	  MONEY,	
						QT_FINAL  MONEY,
						VLR_CUSTO MONEY,
						VLR_CUSTO_TOTAL MONEY,
						VLR_CUSTO_ENTRADA MONEY)

CREATE TABLE #RS_EST_FECHA(CD_EMP INT,
					    CD_PROD INT,
						DS_PROD VARCHAR(300),
						QT_EST MONEY)


DECLARE @DT_FECHA DATETIME
DECLARE @DT_INI_PESQUISA DATETIME
DECLARE @TP_CUSTO   INT
/*
@TIPO_CUSTO - 0 - Custo Medio no periodo (Obs. não forçar para vir qualquer custo, pois dá alteração no fechamento contábil).
              1 - Custo gerencial
              2 - Custo Medio cadastro produto - CPL
              3 - Custo Medio qualquer
              4 - Custo medio fechamento
			  5 - Custo Fechamento
			  6 - Custo de entrada
			  7 - Custo gerencial - CPL
			  9 - Valor de Saldo Custo
			  10 - Valor Custo Md Inicial
			  11 - Valor Custo Md Inicial sobreposto por Ajuste no custo
*/
CREATE TABLE #RsLinha (CD_EMP INT, CD_ARV_MERC_LINHA INT)

INSERT INTO #RsLinha
SELECT 
	@CD_EMP,
	ID 
FROM 
	UFD_PRM_IN_STRING(@CD_LINHA)


SET @TP_CUSTO=0
SET @DT_FECHA = DATEADD(MONTH,-1,@DT_INI)
SET @DT_FECHA = DBO.F_FINAL_MES(@DT_FECHA)

SELECT @DT_INI_PESQUISA = @DT_INI

CREATE TABLE #RS_PROD (CD_EMP INT, DS_PROD VARCHAR(300), CD_PROD INT, QT_EST_ATUAL MONEY)

INSERT INTO #RS_PROD
SELECT 
	EST_PROD.CD_EMP,
	LEFT(EST_PROD.DS_PROD,300) AS DS_PROD,
	EST_PROD.CD_PROD,
	EST_PROD_CPL.QT_EST
FROM
	EST_PROD 
	INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ON
	EST_PROD.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP AND
	EST_PROD.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD
	INNER JOIN EST_PROD_CPL ON
	EST_PROD.CD_EMP = EST_PROD_CPL.CD_EMP AND
	EST_PROD.CD_PROD = EST_PROD_CPL.CD_PROD
	INNER JOIN #RsLinha l ON
	EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP = l.CD_EMP AND
	EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA = l.CD_ARV_MERC_LINHA
WHERE
	EST_PROD_CPL.CD_EMP = @CD_EMP
	AND EST_PROD_CPL.CD_FILIAL = @CD_FILIAL
	AND EST_PROD.STS_PROD = 0

DECLARE @DT_POS_FIM DATETIME
SET @DT_POS_FIM = Dateadd(DAY, -1, @DT_INI);

/***********************************************************************************************************
	
	DEBUG
	select @CD_EMP, @CD_FILIAL,  @DT_POS_FIM dtPosicao, @DT_INI DataInicial
	delete from #RS_PROD
	where not cd_prod=141
	;
******************************************************************************************************************/
 
INSERT INTO #RS_EST_FECHA
SELECT     
	Prod.CD_EMP,
	Prod.CD_PROD, 
	Prod.DS_PROD,
	Pos.QT_IT 
FROM         
	#RS_PROD Prod 
	cross apply (SELECT QT_IT FROM  dbo.UFD_EST_PROD_CPL_POS_PERIODO(@CD_EMP, @CD_FILIAL, Prod.CD_PROD, @DT_POS_FIM,0)) Pos;


	 
INSERT  INTO #RS_MOV_RET
SELECT
	CD_PROD, 
	DT_MOV,
	DS_PROD,
	SUM(QT_INI), 
	SUM(QT_SAI),
	SUM(QT_ENT),
	SUM(QT_FINAL),
	0,
	0,
	MAX(VLR_CUSTO_ENTRADA)
FROM
(SELECT     
	CD_PROD, 
	@DT_FECHA DT_MOV,
	DS_PROD,
	QT_EST QT_INI, 
	0 QT_SAI,
	0 QT_ENT,
	0 QT_FINAL,
	0 VLR_CUSTO_ENTRADA
FROM         
	#RS_EST_FECHA
------------------------------------------------------------------------------------
UNION ALL
--Entrada de mercadoria 
SELECT     
	EST_NF_ENT_IT.CD_PROD, 
	EST_NF_ENT.DT_ENT,
	p.DS_PROD, 
	0,
	0,
	EST_NF_ENT_IT.QT_IT, 
	0,
	ROUND((SUM(EST_NF_ENT_IT.VLR_PRC_CUSTO)*SUM(EST_NF_ENT_IT.QT_IT))/SUM(EST_NF_ENT_IT.QT_IT),2)
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 GLB_MOV_OP ON 
	EST_NF_ENT.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP 
	INNER JOIN #RS_PROD p ON
	EST_NF_ENT_IT.CD_EMP = p.CD_EMP AND
	EST_NF_ENT_IT.CD_PROD = p.CD_PROD	
WHERE     
	EST_NF_ENT.CD_EMP = @CD_EMP 
	AND EST_NF_ENT.CD_FILIAL = @CD_FILIAL
	AND EST_NF_ENT.DT_ENT BETWEEN @DT_INI_PESQUISA AND @DT_FINAL
	AND EST_NF_ENT.STS_NF = 0
	AND GLB_MOV_OP.ATU_EST = 1
	AND GLB_MOV_OP.TP_ESTQ=0
GROUP BY
	EST_NF_ENT_IT.CD_PROD, 
	EST_NF_ENT.DT_ENT,
	p.DS_PROD, 
	EST_NF_ENT_IT.QT_IT
--Ajuste de estoque
UNION ALL
SELECT     
	EST_PROD_IMP.CD_PROD, 
	EST_PROD_IMP.DT_CAD,
	p.DS_PROD, 
	0, 
	CASE WHEN EST_PROD_IMP.QT_IMP < 0 THEN
		EST_PROD_IMP.QT_IMP * -1
	ELSE
		0
	END,
	CASE WHEN EST_PROD_IMP.QT_IMP > 0 THEN
		EST_PROD_IMP.QT_IMP
	ELSE
		0
	END,
	0,
	0
FROM 
	EST_PROD_IMP INNER JOIN #RS_PROD p ON
	EST_PROD_IMP.CD_EMP = p.CD_EMP AND
	EST_PROD_IMP.CD_PROD = p.CD_PROD		
WHERE     
	EST_PROD_IMP.CD_EMP = @CD_EMP 
	AND EST_PROD_IMP.CD_FILIAL = @CD_FILIAL 
	AND EST_PROD_IMP.DT_CAD BETWEEN @DT_INI_PESQUISA AND @DT_FINAL
	AND EST_PROD_IMP.TP_EST = 0

--Saidas por vendas por pdv
UNION ALL
SELECT
	i.CD_PROD, 
	v.DT_VD,
	p.DS_PROD, 
	0,
	CASE WHEN v.ST_VD = 0 THEN
		i.QT_IT
	ELSE
		0
	END,
	0,
	0,
	0
FROM    
	PDV_VD_IT i INNER JOIN PDV_VD v ON 
	i.CD_EMP=v.CD_EMP AND 
	i.CD_FILIAL=v.CD_FILIAL AND 
	i.CD_VD=v.CD_VD
	INNER JOIN #RS_PROD p ON
	i.CD_EMP = p.CD_EMP AND
	i.CD_PROD = p.CD_PROD	 	
WHERE   
	v.CD_EMP = @CD_EMP
	AND v.CD_FILIAL = @CD_FILIAL
	AND v.DT_VD BETWEEN @DT_INI_PESQUISA AND @DT_FINAL
	AND i.ST_IT=1

--Vendas por nota fiscal
UNION ALL
SELECT    	
	EST_NF_SAI_IT.CD_PROD, 
	EST_NF_SAI.DT_EMI_NF,
	p.DS_PROD, 
	0,
	EST_NF_SAI_IT.QT_IT, 
	0,
	0,
	0
FROM    
	EST_NF_SAI INNER JOIN EST_NF_SAI_IT ON
	EST_NF_SAI.CD_EMP = EST_NF_SAI_IT.CD_EMP AND
	EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL AND
	EST_NF_SAI.CD_NF = EST_NF_SAI_IT.CD_NF 
	INNER JOIN GLB_MOV_OP ON 
	EST_NF_SAI.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP 
	INNER JOIN #RS_PROD p ON
	EST_NF_SAI_IT.CD_EMP = p.CD_EMP AND
	EST_NF_SAI_IT.CD_PROD = p.CD_PROD
	LEFT OUTER JOIN PDV_VD_EST_NF_SAI ON
	EST_NF_SAI.CD_EMP = PDV_VD_EST_NF_SAI.CD_EMP AND
	EST_NF_SAI.CD_FILIAL = PDV_VD_EST_NF_SAI.CD_FILIAL AND
	EST_NF_SAI.CD_NF = PDV_VD_EST_NF_SAI.CD_NF 
WHERE   
	EST_NF_SAI.DT_EMI_NF BETWEEN @DT_INI_PESQUISA AND @DT_FINAL
	AND EST_NF_SAI.CD_EMP = @CD_EMP 
	AND EST_NF_SAI.CD_FILIAL = @CD_FILIAL
	AND GLB_MOV_OP.BX_EST = 1
	AND GLB_MOV_OP.TP_ESTQ=0
    AND EST_NF_SAI.STS_NF = 0
	AND PDV_VD_EST_NF_SAI.CD_NF  IS NULL

--Orcamento de vendas
UNION ALL
SELECT     
	EST_PED_VD_IT.CD_PROD, 
	EST_PED_VD_ORC.DT_CAD,
	p.DS_PROD,
	0, 	
	EST_PED_VD_IT.QT_IT, 
	0,
	0,
	0
FROM         
	EST_PED_VD_ORC INNER JOIN EST_PED_VD ON
	EST_PED_VD_ORC.CD_EMP = EST_PED_VD.CD_EMP AND 
	EST_PED_VD_ORC.CD_FILIAL = EST_PED_VD.CD_FILIAL AND 
    EST_PED_VD_ORC.CD_PED = EST_PED_VD.CD_PED
	INNER JOIN EST_PED_VD_IT ON
	EST_PED_VD.CD_EMP = EST_PED_VD_IT.CD_EMP AND 
	EST_PED_VD.CD_FILIAL = EST_PED_VD_IT.CD_FILIAL AND 
    EST_PED_VD.CD_PED = EST_PED_VD_IT.CD_PED
	INNER JOIN #RS_PROD p ON
	EST_PED_VD_IT.CD_EMP = p.CD_EMP AND
	EST_PED_VD_IT.CD_PROD = p.CD_PROD		
WHERE
	EST_PED_VD_ORC.CD_EMP = @CD_EMP
	AND EST_PED_VD_ORC.CD_FILIAL = @CD_FILIAL
	AND EST_PED_VD_ORC.DT_CAD BETWEEN @DT_INI_PESQUISA AND @DT_FINAL

--Orcamento de compras
UNION ALL
SELECT     
    EST_PED_CP_IT.CD_PROD, 
	EST_PED_CP.DT_EMI,
	p.DS_PROD, 
	0,
	0,
	EST_PED_CP_IT.QT_IT,
	0,
	0
FROM         
	EST_PED_CP INNER JOIN EST_PED_CP_IT ON 
	EST_PED_CP.CD_EMP = EST_PED_CP_IT.CD_EMP AND 
	EST_PED_CP.CD_FILIAL = EST_PED_CP_IT.CD_FILIAL AND 
    EST_PED_CP.CD_PED = EST_PED_CP_IT.CD_PED 
	INNER JOIN EST_PED_CP_ORC ON 
	EST_PED_CP.CD_EMP = EST_PED_CP_ORC.CD_EMP AND 
	EST_PED_CP.CD_FILIAL = EST_PED_CP_ORC.CD_FILIAL AND 
    EST_PED_CP.CD_PED = EST_PED_CP_ORC.CD_PED 
	INNER JOIN #RS_PROD p ON
	EST_PED_CP_IT.CD_EMP = p.CD_EMP AND
	EST_PED_CP_IT.CD_PROD = p.CD_PROD
WHERE     
	EST_PED_CP.CD_EMP = @CD_EMP 
	AND EST_PED_CP.CD_FILIAL = @CD_FILIAL 
	AND EST_PED_CP.DT_EMI BETWEEN @DT_INI_PESQUISA AND @DT_FINAL) RS
GROUP BY
	CD_PROD, 
	DT_MOV,
	DS_PROD

IF @FLAG_TIPO_CONSULTA=1
begin
	CREATE TABLE #RS_MOV_RET_tmp (CD_PROD    INT, 
						DT_MOV	  DATETIME,
						DS_PROD   VARCHAR(300), 
						QT_INI	  MONEY, 
						QT_SAI	  MONEY,
						QT_ENT	  MONEY,	
						QT_FINAL  MONEY,
						VLR_CUSTO MONEY,
						VLR_CUSTO_TOTAL MONEY,
						VLR_CUSTO_ENTRADA MONEY)

	INSERT INTO #RS_MOV_RET_tmp
	SELECT
		CD_PROD, 
		@DT_FINAL,
		DS_PROD, 
		SUM(QT_INI), 
		SUM(QT_SAI),
		SUM(QT_ENT),	
		SUM(QT_FINAL),
		0,
		0,
		MAX(VLR_CUSTO_ENTRADA)
	FROM
		#RS_MOV_RET
	GROUP BY
		CD_PROD, 
		DS_PROD

	DELETE FROM #RS_MOV_RET

	INSERT INTO #RS_MOV_RET
	SELECT
		CD_PROD, 
		DT_MOV,
		DS_PROD, 
		QT_INI, 
		QT_SAI,
		QT_ENT,	
		QT_FINAL,
		0,
		0,
		VLR_CUSTO_ENTRADA
	FROM
		#RS_MOV_RET_tmp
end


DECLARE @CD_PROD_PROC INT, 
		@DT_MOV DATETIME,
		@QT_INI MONEY, 
		@QT_SAI MONEY,
		@QT_ENT MONEY,
		@QT_EST MONEY,
		@CD_PROD_ANT int,
		@VLR_CUSTO MONEY

	SET @QT_EST = 0  
	set @CD_PROD_ANT=0
DECLARE CalcEstoque CURSOR FAST_FORWARD FOR 
						SELECT 
							CD_PROD, 
							DT_MOV,
							QT_INI, 
							QT_SAI,
							QT_ENT
						FROM 
							#RS_MOV_RET
						ORDER BY 
							CD_PROD, 
							DT_MOV
				
	OPEN CalcEstoque
	
	FETCH NEXT FROM CalcEstoque INTO @CD_PROD_PROC, 
									 @DT_MOV,
									 @QT_INI, 
									 @QT_SAI,
									 @QT_ENT

	WHILE @@FETCH_STATUS=0
		Begin
			IF @CD_PROD_PROC <> @CD_PROD_ANT 
			   begin
				 SET @CD_PROD_ANT=@CD_PROD_PROC 
				 SET @QT_EST=0
			   end

			UPDATE 
				#RS_MOV_RET 
			SET @QT_EST = (@QT_EST + @QT_INI + @QT_ENT) - @QT_SAI,
				QT_FINAL = ISNULL(@QT_EST,0),
				@VLR_CUSTO = ROUND(ISNULL(dbo.F_VLR_CUSTO_EST_FECHA_PROC_MOV (@CD_EMP,@CD_FILIAL,@CD_PROD_PROC, @DT_MOV,@TP_CUSTO),0),2),
				VLR_CUSTO=@VLR_CUSTO,
				VLR_CUSTO_TOTAL=@VLR_CUSTO*ISNULL(@QT_EST,0)
			where
				CD_PROD = @CD_PROD_PROC
				AND DT_MOV = @DT_MOV

			UPDATE 
				#RS_MOV_RET 
			SET 
				QT_INI = @QT_EST
			where
				CD_PROD = @CD_PROD_PROC
				AND DT_MOV =(SELECT
								MIN(DT_MOV)
							 FROM
								#RS_MOV_RET
							 WHERE
								CD_PROD = @CD_PROD_PROC
								AND DT_MOV > @DT_MOV) 

			--Proximo registro
			FETCH NEXT FROM CalcEstoque INTO @CD_PROD_PROC, 
									 @DT_MOV,
									 @QT_INI, 
									 @QT_SAI,
									 @QT_ENT
		End

	--Fechando cursos
	CLOSE CalcEstoque;
	--Desalocando cursos da memoria
	DEALLOCATE CalcEstoque;


SELECT
*
FROM
#RS_MOV_RET
WHERE
	CD_PROD = CASE WHEN @CD_PROD > 0 THEN @CD_PROD ELSE CD_PROD END
ORDER BY
	CD_PROD,DT_MOV

RETURN  
	DROP TABLE #RS_MOV_RET;
	DROP TABLE #RS_PROD
END

GO
