aonestar.public

Parameters

Name	Type	Mode
user_name	text	IN
status	text	INOUT
msg	text	INOUT

Definition

DECLARE
    no_show record;
    system_date date = fn_system_date();
    book_count int = 0;
    room_count int = 0;
BEGIN
    /* All rooms no-show */
    FOR no_show IN (
        SELECT b.id AS booking_id, sum(bi.qty) AS qty
        FROM booking b LEFT JOIN booking_items bi ON bi.booking_id = b.id
        WHERE b.status IN ('R','C') AND b.arrival <= system_date
        GROUP BY 1)
    LOOP
       UPDATE booking b SET status = 'N'
       WHERE b.id = no_show.booking_id;

       DELETE FROM room_block
       WHERE booking_id = no_show.booking_id;

       UPDATE booking_items bi
       SET --noshow_qty = qty,
           block_qty = 0,
           rooms = null,
           room_numbers = null
       WHERE bi.booking_id = no_show.booking_id;

      --room_count = room_count + no_show.qty;
       book_count = book_count + 1;
       PERFORM sp_log_booking(no_show.booking_id, NULL, 'No-show', 'Mark booking as no-show', 'System');
    END LOOP;

    /* Partial no-show */
    FOR no_show IN (
        SELECT bi.booking_id, bi.item_id, (bi.qty - bi.inhouse_qty) AS qty
        FROM booking b INNER JOIN booking_items bi ON bi.booking_id = b.id
        WHERE b.status = 'I' AND bi.arrival <= system_date AND (bi.qty - bi.inhouse_qty) > 0)
    LOOP
        DELETE FROM room_block rb
        WHERE rb.booking_item = no_show.item_id;

        UPDATE booking_items b
        SET qty = (qty - no_show.qty),
            noshow_qty = no_show.qty,
            block_qty = 0,
            rooms = null,
            room_numbers = null
        WHERE b.item_id = no_show.item_id;

        room_count = room_count + no_show.qty;
        PERFORM sp_log_booking(no_show.booking_id, no_show.item_id, 'No-show', format('Mark %s rooms as no-show', no_show.qty::text), 'System');
    END LOOP;

    status = 'success';
    msg = format('%s booking and %s rooms are marked as no-show', book_count, room_count); --room_count::TEXT||' rooms marked';
END

sp_endday_mark_noshow

Parameters

Definition