Прогноза за потрошувачка на состојки врз основа на продажби

Опис

Извештајот предвидува колку од секоја состојка ќе биде потрошена во следните 30 дена, врз основа на просечната дневна потрошувачка во последните 90 дена. Користи три нивоа на вгнездување: (1) продажби по производ по ден, (2) агрегација по состојка преку рецепт, (3) споредба со моменталната залиха за да се најдат состојки што ќе треба наскоро да се нарачаат.

SQL решение

CREATE OR REPLACE FUNCTION project.get_ingredient_consumption_forecast()
RETURNS TABLE (
    ingredient_id INT,
    ingredient_name TEXT,
    avg_daily_usage NUMERIC,
    forecast_30d NUMERIC,
    current_stock NUMERIC,
    days_until_empty NUMERIC,
    action TEXT
)
LANGUAGE sql
AS $$
    WITH daily_product_sales AS (
        SELECT 
            oi.product_id,
            DATE(o.created_at) AS sale_date,
            SUM(oi.quantity) AS qty_sold
        FROM project.order_item oi
        JOIN project.orders o ON o.order_id = oi.order_id
        WHERE o.status = 'ПЛАТЕНА'
          AND o.created_at >= NOW() - INTERVAL '90 days'
        GROUP BY oi.product_id, DATE(o.created_at)
    ),
    ingredient_usage AS (
        SELECT 
            ri.ingredient_id AS ing_id,
            SUM(dps.qty_sold * ri.quantity_needed)::numeric AS total_used
        FROM daily_product_sales dps
        JOIN project.recipe r ON r.product_id = dps.product_id
        JOIN project.recipe_item ri ON ri.recipe_id = r.recipe_id
        GROUP BY ri.ingredient_id
    ),
    stock AS (
        SELECT 
            ii.ingredient_id AS ing_id,
            COALESCE(SUM(ii.quantity_change), 0)::numeric AS current_stock
        FROM project.ingredient_inventory ii
        GROUP BY ii.ingredient_id
    )
    SELECT 
        i.ingredient_id,
        i.name::TEXT,
        ROUND(iu.total_used / 90.0, 3),
        ROUND((iu.total_used / 90.0) * 30, 2),
        COALESCE(s.current_stock, 0),
        CASE 
            WHEN (iu.total_used / 90.0) > 0 
            THEN ROUND(COALESCE(s.current_stock, 0) / (iu.total_used / 90.0), 1)
            ELSE NULL
        END,
        CASE 
            WHEN COALESCE(s.current_stock, 0) < (iu.total_used / 90.0) * 30 
                 AND i.min_stock > COALESCE(s.current_stock, 0) THEN 'ИТНО НАРАЧАЈ'
            WHEN COALESCE(s.current_stock, 0) < (iu.total_used / 90.0) * 30 THEN 'НАРАЧАЈ'
            ELSE 'ОК'
        END::TEXT
    FROM project.ingredient i
    JOIN ingredient_usage iu ON iu.ing_id = i.ingredient_id
    LEFT JOIN stock s ON s.ing_id = i.ingredient_id
    WHERE i.active = TRUE
    ORDER BY 6 ASC NULLS LAST;
$$;

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

OI(order_id, product_id, quantity, unit_price)
O(order_id, status, created_at)
R(recipe_id, product_id)
RI(recipe_id, ingredient_id, quantity_needed)
I(ingredient_id, name, min_stock, active)
II(id, ingredient_id, quantity_change)

J1 ← OI ⨝ OI.order_id = O.order_id O
F1 ← σ status='ПЛАТЕНА' ∧ created_at ≥ NOW() - INTERVAL '90 days' (J1)

G1 ← γ product_id, sale_date; SUM(quantity) → qty_sold (F1)

J2 ← G1 ⨝ G1.product_id = R.product_id R
J3 ← J2 ⨝ R.recipe_id = RI.recipe_id RI
G2 ← γ ingredient_id; SUM(qty_sold * quantity_needed) → total_used (J3)

G3 ← γ ingredient_id; COALESCE(SUM(quantity_change), 0) → current_stock (II)

J4 ← I ⨝ G2 ⨝ G3
F2 ← σ active=TRUE (J4)

R ← π ingredient_id, ingredient_name, avg_daily_usage, forecast_30d,
      current_stock, days_until_empty, action (F2)

R_final ← τ days_until_empty ASC (R)