IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SPWEB_TETKIKKART]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE SPWEB_TETKIKKART
GO
--   SPWEB_TETKIKKART 220709,10,1
CREATE PROCEDURE [dbo].[SPWEB_TETKIKKART]
@kisino NVARCHAR(30), @kayitsayisi int, @sayfa int
AS
BEGIN
	DECLARE @HKIMLIKID INT = dbo.FN_BASIT_ID_SIFRE_AC(@kisino)
	EXEC SPWEB_LOGLA 'tetkikler',@HKIMLIKID,'Tetkik listesini çekti',''
	
	;WITH tumSatirlar AS(
			SELECT	HTETKIKKARTID as kartid, 
					GIRISTARIH AS tetkiktarih, 
					ROW_NUMBER() OVER(ORDER BY HTETKIKKARTID DESC) id, 
					(SELECT SUBEADI FROM SUBELER WITH(NOLOCK) WHERE SUBEID=(select SUBEID from HDOSYA WITH(NOLOCK) where HDOSYA.HDOSYAID=HTETKIKKART.HDOSYAID)) as sube
			FROM	HTETKIKKART WITH(NOLOCK)
			WHERE	HKIMLIKID=@HKIMLIKID 
					AND EXISTS (	SELECT	HTETKIKID 
									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=HTETKIKKART.HTETKIKKARTID 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' 
																				)
															)
								)
	)

	select '[ ' + STUFF((
        SELECT ', { "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)
		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_TETKIKKART] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO


