Changes between Version 1 and Version 2 of DatabaseProgramming

Timestamp:

06/16/26 00:49:02 (5 days ago)

Author:

231073

Comment:

--

DatabaseProgramming

@@ -4 +4 @@
 === ФУНКЦИИ
 * has_active_membership: Проверува дали даден член има активна и важечка членарина.
+{{{
+CREATE OR REPLACE FUNCTION has_active_membership(p_user_id INT)
+RETURNS BOOLEAN AS $$
+BEGIN
+    RETURN EXISTS (
+        SELECT 1
+        FROM membership
+        WHERE member_user_id = p_user_id
+          AND status = 'active'
+          AND end_date >= CURRENT_DATE
+    );
+END;
+$$ LANGUAGE plpgsql;
+}}}
 * get_available_copies: Го враќа бројот на достапни копии за одредена книга.
+{{{
+CREATE OR REPLACE FUNCTION get_available_copies(p_barcode VARCHAR)
+RETURNS INT AS $$
+DECLARE
+    v_count INT;
+BEGIN
+    SELECT COUNT(*)
+    INTO v_count
+    FROM book_copy
+    WHERE barcode = p_barcode
+      AND status = 'available';
+
+    RETURN v_count;
+END;
+$$ LANGUAGE plpgsql;
+
+}}}
 * calculate_fine: Ја пресметува казната за задоцнето враќање на книга според бројот на денови доцнење.
+{{{
+CREATE OR REPLACE FUNCTION calculate_fine(p_loan_id INTEGER)
+RETURNS INTEGER AS $$
+DECLARE
+    v_due_date DATE;
+    v_return_date DATE;
+    v_days_late INT;
+    v_fine INT;
+BEGIN
+    SELECT due_date, return_date
+    INTO v_due_date, v_return_date
+    FROM loan_history
+    WHERE loan_id = p_loan_id;
+
+    IF v_return_date IS NULL THEN
+        RETURN 0;
+    END IF;
+
+    v_days_late := v_return_date - v_due_date;
+
+    IF v_days_late <= 0 THEN
+        RETURN 0;
+    END IF;
+
+    v_fine := v_days_late * 20;
+
+    RETURN v_fine;
+END;
+$$ LANGUAGE plpgsql;
+}}}
 === ПРОЦЕДУРИ
 * borrow_book: Овозможува позајмување книга со проверки за членство, библиотекар и достапна копија.
+{{{
+CREATE OR REPLACE PROCEDURE borrow_book(
+    p_user_id INT,
+    p_barcode VARCHAR,
+    p_librarian_id INT
+) AS $$
+DECLARE
+    v_copy_id INT;
+BEGIN
+    IF NOT EXISTS (
+        SELECT 1
+        FROM member
+        WHERE user_id = p_user_id
+    ) THEN
+        RAISE EXCEPTION 'User is not a member.';
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1
+        FROM librarian
+        WHERE user_id = p_librarian_id
+    ) THEN
+        RAISE EXCEPTION 'User is not a librarian.';
+    END IF;
+
+    IF NOT has_active_membership(p_user_id) THEN
+        RAISE EXCEPTION 'Member does not have an active membership.';
+    END IF;
+
+    SELECT copy_id
+    INTO v_copy_id
+    FROM book_copy
+    WHERE barcode = p_barcode
+      AND status = 'available'
+    LIMIT 1
+    FOR UPDATE;
+
+    IF v_copy_id IS NULL THEN
+        RAISE EXCEPTION 'No available copies for this book.';
+    END IF;
+
+    INSERT INTO loan_history (
+        member_user_id,
+        copy_id,
+        librarian_user_id,
+        borrow_date,
+        due_date,
+        status
+    )
+    VALUES (
+        p_user_id,
+        v_copy_id,
+        p_librarian_id,
+        CURRENT_DATE,
+        CURRENT_DATE + INTERVAL '14 days',
+        'borrowed'
+    );
+
+    UPDATE book_copy
+    SET status = 'borrowed'
+    WHERE copy_id = v_copy_id;
+
+    RAISE NOTICE 'Book borrowed successfully. Copy ID: %', v_copy_id;
+END;
+$$ LANGUAGE plpgsql;
+}}}
 * return_book: Евидентира враќање на книга и ја прави копијата повторно достапна.
