ALTER PROC [dbo].[sp_invCalculo_Costo_Nuevo]
(
 @tipo int = 0,
 @agno int = 0,
 @codbus varchar(20) = '*',
 @xml varchar(max)='',
 @mescalculod int=1,
 @mescalculoh int=12,
 @codfam varchar(50) ='*',
 @codsub varchar(50) ='*'

)  
AS
 DECLARE @MESFILTROACUMULADO AS INT=0
 DECLARE @StartTime AS DATETIME = GETDATE()

 DECLARE @_xml as varchar(max) 

 set @_xml= @xml


 DECLARE @tblRegistrosTemp TABLE (Indice INT IDENTITY(1,1), codbus VARCHAR(100),nombre varchar(100))
 



IF @xml <> '' and @tipo >= 1
 BEGIN
 
 --select @xml 
 --select @tipo 
    --select 'ok' 

    DECLARE @nIdXml INT
    EXEC dbo.sp_xml_preparedocument @nIdXml OUTPUT, @_xml
    INSERT INTO @tblRegistrosTemp (codbus)
    SELECT codbus
    FROM OPENXML (@nIdXml,'/Articulos/Articulo', 3)
    WITH (codbus VARCHAR(100))
    EXEC dbo.sp_xml_removedocument @nIdXml
 END
     
	 SELECT codbus 
	 INTO #tblRegistros	 	 FROM invArticulos INNER JOIN
     invFamilia ON invArticulos.codfam = invFamilia.codfam
	 WHERE (invFamilia.controlstock = 1)
	 AND (@codbus='*' OR codbus=@codbus)
	 AND (@codfam='*' OR invArticulos.codfam=@codfam)
	 AND (@codsub='*' OR invArticulos.codsub=@codsub)
	 AND (@tipo=0)	 ----CON FILTRO
	 --AND CODBUS='FIAMCECI0359'
	 UNION ALL
	 
	 SELECT Tabla.codbus FROM  invArticulos INNER JOIN
                         invFamilia ON invArticulos.codfam = invFamilia.codfam INNER JOIN
                         @tblRegistrosTemp as Tabla ON invArticulos.codbus = Tabla.codbus
	 WHERE (invFamilia.controlstock = 1)
	 

	 CREATE NONCLUSTERED INDEX ix_tempCIndexBef ON #tblRegistros (codbus)
    
	--select * from  @tblRegistrosTemp


	DECLARE @periodo int = @agno

	IF @tipo=1 
	BEGIN

			--DECLARE @PERIODO INT =@agno
			DECLARE @MES INT =0
			DECLARE @SALIR INT = 0
			DECLARE @MESABIERTO INT =1

			DECLARE @MES1 INT =0 ,  @MES2 INT =0 , @MES3 INT =0 ,  @MES4 INT =0 ,@MES5 INT =0 ,  @MES6 INT =0 
			DECLARE @MES7 INT =0 ,  @MES8 INT =0 , @MES9 INT =0 ,  @MES10 INT =0 ,@MES11 INT =0 ,  @MES12 INT =0 


			SELECT top 1 @MES1=mes01,@MES2=mes02,@MES3=mes03,@MES4=mes04,@MES5=mes05,@MES6=mes06,
			 @MES7=mes07,@MES8=mes08,@MES9=mes09,@MES10=mes10,@MES11=mes11,@MES12=mes12
			FROM invPeriodos
			WHERE año=@PERIODO


			DECLARE @ESTADO INT = 0
			SET @mescalculod =0 
			SET @mescalculoh =0 

			WHILE(@MES < 12 )
			BEGIN
 
			  SET @MES = @MES + 1 
			  
			  SET @ESTADO= CASE WHEN @MES=1 THEN @MES1  
			  WHEN @MES=2 THEN @MES2 
			  WHEN @MES=3 THEN @MES3 
			  WHEN @MES=4 THEN @MES4
			  WHEN @MES=5 THEN @MES5
			  WHEN @MES=6 THEN @MES6
			  WHEN @MES=7 THEN @MES7
			  WHEN @MES=8 THEN @MES8
			  WHEN @MES=9 THEN @MES9
			  WHEN @MES=10 THEN @MES10
			  WHEN @MES=11 THEN @MES11
			  WHEN @MES=12 THEN @MES12
			  END

			  --SELECT @MES
			
			  SET @MESABIERTO = @MES

			  IF @ESTADO = 1 AND @mescalculoD = 0
			  BEGIN 
			   --SELECT @MES
			   SET  @mescalculoD = @MES
			  END

			  IF @ESTADO = 1
			  BEGIN 
			  --SELECT @MES
			   SET  @mescalculoH = @MES
			  END




			END

			--SET  @mescalculoD = @MESABIERTO
			--SET  @mescalculoh  = 12

			
			--select @mescalculoh

	END


 
 DECLARE @mesdesde int = @mescalculod
 DECLARE @meshasta int= @mescalculoh

 --SELECT @mesdesde
 --SELECT @meshasta

SELECT  coddoc,operac,Alias_doc INTO #DOCUMENTOINV FROM vendocumentos WHERE grudoc=1
SELECT  codsii,operac,Alias_doc INTO #DOCUMENTOS FROM vendocumentos WHERE grudoc=50 or grudoc=10


CREATE NONCLUSTERED  INDEX Indice1 ON #DOCUMENTOINV (coddoc)
CREATE NONCLUSTERED  INDEX Indice1 ON #DOCUMENTOS (codsii)




--SELECT @MESABIERTO

BEGIN  
            
	SELECT     'I' AS sistema, invDetalle.coddoc, invDetalle.numinv, invDetalle.numlin,hordoc as fecdoc, invDetalle.codbus, invDetalle.cantidad, invDetalle.preuni, invDetalle.codbod, 
						  invencabezado.boddes, #DOCUMENTOINV.operac, 0 AS tipref1, '0' AS docref1, invDetalle.coddoc AS codsii
	,0 AS grudoc,
	INVEncabezado.fecing,CASE WHEN invEncabezado.coddoc=1 THEN 0 ELSE 1 END AS orden,0 as sis,invEncabezado.fecdoc as fecha, 0 as orden2,periodo,mes
	INTO #A
	FROM            invEncabezado INNER JOIN
                         invDetalle ON invEncabezado.coddoc = invDetalle.coddoc AND invEncabezado.numinv = invDetalle.numinv INNER JOIN
                         #tblRegistros  ON invDetalle.codbus = #tblRegistros.codbus INNER JOIN #DOCUMENTOINV  ON invEncabezado.coddoc = #DOCUMENTOINV.CODDOC 
	WHERE invEncabezado.periodo = @periodo	AND invEncabezado.mes between @mesdesde AND @meshasta
	AND invEncabezado.coddoc < 6  
	AND invEncabezado.estado <> 9
	
	
	UNION  ALL ---FACTURAS
	
	SELECT     'V' AS sistema, venDetalle.coddoc, venDetalle.numinv, venDetalle.numlin, hordoc as fecdoc, venDetalle.codbus, venDetalle.cantidad, venDetalle.preuni, venDetalle.codbod, 
						  venencabezado.boddes, #DOCUMENTOS.operac, venEncabezado.tipref1 AS tipref1, venEncabezado.docref1 AS docref1, venEncabezado.codsii AS codsii
	,venDetalle.grudoc
	,VENEncabezado.fecing,1 AS orden,1 as sis,venEncabezado.fecdoc as fecha ,  0 as orden2,periodo,mes
	FROM            venDetalle INNER JOIN
                         venEncabezado ON venDetalle.grudoc = venEncabezado.grudoc AND venDetalle.coddoc = venEncabezado.coddoc AND 
                         venDetalle.numinv = venEncabezado.numinv INNER JOIN
                         #DOCUMENTOS  ON venEncabezado.codsii = #DOCUMENTOS.codsii INNER JOIN
                         #tblRegistros ON venDetalle.codbus = #tblRegistros.codbus
	WHERE   (venEncabezado.grudoc = 10) 
	AND (venEncabezado.periodo=@periodo AND venEncabezado.mes between @mesdesde AND @meshasta)
	AND (venEncabezado.estado <> 9)
	AND (venEncabezado.codsii NOT IN (55,56,60,61) OR (venEncabezado.codsii IN (55,56,60,61) AND motivo IN (1,4)))  

	UNION ALL ---GUIAS
			
	SELECT     'V' AS sistema, venDetalle.coddoc, venDetalle.numinv, venDetalle.numlin,hordoc as fecdoc, venDetalle.codbus, venDetalle.cantidad, venDetalle.preuni, venDetalle.codbod, 
						  venencabezado.boddes, #DOCUMENTOS.operac, 0 AS tipref1, '0' AS docref1, venEncabezado.codsii AS codsii
	,venDetalle.grudoc
	,VENEncabezado.fecing,1 AS orden,1 as sis,venEncabezado.fecdoc as fecha,  0 as orden2,periodo,mes
	FROM            venDetalle INNER JOIN
                         venEncabezado ON venDetalle.grudoc = venEncabezado.grudoc AND venDetalle.coddoc = venEncabezado.coddoc AND 
                         venDetalle.numinv = venEncabezado.numinv INNER JOIN
                         #DOCUMENTOS  ON venEncabezado.codsii = #DOCUMENTOS.codsii INNER JOIN
                         #tblRegistros ON venDetalle.codbus = #tblRegistros.codbus
	WHERE   (venEncabezado.grudoc = 50) 
	AND (venEncabezado.periodo=@periodo AND venEncabezado.mes between @mesdesde AND @meshasta)
	AND (venEncabezado.estado <> 9) 
	AND (venEncabezado.tip_con=0)
	--AND (venEncabezado.motivo=1)
	

  

	
	SELECT * FROM #A
	ORDER BY codbus,fecha,orden,fecdoc,orden2,numlin

	--DELETE invPasoStockGeneral 
	--WHERE periodo=@agno

	INSERT INTO invPasoStockGeneral (periodo,codbus,codbod,boddes,cantidad,operac)
	SELECT @agno,codbus,codbod,boddes,cantidad,operac FROM #A 
    


	PRINT DATEDIFF(ms,@StartTime,GETDATE()) SET @StartTime  = GETDATE()

	--SELECT * FROM [invCalculoStockIncial]
	--WHERE periodo=@agno

