CREATE PROCEDURE DBO.FixReservationDates @TheHotelNumber int, @StartDate DATETIME=0 AS
SET NOCOUNT ON
/*
    Created by Jacob Pedersen

    Last Update:
	20060627 JP: OPTIMIZE: added dbo to tables and views plus made some SQL faster
	15/7-2003 Jacob Pedersen fixed @TheHotelNumber use
	11/9-2002 Jacob Pedersen

    NOTE: Returns a log over all the reservations which was updated with new data

                @WasUpdated is binary. 1 means subres was updated. 2 means mainres was updated. 3 (1+2) means both were updated.
*/

CREATE TABLE #UpdatedLog (
	ReferenceNumber INT,
	GuestNumber INT,
	Code SMALLINT,
	Description VARCHAR (255) NULL)
ON [PRIMARY]


DECLARE @WasUpdated SMALLINT
DECLARE @Description VARCHAR (255)

DECLARE @ReferenceNumber INT
DECLARE @GuestNumber INT

DECLARE @GuestArrivalDate CHAR (8)
DECLARE @GuestArrivalTime CHAR (5)
DECLARE @GuestDepartureDate CHAR (8)
DECLARE @GuestDepartureTime CHAR (5)
DECLARE @GuestAdults INT
DECLARE @GuestChildren1 INT
DECLARE @GuestChildren2 INT
DECLARE @GuestPrice MONEY
DECLARE @GuestTotalPrice Money
DECLARE @GuestProductNumber INT
DECLARE @ArrivalDate CHAR (8)
DECLARE @ArrivalTime CHAR (5)
DECLARE @DepartureDate CHAR (8)
DECLARE @DepartureTime CHAR (5)
DECLARE @Adults INT
DECLARE @Children1 INT
DECLARE @Children2 INT
DECLARE @Price MONEY
DECLARE @TotalPrice Money

UPDATE reservations SET adults= 0 WHERE adults IS NULL
UPDATE reservations SET children1= 0 WHERE children1 IS NULL
UPDATE reservations SET children2= 0 WHERE children2 IS NULL
UPDATE reservations SET Price= 0 WHERE Price IS NULL
UPDATE reservations SET TotalPrice= 0 WHERE TotalPrice IS NULL
UPDATE ReservationRoomLine SET adults= 0 WHERE adults IS NULL
UPDATE ReservationRoomLine SET children1= 0 WHERE children1 IS NULL
UPDATE ReservationRoomLine SET children2= 0 WHERE children2 IS NULL
UPDATE ReservationRoomLine SET Price= 0 WHERE Price IS NULL
UPDATE ReservationRoomLine SET TotalPrice= 0 WHERE TotalPrice IS NULL

UPDATE ReservationMain SET HotelNumber=0 WHERE HotelNumber IS NULL   -- JP 19/06-2002

