CREATE PROCEDURE dbo.PostStays @FK_TurnoverDepartment INT, @ChargeReferenceNumber int = -1, @ChargeGuestNumber Smallint = -1, @ChargeHoleStay bit = 0  AS
SET NOCOUNT ON

/*
  Changes:
 KJ 20200713: Bug: Fix for "Package on departure day is posted wrong if tables on res after room departure" Folio: 66664 on Stammershalle.
 KJ 20190910: Ext: Possible to charge without room on reservation. Visit Herning
 KJ 20180618: Opti: Optimized sp
 KJ 20130819: Bug: Fix for "Hall line not sharged". Case #CR063298
 KJ 20100730: Ext: Added ISNULL
 KJ 20081201: Bug: Fix for "Wrong charge depatment - always select the first department"
 KJ 20081102: Also join on GuestNo for ReservationConferenceLine
 KJ 20071023: Changed @ChargeReferenceNumber to -1
 KJ 20071017: Added @ChargeReferenceNumber
 KJ 20070410: Department rewrite
 KJ 20060512: Cleanup and added @ShowPrint
 KJ 20060511: AutoCharge Department changed (DefaultExDepartment)
 Rettet d. 14/04-2004  : Charge of tablebooking
 Rettet d. 05/03-2004 : Added (TableBooking=0 or TableBooking is null) in confline
 Rettet d. 03/02-2004 : Changed @TurnoverDepartmentNumber to @TurnoverDepartment (char(1))
 Rettet d. 05/09-2003 : fixed some stuff on @TurnoverDepartmentNumber
 Rettet d. 09/05-2003 : Fixed bug if arrivaltime<arr24starts (in hotelsetup)
 Rettet d. 05/02-2003 : Cancellationfee also on guestnumber>0
 Rettet d. 16/01-2003 : Added Fast_forward + read_only to Cursor
 Rettet d. 10/12-2002 : Moved PostExFromArr to PostDays
 Rettet d. 5/12-2002  : imp charge for a hole reservation, by day or hole stay
*/

DECLARE @ShowPrint BIT
SET @ShowPrint = 0

Declare @CashierDisabling bit
Select @CashierDisabling = keyvalue From HotelSetup WITH (nolock) where Searchkey = 'CashierDisabling'
if ISNULL(@CashierDisabling,1)= 1 SET @CashierDisabling=1

