Changes between Version 5 and Version 6 of DatabaseProgramming


Ignore:
Timestamp:
09/15/26 10:29:13 (10 days ago)
Author:
223235
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseProgramming

    v5 v6  
    11= Functions, Procedures, Triggers =
    22
    3 == Функции ==
    4 
    5 Функциите ги пресметуваат износите и поените и ја проверуваат важноста на договорите. Припаѓаат на шемата `kbnteam`, се напишани на SQL и се означени како STABLE. Читаат податоци и враќаат вредност без да ги менуваат табелите. Можат да се користат во SQL изрази и во логиката на процедурите и тригерите.
    6 
    7 ||= Функција ||= Намена ||= Повратен тип ||
    8 || [#fn_has_active_contract fn_has_active_contract] || Проверка на активен договор || `BOOLEAN` ||
    9 || [#fn_calculate_order_total fn_calculate_order_total] || Пресметка на вкупен износ || `NUMERIC(10,2)` ||
    10 || [#fn_calculate_customer_loyalty_points fn_calculate_customer_loyalty_points] || Пресметка на поени за лојалност || `INTEGER` ||
    11 
    12 [[Anchor(fn_has_active_contract)]]
    13 
    14 === fn_has_active_contract ===
     3Шемата `kbnteam` содржи три функции, тринаесет процедури и три тригери за договори, нарачки, достави, рецензии и лојалност. Функциите враќаат пресметки и проверки, процедурите се повикуваат со `CALL`, а тригерите автоматски се активираат при промени на податоците.
     4
     5== Функција 1: fn_has_active_contract ==
    156
    167Враќа TRUE ако постои договор за зададената компанија и ресторан со статус active, а зададениот датум е меѓу почетниот и крајниот датум, вклучувајќи ги границите. Инаку враќа FALSE. Кога датумот не е зададен, се користи тековниот датум. Називот на статусот се споредува без разлика на големината на буквите.
    178
    18 ||= Својство ||= Вредност ||
    19 || Повратен тип || `BOOLEAN` ||
    20 || Изворни табели || `contract`, `contract_status` ||
    21 
    22 ||= Параметар ||= Тип ||= Стандардна вредност ||= Опис ||
    23 || `p_company_id` || `INTEGER` || — || Идентификатор на компанијата. ||
    24 || `p_rest_id` || `INTEGER` || — || Идентификатор на ресторанот. ||
    25 || `p_on_date` || `DATE` || `CURRENT_DATE` || Датум за проверка на важноста на договорот. ||
    26 
    27 Се користи од: [#trg_order_drink_guard_and_total trg_order_drink_guard_and_total], [#trg_order_meal_guard_and_total trg_order_meal_guard_and_total], [#pr_assign_delivery_to_company_order pr_assign_delivery_to_company_order], [#pr_add_order_item pr_add_order_item], [#pr_change_order_status pr_change_order_status], [#pr_submit_customer_order pr_submit_customer_order].
    28 
    29 [[Anchor(fn_calculate_order_total)]]
    30 
    31 === fn_calculate_order_total ===
     9=== SQL Дефиниција ===
     10
     11{{{
     12#!sql
     13CREATE OR REPLACE FUNCTION kbnteam.fn_has_active_contract(
     14    p_company_id INTEGER,
     15    p_rest_id INTEGER,
     16    p_on_date DATE DEFAULT CURRENT_DATE
     17)
     18RETURNS BOOLEAN
     19LANGUAGE sql
     20STABLE
     21AS $$
     22    SELECT EXISTS (
     23        SELECT 1
     24        FROM kbnteam.contract ct
     25        JOIN kbnteam.contract_status cs
     26          ON cs.contract_status_id = ct.contract_status_id
     27        WHERE ct.company_id = p_company_id
     28          AND ct.rest_id = p_rest_id
     29          AND lower(cs.contract_status_name) = 'active'
     30          AND p_on_date BETWEEN ct.contract_start_date AND ct.contract_end_date
     31    );
     32$$;
     33}}}
     34
     35== Функција 2: fn_calculate_order_total ==
    3236
    3337Ги собира тековните цени на јадењата и пијалоците поврзани со нарачката. Ако нема ставки, враќа 0.00. Не применува попуст за лојалност или дополнителен надомест за достава и не ја менува нарачката; запишувањето на пресметаниот износ го вршат процедурите и тригерите.
    3438
    35 ||= Својство ||= Вредност ||
    36 || Повратен тип || `NUMERIC(10,2)` ||
    37 || Изворни табели || `order_meal`, `meal`, `order_drink`, `drink` ||
    38 
    39 ||= Параметар ||= Тип ||= Стандардна вредност ||= Опис ||
    40 || `p_order_id` || `INTEGER` || — || Идентификатор на клиентската нарачка. ||
    41 
    42 Се користи од: [#trg_order_drink_guard_and_total trg_order_drink_guard_and_total], [#trg_order_meal_guard_and_total trg_order_meal_guard_and_total], [#pr_remove_order_item pr_remove_order_item], [#pr_add_order_item pr_add_order_item], [#pr_finalize_company_order pr_finalize_company_order], [#pr_change_order_status pr_change_order_status], [#pr_submit_customer_order pr_submit_customer_order].
    43 
    44 [[Anchor(fn_calculate_customer_loyalty_points)]]
    45 
    46 === fn_calculate_customer_loyalty_points ===
     39=== SQL Дефиниција ===
     40
     41{{{
     42#!sql
     43CREATE OR REPLACE FUNCTION kbnteam.fn_calculate_order_total(
     44    p_order_id INTEGER
     45)
     46RETURNS NUMERIC(10,2)
     47LANGUAGE sql
     48STABLE
     49AS $$
     50    SELECT (
     51        COALESCE((
     52            SELECT SUM(m.meal_price)
     53            FROM kbnteam.order_meal om
     54            JOIN kbnteam.meal m
     55              ON m.meal_id = om.meal_id
     56            WHERE om.order_id = p_order_id
     57        ), 0)
     58        +
     59        COALESCE((
     60            SELECT SUM(d.drink_price)
     61            FROM kbnteam.order_drink od
     62            JOIN kbnteam.drink d
     63              ON d.drink_id = od.drink_id
     64            WHERE od.order_id = p_order_id
     65        ), 0)
     66    )::NUMERIC(10,2);
     67$$;
     68}}}
     69
     70== Функција 3: fn_calculate_customer_loyalty_points ==
    4771
    4872Ги собира износите на нарачките на клиентот, го заокружува збирот надолу и враќа цел број. Нарачките со статус cancelled придонесуваат со нула. Останатите статуси, вклучително pending и refunded, се вклучени. Ако нема нарачки, враќа нула. Функцијата ги пресметува поените, а нивното запишување го врши процедурата за освежување на лојалноста.
    4973
    50 ||= Својство ||= Вредност ||
    51 || Повратен тип || `INTEGER` ||
    52 || Изворни табели || `customer_order`, `order_status` ||
    53 
    54 ||= Параметар ||= Тип ||= Стандардна вредност ||= Опис ||
    55 || `p_customer_user_id` || `INTEGER` || — || Идентификатор на клиентот. ||
    56 
    57 Се користи од: [#pr_refresh_customer_loyalty pr_refresh_customer_loyalty].
    58 
    59 == Процедури ==
    60 
    61 Процедурите ги опфаќаат операциите за договори, нарачки, достави, рецензии и лојалност. Припаѓаат на шемата `kbnteam`, се напишани на PL/pgSQL и се повикуваат со `CALL`. Проверките и промените на податоците се извршуваат во рамки на тековната трансакција.
    62 
    63 === Договори ===
    64 
    65 ||= Процедура ||= Намена ||= Директно изменети табели ||
    66 || [#pr_activate_or_renew_contract pr_activate_or_renew_contract] || Активирање или обновување договор || `contract` ||
    67 
    68 === Нарачки и фактурирање ===
    69 
    70 ||= Процедура ||= Намена ||= Директно изменети табели ||
    71 || [#pr_create_company_order pr_create_company_order] || Создавање компаниска нарачка || `company_order` ||
    72 || [#pr_create_customer_order pr_create_customer_order] || Создавање клиентска нарачка || `customer_order` ||
    73 || [#pr_add_order_item pr_add_order_item] || Додавање јадење или пијалак || `order_meal`, `order_drink`, `customer_order` ||
    74 || [#pr_remove_order_item pr_remove_order_item] || Отстранување јадење или пијалак || `order_meal`, `order_drink`, `customer_order` ||
    75 || [#pr_submit_customer_order pr_submit_customer_order] || Поднесување клиентска нарачка || `customer_order` ||
    76 || [#pr_change_order_status pr_change_order_status] || Промена на статус на нарачка || `customer_order` ||
    77 || [#pr_finalize_company_order pr_finalize_company_order] || Финализирање и фактурирање || `customer_order`, `invoice` ||
    78 
    79 === Достави ===
    80 
    81 ||= Процедура ||= Намена ||= Директно изменети табели ||
    82 || [#pr_assign_delivery_to_company_order pr_assign_delivery_to_company_order] || Доделување достава || `delivery`, `company_order` ||
    83 || [#pr_change_delivery_status pr_change_delivery_status] || Промена на статус на достава || `delivery` ||
    84 
    85 === Рецензии ===
    86 
    87 ||= Процедура ||= Намена ||= Директно изменети табели ||
    88 || [#pr_create_order_review pr_create_order_review] || Оценување клиентска нарачка || `review`, `order_review` ||
    89 || [#pr_create_delivery_review pr_create_delivery_review] || Оценување достава || `review`, `delivery_review` ||
    90 
    91 === Лојалност ===
    92 
    93 ||= Процедура ||= Намена ||= Директно изменети табели ||
    94 || [#pr_refresh_customer_loyalty pr_refresh_customer_loyalty] || Освежување лојалност || `customer_loyalty` ||
    95 
    96 == Параметри и однесување ==
    97 
    98 Параметрите без наведена насока се влезни (IN). Параметарот INOUT е истовремено влезен и излезен. Единствено создавањето компаниска нарачка го враќа новиот идентификатор преку INOUT параметар; останатите процедури немаат излезни параметри.
    99 
    100 [[Anchor(pr_activate_or_renew_contract)]]
    101 
    102 === pr_activate_or_renew_contract ===
     74=== SQL Дефиниција ===
     75
     76{{{
     77#!sql
     78CREATE OR REPLACE FUNCTION kbnteam.fn_calculate_customer_loyalty_points(
     79    p_customer_user_id INTEGER
     80)
     81RETURNS INTEGER
     82LANGUAGE sql
     83STABLE
     84AS $$
     85    SELECT COALESCE(
     86        FLOOR(SUM(
     87            CASE
     88                WHEN lower(os.o_status_name) = 'cancelled' THEN 0
     89                ELSE o.order_total
     90            END
     91        )),
     92        0
     93    )::INTEGER
     94    FROM kbnteam.customer_order o
     95    JOIN kbnteam.order_status os
     96      ON os.o_status_id = o.o_status_id
     97    WHERE o.customer_user_id = p_customer_user_id;
     98$$;
     99}}}
     100
     101== Процедура 1: pr_activate_or_renew_contract ==
    103102
    104103Проверува дали компанијата и ресторанот постојат и дали периодот е валиден. Кога постои активен договор со период што се преклопува или е непосредно соседен, го проширува неговиот период. Ако нема таков договор, создава нов со статус active. Обновувањето се одбива ако проширениот период се преклопува со друг активен договор.
    105104
    106 ||= Параметар ||= Тип ||= Насока ||= Стандардна вредност ||= Опис ||
    107 || `p_company_id` || `INTEGER` || IN || — || Компанија на која се однесува операцијата. ||
    108 || `p_rest_id` || `INTEGER` || IN || — || Ресторан поврзан со договорот. ||
    109 || `p_start_date` || `DATE` || IN || — || Почетен датум на договорот. ||
    110 || `p_end_date` || `DATE` || IN || — || Краен датум на договорот. ||
    111 
    112 [[Anchor(pr_create_company_order)]]
    113 
    114 === pr_create_company_order ===
     105=== SQL Дефиниција ===
     106
     107{{{
     108#!sql
     109CREATE OR REPLACE PROCEDURE kbnteam.pr_activate_or_renew_contract(
     110    p_company_id INTEGER,
     111    p_rest_id INTEGER,
     112    p_start_date DATE,
     113    p_end_date DATE
     114)
     115LANGUAGE plpgsql
     116AS $$
     117DECLARE
     118    v_active_status_id INTEGER;
     119    v_contract_id INTEGER;
     120    v_existing_start DATE;
     121    v_existing_end DATE;
     122    v_merged_start DATE;
     123    v_merged_end DATE;
     124BEGIN
     125    IF p_start_date IS NULL OR p_end_date IS NULL THEN
     126        RAISE EXCEPTION 'Contract start and end dates are required.';
     127    END IF;
     128
     129    IF p_end_date < p_start_date THEN
     130        RAISE EXCEPTION
     131            'Contract end date % cannot be before start date %.',
     132            p_end_date,
     133            p_start_date;
     134    END IF;
     135
     136    IF NOT EXISTS (
     137        SELECT 1
     138        FROM kbnteam.company c
     139        WHERE c.company_id = p_company_id
     140    ) THEN
     141        RAISE EXCEPTION 'Company % does not exist.', p_company_id;
     142    END IF;
     143
     144    IF NOT EXISTS (
     145        SELECT 1
     146        FROM kbnteam.restaurant r
     147        WHERE r.rest_id = p_rest_id
     148    ) THEN
     149        RAISE EXCEPTION 'Restaurant % does not exist.', p_rest_id;
     150    END IF;
     151
     152    SELECT cs.contract_status_id
     153    INTO v_active_status_id
     154    FROM kbnteam.contract_status cs
     155    WHERE lower(cs.contract_status_name) = 'active';
     156
     157    IF v_active_status_id IS NULL THEN
     158        RAISE EXCEPTION 'The Active contract status does not exist.';
     159    END IF;
     160
     161    SELECT
     162        ct.contract_id,
     163        ct.contract_start_date,
     164        ct.contract_end_date
     165    INTO
     166        v_contract_id,
     167        v_existing_start,
     168        v_existing_end
     169    FROM kbnteam.contract ct
     170    WHERE ct.company_id = p_company_id
     171      AND ct.rest_id = p_rest_id
     172      AND ct.contract_status_id = v_active_status_id
     173      AND p_start_date <= ct.contract_end_date + 1
     174      AND p_end_date >= ct.contract_start_date - 1
     175    ORDER BY ct.contract_end_date DESC
     176    LIMIT 1
     177    FOR UPDATE;
     178
     179    IF FOUND THEN
     180        v_merged_start := LEAST(v_existing_start, p_start_date);
     181        v_merged_end := GREATEST(v_existing_end, p_end_date);
     182
     183        IF EXISTS (
     184            SELECT 1
     185            FROM kbnteam.contract ct
     186            WHERE ct.company_id = p_company_id
     187              AND ct.rest_id = p_rest_id
     188              AND ct.contract_status_id = v_active_status_id
     189              AND ct.contract_id <> v_contract_id
     190              AND v_merged_start <= ct.contract_end_date
     191              AND v_merged_end >= ct.contract_start_date
     192        ) THEN
     193            RAISE EXCEPTION
     194                'Renewing contract % would overlap another active contract.',
     195                v_contract_id;
     196        END IF;
     197
     198        UPDATE kbnteam.contract ct
     199        SET contract_start_date = v_merged_start,
     200            contract_end_date = v_merged_end,
     201            contract_status_id = v_active_status_id
     202        WHERE ct.contract_id = v_contract_id;
     203    ELSE
     204        IF EXISTS (
     205            SELECT 1
     206            FROM kbnteam.contract ct
     207            WHERE ct.company_id = p_company_id
     208              AND ct.rest_id = p_rest_id
     209              AND ct.contract_status_id = v_active_status_id
     210              AND p_start_date <= ct.contract_end_date
     211              AND p_end_date >= ct.contract_start_date
     212        ) THEN
     213            RAISE EXCEPTION
     214                'The requested dates overlap an existing active contract.';
     215        END IF;
     216
     217        INSERT INTO kbnteam.contract (
     218            company_id,
     219            contract_end_date,
     220            contract_start_date,
     221            contract_status_id,
     222            rest_id
     223        )
     224        VALUES (
     225            p_company_id,
     226            p_end_date,
     227            p_start_date,
     228            v_active_status_id,
     229            p_rest_id
     230        );
     231    END IF;
     232END;
     233$$;
     234}}}
     235
     236== Процедура 2: pr_create_company_order ==
    115237
    116238Проверува дали компанијата постои и создава компаниска нарачка без доделена достава. Новиот идентификатор се враќа преку параметарот p_comp_order_id.
    117239
    118 ||= Параметар ||= Тип ||= Насока ||= Стандардна вредност ||= Опис ||
    119 || `p_company_id` || `INTEGER` || IN || — || Компанија на која се однесува операцијата. ||
    120 || `p_comp_order_id` || `INTEGER` || INOUT || `NULL` || Се пополнува со идентификаторот на новата компаниска нарачка. ||
    121 
    122 [[Anchor(pr_create_customer_order)]]
    123 
    124 === pr_create_customer_order ===
     240=== SQL Дефиниција ===
     241
     242{{{
     243#!sql
     244CREATE OR REPLACE PROCEDURE kbnteam.pr_create_company_order(
     245    p_company_id INTEGER,
     246    INOUT p_comp_order_id INTEGER DEFAULT NULL
     247)
     248LANGUAGE plpgsql
     249AS $$
     250BEGIN
     251    IF NOT EXISTS (
     252        SELECT 1
     253        FROM kbnteam.company c
     254        WHERE c.company_id = p_company_id
     255    ) THEN
     256        RAISE EXCEPTION 'Company % does not exist.', p_company_id;
     257    END IF;
     258
     259    INSERT INTO kbnteam.company_order (
     260        company_id,
     261        delivery_id
     262    )
     263    VALUES (
     264        p_company_id,
     265        NULL
     266    )
     267    RETURNING comp_order_id INTO p_comp_order_id;
     268END;
     269$$;
     270}}}
     271
     272== Процедура 3: pr_create_customer_order ==
    125273
    126274Проверува дали клиентот и компаниската нарачка постојат и припаѓаат на иста компанија. Не дозволува додавање нарачка кон компаниска нарачка за која веќе има фактура. Создава клиентска нарачка со зададениот статус и почетен износ нула.
    127275
    128 ||= Параметар ||= Тип ||= Насока ||= Стандардна вредност ||= Опис ||
    129 || `p_customer_user_id` || `INTEGER` || IN || — || Идентификатор на клиентот. ||
    130 || `p_comp_order_id` || `INTEGER` || IN || — || Идентификатор на компаниската нарачка. ||
    131 || `p_o_status_id` || `INTEGER` || IN || — || Почетен статус на клиентската нарачка. ||
    132 
    133 [[Anchor(pr_add_order_item)]]
    134 
    135 === pr_add_order_item ===
     276=== SQL Дефиниција ===
     277
     278{{{
     279#!sql
     280CREATE OR REPLACE PROCEDURE kbnteam.pr_create_customer_order(
     281    p_customer_user_id INTEGER,
     282    p_comp_order_id INTEGER,
     283    p_o_status_id INTEGER
     284)
     285LANGUAGE plpgsql
     286AS $$
     287DECLARE
     288    v_customer_company_id INTEGER;
     289    v_order_company_id INTEGER;
     290BEGIN
     291    SELECT c.company_id
     292    INTO v_customer_company_id
     293    FROM kbnteam.customer c
     294    WHERE c.user_id = p_customer_user_id;
     295
     296    IF v_customer_company_id IS NULL THEN
     297        RAISE EXCEPTION 'Customer % does not exist.', p_customer_user_id;
     298    END IF;
     299
     300    SELECT co.company_id
     301    INTO v_order_company_id
     302    FROM kbnteam.company_order co
     303    WHERE co.comp_order_id = p_comp_order_id
     304    FOR UPDATE;
     305
     306    IF v_order_company_id IS NULL THEN
     307        RAISE EXCEPTION 'Company order % does not exist.', p_comp_order_id;
     308    END IF;
     309
     310    IF v_customer_company_id <> v_order_company_id THEN
     311        RAISE EXCEPTION
     312            'Customer % belongs to company %, but company order % belongs to company %.',
     313            p_customer_user_id,
     314            v_customer_company_id,
     315            p_comp_order_id,
     316            v_order_company_id;
     317    END IF;
     318
     319    IF EXISTS (
     320        SELECT 1
     321        FROM kbnteam.invoice i
     322        WHERE i.comp_order_id = p_comp_order_id
     323    ) THEN
     324        RAISE EXCEPTION
     325            'Company order % is finalized and already has an invoice.',
     326            p_comp_order_id;
     327    END IF;
     328
     329    INSERT INTO kbnteam.customer_order (
     330        comp_order_id,
     331        customer_user_id,
     332        o_status_id,
     333        order_total
     334    )
     335    VALUES (
     336        p_comp_order_id,
     337        p_customer_user_id,
     338        p_o_status_id,
     339        0
     340    );
     341END;
     342$$;
     343}}}
     344
     345== Процедура 4: pr_add_order_item ==
    136346
    137347Го прифаќа типот meal или drink и проверува дали производот постои. За ресторанот на производот е потребен активен договор со компанијата на клиентот на датумот на нарачката. Не дозволува повторно додавање на истиот производ, измена на нарачка со статус completed, cancelled или refunded, ниту измена по фактурирање. По додавањето повторно го пресметува вкупниот износ.
    138348
    139 ||= Параметар ||= Тип ||= Насока ||= Стандардна вредност ||= Опис ||
    140 || `p_order_id` || `INTEGER` || IN || — || Идентификатор на клиентската нарачка. ||
    141 || `p_item_type` || `VARCHAR(10)` || IN || — || Тип на производ: meal или drink. ||
    142 || `p_item_id` || `INTEGER` || IN || — || Идентификатор на јадењето или пијалакот. ||
    143 
    144 [[Anchor(pr_remove_order_item)]]
    145 
    146 === pr_remove_order_item ===
     349=== SQL Дефиниција ===
     350
     351{{{
     352#!sql
     353CREATE OR REPLACE PROCEDURE kbnteam.pr_add_order_item(
     354    p_order_id INTEGER,
     355    p_item_type VARCHAR(10),
     356    p_item_id INTEGER
     357)
     358LANGUAGE plpgsql
     359AS $$
     360DECLARE
     361    v_item_type TEXT;
     362    v_company_id INTEGER;
     363    v_rest_id INTEGER;
     364    v_order_date DATE;
     365    v_order_status TEXT;
     366BEGIN
     367    v_item_type := lower(trim(p_item_type));
     368
     369    IF v_item_type NOT IN ('meal', 'drink') THEN
     370        RAISE EXCEPTION
     371            'Unsupported item type %. Expected meal or drink.',
     372            p_item_type;
     373    END IF;
     374
     375    SELECT
     376        cu.company_id,
     377        o.order_datetime::date,
     378        lower(os.o_status_name)
     379    INTO
     380        v_company_id,
     381        v_order_date,
     382        v_order_status
     383    FROM kbnteam.customer_order o
     384    JOIN kbnteam.customer cu
     385      ON cu.user_id = o.customer_user_id
     386    JOIN kbnteam.order_status os
     387      ON os.o_status_id = o.o_status_id
     388    WHERE o.order_id = p_order_id
     389    FOR UPDATE OF o;
     390
     391    IF NOT FOUND THEN
     392        RAISE EXCEPTION 'Customer order % does not exist.', p_order_id;
     393    END IF;
     394
     395    IF v_order_status IN ('completed', 'cancelled', 'refunded') THEN
     396        RAISE EXCEPTION
     397            'Items cannot be added to order % while its status is %.',
     398            p_order_id,
     399            v_order_status;
     400    END IF;
     401
     402    IF EXISTS (
     403        SELECT 1
     404        FROM kbnteam.customer_order o
     405        JOIN kbnteam.invoice i
     406          ON i.comp_order_id = o.comp_order_id
     407        WHERE o.order_id = p_order_id
     408    ) THEN
     409        RAISE EXCEPTION
     410            'Items cannot be added to order % because its company order is finalized.',
     411            p_order_id;
     412    END IF;
     413
     414    IF v_item_type = 'meal' THEN
     415        SELECT m.rest_id
     416        INTO v_rest_id
     417        FROM kbnteam.meal m
     418        WHERE m.meal_id = p_item_id;
     419
     420        IF NOT FOUND THEN
     421            RAISE EXCEPTION 'Meal % does not exist.', p_item_id;
     422        END IF;
     423
     424        IF NOT kbnteam.fn_has_active_contract(
     425            v_company_id,
     426            v_rest_id,
     427            v_order_date
     428        ) THEN
     429            RAISE EXCEPTION
     430                'Customer company % has no active contract with restaurant % for meal %.',
     431                v_company_id,
     432                v_rest_id,
     433                p_item_id;
     434        END IF;
     435
     436        IF EXISTS (
     437            SELECT 1
     438            FROM kbnteam.order_meal om
     439            WHERE om.order_id = p_order_id
     440              AND om.meal_id = p_item_id
     441        ) THEN
     442            RAISE EXCEPTION
     443                'Meal % is already part of order %.',
     444                p_item_id,
     445                p_order_id;
     446        END IF;
     447
     448        INSERT INTO kbnteam.order_meal (meal_id, order_id)
     449        VALUES (p_item_id, p_order_id);
     450    ELSE
     451        SELECT d.rest_id
     452        INTO v_rest_id
     453        FROM kbnteam.drink d
     454        WHERE d.drink_id = p_item_id;
     455
     456        IF NOT FOUND THEN
     457            RAISE EXCEPTION 'Drink % does not exist.', p_item_id;
     458        END IF;
     459
     460        IF NOT kbnteam.fn_has_active_contract(
     461            v_company_id,
     462            v_rest_id,
     463            v_order_date
     464        ) THEN
     465            RAISE EXCEPTION
     466                'Customer company % has no active contract with restaurant % for drink %.',
     467                v_company_id,
     468                v_rest_id,
     469                p_item_id;
     470        END IF;
     471
     472        IF EXISTS (
     473            SELECT 1
     474            FROM kbnteam.order_drink od
     475            WHERE od.order_id = p_order_id
     476              AND od.drink_id = p_item_id
     477        ) THEN
     478            RAISE EXCEPTION
     479                'Drink % is already part of order %.',
     480                p_item_id,
     481                p_order_id;
     482        END IF;
     483
     484        INSERT INTO kbnteam.order_drink (drink_id, order_id)
     485        VALUES (p_item_id, p_order_id);
     486    END IF;
     487
     488    UPDATE kbnteam.customer_order o
     489    SET order_total = kbnteam.fn_calculate_order_total(p_order_id)
     490    WHERE o.order_id = p_order_id;
     491END;
     492$$;
     493}}}
     494
     495== Процедура 5: pr_remove_order_item ==
    147496
    148497Го отстранува зададениот производ од нарачката и повторно го пресметува износот. Одбива отстранување производ што не е дел од нарачката. Не дозволува измени при статус completed, cancelled или refunded, ниту по фактурирање.
    149498
    150 ||= Параметар ||= Тип ||= Насока ||= Стандардна вредност ||= Опис ||
    151 || `p_order_id` || `INTEGER` || IN || — || Идентификатор на клиентската нарачка. ||
    152 || `p_item_type` || `VARCHAR(10)` || IN || — || Тип на производ: meal или drink. ||
    153 || `p_item_id` || `INTEGER` || IN || — || Идентификатор на јадењето или пијалакот. ||
    154 
    155 [[Anchor(pr_submit_customer_order)]]
    156 
    157 === pr_submit_customer_order ===
     499=== SQL Дефиниција ===
     500
     501{{{
     502#!sql
     503CREATE OR REPLACE PROCEDURE kbnteam.pr_remove_order_item(
     504    p_order_id INTEGER,
     505    p_item_type VARCHAR(10),
     506    p_item_id INTEGER
     507)
     508LANGUAGE plpgsql
     509AS $$
     510DECLARE
     511    v_item_type TEXT;
     512    v_order_status TEXT;
     513BEGIN
     514    v_item_type := lower(trim(p_item_type));
     515
     516    IF v_item_type NOT IN ('meal', 'drink') THEN
     517        RAISE EXCEPTION
     518            'Unsupported item type %. Expected meal or drink.',
     519            p_item_type;
     520    END IF;
     521
     522    SELECT lower(os.o_status_name)
     523    INTO v_order_status
     524    FROM kbnteam.customer_order o
     525    JOIN kbnteam.order_status os
     526      ON os.o_status_id = o.o_status_id
     527    WHERE o.order_id = p_order_id
     528    FOR UPDATE OF o;
     529
     530    IF NOT FOUND THEN
     531        RAISE EXCEPTION 'Customer order % does not exist.', p_order_id;
     532    END IF;
     533
     534    IF v_order_status IN ('completed', 'cancelled', 'refunded') THEN
     535        RAISE EXCEPTION
     536            'Items cannot be removed from order % while its status is %.',
     537            p_order_id,
     538            v_order_status;
     539    END IF;
     540
     541    IF EXISTS (
     542        SELECT 1
     543        FROM kbnteam.customer_order o
     544        JOIN kbnteam.invoice i
     545          ON i.comp_order_id = o.comp_order_id
     546        WHERE o.order_id = p_order_id
     547    ) THEN
     548        RAISE EXCEPTION
     549            'Items cannot be removed from order % because its company order is finalized.',
     550            p_order_id;
     551    END IF;
     552
     553    IF v_item_type = 'meal' THEN
     554        DELETE FROM kbnteam.order_meal om
     555        WHERE om.order_id = p_order_id
     556          AND om.meal_id = p_item_id;
     557
     558        IF NOT FOUND THEN
     559            RAISE EXCEPTION
     560                'Meal % is not part of order %.',
     561                p_item_id,
     562                p_order_id;
     563        END IF;
     564    ELSE
     565        DELETE FROM kbnteam.order_drink od
     566        WHERE od.order_id = p_order_id
     567          AND od.drink_id = p_item_id;
     568
     569        IF NOT FOUND THEN
     570            RAISE EXCEPTION
     571                'Drink % is not part of order %.',
     572                p_item_id,
     573                p_order_id;
     574        END IF;
     575    END IF;
     576
     577    UPDATE kbnteam.customer_order o
     578    SET order_total = kbnteam.fn_calculate_order_total(p_order_id)
     579    WHERE o.order_id = p_order_id;
     580END;
     581$$;
     582}}}
     583
     584== Процедура 6: pr_submit_customer_order ==
    158585
    159586Проверува дали нарачката содржи барем едно јадење или пијалак и дали за секој вклучен ресторан постои активен договор на датумот на нарачката. Не дозволува поднесување при статус completed, cancelled или refunded, ниту по фактурирање. Го пресметува износот и го поставува статусот на pending.
    160587
    161 ||= Параметар ||= Тип ||= Насока ||= Стандардна вредност ||= Опис ||
    162 || `p_order_id` || `INTEGER` || IN || — || Идентификатор на клиентската нарачка. ||
    163 
    164 [[Anchor(pr_change_order_status)]]
    165 
    166 === pr_change_order_status ===
     588=== SQL Дефиниција ===
     589
     590{{{
     591#!sql
     592CREATE OR REPLACE PROCEDURE kbnteam.pr_submit_customer_order(
     593    p_order_id INTEGER
     594)
     595LANGUAGE plpgsql
     596AS $$
     597DECLARE
     598    v_company_id INTEGER;
     599    v_order_date DATE;
     600    v_order_status TEXT;
     601    v_pending_status_id INTEGER;
     602BEGIN
     603    SELECT
     604        cu.company_id,
     605        o.order_datetime::date,
     606        lower(os.o_status_name)
     607    INTO
     608        v_company_id,
     609        v_order_date,
     610        v_order_status
     611    FROM kbnteam.customer_order o
     612    JOIN kbnteam.customer cu
     613      ON cu.user_id = o.customer_user_id
     614    JOIN kbnteam.order_status os
     615      ON os.o_status_id = o.o_status_id
     616    WHERE o.order_id = p_order_id
     617    FOR UPDATE OF o;
     618
     619    IF NOT FOUND THEN
     620        RAISE EXCEPTION 'Customer order % does not exist.', p_order_id;
     621    END IF;
     622
     623    IF v_order_status IN ('completed', 'cancelled', 'refunded') THEN
     624        RAISE EXCEPTION
     625            'Order % cannot be submitted while its status is %.',
     626            p_order_id,
     627            v_order_status;
     628    END IF;
     629
     630    IF EXISTS (
     631        SELECT 1
     632        FROM kbnteam.customer_order o
     633        JOIN kbnteam.invoice i
     634          ON i.comp_order_id = o.comp_order_id
     635        WHERE o.order_id = p_order_id
     636    ) THEN
     637        RAISE EXCEPTION
     638            'Order % belongs to a finalized company order.',
     639            p_order_id;
     640    END IF;
     641
     642    IF NOT EXISTS (
     643        SELECT 1
     644        FROM kbnteam.order_meal om
     645        WHERE om.order_id = p_order_id
     646
     647        UNION ALL
     648
     649        SELECT 1
     650        FROM kbnteam.order_drink od
     651        WHERE od.order_id = p_order_id
     652    ) THEN
     653        RAISE EXCEPTION 'Order % cannot be submitted without items.', p_order_id;
     654    END IF;
     655
     656    IF EXISTS (
     657        SELECT 1
     658        FROM (
     659            SELECT m.rest_id
     660            FROM kbnteam.order_meal om
     661            JOIN kbnteam.meal m
     662              ON m.meal_id = om.meal_id
     663            WHERE om.order_id = p_order_id
     664
     665            UNION
     666
     667            SELECT d.rest_id
     668            FROM kbnteam.order_drink od
     669            JOIN kbnteam.drink d
     670              ON d.drink_id = od.drink_id
     671            WHERE od.order_id = p_order_id
     672        ) item_restaurant
     673        WHERE NOT kbnteam.fn_has_active_contract(
     674            v_company_id,
     675            item_restaurant.rest_id,
     676            v_order_date
     677        )
     678    ) THEN
     679        RAISE EXCEPTION
     680            'Order % contains an item from a restaurant without an active contract.',
     681            p_order_id;
     682    END IF;
     683
     684    SELECT os.o_status_id
     685    INTO v_pending_status_id
     686    FROM kbnteam.order_status os
     687    WHERE lower(os.o_status_name) = 'pending';
     688
     689    IF v_pending_status_id IS NULL THEN
     690        RAISE EXCEPTION 'The Pending order status does not exist.';
     691    END IF;
     692
     693    UPDATE kbnteam.customer_order o
     694    SET order_total = kbnteam.fn_calculate_order_total(p_order_id),
     695        o_status_id = v_pending_status_id
     696    WHERE o.order_id = p_order_id;
     697END;
     698$$;
     699}}}
     700
     701== Процедура 7: pr_change_order_status ==
    167702
    168703Проверува дали нарачката и новиот статус постојат и дали преминот е дозволен. За completed бара барем еден производ и активни договори со вклучените ресторани на датумот на нарачката, а потоа го пресметува износот. По фактурирање дозволува само премин од completed во refunded. При премин во completed, cancelled или refunded ја повикува процедурата за освежување на лојалноста. Повторно задавање на тековниот статус не предизвикува промена.
    169704
    170 ||= Параметар ||= Тип ||= Насока ||= Стандардна вредност ||= Опис ||
    171 || `p_order_id` || `INTEGER` || IN || — || Идентификатор на клиентската нарачка. ||
    172 || `p_new_status_id` || `INTEGER` || IN || — || Идентификатор на новиот статус. ||
    173 
    174 [[Anchor(pr_finalize_company_order)]]
    175 
    176 === pr_finalize_company_order ===
     705=== SQL Дефиниција ===
     706
     707{{{
     708#!sql
     709CREATE OR REPLACE PROCEDURE kbnteam.pr_change_order_status(
     710    p_order_id INTEGER,
     711    p_new_status_id INTEGER
     712)
     713LANGUAGE plpgsql
     714AS $$
     715DECLARE
     716    v_current_status TEXT;
     717    v_new_status TEXT;
     718    v_customer_user_id INTEGER;
     719    v_company_id INTEGER;
     720    v_order_date DATE;
     721    v_transition_allowed BOOLEAN := FALSE;
     722BEGIN
     723    SELECT
     724        lower(os.o_status_name),
     725        o.customer_user_id,
     726        cu.company_id,
     727        o.order_datetime::date
     728    INTO
     729        v_current_status,
     730        v_customer_user_id,
     731        v_company_id,
     732        v_order_date
     733    FROM kbnteam.customer_order o
     734    JOIN kbnteam.order_status os
     735      ON os.o_status_id = o.o_status_id
     736    JOIN kbnteam.customer cu
     737      ON cu.user_id = o.customer_user_id
     738    WHERE o.order_id = p_order_id
     739    FOR UPDATE OF o;
     740
     741    IF NOT FOUND THEN
     742        RAISE EXCEPTION 'Customer order % does not exist.', p_order_id;
     743    END IF;
     744
     745    SELECT lower(os.o_status_name)
     746    INTO v_new_status
     747    FROM kbnteam.order_status os
     748    WHERE os.o_status_id = p_new_status_id;
     749
     750    IF NOT FOUND THEN
     751        RAISE EXCEPTION 'Order status % does not exist.', p_new_status_id;
     752    END IF;
     753
     754    IF v_current_status = v_new_status THEN
     755        RETURN;
     756    END IF;
     757
     758    v_transition_allowed := CASE v_current_status
     759        WHEN 'pending' THEN
     760            v_new_status IN ('on-hold', 'backorder', 'completed', 'cancelled')
     761        WHEN 'on-hold' THEN
     762            v_new_status IN ('pending', 'backorder', 'completed', 'cancelled')
     763        WHEN 'backorder' THEN
     764            v_new_status IN ('pending', 'on-hold', 'completed', 'cancelled')
     765        WHEN 'completed' THEN
     766            v_new_status = 'refunded'
     767        ELSE FALSE
     768    END;
     769
     770    IF NOT v_transition_allowed THEN
     771        RAISE EXCEPTION
     772            'Order status cannot change from % to %.',
     773            v_current_status,
     774            v_new_status;
     775    END IF;
     776
     777    IF EXISTS (
     778        SELECT 1
     779        FROM kbnteam.customer_order o
     780        JOIN kbnteam.invoice i
     781          ON i.comp_order_id = o.comp_order_id
     782        WHERE o.order_id = p_order_id
     783    ) AND NOT (
     784        v_current_status = 'completed'
     785        AND v_new_status = 'refunded'
     786    ) THEN
     787        RAISE EXCEPTION
     788            'Order % belongs to a finalized company order.',
     789            p_order_id;
     790    END IF;
     791
     792    IF v_new_status = 'completed' THEN
     793        IF NOT EXISTS (
     794            SELECT 1
     795            FROM kbnteam.order_meal om
     796            WHERE om.order_id = p_order_id
     797
     798            UNION ALL
     799
     800            SELECT 1
     801            FROM kbnteam.order_drink od
     802            WHERE od.order_id = p_order_id
     803        ) THEN
     804            RAISE EXCEPTION 'Order % cannot be completed without items.', p_order_id;
     805        END IF;
     806
     807        IF EXISTS (
     808            SELECT 1
     809            FROM (
     810                SELECT m.rest_id
     811                FROM kbnteam.order_meal om
     812                JOIN kbnteam.meal m
     813                  ON m.meal_id = om.meal_id
     814                WHERE om.order_id = p_order_id
     815
     816                UNION
     817
     818                SELECT d.rest_id
     819                FROM kbnteam.order_drink od
     820                JOIN kbnteam.drink d
     821                  ON d.drink_id = od.drink_id
     822                WHERE od.order_id = p_order_id
     823            ) item_restaurant
     824            WHERE NOT kbnteam.fn_has_active_contract(
     825                v_company_id,
     826                item_restaurant.rest_id,
     827                v_order_date
     828            )
     829        ) THEN
     830            RAISE EXCEPTION
     831                'Order % contains an item from a restaurant without an active contract.',
     832                p_order_id;
     833        END IF;
     834
     835        UPDATE kbnteam.customer_order o
     836        SET order_total = kbnteam.fn_calculate_order_total(p_order_id)
     837        WHERE o.order_id = p_order_id;
     838    END IF;
     839
     840    UPDATE kbnteam.customer_order o
     841    SET o_status_id = p_new_status_id
     842    WHERE o.order_id = p_order_id;
     843
     844    IF v_new_status IN ('completed', 'cancelled', 'refunded') THEN
     845        CALL kbnteam.pr_refresh_customer_loyalty(v_customer_user_id);
     846    END IF;
     847END;
     848$$;
     849}}}
     850
     851== Процедура 8: pr_finalize_company_order ==
    177852
    178853Бара компаниската нарачка да постои, да нема фактура и да содржи клиентски нарачки. Сите клиентски нарачки мора да бидат completed и да содржат барем еден производ. Повторно ги пресметува нивните износи и создава една фактура за компаниската нарачка. Не бара доставата да има статус delivered.
    179854
    180 ||= Параметар ||= Тип ||= Насока ||= Стандардна вредност ||= Опис ||
    181 || `p_comp_order_id` || `INTEGER` || IN || — || Идентификатор на компаниската нарачка. ||
    182 
    183 [[Anchor(pr_assign_delivery_to_company_order)]]
    184 
    185 === pr_assign_delivery_to_company_order ===
     855=== SQL Дефиниција ===
     856
     857{{{
     858#!sql
     859CREATE OR REPLACE PROCEDURE kbnteam.pr_finalize_company_order(
     860    p_comp_order_id INTEGER
     861)
     862LANGUAGE plpgsql
     863AS $$
     864DECLARE
     865    v_company_id INTEGER;
     866BEGIN
     867    SELECT co.company_id
     868    INTO v_company_id
     869    FROM kbnteam.company_order co
     870    WHERE co.comp_order_id = p_comp_order_id
     871    FOR UPDATE;
     872
     873    IF NOT FOUND THEN
     874        RAISE EXCEPTION 'Company order % does not exist.', p_comp_order_id;
     875    END IF;
     876
     877    IF EXISTS (
     878        SELECT 1
     879        FROM kbnteam.invoice i
     880        WHERE i.comp_order_id = p_comp_order_id
     881    ) THEN
     882        RAISE EXCEPTION
     883            'Company order % is already finalized.',
     884            p_comp_order_id;
     885    END IF;
     886
     887    IF NOT EXISTS (
     888        SELECT 1
     889        FROM kbnteam.customer_order o
     890        WHERE o.comp_order_id = p_comp_order_id
     891    ) THEN
     892        RAISE EXCEPTION
     893            'Company order % cannot be finalized without customer orders.',
     894            p_comp_order_id;
     895    END IF;
     896
     897    IF EXISTS (
     898        SELECT 1
     899        FROM kbnteam.customer_order o
     900        JOIN kbnteam.order_status os
     901          ON os.o_status_id = o.o_status_id
     902        WHERE o.comp_order_id = p_comp_order_id
     903          AND lower(os.o_status_name) <> 'completed'
     904    ) THEN
     905        RAISE EXCEPTION
     906            'Every customer order in company order % must be completed before finalization.',
     907            p_comp_order_id;
     908    END IF;
     909
     910    IF EXISTS (
     911        SELECT 1
     912        FROM kbnteam.customer_order o
     913        WHERE o.comp_order_id = p_comp_order_id
     914          AND NOT EXISTS (
     915              SELECT 1
     916              FROM kbnteam.order_meal om
     917              WHERE om.order_id = o.order_id
     918          )
     919          AND NOT EXISTS (
     920              SELECT 1
     921              FROM kbnteam.order_drink od
     922              WHERE od.order_id = o.order_id
     923          )
     924    ) THEN
     925        RAISE EXCEPTION
     926            'Company order % contains an empty customer order.',
     927            p_comp_order_id;
     928    END IF;
     929
     930    UPDATE kbnteam.customer_order o
     931    SET order_total = kbnteam.fn_calculate_order_total(o.order_id)
     932    WHERE o.comp_order_id = p_comp_order_id;
     933
     934    INSERT INTO kbnteam.invoice (comp_order_id)
     935    VALUES (p_comp_order_id);
     936END;
     937$$;
     938}}}
     939
     940== Процедура 9: pr_assign_delivery_to_company_order ==
    186941
    187942За компаниска нарачка без достава создава нова достава и ја поврзува со нарачката. Проверува дали доставувачот постои и дали неговиот ресторан има активен договор со компанијата на датумот на доставата. Датумот, статусот и забелешките се запишуваат во новата достава.
    188943
    189 ||= Параметар ||= Тип ||= Насока ||= Стандардна вредност ||= Опис ||
    190 || `p_comp_order_id` || `INTEGER` || IN || — || Идентификатор на компаниската нарачка. ||
    191 || `p_driver_user_id` || `INTEGER` || IN || — || Идентификатор на доставувачот. ||
    192 || `p_d_status_id` || `INTEGER` || IN || — || Почетен статус на доставата. ||
    193 || `p_delivery_date` || `DATE` || IN || `CURRENT_DATE` || Датум на доставата. ||
    194 || `p_delivery_notes` || `VARCHAR(255)` || IN || `NULL` || Забелешки за доставата. ||
    195 
    196 [[Anchor(pr_change_delivery_status)]]
    197 
    198 === pr_change_delivery_status ===
     944=== SQL Дефиниција ===
     945
     946{{{
     947#!sql
     948CREATE OR REPLACE PROCEDURE kbnteam.pr_assign_delivery_to_company_order(
     949    p_comp_order_id INTEGER,
     950    p_driver_user_id INTEGER,
     951    p_d_status_id INTEGER,
     952    p_delivery_date DATE DEFAULT CURRENT_DATE,
     953    p_delivery_notes VARCHAR(255) DEFAULT NULL
     954)
     955LANGUAGE plpgsql
     956AS $$
     957DECLARE
     958    v_company_id INTEGER;
     959    v_driver_rest_id INTEGER;
     960    v_delivery_id INTEGER;
     961BEGIN
     962    SELECT co.company_id
     963    INTO v_company_id
     964    FROM kbnteam.company_order co
     965    WHERE co.comp_order_id = p_comp_order_id
     966    FOR UPDATE;
     967
     968    IF v_company_id IS NULL THEN
     969        RAISE EXCEPTION 'Company order % does not exist.', p_comp_order_id;
     970    END IF;
     971
     972    IF EXISTS (
     973        SELECT 1
     974        FROM kbnteam.company_order co
     975        WHERE co.comp_order_id = p_comp_order_id
     976          AND co.delivery_id IS NOT NULL
     977    ) THEN
     978        RAISE EXCEPTION 'Company order % already has a delivery.', p_comp_order_id;
     979    END IF;
     980
     981    SELECT d.rest_id
     982    INTO v_driver_rest_id
     983    FROM kbnteam.driver d
     984    WHERE d.user_id = p_driver_user_id;
     985
     986    IF v_driver_rest_id IS NULL THEN
     987        RAISE EXCEPTION 'Driver % does not exist.', p_driver_user_id;
     988    END IF;
     989
     990    IF NOT kbnteam.fn_has_active_contract(v_company_id, v_driver_rest_id, p_delivery_date) THEN
     991        RAISE EXCEPTION
     992            'Company % has no active contract with restaurant % for delivery assignment.',
     993            v_company_id,
     994            v_driver_rest_id;
     995    END IF;
     996
     997    INSERT INTO kbnteam.delivery (
     998        delivery_date,
     999        delivery_notes,
     1000        d_status_id,
     1001        driver_user_id
     1002    )
     1003    VALUES (
     1004        p_delivery_date,
     1005        p_delivery_notes,
     1006        p_d_status_id,
     1007        p_driver_user_id
     1008    )
     1009    RETURNING delivery_id INTO v_delivery_id;
     1010
     1011    UPDATE kbnteam.company_order
     1012    SET delivery_id = v_delivery_id
     1013    WHERE comp_order_id = p_comp_order_id;
     1014END;
     1015$$;
     1016}}}
     1017
     1018== Процедура 10: pr_change_delivery_status ==
    1991019
    2001020Проверува дали доставата и новиот статус постојат и дали преминот е дозволен. Статусите assigned, picked up, on the way, delivered и delayed бараат назначен доставувач. За delivered дополнително бара доставата да биде поврзана со компаниска нарачка. Повторно задавање на тековниот статус не предизвикува промена.
    2011021
    202 ||= Параметар ||= Тип ||= Насока ||= Стандардна вредност ||= Опис ||
    203 || `p_delivery_id` || `INTEGER` || IN || — || Идентификатор на доставата. ||
    204 || `p_new_status_id` || `INTEGER` || IN || — || Идентификатор на новиот статус. ||
    205 
    206 [[Anchor(pr_create_order_review)]]
    207 
    208 === pr_create_order_review ===
     1022=== SQL Дефиниција ===
     1023
     1024{{{
     1025#!sql
     1026CREATE OR REPLACE PROCEDURE kbnteam.pr_change_delivery_status(
     1027    p_delivery_id INTEGER,
     1028    p_new_status_id INTEGER
     1029)
     1030LANGUAGE plpgsql
     1031AS $$
     1032DECLARE
     1033    v_current_status TEXT;
     1034    v_new_status TEXT;
     1035    v_driver_user_id INTEGER;
     1036    v_transition_allowed BOOLEAN := FALSE;
     1037BEGIN
     1038    SELECT
     1039        lower(ds.d_status_name),
     1040        d.driver_user_id
     1041    INTO
     1042        v_current_status,
     1043        v_driver_user_id
     1044    FROM kbnteam.delivery d
     1045    JOIN kbnteam.delivery_status ds
     1046      ON ds.d_status_id = d.d_status_id
     1047    WHERE d.delivery_id = p_delivery_id
     1048    FOR UPDATE OF d;
     1049
     1050    IF NOT FOUND THEN
     1051        RAISE EXCEPTION 'Delivery % does not exist.', p_delivery_id;
     1052    END IF;
     1053
     1054    SELECT lower(ds.d_status_name)
     1055    INTO v_new_status
     1056    FROM kbnteam.delivery_status ds
     1057    WHERE ds.d_status_id = p_new_status_id;
     1058
     1059    IF NOT FOUND THEN
     1060        RAISE EXCEPTION 'Delivery status % does not exist.', p_new_status_id;
     1061    END IF;
     1062
     1063    IF v_current_status = v_new_status THEN
     1064        RETURN;
     1065    END IF;
     1066
     1067    v_transition_allowed := CASE v_current_status
     1068        WHEN 'pending' THEN
     1069            v_new_status IN ('assigned', 'cancelled')
     1070        WHEN 'assigned' THEN
     1071            v_new_status IN ('picked up', 'delayed', 'failed', 'cancelled')
     1072        WHEN 'picked up' THEN
     1073            v_new_status IN ('on the way', 'delayed', 'failed', 'cancelled')
     1074        WHEN 'on the way' THEN
     1075            v_new_status IN ('delivered', 'delayed', 'failed')
     1076        WHEN 'delayed' THEN
     1077            v_new_status IN (
     1078                'assigned',
     1079                'picked up',
     1080                'on the way',
     1081                'delivered',
     1082                'failed',
     1083                'cancelled'
     1084            )
     1085        ELSE FALSE
     1086    END;
     1087
     1088    IF NOT v_transition_allowed THEN
     1089        RAISE EXCEPTION
     1090            'Delivery status cannot change from % to %.',
     1091            v_current_status,
     1092            v_new_status;
     1093    END IF;
     1094
     1095    IF v_new_status IN (
     1096        'assigned',
     1097        'picked up',
     1098        'on the way',
     1099        'delivered',
     1100        'delayed'
     1101    ) AND v_driver_user_id IS NULL THEN
     1102        RAISE EXCEPTION
     1103            'Delivery % requires an assigned driver before status can become %.',
     1104            p_delivery_id,
     1105            v_new_status;
     1106    END IF;
     1107
     1108    IF v_new_status = 'delivered' AND NOT EXISTS (
     1109        SELECT 1
     1110        FROM kbnteam.company_order co
     1111        WHERE co.delivery_id = p_delivery_id
     1112    ) THEN
     1113        RAISE EXCEPTION
     1114            'Delivery % is not attached to a company order.',
     1115            p_delivery_id;
     1116    END IF;
     1117
     1118    UPDATE kbnteam.delivery d
     1119    SET d_status_id = p_new_status_id
     1120    WHERE d.delivery_id = p_delivery_id;
     1121END;
     1122$$;
     1123}}}
     1124
     1125== Процедура 11: pr_create_order_review ==
    2091126
    2101127Бара завршена нарачка со статус completed, поврзана со зададениот клиент, и проверува дали веќе постои рецензија за неа. Запишува општа оценка и коментар, како и посебни оценки за храната и ресторанот. Оценките се во опсег од 1 до 5.
    2111128
    212 ||= Параметар ||= Тип ||= Насока ||= Стандардна вредност ||= Опис ||
    213 || `p_order_id` || `INTEGER` || IN || — || Идентификатор на клиентската нарачка. ||
    214 || `p_customer_user_id` || `INTEGER` || IN || — || Идентификатор на клиентот. ||
    215 || `p_review_rating` || `INTEGER` || IN || — || Општа оценка од 1 до 5. ||
    216 || `p_review_comment` || `VARCHAR(255)` || IN || — || Коментар за рецензијата. ||
    217 || `p_food_rating` || `INTEGER` || IN || — || Оценка за храната од 1 до 5. ||
    218 || `p_restaurant_rating` || `INTEGER` || IN || — || Оценка за ресторанот од 1 до 5. ||
    219 
    220 [[Anchor(pr_create_delivery_review)]]
    221 
    222 === pr_create_delivery_review ===
     1129=== SQL Дефиниција ===
     1130
     1131{{{
     1132#!sql
     1133CREATE OR REPLACE PROCEDURE kbnteam.pr_create_order_review(
     1134    p_order_id INTEGER,
     1135    p_customer_user_id INTEGER,
     1136    p_review_rating INTEGER,
     1137    p_review_comment VARCHAR(255),
     1138    p_food_rating INTEGER,
     1139    p_restaurant_rating INTEGER
     1140)
     1141LANGUAGE plpgsql
     1142AS $$
     1143DECLARE
     1144    v_order_customer_user_id INTEGER;
     1145    v_order_status TEXT;
     1146    v_review_id INTEGER;
     1147BEGIN
     1148    IF p_review_rating NOT BETWEEN 1 AND 5
     1149       OR p_food_rating NOT BETWEEN 1 AND 5
     1150       OR p_restaurant_rating NOT BETWEEN 1 AND 5 THEN
     1151        RAISE EXCEPTION 'All review ratings must be between 1 and 5.';
     1152    END IF;
     1153
     1154    SELECT
     1155        o.customer_user_id,
     1156        lower(os.o_status_name)
     1157    INTO
     1158        v_order_customer_user_id,
     1159        v_order_status
     1160    FROM kbnteam.customer_order o
     1161    JOIN kbnteam.order_status os
     1162      ON os.o_status_id = o.o_status_id
     1163    WHERE o.order_id = p_order_id
     1164    FOR UPDATE OF o;
     1165
     1166    IF NOT FOUND THEN
     1167        RAISE EXCEPTION 'Customer order % does not exist.', p_order_id;
     1168    END IF;
     1169
     1170    IF v_order_customer_user_id <> p_customer_user_id THEN
     1171        RAISE EXCEPTION
     1172            'Customer % is not the owner of order %.',
     1173            p_customer_user_id,
     1174            p_order_id;
     1175    END IF;
     1176
     1177    IF v_order_status <> 'completed' THEN
     1178        RAISE EXCEPTION
     1179            'Order % must be completed before it can be reviewed.',
     1180            p_order_id;
     1181    END IF;
     1182
     1183    IF EXISTS (
     1184        SELECT 1
     1185        FROM kbnteam.order_review review_link
     1186        WHERE review_link.order_id = p_order_id
     1187    ) THEN
     1188        RAISE EXCEPTION 'Order % has already been reviewed.', p_order_id;
     1189    END IF;
     1190
     1191    INSERT INTO kbnteam.review (
     1192        review_comment,
     1193        review_rating
     1194    )
     1195    VALUES (
     1196        p_review_comment,
     1197        p_review_rating
     1198    )
     1199    RETURNING review_id INTO v_review_id;
     1200
     1201    INSERT INTO kbnteam.order_review (
     1202        order_id,
     1203        order_review_food_rating,
     1204        order_review_res_rating,
     1205        review_id
     1206    )
     1207    VALUES (
     1208        p_order_id,
     1209        p_food_rating,
     1210        p_restaurant_rating,
     1211        v_review_id
     1212    );
     1213END;
     1214$$;
     1215}}}
     1216
     1217== Процедура 12: pr_create_delivery_review ==
    2231218
    2241219Бара достава со статус delivered и клиент што има нарачка поврзана со таа достава. Не дозволува втора рецензија за истата достава, независно од клиентот. Запишува општа оценка и коментар, како и оценки за доставувачот и брзината, во опсег од 1 до 5.
    2251220
    226 ||= Параметар ||= Тип ||= Насока ||= Стандардна вредност ||= Опис ||
    227 || `p_delivery_id` || `INTEGER` || IN || — || Идентификатор на доставата. ||
    228 || `p_customer_user_id` || `INTEGER` || IN || — || Идентификатор на клиентот. ||
    229 || `p_review_rating` || `INTEGER` || IN || — || Општа оценка од 1 до 5. ||
    230 || `p_review_comment` || `VARCHAR(255)` || IN || — || Коментар за рецензијата. ||
    231 || `p_courier_rating` || `INTEGER` || IN || — || Оценка за доставувачот од 1 до 5. ||
    232 || `p_speed_rating` || `INTEGER` || IN || — || Оценка за брзината од 1 до 5. ||
    233 
    234 [[Anchor(pr_refresh_customer_loyalty)]]
    235 
    236 === pr_refresh_customer_loyalty ===
     1221=== SQL Дефиниција ===
     1222
     1223{{{
     1224#!sql
     1225CREATE OR REPLACE PROCEDURE kbnteam.pr_create_delivery_review(
     1226    p_delivery_id INTEGER,
     1227    p_customer_user_id INTEGER,
     1228    p_review_rating INTEGER,
     1229    p_review_comment VARCHAR(255),
     1230    p_courier_rating INTEGER,
     1231    p_speed_rating INTEGER
     1232)
     1233LANGUAGE plpgsql
     1234AS $$
     1235DECLARE
     1236    v_delivery_status TEXT;
     1237    v_review_id INTEGER;
     1238BEGIN
     1239    IF p_review_rating NOT BETWEEN 1 AND 5
     1240       OR p_courier_rating NOT BETWEEN 1 AND 5
     1241       OR p_speed_rating NOT BETWEEN 1 AND 5 THEN
     1242        RAISE EXCEPTION 'All review ratings must be between 1 and 5.';
     1243    END IF;
     1244
     1245    SELECT lower(ds.d_status_name)
     1246    INTO v_delivery_status
     1247    FROM kbnteam.delivery d
     1248    JOIN kbnteam.delivery_status ds
     1249      ON ds.d_status_id = d.d_status_id
     1250    WHERE d.delivery_id = p_delivery_id
     1251    FOR UPDATE OF d;
     1252
     1253    IF NOT FOUND THEN
     1254        RAISE EXCEPTION 'Delivery % does not exist.', p_delivery_id;
     1255    END IF;
     1256
     1257    IF v_delivery_status <> 'delivered' THEN
     1258        RAISE EXCEPTION
     1259            'Delivery % must be delivered before it can be reviewed.',
     1260            p_delivery_id;
     1261    END IF;
     1262
     1263    IF NOT EXISTS (
     1264        SELECT 1
     1265        FROM kbnteam.company_order co
     1266        JOIN kbnteam.customer_order o
     1267          ON o.comp_order_id = co.comp_order_id
     1268        WHERE co.delivery_id = p_delivery_id
     1269          AND o.customer_user_id = p_customer_user_id
     1270    ) THEN
     1271        RAISE EXCEPTION
     1272            'Customer % is not associated with delivery %.',
     1273            p_customer_user_id,
     1274            p_delivery_id;
     1275    END IF;
     1276
     1277    IF EXISTS (
     1278        SELECT 1
     1279        FROM kbnteam.delivery_review review_link
     1280        WHERE review_link.delivery_id = p_delivery_id
     1281    ) THEN
     1282        RAISE EXCEPTION
     1283            'Delivery % has already been reviewed.',
     1284            p_delivery_id;
     1285    END IF;
     1286
     1287    INSERT INTO kbnteam.review (
     1288        review_comment,
     1289        review_rating
     1290    )
     1291    VALUES (
     1292        p_review_comment,
     1293        p_review_rating
     1294    )
     1295    RETURNING review_id INTO v_review_id;
     1296
     1297    INSERT INTO kbnteam.delivery_review (
     1298        del_review_courier_rating,
     1299        del_review_speed_rating,
     1300        delivery_id,
     1301        review_id
     1302    )
     1303    VALUES (
     1304        p_courier_rating,
     1305        p_speed_rating,
     1306        p_delivery_id,
     1307        v_review_id
     1308    );
     1309END;
     1310$$;
     1311}}}
     1312
     1313== Процедура 13: pr_refresh_customer_loyalty ==
    2371314
    2381315Ги пресметува поените на клиентот и избира ниво чиј опсег ги содржи поените. При повеќе совпаѓања го избира нивото со најголем долен праг; ако нема совпаѓање, го избира нивото со најмал долен праг. Го користи статусот на лојалност со најмал идентификатор. Создава членство или ги ажурира поените, нивото и статусот на постојното членство.
    2391316
    240 ||= Параметар ||= Тип ||= Насока ||= Стандардна вредност ||= Опис ||
    241 || `p_customer_user_id` || `INTEGER` || IN || — || Идентификатор на клиентот. ||
    242 
    243 == Дозволени промени на статус ==
    244 
    245 === Клиентска нарачка ===
    246 
    247 ||= Тековен статус ||= Дозволени следни статуси ||
    248 || `pending` || `on-hold`, `backorder`, `completed`, `cancelled` ||
    249 || `on-hold` || `pending`, `backorder`, `completed`, `cancelled` ||
    250 || `backorder` || `pending`, `on-hold`, `completed`, `cancelled` ||
    251 || `completed` || `refunded` ||
    252 || `cancelled`, `refunded` || Нема премин во друг статус. ||
    253 
    254 Овие премини се однесуваат на [#pr_change_order_status pr_change_order_status]. За нарачка во фактурирана компаниска нарачка е дозволен само преминот completed → refunded. Создавањето нарачка го користи зададениот почетен статус, а поднесувањето ја поставува на pending според сопствените проверки.
    255 
    256 === Достава ===
    257 
    258 ||= Тековен статус ||= Дозволени следни статуси ||
    259 || `pending` || `assigned`, `cancelled` ||
    260 || `assigned` || `picked up`, `delayed`, `failed`, `cancelled` ||
    261 || `picked up` || `on the way`, `delayed`, `failed`, `cancelled` ||
    262 || `on the way` || `delivered`, `delayed`, `failed` ||
    263 || `delayed` || `assigned`, `picked up`, `on the way`, `delivered`, `failed`, `cancelled` ||
    264 || `delivered`, `failed`, `cancelled` || Нема премин во друг статус. ||
    265 
    266 Овие премини се однесуваат на [#pr_change_delivery_status pr_change_delivery_status]. При создавање достава се користи зададениот почетен статус. Во двете процедури за промена на статус, називите се споредуваат без разлика на големината на буквите и повторување на тековниот статус завршува без измена.
    267 
    268 == Тригери ==
    269 
    270 Тригерите автоматски се активираат при промени на податоците. Сите се извршуваат за секој засегнат ред. Проверката на компанијата се извршува пред промената на клиентската нарачка, а проверките на ставките и пресметката на износот по нивното внесување, измена или бришење.
    271 
    272 ||= Тригер ||= Табела ||= Момент ||= Настан ||
    273 || [#trg_validate_customer_order_company trg_validate_customer_order_company] || `kbnteam.customer_order` || `BEFORE` || `INSERT OR UPDATE OF customer_user_id, comp_order_id` ||
    274 || [#trg_order_meal_guard_and_total trg_order_meal_guard_and_total] || `kbnteam.order_meal` || `AFTER` || `INSERT OR UPDATE OR DELETE` ||
    275 || [#trg_order_drink_guard_and_total trg_order_drink_guard_and_total] || `kbnteam.order_drink` || `AFTER` || `INSERT OR UPDATE OR DELETE` ||
    276 
    277 [[Anchor(trg_validate_customer_order_company)]]
    278 
    279 === trg_validate_customer_order_company ===
     1317=== SQL Дефиниција ===
     1318
     1319{{{
     1320#!sql
     1321CREATE OR REPLACE PROCEDURE kbnteam.pr_refresh_customer_loyalty(
     1322    p_customer_user_id INTEGER
     1323)
     1324LANGUAGE plpgsql
     1325AS $$
     1326DECLARE
     1327    v_points INTEGER;
     1328    v_tier_id INTEGER;
     1329    v_status_id INTEGER;
     1330BEGIN
     1331    IF NOT EXISTS (
     1332        SELECT 1
     1333        FROM kbnteam.customer c
     1334        WHERE c.user_id = p_customer_user_id
     1335    ) THEN
     1336        RAISE EXCEPTION 'Customer % does not exist.', p_customer_user_id;
     1337    END IF;
     1338
     1339    v_points := kbnteam.fn_calculate_customer_loyalty_points(p_customer_user_id);
     1340
     1341    SELECT lt.tier_id
     1342    INTO v_tier_id
     1343    FROM kbnteam.loyalty_tier lt
     1344    WHERE v_points BETWEEN lt.tier_minimum_points AND lt.tier_maximum_points
     1345    ORDER BY lt.tier_minimum_points DESC
     1346    LIMIT 1;
     1347
     1348    IF v_tier_id IS NULL THEN
     1349        SELECT lt.tier_id
     1350        INTO v_tier_id
     1351        FROM kbnteam.loyalty_tier lt
     1352        ORDER BY lt.tier_minimum_points
     1353        LIMIT 1;
     1354    END IF;
     1355
     1356    SELECT cls.cus_loyalty_status_id
     1357    INTO v_status_id
     1358    FROM kbnteam.customer_loyalty_status cls
     1359    ORDER BY cls.cus_loyalty_status_id
     1360    LIMIT 1;
     1361
     1362    IF v_status_id IS NULL THEN
     1363        RAISE EXCEPTION 'No customer loyalty status rows exist.';
     1364    END IF;
     1365
     1366    INSERT INTO kbnteam.customer_loyalty (
     1367        cus_loyalty_curr_points,
     1368        cus_loyalty_status_id,
     1369        user_id,
     1370        tier_id
     1371    )
     1372    VALUES (
     1373        v_points,
     1374        v_status_id,
     1375        p_customer_user_id,
     1376        v_tier_id
     1377    )
     1378    ON CONFLICT (user_id) DO UPDATE
     1379    SET cus_loyalty_curr_points = EXCLUDED.cus_loyalty_curr_points,
     1380        cus_loyalty_status_id = EXCLUDED.cus_loyalty_status_id,
     1381        tier_id = EXCLUDED.tier_id;
     1382END;
     1383$$;
     1384}}}
     1385
     1386== Тригер 1: trg_validate_customer_order_company ==
    2801387
    2811388Проверува дали клиентот и компаниската нарачка постојат и припаѓаат на иста компанија. При неуспешна проверка ја прекинува наредбата со исклучок. При успешна проверка го враќа новиот ред (NEW).
    … …  
    2831390Проверката се активира при внесување нарачка и при ажурирање што ги наведува customer_user_id или comp_order_id. Ажурирање само на order_total или статусот не го активира овој тригер.
    2841391
    285 ||= Својство ||= Вредност ||
    286 || Табела || `kbnteam.customer_order` ||
    287 || Момент || `BEFORE` ||
    288 || Настан || `INSERT OR UPDATE OF customer_user_id, comp_order_id` ||
    289 || Опфат || За секој засегнат ред (FOR EACH ROW). ||
    290 || Тригер-функција || [#tf_validate_customer_order_company tf_validate_customer_order_company] ||
    291 
    292 [[Anchor(trg_order_meal_guard_and_total)]]
    293 
    294 === trg_order_meal_guard_and_total ===
     1392=== SQL Дефиниција ===
     1393
     1394{{{
     1395#!sql
     1396CREATE OR REPLACE FUNCTION kbnteam.tf_validate_customer_order_company()
     1397RETURNS TRIGGER
     1398LANGUAGE plpgsql
     1399AS $$
     1400DECLARE
     1401    v_customer_company_id INTEGER;
     1402    v_order_company_id INTEGER;
     1403BEGIN
     1404    SELECT c.company_id
     1405    INTO v_customer_company_id
     1406    FROM kbnteam.customer c
     1407    WHERE c.user_id = NEW.customer_user_id;
     1408
     1409    SELECT co.company_id
     1410    INTO v_order_company_id
     1411    FROM kbnteam.company_order co
     1412    WHERE co.comp_order_id = NEW.comp_order_id;
     1413
     1414    IF v_customer_company_id IS NULL THEN
     1415        RAISE EXCEPTION 'Customer % does not exist.', NEW.customer_user_id;
     1416    END IF;
     1417
     1418    IF v_order_company_id IS NULL THEN
     1419        RAISE EXCEPTION 'Company order % does not exist.', NEW.comp_order_id;
     1420    END IF;
     1421
     1422    IF v_customer_company_id <> v_order_company_id THEN
     1423        RAISE EXCEPTION
     1424            'Customer % belongs to company %, but company order % belongs to company %.',
     1425            NEW.customer_user_id,
     1426            v_customer_company_id,
     1427            NEW.comp_order_id,
     1428            v_order_company_id;
     1429    END IF;
     1430
     1431    RETURN NEW;
     1432END;
     1433$$;
     1434
     1435DROP TRIGGER IF EXISTS trg_validate_customer_order_company
     1436ON kbnteam.customer_order;
     1437
     1438CREATE TRIGGER trg_validate_customer_order_company
     1439BEFORE INSERT OR UPDATE OF customer_user_id, comp_order_id
     1440ON kbnteam.customer_order
     1441FOR EACH ROW
     1442EXECUTE FUNCTION kbnteam.tf_validate_customer_order_company();
     1443}}}
     1444
     1445== Тригер 2: trg_order_meal_guard_and_total ==
    2951446
    2961447При внесување или измена на ставка проверува дали нарачката е поврзана со валиден клиент и компанија и дали постои активен договор со ресторанот на јадењето на датумот на нарачката. Потоа повторно го пресметува и запишува вкупниот износ на нарачката. При бришење не проверува договор, туку го освежува износот на старата нарачка.
    … …  
    2981449Ако со UPDATE ставката се префрли во друга нарачка, ги освежува износите и на новата и на старата нарачка.
    2991450
    300 ||= Својство ||= Вредност ||
    301 || Табела || `kbnteam.order_meal` ||
    302 || Момент || `AFTER` ||
    303 || Настан || `INSERT OR UPDATE OR DELETE` ||
    304 || Опфат || За секој засегнат ред (FOR EACH ROW). ||
    305 || Тригер-функција || [#tf_order_meal_guard_and_total tf_order_meal_guard_and_total] ||
    306 
    307 Ги користи [#fn_has_active_contract fn_has_active_contract] за проверка на договорот и [#fn_calculate_order_total fn_calculate_order_total] за пресметка на износот. Бидејќи се извршува AFTER, пресметката ја опфаќа изменетата состојба на ставките. Исклучок од проверката ја прекинува тековната наредба.
    308 
    309 [[Anchor(trg_order_drink_guard_and_total)]]
    310 
    311 === trg_order_drink_guard_and_total ===
     1451Проверките за статус на нарачката и постоење фактура се дел од процедурите за додавање и отстранување производи; овој тригер ги проверува договорите и го освежува износот.
     1452
     1453=== SQL Дефиниција ===
     1454
     1455{{{
     1456#!sql
     1457CREATE OR REPLACE FUNCTION kbnteam.tf_order_meal_guard_and_total()
     1458RETURNS TRIGGER
     1459LANGUAGE plpgsql
     1460AS $$
     1461DECLARE
     1462    v_target_order_id INTEGER;
     1463    v_old_order_id INTEGER;
     1464    v_company_id INTEGER;
     1465    v_rest_id INTEGER;
     1466    v_order_date DATE;
     1467BEGIN
     1468    IF TG_OP <> 'DELETE' THEN
     1469        SELECT
     1470            cu.company_id,
     1471            m.rest_id,
     1472            o.order_datetime::date
     1473        INTO
     1474            v_company_id,
     1475            v_rest_id,
     1476            v_order_date
     1477        FROM kbnteam.customer_order o
     1478        JOIN kbnteam.customer cu
     1479          ON cu.user_id = o.customer_user_id
     1480        JOIN kbnteam.meal m
     1481          ON m.meal_id = NEW.meal_id
     1482        WHERE o.order_id = NEW.order_id;
     1483
     1484        IF v_company_id IS NULL THEN
     1485            RAISE EXCEPTION 'Order % is not linked to a valid customer/company.', NEW.order_id;
     1486        END IF;
     1487
     1488        IF NOT kbnteam.fn_has_active_contract(v_company_id, v_rest_id, v_order_date) THEN
     1489            RAISE EXCEPTION
     1490                'Customer company % has no active contract with restaurant % for meal %.',
     1491                v_company_id,
     1492                v_rest_id,
     1493                NEW.meal_id;
     1494        END IF;
     1495
     1496        v_target_order_id := NEW.order_id;
     1497    ELSE
     1498        v_target_order_id := OLD.order_id;
     1499    END IF;
     1500
     1501    UPDATE kbnteam.customer_order o
     1502    SET order_total = kbnteam.fn_calculate_order_total(v_target_order_id)
     1503    WHERE o.order_id = v_target_order_id;
     1504
     1505    IF TG_OP = 'UPDATE' AND OLD.order_id <> NEW.order_id THEN
     1506        v_old_order_id := OLD.order_id;
     1507
     1508        UPDATE kbnteam.customer_order o
     1509        SET order_total = kbnteam.fn_calculate_order_total(v_old_order_id)
     1510        WHERE o.order_id = v_old_order_id;
     1511    END IF;
     1512
     1513    RETURN COALESCE(NEW, OLD);
     1514END;
     1515$$;
     1516
     1517DROP TRIGGER IF EXISTS trg_order_meal_guard_and_total
     1518ON kbnteam.order_meal;
     1519
     1520CREATE TRIGGER trg_order_meal_guard_and_total
     1521AFTER INSERT OR UPDATE OR DELETE
     1522ON kbnteam.order_meal
     1523FOR EACH ROW
     1524EXECUTE FUNCTION kbnteam.tf_order_meal_guard_and_total();
     1525}}}
     1526
     1527== Тригер 3: trg_order_drink_guard_and_total ==
    3121528
    3131529При внесување или измена на ставка проверува дали нарачката е поврзана со валиден клиент и компанија и дали постои активен договор со ресторанот на пијалакот на датумот на нарачката. Потоа повторно го пресметува и запишува вкупниот износ на нарачката. При бришење не проверува договор, туку го освежува износот на старата нарачка.
    … …  
    3151531Ако со UPDATE ставката се префрли во друга нарачка, ги освежува износите и на новата и на старата нарачка.
    3161532
    317 ||= Својство ||= Вредност ||
    318 || Табела || `kbnteam.order_drink` ||
    319 || Момент || `AFTER` ||
    320 || Настан || `INSERT OR UPDATE OR DELETE` ||
    321 || Опфат || За секој засегнат ред (FOR EACH ROW). ||
    322 || Тригер-функција || [#tf_order_drink_guard_and_total tf_order_drink_guard_and_total] ||
    323 
    324 Ги користи [#fn_has_active_contract fn_has_active_contract] за проверка на договорот и [#fn_calculate_order_total fn_calculate_order_total] за пресметка на износот. Бидејќи се извршува AFTER, пресметката ја опфаќа изменетата состојба на ставките. Исклучок од проверката ја прекинува тековната наредба.
    325 
    326 === Тригер-функции ===
    327 
    328 Тригер-функциите се напишани на PL/pgSQL и враќаат тип TRIGGER. Немаат декларирани влезни параметри; ги користат податоците за активирањето и редовите NEW и OLD. Нивното извршување го повикува соодветниот тригер.
    329 
    330 [[Anchor(tf_validate_customer_order_company)]]
    331 
    332 ==== tf_validate_customer_order_company ====
    333 
    334 ||= Својство ||= Вредност ||
    335 || Повратен тип || `TRIGGER` ||
    336 || Активиран преку || [#trg_validate_customer_order_company trg_validate_customer_order_company] ||
    337 || Однесување || Го враќа NEW по успешна проверка на компанијата. ||
    338 
    339 [[Anchor(tf_order_meal_guard_and_total)]]
    340 
    341 ==== tf_order_meal_guard_and_total ====
    342 
    343 ||= Својство ||= Вредност ||
    344 || Повратен тип || `TRIGGER` ||
    345 || Активиран преку || [#trg_order_meal_guard_and_total trg_order_meal_guard_and_total] ||
    346 || Однесување || Го враќа COALESCE(NEW, OLD). Кај AFTER тригерот повратниот ред не го заменува запишаниот ред; ефектот е проверката и ажурирањето на износот. ||
    347 
    348 [[Anchor(tf_order_drink_guard_and_total)]]
    349 
    350 ==== tf_order_drink_guard_and_total ====
    351 
    352 ||= Својство ||= Вредност ||
    353 || Повратен тип || `TRIGGER` ||
    354 || Активиран преку || [#trg_order_drink_guard_and_total trg_order_drink_guard_and_total] ||
    355 || Однесување || Го враќа COALESCE(NEW, OLD). Кај AFTER тригерот повратниот ред не го заменува запишаниот ред; ефектот е проверката и ажурирањето на износот. ||
    356 
    357 === Поврзаност со процедурите ===
    358 
    359 Проверките за статус на нарачката и за постоење фактура се дел од процедурите за додавање и отстранување производи. Тригерите за ставки ги проверуваат договорите и го освежуваат износот, но не ги повторуваат тие проверки за статус и фактурирање.
    360 
    361 Процедурите за додавање и отстранување производи исто така го пресметуваат износот. Кога тригерите се активни, пресметката се извршува и автоматски при промената на ставките. Ажурирањето на order_total не го активира тригерот за проверка на компанијата.
    362 
    363 == Поврзаност меѓу операциите ==
    364 
    365 Компаниската нарачка ги обединува клиентските нарачки на една компанија. Во клиентските нарачки се додаваат производи, се проверуваат договорите и се пресметуваат износите. Завршените нарачки можат да се оценуваат, а компаниската нарачка се финализира со создавање фактура. Доставата има сопствен тек на статуси и може да се оценува откако ќе биде доставена.
    366 
    367 === Помошни пресметки и проверки ===
    368 
    369 ||= Функција ||= Улога ||
    370 || [#fn_has_active_contract fn_has_active_contract] || Проверува дали за компанијата и ресторанот постои договор со статус active што го опфаќа зададениот датум. ||
    371 || [#fn_calculate_order_total fn_calculate_order_total] || Ги собира тековните цени на јадењата и пијалоците во нарачката. ||
    372 || [#fn_calculate_customer_loyalty_points fn_calculate_customer_loyalty_points] || Го заокружува надолу збирот на износите на нарачките на клиентот, при што нарачките со статус cancelled придонесуваат со нула. Останатите статуси, вклучително refunded, се вклучени во тековната пресметка. ||
    373 
    374 Процедурата [#pr_change_order_status pr_change_order_status] ја повикува [#pr_refresh_customer_loyalty pr_refresh_customer_loyalty] при промена во completed, cancelled или refunded. Освежувањето бара постојни нивоа и статуси на лојалност.
     1533Проверките за статус на нарачката и постоење фактура се дел од процедурите за додавање и отстранување производи; овој тригер ги проверува договорите и го освежува износот.
     1534
     1535=== SQL Дефиниција ===
     1536
     1537{{{
     1538#!sql
     1539CREATE OR REPLACE FUNCTION kbnteam.tf_order_drink_guard_and_total()
     1540RETURNS TRIGGER
     1541LANGUAGE plpgsql
     1542AS $$
     1543DECLARE
     1544    v_target_order_id INTEGER;
     1545    v_old_order_id INTEGER;
     1546    v_company_id INTEGER;
     1547    v_rest_id INTEGER;
     1548    v_order_date DATE;
     1549BEGIN
     1550    IF TG_OP <> 'DELETE' THEN
     1551        SELECT
     1552            cu.company_id,
     1553            d.rest_id,
     1554            o.order_datetime::date
     1555        INTO
     1556            v_company_id,
     1557            v_rest_id,
     1558            v_order_date
     1559        FROM kbnteam.customer_order o
     1560        JOIN kbnteam.customer cu
     1561          ON cu.user_id = o.customer_user_id
     1562        JOIN kbnteam.drink d
     1563          ON d.drink_id = NEW.drink_id
     1564        WHERE o.order_id = NEW.order_id;
     1565
     1566        IF v_company_id IS NULL THEN
     1567            RAISE EXCEPTION 'Order % is not linked to a valid customer/company.', NEW.order_id;
     1568        END IF;
     1569
     1570        IF NOT kbnteam.fn_has_active_contract(v_company_id, v_rest_id, v_order_date) THEN
     1571            RAISE EXCEPTION
     1572                'Customer company % has no active contract with restaurant % for drink %.',
     1573                v_company_id,
     1574                v_rest_id,
     1575                NEW.drink_id;
     1576        END IF;
     1577
     1578        v_target_order_id := NEW.order_id;
     1579    ELSE
     1580        v_target_order_id := OLD.order_id;
     1581    END IF;
     1582
     1583    UPDATE kbnteam.customer_order o
     1584    SET order_total = kbnteam.fn_calculate_order_total(v_target_order_id)
     1585    WHERE o.order_id = v_target_order_id;
     1586
     1587    IF TG_OP = 'UPDATE' AND OLD.order_id <> NEW.order_id THEN
     1588        v_old_order_id := OLD.order_id;
     1589
     1590        UPDATE kbnteam.customer_order o
     1591        SET order_total = kbnteam.fn_calculate_order_total(v_old_order_id)
     1592        WHERE o.order_id = v_old_order_id;
     1593    END IF;
     1594
     1595    RETURN COALESCE(NEW, OLD);
     1596END;
     1597$$;
     1598
     1599DROP TRIGGER IF EXISTS trg_order_drink_guard_and_total
     1600ON kbnteam.order_drink;
     1601
     1602CREATE TRIGGER trg_order_drink_guard_and_total
     1603AFTER INSERT OR UPDATE OR DELETE
     1604ON kbnteam.order_drink
     1605FOR EACH ROW
     1606EXECUTE FUNCTION kbnteam.tf_order_drink_guard_and_total();
     1607}}}