-- =============================================
-- SP: sp_invCalculo_Stock_Nuevo_RAPIDO
-- Descripcion: Calcula stock con soporte para lista de articulos (XML)
-- Parametros:
--   @agno: Periodo (ano)
--   @codbus: Codigo articulo individual (opcional)
--   @xml: Lista de articulos en formato XML (opcional)
-- Modos:
--   - XML con lista: Filtra solo esos articulos
--   - codbus especifico: Filtra solo ese articulo
--   - Vacio: Procesa todos los articulos
-- Fecha: 2025-12-27
-- =============================================

IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'sp_invCalculo_Stock_Nuevo_RAPIDO')
    DROP PROCEDURE sp_invCalculo_Stock_Nuevo_RAPIDO
GO

CREATE PROC dbo.sp_invCalculo_Stock_Nuevo_RAPIDO
(
    @agno int,
    @codbus varchar(20) = '',
    @xml varchar(max) = ''
)
AS
SET NOCOUNT ON;
BEGIN
    -- Tabla temporal para lista de articulos
    CREATE TABLE #Articulos (codbus VARCHAR(100) PRIMARY KEY CLUSTERED);

    IF @xml <> '' AND @xml IS NOT NULL
    BEGIN
        DECLARE @nIdXml INT;
        EXEC dbo.sp_xml_preparedocument @nIdXml OUTPUT, @xml;
        INSERT INTO #Articulos (codbus)
        SELECT codbus FROM OPENXML(@nIdXml, '/Articulos/Articulo', 3) WITH (codbus VARCHAR(100));
        EXEC dbo.sp_xml_removedocument @nIdXml;
    END
    ELSE IF @codbus <> '' AND @codbus IS NOT NULL
    BEGIN
        INSERT INTO #Articulos (codbus) VALUES (@codbus);
    END

    DECLARE @hayFiltro BIT = CASE WHEN EXISTS(SELECT 1 FROM #Articulos) THEN 1 ELSE 0 END;

    IF @hayFiltro = 1
    BEGIN
        -- MODO FILTRADO con optimizaciones
        SELECT codbus, codbod, SUM(cantidad) as saltot
        INTO #StockFiltrado
        FROM (
            SELECT p.codbus, p.codbod, CASE WHEN p.operac = '+' THEN p.cantidad ELSE -p.cantidad END as cantidad
            FROM invPasoStockGeneral p WITH (NOLOCK)
            WHERE p.periodo = @agno AND p.operac IN ('+', '-') AND p.codbus IN (SELECT codbus FROM #Articulos)
            UNION ALL
            SELECT p.codbus, p.codbod, -p.cantidad
            FROM invPasoStockGeneral p WITH (NOLOCK)
            WHERE p.periodo = @agno AND p.operac = '=' AND p.codbus IN (SELECT codbus FROM #Articulos)
            UNION ALL
            SELECT p.codbus, p.boddes, p.cantidad
            FROM invPasoStockGeneral p WITH (NOLOCK)
            WHERE p.periodo = @agno AND p.operac = '=' AND p.codbus IN (SELECT codbus FROM #Articulos)
        ) t
        GROUP BY codbus, codbod OPTION (MAXDOP 4);

        CREATE CLUSTERED INDEX IX_sf ON #StockFiltrado(codbus, codbod);

        MERGE invStock WITH (TABLOCK) AS target
        USING #StockFiltrado AS source ON target.codbus = source.codbus AND target.codbod = source.codbod
        WHEN MATCHED THEN UPDATE SET saltot = source.saltot
        WHEN NOT MATCHED THEN INSERT (codbod, codbus, saltot) VALUES (source.codbod, source.codbus, source.saltot);

        ;WITH StockTotal AS (
            SELECT codbus, SUM(saltot) as stogen FROM invStock WITH (NOLOCK) WHERE codbus IN (SELECT codbus FROM #Articulos) GROUP BY codbus
        )
        UPDATE a WITH (TABLOCK) SET a.stogen = ISNULL(t.stogen, 0)
        FROM invArticulos a
        INNER JOIN #Articulos f ON a.codbus = f.codbus
        LEFT JOIN StockTotal t ON a.codbus = t.codbus
        OPTION (MAXDOP 4);

        DROP TABLE #StockFiltrado;
    END
    ELSE
    BEGIN
        -- MODO TODOS (V4 optimizado)
        SELECT codbus, codbod, SUM(cantidad) as saltot INTO #Stock
        FROM (
            SELECT codbus, codbod, CASE WHEN operac = '+' THEN cantidad ELSE -cantidad END as cantidad
            FROM invPasoStockGeneral WITH (NOLOCK) WHERE periodo = @agno AND operac IN ('+', '-')
            UNION ALL SELECT codbus, codbod, -cantidad FROM invPasoStockGeneral WITH (NOLOCK) WHERE periodo = @agno AND operac = '='
            UNION ALL SELECT codbus, boddes, cantidad FROM invPasoStockGeneral WITH (NOLOCK) WHERE periodo = @agno AND operac = '='
        ) t GROUP BY codbus, codbod OPTION (MAXDOP 4);

        CREATE CLUSTERED INDEX IX_tmp ON #Stock(codbus, codbod);

        MERGE invStock WITH (TABLOCK) AS target USING #Stock AS source ON target.codbus = source.codbus AND target.codbod = source.codbod
        WHEN MATCHED THEN UPDATE SET saltot = source.saltot
        WHEN NOT MATCHED THEN INSERT (codbod, codbus, saltot) VALUES (source.codbod, source.codbus, source.saltot);

        ;WITH StockTotal AS (SELECT codbus, SUM(saltot) as stogen FROM invStock WITH (NOLOCK) GROUP BY codbus)
        UPDATE a WITH (TABLOCK) SET a.stogen = t.stogen FROM invArticulos a INNER JOIN StockTotal t ON a.codbus = t.codbus OPTION (MAXDOP 4);

        SELECT DISTINCT codbus INTO #ConMov FROM invDetalle WITH (NOLOCK) WHERE YEAR(fecdoc) = @agno
        UNION SELECT DISTINCT codbus FROM venDetalle WITH (NOLOCK) WHERE YEAR(fecdoc) = @agno;
        UPDATE invArticulos SET costo = 0 WHERE activo = 1 AND costo <> 0 AND codbus NOT IN (SELECT codbus FROM #ConMov);
        DROP TABLE #Stock; DROP TABLE #ConMov;
    END
    DROP TABLE #Articulos;
END
GO

PRINT 'SP sp_invCalculo_Stock_Nuevo_RAPIDO creado exitosamente'
GO