
CREATE PROCEDURE DBO.RecalculatePriceForReservation @ReferenceNumber INT, @GuestNumber INT AS
SET NOCOUNT ON
/*
    Created By: Jacob Pedersen 2004-09-01 + 2004-09-13 + 2004-09-14

    Last Updated:	

    2004XXXX YY: N/A

    NOTES: 

*/

DECLARE
@LineNumber INT,
@OldProductNumber INT,
@ProductNumber2 INT,
@ProductNumber3 INT,
@ProductNumber4 INT,
@StartDate DATETIME,
@EndDate DATETIME,
@OldRoomTypeNumber INT,
@NumberOfRooms INT,
@AdultPax INT,
@Child1Pax INT,
@Child2Pax INT,
@CalcTotalPrice REAL,
@CalcArrivalPrice REAL,
@Day2 SMALLINT,
@Day3 SMALLINT,
@Day4 SMALLINT,
@PriceOverwritten BIT,
@PriceOverwritten2 BIT,
@PriceOverwritten3 BIT,
@PriceOverwritten4 BIT,
@Price MONEY,
@TotalPrice MONEY,
@FirstPrice1 MONEY,
@FirstPrice2 MONEY,
@FirstPrice3 MONEY,
@FirstPrice4 MONEY

SELECT
@OldProductNumber=Reservations.ProductNumber,
@ProductNumber2=Reservations.ProductNumber2,
@ProductNumber3=Reservations.ProductNumber3,
@ProductNumber4=Reservations.ProductNumber4,
@StartDate = CONVERT(DATETIME, ReservationRoomLine.ArrivalDate, 108),
@EndDate = CONVERT(DATETIME, ReservationRoomLine.DepartureDate, 108),
@OldRoomTypeNumber = ReservationRoomLine.BookedTypeNumber,
@NumberOfRooms=ReservationRoomLine.NumberOfRooms,
@AdultPax=Reservations.PaysAdults,
@Child1Pax=Reservations.PaysChildren1,
@Child2Pax=Reservations.PaysChildren2,
@Day2=Reservations.Day2,
@Day3=Reservations.Day3,
@Day4=Reservations.Day4,
@PriceOverwritten=Reservations.PriceOverwritten,
@PriceOverwritten2=Reservations.PriceOverwritten2,
@PriceOverwritten3=Reservations.PriceOverwritten3,
@PriceOverwritten4=Reservations.PriceOverwritten4,
@Price=Reservations.Price,
@FirstPrice1=Reservations.FirstPrice1,
@FirstPrice2=Reservations.FirstPrice2,
@FirstPrice3=Reservations.FirstPrice3,
@FirstPrice4=Reservations.FirstPrice4,
@LineNumber=ReservationRoomLineXTab.LineNumber
FROM Reservations WITH (NOLOCK)
INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLineXTab.ReferenceNumber=Reservations.ReferenceNumber AND ReservationRoomLineXTab.GuestNumber=Reservations.GuestNumber
INNER JOIN ReservationRoomLine WITH (NOLOCK) ON ReservationRoomLine.LineNumber=ReservationRoomLineXTab.LineNumber
WHERE Reservations.ReferenceNumber=@ReferenceNumber AND Reservations.GuestNumber=@GuestNumber

--EXEC ProductPrices @OldProductNumber int,  @StartDate datetime, @EndDate datetime, @OldRoomTypeNumber int, @NumberOfRooms int, @AdultPax int, @Child1Pax int, @Child2Pax int, @CalcTotalPrice real OUTPUT, @CalcArrivalPrice real OUTPUT, @AddonAmount MONEY = 0 OUTPUT, @AddonVatAmount MONEY=0 OUTPUT, @PlusProduct BIT=0 AS
IF @OldProductNumber = 0
  SET @OldProductNumber = NULL

IF IsNull (@Day2, 0) = 0
  SET @Day2 = 0
IF IsNull (@Day3, 0) = 0
  SET @Day3 = 0
IF IsNull (@Day4, 0) = 0
  SET @Day4 = 0

