IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[RANDEVUGUNFTRHEPSI]') and xtype in (N'FN', N'IF', N'TF'))
	DROP FUNCTION [dbo].[RANDEVUGUNFTRHEPSI]
GO

CREATE FUNCTION [dbo].[RANDEVUGUNFTRHEPSI] (@gun datetime)  
RETURNS table AS  
return
SELECT FTRYERIODA.TANIM AS YERI,convert(nvarchar(5), TGUNSAATFTR.Saat ,14) as Saat,RANDEVUID, RANDDURUMID,KIMLIKID, 
       CASE WHEN HKIMLIKID IS NOT NULL THEN (HADI+' '+HSOYADI)
            ELSE 
               CASE WHEN  (ACIKLAMA IS NOT NULL) THEN ACIKLAMA 
		    WHEN ((TGUNSAATFTR.Saat>=SBASSAAT and TGUNSAATFTR.Saat<SBITSAAT) OR (TGUNSAATFTR.Saat>=OSBASSAAT and TGUNSAATFTR.Saat<OSBITSAAT)) AND (ACIKLAMA IS NULL) THEN NULL 
                ELSE '---' 
           END 
       END AS HASTA,RBTARIH,TDRKIMLIK.DRID,TDRKIMLIK.DRADI,TSAGLIKP.SPID AS TERAPISTID,TSAGLIKP.SPADI AS TERAPISTADI
FROM TGUNSAATFTR WITH(NOLOCK) CROSS JOIN 
     FTRYERIODA WITH(NOLOCK) INNER JOIN 
     FTRYERIODACALSAAT WITH(NOLOCK) ON FTRYERIODACALSAAT.FTRYERIODAID=FTRYERIODA.FTRYERIODAID and FTRYERIODACALSAAT.BASTARIH<=@gun and FTRYERIODACALSAAT.BITTARIH>=@gun  AND HAFTAGUNU=DATEPART(dw,@gun)  AND CAST(ROUND(CAST(SAAT-SBASSAAT AS FLOAT) * 24*60,0)AS INT)%ISNULL(FTRYERIODA.RANDEVUDAKIKA,5) = 0 LEFT OUTER JOIN
     HRANDEVUFTR WITH(NOLOCK) ON HRANDEVUFTR.FTRYERIODAID = FTRYERIODA.FTRYERIODAID AND RTARIH =@gun AND TGUNSAATFTR.Saat>=RSAAT and TGUNSAATFTR.Saat<dateadd(n,RSURE,RSAAT)   AND RANDDURUMID<7 LEFT OUTER JOIN 
     HKIMLIK WITH(NOLOCK) ON HRANDEVUFTR.KIMLIKID=HKIMLIK.HKIMLIKID LEFT OUTER JOIN 
     TDRKIMLIK WITH(NOLOCK) ON HRANDEVUFTR.DRID=TDRKIMLIK.DRID LEFT OUTER JOIN 
     TSAGLIKP WITH(NOLOCK) ON HRANDEVUFTR.TERAPISTID=TSAGLIKP.SPID
WHERE ((TGUNSAATFTR.Saat>=SBASSAAT and TGUNSAATFTR.Saat<OSBITSAAT) OR RANDEVUID IS NOT NULL)

GO

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


