-- =============================================
-- Author:		<YONATAN GAETE>
-- Edition date: <19-06-2024>
-- Description:	<Se agrega numlin para Lotes y Series>
-- =============================================
ALTER PROCEDURE dbo.sp_invArticulos_Toma
(
@bodega int = 0,
@codfam varchar(50),
@codsub varchar(50),
@codbus varchar(50),
@codbar varchar(50),
@nomart varchar(300),
@top int = 0,
@fecha date,
@limpiar int = 0
)
AS

DECLARE @codbusi	char(100)='0'
DECLARE @codbusf	char(100)='ZZZZZZZZZZZZZZZZZZZZZZZZ'
DECLARE @famini     varchar(10)='0'
DECLARE @famfin     varchar(10)='ZZZZZZZZZZZZZZZZZZZZZZZ'
DECLARE @subini     varchar(10)='0'
DECLARE @subfin     varchar(10)='ZZZZZZZZZZZZZZZZZZZZZZ'

IF @limpiar=1 BEGIN
SELECT invArticulos.codbar,
'0' as Stock_dif,
invArticulos.codalt,
invArticulos.ubiinv1,
invArticulos.codbus,
fecha_toma,
invArticulos.NOMART,
COALESCE(stock_fisico,-1) AS stock_fisico ,
0 as stock_logico
,documento
,0 as idlinea
,0 as stock_alt
,0 AS numlin
FROM      
          invArticulos left outer JOIN
                        invtoma  ON invArticulos.codbus = invToma.codbus
AND InvToma.bodega= @bodega 
AND InvToma.fecha_toma= @fecha 
WHERE 1 = 2

return 0

END

IF  @codbus <> '*' 
BEGIN
SET @codbusi=@codbus
SET @codbusF=@codbus
--SET @codbusi=@codbus
--SET @codbusF=@codbus

SELECT top (1) @codbusi=codbus,@codbusf=codbus,@Codbus=codbus
FROM     invArticulos ART WHERE  (ART.codbus= @codbus) OR (ART.codbar=@codbus) OR (ART.codalt=@codbus)
OR ART.codbus IN (SELECT ALT.codbus FROM invArticulosAlt ALT WHERE ALT.codalt=@codbus)

--select @codbusi,@codbusf,@Codbus

END

IF  @codFAM <> '*' 
BEGIN
SET @FAMini=@codfam
SET @FAMfin=@codFAM
END


IF  @codsub <> '*' 
BEGIN
SET @subini=@codsub
SET @subfin=@codsub
END


IF @nomart <> '*' 
BEGIN

SELECT   @codbusi=min(codbus)
FROM            invArticulos
WHERE        (nomart LIKE '%' + @NOMART + '%')

SELECT   @codbusf=max(codbus)
FROM            invArticulos
WHERE        (nomart LIKE '%'+@NOMART + '%')

END


IF @codbar <> '*' 
BEGIN

SELECT  top (1) @codbusi=codbus,@codbusf=codbus
FROM     invArticulos WHERE  codbar=@codbar
END

--SELECT @codbusi



Declare @DayOfMonth TinyInt Set @DayOfMonth = 1
Declare @Month TinyInt Set @Month = 1
Declare @Year Integer 

Set @Year =YEAR(@fecha)

DECLARE @FECHAINI DATETIME
Select @FECHAINI=DateAdd(day, @DayOfMonth - 1, 
          DateAdd(month, @Month - 1, 
              DateAdd(Year, @Year-1900, 0)))



CREATE TABLE #tmpBus
(
   codbus varchar(200),
   stock_logicoAux decimal (18,4),
   stock_logicoAuxalt decimal (18,4)
)


INSERT INTO #tmpBus
EXEC sp_invAuxiliar_Existencia_Stim @FECHAINI,@fecha,@codbusi, @codbusf, @famini,@famfin, @subini,@subfin,@bodega ,'1',@fecha



--select * from #tmpbus



