CREATE PROCEDURE DBO.[GetContentHallLine] @ReferenceNumber INT, @GuestNumber INT, @DiscountInc BIT, @FkAccountTaxGroup INT = NULL, @FixedPriceProduct INT = NULL AS
SET NOCOUNT ON
/*
  NB20190722 Created
  NOTE- Get the hall info @FixedPriceProduct = NULL --0=No; 1=Yes; and NULL=Ignore/get all
*/
DECLARE @DebugLevel TINYINT
SET @DebugLevel = 0
IF @DebugLevel >= 1 
  PRINT '-[GetContentHallLine] begin'
DECLARE 
@LineNumber INT
, @ProductNumber INT
, @ArrivalDate DATETIME
, @DepartureDate DATETIME
, @DayProduct INT 
, @BookedTypeNumber INT
, @NumberOfRooms INT
, @AdultPax INT
, @Child1Pax INT
, @Child2Pax INT
, @VatPercent FLOAT 
, @ArrivalTime CHAR(5)
, @DepartureTime CHAR(5)
, @PriceOverwritten INT
, @Price MONEY
, @FirstPrice MONEY
, @PackageNumber INT
, @PriceProduct REAL
, @PriceProductArrival REAL
, @VatProduct MONEY
, @VatProductArrival MONEY
, @CurrentDate DATETIME
, @PricePackage	MONEY
, @VatPackage MONEY
, @MyFetchStatus INT
, @ProductContentInt BIT
, @TblPriceHallsInt BIT
SET @DayProduct=1
--SET @Child2Pax=0
IF OBJECT_ID('tempdb..#ProductContent') IS NULL
BEGIN
  CREATE TABLE #ProductContent
  (  
    Price MONEY null,
    Tax MONEY 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 LineCur Cursor Fast_Forward Read_Only For
SELECT RCL.ReservationConfLineNumber
, RCL.BookedConferenceRoomTypeNumber
, Convert(DateTime, RCL.ArrivalDate) AS ArrivalDate
, RCL.ArrivalTime
, CASE WHEN RCL.DepartureTime > (SELECT TOP 1 KeyValue FROM HotelSetup WITH (NOLOCK) WHERE Searchkey LIKE 'Arr24HoursStarts') 
  THEN Convert(DateTime, RCL.DepartureDate) 
  ELSE DATEADD(DAY, -1, CONVERT(DATETIME, RCL.DepartureDate)) END  AS DepartureDate
, RCL.DepartureTime
, ISNULL(RCL.Adults, 0)
, ISNULL(RCL.Children1, 0)
, ISNULL(RCL.Children2, 0)
, RCL.NumberOfRooms
, RCL.ProductNumber
, RCL.PackageNumber
, RCL.Price
, RCL.TotalPrice
, RCL.PriceOverwritten
FROM RESERVATIONCONFERENCELINE RCL WITH (NOLOCK) 
  INNER JOIN Reservations R WITH (NOLOCK) ON R.ReferenceNumber=RCL.ReferenceNumber 
    AND R.GuestNumber=ISNULL(RCL.GuestNo,0) 
  LEFT OUTER JOIN Products WITH (NOLOCK) ON RCL.ProductNumber = Products.ProductNumber
WHERE RCL.referenceNumber = @ReferenceNumber AND R.GuestNumber= @GuestNumber and (@FixedPriceProduct IS NULL OR FixedPrice=@FixedPriceProduct)
ORDER BY RCL.ArrivalDate, RCL.DepartureDate, RCL.ArrivalTime, RCL.DepartureTime, RCL.ReservationConfLineNumber

OPEN LineCur
FETCH NEXT FROM LineCur 
  INTO @LineNumber, @BookedTypeNumber,
    @ArrivalDate,@ArrivalTime,@DepartureDate,@DepartureTime,
    @AdultPax,@Child1Pax,@Child2Pax,@NumberOfRooms,
    @ProductNumber,@PackageNumber,@PriceProductArrival, @PriceProduct,@PriceOverwritten
SET @MyFetchStatus = @@FETCH_STATUS
IF ( @MyFetchStatus= 0) -- safe
BEGIN
  DECLARE @SegmentNumber INT, @PointofSaleNumber INT
  SELECT @SegmentNumber= MarketSegment, @PointOfSaleNumber= PointOfSale
  FROM Reservations WITH (NOLOCK)
  WHERE ReferenceNumber=@ReferenceNumber AND Reservations.GuestNumber= @GuestNumber
  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 (@MyFetchStatus = 0) -- safe
BEGIN
  IF @DebugLevel > 0 
    PRINT 'Linenumber: ' + CONVERT(VARCHAR, @LineNumber)
  IF ISNULL(@ProductNumber, 0)>0
  BEGIN
    IF ISNULL(@PriceOverwritten, 0) = 0
      SET @PriceProductArrival=0
  IF @DebugLevel > 1 
    PRINT '@FkAccountTaxGroup: ' + CONVERT(VARCHAR, @FkAccountTaxGroup)
    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
  IF @DebugLevel > 1 
    PRINT '@FkAccountTaxGroup1: ' + CONVERT(VARCHAR, @FkAccountTaxGroup)
    END
    SET @VatProductArrival = 0
  END 
  DECLARE @DiscountRate smallmoney, @DiscountNettoRate smallmoney, @DiscountAsNettoPrice BIT
  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 
        CASE WHEN ISNULL((SELECT COUNT(*) FROM ReservationRoomlineXtab RLX (NOLOCK) WHERE RLX.ReferenceNumber = R.ReferenceNumber AND RLX.GuestNumber = R.GuestNumber), 0)= 0 THEN RA.MeetingDiscountRate ELSE RA.HallDiscountRate END 
      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 @PriceProductArrival <> 0 or ISNULL(@PriceOverwritten, 0) = 0
    EXEC dbo.GetContentProduct @PriceProduct, @PriceProductArrival, @DayProduct, @ProductNumber, @ArrivalDate, @DepartureDate, @BookedTypeNumber, @NumberOfRooms
    , @AdultPax, @Child1Pax, @Child2Pax, @VatPercent, @FkAccountTaxGroup, @DiscountAsNettoPrice
  
  IF @ProductContentInt = 0 AND ISNULL(@DiscountRate, 0) > 0
  --calc discount ref 5430
  BEGIN
    DECLARE @Tax MONEY, @FkAccountTaxGroupDiscount INT
	SELECT Top 1 @FkAccountTaxGroupDiscount = FkAccountTaxGroup FROM #ProductContent WHERE LineNumber IS NULL AND Kind = 3 
	SELECT @Price = SUM(Price), @Tax = SUM(Tax) FROM #ProductContent WHERE LineNumber IS NULL AND Kind = 3
  IF @Price <> 0
    SET @Price = - @Price*@DiscountRate/100
  IF @Tax <> 0
    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
	SELECT @Price = SUM(Price), @Tax = SUM(Tax) FROM #ProductContent WHERE LineNumber IS NULL AND Kind <> 3
  IF @Price <> 0
    SET @Price = - @Price*@DiscountRate/100
  IF @Tax <> 0
    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, LineNumber, ProductNumber)
      VALUES (0, 0, 0, 4, @LineNumber, @ProductNumber)
  FETCH NEXT FROM LineCur
  INTO @LineNumber, @BookedTypeNumber,
    @ArrivalDate,@ArrivalTime,@DepartureDate,@DepartureTime,
    @AdultPax,@Child1Pax,@Child2Pax,@NumberOfRooms,
    @ProductNumber,@PackageNumber,@PriceProductArrival, @PriceProduct,@PriceOverwritten
  SET @MyFetchStatus = @@FETCH_STATUS
