ALTER PROCEDURE [dbo].[sp_invGeneraOC]
(
    @codfam      varchar(30),
    @codsub      varchar(30),
    @fecini      date,
    @fecfin      date,
    @uniini      int,
    @codbod      int,
    @listaprecio int = 1,
    @codbus      varchar(50) = '',
    @codalt      varchar(50) = '',
    @codbar      varchar(50) = '',
    @nomart      varchar(300) = '',
    @tipo        int = 0,
    @top         int = 1,
    @mes         int = 1,
    @rutcte      int = 0
)
AS
BEGIN
    SET NOCOUNT ON;

 
    SELECT
        D.codbus,
        ISNULL(SUM(D.cantidad), 0) AS cantidad,
        ISNULL(SUM(D.salcan),   0) AS salcan
    INTO #D
    FROM invDetalle D
    INNER JOIN invEncabezado E
        ON D.coddoc = E.coddoc
       AND D.numinv = E.numinv
    WHERE D.coddoc = 8
      AND D.fecdoc BETWEEN @fecini AND @fecfin
      AND ((D.codbod = @codbod) OR (@codbod = 0))
      AND ((@uniini = 0) OR (D.coduni = @uniini))       -- 🔹 filtro por coduni en DETALLE
      AND ((E.rutcte = @rutcte) OR (@rutcte = 0))
    GROUP BY D.codbus;

   
    SELECT
        S.codbus,
        ISNULL(SUM(S.saltot), 0) AS saltot
    INTO #ST
    FROM invStock S
    GROUP BY S.codbus;

  
    SELECT
        ISNULL(S.codbod, 0) AS codbod,
        S.codbus,
        ISNULL(S.saltot, 0) AS saltot
    INTO #S
    FROM invStock S
    WHERE S.codbod = @codbod;

    
    SELECT
        VLD.codbus          AS codbusVenta,
        ISNULL(VLD.valor1, 0) AS precioVenta,
        ISNULL(VLD.margen, 0) AS margenVenta
    INTO #LP
    FROM venListaDetalleRango VLD
    WHERE VLD.codlis = @listaprecio;

    IF @codbod = 0
    BEGIN
        SELECT
            A.codbar,
            A.codbus,
            A.codalt,
            A.nomart,
            ISNULL(DT.cantidad, 0) AS solicitada,
            ISNULL(DT.salcan,   0) AS pendiente,
            ISNULL(
                dbo.invQuiebreStock_Art(A.codbus, @fecini, @fecfin),
                0
            ) AS quiebreStock,
            ISNULL(
                dbo.invPromedioVentas_Art60(
                    A.codbus,
                    YEAR(DATEADD(MONTH, -1, GETDATE())),
                    YEAR(DATEADD(MONTH, -(@mes), GETDATE())),
                    MONTH(DATEADD(MONTH, -1, GETDATE())),
                    MONTH(DATEADD(MONTH, -(@mes), GETDATE()))
                ),
                0
            ) AS promedioVentas,
            ISNULL(ST.saltot, 0)      AS stogen,
            ISNULL(A.stomin,  0)      AS stomin,
            0                         AS encompra,
            0                         AS acomprar,
            CONVERT(int, (
                SELECT TOP (1) D2.preuni
                FROM invDetalle D2
                WHERE D2.coddoc = 1
                  AND D2.codbus = A.codbus
                ORDER BY D2.fecdoc DESC
            )) AS UltimoPrecio,
            ''                        AS proveedor,
            ISNULL(LP.precioVenta, 0) AS precioVenta,
            ISNULL(LP.margenVenta, 0) AS margenVenta,
            A.costo,
            0 AS preciocompra
        INTO #b1
        FROM invArticulos A
        LEFT JOIN #D  AS DT ON A.codbus = DT.codbus
        LEFT JOIN #ST AS ST ON A.codbus = ST.codbus
        LEFT JOIN #LP AS LP ON A.codbus = LP.codbusVenta
        WHERE ((A.codfam = @codfam) OR (@codfam = '(TODAS)'))
          AND ((A.codsub = @codsub) OR (@codsub = '(TODAS)'))
          AND (
                @uniini = 0
                OR EXISTS (SELECT 1 FROM #D D2 WHERE D2.codbus = A.codbus)
              );   -- 🔹 si hay unidad -> solo artículos con movimiento en esa unidad
    END
    ELSE
    BEGIN
        SELECT
            A.codbar,
            A.codbus,
            A.codalt,
            A.nomart,
            ISNULL(DT.cantidad, 0) AS solicitada,
            ISNULL(DT.salcan,   0) AS pendiente,
            ISNULL(
                dbo.invQuiebreStock_Art(A.codbus, @fecini, @fecfin),
                0
            ) AS quiebreStock,
            ISNULL(
                dbo.invPromedioVentas_Art60(
                    A.codbus,
                    YEAR(DATEADD(MONTH, -1, GETDATE())),
                    YEAR(DATEADD(MONTH, -(@mes), GETDATE())),
                    MONTH(DATEADD(MONTH, -1, GETDATE())),
                    MONTH(DATEADD(MONTH, -(@mes), GETDATE()))
                ),
                0
            ) AS promedioVentas,
            ISNULL(SB.saltot, 0)      AS stogen,
            ISNULL(A.stomin,  0)      AS stomin,
            0                         AS encompra,
            0                         AS acomprar,
            CONVERT(int, (
                SELECT TOP (1) D2.preuni
                FROM invDetalle D2
                WHERE D2.coddoc = 1
                  AND D2.codbus = A.codbus
                ORDER BY D2.fecdoc DESC
            )) AS UltimoPrecio,
            ''                        AS proveedor,
            ISNULL(LP.precioVenta, 0) AS precioVenta,
            ISNULL(LP.margenVenta, 0) AS margenVenta,
            A.costo,
            0 AS preciocompra
        INTO #b2
        FROM invArticulos A
        LEFT JOIN #D  AS DT ON A.codbus = DT.codbus
        LEFT JOIN #S  AS SB ON A.codbus = SB.codbus
        LEFT JOIN #LP AS LP ON A.codbus = LP.codbusVenta
        WHERE ((A.codfam = @codfam) OR (@codfam = '(TODAS)'))
          AND ((A.codsub = @codsub) OR (@codsub = '(TODAS)'))
          AND (
                @uniini = 0
                OR EXISTS (SELECT 1 FROM #D D2 WHERE D2.codbus = A.codbus)
              );
    END

   
    SELECT
        DOC.codbus,
        ISNULL(SUM(DOC.salcan), 0) AS saldo
    INTO #bb
    FROM invDetalle AS DOC
    INNER JOIN invEncabezado AS E
        ON DOC.numinv = E.numinv
       AND DOC.coddoc = E.coddoc
    WHERE DOC.coddoc = 6
      AND E.Estado <> 3
      AND E.cerrado <> 1
    GROUP BY DOC.codbus;

    
    IF @codbod = 0
    BEGIN
        UPDATE B
        SET B.encompra = BB.saldo
        FROM #b1 AS B
        INNER JOIN #bb AS BB
            ON B.codbus = BB.codbus;

        UPDATE #b1
        SET acomprar =
        CASE
            WHEN (pendiente - stogen + stomin - encompra + quiebreStock + promedioVentas) < 0
                THEN 0
            ELSE (pendiente - stogen + stomin - encompra + quiebreStock + promedioVentas)
        END;

        IF @tipo = 1
        BEGIN
            SELECT *
            FROM #b1
            ORDER BY quiebreStock DESC, acomprar DESC;
        END

        IF @tipo = 2
        BEGIN
            SELECT TOP (@top) *
            FROM #b1
            ORDER BY promedioVentas DESC;
        END

        IF @codbus <> ''
        BEGIN
            SELECT *
            FROM #b1
            WHERE codbus LIKE '%' + @codbus + '%'
            ORDER BY quiebreStock DESC, acomprar DESC;
        END

        IF @codalt <> ''
        BEGIN
            SELECT *
            FROM #b1
            WHERE codalt LIKE '%' + @codalt + '%'
            ORDER BY quiebreStock DESC, acomprar DESC;
        END

        IF @codbar <> ''
        BEGIN
            SELECT *
            FROM #b1
            WHERE codbar LIKE '%' + @codbar + '%'
            ORDER BY quiebreStock DESC, acomprar DESC;
        END

        IF @nomart <> ''
        BEGIN
            SELECT *
            FROM #b1
            WHERE nomart LIKE '%' + @nomart + '%'
            ORDER BY quiebreStock DESC, acomprar DESC;
        END
    END
    ELSE
    BEGIN
        UPDATE B
        SET B.encompra = BB.saldo
        FROM #b2 AS B
        INNER JOIN #bb AS BB
            ON B.codbus = BB.codbus;

        UPDATE #b2
        SET acomprar = pendiente - stogen + stomin - encompra + quiebreStock + promedioVentas;

        IF @tipo = 1
        BEGIN
            SELECT *
            FROM #b2
            ORDER BY quiebreStock DESC, acomprar DESC;
        END

        IF @tipo = 2
        BEGIN
            SELECT TOP (@top) *
            FROM #b2
            ORDER BY promedioVentas DESC;
        END

        IF @codbus <> ''
        BEGIN
            SELECT *
            FROM #b2
            WHERE promedioVentas > 0
              AND codbus LIKE '%' + @codbus + '%'
            ORDER BY quiebreStock DESC, acomprar DESC;
        END

        IF @codalt <> ''
        BEGIN
            SELECT *
            FROM #b2
            WHERE codalt LIKE '%' + @codalt + '%'
            ORDER BY quiebreStock DESC, acomprar DESC;
        END

        IF @codbar <> ''
        BEGIN
            SELECT *
            FROM #b2
            WHERE codbar LIKE '%' + @codbar + '%'
            ORDER BY quiebreStock DESC, acomprar DESC;
        END

        IF @nomart <> ''
        BEGIN
            SELECT *
            FROM #b2
            WHERE nomart LIKE '%' + @nomart + '%'
            ORDER BY quiebreStock DESC, acomprar DESC;
        END
    END
END
GO