aonestar.public

Parameters

Name	Type	Mode

Definition

SELECT jsonb_build_object(
        'rows', (
            SELECT coalesce(jsonb_agg(jsonb_build_object(
                       'id',                  m.id,
                       'src_ota_code',        m.src_ota_code::text,
                       'src_hotel_code',      m.src_hotel_code::text,
                       'src_payment',         m.src_payment,
                       'src_rateplan',        m.src_rateplan,
                       'src_rateplan_name',   p.name,
                       'src_roomtype',        m.src_roomtype,
                       'src_roomtype_name',   r.name,
                       'des_channel_id',      m.des_channel_id,
                       'des_channel_name',    c.name::text,
                       'des_channel_otacode', btrim(c.otacode))
                   ORDER BY m.src_ota_code, m.id), '[]'::jsonb)
            FROM channel_mapping m
            LEFT JOIN rate_plan p ON p.id = m.src_rateplan
            LEFT JOIN room_type r ON r.id = m.src_roomtype
            LEFT JOIN channel   c ON c.id = m.des_channel_id
        ),
        'lookups', jsonb_build_object(
            'channels', (
                SELECT coalesce(jsonb_agg(jsonb_build_object(
                           'id', c.id, 'name', c.name::text,
                           'otacode', btrim(c.otacode),
                           'link_channel_mgr', coalesce(c.link_channel_mgr, false))
                       ORDER BY c.name), '[]'::jsonb)
                FROM channel c WHERE coalesce(c.enabled, true)
            ),
            'rate_plans', (
                SELECT coalesce(jsonb_agg(jsonb_build_object(
                           'id', p.id, 'code', p.code::text, 'name', p.name)
                       ORDER BY p.order_seq NULLS LAST, p.code), '[]'::jsonb)
                FROM rate_plan p WHERE coalesce(p.enabled, true)
            ),
            'room_types', (
                SELECT coalesce(jsonb_agg(jsonb_build_object(
                           'id', r.id, 'code', r.code::text, 'name', r.name)
                       ORDER BY r.order_seq NULLS LAST, r.code), '[]'::jsonb)
                FROM room_type r WHERE coalesce(r.enabled, true)
            ),
            -- คอลัมน์เป็น free text ไม่มีตารางแม่ จึงเสนอค่าที่เคยใช้มาให้เลือก
            'payment_methods', (
                SELECT coalesce(jsonb_agg(DISTINCT m.src_payment), '[]'::jsonb)
                FROM channel_mapping m WHERE nullif(btrim(m.src_payment), '') IS NOT NULL
            )
        )
    )

fn_nbis_ota_mapping_json

Parameters

Definition