IF (@Day2=0) AND (@PriceOverwritten=0 OR @PriceOverwritten IS NULL) AND (@OldProductNumber <> NULL) -- Normal product which is not overwritten or NUL product
BEGIN
  IF @OldProductNumber >= 1
    EXEC ProductPrices @OldProductNumber, @StartDate, @EndDate, @OldRoomTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT -- @AddonAmount MONEY = 0 OUTPUT, @AddonVatAmount MONEY=0 OUTPUT, @PlusProduct BIT=0
  ELSE -- NUL Product
  BEGIN
    SET @CalcArrivalPrice=0
    SET @CalcTotalPrice=0
  END

  UPDATE Reservations SET Price=@CalcArrivalPrice, TotalPrice=@CalcTotalPrice WHERE ReferenceNumber=@ReferenceNumber AND GuestNumber=@GuestNumber AND (Price<>@CalcArrivalPrice OR TotalPrice<>@CalcTotalPrice)
END
ELSE
BEGIN
  DECLARE @HSNameListReservationPerPerson BIT -- From HotelSetup
  SELECT @HSNameListReservationPerPerson=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE Upper (SearchKey) = Upper ('NameListReservationPerPerson')
  IF (IsNull (@HSNameListReservationPerPerson, 0)=0) or (@HSNameListReservationPerPerson=0)
    SET @HSNameListReservationPerPerson=1  -- default 1!

  DECLARE @HSOnePersonPaysForAll BIT -- From HotelSetup
  SELECT @HSOnePersonPaysForAll = Convert (INT, Keyvalue) FROM HotelSetup WITH (NOLOCK) WHERE Upper (SearchKey) =Upper ('OnePersonPaysForAll')
  IF IsNull (@HSOnePersonPaysForAll, 0)=0
    SET @HSOnePersonPaysForAll=0

  IF (@Day2=0) AND ((@PriceOverwritten=1) OR (@OldProductNumber = NULL)) -- Normal product where price is overwritten or res with no product and just a price
  BEGIN
    DECLARE @NumberOfDays INT
    SET @NumberOfDays = CONVERT(INT, @EndDate)-CONVERT(INT, @StartDate)
    IF @NumberOfDays < 1
      SET @NumberOfDays = 1

    IF @OldProductNumber >= 1
    BEGIN
      DECLARE @ProductNameListSetting TINYINT
      SELECT @ProductNameListSetting=NameListSetting FROM Products WITH (NOLOCK) WHERE ProductNumber=@OldProductNumber
      IF @ProductNameListSetting = NULL
        SET @ProductNameListSetting = 0
    END
/*            if not Boolean(StrToIntDef(HotelSetup.Setting('OnePersonPaysForAll', '1'), 1)) and
              ((Product1.Active and ((Product1.NameListSetting = nlsNameListPerPerson) or ((Product1.NameListSetting = nlsFollowHotelSetup) and (HSNameListResPerPerson = nlsNameListPerPerson)))) or // JP 20040210
              (not Product1.Active and (HSNameListResPerPerson = nlsNameListPerPerson))) then // JP 20040210
*/
    IF @HSOnePersonPaysForAll = 0 AND
      ((@OldProductNumber >= 1) AND ((@ProductNameListSetting = 1) OR (@ProductNameListSetting = 0 AND @HSNameListReservationPerPerson = 1)) OR
      (@OldProductNumber = NULL AND @HSNameListReservationPerPerson = 1))
      SET @CalcTotalPrice = @Price * @NumberOfDays * @NumberOfRooms * (@AdultPax + @Child1Pax + @Child2Pax)
    ELSE
    begin
      SET @CalcTotalPrice = @Price * @NumberOfDays * @NumberOfRooms
