CREATE PROCEDURE [dbo].[GetContentRoomLine] @ReferenceNumber INT, @GuestNumber INT, @DiscountInc BIT, @FkAccountTaxGroup INT = NULL AS
SET NOCOUNT ON
/*
NB20190704: Created
NOTE: Get the reservation roomline content
*/
DECLARE @DebugLevel TINYINT, @tPre DATETIME, @tPost DATETIME
SET @DebugLevel = 0
IF @DebugLevel > 0
BEGIN
  SET @tPre = GETDATE();
  PRINT 'Call SP [GetContentRoomLine] begin --------------------------------'
END
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 
, @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)
, @ProductContentInt BIT
, @TblPriceRoomsInt BIT
SET @DayProduct=0
IF OBJECT_ID('tempdb..#ProductContent') IS NULL
BEGIN
  CREATE TABLE #ProductContent
  (  
    Price MONEY not null, 
    Tax MONEY not null,
    Kind TINYINT not null, --1: package arrival; 2: package stay and departure 3: product arrival; 4: product stay and departure; 5: addon
    FkAccountTaxGroup INT null,
    ProductNumber INT null, --null used temporary as flag 4 filtering done from undone packet records (SUM = PackagePrice and PackagePriceArrival
    LineNumber INT null
  )
  SET @ProductContentInt = 1
END
ELSE
  SET @ProductContentInt = 0

DECLARE @NameListCreated BIT
IF (SELECT COUNT(*) FROM Reservations R (NOLOCK) WHERE R.ReferenceNumber = @ReferenceNumber) = 1
  SET @NameListCreated = 0
ELSE
  SET @NameListCreated = 1

DECLARE LineCur Cursor Fast_Forward Read_Only For
SELECT LineNumber, BookedTypeNumber, NumberOfRooms, NameListSetting
, Convert(DateTime, ArrivalDate) AS ArrivalDate, Convert(DateTime, DepartureDate) AS DepartureDate
, Adults, Children1, Children2, ProductNumber
, PriceOverwritten
, Price
, FirstPrice1
, TotalPrice1
, PriceOverwritten2
, FirstPrice2
, TotalPrice2
, Day2
, ProductNumber2
, PriceOverwritten3
, FirstPrice3
, TotalPrice3
, Day3
, ProductNumber3
, PriceOverwritten4
, FirstPrice4
, TotalPrice4
, Day4
, ProductNumber4
FROM ReservationRoomLine WITH (NOLOCK)
WHERE 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 
  WHERE R.ReferenceNumber=@ReferenceNumber AND (@GuestNumber = 0 OR R.GuestNumber= @GuestNumber) AND R.Status NOT IN (7,8)
  ) 

/*SELECT RL.LineNumber, RL.BookedTypeNumber
, RL.NumberOfRooms
, NameListSetting
, CASE WHEN @NameListCreated = 0 THEN Convert(DateTime, RL.ArrivalDate) ELSE Convert(DateTime, R.ArrivalDate) END AS ArrivalDate
, CASE WHEN @NameListCreated = 0 THEN Convert(DateTime, RL.DepartureDate) ELSE Convert(DateTime, R.DepartureDate) END AS DepartureDate
, CASE WHEN @NameListCreated = 0 THEN RL.Adults ELSE R.Adults END AS Adults
, CASE WHEN @NameListCreated = 0 THEN RL.Children1 ELSE R.Children1 END AS Children1
, CASE WHEN @NameListCreated = 0 THEN RL.Children2 ELSE R.Children2 END AS Children2
, CASE WHEN @NameListCreated = 0 THEN RL.ProductNumber ELSE R.ProductNumber END AS ProductNumber
, CASE WHEN @NameListCreated = 0 THEN RL.PriceOverwritten ELSE R.PriceOverwritten END AS PriceOverwritten
, CASE WHEN @NameListCreated = 0 THEN RL.Price ELSE R.Price END AS Price
, CASE WHEN @NameListCreated = 0 THEN RL.FirstPrice1 ELSE R.FirstPrice1 END AS FirstPrice1
, CASE WHEN @NameListCreated = 0 THEN RL.TotalPrice1 ELSE R.TotalPrice1 END AS TotalPrice1
, CASE WHEN @NameListCreated = 0 THEN RL.PriceOverwritten2 ELSE R.PriceOverwritten2 END AS PriceOverwritten2
, CASE WHEN @NameListCreated = 0 THEN RL.FirstPrice2 ELSE R.FirstPrice2 END AS FirstPrice2
, CASE WHEN @NameListCreated = 0 THEN RL.TotalPrice2 ELSE R.TotalPrice2 END AS TotalPrice2
, CASE WHEN @NameListCreated = 0 THEN RL.Day2 ELSE R.Day2 END AS Day2
, CASE WHEN @NameListCreated = 0 THEN RL.ProductNumber2 ELSE R.ProductNumber2 END AS ProductNumber2
, CASE WHEN @NameListCreated = 0 THEN RL.PriceOverwritten3 ELSE R.PriceOverwritten3 END AS PriceOverwritten3
, CASE WHEN @NameListCreated = 0 THEN RL.FirstPrice3 ELSE R.FirstPrice3 END AS FirstPrice3
, CASE WHEN @NameListCreated = 0 THEN RL.TotalPrice3 ELSE R.TotalPrice3 END AS TotalPrice3
, CASE WHEN @NameListCreated = 0 THEN RL.Day3 ELSE R.Day3 END AS Day3
, CASE WHEN @NameListCreated = 0 THEN RL.ProductNumber3 ELSE R.ProductNumber3 END AS ProductNumber3
, CASE WHEN @NameListCreated = 0 THEN RL.PriceOverwritten4 ELSE R.PriceOverwritten4 END AS PriceOverwritten4
, CASE WHEN @NameListCreated = 0 THEN RL.FirstPrice4 ELSE R.FirstPrice4 END AS FirstPrice4
, CASE WHEN @NameListCreated = 0 THEN RL.TotalPrice4 ELSE R.TotalPrice4 END AS TotalPrice4
, CASE WHEN @NameListCreated = 0 THEN RL.Day4 ELSE R.Day4 END AS Day4
, CASE WHEN @NameListCreated = 0 THEN RL.ProductNumber4 ELSE R.ProductNumber4 END AS ProductNumber4
FROM dbo.Reservations R (NOLOCK)  
  INNER JOIN dbo.ReservationRoomLineXTab RLX (NOLOCK) ON R.ReferenceNumber=RLX.ReferenceNumber AND R.GuestNumber=RLX.GuestNumber 
  INNER JOIN dbo.ReservationRoomLine RL (NOLOCK) ON RL.LineNumber = RLX.LineNumber
WHERE R.ReferenceNumber=@ReferenceNumber AND (@GuestNumber = 0 OR R.GuestNumber= @GuestNumber) AND R.Status NOT IN (7,8)
ORDER BY RL.LineNumber
*/
OPEN LineCur
FETCH NEXT FROM LineCur 
  INTO @LineNumber, @BookedTypeNumber, @NumberOfRooms, @NameListSetting
  , @ArrivalDate,@DepartureDate,
    @AdultpAX,@Child1Pax,@Child2Pax,@ProductNumber,
    @PriceOverwritten,@Price,@FirstPrice1,@TotalPrice1,
    @PriceOverwritten2,@FirstPrice2,@TotalPrice2,@Day2,@ProductNumber2,
    @PriceOverwritten3,@FirstPrice3,@TotalPrice3,@Day3,@ProductNumber3,
    @PriceOverwritten4,@FirstPrice4,@TotalPrice4,@Day4,@ProductNumber4
IF (@@FETCH_STATUS = 0)
BEGIN
  DECLARE @OldProductNumber INT, @OldBookedTypeNumber INT, @TaxFrom INT
  SET @OldProductNumber = @ProductNumber
  SET @OldBookedTypeNumber = @BookedTypeNumber
  SELECT @TaxFrom=CONVERT(INT,KeyValue) FROM HotelSetup WITH (NOLOCK)  WHERE  SearchKey='BookkeepingReport'
  EXEC dbo.GetProductInfo @FkAccountTaxGroup OUTPUT, @VatPercent OUTPUT, @TaxFrom, @DayProduct, @ProductNumber, @SegmentNumber, @PointofSaleNumber, @BookedTypeNumber
