IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SPWEB_KURUMTETKIKKART]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE SPWEB_KURUMTETKIKKART
GO
--[dbo].[SPWEB_KURUMTETKIKKART] 'wl49mhf7q','','','',10,1
CREATE PROCEDURE [dbo].[SPWEB_KURUMTETKIKKART]
@cariid nvarchar(30), @tcno nvarchar(20), @isim nvarchar(50), @soyisim nvarchar(50), @kayitsayisi int, @sayfa int
AS
BEGIN
	DECLARE @CARINO INT = dbo.FN_BASIT_ID_SIFRE_AC(@cariid)
	EXEC SPWEB_LOGLA 'kurumtetkikler',@CARINO,'Tetkik listesini çekti',''

	DECLARE @BASLANGICTARIHI DATETIME = CONVERT(DATETIME,CONVERT(DATE,DATEADD(year,-3,GETDATE())))
	IF @tcno<>'' OR @isim<>'' OR @soyisim<>''
		SET @BASLANGICTARIHI = CONVERT(DATETIME,CONVERT(DATE,DATEADD(MONTH,-90,GETDATE())))

	
	;WITH tumSatirlar AS(
			SELECT	HKIMLIK.HADI+' '+HKIMLIK.HSOYADI as hastaismi,
					HTETKIKKART.HTETKIKKARTID as kartid, 
					HTETKIKKART.GIRISTARIH AS tetkiktarih,
					SUBELER.SUBEADI as sube,
					ROW_NUMBER() OVER(ORDER BY HTETKIKKART.HTETKIKKARTID DESC) id					
			FROM	HTETKIKKART WITH(NOLOCK)
			JOIN	HDOSYA WITH(NOLOCK) ON HDOSYA.HDOSYAID = HTETKIKKART.HDOSYAID
			JOIN	HKIMLIK WITH(NOLOCK) ON HKIMLIK.HKIMLIKID = HDOSYA.HKIMLIKID
			JOIN	HTETKIK ON HTETKIK.HTETKIKKARTID = HTETKIKKART.HTETKIKKARTID
			JOIN	HESAPISL WITH(NOLOCK) ON HESAPISL.HESAPISLID = HTETKIK.HESAPISLID
			JOIN	TITEM WITH(NOLOCK) ON TITEM.ITEMID = HESAPISL.ITEMID
			JOIN	TTETKIK WITH(NOLOCK) ON TTETKIK.ITEMID = TITEM.ITEMID   
			JOIN	CARI WITH(NOLOCK) ON dbo.HDOSYA.HKCARIID = dbo.CARI.CARIID
			JOIN	SUBELER WITH(NOLOCK) ON SUBELER.SUBEID=HDOSYA.SUBEID
			
			WHERE	CARI.CARIID=@CARINO
				AND HESAPISL.IPTAL=0 
				AND HESAPISL.ONAYLI=1 
				AND HESAPISL.TEKLIF=0 
				AND HTETKIK.DURUM=2 
				AND TTETKIK.WEBGORUNSUN=1
				AND HTETKIKKART.GIRISTARIH>@BASLANGICTARIHI
				AND (@tcno='' OR HKIMLIK.HTCKIMLIKNO=@tcno)
				AND (@isim='' OR HKIMLIK.HADI LIKE @isim+'%')
				AND (@soyisim='' OR HKIMLIK.HSOYADI LIKE @soyisim+'%')
			GROUP BY HKIMLIK.HADI,HKIMLIK.HSOYADI,HTETKIKKART.HTETKIKKARTID,HTETKIKKART.GIRISTARIH,SUBELER.SUBEADI
	)

	select '[ ' + STUFF((
        SELECT ', { "hastaismi": "' + hastaismi + '", "kartid": ' + cast(kartid as varchar) + ', "sube": "' + sube + '", "satirsayi": ' + cast((select count(*) from tumSatirlar WITH(NOLOCK)) as varchar) + ', "tetkiktarih": "' + RIGHT(('00'+cast(datepart(day,tetkiktarih) as varchar)),2)+'/'+RIGHT(('00'+cast(datepart(month,tetkiktarih) as varchar)),2)+'/'+cast(datepart(year,tetkiktarih) as varchar) + '"'

		+	(select ', "tetkikler": [ ' + STUFF((

		
					SELECT ', { "tetkikid": ' + cast(HTETKIKID as varchar) + ',"tetkik": "' + ITEMADI + '"' + ', "raporvar": ' + cast((CASE WHEN HTRAPORU IS NULL THEN 0 ELSE 1 END) as varchar) +' }' 
					FROM	HTETKIK WITH(NOLOCK)
					JOIN	HESAPISL WITH(NOLOCK) ON HESAPISL.HESAPISLID = HTETKIK.HESAPISLID
					JOIN	TITEM WITH(NOLOCK) ON TITEM.ITEMID = HESAPISL.ITEMID
					JOIN	TTETKIK  WITH(NOLOCK) ON TTETKIK.ITEMID = TITEM.ITEMID   
					WHERE	HESAPISL.IPTAL=0 AND HESAPISL.ONAYLI=1 AND HESAPISL.TEKLIF=0 AND
							HTETKIK.HTETKIKKARTID=tumSatirlar.kartid AND HTETKIK.DURUM=2 AND TTETKIK.WEBGORUNSUN=1 AND
							HESAPISL.HDOSYAID NOT IN (	SELECT	HYATIS.HDOSYAID 
												FROM	HYATIS WITH(NOLOCK) 
												JOIN	HESAPISL WITH(NOLOCK) ON HESAPISL.HESAPISLID = HYATIS.HESAPISLID 
												WHERE	HESAPISL.IPTAL = 0 AND HESAPISL.ONAYLI = 1 AND HESAPISL.TEKLIF = 0 AND
														HYATIS.HDOSYAID= HESAPISL.HDOSYAID AND HYYATISTIPI=2 AND
														EXISTS (	SELECT	CONFIGID 
																	FROM	CONFIG WITH(NOLOCK) 
																	WHERE SECTIONSTR='Tetkik' AND KEYNAME='Webde yogun bakim tetkikleri gorunmesin' AND VALUESTR='1' 
																)
											)
					ORDER BY HTETKIK.HTETKIKID
					FOR XML PATH(''), TYPE
		

			).value('.', 'varchar(max)'), 1, 1, '') + ' ]')
		+' }'

		FROM tumSatirlar WITH(NOLOCK)
		WHERE   id  BETWEEN ((@sayfa - 1) * @kayitsayisi + 1) AND (@sayfa * @kayitsayisi)
		ORDER BY kartid DESC
		FOR XML PATH(''), TYPE
    ).value('.', 'varchar(max)'), 1, 1, '') + ' ]' as cikti
END
GO

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