print '22'
print convert(char(8),@CalcTotalPrice)
    end

    UPDATE Reservations SET TotalPrice=@CalcTotalPrice WHERE ReferenceNumber=@ReferenceNumber AND GuestNumber=@GuestNumber AND TotalPrice<>@CalcTotalPrice
  END
  ELSE IF (@Day2>=1) -- + PRODUCT
  BEGIN
    DECLARE @NameListReservationPerPerson BIT -- From Product - updated throughout the code

    DECLARE @AddOnIfPriceOverwritten BIT
    SELECT @AddOnIfPriceOverwritten=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE Upper (SearchKey) = Upper ('AddOnIfPriceOverwritten')
    IF (IsNull (@AddOnIfPriceOverwritten, 0)=0)
      SET @AddOnIfPriceOverwritten=0

    DECLARE
    @TmpArrDateTime DATETIME,
    @TmpDepDateTime DATETIME,
    @CalcArrivalPrice1 MONEY,
    @CalcArrivalPrice2 MONEY,
    @CalcArrivalPrice3 MONEY,
    @CalcArrivalPrice4 MONEY,
    @CalcTotalPrice1 MONEY,
    @CalcTotalPrice2 MONEY,
    @CalcTotalPrice3 MONEY,
    @CalcTotalPrice4 MONEY

    SET @CalcTotalPrice =0
    SET @CalcArrivalPrice =0
    SET @CalcTotalPrice1 =0
    SET @CalcArrivalPrice1 =0
    SET @CalcTotalPrice2 =0
    SET @CalcArrivalPrice2 =0
    SET @CalcTotalPrice3 =0
    SET @CalcArrivalPrice3 =0
    SET @CalcTotalPrice4 =0
    SET @CalcArrivalPrice4 =0

    SET @TmpArrDateTime = Convert (DATETIME, @StartDate)

     IF @Day2=0
       SET @TmpDepDateTime = Convert (DATETIME, @EndDate)
     ELSE
       SET @TmpDepDateTime = Convert (DATETIME, @StartDate)+@Day2-1

print 'row1'
       IF @PriceOverwritten=0
         EXEC ProductPrices @OldProductNumber, @TmpArrDateTime, @TmpDepDateTime, @OldRoomTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @CalcTotalPrice1 OUTPUT, @CalcArrivalPrice1 OUTPUT
       ELSE
       BEGIN
         IF @AddOnIfPriceOverwritten = 1
         BEGIN
           SET @NumberOfDays = DATEDIFF (DAY,@TmpArrDateTime,@TmpDepDateTime)
           IF @NumberOfDays = 0 SET @NumberOfDays = 1
           EXEC CalcAddonPrice @FirstPrice1, @TmpArrDateTime, @NumberOfDays, @AdultPax, @Child1Pax, @Child2Pax, @OldProductNumber, @OldRoomTypeNumber, @CalcTotalPrice1 OUTPUT

           SET @CalcTotalPrice1 = @CalcTotalPrice1*@NumberOfRooms
         END
         ELSE
         BEGIN
           SET @CalcArrivalPrice1 = @FirstPrice1

           SET @CalcTotalPrice1 = @CalcArrivalPrice1 * DATEDIFF (DAY,@TmpArrDateTime,@TmpDepDateTime) *@NumberOfRooms

           -- NameListPerPerson contra OnePersonPaysForAll
           SELECT @NameListReservationPerPerson=NameListSetting FROM ReservationRoomLine WITH (NOLOCK) WHERE LineNumber=@LineNumber
           IF @NameListReservationPerPerson=0 OR IsNull(@NameListReservationPerPerson, 0)=0  -- follow hotelsetup
             SET @NameListReservationPerPerson=@HSNameListReservationPerPerson

           IF ((@OldProductNumber > 0 AND @NameListReservationPerPerson=1) or (@OldProductNumber = 0 AND @HSNameListReservationPerPerson=1)) AND (@HSOnePersonPaysForAll=0)
             SET @CalcTotalPrice1 = @CalcTotalPrice1*(@AdultPax+@Child1Pax+@Child2Pax) -- multiply with pax
         END
       END

       IF @Day2>0
       BEGIN
         SET @TmpArrDateTime = Convert (DATETIME, @StartDate)+@Day2-1
         IF @Day3=0
           SET @TmpDepDateTime = Convert (DATETIME, @EndDate)
         ELSE
           SET @TmpDepDateTime = Convert (DATETIME, @StartDate)+@Day3-1

