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

CREATE FUNCTION [dbo].[RANDEVUGUNAMELIYAT] (@salonid int, @gun datetime)
RETURNS table AS
return
SELECT convert(nvarchar(5), TGUNSAAT.Saat ,14) as Saat, RANDEVUID, RANDDURUMID ,RNEDENID,
	CASE WHEN RANDEVUID IS NOT NULL THEN ADISOYADI
		ELSE
	CASE WHEN ((TGUNSAAT.Saat>=SBASSAAT and TGUNSAAT.Saat<SBITSAAT) OR
	 (TGUNSAAT.Saat>=OSBASSAAT and TGUNSAAT.Saat<OSBITSAAT)) THEN NULL
		ELSE '---'
	END
END AS HASTA, 0 AS IZINLI,RBTARIH,TASALON.SALONADI,TASALON.SALONID,DRADI,RACIKLAMA
FROM TGUNSAAT WITH(NOLOCK) INNER JOIN
     TASALON WITH(NOLOCK) ON TASALON.SALONID = @salonid INNER JOIN
     TASALONCALSAAT WITH(NOLOCK) ON TASALONCALSAAT.SALONID=@salonid and HAFTAGUNU=DATEPART(dw,@gun) AND 
     CAST(ROUND(CAST(SAAT-SBASSAAT AS FLOAT) * 24*60,0)AS INT)%(CASE WHEN (RANDEVUDAKIKA IS NULL OR RANDEVUDAKIKA=0) THEN 5 ELSE RANDEVUDAKIKA END )=0 LEFT OUTER JOIN
     HRANDEVU WITH(NOLOCK) ON HRANDEVU.SALONID = @salonid AND RTARIH =@gun      AND TGUNSAAT.Saat>=RSAAT and  TGUNSAAT.Saat<dateadd(n,RSURE,RSAAT)  LEFT OUTER JOIN
     TDRKIMLIK WITH(NOLOCK) ON HRANDEVU.DRID=TDRKIMLIK.DRID
WHERE ((TGUNSAAT.Saat>=SBASSAAT and TGUNSAAT.Saat<OSBITSAAT) 
OR RANDEVUID IS NOT NULL) AND RANDDURUMID NOT IN (5, 6)

GO

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


