aonestar.public

Parameters

Name	Type	Mode
register_id	integer	IN

Definition

with REG as (       
        select  rg.id AS register_id, 
                rg.booking_id, rg.booking_item, b.book_type, rg.status,
                rg.stay_type, b.confirm_no, rg.folio_id, 
                COALESCE(rg.rsvtype_id, b.rsvtype_id) AS rsvtype_id, --rst.name as reservation_type, 
                b.book_by, rg.guest_id, rg.group_id, grp.name as group_name, 
                rg.member_code, rg.channel_id, cn.name as channel_name, 
                rg.voucher_no, rg.origin_id, org.name as origin_name, rg.arrival, rg.checkin_time::time AS arrival_time, 
                rg.arrive_by, rg.arrive_from, 
                rg.departure, rg.departure_time, rg.depart_by, rg.depart_to, (rg.departure-rg.arrival) AS nights,                            
                rg.sales_id, ss.name as sales_name, 
                rg.market_id, mk.name as market_name, 
                rg.checkin_user, rg.checkin_time, 
                rg.modify_user, rg.modify_time, 
                rg.checkout_user, rg.checkout_time,
                rg.msg_count, rg.flag_count, rg.note,
                json_build_object('payment_method', rg.payment_method, 
                    'folio_pattern_id',rg.folio_pattern_id,
                    'folio_pattern_name', fp.name,
                    'credit_limit', rg.credit_limit,
                    'promo_code', rg.promo_code
                ) AS payment,
                
                rg.room_id, rm.room_number, rm.roomtype_id, 
                rt.code AS roomtype_code, rt."name" as roomtype_name, 
                rm.bedtype_id, bt."name" as bedtype_name, 
                rg.rateplan_id, rp."name" as rateplan_name, 
                rg.mealtype_id, mt."name" as mealtype_name, 
                rg.rate_id, rg.adult, rg.child, rg.infant, rg.extra_adult, 
                rg.extra_child, rg.extra_infant, rg.charge_to_booking, --bi.no_amend, bi.no_cancel, bi.no_refund,  
                (SELECT COALESCE(json_agg(rates), '[]')
                 FROM (
                        SELECT charge_date AS "date", br.room_rate AS amount, 
                             coalesce(br.extrabed_rate, rd.rate_extra_adult) AS extra_adult_rate, 
                             coalesce(br.child_rate, rd.rate_extra_child) AS extra_child_rate, 
                             coalesce(br.infant_rate, rd.rate_extra_infant) AS extra_infant_rate, 
                             br.comp, br.comp_abf, br.rateplan_id, br.mealtype_id
                        FROM registration_rates br 
                        WHERE br.register_id = $1
                        ORDER BY 1
                      ) AS rates
                ) AS rate_schedule
                ,json_build_object(
                    'max_adult', coalesce(rt.max_adult,0),
                    'max_child', coalesce(rt.max_child, 0),
                    'max_infant', coalesce(rt.max_infant, 0),
                    'max_pax',coalesce(rt.max_pax,0)
                ) as pax

        from registration rg LEFT JOIN booking b ON rg.booking_id = b.id
                left join channel cn on cn.id = rg.channel_id
                left join group_info grp on grp.id = rg.group_id
                left join origin org on org.id = rg.origin_id
                left join folio_pattern fp on fp.id = rg.folio_pattern_id
                left join sales_staff ss on ss.id = rg.sales_id
                left join market mk on mk.id = rg.market_id              
                left join rate_plan rp on rp.id = rg.rateplan_id 
                left join room rm on rm.id = rg.room_id 
                left join room_type rt on rt.id = rm.roomtype_id
                left join meal_type mt on mt.id = rg.mealtype_id
                left join bed_type bt on bt.id = rm.bedtype_id
                LEFT JOIN v_rate_details rd ON rd.roomtype_id = rm.roomtype_id AND rd.rateplan_id = rg.rateplan_id 
               -- LEFT JOIN registration rg ON rg.booking_id = b.id
        where rg.id = $1 
             
    ), requests AS (
        SELECT rr.id, rr.inclusion_id, inc."name", rr.multiplier AS "charge_type", rr.frequency,  
            rr.charge_dept, rr.charge_rate, rr.qty, rr.start_date, rr.end_date, rr.request_time, rr.remark, rr.inclusive
        FROM guest_request rr LEFT JOIN inclusion inc ON inc.id = rr.inclusion_id
        WHERE rr.register_id = $1 --AND NOT rr.inclusive 
                                     
    ), guests as (
        SELECT guest_id FROM REG 
        UNION
        SELECT guest_id FROM register_guests WHERE register_id = $1
        
    ), deposits AS (
        SELECT d.id, d.status, d.deposit_date, d.payment_id, dp.name AS payment_name, d.amount,
            d.remark, d.create_user, d.create_time, d.modify_user, d.modify_time, d.cancel_user, d.cancel_time 
        FROM deposit d LEFT JOIN department dp ON dp.id = d.payment_id
        WHERE d.register_id = $1
        
    ), reg_log AS (      
        SELECT id, log_user AS user_name, log_type, COALESCE(log_text,log_type) AS log_text, log_date, log_time
        FROM registration_log WHERE register_id = $1
        
    ), jsons AS (
        SELECT sp_get_guest_list_js((SELECT array_agg(guest_id) FROM guests)) as guests, 
            (SELECT COALESCE(json_agg(requests), '[]') from requests) as all_requests,
            (SELECT COALESCE(json_agg(requests), '[]') from requests WHERE NOT inclusive) as requirements,
            (SELECT COALESCE(json_agg(deposits), '[]') from deposits) as deposits,
            (SELECT COALESCE(json_agg(reg_log), '[]') from reg_log) AS logs
        
    ), summ AS (
        SELECT sp_get_checkin_summary_js(row_to_json(checkin_data)::jsonb) AS summary 
        FROM  (SELECT REG.*, all_requests AS requirements, deposits FROM REG, jsons) AS checkin_data 
    )
    SELECT row_to_json(dataset)
    FROM (SELECT REG.*, guests, requirements, deposits, logs, summary FROM REG, jsons, summ) AS dataset

sp_get_guest_inhouse_js

Parameters

Definition