CREATE PROCEDURE DBO.[GetHalls] @ReferenceNumber INT, @GuestNumber INT, @FixedPriceProduct INT = NULL AS
SET NOCOUNT ON

/*
  vd20120913 CR046629 Involving ReservationConferenceline.Children2
  nb20081023 Same order clause as in Letter components to prevent switching records = switching prices
  nb20080625 0 overwritten price amounts fixed
  nb20070806 Sorting extended with ConfLineNumber after Arrival and Departure date 
  nb20070118 DayProduct variable price products for several days fixed. dependancy in getPriceProducts revision from today
  nb20061008 fix sikret fetch status med en variable
  nb20060815 fix for personprice hall lines when several and diff persons
  NB20060124 secure sorting
  NB20050825 Discount as netto price
  NB20050628 Created
  NOTE- Get the hall info @FixedPriceProduct = NULL --0=No; 1=Yes; and NULL=Ignore/get all
*/

DECLARE 
@LineNumber INT
, @ProductNumber INT
, @ArrivalDate DATETIME
, @DepartureDate DATETIME
, @DayProduct INT 
, @BookedTypeNumber INT
, @NumberOfRooms INT
, @AdultPax INT
, @Child1Pax INT
, @Child2Pax INT
, @VatPercent FLOAT 
, @VatFrom INT
, @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
SET @DayProduct=1
--SET @Child2Pax=0
CREATE TABLE #PriceHalls(
  LineNumber INT NOT NULL
  , PriceTotal MONEY NULL
  , VatTotal MONEY NULL
  , PriceArrival MONEY NULL
  , VatArrival MONEY NULL
)
DECLARE LineCur Cursor Fast_Forward Read_Only For
SELECT ReservationConferenceline.ReservationConfLineNumber
, ReservationConferenceline.BookedConferenceRoomTypeNumber
, Convert(DateTime, ReservationConferenceline.ArrivalDate) AS ArrivalDate
, ReservationConferenceline.ArrivalTime
, CASE WHEN ReservationConferenceLine.DepartureTime > (SELECT TOP 1 KeyValue FROM HotelSetup WITH (NOLOCK) WHERE Searchkey LIKE 'Arr24HoursStarts') 
  THEN Convert(DateTime, ReservationConferenceline.DepartureDate) 
  ELSE DATEADD(DAY, -1, CONVERT(DATETIME, ReservationConferenceline.DepartureDate)) END  AS DepartureDate
, ReservationConferenceline.DepartureTime
, ISNULL(ReservationConferenceline.Adults, 0)
, ISNULL(ReservationConferenceline.Children1, 0)
, ISNULL(ReservationConferenceline.Children2, 0)
, ReservationConferenceline.NumberOfRooms
, ReservationConferenceline.ProductNumber
, ReservationConferenceline.PackageNumber
, ReservationConferenceline.Price
, ReservationConferenceline.TotalPrice
, ReservationConferenceline.PriceOverwritten
FROM RESERVATIONCONFERENCELINE WITH (NOLOCK) LEFT OUTER JOIN
  Products WITH (NOLOCK) ON RESERVATIONCONFERENCELINE.ProductNumber = Products.ProductNumber
WHERE ReservationConferenceLine.referenceNumber = @ReferenceNumber AND (@FixedPriceProduct IS NULL OR FixedPrice=@FixedPriceProduct)
ORDER BY ReservationConferenceline.ArrivalDate, ReservationConferenceline.DepartureDate, ReservationConferenceline.ArrivalTime, ReservationConferenceline.DepartureTime, ReservationConferenceline.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
  SET @OldProductNumber = @ProductNumber
  SET @OldBookedTypeNumber = @BookedTypeNumber
  EXEC GetVatValueProduct @VatPercent OUTPUT, @VatFrom OUTPUT, @DayProduct, @ProductNumber, @SegmentNumber, @PointofSaleNumber, @BookedTypeNumber
