CREATE PROCEDURE DBO.UpdateNoShows @EndDate CHAR (8) AS
SET NOCOUNT ON
/*
    Created by Jacob Pedersen 2002

    20240801: CR246994, Guest was set Noshow despite arrival date was several days after system date
    20240731: AMH: Simplified several parts consisting of repetitive code
    20170531: Tables arriving before rooms or halls makes whole reservation noshow
    20160607: Hall reservations where hall types isn't related to a hotel, isn't handled.
    20151104: CR094473, Fixed bowling reservations sometimes changed to noshow.
    20150526: Ext: Added some fields to result. Used for Roger Key
    20110609: BA/EXT: CR024455/Implemented extra logging when NightAudit changes status. In some cases inhouse reservations becomes
                      NoShow for no obvious reason. In this case it happend at step (33).
    20110210: Ext: Deposit and followup handling is done in the UpdateNoShow plugin CR018919 
    20110209: BA/EXT: CR018919/Return processed reservations from the Stored Procedure for further external processing 
                      outside of this Stored Procedure (currently the UpdateNoShows plugin and handling of Deposit).
    20080213: BA/EXT: The procedure uses - if exists - dbo.GetReservationStatus to write Reservation Status to the Log/Contact sheet on the reservation.
    20061110 JP: BUG: possible fix for setting Inhouse res Noshow
    20060929 JP:BUG: fix for not setting reservations noshow
    20060116 JP: BUG: fix for missing check for status 7 or 8 many places    
    2005-11-24 AV : BUG: The table section : if GuestNumber is null then return 0
    2005-10-27 JP: BUG: Do not clear prices when setting res noshow if not linked to other guests and do not do it if last person in room
    2004-10-25 AV: Tables must also be set Noshow
    2004-09-14 JP: fix for pays cols with NULL
    2004-09-01 JP: Update pays cols correct on namelist, plus recalc prices
    2004-08-31 JP: Set Prices and Pays cols to 0 when noshow/cancel status is set
    2004-01-08 JP: Don't set outoforder noshow!

    Last modified by: KS+JP  06/09-2002
    Last modified by: KS 09/09-2002    
    Last modified by: JP 13/09-2002    
    Last modified by: JP 17/09-2002    
    Last modified by Spider 10/12-2002
    Last modified by KS 23/01-2003 --> Changed some stuff when canceldepositonreservation
    Last modified by KS 30/01-2003 --> now the status wiil be taken from hotelsetup if roomnumber=0 and not allways cancel
    Spider 01/03-2003
    Spider 29/04-2003
    Jacob Pedersen 22-09-2003 added call to SetOutOfOrderRoomAllFutureAllRooms for Skallerup
    KS 24/09-2003 --> added IF @GuestNumber = 0 when canceldeposit

    NOTE: This routine has become a big mess and could be done in a much smaller and nicer way if it was re-coded from scratch! / JP

*/

/*
    This routine only set subres and mainres noshow. It doesn't create namelist!

  SELECT Reservations.ReferenceNumber, Reservations.GuestNumber, Reservations.Status, ReservationRoomLine.LineNumber, ReservationRoomLine.RoomNumber
  FROM Reservations (NOLOCK) INNER JOIN ReservationRoomLineXTab (NOLOCK) ON Reservations.ReferenceNumber = ReservationRoomLineXTab.ReferenceNumber AND 
  Reservations.GuestNumber = ReservationRoomLineXTab.GuestNumber INNER JOIN ReservationRoomLine (NOLOCK) ON ReservationRoomLineXTab.LineNumber = ReservationRoomLine.LineNumber
  WHERE Reservations.Status<5 and Reservations.ArrivalDate<='19980315' and Reservations.GuestNumber > 0 AND Reservations.ReferenceNumber>0
union
  SELECT DISTINCT Reservations.ReferenceNumber, Reservations.GuestNumber, Reservations.Status, -ReservationMeetingXTab.ReservationConfLineNumber, RESERVATIONCONFERENCELINE.ConferenceRoomNumber
  FROM Reservations (NOLOCK) INNER JOIN ReservationMeetingXTab (NOLOCK) ON ReservationMeetingXTab.ReferenceNumber=Reservations.ReferenceNumber INNER JOIN RESERVATIONCONFERENCELINE (nolock) ON
RESERVATIONCONFERENCELINE.ReservationConfLineNumber=ReservationMeetingXTab.ReservationConfLineNumber
  WHERE Reservations.Status<5 and Reservations.ArrivalDate<='19980315' and Reservations.GuestNumber > 0 AND Reservations.ReferenceNumber>0
union
  SELECT DISTINCT Reservations.ReferenceNumber, Reservations.GuestNumber, Reservations.Status, -RESERVATIONCONFERENCELINE.ReservationConfLineNumber, RESERVATIONCONFERENCELINE.ConferenceRoomNumber
  FROM Reservations (NOLOCK) INNER JOIN RESERVATIONCONFERENCELINE (nolock) ON RESERVATIONCONFERENCELINE.ReferenceNumber=Reservations.ReferenceNumber
  WHERE Reservations.Status<5 and Reservations.ArrivalDate<='19980315' and Reservations.GuestNumber = 0 AND Reservations.ReferenceNumber>0
*/

DECLARE @NoshowStatus INT,
@DepartingReservations INT,
@IsRoomAvailable INT, -- 0 means available. a higher number indicates the number of reservations besides one self occupying the room

@FirstDate CHAR (8),
@LastDate CHAR (8),
@FirstDateConf CHAR (8),
@LastDateConf CHAR (8),

@RoomNumber INT,
@SubResCount INT,
@SubResCountNX INT,
@rowcount INT,

@ReferenceNumber INT,
@GuestNumber INT,
@BGuestNumber INT
DECLARE @LineNumber      INT
DECLARE @Status          INT
DECLARE @NumberOfRooms   SMALLINT
DECLARE @ArrivalDate     CHAR (8)
DECLARE @ArrivalTime     CHAR (5)
DECLARE @DepartureDate   CHAR (8)
DECLARE @DepartureTime   CHAR (5)
DECLARE @CustomerNumber  INT
DECLARE @HotelNumber     INT

DECLARE @NumberOfLines     INT
DECLARE @NumberOfLinesOK   INT
DECLARE @OnePersonPays     SmallInt
DECLARE @Adults            SmallInt
DECLARE @NumberOfAdults    INT
DECLARE @NumberOfAdultsOK  INT
DECLARE @Children1         SmallInt
DECLARE @NumberOfChild1    INT
DECLARE @NumberOfChild1OK  INT
DECLARE @Children2         SmallInt

DECLARE @StatusNow   INT
DECLARE @MainStatus  SMALLINT

DECLARE @NumberOfChild2       INT
DECLARE @NumberOfChild2OK     INT
DECLARE @SpecBookedTypeNumber INT

DECLARE @Day2               INT
DECLARE @Day3               INT
DECLARE @Day4               INT
DECLARE @PriceOverwritten   INT
DECLARE @PriceOverwritten2  INT
DECLARE @PriceOverwritten3  INT
DECLARE @PriceOverwritten4  INT
DECLARE @FirstPrice1        INT
DECLARE @FirstPrice2        INT
DECLARE @FirstPrice3        INT
DECLARE @FirstPrice4        INT
DECLARE @TotalPrice1        INT
DECLARE @TotalPrice2        INT
DECLARE @TotalPrice3        INT
DECLARE @TotalPrice4        INT,
        @PaysAdults         TINYINT,
        @PaysChildren1      TINYINT,
        @PaysChildren2      TINYINT

DECLARE @RoomTypeNumber     INT
DECLARE @ConvStartDate      DATETIME
DECLARE @ConvEndDate        DATETIME
DECLARE @CalcTotalPrice     REAL
DECLARE @CalcArrivalPrice   REAL

Declare @UserIDNumber int
Declare @FolioNumber int
Declare @AddressType tinyint
Declare @CreateGuestFolio Bit

DECLARE @WasUpdated SMALLINT

--DECLARE @SystemDate CHAR (8)
--SELECT @SystemDate = SystemDate FROM System WITH (NOLOCK) WHERE Number=1
DECLARE @LogText VarChar(8000)
DECLARE @OutputText VARCHAR(800) -- BA/080213
DECLARE @AllowCheckinWithoutRoom INT

SELECT @DepartingReservations = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'DepartingReservations' -- 0 = checkout, 1 = extend
SELECT @AllowCheckinWithoutRoom = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AllowCheckinWithoutRoom'

SET  @AllowCheckinWithoutRoom = ISNULL(@AllowCheckinWithoutRoom, 0) 

DECLARE @GetReservationStatusExists BIT

-- BA/20110609: CR024455/Avoid NULL error when converting to string
SET @SubResCount = -1
SET @SubResCountNX = -1

-- BA/20110209: CR018919
CREATE TABLE #ProcessedReservations
(
  ReferenceNumber INT,
  GuestNumber     INT,
  RoomNumber      INT,
  OldStatus       INT,
  NewStatus       INT,
  ArrivalDate     CHAR(8),
  ArrivalTime     CHAR(5),
  DepartureDate   CHAR(8),
  DepartureTime   CHAR(5),
  HotelNumber     INT,
  Adults          INT,
  Children1       INT,
  Children2       INT
) 

  IF EXISTS (SELECT * from sysobjects (NOLOCK) WHERE Name = 'GetReservationStatus' AND XTYPE = 'FN')
    SET @GetReservationStatusExists = 1
  ELSE 
    SET @GetReservationStatusExists = 0

  DECLARE #ReservationCur CURSOR FORWARD_ONLY FOR  -- /*FORWARD_ONLY  <- makes it start over when recalcing price for other persons which we want for correct read of pays cols */ 

  SELECT R.ReferenceNumber,R.GuestNumber,R.Status,RL.LineNumber,RL.RoomNumber,
    R.Adults, R.Children1, R.Children2, R.PaysAdults, R.PaysChildren1, R.PaysChildren2,
    R.ArrivalDate, R.ArrivalTime,  R.DepartureDate, R.DepartureTime, 
    CASE WHEN ISNULL(RL.RoomNumber, 0) > 0
      THEN
        CRO.CustomerNumber
      ELSE
        CRT.CustomerNumber
    END AS CustomerNumber,
    CASE WHEN ISNULL(RL.RoomNumber, 0) > 0
      THEN
        RO.HotelNumber
      ELSE
        RT.HotelNumber
    END AS HotelNumber
  FROM ReservationRoomLine RL WITH (NOLOCK)
  INNER JOIN ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber = RLX.LineNumber 
  RIGHT OUTER JOIN Reservations R WITH (NOLOCK)ON RLX.ReferenceNumber = R.ReferenceNumber AND RLX.GuestNumber = R.GuestNumber
  INNER JOIN ROOMTYPES RT WITH (NOLOCK) ON RL.BookedTypeNumber = RT.RoomTypeNumber
  INNER JOIN Customer CRT WITH (NOLOCK) ON RT.HotelNumber = CRT.HotelNumber
  LEFT OUTER JOIN ROOMS RO WITH (NOLOCK) On RL.RoomNumber = RO.RoomNumber
  LEFT OUTER JOIN Customer CRO WITH (NOLOCK) ON RO.HotelNumber = CRO.HotelNumber
  WHERE R.ReferenceNumber > 0 AND R.Status < 5 and R.ArrivalDate <= @EndDate and R.GuestNumber > 0  -- JP 15042002 also cancel those with a roomnumber!

  UNION

  SELECT R.ReferenceNumber,R.GuestNumber,R.Status, 0 AS LineNumber,0 AS RoomNumber,
    R.Adults, R.Children1, R.Children2, R.PaysAdults, R.PaysChildren1, R.PaysChildren2,
    R.ArrivalDate, R.ArrivalTime,  R.DepartureDate, R.DepartureTime, RM.FkCustomerHotel, RM.HotelNumber
  FROM ReservationMeetingXTab RMX WITH (NOLOCK)  
  INNER JOIN Reservations R WITH (NOLOCK) ON RMX.ReferenceNumber = R.ReferenceNumber AND RMX.GuestNumber = R.GuestNumber
  INNER JOIN ReservationMain RM WITH (NOLOCK) ON R.ReferenceNumber = RM.Referencenumber
  WHERE R.ReferenceNumber > 0 AND R.Status < 5 and R.ArrivalDate <= @EndDate and R.GuestNumber > 0

  Open #ReservationCur

  Fetch Next From #ReservationCur
  Into @ReferenceNumber, @GuestNumber, @Status, @LineNumber, @RoomNumber, @Adults, @Children1, @Children2, @PaysAdults, @PaysChildren1, @PaysChildren2, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @CustomerNumber, @HotelNumber

  WHILE  (@@FETCH_STATUS <> -1)
  BEGIN
    IF IsNull (@Status, 0) = 0  -- Replaces a NULL with a 0
      SET @Status = 0

    IF @Status = 1
    BEGIN
      SELECT @NoshowStatus = HSV.KeyValue FROM HotelSetup HS WITH (NOLOCK) 
      INNER JOIN HotelSetupValueXTab HSVX WITH (NOLOCK) ON HS.HotelSetUpNumber = HSVX.HotelSetupNumber
      INNER JOIN HotelSetupValue HSV WITH (NOLOCK) ON HSVX.FK_ValueNumber = HSV.PK_ValueNumber
      WHERE SearchKey = 'NoshowStatusProvisional' and HSVX.CustomerNumber = @CustomerNumber

      IF ISNULL(@NoshowStatus, -1) = -1
        SELECT @NoshowStatus = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'NoshowStatusProvisional'
    END
    ELSE IF @Status = 2
    BEGIN
      SELECT @NoshowStatus = HSV.KeyValue FROM HotelSetup HS WITH (NOLOCK) 
      INNER JOIN HotelSetupValueXTab HSVX WITH (NOLOCK) ON HS.HotelSetUpNumber = HSVX.HotelSetupNumber
      INNER JOIN HotelSetupValue HSV WITH (NOLOCK) ON HSVX.FK_ValueNumber = HSV.PK_ValueNumber
      WHERE SearchKey = 'NoshowStatusGuaranteed' and HSVX.CustomerNumber = @CustomerNumber

      IF ISNULL(@NoshowStatus, -1) = -1
        SELECT @NoshowStatus = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'NoshowStatusGuaranteed'
    END
    ELSE IF @Status = 3
    BEGIN
      SELECT @NoshowStatus = HSV.KeyValue FROM HotelSetup HS WITH (NOLOCK) 
      INNER JOIN HotelSetupValueXTab HSVX WITH (NOLOCK) ON HS.HotelSetUpNumber = HSVX.HotelSetupNumber
      INNER JOIN HotelSetupValue HSV WITH (NOLOCK) ON HSVX.FK_ValueNumber = HSV.PK_ValueNumber
      WHERE SearchKey = 'NoshowStatusWaitinglist' and HSVX.CustomerNumber = @CustomerNumber

      IF ISNULL(@NoshowStatus, -1) = -1
        SELECT @NoshowStatus = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'NoshowStatusWaitinglist'
    END
    ELSE IF @Status = 4
    BEGIN
      SELECT @NoshowStatus = HSV.KeyValue FROM HotelSetup HS WITH (NOLOCK) 
      INNER JOIN HotelSetupValueXTab HSVX WITH (NOLOCK) ON HS.HotelSetUpNumber = HSVX.HotelSetupNumber
      INNER JOIN HotelSetupValue HSV WITH (NOLOCK) ON HSVX.FK_ValueNumber = HSV.PK_ValueNumber
      WHERE SearchKey = 'NoshowStatusTentative' and HSVX.CustomerNumber = @CustomerNumber

      IF ISNULL(@NoshowStatus, -1) = -1
        SELECT @NoshowStatus = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'NoshowStatusTentative'
    END
    ELSE
    BEGIN
      SELECT @NoshowStatus = HSV.KeyValue FROM HotelSetup HS WITH (NOLOCK) 
      INNER JOIN HotelSetupValueXTab HSVX WITH (NOLOCK) ON HS.HotelSetUpNumber = HSVX.HotelSetupNumber
      INNER JOIN HotelSetupValue HSV WITH (NOLOCK) ON HSVX.FK_ValueNumber = HSV.PK_ValueNumber
      WHERE SearchKey = 'NoshowStatusConfirmed' and HSVX.CustomerNumber = @CustomerNumber

      IF ISNULL(@NoshowStatus, -1) = -1
        SELECT @NoshowStatus = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'NoshowStatusConfirmed'
    END

    IF IsNull (@PaysAdults, 0) = 0  -- Replaces a NULL with a 0
      SET @PaysAdults = 0
    IF IsNull (@PaysChildren1, 0) = 0  -- Replaces a NULL with a 0
      SET @PaysChildren1 = 0
    IF IsNull (@PaysChildren2, 0) = 0  -- Replaces a NULL with a 0
      SET @PaysChildren2 = 0

    IF IsNull (@RoomNumber, 0) = 0  -- Replaces a NULL with a 0
      SET @RoomNumber = 0
    IF (@NoshowStatus = 5) and (@LineNumber > 0) and (@RoomNumber = 0) AND (@AllowCheckinWithoutRoom = 0)
    BEGIN
      UPDATE Reservations SET Status = 8 WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber AND Status < 5 -- 8 = rsNoShow

      IF (SELECT COUNT(*) FROM ReservationRoomLine rl (NOLOCK)
          INNER JOIN ReservationRoomLineXTab xt (NOLOCK) ON xt.LineNumber = rl.LineNumber
          INNER JOIN Reservations r (NOLOCK) ON r.ReferenceNumber = xt.ReferenceNumber AND r.GuestNumber = xt.GuestNumber AND r.Status <> 8
          WHERE rl.LineNumber = @LineNumber) = 0
        UPDATE ReservationRoomline
        SET StatusRL = 8
        WHERE LineNumber = @LineNumber

      -- BA/20110209: CR018919
      INSERT INTO #ProcessedReservations
           (ReferenceNumber, GuestNumber, RoomNumber, OldStatus, NewStatus, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, HotelNumber, Adults, Children1, Children2)
      VALUES (@ReferenceNumber, @GuestNumber, @RoomNumber, @Status, 8, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @HotelNumber, @Adults, @Children1, @Children2)

      SET @OutputText = 'Status changed by nightaudit (1): '

      IF @GetReservationStatusExists = 1
        SET @OutputText = @OutputText + 'Original state: ' + dbo.GetReservationStatus(@Status) +  '. New state: ' + dbo.GetReservationStatus(8) + '.'

      -- BA/20110609: CR024455
      SET @OutputText = @OutputText + ' ArrivalDate: ' + @ArrivalDate + '. DepartureDate: ' + @DepartureDate + '. EndDate: ' + @EndDate + 
        '. SubResCount: ' + cast(@SubResCount as varchar(3)) + '. SubResCountNX: ' + cast(@SubResCountNX as varchar(3)) + '.'

      EXEC WriteToLog @ReferenceNumber, @GuestNumber, @OutputText, 3 
    END
    ELSE
    BEGIN
      UPDATE Reservations SET Status = @NoshowStatus WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber AND Status < 5

      IF (@LineNumber > 0) AND
         ((SELECT COUNT(*) FROM ReservationRoomLine rl (NOLOCK)
           INNER JOIN ReservationRoomLineXTab xt (NOLOCK) ON xt.LineNumber = rl.LineNumber
           INNER JOIN Reservations r (NOLOCK) ON r.ReferenceNumber = xt.ReferenceNumber AND r.GuestNumber = xt.GuestNumber AND r.Status <> @NoshowStatus
           WHERE rl.LineNumber = @LineNumber) = 0)
        UPDATE ReservationRoomline
        SET StatusRL = @NoshowStatus
        WHERE LineNumber = @LineNumber

      -- BA/20110209: CR018919
      INSERT INTO #ProcessedReservations
             (ReferenceNumber, GuestNumber, RoomNumber, OldStatus, NewStatus, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, HotelNumber, Adults, Children1, Children2)
      VALUES (@ReferenceNumber, @GuestNumber, @RoomNumber, @Status, @NoShowStatus, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @HotelNumber, @Adults, @Children1, @Children2)

      IF @NoshowStatus in (7, 8)
      BEGIN
        SET @OutputText = 'Status changed by nightaudit (2): '

        IF @GetReservationStatusExists = 1
          SET @OutputText = @OutputText + 'Original state: ' + dbo.GetReservationStatus(@Status) +  '. New state: ' + dbo.GetReservationStatus(@NoShowStatus) + '.'

        -- BA/20110609: CR024455
        SET @OutputText = @OutputText + ' ArrivalDate: ' + @ArrivalDate + '. DepartureDate: ' + @DepartureDate + '. EndDate: ' + @EndDate + 
          '. SubResCount: ' + cast(@SubResCount as varchar(3)) + '. SubResCountNX: ' + cast(@SubResCountNX as varchar(3)) + '.'

        EXEC WriteToLog @ReferenceNumber, @GuestNumber, @OutputText, 3 
      END
      ELSE
      BEGIN
        SET @OutputText = 'Status changed by nightaudit (3): '

        IF @GetReservationStatusExists = 1
        SET @OutputText = @OutputText + 'Original state: ' + dbo.GetReservationStatus(@Status) +  '. New state: ' + dbo.GetReservationStatus(@NoShowStatus) + '.'

        -- BA/20110609: CR024455
        SET @OutputText = @OutputText + ' ArrivalDate: ' + @ArrivalDate + '. DepartureDate: ' + @DepartureDate + '. EndDate: ' + @EndDate + 
          '. SubResCount: ' + cast(@SubResCount as varchar(3)) + '. SubResCountNX: ' + cast(@SubResCountNX as varchar(3)) + '.'

        EXEC WriteToLog @ReferenceNumber, @GuestNumber, @OutputText, 2
      END
    END

    SELECT @StatusNow = Status FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber

    IF @StatusNow = 5 AND @GuestNumber > 0 -- We have checked in a subres. We need to checkin mainres also!
    BEGIN
      SELECT @MainStatus = Status FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = 0
      IF @MainStatus < 5
      BEGIN
        UPDATE Reservations SET Status = 5 WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = 0

        -- BA/20110209: CR018919
        INSERT INTO #ProcessedReservations
               (ReferenceNumber, GuestNumber, RoomNumber, OldStatus, NewStatus, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, HotelNumber, Adults, Children1, Children2)
        VALUES (@ReferenceNumber, @GuestNumber, @RoomNumber, @MainStatus, 5, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @HotelNumber, @Adults, @Children1, @Children2)

        SET @OutputText = 'Mainreservation checked-in by nightaudit (16): '

        IF @GetReservationStatusExists = 1
          SET @OutputText = @OutputText  + 'Original state: ' + dbo.GetReservationStatus(@MainStatus) +  '. New state: ' + dbo.GetReservationStatus(5) + '.'

        -- BA/20110609: CR024455
        SET @OutputText = @OutputText + ' ArrivalDate: ' + @ArrivalDate + '. DepartureDate: ' + @DepartureDate + '. EndDate: ' + @EndDate + 
          '. SubResCount: ' + cast(@SubResCount as varchar(3)) + '. SubResCountNX: ' + cast(@SubResCountNX as varchar(3)) + '.'

        EXEC WriteToLog @ReferenceNumber, @GuestNumber, @OutputText, 2  ---Always only changed
      END
    END

    IF (@StatusNow IN (7, 8)) AND (@Status NOT IN (7, 8))
    BEGIN
      -- Clear prices
      DECLARE @LinkedCount INT
      SELECT @LinkedCount = COUNT(*) FROM ReservationRoomLineXTab RLX WITH (NOLOCK)
      INNER JOIN Reservations R WITH (NOLOCK) ON R.ReferenceNumber = RLX.ReferenceNumber AND R.GuestNumber = RLX.GuestNumber AND R.Status <= 6
      WHERE RLX.ReferenceNumber=@ReferenceNumber AND RLX.GuestNumber=@GuestNumber 

      IF @LinkedCount > 1 -- Only clear prices etc when we are linked to other persons. we need to calculate a new price for the room
        UPDATE Reservations SET Price=0, TotalPrice=0, PaysAdults=0, PaysChildren1=0, PaysChildren2=0
        WHERE ReferenceNumber=@ReferenceNumber AND GuestNumber=@GuestNumber 

      IF @PaysAdults+@PaysChildren1+@PaysChildren2 = 0  -- we need to find the other row that pays for this row, subtract the amount of persons he pays for, plus recalc the price
      BEGIN  -- problem if the one that pays is now outofhouse!
        SELECT TOP 1 @BGuestNumber = Reservations.GuestNumber
        FROM Reservations WITH (NOLOCK) INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON
        ReservationRoomLineXTab.ReferenceNumber=Reservations.ReferenceNumber AND ReservationRoomLinextab.GuestNumber=Reservations.GuestNumber
        WHERE Reservations.ReferenceNumber=@ReferenceNumber AND Reservations.Status<=5 AND ReservationRoomLineXTab.LineNumber=@LineNumber AND PaysAdults >= @Adults AND PaysChildren1 >= @Children1 AND PaysChildren2 >= @Children2
        SET @rowcount=@@rowcount
      END
      ELSE  -- we need to update someone else with my pays so it is not lost. dont forget to subtract my adults+c1+c2
      BEGIN
      SELECT TOP 1 @BGuestNumber = Reservations.GuestNumber
        FROM Reservations WITH (NOLOCK) INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON
          ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber AND ReservationRoomLinextab.GuestNumber = Reservations.GuestNumber
        WHERE Reservations.ReferenceNumber = @ReferenceNumber AND Reservations.Status <= 5 AND ReservationRoomLineXTab.LineNumber = @LineNumber  
        SET @rowcount=@@rowcount
      END

      IF @rowcount = 1
      BEGIN
        UPDATE Reservations SET PaysAdults=PaysAdults+@PaysAdults-@Adults, PaysChildren1=PaysChildren1+@PaysChildren1-@Children1, PaysChildren2=PaysChildren2+@PaysChildren2-@Children2
        WHERE ReferenceNumber=@ReferenceNumber AND GuestNumber=@BGuestNumber

        EXEC RecalculatePriceForReservation @ReferenceNumber, @BGuestNumber
      END
    END

    EXEC UpdateArrivalAndDeparture2 @ReferenceNumber, @GuestNumber -- JP 24072002

    Fetch Next From #ReservationCur
    Into @ReferenceNumber, @GuestNumber, @Status, @LineNumber, @RoomNumber, @Adults, @Children1, @Children2, @PaysAdults, @PaysChildren1, @PaysChildren2, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @CustomerNumber, @HotelNumber
  END
  
  CLOSE #ReservationCur
  DEALLOCATE #ReservationCur
 
  -- Set mainres noshow if all subres are N or X
  SELECT @UserIDNumber = UserIDNumber FROM Personel WITH (Nolock)  WHERE UserTypeNumber = 4 -- KS 1/3-2002

  DECLARE #ReservationCur CURSOR FORWARD_ONLY FOR

