Changes between Initial Version and Version 1 of AdvancedReport9

Timestamp:

09/25/26 16:22:27 (5 days ago)

Author:

201178

Comment:

--

AdvancedReport9

@@ +1 @@
+= Прогноза за потрошувачка на состојки врз основа на продажби =
+
+== Опис ==
+
+Извештајот предвидува колку од секоја состојка ќе биде потрошена во следните 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)
+}}}