aonestar.public

Parameters

Name	Type	Mode
folio_id	integer	IN (DEFAULT NULL)
booking_id	integer	IN (DEFAULT NULL)
register_id	integer	IN (DEFAULT NULL)

Definition

DECLARE 
    _folio_type char(1);
    _result_js jsonb = '[]';
BEGIN 
    IF folio_id IS NOT NULL THEN
        SELECT f.folio_type, COALESCE(rg.booking_id, f.booking_id), f.register_id 
        FROM folio f LEFT JOIN registration rg ON rg.id = f.register_id
        WHERE f.id = $1
        INTO _folio_type, booking_id, register_id; 
    
        IF _folio_type = 'C' THEN 
            RETURN NULL;
        END IF;       
    ELSEIF booking_id IS NOT NULL THEN 
        _folio_type = 'B';
        folio_id = (SELECT f.id FROM folio f WHERE f.booking_id = $2 AND f.folio_type = 'B');
        register_id = NULL;
    ELSEIF register_id IS NOT NULL THEN 
        _folio_type = 'R';
        SELECT rg.folio_id, rg.booking_id FROM registration rg WHERE rg.id = $3
        INTO folio_id, booking_id;
    END IF;

    WITH booking_folio AS (
        SELECT f.id AS folio_id, NULL::int AS register_id, 'Booking' AS room_number, b.booking_name AS name, b.arrival, b.departure, b.status,   
            COALESCE(f1.balance,0.0) as folio_balance1, 
            COALESCE(f2.balance,0.0) as folio_balance2, 
            COALESCE(f3.balance,0.0) as folio_balance3, 
            COALESCE(f4.balance,0.0) as folio_balance4,
            COALESCE(f1.balance, 0.0) + COALESCE(f2.balance, 0.0) + COALESCE(f3.balance, 0.0) + COALESCE(f4.balance, 0.0) AS total_balance,
            (b.status = 'I' AND COALESCE(f1.balance,0.0) = 0 AND COALESCE(f2.balance,0.0) = 0 AND COALESCE(f3.balance,0.0) = 0 AND COALESCE(f4.balance,0.0) = 0) AS checkout_available
        FROM booking b
            LEFT JOIN folio f ON b.id = f.booking_id
            LEFT JOIN folio_items f1 ON f1.folio_id = f.id AND f1.folio_seq = 1 AND NOT f1.closed 
            LEFT JOIN folio_items f2 ON f2.folio_id = f.id AND f2.folio_seq = 2 AND NOT f2.closed 
            LEFT JOIN folio_items f3 ON f3.folio_id = f.id AND f3.folio_seq = 3 AND NOT f3.closed 
            LEFT JOIN folio_items f4 ON f4.folio_id = f.id AND f4.folio_seq = 4 AND NOT f4.closed 
        WHERE b.id = $2
    ), guest_folios AS (
        SELECT f.id AS folio_id, rg.id AS register_id, rm.room_number, g.full_name AS name, rg.arrival, rg.departure, rg.status,   
            COALESCE(f1.balance,0.0) as folio_balance1, 
            COALESCE(f2.balance,0.0) as folio_balance2, 
            COALESCE(f3.balance,0.0) as folio_balance3, 
            COALESCE(f4.balance,0.0) as folio_balance4,
            COALESCE(f1.balance, 0.0) + COALESCE(f2.balance, 0.0) + COALESCE(f3.balance, 0.0) + COALESCE(f4.balance, 0.0) AS total_balance,
            (rg.status = 'I' AND COALESCE(f1.balance,0.0) = 0 AND COALESCE(f2.balance,0.0) = 0 AND COALESCE(f3.balance,0.0) = 0 AND COALESCE(f4.balance,0.0) = 0) AS checkout_available
        FROM registration rg 
            LEFT JOIN folio f ON rg.id = f.register_id 
            LEFT JOIN room rm ON rm.id = rg.room_id 
            LEFT JOIN guest g ON g.id = rg.guest_id 
            LEFT JOIN folio_items f1 ON f1.folio_id = f.id AND f1.folio_seq = 1 AND NOT f1.closed 
            LEFT JOIN folio_items f2 ON f2.folio_id = f.id AND f2.folio_seq = 2 AND NOT f2.closed 
            LEFT JOIN folio_items f3 ON f3.folio_id = f.id AND f3.folio_seq = 3 AND NOT f3.closed 
            LEFT JOIN folio_items f4 ON f4.folio_id = f.id AND f4.folio_seq = 4 AND NOT f4.closed 
         WHERE CASE _folio_type 
                   WHEN 'B' THEN rg.booking_id = $2
                   WHEN 'R' THEN rg.id = $3
                   ELSE FALSE 
               END          
         ORDER BY 1 
    ), all_folios AS (
        SELECT *, checkout_available AND (departure = fn_system_date()) AS default_selected FROM booking_folio
        UNION ALL 
        SELECT *, checkout_available AND (departure = fn_system_date()) AS default_selected FROM guest_folios
    )
    SELECT json_agg(all_folios) FROM all_folios
    INTO _result_js;

    RETURN _result_js;
END

sp_get_checkout_balances_js

Parameters

Definition