aonestar.public

Parameters

Name	Type	Mode

Definition

WITH brk AS (
        -- breakdown ของ in-house registration ทั้งหมด (group + non-group) ในครั้งเดียว
        -- ไม่รวมแขก departure = วันนี้ (checkout today)
        SELECT s.register_id, rm.room_number, rg.booking_id, rg.group_id, rg.is_group,
            rg.folio_id,
            g.full_name AS name,
            s.rate_amount AS room_rate, s.room AS room_charge,
            s.extrabed, s.abf, s.lunch, s.dinner,
            s.inclusive AS inclusive_charge,
            s.other AS other_charge,
            s.room + s.extrabed + s.abf + s.lunch + s.dinner + s.inclusive + s.other AS total,
            IIF(s.room_posted, 'posted', 'unposted') AS status,
            s.room_posted AS posted, s.charge_to_booking
        FROM sp_rate_schedule_breakdown(fn_system_date(), posted := null, charge_to_booking := null) s
            JOIN registration rg ON rg.id = s.register_id
            LEFT JOIN room rm ON rm.id = s.room_id
            LEFT JOIN guest g ON g.id = rg.guest_id
        WHERE rg.status = 'I' AND (rg.departure > fn_system_date() OR rg.day_used)
    ),
    reg_req AS (
        -- requirement ราย registration แยกตาม charge_to_booking ของ "requirement" เอง
        --   req_book = ลง group folio (charge_to_booking) / req_own = ลง own folio
        SELECT cs.register_id,
               coalesce(sum(cs.total_amount) FILTER (WHERE cs.charge_to_booking), 0)     AS req_book,
               coalesce(sum(cs.total_amount) FILTER (WHERE NOT cs.charge_to_booking), 0) AS req_own
        FROM charge_schedule cs
        WHERE cs.charge_date = fn_system_date()
            AND cs.charge_type = 'requirement'
            AND cs.register_id IS NOT NULL
        GROUP BY cs.register_id
    ),
    individual AS (
        -- เฉพาะห้อง non-group
        -- total ไม่แก้: non-group ไม่มีแนวคิด charge_to_booking ทุกยอดเป็น own folio
        SELECT brk.register_id, brk.room_number, brk.booking_id, brk.name,
            brk.folio_id,
            brk.room_rate, brk.room_charge, brk.extrabed, brk.abf, brk.lunch, brk.dinner,
            brk.inclusive_charge, brk.other_charge,
            coalesce(rr.req_book, 0) AS other_charge_book,
            coalesce(rr.req_own,  0) AS other_charge_own,
            brk.total, brk.status, brk.posted, brk.charge_to_booking
        FROM brk
            LEFT JOIN reg_req rr ON rr.register_id = brk.register_id
        WHERE NOT brk.is_group
    ),
    member_rows AS (
        -- group member ทุกตัว ผูกกับ group folio ของ booking
        SELECT bk.folio_id, brk.folio_id AS reg_folio_id,
            brk.booking_id, brk.group_id, gi.name AS group_name, bk.status AS booking_status,
            brk.register_id, brk.room_number, brk.name,
            brk.room_rate, brk.room_charge, brk.extrabed, brk.abf, brk.lunch, brk.dinner,
            brk.inclusive_charge, brk.other_charge, brk.total, brk.status, brk.posted, brk.charge_to_booking
        FROM brk
            JOIN booking bk ON bk.id = brk.booking_id AND bk.book_type = 'G'
            LEFT JOIN group_info gi ON gi.id = brk.group_id
        WHERE brk.is_group
    ),
    per_booking_req AS (
        -- per-booking requirement = multiplier=1 (ผูก group booking, post เข้า group ครั้งเดียว)
        -- per-item/per-pax (multiplier<>1) ตัดออก เพราะนับผ่าน reg-level (reg_req) แล้ว
        -- group by booking_id (ไม่ใช่ folio_id) เพราะ booking.folio_id อาจ NULL ได้
        SELECT b.id AS booking_id, SUM(cs.total_amount) AS req_amount
        FROM charge_schedule cs
            JOIN guest_request gr ON gr.id = cs.request_id
            JOIN booking b ON b.id = cs.booking_id
        WHERE cs.charge_date = fn_system_date()
            AND cs.charge_type = 'requirement'
            AND cs.register_id IS NULL AND cs.booking_id IS NOT NULL
            AND gr.multiplier = 1
            AND cs.charge_to_booking
            AND b.book_type = 'G' AND b.status IN ('R', 'C', 'I')
        GROUP BY b.id
    ),
    group_agg AS (
        SELECT
            m.folio_id, m.booking_id, m.group_id, m.group_name,
            max(m.booking_status) AS booking_status,
            coalesce(sum(m.room_charge)      FILTER (WHERE m.charge_to_booking), 0) AS room_charge,
            coalesce(sum(m.extrabed)         FILTER (WHERE m.charge_to_booking), 0) AS extrabed,
            coalesce(sum(m.abf)              FILTER (WHERE m.charge_to_booking), 0) AS abf,
            coalesce(sum(m.lunch)            FILTER (WHERE m.charge_to_booking), 0) AS lunch,
            coalesce(sum(m.dinner)           FILTER (WHERE m.charge_to_booking), 0) AS dinner,
            coalesce(sum(m.inclusive_charge) FILTER (WHERE m.charge_to_booking), 0) AS inclusive_charge,
            -- requirement: reg-level charge_to_booking (req_book) + per-booking (multiplier=1)
            -- GROUP HEAD แสดงเฉพาะยอดที่ลง group folio → other_charge = other_charge_book เสมอ
            coalesce(sum(rr.req_book), 0) + coalesce(max(r.req_amount), 0) AS other_charge,
            coalesce(sum(rr.req_book), 0) + coalesce(max(r.req_amount), 0) AS other_charge_book,
            0                                                               AS other_charge_own,
            -- total = room/extrabed/meals/inclusive (FILTER ctb ของห้อง) + other_charge ข้างต้น
              coalesce(sum(m.room_charge)      FILTER (WHERE m.charge_to_booking), 0)
            + coalesce(sum(m.extrabed)         FILTER (WHERE m.charge_to_booking), 0)
            + coalesce(sum(m.abf)              FILTER (WHERE m.charge_to_booking), 0)
            + coalesce(sum(m.lunch)            FILTER (WHERE m.charge_to_booking), 0)
            + coalesce(sum(m.dinner)           FILTER (WHERE m.charge_to_booking), 0)
            + coalesce(sum(m.inclusive_charge) FILTER (WHERE m.charge_to_booking), 0)
            + coalesce(sum(rr.req_book), 0) + coalesce(max(r.req_amount), 0) AS total,
            -- posted = true เมื่อ CTB member ทุกคน posted (bool_and)
            -- any_posted = true เมื่อ CTB member อย่างน้อยหนึ่งคน posted (bool_or)
            -- → partial_posted เมื่อ any_posted=true แต่ posted=false
            -- fallback bool_and/or(m.posted) ครอบทุก member เมื่อไม่มี CTB member เลย
            -- (own account ล้วน → FILTER ได้ empty set → NULL → ใช้ all-member aggregate แทน false)
            coalesce(bool_and(m.posted) FILTER (WHERE m.charge_to_booking), bool_and(m.posted)) AS posted,
            coalesce(bool_or(m.posted)  FILTER (WHERE m.charge_to_booking), bool_or(m.posted))  AS any_posted,
            json_agg(
                json_build_object(
                    'register_id', m.register_id,
                    'room_number', m.room_number,
                    'booking_id', m.booking_id,
                    'folio_id', m.reg_folio_id,
                    'name', m.name,
                    'room_rate', m.room_rate,
                    'room_charge', m.room_charge,
                    'extrabed', m.extrabed,
                    'abf', m.abf,
                    'lunch', m.lunch,
                    'dinner', m.dinner,
                    'inclusive_charge', m.inclusive_charge,
                    'other_charge', m.other_charge,
                    'other_charge_book', coalesce(rr.req_book, 0),
                    'other_charge_own', coalesce(rr.req_own, 0),
                    -- total = ยอดที่ลง own folio ของ member
                    --   charge_to_booking=true  → ค่าห้อง/meal/inclusive ลง group folio แล้ว ได้แค่ req_own
                    --   charge_to_booking=false → ทุก charge + req_own
                    'total',
                        CASE WHEN NOT m.charge_to_booking
                            THEN m.room_charge + m.extrabed + m.abf + m.lunch + m.dinner + m.inclusive_charge
                            ELSE 0
                        END + coalesce(rr.req_own, 0),
                    'status', m.status,
                    'posted', m.posted,
                    'charge_to_booking', m.charge_to_booking
                )
                ORDER BY m.room_number, m.register_id
            ) AS members
        FROM member_rows m
            LEFT JOIN reg_req rr ON rr.register_id = m.register_id
            LEFT JOIN per_booking_req r ON r.booking_id = m.booking_id
        GROUP BY m.folio_id, m.booking_id, m.group_id, m.group_name
    )
    SELECT json_build_object(
        'Group', coalesce((
            SELECT json_agg(
                json_build_object(
                    'register_id', NULL,
                    'room_number', NULL,
                    'booking_id', ga.booking_id,
                    'name', ga.group_name,
                    'room_rate', NULL,
                    'room_charge', ga.room_charge,
                    'extrabed', ga.extrabed,
                    'abf', ga.abf,
                    'lunch', ga.lunch,
                    'dinner', ga.dinner,
                    'inclusive_charge', ga.inclusive_charge,
                    'other_charge', ga.other_charge,
                    'other_charge_book', ga.other_charge_book,
                    'other_charge_own', ga.other_charge_own,
                    'total', ga.total,
                    'status', CASE WHEN ga.posted     THEN 'posted'
                                   WHEN ga.any_posted THEN 'partial_posted'
                                   ELSE 'unposted' END,
                    'posted', ga.posted,
                    'charge_to_booking', true,
                    'group_status', ga.booking_status,
                    'folio_id', ga.folio_id,
                    'group_id', ga.group_id,
                    'members', ga.members
                )
                ORDER BY ga.booking_id
            )
            FROM group_agg ga
        ), '[]'::json),
        'Individual', coalesce((
            SELECT json_agg(i ORDER BY i.room_number, i.register_id)
            FROM individual i
        ), '[]'::json)
    );

sp_get_auto_post_rooms_js

Parameters

Definition