wiki:DatabaseProgramming

Context Navigation

Version 3 (modified by 231003, 3 weeks ago) ( diff )
--

Функции, процедури и тригери

Функции

`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`

Оваj 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`

Оваj 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();

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text