aonestar.public

Parameters

Name	Type	Mode
folio_id	integer	IN

Definition

WITH fol AS (
        SELECT f.id AS folio_id, f.folio_type,
               IIF(f.folio_type = 'R', f.register_id, NULL) AS register_id,
               IIF(f.folio_type IN ('B', 'G'), f.booking_id, NULL) AS booking_id,
               (SELECT count(DISTINCT rg.room_id) FROM registration rg WHERE rg.booking_id = f.booking_id) AS room_count
        FROM folio f
        WHERE id = $1
    ), all_charges AS (
        SELECT cs.charge_type, cs.charge_date, rm.room_number, g.full_name AS guest_name, cs.charge_dept,
            case cs.charge_type
                when 'requirement' then '* '||cs.remark
                when 'inclusion' then cs.remark
                when 'extrabed' then cs.remark
                else dp."name"||COALESCE(' ('||cs.remark||')', '')
            end AS department,
            cs.charge_qty AS qty, cs.charge_amount AS amount, total_amount, cs.charge_to_booking, cs.posted
        FROM fol, LATERAL sp_get_charge_schedule(
            register_id => fol.register_id,
            booking_id => fol.booking_id,
            folio_id => fol.folio_id,
            include_booking => fol.booking_id IS NOT NULL AND fol.folio_type <> 'G',
            include_inhouse => fol.register_id IS NOT NULL OR fol.folio_type = 'G'
        ) AS cs
            LEFT JOIN department dp ON dp.id = cs.charge_dept
            LEFT JOIN room rm ON rm.id = cs.room_id
            LEFT JOIN registration rg ON rg.id = cs.register_id
            LEFT JOIN guest g ON g.id = rg.guest_id
    ), grp_items AS (
        SELECT fn_charge_object(null,
                    to_char(C1.charge_date,'dd Mon yyyy'),
                    sum(C1.qty)::int,
                    sum(C1.total_amount),
                    sum(iif(C1.posted, 1, 0))::int,
                    count(C1.*)::int,
                    ARRAY(SELECT fn_charge_object(
                                    C2.charge_type,
                                    C2.department,
                                    sum(C2.qty)::int,
                                    sum(C2.total_amount),
                                    sum(iif(C2.posted, 1, 0))::int,
                                    count(C2.*)::int,
                                    ARRAY(SELECT fn_charge_object(C3.charge_type,
                                                                  coalesce(C3.room_number||COALESCE(' - '||C3.guest_name, ''), 'Room '||(ROW_NUMBER() OVER())::TEXT),
                                                                  C3.room_number, C3.qty, C3.amount, C3.total_amount, C3.charge_to_booking, C3.posted)
                                          FROM all_charges AS C3
                                          WHERE C3.charge_date = C2.charge_date AND C3.charge_dept = C2.charge_dept
                                          ORDER BY C3.room_number
                                    )
                                 )
                          FROM all_charges AS C2
                          WHERE C2.charge_date = C1.charge_date
                          GROUP BY charge_date, department, charge_dept, charge_type
                          ORDER BY charge_dept
                    )
                ) AS item
        FROM all_charges AS C1
        GROUP BY charge_date
        ORDER BY charge_date
    ), ind_items AS (
        SELECT fn_charge_object(null,
                    to_char(C1.charge_date,'dd Mon yyyy'),
                    sum(C1.qty)::int,
                    sum(C1.total_amount),
                    sum(iif(C1.posted, 1, 0))::int,
                    count(C1.*)::int,
                    ARRAY(SELECT fn_charge_object(C3.charge_type,
                                                  C3.department,
                                                  C3.room_number, C3.qty, C3.amount, C3.total_amount, C3.charge_to_booking, C3.posted)
                          FROM all_charges AS C3
                          WHERE C3.charge_date = C1.charge_date
                          ORDER BY C3.room_number
                    )
                ) AS item
        FROM all_charges AS C1
        GROUP BY charge_date
        ORDER BY charge_date
    )
    SELECT
        CASE
            WHEN (SELECT fol.room_count) > 1 THEN
                array_to_json(array(SELECT item FROM grp_items))
            ELSE
                array_to_json(array(SELECT item FROM ind_items))
        END
    FROM fol

sp_get_charge_schedule_js

Parameters

Definition