Functions, Procedures, Triggers
Шемата kbnteam содржи три функции, тринаесет процедури и три тригери за договори, нарачки, достави, рецензии и лојалност. Функциите враќаат пресметки и проверки, процедурите се повикуваат со CALL, а тригерите автоматски се активираат при промени на податоците.
Функција 1: fn_has_active_contract
Враќа TRUE ако постои договор за зададената компанија и ресторан со статус active, а зададениот датум е меѓу почетниот и крајниот датум, вклучувајќи ги границите. Инаку враќа FALSE. Кога датумот не е зададен, се користи тековниот датум. Називот на статусот се споредува без разлика на големината на буквите.
SQL Дефиниција
CREATE OR REPLACE FUNCTION kbnteam.fn_has_active_contract(
p_company_id INTEGER,
p_rest_id INTEGER,
p_on_date DATE DEFAULT CURRENT_DATE
)
RETURNS BOOLEAN
LANGUAGE sql
STABLE
AS $$
SELECT EXISTS (
SELECT 1
FROM kbnteam.contract ct
JOIN kbnteam.contract_status cs
ON cs.contract_status_id = ct.contract_status_id
WHERE ct.company_id = p_company_id
AND ct.rest_id = p_rest_id
AND lower(cs.contract_status_name) = 'active'
AND p_on_date BETWEEN ct.contract_start_date AND ct.contract_end_date
);
$$;
Функција 2: fn_calculate_order_total
Ги собира тековните цени на јадењата и пијалоците поврзани со нарачката. Ако нема ставки, враќа 0.00. Не применува попуст за лојалност или дополнителен надомест за достава и не ја менува нарачката; запишувањето на пресметаниот износ го вршат процедурите и тригерите.
SQL Дефиниција
CREATE OR REPLACE FUNCTION kbnteam.fn_calculate_order_total(
p_order_id INTEGER
)
RETURNS NUMERIC(10,2)
LANGUAGE sql
STABLE
AS $$
SELECT (
COALESCE((
SELECT SUM(m.meal_price)
FROM kbnteam.order_meal om
JOIN kbnteam.meal m
ON m.meal_id = om.meal_id
WHERE om.order_id = p_order_id
), 0)
+
COALESCE((
SELECT SUM(d.drink_price)
FROM kbnteam.order_drink od
JOIN kbnteam.drink d
ON d.drink_id = od.drink_id
WHERE od.order_id = p_order_id
), 0)
)::NUMERIC(10,2);
$$;
Функција 3: fn_calculate_customer_loyalty_points
Ги собира износите на нарачките на клиентот, го заокружува збирот надолу и враќа цел број. Нарачките со статус cancelled придонесуваат со нула. Останатите статуси, вклучително pending и refunded, се вклучени. Ако нема нарачки, враќа нула. Функцијата ги пресметува поените, а нивното запишување го врши процедурата за освежување на лојалноста.
SQL Дефиниција
CREATE OR REPLACE FUNCTION kbnteam.fn_calculate_customer_loyalty_points(
p_customer_user_id INTEGER
)
RETURNS INTEGER
LANGUAGE sql
STABLE
AS $$
SELECT COALESCE(
FLOOR(SUM(
CASE
WHEN lower(os.o_status_name) = 'cancelled' THEN 0
ELSE o.order_total
END
)),
0
)::INTEGER
FROM kbnteam.customer_order o
JOIN kbnteam.order_status os
ON os.o_status_id = o.o_status_id
WHERE o.customer_user_id = p_customer_user_id;
$$;
Процедура 1: pr_activate_or_renew_contract
Проверува дали компанијата и ресторанот постојат и дали периодот е валиден. Кога постои активен договор со период што се преклопува или е непосредно соседен, го проширува неговиот период. Ако нема таков договор, создава нов со статус active. Обновувањето се одбива ако проширениот период се преклопува со друг активен договор.
SQL Дефиниција
CREATE OR REPLACE PROCEDURE kbnteam.pr_activate_or_renew_contract(
p_company_id INTEGER,
p_rest_id INTEGER,
p_start_date DATE,
p_end_date DATE
)
LANGUAGE plpgsql
AS $$
DECLARE
v_active_status_id INTEGER;
v_contract_id INTEGER;
v_existing_start DATE;
v_existing_end DATE;
v_merged_start DATE;
v_merged_end DATE;
BEGIN
IF p_start_date IS NULL OR p_end_date IS NULL THEN
RAISE EXCEPTION 'Contract start and end dates are required.';
END IF;
IF p_end_date < p_start_date THEN
RAISE EXCEPTION
'Contract end date % cannot be before start date %.',
p_end_date,
p_start_date;
END IF;
IF NOT EXISTS (
SELECT 1
FROM kbnteam.company c
WHERE c.company_id = p_company_id
) THEN
RAISE EXCEPTION 'Company % does not exist.', p_company_id;
END IF;
IF NOT EXISTS (
SELECT 1
FROM kbnteam.restaurant r
WHERE r.rest_id = p_rest_id
) THEN
RAISE EXCEPTION 'Restaurant % does not exist.', p_rest_id;
END IF;
SELECT cs.contract_status_id
INTO v_active_status_id
FROM kbnteam.contract_status cs
WHERE lower(cs.contract_status_name) = 'active';
IF v_active_status_id IS NULL THEN
RAISE EXCEPTION 'The Active contract status does not exist.';
END IF;
SELECT
ct.contract_id,
ct.contract_start_date,
ct.contract_end_date
INTO
v_contract_id,
v_existing_start,
v_existing_end
FROM kbnteam.contract ct
WHERE ct.company_id = p_company_id
AND ct.rest_id = p_rest_id
AND ct.contract_status_id = v_active_status_id
AND p_start_date <= ct.contract_end_date + 1
AND p_end_date >= ct.contract_start_date - 1
ORDER BY ct.contract_end_date DESC
LIMIT 1
FOR UPDATE;
IF FOUND THEN
v_merged_start := LEAST(v_existing_start, p_start_date);
v_merged_end := GREATEST(v_existing_end, p_end_date);
IF EXISTS (
SELECT 1
FROM kbnteam.contract ct
WHERE ct.company_id = p_company_id
AND ct.rest_id = p_rest_id
AND ct.contract_status_id = v_active_status_id
AND ct.contract_id <> v_contract_id
AND v_merged_start <= ct.contract_end_date
AND v_merged_end >= ct.contract_start_date
) THEN
RAISE EXCEPTION
'Renewing contract % would overlap another active contract.',
v_contract_id;
END IF;
UPDATE kbnteam.contract ct
SET contract_start_date = v_merged_start,
contract_end_date = v_merged_end,
contract_status_id = v_active_status_id
WHERE ct.contract_id = v_contract_id;
ELSE
IF EXISTS (
SELECT 1
FROM kbnteam.contract ct
WHERE ct.company_id = p_company_id
AND ct.rest_id = p_rest_id
AND ct.contract_status_id = v_active_status_id
AND p_start_date <= ct.contract_end_date
AND p_end_date >= ct.contract_start_date
) THEN
RAISE EXCEPTION
'The requested dates overlap an existing active contract.';
END IF;
INSERT INTO kbnteam.contract (
company_id,
contract_end_date,
contract_start_date,
contract_status_id,
rest_id
)
VALUES (
p_company_id,
p_end_date,
p_start_date,
v_active_status_id,
p_rest_id
);
END IF;
END;
$$;
Процедура 2: pr_create_company_order
Проверува дали компанијата постои и создава компаниска нарачка без доделена достава. Новиот идентификатор се враќа преку параметарот p_comp_order_id.
SQL Дефиниција
CREATE OR REPLACE PROCEDURE kbnteam.pr_create_company_order(
p_company_id INTEGER,
INOUT p_comp_order_id INTEGER DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
BEGIN
IF NOT EXISTS (
SELECT 1
FROM kbnteam.company c
WHERE c.company_id = p_company_id
) THEN
RAISE EXCEPTION 'Company % does not exist.', p_company_id;
END IF;
INSERT INTO kbnteam.company_order (
company_id,
delivery_id
)
VALUES (
p_company_id,
NULL
)
RETURNING comp_order_id INTO p_comp_order_id;
END;
$$;
Процедура 3: pr_create_customer_order
Проверува дали клиентот и компаниската нарачка постојат и припаѓаат на иста компанија. Не дозволува додавање нарачка кон компаниска нарачка за која веќе има фактура. Создава клиентска нарачка со зададениот статус и почетен износ нула.
SQL Дефиниција
CREATE OR REPLACE PROCEDURE kbnteam.pr_create_customer_order(
p_customer_user_id INTEGER,
p_comp_order_id INTEGER,
p_o_status_id INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
v_customer_company_id INTEGER;
v_order_company_id INTEGER;
BEGIN
SELECT c.company_id
INTO v_customer_company_id
FROM kbnteam.customer c
WHERE c.user_id = p_customer_user_id;
IF v_customer_company_id IS NULL THEN
RAISE EXCEPTION 'Customer % does not exist.', p_customer_user_id;
END IF;
SELECT co.company_id
INTO v_order_company_id
FROM kbnteam.company_order co
WHERE co.comp_order_id = p_comp_order_id
FOR UPDATE;
IF v_order_company_id IS NULL THEN
RAISE EXCEPTION 'Company order % does not exist.', p_comp_order_id;
END IF;
IF v_customer_company_id <> v_order_company_id THEN
RAISE EXCEPTION
'Customer % belongs to company %, but company order % belongs to company %.',
p_customer_user_id,
v_customer_company_id,
p_comp_order_id,
v_order_company_id;
END IF;
IF EXISTS (
SELECT 1
FROM kbnteam.invoice i
WHERE i.comp_order_id = p_comp_order_id
) THEN
RAISE EXCEPTION
'Company order % is finalized and already has an invoice.',
p_comp_order_id;
END IF;
INSERT INTO kbnteam.customer_order (
comp_order_id,
customer_user_id,
o_status_id,
order_total
)
VALUES (
p_comp_order_id,
p_customer_user_id,
p_o_status_id,
0
);
END;
$$;
Процедура 4: pr_add_order_item
Го прифаќа типот meal или drink и проверува дали производот постои. За ресторанот на производот е потребен активен договор со компанијата на клиентот на датумот на нарачката. Не дозволува повторно додавање на истиот производ, измена на нарачка со статус completed, cancelled или refunded, ниту измена по фактурирање. По додавањето повторно го пресметува вкупниот износ.
SQL Дефиниција
CREATE OR REPLACE PROCEDURE kbnteam.pr_add_order_item(
p_order_id INTEGER,
p_item_type VARCHAR(10),
p_item_id INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
v_item_type TEXT;
v_company_id INTEGER;
v_rest_id INTEGER;
v_order_date DATE;
v_order_status TEXT;
BEGIN
v_item_type := lower(trim(p_item_type));
IF v_item_type NOT IN ('meal', 'drink') THEN
RAISE EXCEPTION
'Unsupported item type %. Expected meal or drink.',
p_item_type;
END IF;
SELECT
cu.company_id,
o.order_datetime::date,
lower(os.o_status_name)
INTO
v_company_id,
v_order_date,
v_order_status
FROM kbnteam.customer_order o
JOIN kbnteam.customer cu
ON cu.user_id = o.customer_user_id
JOIN kbnteam.order_status os
ON os.o_status_id = o.o_status_id
WHERE o.order_id = p_order_id
FOR UPDATE OF o;
IF NOT FOUND THEN
RAISE EXCEPTION 'Customer order % does not exist.', p_order_id;
END IF;
IF v_order_status IN ('completed', 'cancelled', 'refunded') THEN
RAISE EXCEPTION
'Items cannot be added to order % while its status is %.',
p_order_id,
v_order_status;
END IF;
IF EXISTS (
SELECT 1
FROM kbnteam.customer_order o
JOIN kbnteam.invoice i
ON i.comp_order_id = o.comp_order_id
WHERE o.order_id = p_order_id
) THEN
RAISE EXCEPTION
'Items cannot be added to order % because its company order is finalized.',
p_order_id;
END IF;
IF v_item_type = 'meal' THEN
SELECT m.rest_id
INTO v_rest_id
FROM kbnteam.meal m
WHERE m.meal_id = p_item_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Meal % does not exist.', p_item_id;
END IF;
IF NOT kbnteam.fn_has_active_contract(
v_company_id,
v_rest_id,
v_order_date
) THEN
RAISE EXCEPTION
'Customer company % has no active contract with restaurant % for meal %.',
v_company_id,
v_rest_id,
p_item_id;
END IF;
IF EXISTS (
SELECT 1
FROM kbnteam.order_meal om
WHERE om.order_id = p_order_id
AND om.meal_id = p_item_id
) THEN
RAISE EXCEPTION
'Meal % is already part of order %.',
p_item_id,
p_order_id;
END IF;
INSERT INTO kbnteam.order_meal (meal_id, order_id)
VALUES (p_item_id, p_order_id);
ELSE
SELECT d.rest_id
INTO v_rest_id
FROM kbnteam.drink d
WHERE d.drink_id = p_item_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Drink % does not exist.', p_item_id;
END IF;
IF NOT kbnteam.fn_has_active_contract(
v_company_id,
v_rest_id,
v_order_date
) THEN
RAISE EXCEPTION
'Customer company % has no active contract with restaurant % for drink %.',
v_company_id,
v_rest_id,
p_item_id;
END IF;
IF EXISTS (
SELECT 1
FROM kbnteam.order_drink od
WHERE od.order_id = p_order_id
AND od.drink_id = p_item_id
) THEN
RAISE EXCEPTION
'Drink % is already part of order %.',
p_item_id,
p_order_id;
END IF;
INSERT INTO kbnteam.order_drink (drink_id, order_id)
VALUES (p_item_id, p_order_id);
END IF;
UPDATE kbnteam.customer_order o
SET order_total = kbnteam.fn_calculate_order_total(p_order_id)
WHERE o.order_id = p_order_id;
END;
$$;
Процедура 5: pr_remove_order_item
Го отстранува зададениот производ од нарачката и повторно го пресметува износот. Одбива отстранување производ што не е дел од нарачката. Не дозволува измени при статус completed, cancelled или refunded, ниту по фактурирање.
SQL Дефиниција
CREATE OR REPLACE PROCEDURE kbnteam.pr_remove_order_item(
p_order_id INTEGER,
p_item_type VARCHAR(10),
p_item_id INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
v_item_type TEXT;
v_order_status TEXT;
BEGIN
v_item_type := lower(trim(p_item_type));
IF v_item_type NOT IN ('meal', 'drink') THEN
RAISE EXCEPTION
'Unsupported item type %. Expected meal or drink.',
p_item_type;
END IF;
SELECT lower(os.o_status_name)
INTO v_order_status
FROM kbnteam.customer_order o
JOIN kbnteam.order_status os
ON os.o_status_id = o.o_status_id
WHERE o.order_id = p_order_id
FOR UPDATE OF o;
IF NOT FOUND THEN
RAISE EXCEPTION 'Customer order % does not exist.', p_order_id;
END IF;
IF v_order_status IN ('completed', 'cancelled', 'refunded') THEN
RAISE EXCEPTION
'Items cannot be removed from order % while its status is %.',
p_order_id,
v_order_status;
END IF;
IF EXISTS (
SELECT 1
FROM kbnteam.customer_order o
JOIN kbnteam.invoice i
ON i.comp_order_id = o.comp_order_id
WHERE o.order_id = p_order_id
) THEN
RAISE EXCEPTION
'Items cannot be removed from order % because its company order is finalized.',
p_order_id;
END IF;
IF v_item_type = 'meal' THEN
DELETE FROM kbnteam.order_meal om
WHERE om.order_id = p_order_id
AND om.meal_id = p_item_id;
IF NOT FOUND THEN
RAISE EXCEPTION
'Meal % is not part of order %.',
p_item_id,
p_order_id;
END IF;
ELSE
DELETE FROM kbnteam.order_drink od
WHERE od.order_id = p_order_id
AND od.drink_id = p_item_id;
IF NOT FOUND THEN
RAISE EXCEPTION
'Drink % is not part of order %.',
p_item_id,
p_order_id;
END IF;
END IF;
UPDATE kbnteam.customer_order o
SET order_total = kbnteam.fn_calculate_order_total(p_order_id)
WHERE o.order_id = p_order_id;
END;
$$;
Процедура 6: pr_submit_customer_order
Проверува дали нарачката содржи барем едно јадење или пијалак и дали за секој вклучен ресторан постои активен договор на датумот на нарачката. Не дозволува поднесување при статус completed, cancelled или refunded, ниту по фактурирање. Го пресметува износот и го поставува статусот на pending.
SQL Дефиниција
CREATE OR REPLACE PROCEDURE kbnteam.pr_submit_customer_order(
p_order_id INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
v_company_id INTEGER;
v_order_date DATE;
v_order_status TEXT;
v_pending_status_id INTEGER;
BEGIN
SELECT
cu.company_id,
o.order_datetime::date,
lower(os.o_status_name)
INTO
v_company_id,
v_order_date,
v_order_status
FROM kbnteam.customer_order o
JOIN kbnteam.customer cu
ON cu.user_id = o.customer_user_id
JOIN kbnteam.order_status os
ON os.o_status_id = o.o_status_id
WHERE o.order_id = p_order_id
FOR UPDATE OF o;
IF NOT FOUND THEN
RAISE EXCEPTION 'Customer order % does not exist.', p_order_id;
END IF;
IF v_order_status IN ('completed', 'cancelled', 'refunded') THEN
RAISE EXCEPTION
'Order % cannot be submitted while its status is %.',
p_order_id,
v_order_status;
END IF;
IF EXISTS (
SELECT 1
FROM kbnteam.customer_order o
JOIN kbnteam.invoice i
ON i.comp_order_id = o.comp_order_id
WHERE o.order_id = p_order_id
) THEN
RAISE EXCEPTION
'Order % belongs to a finalized company order.',
p_order_id;
END IF;
IF NOT EXISTS (
SELECT 1
FROM kbnteam.order_meal om
WHERE om.order_id = p_order_id
UNION ALL
SELECT 1
FROM kbnteam.order_drink od
WHERE od.order_id = p_order_id
) THEN
RAISE EXCEPTION 'Order % cannot be submitted without items.', p_order_id;
END IF;
IF EXISTS (
SELECT 1
FROM (
SELECT m.rest_id
FROM kbnteam.order_meal om
JOIN kbnteam.meal m
ON m.meal_id = om.meal_id
WHERE om.order_id = p_order_id
UNION
SELECT d.rest_id
FROM kbnteam.order_drink od
JOIN kbnteam.drink d
ON d.drink_id = od.drink_id
WHERE od.order_id = p_order_id
) item_restaurant
WHERE NOT kbnteam.fn_has_active_contract(
v_company_id,
item_restaurant.rest_id,
v_order_date
)
) THEN
RAISE EXCEPTION
'Order % contains an item from a restaurant without an active contract.',
p_order_id;
END IF;
SELECT os.o_status_id
INTO v_pending_status_id
FROM kbnteam.order_status os
WHERE lower(os.o_status_name) = 'pending';
IF v_pending_status_id IS NULL THEN
RAISE EXCEPTION 'The Pending order status does not exist.';
END IF;
UPDATE kbnteam.customer_order o
SET order_total = kbnteam.fn_calculate_order_total(p_order_id),
o_status_id = v_pending_status_id
WHERE o.order_id = p_order_id;
END;
$$;
Процедура 7: pr_change_order_status
Проверува дали нарачката и новиот статус постојат и дали преминот е дозволен. За completed бара барем еден производ и активни договори со вклучените ресторани на датумот на нарачката, а потоа го пресметува износот. По фактурирање дозволува само премин од completed во refunded. При премин во completed, cancelled или refunded ја повикува процедурата за освежување на лојалноста. Повторно задавање на тековниот статус не предизвикува промена.
SQL Дефиниција
CREATE OR REPLACE PROCEDURE kbnteam.pr_change_order_status(
p_order_id INTEGER,
p_new_status_id INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
v_current_status TEXT;
v_new_status TEXT;
v_customer_user_id INTEGER;
v_company_id INTEGER;
v_order_date DATE;
v_transition_allowed BOOLEAN := FALSE;
BEGIN
SELECT
lower(os.o_status_name),
o.customer_user_id,
cu.company_id,
o.order_datetime::date
INTO
v_current_status,
v_customer_user_id,
v_company_id,
v_order_date
FROM kbnteam.customer_order o
JOIN kbnteam.order_status os
ON os.o_status_id = o.o_status_id
JOIN kbnteam.customer cu
ON cu.user_id = o.customer_user_id
WHERE o.order_id = p_order_id
FOR UPDATE OF o;
IF NOT FOUND THEN
RAISE EXCEPTION 'Customer order % does not exist.', p_order_id;
END IF;
SELECT lower(os.o_status_name)
INTO v_new_status
FROM kbnteam.order_status os
WHERE os.o_status_id = p_new_status_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Order status % does not exist.', p_new_status_id;
END IF;
IF v_current_status = v_new_status THEN
RETURN;
END IF;
v_transition_allowed := CASE v_current_status
WHEN 'pending' THEN
v_new_status IN ('on-hold', 'backorder', 'completed', 'cancelled')
WHEN 'on-hold' THEN
v_new_status IN ('pending', 'backorder', 'completed', 'cancelled')
WHEN 'backorder' THEN
v_new_status IN ('pending', 'on-hold', 'completed', 'cancelled')
WHEN 'completed' THEN
v_new_status = 'refunded'
ELSE FALSE
END;
IF NOT v_transition_allowed THEN
RAISE EXCEPTION
'Order status cannot change from % to %.',
v_current_status,
v_new_status;
END IF;
IF EXISTS (
SELECT 1
FROM kbnteam.customer_order o
JOIN kbnteam.invoice i
ON i.comp_order_id = o.comp_order_id
WHERE o.order_id = p_order_id
) AND NOT (
v_current_status = 'completed'
AND v_new_status = 'refunded'
) THEN
RAISE EXCEPTION
'Order % belongs to a finalized company order.',
p_order_id;
END IF;
IF v_new_status = 'completed' THEN
IF NOT EXISTS (
SELECT 1
FROM kbnteam.order_meal om
WHERE om.order_id = p_order_id
UNION ALL
SELECT 1
FROM kbnteam.order_drink od
WHERE od.order_id = p_order_id
) THEN
RAISE EXCEPTION 'Order % cannot be completed without items.', p_order_id;
END IF;
IF EXISTS (
SELECT 1
FROM (
SELECT m.rest_id
FROM kbnteam.order_meal om
JOIN kbnteam.meal m
ON m.meal_id = om.meal_id
WHERE om.order_id = p_order_id
UNION
SELECT d.rest_id
FROM kbnteam.order_drink od
JOIN kbnteam.drink d
ON d.drink_id = od.drink_id
WHERE od.order_id = p_order_id
) item_restaurant
WHERE NOT kbnteam.fn_has_active_contract(
v_company_id,
item_restaurant.rest_id,
v_order_date
)
) THEN
RAISE EXCEPTION
'Order % contains an item from a restaurant without an active contract.',
p_order_id;
END IF;
UPDATE kbnteam.customer_order o
SET order_total = kbnteam.fn_calculate_order_total(p_order_id)
WHERE o.order_id = p_order_id;
END IF;
UPDATE kbnteam.customer_order o
SET o_status_id = p_new_status_id
WHERE o.order_id = p_order_id;
IF v_new_status IN ('completed', 'cancelled', 'refunded') THEN
CALL kbnteam.pr_refresh_customer_loyalty(v_customer_user_id);
END IF;
END;
$$;
Процедура 8: pr_finalize_company_order
Бара компаниската нарачка да постои, да нема фактура и да содржи клиентски нарачки. Сите клиентски нарачки мора да бидат completed и да содржат барем еден производ. Повторно ги пресметува нивните износи и создава една фактура за компаниската нарачка. Не бара доставата да има статус delivered.
SQL Дефиниција
CREATE OR REPLACE PROCEDURE kbnteam.pr_finalize_company_order(
p_comp_order_id INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
v_company_id INTEGER;
BEGIN
SELECT co.company_id
INTO v_company_id
FROM kbnteam.company_order co
WHERE co.comp_order_id = p_comp_order_id
FOR UPDATE;
IF NOT FOUND THEN
RAISE EXCEPTION 'Company order % does not exist.', p_comp_order_id;
END IF;
IF EXISTS (
SELECT 1
FROM kbnteam.invoice i
WHERE i.comp_order_id = p_comp_order_id
) THEN
RAISE EXCEPTION
'Company order % is already finalized.',
p_comp_order_id;
END IF;
IF NOT EXISTS (
SELECT 1
FROM kbnteam.customer_order o
WHERE o.comp_order_id = p_comp_order_id
) THEN
RAISE EXCEPTION
'Company order % cannot be finalized without customer orders.',
p_comp_order_id;
END IF;
IF EXISTS (
SELECT 1
FROM kbnteam.customer_order o
JOIN kbnteam.order_status os
ON os.o_status_id = o.o_status_id
WHERE o.comp_order_id = p_comp_order_id
AND lower(os.o_status_name) <> 'completed'
) THEN
RAISE EXCEPTION
'Every customer order in company order % must be completed before finalization.',
p_comp_order_id;
END IF;
IF EXISTS (
SELECT 1
FROM kbnteam.customer_order o
WHERE o.comp_order_id = p_comp_order_id
AND NOT EXISTS (
SELECT 1
FROM kbnteam.order_meal om
WHERE om.order_id = o.order_id
)
AND NOT EXISTS (
SELECT 1
FROM kbnteam.order_drink od
WHERE od.order_id = o.order_id
)
) THEN
RAISE EXCEPTION
'Company order % contains an empty customer order.',
p_comp_order_id;
END IF;
UPDATE kbnteam.customer_order o
SET order_total = kbnteam.fn_calculate_order_total(o.order_id)
WHERE o.comp_order_id = p_comp_order_id;
INSERT INTO kbnteam.invoice (comp_order_id)
VALUES (p_comp_order_id);
END;
$$;
Процедура 9: pr_assign_delivery_to_company_order
За компаниска нарачка без достава создава нова достава и ја поврзува со нарачката. Проверува дали доставувачот постои и дали неговиот ресторан има активен договор со компанијата на датумот на доставата. Датумот, статусот и забелешките се запишуваат во новата достава.
SQL Дефиниција
CREATE OR REPLACE PROCEDURE kbnteam.pr_assign_delivery_to_company_order(
p_comp_order_id INTEGER,
p_driver_user_id INTEGER,
p_d_status_id INTEGER,
p_delivery_date DATE DEFAULT CURRENT_DATE,
p_delivery_notes VARCHAR(255) DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
v_company_id INTEGER;
v_driver_rest_id INTEGER;
v_delivery_id INTEGER;
BEGIN
SELECT co.company_id
INTO v_company_id
FROM kbnteam.company_order co
WHERE co.comp_order_id = p_comp_order_id
FOR UPDATE;
IF v_company_id IS NULL THEN
RAISE EXCEPTION 'Company order % does not exist.', p_comp_order_id;
END IF;
IF EXISTS (
SELECT 1
FROM kbnteam.company_order co
WHERE co.comp_order_id = p_comp_order_id
AND co.delivery_id IS NOT NULL
) THEN
RAISE EXCEPTION 'Company order % already has a delivery.', p_comp_order_id;
END IF;
SELECT d.rest_id
INTO v_driver_rest_id
FROM kbnteam.driver d
WHERE d.user_id = p_driver_user_id;
IF v_driver_rest_id IS NULL THEN
RAISE EXCEPTION 'Driver % does not exist.', p_driver_user_id;
END IF;
IF NOT kbnteam.fn_has_active_contract(v_company_id, v_driver_rest_id, p_delivery_date) THEN
RAISE EXCEPTION
'Company % has no active contract with restaurant % for delivery assignment.',
v_company_id,
v_driver_rest_id;
END IF;
INSERT INTO kbnteam.delivery (
delivery_date,
delivery_notes,
d_status_id,
driver_user_id
)
VALUES (
p_delivery_date,
p_delivery_notes,
p_d_status_id,
p_driver_user_id
)
RETURNING delivery_id INTO v_delivery_id;
UPDATE kbnteam.company_order
SET delivery_id = v_delivery_id
WHERE comp_order_id = p_comp_order_id;
END;
$$;
Процедура 10: pr_change_delivery_status
Проверува дали доставата и новиот статус постојат и дали преминот е дозволен. Статусите assigned, picked up, on the way, delivered и delayed бараат назначен доставувач. За delivered дополнително бара доставата да биде поврзана со компаниска нарачка. Повторно задавање на тековниот статус не предизвикува промена.
SQL Дефиниција
CREATE OR REPLACE PROCEDURE kbnteam.pr_change_delivery_status(
p_delivery_id INTEGER,
p_new_status_id INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
v_current_status TEXT;
v_new_status TEXT;
v_driver_user_id INTEGER;
v_transition_allowed BOOLEAN := FALSE;
BEGIN
SELECT
lower(ds.d_status_name),
d.driver_user_id
INTO
v_current_status,
v_driver_user_id
FROM kbnteam.delivery d
JOIN kbnteam.delivery_status ds
ON ds.d_status_id = d.d_status_id
WHERE d.delivery_id = p_delivery_id
FOR UPDATE OF d;
IF NOT FOUND THEN
RAISE EXCEPTION 'Delivery % does not exist.', p_delivery_id;
END IF;
SELECT lower(ds.d_status_name)
INTO v_new_status
FROM kbnteam.delivery_status ds
WHERE ds.d_status_id = p_new_status_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Delivery status % does not exist.', p_new_status_id;
END IF;
IF v_current_status = v_new_status THEN
RETURN;
END IF;
v_transition_allowed := CASE v_current_status
WHEN 'pending' THEN
v_new_status IN ('assigned', 'cancelled')
WHEN 'assigned' THEN
v_new_status IN ('picked up', 'delayed', 'failed', 'cancelled')
WHEN 'picked up' THEN
v_new_status IN ('on the way', 'delayed', 'failed', 'cancelled')
WHEN 'on the way' THEN
v_new_status IN ('delivered', 'delayed', 'failed')
WHEN 'delayed' THEN
v_new_status IN (
'assigned',
'picked up',
'on the way',
'delivered',
'failed',
'cancelled'
)
ELSE FALSE
END;
IF NOT v_transition_allowed THEN
RAISE EXCEPTION
'Delivery status cannot change from % to %.',
v_current_status,
v_new_status;
END IF;
IF v_new_status IN (
'assigned',
'picked up',
'on the way',
'delivered',
'delayed'
) AND v_driver_user_id IS NULL THEN
RAISE EXCEPTION
'Delivery % requires an assigned driver before status can become %.',
p_delivery_id,
v_new_status;
END IF;
IF v_new_status = 'delivered' AND NOT EXISTS (
SELECT 1
FROM kbnteam.company_order co
WHERE co.delivery_id = p_delivery_id
) THEN
RAISE EXCEPTION
'Delivery % is not attached to a company order.',
p_delivery_id;
END IF;
UPDATE kbnteam.delivery d
SET d_status_id = p_new_status_id
WHERE d.delivery_id = p_delivery_id;
END;
$$;
Процедура 11: pr_create_order_review
Бара завршена нарачка со статус completed, поврзана со зададениот клиент, и проверува дали веќе постои рецензија за неа. Запишува општа оценка и коментар, како и посебни оценки за храната и ресторанот. Оценките се во опсег од 1 до 5.
SQL Дефиниција
CREATE OR REPLACE PROCEDURE kbnteam.pr_create_order_review(
p_order_id INTEGER,
p_customer_user_id INTEGER,
p_review_rating INTEGER,
p_review_comment VARCHAR(255),
p_food_rating INTEGER,
p_restaurant_rating INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
v_order_customer_user_id INTEGER;
v_order_status TEXT;
v_review_id INTEGER;
BEGIN
IF p_review_rating NOT BETWEEN 1 AND 5
OR p_food_rating NOT BETWEEN 1 AND 5
OR p_restaurant_rating NOT BETWEEN 1 AND 5 THEN
RAISE EXCEPTION 'All review ratings must be between 1 and 5.';
END IF;
SELECT
o.customer_user_id,
lower(os.o_status_name)
INTO
v_order_customer_user_id,
v_order_status
FROM kbnteam.customer_order o
JOIN kbnteam.order_status os
ON os.o_status_id = o.o_status_id
WHERE o.order_id = p_order_id
FOR UPDATE OF o;
IF NOT FOUND THEN
RAISE EXCEPTION 'Customer order % does not exist.', p_order_id;
END IF;
IF v_order_customer_user_id <> p_customer_user_id THEN
RAISE EXCEPTION
'Customer % is not the owner of order %.',
p_customer_user_id,
p_order_id;
END IF;
IF v_order_status <> 'completed' THEN
RAISE EXCEPTION
'Order % must be completed before it can be reviewed.',
p_order_id;
END IF;
IF EXISTS (
SELECT 1
FROM kbnteam.order_review review_link
WHERE review_link.order_id = p_order_id
) THEN
RAISE EXCEPTION 'Order % has already been reviewed.', p_order_id;
END IF;
INSERT INTO kbnteam.review (
review_comment,
review_rating
)
VALUES (
p_review_comment,
p_review_rating
)
RETURNING review_id INTO v_review_id;
INSERT INTO kbnteam.order_review (
order_id,
order_review_food_rating,
order_review_res_rating,
review_id
)
VALUES (
p_order_id,
p_food_rating,
p_restaurant_rating,
v_review_id
);
END;
$$;
Процедура 12: pr_create_delivery_review
Бара достава со статус delivered и клиент што има нарачка поврзана со таа достава. Не дозволува втора рецензија за истата достава, независно од клиентот. Запишува општа оценка и коментар, како и оценки за доставувачот и брзината, во опсег од 1 до 5.
SQL Дефиниција
CREATE OR REPLACE PROCEDURE kbnteam.pr_create_delivery_review(
p_delivery_id INTEGER,
p_customer_user_id INTEGER,
p_review_rating INTEGER,
p_review_comment VARCHAR(255),
p_courier_rating INTEGER,
p_speed_rating INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
v_delivery_status TEXT;
v_review_id INTEGER;
BEGIN
IF p_review_rating NOT BETWEEN 1 AND 5
OR p_courier_rating NOT BETWEEN 1 AND 5
OR p_speed_rating NOT BETWEEN 1 AND 5 THEN
RAISE EXCEPTION 'All review ratings must be between 1 and 5.';
END IF;
SELECT lower(ds.d_status_name)
INTO v_delivery_status
FROM kbnteam.delivery d
JOIN kbnteam.delivery_status ds
ON ds.d_status_id = d.d_status_id
WHERE d.delivery_id = p_delivery_id
FOR UPDATE OF d;
IF NOT FOUND THEN
RAISE EXCEPTION 'Delivery % does not exist.', p_delivery_id;
END IF;
IF v_delivery_status <> 'delivered' THEN
RAISE EXCEPTION
'Delivery % must be delivered before it can be reviewed.',
p_delivery_id;
END IF;
IF NOT EXISTS (
SELECT 1
FROM kbnteam.company_order co
JOIN kbnteam.customer_order o
ON o.comp_order_id = co.comp_order_id
WHERE co.delivery_id = p_delivery_id
AND o.customer_user_id = p_customer_user_id
) THEN
RAISE EXCEPTION
'Customer % is not associated with delivery %.',
p_customer_user_id,
p_delivery_id;
END IF;
IF EXISTS (
SELECT 1
FROM kbnteam.delivery_review review_link
WHERE review_link.delivery_id = p_delivery_id
) THEN
RAISE EXCEPTION
'Delivery % has already been reviewed.',
p_delivery_id;
END IF;
INSERT INTO kbnteam.review (
review_comment,
review_rating
)
VALUES (
p_review_comment,
p_review_rating
)
RETURNING review_id INTO v_review_id;
INSERT INTO kbnteam.delivery_review (
del_review_courier_rating,
del_review_speed_rating,
delivery_id,
review_id
)
VALUES (
p_courier_rating,
p_speed_rating,
p_delivery_id,
v_review_id
);
END;
$$;
Процедура 13: pr_refresh_customer_loyalty
Ги пресметува поените на клиентот и избира ниво чиј опсег ги содржи поените. При повеќе совпаѓања го избира нивото со најголем долен праг; ако нема совпаѓање, го избира нивото со најмал долен праг. Го користи статусот на лојалност со најмал идентификатор. Создава членство или ги ажурира поените, нивото и статусот на постојното членство.
SQL Дефиниција
CREATE OR REPLACE PROCEDURE kbnteam.pr_refresh_customer_loyalty(
p_customer_user_id INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
v_points INTEGER;
v_tier_id INTEGER;
v_status_id INTEGER;
BEGIN
IF NOT EXISTS (
SELECT 1
FROM kbnteam.customer c
WHERE c.user_id = p_customer_user_id
) THEN
RAISE EXCEPTION 'Customer % does not exist.', p_customer_user_id;
END IF;
v_points := kbnteam.fn_calculate_customer_loyalty_points(p_customer_user_id);
SELECT lt.tier_id
INTO v_tier_id
FROM kbnteam.loyalty_tier lt
WHERE v_points BETWEEN lt.tier_minimum_points AND lt.tier_maximum_points
ORDER BY lt.tier_minimum_points DESC
LIMIT 1;
IF v_tier_id IS NULL THEN
SELECT lt.tier_id
INTO v_tier_id
FROM kbnteam.loyalty_tier lt
ORDER BY lt.tier_minimum_points
LIMIT 1;
END IF;
SELECT cls.cus_loyalty_status_id
INTO v_status_id
FROM kbnteam.customer_loyalty_status cls
ORDER BY cls.cus_loyalty_status_id
LIMIT 1;
IF v_status_id IS NULL THEN
RAISE EXCEPTION 'No customer loyalty status rows exist.';
END IF;
INSERT INTO kbnteam.customer_loyalty (
cus_loyalty_curr_points,
cus_loyalty_status_id,
user_id,
tier_id
)
VALUES (
v_points,
v_status_id,
p_customer_user_id,
v_tier_id
)
ON CONFLICT (user_id) DO UPDATE
SET cus_loyalty_curr_points = EXCLUDED.cus_loyalty_curr_points,
cus_loyalty_status_id = EXCLUDED.cus_loyalty_status_id,
tier_id = EXCLUDED.tier_id;
END;
$$;
Тригер 1: trg_validate_customer_order_company
Проверува дали клиентот и компаниската нарачка постојат и припаѓаат на иста компанија. При неуспешна проверка ја прекинува наредбата со исклучок. При успешна проверка го враќа новиот ред (NEW).
Проверката се активира при внесување нарачка и при ажурирање што ги наведува customer_user_id или comp_order_id. Ажурирање само на order_total или статусот не го активира овој тригер.
SQL Дефиниција
CREATE OR REPLACE FUNCTION kbnteam.tf_validate_customer_order_company()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
v_customer_company_id INTEGER;
v_order_company_id INTEGER;
BEGIN
SELECT c.company_id
INTO v_customer_company_id
FROM kbnteam.customer c
WHERE c.user_id = NEW.customer_user_id;
SELECT co.company_id
INTO v_order_company_id
FROM kbnteam.company_order co
WHERE co.comp_order_id = NEW.comp_order_id;
IF v_customer_company_id IS NULL THEN
RAISE EXCEPTION 'Customer % does not exist.', NEW.customer_user_id;
END IF;
IF v_order_company_id IS NULL THEN
RAISE EXCEPTION 'Company order % does not exist.', NEW.comp_order_id;
END IF;
IF v_customer_company_id <> v_order_company_id THEN
RAISE EXCEPTION
'Customer % belongs to company %, but company order % belongs to company %.',
NEW.customer_user_id,
v_customer_company_id,
NEW.comp_order_id,
v_order_company_id;
END IF;
RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS trg_validate_customer_order_company
ON kbnteam.customer_order;
CREATE TRIGGER trg_validate_customer_order_company
BEFORE INSERT OR UPDATE OF customer_user_id, comp_order_id
ON kbnteam.customer_order
FOR EACH ROW
EXECUTE FUNCTION kbnteam.tf_validate_customer_order_company();
Тригер 2: trg_order_meal_guard_and_total
При внесување или измена на ставка проверува дали нарачката е поврзана со валиден клиент и компанија и дали постои активен договор со ресторанот на јадењето на датумот на нарачката. Потоа повторно го пресметува и запишува вкупниот износ на нарачката. При бришење не проверува договор, туку го освежува износот на старата нарачка.
Ако со UPDATE ставката се префрли во друга нарачка, ги освежува износите и на новата и на старата нарачка.
Проверките за статус на нарачката и постоење фактура се дел од процедурите за додавање и отстранување производи; овој тригер ги проверува договорите и го освежува износот.
SQL Дефиниција
CREATE OR REPLACE FUNCTION kbnteam.tf_order_meal_guard_and_total()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
v_target_order_id INTEGER;
v_old_order_id INTEGER;
v_company_id INTEGER;
v_rest_id INTEGER;
v_order_date DATE;
BEGIN
IF TG_OP <> 'DELETE' THEN
SELECT
cu.company_id,
m.rest_id,
o.order_datetime::date
INTO
v_company_id,
v_rest_id,
v_order_date
FROM kbnteam.customer_order o
JOIN kbnteam.customer cu
ON cu.user_id = o.customer_user_id
JOIN kbnteam.meal m
ON m.meal_id = NEW.meal_id
WHERE o.order_id = NEW.order_id;
IF v_company_id IS NULL THEN
RAISE EXCEPTION 'Order % is not linked to a valid customer/company.', NEW.order_id;
END IF;
IF NOT kbnteam.fn_has_active_contract(v_company_id, v_rest_id, v_order_date) THEN
RAISE EXCEPTION
'Customer company % has no active contract with restaurant % for meal %.',
v_company_id,
v_rest_id,
NEW.meal_id;
END IF;
v_target_order_id := NEW.order_id;
ELSE
v_target_order_id := OLD.order_id;
END IF;
UPDATE kbnteam.customer_order o
SET order_total = kbnteam.fn_calculate_order_total(v_target_order_id)
WHERE o.order_id = v_target_order_id;
IF TG_OP = 'UPDATE' AND OLD.order_id <> NEW.order_id THEN
v_old_order_id := OLD.order_id;
UPDATE kbnteam.customer_order o
SET order_total = kbnteam.fn_calculate_order_total(v_old_order_id)
WHERE o.order_id = v_old_order_id;
END IF;
RETURN COALESCE(NEW, OLD);
END;
$$;
DROP TRIGGER IF EXISTS trg_order_meal_guard_and_total
ON kbnteam.order_meal;
CREATE TRIGGER trg_order_meal_guard_and_total
AFTER INSERT OR UPDATE OR DELETE
ON kbnteam.order_meal
FOR EACH ROW
EXECUTE FUNCTION kbnteam.tf_order_meal_guard_and_total();
Тригер 3: trg_order_drink_guard_and_total
При внесување или измена на ставка проверува дали нарачката е поврзана со валиден клиент и компанија и дали постои активен договор со ресторанот на пијалакот на датумот на нарачката. Потоа повторно го пресметува и запишува вкупниот износ на нарачката. При бришење не проверува договор, туку го освежува износот на старата нарачка.
Ако со UPDATE ставката се префрли во друга нарачка, ги освежува износите и на новата и на старата нарачка.
Проверките за статус на нарачката и постоење фактура се дел од процедурите за додавање и отстранување производи; овој тригер ги проверува договорите и го освежува износот.
SQL Дефиниција
CREATE OR REPLACE FUNCTION kbnteam.tf_order_drink_guard_and_total()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
v_target_order_id INTEGER;
v_old_order_id INTEGER;
v_company_id INTEGER;
v_rest_id INTEGER;
v_order_date DATE;
BEGIN
IF TG_OP <> 'DELETE' THEN
SELECT
cu.company_id,
d.rest_id,
o.order_datetime::date
INTO
v_company_id,
v_rest_id,
v_order_date
FROM kbnteam.customer_order o
JOIN kbnteam.customer cu
ON cu.user_id = o.customer_user_id
JOIN kbnteam.drink d
ON d.drink_id = NEW.drink_id
WHERE o.order_id = NEW.order_id;
IF v_company_id IS NULL THEN
RAISE EXCEPTION 'Order % is not linked to a valid customer/company.', NEW.order_id;
END IF;
IF NOT kbnteam.fn_has_active_contract(v_company_id, v_rest_id, v_order_date) THEN
RAISE EXCEPTION
'Customer company % has no active contract with restaurant % for drink %.',
v_company_id,
v_rest_id,
NEW.drink_id;
END IF;
v_target_order_id := NEW.order_id;
ELSE
v_target_order_id := OLD.order_id;
END IF;
UPDATE kbnteam.customer_order o
SET order_total = kbnteam.fn_calculate_order_total(v_target_order_id)
WHERE o.order_id = v_target_order_id;
IF TG_OP = 'UPDATE' AND OLD.order_id <> NEW.order_id THEN
v_old_order_id := OLD.order_id;
UPDATE kbnteam.customer_order o
SET order_total = kbnteam.fn_calculate_order_total(v_old_order_id)
WHERE o.order_id = v_old_order_id;
END IF;
RETURN COALESCE(NEW, OLD);
END;
$$;
DROP TRIGGER IF EXISTS trg_order_drink_guard_and_total
ON kbnteam.order_drink;
CREATE TRIGGER trg_order_drink_guard_and_total
AFTER INSERT OR UPDATE OR DELETE
ON kbnteam.order_drink
FOR EACH ROW
EXECUTE FUNCTION kbnteam.tf_order_drink_guard_and_total();
Last modified
10 days ago
Last modified on 09/15/26 10:29:13
Note:
See TracWiki
for help on using the wiki.