/*
  SELECT ReferenceNumber, GuestNumber, Status, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime
  FROM Reservations AS A (NOLOCK) WHERE ArrivalDate <= @EndDate AND Status < 5  AND ReferenceNumber > 0 AND ReferenceNumber not in 
  (Select ReferenceNumber From RESERVATIONConferenceLINE (NOLOCK))
*/ -- JP 20060929 BUG: fix for not setting reservations noshow
  SELECT DISTINCT A.ReferenceNumber,A.GuestNumber,A.Status,RL.ArrivalDate,RL.ArrivalTime,RL.DepartureDate,RL.DepartureTime,
  CASE WHEN ISNULL(RL.LINENUMBER, 0) > 0
    THEN
      CASE WHEN ISNULL(RL.RoomNumber, 0) > 0
        THEN
          CRO.CustomerNumber
        ELSE
          CRT.CustomerNumber    
      END
    ELSE
      CRM.CustomerNumber
  END AS HotelCustomerNumber
  FROM Reservations AS A WITH (NOLOCK) 
  INNER JOIN RESERVATIONMAIN RM WITH (NOLOCK) ON A.ReferenceNumber = RM.REFERENCENUMBER
  INNER JOIN Customer CRM WITH (NOLOCK) ON RM.HOTELNUMBER = CRM.HotelNumber
  INNER JOIN RESERVATIONROOMLINEXTAB RLX WITH (NOLOCK) ON A.ReferenceNumber = RLX.ReferenceNumber AND A.GuestNumber = RLX.GuestNumber
  INNER JOIN RESERVATIONROOMLINE RL WITH (NOLOCK) ON RLX.LINENUMBER = RL.LINENUMBER and RL.ArrivalDate <= @EndDate
  LEFT OUTER JOIN ROOMTYPES RT WITH (NOLOCK) ON RL.BookedTypeNumber = RT.RoomTypeNumber
  LEFT OUTER JOIN ROOMS RO WITH (NOLOCK) On RL.RoomNumber = RO.RoomNumber
  LEFT OUTER JOIN CUSTOMER CRT WITH (NOLOCK) ON RT.HotelNumber = CRT.HotelNumber
  LEFT OUTER JOIN CUSTOMER CRO WITH (NOLOCK) ON RO.HotelNumber = CRO.HotelNumber
  WHERE A.Status < 5 AND A.ReferenceNumber > 0 AND A.GuestNumber = 0

  UNION

  SELECT DISTINCT A.ReferenceNumber,A.GuestNumber,A.Status,A.ArrivalDate,A.ArrivalTime,A.DepartureDate,A.DepartureTime, CRM.CustomerNumber AS HOTELCustomerNumber 
  FROM Reservations AS A WITH (NOLOCK) 
  INNER JOIN RESERVATIONMAIN RM WITH (NOLOCK) ON A.ReferenceNumber = RM.REFERENCENUMBER
  INNER JOIN Customer CRM WITH (NOLOCK) ON RM.HOTELNUMBER = CRM.HotelNumber
  WHERE Status < 5 AND A.ReferenceNumber > 0 AND A.GuestNumber = 0 AND a.ArrivalDate <= @EndDate AND
    (SELECT COUNT (*) FROM Reservations R1 WITH (NOLOCK) WHERE R1.ReferenceNumber = A.ReferenceNumber AND GuestNumber > 0) > 0 AND
    (SELECT COUNT (*) FROM Reservations R2 WITH (NOLOCK) WHERE R2.ReferenceNumber = A.ReferenceNumber AND GuestNumber > 0) =
    (SELECT COUNT (*) FROM Reservations R3 WITH (NOLOCK) WHERE R3.ReferenceNumber = A.ReferenceNumber AND GuestNumber > 0 AND Status IN (7,8)) AND
    (SELECT COUNT (*) FROM RESERVATIONCONFERENCELINE RCL WITH (NOLOCK) WHERE RCL.ReferenceNumber = A.ReferenceNumber and tablebooking = 0) = 0

  UNION

  SELECT DISTINCT A.ReferenceNumber,A.GuestNumber,A.Status,B.ArrivalDate,B.ArrivalTime,B.DepartureDate,B.DepartureTime,CRM.CustomerNumber AS HOTELCustomerNumber
  FROM Reservations AS A WITH (NOLOCK) 
  INNER JOIN RESERVATIONMAIN RM WITH (NOLOCK) ON A.ReferenceNumber = RM.REFERENCENUMBER
  INNER JOIN Customer CRM WITH (NOLOCK) ON RM.HOTELNUMBER = CRM.HotelNumber
  INNER JOIN RESERVATIONConferenceLINE B WITH (NOLOCK) ON A.ReferenceNumber = B.ReferenceNumber AND A.GuestNumber = B.GuestNo and B.TableBooking = 0 
  WHERE B.ArrivalDate <= @EndDate AND A.Status < 5 AND A.ReferenceNumber > 0
  --LEFT OUTER JOIN RESERVATIONConferenceLINE B2 WITH (NOLOCK) ON A.ReferenceNumber=B2.ReferenceNumber AND A.GuestNumber = B2.GuestNo AND B2.TableBooking = 0

  UNION

  SELECT A.ReferenceNumber, A.GuestNumber, A.Status, A.ArrivalDate,A.ArrivalTime, A.DepartureDate, A.DepartureTime, CRM.CustomerNumber AS HOTELCustomerNumber
  FROM Reservations A WITH (NOLOCK) 
  INNER JOIN RESERVATIONMAIN RM WITH (NOLOCK) ON A.ReferenceNumber = RM.REFERENCENUMBER
  INNER JOIN Customer CRM WITH (NOLOCK) ON RM.HOTELNUMBER = CRM.HotelNumber
  WHERE A.ReferenceNumber > 0 AND A.ArrivalDate IS NULL AND A.Status < 5

  Open #ReservationCur

  Fetch Next From #ReservationCur
  Into @ReferenceNumber, @GuestNumber, @Status, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @CustomerNumber

  WHILE  (@@FETCH_STATUS <> -1)
  BEGIN
    IF IsNull (@Status, 0) = 0  -- Replaces a NULL with a 0
      SET @Status = 0

    IF @Status = 1
    BEGIN
      SELECT @NoshowStatus = HSV.KeyValue FROM HotelSetup HS WITH (NOLOCK) 
      INNER JOIN HotelSetupValueXTab HSVX WITH (NOLOCK) ON HS.HotelSetUpNumber = HSVX.HotelSetupNumber
      INNER JOIN HotelSetupValue HSV WITH (NOLOCK) ON HSVX.FK_ValueNumber = HSV.PK_ValueNumber
      WHERE SearchKey = 'NoshowStatusProvisional' and HSVX.CustomerNumber = @CustomerNumber

      IF ISNULL(@NoshowStatus, -1) = -1
        SELECT @NoshowStatus = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'NoshowStatusProvisional'
    END
    ELSE IF @Status = 2
    BEGIN
      SELECT @NoshowStatus = HSV.KeyValue FROM HotelSetup HS WITH (NOLOCK) 
      INNER JOIN HotelSetupValueXTab HSVX WITH (NOLOCK) ON HS.HotelSetUpNumber = HSVX.HotelSetupNumber
      INNER JOIN HotelSetupValue HSV WITH (NOLOCK) ON HSVX.FK_ValueNumber = HSV.PK_ValueNumber
      WHERE SearchKey = 'NoshowStatusGuaranteed' and HSVX.CustomerNumber = @CustomerNumber

      IF ISNULL(@NoshowStatus, -1) = -1
        SELECT @NoshowStatus = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'NoshowStatusGuaranteed'
    END
    ELSE IF @Status = 3
    BEGIN
      SELECT @NoshowStatus = HSV.KeyValue FROM HotelSetup HS WITH (NOLOCK) 
      INNER JOIN HotelSetupValueXTab HSVX WITH (NOLOCK) ON HS.HotelSetUpNumber = HSVX.HotelSetupNumber
      INNER JOIN HotelSetupValue HSV WITH (NOLOCK) ON HSVX.FK_ValueNumber = HSV.PK_ValueNumber
      WHERE SearchKey = 'NoshowStatusWaitinglist' and HSVX.CustomerNumber = @CustomerNumber

      IF ISNULL(@NoshowStatus, -1) = -1
        SELECT @NoshowStatus = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'NoshowStatusWaitinglist'
    END
    ELSE IF @Status = 4
    BEGIN
      SELECT @NoshowStatus = HSV.KeyValue FROM HotelSetup HS WITH (NOLOCK) 
      INNER JOIN HotelSetupValueXTab HSVX WITH (NOLOCK) ON HS.HotelSetUpNumber = HSVX.HotelSetupNumber
      INNER JOIN HotelSetupValue HSV WITH (NOLOCK) ON HSVX.FK_ValueNumber = HSV.PK_ValueNumber
      WHERE SearchKey = 'NoshowStatusTentative' and HSVX.CustomerNumber = @CustomerNumber

      IF ISNULL(@NoshowStatus, -1) = -1
        SELECT @NoshowStatus = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'NoshowStatusTentative'
    END
    ELSE
    BEGIN
      SELECT @NoshowStatus = HSV.KeyValue FROM HotelSetup HS WITH (NOLOCK) 
      INNER JOIN HotelSetupValueXTab HSVX WITH (NOLOCK) ON HS.HotelSetUpNumber = HSVX.HotelSetupNumber
      INNER JOIN HotelSetupValue HSV WITH (NOLOCK) ON HSVX.FK_ValueNumber = HSV.PK_ValueNumber
      WHERE SearchKey = 'NoshowStatusConfirmed' and HSVX.CustomerNumber = @CustomerNumber

      IF ISNULL(@NoshowStatus, -1) = -1
        SELECT @NoshowStatus = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'NoshowStatusConfirmed'
    END

    SELECT @SubResCount = COUNT (*) FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber > 0
    SELECT @SubResCountNX = COUNT (*) FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber > 0 AND Status < 7  -- not NX

    SET @LineNumber = 0  -- assume 0
    SET @RoomNumber = 0
    SELECT TOP 1 @LineNumber = LineNumber FROM ReservationRoomLineXTab WITH (NOLOCK) WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber
    SELECT @RoomNumber = RoomNumber FROM ReservationRoomLine WITH (NOLOCK) WHERE LineNumber = @LineNumber
    IF IsNull (@RoomNumber, 0) = 0  -- Replaces a NULL with a 0
      SET @RoomNumber = 0

    SET @IsRoomAvailable = 0

    -- Room Single res. no namelist!!
    IF @ArrivalDate <= @EndDate AND (@SubResCount = 0 OR @SubResCountNX = 0) -- ALL are Noshow or Cancel. We need to set mainres cancelled too!   this is made since the code below checks for linenumber and conf res does not have a roomlinenumber!
    BEGIN
      IF @DepartingReservations = 1 AND @NoshowStatus = 5 AND @RoomNumber > 0
      BEGIN
        SELECT @IsRoomAvailable = COUNT (*) FROM TMultiPurposeView WITH (NOLOCK) WHERE ((Status < 6
          ) AND SpecRoomNumberID = @RoomNumber AND LineNumber <> @LineNumber AND (HotelNumber = 0 OR HotelNumber IS NULL)) AND
          ((GuestNumber > 0 AND ((ArrivalDate < @DepartureDate AND DepartureDate > @ArrivalDate)
          OR ((ArrivalDate = @DepartureDate AND ArrivalTime < @DepartureTime) OR (DepartureDate = @ArrivalDate AND DepartureTime > @ArrivalTime))
          )) OR
          (GuestNumber = 0 AND ((SpecArrivalDate < @DepartureDate AND SpecDepartureDate > @ArrivalDate)
          OR ((SpecArrivalDate = @DepartureDate AND SpecArrivalTime < @DepartureTime) OR (SpecDepartureDate = @ArrivalDate AND SpecDepartureTime > @ArrivalTime))
          )))
      END

      IF (@IsRoomAvailable = 0 AND @RoomNumber > 0) OR (@RoomNumber = 0)
      BEGIN
        UPDATE Reservations SET Status = @NoshowStatus WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber
        UPDATE ReservationRoomline
        SET StatusRL = @NoshowStatus
        FROM ReservationRoomline rl (NOLOCK)
        INNER JOIN ReservationRoomLineXTAB rlx (NOLOCK) ON rl.LineNumber = rlx.LineNumber and ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber AND ArrivalDate = @EndDate

        -- BA/20110209: CR018919
        INSERT INTO #ProcessedReservations
               (ReferenceNumber, GuestNumber, RoomNumber, OldStatus, NewStatus, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, HotelNumber, Adults, Children1, Children2)
        VALUES (@ReferenceNumber, @GuestNumber, @RoomNumber, @Status, @NoshowStatus, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @HotelNumber, @Adults, @Children1, @Children2)

        IF @NoshowStatus in (7, 8)
        BEGIN
          SET @OutputText = 'Status changed by nightaudit (17): '

          IF @GetReservationStatusExists = 1
            SET @OutputText = @OutputText  + 'Original state: ' + dbo.GetReservationStatus(@Status) +  '. New state: ' + dbo.GetReservationStatus(@NoshowStatus) + '.'

          -- BA/20110609: CR024455
          SET @OutputText = @OutputText + ' ArrivalDate: ' + @ArrivalDate + '. DepartureDate: ' + @DepartureDate + '. EndDate: ' + @EndDate + 
            '. SubResCount: ' + cast(@SubResCount as varchar(3)) + '. SubResCountNX: ' + cast(@SubResCountNX as varchar(3)) + '.'

          EXEC WriteToLog @ReferenceNumber, @GuestNumber, @OutputText, 3
        END
        else
        BEGIN
          SET @OutputText = 'Status changed by nightaudit (18): ' 

          IF @GetReservationStatusExists = 1
            SET @OutputText = @OutputText + 'Original state: ' + dbo.GetReservationStatus(@Status) +  '. New state: ' + dbo.GetReservationStatus(@NoshowStatus) + '.'

          -- BA/20110609: CR024455
          SET @OutputText = @OutputText + ' ArrivalDate: ' + @ArrivalDate + '. DepartureDate: ' + @DepartureDate + '. EndDate: ' + @EndDate + 
            '. SubResCount: ' + cast(@SubResCount as varchar(3)) + '. SubResCountNX: ' + cast(@SubResCountNX as varchar(3)) + '.'

          EXEC WriteToLog @ReferenceNumber, @GuestNumber, @OutputText, 2
        END
        SET @Status = @NoshowStatus
      END
      ELSE  -- NOT FREE!
      BEGIN
        UPDATE Reservations SET Status = 7 WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber
        UPDATE ReservationRoomline
        SET StatusRL = 7
        FROM ReservationRoomline rl (NOLOCK)
        INNER JOIN ReservationRoomLineXTAB rlx (NOLOCK) ON rl.LineNumber = rlx.LineNumber and ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber

        -- BA/20110209: CR018919
        INSERT INTO #ProcessedReservations
               (ReferenceNumber, GuestNumber, RoomNumber, OldStatus, NewStatus, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, HotelNumber, Adults, Children1, Children2)
        VALUES (@ReferenceNumber, @GuestNumber, @RoomNumber, @Status, 7, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @HotelNumber, @Adults, @Children1, @Children2)

        SET @OutputText = 'Status changed by nightaudit (19): '

        IF @GetReservationStatusExists = 1
          SET @OutputText = @OutputText  + 'Original state: ' + dbo.GetReservationStatus(@Status) +  '. New state: ' + dbo.GetReservationStatus(7) + '.'

        -- BA/20110609: CR024455
        SET @OutputText = @OutputText + ' ArrivalDate: ' + @ArrivalDate + '. DepartureDate: ' + @DepartureDate + '. EndDate: ' + @EndDate + 
          '. SubResCount: ' + cast(@SubResCount as varchar(3)) + '. SubResCountNX: ' + cast(@SubResCountNX as varchar(3)) + '.'

        EXEC WriteToLog @ReferenceNumber, @GuestNumber, @OutputText, 3
        SET @Status = 7
      END

      IF @Status = 5 AND @GuestNumber > 0 -- We have checked in a subres. We need to checkin mainres also!
      BEGIN
        SELECT @MainStatus = Status FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = 0
        IF @MainStatus < 5
        begin 
          UPDATE Reservations SET Status = 5 WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = 0
          UPDATE ReservationRoomline
          SET StatusRL = 5
          FROM ReservationRoomline rl (NOLOCK)
          INNER JOIN ReservationRoomLineXTAB rlx (NOLOCK) ON rl.LineNumber = rlx.LineNumber and ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber

          -- BA/20110209: CR018919
          INSERT INTO #ProcessedReservations
                 (ReferenceNumber, GuestNumber, RoomNumber, OldStatus, NewStatus, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, HotelNumber, Adults, Children1, Children2)
          VALUES (@ReferenceNumber, @GuestNumber, @RoomNumber, @MainStatus, 5, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @HotelNumber, @Adults, @Children1, @Children2)

          SET @OutputText = 'Mainreservation checked-in by nightaudit (32): '

          IF @GetReservationStatusExists = 1
            SET @OutputText = @OutputText  + 'Original state: ' + dbo.GetReservationStatus(@MainStatus) +  '. New state: ' + dbo.GetReservationStatus(5) + '.'

          -- BA/20110609: CR024455
          SET @OutputText = @OutputText + ' ArrivalDate: ' + @ArrivalDate + '. DepartureDate: ' + @DepartureDate + '. EndDate: ' + @EndDate + 
            '. SubResCount: ' + cast(@SubResCount as varchar(3)) + '. SubResCountNX: ' + cast(@SubResCountNX as varchar(3)) + '.'

          EXEC WriteToLog @ReferenceNumber, @GuestNumber, @OutputText, 2 --Always only changed
        end
      END
    END

    SET @StatusNow = 0

    IF (@NoshowStatus = 5) and (((@LineNumber > 0) and (@RoomNumber = 0) AND (@AllowCheckinWithoutRoom = 0)) or ((@SubResCount > 0) and @SubResCountNX = 0))
    BEGIN
      UPDATE Reservations SET Status = 8 WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber  -- noshow
      UPDATE ReservationRoomline
      SET StatusRL = 8
      FROM ReservationRoomline rl (NOLOCK)
      INNER JOIN ReservationRoomLineXTAB rlx (NOLOCK) ON rl.LineNumber = rlx.LineNumber and ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber

       -- BA/20110209: CR018919
       INSERT INTO #ProcessedReservations
              (ReferenceNumber, GuestNumber, RoomNumber, OldStatus, NewStatus, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, HotelNumber, Adults, Children1, Children2)
       VALUES (@ReferenceNumber, @GuestNumber, @RoomNumber, @Status, 8, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @HotelNumber, @Adults, @Children1, @Children2)

      SET @OutputText = 'Status changed by nightaudit (E): '

      IF @GetReservationStatusExists = 1
        SET @OutputText = @OutputText  + 'Original state: ' + dbo.GetReservationStatus(@Status) +  '. New state: ' + dbo.GetReservationStatus(8) + '.'

      -- BA/20110609: CR024455
      SET @OutputText = @OutputText + ' ArrivalDate: ' + @ArrivalDate + '. DepartureDate: ' + @DepartureDate + '. EndDate: ' + @EndDate + 
        '. SubResCount: ' + cast(@SubResCount as varchar(3)) + '. SubResCountNX: ' + cast(@SubResCountNX as varchar(3)) + '.'

      EXEC WriteToLog @ReferenceNumber, @GuestNumber, @OutputText, 3 
    END 
    ELSE
    BEGIN
      IF @ArrivalDate < @EndDate AND @SubResCount = @SubResCountNX AND @NoshowStatus <> 5  -- JP 11092002
      BEGIN
        UPDATE Reservations SET Status = @NoshowStatus WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber
        UPDATE ReservationRoomline
        SET StatusRL = @NoshowStatus
        FROM ReservationRoomline rl (NOLOCK)
        INNER JOIN ReservationRoomLineXTAB rlx (NOLOCK) ON rl.LineNumber = rlx.LineNumber and ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber

        -- BA/ 2 0110209: CR018919
        INSERT INTO #ProcessedReservations
               (ReferenceNumber, GuestNumber, RoomNumber, OldStatus, NewStatus, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, HotelNumber, Adults, Children1, Children2)
        VALUES (@ReferenceNumber, @GuestNumber, @RoomNumber, @Status, @NoshowStatus, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @HotelNumber, @Adults, @Children1, @Children2)

        IF @NoshowStatus in (7, 8)
        BEGIN
          SET @OutputText = 'Status changed by nightaudit (33): '

          IF @GetReservationStatusExists = 1
            SET @OutputText = @OutputText + 'Original state: ' + dbo.GetReservationStatus(@Status) +  '. New state: ' + dbo.GetReservationStatus(@NoshowStatus) + '.'

          -- BA/20110609: CR024455
          SET @OutputText = @OutputText + ' ArrivalDate: ' + @ArrivalDate + '. DepartureDate: ' + @DepartureDate + '. EndDate: ' + @EndDate + 
            '. SubResCount: ' + cast(@SubResCount as varchar(3)) + '. SubResCountNX: ' + cast(@SubResCountNX as varchar(3)) + '.'

          EXEC WriteToLog @ReferenceNumber, @GuestNumber, @OutputText, 3
        END
        ELSE
        BEGIN
          SET @OutputText = 'Status changed by nightaudit (34): '

          IF @GetReservationStatusExists = 1
            SET @OutputText = @OutputText + 'Original state: ' + dbo.GetReservationStatus(@Status) +  '. New state: ' + dbo.GetReservationStatus(@NoshowStatus) + '.'

          -- BA/20110609: CR024455
          SET @OutputText = @OutputText + ' ArrivalDate: ' + @ArrivalDate + '. DepartureDate: ' + @DepartureDate + '. EndDate: ' + @EndDate + 
            '. SubResCount: ' + cast(@SubResCount as varchar(3)) + '. SubResCountNX: ' + cast(@SubResCountNX as varchar(3)) + '.'

          EXEC WriteToLog @ReferenceNumber, @GuestNumber, @OutputText, 2
        END 

        SET @StatusNow = @NoshowStatus
      END
    END

    IF @StatusNow = 5 
    BEGIN
      IF @GuestNumber > 0 -- We have checked in a subres. We need to checkin mainres also!
      BEGIN
        SELECT @MainStatus = Status FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = 0
        IF @MainStatus < 5
        BEGIN
          UPDATE Reservations SET Status=5 WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = 0 
          UPDATE ReservationRoomline
          SET StatusRL = 5
          FROM ReservationRoomline rl (NOLOCK)
          INNER JOIN ReservationRoomLineXTAB rlx (NOLOCK) ON rl.LineNumber = rlx.LineNumber and ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber
 
          -- BA/20110209: CR018919
          INSERT INTO #ProcessedReservations
                 (ReferenceNumber, GuestNumber, RoomNumber, OldStatus, NewStatus, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, HotelNumber, Adults, Children1, Children2)
          VALUES (@ReferenceNumber, @GuestNumber, @RoomNumber, @MainStatus, 5, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @HotelNumber, @Adults, @Children1, @Children2)

          SET @OutputText = 'Mainreservation checked-in by nightaudit (44): '

          IF @GetReservationStatusExists = 1
            SET @OutputText = @OutputText + 'Original state: ' + dbo.GetReservationStatus(@MainStatus) +  '. New state: ' + dbo.GetReservationStatus(5) + '.'

          -- BA/20110609: CR024455
          SET @OutputText = @OutputText + ' ArrivalDate: ' + @ArrivalDate + '. DepartureDate: ' + @DepartureDate + '. EndDate: ' + @EndDate + 
            '. SubResCount: ' + cast(@SubResCount as varchar(3)) + '. SubResCountNX: ' + cast(@SubResCountNX as varchar(3)) + '.'

          EXEC WriteToLog @ReferenceNumber, @GuestNumber, @OutputText, 2  -- Allways only changed

        END
      END

      if (((select Count(*) from ReservationConferenceLine WITH (NOLOCK) where ReferenceNumber = @ReferenceNumber) > 0) OR ((@SubResCount > 0) AND (@GuestNumber = 0)))--  if exist in conferenceroomline or mainres
      begin  
        DECLARE #ReservationAddCur SCROLL CURSOR FOR

        SELECT AddressType
        FROM ReservationAddress WITH (NOLOCK) INNER JOIN ReservationAddressXTab WITH (NOLOCK)  on ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber 
        WHERE ReservationAddress.ADDRESSTYPE IN (2,3) AND ReservationAddressXTab.ReferenceNumber = @ReferenceNumber AND ReservationAddressXTab.GuestNumber = @GuestNumber

        Open #ReservationAddCur
        Fetch Next From #ReservationAddCur
        Into @AddressType

        SET @CreateGuestFolio = 1

        WHILE  (@@FETCH_STATUS <> -1)
        begin
          if @AddressType in (2,3) 
            SET @CreateGuestFolio = 0

          Fetch Next From #ReservationAddCur
          Into @AddressType
        end
           
        if @CreateGuestFolio = 1
        begin
          SET @AddressType = 1 -- Der bliver oprette en guest
          exec FindFolioNumber @ReferenceNumber, @GuestNumber, @UserIDNumber, 0, 0, 1,  @FolioNumber OUTPUT, @AddressType OUTPUT
        end
        else
        begin
          Fetch First From #ReservationAddCur
          Into @AddressType

          WHILE  (@@FETCH_STATUS <> -1)
          begin
            exec FindFolioNumber @ReferenceNumber, @GuestNumber, @UserIDNumber, 0, 0, 1,  @FolioNumber OUTPUT, @AddressType OUTPUT
            Fetch Next From #ReservationAddCur
            Into @AddressType
          end
        end
 
        Close #ReservationAddCur
        Deallocate #ReservationAddCur
      end
      else
      begin
        SET @AddressType = 1 -- Der bliver oprette en guest
        exec FindFolioNumber @ReferenceNumber, @GuestNumber, @UserIDNumber, 0, 0, 1,  @FolioNumber OUTPUT, @AddressType OUTPUT
      end
    end

    SELECT @StatusNow = Status FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber

    Fetch Next From #ReservationCur
    Into @ReferenceNumber, @GuestNumber, @Status, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @CustomerNumber
  END

  /* AV 20041025 Tables must also be handled */
  DECLARE @TableBookingEnabled bit
  SELECT @TableBookingEnabled = CONVERT(bit,KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'TableBookingEnabled'
  IF @TableBookingEnabled = 1
  BEGIN
    DECLARE @TableStatus int
    DECLARE @TAbleLineId int
    DECLARE @SCrNumber int
    DECLARE @ScrNumberChar char 
    DECLARE @DisplayText Varchar (35)

    DECLARE TableCur CURSOR FOR
    SELECT ReservationConfLineNumber,ReferenceNumber, ISNULL(GuestNo,0),TableStatus,Number FROM ReservationConferenceLine WITH (NOLOCK)
    LEFT OUTER JOIN ConferenceRooms WITH (NOLOCK) ON ReservationConferenceLine.ConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber
    INNER JOIN CONFERENCEROOMTYPES WITH (NOLOCK) ON RESERVATIONCONFERENCELINE.BookedConferenceRoomTypeNumber = ConferenceRoomTypes.ConferenceRoomTypeNumber
      AND ISNULL(ConferenceRoomTypes.ResourceType, 0) NOT IN (3,9) -- do not select bowling resourcetypes
    WHERE TableBooking = 1 AND ISNULL(TableStatus,-1) < 5 AND ArrivalDate <= @EndDate -- and ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationRoomLineXtab WITH (NOLOCK))
    OPEN TableCur
    FETCH NEXT FROM TableCur
    INTO @TableLineId,@ReferenceNumber,@GuestNumber,@TableStatus,@ScrNumber
    WHILE(@@Fetch_status=0)
    BEGIN
      SET @ScrNumberChar = ''  
      IF IsNull (@ScrNumber,0) > 0
        SET @ScrNumberChar = Convert(Char(3),@ScrNumber)
      if @TableStatus = 5  
      BEGIN  
        PRINT 'Table InHouse'
        /* 
        UPDATE ReservationConferenceLine SET TableStatus = 6 WHERE ReservationConfLineNumber =  @TableLineId
        IF @@RowCount > 0
        BEGIN
          SET @DisplayText = 'Table '+ @ScrNumberChar+' Checked out by nightaudit' 
          EXEC WriteToLog @ReferenceNumber, @GuestNumber, @DisplayText, 4
        END
         */
      END
      ELSE
      BEGIN
        UPDATE ReservationConferenceLine SET TableStatus = 8 WHERE ReservationConfLineNumber =  @TableLineId
        IF @@RowCount > 0
        BEGIN
          SET @DisplayText = 'Table ' + @ScrNumberChar + ' Set NoShow by nightaudit'

          IF EXISTS (SELECT * FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber)
            EXEC WriteToLog @ReferenceNumber, @GuestNumber,@DisplayText , 2
          IF NOT EXISTS (SELECT * FROM RESERVATIONCONFERENCELINE (NOLOCK) WHERE ReferenceNumber =  @ReferenceNumber AND ReservationConfLineNumber <> @TableLineId) AND
           NOT EXISTS (SELECT * FROM ReservationRoomlineXTAB (NOLOCK) WHERE ReferenceNumber =  @ReferenceNumber) 
            UPDATE Reservations
            SET Status = 8
            WHERE ReferenceNumber = @ReferenceNumber AND
            GuestNumber = @GuestNumber

        END
      END
     
      FETCH NEXT FROM TableCur
      INTO @TableLineId,@ReferenceNumber,@GuestNumber,@TableStatus,@ScrNumber
    END
    CLOSE TableCur
    DEALLOCATE TableCur

  END 
  /* AV 20041025 END */

  CLOSE #ReservationCur
  DEALLOCATE #ReservationCur

  DECLARE #ReservationCur CURSOR FORWARD_ONLY FOR

  SELECT DISTINCT R.ReferenceNumber,R.GuestNumber,ISNULL(R.Status, 0) AS STATUS ,R.ArrivalDate,R.ArrivalTime,R.DepartureDate,R.DepartureTime,
                  R.Adults, R.Children1, R.Children2, RM.FkCustomerHotel
  FROM Reservations R  WITH (NOLOCK)
  INNER JOIN ReservationMain RM WITH (NOLOCK) ON RM.ReferenceNumber = R.ReferenceNumber
  INNER JOIN ArrangementReservationsXtab ARX WITH (NOLOCK) ON ARX.ReferenceNumber = R.ReferenceNumber  AND ARX.GuestNumber = R.GuestNumber
  INNER JOIN ArrangementLines AL WITH (NOLOCK) ON AL.ArrangementLineNumber = ARX.ArrangementLineNumber AND AL.Date <= @EndDate
  LEFT OUTER JOIN ReservationRoomLineXTab RLX WITH (NOLOCK) ON RLX.ReferenceNumber = R.ReferenceNumber
  LEFT OUTER JOIN ReservationConferenceLine RCL WITH (NOLOCK) ON RCL.ReferenceNumber = R.ReferenceNumber
  WHERE RLX.LineNumber IS NULL AND RCL.ReservationConfLineNumber IS NULL AND ISNULL(Status,0) < 5

  Open #ReservationCur

  Fetch Next From #ReservationCur
  Into @ReferenceNumber, @GuestNumber, @Status, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @Adults, @Children1, @Children2, @CustomerNumber

  WHILE  (@@FETCH_STATUS <> -1)
  BEGIN
    IF IsNull (@Status, 0) = 0  -- Replaces a NULL with a 0
      SET @Status = 0

    IF @Status = 1
    BEGIN
      SELECT @NoshowStatus = HSV.KeyValue FROM HotelSetup HS WITH (NOLOCK) 
      INNER JOIN HotelSetupValueXTab HSVX WITH (NOLOCK) ON HS.HotelSetUpNumber = HSVX.HotelSetupNumber
      INNER JOIN HotelSetupValue HSV WITH (NOLOCK) ON HSVX.FK_ValueNumber = HSV.PK_ValueNumber
      WHERE SearchKey = 'WareNoshowStatusProvisional' and HSVX.CustomerNumber = @CustomerNumber

      IF ISNULL(@NoshowStatus, -1) = -1
        SELECT @NoshowStatus = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'WareNoshowStatusProvisional'
    END
    ELSE IF @Status = 2
    BEGIN
      SELECT @NoshowStatus = HSV.KeyValue FROM HotelSetup HS WITH (NOLOCK) 
      INNER JOIN HotelSetupValueXTab HSVX WITH (NOLOCK) ON HS.HotelSetUpNumber = HSVX.HotelSetupNumber
      INNER JOIN HotelSetupValue HSV WITH (NOLOCK) ON HSVX.FK_ValueNumber = HSV.PK_ValueNumber
      WHERE SearchKey = 'WareNoshowStatusGuaranteed' and HSVX.CustomerNumber = @CustomerNumber

      IF ISNULL(@NoshowStatus, -1) = -1
        SELECT @NoshowStatus = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'WareNoshowStatusGuaranteed'
    END
    ELSE IF @Status = 3
    BEGIN
      SELECT @NoshowStatus = HSV.KeyValue FROM HotelSetup HS WITH (NOLOCK) 
      INNER JOIN HotelSetupValueXTab HSVX WITH (NOLOCK) ON HS.HotelSetUpNumber = HSVX.HotelSetupNumber
      INNER JOIN HotelSetupValue HSV WITH (NOLOCK) ON HSVX.FK_ValueNumber = HSV.PK_ValueNumber
      WHERE SearchKey = 'WareNoshowStatusWaitinglist' and HSVX.CustomerNumber = @CustomerNumber

      IF ISNULL(@NoshowStatus, -1) = -1
        SELECT @NoshowStatus = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'WareNoshowStatusWaitinglist'
    END
    ELSE IF @Status = 4
    BEGIN
      SELECT @NoshowStatus = HSV.KeyValue FROM HotelSetup HS WITH (NOLOCK) 
      INNER JOIN HotelSetupValueXTab HSVX WITH (NOLOCK) ON HS.HotelSetUpNumber = HSVX.HotelSetupNumber
      INNER JOIN HotelSetupValue HSV WITH (NOLOCK) ON HSVX.FK_ValueNumber = HSV.PK_ValueNumber
      WHERE SearchKey = 'WareNoshowStatusTentative' and HSVX.CustomerNumber = @CustomerNumber

      IF ISNULL(@NoshowStatus, -1) = -1
        SELECT @NoshowStatus = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'WareNoshowStatusTentative'
    END
    ELSE
    BEGIN
      SELECT @NoshowStatus = HSV.KeyValue FROM HotelSetup HS WITH (NOLOCK) 
      INNER JOIN HotelSetupValueXTab HSVX WITH (NOLOCK) ON HS.HotelSetUpNumber = HSVX.HotelSetupNumber
      INNER JOIN HotelSetupValue HSV WITH (NOLOCK) ON HSVX.FK_ValueNumber = HSV.PK_ValueNumber
      WHERE SearchKey = 'WareNoshowStatusConfirmed' and HSVX.CustomerNumber = @CustomerNumber

      IF ISNULL(@NoshowStatus, -1) = -1
        SELECT @NoshowStatus = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'WareNoshowStatusConfirmed'
    END

    UPDATE Reservations SET Status = @NoshowStatus WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber

    INSERT INTO #ProcessedReservations
           (ReferenceNumber, GuestNumber, RoomNumber, OldStatus, NewStatus, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, HotelNumber, Adults, Children1, Children2)
    VALUES (@ReferenceNumber, @GuestNumber, @RoomNumber, @Status, @NoshowStatus, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @HotelNumber, @Adults, @Children1, @Children2)

    SET @OutputText = 'Status changed by nightaudit (46): '

    IF @GetReservationStatusExists = 1
      SET @OutputText = @OutputText + 'Original state: ' + dbo.GetReservationStatus(@Status) +  '. New state: ' + dbo.GetReservationStatus(@NoshowStatus) + '.'        
            
    EXEC WriteToLog @ReferenceNumber, @GuestNumber, @OutputText, 2  -- Allways only changed

    Fetch Next From #ReservationCur
    Into @ReferenceNumber, @GuestNumber, @Status, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @Adults, @Children1, @Children2, @CustomerNumber
  END

CLOSE #ReservationCur
DEALLOCATE #ReservationCur

EXEC SetOutOfOrderRoomAllFutureAllRooms

-- BA/20110209: CR018919
SELECT * FROM #ProcessedReservations