CREATE FUNCTION DBO.GetRoomPrice (@ProductNumber int, @DateStamp Char(8), @RoomTypeNumber int, @Adult int, @Children1 int, @Children2 int
, @NameListSetting INT = -1, @PriceOverwritten BIT = 0, @NumberOfRooms INT = 0, @FixedPrice BIT = 0)
RETURNS @RoomPrice TABLE (Adult MONEY, ChildPax1 MONEY, ChildPax2 MONEY) AS
BEGIN
-- NB 20190705 Replaces CalcRoomPrice sp as function to extend the ablility to use it in functions
-- KJ 20160113: Bug Fix for "Wrong room price". Case #CR097289
-- KJ 20081118: Bug Fix for "Price not correct for dayproducts"
-- KJ 20051019: Added @ArrivalAddon, @StayAddon and @DepartureAddon as OUTPUT
-- 5/2-2005 : Imp price on child2 when dayproduct
-- 15/1 : added (NOLOCK) + removed convert(money,price) --> already as money

Declare @AdultPrice1 MONEY
Declare @AdultPrice2 MONEY
Declare @AdultPrice3 MONEY
Declare @AdultPrice4 MONEY
Declare @AdultPrice5 MONEY
Declare @AdultPrice6 MONEY
Declare @AdultPrice7 MONEY
Declare @AdultPrice8 MONEY
Declare @AdultPrice9 MONEY

Declare @Children1Price1 MONEY
Declare @Children1Price2 MONEY
Declare @Children1Price3 MONEY
Declare @Children1Price4 MONEY
Declare @Children1Price5 MONEY
Declare @Children1Price6 MONEY
Declare @Children1Price7 MONEY
Declare @Children1Price8 MONEY
Declare @Children1Price9 MONEY

Declare @Children2Price1 MONEY
Declare @Children2Price2 MONEY
Declare @Children2Price3 MONEY
Declare @Children2Price4 MONEY
Declare @Children2Price5 MONEY
Declare @Children2Price6 MONEY
Declare @Children2Price7 MONEY
Declare @Children2Price8 MONEY
Declare @Children2Price9 MONEY

Declare @DayProduct Bit
DECLARE @PricePerType BIT

DECLARE @RoomPriceAdult MONEY, @RoomPriceChildren1 MONEY, @RoomPriceChildren2 MONEY
if IsNull(@ProductNumber,0)=0   Set  @ProductNumber = 0 -- safe

DECLARE @OnePersonPaysForAll BIT
SELECT @OnePersonPaysForAll = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='OnePersonPaysForAll'
IF ISNULL(@OnePersonPaysForAll,0)=0 SET @OnePersonPaysForAll=0

Select @DayProduct = DayProduct, @PricePerType = PricePerType from Products where ProductNumber=@ProductNumber

--SET @PricePerType = ISNULL(@PricePerType, 0)
IF @DayProduct = 1
  SET @PricePerType = ISNULL(@PricePerType, 0)
ELSE
  SET @PricePerType = NULL

if IsNull(@DayProduct,2)=2-- Safe (KS 30/10-2001 : hvis ikke dagproduct værdi = 0 . Så værdien skal bare være større end 1)
begin
  SET @RoomPriceAdult=Convert(Money,0)
  SET @RoomPriceChildren1=Convert(Money,0)
  SET @RoomPriceChildren2=Convert(Money,0)