END
WHILE (@MyFetchStatus = 0) -- safe
BEGIN
  IF ISNULL(@ProductNumber, 0)>0
  BEGIN
    IF ISNULL(@PriceOverwritten, 0) = 0
      SET @PriceProductArrival=0
    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

    SET @VatProductArrival = 0
  IF @PriceProductArrival <> 0 or ISNULL(@PriceOverwritten, 0) = 0
   EXEC GetPriceProduct @PriceProduct OUTPUT, @PriceProductArrival OUTPUT, @VatProduct OUTPUT, @VatProductArrival OUTPUT
     , @SegmentNumber, @PointofSaleNumber, @DayProduct, @ProductNumber, @ArrivalDate, @DepartureDate, @BookedTypeNumber, @NumberOfRooms
     , @AdultPax, @Child1Pax, @Child2Pax, @VatPercent
  END 
  ELSE
  BEGIN
    SET @CurrentDate = @ArrivalDate
    SET @VatProduct= 0
    WHILE @CurrentDate <= @DepartureDate
    BEGIN
      EXEC GetPricePackage @PackageNumber, @CurrentDate, 0, 
        @BookedTypeNumber, @NumberOfRooms, 
	    @AdultPax, @Child1Pax, @Child2Pax, 0, 
	    @PricePackage OUTPUT, @VatPackage OUTPUT

      IF @CurrentDate = @ArrivalDate
        SET @VatProductArrival=@VatPackage
      SET @VatProduct = @VatProduct + @VatPackage
      SET @CurrentDate = DATEADD(Day, 1, @CurrentDate)
    END
  END
  IF (SELECT TOP 1 KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE 'DiscountAsNettoPrice') = 1
  BEGIN
    DECLARE @DiscountRate FLOAT
    SELECT TOP 1 @DiscountRate = CASE WHEN (ISNULL(DiscountRate, 0) = 0) THEN 
        CASE WHEN ISNULL((SELECT COUNT(*) FROM ReservationRoomlineXtab WITH (NOLOCK)), 0)= 0 THEN MeetingDiscountRate ELSE HallDiscountRate END 
      ELSE DiscountRate END 
    FROM ReservationAddress WITH (NOLOCK) 
      INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.AddressNumber = ReservationAddress.AddressNumber
    WHERE ReservationAddressXTab.ReferenceNumber = @ReferenceNumber AND ReservationAddressXTab.GuestNumber = @GuestNumber
    IF ISNULL(@DiscountRate, 0) > 0
    BEGIN
      SET @PriceProduct = @PriceProduct - @PriceProduct*@DiscountRate/100
      SET @VatProduct = @VatProduct - @VatProduct*@DiscountRate/100
      SET @PriceProductArrival = @PriceProductArrival - @PriceProductArrival*@DiscountRate/100
      SET @VatProductArrival = @VatProductArrival - @VatProductArrival*@DiscountRate/100
    END
  END
  INSERT INTO #PriceHalls (LineNumber, PriceTotal, VatTotal, PriceArrival, VatArrival) VALUES (@LineNumber, @PriceProduct, @VatProduct, @PriceProductArrival, @VatProductArrival)
  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
SELECT PriceTotal, VatTotal, PriceArrival, VatArrival
, RCL.ReservationConfLineNumber AS LineNumber, RCL.BookedConferenceRoomTypeNumber AS BookedTypeNumber
, Convert(DateTime, RCL.ArrivalDate) AS ArrivalDate, Convert(DateTime, RCL.DepartureDate) AS DepartureDate
, RCL.DepartureTime, RCL.ProductNumber, RCL.NumberOfRooms, RCL.PackageNumber, RCL.Price, RCL.TotalPrice, RCL.PriceOverWritten
, RCL.ConferenceRoomSetupNumber, RCL.ConferenceRoomNumber, RCL.Adults, RCL.Children1, RCL.Children2
, RCL.ScreenLine, RCL.ReferenceNumber, RCL.SchemePattern, RCL.SchemeWeekDays
, RCL.LinkLineNumber, RCL.RebookedUnderLineNumber
, Products.FixedPrice, Products.ProductName, Products.Description AS ProductDescription
, ConferenceRooms.Name
, CONFERENCEROOMTYPES_1.Type AS BookedType, CONFERENCEROOMTYPES.Type AS HallType
, CONFERENCEROOMTYPES_1.ConferenceRoomTypeNumber AS BookedTypeNumber, CONFERENCEROOMTYPES.ConferenceRoomTypeNumber AS HallTypeNumber
, CONFERENCEROOMTABLES.Text AS HallTableName, CONFERENCEROOMTYPES.Text AS HallTypeName
FROM #PriceHalls WITH (NOLOCK) INNER JOIN
  RESERVATIONCONFERENCELINE RCL WITH (NOLOCK) ON RCL.ReservationConfLineNumber = #PriceHalls.LineNumber LEFT OUTER JOIN
  CONFERENCEROOMTABLES WITH (NOLOCK) ON RCL.ConferenceRoomSetupNumber = CONFERENCEROOMTABLES.ConferenceRoomTableNumber LEFT OUTER JOIN
  Products WITH (NOLOCK) ON RCL.ProductNumber = Products.ProductNumber LEFT OUTER JOIN
  CONFERENCEROOMTYPES WITH (NOLOCK) INNER JOIN
  CONFERENCEROOMS WITH (NOLOCK) ON CONFERENCEROOMTYPES.ConferenceRoomTypeNumber = CONFERENCEROOMS.ConferenceRoomTypeNumber
    ON RCL.ConferenceRoomNumber = CONFERENCEROOMS.ConferenceRoomNumber LEFT OUTER JOIN
  CONFERENCEROOMTYPES AS CONFERENCEROOMTYPES_1 WITH (NOLOCK) ON RCL.BookedConferenceRoomTypeNumber = CONFERENCEROOMTYPES_1.ConferenceRoomTypeNumber
WHERE RCL.ReferenceNumber=@ReferenceNumber
  ORDER BY RCL.ArrivalDate, RCL.DepartureDate, RCL.ArrivalTime, RCL.DepartureTime, RCL.ReservationConfLineNumber

DROP TABLE #PriceHalls
CLOSE LineCur
DEALLOCATE LineCur
