SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go

--select getdate()
--SELECT * FROM MEDWEB_DRRANDEVUGUN(1,1,'20210105 14:00:00')
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MEDWEB_DRRANDEVUGUN]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.MEDWEB_DRRANDEVUGUN
go

CREATE FUNCTION dbo.MEDWEB_DRRANDEVUGUN  
(
    @SUBEID int,
    @DRID int,
    @TARIH smalldatetime
)
RETURNS @retRANDSAAT TABLE 
(
	SAAT smalldatetime,
	EK bit
) 
AS
BEGIN
	DECLARE 
	  @RBASSAAT smalldatetime,
	  @RBITISSAAT smalldatetime,
	  @MAXRANDEVUGUNSAYI int,
	  @RANDEVUSONTARIH datetime,
	  @BUGUN smalldatetime=GETDATE(),

	@AYNIGUN BIT

    IF(CAST(@TARIH AS DATE) = CAST(@BUGUN AS DATE))
	BEGIN
	   SET @AYNIGUN=1
	    SELECT @RBASSAAT = RANDBASSAAT, @RBITISSAAT = RANDBITISSAAT FROM SUBELER WHERE SUBEID = @SUBEID
	    IF @TARIH = CAST(@RANDEVUSONTARIH AS DATE) AND CAST(@RANDEVUSONTARIH AS TIME) < CAST(@RBITISSAAT AS TIME) 
	      SET @RBITISSAAT = CAST(CAST(@RANDEVUSONTARIH AS TIME) AS DATETIME)
        --PRINT 'RBASSAT: ' + cast(@RBASSAAT as nvarchar(50)) + ' RBITISSAAT: ' + cast(@RBITISSAAT as nvarchar(50))
        INSERT @retRANDSAAT (SAAT)
	    SELECT SAAT
	    FROM dbo.RANDEVUGUN (@DRID, @TARIH, default)
	    WHERE RANDEVUID IS NULL AND HASTA IS NULL AND IZINLI = 0 AND 
	          CAST(SAAT AS datetime) >= @RBASSAAT AND 
		      CAST(SAAT AS datetime) < @RBITISSAAT
			  AND CAST(SAAT AS time) > CAST(GETDATE() AS time)
		RETURN
	   
	  
	END 
	ELSE IF(CAST(@TARIH AS DATE) > CAST(@BUGUN AS DATE))
	   BEGIN
	   SELECT @AYNIGUN = 0
	END
	ELSE
	   BEGIN
	   SELECT @AYNIGUN = 0
	   RETURN
	END
	  
	SELECT @MAXRANDEVUGUNSAYI = WEBMAXRANDEVUGUNSAYI, @RANDEVUSONTARIH = RANDEVUSONTARIH FROM TDRKIMLIK WHERE DRID = @DRID
	
	IF (@MAXRANDEVUGUNSAYI IS NOT NULL AND @TARIH > DATEADD(DAY,ISNULL(@MAXRANDEVUGUNSAYI,0),dbo.TARIHAL(GETDATE()))) OR
	   (@RANDEVUSONTARIH IS NOT NULL AND @TARIH > @RANDEVUSONTARIH)
	    RETURN
	ELSE
	BEGIN  
	    SELECT @RBASSAAT = RANDBASSAAT, @RBITISSAAT = RANDBITISSAAT FROM SUBELER WHERE SUBEID = @SUBEID
	    IF @TARIH = CAST(@RANDEVUSONTARIH AS DATE) AND CAST(@RANDEVUSONTARIH AS TIME) < CAST(@RBITISSAAT AS TIME) 
	      SET @RBITISSAAT = CAST(CAST(@RANDEVUSONTARIH AS TIME) AS DATETIME)
        --PRINT 'RBASSAT: ' + cast(@RBASSAAT as nvarchar(50)) + ' RBITISSAAT: ' + cast(@RBITISSAAT as nvarchar(50))
        INSERT @retRANDSAAT (SAAT)
	    SELECT SAAT
	    FROM dbo.RANDEVUGUN (@DRID, @TARIH, default)
	    WHERE RANDEVUID IS NULL AND HASTA IS NULL AND IZINLI = 0 AND 
	          CAST(SAAT AS datetime) >= @RBASSAAT AND 
		      CAST(SAAT AS datetime) < @RBITISSAAT 	OR
			  (CAST(@TARIH AS DATE) = CAST(@BUGUN AS DATE) OR CAST(SAAT AS time) > CAST(@TARIH AS time))
			  --CAST(SAAT AS time) > CAST(@TARIH AS time)
			  
    END
    RETURN
END
go

IF EXISTS (SELECT [name] FROM [sys].[database_principals] WHERE [type] = N'S' AND [name] = N'mediwebuser')
BEGIN
	BEGIN TRY
		GRANT SELECT ON [dbo].[MEDWEB_DRRANDEVUGUN] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO
