VOC

SET @startDate = '20260106';

-- 30

SET @fromDate = DATE(@startDate);

SET @toDate = DATE_ADD(@fromDate, INTERVAL 30 DAY);

-- 60

SET @fromDate60 = DATE_ADD(@fromDate, INTERVAL 31 DAY);

SET @toDate60 = DATE_ADD(@fromDate, INTERVAL 60 DAY);

-- 90

SET @fromDate90 = DATE_ADD(@fromDate, INTERVAL 61 DAY);

SET @toDate90 = DATE_ADD(@fromDate, INTERVAL 90 DAY);

-- 1: 0-30; 2: 31-60; 3: 61-90

SET @range = 3;

SET @countryCode = 

'JP' -- 2359 - 363,, bookable

-- 'KR' -- 952 - 180,156,120 bookable

-- 'VN' -- 1248 - 1463,1513,1554 bookable

-- 'HK' -- 38 - 19,19,19

-- 'LA' -- 2 - unbookable

-- 'MX' -- 7 - unbookable

-- 'TH' -- 148 - 76,89,80 bookable

-- 'TW' -- 26 - 2,2,2 bookable

-- 'IL' -- 2 - unbookable

-- 'MY' -- 7 - unbookable

-- 'ID' -- 2 - 2,2,2 bookable

;

SET @usingFromDate = CAST(DATE_FORMAT(

  CASE WHEN @range=1 THEN @fromDate

    WHEN @range=2 THEN @fromDate60

    ELSE @fromDate90

  END

, '%Y%m%d') AS CHAR);

SET @usingToDate = CAST(DATE_FORMAT(

  CASE WHEN @range=1 THEN @toDate

    WHEN @range=2 THEN @toDate60

    ELSE @toDate90

  END

, '%Y%m%d') AS CHAR);

SELECT HM.HOTEL_CODE AS HotelCode

  , A.LegacyHotelCode

  , A.HotelName

  , A.CountryCode

  , A.CountryName

  , A.RegionCode

  , A.RegionName

  , A.Address

  , A.PhoneNumber

  , A.RoomCode

  , A.LegacyRoomCode

  , A.RoomName

  , A.Bookable

FROM (

  SELECT DISTINCT HHM.HOTEL_CODE

  FROM HO_HOTEL_MASTER HHM

  INNER JOIN VD_VENDOR_HOTEL_CONTRACT VHC ON VHC.HOTEL_CODE = HHM.HOTEL_CODE

  WHERE 1=1

    AND HHM.COUNTRY_CODE = @countryCode

    AND HHM.REGISTER_STATUS_CODE = 'RST02'

    AND VHC.CONTRACT_STATUS_CODE = 'A'

  LIMIT 200 OFFSET 2200

) HM

INNER JOIN LATERAL(

  SELECT

    HHM.HOTEL_CODE AS HotelCode

    , IFNULL(HHM.LEGACY_HOTEL_CODE, VHC.LEGACY_HOTEL_CODE) AS LegacyHotelCode

    , HHM.HOTEL_NAME_EN AS HotelName

    , COUNTRY.COUNTRY_CODE AS CountryCode

    , COUNTRY.COUNTRY_NAME_EN AS CountryName

    , REGION.REGION_CODE AS RegionCode

    , REGION.REGION_NAME_EN AS RegionName

    , IFNULL(HHM.ADDRESS_EN, IFNULL(HHM.ADDRESS_KO, IFNULL(HHM.ADDRESS_JA, IFNULL(HHM.ADDRESS_VI, HHM.ADDRESS_ZH)))) AS Address

    , HHM.PHONE_NO AS PhoneNumber

    , RT.VENDOR_ROOM_TYPE_SEQ AS RoomCode

    , RT.LEGACY_ROOM_TYPE_CODE AS LegacyRoomCode

    , RT.ROOM_TYPE_NAME_EN AS RoomName

    , CASE 

          WHEN COUNT(RDP.DAILY_PRICE_SEQ) <= 10

          THEN 'No bookable'

          ELSE 'Bookable'

      END AS Bookable

  FROM VD_VENDOR_ROOM_ALLOTMENT RA

  INNER JOIN VD_VENDOR_ROOM_DAILY_PRICE RDP ON RA.VENDOR_ROOM_TYPE_SEQ = RDP.VENDOR_ROOM_TYPE_SEQ

    AND RA.APPLIED_DATE = RDP.APPLIED_DATE

    AND RA.SALES_STOP_YN = RDP.SALES_STOP_YN

    AND RDP.SALES_STOP_YN = 'N'

    AND RDP.VENDOR_NET_AMOUNT > 0

  INNER JOIN VD_VENDOR_ROOM_PLAN RP ON RDP.ROOM_PLAN_SEQ = RP.ROOM_PLAN_SEQ

    AND RA.HOTEL_CONTRACT_SEQ = RP.HOTEL_CONTRACT_SEQ

  INNER JOIN VD_VENDOR_HOTEL_CONTRACT VHC ON RP.HOTEL_CONTRACT_SEQ = VHC.HOTEL_CONTRACT_SEQ

  INNER JOIN US_COMP_MASTER TRADER ON VHC.VENDOR_COMP_CODE = TRADER.COMP_CODE

  INNER JOIN VD_VENDOR_ROOM_TYPE RT ON RP.VENDOR_ROOM_TYPE_SEQ = RT.VENDOR_ROOM_TYPE_SEQ

  INNER JOIN HO_HOTEL_MASTER HHM ON VHC.HOTEL_CODE = HHM.HOTEL_CODE

  INNER JOIN BS_COUNTRY_CODE COUNTRY ON HHM.COUNTRY_CODE = COUNTRY.COUNTRY_CODE

  LEFT JOIN BS_REGION_CODE REGION ON HHM.REGION_CODE = REGION.REGION_CODE

  WHERE 1=1

    AND HHM.COUNTRY_CODE = @countryCode

    AND VHC.HOTEL_CODE = HM.HOTEL_CODE

    AND RDP.APPLIED_DATE BETWEEN @usingFromDate AND @usingToDate -- '20260106' AND '20260301' 

    AND VHC.CONTRACT_STATUS_CODE = 'A'

    AND TRADER.TRADE_STATUS_CODE = 'TST01'

    AND RP.SALES_STOP_YN = 'N'

    AND RT.SALES_STOP_YN = 'N'

    AND HHM.REGISTER_STATUS_CODE = 'RST02' -- Approved

  GROUP BY HHM.HOTEL_CODE

    , IFNULL(HHM.LEGACY_HOTEL_CODE, VHC.LEGACY_HOTEL_CODE)

    , HHM.HOTEL_NAME_EN

    , COUNTRY.COUNTRY_CODE

    , COUNTRY.COUNTRY_NAME_EN

    , REGION.REGION_CODE

    , REGION.REGION_NAME_EN

    , IFNULL(HHM.ADDRESS_EN, IFNULL(HHM.ADDRESS_KO, IFNULL(HHM.ADDRESS_JA, IFNULL(HHM.ADDRESS_VI, HHM.ADDRESS_ZH))))

    , HHM.PHONE_NO

    , RT.VENDOR_ROOM_TYPE_SEQ

    , RT.LEGACY_ROOM_TYPE_CODE

    , RT.ROOM_TYPE_NAME_EN

) A ON TRUE

;