Changes between Version 11 and Version 12 of AdvancedReports

Timestamp:

02/12/26 05:11:39 (12 days ago)

Author:

221181

Comment:

--

AdvancedReports

@@ -322 +322 @@
 }}}
 
+== Месечен преглед на набавни нарачки (последни 12 месеци) ==
+
+Цел: Овој query прави агрегатен месечен извештај за набавките во последните 12 месеци.
+Се прикажува вкупен број на нарачки, вкупно нарачана количина, примена количина и количина што сe уште не е примена, по добавувач и магацин.
+
+{{{#!sql
+SELECT
+    TO_CHAR(date_trunc('month', po.order_date), 'YYYY-MM') AS order_month,
+    w.name AS warehouse_name,
+    sup.name AS supplier_name,
+    COUNT(DISTINCT po.po_id) AS total_orders,
+    SUM(poi.quantity) AS total_ordered_quantity,
+    SUM(poi.received_quantity) AS total_received_quantity,
+    SUM(poi.quantity - poi.received_quantity) AS total_pending_quantity
+FROM purchase_order po
+JOIN purchase_order_item poi ON po.po_id = poi.po_id
+LEFT JOIN supplier sup ON po.supplier_id = sup.supplier_id
+JOIN warehouse w ON po.warehouse_id = w.warehouse_id
+WHERE po.order_date >= date_trunc('month', CURRENT_DATE) - INTERVAL '11 months'
+GROUP BY order_month, w.name, sup.name
+ORDER BY order_month DESC;
+}}}
+
+'''Релациона алгебра:'''
+{{{
+γ warehouse_name, supplier_name ; COUNT(po_id)->total_orders, SUM(quantity)->total_ordered_quantity, SUM(received_quantity)->total_received_quantity, SUM(pending)->total_pending_quantity (
+  π po_id, warehouse_name, supplier_name, quantity, received_quantity, quantity - received_quantity -> pending (
+    (
+      (
+        purchase_order
+        ⋈ purchase_order_item
+      )
+      ⟕ ρ name->supplier_name (supplier)
+    )
+    ⋈ ρ name->warehouse_name (warehouse)
+  )
+)
+}}}
+