Changes between Initial Version and Version 1 of AdvancedDatabaseDevelopment2

Timestamp:

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

Author:

201178

Comment:

--

AdvancedDatabaseDevelopment2

@@ +1 @@
+= Складирани процедури и функции =
+
+== Пресметка на вкупен приход за период ==
+
+=== Опис ===
+
+Функцијата го пресметува вкупниот приход за даден период.
+
+=== Имплементација ===
+
+{{{
+CREATE OR REPLACE FUNCTION get_total_revenue(
+    p_start_date DATE,
+    p_end_date DATE
+)
+RETURNS NUMERIC AS $$
+DECLARE
+    total NUMERIC;
+BEGIN
+    SELECT COALESCE(SUM(p.amount), 0) INTO total
+    FROM project.payment p
+    JOIN project.orders o ON o.order_id = p.order_id
+    WHERE o.status = 'ПЛАТЕНА'
+      AND DATE(p.payment_date) BETWEEN p_start_date AND p_end_date;
+    
+    RETURN total;
+END;
+$$ LANGUAGE plpgsql;
+
+-- Употреба:
+-- SELECT get_total_revenue('2026-07-01', '2026-07-31');
+}}}
+
+== Пресметка на дневен буџет за набавка ==
+
+=== Опис ===
+
+Функцијата го пресметува препорачаниот дневен буџет за набавка на производи под минималното ниво.
+
+=== Имплементација ===
+
+{{{
+CREATE OR REPLACE FUNCTION get_daily_procurement_budget()
+RETURNS NUMERIC AS $$
+DECLARE
+    total_cost NUMERIC := 0;
+    rec RECORD;
+BEGIN
+    FOR rec IN
+        SELECT p.product_id, p.name, p.min_stock,
+               COALESCE(SUM(i.quantity_change), 0) AS current_stock,
+               p.min_stock - COALESCE(SUM(i.quantity_change), 0) AS shortage
+        FROM project.product p
+        LEFT JOIN project.inventory i ON i.product_id = p.product_id
+        WHERE p.active = TRUE
+        GROUP BY p.product_id, p.name, p.min_stock
+        HAVING COALESCE(SUM(i.quantity_change), 0) < p.min_stock
+    LOOP
+        total_cost := total_cost + (rec.shortage * 10);
+    END LOOP;
+    
+    RETURN total_cost;
+END;
+$$ LANGUAGE plpgsql;
+
+-- Употреба:
+-- SELECT get_daily_procurement_budget();
+}}}
+
+== Затворање на смена со автоматска пресметка ==
+
+=== Опис ===
+
+Процедурата ја затвора активната смена и ги пресметува вкупниот приход и бројот на нарачки.
+
+=== Имплементација ===
+
+{{{
+CREATE OR REPLACE PROCEDURE close_current_shift(
+    p_closed_by INT
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_shift_id INT;
+    v_start_time TIMESTAMP;
+    v_total NUMERIC;
+    v_order_count INT;
+BEGIN
+    SELECT shift_id, start_time INTO v_shift_id, v_start_time
+    FROM project.shift
+    WHERE end_time IS NULL
+    LIMIT 1;
+    
+    IF v_shift_id IS NULL THEN
+        RAISE EXCEPTION 'Нема активна смена.';
+    END IF;
+    
+    SELECT COUNT(*), COALESCE(SUM(p.amount), 0)
+    INTO v_order_count, v_total
+    FROM project.orders o
+    JOIN project.payment p ON p.order_id = o.order_id
+    WHERE o.status = 'ПЛАТЕНА'
+      AND o.created_at >= v_start_time;
+    
+    INSERT INTO project.shift_close (closed_by, total, order_count)
+    VALUES (p_closed_by, v_total, v_order_count);
+    
+    UPDATE project.shift
+    SET end_time = NOW()
+    WHERE shift_id = v_shift_id;
+END;
+$$;
+
+-- Употреба:
+-- CALL close_current_shift(1);
+}}}
+
+== Препорака за набавка на производи под минимум ==
+
+=== Опис ===
+
+Функцијата враќа листа на производи под минималното ниво со препорачана количина за набавка.
+
+=== Имплементација ===
+
+{{{
+CREATE OR REPLACE FUNCTION get_procurement_recommendations()
+RETURNS TABLE (
+    product_id INT,
+    product_name TEXT,
+    category_name TEXT,
+    current_stock NUMERIC,
+    min_stock INT,
+    recommended_order NUMERIC
+) AS $$
+BEGIN
+    RETURN QUERY
+    SELECT 
+        p.product_id,
+        p.name::TEXT AS product_name,
+        c.name::TEXT AS category_name,
+        COALESCE(SUM(i.quantity_change), 0) AS current_stock,
+        p.min_stock,
+        (p.min_stock * 2 - COALESCE(SUM(i.quantity_change), 0)) AS recommended_order
+    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
+    HAVING COALESCE(SUM(i.quantity_change), 0) < p.min_stock
+    ORDER BY recommended_order DESC;
+END;
+$$ LANGUAGE plpgsql;
+
+-- Употреба:
+-- SELECT * FROM get_procurement_recommendations();
+}}}