| Version 6 (modified by , 10 hours ago) ( diff ) |
|---|
Фаза 4 - Функции, процедури и тригери
Извештај
Извештајот за фаза 4 е достапен овде: Извештај
Функции
fn_slobodni_mesta(p_eventid) - Слободни места за настан
CREATE OR REPLACE FUNCTION fn_slobodni_mesta(p_eventid BIGINT)
RETURNS INT AS $$
DECLARE
v_kapacitet INT;
v_prodadeni INT;
BEGIN
SELECT SUM(h.capacity)
INTO v_kapacitet
FROM event_hall eh
JOIN hall h ON h.id = eh.hallid
WHERE eh.eventid = p_eventid;
SELECT COUNT(*)
INTO v_prodadeni
FROM ticket
WHERE eventid = p_eventid AND status = 'ACTIVE';
RETURN v_kapacitet - v_prodadeni;
END;
$$ LANGUAGE plpgsql;
SELECT fn_slobodni_mesta(100) AS slobodni_mesta;
-- Резултат: slobodni_mesta 736
Функцијата го пресметува бројот на слободни места за даден настан така што од вкупниот капацитет на сите сали го одзема бројот на активни тикети. Ја имплементира бизнис логиката за проверка на достапност пред купување на тикет и се користи во процедурата proc_kupi_tiket и proc_dodaj_vo_waitlist.
fn_prihod_po_nastan(p_eventid) - Вкупен приход по настан
CREATE OR REPLACE FUNCTION fn_prihod_po_nastan(p_eventid BIGINT)
RETURNS INT AS $$
DECLARE
v_prihod INT;
BEGIN
SELECT SUM(p.amount)
INTO v_prihod
FROM payment p
JOIN user_order uo ON uo.id = p.user_orderid
JOIN ticket t ON t.user_orderid = uo.id
WHERE t.eventid = p_eventid AND p.status = 'COMPLETED';
RETURN v_prihod;
END;
$$ LANGUAGE plpgsql;
SELECT fn_prihod_po_nastan(1) AS vkupen_prihod;
-- Резултат: 370300
Функцијата го пресметува вкупниот приход за даден настан преку сумирање на сите завршени плаќања поврзани со тикетите за тој настан. Ја имплементира бизнис логиката за финансиски извештај и се користи при генерирање на статистики за организаторите на настани.
fn_moze_review(p_userid, p_eventid) - Дозвола за оставање рецензија
CREATE OR REPLACE FUNCTION fn_moze_review(
p_userid BIGINT,
p_eventid BIGINT
)
RETURNS BOOLEAN AS $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM ticket
WHERE app_userid = p_userid
AND eventid = p_eventid
AND status IN ('USED')
) THEN
RETURN FALSE;
END IF;
IF EXISTS (
SELECT 1 FROM review
WHERE app_userid = p_userid
AND eventid = p_eventid
) THEN
RETURN FALSE;
END IF;
RETURN TRUE;
END;
$$ LANGUAGE plpgsql;
SELECT fn_moze_review(28547, 35) AS moze_review;
-- Резултат: true
Функцијата проверува дали корисникот смее да остави рецензија за даден настан — враќа TRUE само ако корисникот има искористен тикет за тој настан и сè уште нема оставено рецензија. Ја имплементира бизнис логиката за заштита од лажни рецензии и се користи во процедурата proc_ostavi_review и тригерот trig_review.
fn_statistika_recenzii(p_eventid) - Вкупно рецензии и просечен рејтинг
CREATE OR REPLACE FUNCTION fn_statistika_recenzii(p_eventid BIGINT)
RETURNS TABLE (
vkupno_recenzii BIGINT,
prosecen_rejting NUMERIC,
pet_zvezdi BIGINT,
cetiri_zvezdi BIGINT,
tri_zvezdi BIGINT,
dve_zvezdi BIGINT,
edna_zvezda BIGINT
) AS $$
BEGIN
RETURN QUERY
SELECT
COUNT(*) AS vkupno_recenzii,
ROUND(AVG(rating), 2) AS prosecen_rejting,
COUNT(*) FILTER (WHERE rating = 5) AS pet_zvezdi,
COUNT(*) FILTER (WHERE rating = 4) AS cetiri_zvezdi,
COUNT(*) FILTER (WHERE rating = 3) AS tri_zvezdi,
COUNT(*) FILTER (WHERE rating = 2) AS dve_zvezdi,
COUNT(*) FILTER (WHERE rating = 1) AS edna_zvezda
FROM review
WHERE eventid = p_eventid;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM fn_statistika_recenzii(33);
-- Резултат: 94 3.83 33 29 19 9 4
Функцијата враќа детална статистика за рецензиите на даден настан — вкупен број, просечен рејтинг и распределба по број на ѕвезди. Ја имплементира бизнис логиката за приказ на статистики на страницата на настанот и им овозможува на корисниците да донесат информирана одлука пред купување на тикет.
fn_pozicija_vo_waitlist(p_userid, p_eventid) - Реден број на чекање во waitlist
CREATE OR REPLACE FUNCTION fn_pozicija_vo_waitlist(
p_userid BIGINT,
p_eventid BIGINT
)
RETURNS INT AS $$
DECLARE
v_pozicija INT;
BEGIN
SELECT pozicija INTO v_pozicija
FROM (
SELECT
uw.app_userid,
ROW_NUMBER() OVER (ORDER BY w.created_at ASC) AS pozicija
FROM waitlist w
JOIN user_waitlist uw ON uw.waitlistid = w.id
WHERE w.eventid = p_eventid
AND w.status = 'PENDING'
) sub
WHERE app_userid = p_userid;
IF v_pozicija IS NULL THEN
RAISE EXCEPTION 'Korisnikot % ne e vo waitlist za event %', p_userid, p_eventid;
END IF;
RETURN v_pozicija;
END;
$$ LANGUAGE plpgsql;
SELECT fn_pozicija_vo_waitlist(2, 1) AS pozicija;
-- Резултат: pozicija 1
Функцијата го враќа редниот број на корисникот во листата на чекање за даден настан, подреден по времето на пријавување. Ја имплементира бизнис логиката за информирање на корисникот за неговата позиција во редот при пополнет настан.
fn_verifikacija_tiket(p_ticketid) - Верификација на тикет
CREATE OR REPLACE FUNCTION fn_verifikacija_tiket(p_ticketid BIGINT)
RETURNS BOOLEAN AS $$
DECLARE
v_status VARCHAR(20);
v_start_date DATE;
v_end_date DATE;
BEGIN
SELECT t.status, e.start_date, e.end_date
INTO v_status, v_start_date, v_end_date
FROM ticket t
JOIN event e ON e.id = t.eventid
WHERE t.id = p_ticketid;
IF v_status IS NULL THEN
RAISE EXCEPTION 'Tiketot % ne postoi', p_ticketid;
END IF;
IF v_status != 'ACTIVE' THEN
RETURN FALSE;
END IF;
IF CURRENT_DATE > v_end_date THEN
RETURN FALSE;
END IF;
RETURN TRUE;
END;
$$ LANGUAGE plpgsql;
SELECT fn_verifikacija_tiket(1) AS e_validen;
-- Резултат: false
Функцијата проверува дали тикетот е валиден за скенирање при влез на настан — враќа TRUE само ако тикетот е со статус ACTIVE и настанот сè уште не е завршен. Ја имплементира бизнис логиката за контрола на влез на настани.
Процедури
proc_najavi_korisnik(p_email, p_password) - Најава на корисник
CREATE OR REPLACE PROCEDURE proc_najavi_korisnik(
p_email VARCHAR(50),
p_password VARCHAR(255)
) LANGUAGE plpgsql AS $$
DECLARE
v_userid BIGINT;
v_role_name VARCHAR(20);
BEGIN
SELECT id INTO v_userid
FROM app_user
WHERE email = p_email
AND user_password = p_password
AND id != 1;
IF v_userid IS NULL THEN
RAISE EXCEPTION 'Pogreshen email ili lozinka';
END IF;
SELECT r.role_name INTO v_role_name
FROM user_roles ur
JOIN roles r ON r.id = ur.rolesid
WHERE ur.app_userid = v_userid
LIMIT 1;
RAISE NOTICE 'Korisnikot % se najavi kako %', p_email, v_role_name;
END;
$$;
SELECT email, user_password FROM app_user WHERE id != 1 LIMIT 1;
-- Gertie.Gold0@gmail.com GertieGold
CALL proc_najavi_korisnik('Gertie.Gold0@gmail.com', 'GertieGold');
-- Резултат: Korisnikot Gertie.Gold0@gmail.com se najavi kako USER
Процедурата ја верификува најавата на корисникот преку проверка на email и лозинка и ја враќа неговата улога во системот. Ја имплементира бизнис логиката за автентикација на корисници. Напомена: ова е поедноставена верзија — во продукциски систем лозинката би се споредувала со bcrypt хаш.
proc_kupi_tiket(p_userid, p_eventid, p_hallid, p_typeid, p_seatid, p_promo_codeid) - Купување тикет
CREATE OR REPLACE PROCEDURE proc_kupi_tiket(
p_userid BIGINT,
p_eventid BIGINT,
p_hallid BIGINT,
p_typeid BIGINT,
p_seatid BIGINT,
p_promo_codeid BIGINT DEFAULT NULL
) LANGUAGE plpgsql AS $$
DECLARE
v_orderid BIGINT;
v_price INT;
v_discount INT := 0;
v_final_price INT;
v_status_id BIGINT;
v_ticket_code VARCHAR(50);
BEGIN
SELECT price INTO v_price
FROM event_ticket_type
WHERE eventid = p_eventid AND ticket_typeid = p_typeid;
IF v_price IS NULL THEN
RAISE EXCEPTION 'Ne postoi ticket type % za event %', p_typeid, p_eventid;
END IF;
IF fn_slobodni_mesta(p_eventid) <= 0 THEN
RAISE EXCEPTION 'Nema slobodni mesta za event %', p_eventid;
END IF;
IF p_promo_codeid IS NOT NULL THEN
SELECT discount_percent INTO v_discount
FROM promo_code
WHERE id = p_promo_codeid AND expiration_date >= CURRENT_DATE;
IF v_discount IS NULL THEN
RAISE EXCEPTION 'Promo kodot ne e validen ili e istecen';
END IF;
END IF;
v_final_price := v_price - (v_price * v_discount / 100);
SELECT id INTO v_status_id
FROM status WHERE status_name = 'CREATED';
INSERT INTO user_order (order_date, total_amount, app_userid, statusid, promo_codeid)
VALUES (CURRENT_DATE, v_final_price, p_userid, v_status_id, p_promo_codeid)
RETURNING id INTO v_orderid;
v_ticket_code := 'TKT-' || v_orderid || '-' || extract(epoch FROM now())::BIGINT;
INSERT INTO ticket (code, status, ticket_typeid, user_orderid, seatid, app_userid, eventid, hallid)
VALUES (v_ticket_code, 'ACTIVE', p_typeid, v_orderid, p_seatid, p_userid, p_eventid, p_hallid);
RAISE NOTICE 'Tiketot e kreiran so kod: %', v_ticket_code;
END;
$$;
CALL proc_kupi_tiket(2, 1, 1, 1, 5, NULL);
-- Резултат: Tiketot e kreiran so kod: TKT-12500002-1778661805
Процедурата го имплементира целиот процес на купување тикет — проверува дали постои типот на тикет за тој настан, дали има слободни места, дали промо кодот е валиден, ја пресметува конечната цена со попуст и креира нарачка и тикет. Ја имплементира централната бизнис логика за продажба на тикети во апликацијата.
proc_plati_narachka(p_orderid, p_payment_methodid) - Плаќање на нарачка
CREATE OR REPLACE PROCEDURE proc_plati_narachka(
p_orderid BIGINT,
p_payment_methodid BIGINT
) LANGUAGE plpgsql AS $$
DECLARE
v_amount INT;
v_status_name VARCHAR(20);
v_paid_id BIGINT;
BEGIN
IF NOT EXISTS (SELECT 1 FROM user_order WHERE id = p_orderid) THEN
RAISE EXCEPTION 'Order % ne postoi', p_orderid;
END IF;
SELECT s.status_name INTO v_status_name
FROM user_order uo
JOIN status s ON s.id = uo.statusid
WHERE uo.id = p_orderid;
IF v_status_name = 'PAID' THEN
RAISE EXCEPTION 'Order % e vekje platen', p_orderid;
END IF;
IF v_status_name = 'CANCELLED' THEN
RAISE EXCEPTION 'Order % e otkazan i ne moze da se plati', p_orderid;
END IF;
SELECT total_amount INTO v_amount
FROM user_order WHERE id = p_orderid;
INSERT INTO payment (amount, status, payment_date, user_orderid, payment_methodid)
VALUES (v_amount, 'COMPLETED', CURRENT_DATE, p_orderid, p_payment_methodid);
SELECT id INTO v_paid_id FROM status WHERE status_name = 'PAID';
UPDATE user_order SET statusid = v_paid_id WHERE id = p_orderid;
INSERT INTO notification (message, created_at, app_userid)
SELECT 'Narachkata e uspeshno platena', CURRENT_DATE, app_userid
FROM user_order WHERE id = p_orderid;
RAISE NOTICE 'Order % e platen so iznos %', p_orderid, v_amount;
END;
$$;
SELECT uo.id
FROM user_order uo
JOIN status s ON s.id = uo.statusid
WHERE s.status_name = 'CREATED'
LIMIT 1;
CALL proc_plati_narachka(4, 1);
-- Резултат:
-- Order 4 e azuriran vo PAID
-- Order 4 e platеn so iznos 3000
Процедурата го обработува плаќањето на нарачка — проверува дали нарачката постои и дали не е веќе платена или откажана, креира запис за плаќање, го ажурира статусот на нарачката во PAID и праќа нотификација до корисникот. Ја имплементира бизнис логиката за финансиски трансакции во апликацијата.
proc_otkazi_narachka(p_orderid) - Откажување на нарачка
CREATE OR REPLACE PROCEDURE proc_otkazi_narachka(p_orderid BIGINT)
LANGUAGE plpgsql AS $$
DECLARE
v_paymentid BIGINT;
v_amount INT;
v_status_id BIGINT;
BEGIN
IF NOT EXISTS (SELECT 1 FROM user_order WHERE id = p_orderid) THEN
RAISE EXCEPTION 'Order % ne postoi', p_orderid;
END IF;
SELECT id INTO v_status_id
FROM status WHERE status_name = 'CANCELLED';
UPDATE user_order SET statusid = v_status_id WHERE id = p_orderid;
UPDATE ticket SET status = 'CANCELLED' WHERE user_orderid = p_orderid;
SELECT id, amount INTO v_paymentid, v_amount
FROM payment
WHERE user_orderid = p_orderid AND status = 'COMPLETED'
LIMIT 1;
IF v_paymentid IS NOT NULL THEN
INSERT INTO refund (amount, reason, status, paymentid)
VALUES (v_amount, 'Customer request', 'APPROVED', v_paymentid);
RAISE NOTICE 'Refund kreiran za order %', p_orderid;
END IF;
RAISE NOTICE 'Narachkata % e otkazana', p_orderid;
END;
$$;
-- Барање на order со COMPLETED payment
SELECT uo.id, count(t.id) AS tiketi
FROM user_order uo
JOIN payment p ON p.user_orderid = uo.id AND p.status = 'COMPLETED'
LEFT JOIN ticket t ON t.user_orderid = uo.id
GROUP BY uo.id
ORDER BY tiketi ASC
LIMIT 5;
CALL proc_otkazi_narachka(12500001);
Процедурата го обработува откажувањето на нарачка — ги откажува нарачката и сите нејзини тикети и доколку нарачката е претходно платена, автоматски креира барање за рефундирање. Ја имплементира бизнис логиката за откажување и рефундирање во апликацијата.
proc_dodaj_vo_waitlist(p_userid, p_eventid) - Додавање корисник на waitlist
CREATE OR REPLACE PROCEDURE proc_dodaj_vo_waitlist(
p_userid BIGINT,
p_eventid BIGINT
) LANGUAGE plpgsql AS $$
DECLARE
v_waitlist_id BIGINT;
BEGIN
IF NOT EXISTS (SELECT 1 FROM event WHERE id = p_eventid) THEN
RAISE EXCEPTION 'Nastanot % ne postoi', p_eventid;
END IF;
IF NOT EXISTS (SELECT 1 FROM app_user WHERE id = p_userid AND id != 1) THEN
RAISE EXCEPTION 'Korisnikot % ne postoi', p_userid;
END IF;
IF fn_slobodni_mesta(p_eventid) > 0 THEN
RAISE EXCEPTION 'Ima slobodni mesta za event %, kupi tiket namesto waitlist', p_eventid;
END IF;
IF EXISTS (
SELECT 1 FROM user_waitlist uw
JOIN waitlist w ON w.id = uw.waitlistid
WHERE uw.app_userid = p_userid
AND w.eventid = p_eventid
AND w.status = 'PENDING'
) THEN
RAISE EXCEPTION 'Korisnikot % vekje e vo waitlist za event %', p_userid, p_eventid;
END IF;
INSERT INTO waitlist (created_at, status, eventid)
VALUES (CURRENT_DATE, 'PENDING', p_eventid)
RETURNING id INTO v_waitlist_id;
INSERT INTO user_waitlist (app_userid, waitlistid)
VALUES (p_userid, v_waitlist_id);
INSERT INTO notification (message, created_at, app_userid)
VALUES ('You have been added to the waitlist.', CURRENT_DATE, p_userid);
RAISE NOTICE 'Korisnikot % e dodaden vo waitlist za event %', p_userid, p_eventid;
END;
$$;
CALL proc_dodaj_vo_waitlist(2, 1);
Процедурата го додава корисникот на листата на чекање за пополнет настан — проверува дали настанот и корисникот постојат, дали навистина нема слободни места, дали корисникот не е веќе на листата и по успешно додавање праќа нотификација. Ја имплементира бизнис логиката за управување со листата на чекање при пополнети настани.
proc_ostavi_review(p_userid, p_eventid, p_rating, p_comment) - Оставање на рецензија
CREATE OR REPLACE PROCEDURE proc_ostavi_review(
p_userid BIGINT,
p_eventid BIGINT,
p_rating INT,
p_comment VARCHAR(255) DEFAULT NULL
) LANGUAGE plpgsql AS $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM event WHERE id = p_eventid) THEN
RAISE EXCEPTION 'Nastanot % ne postoi', p_eventid;
END IF;
IF NOT EXISTS (SELECT 1 FROM event WHERE id = p_eventid AND end_date < CURRENT_DATE) THEN
RAISE EXCEPTION 'Ne moze da se ostavi review za nastan koj ne e zavrshen';
END IF;
IF NOT fn_moze_review(p_userid, p_eventid) THEN
RAISE EXCEPTION 'Korisnikot % ne smee da ostavi review za event %', p_userid, p_eventid;
END IF;
IF p_rating NOT BETWEEN 1 AND 5 THEN
RAISE EXCEPTION 'Ratingot mora da bide pomegu 1 i 5';
END IF;
INSERT INTO review (rating, review_comment, app_userid, eventid)
VALUES (p_rating, p_comment, p_userid, p_eventid);
RAISE NOTICE 'Review e ostaveno za event % od korisnik %', p_eventid, p_userid;
END;
$$;
SELECT app_userid, eventid
FROM ticket
WHERE status = 'USED'
LIMIT 1;
CALL proc_ostavi_review(21615, 49952, 5, 'Odlichno iskustvo!');
-- Резултат: Review e ostaveno za event 49952 od korisnik 21615
Процедурата го обработува оставањето рецензија за минат настан — проверува дали настанот постои и е завршен, го користи fn_moze_review за да провери дали корисникот смее да остави рецензија и дали рејтингот е во валиден опсег. Ја имплементира бизнис логиката за систем на рецензии во апликацијата.
Тригери
trig_waitlist - При откажан тикет --> confirm на следниот user од waitlist
CREATE OR REPLACE FUNCTION fn_trig_waitlist()
RETURNS TRIGGER AS $$
DECLARE
v_waitlist_id BIGINT;
BEGIN
IF NEW.status = 'CANCELLED' AND OLD.status != 'CANCELLED' THEN
SELECT w.id INTO v_waitlist_id
FROM waitlist w
WHERE w.eventid = NEW.eventid AND w.status = 'PENDING'
ORDER BY w.created_at
LIMIT 1;
IF v_waitlist_id IS NOT NULL THEN
UPDATE waitlist SET status = 'CONFIRMED'
WHERE id = v_waitlist_id;
RAISE NOTICE 'Waitlist % e potvrden za event %', v_waitlist_id, NEW.eventid;
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trig_waitlist
AFTER UPDATE ON ticket
FOR EACH ROW
EXECUTE FUNCTION fn_trig_waitlist();
UPDATE ticket SET status = 'CANCELLED' WHERE id = (
SELECT t.id FROM ticket t
JOIN waitlist w ON w.eventid = t.eventid
WHERE w.status = 'PENDING' LIMIT 1
);
Тригерот се активира автоматски по секое ажурирање на статусот на тикет во CANCELLED и го потврдува првиот корисник во редот на чекање за тој настан. Ја имплементира бизнис логиката за автоматско управување со листата на чекање при ослободување на место.
trig_refund - Заштита од двоен refund
CREATE OR REPLACE FUNCTION fn_trig_refund()
RETURNS TRIGGER AS $$
BEGIN
IF EXISTS (
SELECT 1 FROM refund
WHERE paymentid = NEW.paymentid
AND status IN ('REQUESTED', 'APPROVED')
) THEN
RAISE EXCEPTION 'Vekje postoi refund za payment %', NEW.paymentid;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trig_refund
BEFORE INSERT ON refund
FOR EACH ROW
EXECUTE FUNCTION fn_trig_refund();
SELECT paymentid FROM refund
WHERE status IN ('REQUESTED', 'APPROVED')
LIMIT 1;
INSERT INTO refund (amount, reason, status, paymentid)
VALUES (100, 'Test duplikat', 'REQUESTED', 1);
-- Резултат: Error: Vekje postoi refund za payment 1
Тригерот се активира пред секое вметнување во табелата REFUND и проверува дали за тоа плаќање веќе постои активно барање за рефундирање. Ја имплементира бизнис логиката за заштита од двојно рефундирање на исто плаќање.
trig_quantity - Ажурирање на quantity_available при купување и откажување
CREATE OR REPLACE FUNCTION fn_trig_quantity()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' AND NEW.status = 'ACTIVE' THEN
UPDATE event_ticket_type
SET quantity_available = quantity_available - 1
WHERE eventid = NEW.eventid AND ticket_typeid = NEW.ticket_typeid;
IF NOT FOUND THEN
RAISE EXCEPTION 'Ne postoi ticket type % za event %',
NEW.ticket_typeid, NEW.eventid;
END IF;
ELSIF TG_OP = 'UPDATE'
AND NEW.status = 'CANCELLED'
AND OLD.status != 'CANCELLED' THEN
UPDATE event_ticket_type
SET quantity_available = quantity_available + 1
WHERE eventid = NEW.eventid AND ticket_typeid = NEW.ticket_typeid;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trig_quantity
AFTER INSERT OR UPDATE ON ticket
FOR EACH ROW
EXECUTE FUNCTION fn_trig_quantity();
-- Pred insert: quantity_available = 692
INSERT INTO ticket (code, status, ticket_typeid, user_orderid, seatid, app_userid, eventid, hallid)
VALUES ('TKT-TEST-TRIG', 'ACTIVE', 1,
(SELECT MIN(id) FROM user_order), NULL,
(SELECT MIN(id) FROM app_user WHERE id != 1), 33,
(SELECT MIN(hallid) FROM event_hall WHERE eventid = 33));
-- Po insert: quantity_available = 691
UPDATE ticket SET status = 'CANCELLED' WHERE code = 'TKT-TEST-TRIG';
-- Po update: quantity_available = 692
Тригерот се активира по секое вметнување или ажурирање на тикет и автоматски го ажурира бројот на достапни тикети во табелата event_ticket_type — го намалува при продажба и го зголемува при откажување. Ја имплементира бизнис логиката за следење на достапноста на тикети во реално време.
Attachments (1)
- db-programming.sql (27.2 KB ) - added by 9 hours ago.
Download all attachments as: .zip
