CREATE PROCEDURE DBO.[GetRooms] @ReferenceNumber INT, @GuestNumber INT AS
SET NOCOUNT ON
/*
  nb20090320 +product reset arrivalprice which is also used to indicate price overwritten
  nb20080625 Overwritten price vat problem fixed  (new attempt)
  nb20071031 Attempt to fix overwritten price vat problem on several days roomlines
  nb20070813 reset productpricearrival for each roomline
  nb20070117 plus product finished, errors found in call to GetProductPrice and VatArrival specification
  nb20061111 linenumbers fixed  NB20060228: Order by syncronized with those one from delphi
  NB20060203: PriceOverwritten logik var byttet om fix
  NB20050628: Created
  NOTE: Get the room info

*/
DECLARE 
  @SegmentNumber INT
, @PointofSaleNumber INT
SELECT @SegmentNumber= MarketSegment 
, @PointOfSaleNumber= PointOfSale
FROM Reservations WITH (NOLOCK)
WHERE ReferenceNumber=@ReferenceNumber AND Reservations.GuestNumber= @GuestNumber
DECLARE 
--Input parameters
@LineNumber INT
, @ProductNumber INT
, @ArrivalDate DATETIME
, @DepartureDate DATETIME
, @DayProduct INT 
, @BookedTypeNumber INT
, @NumberOfRooms INT
, @AdultPax INT
, @Child1Pax INT
, @Child2Pax INT
, @PlusProduct BIT
, @VatPercent FLOAT 
, @VatFrom INT
, @PriceOverwritten BIT
, @Price MONEY
, @FirstPrice1 MONEY
, @TotalPrice1 MONEY
, @PriceOverwritten2 BIT
, @FirstPrice2 MONEY
, @TotalPrice2 MONEY
, @Day2 INT
, @ProductNumber2 INT
, @PriceOverwritten3 BIT
, @FirstPrice3 MONEY
, @TotalPrice3 MONEY
, @Day3 INT
, @ProductNumber3 INT
, @PriceOverwritten4 BIT
, @FirstPrice4 MONEY
, @TotalPrice4 MONEY
, @Day4 INT
, @ProductNumber4 INT
, @SidehouseNumber INT
, @NameListSetting INT
, @ReservationPackageNumber INT
, @PriceProduct REAL
, @PriceProductArrival REAL
, @VatProduct MONEY
, @VatProductArrival MONEY
, @Msg VARCHAR(1000)
SET @DayProduct=0
DECLARE LineCur Cursor Fast_Forward Read_Only For
SELECT LineNumber, BookedTypeNumber, Convert(DateTime, ArrivalDate) AS ArrivalDate, Convert(DateTime, DepartureDate) AS DepartureDate
, Adults, Children1, Children2, ProductNumber, NumberOfRooms
, PriceOverwritten
, Price
, FirstPrice1
, TotalPrice1
, PriceOverwritten2
, FirstPrice2
, TotalPrice2
, Day2
, ProductNumber2
, PriceOverwritten3
, FirstPrice3
, TotalPrice3
, Day3
, ProductNumber3
, PriceOverwritten4
, FirstPrice4
, TotalPrice4
, Day4
, ProductNumber4
, NameListSetting
FROM ReservationRoomLine WITH (NOLOCK)
WHERE LineNumber IN (
  SELECT LineNumber
  FROM Reservations WITH (NOLOCK) INNER JOIN
    ReservationRoomLineXTab WITH (NOLOCK) ON Reservations.ReferenceNumber=ReservationRoomLineXTab.ReferenceNumber AND 
      Reservations.GuestNumber=ReservationRoomLineXTab.GuestNumber 
  WHERE Reservations.ReferenceNumber=@ReferenceNumber AND (@GuestNumber = 0 OR Reservations.GuestNumber= @GuestNumber) AND Reservations.Status NOT IN (7,8)
  ) 
OPEN LineCur
FETCH NEXT FROM LineCur 
  INTO @LineNumber, @BookedTypeNumber,@ArrivalDate,@DepartureDate,
    @AdultpAX,@Child1Pax,@Child2Pax,@ProductNumber,@NumberOfRooms,
    @PriceOverwritten,@Price,@FirstPrice1,@TotalPrice1,
    @PriceOverwritten2,@FirstPrice2,@TotalPrice2,@Day2,@ProductNumber2,
    @PriceOverwritten3,@FirstPrice3,@TotalPrice3,@Day3,@ProductNumber3,
    @PriceOverwritten4,@FirstPrice4,@TotalPrice4,@Day4,@ProductNumber4,
    @NameListSetting