if @CashierDisabling=0
begin
  Declare @ReferenceNumber Int
  Declare @GuestNumber Int

  Declare @PrevReferenceNumberHalls Int
  Declare @PrevGuestNumberHalls Int

  Declare @PrevReferenceNumberRooms Int
  Declare @PrevGuestNumberRooms Int

  Declare @DepartureDate Char(8)
  Declare @ArrivalDate Char(8)

  Declare @ToDayDate Char(8)

  Declare @ToDay Datetime
  Declare @DepartureDay Datetime
  Declare @ArrivalDay Datetime

  Declare @UserIDNumber Integer
  Declare @PostIndexNumber TinyInt 
  Declare @BookedConferenceRoomTypeNumber int
  Declare @PostStay Bit

  Declare @HotelCountryNumber INT

  Declare @TotalAmount money 
  Declare @CancellationFeeAmount money

  Declare @ArrivalTime char(5)
  Declare @DepartureTime char(5)

  Declare @Arr24HoursStarts char(5)

  Declare @HallDisabling bit
  Declare @RoomDisabling bit

  Declare @HotelCustomerNumber int

  Declare @CentralBookingType smallint
  Select @CentralBookingType = Convert(Integer,isnull(KeyValue,0)) from HotelSetup WITH (Nolock) where searchkey like '%CentralBookingType%'

  if @FK_TurnoverDepartment > 0
  begin
    SELECT @FK_TurnoverDepartment = PK_Department FROM Departments WITH (NOLOCK) WHERE ISNULL(MarkAsDeleted, 0) = 0 AND PK_Department = @FK_TurnoverDepartment
    if ISNULL(@FK_TurnoverDepartment, 0) = 0 SET @FK_TurnoverDepartment = 0
  end 

  if @FK_TurnoverDepartment = 0
  begin
    SELECT TOP 1 @FK_TurnoverDepartment = PK_Department FROM Departments WITH (NOLOCK) WHERE ISNULL(MarkAsDeleted, 0) = 0 AND AutoCharge = 1
    IF ISNULL(@FK_TurnoverDepartment, 0) = 0 
      Select TOP 1 @FK_TurnoverDepartment = PK_Department FROM Departments WITH (NOLOCK) WHERE ISNULL(MarkAsDeleted, 0) = 0 ORDER BY SortNumber
  end

  Select @HotelCountryNumber = Convert(INT,KeyValue) from HotelSetUp WITH (NOLOCK)  Where  SearchKey='CountryCode'
  Select @Arr24HoursStarts = KeyValue from HotelSetUp WITH (NOLOCK) Where  SearchKey='Arr24HoursStarts'

  Select @UserIDNumber = UserIDNumber from Personel WITH (NOLOCK) WHERE UserTypeNumber = 4

  select @HallDisabling = Convert(Bit,KeyValue) from Hotelsetup WITH (nolock) where searchkey ='HallDisabling'
  select @RoomDisabling = Convert(Bit,KeyValue) from Hotelsetup WITH (nolock) where searchkey ='RoomDisabling'

  if isnull(@HallDisabling,0) = 0 SET @HallDisabling = 0
  if isnull(@RoomDisabling,0) = 0 SET @RoomDisabling = 0

  SELECT @ToDayDate = SYSTEMDATE FROM System WITH (NOLOCK) WHERE NUMBER = 1

  SET @ToDay = Convert(DATETIME, @ToDayDate)
  SET @PostIndexNumber = 0
  SET @PostStay = 1

  CREATE TABLE #PacketInfo
  (
    ProductNumber INT,
    DateStamp DATE,
    PacketType INT,
    PacketNumber INT
  )

  While (@PostStay=1)
  begin
    IF @ShowPrint = 1
      print @PostIndexNumber
  
  if (@PostIndexNumber=0)
    begin
      -- HER ER DET DAYPRODUCTS DER BLIVER UDKONTERET!!!!!!!!
      Declare ReservationCur Cursor Fast_Forward Read_Only For
      SELECT
         RESERVATIONConferenceLINE.ArrivalTime,
         RESERVATIONConferenceLINE.ArrivalDate,
         RESERVATIONConferenceLINE.DepartureTime,
         RESERVATIONConferenceLINE.DepartureDate,
         RESERVATIONConferenceLINE.ReferenceNumber,
         RESERVATIONS.GuestNumber, 
         RESERVATIONConferenceLINE.BookedConferenceRoomTypeNumber
      from RESERVATIONConferenceLINE WITH (NOLOCK) 
      INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber=RESERVATIONConferenceLINE.ReferenceNumber 
        AND Reservations.GuestNumber=ISNULL(RESERVATIONConferenceLINE.GuestNo,0)
      where ((RESERVATIONConferenceLINE.DepartureDate >= @ToDayDate) AND
      ((@Arr24HoursStarts<= RESERVATIONConferenceLINE.ArrivalTime AND RESERVATIONConferenceLINE.ArrivalDate <= @ToDayDate) OR
      (@Arr24HoursStarts> RESERVATIONConferenceLINE.ArrivalTime AND Convert(Char(8),Convert(Integer,RESERVATIONConferenceLINE.ArrivalDate)-1,112) <= @ToDayDate))
       AND (RESERVATIONS.Status=5) AND (ISNULL(RESERVATIONConferenceLINE.SchemePattern, 0) = 0))   --5 = rsInhouse
       AND ((TableBooking = 1 AND TableStatus = 5) OR (TableBooking = 0 or TableBooking is null))
      AND (@HallDisabling=0)
      AND (ISNULL(@ChargeReferenceNumber, -1) = -1 OR Reservations.ReferenceNumber = ISNULL(@ChargeReferenceNumber, -1))
      order by RESERVATIONConferenceLINE.ReferenceNumber, RESERVATIONS.GuestNumber -- Vigtigt så der kun kommer en reservation ud indtil videre

      Open ReservationCur

      Fetch Next From ReservationCur Into @ArrivalTime,@ArrivalDate, @DepartureTime,@DepartureDate, @ReferenceNumber, @GuestNumber, @BookedConferenceRoomTypeNumber
    end
    else if @PostIndexNumber = 1 
    begin
      Declare ReservationCur Cursor Fast_Forward Read_Only For
      SELECT 
         CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.ArrivalDate ELSE Reservations.ArrivalDate END,
         CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.DepartureDate ELSE Reservations.DepartureDate END,
         RESERVATIONROOMLINEXTAB.ReferenceNumber,
         RESERVATIONROOMLINEXTAB.GuestNumber
      FROM RESERVATIONROOMLINE WITH (NOLOCK) 
      INNER JOIN RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON RESERVATIONROOMLINE.LINENUMBER = RESERVATIONROOMLINEXTAB.LINENUMBER
      INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONROOMLINEXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber AND RESERVATIONROOMLINEXTAB.GuestNumber = RESERVATIONS.GuestNumber
      INNER JOIN RESERVATIONMAIN WITH (NOLOCK) ON RESERVATIONMAIN.Referencenumber = RESERVATIONS.ReferenceNumber 
      WHERE CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.DepartureDate ELSE Reservations.DepartureDate END >= @ToDayDate
      AND CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.ArrivalDate ELSE Reservations.ArrivalDate END <= @ToDayDate 
      AND RESERVATIONS.Status = 5 
      AND ((dbo.GetHotelSetupKeyValue('AllowCheckinWithoutRoom', -1) = 1) OR (RESERVATIONROOMLINE.RoomNumber > 0))
      AND @RoomDisabling = 0
      AND (ISNULL(@ChargeReferenceNumber, -1) = -1 OR Reservations.ReferenceNumber = ISNULL(@ChargeReferenceNumber, -1))
      ORDER BY RESERVATIONS.ReferenceNumber, RESERVATIONS.GuestNumber -- Vigtigt så der kun kommer en reservation ud indtil videre

      Open ReservationCur 

      Fetch Next From ReservationCur Into @ArrivalDate, @DepartureDate, @ReferenceNumber, @GuestNumber
    end
    else if @PostIndexNumber = 2
    begin
      Declare ReservationCur Cursor Fast_Forward Read_Only For
      SELECT 
         RESERVATIONS.ReferenceNumber,
         RESERVATIONS.GuestNumber
      FROM RESERVATIONS WITH (NOLOCK) 
      INNER JOIN RESERVATIONMAIN WITH (NOLOCK) ON  RESERVATIONMAIN.Referencenumber =  RESERVATIONS.ReferenceNumber 
      WHERE ArrivalDate = @ToDayDate
      AND Status = 5
      AND CancellationPercent > 0
      AND (@HallDisabling = 0 OR @RoomDisabling = 0)
      AND (ISNULL(@ChargeReferenceNumber, -1) = -1 OR Reservations.ReferenceNumber = @ChargeReferenceNumber)
      
      Open ReservationCur

      Fetch Next From ReservationCur Into @ReferenceNumber, @GuestNumber
    end 

    SET @PrevReferenceNumberHalls = 0
    SET @PrevGuestNumberHalls = 0

    SET @PrevReferenceNumberRooms = -1
    SET @PrevGuestNumberRooms = -1

    IF @ShowPrint = 1
    BEGIN 
      if @PostIndexNumber=0
        print 'Halls res Start'
      else if @PostIndexNumber=1
        print 'Room res Start'
      else if @PostIndexNumber=2
        print 'Cancellationfee Start'
    END

    While (@@FETCH_STATUS = 0)
    Begin
      if ((@PostIndexNumber=1) OR (@PostIndexNumber=0)) 
      begin
        IF @ShowPrint = 1
        BEGIN 
          PRINT 'ReferenceNumber og guestnumber (PostStay)'
          PRINT @ReferenceNumber
          PRINT @GuestNumber
        END

        SET @DepartureDay = Convert(DATETIME, @DepartureDate)
        SET @ArrivalDay = Convert(DATETIME,@ArrivalDate)

        if (@PostIndexNumber=0)
        begin    
          IF @ShowPrint = 1
          BEGIN 
            print '@PrevReferenceNumberHalls'
            print '@PrevGuestNumberHalls'
            print @PrevReferenceNumberHalls
            print @PrevGuestNumberHalls
          END

          if (@PrevReferenceNumberHalls <> @ReferenceNumber OR @PrevGuestNumberHalls <> @GuestNumber) 
          begin
            if (isNull(@BookedConferenceRoomTypeNumber,0)=0 ) OR (@Arr24HoursStarts>@DepartureTime)
            begin
              SET @DepartureDay = @DepartureDay - 1
              SET @DepartureDate = Convert(Char,@DepartureDay,112)
            end
            else if (@Arr24HoursStarts> @ArrivalTime) 
            begin
              SET @ArrivalDay = @ArrivalDay - 1                
              SET @ArrivalDate = Convert(Char(8),@ArrivalDay,112)
            end

            if (@ToDay <= @DepartureDay AND @ToDay >= @ArrivalDay)
            begin
              IF @ShowPrint = 1
                print 'PostDays from poststays DayProduct'
       
              Exec PostDays @ReferenceNumber, @GuestNumber, 1, @ToDayDate, @ToDayDate, @UserIDNumber, @FK_TurnoverDepartment
              SET @PrevReferenceNumberHalls = @ReferenceNumber
              SET @PrevGuestNumberHalls = @GuestNumber
            end
          end
        end
        else if @PostIndexNumber = 1
        begin
          if (@PrevReferenceNumberRooms <> @ReferenceNumber OR @PrevGuestNumberRooms <> @GuestNumber)
          Begin
            if (@ToDay=(@DepartureDay-1))
              Exec PostDays @ReferenceNumber, @GuestNumber, 0, @ToDayDate, @DepartureDate, @UserIDNumber, @FK_TurnoverDepartment
            else
              Exec PostDays @ReferenceNumber, @GuestNumber, 0, @ToDayDate , @ToDayDate, @UserIDNumber, @FK_TurnoverDepartment

            SET @PrevReferenceNumberRooms = @ReferenceNumber
            SET @PrevGuestNumberRooms = @GuestNumber
          end
        end
      end
      else if @PostIndexNumber = 2
        exec ChargeAndCalcCancellationFee @ReferenceNumber, @GuestNumber, @TotalAmount output, @CancellationFeeAmount output, @FK_TurnoverDepartment, @UserIDNumber, @HotelCountryNumber
 
      if @PostIndexNumber = 0
        Fetch Next From ReservationCur Into @ArrivalTime, @ArrivalDate, @DepartureTime,@DepartureDate, @ReferenceNumber, @GuestNumber, @BookedConferenceRoomTypeNumber
      else if @PostIndexNumber = 1
        Fetch Next From ReservationCur Into @ArrivalDate, @DepartureDate, @ReferenceNumber, @GuestNumber
      else if @PostIndexNumber = 2
        Fetch Next From ReservationCur Into @ReferenceNumber, @GuestNumber
    end

    IF @ShowPrint = 1
    BEGIN 
      if @PostIndexNumber=0
        print 'Halls res  End'
      else if @PostIndexNumber=1
        print 'Room res  End'
      else if @PostIndexNumber=2
        print 'Cancellationfee End'
    END

    if (@PostIndexNumber=2)
      SET @PostStay = 0
    else
      SET @PostIndexNumber = @PostIndexNumber + 1

    Close ReservationCur
    Deallocate ReservationCur
  end

  DROP TABLE #PacketInfo
end