
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MEDWEB_AKTIFDOKTORLAR_HASTAYAGORE]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].MEDWEB_AKTIFDOKTORLAR_HASTAYAGORE
go
CREATE FUNCTION [dbo].[MEDWEB_AKTIFDOKTORLAR_HASTAYAGORE](@SUBEID int, @BOLUMID int, @DRID int, @HTCKIMLIKNO VARCHAR(15), @RANDTATIH smalldatetime ) 
RETURNS @retSUBEDOKTORLAR TABLE 
(
	DRID int,
	SUBEID int,
	BOLUMID int,
	BOLUM nvarchar(100), 
	DRADI nvarchar(100), 
	MAXRANDEVUGUNSAYI int,
	ALTYASSINIRI int,
	USTYASSINIRI int,
	KONTROLLLERIRANDEVUSUZALIR int,
	WEBMAXRANDEVUGUNSAYI int,
	HASTAYASI INT,
	KONTROLMUAYENESIVAR INT,
	MAXRANDEVUGUNSUREASILDI INT
)
AS
BEGIN
	INSERT INTO @retSUBEDOKTORLAR
	   SELECT  ISNULL(DRID,0) AS DRID,
			 ISNULL(SUBEID,0) AS SUBEID,
			 ISNULL(BOLUMID,0) AS BOLUMID,
			 ISNULL((SELECT TOP 1 BOLUMADI FROM TBOLUM WHERE TBOLUM.BOLUMID=TDRKIMLIK.BOLUMID),'') AS BOLUM,			 
			(CASE WHEN ISNULL(DRUNVAN, '')='' THEN DRADI ELSE DRUNVAN+' '+DRADI END) AS DRADI,
			ISNULL(MAXRANDEVUGUNSAYI,0) AS MAXRANDEVUGUNSAYI,
			ISNULL(ALTYASSINIRI,199) AS ALTYASSINIRI,
			ISNULL(USTYASSINIRI,0) AS USTYASSINIRI,
			(CASE WHEN KONTROLLLERIRANDEVUSUZALIR=1 THEN 1 ELSE 0 END) AS  KONTROLLLERIRANDEVUSUZALIR, 
			ISNULL(WEBMAXRANDEVUGUNSAYI,0) AS WEBMAXRANDEVUGUNSAYI,
			CASE WHEN @HTCKIMLIKNO<>'' AND (SELECT TOP 1 HDTARIHI FROM HKIMLIK WITH(NOLOCK) WHERE HTCKIMLIKNO=@HTCKIMLIKNO) IS NOT NULL THEN 
				DATEDIFF(YEAR,(SELECT TOP 1 HDTARIHI FROM HKIMLIK WITH(NOLOCK) WHERE HTCKIMLIKNO=@HTCKIMLIKNO),GETDATE()) ELSE 999 END AS HASTAYASI,
			CASE WHEN @HTCKIMLIKNO<>'' AND @RANDTATIH<>'2000-01-01' THEN
				CASE WHEN (SELECT COUNT(HMUAYENEID) FROM VWMUAYENE WITH(NOLOCK) WHERE HTCKIMLIKNO=@HTCKIMLIKNO AND HMDRID=TDRKIMLIK.DRID AND HMTARIH>DATEADD(DAY,-12,GETDATE()) AND DATEADD(DAY,10,HMTARIH)>@RANDTATIH)=0 
				    THEN 0 ELSE 1 END
			ELSE 0 END AS KONTROLMUAYENESIVAR,
			CASE WHEN WEBMAXRANDEVUGUNSAYI>0 AND @RANDTATIH<>'2000-01-01' AND DATEDIFF(DAY,GETDATE(),@RANDTATIH)>WEBMAXRANDEVUGUNSAYI THEN
				1 ELSE 0 END AS MAXRANDEVUGUNSUREASILDI
	   FROM TDRKIMLIK WITH(NOLOCK)
	   WHERE (SUBEID = @SUBEID OR @SUBEID=0)
		    AND (BOLUMID = @BOLUMID OR @BOLUMID=0)
		    AND (DRID = @DRID OR @DRID=0) 
		    AND STATUS = 'A'
		    AND WEBRANDEVUVERILIR = 1;

    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_AKTIFDOKTORLAR_HASTAYAGORE] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO
