IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FNTELETIP_TETKIKLIST]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION  [dbo].[FNTELETIP_TETKIKLIST]
GO
CREATE FUNCTION [dbo].[FNTELETIP_TETKIKLIST](@KIMLIKNO VARCHAR(15),@TARIHBAS DATETIME, @TARIHSON DATETIME)
RETURNS @TETKIKLIST TABLE 
(
	KIMLIKNO NVARCHAR(50) NULL,
	ADSOYAD NVARCHAR(100) NULL,
	TETKIKID INT NULL,
	TETKIK NVARCHAR(200) NULL,
	TAKIPNO NVARCHAR(80) NULL,
	KURUMSKRSKODU NVARCHAR(80) NULL	
) 
AS
BEGIN

  INSERT INTO @TETKIKLIST (KIMLIKNO, ADSOYAD, TETKIKID, TETKIK, TAKIPNO, KURUMSKRSKODU)
  SELECT VWTETKIK.LTCKIMLIKNO, VWTETKIK.LADISOYADI, VWTETKIK.HTETKIKID, VWTETKIK.LITEMADI,
	   CASE 
	   WHEN SUBELER.LAB = 1 OR VWTETKIKHASTA.DISISTEK=1
	   THEN VWTETKIKHASTA.SNOTAKIPNO
	   ELSE		 
		  CASE 
		  WHEN VWTETKIKHASTA.MDLTEDAVITURU = 'A' 
		  THEN ISNULL((SELECT TOP 1 VWMUAYENEISL.SNOTAKIPNO FROM VWMUAYENEISL (NOLOCK) WHERE HESAPISLID=(SELECT TOP 1 VT.OPHESAPISLID FROM VWTETKIK VT WHERE VT.HTETKIKID = VWTETKIK.HTETKIKID AND VT.OPHESAPISLID IS NOT NULL ORDER BY VT.OPHESAPISLID DESC) ORDER BY HESAPISLID DESC),'')
		  ELSE ISNULL((SELECT TOP 1 VWYATIS.SNOTAKIPNO FROM VWYATIS (NOLOCK) WHERE HESAPISLID=(SELECT TOP 1 VT.OPHESAPISLID FROM VWTETKIK VT WHERE VT.HTETKIKID = VWTETKIK.HTETKIKID AND VT.OPHESAPISLID IS NOT NULL ORDER BY VT.OPHESAPISLID DESC) ORDER BY HESAPISLID DESC),'')
		  END
	   END AS TAKIPNO,
	   ISNULL((SELECT TOP 1 VALUESTR FROM [CONFIG] WHERE KEYNAME = CONVERT(VARCHAR,SUBELER.SUBEID)+' numaralı şubenin Sağlık.NET 2 kullanıcı adı'),'') AS KURUMSKRSKODU
  FROM VWTETKIK 
  JOIN VWTETKIKHASTA ON VWTETKIK.HTETKIKKARTID = VWTETKIKHASTA.HTETKIKKARTID
  JOIN SUBELER ON VWTETKIKHASTA.SUBEID =  SUBELER.SUBEID
  WHERE (@TARIHBAS IS NULL OR VWTETKIK.GIRISTARIH>@TARIHBAS) AND (@TARIHSON IS NULL OR  VWTETKIK.GIRISTARIH<@TARIHSON) AND (@KIMLIKNO ='' OR @KIMLIKNO IS NULL OR VWTETKIK.LTCKIMLIKNO=@KIMLIKNO) AND NOT(@KIMLIKNO IS NULL AND @TARIHBAS IS NULL AND @TARIHSON IS NULL)
  RETURN 
END


