= Купувач и Нарачки

== Извештај за топ-купувачи

Идентификување на купувачите кои направиле најголем број нарачки или (вкупно) потрошиле најмногу.

{{{#!sql
SELECT 
    b.buyer_id, 
    b.company_name, 
    COUNT(o.order_id) AS total_orders, 
    COALESCE(SUM(i.total_amount), 0) AS total_spent
FROM 
    BUYER b
LEFT JOIN "ORDER" o ON b.buyer_id = o.buyer_id
LEFT JOIN INVOICE i ON o.order_id = i.order_id
GROUP BY 
    b.buyer_id, b.company_name
ORDER BY 
    total_spent DESC, total_orders DESC;
}}}

== Очекувани нарачки од купувач

Наведени се сите неплатени нарачки групирани по купувачи, вклучувајќи ја и вкупната вредност.

{{{#!sql
SELECT 
    b.buyer_id, 
    b.company_name, 
    COUNT(o.order_id) AS pending_orders, 
    COALESCE(SUM(i.total_amount), 0) AS total_pending_value
FROM 
    BUYER b
LEFT JOIN "ORDER" o ON b.buyer_id = o.buyer_id
LEFT JOIN INVOICE i ON o.order_id = i.order_id
WHERE 
    o.status = 'Pending'
GROUP BY 
    b.buyer_id, b.company_name
ORDER BY 
    total_pending_value DESC;
}}}

== Нарачки по земја

Собирање нарачки по земјата на купувач и прикажување на нивниот соодветен придонес на целокупниот приходот.

{{{#!sql
SELECT 
    b.country, 
    COUNT(o.order_id) AS total_orders, 
    COALESCE(SUM(i.total_amount), 0) AS total_revenue
FROM 
    BUYER b
LEFT JOIN "ORDER" o ON b.buyer_id = o.buyer_id
LEFT JOIN INVOICE i ON o.order_id = i.order_id
GROUP BY 
    b.country
ORDER BY 
    total_revenue DESC, total_orders DESC;
}}}

= Производ и Снабдувач

== Најпродавани производи

Прикажување на најкупуваните производи - вкупен приход, продадена количина и поврзани производители.

- **БЕЛЕШКА**: Бидејќи нема колона за количина во ORDER_PRODUCT, се смета "times_ordered" како број на нарачки што го содржи тој производ.

{{{#!sql
SELECT
    p.product_id,
    p.name AS product_name,
    prod.company_name AS producer_name,
    COUNT(op.order_id) AS times_ordered,
    COALESCE(SUM(i.total_amount), 0) AS total_revenue
FROM PRODUCT p
JOIN PRODUCER prod ON p.producer_id = prod.producer_id
LEFT JOIN ORDER_PRODUCT op ON p.product_id = op.product_id
LEFT JOIN "ORDER" o ON op.order_id = o.order_id
LEFT JOIN INVOICE i ON o.order_id = i.order_id
GROUP BY p.product_id, prod.company_name
ORDER BY total_revenue DESC, times_ordered DESC;
}}}

== Приход по произведувач

Прикажување вкупнен приход генериран од секој производител врз основа на нарачките што ги вклучува неговите производи.

- **БЕЛЕШКА**: Се сумираат вкупните суми на уникатните фактура за да се избегне двојно броење на нарачката повеќекратно, ако има повеќе производи од ист производител.

{{{#!sql
SELECT
    pr.producer_id,
    pr.company_name,
    COALESCE(SUM(DISTINCT i.total_amount), 0) AS total_revenue
FROM PRODUCER pr
JOIN PRODUCT p ON pr.producer_id = p.producer_id
JOIN ORDER_PRODUCT op ON p.product_id = op.product_id
JOIN "ORDER" o ON op.order_id = o.order_id
JOIN INVOICE i ON o.order_id = i.order_id
GROUP BY pr.producer_id, pr.company_name
ORDER BY total_revenue DESC;
}}}

== Производи со слаби перформанси

Надевени се производите кои не се нарачани или имаат минимална продажба во последните 6 месеци.
"Минимално" овде се дефинира како помалку од 5 нарачки во тој период. Овој временски период може да се смени.

{{{#!sql
SELECT 
    p.product_id,
    p.name AS product_name,
    pr.company_name AS producer_name,
    COUNT(o.order_id) AS order_count
FROM PRODUCT p
JOIN PRODUCER pr ON p.producer_id = pr.producer_id
LEFT JOIN ORDER_PRODUCT op ON p.product_id = op.product_id
LEFT JOIN "ORDER" o 
       ON op.order_id = o.order_id
      AND o.order_date >= CURRENT_DATE - INTERVAL '6 MONTH'
GROUP BY p.product_id, pr.company_name
HAVING COUNT(o.order_id) < 5
ORDER BY order_count ASC;
}}}

= Детали за нарачка

== Извештај за исполнување нарачка

Прикажување статус на испорака на секоја нарачка, вклучувајќи ги проценетите и датумите на испорака.

