CREATE FUNCTION [dbo].[GetMiceReservationData] (@AReferenceNumber int, @AGuestNumber int, @ADateFrom char(8), @ADateTo char(8))  
RETURNS @table TABLE
(
    ReferenceNumber int NOT NULL,
    RoomLineNumber int NOT NULL,
    ArrivalDate char(8) COLLATE DATABASE_DEFAULT NOT NULL,
    DepartureDate char(8) COLLATE DATABASE_DEFAULT NOT NULL,
    RoomCount int DEFAULT 0,
    GuestCount int DEFAULT 0,
    CountryCode varchar(3) COLLATE DATABASE_DEFAULT NOT NULL
)
AS  
BEGIN 

-- OF 20100810 Count of guests on a room was always 1. @rlGuestCount used instead of @cGuestCount  
-- OF 20100517 GuestNumber introduced for returning data pr. guest only.
-- TL 20070117 Created. Used by SP GetMiceDataReservation.

DECLARE @rlRefNo int
DECLARE @rlLineNumber int
DECLARE @rlArrivalDate char(8)
DECLARE @rlDepartureDate char(8)
DECLARE @rlRoomCount int
DECLARE @rlGuestCount int

DECLARE @cCountryCode varchar(3)
--DECLARE @cGuestCount int

DECLARE CurMiceRoomLines CURSOR FAST_FORWARD READ_ONLY FOR
	SELECT DISTINCT RLX.ReferenceNumber, RL.LineNumber, RL.ArrivalDate, RL.DepartureDate, RL.NumberOfRooms AS RoomCount, (RL.Adults + RL.Children1 + RL.Children2) AS GuestCount FROM ReservationRoomline RL WITH (nolock)
	INNER JOIN ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber = RL.LineNumber AND ((RL.DepartureDate BETWEEN @ADateFrom AND @ADateTo) OR (RL.ArrivalDate > @ADateTo)) AND RLX.ReferenceNumber = @AReferenceNumber AND RLX.GuestNumber = @AGuestNumber
	INNER JOIN Reservations R WITH (NOLOCK) ON R.ReferenceNumber = RLX.ReferenceNumber AND R.GuestNumber = RLX.GuestNumber and R.Status < 7 -- rsCanceled
	ORDER BY RL.ArrivalDate, RL.DepartureDate

OPEN CurMiceRoomLines
FETCH NEXT FROM CurMiceRoomLines INTO @rlRefNo, @rlLineNumber, @rlArrivalDate, @rlDepartureDate, @rlRoomCount, @rlGuestCount

WHILE @@FETCH_STATUS <> -1
BEGIN
	DECLARE CurCountries CURSOR FAST_FORWARD READ_ONLY FOR
		SELECT DISTINCT IsNull(RA.CountryCode, '') AS CountryCode/*, COUNT(CountryCode) AS GuestCount*/ FROM Reservations R WITH (NOLOCK)
		INNER JOIN ReservationRoomLineXTab RLX WITH (NOLOCK) ON RLX.ReferenceNumber = R.ReferenceNumber AND RLX.GuestNumber = R.GuestNumber AND RLX.LineNumber = @rlLineNumber AND R.Status < 7
		INNER JOIN ReservationAddressXTab RAX WITH (NOLOCK) ON RAX.ReferenceNumber = R.ReferenceNumber AND RAX.GuestNumber = R.GuestNumber AND RAX.AddressTypeNumber = 1
		INNER JOIN ReservationAddress RA WITH (NOLOCK) ON RA.AddressNumber = RAX.AddressNumber
		GROUP BY RA.CountryCode
	OPEN CurCountries

	FETCH NEXT FROM CurCountries INTO @cCountryCode/*, @cGuestCount*/
	WHILE @@FETCH_STATUS <> -1
	BEGIN
		IF NOT EXISTS (SELECT ReferenceNumber FROM @table WHERE ReferenceNumber = @rlRefNo AND ArrivalDate = @rlArrivalDate AND DepartureDate = @rlDepartureDate AND RoomLineNumber = @rlLineNumber)
		BEGIN
			INSERT INTO @table (ReferenceNumber, RoomLineNumber, ArrivalDate, DepartureDate, RoomCount, GuestCount, CountryCode) VALUES (@rlRefNo, @rlLineNumber, @rlArrivaldate, @rlDepartureDate, @rlRoomCount, @rlRoomCount * @rlGuestCount/*@cGuestCount*/, @cCountryCode)
		END
		ELSE
		BEGIN
			IF NOT EXISTS (SELECT ReferenceNumber FROM @table WHERE ReferenceNumber = @rlRefNo AND ArrivalDate = @rlArrivalDate AND DepartureDate = @rlDepartureDate AND RoomLineNumber = @rlLineNumber AND CountryCode = @cCountryCode)
			BEGIN
				INSERT INTO @table (ReferenceNumber, RoomLineNumber, ArrivalDate, DepartureDate, RoomCount, GuestCount, CountryCode) VALUES (@rlRefNo, @rlLineNumber, @rlArrivaldate, @rlDepartureDate, 0, @rlGuestCount/*@cGuestCount*/, @cCountryCode)
			END
			ELSE
			BEGIN
				UPDATE @table SET RoomCount = RoomCount + @rlRoomCount, GuestCount = GuestCount + (@rlRoomCount * @rlGuestCount/*@cGuestCount*/) WHERE ReferenceNumber = @rlRefNo AND RoomLineNumber = @rlLineNumber AND ArrivalDate = @rlArrivalDate AND DepartureDate = @rlDepartureDate AND CountryCode = @cCountryCode
			END
		END

		FETCH NEXT FROM CurCountries INTO @cCountryCode/*, @cGuestCount*/
	END
	CLOSE CurCountries
	DEALLOCATE CurCountries
	FETCH NEXT FROM CurMiceRoomLines INTO @rlRefNo, @rlLineNumber, @rlArrivalDate, @rlDepartureDate, @rlRoomCount, @rlGuestCount
END
CLOSE CurMiceRoomLines
DEALLOCATE CurMiceRoomLines

RETURN

END