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

Процедури

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

prodecures.sql​

Процедура 1 : pr_sell_ticket

CREATE OR REPLACE PROCEDURE pr_sell_ticket(
    IN p_passenger_embg CHAR(13),
    IN p_passenger_id INT4,
    IN p_trip_id INT4,
    IN p_start_station_id INT4,
    IN p_end_station_id INT4,
    IN p_seat_number INT4,
    IN p_carriage_number INT4,
    IN p_price NUMERIC(2, 0),
    IN p_payment_method VARCHAR(20),
    OUT o_ticket_id INT4,
    OUT o_payment_id INT4,
    IN p_reservation_id INT4 DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_passenger_exists INT;
    v_trip_exists INT;
    v_station_start_exists INT;
    v_station_end_exists INT;
    v_seat_taken INT;
BEGIN
    SELECT COUNT(*) INTO v_passenger_exists
    FROM Passenger
    WHERE PersonEMBG = p_passenger_embg AND passenger_id = p_passenger_id;

    IF v_passenger_exists = 0 THEN
        RAISE EXCEPTION 'Passenger with EMBG % and ID % does not exist.', p_passenger_embg, p_passenger_id;
    END IF;

    SELECT COUNT(*) INTO v_trip_exists FROM "Train Trip" WHERE trip_id = p_trip_id;
    IF v_trip_exists = 0 THEN
        RAISE EXCEPTION 'Train Trip ID % does not exist.', p_trip_id;
    END IF;

    SELECT COUNT(*) INTO v_station_start_exists FROM Station WHERE station_id = p_start_station_id;
    SELECT COUNT(*) INTO v_station_end_exists FROM Station WHERE station_id = p_end_station_id;
    IF v_station_start_exists = 0 OR v_station_end_exists = 0 THEN
        RAISE EXCEPTION 'Invalid origin or destination station provided.';
    END IF;

    SELECT COUNT(*) INTO v_seat_taken
    FROM Ticket
    WHERE "Train Triptrip_id" = p_trip_id
      AND carriage_number = p_carriage_number
      AND seat_number = p_seat_number
      AND ticket_status = 'Active';

    IF v_seat_taken > 0 THEN
        RAISE EXCEPTION 'Seat % in Carriage % is already booked for Trip %.', p_seat_number, p_carriage_number, p_trip_id;
    END IF;

    IF p_reservation_id IS NOT NULL THEN
        UPDATE Reservation
        SET status = 'Completed'
        WHERE reservation_id = p_reservation_id;
    END IF;

    INSERT INTO Payment (
        payment_method,
        amount,
        transaction_date,
        Reservationreservation_id,
        Passengerpassenger_id,
        PassengerPersonEMBG2
    )
    VALUES (
        p_payment_method,
        p_price,
        CURRENT_DATE,
        p_reservation_id,
        p_passenger_id,
        p_passenger_embg
    )
    RETURNING payment_id INTO o_payment_id;

    INSERT INTO Ticket (
        seat_number,
        carriage_number,
        price,
        ticket_status,
        Paymentpayment_id,
        "Train Triptrip_id",
        Stationstation_id,
        Stationstation_id2
    )
    VALUES (
        p_seat_number,
        p_carriage_number,
        p_price,
        'Active',
        o_payment_id,
        p_trip_id,
        p_start_station_id,
        p_end_station_id
    )
    RETURNING ticket_id INTO o_ticket_id;

EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Transaction rolled back due to error: %', SQLERRM;
        RAISE;
END;
$$;


DO $$
DECLARE
    v_ticket_output INT4;
    v_payment_output INT4;
BEGIN
    CALL pr_sell_ticket(
        p_passenger_embg   => '2510985918703',
        p_passenger_id     => 290,
        p_trip_id          => 4,
        p_start_station_id => 10,
        p_end_station_id   => 14,
        p_seat_number      => 22,
        p_carriage_number  => 2,
        p_price            => 45,
        p_payment_method   => 'Apple Pay',
        o_ticket_id        => v_ticket_output,
        o_payment_id       => v_payment_output,
        p_reservation_id   => NULL
    );

    RAISE NOTICE 'Sale Complete! Ticket ID: %, Payment ID: %', v_ticket_output, v_payment_output;
END $$;

select * from ticket where ticket_id='23989062';

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

Процедура 2 : cancel_reservation

CREATE OR REPLACE PROCEDURE cancel_reservation(p_reservation_id INT)
AS $$
BEGIN
    UPDATE Reservation
    SET status = 'Cancelled'
    WHERE reservation_id = p_reservation_id;

    UPDATE Ticket
    SET ticket_status = 'Cancelled'
    WHERE Paymentpayment_id IN (
        SELECT payment_id
        FROM Payment
        WHERE Reservationreservation_id = p_reservation_id
    );
    RAISE NOTICE 'Reservation % has been cancelled successfully.', p_reservation_id;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM Reservation where reservation_id = 17;

SELECT * FROM Ticket
WHERE Paymentpayment_id IN (
    SELECT payment_id FROM Payment WHERE Reservationreservation_id = 17
);

CALL cancel_reservation(17);

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

Процедура 3 : apply_seasonal_discount

CREATE OR REPLACE PROCEDURE apply_seasonal_discount(p_route_id INT, p_discount_pct NUMERIC)
AS $$
BEGIN
    UPDATE Ticket
    SET price = price * (1 - p_discount_pct / 100)
    WHERE "Train Triptrip_id" IN (
        SELECT trip_id FROM "Train Trip" WHERE Routeroute_id = p_route_id
    );

    RAISE NOTICE 'Fares for route % have been reduced by %%%.', p_route_id, p_discount_pct;
END;
$$ LANGUAGE plpgsql;


CALL apply_seasonal_discount(355, 20);

SELECT t.ticket_id, t.price, t.ticket_status, tt.trip_id, tt.Routeroute_id
FROM Ticket t
JOIN "Train Trip" tt ON t."Train Triptrip_id" = tt.trip_id
WHERE tt.Routeroute_id = 355;

select * from "Train Trip" where Routeroute_id=355;

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

Процедура 4 : update_trip_delay

CREATE OR REPLACE PROCEDURE update_trip_delay(p_trip_id INT, p_delay_mins INT)
AS $$
BEGIN
    UPDATE "Train Trip"
    SET delay_minutes = p_delay_mins,
        trip_status = CASE WHEN p_delay_mins > 0 THEN 'Delayed' ELSE 'On Time' END
    WHERE trip_id = p_trip_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Trip with ID % not found.', p_trip_id;
    END IF;
END;
$$ LANGUAGE plpgsql;

CALL update_trip_delay(1220, 15);

select * from "Train Trip" where trip_id=1220;

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

Процедура 5 : reassign_passengers_to_new_trip

CREATE OR REPLACE PROCEDURE reassign_passengers_to_new_trip(p_cancelled_trip_id INT, p_new_trip_id INT)
AS $$
BEGIN
    UPDATE Ticket
    SET "Train Triptrip_id" = p_new_trip_id,
        ticket_status = 'Reassigned'
    WHERE "Train Triptrip_id" = p_cancelled_trip_id;

    UPDATE "Train Trip"
    SET trip_status = 'Cancelled'
    WHERE trip_id = p_cancelled_trip_id;

    RAISE NOTICE 'All passengers from % have been transferred to trip %.', p_cancelled_trip_id, p_new_trip_id;
END;
$$ LANGUAGE plpgsql;

SELECT ticket_id, "Train Triptrip_id", ticket_status
FROM Ticket
WHERE "Train Triptrip_id" = 380477;

SELECT ticket_id, "Train Triptrip_id", ticket_status
FROM Ticket
WHERE "Train Triptrip_id" = 200;

CALL reassign_passengers_to_new_trip(380477, 200);

Оваа процедура ги префрла сите патници од едно откажано патување на ново патување. Кога ќе се повика, ги ажурира билетите да покажуваат нов "Train Trip", им го менува статусот во "Reassigned", а старото патување го означува како "Cancelled". Се користи за брзо пренасочување на патници при откажани или променети возни линии.

Процедура 6 : transfer_employee

CREATE OR REPLACE PROCEDURE transfer_employee(
    p_embg CHAR(13),
    p_new_company_id INT
)
AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Company WHERE company_id = p_new_company_id) THEN
        RAISE EXCEPTION 'Company ID % does not exist.', p_new_company_id;
    END IF;

    UPDATE Employee
    SET Companycompany_id = p_new_company_id
    WHERE PersonEMBG = p_embg;

    RAISE NOTICE 'Employee with EMBG % has been successfully transferred to company %.', p_embg, p_new_company_id;
