CREATE FUNCTION [dbo].[GetNumberOfRoomsForCustomer] (@ACustomerNumber INT) RETURNS INT AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result INT
	DECLARE @NumberOfNights INT
	DECLARE @NumberOfRooms INT
	DECLARE @TurnOver MONEY

	DECLARE @LCurDT DATETIME
	SELECT @LCurDT = dbo.GetSystemDateTime('')
	SET @LCurDT = CAST(@LCurDT AS DATE)

	DECLARE @LDaysBack INT
	DECLARE @LDaysForward INT
	DECLARE @LUseMISAsOrderBook BIT
	DECLARE @LField INT

	SELECT @LDaysBack = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'PlanGuestNightDaysBack'
	SET @LDaysBack = ISNULL(@LDaysBack, 365)
	SELECT @LDaysForward = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'PlanGuestNightDaysForward'
	SET @LDaysForward = ISNULL(@LDaysForward, 365)
	SELECT @LUseMISAsOrderBook = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'OrderbookUseMIS'
	SET @LUseMISAsOrderBook = ISNULL(@LUseMISAsOrderBook, 0)
	SELECT @LField = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'PlanGuestNightBasedOn'
	SET @LField = ISNULL(@LField, 0)

	IF @LUseMISAsOrderBook = 1
	BEGIN
		DECLARE @tmp TABLE  (CustomerNumber INT NOT NULL)
		INSERT INTO @tmp
		SELECT CustomerNumber FROM dbo.GetOrderBookCustomerLinks(@ACustomerNumber)
		INSERT INTO @tmp (CustomerNumber) VALUES (@ACustomerNumber)

		SELECT  @NumberOfRooms = SUM(ISNULL(BookedNumberOfRooms, 0)) + SUM(ISNULL(ActualNumberOfRooms, 0)),
				@TurnOver = SUM(ISNULL(BookedPrice, 0)) + SUM(ISNULL(ActualPrice, 0)) + SUM(ISNULL(ActualPackage, 0))
		FROM MISTable WITH (NOLOCK)
		INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = MISTable.ReferenceNumber
		  AND Reservations.GuestNumber = MISTable.GuestNumber
		INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = Reservations.ReferenceNumber
		  AND ReservationAddressXTab.GuestNumber = Reservations.GuestNumber
		INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber		
		INNER JOIN @tmp t1 ON ReservationAddress.Link = t1.CustomerNumber

		WHERE MISTable.DateStamp BETWEEN DATEADD(DAY, -@LDaysBack, @LCurDT) AND DATEADD(DAY, @LDaysForward, @LCurDT)
		AND MISTable.ReservationStatus IN (0,1,2,5,6)
		AND MISTable.ValueType = 1
		AND ((@LField = 1 AND MISTable.DateStamp = Reservations.ArrivalDate) OR @LField <> 1)

		IF @LField = 2
			SET @Result = CAST (ISNULL(@TurnOver, 0) AS INT)
		ELSE 
			SET @Result = ISNULL(@NumberOfRooms, 0)
	END
	ELSE
	BEGIN
		SELECT  @NumberOfNights = SUM(ISNULL(NumberOfNightRooms, 0)), @NumberOfRooms = SUM(ISNULL(NumberOfStaysRooms, 0)), @TurnOver = SUM(ISNULL(TurnOverRooms, 0))
		FROM CustomerOrdreBook WITH (NOLOCK)
		WHERE CustomerNumber = @ACustomerNumber
		AND DateForValue BETWEEN DATEADD(DAY, -@LDaysBack, @LCurDT) AND DATEADD(DAY, @LDaysForward, @LCurDT)

		SET @Result = CASE @LField
							WHEN 0 THEN ISNULL(@NumberOfNights, 0)
							WHEN 1 THEN ISNULL(@NumberOfRooms, 0)
							WHEN 2 THEN CAST(ISNULL(@TurnOver, 0) AS INT)
						END

	END

	RETURN(@Result)
END
	

