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

CREATE   FUNCTION [dbo].[RANDEVUGUNFTRTERAPIST] (@SPID int, @GUN datetime,@IlkCalismaSaati datetime,@SonCalismaSaati datetime)  
RETURNS table AS  
return
SELECT convert(nvarchar(5), TGUNSAATFTR.Saat ,14) as Saat, RANDEVUID, RANDDURUMID,KIMLIKID, HRAPORID,
       CASE WHEN HKIMLIKID IS NOT NULL THEN (HADI+' '+HSOYADI) 
            ELSE 
               CASE WHEN  (ACIKLAMA IS NOT NULL) THEN ACIKLAMA ELSE '---' 
           END 
       END AS HASTA,TDRKIMLIK.DRADI,TSAGLIKP.SPADI,TANIM As ODA 
FROM TSAGLIKP WITH(NOLOCK) CROSS JOIN TGUNSAATFTR WITH(NOLOCK)  LEFT JOIN 
     HRANDEVUFTR WITH(NOLOCK) ON HRANDEVUFTR.TERAPISTID=TSAGLIKP.SPID AND RTARIH =@gun AND TGUNSAATFTR.Saat>=RSAAT and TGUNSAATFTR.Saat<dateadd(n,RSURE,RSAAT) AND RANDDURUMID<7 LEFT OUTER JOIN  
     FTRYERIODA WITH(NOLOCK) ON FTRYERIODA.FTRYERIODAID = HRANDEVUFTR.FTRYERIODAID  LEFT JOIN 
     HKIMLIK WITH(NOLOCK) ON HRANDEVUFTR.KIMLIKID=HKIMLIK.HKIMLIKID LEFT OUTER JOIN 
     TDRKIMLIK WITH(NOLOCK) ON HRANDEVUFTR.DRID=TDRKIMLIK.DRID 
WHERE TSAGLIKP.SPID=@SPID and CAST(ROUND(CAST(SAAT-dbo.HaftalikIlkCalSaatTumFtrOdalar() AS FLOAT) * 24*60,0)AS INT)%60 = 0
AND TGUNSAATFTR.Saat>=@IlkCalismaSaati and TGUNSAATFTR.Saat<@SonCalismaSaati  
GO


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


