IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MEDWEB_GECMISTETKIKKARTLAR]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[MEDWEB_GECMISTETKIKKARTLAR]
GO
CREATE FUNCTION [dbo].[MEDWEB_GECMISTETKIKKARTLAR](@HKIMLIKID int,@KURUMID int,@SUBEID int) 
RETURNS @retGECMISTETKIKKARTLAR TABLE 
(   UYGBOLUMADI nvarchar(100),
	BOLUMADI nvarchar(100),
	DRADI nvarchar(50),
	GIRISTARIH datetime,
	HTETKIKKARTID int,
    HADISOYADI nvarchar(100),
    HKIMLIKID int
)
AS
BEGIN
    IF @HKIMLIKID IS NOT NULL
		INSERT INTO @retGECMISTETKIKKARTLAR
		SELECT LUYGBOLUM AS UYGBOLUMADI, LBOLUM AS BOLUMADI,  LDOKTOR AS DRADI, GIRISTARIH, HTETKIKKARTID, LADISOYADI AS HADISOYADI, HKIMLIKID 
		FROM VWTETKIKHASTA WHERE HKIMLIKID  = @HKIMLIKID AND SUBEID = @SUBEID AND
			VWTETKIKHASTA.HDOSYAID NOT IN (
												SELECT HYATIS.HDOSYAID FROM HYATIS JOIN HESAPISL ON HESAPISL.HESAPISLID = HYATIS.HESAPISLID   
												WHERE HESAPISL.IPTAL = 0 AND HESAPISL.ONAYLI = 1 AND HESAPISL.TEKLIF = 0   
													AND HYATIS.HDOSYAID= VWTETKIKHASTA.HDOSYAID AND HYYATISTIPI=2   
													AND EXISTS (SELECT CONFIGID FROM CONFIG WHERE SECTIONSTR='Tetkik' AND KEYNAME='Webde yogun bakim tetkikleri gorunmesin' AND VALUESTR='1' )
											)  
	    
		   --AND EXISTS (SELECT TOP 1 HTETKIKID FROM VWTETKIK WHERE VWTETKIK.HTETKIKKARTID = VWTETKIKHASTA.HTETKIKKARTID AND VWTETKIK.DURUM = 2 AND WEBGORUNSUN = 1 AND HDYATIYOR=0 )
	ELSE
		INSERT INTO @retGECMISTETKIKKARTLAR
		SELECT LUYGBOLUM AS UYGBOLUMADI, LBOLUM AS BOLUMADI,  LDOKTOR AS DRADI, GIRISTARIH, HTETKIKKARTID, LADISOYADI AS HADISOYADI, HKIMLIKID 
		FROM VWTETKIKHASTA WHERE HKCARIID  = @KURUMID AND SUBEID = @SUBEID 
		AND EXISTS (SELECT TOP 1 HTETKIKID FROM VWTETKIK WHERE VWTETKIK.HTETKIKKARTID = VWTETKIKHASTA.HTETKIKKARTID AND VWTETKIK.DURUM = 2 AND WEBGORUNSUN = 1 AND HDYATIYOR=0 )		
    RETURN
END
GO

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

