Changes between Version 1 and Version 2 of AdvancedReports

Timestamp:

01/28/25 20:49:38 (9 months ago)

Author:

211561

Comment:

--

AdvancedReports

@@ -1 @@
-temporary text
+= Купувач и Нарачки
+
+== Извештај за топ-купувачи
+
+Идентификување на купувачите кои направиле најголем број нарачки или (вкупно) потрошиле најмногу.
+
+{{{#!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;
+}}}
+
+== Несовпаѓање на плаќање наспроти фактура
+
+Идентификација на нарачки каде збирот на плаќања не се совпаѓа со вкупниот износ на фактурата.
+
+{{{#!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;
+}}}