END
CLOSE LineCur
DEALLOCATE LineCur
IF @DebugLevel > 0
  SELECT * FROM #ProductContent

IF @FkAccountTaxGroup IS NULL
BEGIN
  IF OBJECT_ID('tempdb..#PriceHalls') IS NULL
  BEGIN
    CREATE TABLE #PriceHalls(LineNumber INT NOT NULL
    , PriceTotal MONEY NULL
    , TaxTotal MONEY NULL
    , PriceArrival MONEY NULL
    , TaxArrival MONEY NULL
    )
  	SET @TblPriceHallsInt = 1
  END
  ELSE
    SET @TblPriceHallsInt = 0
  ;WITH LineCte (LineNumber, PriceTotal, TaxTotal, PriceArrival, TaxArrival) AS (
  --1: package arrival; 2: package stay and departure 3: product arrival; 4: product stay and departure; 5: addon 
    select LineNumber, SUM(Price), SUM(Tax), 0, 0 from #ProductContent (nolock) where Kind in (1,2,4,5)
    group by LineNumber 
    union
    select LineNumber, 0, 0, SUM(Price), SUM(Tax) from #ProductContent (nolock) where Kind in (1, 3) 
    group by LineNumber
  )
  INSERT INTO #PriceHalls
    select LineNumber, SUM(PriceTotal), SUM(TaxTotal), SUM(PriceArrival), SUM(TaxArrival) from LineCte 
    group by LineNumber
  IF @TblPriceHallsInt = 1
    DROP TABLE #PriceHalls
END
IF @ProductContentInt = 1
  DROP TABLE #ProductContent
IF @DebugLevel >= 1 
  PRINT '-[GetContentHallLine] end'
