set @NOWADAY = NOW;
set @F_R1 = date(@NOWADAY);
set @T_R1 = DATE_ADD(date(@NOWADAY), INTERVAL 30 DAY);
set @F_R3 = date(@NOWADAY);
set @T_R3 = DATE_ADD(date(@NOWADAY), INTERVAL 60 DAY);
set @F_R4 = date(@NOWADAY);
set @T_R4 = DATE_ADD(date(@NOWADAY), INTERVAL 90 DAY);
SELECT A.*
FROM (
SELECT DISTINCT
HM.COUNTRY_CODE
, HM.COUNTRY_NAME AS COUNTRY_NAME
, HM.HOTEL_CODE AS HotelCode
, HM.HOTEL_NAME_EN as HotelName
, (
SELECT IFNULL(VVHM.LEGACY_HOTEL_CODE, VVHM.VENDOR_HOTEL_CODE)
FROM VD_VENDOR_HOTEL_MAPPING VVHM
WHERE VVHM.HOTEL_CODE = HM.HOTEL_CODE
AND VVHM.VENDOR_COMP_CODE = 110000
LIMIT 1
) as AsisHotelCode
, (SELECT DETAIL_CODE_NAME_EN FROM BS_DETAIL_CODE WHERE MASTER_CODE = 'BS018' AND DETAIL_CODE = HM.REGISTER_STATUS_CODE) AS HotelStatus
, ( CASE WHEN (
SELECT COUNT(VRDP.APPLIED_DATE)
FROM VD_VENDOR_ROOM_DAILY_PRICE VRDP
INNER JOIN VD_VENDOR_ROOM_PLAN RP ON VRDP.ROOM_PLAN_SEQ = RP.ROOM_PLAN_SEQ
AND VRDP.APPLIED_DATE >= date(@F_R1)
WHERE VRDP.HOTEL_CODE = HM.HOTEL_CODE AND VRDP.VENDOR_ROOM_TYPE_SEQ = VRT.VENDOR_ROOM_TYPE_SEQ
AND VRDP.APPLIED_DATE BETWEEN date(@F_R1) AND date(@T_R1)
AND VRDP.VENDOR_NET_AMOUNT > 0 AND VRDP.SALES_STOP_YN = 'N'
LIMIT 1) >= 10 THEN 'Y' ELSE 'N' END
) AS Bookable_30_Rate
, ( CASE WHEN (
SELECT COUNT(VRA.APPLIED_DATE)
FROM VD_VENDOR_ROOM_ALLOTMENT VRA
WHERE VRA.VENDOR_ROOM_TYPE_SEQ = VRT.VENDOR_ROOM_TYPE_SEQ
AND VRA.APPLIED_DATE BETWEEN date(@F_R1) AND date(@T_R1)
AND VRA.LEFT_COUNT_SUM > 0 AND VRA.SALES_STOP_YN = 'N'
LIMIT 1) >= 10 THEN 'Y' ELSE 'N' END
) AS Bookable_30_Allotment
, ( CASE WHEN (
SELECT COUNT(VRDP.APPLIED_DATE)
FROM VD_VENDOR_ROOM_DAILY_PRICE VRDP
INNER JOIN VD_VENDOR_ROOM_PLAN RP ON VRDP.ROOM_PLAN_SEQ = RP.ROOM_PLAN_SEQ
AND VRDP.APPLIED_DATE >= date(@F_R3)
WHERE VRDP.HOTEL_CODE = HM.HOTEL_CODE AND VRDP.VENDOR_ROOM_TYPE_SEQ = VRT.VENDOR_ROOM_TYPE_SEQ
AND VRDP.APPLIED_DATE BETWEEN date(@F_R3) AND date(@T_R3)
AND VRDP.VENDOR_NET_AMOUNT > 0 AND VRDP.SALES_STOP_YN = 'N'
LIMIT 1) >= 10 THEN 'Y' ELSE 'N' END
) AS Bookable_60_Rate
, ( CASE WHEN (
SELECT COUNT(VRA.APPLIED_DATE)
FROM VD_VENDOR_ROOM_ALLOTMENT VRA
WHERE VRA.VENDOR_ROOM_TYPE_SEQ = VRT.VENDOR_ROOM_TYPE_SEQ
AND VRA.APPLIED_DATE BETWEEN date(@F_R3) AND date(@T_R3)
AND VRA.LEFT_COUNT_SUM > 0 AND VRA.SALES_STOP_YN = 'N'
LIMIT 1) >= 10 THEN 'Y' ELSE 'N' END
) AS Bookable_60_Allotment
, ( CASE WHEN (
SELECT COUNT(VRDP.APPLIED_DATE)
FROM VD_VENDOR_ROOM_DAILY_PRICE VRDP
INNER JOIN VD_VENDOR_ROOM_PLAN RP ON VRDP.ROOM_PLAN_SEQ = RP.ROOM_PLAN_SEQ
AND VRDP.APPLIED_DATE >= date(@F_R4)
WHERE VRDP.HOTEL_CODE = HM.HOTEL_CODE AND VRDP.VENDOR_ROOM_TYPE_SEQ = VRT.VENDOR_ROOM_TYPE_SEQ
AND VRDP.APPLIED_DATE BETWEEN date(@F_R4) AND date(@T_R4)
AND VRDP.VENDOR_NET_AMOUNT > 0 AND VRDP.SALES_STOP_YN = 'N'
LIMIT 1) >= 10 THEN 'Y' ELSE 'N' END
) AS Bookable_90_Rate
, ( CASE WHEN (
SELECT COUNT(VRA.APPLIED_DATE)
FROM VD_VENDOR_ROOM_ALLOTMENT VRA
WHERE VRA.VENDOR_ROOM_TYPE_SEQ = VRT.VENDOR_ROOM_TYPE_SEQ
AND VRA.APPLIED_DATE BETWEEN date(@F_R4) AND date(@T_R4)
AND VRA.LEFT_COUNT_SUM > 0 AND VRA.SALES_STOP_YN = 'N'
LIMIT 1) >= 10 THEN 'Y' ELSE 'N' END
) AS Bookable_90_Allotment
FROM (
SELECT DISTINCT
HM.COUNTRY_CODE as COUNTRY_CODE,
CC.COUNTRY_NAME_EN AS COUNTRY_NAME
, HM.HOTEL_CODE
, HM.HOTEL_NAME_EN
, HM.REGION_CODE
, RC.REGION_NAME_EN AS REGION_NAME
, HM.REGISTER_STATUS_CODE
, HM.ADDRESS_EN
, HM.PHONE_NO
, HM.STAR_RATING
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 BS_COUNTRY_CODE CC ON HM.COUNTRY_CODE = CC.COUNTRY_CODE
LEFT JOIN BS_REGION_CODE RC ON HM.REGION_CODE = RC.REGION_CODE
) HM
INNER JOIN VD_VENDOR_ROOM_TYPE VRT ON VRT.HOTEL_CODE = HM.HOTEL_CODE
AND VRT.SALES_STOP_YN = 'N'
LEFT JOIN BS_COUNTRY_CODE CC ON HM.COUNTRY_CODE = CC.COUNTRY_CODE
LEFT JOIN BS_DETAIL_CODE DC ON HM.REGISTER_STATUS_CODE = DC.DETAIL_CODE
) A
WHERE 1=1
AND (
(
A.Bookable_30_Rate = 'Y'
AND A.Bookable_30_Allotment = 'Y'
)
OR (
A.Bookable_60_Rate = 'Y'
AND A.Bookable_60_Allotment = 'Y'
)
OR (
A.Bookable_90_Rate = 'Y'
AND A.Bookable_90_Allotment = 'Y'
)
)
ORDER BY A.COUNTRY_CODE, A.HotelCode;