END
WHILE (@@FETCH_STATUS = 0) 
BEGIN
/*
IF @Linenumber = 156929 or @Linenumber =156940 --@Linenumber = 156929 or @Linenumber =156940 
BEGIN
*/
  IF @DebugLevel > 0
    PRINT 'Linenumber: ' + CONVERT(VARCHAR, @LineNumber)
  SET @DayProduct= 0
  IF ISNULL(@PriceOverwritten, 0) = 0
  begin
    SET @PriceProduct= 0
    set @PriceProductArrival= NULL
  end
  ELSE
  begin
    SET @PriceProduct=@Price
    set @PriceProductArrival= @Price
  end
  DECLARE @DiscountRate smallmoney, @DiscountNettoRate smallmoney, @DiscountAsNettoPrice BIT, @DiscountPercent smallmoney
  SELECT TOP 1 @DiscountAsNettoPrice = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE 'DiscountAsNettoPrice'
  IF @ProductContentInt = 0 OR @DiscountAsNettoPrice = 1 OR @DiscountInc = 1
  BEGIN
    SELECT TOP 1 @DiscountRate = CASE WHEN (ISNULL(RA.DiscountRate, 0) = 0) THEN RA.RoomDiscountRate ELSE DiscountRate END 
    FROM dbo.ReservationAddress RA (NOLOCK) 
      INNER JOIN dbo.ReservationAddressXTab RAX (NOLOCK) ON RAX.AddressNumber = RA.AddressNumber
	  INNER JOIN dbo.Reservations R (NOLOCK) ON R.ReferenceNumber = RAX.ReferenceNumber AND R.GuestNumber = RAX.GuestNumber AND R.DiscountCustomerType = RA.ADDRESSTYPE
    WHERE R.ReferenceNumber = @ReferenceNumber AND R.GuestNumber = @GuestNumber
  END
  IF @DiscountAsNettoPrice = 1
    SET @DiscountAsNettoPrice = @DiscountRate

  IF (ISNULL(@Day2, 0) = 0)
  BEGIN
    IF (@Price <> 0) or ISNULL(@PriceOverwritten, 0) = 0
	BEGIN
      IF @OldProductNumber <> @ProductNumber OR @OldBookedTypeNumber <> @BookedTypeNumber --Call only if needed
      BEGIN
        SET @OldProductNumber = @ProductNumber
        SET @OldBookedTypeNumber = @BookedTypeNumber
        EXEC dbo.GetProductInfo @FkAccountTaxGroup OUTPUT, @VatPercent OUTPUT, @TaxFrom, @DayProduct, @ProductNumber, @SegmentNumber, @PointofSaleNumber, @BookedTypeNumber
      END
      EXEC GetContentProduct @PriceProduct, @PriceProductArrival, @DayProduct, @ProductNumber, @ArrivalDate, @DepartureDate, @BookedTypeNumber, @NumberOfRooms
      , @AdultPax, @Child1Pax, @Child2Pax, @VatPercent, @FkAccountTaxGroup, @DiscountAsNettoPrice
      IF @DebugLevel > 1 
      BEGIN
        PRINT '@VatProductArrival=' +CONVERT(VARCHAR, @VatProductArrival)
        PRINT '@VatProduct=' +CONVERT(VARCHAR, @VatProduct)
	  END
    END
  END
  ELSE
  BEGIN
    DECLARE @MyArrivalDate DATETIME, @MyDepartureDate DATETIME, @MyDay INT, @MyProductNumber INT 
    , @MyPriceProduct MONEY, @MyPriceProductArrival MONEY, @MyVatProduct MONEY, @MyVatProductArrival MONEY
	, @MyPriceOverwritten BIT
    SET @MyDay= 1
	  SET @MyPriceOverwritten = 0
    SET @MyArrivalDate = @ArrivalDate
    SET @PriceProductArrival = 0
    SET @PriceProduct = 0
    SET @VatProduct = 0
    SET @VatProductArrival = 0
    WHILE ISNULL(@MyDay, 0) <> 0 
    BEGIN
      SET @MyPriceProductArrival = NULL;
      IF @MyDay = 1
      BEGIN
        SET @MyDay= @Day2 
        SET @MyProductNumber= @ProductNumber
		    SET @MyPriceOverwritten = @PriceOverwritten
        SET @MyPriceProduct = @TotalPrice1
        SET @DayProduct= 3
      END
	  ELSE IF @MyDay = @Day2
      BEGIN
        SET @DayProduct= 2
        SET @MyDay=@Day3
        SET @MyProductNumber= @ProductNumber2
        SET @MyPriceOverwritten = @PriceOverwritten2
        SET @MyPriceProduct = @TotalPrice2
      END
      ELSE IF @MyDay = @Day3
      BEGIN
        SET @MyDay=@Day4
        SET @MyProductNumber= @ProductNumber3
		    SET @MyPriceOverwritten = @PriceOverwritten3
        SET @MyPriceProduct = @TotalPrice3
      END
      ELSE IF @MyDay = @Day4
      BEGIN
        SET @MyDay= NULL
        SET @MyProductNumber= @ProductNumber4
  		  SET @MyPriceOverwritten = @PriceOverwritten4
        SET @MyPriceProduct = @TotalPrice4
      END
      IF ISNULL(@MyPriceOverwritten,0) = 1
	      SET @MyPriceProductArrival = ISNULL(@MyPriceProduct, 0)
	    ELSE
        SET @MyPriceProduct = 0
      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 @OldProductNumber <> @MyProductNumber OR @OldBookedTypeNumber <> @BookedTypeNumber --Call only if needed
      BEGIN
        SET @OldProductNumber = @MyProductNumber
        SET @OldBookedTypeNumber = @BookedTypeNumber
        EXEC dbo.GetProductInfo @FkAccountTaxGroup OUTPUT, @VatPercent OUTPUT, @TaxFrom, @DayProduct, @MyProductNumber, @SegmentNumber, @PointofSaleNumber, @BookedTypeNumber
      END
    IF @DebugLevel > 1 
    BEGIN
      PRINT '@MyDay=' +CONVERT(VARCHAR, @MyDay)
      PRINT '@MyPriceOverwritten=' +CONVERT(VARCHAR, @MyPriceOverwritten)
      PRINT '@MyProductNumber=' +CONVERT(VARCHAR, @MyProductNumber)
      PRINT '@MyPriceProduct=' +CONVERT(VARCHAR, @MyPriceProduct)
      PRINT '@MyPriceProductArrival=' +CONVERT(VARCHAR, @MyPriceProductArrival)
      PRINT  ' @MyArrivalDate = ' + CONVERT(VARCHAR, @MyArrivalDate)
      PRINT  ' @MyDepartureDate = ' + CONVERT(VARCHAR, @MyDepartureDate)
    END
      EXEC dbo.GetContentProduct @MyPriceProduct, @MyPriceProductArrival, @DayProduct, @MyProductNumber, @MyArrivalDate, @MyDepartureDate, @BookedTypeNumber, @NumberOfRooms
        , @AdultPax, @Child1Pax, @Child2Pax, @VatPercent, @FkAccountTaxGroup, @DiscountAsNettoPrice
--    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
      IF @DebugLevel > 1 
      BEGIN
        PRINT  ' @MyVatProduct = ' + CONVERT(VARCHAR, @MyVatProduct )
        PRINT  ' @MyVatProductArrival = ' + CONVERT(VARCHAR, @MyVatProductArrival)
      END
    END 
    IF @DebugLevel > 1
    BEGIN
      PRINT 'PlusProduct after loop'
      PRINT CONVERT(VARCHAR, @PriceProduct )
      PRINT CONVERT(VARCHAR, @PriceProductArrival)
      PRINT CONVERT(VARCHAR, @VatProduct)
      PRINT CONVERT(VARCHAR, @VatProductArrival)
    END
  END

  IF @ProductContentInt = 0 AND ISNULL(@DiscountRate, 0) > 0
  --calc discount ref 18133
  BEGIN
    DECLARE @Tax MONEY, @FkAccountTaxGroupDiscount INT
	  SELECT Top 1 @FkAccountTaxGroupDiscount = FkAccountTaxGroup FROM #ProductContent WHERE LineNumber IS NULL AND Kind = 3 
    --nb20200505 Not entirely correct because the Discount ware tax ca nbe different than the product tax (when discount posted separate)
  	SELECT @Price = SUM(Price), @Tax = SUM(Tax) FROM #ProductContent WHERE LineNumber IS NULL AND Kind = 3 
    SET @Price = - @Price*@DiscountRate/100
    SET @Tax = - @Tax*@DiscountRate/100
    INSERT INTO #ProductContent (Price, Tax, FkAccountTaxGroup, Kind, LineNumber, ProductNumber) 
      VALUES (@Price, @Tax, @FkAccountTaxGroupDiscount, 3, @LineNumber, @ProductNumber) 

	  SELECT Top 1 @FkAccountTaxGroupDiscount = FkAccountTaxGroup FROM #ProductContent WHERE LineNumber IS NULL AND Kind = 4 
    --nb20200505 Not entirely correct because the Discount ware tax ca nbe different than the product tax (when discount posted separate)
	  SELECT @Price = SUM(Price), @Tax = SUM(Tax) FROM #ProductContent WHERE LineNumber IS NULL AND Kind <> 3 
    SET @Price = - @Price*@DiscountRate/100
    SET @Tax = - @Tax*@DiscountRate/100
    INSERT INTO #ProductContent (Price, Tax, FkAccountTaxGroup, Kind, LineNumber, ProductNumber) 
      VALUES (@Price, @Tax, @FkAccountTaxGroupDiscount, 4, @LineNumber, @ProductNumber) 

  END

  UPDATE #ProductContent SET LineNumber = @LineNumber WHERE LineNumber IS NULL
  IF @@ROWCOUNT = 0
    INSERT INTO #ProductContent (Price, Tax, FkAccountTaxGroup, Kind, ProductNumber, LineNumber) 
    VALUES (0, 0, @FkAccountTaxGroup, 4, @ProductNumber, @LineNumber) 
