| Version 3 (modified by , 11 days ago) ( diff ) |
|---|
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;
-- Провери дали корисникот веќе оставил 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; $$; 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. Ја имплементира бизнис логиката за автоматско завршување на резервации чија проекција веќе поминала.
