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;
get_reservation_total_with_discount
Ја пресметува вкупната цена на сите тикети за одредена резервација со применет попуст од промоција. Го зема збирот на цените на тикетите и го намалува според процентот на попуст. Се користи при финализирање на резервација кога корисникот применува промотивен код.
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;
get_available_seats
Го враќа бројот на слободни места за одредена проекција. Го пресметува капацитетот на салата намален за бројот на веќе продадени тикети. Се користи при прикажување на достапност на места пред купување тикет.
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;
get_movie_avg_rating
Го пресметува просечниот рејтинг на филм врз основа на сите рецензии. Враќа заокружена вредност на 2 децимали. Се користи при прикажување на оценката на филмот на корисниците.
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; $$; Процедури
generate_tickets_for_screening
Автоматски генерира тикети за секое седиште во салата за одредена проекција. Содржи валидации за постоење на проекцијата, датумот, цената, салата и седиштата. Се користи од администраторот при додавање нова проекција во системот.
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; $$;
purchase_ticket_with_products
Овозможува купување на тикет заедно со производи (пр. пуканки, пијалоци) во една трансакција. Содржи валидации за тикетот, корисникот, резервацијата и производите, и прави ROLLBACK при грешка. Се користи при процесот на купување тикет од страна на корисникот.
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;
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;
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; $$;
add_movie_review
Овозможува корисник да остави рецензија и рејтинг за филм само ако купил тикет за истиот. Содржи валидации за постоење на корисникот, филмот, рејтингот (1-10) и дали веќе оставил рецензија. Се користи при оставање feedback по гледање на филмот.
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();
trg_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();
trg_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 OR REPLACE TRIGGER trg_complete_past_reservations AFTER INSERT ON SCREENING FOR EACH ROW EXECUTE FUNCTION complete_past_reservations();
trg_complete_past_reservations
По внесување на нова проекција, автоматски ги означува минатите резервации со статус COMPLETED. Ја имплементира бизнис логиката за автоматско завршување на резервации чија проекција веќе поминала.