- **БЕЛЕШКА**: бидејќи нема колона "actual_delivery_date", се споредуваат "estimated_delivery_date" (Табела {{{ORDERS}}}) и "estimated_arrival" (табела {{{TRANSPORT}}}). Полето "статус" може да покаже дали е испорачано или не.

{{{#!sql
SELECT
    o.order_id,
    o.order_date,
    o.status AS order_status,
    o.estimated_delivery_date,
    t.estimated_departure,
    t.estimated_arrival,
    CASE
        WHEN o.status = 'Delivered' THEN 'Fulfilled'
        WHEN o.status IN ('Shipped', 'In Transit') THEN 'In Transit'
        ELSE 'Pending'
    END AS fulfillment_status
FROM "ORDER" o
LEFT JOIN TRANSPORT t ON o.transport_id = t.transport_id
ORDER BY o.order_date DESC;
}}}

== Анализа на нарачки по временски период

Анализа на времето потребно од поставување на нарачката до проценетата испорака за секоја нарачка.

- **БЕЛЕШКА**: се користи "estimated_delivery_date“ од {{{ORDER}}}. Може да се спореди и "transport.estimated_arrival".

{{{#!sql
SELECT
    o.order_id,
    o.order_date,
    o.estimated_delivery_date,
    (o.estimated_delivery_date - o.order_date) AS days_from_order_to_estimated_delivery,
    t.estimated_departure,
    t.estimated_arrival,
    (t.estimated_arrival - o.order_date) AS days_from_order_to_estimated_arrival
FROM "ORDER" o
LEFT JOIN TRANSPORT t ON o.transport_id = t.transport_id
ORDER BY o.order_id;
}}}

== Детали за транспорт на нарачка

Извештај кој прикажува детали за транспортот (поаѓање, пристигнување и растојание) поврзани со секоја нарачка.

{{{#!sql
SELECT
    o.order_id,
    o.order_date,
    t.carrier_name,
    t.departure_point,
    t.arrival_point,
    t.estimated_departure,
    t.estimated_arrival,
    t.total_distance,
    t.special_requirements,
    t.insurance_conditions,
    t.incoterm
FROM "ORDER" o
JOIN TRANSPORT t ON o.transport_id = t.transport_id
ORDER BY o.order_date DESC;
}}}

= Фактура и Наплата

== Извештај за неплатени фактури

Наведени се сите неплатени фактури со нивната соодветна нарачка и детали за купувачот.
Претпоставува статус "Неплатен" или "Во тек" во табелата {{{INVOICE}}} или статус на плаќање различен од "payed" во табелата {{{PAYMENT}}}.
Ако информациите строго зависат од {{{invoice.status}}}, филтрирањето е "статус <---> платено".

{{{#!sql
SELECT
    i.invoice_key,
    i.invoice_date,
    i.status AS invoice_status,
    p.due_date AS payment_due_date,
    p.payment_status,
    b.buyer_id,
    b.company_name AS buyer_name,
    b.billing_address
FROM INVOICE i
JOIN "ORDER" o ON i.order_id = o.order_id
JOIN BUYER b ON o.buyer_id = b.buyer_id
JOIN PAYMENT p ON o.order_id = p.order_id
WHERE p.payment_status NOT IN ('Paid')
ORDER BY p.due_date DESC;
}}}

== Анализа на задоцнети плаќања

Прикажани се сите нарачки чиј плаќања се извршени по крајниот датумот, вклучително и примерок од пресметката на задоцнетите трошоци.
Може да се промени дневната стапка.

{{{#!sql
SELECT
    p.payment_id,
    p.order_id,
    p.amount,
    p.due_date,
    p.payment_date,
    CASE 
        WHEN p.payment_date > p.due_date THEN (p.payment_date - p.due_date)
        ELSE 0
    END AS days_late,
    CASE 
        WHEN p.payment_date > p.due_date THEN (p.amount * 0.01 * (p.payment_date - p.due_date))
        ELSE 0
    END AS late_fee
FROM PAYMENT p
WHERE p.payment_date > p.due_date;
}}}

== Преглед на статусот на плаќање

Агрегација и прикажување процент на плаќања кои се во различни статуси.

{{{#!sql
SELECT
    payment_status,
    COUNT(*) AS count_payments,
    SUM(amount) AS total_amount,
    ROUND(
        (COUNT(*) * 100.0 / SUM(COUNT(*)) OVER ()), 2
    ) AS percentage_of_payments
FROM PAYMENT
GROUP BY payment_status
ORDER BY count_payments DESC;
}}}

= Финансиска Анализа

== Трендови на приходи

Месечни или квартални движења на приходите генерирани од нарачки.
Ова се заснова на табелата {{{INVOICE}}} ({{{invoice_date}}}, {{{total_amount}}}).
За месечни трендови, користиме {{{DATE_TRUNC}}} ({{{month}}}, i.invoice_date)

- **БЕЛЕШКА**: За квартални трендови, се заменува {{{month}}} со {{{quarter}}}.

{{{#!sql
SELECT
    DATE_TRUNC('month', i.invoice_date) AS month,
    SUM(i.total_amount) AS monthly_revenue
FROM INVOICE i
GROUP BY DATE_TRUNC('month', i.invoice_date)
ORDER BY month;
}}}

