aonestar.public

Parameters

Name	Type	Mode
mealtype_id	integer	IN
include_adult	integer	IN
include_child	integer	IN
extra_adult	integer	IN
extra_adult_rate	numeric	IN
extra_child	integer	IN
extra_child_rate	numeric	IN
rate_amount	numeric	IN
other_charge	numeric	IN (DEFAULT 0)
break_extrabed	boolean	IN (DEFAULT true)

Definition

DECLARE
   IS_NET_AMOUNT BOOLEAN := FALSE;
   OTHERS NUMERIC := COALESCE(OTHER_CHARGE, 0);
   ROOM NUMERIC := 0; 
   SERVICE NUMERIC := 0; 
   TAX NUMERIC := 0; 
   ABF NUMERIC := 0; 
   LUNCH NUMERIC := 0; 
   DINNER NUMERIC := 0; 
   EXTRABED NUMERIC := 0; 
   EXTBEDSERV NUMERIC := 0; 
   EXTBEDTAX NUMERIC := 0;
   ROOM_RATE NUMERIC := 0;
   TOTAL NUMERIC := 0;

   ExtAdultAmt NUMERIC := COALESCE(EXTRA_ADULT * EXTRA_ADULT_RATE, 0);
   ExtChildAmt NUMERIC := COALESCE(EXTRA_CHILD * EXTRA_CHILD_RATE, 0);

   ExtAdultABF NUMERIC := 0;
   ExtAdultLunch NUMERIC := 0;
   ExtAdultDinner NUMERIC := 0;
   ExtChildABF NUMERIC := 0;
   ExtChildLunch NUMERIC := 0;
   ExtChildDinner NUMERIC := 0;
BEGIN
   -- ตรวจสอบ MEALTYPE_ID
   IF MEALTYPE_ID IS NOT NULL THEN
      SELECT INC_ABF, INC_LUNCH, INC_DINNER,
             EXT_ADULT_ABF, EXT_ADULT_LUNCH, EXT_ADULT_DINNER,
             EXT_CHILD_ABF, EXT_CHILD_LUNCH, EXT_CHILD_DINNER
      INTO ABF, LUNCH, DINNER, ExtAdultABF, ExtAdultLunch, ExtAdultDinner, ExtChildABF, ExtChildLunch, ExtChildDinner
      FROM SP_CALC_MEAL_PRICES(MEALTYPE_ID, INCLUDE_ADULT, INCLUDE_CHILD, EXTRA_ADULT, EXTRA_CHILD);
   END IF;

   -- หาก ExtAdultAmt เท่ากับ 0 ตั้งค่าตัวแปรที่เกี่ยวข้องเป็น 0
   IF ExtAdultAmt = 0 THEN
      ExtAdultABF := 0; ExtAdultLunch := 0; ExtAdultDinner := 0;
   END IF;

   -- หาก ExtChildAmt เท่ากับ 0 ตั้งค่าตัวแปรที่เกี่ยวข้องเป็น 0
   IF ExtChildAmt = 0 THEN
      ExtChildABF := 0; ExtChildLunch := 0; ExtChildDinner := 0;
   END IF;

   -- คำนวณค่าหลังหักมื้ออาหาร
   ExtAdultAmt := ExtAdultAmt - ExtAdultABF - ExtAdultLunch - ExtAdultDinner;
   IF ExtAdultAmt < 0 THEN
      RAISE EXCEPTION 'Extra adult rate lower than meal rate!';
   END IF;

   ExtChildAmt := ExtChildAmt - ExtChildABF - ExtChildLunch - ExtChildDinner;
   IF ExtChildAmt < 0 THEN
      RAISE EXCEPTION 'Extra child rate lower than meal rate!';
   END IF;

   -- รวมค่า Extra Bed
   EXTRABED := ExtAdultAmt + ExtChildAmt;

   -- แยกราคา Extra Bed หากค่าเป็นบวก
   IF EXTRABED > 0 AND COALESCE(BREAK_EXTRABED, TRUE) THEN
      SELECT ROOM, SERVICE, TAX
      INTO EXTRABED, EXTBEDSERV, EXTBEDTAX
      FROM SP_BREAK_ROOMRATE(EXTRABED);
   END IF;

   -- คำนวณราคาห้องพัก
   ROOM := CASE WHEN RATE_AMOUNT > 0 THEN RATE_AMOUNT - ABF - LUNCH - DINNER - OTHERS ELSE 0 END;

   -- รวมค่ามื้ออาหารเพิ่มเติม
   ABF := ABF + ExtAdultABF + ExtChildABF;
   LUNCH := LUNCH + ExtAdultLunch + ExtChildLunch;
   DINNER := DINNER + ExtAdultDinner + ExtChildDinner;

   ROOM_RATE := RATE_AMOUNT;

   -- หากเป็นราคาสุทธิ
   IF IS_NET_AMOUNT THEN
      ROOM := RATE_AMOUNT - (EXTRABED + EXTBEDSERV + EXTBEDTAX + ABF + LUNCH + DINNER + OTHERS);
      ROOM_RATE := RATE_AMOUNT - (ExtAdultAmt + ExtAdultABF + ExtAdultLunch + ExtAdultDinner)
                                - (ExtChildAmt + ExtChildABF + ExtChildLunch + ExtChildDinner);
   END IF;

   -- ตรวจสอบว่าราคาห้องติดลบหรือไม่
   IF ROOM < 0 THEN
      RAISE EXCEPTION 'Rate amount too small to break down (% > Room:% , ABF:% , Lunch:% , Dinner:% , Other:% )',
                      RATE_AMOUNT, ROOM, ABF, LUNCH, DINNER, OTHERS;
   END IF;

   -- แยกราคาห้องพักเพิ่มเติม
   IF ROOM <> 0 THEN
      SELECT ROOM, SERVICE, TAX
      INTO ROOM, SERVICE, TAX
      FROM SP_BREAK_ROOMRATE(ROOM, 'Y');
   END IF;

   -- คำนวณยอดรวม
   TOTAL := ROOM + SERVICE + TAX + EXTRABED + EXTBEDSERV + EXTBEDTAX + ABF + LUNCH + DINNER + OTHERS;

   RETURN TOTAL;

EXCEPTION WHEN OTHERS THEN
   -- หากไม่ละเว้นข้อผิดพลาด ให้โยนข้อผิดพลาด
   RAISE;
END;

sp_calc_meal

Parameters

Definition