CREATE TABLE #PriceRooms(
  LineNumber INT NOT NULL
  , PriceTotal MONEY NOT NULL
  , VatTotal MONEY NOT NULL
  , PriceArrival MONEY NOT NULL
  , VatArrival MONEY NOT NULL
)
IF (@@FETCH_STATUS = 0)
BEGIN
  DECLARE @OldProductNumber INT, @OldBookedTypeNumber INT
  SET @OldProductNumber = @ProductNumber
  SET @OldBookedTypeNumber = @BookedTypeNumber
  EXEC GetVatValueProduct @VatPercent OUTPUT, @VatFrom OUTPUT, @DayProduct, @ProductNumber, @SegmentNumber, @PointofSaleNumber, @BookedTypeNumber
END
WHILE (@@FETCH_STATUS = 0) -- safe
BEGIN
/*
  IF ISNULL(@PriceOverwritten, 0) = 0
    SET @PriceProduct=@Price
  ELSE
    SET @PriceProduct= 0
*/--nb20060203 
-- nb20070813 reset productpricearrival for each roomline
  IF ISNULL(@PriceOverwritten, 0) = 0
  begin
    SET @PriceProduct= 0
    set @PriceProductArrival= 0
  end
  ELSE
  begin
    /*
    SET @PriceProduct=@Price
    set @PriceProductArrival= @FirstPrice1
    */--nb20071031 Attempt to fix overwritten price vat problem on several days roomlines
    SET @PriceProduct=@Price
    set @PriceProductArrival= @Price
  end
  IF (@Price <> 0) or ISNULL(@PriceOverwritten, 0) = 0
  IF (ISNULL(@Day2, 0) = 0)
  BEGIN
    IF @VatFrom>1 AND @OldProductNumber <> @ProductNumber AND @OldBookedTypeNumber <> @BookedTypeNumber --Call only if needed
    BEGIN
      SET @OldProductNumber = @ProductNumber
      SET @OldBookedTypeNumber = @BookedTypeNumber
      EXEC GetVatValueProduct @VatPercent OUTPUT, @VatFrom OUTPUT, @DayProduct, @ProductNumber, @SegmentNumber, @PointofSaleNumber, @BookedTypeNumber
    END
/*DEBUG BEGIN nb20060204 - do not remove
  PRINT 'Procedure GetRooms begin --------------------------------'
  PRINT @PriceOverwritten
  SET @Msg = 'DECLARE @PriceProduct MONEY, @PriceProductArrival MONEY, @VatProduct MONEY, @VatProductArrival MONEY, @ArrivalDate DATETIME, @DepartureDate DATETIME'+CHAR(13)
  IF ISNULL( @PriceProduct, -1) = -1 
    SET @Msg = @Msg+'SET @PriceProduct = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @PriceProduct = '+CONVERT(VARCHAR,@PriceProduct)+CHAR(13)
  IF ISNULL( @PriceProductArrival, -1) = -1 
    SET @Msg = @Msg+'SET @PriceProductArrival = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg+'SET @PriceProductArrival = '+CONVERT(VARCHAR,@PriceProductArrival)+CHAR(13)
  IF ISNULL( @VatProduct, -1) = -1 
    SET @Msg = @Msg+'SET @VatProduct = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @VatProduct = '+CONVERT(VARCHAR,@VatProduct)+CHAR(13)
  IF ISNULL( @VatProductArrival, -1) = -1 
    SET @Msg = @Msg+'SET @VatProductArrival = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @VatProductArrival = '+CONVERT(VARCHAR,@VatProductArrival)+CHAR(13)
  SET @Msg = @Msg + 'SET @ArrivalDate = CONVERT(DATETIME, '''+CONVERT(VARCHAR, ISNULL(@ArrivalDate, -1), 112)+''' , 112)'+CHAR(13)
  SET @Msg = @Msg + 'SET @DepartureDate = CONVERT(DATETIME, '''+CONVERT(VARCHAR, ISNULL(@DepartureDate, -1), 112)+''' , 112)'+CHAR(13)
  SET @Msg = @Msg+'EXEC GetPriceProduct @PriceProduct OUTPUT, @PriceProductArrival OUTPUT, @VatProduct OUTPUT, @VatProductArrival OUTPUT, '+
    CONVERT(VARCHAR, ISNULL(@SegmentNumber, -1))+', '+CONVERT(VARCHAR, ISNULL(@PointofSaleNumber, -1))+', '+CONVERT(VARCHAR, ISNULL(@DayProduct, -1))+', '+CONVERT(VARCHAR, ISNULL(@ProductNumber, -1))+
  ', @ArrivalDate, @DepartureDate, '+CONVERT(VARCHAR, ISNULL(@BookedTypeNumber, -1))+', '+CONVERT(VARCHAR, ISNULL(@NumberOfRooms, -1))+
  ', '+CONVERT(VARCHAR, ISNULL(@AdultPax, -1))+', '+CONVERT(VARCHAR, ISNULL(@Child1Pax, -1))+', '+CONVERT(VARCHAR, ISNULL(@Child2Pax, -1))+', '+CONVERT(VARCHAR, ISNULL(@VatPercent, -1)) + CHAR(13)
    SET @Msg = @Msg + 'PRINT CONVERT(varchar, @PriceProduct) ' + CONVERT(varchar, ISNULL(@PriceProduct, -1)) + CHAR(13)
    SET @Msg = @Msg + 'PRINT CONVERT(varchar, @PriceProductArrival) ' + CONVERT(varchar, ISNULL(@PriceProductArrival, -1))+ CHAR(13)
    SET @Msg = @Msg + 'PRINT CONVERT(varchar, @VatProduct) ' + CONVERT(varchar, ISNULL(@VatProduct, -1))+ CHAR(13)
    SET @Msg = @Msg + 'PRINT CONVERT(varchar, @VatProductArrival) ' + CONVERT(varchar, ISNULL(@VatProductArrival, -1)) + CHAR(13)
  PRINT @Msg
  PRINT 'Procedure GetRooms end --------------------------------'
DEBUG END*/
    EXEC GetPriceProduct @PriceProduct OUTPUT, @PriceProductArrival OUTPUT, @VatProduct OUTPUT, @VatProductArrival OUTPUT
    , @SegmentNumber, @PointofSaleNumber, @DayProduct, @ProductNumber, @ArrivalDate, @DepartureDate, @BookedTypeNumber, @NumberOfRooms
    , @AdultPax, @Child1Pax, @Child2Pax, @VatPercent

    INSERT INTO #PriceRooms (LineNumber, PriceTotal, VatTotal, PriceArrival, VatArrival) VALUES (@LineNumber, @PriceProduct, @VatProduct, @PriceProductArrival, @VatProductArrival)
--PRINT '@VatProductArrival' +CONVERT(VARCHAR, @VatProductArrival)
--PRINT '@VatProduct' +CONVERT(VARCHAR, @VatProduct)

  END
  ELSE
  BEGIN
    PRINT '+Product'
    DECLARE @MyArrivalDate DATETIME, @MyDepartureDate DATETIME, @MyDay INT, @MyProductNumber INT
    , @MyPriceProduct MONEY, @MyPriceProductArrival MONEY, @MyVatProduct MONEY, @MyVatProductArrival MONEY
    SET @MyDay= -1
    SET @MyArrivalDate = @ArrivalDate
    SET @PriceProductArrival = 0
    SET @PriceProduct = 0
    SET @VatProduct = 0
    SET @VatProductArrival = 0
    SET @DayProduct= 2
    WHILE ISNULL(@MyDay, 0) <> 0
    BEGIN
--PRINT 'PlusProduct loop'
      SET @MyPriceProductArrival = 0;
      SET @MyPriceProduct = 0
      IF @MyDay = -1
      BEGIN
        SET @MyDay= @Day2 
        SET @MyProductNumber= @ProductNumber
        IF ISNULL(@PriceOverwritten,0) = 1
          SET @MyPriceProduct = @TotalPrice1--*@NumberOfRooms
      END
      ELSE IF @MyDay = @Day2
      BEGIN
        SET @MyDay=@Day3
        SET @MyProductNumber= @ProductNumber2
        IF ISNULL(@PriceOverwritten2,0) = 1
          SET @MyPriceProduct = @TotalPrice2--*@NumberOfRooms
      END
      ELSE IF @MyDay = @Day3
      BEGIN
        SET @MyDay=@Day4
        SET @MyProductNumber= @ProductNumber3
        IF ISNULL(@PriceOverwritten3,0) = 1
          SET @MyPriceProduct = @TotalPrice3--*@NumberOfRooms
      END
      ELSE IF @MyDay = @Day4
      BEGIN
        SET @MyDay= NULL
        SET @MyProductNumber= @ProductNumber4
        IF ISNULL(@PriceOverwritten4,0) = 1
          SET @MyPriceProduct = @TotalPrice4--*@NumberOfRooms
      END
      IF ISNULL(@MyDay, 0) <> @Day2
        SET @MyArrivalDate= @MyDepartureDate
      IF (ISNULL(@MyDay, 0) = 0)
        SET @MyDepartureDate = @DepartureDate
      ELSE
        SET @MyDepartureDate =Convert (DATETIME, @ArrivalDate)+@MyDay-1
      IF @VatFrom>1 AND @OldProductNumber <> @ProductNumber AND @OldBookedTypeNumber <> @BookedTypeNumber --Call only if needed
      BEGIN
        SET @OldProductNumber = @ProductNumber
        SET @OldBookedTypeNumber = @BookedTypeNumber
        EXEC GetVatValueProduct @VatPercent OUTPUT, @VatFrom OUTPUT, @DayProduct, @ProductNumber, @SegmentNumber, @PointofSaleNumber, @BookedTypeNumber
      END