--SET @MESFILTROACUMULADO= (select  TOP(1) mes
--FROM invCalculoStockIncial
--where periodo = @agno AND mes < @mescalculod
--AND exists (select codbus from #tblRegistros where #tblRegistros.codbus = invCalculoStockIncial.codbus)
--order by mes desc)

--SELECT @MESFILTROACUMULADO



SELECT * INTO #invCalculoStockIncial
from invCalculoStockIncial
WHERE periodo=@agno AND mes < @mescalculod
AND exists (select codbus from #tblRegistros where #tblRegistros.codbus = invCalculoStockIncial.codbus)
order by mes desc,  identificadororden desc

--select * from invCalculoStockIncial A
--where mes =  (select top(1) mes from invCalculoStockIncial B WHERE A.codbus = b.codbus AND A.codbod = B.codbod order by mes desc)



select   periodo, mes, codbus, codbod, costo, costobodega, 
stockGral, stockGralOrigen, stockGralDestino, stockBodega, stockBodegaOrigen, stockBodegaDes, 
                         saldoGral, loging, fecing, logmod, fecmod, identificadororden
						 ,ROW_NUMBER() OVER (PARTITION BY codbus,codbod ORDER BY MES DESC) as idorden
--(SELECT  sum(stockBodega) FROM #invCalculoStockIncial AS t 
--WHERE t.codbus=#invCalculoStockIncial.codbus ) AS _stockGral
--,
--(SELECT TOP (1) saldoGral FROM #invCalculoStockIncial AS t 
--WHERE t.codbus=#invCalculoStockIncial.codbus order by identificadororden desc ) AS _saldoGral
--,saldocodbod
--,
--(SELECT TOP (1) costo FROM #invCalculoStockIncial AS t 
--WHERE t.codbus=#invCalculoStockIncial.codbus order by identificadororden desc ) AS _costo
, 0 as  _stockGral
, 0 as  _saldoGral
, 0 as  _costo
,saldocodbod
INTO #detalle
FROM #invCalculoStockIncial 

SELECT * FROM #DETALLE
WHERE idorden = 1
--SELECT * FROM 
-- #invCalculoStockIncial 

SELECT codbus, stockGral , saldoGral  , costo 
,ROW_NUMBER() OVER (PARTITION BY codbus ORDER BY MES DESC) as idorden
	INTO #TOTALES
	FROM #invCalculoStockIncial




SELECT codbus, stockGral AS _stockGral , saldoGral AS _saldoGral , costo AS _costo
FROM #TOTALES
WHERE idorden =1 
--group by codbus
--WHERE exists (select codbus from #tblRegistros where #tblRegistros.codbus = #invCalculoStockIncial.codbus)



	--SELECT * FROM invPasoStockGeneral where periodo = @agno
	--ORDER BY codbus,fecha,orden,fecdoc,numlin


	--SELECT * FROM #REFRENCIAS


   --SELECT * FROM invPasoStockGeneral
END