aonestar.public

Parameters

Name	Type	Mode
register_id	integer	IN
i_source	text	IN (DEFAULT 'active')

Definition

DECLARE
    json_result json;
    _use_active boolean;
    v_register_id integer;
BEGIN
    v_register_id := register_id;
    IF i_source = 'any' THEN
        _use_active := EXISTS (SELECT 1 FROM registration WHERE id = v_register_id);
    ELSE
        _use_active := (i_source <> 'archive');
    END IF;

WITH
    src_registration AS (
        SELECT id, booking_id, booking_item, status, stay_type, folio_id, rsvtype_id, NULL::text AS book_by,
               guest_id, group_id, member_code, channel_id, voucher_no, origin_id, room_id,
               arrival, checkin_time, arrive_by, arrive_from,
               departure, departure_time, depart_by, depart_to, nights,
               sales_id, market_id, checkin_user, checkin_time AS checkin_timestamp,
               modify_user, modify_time, checkout_user, checkout_time,
               msg_count, flag_count, note, payment_method, folio_pattern_id, credit_limit, promo_code,
               rateplan_id, mealtype_id, rate_id, adult, child, infant, extra_adult,
               extra_child, extra_infant, charge_to_booking,
               false AS is_archived
        FROM registration WHERE id = v_register_id AND _use_active
        UNION ALL
        SELECT id, booking_id, booking_item, status, stay_type, folio_id, rsvtype_id, NULL AS book_by,
               guest_id, group_id, member_code, channel_id, voucher_no, origin_id, room_id,
               arrival, checkin_time, arrive_by, arrive_from,
               departure, departure_time, depart_by, depart_to, nights,
               sales_id, market_id, checkin_user, checkin_time AS checkin_timestamp,
               modify_user, modify_time, checkout_user, checkout_time,
               msg_count, flag_count, note, payment_method, folio_pattern_id, credit_limit, promo_code,
               rateplan_id, mealtype_id, rate_id, adult, child, infant, extra_adult,
               extra_child, extra_infant, charge_to_booking,
               true AS is_archived
        FROM backup.registration WHERE id = v_register_id AND NOT _use_active
    ),
    src_registration_rates AS (
        SELECT rr.register_id AS reg_id, rr.charge_date, rr.room_rate, rr.extrabed_rate, rr.child_rate, rr.infant_rate,
               rr.comp, rr.comp_abf, rr.rateplan_id, rr.mealtype_id
        FROM registration_rates rr WHERE rr.register_id = v_register_id AND _use_active
        UNION ALL
        SELECT rr.register_id AS reg_id, rr.charge_date, rr.room_rate, rr.extrabed_rate, rr.child_rate, rr.infant_rate,
               rr.comp, rr.comp_abf, rr.rateplan_id, rr.mealtype_id
        FROM backup.registration_rates rr WHERE rr.register_id = v_register_id AND NOT _use_active
    ),
    src_guest_request AS (
        SELECT gr.id, gr.inclusion_id, gr.multiplier, gr.frequency, gr.charge_dept, gr.charge_rate,
               gr.qty, gr.start_date, gr.end_date, gr.request_time, gr.remark, gr.inclusive
        FROM guest_request gr WHERE gr.register_id = v_register_id AND _use_active
        UNION ALL
        SELECT gr.id, gr.inclusion_id, gr.multiplier, gr.frequency, gr.charge_dept, gr.charge_rate,
               gr.qty, gr.start_date, gr.end_date, gr.request_time, gr.remark, gr.inclusive
        FROM backup.guest_request gr WHERE gr.register_id = v_register_id AND NOT _use_active
    ),
    src_deposit AS (
        SELECT d.id, d.status, d.deposit_date, d.payment_id, 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 WHERE d.register_id = v_register_id AND _use_active
        UNION ALL
        SELECT d.id, d.status, d.deposit_date, d.payment_id, d.amount, d.remark,
               d.create_user, d.create_time, d.modify_user, d.modify_time, d.cancel_user, d.cancel_time
        FROM backup.deposit d WHERE d.register_id = v_register_id AND NOT _use_active
    ),
    src_registration_log AS (
        SELECT rl.id, rl.log_user, rl.log_type, rl.log_text, rl.log_date, rl.log_time
        FROM registration_log rl WHERE rl.register_id = v_register_id AND _use_active
        UNION ALL
        SELECT rl.id, rl.log_user, rl.log_type, rl.log_text, rl.log_date, rl.log_time
        FROM backup.registration_log rl WHERE rl.register_id = v_register_id AND NOT _use_active
    ),
    src_register_guests AS (
        SELECT rg.guest_id FROM register_guests rg WHERE rg.register_id = v_register_id AND _use_active
        UNION ALL
        SELECT rg.guest_id FROM backup.register_guests rg WHERE rg.register_id = v_register_id AND NOT _use_active
    ),
    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,
            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_timestamp AS 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,
            (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 src_registration_rates br
                   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,
            rg.is_archived
        FROM src_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
    ),
    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 src_guest_request rr LEFT JOIN inclusion inc ON inc.id = rr.inclusion_id
    ),
    guests AS (
        SELECT guest_id FROM REG
        UNION
        SELECT guest_id FROM src_register_guests
    ),
    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 src_deposit d LEFT JOIN department dp ON dp.id = d.payment_id
    ),
    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 src_registration_log
    ),
    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
    INTO json_result;

RETURN json_result;
END

sp_get_guest_inhouse_js

Parameters

Definition