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

CREATE FUNCTION [dbo].[FNMED_DOKTOR_RANDEVULAR] (
	@DRID INT,
	@OFFSET INT,
	@ROWNUM INT,
	@BASTARIH DATETIME,
	@BITTARIH DATETIME,
	@ADISOYADI NVARCHAR(50),
	@HTCKIMLIKNO NVARCHAR(50)
)
RETURNS @Results TABLE (
	RANDEVUID INT,
	HKIMLIKID INT,
	HADISOYADI NVARCHAR(50),
	RTARIH DATETIME,
	RSAAT DATETIME,
	RSURE INT,
	HTCKIMLIKNO NVARCHAR(50)
)
AS
BEGIN
	INSERT INTO @Results (RANDEVUID, HKIMLIKID, HADISOYADI, RTARIH, RSAAT, RSURE, HTCKIMLIKNO)
	SELECT
		RANDEVUID,
		HKIMLIKID,
		HADISOYADI,
		RTARIH,
		RSAAT,
		RSURE,
		HTCKIMLIKNO
	FROM (
		SELECT
			R.RANDEVUID,
			ISNULL(H.HKIMLIKID, 0) AS HKIMLIKID,
			R.ADISOYADI AS HADISOYADI,
			R.RTARIH,
			R.RSAAT,
			R.RSURE,
			ISNULL(H.HTCKIMLIKNO, '') AS HTCKIMLIKNO,
			ROW_NUMBER() OVER (ORDER BY R.RTARIH ASC, R.RSAAT ASC) AS RowNum
		FROM HRANDEVU R WITH (NOLOCK)
		LEFT JOIN HKIMLIK H WITH (NOLOCK) ON H.HKIMLIKID = R.KIMLIKID
		WHERE R.DRID = ISNULL(@DRID, 0)
			AND (
				@BASTARIH IS NULL
				OR R.RTARIH >= @BASTARIH
			)
			AND (
				@BITTARIH IS NULL
				OR R.RTARIH < DATEADD(DAY, 1, @BITTARIH)
			)
			AND (
				ISNULL(@ADISOYADI, '') = ''
				OR R.RADI + ' ' + R.RSOYAD LIKE @ADISOYADI + '%'
			)
			AND (
				ISNULL(@HTCKIMLIKNO, '') = ''
				OR R.RTCKIMLIKNO LIKE @HTCKIMLIKNO + '%'
			)
	) AS SubQuery
	WHERE RowNum > @OFFSET AND RowNum <= @OFFSET + @ROWNUM

	RETURN
END
GO

IF EXISTS (
	SELECT [name]
	FROM [sys].[database_principals]
	WHERE [type] = N'S'
		AND [name] = N'mediwebuser'
	)
BEGIN
	BEGIN TRY
		EXEC sp_addrolemember 'db_datareader', 'mediwebuser'
		EXEC sp_addrolemember 'db_datawriter', 'mediwebuser'
	END TRY

	BEGIN CATCH
	END CATCH
END
GO
