Changes between Initial Version and Version 1 of AdvancedDatabaseDevelopment3

Timestamp:

09/24/26 02:13:28 (7 days ago)

Author:

201178

Comment:

--

AdvancedDatabaseDevelopment3

@@ +1 @@
+= Погледи =
+
+== Целосен преглед на нарачки ==
+
+=== Опис ===
+
+Погледот прикажува целосен преглед на сите нарачки со детали за масата, келнерот, плаќањето и вкупниот износ.
+
+=== Имплементација ===
+
+{{{
+CREATE OR REPLACE VIEW v_orders_full AS
+SELECT 
+    o.order_id,
+    o.created_at,
+    o.status AS order_status,
+    rt.table_number,
+    rt.capacity,
+    u.first_name || ' ' || u.last_name AS waiter_name,
+    r.role_name AS waiter_role,
+    p.amount AS total_paid,
+    p.method AS payment_method,
+    p.payment_date,
+    i.invoice_number,
+    i.issued_at,
+    COUNT(oi.item_number) AS total_items,
+    SUM(oi.quantity) AS total_quantity
+FROM project.orders o
+JOIN project.restaurant_table rt ON rt.table_id = o.table_id
+JOIN project.app_user u ON u.user_id = o.user_id
+JOIN project.role r ON r.role_id = u.role_id
+LEFT JOIN project.payment p ON p.order_id = o.order_id
+LEFT JOIN project.invoice i ON i.payment_id = p.payment_id
+LEFT JOIN project.order_item oi ON oi.order_id = o.order_id
+GROUP BY o.order_id, o.created_at, o.status, rt.table_number, rt.capacity,
+         u.first_name, u.last_name, r.role_name,
+         p.amount, p.method, p.payment_date,
+         i.invoice_number, i.issued_at
+ORDER BY o.created_at DESC;
+}}}
+
+== Преглед на маси со нивниот статус ==
+
+=== Опис ===
+
+Погледот прикажува сите маси со нивниот статус и активна нарачка (ако има).
+
+=== Имплементација ===
+
+{{{
+CREATE OR REPLACE VIEW v_tables_status AS
+SELECT 
+    rt.table_id,
+    rt.table_number,
+    rt.capacity,
+    rt.status,
+    o.order_id AS active_order_id,
+    o.created_at AS order_time,
+    u.first_name || ' ' || u.last_name AS waiter_name,
+    COUNT(oi.item_number) AS items_in_order
+FROM project.restaurant_table rt
+LEFT JOIN project.orders o ON o.table_id = rt.table_id AND o.status != 'ПЛАТЕНА'
+LEFT JOIN project.app_user u ON u.user_id = o.user_id
+LEFT JOIN project.order_item oi ON oi.order_id = o.order_id
+GROUP BY rt.table_id, rt.table_number, rt.capacity, rt.status,
+         o.order_id, o.created_at, u.first_name, u.last_name
+ORDER BY rt.table_number::int;
+}}}
+
+== Месечен приход по категорија ==
+
+=== Опис ===
+
+Погледот го прикажува месечниот приход групиран по категорија.
+
+=== Имплементација ===
+
+{{{
+CREATE OR REPLACE VIEW v_monthly_revenue_by_category AS
+SELECT 
+    TO_CHAR(o.created_at, 'YYYY-MM') AS month,
+    c.category_id,
+    c.name AS category_name,
+    COUNT(DISTINCT o.order_id) AS order_count,
+    SUM(oi.quantity) AS items_sold,
+    SUM(oi.quantity * oi.unit_price) AS total_revenue,
+    ROUND(AVG(oi.quantity * oi.unit_price), 2) AS avg_item_price
+FROM project.orders o
+JOIN project.order_item oi ON oi.order_id = o.order_id
+JOIN project.product p ON p.product_id = oi.product_id
+JOIN project.category c ON c.category_id = p.category_id
+WHERE o.status = 'ПЛАТЕНА'
+GROUP BY TO_CHAR(o.created_at, 'YYYY-MM'), c.category_id, c.name
+ORDER BY month DESC, total_revenue DESC;
+}}}
+
+== Извештај за најпродавани производи ==
+
+=== Опис ===
+
+Погледот ги прикажува најпродаваните производи со вкупна количина и приход.
+
+=== Имплементација ===
+
+{{{
+CREATE OR REPLACE VIEW v_top_products AS
+SELECT 
+    p.product_id,
+    p.name AS product_name,
+    c.name AS category_name,
+    p.price AS current_price,
+    COUNT(DISTINCT oi.order_id) AS order_count,
+    SUM(oi.quantity) AS total_quantity,
+    SUM(oi.quantity * oi.unit_price) AS total_revenue,
+    ROUND(AVG(oi.unit_price), 2) AS avg_selling_price
+FROM project.product p
+JOIN project.category c ON c.category_id = p.category_id
+LEFT JOIN project.order_item oi ON oi.product_id = p.product_id
+LEFT JOIN project.orders o ON o.order_id = oi.order_id AND o.status = 'ПЛАТЕНА'
+WHERE p.active = TRUE
+GROUP BY p.product_id, p.name, c.name, p.price
+ORDER BY total_revenue DESC NULLS LAST;
+}}}
+
+== Дневен извештај за продажба ==
+
+=== Опис ===
+
+Погледот прикажува дневен извештај за продажбата со вкупен приход, број на нарачки и просечна вредност.
+
+=== Имплементација ===
+
+{{{
+CREATE OR REPLACE VIEW v_daily_sales AS
+SELECT 
+    DATE(o.created_at) AS sale_date,
+    TO_CHAR(o.created_at, 'Day') AS day_of_week,
+    COUNT(DISTINCT o.order_id) AS order_count,
+    COUNT(DISTINCT o.table_id) AS tables_used,
+    COUNT(DISTINCT o.user_id) AS waiters_active,
+    SUM(p.amount) AS total_revenue,
+    ROUND(AVG(p.amount), 2) AS avg_order_value,
+    SUM(CASE WHEN p.method = 'КЕШ' THEN p.amount ELSE 0 END) AS cash_revenue,
+    SUM(CASE WHEN p.method = 'КАРТИЧКА' THEN p.amount ELSE 0 END) AS card_revenue
+FROM project.orders o
+JOIN project.payment p ON p.order_id = o.order_id
+WHERE o.status = 'ПЛАТЕНА'
+GROUP BY DATE(o.created_at), TO_CHAR(o.created_at, 'Day')
+ORDER BY sale_date DESC;
+}}}
+
+== Преглед на залиха по производ ==
+
+=== Опис ===
+
+Погледот прикажува моментална залиха за секој производ со статус (ОК, НИСКО, КРИТИЧНО).
+
+=== Имплементација ===
+
+{{{
+CREATE OR REPLACE VIEW v_product_stock AS
+SELECT 
+    p.product_id,
+    p.name AS product_name,
+    c.name AS category_name,
+    p.min_stock,
+    COALESCE(SUM(i.quantity_change), 0) AS current_stock,
+    CASE 
+        WHEN COALESCE(SUM(i.quantity_change), 0) <= 0 THEN 'КРИТИЧНО'
+        WHEN COALESCE(SUM(i.quantity_change), 0) < p.min_stock THEN 'НИСКО'
+        ELSE 'ОК'
+    END AS stock_status,
+    MAX(i.created_at) AS last_movement
+FROM project.product p
+JOIN project.category c ON c.category_id = p.category_id
+LEFT JOIN project.inventory i ON i.product_id = p.product_id
+WHERE p.active = TRUE
+GROUP BY p.product_id, p.name, c.name, p.min_stock
+ORDER BY stock_status, current_stock ASC;
+}}}