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);
    bk_items jsonb = '[]';
    rg_items jsonb = '[]';
BEGIN 
    IF folio_id IS NOT NULL THEN
        SELECT f.folio_type, f.booking_id, f.register_id FROM folio f 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;

    IF booking_id IS NOT NULL THEN 
        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 f.booking_id = $2
        )
        SELECT json_agg(booking_folio) FROM booking_folio
        INTO bk_items;
    END IF;
    
    WITH 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 
    )
    SELECT json_agg(guest_folios) FROM guest_folios
    INTO rg_items;
    
    RETURN COALESCE(bk_items,'[]') || COALESCE(rg_items,'[]');
    /*
    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 $1 IS NOT NULL AND f.booking_id = $1
    ), 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 ($2 IS NOT NULL AND rg.id = $2) OR ($2 IS NULL AND rg.booking_id = $1)
         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
    */
END

sp_get_folio_balances_js

Parameters

Definition