+{{{
+CREATE OR REPLACE PROCEDURE return_book(
+    IN p_copy_id INTEGER,
+    IN p_is_damaged BOOLEAN
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_loan_id INTEGER;
+BEGIN
+    UPDATE loan_history
+    SET return_date = CURRENT_DATE,
+        status = 'returned'
+    WHERE copy_id = p_copy_id
+      AND status = 'borrowed'
+    RETURNING loan_id INTO v_loan_id;
+
+    IF v_loan_id IS NULL THEN
+        RAISE NOTICE 'No active borrowed loan found for copy %.', p_copy_id;
+        RETURN;
+    END IF;
+
+    IF p_is_damaged = TRUE THEN
+        UPDATE book_copy
+        SET status = 'damaged'
+        WHERE copy_id = p_copy_id;
+    ELSE
+        UPDATE book_copy
+        SET status = 'available'
+        WHERE copy_id = p_copy_id;
+    END IF;
+
+    RAISE NOTICE 'Book copy % returned successfully.', p_copy_id;
+END;
+$$;
+}}}
 * reserve_event: Креира резервација за настан и го намалува бројот на слободни места.
+{{{
+CREATE OR REPLACE PROCEDURE reserve_event(
+    p_event_id INT,
+    p_member_id INT,
+    p_seats INT
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_available_seats INT;
+BEGIN
+    SELECT available_seats
+    INTO v_available_seats
+    FROM event
+    WHERE event_id = p_event_id;
+
+    IF NOT FOUND THEN
+        RAISE EXCEPTION 'Event with ID % does not exist.', p_event_id;
+    END IF;
+
+    IF v_available_seats < p_seats THEN
+        RAISE EXCEPTION 'Not enough available seats.';
+    END IF;
+
+    INSERT INTO event_reservation(
+        event_id,
+        member_user_id,
+        seats_reserved,
+        status
+    )
+    VALUES(
+        p_event_id,
+        p_member_id,
+        p_seats,
+        'reserved'
+    );
+
+    UPDATE event
+    SET available_seats = available_seats - p_seats
+    WHERE event_id = p_event_id;
+END;
+$$;
+}}}
+
 * cancel_event_reservation: Откажува резервација и ги враќа резервираните места.
+{{{
+CREATE OR REPLACE PROCEDURE cancel_event_reservation(
+    p_event_reservation_id INT
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_event_id INT;
+    v_seats INT;
+    v_current_status event_reservation_status;
+BEGIN
+    SELECT event_id, seats_reserved, status
+    INTO v_event_id, v_seats, v_current_status
+    FROM event_reservation
+    WHERE event_reservation_id = p_event_reservation_id;
+
+    IF NOT FOUND THEN
+        RAISE EXCEPTION 'Reservation with ID % does not exist!', p_event_reservation_id;
+    END IF;
+
+    IF v_current_status = 'cancelled' THEN
+        RAISE EXCEPTION 'Reservation is already cancelled!';
+    END IF;
+
+    UPDATE event_reservation
+    SET status = 'cancelled'
+    WHERE event_reservation_id = p_event_reservation_id;
+
+    UPDATE event
+    SET available_seats = available_seats + v_seats
+    WHERE event_id = v_event_id;
+END;
+$$;
+}}}
 * create_event: Креира нов настан по проверка на библиотекарот, времето и бројот на места.
+{{{
+CREATE OR REPLACE PROCEDURE create_event(
+    p_title VARCHAR,
+    p_description TEXT,
+    p_event_date DATE,
+    p_start_time TIME,
+    p_end_time TIME,
+    p_location VARCHAR,
+    p_max_seats INT,
+    p_created_by INT
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+
+    IF NOT EXISTS (
+        SELECT 1
+        FROM librarian
+        WHERE user_id = p_created_by
+    ) THEN
+        RAISE EXCEPTION 'Librarian with ID % does not exist!', p_created_by;
+    END IF;
+
+
+    IF p_end_time <= p_start_time THEN
+        RAISE EXCEPTION 'End time must be greater than start time!';
+    END IF;
+
+
+    IF p_max_seats <= 0 THEN
+        RAISE EXCEPTION 'Max seats must be greater than 0!';
+    END IF;
+
+
+    INSERT INTO event (
+        title,
+        description,
+        event_date,
+        start_time,
+        end_time,
+        location,
+        max_seats,
+        available_seats,
+        created_by
+    )
+    VALUES (
+        p_title,
+        p_description,
+        p_event_date,
+        p_start_time,
+        p_end_time,
+        p_location,
+        p_max_seats,
+        p_max_seats,
+        p_created_by
+    );
+
+END;
+$$;
+}}}
 === ТРИГЕРИ
 * trigger_update_copy_status: Автоматски го ажурира статусот на копијата при позајмување или враќање.
+{{{
+CREATE OR REPLACE FUNCTION fn_update_copy_status()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF (TG_OP = 'INSERT') THEN
+        UPDATE book_copy
+        SET status = 'borrowed'
+        WHERE copy_id = NEW.copy_id;
+
+    ELSIF (NEW.status = 'returned') THEN
+        UPDATE book_copy
+        SET status = 'available'
+        WHERE copy_id = NEW.copy_id;
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+}}}
+{{{
+CREATE TRIGGER trigger_update_copy_status
+AFTER INSERT OR UPDATE ON loan_history
+FOR EACH ROW
+EXECUTE FUNCTION fn_update_copy_status();
+}}}
 * trigger_create_fine: Автоматски креира казна кога книгата е вратена по рокот.
+{{{
+CREATE OR REPLACE FUNCTION trigger_create_fine_function()
+RETURNS TRIGGER AS $$
+DECLARE
+    v_amount INT;
+BEGIN
+    IF NEW.return_date IS NOT NULL
+       AND NEW.return_date > NEW.due_date THEN
+
+        v_amount := calculate_fine(NEW.loan_id);
+
+        IF v_amount > 0 THEN
+            INSERT INTO fines(
+                loan_id,
+                amount,
+                payment_due_date,
+                status
+            )
+            VALUES(
+                NEW.loan_id,
+                v_amount,
+                CURRENT_DATE + INTERVAL '7 days',
+                'unpaid'
+            );
+        END IF;
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+}}}
+{{{
+CREATE TRIGGER trigger_create_fine
+AFTER UPDATE OF return_date ON loan_history
+FOR EACH ROW
+EXECUTE FUNCTION trigger_create_fine_function();
+}}}
 * trigger_notification_on_fine: Испраќа нотификација до членот при креирање казна.
+{{{
+CREATE OR REPLACE FUNCTION trigger_notification_on_fine_function()
+RETURNS TRIGGER AS $$
+DECLARE
+    v_member_id INT;
+    v_notification_id INT;
+BEGIN
+    SELECT member_user_id
+    INTO v_member_id
+    FROM loan_history
+    WHERE loan_id = NEW.loan_id;
+
+    INSERT INTO notification(
+        member_user_id,
+        notification_type,
+        title,
+        message,
+        status
+    )
+    VALUES(
+        v_member_id,
+        'fine',
+        'New Fine Created',
+        'You have received a fine for returning a book late.',
+        'pending'
+    )
+    RETURNING notification_id INTO v_notification_id;
+
+    INSERT INTO fines_notification(
+        fine_id,
+        notification_id
+    )
+    VALUES(
+        NEW.fine_id,
+        v_notification_id
+    );
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+}}}
+
+{{{
+CREATE TRIGGER trigger_notification_on_fine
+AFTER INSERT ON fines
+FOR EACH ROW
+EXECUTE FUNCTION trigger_notification_on_fine_function();
+}}}
 * trigger_activate_reservation: Активира резервација и испраќа известување кога книгата станува достапна.
+{{{
+CREATE OR REPLACE FUNCTION trigger_activate_reservation_function()
+RETURNS TRIGGER AS $$
+DECLARE
+    v_barcode VARCHAR(13);
+    v_reservation_id INT;
+    v_member_id INT;
+BEGIN
+    SELECT barcode
+    INTO v_barcode
+    FROM book_copy
+    WHERE copy_id = NEW.copy_id;
+
+    SELECT reservation_id, member_user_id
+    INTO v_reservation_id, v_member_id
+    FROM reservation
+    WHERE barcode = v_barcode
+      AND status = 'pending'
+    ORDER BY reservation_timestamp
+    LIMIT 1;
+
+    IF v_reservation_id IS NOT NULL THEN
+
+        UPDATE reservation
+        SET status = 'active',
+            notified_at = CURRENT_TIMESTAMP
+        WHERE reservation_id = v_reservation_id;
+
+        INSERT INTO notification(
+            member_user_id,
+            notification_type,
+            title,
+            message,
+            status
+        )
+        VALUES(
+            v_member_id,
+            'reservation',
+            'Reserved Book Available',
+            'The book you reserved is now available.',
+            'pending'
+        );
+
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+}}}
+
+{{{
+CREATE TRIGGER trigger_activate_reservation
+AFTER UPDATE OF status ON book_copy
+FOR EACH ROW
+WHEN (NEW.status = 'available')
+EXECUTE FUNCTION trigger_activate_reservation_function();
+}}}