IF @StartDate=0
  SELECT @StartDate=SystemDate FROM System WITH (NOLOCK) WHERE Number=1


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--  OUTROOM ERROR
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/* NOTE: Remarked because it does NOT work with the combination of early payout, outroom and then checkout.  This will make the departuredate set to last tran date eventhough he departed days ago. BIG ERROR.
                Code has been remarked, because the error is fixed and then there is no need for it since it cannot be fixed in all cases

Declare #RoomLineCur Cursor Forward_Only For

select referencenumber, transactions.guestnumber, convert (char, max (originaldate)+1, 112) as lasttrandate --, (select departuredate from reservations (nolock) where reservations.referencenumber=folios.referencenumber and reservations.guestnumber=transactions.guestnumber) as departuredate
from folios (nolock) left outer join transactions (nolock) on transactions.folionumber=folios.folionumber
where warenumber in (select warenumber from ware (nolock) where waregroupnumber in (select waregroupnumber from waregroups (nolock) where payment=2))
and debit=1 and pieces>0 and showonbillflag=1
group by referencenumber, transactions.guestnumber -- , debit, pieces, showonbillflag
having
convert (char, max (originaldate)+1, 112) > (select departuredate from reservations (nolock) where reservations.referencenumber=folios.referencenumber and reservations.guestnumber=transactions.guestnumber)
and (select departuredate from reservations (nolock) where reservations.referencenumber=folios.referencenumber and reservations.guestnumber=transactions.guestnumber) > CONVERT (CHAR (8), @StartDate, 112)
and (select status from reservations (nolock) where reservations.referencenumber=folios.referencenumber and reservations.guestnumber=transactions.guestnumber) in (5, 6)

Open #RoomLineCur

Fetch Next From #RoomLineCur
Into @ReferenceNumber, @GuestNumber, @DepartureDate  -- @departuredate = lasttrandate

While  (@@FETCH_STATUS <> -1)
Begin
  IF @GuestNumber=0
    UPDATE ReservationRoomLine SET DepartureDate=@DepartureDate WHERE LineNumber = (SELECT LineNumber FROM ReservationRoomLineXTab (NOLOCK) WHERE ReferenceNumber=@ReferenceNumber AND GuestNumber=0)
  ELSE
    UPDATE Reservations SET DepartureDate=@DepartureDate WHERE ReferenceNumber=@ReferenceNumber AND GuestNumber=@GuestNumber
  IF @@rowcount > 0
  BEGIN
    EXEC UpdateArrivalAndDeparture @ReferenceNumber, @GuestNumber,
              @GuestArrivalDate output, @GuestArrivalTime output, @GuestDepartureDate output, @GuestDepartureTime output, @GuestAdults output, @GuestChildren1 output, @GuestChildren2 output, @GuestPrice output, @GuestTotalPrice output, @GuestProductNumber output,
              @ArrivalDate output, @ArrivalTime output, @DepartureDate output, @DepartureTime output, @Adults output, @Children1 output, @Children2 output, @Price output, @TotalPrice output, 1, @WasUpdated output

    IF @WasUpdated in (1,3)
      INSERT INTO #UpdatedLog (ReferenceNumber, GuestNumber, Code, Description) VALUES (@ReferenceNumber, @GuestNumber, 0,  'Fixed Outroom error')
    IF @WasUpdated in (2,3)
      INSERT INTO #UpdatedLog (ReferenceNumber, GuestNumber, Code, Description) VALUES (@ReferenceNumber, 0, 0,  'Fixed Outroom error')
  END

  Fetch Next From #RoomLineCur
  Into @ReferenceNumber, @GuestNumber, @DepartureDate
END -- WHILE

CLOSE #RoomLineCur
DEALLOCATE #RoomLineCur
*/
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--  SUB RES
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE #RoomLineCur Cursor Forward_Only For

SELECT ReferenceNumber, GuestNumber FROM dbo.TMultiPurposeView WITH (NOLOCK)
WHERE (Status < 7 or Status is null)  AND HotelNumber=@TheHotelNumber AND ReferenceNumber > 0 AND GuestNumber > 0 AND (DepartureDate>= CONVERT (CHAR (8), @StartDate, 112) OR (SpecDepartureDate>= CONVERT (CHAR (8), @StartDate, 112) ))
ORDER BY ReferenceNumber, LineNumber

Open #RoomLineCur

Fetch Next From #RoomLineCur
Into @ReferenceNumber, @GuestNumber

WHILE  (@@FETCH_STATUS <> -1)
BEGIN
  EXEC UpdateArrivalAndDeparture @ReferenceNumber, @GuestNumber,
            @GuestArrivalDate output, @GuestArrivalTime output, @GuestDepartureDate output, @GuestDepartureTime output, @GuestAdults output, @GuestChildren1 output, @GuestChildren2 output, @GuestPrice output, @GuestTotalPrice output, @GuestProductNumber output,
            @ArrivalDate output, @ArrivalTime output, @DepartureDate output, @DepartureTime output, @Adults output, @Children1 output, @Children2 output, @Price output, @TotalPrice output, 1, @WasUpdated output

  IF @WasUpdated in (1,3)
    INSERT INTO #UpdatedLog (ReferenceNumber, GuestNumber, Code) VALUES (@ReferenceNumber, @GuestNumber, 1)
  IF @WasUpdated in (2,3)

    INSERT INTO #UpdatedLog (ReferenceNumber, GuestNumber, Code) VALUES (@ReferenceNumber, 0, 1)
  
  Fetch Next From #RoomLineCur
  INTO @ReferenceNumber, @GuestNumber
END -- WHILE

CLOSE #RoomLineCur
DEALLOCATE #RoomLineCur

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--  MAIN RES without hall lines
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Declare #RoomLineCur Cursor Forward_Only For

