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

Процедури

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

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

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

Тригер 2 : prevent_duplicate_seat_booking

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

Тригер 3 : update_trip_status_based_on_delay

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

Тригер 4 : auto_set_transaction_date

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

Тригер 5 : prevent_employee_trip_overlap

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

Тригер 6 : check_and_mark_overbooked

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

Тригер 7 : prevent_station_delete_active_trip

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

Функции

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

functions.sql​

Функција 1 : free_seats_trip

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

Функција 1 : free_seats_trip

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

Функција 2 : get_route_stations

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

Функција 3 : get_next_departing_trains

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

Функција 4 : get_passenger_total_spending

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

Функција 5 : get_trip_revenue_efficiency

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

Функција 6 : get_passenger_name_by_id

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

Функција 6 : get_most_profitable_route

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