/*
PRINT 'Price'
PRINT CONVERT(VARCHAR,@MyPriceProduct)
PRINT @PriceOverwritten
PRINT @PriceOverwritten2
*/
--    IF @MyPriceProduct=0
--    BEGIN
/*DEBUG BEGIN nb20060204 - do not remove
  PRINT @PriceOverwritten
  SET @Msg = 'DECLARE @PriceProduct MONEY, @PriceProductArrival MONEY, @VatProduct MONEY, @VatProductArrival MONEY, @ArrivalDate DATETIME, @DepartureDate DATETIME'+CHAR(13)
  IF ISNULL( @PriceProduct, -1) = -1 
    SET @Msg = @Msg+'SET @PriceProduct = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @PriceProduct = '+CONVERT(VARCHAR,@MyPriceProduct)+CHAR(13)
  IF ISNULL( @PriceProductArrival, -1) = -1 
    SET @Msg = @Msg+'SET @PriceProductArrival = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg+'SET @PriceProductArrival = '+CONVERT(VARCHAR,@PriceProductArrival)+CHAR(13)
  IF ISNULL( @VatProduct, -1) = -1 
    SET @Msg = @Msg+'SET @VatProduct = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @VatProduct = '+CONVERT(VARCHAR,@VatProduct)+CHAR(13)
  IF ISNULL( @VatProductArrival, -1) = -1 
    SET @Msg = @Msg+'SET @VatProductArrival = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @VatProductArrival = '+CONVERT(VARCHAR,@VatProductArrival)+CHAR(13)
  SET @Msg = @Msg + 'SET @ArrivalDate = CONVERT(DATETIME, '''+CONVERT(VARCHAR, ISNULL(@MyArrivalDate, -1), 112)+''' , 112)'+CHAR(13)
  SET @Msg = @Msg + 'SET @DepartureDate = CONVERT(DATETIME, '''+CONVERT(VARCHAR, ISNULL(@MyDepartureDate, -1), 112)+''' , 112)'+CHAR(13)

  SET @Msg = @Msg+'EXEC GetPriceProduct @PriceProduct, @PriceProductArrival, @VatProduct, @VatProductArrival, '+
    CONVERT(VARCHAR, ISNULL(@SegmentNumber, -1))+', '+CONVERT(VARCHAR, ISNULL(@PointofSaleNumber, -1))+', '+CONVERT(VARCHAR, ISNULL(@DayProduct, -1))+', '+CONVERT(VARCHAR, ISNULL(@ProductNumber, -1))+
  ', @ArrivalDate, @DepartureDate, '+CONVERT(VARCHAR, ISNULL(@BookedTypeNumber, -1))+', '+CONVERT(VARCHAR, ISNULL(@NumberOfRooms, -1))+
  ', '+CONVERT(VARCHAR, ISNULL(@AdultPax, -1))+', '+CONVERT(VARCHAR, ISNULL(@Child1Pax, -1))+', '+CONVERT(VARCHAR, ISNULL(@Child2Pax, -1))+', '+CONVERT(VARCHAR, ISNULL(@VatPercent, -1))
  PRINT @Msg
DEBUG END*/
        EXEC GetPriceProduct @MyPriceProduct OUTPUT, @MyPriceProductArrival OUTPUT, @MyVatProduct OUTPUT, @MyVatProductArrival OUTPUT
        , @SegmentNumber, @PointofSaleNumber, @DayProduct, @MyProductNumber, @MyArrivalDate, @MyDepartureDate, @BookedTypeNumber, @NumberOfRooms
        , @AdultPax, @Child1Pax, @Child2Pax, @VatPercent
