Changes between Version 10 and Version 11 of AdvancedReports

Timestamp:

02/12/26 04:58:28 (12 days ago)

Author:

221181

Comment:

--

AdvancedReports

@@ -177 +177 @@
 )
 }}}
+
+== 7. Анализа на тренд на продажба и доволност на залиха ==
+
+Цел: Да се анализира продажбата во последните 60 дена и врз основа на просечната дневна продажба да се пресмета дали моменталната залиха е доволна за наредните 30 дена.
+
+{{{#!sql
+WITH recent_sales AS (
+    SELECT
+        si.product_id,
+        SUM(si.quantity) AS sold_last_60_days
+    FROM sale_item si
+    JOIN sale s ON si.sale_id = s.sale_id
+    WHERE s.date_time >= CURRENT_DATE - INTERVAL '60 days'
+    GROUP BY si.product_id
+),
+current_stock AS (
+    SELECT
+        product_id,
+        SUM(quantity_on_hand) AS total_stock
+    FROM warehouse_stock
+    GROUP BY product_id
+)
+SELECT
+    p.product_id,
+    p.name AS product_name,
+    COALESCE(rs.sold_last_60_days, 0) AS sold_last_60_days,
+    ROUND(COALESCE(rs.sold_last_60_days,0)::numeric / 60, 2) AS avg_daily_sales,
+    ROUND((COALESCE(rs.sold_last_60_days,0)::numeric / 60) * 30, 2) AS projected_next_30_days,
+    COALESCE(cs.total_stock,0) AS current_total_stock,
+    CASE
+        WHEN COALESCE(cs.total_stock,0) >= 
+             ((COALESCE(rs.sold_last_60_days,0)::numeric / 60) * 30)
+        THEN 'SUFFICIENT'
+        ELSE 'INSUFFICIENT'
+    END AS stock_status
+FROM product p
+LEFT JOIN recent_sales rs ON p.product_id = rs.product_id
+LEFT JOIN current_stock cs ON p.product_id = cs.product_id
+ORDER BY stock_status, projected_next_30_days DESC;
+
+}}}
+
+'''Релациона алгебра:'''
+{{{
+τ stock_status, projected_next_30_days (
+  π product_id, product_name, sold_last_60_days, avg_daily_sales, projected_next_30_days, current_total_stock, 
+    (current_total_stock >= projected_next_30_days ? 'SUFFICIENT' : 'INSUFFICIENT') -> stock_status (
+    π product_id, product_name, sold_last_60_days, sold_last_60_days / 60 -> avg_daily_sales, 
+      (sold_last_60_days / 60) * 30 -> projected_next_30_days, current_total_stock (
+      product ⟕ (
+        γ product_id ; SUM(quantity)->sold_last_60_days (
+          σ date_time >= CURRENT_DATE - 60 (sale_item ⋈ sale)
+        )
+      ) ⟕ (
+        γ product_id ; SUM(quantity_on_hand)->current_total_stock (warehouse_stock)
+      )
+    )
+  )
+)
+}}}
+
+== 8. Годишен извештај за продажба (последни 12 месеци) ==
+
+Цел: Прикажува детална анализа на продажбата за последните 12 месеци, групирана по месец, складиште, категорија и добавувач.
+
+{{{#!sql
+SELECT 
+    TO_CHAR(date_trunc('month', s.date_time), 'YYYY-MM') AS sales_month,
+    w.name AS warehouse_name,
+    c.name AS category_name,
+    sup.name AS supplier_name,
+    COUNT(DISTINCT s.sale_id) AS total_order_count,
+    SUM(si.quantity) AS total_units_sold,
+    SUM(si.quantity * si.unit_price_at_sale) AS total_gross_revenue
+FROM sale s
+JOIN sale_item si ON s.sale_id = si.sale_id
+JOIN product p ON si.product_id = p.product_id
+LEFT JOIN category c ON p.category_id = c.category_id
+LEFT JOIN supplier sup ON p.supplier_id = sup.supplier_id
+JOIN warehouse w ON s.warehouse_id = w.warehouse_id
+WHERE s.date_time >= date_trunc('month', CURRENT_DATE) - INTERVAL '11 months'
+GROUP BY sales_month, w.name, c.name, sup.name
+ORDER BY sales_month DESC, total_gross_revenue DESC;
+}}}
+
+'''Релациона алгебра:'''
+{{{
+γ warehouse_name, category_name, supplier_name ; COUNT(sale_id)->total_order_count, SUM(quantity)->total_units_sold, SUM(revenue)->total_gross_revenue (
+  π sale_id, warehouse_name, category_name, supplier_name, quantity, quantity * unit_price_at_sale -> revenue (
+    (
+      (
+        sale
+        ⋈ sale_item
+        ⋈ ρ name->product_name, description->product_desc (product)
+      )
+      ⟕ ρ name->category_name, description->category_desc (category)
+    )
+    ⟕ ρ name->supplier_name (supplier)
+    ⋈ ρ name->warehouse_name (warehouse)
+  )
+)
+}}}
+
+== 9. Детален извештај за набавни нарачки ==
+
+Цел: Да се прикаже детална состојба на сите набавни нарачки: статус на нарачката, добавувач, магацин, нарачана количина, примена количина и преостаната количина.
+
+{{{#!sql
+SELECT 
+    po.po_id,
+    po.status AS order_status,
+    po.order_date,
+    po.expected_delivery_date,
+    po.actual_delivery_date,
+    w.name AS warehouse_name,
+    sup.name AS supplier_name,
+    p.name AS product_name,
+    poi.quantity AS ordered_qty,
+    poi.received_quantity AS received_qty,
+    (poi.quantity - poi.received_quantity) AS remaining_to_receive
+FROM purchase_order po
+JOIN purchase_order_item poi ON po.po_id = poi.po_id
+JOIN product p ON poi.product_id = p.product_id
+LEFT JOIN supplier sup ON po.supplier_id = sup.supplier_id
+JOIN warehouse w ON po.warehouse_id = w.warehouse_id
+ORDER BY po.status, po.expected_delivery_date;
+}}}
+
+'''Релациона алгебра:'''
+{{{
+τ status (
+  π po_id, status, order_date, expected_delivery_date, warehouse_name, supplier_name, product_name, quantity->ordered_qty, received_quantity->received_qty, quantity - received_quantity -> remaining_to_receive (
+    (
+      (
+        purchase_order
+        ⋈ purchase_order_item
+        ⋈ ρ name->product_name, description->product_desc, supplier_id->product_supplier_id (product)
+      )
+      ⟕ ρ name->supplier_name (supplier)
+    )
+    ⋈ ρ name->warehouse_name (warehouse)
+  )
+)
+}}}
+