print 'row2'
         IF @PriceOverwritten2=0
           EXEC ProductPrices @ProductNumber2, @TmpArrDateTime, @TmpDepDateTime, @OldRoomTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @CalcTotalPrice2 OUTPUT, @CalcArrivalPrice2 OUTPUT
         ELSE
         BEGIN
           IF @AddOnIfPriceOverwritten = 1
           BEGIN
             SET @NumberOfDays = DATEDIFF (DAY,@TmpArrDateTime,@TmpDepDateTime)
             IF @NumberOfDays = 0 SET @NumberOfDays = 1
             EXEC CalcAddonPrice @FirstPrice2, @TmpArrDateTime, @NumberOfDays, @AdultPax, @Child1Pax, @Child2Pax, @ProductNumber2, @OldRoomTypeNumber, @CalcTotalPrice2 OUTPUT

             SET @CalcTotalPrice2 = @CalcTotalPrice2*@NumberOfRooms
           END
           ELSE
           BEGIN
             SET @CalcArrivalPrice2 = @FirstPrice2

             SET @CalcTotalPrice2 = @CalcArrivalPrice2 * DATEDIFF (DAY,@TmpArrDateTime,@TmpDepDateTime) *@NumberOfRooms

             -- NameListPerPerson contra OnePersonPaysForAll
             SELECT @NameListReservationPerPerson=NameListSetting FROM ReservationRoomLine WITH (NOLOCK) WHERE LineNumber=@LineNumber
             IF @NameListReservationPerPerson=0 OR IsNull(@NameListReservationPerPerson, 0)=0  -- follow hotelsetup
               SET @NameListReservationPerPerson=@HSNameListReservationPerPerson

             IF ((@ProductNumber2 > 0 AND @NameListReservationPerPerson=1) or (@ProductNumber2 = 0 AND @HSNameListReservationPerPerson=1)) AND (@HSOnePersonPaysForAll=0)
               SET @CalcTotalPrice2 = @CalcTotalPrice2*(@Child2Pax+@Child1Pax+@Child2Pax) -- multiply with pax
           END
         END
       END
 
       IF @Day3>0
       BEGIN
         SET @TmpArrDateTime = Convert (DATETIME, @StartDate)+@Day3-1
         IF @Day4=0
           SET @TmpDepDateTime = Convert (DATETIME, @EndDate)
         ELSE
           SET @TmpDepDateTime = Convert (DATETIME, @StartDate)+@Day4-1

print 'row3'
         IF @PriceOverwritten3=0
           EXEC ProductPrices @ProductNumber3, @TmpArrDateTime, @TmpDepDateTime, @OldRoomTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @CalcTotalPrice3 OUTPUT, @CalcArrivalPrice3 OUTPUT
         ELSE
         BEGIN
           IF @AddOnIfPriceOverwritten = 1
           BEGIN
             SET @NumberOfDays = DATEDIFF (DAY,@TmpArrDateTime,@TmpDepDateTime)
             IF @NumberOfDays = 0 SET @NumberOfDays = 1
             EXEC CalcAddonPrice @FirstPrice3, @TmpArrDateTime, @NumberOfDays, @AdultPax, @Child1Pax, @Child2Pax, @ProductNumber3, @OldRoomTypeNumber, @CalcTotalPrice3 OUTPUT

             SET @CalcTotalPrice3 = @CalcTotalPrice3*@NumberOfRooms
           END
           ELSE
           BEGIN
             SET @CalcArrivalPrice3 = @FirstPrice3

             SET @CalcTotalPrice3 = @CalcArrivalPrice3 * DATEDIFF (DAY,@TmpArrDateTime,@TmpDepDateTime) *@NumberOfRooms

             -- NameListPerPerson contra OnePersonPaysForAll
             SELECT @NameListReservationPerPerson=NameListSetting FROM ReservationRoomLine WITH (NOLOCK) WHERE LineNumber=@LineNumber
             IF @NameListReservationPerPerson=0 OR IsNull(@NameListReservationPerPerson, 0)=0  -- follow hotelsetup
               SET @NameListReservationPerPerson=@HSNameListReservationPerPerson

             IF ((@ProductNumber3 > 0 AND @NameListReservationPerPerson=1) or (@ProductNumber3 = 0 AND @HSNameListReservationPerPerson=1)) AND (@HSOnePersonPaysForAll=0)
               SET @CalcTotalPrice3 = @CalcTotalPrice3*(@AdultPax+@Child1Pax+@Child2Pax) -- multiply with pax
           END
         END
       END

       IF @Day4>0
       BEGIN
         SET @TmpArrDateTime = Convert (DATETIME, @StartDate)+@Day4-1
         SET @TmpDepDateTime = Convert (DATETIME, @EndDate)