end
else
begin
  Select @AdultPrice1=AdultPrice1, @AdultPrice2=AdultPrice2, @AdultPrice3=AdultPrice3, @AdultPrice4=AdultPrice4, @AdultPrice5=AdultPrice5, @AdultPrice6=AdultPrice6, @AdultPrice7=AdultPrice7, @AdultPrice8=AdultPrice8, @AdultPrice9=AdultPrice9,
         @Children1Price1=Children1Price1, @Children1Price2=Children1Price2, @Children1Price3=Children1Price3, @Children1Price4=Children1Price4, @Children1Price5=Children1Price5,
         @Children1Price6=Children1Price6, @Children1Price7=Children1Price7, @Children1Price8=Children1Price8, @Children1Price9=Children1Price9,
         @Children2Price1=Children2Price1, @Children2Price2=Children2Price2, @Children2Price3=Children2Price3, @Children2Price4=Children2Price4, @Children2Price5=Children2Price5,
         @Children2Price6=Children2Price6, @Children2Price7=Children2Price7, @Children2Price8=Children2Price8, @Children2Price9=Children2Price9
  From ProductPrice WITH (NOLOCK) where (ProductNumber=@ProductNumber AND DateStamp=@DateStamp AND ((@PricePerType=0) OR (RoomTypeNumber=@RoomTypeNumber)))

  if (@DayProduct=0)
  begin
     if (@Adult=1)
       SET @RoomPriceAdult=@AdultPrice1
     else
     if (@Adult=2)
       SET @RoomPriceAdult=@AdultPrice2
     else
     if (@Adult=3)
       SET @RoomPriceAdult=@AdultPrice3
     else
     if (@Adult=4)
       SET @RoomPriceAdult=@AdultPrice4
     else
     if (@Adult=5)
       SET @RoomPriceAdult=@AdultPrice5
     else
     if (@Adult=6)
       SET @RoomPriceAdult=@AdultPrice6
     else
     if (@Adult=7)
       SET @RoomPriceAdult=@AdultPrice7
     else
     if (@Adult=8)
       SET @RoomPriceAdult=@AdultPrice8
     else
     if (@Adult=9)
       SET @RoomPriceAdult=@AdultPrice9

     if (@Children1=1)
       SET @RoomPriceChildren1=@Children1Price1
     else
     if (@Children1=2)
       SET @RoomPriceChildren1=@Children1Price2
     else
     if (@Children1=3)
       SET @RoomPriceChildren1=@Children1Price3
     else
     if (@Children1=4)
       SET @RoomPriceChildren1=@Children1Price4
     else
     if (@Children1=5)
       SET @RoomPriceChildren1=@Children1Price5
     else
     if (@Children1=6)
       SET @RoomPriceChildren1=@Children1Price6
     else
     if (@Children1=7)
       SET @RoomPriceChildren1=@Children1Price7
     else
     if (@Children1=8)
       SET @RoomPriceChildren1=@Children1Price8
     else
     if (@Children1=9)
       SET @RoomPriceChildren1=@Children1Price9

     if (@Children2=1)
       SET @RoomPriceChildren2=@Children2Price1
     else
     if (@Children2=2)
       SET @RoomPriceChildren2=@Children2Price2
     else
     if (@Children2=3)
       SET @RoomPriceChildren2=@Children2Price3
     else
     if (@Children2=4)
       SET @RoomPriceChildren2=@Children2Price4
     else
     if (@Children2=5)
       SET @RoomPriceChildren2=@Children2Price5
     else
     if (@Children2=6)
       SET @RoomPriceChildren2=@Children2Price6
     else
     if (@Children2=7)
       SET @RoomPriceChildren2=@Children2Price7
     else
     if (@Children2=8)
       SET @RoomPriceChildren2=@Children2Price8
     else
     if (@Children2=9)
       SET @RoomPriceChildren2=@Children2Price9
  end
  else -- dayproduct
  begin
    SET @RoomPriceAdult=@AdultPrice1
    SET @RoomPriceChildren1=@Children1Price1
    SET @RoomPriceChildren2=@Children2Price1

    IF @NameListSetting > 0
    BEGIN
      if @FixedPrice = 1 --AND (@PriceOverWritten = 1) 
      begin 
        SET @RoomPriceAdult = @AdultPrice1*@NumberOfRooms
        SET @RoomPriceChildren1=@Children1Price1*@NumberOfRooms
        SET @RoomPriceChildren2=@Children2Price1*@NumberOfRooms
      end 
      else --if(@PriceOverWritten = 1) 
      begin 
        SET @RoomPriceAdult=@AdultPrice1*ISNULL(@Adult,0)
        SET @RoomPriceChildren1=@Children1Price1*ISNULL(@Children1,0)
        SET @RoomPriceChildren2=@Children2Price1*ISNULL(@Children2,0)
      end 
    END 
  END

  if IsNull(@RoomPriceAdult,0)=0  SET  @RoomPriceAdult = 0 -- safe
  if IsNull(@RoomPriceChildren1,0)=0  SET  @RoomPriceChildren1 = 0 -- safe
  if IsNull(@RoomPriceChildren2,0)=0   Set  @RoomPriceChildren2 = 0 -- safe
  INSERT INTO @RoomPrice (Adult, ChildPax1, ChildPax2) VALUES (@RoomPriceAdult, @RoomPriceChildren1, @RoomPriceChildren2)
END
  RETURN
END