Changes between Version 2 and Version 3 of DatabaseProgramming

Timestamp:

05/06/26 13:27:13 (3 weeks ago)

Author:

231035

Comment:

--

DatabaseProgramming

@@ -68 +68 @@
     execute function trg_review_only_completed_booking();
 }}}
-== Only an available room can be booked
-{{{
-
-create or replace function trg_room_is_available_for_booking()
-returns trigger
-as $$
-    begin
-    if new.check_in_date > new.check_out_date then
-        raise exception 'Booking start date cannot be after end date';
-    end if;
-
-
-    if not exists(
-        select 1
+== Check if a room is available
+{{{
+create or replace function fn_is_room_available(p_room_id bigint,p_check_in_date date, p_check_out_date date)
+returns boolean
+language plpgsql
+as $$
+    begin
+        if p_check_in_date >= p_check_out_date then
+            return false;
+        end if;
+
+        if not exists(
+            select 1
+            from rooms r
+            where r.room_id = p_room_id
+            and r.status = 'ACTIVE'
+        )then
+            return false;
+        end if;
+
+        if exists(
+            select 1
+            from bookings b
+            where b.room_id = p_room_id
+            and b.booking_status in ('PENDING','CONFIRMED')
+            and daterange(b.check_in_date,b.check_out_date,'[)')
+            && daterange(p_check_in_date,p_check_out_date,'[)')
+        )then
+            return false;
+        end if;
+        return true;
+    end;
+    $$;
+}}}
+== Calculate the number of nights stayed
+{{{
+create or replace function fn_calculate_nights(p_check_in_date date, p_check_out_date date)
+returns int
+language plpgsql
+as $$
+    begin
+        if p_check_in_date >= p_check_out_date then
+            raise exception 'Check out must be after check in';
+        end if;
+        return p_check_out_date - p_check_in_date;
+    end;
+    $$;
+}}}
+== Calculate the total price of a booking
+{{{
+create or replace function fn_calculate_booking_price(p_room_id bigint, p_check_in_date date,p_check_out_date date,
+p_guest_count int)
+returns numeric
+language plpgsql
+as $$
+    declare
+        v_price_per_night numeric;
+        v_capacity int;
+        v_extra_capacity int;
+        v_extra_guest_price numeric;
+
+        v_nights int;
+        v_room_total numeric;
+        v_amenities_total numeric;
+        v_extra_guests int;
+        v_extra_guest_total numeric;
+        v_total numeric;
+    begin
+        select
+            r.price_per_night,
+            r.capacity,
+            coalesce(r.extra_capacity,0),
+            coalesce(r.extra_guest_price,0)
+        into
+        v_price_per_night,
+            v_capacity,
+            v_extra_capacity,
+            v_extra_guest_price
         from rooms r
-        where r.room_id = new.room_id
-        and r.status = 'ACTIVE'
-    )then
-        raise exception 'Room is not active';
-    end if;
-
-    if exists(
-        select 1
-        from bookings b
-        where b.room_id = new.room_id
-        and b.booking_status in ('PENDING','CONFIRMED')
-        and b.booking_id <> new.booking_id
-        and (
-            (new.check_in_date,new.check_out_date)
-            overlaps
-            (b.check_in_date,b.check_out_date)
-            )
-    )then
-        raise exception 'Room is already booked';
-    end if;
-
-    return new;
-
-    end;
-    $$ language plpgsql;
-create or replace trigger book_only_available_room
-    before insert or update on bookings for each row
-    execute function trg_room_is_available_for_booking();
-}}}
+        where r.room_id = p_room_id;
+
+        if p_check_in_date>=p_check_out_date then
+            raise exception 'Check out must be after check in';
+        end if;
+        v_nights := p_check_in_date - p_check_out_date;
+        if p_guest_count > v_capacity + v_extra_capacity then
+            raise exception 'Guest count exceeds';
+        end if;
+        v_room_total := v_price_per_night * v_nights;
+
+        select coalesce(sum(a.price),0)
+        into v_amenities_total
+        from room_amenities ra
+        join amenities a on a.amenity_id = ra.amenity_id
+        where ra.room_id = p_room_id
+        and a.is_included = false;
+
+        v_extra_guests := greatest(p_guest_count-v_capacity,0);
+        v_extra_guest_total := v_extra_guests*v_extra_guest_price*v_nights;
+
+        v_total := v_room_total + v_extra_guest_total +v_amenities_total;
+        return v_total;
+
+    end;
+    $$;
+}}}
+== Cancel a booking
+{{{
+create or replace procedure sp_cancel_booking(p_booking_id bigint)
+language plpgsql
+as $$
+    declare
+        v_status varchar;
+    begin
+        select b.booking_status
+        into v_status
+            from bookings b
+        where booking_id = p_booking_id;
+
+        if v_status is null then
+            raise exception 'Booking does not exsists';
+        end if;
+
+        if v_status = 'COMPLETED' then
+            raise exception 'Completed booking cannot be canceled';
+        end if;
+        if v_status = 'CANCELLED' then
+            raise exception 'The booking is already cancelled';
+        end if;
+
+        update bookings
+        set booking_status = 'CANCELLED'
+        where booking_id = p_booking_id;
+
+    end;
+    $$;
+}}}
+== Complete old bookings
+{{{
+create or replace procedure  sp_complete_finished_bookings()
+language plpgsql
+as $$
+    begin
+        update bookings
+        set booking_status = 'COMPLETED'
+        where booking_status = 'CONFIRMED'
+        and check_out_date < current_date;
+    end;
+    $$;
+}}}