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

Context Navigation

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

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

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

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

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

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 = '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, 'Многу добар филм!');

Last modified 12 hours ago Last modified on 05/25/26 16:36:16

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text