SELECT invArticulos.codbar,
'0' as Stock_dif,
invArticulos.codalt,
invArticulos.ubiinv1,
invArticulos.codbus,
fecha_toma,
invArticulos.NOMART,
COALESCE(stock_fisico,-1) AS stock_fisico ,
COALESCE((select stock_logicoAux from #tmpbus where invarticulos.codbus=#tmpbus.codbus),0) as stock_logico
,documento
, 0 as idlinea
, 0 as stock_alt
,invtoma.numlin
FROM      
          invArticulos left outer JOIN
          invtoma  ON invArticulos.codbus = invToma.codbus
AND InvToma.bodega= @bodega 
AND InvToma.fecha_toma= @fecha 
WHERE
invArticulos.activo=1
    --        AND      (invArticulos.codfam    = @codfam OR @codfam = '*')   
 			--AND      (invArticulos.codsub    = @codsub OR @codsub = '*')
			AND      (invArticulos.codbus    = @codbus OR @codbus = '*')
			----AND      (invArticulos.codbar    = @codbar OR @codbar = '*')
			--AND      (invArticulos.nomart    LIKE '%'+@nomart +'%'  OR @nomart= '*')                  
AND coalesce(documento,0) <=0
AND EXISTS(select codbus FROM #tmpBus WHERE #tmpBus.codbus=invarticulos.codbus)
--ORDER BY stock_logico desc  


UNION ALL

 SELECT invArticulos.codbar,
'0' as Stock_dif,
invArticulos.codalt,
invArticulos.ubiinv1,
invArticulos.codbus,
fecha_toma,
invArticulos.NOMART,
COALESCE(stock_fisico,-1) AS stock_fisico ,
0 as stock_logico
,documento
, 0 as idlinea
, 0 as stock_alt
,invtoma.numlin
FROM      
          invArticulos left outer JOIN
                        invtoma  ON invArticulos.codbus = invToma.codbus
AND InvToma.bodega= @bodega 
AND InvToma.fecha_toma= @fecha 
WHERE
invArticulos.activo=1
   --         AND      (invArticulos.codfam    = @codfam OR @codfam = '*')
 		--	AND      (invArticulos.codsub    = @codsub OR @codsub = '*')
			AND      (invArticulos.codbus    = @codbus OR @codbus = '*')
			--AND      (invArticulos.codbar    = @codbar OR @codbar = '*')
			--AND      (invArticulos.nomart LIKE '%'+@nomart +'%' OR @nomart = '*')
AND NOT EXISTS(select codbus FROM #tmpBus WHERE #tmpBus.codbus=invarticulos.codbus)
ORDER BY numlin desc  



        




  
----SELECT codbus 
----INTO #TempTable
----FROM invArticulos

--ALTER TABLE #TempTable ADD PRIMARY KEY NONCLUSTERED (codbus)


--SELECT invArticulos.codbar,
--'0' as Stock_dif,
--codalt,
--ubiinv1,
--codbus,
--'1900-01-01' AS fecha_toma,
--NOMART,
--0 as stock_fisico,
--0 as stock_logico
--INTO #TODO
--FROM invArticulos 

--UNION ALL

--SELECT '' as codbar,
--'0' as Stock_dif,
--'' as codalt,
--'' as ubiinv1,
-- invToma.codbus,
--fecha_toma,
--'' as NOMART,
--stock_fisico,
--stock_logico
--FROM      
--          invToma INNER JOIN
--                         invArticulos ON invArticulos.codbus = invToma.codbus
--AND InvToma.bodega= @bodega  AND
--InvToma.fecha_toma= @fecha 
--WHERE 
--invArticulos.activo=1    
-- 			AND      (invArticulos.codsub    = @codsub OR @codsub = '*')
--			AND      (invArticulos.codbus    = @codbus OR @codbus = '*')
--			AND      (invArticulos.codbar    = @codbar OR @codbar = '*')
--			----AND      (invArticulos.nomart    LIKE '%'+@nomart +'%'  OR @nomart= '*')                  
--ORDER BY stock_logico desc  
        

	 
----SELECT '' as codbar,
---- Stock_dif,
----codalt,
----ubiinv1,
----codbus,
----fecha_toma,
----NOMART,
----stock_fisico,
----stock_logico
----FROM #TODO
----ORDER BY CODBUS

  
--SELECT     InvToma.fecha_toma, InvToma.codbus, InvToma.nomart, InvToma.stock_logico, InvToma.stock_fisico, InvToma.documento, InvToma.bodega, 
--                      invArticulos.codfam, invArticulos.codsub, invArticulos.codbar,'0' as Stock_dif
--FROM         InvToma INNER JOIN
--                      invArticulos ON InvToma.codbus = invArticulos.codbus
--WHERE InvToma.bodega= @bodega AND convert(date,fecha_toma)= @fecha 
--			AND      ((invArticulos.codsub    = @codsub) OR (@codsub = '*'))
--			AND      ((invArticulos.codbus    = @codbus) OR (@codbus = '*'))
--			AND      ((invArticulos.codbar    = @codbar) OR (@codbar = '*'))
--			AND      ((invArticulos.nomart    LIKE '%'+@nomart +'%' ) OR (@nomart= '*'))

--SELECT     invArticulos.codbus, invArticulos.nomart, COALESCE (invStock.saltot, 0) AS saltot, COALESCE (COALESCE (InvToma.stock_logico, invStock.saltot), 0) AS stock_logico, 
--                      COALESCE (InvToma.stock_fisico, 0) AS Stock_Fisico, '0' AS Stock_dif, COALESCE (InvToma.documento, 0) AS documento, InvToma.fecha_toma, invStock.codbod, 
--                      invStock.saltot AS Expr2
--FROM         invStock LEFT OUTER JOIN
--                      InvToma ON invStock.codbus = InvToma.codbus RIGHT OUTER JOIN
--                      invArticulos ON invStock.codbus = invArticulos.codbus
--          AND invStock.codbod =@bodega
--          AND codbod=@bodega
--          AND documento = 0 AND COALESCE(DOCUMENTO,0) = 0
--          AND convert(date,InvToma.fecha_toma) = @FECHA
-- WHERE    ((invArticulos.codfam    = @codfam) OR (@codfam = '*'))
--AND      ((invArticulos.codsub    = @codsub) OR (@codsub = '*'))
--AND      ((invArticulos.codbus    = @codbus) OR (@codbus = '*'))
--AND      ((invArticulos.codbar    = @codbar) OR (@codbar = '*'))
--AND      ((invArticulos.nomart    LIKE '%'+@nomart +'%' ) OR (@nomart= '*'))
--ORDER BY invStock.saltot desc