END;
$$ LANGUAGE plpgsql;

CALL transfer_employee('2005992333958', 4);

select * from employee where PersonEMBG='2005992333958'

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

Процедура 7 : assign_platform_to_trip

CREATE OR REPLACE PROCEDURE assign_platform_to_trip(
    p_trip_id INT,
    p_platform_id INT,
    p_station_id INT
)
AS $$
BEGIN
    UPDATE "Train Trip"
    SET Platformplatform_id = p_platform_id,
        PlatformStationstation_id = p_station_id
    WHERE trip_id = p_trip_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Trip with ID % not found.', p_trip_id;
    END IF;

    RAISE NOTICE 'Platform % assigned to trip %.',
                 p_platform_id, p_trip_id;
END;
$$ LANGUAGE plpgsql;
CALL assign_platform_to_trip(3300, 3, 1084);

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

Процедура 8 : change_trip_platform

CREATE OR REPLACE PROCEDURE change_trip_platform(
    p_trip_id INT,
    p_new_platform_id INT
)
AS $$
BEGIN
    UPDATE "Train Trip"
    SET Platformplatform_id = p_new_platform_id
    WHERE trip_id = p_trip_id;

    RAISE NOTICE 'Trip % has been transferred to platform %.', p_trip_id, p_new_platform_id;
END;
$$ LANGUAGE plpgsql;


CALL change_trip_platform(3300, 2);

select * from "Train Trip" where trip_id=3300

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

Тригери

Во овој дел се прикажани скриптите потребни за дефинирање на тригерите кои овозможуваат автоматска обработка и контрола на податоците во базата. Тригерите се користат за одржување на интегритетот, автоматско ажурирање на вредности и евиденција на промените во системот.

triggers.sql​

Тригер 1 : auto_set_reservation_status

CREATE FUNCTION auto_set_reservation_status()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.expiry_time < CURRENT_DATE THEN
        NEW.status := 'Expired';
    ELSE
        NEW.status := 'Active';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_auto_set_reservation_status
BEFORE INSERT OR UPDATE ON Reservation
FOR EACH ROW
EXECUTE FUNCTION auto_set_reservation_status();

Овој trigger автоматски го поставува статусот на резервацијата како "Active" или "Expired" според датумот на истекување. Се користи за автоматско ажурирање на резервациите и одржување точни податоци во системот

Тригер 2 : prevent_duplicate_seat_booking

CREATE  FUNCTION prevent_duplicate_seat_booking()
RETURNS TRIGGER AS $$
DECLARE
    seat_exists INT;
BEGIN
    SELECT COUNT(*)
    INTO seat_exists
    FROM Ticket
    WHERE seat_number = NEW.seat_number
      AND carriage_number = NEW.carriage_number
      AND "Train Triptrip_id" = NEW."Train Triptrip_id";

    IF seat_exists > 0 THEN
        RAISE EXCEPTION 'Seat already booked for this train trip!';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_prevent_duplicate_seat_booking
BEFORE INSERT ON Ticket
FOR EACH ROW
EXECUTE FUNCTION prevent_duplicate_seat_booking();

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

Тригер 3 : update_trip_status_based_on_delay

CREATE FUNCTION update_trip_status_based_on_delay()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.delay_minutes > 0 THEN
        NEW.trip_status := 'Delayed';
    ELSE
        NEW.trip_status := 'On Time';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_trip_status
BEFORE INSERT OR UPDATE ON "Train Trip"
FOR EACH ROW
EXECUTE FUNCTION update_trip_status_based_on_delay();

Овој trigger автоматски го ажурира статусот на патувањето според доцнењето на возот. Ако бројот на минути за доцнење е поголем од 0, статусот се поставува на "Delayed", а во спротивно на "On Time". Се користи за автоматско следење и прикажување на точниот статус на возовите.

Тригер 4 : auto_set_transaction_date

CREATE FUNCTION auto_set_transaction_date()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.transaction_date IS NULL THEN
        NEW.transaction_date := CURRENT_DATE;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_auto_set_transaction_date
BEFORE INSERT ON Payment
FOR EACH ROW
EXECUTE FUNCTION auto_set_transaction_date();

Овој trigger автоматски ја поставува датата на трансакцијата ако не е внесена при креирање на плаќање. Ако transaction_date е NULL, системот ја става тековната дата. Се користи за да се осигураме дека секое плаќање има точен датум и за автоматско и конзистентно водење на финансиски записи.

Тригер 5 : prevent_employee_trip_overlap

CREATE FUNCTION prevent_employee_trip_overlap()
RETURNS TRIGGER AS $$
DECLARE
    overlap_count INT;
BEGIN
    SELECT COUNT(*)
    INTO overlap_count
    FROM "Train Trip"
    WHERE Employeeemployee_id = NEW.Employeeemployee_id
      AND EmployeePersonEMBG2 = NEW.EmployeePersonEMBG2
      AND departure_time = NEW.departure_time;

    IF overlap_count > 0 THEN
        RAISE EXCEPTION
        'Employee already assigned to another trip at this time!';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_prevent_employee_trip_overlap
BEFORE INSERT ON "Train Trip"
FOR EACH ROW
EXECUTE FUNCTION prevent_employee_trip_overlap();

Овој trigger спречува еден вработен да биде доделен на две патувања во исто време. Пред внесување нов запис во "Train Trip", системот проверува дали веќе постои ист вработен со исто време на поаѓање и ако да, фрла грешка. Се користи за да се избегнат конфликти во распоредот и за правилно распределување на вработените.

Тригер 6 : check_and_mark_overbooked

CREATE OR REPLACE FUNCTION check_and_mark_overbooked()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_capacity INT;
    v_sold_tickets INT;
BEGIN
    SELECT t.capacity
    INTO v_capacity
    FROM "Train Trip" tt
    JOIN Train t ON tt.Traintrain_id = t.train_id
    WHERE tt.trip_id = NEW."Train Triptrip_id";

    SELECT COUNT(*)
    INTO v_sold_tickets
    FROM Ticket
    WHERE "Train Triptrip_id" = NEW."Train Triptrip_id"
      AND ticket_status = 'Active';

    IF v_sold_tickets > v_capacity THEN
        UPDATE "Train Trip"
        SET trip_status = 'Overbooked'
        WHERE trip_id = NEW."Train Triptrip_id";

        RAISE NOTICE 'Trip % is OVERBOOKED automatically via Trigger. Capacity: %, Sold: %.',
                     NEW."Train Triptrip_id", v_capacity, v_sold_tickets;
    END IF;

    RETURN NEW;
END;
$$;

Овој trigger проверува дали бројот на продадени билети го надминува капацитетот на возот за дадено патување. Ако има повеќе билети од дозволени места, автоматски го менува статусот на патувањето во "Overbooked" и дава известување. Се користи за автоматско следење на пополнетост и спречување на преголем број резервации.

Тригер 7 : prevent_station_delete_active_trip

CREATE FUNCTION prevent_station_delete_active_trip()
RETURNS TRIGGER AS $$
DECLARE
    trip_count INT;
BEGIN
    SELECT COUNT(*)
    INTO trip_count
    FROM Ticket
    WHERE Stationstation_id = OLD.station_id
       OR Stationstation_id2 = OLD.station_id;

    IF trip_count > 0 THEN
        RAISE EXCEPTION
        'Cannot delete station because tickets/trips reference it!';
    END IF;

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_prevent_station_delete_active_trip
BEFORE DELETE ON Station
FOR EACH ROW
EXECUTE FUNCTION prevent_station_delete_active_trip();

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

Функции

Во овој дел се прикажани функциите кои се користат за извршување на специфични пресметки и обработка на податоците во базата. Функциите овозможуваат повторна употреба на логика и поедноставување на SQL наредбите.

functions.sql​

Функција 1 : free_seats_trip

CREATE OR REPLACE FUNCTION free_seats_trip(
    p_trip_id INT
)
RETURNS INT
AS $$
DECLARE
    v_capacity INT;
    v_sold_tickets INT;
BEGIN
    SELECT t.capacity
    INTO v_capacity
    FROM "Train Trip" tt
    JOIN Train t
        ON tt.Traintrain_id = t.train_id
    WHERE tt.trip_id = p_trip_id;

    SELECT COUNT(*)
    INTO v_sold_tickets
    FROM Ticket
    WHERE "Train Triptrip_id" = p_trip_id;

    RETURN v_capacity - v_sold_tickets;
END;
$$ LANGUAGE plpgsql;

SELECT free_seats_trip(155);

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

Функција 2 : get_route_stations

CREATE FUNCTION get_route_stations(
    p_route_id INT
)
RETURNS TABLE (
    sequence_number INT,
    station_name VARCHAR,
    city VARCHAR
)
AS $$
BEGIN

    RETURN QUERY
    SELECT
        rs.sequence_number,
        st.station_name,
        st.city
    FROM Route_Segment rs
    JOIN Station st
      ON st.station_id = rs.Stationstation_id
    WHERE rs.Routeroute_id = p_route_id
    ORDER BY rs.sequence_number;

END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_route_stations(1);

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

Функција 3 : get_next_departing_trains

CREATE FUNCTION get_next_departing_trains()
RETURNS TABLE (
    trip_id INT,
    train_id INT,
    departure_time DATE,
    arrival_time DATE,
    trip_status VARCHAR
)
AS $$
BEGIN

    RETURN QUERY
    SELECT
        tt.trip_id,
        tt.Traintrain_id,
        tt.departure_time,
        tt.arrival_time,
        tt.trip_status
    FROM "Train Trip" tt
    WHERE tt.departure_time >= CURRENT_DATE
    ORDER BY tt.departure_time ASC;

END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_next_departing_trains();

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

Функција 4 : get_passenger_total_spending

CREATE FUNCTION get_passenger_total_spending(
    p_embg CHAR(13)
)
RETURNS NUMERIC AS $$
DECLARE
    total_spending NUMERIC;
BEGIN

    SELECT COALESCE(SUM(pay.amount), 0)
    INTO total_spending
    FROM Payment pay
    JOIN Passenger pas
      ON pas.PersonEMBG = pay.PassengerPersonEMBG2
    WHERE pas.PersonEMBG = p_embg;

    RETURN total_spending;

END;
$$ LANGUAGE plpgsql;

SELECT get_passenger_total_spending('2509982211077');

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

Функција 5 : get_trip_revenue_efficiency

CREATE OR REPLACE FUNCTION get_trip_revenue_efficiency(p_trip_id INT)
RETURNS TEXT AS $$
DECLARE
    v_capacity INT;
    v_sold_count INT;
    v_total_revenue NUMERIC;
    v_occupancy_pct NUMERIC;
    v_rev_per_seat NUMERIC;
    v_result TEXT;
BEGIN
    SELECT t.capacity INTO v_capacity
    FROM "Train Trip" tt
    JOIN Train t ON tt.Traintrain_id = t.train_id
    WHERE tt.trip_id = p_trip_id;

    SELECT COUNT(*), COALESCE(SUM(price), 0)
    INTO v_sold_count, v_total_revenue
    FROM Ticket
    WHERE "Train Triptrip_id" = p_trip_id;

    IF v_capacity > 0 THEN
        v_occupancy_pct := (v_sold_count::NUMERIC / v_capacity) * 100;
        v_rev_per_seat := v_total_revenue / v_capacity;
    ELSE
        RETURN 'Error: Train has no capacity';
    END IF;

    IF v_occupancy_pct >= 85 THEN
        v_result := 'High Demand (' || ROUND(v_occupancy_pct, 1) || '%)';
    ELSIF v_occupancy_pct >= 50 THEN
        v_result := 'Optimal (' || ROUND(v_occupancy_pct, 1) || '%)';
    ELSE
        v_result := 'Low Efficiency (' || ROUND(v_occupancy_pct, 1) || '%)';
    END IF;

    RETURN v_result || ' | Rev/Seat: ' || ROUND(v_rev_per_seat, 2);
END;
$$ LANGUAGE plpgsql;

SELECT
    tt.trip_id,
    r.route_name,
    tt.departure_time,
    get_trip_revenue_efficiency(tt.trip_id) AS performance_report
FROM "Train Trip" tt
JOIN Route r ON tt.Routeroute_id = r.route_id
WHERE tt.trip_status = 'Completed'
ORDER BY tt.departure_time DESC
LIMIT 150;

Оваа функција ја анализира ефикасноста на едно патување со воз врз основа на пополнетост и приход. Го пресметува процентот на искористеност на капацитетот и просечниот приход по седиште, па враќа оценка како „High Demand“, „Optimal“ или „Low Efficiency“ заедно со финансискиот резултат. Cе користи за анализа на профитабилноста и перформансите на завршените патувања.

Функција 6 : get_passenger_name_by_id

CREATE OR REPLACE FUNCTION get_passenger_name_by_id(p_id INT)
RETURNS VARCHAR AS $$
DECLARE
    v_full_name VARCHAR;
BEGIN
    SELECT p.first_name || ' ' || p.last_name INTO v_full_name
    FROM Passenger pass
    JOIN Person p ON pass.PersonEMBG = p.EMBG
    WHERE pass.passenger_id = p_id;

    RETURN COALESCE(v_full_name, 'Passenger not found');
END;
$$ LANGUAGE plpgsql;