SELECT DISTINCT ReferenceNumber FROM dbo.TMultiPurposeView WITH (NOLOCK)  -- , specarrivaldate, specdeparturedate, rowsonmainres
WHERE ((Status < 7 or Status is null) AND HotelNumber=@TheHotelNumber) AND ReferenceNumber > 0 AND GuestNumber = 0 and RowsOnMainRes>0 and (not ReferenceNumber in (SELECT DISTINCT ReferenceNumber FROM dbo.ReservationConferenceLine WITH (NOLOCK)))
AND (DepartureDate>= CONVERT (CHAR (8), @StartDate, 112) OR (SpecDepartureDate>= CONVERT (CHAR (8), @StartDate, 112) ))
ORDER BY ReferenceNumber -- needed?

Open #RoomLineCur

Fetch Next From #RoomLineCur
Into @ReferenceNumber

While  (@@FETCH_STATUS <> -1)
Begin
  EXEC UpdateArrivalAndDeparture @ReferenceNumber, 0, 
            @GuestArrivalDate output, @GuestArrivalTime output, @GuestDepartureDate output, @GuestDepartureTime output, @GuestAdults output, @GuestChildren1 output, @GuestChildren2 output, @GuestPrice output, @GuestTotalPrice output, @GuestProductNumber output,
            @ArrivalDate output, @ArrivalTime output, @DepartureDate output, @DepartureTime output, @Adults output, @Children1 output, @Children2 output, @Price output, @TotalPrice output, 1, @WasUpdated output

  IF @WasUpdated in (1,3)
    INSERT INTO #UpdatedLog (ReferenceNumber, GuestNumber, Code) VALUES (@ReferenceNumber, @GuestNumber, 2)
  IF @WasUpdated in (2,3)
    INSERT INTO #UpdatedLog (ReferenceNumber, GuestNumber, Code) VALUES (@ReferenceNumber, 0, 2)

  Fetch Next From #RoomLineCur
  Into @ReferenceNumber
End -- WHILE

CLOSE #RoomLineCur
DEALLOCATE #RoomLineCur

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--  MAIN RES WITH NO ROOMLINES BUT HALLLINES INSTEAD
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Declare #RoomLineCur Cursor Forward_Only For

SELECT DISTINCT ReservationConferenceLine.ReferenceNumber FROM dbo.ReservationConferenceLine WITH (NOLOCK) INNER JOIN dbo.Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber=ReservationConferenceLine.ReferenceNumber AND Reservations.GuestNumber=0 AND ReservationConferenceLine.ReferenceNumber>0 AND (Status < 7 or Status is null) 
INNER JOIN dbo.ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber=Reservations.ReferenceNumber AND HotelNumber=@TheHotelNumber
ORDER BY ReservationConferenceLine.ReferenceNumber -- needed?

Open #RoomLineCur

Fetch Next From #RoomLineCur
Into @ReferenceNumber

While  (@@FETCH_STATUS <> -1)
Begin
  EXEC UpdateArrivalAndDeparture @ReferenceNumber, 0, 
            @GuestArrivalDate output, @GuestArrivalTime output, @GuestDepartureDate output, @GuestDepartureTime output, @GuestAdults output, @GuestChildren1 output, @GuestChildren2 output, @GuestPrice output, @GuestTotalPrice output, @GuestProductNumber output,
            @ArrivalDate output, @ArrivalTime output, @DepartureDate output, @DepartureTime output, @Adults output, @Children1 output, @Children2 output, @Price output, @TotalPrice output, 1, @WasUpdated output

  IF @WasUpdated in (1,3)
    INSERT INTO #UpdatedLog (ReferenceNumber, GuestNumber, Code) VALUES (@ReferenceNumber, @GuestNumber, 3)
  IF @WasUpdated in (2,3)
    INSERT INTO #UpdatedLog (ReferenceNumber, GuestNumber, Code) VALUES (@ReferenceNumber, 0, 3)

  Fetch Next From #RoomLineCur
  Into @ReferenceNumber
End -- WHILE

CLOSE #RoomLineCur
DEALLOCATE #RoomLineCur

SELECT * FROM #UpdatedLog WITH (NOLOCK) ORDER BY ReferenceNumber, GuestNumber

DROP TABLE #UpdatedLog

