Функции, процедури и тригери

Во овој дел се прикажани SQL функциите, процедурите и тригер-функциите кои се користат во системот за управување со библиотека. Тие ја имплементираат главната бизнис логика на апликацијата, како што се позајмување книги, враќање книги, регистрација на членови, резервација на книги, автоматска проверка на членство и автоматско креирање казна при задоцнето враќање.

1. Функции

1.1 borrow_book

CREATE OR REPLACE FUNCTION public.borrow_book(
p_borrowing_id bigint,
p_member_id bigint,
p_copy_id bigint,
p_employee_id bigint
)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
v_available INTEGER;
v_membership_status VARCHAR;
BEGIN

```
-- Проверка дали книгата е достапна
SELECT available
INTO v_available
FROM bookcopy
WHERE copy_id = p_copy_id;

IF v_available = 0 THEN
    RETURN 'Book copy is not available';
END IF;

-- Проверка дали членот е активен
SELECT membership_status
INTO v_membership_status
FROM member
WHERE member_id = p_member_id;

IF v_membership_status != 'ACTIVE' THEN
    RETURN 'Member is not active';
END IF;

-- Креирање borrowing
INSERT INTO borrowing(
    borrowing_id,
    book_id,
    borrow_date,
    due_date,
    copy_id,
    membership_id,
    borrowed_by
)
VALUES(
    p_borrowing_id,
    (SELECT book_id
     FROM bookcopy
     WHERE copy_id = p_copy_id),
    CURRENT_DATE,
    CURRENT_DATE + INTERVAL '14 days',
    p_copy_id,
    p_member_id,
    p_employee_id
);

-- Update availability
UPDATE bookcopy
SET available = 0
WHERE copy_id = p_copy_id;

RETURN 'Book borrowed successfully';
```

END;
$function$;

Опис: Функцијата borrow_book се користи за позајмување конкретна копија од книга. Најпрво проверува дали копијата е достапна, а потоа проверува дали членот има активен статус. Ако условите се исполнети, се внесува нов запис во табелата borrowing и копијата се означува како недостапна.

Употреба: Оваа функција ја имплементира главната логика за позајмување книга во библиотеката. Се користи за да се спречи позајмување на недостапна книга или позајмување од член кој нема активен статус. Со тоа се обезбедува контрола врз процесот на позајмување и ажурирање на достапноста на книгите.

1.2 register_member

CREATE OR REPLACE FUNCTION public.register_member(
p_member_id bigint,
p_first_name character varying,
p_last_name character varying,
p_email character varying,
p_phone character varying,
p_address text
)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
v_existing_email INTEGER;
v_existing_member INTEGER;
BEGIN

```
-- check for existing member
SELECT COUNT(*)
INTO v_existing_member
FROM member
WHERE member_id = p_member_id;

IF v_existing_member > 0 THEN
    RETURN 'Member is already registered';
END IF;

-- check for existing email
SELECT COUNT(*)
INTO v_existing_email
FROM member
WHERE email = p_email;

IF v_existing_email > 0 THEN
    RETURN 'Email already exists';
END IF;

-- create new member
INSERT INTO member(
    member_id,
    first_name,
    last_name,
    email,
    phone,
    address,
    membership_status,
    membership_start_date,
    membership_end_date
)
VALUES(
    p_member_id,
    p_first_name,
    p_last_name,
    p_email,
    p_phone,
    p_address,
    'ACTIVE',
    CURRENT_DATE,
    CURRENT_DATE + INTERVAL '1 year'
);

RETURN 'Member registered successfully';
```

END;
$function$;

Опис: Функцијата register_member се користи за регистрација на нов член во библиотеката. Најпрво проверува дали веќе постои член со истиот member_id, а потоа проверува дали внесениот email веќе се користи. Ако членот и email адресата не постојат, се креира нов член со статус ACTIVE и членство со важност од една година.

Употреба: Оваа функција ја имплементира логиката за регистрација на корисници во библиотечниот систем. Се користи за да се спречи дуплирање на членови и користење на иста email адреса од повеќе корисници. На овој начин системот обезбедува правилна евиденција на членовите.

