CREATE PROCEDURE [dbo].[OnlineAllRoomtypes]  @LangInt INT, @ChainNumber  INT, @AResourceTypeNumber INT = 0, @Sector VARCHAR(20) = '', @Pax INT=0 AS
SET NOCOUNT ON
/*
    Created by Troels Buch Aug 2007
    
    Changes: 
	TBU20170222: Changed select at @UseDynamicPaxText=1
	TBU20160501: Added @OnlineignoreroomtypeResource
	TBU20151029: Added @UseDynamicPaxText
	TBU20150904: Added @Pax
    TB20130711: Added Bunkbed	
	TB20091211: Added @Sector
	TB20091208: Added @AResourceTypeNumber

    NOTES: 	- 
*/

DECLARE @UseDynamicPaxText BIT
DECLARE @OnlineignoreroomtypeResource BIT

EXEC @UseDynamicPaxText = GetHotelSetupKeyValue 'OnlineRoomTypeTextPerPAX', @ChainNumber
EXEC @OnlineignoreroomtypeResource = GetHotelSetupKeyValue 'onlineignoreroomtyperesource', @ChainNumber

--print @UseDynamicPaxText
--SET @UseDynamicPaxText =

--SET @UseDynamicPaxText = 0

IF @UseDynamicPaxText=1
BEGIN
	/*SELECT OnlineRoomTypesTranslated.Text, OnlineRoomTypesTranslated.Description, OnlineRoomTypes.RoomTypeNumber, OnlineRoomTypes.OnlineRoomTypeNumber, IsNull(BunkBed,0) AS Bunkbed, IsNull(OnlineRoomTypesTranslated.pax, '') AS Pax FROM OnlineRoomTypes (nolock)
   INNER JOIN OnlineRoomTypesTranslated (NOLOCK) ON OnlineRoomTypes.OnlineRoomTypeNumber = OnlineRoomTypesTranslated.OnlineRoomTypeNumber
   WHERE (CountryNumber = @LangInt) /*AND OnlineRoomTypes.OnlineRoomTypeNumber IN (SELECT Roomtypes.OnlineRoomTypenumber
   FROM Onlineroomtypes (NOLOCK) INNER JOIN Roomtypes (NOLOCK) ON Onlineroomtypes.Onlineroomtypenumber = Roomtypes.onlineroomtypenumber 
   WHERE IsNull(Roomtypes.Hotelnumber, 0) = @ChainNumber AND IsNull(Roomtypes.ResourceTypenumber, 0) = @AResourceTypeNumber) */
   AND (OnlineRoomTypesTranslated.pax=@Pax OR  OnlineRoomTypesTranslated.pax=0)
   ORDER BY SortNumber, OnlineRoomTypesTranslated.Text, OnlineRoomTypes.RoomTypeNumber*/
   SELECT OnlineRoomTypesTranslated.Text, OnlineRoomTypesTranslated.Description, ROOMTYPES.RoomTypeNumber, OnlineRoomTypes.OnlineRoomTypeNumber, IsNull(BunkBed,0) AS Bunkbed, IsNull(OnlineRoomTypesTranslated.pax, '') AS Pax FROM OnlineRoomTypes (nolock)
   INNER JOIN OnlineRoomTypesTranslated (NOLOCK) ON OnlineRoomTypes.OnlineRoomTypeNumber = OnlineRoomTypesTranslated.OnlineRoomTypeNumber
   INNER JOIN ROOMTYPES (nolock) on ROOMTYPES.OnlineRoomTypeNumber = OnlineRoomTypes.OnlineRoomTypeNumber 
   WHERE (CountryNumber = @LangInt) 
   AND (OnlineRoomTypesTranslated.pax=@Pax OR  OnlineRoomTypesTranslated.pax=0)
   ORDER BY OnlineRoomTypes.SortNumber, OnlineRoomTypesTranslated.Text, OnlineRoomTypes.RoomTypeNumber
END 
ELSE IF @Sector = ''
BEGIN
   SELECT OnlineRoomTypesTranslated.Text, OnlineRoomTypesTranslated.Description, OnlineRoomTypes.RoomTypeNumber, OnlineRoomTypes.OnlineRoomTypeNumber, IsNull(BunkBed,0) AS Bunkbed, IsNull(OnlineRoomTypesTranslated.pax, '') AS Pax FROM OnlineRoomTypes (nolock)
   INNER JOIN OnlineRoomTypesTranslated (NOLOCK) ON OnlineRoomTypes.OnlineRoomTypeNumber = OnlineRoomTypesTranslated.OnlineRoomTypeNumber
   WHERE (CountryNumber = @LangInt) AND OnlineRoomTypes.OnlineRoomTypeNumber IN (SELECT Roomtypes.OnlineRoomTypenumber
   FROM Onlineroomtypes (NOLOCK) INNER JOIN Roomtypes (NOLOCK) ON Onlineroomtypes.Onlineroomtypenumber = Roomtypes.onlineroomtypenumber 
   WHERE IsNull(Roomtypes.Hotelnumber, 0) = @ChainNumber AND ( (IsNull(Roomtypes.ResourceTypenumber, 0) = @AResourceTypeNumber AND @OnlineignoreroomtypeResource = 0)) OR (@OnlineignoreroomtypeResource = 1) ) 
   AND (OnlineRoomTypesTranslated.pax=@Pax OR  OnlineRoomTypesTranslated.pax=0)
   ORDER BY SortNumber, OnlineRoomTypesTranslated.Text, OnlineRoomTypes.RoomTypeNumber
END
ELSE
BEGIN
   SELECT OnlineRoomTypesTranslated.Text, OnlineRoomTypesTranslated.Description, OnlineRoomTypes.RoomTypeNumber, OnlineRoomTypes.OnlineRoomTypeNumber, IsNull(BunkBed,0) AS Bunkbed FROM OnlineRoomTypes (nolock)
   INNER JOIN OnlineRoomTypesTranslated (NOLOCK) ON OnlineRoomTypes.OnlineRoomTypeNumber = OnlineRoomTypesTranslated.OnlineRoomTypeNumber
   WHERE (CountryNumber = @LangInt) AND OnlineRoomTypes.OnlineRoomTypeNumber IN (SELECT Roomtypes.OnlineRoomTypenumber
   FROM Onlineroomtypes (NOLOCK) INNER JOIN Roomtypes (NOLOCK) ON Onlineroomtypes.Onlineroomtypenumber = Roomtypes.onlineroomtypenumber 
   WHERE IsNull(Roomtypes.Hotelnumber, 0) = @ChainNumber 
   AND Onlineroomtypes.RoomTypeNumber IN (SELECT DISTINCT(RoomTypeNumber) FROM Sectors (NOLOCK)
   INNER JOIN Rooms (NOLOCK) ON Sectors.SectorNumber = rooms.Sector
   WHERE Sectors.SectorNumber =@Sector)
   AND IsNull(Roomtypes.ResourceTypenumber, 0) = @AResourceTypeNumber) ORDER BY SortNumber, OnlineRoomTypesTranslated.Text, OnlineRoomTypes.RoomTypeNumber
END


