Changes between Version 2 and Version 3 of Напредни извештаи од базата (SQL и складирани процедури)

Timestamp:

06/26/25 14:56:39 (11 days ago)

Author:

183175

Comment:

--

Напредни извештаи од базата (SQL и складирани процедури)

@@ -92 +92 @@
 Креираме повик до API за предикција на ризична залиха \\
 кое е поставено на {{{app.js}}} како {{{/admin/stock-alerts}}}.\\
+\\
+{{{#!sql
+SELECT 
+        p.id_product,
+        p.product_name,
+        sz.size_label AS size,
+        COALESCE(SUM(s.quantity), 0) AS current_stock,
+        ROUND(AVG(cq.weekly_sales), 2) AS avg_weekly_sales,
+        CASE 
+          WHEN COALESCE(SUM(s.quantity), 0) <= ROUND(AVG(cq.weekly_sales), 2) THEN 'ИТНО'
+          ELSE 'Набљудувај'
+        END AS status
+      FROM products p
+      JOIN stock s ON p.id_product = s.id_product
+      JOIN sizes sz ON s.id_size = sz.id
+      LEFT JOIN (
+        SELECT 
+          s.id_product,
+          s.id_size,
+          COUNT(*) AS weeks,
+          SUM(c.quantity)::decimal / COUNT(DISTINCT DATE_TRUNC('week', o.order_date)) AS weekly_sales
+        FROM contains c
+        JOIN orders o ON c.id_order = o.id_order
+        JOIN stock s ON c.id_stock = s.id_stock
+        WHERE o.order_date >= NOW() - INTERVAL '4 weeks'
+        GROUP BY s.id_product, s.id_size
+      ) cq ON cq.id_product = s.id_product AND cq.id_size = s.id_size
+      GROUP BY p.id_product, p.product_name, sz.size_label
+      HAVING ROUND(AVG(cq.weekly_sales), 2) IS NOT NULL
+      ORDER BY status DESC, avg_weekly_sales DESC;
+    `);
+}}}
+\\
 Испраќаме авторизирано {{{GET}}} барање до backend за да добиеме податоци\\ 
 за сите продукти и нивната состојба.\\
@@ -105 +138 @@
 Следни 2 → {{{Набљудувај}}} \\
 Останати → {{{Стабилно}}} \\
+\\
 [[Image(prediction.png, height=300px)]]
 \\