Changes between Initial Version and Version 1 of DatabaseProgramming

Timestamp:

05/29/26 21:53:28 (3 weeks ago)

Author:

231003

Comment:

--

DatabaseProgramming

@@ +1 @@
+= Програмирање на базата на податоци
+
+== Функции
+
+=== `get_customer_ticket_summary`
+
+Оваа функција враќа детален преглед за билетите на одреден корисник, прикажувајќи колку вкупно билети има купено, колку од нив се single или pass билети, како и вкупната сума што ја има потрошено преку успешно реализирани плаќања.
+
+{{{
+
+CREATE OR REPLACE FUNCTION get_customer_ticket_summary(p_customer_id BIGINT)
+RETURNS TABLE (
+    customer_id BIGINT,
+    total_tickets BIGINT,
+    single_tickets BIGINT,
+    pass_tickets BIGINT,
+    total_spent NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    IF NOT EXISTS (
+        SELECT 1
+        FROM Customer c
+        WHERE c.user_id = p_customer_id
+    ) THEN
+        RAISE EXCEPTION 'Customer with id % does not exist.', p_customer_id;
+    END IF;
+
+    RETURN QUERY
+    SELECT
+        c.user_id AS customer_id,
+        COUNT(DISTINCT t.ticket_id) AS total_tickets,
+        COUNT(DISTINCT st.ticket_id) AS single_tickets,
+        COUNT(DISTINCT pt.ticket_id) AS pass_tickets,
+        COALESCE(
+            SUM(
+                CASE
+                    WHEN p.status = 'Completed'::payment_status
+                    THEN COALESCE(st.amount, pass.amount, 0)
+                    ELSE 0
+                END
+            ),
+            0
+        )::NUMERIC AS total_spent
+    FROM Customer c
+    LEFT JOIN Ticket t
+        ON c.user_id = t.user_id
+    LEFT JOIN Single_ticket st
+        ON t.ticket_id = st.ticket_id
+    LEFT JOIN Pass_ticket pt
+        ON t.ticket_id = pt.ticket_id
+    LEFT JOIN Pass_type pass
+        ON pt.type_id = pass.id
+    LEFT JOIN Customer_Payment_Ticket cpt
+        ON t.ticket_id = cpt.ticket_id
+       AND c.user_id = cpt.user_id
+    LEFT JOIN Payment p
+        ON cpt.payment_id = p.payment_id
+    WHERE c.user_id = p_customer_id
+    GROUP BY c.user_id;
+END;
+$$;
+
+}}}
+
+=== `is_ticket_valid`
+
+Оваа функција служи за проверка дали даден билет е валиден за користење односно дали е платен и активен според неговиот тип (single или pass) и условите за важност.
+
+{{{
+
+CREATE OR REPLACE FUNCTION is_ticket_valid(
+    p_ticket_id BIGINT,
+    p_assignment_id BIGINT
+)
+RETURNS BOOLEAN
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_is_valid BOOLEAN;
+BEGIN
+    IF NOT EXISTS (
+        SELECT 1
+        FROM Ticket t
+        WHERE t.ticket_id = p_ticket_id
+    ) THEN
+        RAISE EXCEPTION 'Ticket with id % does not exist.', p_ticket_id;
+    END IF;
+
+    SELECT
+        EXISTS (
+            SELECT 1
+            FROM Single_ticket st
+            JOIN Customer_Payment_Ticket cpt
+                ON st.ticket_id = cpt.ticket_id
+            JOIN Payment p
+                ON cpt.payment_id = p.payment_id
+            WHERE st.ticket_id = p_ticket_id
+              AND st.assignment_id = p_assignment_id
+              AND p.status = 'Completed'::payment_status
+        )
+        OR
+        EXISTS (
+            SELECT 1
+            FROM Pass_ticket pt
+            JOIN Pass_type pass
+                ON pt.type_id = pass.id
+            JOIN Customer_Payment_Ticket cpt
+                ON pt.ticket_id = cpt.ticket_id
+            JOIN Payment p
+                ON cpt.payment_id = p.payment_id
+            WHERE pt.ticket_id = p_ticket_id
+              AND p.status = 'Completed'::payment_status
+              AND CURRENT_DATE BETWEEN pass.valid_from AND pass.valid_until
+        )
+    INTO v_is_valid;
+
+    RETURN v_is_valid;
+END;
+$$;
+
+}}}
+
+=== `get_customer_monthly_spending`
+
+Оваа функција служи за пресметување на вкупната сума што одреден клиент ја има потрошено во даден месец за сите успешно завршени плаќања за билети (single или pass).
+
+{{{
+
+CREATE OR REPLACE FUNCTION get_customer_monthly_spending(
+    p_customer_id BIGINT,
+    p_month DATE
+)
+RETURNS NUMERIC
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_month_start DATE;
+    v_month_end DATE;
+    v_total NUMERIC;
+BEGIN
+    IF NOT EXISTS (
+        SELECT 1
+        FROM Customer c
+        WHERE c.user_id = p_customer_id
+    ) THEN
+        RAISE EXCEPTION 'Customer with id % does not exist.', p_customer_id;
+    END IF;
+
+    v_month_start := date_trunc('month', p_month)::date;
+    v_month_end := (v_month_start + INTERVAL '1 month')::date;
+
+    SELECT
+        COALESCE(SUM(COALESCE(st.amount, pass.amount, 0)), 0)
+    INTO v_total
+    FROM Customer_Payment_Ticket cpt
+    JOIN Payment p
+        ON cpt.payment_id = p.payment_id
+    JOIN Ticket t
+        ON cpt.ticket_id = t.ticket_id
+    LEFT JOIN Single_ticket st
+        ON t.ticket_id = st.ticket_id
+    LEFT JOIN Pass_ticket pt
+        ON t.ticket_id = pt.ticket_id
+    LEFT JOIN Pass_type pass
+        ON pt.type_id = pass.id
+    WHERE cpt.user_id = p_customer_id
+      AND p.status = 'Completed'::payment_status
+      AND p.payment_date >= v_month_start
+      AND p.payment_date < v_month_end;
+
+    RETURN v_total;
+END;
+$$;
+
+}}}
+
+=== `get_driver_shift_count`
+
+Оваа функција служи за да го пресмета бројот на смени (line assignments) што одреден возач ги има извршено во даден временски интервал.
+
+{{{
+
+CREATE OR REPLACE FUNCTION get_driver_shift_count(
+    p_driver_id BIGINT,
+    p_date_from TIMESTAMP,
+    p_date_to TIMESTAMP
+)
+RETURNS INTEGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_count INTEGER;
+BEGIN
+    IF p_date_from >= p_date_to THEN
+        RAISE EXCEPTION 'Start date must be before end date.';
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1
+        FROM Driver d
+        WHERE d.user_id = p_driver_id
+    ) THEN
+        RAISE EXCEPTION 'Driver with id % does not exist.', p_driver_id;
+    END IF;
+
+    SELECT COUNT(*)
+    INTO v_count
+    FROM Line_assignment la
+    WHERE la.driver_id = p_driver_id
+      AND la.start_time >= p_date_from
+      AND la.start_time < p_date_to;
+
+    RETURN v_count;
+END;
+$$;
+
+}}}
+
+== Процедури
+
+=== `buy_single_ticket`
+
+Оваа процедура служи за креирање и купување на single билет за одреден клиент, при што автоматски се регистрира плаќање, се генерира билет поврзан со одредена линија и се поврзуваат сите потребни записи во системот за евиденција.
+
+{{{
+
+CREATE OR REPLACE PROCEDURE buy_single_ticket(
+    p_customer_id BIGINT,
+    p_assignment_id BIGINT,
+    p_payment_type_id BIGINT
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_payment_id BIGINT;
+    v_ticket_id BIGINT;
+BEGIN
+    IF NOT EXISTS (
+        SELECT 1
+        FROM Customer c
+        WHERE c.user_id = p_customer_id
+    ) THEN
+        RAISE EXCEPTION 'Customer with id % does not exist.', p_customer_id;
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1
+        FROM Line_assignment la
+        WHERE la.assignment_id = p_assignment_id
+    ) THEN
+        RAISE EXCEPTION 'Line assignment with id % does not exist.', p_assignment_id;
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1
+        FROM Payment_type pt
+        WHERE pt.type_id = p_payment_type_id
+    ) THEN
+        RAISE EXCEPTION 'Payment type with id % does not exist.', p_payment_type_id;
+    END IF;
+
+    INSERT INTO Payment (type_id, status, payment_date, transaction_number)
+    VALUES (
+        p_payment_type_id,
+        'Completed'::payment_status,
+        CURRENT_DATE,
+        'TRX-SINGLE-' || extract(epoch from clock_timestamp())::BIGINT || '-' || p_customer_id
+    )
+    RETURNING payment_id INTO v_payment_id;
+
+    INSERT INTO Ticket (user_id)
+    VALUES (p_customer_id)
+    RETURNING ticket_id INTO v_ticket_id;
+
+    INSERT INTO Single_ticket (ticket_id, amount, assignment_id)
+    VALUES (v_ticket_id, 50, p_assignment_id);
+
+    INSERT INTO Customer_Payment_Ticket (payment_id, ticket_id, user_id)
+    VALUES (v_payment_id, v_ticket_id, p_customer_id);
+
+    RAISE NOTICE 'Single ticket created. ticket_id=%, payment_id=%', v_ticket_id, v_payment_id;
+END;
+$$;
+
+}}}
+
+=== `buy_pass_ticket`
+
+Оваа процедура служи за купување и креирање на pass билет за клиент, при што се регистрира плаќање, се создава билет од избран тип на pass и се поврзуваат сите релевантни записи во системот за евиденција.
+
+{{{
+
+CREATE OR REPLACE PROCEDURE buy_pass_ticket(
+    p_customer_id BIGINT,
+    p_pass_type_id INTEGER,
+    p_payment_type_id BIGINT
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_payment_id BIGINT;
+    v_ticket_id BIGINT;
+BEGIN
+    IF NOT EXISTS (
+        SELECT 1
+        FROM Customer c
+        WHERE c.user_id = p_customer_id
+    ) THEN
+        RAISE EXCEPTION 'Customer with id % does not exist.', p_customer_id;
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1
+        FROM Pass_type pt
+        WHERE pt.id = p_pass_type_id
+    ) THEN
+        RAISE EXCEPTION 'Pass type with id % does not exist.', p_pass_type_id;
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1
+        FROM Payment_type pt
+        WHERE pt.type_id = p_payment_type_id
+    ) THEN
+        RAISE EXCEPTION 'Payment type with id % does not exist.', p_payment_type_id;
+    END IF;
+
+    INSERT INTO Payment (type_id, status, payment_date, transaction_number)
+    VALUES (
+        p_payment_type_id,
+        'Completed'::payment_status,
+        CURRENT_DATE,
+        'TRX-PASS-' || extract(epoch from clock_timestamp())::BIGINT || '-' || p_customer_id
+    )
+    RETURNING payment_id INTO v_payment_id;
+
+    INSERT INTO Ticket (user_id)
+    VALUES (p_customer_id)
+    RETURNING ticket_id INTO v_ticket_id;
+
+    INSERT INTO Pass_ticket (ticket_id, type_id)
+    VALUES (v_ticket_id, p_pass_type_id);
+
+    INSERT INTO Customer_Payment_Ticket (payment_id, ticket_id, user_id)
+    VALUES (v_payment_id, v_ticket_id, p_customer_id);
+
+    RAISE NOTICE 'Pass ticket created. ticket_id=%, payment_id=%', v_ticket_id, v_payment_id;
+END;
+$$;
+
+}}}
+
+=== `assign_driver_to_line`
+
+Оваа процедура служи за доделување на возач на конкретна линија и автобус во одреден временски период, при што прво проверува дали сите учесници и ресурси се активни и достапни и дали нема преклопување со други смени.
+
+{{{
+
+CREATE OR REPLACE PROCEDURE assign_driver_to_line(
+    p_driver_id BIGINT,
+    p_admin_id BIGINT,
+    p_chassis_number VARCHAR,
+    p_schedule_id INTEGER,
+    p_start_time TIMESTAMP,
+    p_end_time TIMESTAMP
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_assignment_id BIGINT;
+BEGIN
+    IF p_start_time >= p_end_time THEN
+        RAISE EXCEPTION 'Start time must be before end time.';
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1
+        FROM Driver d
+        WHERE d.user_id = p_driver_id
+          AND d.status = 'Active'::employee_status
+    ) THEN
+        RAISE EXCEPTION 'Driver % does not exist or is not active.', p_driver_id;
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1
+        FROM Admin a
+        WHERE a.user_id = p_admin_id
+          AND a.status = 'Active'::employee_status
+    ) THEN
+        RAISE EXCEPTION 'Admin % does not exist or is not active.', p_admin_id;
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1
+        FROM Bus_instance bi
+        WHERE bi.chassis_number = p_chassis_number
+          AND bi.status = 'Active'::bus_status
+    ) THEN
+        RAISE EXCEPTION 'Bus % does not exist or is not active.', p_chassis_number;
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1
+        FROM Schedule s
+        WHERE s.schedule_id = p_schedule_id
+    ) THEN
+        RAISE EXCEPTION 'Schedule % does not exist.', p_schedule_id;
+    END IF;
+
+    IF EXISTS (
+        SELECT 1
+        FROM Line_assignment la
+        WHERE la.driver_id = p_driver_id
+          AND p_start_time < la.end_time
+          AND p_end_time > la.start_time
+    ) THEN
+        RAISE EXCEPTION 'Driver % already has an overlapping shift.', p_driver_id;
+    END IF;
+
+    IF EXISTS (
+        SELECT 1
+        FROM Line_assignment la
+        WHERE la.chassis_number = p_chassis_number
+          AND p_start_time < la.end_time
+          AND p_end_time > la.start_time
+    ) THEN
+        RAISE EXCEPTION 'Bus % is already assigned in this time interval.', p_chassis_number;
+    END IF;
+
+    INSERT INTO Line_assignment
+        (driver_id, admin_id, chassis_number, schedule_id, start_time, end_time)
+    VALUES
+        (p_driver_id, p_admin_id, p_chassis_number, p_schedule_id, p_start_time, p_end_time)
+    RETURNING assignment_id INTO v_assignment_id;
+
+    RAISE NOTICE 'Line assignment created. assignment_id=%', v_assignment_id;
+END;
+$$;
+
+}}}
+
+== Тригери
+
+=== `prevent_driver_shift_overlap`
+
+Оваа функција со trigger служи за автоматска проверка и спречување на преклопување на работни смени за возач, односно не дозволува внес или измена на assignment ако временски се поклопува со друга веќе постоечка смена за истиот возач.
+
+{{{
+
+CREATE OR REPLACE FUNCTION prevent_driver_shift_overlap_fn()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    IF NEW.start_time >= NEW.end_time THEN
+        RAISE EXCEPTION 'Start time must be before end time.';
+    END IF;
+
+    IF EXISTS (
+        SELECT 1
+        FROM Line_assignment la
+        WHERE la.driver_id = NEW.driver_id
+          AND la.assignment_id <> COALESCE(NEW.assignment_id, -1)
+          AND NEW.start_time < la.end_time
+          AND NEW.end_time > la.start_time
+    ) THEN
+        RAISE EXCEPTION 'Driver % already has an overlapping shift.', NEW.driver_id;
+    END IF;
+
+    RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_prevent_driver_shift_overlap ON Line_assignment;
+
+CREATE TRIGGER trg_prevent_driver_shift_overlap
+BEFORE INSERT OR UPDATE ON Line_assignment
+FOR EACH ROW
+EXECUTE FUNCTION prevent_driver_shift_overlap_fn();
+
+}}}
+
+=== `log_payment_status_change`
+
+Оваа функција со trigger служи за автоматско логирање на секоја промена на статусот на плаќање, така што секогаш кога ќе се смени статусот во табелата Payment, се зачувува запис со стар и нов статус во посебна лог табела.
+
+{{{
+
+CREATE TABLE IF NOT EXISTS Payment_status_log
+(
+    log_id BIGSERIAL PRIMARY KEY,
+    payment_id BIGINT NOT NULL,
+    old_status payment_status,
+    new_status payment_status,
+    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    description TEXT,
+    CONSTRAINT payment_status_log_payment_fk
+        FOREIGN KEY (payment_id) REFERENCES Payment(payment_id)
+        ON DELETE CASCADE
+        ON UPDATE CASCADE
+);
+
+CREATE OR REPLACE FUNCTION log_payment_status_change_fn()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    IF OLD.status IS DISTINCT FROM NEW.status THEN
+        INSERT INTO Payment_status_log
+            (payment_id, old_status, new_status, description)
+        VALUES
+            (
+                NEW.payment_id,
+                OLD.status,
+                NEW.status,
+                'Payment status changed from ' || OLD.status || ' to ' || NEW.status
+            );
+    END IF;
+
+    RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_log_payment_status_change ON Payment;
+
+CREATE TRIGGER trg_log_payment_status_change
+AFTER UPDATE OF status ON Payment
+FOR EACH ROW
+EXECUTE FUNCTION log_payment_status_change_fn();
+
+}}}