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

CREATE FUNCTION [dbo].[RANDEVUGUN] (@dr int, @gun datetime, @AmeliyatRandevuGoster BIT=1)  
RETURNS table AS  
return
SELECT convert(nvarchar(5), TGUNSAAT.Saat ,14) as Saat, RANDEVUID, HMUAYENEISLID AS HMUAYENEISLID, RNEDENID,RANDDURUMID , 
       CASE WHEN RANDEVUID IS NOT NULL THEN ADISOYADI 
            ELSE 
               CASE WHEN  (ACIKLAMA IS NOT NULL) THEN '---' 
		    WHEN ((TGUNSAAT.Saat>=SBASSAAT and TGUNSAAT.Saat<SBITSAAT) OR (TGUNSAAT.Saat>=OSBASSAAT and TGUNSAAT.Saat<OSBITSAAT)) AND (ACIKLAMA IS NULL) THEN NULL 
                ELSE '---' 
           END 
       END AS HASTA, RBTARIH, CAST(CASE WHEN TDRIZINLERI.BASTARIHI IS NULL THEN 0 ELSE 1 END AS BIT) AS IZINLI, WEB ,RACIKLAMA
FROM TGUNSAAT WITH(NOLOCK) INNER JOIN 
     TDRKIMLIK WITH(NOLOCK) ON TDRKIMLIK.DRID = @dr INNER JOIN 
     TDRCALSAAT WITH(NOLOCK) ON TDRCALSAAT.DRID=@dr and HAFTAGUNU=DATEPART(dw,@gun)  AND CAST(ROUND(CASE WHEN SAAT < OSBASSAAT THEN CAST(SAAT-SBASSAAT AS FLOAT) ELSE CAST(SAAT - OSBASSAAT AS FLOAT) END * 24 * 60, 0)AS INT) % ISNULL(RANDEVUDAKIKA,5) = 0 LEFT OUTER JOIN
     HRANDEVU WITH(NOLOCK) ON (@AmeliyatRandevuGoster = 1 OR HRANDEVU.SALONID IS NULL) AND HRANDEVU.DRID = @dr AND RTARIH =@gun AND TGUNSAAT.Saat>=RSAAT and TGUNSAAT.Saat<dateadd(n,RSURE,RSAAT) AND RANDDURUMID NOT IN (5, 6) LEFT OUTER JOIN
     TDRIZINLERI WITH(NOLOCK) ON TDRIZINLERI.DRID=@dr and (TDRIZINLERI.BASTARIHI<=@gun) and (@gun<TDRIZINLERI.BITISTARIHI)
WHERE ((TGUNSAAT.Saat>=SBASSAAT and TGUNSAAT.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].[RANDEVUGUN] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO




