Changes between Initial Version and Version 1 of DatabaseProgramming

Timestamp:

05/26/26 22:16:26 (16 hours ago)

Author:

231203

Comment:

--

DatabaseProgramming

@@ +1 @@
+= Procedures =
+
+== create_parking_reservation ==
+
+{{{
+CREATE OR REPLACE PROCEDURE public.create_parking_reservation(
+    IN p_user_id integer,
+    IN p_vehicle_id integer,
+    IN p_parking_id integer,
+    IN p_reservation_status_id integer,
+    IN p_start_time timestamp without time zone,
+    IN p_end_time timestamp without time zone
+)
+LANGUAGE plpgsql
+AS $procedure$
+BEGIN
+    IF NOT EXISTS (SELECT 1 FROM app_user WHERE user_id = p_user_id) THEN
+        RAISE EXCEPTION 'User does not exist.';
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1 FROM vehicle
+        WHERE vehicle_id = p_vehicle_id AND user_id = p_user_id
+    ) THEN
+        RAISE EXCEPTION 'Vehicle does not exist or does not belong to this user.';
+    END IF;
+
+    IF NOT EXISTS (SELECT 1 FROM parking WHERE parking_id = p_parking_id) THEN
+        RAISE EXCEPTION 'Parking does not exist.';
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1 FROM parking
+        WHERE parking_id = p_parking_id AND supports_reservation = true
+    ) THEN
+        RAISE EXCEPTION 'Parking does not support reservations.';
+    END IF;
+
+    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 parking_spot
+        WHERE parking_id = p_parking_id AND status = 'free'
+    ) THEN
+        RAISE EXCEPTION 'No free parking spots available.';
+    END IF;
+
+    INSERT INTO reservation (
+        user_id,
+        vehicle_id,
+        parking_id,
+        reservation_status_id,
+        start_time,
+        end_time,
+        reservation_code
+    )
+    VALUES (
+        p_user_id,
+        p_vehicle_id,
+        p_parking_id,
+        p_reservation_status_id,
+        p_start_time,
+        p_end_time,
+        'RES-' || p_user_id || '-' || EXTRACT(EPOCH FROM NOW())::INT
+    );
+
+    UPDATE parking_spot
+    SET status = 'reserved'
+    WHERE parking_spot_id = (
+        SELECT parking_spot_id
+        FROM parking_spot
+        WHERE parking_id = p_parking_id AND status = 'free'
+        LIMIT 1
+    );
+END;
+$procedure$;
+}}}
+
+Оваа процедура креира нова паркинг резервација само доколку се исполнети сите потребни услови, како постоење на корисник, возило, паркинг и слободно место.
+Ја имплементира бизнис логиката за управување со резервации и автоматски резервира слободно паркинг место.
+
+== cancel_reservation ==
+
+{{{
+CREATE OR REPLACE PROCEDURE public.cancel_reservation(
+    IN p_reservation_id integer
+)
+LANGUAGE plpgsql
+AS $procedure$
+BEGIN
+    IF NOT EXISTS (
+        SELECT 1 FROM reservation
+        WHERE reservation_id = p_reservation_id
+    ) THEN
+        RAISE EXCEPTION 'Reservation does not exist.';
+    END IF;
+
+    IF EXISTS (
+        SELECT 1 FROM reservation
+        WHERE reservation_id = p_reservation_id
+          AND reservation_status = 'cancelled'
+    ) THEN
+        RAISE EXCEPTION 'Reservation is already cancelled.';
+    END IF;
+
+    IF EXISTS (
+        SELECT 1 FROM reservation
+        WHERE reservation_id = p_reservation_id
+          AND reservation_status = 'completed'
+    ) THEN
+        RAISE EXCEPTION 'Completed reservation cannot be cancelled.';
+    END IF;
+
+    UPDATE reservation
+    SET reservation_status = 'cancelled'
+    WHERE reservation_id = p_reservation_id;
+END;
+$procedure$;
+}}}
+
+Оваа процедура овозможува откажување на постоечка резервација, при што спречува откажување на веќе завршени или претходно откажани резервации.
+Ја имплементира бизнис логиката за контрола и правилно управување со статусите на резервациите.
+
+== complete_reservation ==
+
+{{{
+CREATE OR REPLACE PROCEDURE public.complete_reservation(
+    IN p_reservation_id integer
+)
+LANGUAGE plpgsql
+AS $procedure$
+BEGIN
+    IF NOT EXISTS (
+        SELECT 1 FROM reservation
+        WHERE reservation_id = p_reservation_id
+    ) THEN
+        RAISE EXCEPTION 'Reservation does not exist.';
+    END IF;
+
+    IF EXISTS (
+        SELECT 1 FROM reservation
+        WHERE reservation_id = p_reservation_id
+          AND reservation_status = 'completed'
+    ) THEN
+        RAISE EXCEPTION 'Reservation is already completed.';
+    END IF;
+
+    IF EXISTS (
+        SELECT 1 FROM reservation
+        WHERE reservation_id = p_reservation_id
+          AND reservation_status = 'cancelled'
+    ) THEN
+        RAISE EXCEPTION 'Cancelled reservation cannot be completed.';
+    END IF;
+
+    UPDATE reservation
+    SET reservation_status = 'completed'
+    WHERE reservation_id = p_reservation_id;
+END;
+$procedure$;
+}}}
+
+Оваа процедура ја означува резервацијата како завршена, при што врши проверки за валидност на тековниот статус.
+Ја имплементира бизнис логиката за правилно завршување на процесот на резервација и спречување нелогични промени.
+
+== end_parking_session ==
+
+{{{
+CREATE OR REPLACE PROCEDURE public.end_parking_session(
+    IN p_session_id integer,
+    IN p_session_status_id integer
+)
+LANGUAGE plpgsql
+AS $procedure$
+BEGIN
+    IF NOT EXISTS (SELECT 1 FROM parking_session WHERE session_id = p_session_id) THEN
+        RAISE EXCEPTION 'Parking session does not exist.';
+    END IF;
+
+    IF EXISTS (
+        SELECT 1 FROM parking_session
+        WHERE session_id = p_session_id AND end_time IS NOT NULL
+    ) THEN
+        RAISE EXCEPTION 'Parking session is already finished.';
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1 FROM session_status
+        WHERE session_status_id = p_session_status_id
+    ) THEN
+        RAISE EXCEPTION 'Session status does not exist.';
+    END IF;
+
+    UPDATE parking_session
+    SET end_time = NOW(),
+        session_status_id = p_session_status_id
+    WHERE session_id = p_session_id;
+END;
+$procedure$;
+}}}
+
+Оваа процедура ја завршува активната паркинг сесија со поставување на крајно време и ажурирање на нејзиниот статус.
+Ја имплементира бизнис логиката за правилно затворање на паркинг сесиите и спречување повторно завршување на веќе затворени сесии.
+
+== fill_total_amount ==
+
+{{{
+CREATE OR REPLACE PROCEDURE public.fill_total_amount(
+    IN p_session_id integer
+)
+LANGUAGE plpgsql
+AS $procedure$
+BEGIN
+    IF NOT EXISTS (SELECT 1 FROM parking_session WHERE session_id = p_session_id) THEN
+        RAISE EXCEPTION 'Parking session does not exist.';
+    END IF;
+
+    IF EXISTS (
+        SELECT 1 FROM parking_session
+        WHERE session_id = p_session_id AND end_time IS NULL
+    ) THEN
+        RAISE EXCEPTION 'Parking session is still active.';
+    END IF;
+
+    UPDATE parking_session
+    SET total_amount = calculate_session_amount(p_session_id)
+    WHERE session_id = p_session_id;
+END;
+$procedure$;
+}}}
+
+Оваа процедура го пресметува и ажурира вкупниот износ за завршена паркинг сесија користејќи функција за пресметка на цена.
+Ја имплементира бизнис логиката за автоматска наплата според времетраењето и тарифата на паркирањето.
+
+== start_parking_session ==
+
+{{{
+CREATE OR REPLACE PROCEDURE public.start_parking_session(
+    IN p_user_id integer,
+    IN p_vehicle_id integer,
+    IN p_parking_id integer,
+    IN p_tariff_id integer,
+    IN p_session_status_id integer
+)
+LANGUAGE plpgsql
+AS $procedure$
+BEGIN
+    IF NOT EXISTS (SELECT 1 FROM app_user WHERE user_id = p_user_id) THEN
+        RAISE EXCEPTION 'User does not exist.';
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1 FROM vehicle
+        WHERE vehicle_id = p_vehicle_id AND user_id = p_user_id
+    ) THEN
+        RAISE EXCEPTION 'Vehicle does not exist or does not belong to this user.';
+    END IF;
+
+    IF NOT EXISTS (SELECT 1 FROM parking WHERE parking_id = p_parking_id) THEN
+        RAISE EXCEPTION 'Parking does not exist.';
+    END IF;
+
+    IF NOT EXISTS (SELECT 1 FROM tariff WHERE tariff_id = p_tariff_id) THEN
+        RAISE EXCEPTION 'Tariff does not exist.';
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1 FROM session_status WHERE session_status_id = p_session_status_id
+    ) THEN
+        RAISE EXCEPTION 'Session status does not exist.';
+    END IF;
+
+    IF has_active_session(p_user_id) THEN
+        RAISE EXCEPTION 'User already has an active parking session.';
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1 FROM parking_spot
+        WHERE parking_id = p_parking_id AND status = 'free'
+    ) THEN
+        RAISE EXCEPTION 'No free parking spots available.';
+    END IF;
+
+    INSERT INTO parking_session (
+        user_id,
+        vehicle_id,
+        parking_id,
+        tariff_id,
+        session_status_id,
+        start_time,
+        end_time,
+        total_amount
+    )
+    VALUES (
+        p_user_id,
+        p_vehicle_id,
+        p_parking_id,
+        p_tariff_id,
+        p_session_status_id,
+        NOW(),
+        NULL,
+        0.00
+    );
+
+    UPDATE parking_spot
+    SET status = 'occupied'
+    WHERE parking_spot_id = (
+        SELECT parking_spot_id
+        FROM parking_spot
+        WHERE parking_id = p_parking_id AND status = 'free'
+        LIMIT 1
+    );
+END;
+$procedure$;
+}}}
+
+Оваа процедура започнува нова паркинг сесија доколку корисникот ги исполнува сите услови и има достапно слободно место.
+Ја имплементира бизнис логиката за иницирање на паркирање, контрола на активни сесии и автоматско зафаќање на паркинг место.
+
+= Functions =
+
+== calculate_session_amount ==
+
+{{{
+CREATE OR REPLACE FUNCTION public.calculate_session_amount(p_session_id integer)
+RETURNS numeric
+LANGUAGE plpgsql
+AS $function$
+DECLARE
+    v_start TIMESTAMP;
+    v_end TIMESTAMP;
+    v_tariff_id INT;
+    v_price NUMERIC;
+    v_hours NUMERIC;
+BEGIN
+    SELECT start_time, end_time, tariff_id
+    INTO v_start, v_end, v_tariff_id
+    FROM parking_session
+    WHERE session_id = p_session_id;
+
+    IF NOT FOUND THEN
+        RAISE EXCEPTION 'Parking session does not exist.';
+    END IF;
+
+    IF v_end IS NULL THEN
+        RETURN 0.00;
+    END IF;
+
+    v_hours := CEIL(EXTRACT(EPOCH FROM (v_end - v_start)) / 3600.0);
+
+    SELECT price
+    INTO v_price
+    FROM price_list
+    WHERE tariff_id = v_tariff_id
+      AND CURRENT_DATE BETWEEN valid_from AND valid_to
+    ORDER BY valid_from DESC
+    LIMIT 1;
+
+    IF NOT FOUND THEN
+        RAISE EXCEPTION 'Valid price for tariff does not exist.';
+    END IF;
+
+    RETURN ROUND(v_hours * v_price, 2);
+END;
+$function$;
+}}}
+
+Оваа функција ја пресметува вкупната цена за дадена паркинг сесија според времетраењето и важечката тарифа.
+Ја имплементира бизнис логиката за автоматска пресметка на наплата и се користи при ажурирање на вкупниот износ за завршена сесија.
+
+== has_active_session ==
+
+{{{
+CREATE OR REPLACE FUNCTION public.has_active_session(p_user_id integer)
+RETURNS boolean
+LANGUAGE plpgsql
+AS $function$
+DECLARE
+    v_exists BOOLEAN;
+BEGIN
+    IF NOT EXISTS (
+        SELECT 1
+        FROM app_user
+        WHERE user_id = p_user_id
+    ) THEN
+        RAISE EXCEPTION 'User does not exist.';
+    END IF;
+
+    SELECT EXISTS (
+        SELECT 1
+        FROM parking_session
+        WHERE user_id = p_user_id
+          AND end_time IS NULL
+    )
+    INTO v_exists;
+
+    RETURN v_exists;
+END;
+$function$;
+}}}
+
+Оваа функција проверува дали даден корисник има активна паркинг сесија, односно сесија која сè уште нема крајно време.
+Ја имплементира бизнис логиката за спречување на повеќе активни сесии кај ист корисник и се користи при започнување нова паркинг сесија.
+
+== trg_set_total_amount ==
+
+{{{
+CREATE OR REPLACE FUNCTION public.trg_set_total_amount()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $function$
+DECLARE
+    v_price NUMERIC;
+    v_hours NUMERIC;
+BEGIN
+    IF NEW.end_time IS NULL THEN
+        NEW.total_amount := 0.00;
+    ELSE
+        IF NEW.start_time IS NULL THEN
+            RAISE EXCEPTION 'Start time cannot be null.';
+        END IF;
+
+        IF NEW.end_time <= NEW.start_time THEN
+            RAISE EXCEPTION 'End time must be after start time.';
+        END IF;
+
+        SELECT price
+        INTO v_price
+        FROM price_list
+        WHERE tariff_id = NEW.tariff_id
+          AND CURRENT_DATE BETWEEN valid_from AND valid_to
+        ORDER BY valid_from DESC
+        LIMIT 1;
+
+        IF NOT FOUND THEN
+            RAISE EXCEPTION 'Valid price for tariff does not exist.';
+        END IF;
+
+        v_hours := CEIL(EXTRACT(EPOCH FROM (NEW.end_time - NEW.start_time)) / 3600.0);
+
+        NEW.total_amount := ROUND(v_hours * v_price, 2);
+    END IF;
+
+    RETURN NEW;
+END;
+$function$;
+}}}
+
+Оваа trigger функција автоматски го поставува или пресметува вкупниот износ на паркинг сесијата при внесување или ажурирање на податоци.
+Ја имплементира бизнис логиката за автоматска наплата според времетраењето на сесијата и важечката тарифа.
+
+= Triggers =
+
+== trg_update_total_amount ==
+
+{{{
+CREATE TRIGGER trg_update_total_amount
+BEFORE INSERT OR UPDATE OF end_time
+ON public.parking_session
+FOR EACH ROW
+EXECUTE FUNCTION trg_set_total_amount();
+}}}
+
+Овој trigger автоматски се активира при креирање нова паркинг сесија или при промена на крајното време на постоечка сесија.
+Ја имплементира бизнис логиката за автоматска пресметка на вкупниот износ за паркирање без потреба од рачна интервенција, преку повикување на trigger функцијата trg_set_total_amount().