== Анализа на валути

Распределба на плаќањата примени во различни валути, претворајќи ги во единствена референтна валута (на пр. {{{USD}}}) со користење на девизен курс.

{{{#!sql
SELECT
    p.currency,
    COUNT(*) AS total_payments,
    SUM(p.amount) AS total_amount_in_currency,
    SUM(p.amount * p.exchange_rate) AS total_amount_converted -- e.g., to USD
FROM PAYMENT p
GROUP BY p.currency;
}}}

== Даночни придонеси

Апроксимација на даночните придонеси по купувач или производител.
Бидејќи нема експлицитна даночна стапка во базата на податоци, претпоставена е хипотетичка стапка (на пр., 20%).
Овој пример ја пресметува вкупната вредност на фактурата по купувач и се множи со 20%.

{{{#!sql
SELECT
    b.buyer_id,
    b.company_name,
    b.country,
    COALESCE(SUM(i.total_amount), 0) AS total_sales,
    COALESCE(SUM(i.total_amount) * 0.20, 0) AS estimated_tax_contribution
FROM BUYER b
JOIN "ORDER" o ON b.buyer_id = o.buyer_id
JOIN INVOICE i ON o.order_id = i.order_id
GROUP BY b.buyer_id, b.company_name, b.country
ORDER BY total_sales DESC;
}}}

= Транспорт и логистика

== Перформанси на превозникот

Анализа на перформансите на секој превозник врз основа на бројот на пратки, просечното растојание и просечното време на транзит.

{{{#!sql
SELECT
    t.carrier_name,
    COUNT(o.order_id) AS total_shipments,
    COALESCE(AVG(t.total_distance), 0) AS avg_distance,
    COALESCE(AVG(t.estimated_arrival - t.estimated_departure), 0) AS avg_transit_days
FROM TRANSPORT t
LEFT JOIN "ORDER" o ON t.transport_id = o.transport_id
GROUP BY t.carrier_name
ORDER BY total_shipments DESC;
}}}

== Анализа на incoterm

Се прикажива распределбата на нарачките по incoterms, заедно со вкупните износи на фактурата.

{{{#!sql
SELECT
    t.incoterm,
    COUNT(o.order_id) AS total_orders,
    COALESCE(SUM(i.total_amount), 0) AS total_invoice_amount
FROM TRANSPORT t
LEFT JOIN "ORDER" o ON t.transport_id = o.transport_id
LEFT JOIN INVOICE i ON o.order_id = i.order_id
GROUP BY t.incoterm
ORDER BY total_orders DESC;
}}}

== Ефикасност на рутата

Оценување на просечното растојание и време на транзит по рута ({{{departure_point}}}, {{{arrival_point}}}).

{{{#!sql
SELECT
    t.departure_point,
    t.arrival_point,
    COUNT(o.order_id) AS total_orders,
    COALESCE(AVG(t.total_distance), 0) AS avg_distance,
    COALESCE(AVG(t.estimated_arrival - t.estimated_departure), 0) AS avg_transit_time
FROM TRANSPORT t
LEFT JOIN "ORDER" o ON t.transport_id = o.transport_id
GROUP BY t.departure_point, t.arrival_point
ORDER BY total_orders DESC;
}}}

= Усогласенот и ревизија

== Усогласеност со извозот на производителот

Идентификација на производителите со нецелосни или невалидни детали за лиценцата за извоз.
За демонстрација, наведени се сите производители кои имаат NULL или празен {{{export_license_number}}}.

{{{#!sql
SELECT
    producer_id,
    company_name,
    export_license_number
FROM PRODUCER
WHERE export_license_number IS NULL
   OR export_license_number = '';
}}}

== Трага за ревизија на фактура

Целосен преглед на фактури, вклучувајќи ги поврзаните нарачки, плаќања и тековниот статус.

{{{#!sql
SELECT
    i.invoice_key,
    i.invoice_date,
    i.status AS invoice_status,
    i.total_amount,
    o.order_id,
    o.status AS order_status,
    p.payment_id,
    p.payment_status,
    p.payment_method
FROM INVOICE i
JOIN "ORDER" o ON i.order_id = o.order_id
LEFT JOIN PAYMENT p ON o.order_id = p.order_id
ORDER BY i.invoice_date DESC;
}}}

== Несовпаѓање на плаќањa

Идентификација на нарачки каде збирот на плаќања не се совпаѓа со вкупниот износ на фактурата.

{{{#!sql
SELECT
    i.invoice_key,
    i.order_id,
    i.total_amount AS invoice_total,
    COALESCE(SUM(p.amount), 0) AS total_payment,
    (i.total_amount - COALESCE(SUM(p.amount), 0)) AS difference
FROM INVOICE i
LEFT JOIN PAYMENT p ON i.order_id = p.order_id
GROUP BY i.invoice_key, i.order_id, i.total_amount
HAVING i.total_amount <> COALESCE(SUM(p.amount), 0)
ORDER BY difference DESC;
}}}