--END--
  FETCH NEXT FROM LineCur
  INTO @LineNumber, @BookedTypeNumber, @NumberOfRooms, @NameListSetting
  , @ArrivalDate,@DepartureDate,
    @AdultpAX,@Child1Pax,@Child2Pax,@ProductNumber,
    @PriceOverwritten,@Price,@FirstPrice1,@TotalPrice1,
    @PriceOverwritten2,@FirstPrice2,@TotalPrice2,@Day2,@ProductNumber2,
    @PriceOverwritten3,@FirstPrice3,@TotalPrice3,@Day3,@ProductNumber3,
    @PriceOverwritten4,@FirstPrice4,@TotalPrice4,@Day4,@ProductNumber4
END
CLOSE LineCur
DEALLOCATE LineCur

IF @FkAccountTaxGroup IS NULL
BEGIN
  IF OBJECT_ID('tempdb..#PriceRooms') IS NULL
  BEGIN
    CREATE TABLE #PriceRooms(LineNumber INT NOT NULL
    , PriceTotal MONEY NOT NULL
    , TaxTotal MONEY NOT NULL
    , PriceArrival MONEY NOT NULL
    , TaxArrival MONEY NOT NULL
    )
	SET @TblPriceRoomsInt = 1
  END
  ELSE
    SET @TblPriceRoomsInt = 0
  ;WITH LineCte (LineNumber, PriceTotal, TaxTotal, PriceArrival, TaxArrival) AS (
    select LineNumber, SUM(Price), SUM(Tax), 0, 0 from #ProductContent (nolock) where Kind in (1,2,4,5) --Total
    group by LineNumber 
    union
    select LineNumber, 0, 0, SUM(Price), SUM(Tax) from #ProductContent (nolock) where Kind in (3) --Arrival
    --1: package arrival; 2: package stay and departure 3: product arrival; 4: product stay and departure; 5: addon 
    group by LineNumber
  )
  INSERT INTO #PriceRooms
    select LineNumber, SUM(PriceTotal), SUM(TaxTotal), SUM(PriceArrival), SUM(TaxArrival) from LineCte 
    group by LineNumber
  IF @DebugLevel > 1
  BEGIN
    SELECT PR.* FROM dbo.ReservationRoomline RRL WITH (NOLOCK)
      INNER JOIN #PriceRooms PR WITH (NOLOCK) ON PR.LineNumber = RRL.LineNumber
    ORDER BY RRL.ArrivalDate, RRL.DepartureDate, RRL.BookedTypeNumber, RRL.Adults, RRL.Children1, RRL.Children2, RRL.Price, RRL.TotalPrice, RRL.NumberOfRooms
    SELECT * FROM #ProductContent
  END
  IF @TblPriceRoomsInt = 1
    DROP TABLE #PriceRooms
END
IF @ProductContentInt = 1
  DROP TABLE #ProductContent
IF @DebugLevel > 0 
BEGIN
  SET @tPost = GETDATE();
  PRINT 'Time SP [GetContentRoomLine]: ' + Convert(VARCHAR, DATEDIFF(millisecond, @tPre, @tPost))
END
