Changes between Initial Version and Version 1 of функции,процедури,тригери

Timestamp:

05/25/26 16:36:16 (13 hours ago)

Author:

231233

Comment:

--

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

@@ +1 @@
+--------------Функции------------------------------
+---------- Вкупна цена со попуст---------
+CREATE OR REPLACE FUNCTION get_reservation_total_with_discount(
+    p_reservation_id INT,
+    p_promotion_id INT
+)
+RETURNS FLOAT AS $$
+DECLARE
+    v_total FLOAT;
+    v_discount FLOAT;
+    v_final FLOAT;
+BEGIN
+    -- Вкупна цена на сите тикети за резервацијата
+    SELECT SUM(price)
+    INTO v_total
+    FROM TICKET
+    WHERE reservation_id = p_reservation_id;
+
+    -- Попустот од промоцијата (во проценти)
+    SELECT discount
+    INTO v_discount
+    FROM PROMOTION
+    WHERE promotion_id = p_promotion_id;
+
+    -- Пресметај крајна цена
+    v_final := v_total - (v_total * v_discount / 100);
+
+    RETURN v_final;
+END;
+$$ LANGUAGE plpgsql;
+-----------------Слободни места-------------
+CREATE OR REPLACE FUNCTION get_available_seats(
+    p_screening_id INT
+)
+RETURNS INT AS $$
+DECLARE
+    v_capacity INT;
+    v_sold INT;
+BEGIN
+    -- Капацитет на салата
+    SELECT ch.capacity
+    INTO v_capacity
+    FROM SCREENING s
+    JOIN CINEMA_HALL ch ON ch.cinemahall_id = s.cinemahall_id
+    WHERE s.screening_id = p_screening_id;
+
+    -- Продадени тикети
+    SELECT COUNT(*)
+    INTO v_sold
+    FROM TICKET
+    WHERE screening_id = p_screening_id;
+
+    RETURN v_capacity - v_sold;
+END;
+$$ LANGUAGE plpgsql;
+-------------------Просечен рејтинг--------------
+CREATE OR REPLACE FUNCTION get_movie_avg_rating(
+    p_movie_id INT
+)
+RETURNS FLOAT AS $$
+DECLARE
+    v_avg FLOAT;
+BEGIN
+    SELECT ROUND(AVG(rating)::numeric, 2)
+    INTO v_avg
+    FROM REVIEW
+    WHERE movie_id = p_movie_id;
+
+    RETURN v_avg;
+END;
+$$ LANGUAGE plpgsql;
+----------------Процедури--------------------
+----------------Генерирање тикети за проекција  — ------------
+CREATE OR REPLACE PROCEDURE generate_tickets_for_screening(
+    p_screening_id INT,
+    p_price FLOAT
+)
+LANGUAGE plpgsql AS $$
+DECLARE
+    v_cinemahall_id INT;
+    v_cinema_id INT;
+    v_seat RECORD;
+    v_count INT := 0;
+    v_screening_date DATE;
+BEGIN
+    -- Провери дали проекцијата постои
+    IF NOT EXISTS (SELECT 1 FROM SCREENING WHERE screening_id = p_screening_id) THEN
+        RAISE EXCEPTION 'Проекцијата со ID % не постои!', p_screening_id;
+    END IF;
+
+    -- Провери дали датумот на проекцијата не е во минатото
+    SELECT date INTO v_screening_date FROM SCREENING WHERE screening_id = p_screening_id;
+    IF v_screening_date < CURRENT_DATE THEN
+        RAISE EXCEPTION 'Не може да се генерираат тикети за минати проекции! Датум: %', v_screening_date;
+    END IF;
+
+    -- Провери дали цената е валидна
+    IF p_price <= 0 THEN
+        RAISE EXCEPTION 'Цената мора да биде поголема од 0!';
+    END IF;
+
+    -- Земи ја салата
+    SELECT cinemahall_id INTO v_cinemahall_id
+    FROM SCREENING WHERE screening_id = p_screening_id;
+
+    -- Провери дали салата постои
+    IF NOT EXISTS (SELECT 1 FROM CINEMA_HALL WHERE cinemahall_id = v_cinemahall_id) THEN
+        RAISE EXCEPTION 'Салата со ID % не постои!', v_cinemahall_id;
+    END IF;
+
+    -- Провери дали салата е активна
+    IF NOT EXISTS (SELECT 1 FROM CINEMA_HALL WHERE cinemahall_id = v_cinemahall_id AND status = 'ACTIVE') THEN
+        RAISE EXCEPTION 'Салата со ID % не е активна!', v_cinemahall_id;
+    END IF;
+
+    -- Провери дали киното постои
+    SELECT cinema_id INTO v_cinema_id FROM CINEMA_HALL WHERE cinemahall_id = v_cinemahall_id;
+    IF NOT EXISTS (SELECT 1 FROM CINEMA WHERE cinema_id = v_cinema_id) THEN
+        RAISE EXCEPTION 'Киното со ID % не постои!', v_cinema_id;
+    END IF;
+
+    -- Провери дали воопшто има седишта во салата
+    IF NOT EXISTS (SELECT 1 FROM SEAT WHERE cinemahall_id = v_cinemahall_id) THEN
+        RAISE EXCEPTION 'Салата со ID % нема седишта!', v_cinemahall_id;
+    END IF;
+
+    -- За секое седиште во салата креирај тикет
+    FOR v_seat IN
+        SELECT seat_id FROM SEAT WHERE cinemahall_id = v_cinemahall_id
+    LOOP
+        IF NOT EXISTS (
+            SELECT 1 FROM TICKET
+            WHERE seat_id = v_seat.seat_id
+            AND screening_id = p_screening_id
+        ) THEN
+            INSERT INTO TICKET (price, user_id, reservation_id, seat_id, screening_id)
+            VALUES (p_price, NULL, NULL, v_seat.seat_id, p_screening_id);
+            v_count := v_count + 1;
+        END IF;
+    END LOOP;
+
+    IF v_count = 0 THEN
+        RAISE NOTICE 'Сите тикети за проекција % веќе се генерирани!', p_screening_id;
+    ELSE
+        RAISE NOTICE 'Генерирани % тикети за проекција % во сала % по цена %',
+            v_count, p_screening_id, v_cinemahall_id, p_price;
+    END IF;
+END;
+$$;
+-------------------------Процедура 2 — Купување тикет со производи-----------------
+CREATE OR REPLACE PROCEDURE purchase_ticket_with_products(
+    p_ticket_id INT,
+    p_user_id VARCHAR(13),
+    p_reservation_id INT,
+    p_products INT[]
+)
+LANGUAGE plpgsql AS $$
+DECLARE
+    v_product_id INT;
+    v_price FLOAT;
+    v_screening_id INT;
+    v_screening_date DATE;
+    v_total_price FLOAT := 0;
+BEGIN
+    -- Провери дали тикетот постои
+    IF NOT EXISTS (SELECT 1 FROM TICKET WHERE ticket_id = p_ticket_id) THEN
+        RAISE EXCEPTION 'Тикетот со ID % не постои!', p_ticket_id;
+    END IF;
+
+    -- Провери дали тикетот е слободен
+    IF EXISTS (SELECT 1 FROM TICKET WHERE ticket_id = p_ticket_id AND user_id IS NOT NULL) THEN
+        RAISE EXCEPTION 'Тикетот со ID % е веќе купен!', p_ticket_id;
+    END IF;
+
+    -- Провери дали корисникот постои
+    IF NOT EXISTS (SELECT 1 FROM "USER" WHERE user_id = p_user_id) THEN
+        RAISE EXCEPTION 'Корисникот % не постои!', p_user_id;
+    END IF;
+
+    -- Провери дали резервацијата постои
+    IF NOT EXISTS (SELECT 1 FROM RESERVATION WHERE reservation_id = p_reservation_id) THEN
+        RAISE EXCEPTION 'Резервацијата со ID % не постои!', p_reservation_id;
+    END IF;
+
+    -- Провери дали резервацијата не е откажана
+    IF EXISTS (SELECT 1 FROM RESERVATION WHERE reservation_id = p_reservation_id AND status = 'CANCELLED') THEN
+        RAISE EXCEPTION 'Резервацијата со ID % е откажана!', p_reservation_id;
+    END IF;
+
+    -- Провери дали проекцијата не е во минатото
+    SELECT screening_id INTO v_screening_id FROM TICKET WHERE ticket_id = p_ticket_id;
+    SELECT date INTO v_screening_date FROM SCREENING WHERE screening_id = v_screening_id;
+    IF v_screening_date < CURRENT_DATE THEN
+        RAISE EXCEPTION 'Не може да се купи тикет за минати проекции! Датум: %', v_screening_date;
+    END IF;
+
+    -- Провери дали производите постојат и пресметај вкупна цена
+    FOREACH v_product_id IN ARRAY p_products
+    LOOP
+        IF NOT EXISTS (SELECT 1 FROM PRODUCT WHERE product_id = v_product_id) THEN
+            RAISE EXCEPTION 'Производот со ID % не постои!', v_product_id;
+        END IF;
+        SELECT price INTO v_price FROM PRODUCT WHERE product_id = v_product_id;
+        v_total_price := v_total_price + v_price;
+    END LOOP;
+
+    -- Ажурирај го тикетот
+    UPDATE TICKET
+    SET user_id = p_user_id,
+        reservation_id = p_reservation_id
+    WHERE ticket_id = p_ticket_id;
+
+    -- Додај ги производите
+    FOREACH v_product_id IN ARRAY p_products
+    LOOP
+        INSERT INTO PURCHASED_PRODUCT (numbers, ticket_id, product_id)
+        VALUES (1, p_ticket_id, v_product_id);
+    END LOOP;
+
+    RAISE NOTICE 'Тикетот % е успешно купен! Корисник: %, Производи: %, Вкупна цена на производи: %',
+        p_ticket_id, p_user_id, array_length(p_products, 1), v_total_price;
+
+EXCEPTION
+    WHEN OTHERS THEN
+        RAISE NOTICE 'Грешка: % — се прави ROLLBACK!', SQLERRM;
+        ROLLBACK;
+        RETURN;
+END;
+$$;
+----------------------------Процедура 3 — Додај review за филм--------------------
+CREATE OR REPLACE PROCEDURE add_movie_review(
+    p_user_id VARCHAR(13),
+    p_movie_id INT,
+    p_rating INT,
+    p_comment VARCHAR(255)
+)
+LANGUAGE plpgsql AS $$
+DECLARE
+    v_existing_reviews INT;
+    v_avg_rating FLOAT;
+BEGIN
+    -- Провери дали корисникот постои
+    IF NOT EXISTS (SELECT 1 FROM "USER" WHERE user_id = p_user_id) THEN
+        RAISE EXCEPTION 'Корисникот % не постои!', p_user_id;
+    END IF;
+
+    -- Провери дали филмот постои
+    IF NOT EXISTS (SELECT 1 FROM MOVIE WHERE movie_id = p_movie_id) THEN
+        RAISE EXCEPTION 'Филмот со ID % не постои!', p_movie_id;
+    END IF;
+
+    -- Провери дали рејтингот е валиден
+    IF p_rating < 1 OR p_rating > 10 THEN
+        RAISE EXCEPTION 'Рејтингот мора да биде помеѓу 1 и 10! Внесен рејтинг: %', p_rating;
+    END IF;
+
+    -- Провери дали коментарот е празен
+    IF p_comment IS NULL OR TRIM(p_comment) = '' THEN
+        RAISE EXCEPTION 'Коментарот не може да биде празен!';
+    END IF;
+
+    -- Провери дали корисникот купил тикет за овој филм
+    IF NOT EXISTS (
+        SELECT 1 FROM TICKET t
+        JOIN SCREENING s ON s.screening_id = t.screening_id
+        WHERE t.user_id = p_user_id
+        AND s.movie_id = p_movie_id
+    ) THEN
+        RAISE EXCEPTION 'Корисникот % не купил тикет за филмот %!', p_user_id, p_movie_id;
+    END IF;
+
+    -- Провери дали корисникот веќе оставил review за овој филм
+    IF EXISTS (SELECT 1 FROM REVIEW WHERE user_id = p_user_id AND movie_id = p_movie_id) THEN
+        RAISE EXCEPTION 'Корисникот % веќе оставил review за филмот %!', p_user_id, p_movie_id;
+    END IF;
+
+    -- Додај review
+    INSERT INTO REVIEW (comment, date, user_id, movie_id, rating)
+    VALUES (p_comment, CURRENT_DATE, p_user_id, p_movie_id, p_rating);
+
+    -- Пресметај нов просечен рејтинг
+    SELECT COUNT(*), ROUND(AVG(rating)::numeric, 2)
+    INTO v_existing_reviews, v_avg_rating
+    FROM REVIEW WHERE movie_id = p_movie_id;
+
+    RAISE NOTICE 'Review за филм % додаден! Рејтинг: %, Вкупно reviews: %, Просечен рејтинг: %',
+        p_movie_id, p_rating, v_existing_reviews, v_avg_rating;
+END;
+$$;
+
+-----------------------Тригери------------------------------------
+----------------Тригер 1 — Спречи двојно резервирање на исто седиште-----------
+CREATE OR REPLACE FUNCTION check_seat_availability()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF EXISTS (
+        SELECT 1 FROM TICKET
+        WHERE seat_id = NEW.seat_id
+        AND screening_id = NEW.screening_id
+    ) THEN
+        RAISE EXCEPTION 'Седиштето % е веќе зафатено за оваа проекција!', NEW.seat_id;
+    END IF;
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE TRIGGER trg_check_seat_availability
+BEFORE INSERT ON TICKET
+FOR EACH ROW
+EXECUTE FUNCTION check_seat_availability();
+-----------------------Тригер 2 — Автоматска нотификација при резервација-----------
+    CREATE OR REPLACE FUNCTION notify_on_reservation()
+RETURNS TRIGGER AS $$
+BEGIN
+    INSERT INTO NOTIFICATION (message, date, user_id)
+    VALUES (
+        'Вашата резервација е успешно креирана со ID: ' || NEW.reservation_id,
+        CURRENT_DATE,
+        NEW.user_id
+    );
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE TRIGGER trg_notify_on_reservation
+AFTER INSERT ON USER_RESERVATION
+FOR EACH ROW
+EXECUTE FUNCTION notify_on_reservation();
+-----------------------Тригер 3 — Автоматски COMPLETED статус----------------------
+CREATE OR REPLACE FUNCTION complete_past_reservations()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF NEW.date < CURRENT_DATE THEN
+        UPDATE RESERVATION r
+        SET status = 'COMPLETED'
+        FROM TICKET t
+        WHERE t.reservation_id = r.reservation_id
+        AND t.screening_id = NEW.screening_id
+        AND r.status = 'CONFIRMED';
+    END IF;
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+CREATE EXTENSION IF NOT EXISTS postgis;
+
+
+
+-- Тест процедура 1 — генерирај тикети
+CALL generate_tickets_for_screening(55105026, 350.00);
+
+ALTER TABLE TICKET ALTER COLUMN user_id DROP NOT NULL;
+ALTER TABLE TICKET ALTER COLUMN reservation_id DROP NOT NULL;
+
+SELECT column_name, is_nullable
+FROM information_schema.columns
+WHERE table_name = 'ticket'
+AND column_name IN ('user_id', 'reservation_id');
+
+CALL generate_tickets_for_screening(55105026, 350.00);
+
+-- Земи слободен тикет од новата проекција
+SELECT ticket_id FROM TICKET
+WHERE screening_id = 55105026
+AND user_id IS NULL
+LIMIT 1;
+
+-- Земи вистински user_id
+SELECT user_id FROM "USER" LIMIT 1;
+
+-- Земи вистински reservation_id со статус PENDING
+SELECT reservation_id FROM RESERVATION
+WHERE status = 'PENDING'
+LIMIT 1;
+
+-- Земи вистински product_id
+SELECT product_id FROM PRODUCT LIMIT 3;
+
+CALL purchase_ticket_with_products(12818005, 'admin01', 4000005, ARRAY[1, 2, 3]);
+CALL add_movie_review('admin01', 195, 8, 'Одличен филм, препорачувам!');
+
+-- Земи филм за кој admin01 има тикет но нема review
+SELECT DISTINCT s.movie_id
+FROM TICKET t
+JOIN SCREENING s ON s.screening_id = t.screening_id
+WHERE t.user_id = 'admin01'
+AND s.movie_id NOT IN (SELECT movie_id FROM REVIEW WHERE user_id = 'admin01')
+LIMIT 1;
+
+SELECT DISTINCT s.movie_id
+FROM TICKET t
+JOIN SCREENING s ON s.screening_id = t.screening_id
+WHERE t.user_id = 'admin01'
+AND s.movie_id NOT IN (SELECT movie_id FROM REVIEW WHERE user_id = 'admin01')
+LIMIT 1;
+
+SELECT DISTINCT s.movie_id
+FROM TICKET t
+JOIN SCREENING s ON s.screening_id = t.screening_id
+WHERE t.user_id = 'admin01'
+AND s.movie_id NOT IN (SELECT movie_id FROM REVIEW WHERE user_id = 'admin01')
+LIMIT 1;
+SELECT user_id FROM "USER" WHERE user_id != 'admin01' LIMIT 1;
+
+SELECT DISTINCT s.movie_id
+FROM TICKET t
+JOIN SCREENING s ON s.screening_id = t.screening_id
+WHERE t.user_id = 'admin02'
+AND s.movie_id NOT IN (SELECT movie_id FROM REVIEW WHERE user_id = 'admin02')
+LIMIT 1;
+CALL add_movie_review('admin02', 195, 7, 'Многу добар филм!');