/****** Object:  StoredProcedure [dbo].[OnlineFindReservationNoViaRoomNo]    Script Date: 02/23/2012 14:27:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- =============================================
-- Author:		LJE
-- Create date: 2012.02.22
-- Description:	Find ReservationNo. via RoomNo; used in 
--              PayAndCheckOut - when user do not enter reservation no.
-- 
-- Changes:    
--              LJE2012.07.09: @AHotelChainNumber parameter added.
--              LJE2012.06.28: Do not use GuestNumber. Only call with roomnumber, i.e. "107" - and select top 1, order by LineNumber desc. 
--              LJE2012.04.24: Only find in-house-reservations, AND Status = 5.
--              LJE2012.03.01: Change where-clause for systemDate.
--              LJE2012.02.28: Do not select rows where referencenumber = "0".
-- =============================================

CREATE PROCEDURE [dbo].[OnlineFindReservationNoViaRoomNo]
	@ARoomNumber int
	,@AGuestNumber int = 0  -- Not used!
	,@ACurrentDateTime datetime  -- Not used!
	,@AHotelChainNumber int = 0
AS

BEGIN
	SET NOCOUNT ON;
	
	declare @systemDate varchar(20);
	select @systemDate = SYSTEMDATE from SYSTEM with (nolock)

	select top 1 RESERVATIONROOMLINEXTAB.[LineNumber] LineNumber
	  ,RESERVATIONS.[ReferenceNumber] ReferenceNumber
	  ,RESERVATIONROOMLINEXTAB.[GuestNumber] GuestNumber
	  ,RESERVATIONROOMLINE.[RoomNumber] RoomNumber
	  ,ROOMS.[Number] Number
	  ,RESERVATIONS.[Status] Status
	  ,CONVERT(DATETIME, RESERVATIONS.ArrivalDate, 112) + CONVERT(DATETIME, RESERVATIONS.ArrivalTime, 108) ArrivalDateTime
	  ,CONVERT(DATETIME, RESERVATIONS.DepartureDate, 112) + CONVERT(DATETIME, RESERVATIONS.DepartureTime, 108) DepartureDateTime
	from RESERVATIONS with (nolock) 
	  inner join RESERVATIONROOMLINEXTAB with (nolock) on RESERVATIONS.ReferenceNumber = RESERVATIONROOMLINEXTAB.ReferenceNumber
	  inner join RESERVATIONROOMLINE with (nolock) on RESERVATIONROOMLINEXTAB.LINENUMBER = RESERVATIONROOMLINE.LINENUMBER
	  inner join ROOMS with (nolock) on RESERVATIONROOMLINE.RoomNumber = ROOMS.RoomNumber
	where ROOMS.[Number] = @ARoomNumber
	  AND ROOMS.[HotelNumber] = @AHotelChainNumber
	  AND CONVERT(DATETIME, RESERVATIONS.ArrivalDate, 112) --+ CONVERT(DATETIME, RESERVATIONS.ArrivalTime, 108) 
	    <= @systemDate
	  AND CONVERT(DATETIME, RESERVATIONS.DepartureDate, 112) --+ CONVERT(DATETIME, RESERVATIONS.DepartureTime, 108) 
	    >= @systemDate
	  --AND RESERVATIONROOMLINEXTAB.GuestNumber = @AGuestNumber
	  AND Status = 5 -- Only find in-house-reservations --AND Status <= 5
	  AND RESERVATIONS.[ReferenceNumber] > 0 
	order by LineNumber desc, ArrivalDateTime desc
END
GO
