Ellis improvement

Request :

1. Check export excel bookable in Admin site, optimize SQL query and logic

SELECT DISTINCT
HM.HOTEL_CODE AS HotelCode
, (
SELECT IFNULL
FROM VD_VENDOR_HOTEL_MAPPING VVHM
WHERE VVHM.HOTEL_CODE = HM.HOTEL_CODE
AND VVHM.VENDOR_COMP_CODE = 110000
LIMIT 1
) as LegacyHotelCode
, HM.HOTEL_NAME_EN as HotelName
, HM.COUNTRY_CODE as CountryCode
, (SELECT CC.COUNTRY_NAME_EN FROM BS_COUNTRY_CODE CC WHERE HM.COUNTRY_CODE = CC.COUNTRY_CODE LIMIT 1) as CountryName
, HM.REGION_CODE as RegionCode
, (SELECT RC.REGION_NAME_EN FROM BS_REGION_CODE RC WHERE HM.REGION_CODE = RC.REGION_CODE LIMIT 1) as RegionName
, HM.STAR_RATING as HotelGrade
, HM.ADDRESS_EN as Address
, HM.PHONE_NO as PhoneNumber
, (SELECT DETAIL_CODE_NAME_EN FROM BS_DETAIL_CODE WHERE MASTER_CODE = 'BS018' AND DETAIL_CODE = HM.REGISTER_STATUS_CODE) AS HotelStatus
, VRT.SALES_STOP_YN as RoomSalesStopYN
, VRT.VENDOR_ROOM_TYPE_SEQ as RoomCode
, VRT.LEGACY_ROOM_TYPE_CODE as LegacyRoomCode
, VRT.ROOM_TYPE_NAME_EN as RoomName
, (CASE WHEN EXISTS (
SELECT 1
FROM VD_VENDOR_ROOM_DAILY_PRICE VRDP
LEFT JOIN VD_VENDOR_ROOM_ALLOTMENT VRA ON VRDP.VENDOR_ROOM_TYPE_SEQ = VRA.VENDOR_ROOM_TYPE_SEQ AND VRA.APPLIED_DATE = VRDP.APPLIED_DATE
WHERE VRDP.HOTEL_CODE = HM.HOTEL_CODE AND VRA.VENDOR_ROOM_TYPE_SEQ = VRT.VENDOR_ROOM_TYPE_SEQ
AND VRDP.APPLIED_DATE BETWEEN DATE AND DATE
AND VRDP.VENDOR_NET_AMOUNT > 0 AND VRDP.SALES_STOP_YN = 'N'
AND VRA.LEFT_COUNT_SUM > 0 AND VRA.SALES_STOP_YN = 'N'
LIMIT 1
) THEN 'Y' ELSE 'N' END) as Bookable
FROM HO_HOTEL_MASTER HM
INNER JOIN VD_VENDOR_HOTEL_CONTRACT VHC ON VHC.HOTEL_CODE = HM.HOTEL_CODE AND VHC.CONTRACT_STATUS_CODE = 'A'
INNER JOIN VD_VENDOR_ROOM_TYPE VRT ON VRT.HOTEL_CODE = HM.HOTEL_CODE
LEFT JOIN BS_COUNTRY_CODE CC ON HM.COUNTRY_CODE = CC.COUNTRY_CODE
LEFT JOIN BS_REGION_CODE RC ON HM.REGION_CODE = RC.REGION_CODE
LEFT JOIN BS_DETAIL_CODE DC ON HM.REGISTER_STATUS_CODE = DC.DETAIL_CODE
WHERE HM.COUNTRY_CODE = 'VN'