1.3 return_book

CREATE OR REPLACE FUNCTION public.return_book(p_borrowing_id bigint)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
v_due_date DATE;
v_copy_id BIGINT;
v_return_date DATE;
v_late_days INTEGER;
v_status_id INTEGER;
BEGIN

```
-- take info from borrowing
SELECT due_date, copy_id, return_date
INTO v_due_date, v_copy_id, v_return_date
FROM borrowing
WHERE borrowing_id = p_borrowing_id;

-- check for existing borrow
IF v_copy_id IS NULL THEN
    RETURN 'Borrowing record not found';
END IF;

-- check if book is returned
IF v_return_date IS NOT NULL THEN
    RETURN 'Book already returned';
END IF;

-- Update return date
UPDATE borrowing
SET return_date = CURRENT_DATE
WHERE borrowing_id = p_borrowing_id;

-- bookstatus
SELECT status_id
INTO v_status_id
FROM bookcopy
WHERE copy_id = v_copy_id;

-- check for dameged book
IF v_status_id = 5 THEN
    UPDATE bookcopy
    SET available = 0
    WHERE copy_id = v_copy_id;

    RETURN 'Book returned with damage';
END IF;

-- book is available
UPDATE bookcopy
SET available = 1,
    status_id = 1
WHERE copy_id = v_copy_id;

-- check for late retun
IF v_due_date IS NOT NULL THEN
    v_late_days := CURRENT_DATE - v_due_date;

    IF v_late_days > 0 THEN
        RETURN 'Book returned late by ' || v_late_days || ' days';
    END IF;
END IF;

RETURN 'Book returned successfully';
```

END;
$function$;

Опис: Функцијата return_book се користи при враќање на позајмена книга. Таа проверува дали постои запис за позајмување, дали книгата веќе е вратена и потоа го ажурира датумот на враќање. Дополнително, проверува дали книгата е оштетена и дали е вратена со задоцнување.

Употреба: Оваа функција ја имплементира логиката за враќање книги во библиотеката. Ако книгата е вратена во добра состојба, копијата повторно се означува како достапна. Ако книгата е оштетена, таа останува недостапна, а ако е вратена по рокот, системот враќа информација за бројот на денови на доцнење.

Забелешка: Во оваа функција оштетена книга се проверува со status_id = 5. Ако во базата оштетените книги се означуваат со друга вредност, на пример status_id = 2, тогаш условот треба да се усогласи со вистинската вредност во табелата bookcopy.

2. Процедури

2.1 sp_borrow_book

CREATE OR REPLACE PROCEDURE public.sp_borrow_book(
IN p_book_id integer,
IN p_membership_id integer,
IN p_due_date date,
IN p_borrowed_by integer
)
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_copy_id INT;
BEGIN

```
-- free copy
SELECT copy_id
INTO v_copy_id
FROM bookcopy
WHERE book_id = p_book_id
  AND available = 1
LIMIT 1;

-- doesnt have-error
IF v_copy_id IS NULL THEN
    RAISE EXCEPTION 'No available copy for this book!';
END IF;

-- borrowing row
INSERT INTO borrowing(
    book_id,
    borrow_date,
    return_date,
    due_date,
    return_status,
    copy_id,
    membership_id,
    borrowed_by,
    returned_by,
    reservation_id
)
VALUES (
    p_book_id,
    CURRENT_DATE,
    null,
    p_due_date,
    NULL,
    v_copy_id,
    p_membership_id,
    p_borrowed_by,
    NULL,
    NULL
);

-- not available
UPDATE bookcopy
SET available = 0
WHERE copy_id = v_copy_id;
```

END;
$procedure$;

Опис: Процедурата sp_borrow_book се користи за позајмување книга според book_id. Таа најпрво бара слободна копија од дадената книга во табелата bookcopy. Ако нема достапна копија, се јавува грешка, а ако има, се креира запис во borrowing и копијата се означува како недостапна.

