CREATE FUNCTION [dbo].[GetMISRoomCountInPeriod] (@StartDate DateTime, @EndDate DateTime, @SelectHotelNumbers varchar(50), @TypeGroupingValue int, @CurrentDate DateTime, @Type Char(6)) --ACTUAL or BOOK
RETURNS INT
AS
/*
  SG 2022-05-16: Return 0 when negative datespan. 
  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 DATEDIFF(day, @StartDate, @EndDate) < 0
    RETURN 0
   
  IF @TypeGroupingValue = 1 --Day
  BEGIN
    IF @Type = 'BOOK' 
    BEGIN
      If @CurrentDate >= @StartDate
      Begin
        Set @ActualStartDate = @CurrentDate
        Set @ActualEndDate = @CurrentDate
      End 
      Else
        RETURN 0
    End 
    Else
    Begin --ACTUAL
      If @CurrentDate <= @EndDate
      Begin
        Set @ActualStartDate = @CurrentDate
        Set @ActualEndDate = @CurrentDate
      End 
      Else
        RETURN 0
    End
  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
        RETURN 0
    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
        RETURN 0
    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
        RETURN 0
    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
        RETURN 0
    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
        RETURN 0
    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
        RETURN 0
    End
  End 

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

  If @StartDate > @ActualStartDate 
    Set @ActualStartDate = @StartDate

  If @EndDate < @ActualEndDate
    Set @ActualEndDate = @EndDate
  
  --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)

  RETURN @CountInPeriod
END
