CREATE PROCEDURE DBO.UpdateArrivalDateOnNoShows @EndDate CHAR (8) AS
SET NOCOUNT ON


DECLARE @PostponeArrivalConfirmed BIT,
@PostponeArrivalProvisional BIT,
@PostponeArrivalGuaranteed BIT,
@PostponeArrivalWaitinglist BIT,
@PostponeArrivalTentative BIT,
@ReferenceNumber INT,
@GuestNumber INT,
@BGuestNumber INT,
@LineNumber	INT,
@Status INT,
@ArrivalDate CHAR (8),
@DepartureDate CHAR (8),
@CustomerNumber	INT,
@tmpDate DATETIME,
@NewArrivalDate CHAR(8),
@OutputText VARCHAR(800)

DECLARE #ReservationCur CURSOR FORWARD_ONLY FOR

	SELECT R.ReferenceNumber,R.GuestNumber,R.Status,RL.LineNumber,
	CASE WHEN R.GuestNumber = 0 THEN rl.ArrivalDate ELSE R.ArrivalDate END AS ArrivalDate, 
	CASE WHEN R.GuestNumber = 0 THEN rl.DepartureDate ELSE R.DepartureDate END AS DepartureDate, 
	CASE WHEN ISNULL(RL.RoomNumber, 0) > 0
		THEN
		CRO.CustomerNumber
		ELSE
		CRT.CustomerNumber
	END AS CustomerNumber
	FROM ReservationRoomLine RL WITH (NOLOCK)
	INNER JOIN ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber=RLX.LineNumber 
	INNER 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 
	CASE WHEN R.GuestNumber = 0 THEN rl.ArrivalDate ELSE R.ArrivalDate END = @EndDate

Open #ReservationCur

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

  WHILE  (@@FETCH_STATUS <> -1)
  BEGIN
	 SELECT  @PostponeArrivalConfirmed = 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 = 'PostponeArrivalConfirmed' and HSVX.CustomerNumber = @CustomerNumber

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

     SELECT  @PostponeArrivalProvisional = 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 = 'PostponeArrivalProvisional' and HSVX.CustomerNumber = @CustomerNumber

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

	 SELECT  @PostponeArrivalGuaranteed = 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 = 'PostponeArrivalGuaranteed' and HSVX.CustomerNumber = @CustomerNumber

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

	 SELECT  @PostponeArrivalWaitinglist = 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 = 'PostponeArrivalWaitinglist' and HSVX.CustomerNumber = @CustomerNumber

	 IF ISNULL(@PostponeArrivalWaitinglist, -1) = -1
		SELECT @PostponeArrivalWaitinglist = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'PostponeArrivalWaitinglist'
	 
	 SELECT  @PostponeArrivalTentative = 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 = 'PostponeArrivalTentative' and HSVX.CustomerNumber = @CustomerNumber

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

	IF ((@Status = 0 AND @PostponeArrivalConfirmed = 1) OR
	   (@Status = 1 AND @PostponeArrivalProvisional = 1) OR
	   (@Status = 2 AND @PostponeArrivalGuaranteed = 1) OR
	   (@Status = 3 AND @PostponeArrivalWaitinglist = 1) OR
	   (@Status = 4 AND @PostponeArrivalTentative = 1)) AND
	   (@ArrivalDate < @DepartureDate)
	   BEGIN
		 SET @tmpDate = CONVERT(DATETIME, @ArrivalDate, 112)
		 SET @TmpDate = DATEADD(day,1,@tmpDate)
		 SET @NewArrivalDate = CONVERT(CHAR(8), @tmpDate, 112)
	     IF @GuestNumber > 0
			UPDATE Reservations
			Set ArrivalDate = @NewArrivalDate
			WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber

		 UPDATE ReservationRoomline
		 SET ArrivalDate = @NewArrivalDate
		 WHERE LINENUMBER = @LineNumber

		 EXEC UpdateArrivalAndDeparture2 @ReferenceNumber, @GuestNumber

		 SET @OutputText = 'Arrival date changed by nightaudit: Original arrival date: ' + @ArrivalDate + ' New arrival date: ' + @NewArrivalDate
		 EXEC WriteToLog @ReferenceNumber, @GuestNumber, @OutputText, 3 

	   END

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

  CLOSE #ReservationCur
  DEALLOCATE #ReservationCur