Употреба: Оваа процедура ја имплементира логиката за автоматско избирање достапна копија при позајмување книга. Корисникот или библиотекарот не мора рачно да избира конкретен copy_id, туку системот сам ја наоѓа првата достапна копија. Ова ја поедноставува работата на библиотекарот и го намалува ризикот од грешки.

2.2 sp_reserve_book

CREATE OR REPLACE PROCEDURE public.sp_reserve_book(
IN p_member_id integer,
IN p_book_id integer
)
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_copy_id INT;
BEGIN

```
-- check for free copy
SELECT copy_id
INTO v_copy_id
FROM bookcopy
WHERE book_id = p_book_id
  AND available = 1
LIMIT 1;

--if doesnt have copy, make reservation
IF v_copy_id IS NULL THEN

    INSERT INTO reservation(
        member_id,
        book_id,
        requested_date,
        expected_date,
        status,
        copy_id
    )
    VALUES (
        p_member_id,
        p_book_id,
        CURRENT_DATE,
        CURRENT_DATE + 7,
        'waiting',
        NULL
    );

ELSE

    RAISE NOTICE 'Book is available. No reservation needed.';

END IF;
```

END;
$procedure$;

Опис: Процедурата sp_reserve_book се користи за резервација на книга. Најпрво проверува дали постои достапна копија од книгата. Ако нема достапна копија, се креира запис во табелата reservation со статус waiting.

Употреба: Оваа процедура ја имплементира логиката за резервација на книги кои моментално не се достапни. Ако книгата има слободна копија, резервација не е потребна и системот прикажува известување. Ако книгата нема слободна копија, членот се става на листа на чекање.

3. Trigger функции

3.1 fn_create_fine_on_return

CREATE OR REPLACE FUNCTION public.fn_create_fine_on_return()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
days_late INT;
total_amount INT;
fine_per_day INT := 10;
BEGIN

```
IF NEW.return_date IS NOT NULL
   AND NEW.return_date > NEW.due_date THEN

    days_late := NEW.return_date - NEW.due_date;
    total_amount := days_late * fine_per_day;

    INSERT INTO fine(
        fine_id,
        membership_id,
        borrowing_id,
        amount,
        created_date,
        payment_id,
        fee_type_id
    )
    VALUES (
        (SELECT COALESCE(MAX(fine_id),0)+1 FROM fine),
        NEW.membership_id,
        NEW.borrowing_id,
        total_amount,
        CURRENT_DATE,
        NULL,
        1
    );

END IF;

RETURN NEW;
```

END;
$function$;

Опис: Trigger функцијата fn_create_fine_on_return служи за автоматско креирање казна при задоцнето враќање на книга. Таа проверува дали return_date е поголем од due_date. Ако книгата е вратена по рокот, се пресметува бројот на задоцнети денови и се внесува нов запис во табелата fine.

Употреба: Оваа trigger функција ја имплементира логиката за автоматска наплата на казни. Се користи за да не мора библиотекарот рачно да пресметува казна при секое задоцнето враќање. Висината на казната се пресметува според бројот на задоцнети денови, при што во функцијата е поставена вредност од 10 по ден.

3.2 check_membership_status

CREATE OR REPLACE FUNCTION public.check_membership_status()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN

```
--if membership is expiredd
IF NEW.membership_end_date < CURRENT_DATE THEN
    NEW.membership_status := 'EXPIRED';

--if doesn't
ELSE
    NEW.membership_status := 'ACTIVE';
END IF;

RETURN NEW;
```

END;
$function$;

Опис: Trigger функцијата check_membership_status се користи за автоматска проверка на статусот на членството. Ако датумот membership_end_date е помал од тековниот датум, статусот на членот се поставува на EXPIRED. Во спротивно, статусот се поставува на ACTIVE.

Употреба: Оваа trigger функција ја имплементира логиката за автоматско ажурирање на статусот на членството. Се користи за системот сам да одреди дали членот има активно или истечено членство. На овој начин се избегнува рачно ажурирање на статусот на членовите.