Context Navigation

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

Функции

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

`count_active_lines`

Оваа функција е имплементирана со цел да обезбеди брз и едноставен начин за добивање на бројот на активни линии во системот. Нејзината намена е да поддржи анализа и следење на состојбата на транспортната мрежа преку централизирано пресметување на активните линии.

CREATE OR REPLACE FUNCTION count_active_lines()
RETURNS INTEGER AS $$
DECLARE
    v_active_count INTEGER;
BEGIN
    SELECT COUNT(*)
    INTO v_active_count
    FROM Line
    WHERE is_active = TRUE;
    
    RETURN v_active_count;
END;
$$ LANGUAGE plpgsql;

SELECT count_active_lines();

Процедури

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

`generate_line_assignment`

Оваа процедура служи за внесување на нова распределба на линија, при што се зачувуваат информациите за возачот, администраторот, автобусот и возниот ред што ќе се користи. Со нејзина помош процесот на креирање на распределби е поедноставен и се намалува можноста за грешки при внесувањето на податоците.

CREATE OR REPLACE PROCEDURE generate_line_assignment(
    assignment_driver_id BIGINT,
    assignment_admin_id BIGINT,
    assignment_chassis_number VARCHAR,
    assignment_schedule_id INT,
    assignment_start_time TIMESTAMP,
    assignment_end_time TIMESTAMP
)
LANGUAGE plpgsql AS $$

DECLARE
    new_assignment_id BIGINT;

BEGIN

    INSERT INTO Line_assignment(
        driver_id,
        admin_id,
        chassis_number,
        schedule_id,
        start_time,
        end_time
    )
    VALUES(
        assignment_driver_id,
        assignment_admin_id,
        assignment_chassis_number,
        assignment_schedule_id,
        assignment_start_time,
        assignment_end_time
    )
    RETURNING assignment_id INTO new_assignment_id;

    COMMIT;

END;
$$;

`register_customer`

Оваа процедура овозможува регистрација на нов корисник во системот преку внесување на неговите лични податоци и автоматско креирање на запис во табелата за патници. Со тоа се обезбедува едноставен и организиран процес на регистрација, при што податоците за корисникот и неговиот тип се зачувуваат на соодветните места во базата на податоци.

create or replace procedure register_customer(
    customer_name varchar,
    customer_surname varchar,
    customer_email varchar,
    customer_password varchar,
    customer_address varchar,
    customer_birth date,
    customer_phone varchar,
    customer_id_card varchar,
    customer_type_value customer_type
)
language plpgsql as $$

declare
    new_user_id bigint;

begin

    insert into ApplicationUser(
        name,
        surname,
        email,
        password,
        address,
        date_of_birth,
        phone_number,
        id_card
    )
    values(
        customer_name,
        customer_surname,
        customer_email,
        customer_password,
        customer_address,
        customer_birth,
        customer_phone,
        customer_id_card
    )
    returning user_id into new_user_id;

    insert into Customer(user_id, type)
    values(new_user_id, customer_type_value);

    commit;

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();

`payment_expired`

Овој тригер автоматски го проверува статусот на секоја уплата при нејзино внесување или ажурирање во системот. Доколку уплатата е сè уште во статус „Pending“, а датумот за плаќање е поминат, статусот автоматски се менува во „Failed“, со што се обезбедува точно евидентирање на неуспешните уплати.

create or replace function payment_expired()
returns trigger as $$

begin

    if NEW.status = 'Pending'
    and NEW.payment_date < current_date then

        NEW.status := 'Failed';

    end if;

    return NEW;

end;
$$ language plpgsql;

create trigger payment_status_trigger
before insert or update on Payment
for each row
execute function payment_expired();

Last modified 10 days ago Last modified on 06/08/26 18:17:22

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text