print 'row4'
         IF @PriceOverwritten4=0
           EXEC ProductPrices @ProductNumber4, @TmpArrDateTime, @TmpDepDateTime, @OldRoomTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @CalcTotalPrice4 OUTPUT, @CalcArrivalPrice4 OUTPUT
         ELSE
         BEGIN
           IF @AddOnIfPriceOverwritten = 1
           BEGIN
             SET @NumberOfDays = DATEDIFF (DAY,@TmpArrDateTime,@TmpDepDateTime)
             IF @NumberOfDays = 0 SET @NumberOfDays = 1
             EXEC CalcAddonPrice @FirstPrice4, @TmpArrDateTime, @NumberOfDays, @AdultPax, @Child1Pax, @Child2Pax, @ProductNumber4, @OldRoomTypeNumber, @CalcTotalPrice4 OUTPUT

             SET @CalcTotalPrice4 = @CalcTotalPrice4*@NumberOfRooms
           END
           ELSE
           BEGIN
             SET @CalcArrivalPrice4 = @FirstPrice4

             SET @CalcTotalPrice4 = @CalcArrivalPrice4 * DATEDIFF (DAY,@TmpArrDateTime,@TmpDepDateTime) *@NumberOfRooms

             -- NameListPerPerson contra OnePersonPaysForAll
             SELECT @NameListReservationPerPerson=NameListSetting FROM ReservationRoomLine WITH (NOLOCK) WHERE LineNumber=@LineNumber
             IF @NameListReservationPerPerson=0 OR IsNull(@NameListReservationPerPerson, 0)=0  -- follow hotelsetup
               SET @NameListReservationPerPerson=@HSNameListReservationPerPerson

             IF ((@ProductNumber4 > 0 AND @NameListReservationPerPerson=1) or (@ProductNumber4 = 0 AND @HSNameListReservationPerPerson=1)) AND (@HSOnePersonPaysForAll=0)
               SET @CalcTotalPrice4 = @CalcTotalPrice4*(@AdultPax+@Child1Pax+@Child2Pax) -- multiply with pax
           END
         END
       END
/*
       print '+product calced prices:'
       print convert (varchar, @CalcArrivalPrice1)
       print convert (varchar, @CalcTotalPrice1)
       print convert (varchar, @CalcArrivalPrice2)
       print convert (varchar, @CalcTotalPrice2)
       print convert (varchar, @CalcArrivalPrice3)
       print convert (varchar, @CalcTotalPrice3)
       print convert (varchar, @CalcArrivalPrice4)
       print convert (varchar, @CalcTotalPrice4)
*/
       SET @CalcArrivalPrice=@CalcArrivalPrice1
       SET @CalcTotalPrice=@CalcTotalPrice1+@CalcTotalPrice2+@CalcTotalPrice3+@CalcTotalPrice4

       UPDATE Reservations SET Price=@CalcArrivalPrice, TotalPrice=@CalcTotalPrice, FirstPrice1=@CalcArrivalPrice1, TotalPrice1=@CalcTotalPrice1, FirstPrice2=@CalcArrivalPrice2, TotalPrice2=@CalcTotalPrice2, FirstPrice3=@CalcArrivalPrice3, TotalPrice3=@CalcTotalPrice3, FirstPrice4=@CalcArrivalPrice4, TotalPrice4=@CalcTotalPrice4 
       WHERE ReferenceNumber=@ReferenceNumber AND GuestNumber=@GuestNumber AND (Price<>@CalcArrivalPrice OR TotalPrice<>@CalcTotalPrice)
  END
END

