Напредни извештаи од базата

1. Тековен залиха по складиште

Цел: Прикажува вкупниот број производи и вредност на залихата по складишта.

SELECT
    w.warehouse_id,
    w.name AS warehouse_name,
    SUM(ws.quantity_on_hand) AS total_units,
    SUM(ws.quantity_on_hand * p.unit_price) AS total_stock_value
FROM warehouse_stock ws
JOIN warehouse w ON ws.warehouse_id = w.warehouse_id
JOIN product p ON ws.product_id = p.product_id
GROUP BY w.warehouse_id, w.name
ORDER BY total_stock_value DESC;

2. Приход од продажба по производ

Цел: Прикажува кој производ носи најмногу приход.

SELECT
    p.product_id,
    p.name AS product_name,
    SUM(si.quantity) AS total_units_sold,
    SUM(si.quantity * si.unit_price_at_sale) AS total_revenue
FROM sale_item si
JOIN product p ON si.product_id = p.product_id
GROUP BY p.product_id, p.name
ORDER BY total_revenue DESC;

3. Статус на нарачки за набавка

Цел: Прикажува колку е нарачано и колку примено од секој производ.

SELECT
    po.po_id,
    po.status,
    p.name AS product_name,
    poi.quantity AS ordered_quantity,
    poi.received_quantity,
    (poi.quantity - poi.received_quantity) AS pending_quantity
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
ORDER BY po.po_id;

4. Продажба по категорија

Цел: Прикажува кои категории носат најголем приход.

SELECT
    c.category_id,
    c.name AS category_name,
    SUM(si.quantity * si.unit_price_at_sale) AS total_category_revenue
FROM sale_item si
JOIN product p ON si.product_id = p.product_id
JOIN category c ON p.category_id = c.category_id
GROUP BY c.category_id, c.name
ORDER BY total_category_revenue DESC;

5. Искористеност на капацитетот на магацините

Цел: Пресметува колкав процент од физичкиот простор во секој магацин е моментално пополнет.

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;

6. Анализа на застојни производи

Цел: Ги прикажува производите што не се продале во последните 90 дена и само зафаќаат простор.

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;