SELECT get_passenger_name_by_id(500);

Оваа функција го враќа целосното име на патник според неговиот passenger_id. Ги спојува името и презимето од табелата Person. Ако патникот не постои, враќа порака „Passenger not found“. Cе користи за брзо добивање на информации за патници во извештаи и интерфејсот.

Функција 6 : get_most_profitable_route

CREATE OR REPLACE FUNCTION get_most_profitable_route()
RETURNS TEXT AS $$
DECLARE
    v_route_name VARCHAR;
    v_total_revenue NUMERIC;
BEGIN
    SELECT
        r.route_name,
        COALESCE(SUM(t.price), 0)
    INTO
        v_route_name,
        v_total_revenue
    FROM Route r
    JOIN "Train Trip" tt
        ON r.route_id = tt.Routeroute_id
    JOIN Ticket t
        ON tt.trip_id = t."Train Triptrip_id"
    GROUP BY r.route_id, r.route_name
    ORDER BY SUM(t.price) DESC
    LIMIT 1;

    RETURN
        'Most Profitable Route: ' || v_route_name ||
        ' | Total Revenue: ' || ROUND(v_total_revenue, 2);
END;
$$ LANGUAGE plpgsql;

SELECT get_most_profitable_route();

Оваа функција ја наоѓа најпрофитабилната рута во системот. Го пресметува вкупниот приход од билети за секоја рута и ја враќа онаа со најголем приход, заедно со сумата. Cе користи за анализа на бизнис перформанси и идентификација на најисплатливи линии.

Функција 7 : check_frequent_traveler

CREATE OR REPLACE FUNCTION check_frequent_traveler(p_passenger_id INT)
RETURNS TEXT AS $$
DECLARE
    v_ticket_count INT;
    v_full_name VARCHAR;
BEGIN

    SELECT p.first_name || ' ' || p.last_name
    INTO v_full_name
    FROM Passenger pass
    JOIN Person p ON pass.PersonEMBG = p.EMBG
    WHERE pass.passenger_id = p_passenger_id
    LIMIT 1;

    SELECT COUNT(t.ticket_id)
    INTO v_ticket_count
    FROM Ticket t
    JOIN Payment pay ON t.Paymentpayment_id = pay.payment_id
    JOIN Reservation r ON pay.Reservationreservation_id = r.reservation_id
    WHERE r.Passengerpassenger_id = p_passenger_id;

    IF v_ticket_count >= 20 THEN
        RETURN v_full_name || ' is a VIP Frequent Traveler with ' || v_ticket_count || ' trips.';
    ELSIF v_ticket_count >= 10 THEN
        RETURN v_full_name || ' is a Frequent Traveler with ' || v_ticket_count || ' trips.';
    ELSIF v_ticket_count > 0 THEN
        RETURN v_full_name || ' is an Occasional Traveler with ' || v_ticket_count || ' trips.';
    ELSE
        RETURN v_full_name || ' has no trips.';
    END IF;
END;
$$ LANGUAGE plpgsql;

SELECT check_frequent_traveler(800);

Оваа функција проверува колку патувања има направено одреден патник во системот. Го пресметува вкупниот број на купени билети поврзани со неговите резервации и врз основа на тоа го класифицира како VIP Frequent Traveler, Frequent Traveler, Occasional Traveler или патник без патувања. Cе користи за анализа на активноста и лојалноста на патниците, како и за идентификација на најактивните корисници.

Функција 8 : get_revenue_by_period

CREATE OR REPLACE FUNCTION get_revenue_by_period(
    p_start_date DATE,
    p_end_date DATE
)
RETURNS TEXT AS $$
DECLARE
    v_total_tickets INT;
    v_total_revenue NUMERIC;
BEGIN
    SELECT
        COUNT(*),
        COALESCE(SUM(t.price), 0)
    INTO
        v_total_tickets,
        v_total_revenue
    FROM Ticket t
    JOIN "Train Trip" tt
        ON t."Train Triptrip_id" = tt.trip_id
    WHERE tt.departure_time::DATE
          BETWEEN p_start_date AND p_end_date;

    RETURN
        'Period: ' || p_start_date || ' to ' || p_end_date ||
        ' | Tickets Sold: ' || v_total_tickets ||
        ' | Total Revenue: ' || ROUND(v_total_revenue, 2);
END;
$$ LANGUAGE plpgsql;

SELECT get_revenue_by_period('2025-01-01', '2026-03-31');

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