--    END
      IF ISNULL(@MyPriceProduct, 0) > 0
        SET @PriceProduct = @PriceProduct + @MyPriceProduct
      IF ISNULL(@MyVatProduct, 0) > 0
        SET @VatProduct = @VatProduct + @MyVatProduct
      IF ISNULL(@MyDay, 0) = @Day2
      BEGIN
        IF ISNULL(@MyPriceProductArrival, 0) > 0
          SET @PriceProductArrival = @PriceProductArrival + @MyPriceProductArrival
        IF ISNULL(@MyVatProductArrival, 0) > 0
          SET @VatProductArrival = @VatProductArrival + @MyVatProductArrival
      END
/*
PRINT  ' @MyDay = ' + CONVERT(VARCHAR, @MyDay) 
PRINT  ' @Day2 = ' + CONVERT(VARCHAR, @Day2)
PRINT  ' @MyArrivalDate = ' + CONVERT(VARCHAR, @MyArrivalDate)
PRINT  ' @MyDepartureDate = ' + CONVERT(VARCHAR, @MyDepartureDate)
PRINT ' @MyPriceProduct = ' + CONVERT(VARCHAR, @MyPriceProduct )
PRINT  ' @MyPriceProductArrival = ' + CONVERT(VARCHAR, @MyPriceProductArrival)
PRINT  ' @MyVatProduct = ' + CONVERT(VARCHAR, @MyVatProduct )
PRINT  ' @MyVatProductArrival = ' + CONVERT(VARCHAR, @MyVatProductArrival)
*/
    END 
/*
PRINT 'PlusProduct after loop'
    PRINT CONVERT(VARCHAR, @PriceProduct )
    PRINT CONVERT(VARCHAR, @PriceProductArrival)
    PRINT CONVERT(VARCHAR, @VatProduct)
    PRINT CONVERT(VARCHAR, @VatProductArrival)
*/
    INSERT INTO #PriceRooms (LineNumber, PriceTotal, VatTotal, PriceArrival, VatArrival) VALUES (@LineNumber, @PriceProduct, @VatProduct, @PriceProductArrival, @VatProductArrival)
  END
  ELSE
    INSERT INTO #PriceRooms (LineNumber, PriceTotal, VatTotal, PriceArrival, VatArrival) VALUES (@LineNumber, 0, 0, 0, 0)
FETCH NEXT FROM LineCur
  INTO @LineNumber, @BookedTypeNumber,@ArrivalDate,@DepartureDate,
    @AdultpAX,@Child1Pax,@Child2Pax,@ProductNumber,@NumberOfRooms,
    @PriceOverwritten,@Price,@FirstPrice1,@TotalPrice1,
    @PriceOverwritten2,@FirstPrice2,@TotalPrice2,@Day2,@ProductNumber2,
    @PriceOverwritten3,@FirstPrice3,@TotalPrice3,@Day3,@ProductNumber3,
    @PriceOverwritten4,@FirstPrice4,@TotalPrice4,@Day4,@ProductNumber4,
    @NameListSetting
END

SELECT BookedTypeNumber, Convert(DateTime, ArrivalDate) AS ArrivalDate, Convert(DateTime, DepartureDate) AS DepartureDate
, Adults, Children1, Children2 
, RRL.Price
, SUM(RRL.NumberOfRooms) AS NumberOfRooms
, SUM(PriceTotal) AS TotalPrice
, VatArrival
, SUM(VatTotal) AS VatTotal
FROM dbo.ReservationRoomline RRL WITH (NOLOCK) INNER JOIN 
  #PriceRooms PR WITH (NOLOCK) ON PR.LineNumber = RRL.LineNumber
WHERE RRL.LineNumber  IN (
  SELECT LineNumber
  FROM dbo.Reservations R WITH (NOLOCK) INNER JOIN
    dbo.ReservationRoomLineXTab RLX WITH (NOLOCK) ON R.ReferenceNumber= RLX.ReferenceNumber AND R.GuestNumber= RLX.GuestNumber AND R.Status NOT IN (7,8)
  WHERE RLX.ReferenceNumber=@ReferenceNumber
) 
GROUP BY RRL.NumberOfRooms, RRL.BookedTypeNumber, RRL.ArrivalDate, RRL.DepartureDate, RRL.Adults, RRL.Children1, RRL.Children2, RRL.Price, RRL.TotalPrice, VatArrival, VatTotal
ORDER BY RRL.ArrivalDate, RRL.DepartureDate, RRL.BookedTypeNumber, RRL.Adults, RRL.Children1, RRL.Children2, RRL.Price, RRL.TotalPrice, RRL.NumberOfRooms

DROP TABLE #PriceRooms
CLOSE LineCur
DEALLOCATE LineCur