aonestar.public

Parameters

Name	Type	Mode
folio_id	integer	IN

Definition

WITH fol AS (
        SELECT IIF(f.folio_type = 'R', f.register_id, NULL) AS register_id, 
               IIF(f.folio_type = 'B', 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--||COALESCE(' ('||dp."name"||')', '')
                when 'inclusion' then cs.remark--||COALESCE(' ('||dp."name"||')', '')
                when 'extrabed' then cs.remark
                else dp."name"||COALESCE(' ('||cs.remark||')', '')
            end AS department, 
--            case when (cs.remark = 'Extra Bed' and lower(dp."name") = 'room charge') 
--                then 'Extra Bed'
--                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) 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 
--    ), other AS (
--        SELECT charge_date, rm.room_number, g.full_name AS guest_name, cs.charge_dept, 
--            iif(ic.name <> dp."name", dp."name"||' ('||ic.name||')', dp."name") AS department, 
--            charge_qty AS qty, charge_amount AS amount, total_amount, cs.charge_to_booking, posted  
--        FROM fol, charge_schedule cs 
--            LEFT JOIN guest_request rq ON rq.id = cs.request_id
--            LEFT JOIN inclusion ic ON ic.id = rq.inclusion_id 
--            LEFT JOIN department dp ON dp.id = cs.charge_dept  
--            LEFT JOIN registration rg ON rg.id = cs.register_id
--            LEFT JOIN room rm ON rm.id = rg.room_id
--            LEFT JOIN guest g ON g.id = rg.guest_id 
--        WHERE (cs.register_id = fol.register_id OR cs.booking_id = fol.booking_id) AND cs.charge_type = 'requirement'
--        ORDER BY 1,2
--    ), all_charges AS (
--        SELECT charge_type, charge_date, room_number, guest_name, charge_dept, department, qty, amount, total_amount, charge_to_booking, posted 
--        FROM schedule
--        UNION ALL
--        SELECT 'other', charge_date, room_number, guest_name, charge_dept, department, qty, amount, total_amount, charge_to_booking, posted  
--        FROM  other
    ), 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 --AND C2.charge_dept = C1.charge_dept
                          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,--||coalesce(', '||C3.room_number||' - '||C3.guest_name, ''), 
                                                  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