aonestar.public

Parameters

Name	Type	Mode
post_date	date	IN (DEFAULT NULL)
charge_type	text	IN (DEFAULT NULL)
posted	boolean	IN (DEFAULT NULL)
register_id	integer	IN (DEFAULT NULL)
booking_id	integer	IN (DEFAULT NULL)
folio_id	integer	IN (DEFAULT NULL)
include_inhouse	boolean	IN (DEFAULT true)
include_booking	boolean	IN (DEFAULT false)
post_date	date	OUT
register_id	integer	OUT
room_id	integer	OUT
booking_id	integer	OUT
booking_item	integer	OUT
arrival	date	OUT
departure	date	OUT
charge_date	date	OUT
charge_type	text	OUT
inclusion_id	integer	OUT
charge_dept	integer	OUT
charge_qty	integer	OUT
charge_amount	t_money	OUT
total_amount	t_money	OUT
remark	text	OUT
charge_folio_id	integer	OUT
charge_to_booking	boolean	OUT
split_room_charge	boolean	OUT
posted	boolean	OUT

Definition

WITH
--================================================
-- 1) ดึงรายการ "การจอง" (rsv)
--================================================
rsv AS (
    SELECT NULL::int AS register_id,
           bi.booking_id,
           bi.item_id AS booking_item,
           bk.folio_id,
           bk.folio_id AS booking_folio_id,
           NULL::int AS room_id,
           bi.arrival,
           bi.departure,
           bi.rateplan_id,
           (bi.charge_to = 'B') AS charge_to_booking,
           false AS split_room_charge,
           (bi.qty - bi.inhouse_qty) AS qty,
           bi.adult,
           bi.child,
           bi.infant,
           bi.extra_adult,
           bi.extra_child,
           bi.extra_infant,
           bi.mealtype_id
    FROM booking_items bi
         LEFT JOIN booking bk ON bk.id = bi.booking_id
    WHERE include_booking
      AND bk.status IN ('R','C','I')
      AND (bi.qty - bi.inhouse_qty) > 0
      AND ($5 IS NULL OR bi.booking_id = $5)
),
--================================================
-- 2) คำนวณค่า Inclusions จากการจอง (rsv_inclusion_charge)
--================================================
rsv_inclusion_charge AS (
    SELECT d.post_date,
           rsv.register_id,
           rsv.room_id,
           rsv.booking_id,
           rsv.booking_item,
           rsv.arrival,
           rsv.departure,
           inc.charge_dept,
           inc.frequency,
           CASE frequency
                WHEN 1 THEN d.post_date + 1     -- Daily except arrival
                WHEN 2 THEN d.post_date         -- Daily except departure
                WHEN 3 THEN rsv.arrival         -- Once on arrival
                WHEN 4 THEN rsv.departure       -- Once on departure
           END AS charge_date,
           fn_calc_inclusion_qty(rsv.qty,
                                 rsv.adult,
                                 rsv.child,
                                 rsv.infant,
                                 1,
                                 inc.charge_rate,
                                 inc.multiplier,
                                 inc.frequency,
                                 d.post_date,
                                 d.post_date + 1) AS qty,
           inc.charge_rate,
           iif(inc.name <> dp.name, inc.name, null) AS remark,
           rsv.charge_to_booking,
           iif(rsv.charge_to_booking, rsv.booking_folio_id, rsv.folio_id) AS charge_folio_id,
           rsv.split_room_charge,
           inc.inclusion_id
    FROM rsv,
         LATERAL sp_get_rate_inclusions(rsv.rateplan_id) AS inc
         LEFT JOIN department dp ON dp.id = inc.charge_dept,
         LATERAL fn_date_range(rsv.arrival, rsv.departure) AS d(post_date)
    WHERE (frequency = 1 AND d.post_date <> rsv.arrival)
       OR (frequency = 2 AND d.post_date <> rsv.departure)
       OR (frequency = 3 AND d.post_date = rsv.arrival)
       OR (frequency = 4 AND d.post_date = rsv.departure)
),
--================================================
-- 3) คำนวณ Room Charge ของการจอง (rsv_room_charge)
--================================================
rsv_room_charge AS (
    WITH ic AS (
        SELECT ic.booking_item,
               ic.post_date,
               sum(ic.qty * ic.charge_rate) AS inclusion_charge_amount
        FROM rsv_inclusion_charge AS ic
        WHERE ($1 IS NULL OR ic.post_date = $1)
          AND ic.frequency IN (1,2)  -- daily
        GROUP BY 1,2
    )
    SELECT rr.charge_date AS post_date,
           rr.charge_date,
           rsv.booking_id,
           rsv.booking_item,
           rsv.arrival,
           rsv.departure,
           COALESCE(rr.room_charge_dept, default_charge_dept) AS charge_dept,
           rsv.qty,
           (
               rr.room_rate
               + (rsv.extra_adult * rr.extrabed_rate)
               + (rsv.extra_child * rr.child_rate)
               + (rsv.extra_infant * rr.infant_rate)
               - COALESCE(ic.inclusion_charge_amount, 0)
           )::t_money AS room_charge_amount,
           NULL AS remark,
           rsv.charge_to_booking,
           iif(rsv.charge_to_booking, rsv.booking_folio_id, rsv.folio_id) AS charge_folio_id,
           rsv.split_room_charge,
           FALSE AS posted
    FROM rsv
         LEFT JOIN booking_rates rr ON rsv.booking_item = rr.booking_item
         LEFT JOIN ic ON ic.post_date = rr.charge_date
                    AND ic.booking_item = rr.booking_item,
         LATERAL fn_default('ROOM_CHARGE_DEPT', 0) AS default_charge_dept
    WHERE ($1 IS NULL OR rr.charge_date = $1)
      AND ($3 IS NULL OR $3 = FALSE)
),
--================================================
-- 4) คำนวณค่าอาหาร (Meal) ของการจอง (rsv_meal_charge)
--    เรียกใช้ sp_calc_meal_prices(mealtype_id, included_adult, included_child, extra_adult, extra_child)
--================================================
rsv_meal_charge AS (
    SELECT rsv.booking_id,
           rsv.booking_item,
           rsv.arrival,
           rsv.departure,
           rsv.charge_to_booking,
           iif(rsv.charge_to_booking, rsv.booking_folio_id, rsv.folio_id) AS charge_folio_id,
           rsv.split_room_charge,
           rsv.qty,
           -- ใช้ LATERAL เพื่อเรียกฟังก์ชัน sp_calc_meal_prices
           meal.inc_abf + meal.inc_lunch + meal.inc_dinner + meal.ext_abf + meal.ext_lunch + meal.ext_dinner AS total_meal,
           meal.inc_abf,
           meal.inc_lunch,
           meal.inc_dinner,
           meal.ext_abf,
           meal.ext_lunch,
           meal.ext_dinner
    FROM rsv
         LEFT JOIN LATERAL sp_calc_meal_prices(rsv.mealtype_id,
                                             rsv.adult,
                                             rsv.child,
                                             rsv.extra_adult,
                                             rsv.extra_child) meal ON TRUE
),
--================================================
-- 5) รวมค่าห้อง + Inclusions (booking_charges)
--================================================
booking_charges AS (
    SELECT 'room' AS charge_type,
           NULL::int AS inclusion_id,
           r.*
    FROM rsv_room_charge r
    WHERE ($2 IS NULL OR $2 = 'room')
      AND ($6 IS NULL OR $6 = r.charge_folio_id)

    UNION ALL

    SELECT 'inclusion',
           inc.inclusion_id,
           inc.post_date,
           inc.charge_date,
           inc.booking_id,
           inc.booking_item,
           inc.arrival,
           inc.departure,
           inc.charge_dept,
           inc.qty,
           inc.charge_rate,
           inc.remark,
           inc.charge_to_booking,
           inc.charge_folio_id,
           inc.split_room_charge,
           FALSE AS posted
    FROM rsv_inclusion_charge inc
    WHERE ($2 IS NULL OR $2 = 'inclusion')
      AND NOT(inc.qty = 0 OR inc.charge_rate = 0)
      AND ($1 IS NULL OR inc.post_date = $1)
      AND ($3 IS NULL OR $3 = FALSE)
      AND ($6 IS NULL OR $6 = inc.charge_folio_id)

    UNION ALL

    --================================================
    -- เพิ่มบรรทัด Meal ("meal" charge_type) สำหรับการจอง
    --================================================
    SELECT 'meal' AS charge_type,
           NULL::int AS inclusion_id,
           NULL::date AS post_date,   -- ไม่ได้ระบุ post_date เฉพาะ ให้เป็น NULL
           NULL::date AS charge_date, -- ไม่ได้ระบุ charge_date เฉพาะ ให้เป็น NULL
           m.booking_id,
           m.booking_item,
           m.arrival,
           m.departure,
           0 AS charge_dept,
           m.qty,
           (m.total_meal)::t_money AS charge_rate,
           NULL::text AS remark,
           m.charge_to_booking,
           m.charge_folio_id,
           m.split_room_charge,
           FALSE AS posted
    FROM rsv_meal_charge m
    WHERE ($2 IS NULL OR $2 = 'meal')
      AND ($6 IS NULL OR $6 = m.charge_folio_id)
),
--================================================
-- 6) ส่วนของผู้เข้าพัก In-House (in_house)
--================================================
in_house AS (
    SELECT rg.id AS register_id,
           rg.booking_id,
           rg.folio_id,
           bk.folio_id AS booking_folio_id,
           rg.room_id,
           rg.arrival,
           rg.departure,
           rg.rateplan_id,
           (rg.booking_id IS NOT NULL) AND (MOD(rg.id, 2) = 0) AS charge_to_booking,
           false AS split_room_charge,
           rg.adult,
           rg.child,
           rg.infant,
           rg.extra_adult,
           rg.extra_child,
           rg.extra_infant
    FROM registration rg
         LEFT JOIN booking bk ON bk.id = rg.booking_id
         LEFT JOIN folio_pattern fp ON fp.id = bk.folio_pattern_id
         LEFT JOIN folio f ON f.id = bk.folio_id
    WHERE include_inhouse
      AND (rg.status = 'I')
      AND ($4 IS NULL OR rg.id = $4)
      AND ($5 IS NULL OR rg.booking_id = $5)
),
--================================================
-- 7) Inclusion ของ In-House (inh_inclusion_charge)
--================================================
inh_inclusion_charge AS (
    SELECT d.post_date,
           inh.register_id,
           inh.room_id,
           inh.booking_id,
           inh.arrival,
           inh.departure,
           inc.charge_dept,
           inc.frequency,
           CASE frequency
                WHEN 1 THEN d.post_date + 1     -- Daily except arrival
                WHEN 2 THEN d.post_date         -- Daily except departure
                WHEN 3 THEN inh.arrival         -- Once on arrival
                WHEN 4 THEN inh.departure       -- Once on departure
           END AS charge_date,
           fn_calc_inclusion_qty(1,
                                 inh.adult,
                                 inh.child,
                                 inh.infant,
                                 1,
                                 inc.charge_rate,
                                 inc.multiplier,
                                 inc.frequency,
                                 d.post_date,
                                 d.post_date + 1) AS qty,
           inc.charge_rate,
           iif(inc.name <> dp.name, inc.name, null) AS remark,
           inh.charge_to_booking,
           iif(inh.charge_to_booking, inh.booking_folio_id, inh.folio_id) AS charge_folio_id,
           inh.split_room_charge,
           inc.inclusion_id
    FROM in_house inh,
         LATERAL sp_get_rate_inclusions(inh.rateplan_id) AS inc
         LEFT JOIN department dp ON dp.id = inc.charge_dept,
         LATERAL fn_date_range(inh.arrival, inh.departure) AS d(post_date)
    WHERE (frequency = 1 AND d.post_date <> inh.arrival)
       OR (frequency = 2 AND d.post_date <> inh.departure)
       OR (frequency = 3 AND d.post_date = inh.arrival)
       OR (frequency = 4 AND d.post_date = inh.departure)
),
--================================================
-- 8) Room Charge ของ In-House (inh_room_charge)
--================================================
inh_room_charge AS (
    WITH ic AS (
        SELECT ic.register_id,
               ic.post_date,
               sum(ic.qty * ic.charge_rate) AS inclusion_charge_amount
        FROM inh_inclusion_charge AS ic
        WHERE ($1 IS NULL OR ic.post_date = $1)
          AND ic.frequency IN (1,2)
        GROUP BY 1,2
    )
    SELECT rr.charge_date AS post_date,
           rr.charge_date,
           inh.register_id,
           inh.room_id,
           inh.booking_id,
           inh.arrival,
           inh.departure,
           COALESCE(rr.room_charge_dept, default_charge_dept) AS charge_dept,
           1 AS qty,
           (
               rr.room_rate
               + (inh.extra_adult * rr.extrabed_rate)
               + (inh.extra_child * rr.child_rate)
               + (inh.extra_infant * rr.infant_rate)
               - COALESCE(ic.inclusion_charge_amount, 0)
           )::t_money AS room_charge_amount,
           NULL AS remark,
           inh.charge_to_booking,
           iif(inh.charge_to_booking, inh.booking_folio_id, inh.folio_id) AS charge_folio_id,
           inh.split_room_charge,
           rr.posted
    FROM in_house inh
         LEFT JOIN registration_rates rr ON inh.register_id = rr.register_id
         LEFT JOIN ic ON ic.post_date = rr.charge_date
                    AND ic.register_id = rr.register_id,
         LATERAL fn_default('ROOM_CHARGE_DEPT', 0) AS default_charge_dept
    WHERE ($1 IS NULL OR rr.charge_date = $1)
      AND ($3 IS NULL OR rr.posted = $3)
),
--================================================
-- 9) Meal Charge ของ In-House (inh_meal_charge)
--    เรียกใช้ sp_calc_meal_prices(mealtype_id, included_adult, included_child, extra_adult, extra_child)
--================================================
inh_meal_charge AS (
    SELECT inh.register_id,
           inh.room_id,
           inh.booking_id,
           inh.arrival,
           inh.departure,
           inh.charge_to_booking,
           iif(inh.charge_to_booking, inh.booking_folio_id, inh.folio_id) AS charge_folio_id,
           inh.split_room_charge,
           1 AS qty,
           meal.inc_abf + meal.inc_lunch + meal.inc_dinner + meal.ext_abf + meal.ext_lunch + meal.ext_dinner AS total_meal,
           meal.inc_abf,
           meal.inc_lunch,
           meal.inc_dinner,
           meal.ext_abf,
           meal.ext_lunch,
           meal.ext_dinner
    FROM in_house inh
         LEFT JOIN LATERAL sp_calc_meal_prices(
             -- สมมติว่า in_house มี mealtype_id เช่นเดียวกับ booking_items
             0,                 -- กำหนดค่า default = 0 (คุณอาจแก้เป็น inh.mealtype_id ถ้ามี)
             inh.adult,
             inh.child,
             inh.extra_adult,
             inh.extra_child
         ) meal ON TRUE
),
--================================================
-- 10) รวม Charges สำหรับ In-House (inh_charges)
--================================================
inh_charges AS (
    SELECT 'room' AS charge_type,
           NULL::int AS inclusion_id,
           r.*
    FROM inh_room_charge r
    WHERE ($2 IS NULL OR $2 = 'room')
      AND ($6 IS NULL OR $6 = r.charge_folio_id)

    UNION ALL

    SELECT 'inclusion',
           inc.inclusion_id,
           inc.post_date,
           inc.charge_date,
           inc.register_id,
           inc.room_id,
           inc.booking_id,
           inc.arrival,
           inc.departure,
           inc.charge_dept,
           inc.qty,
           inc.charge_rate,
           inc.remark,
           inc.charge_to_booking,
           inc.charge_folio_id,
           inc.split_room_charge,
           rr.posted
    FROM inh_inclusion_charge inc
         LEFT JOIN registration_rates rr ON inc.register_id = rr.register_id
                                       AND inc.post_date = rr.charge_date
    WHERE ($2 IS NULL OR $2 = 'inclusion')
      AND NOT(inc.qty = 0 OR inc.charge_rate = 0)
      AND ($1 IS NULL OR inc.post_date = $1)
      AND ($3 IS NULL OR rr.posted = $3)
      AND ($6 IS NULL OR $6 = inc.charge_folio_id)

    UNION ALL

    --================================================
    -- เพิ่มบรรทัด Meal ("meal" charge_type) สำหรับ In-House
    --================================================
    SELECT 'meal' AS charge_type,
           NULL::int AS inclusion_id,
           NULL::date AS post_date,
           NULL::date AS charge_date,
           m.register_id,
           m.room_id,
           m.booking_id,
           m.arrival,
           m.departure,
           0 AS charge_dept,
           m.qty,
           (m.total_meal)::t_money AS room_charge_amount,
           NULL::text AS remark,
           m.charge_to_booking,
           m.charge_folio_id,
           m.split_room_charge,
           FALSE AS posted
    FROM inh_meal_charge m
    WHERE ($2 IS NULL OR $2 = 'meal')
      AND ($6 IS NULL OR $6 = m.charge_folio_id)
)
SELECT
    rc.post_date,
    NULL::int AS register_id,
    NULL::int AS room_id,
    rc.booking_id,
    rc.booking_item,
    rc.arrival,
    rc.departure,
    rc.charge_date,
    rc.charge_type,
    rc.inclusion_id,
    rc.charge_dept,
    rc.qty,
    rc.room_charge_amount::t_money,
    (rc.qty * rc.room_charge_amount)::t_money,
    rc.remark,
    rc.charge_folio_id,
    rc.charge_to_booking,
    rc.split_room_charge,
    rc.posted
FROM booking_charges rc
WHERE include_booking
UNION ALL
SELECT
    rc.post_date,
    rc.register_id,
    rc.room_id,
    rc.booking_id,
    NULL::int,
    rc.arrival,
    rc.departure,
    rc.charge_date,
    rc.charge_type,
    rc.inclusion_id,
    rc.charge_dept,
    rc.qty,
    rc.room_charge_amount::t_money,
    (rc.qty * rc.room_charge_amount)::t_money,
    rc.remark,
    rc.charge_folio_id,
    rc.charge_to_booking,
    rc.split_room_charge,
    rc.posted
FROM inh_charges rc
WHERE include_inhouse

sp_get_charge_schedule_v3

Parameters

Definition