CREATE FUNCTION [dbo].[GetMISRoomCountInPeriod] (@StartDate DateTime, @EndDate DateTime,
                                                @SelectHotelNumbers varchar(50), --comma separated list
												@TypeGroupingValue int, -- 1: Day, 2: Week, 3: Month, 4: Year
												@CurrentDate DateTime, 
												@Type Char(6)) --ACTUAL or BOOK

RETURNS INT
AS
/*
  SG 2022-01-17: IsNull(@NoOfOOODays, 0) 
  SG 2021-02-02: Created
  Notes: Used in MIS (SP MISSelect) for REVPAR
         Calculates the accumulated sum of rooms in the period, taking "Out Of Order" into account
		 Only Rooms
*/
BEGIN
  DECLARE @CountInPeriod int

  Declare @ActualStartDate DateTime 
  Declare @ActualEndDate DateTime

  Declare @CurrentYear int
  Declare @CurrentMonth int
  Declare @CurrentDay int

  Set @CurrentYear  = DatePart(year, @CurrentDate)
  Set @CurrentMonth = DatePart(month, @CurrentDate)
  Set @CurrentDay   = DatePart(day, @CurrentDate)
  
   
  If @TypeGroupingValue = 1 --Day
  Begin
    If @Type = 'BOOK' 
	Begin
	  If @CurrentDate >= @StartDate
	  Begin
        Set @ActualStartDate = @CurrentDate
        Set @ActualEndDate = @CurrentDate
	  End Else
	  Begin
	    RETURN 0
	  End
    End Else
	Begin --ACTUAL
	  If @CurrentDate < @EndDate
	  Begin
        Set @ActualStartDate = @CurrentDate
        Set @ActualEndDate = @CurrentDate
	  End Else
	  Begin
	    RETURN 0
	  End
	End
  End

  If @Type = 'ACTUAL'
  Begin
    Set @EndDate = DateAdd(Day, -1, @EndDate)
  End
   
  If @TypeGroupingValue = 2 --Week
  Begin
	Declare @DateFirst int
    SELECT @DateFirst = @@DATEFIRST 

	If @Type = 'BOOK'
	Begin
	  If DatePart(Week, @CurrentDate) = DatePart(Week, @StartDate) And DatePart(Year, @CurrentDate) = DatePart(Year, @StartDate) 
	  Begin
	    Set @ActualStartDate = @StartDate
		Set @ActualEndDate = DATEADD(DAY, (8 - DATEPART(WEEKDAY, @CurrentDate)) % 7, @CurrentDate)  --Last day in week
      End Else
	  If DatePart(Week, @CurrentDate) > DatePart(Week, @StartDate) And DatePart(Year, @CurrentDate) >= DatePart(Year, @StartDate)
      Begin
        Set @ActualStartDate = CAST(CAST(@CurrentDate AS datetime) - DATEPART(WEEKDAY, CAST(@CurrentDate as datetime) + @@DATEFIRST + 5 ) % 7 AS date) --1. day in week
		Set @ActualEndDate = DATEADD(DAY, (8 - DATEPART(WEEKDAY, @CurrentDate)) % 7, @CurrentDate)  --Last day in week
	  End Else
	  Begin
	    RETURN 0
	  End
	End Else
	Begin --Actual
	  If DatePart(Week, @CurrentDate) = DatePart(Week, @StartDate) And DatePart(Year, @CurrentDate) = DatePart(Year, @StartDate) 
	  Begin
	    Set @ActualStartDate = CAST(CAST(@CurrentDate AS datetime) - DATEPART(WEEKDAY, CAST(@CurrentDate as datetime) + @@DATEFIRST + 5 ) % 7 AS date) --1. day in week
		Set @ActualEndDate = @EndDate
	  End Else
      If DatePart(Week, @CurrentDate) < DatePart(Week, @StartDate) And DatePart(Year, @CurrentDate) <= DatePart(Year, @StartDate) 
	  Begin
	    Set @ActualStartDate = CAST(CAST(@CurrentDate AS datetime) - DATEPART(WEEKDAY, CAST(@CurrentDate as datetime) + @@DATEFIRST + 5 ) % 7 AS date) --1. day in week
	    Set @ActualEndDate = DATEADD(DAY, (8 - DATEPART(WEEKDAY, @CurrentDate)) % 7, @CurrentDate)  --Last day in week
      End Else
	  Begin
	    RETURN 0
	  End
    End
  End

  If @TypeGroupingValue = 3 --Month
  Begin 
    If @Type = 'BOOK' 
	Begin
	  If DatePart(Month, @CurrentDate) = DatePart(Month, @StartDate) And DatePart(Year, @CurrentDate) = DatePart(Year, @StartDate) 
      Begin
	    Set @ActualStartDate = @StartDate
		Set @ActualEndDate = EOMonth(@CurrentDate)
      End Else
	  If DatePart(Month, @CurrentDate) > DatePart(Month, @StartDate) And DatePart(Year, @CurrentDate) >= DatePart(Year, @StartDate)
      Begin
        Set @ActualStartDate = DateFromParts(@CurrentYear, @CurrentMonth, 1) --1. day in month
		Set @ActualEndDate = EOMonth(@CurrentDate)
      End Else
	  Begin
	    RETURN 0
	  End
	End Else
    Begin --'ACTUAL' 
	  If DatePart(Month, @CurrentDate) = DatePart(Month, @EndDate) And DatePart(Year, @CurrentDate) = DatePart(Year, @EndDate)                               
      Begin
        Set @ActualStartDate = DateFromParts(@CurrentYear, @CurrentMonth, 1) --1. day in month 
		Set @ActualEndDate = @EndDate
      End Else
	  If DatePart(Month, @CurrentDate) < DatePart(Month, @EndDate) And DatePart(Year, @CurrentDate) <= DatePart(Year, @EndDate)
	  Begin
	    Set @ActualStartDate = DateFromParts(@CurrentYear, @CurrentMonth, 1) --1. day in month
	    Set @ActualEndDate = EOMonth(@CurrentDate)
      End Else
	  Begin
	    RETURN 0
	  End
	End
  End

  If @TypeGroupingValue = 4 --Year
  Begin
    If @Type = 'BOOK' 
	Begin
      If DatePart(Year, @CurrentDate) = DatePart(Year, @StartDate)
	  Begin
        Set @ActualStartDate = @StartDate
        Set @ActualEndDate = DateFromParts(@CurrentYear, 12, 31)
      End Else
	  If DatePart(Year, @CurrentDate) > DatePart(Year, @StartDate)
      Begin
        Set @ActualStartDate = DateFromParts(@CurrentYear, 1, 1) 
        Set @ActualEndDate = DateFromParts(@CurrentYear, 12, 31)
      End Else
      Begin
		RETURN 0
      End
    End Else
	Begin --Actual
	  If DatePart(Year, @CurrentDate) = DatePart(Year, @EndDate)
	  Begin
        Set @ActualStartDate = DateFromParts(@CurrentYear, 1, 1)  
        Set @ActualEndDate = @EndDate
      End Else
	  If DatePart(Year, @CurrentDate) < DatePart(Year, @EndDate)
	  Begin
        Set @ActualStartDate = DateFromParts(@CurrentYear, 1, 1) 
        Set @ActualEndDate = DateFromParts(@CurrentYear, 12, 31) 
	  End Else
	  Begin
	    RETURN 0
	  End
	End
  End 

  If not @TypeGroupingValue in (1,2,3,4)
  Begin
    Set @ActualStartDate = @StartDate
    Set @ActualEndDate = @EndDate
  End

  If @StartDate > @ActualStartDate 
  Begin
    Set @ActualStartDate = @StartDate
  End

  If @EndDate < @ActualEndDate
  Begin
    Set @ActualEndDate = @EndDate
  End

  
  --Correction for time of the day  
  Declare @TimeOfDepartureForRes char(5)
  Declare @TimeOfArrivalForRes char(5)
  SELECT @TimeOfDepartureForRes = ISNULL(KeyValue, '10:00') FROM HotelSetup (nolock) WHERE SearchKey = 'TimeOfDepartureForRes'
  SELECT @TimeOfArrivalForRes = ISNULL(KeyValue, '18:00') FROM HotelSetup (nolock) WHERE SearchKey = 'TimeOfArrivalForRes'
  
  Declare @TempStart DateTime
  Declare @TempEnd DateTime
  SET @TempStart = @ActualStartDate + @TimeOfDepartureForRes --Det skulle faktisk kunne lade sig gøre!
  SET @TempEnd = @ActualEndDate + @TimeOfArrivalForRes
  
  Declare @NoOfOOODays int
  Declare @NoOfRooms int
 
  select @NoOfOOODays = SUM( DateDiff(Day, CASE WHEN ArrivalDate > @TempStart THEN Convert(dateTime, ArrivalDate) ELSE @TempStart END, 
                                           CASE WHEN DepartureDate < @TempEnd THEN Convert(DateTime, DepartureDate) ELSE @TempEnd END) + 1)
		FROM RESERVATIONROOMLINE rl  WITH (NOLOCK)
		inner Join RESERVATIONROOMLINEXTAB x (nolock) on x.LINENUMBER = rl.LINENUMBER
		inner join Rooms r on rl.RoomNumber = r.RoomNumber
		WHERE ReferenceNumber = 0
		and (@SelectHotelNumbers = '' OR r.HotelNumber IN (SELECT * FROM dbo.SplitListMax(@SelectHotelNumbers)))
		and Convert(DateTime, ArrivalDate + ' ' + ArrivalTime)  < @TempEnd
		and Convert(DateTime, DepartureDate + ' ' + DepartureTime) > @Tempstart

  SELECT @NoOfRooms = COUNT(*) From Rooms (nolock) 
  where @SelectHotelNumbers = '' OR
        HotelNumber IN (SELECT * FROM dbo.SplitListMax(@SelectHotelNumbers))

  Set @CountInPeriod = ((DateDiff(day, @ActualStartDate, @ActualEndDate) + 1) * @NoOfRooms) - IsNull(@NoOfOOODays, 0)

  --Print @ActualStartDate
  --Print @ActualEndDate 
  --Print @CountInPeriod
  --Print @NoOfOOODays
  --Print @NoOfRooms
  RETURN @CountInPeriod
END

