CREATE FUNCTION [dbo].[MCC_IsRoom_OOO_OFS] (@DBRooNo INT) RETURNS INT  AS 
BEGIN

--SET NOCOUNT ON
/*
  Created by Carsten Cremer October 2022 
  Purpose:
   Return YES OR NO (1/0) Depending on whether the room currently is out of order or not
   Used to colour the tasks in Piccoline.
*/



DECLARE @Result INT = -1;
DECLARE @cNowDate CHAR(8);
DECLARE @cNowTime CHAR(5);
SET		@cNowDate = CONVERT(CHAR(8), GETDATE(), 112);
SET		@cNowTime = REPLACE(CONVERT(CHAR(5), CONVERT(TIME, GETDATE() )), ':', '');

SET @Result = ISNULL(( 
				SELECT DISTINCT RoomNumber
				FROM    RESERVATIONROOMLINE WITH (NOLOCK)
				WHERE   RoomNumber = @DBRooNo 
				AND
				(BookedTypeNumber IS NULL)
					AND(
 					    ( ArrivalDate < @cNowDate) 
 					    OR  ( ArrivalDate = @cNowDate) AND (REPLACE(ArrivalTime, ':', '') <= @cNowTime)
					)
					AND (
						(@cNowDate < DepartureDate )  
						OR (@cNowDate= DepartureDate ) AND (@cNowTime <= REPLACE(DepartureTime, ':', ''))
				)
	),0)

IF @Result > 0 
   SET @Result = 1;

RETURN @Result;

END



