Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

01/15/26 07:16:35 (12 days ago)

Author:

221181

Comment:

--

AdvancedReports

@@ +1 @@
+= Напредни извештаи од базата (SQL, складирани процедури и релациона алгебра) =
+----
+
+== 1. ABC Анализа на производи (ABC Product Analysis) ==
+
+Овој извештај ги дели производите во сегменти A (80%), B (15%) и C (5%) според нивниот придонес во вкупниот промет, користејќи ги овие проценти за да се прикаже кои производи се најважни за приходот и да се олесни управувањето со залихите.
+
+{{{#!sql
+WITH ProductRevenue AS (
+    SELECT 
+        p.name AS product_name, 
+        SUM(si.quantity * si.unit_price_at_sale) AS total_revenue
+    FROM product p
+    JOIN sale_item si ON p.product_id = si.product_id
+    GROUP BY p.name
+),
+RevenueCalculations AS (
+    SELECT 
+        product_name, 
+        total_revenue,
+        SUM(total_revenue) OVER (ORDER BY total_revenue DESC) / SUM(total_revenue) OVER () AS cumulative_share
+    FROM ProductRevenue
+)
+SELECT 
+    product_name, 
+    ROUND(total_revenue, 2) AS revenue_mkd,
+    CASE 
+        WHEN cumulative_share <= 0.80 THEN 'A (High Value)'
+        WHEN cumulative_share <= 0.95 THEN 'B (Medium Value)'
+        ELSE 'C (Low Value)' 
+    END AS abc_classification
+FROM RevenueCalculations;
+}}}
+
+----
+
+== 2. Месечен тренд на продажба (Monthly Sales Growth Trend) ==
+
+Ги споредува приходите од тековниот месец со претходниот за да го пресмета процентот на раст или пад на бизнисот.
+
+{{{#!sql
+WITH MonthlySales AS (
+    SELECT 
+        DATE_TRUNC('month', date_time) AS sales_month, 
+        SUM(total_amount) AS revenue
+    FROM sale 
+    GROUP BY 1
+)
+SELECT 
+    sales_month, 
+    revenue AS current_month_revenue,
+    LAG(revenue) OVER (ORDER BY sales_month) AS previous_month_revenue,
+    ROUND(((revenue - LAG(revenue) OVER (ORDER BY sales_month)) / 
+           NULLIF(LAG(revenue) OVER (ORDER BY sales_month), 0)) * 100, 2) AS growth_percentage
+FROM MonthlySales;
+}}}
+
+----
+
+== 3. Состојба на залихи и критични точки (Inventory Health & Reorder Levels) ==
+
+Ги идентификува производите што се под минимумот во магацините и дава аларм за итна набавка.
+
+{{{#!sql
+SELECT 
+    p.name AS product_name, 
+    w.name AS warehouse_location, 
+    ws.quantity_on_hand, 
+    p.reorder_level,
+    CASE 
+        WHEN ws.quantity_on_hand <= p.reorder_level THEN 'CRITICAL: REORDER'
+        WHEN ws.quantity_on_hand <= p.reorder_level * 1.5 THEN 'WARNING: LOW'
+        ELSE 'HEALTHY' 
+    END AS stock_status
+FROM product p
+JOIN warehouse_stock ws ON p.product_id = ws.product_id
+JOIN warehouse w ON ws.warehouse_id = w.warehouse_id;
+}}}
+
+----
+
+== 4. Перформанси на добавувачите (Supplier Performance Matrix) ==
+
+Го мери доцнењето на испораките од добавувачите и ја пресметува нивната доверливост во исполнување на роковите.
+
+{{{#!sql
+SELECT 
+    s.name AS supplier_name,
+    COUNT(po.po_id) AS total_orders,
+    AVG(po.actual_delivery_date - po.expected_delivery_date) AS average_delay_days,
+    ROUND(COUNT(CASE WHEN po.actual_delivery_date <= po.expected_delivery_date THEN 1 END) * 100.0 / COUNT(*), 2) AS on_time_delivery_rate
+FROM supplier s
+JOIN purchase_order po ON s.supplier_id = po.supplier_id
+WHERE po.status = 'DELIVERED'
+GROUP BY s.name;
+}}}
+
+----
+
+== 5. RFM Анализа на клиенти (Customer RFM Segmentation) ==
+
+Ги сегментира купувачите според тоа кога последен пат купувале, колку често се враќаат и колку пари потрошиле.
+
+{{{#!sql
+SELECT 
+    c.name AS customer_name,
+    EXTRACT(DAY FROM CURRENT_TIMESTAMP - MAX(s.date_time)) AS recency_days,
+    COUNT(s.sale_id) AS frequency_count,
+    SUM(s.total_amount) AS monetary_total,
+    NTILE(5) OVER (ORDER BY MAX(s.date_time)) AS r_score,
+    NTILE(5) OVER (ORDER BY COUNT(s.sale_id)) AS f_score,
+    NTILE(5) OVER (ORDER BY SUM(s.total_amount)) AS m_score
+FROM customer c
+JOIN sale s USING (customer_id)
+GROUP BY c.customer_id, c.name;
+}}}
+
+----
+
+== 6. Искористеност на капацитетот на магацините (Warehouse Utilization Rate) ==
+
+Пресметува колкав процент од физичкиот простор во секој магацин е моментално пополнет.
+
+{{{#!sql
+SELECT 
+    w.name AS warehouse_name, 
+    w.capacity AS total_unit_capacity,
+    SUM(ws.quantity_on_hand) AS units_in_stock,
+    ROUND((SUM(ws.quantity_on_hand)::NUMERIC / w.capacity) * 100, 2) AS occupancy_percentage
+FROM warehouse w
+LEFT JOIN warehouse_stock ws ON w.warehouse_id = ws.warehouse_id
+GROUP BY w.warehouse_id, w.name, w.capacity;
+}}}
+
+----
+
+== 7. Анализа на застојни производи ==
+
+Ги прикажува производите што не се продале во последните 90 дена и само зафаќаат простор и капитал.
+
+{{{#!sql
+SELECT 
+    p.name AS product_name, 
+    p.sku, 
+    ws.quantity_on_hand, 
+    COALESCE(MAX(s.date_time)::TEXT, 'NO SALES RECORDED') AS last_sold_date
+FROM product p
+JOIN warehouse_stock ws ON p.product_id = ws.product_id
+LEFT JOIN sale_item si ON p.product_id = si.product_id
+LEFT JOIN sale s ON si.sale_id = s.sale_id
+GROUP BY p.product_id, p.name, p.sku, ws.quantity_on_hand
+HAVING MAX(s.date_time) < CURRENT_DATE - INTERVAL '90 days' OR MAX(s.date_time) IS NULL;
+}}}
+
+----
+
+== 8. Маржа на профит по категорија (Category Profit Margin) ==
+
+Ја пресметува разликата помеѓу продажната и набавната цена за секоја категорија производи.
+
+{{{#!sql
+WITH ProductCosts AS (
+    SELECT product_id, AVG(unit_cost) AS average_cost FROM purchase_order_item GROUP BY 1
+)
+SELECT 
+    c.name AS category_name,
+    SUM(si.quantity * si.unit_price_at_sale) AS gross_revenue,
+    SUM(si.quantity * pc.average_cost) AS total_cost_of_goods,
+    ROUND(((SUM(si.quantity * si.unit_price_at_sale) - SUM(si.quantity * pc.average_cost)) / 
+           NULLIF(SUM(si.quantity * si.unit_price_at_sale), 0)) * 100, 2) AS profit_margin_pct
+FROM category c
+JOIN product p USING (category_id)
+JOIN sale_item si USING (product_id)
+JOIN ProductCosts pc USING (product_id)
+GROUP BY c.name;
+}}}
+
+----
+
+== 9. Анализа на „пазарна кошничка" (Product Affinity / Market Basket) ==
+
+Открива кои производи купувачите најчесто ги купуваат заедно во иста трансакција.
+
+{{{#!sql
+SELECT 
+    p1.name AS product_a, 
+    p2.name AS product_b, 
+    COUNT(*) AS pair_occurrence_count
+FROM sale_item si1
+JOIN sale_item si2 ON si1.sale_id = si2.sale_id AND si1.product_id < си2.product_id
+JOIN product p1 ON si1.product_id = p1.product_id
+JOIN product p2 ON si2.product_id = p2.product_id
+GROUP BY 1, 2
+ORDER BY pair_occurrence_count DESC 
+LIMIT 10;
+}}}
+
+----
+
+== 10. Финансиска вредност на залихите (Stock Asset Valuation) ==
+
+Дава точен преглед на вкупната парична вредност на производите во магацините врз основа на последните набавни цени.
+
+{{{#!sql
+SELECT 
+    w.name AS warehouse_location,
+    SUM(ws.quantity_on_hand * (
+        SELECT unit_cost FROM purchase_order_item poi 
+        WHERE poi.product_id = ws.product_id 
+        ORDER BY po_id DESC LIMIT 1
+    )) AS total_inventory_valuation_mkd
+FROM warehouse w
+JOIN warehouse_stock ws USING (warehouse_id)
+GROUP BY w.name;
+}}}
+
+----