Залихи под минималното ниво

Опис

Извештајот ги прикажува сите производи чија вкупна залиха е под минималното ниво дефинирано во табелата product. Овозможува навремено нарачување.

SQL решение

CREATE OR REPLACE FUNCTION get_low_stock_products()
RETURNS TABLE (
    product_id INT,
    product_name TEXT,
    category_name TEXT,
    min_stock INT,
    current_stock NUMERIC,
    shortage NUMERIC,
    status TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        p.product_id,
        p.name::TEXT AS product_name,
        c.name::TEXT AS category_name,
        p.min_stock,
        COALESCE(SUM(i.quantity_change), 0) AS current_stock,
        p.min_stock - COALESCE(SUM(i.quantity_change), 0) AS shortage,
        CASE 
            WHEN COALESCE(SUM(i.quantity_change), 0) <= 0 THEN 'КРИТИЧНО'::TEXT
            WHEN COALESCE(SUM(i.quantity_change), 0) < p.min_stock THEN 'НИСКО'::TEXT
            ELSE 'ОК'::TEXT
        END AS status
    FROM project.product p
    JOIN project.category c ON c.category_id = p.category_id
    LEFT JOIN project.inventory i ON i.product_id = p.product_id
    WHERE p.active = TRUE
    GROUP BY p.product_id, p.name, c.name, p.min_stock
    HAVING COALESCE(SUM(i.quantity_change), 0) < p.min_stock
    ORDER BY shortage DESC;
END;
$$;

Релациона алгебра

P(product_id, name, category_id, min_stock, active)
C(category_id, name)
I(inventory_id, product_id, quantity_change)

J1 ← P ⨝ P.category_id = C.category_id C
J2 ← J1 ⟕ P.product_id = I.product_id I

F1 ← σ active=TRUE (J2)

G ← γ product_id, name, category_name, min_stock;
     COALESCE(SUM(quantity_change), 0) → current_stock,
     min_stock - COALESCE(SUM(quantity_change), 0) → shortage (F1)

F2 ← σ current_stock < min_stock (G)

R ← π product_id, product_name, category_name, min_stock, current_stock, shortage, status (F2)

R_final ← τ shortage DESC (R)