Changes between Initial Version and Version 1 of AdvancedReport6

Timestamp:

09/24/26 02:08:25 (7 days ago)

Author:

201178

Comment:

--

AdvancedReport6

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