IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SPUSS_103_MUAYENE_KAYIT]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE SPUSS_103_MUAYENE_KAYIT
GO

CREATE PROCEDURE [dbo].[SPUSS_103_MUAYENE_KAYIT] 
	@HDOSYAID INT=0,
	@HMUAYENEID INT = 0,
	@TUMUNUTEKRAR BIT = 0
AS
BEGIN
	--SPUSS_103_MUAYENE_KAYIT 360,0,1
	DECLARE @HATA NVARCHAR(2000) = ''
	DECLARE @MESAJ NVARCHAR(2000) = ''
	DECLARE @SAYIHATA BIT = 0
	DECLARE @XMLBASLIK NVARCHAR(2000) = ''
	DECLARE @SUBEID INT = 1
	DECLARE @ADET INT;
	DECLARE @UYGADET INT;

	DECLARE @GONDERIMTABLOSU TABLE 
		(
			GONDERILECEKXML XML,
			HATA NVARCHAR(2000),
			MESAJ NVARCHAR(2000),	
			XMLBASLIK NVARCHAR(3500),
			TABLOID INT,
			MINID INT,
			MAXID INT
		)

	IF OBJECT_ID('tempdb..#TMPMUAYENE') IS NOT NULL 
		DROP TABLE #TMPMUAYENE
	IF OBJECT_ID('tempdb..#TMPRECETE') IS NOT NULL 
		DROP TABLE #TMPRECETE
	IF OBJECT_ID('tempdb..#TMPRAPOR') IS NOT NULL 
		DROP TABLE TMPRAPOR
	IF OBJECT_ID('tempdb..#TMPRAPORDR') IS NOT NULL 
		DROP TABLE TMPRAPORDR
	IF OBJECT_ID('tempdb..#TMPILAC') IS NOT NULL 
		DROP TABLE #TMPILAC
	IF OBJECT_ID('tempdb..#TMPTANI') IS NOT NULL 
		DROP TABLE #TMPTANI

	IF OBJECT_ID('tempdb..#TMPNADIRHASTALIK') IS NOT NULL 
		DROP TABLE TMPNADIRHASTALIK

	SELECT	DISTINCT 
			TICD.ICDKOD AS TICD_ICDKOD,
			TICD.ICDADI AS TICD_ICDADI,
			HDOSYAICD.ICDTURUID AS TICD_ICDTURUID
	INTO #TMPTANI
	FROM HDOSYAICD
		INNER JOIN TICD ON TICD.ICDKOD = HDOSYAICD.ICDKOD
	WHERE HDOSYAICD.ICDMDLKAYDET=1 AND HDOSYAICD.HDOSYAID = @HDOSYAID 
		


	IF @HMUAYENEID = 0
	BEGIN
	
		SELECT @ADET=COUNT(*) FROM HMUAYENE 
								JOIN HYATIS ON HMUAYENE.HMUAYENEID=HYATIS.HMUAYENEID
								JOIN HESAPISL ON HESAPISL.HESAPISLID=HYATIS.HESAPISLID
								JOIN HDOSYA ON HYATIS.HDOSYAID=HDOSYA.HDOSYAID
								WHERE HDYATIYOR=1 AND HMUAYENE.HDOSYAID=@HDOSYAID
								AND HESAPISL.IPTAL = 0  AND HESAPISL.ONAYLI = 1 AND HESAPISL.TEKLIF = 0
		IF @ADET=0 
		BEGIN
			SELECT TOP 1  @HMUAYENEID=HMUAYENE.HMUAYENEID
									FROM HMUAYENE 
									JOIN HMUAYENEISL ON HMUAYENEISL.HMUAYENEID=HMUAYENE.HMUAYENEID
									JOIN HESAPISL ON HESAPISL.HESAPISLID=HMUAYENEISL.HESAPISLID
									JOIN HDOSYA ON HMUAYENE.HDOSYAID=HDOSYA.HDOSYAID
									WHERE HDYATIYOR=0 AND HMUAYENE.HDOSYAID=@HDOSYAID
									AND HESAPISL.IPTAL = 0  AND HESAPISL.ONAYLI = 1 AND HESAPISL.TEKLIF = 0
									ORDER BY HMUAYENEISL.HMUAYENEID 

		END


		IF @HMUAYENEID=0 AND @ADET=0
		BEGIN
			SELECT @UYGADET=COUNT(*) FROM HUYGISLEM 									
									JOIN HESAPISL ON HESAPISL.HESAPISLID=HUYGISLEM.HESAPISLID
									INNER JOIN TITEM ON TITEM.ITEMID=HESAPISL.ITEMID
									JOIN HDOSYA ON HUYGISLEM.HDOSYAID=HDOSYA.HDOSYAID
									WHERE  HUYGISLEM.HDOSYAID=@HDOSYAID
									AND HESAPISL.IPTAL = 0  AND HESAPISL.ONAYLI = 1 AND HESAPISL.TEKLIF = 0 
									AND ITEMKODU2 IN ('P610820','530140','530150','530580','P621045')

		   
		
		END
		

	END

	
	/*
	ELSE
	BEGIN
		SELECT TOP 1  @HMUAYENEID=HMUAYENE.HMUAYENEID FROM HMUAYENE 
								JOIN HYATIS ON HMUAYENE.HMUAYENEID=HYATIS.HMUAYENEID
								JOIN HESAPISL ON HESAPISL.HESAPISLID=HYATIS.HESAPISLID
								JOIN HDOSYA ON HYATIS.HDOSYAID=HDOSYA.HDOSYAID
								WHERE HDYATIYOR=1 AND HMUAYENE.HDOSYAID=@HDOSYAID
								AND HESAPISL.IPTAL = 0  AND HESAPISL.ONAYLI = 1 AND HESAPISL.TEKLIF = 0
								ORDER BY HMUAYENE.HMUAYENEID

	END*/

	
	SELECT DISTINCT
		HRECETEISL.HRECETEID AS HRECETEISL_HRECETEID,
		HRECETEISL.ILACBARKOD AS HRECETEISL_ILACBARKOD,
		HRECETEISL.MIKTAR AS HRECETEISL_MIKTAR,
		HRECETEISL.ILACADI AS HRECETEISL_ILACADI,
		HRECETEISL.ILACKULSEKLIID AS HRECETEISL_ILACKULSEKLIID,
		TILACKULSEKLI.ILACKULSEKLIADI AS TILACKULSEKLI_ILACKULSEKLIADI,
		HRECETEISL.KULLANIMADET AS HRECETEISL_KULLANIMADET,
		HRECETEISL.KULPERIYODU AS HRECETEISL_KULPERIYODU,
		HRECETEISL.KULPERIYODBIRIMI AS HRECETEISL_KULPERIYODBIRIMI,		
		TILAC.BARKOD AS TILAC_BARKOD
	INTO #TMPILAC
	FROM HRECETE
		JOIN HRECETEISL ON HRECETEISL.HRECETEID = HRECETE.HRECETEID
		LEFT JOIN TILAC ON TILAC.ILACID = HRECETEISL.ILACID AND TILAC.AKTIF = 1
		LEFT OUTER JOIN TILACKULSEKLI ON TILACKULSEKLI.ILACKULSEKLIID = HRECETEISL.ILACKULSEKLIID
		INNER JOIN HMUAYENE ON HMUAYENE.HMUAYENEID=HRECETE.HMUAYENEID
		INNER JOIN HMUAYENEISL ON HMUAYENEISL.HMUAYENEID=HMUAYENE.HMUAYENEID
		INNER JOIN HESAPISL ON HESAPISL.HESAPISLID=HMUAYENEISL.HESAPISLID

	WHERE HESAPISL.IPTAL=0  AND  HRECETE.HDOSYAID = @HDOSYAID 
		/*AND (
				@HMUAYENEID = 0
				OR
				HRECETE.HMUAYENEID = @HMUAYENEID
			)*/
	--SELECT * FROM #TMPILAC

	SELECT DISTINCT
		HRECETE.HMUAYENEID AS HRECETE_HMUAYENEID,
		HRECETE.HRECETEID AS HRECETE_HRECETEID,
		HRECETE.HRECTARIH AS HRECETE_HRECTARIH,
		HRECETE.RECETETURID AS HRECETE_RECETETURID,
		TDRKIMLIK.DRTCKIMLIKNO AS TDRKIMLIK_DRTCKIMLIKNO,
		CASE HRECETE.RECETETURID
			WHEN 1 THEN 'NORMAL' 
			WHEN 2 THEN 'KIRMIZI'  
			WHEN 3 THEN 'TURUNCU' 
			WHEN 4 THEN 'MOR'  
			WHEN 5 THEN 'YEŞİL' 
		END AS RECETE_TURUADI
		
	INTO #TMPRECETE
	FROM HRECETE
		INNER JOIN  TDRKIMLIK ON TDRKIMLIK.DRID = HRECETE.DRID
		INNER JOIN HMUAYENE ON HMUAYENE.HMUAYENEID=HRECETE.HMUAYENEID
		INNER JOIN HMUAYENEISL ON HMUAYENEISL.HMUAYENEID=HMUAYENE.HMUAYENEID
		INNER JOIN HESAPISL ON HESAPISL.HESAPISLID=HMUAYENEISL.HESAPISLID
	WHERE HESAPISL.IPTAL=0 AND  HRECETE.HDOSYAID = @HDOSYAID AND HRECETE.MdlEreceteNo IS NOT NULL


	--SELECT * FROM #TMPRECETE
	IF (SELECT COUNT(*) FROM #TMPILAC)=0
	BEGIN
		DELETE FROM #TMPRECETE
	END
	--SELECT * FROM #TMPRECETE


	SELECT 
		TDRKIMLIK.DRTCKIMLIKNO AS TDRKIMLIK_HEKIMKIMLIKNO
	INTO #TMPRAPORDR
	FROM HRAPOR		
		INNER JOIN HRAPORDR ON HRAPOR.HRAPORID=HRAPORDR.HRAPORID	
		LEFT OUTER JOIN HMUAYENE ON HMUAYENE.HMUAYENEID=HRAPOR.HMUAYENEID
		LEFT OUTER JOIN HDOSYA ON HMUAYENE.HDOSYAID = HDOSYA.HDOSYAID			
		LEFT OUTER JOIN  TDRKIMLIK ON HRAPORDR.DRID = TDRKIMLIK.DRID
	WHERE HDOSYA.HDOSYAID = @HDOSYAID AND HRAPOR.RAPORTAKIPNO IS NOT NULL

	IF (SELECT COUNT(*) FROM #TMPRAPORDR)=0
	BEGIN
		DELETE FROM #TMPRAPORDR
	END

	SELECT DISTINCT
		HRAPOR.RAPORTARIHI AS HRAPOR_RAPORTARIHI,
		HRAPOR.RAPORNO AS HRAPOR_RAPORNO,
		HRAPOR.RAPORTURID AS HRAPOR_RAPORTURID,
		TRAPORTUR.RAPORTURADI AS TRAPORTUR_RAPORTURADI,
		HRAPOR.BASLANGICTARIHI AS HRAPOR_BASLANGICTARIHI,
		HRAPOR.BITISTARIHI AS HRAPOR_BITISTARIHI,
		HRAPOR.RAPORTAKIPNO AS HRAPOR_RAPORTAKIPNO,
		HRAPOR.HRAPORID AS HRAPOR_HRAPORID
	INTO #TMPRAPOR
	FROM HRAPOR		
		LEFT OUTER JOIN HMUAYENE ON HMUAYENE.HMUAYENEID=HRAPOR.HMUAYENEID
		LEFT OUTER JOIN HDOSYA ON HMUAYENE.HDOSYAID = HDOSYA.HDOSYAID
		INNER JOIN TRAPORTUR ON HRAPOR.RAPORTURID = TRAPORTUR.RAPORTURID 
	WHERE HDOSYA.HDOSYAID = @HDOSYAID AND HRAPOR.RAPORTURID IN(1, 10, 11, 12, 13, 14, 15, 16, 2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,98)  AND HRAPOR.RAPORTAKIPNO IS NOT NULL

	--SELECT * FROM ##TMPRAPOR
	IF (SELECT COUNT(*) FROM #TMPRAPOR)=0
	BEGIN
		DELETE FROM #TMPRAPOR
	END

	SELECT 
		(SELECT MIN(UZMANGORUSKODU) FROM HMUAYENENADIRHASTALIKTANI HM WHERE HM.HDOSYAID=HMUAYENENADIRHASTALIKTANI.HDOSYAID ) UZMANGORUSKODU,
       (
	     SELECT CASE WHEN MIN(UZMANGORUSKODU)=1 THEN 'KESİN TANI, NADİR HASTALIK LİSTESİNDE VAR'
		            WHEN MIN(UZMANGORUSKODU)=2 THEN 'TEST SÜRECİ DEVAM EDİYOR, TANI KONULAMADI'
					WHEN MIN(UZMANGORUSKODU)=3 THEN 'KESİN TANI, NADİR HASTALIK LİSTESİNDE YOK' END 
           FROM HMUAYENENADIRHASTALIKTANI HM WHERE HM.HDOSYAID=HMUAYENENADIRHASTALIKTANI.HDOSYAID
	   )  UZMANGORUSADI,
		NADIRHASTALIKTANIID AS HMUAYENENADIRHASTALIKTANI_NADIRHASTALIKTANIID,
		NADIRHASTALIKADI AS TSKRSNADIRHASTALIKLISTESI_NADIRHASTALIKADI ,
		HDOSYAID,
		HMUAYENEID 
	INTO #TMPNADIRHASTALIK FROM HMUAYENENADIRHASTALIKTANI
		INNER JOIN TSKRSNADIRHASTALIKLISTESI NADIR ON HMUAYENENADIRHASTALIKTANI.NADIRHASTALIKTANIID = NADIR.NHSTKODU
		INNER JOIN (SELECT '1'	KODU,'KESİN TANI, NADİR HASTALIK LİSTESİNDE VAR' ADI
					UNION ALL
					SELECT '2' ,'TEST SÜRECİ DEVAM EDİYOR, TANI KONULAMADI'
					UNION ALL
					SELECT '3' ,'KESİN TANI, NADİR HASTALIK LİSTESİNDE YOK') UZM ON UZM.KODU=UZMANGORUSKODU
	WHERE HDOSYAID = @HDOSYAID

	IF (SELECT COUNT(*) FROM #TMPNADIRHASTALIK)=0
	BEGIN
		DELETE FROM #TMPNADIRHASTALIK
	END

-------- ASASKOR MUAYENEDETAY

SELECT 
      USSAMLASASKORID AS HMUAYENEDETAY_USSASASKORID,
 CASE
    WHEN USSAMLASASKORID = 1 THEN 'NORMAL SAĞLIKLI HASTA (ASA 1)'
    WHEN USSAMLASASKORID = 2 THEN 'HAFİF SİSTEMİK HASTALIĞI OLAN HASTA (ASA 2)'
    WHEN USSAMLASASKORID = 3 THEN 'CİDDİ SİSTEMİK HASTALIĞI OLAN HASTA, GÜNLÜK AKTİVİTELERİ ETKİLEMEYEN (ASA 3)'
    WHEN USSAMLASASKORID = 4 THEN 'HAYATİ TEHLİKE YARATAN CİDDİ SİSTEMİK HASTALIĞI OLAN HASTA, GÜNLÜK AKTİVİTELERİ ETKİLENEN (ASA 4)'
    WHEN USSAMLASASKORID = 5 THEN 'AMELİYATSIZ YAŞAM ÜMİDİ OLMAYAN, ÖLÜMCÜL HASTA (ASA 5)'
    WHEN USSAMLASASKORID = 6 THEN 'BEYİN ÖLÜMÜ BİLDİRİLMİŞ, ORGAN NAKLİ İÇİN BEKLETİLEN HASTA (ASA 6)'
 END HMUAYENEDETAY_USSASASKORADI, HMUAYENEDETAY.HMUAYENEID  
	INTO #TMPMUAYENEASASKOR FROM HMUAYENEDETAY
		INNER JOIN HMUAYENE ON HMUAYENEDETAY.HMUAYENEID=HMUAYENE.HMUAYENEID
	WHERE ISNULL(USSAMLASASKORID,'')<>'' AND HMUAYENE.HDOSYAID = @HDOSYAID

	IF (SELECT COUNT(*) FROM #TMPMUAYENEASASKOR)=0
	BEGIN
		DELETE FROM #TMPMUAYENEASASKOR
	END

--------
	
	
	SELECT	DISTINCT  HMUAYENE.HMUAYENEID AS HMUAYENE_HMUAYENEID,
			HDOSYA.SUBEID AS HDOSYA_SUBEID,
			SUBELER.SUBEADI AS SUBELER_SUBEADI,			
			HDOSYA.SNOTAKIPNO AS HDOSYA_SNOTAKIPNO,
			(SELECT COUNT(*) FROM #TMPTANI) AS ICD_SAYI,
			(SELECT COUNT(*) FROM #TMPRAPOR) AS RAPOR_SAYI,
			(SELECT COUNT(*) FROM #TMPRAPORDR) AS RAPOR_DR_SAYI,
			(SELECT COUNT(*) FROM #TMPRECETE) AS RECETE_SAYI,			
			(SELECT COUNT(*) FROM #TMPNADIRHASTALIK) AS NADIR_HASTALIK_SAYI,
			(SELECT COUNT(*) FROM #TMPMUAYENEASASKOR) AS ASA_SKOR_SAYI,
			--ISNULL(CONVERT(NVARCHAR(MAX),HMUAYENE.HMACIKLAMA),'') AS HMUAYENE_HMACIKLAMA,
			ISNULL(CONVERT(NVARCHAR(MAX),HMUAYENE.HMSIKAYETI),'') AS HMUAYENE_HMSIKAYETI,
			ISNULL(CONVERT(NVARCHAR(MAX),HMUAYENE.HMHKIKAYESI),'') AS HMUAYENE_HMHKIKAYESI,
			ISNULL(CONVERT(NVARCHAR(MAX),HMUAYENE.HMMUAYENE),'') AS HMUAYENE_HMMUAYENE,
			ISNULL(CONVERT(NVARCHAR(MAX),HMUAYENE.HMLABNOT),'') AS HMUAYENE_HMLABNOT,
			ISNULL(CONVERT(NVARCHAR(MAX),HMUAYENE.HMRADNOT),'') AS HMUAYENE_HMRADNOT,
			ISNULL(CONVERT(NVARCHAR(MAX),HMUAYENE.HMAMELNOT),'') AS HMUAYENE_HMAMELNOT,
			ISNULL(CONVERT(NVARCHAR(MAX),HMUAYENE.HMTEDAVI),'') AS HMUAYENE_HMTEDAVI,
			ISNULL(CONVERT(NVARCHAR(MAX),HMUAYENE.HMONERI),'') AS HMUAYENE_HMONERI,	
			ISNULL(CONVERT(NVARCHAR(MAX),HMUAYENE.HMSONUC),'') AS HMUAYENE_HMSONUC,	
			--(SELECT TOP 1 ISNULL(CONVERT(NVARCHAR(MAX),HSAGBILGI.ACIKLAMA),'') AS HSAGBILGI_ACIKLAMA FROM HSAGBILGI WHERE HKIMLIKID = HDOSYA.HKIMLIKID ) HSAGBILGI_ACIKLAMA ,
			--(SELECT TOP 1 ISNULL(CONVERT(NVARCHAR(MAX),HSAGBILGI.ACIKLAMA),'') AS HSAGBILGI_ACIKLAMA FROM HSAGBILGI WHERE HKIMLIKID = HDOSYA.HKIMLIKID ) HSAGBILGI_ACIKLAMA ,
			ISNULL((SELECT VALUESTR FROM CONFIG WHERE SECTIONSTR = 'Saglik-Net' AND PROGNAME = 'MEDISOFT' AND KEYNAME = CONVERT(VARCHAR, HDOSYA.SUBEID)+' numaralı şubenin kurum numarası'), '') AS KURUMKODU
	INTO #TMPMUAYENE
	FROM HDOSYA WITH(NOLOCK)
	 INNER JOIN HMUAYENE WITH(NOLOCK) ON HMUAYENE.HDOSYAID = HDOSYA.HDOSYAID
	 LEFT JOIN HMUAYENEISL WITH(NOLOCK) ON HMUAYENEISL.HMUAYENEID = HMUAYENE.HMUAYENEID 
     LEFT JOIN HESAPISL WITH(NOLOCK) ON HESAPISL.HESAPISLID = HMUAYENEISL.HESAPISLID
	 LEFT JOIN HYATIS WITH(NOLOCK) ON HYATIS.HMUAYENEID = HMUAYENE.HMUAYENEID
	 LEFT JOIN HESAPISL HESAPISL2 WITH(NOLOCK) ON HESAPISL2.HESAPISLID = HYATIS.HESAPISLID

	 INNER JOIN SUBELER WITH(NOLOCK) ON SUBELER.SUBEID = HDOSYA.SUBEID	 
WHERE (ISNULL(HESAPISL.IPTAL,0) <> 1 AND ISNULL(HESAPISL2.IPTAL,0) <> 1) AND 
	  (ISNULL(HESAPISL.ONAYLI,1)=1 AND ISNULL(HESAPISL2.ONAYLI,1)=1) AND
	  (ISNULL(HESAPISL.TEKLIF,0) <> 1 AND ISNULL(HESAPISL2.TEKLIF,0) <> 1)
	 AND	HDOSYA.HDOSYAID =  @HDOSYAID
	 AND	(
				@HMUAYENEID=0
				OR 
				HMUAYENE.HMUAYENEID =@HMUAYENEID
			)
	AND (@TUMUNUTEKRAR=1 OR (@TUMUNUTEKRAR=0 AND 
	
	((HMUAYENEISL.SNOMUAYENEGONDERILDI = 0 OR HMUAYENEISL.SNOMUAYENEGONDERILDI IS NULL)
	OR
	(HYATIS.SNOMUAYENEGONDERILDI = 0 OR HYATIS.SNOMUAYENEGONDERILDI IS NULL)) 
	))
	
	  
	PRINT CAST(@UYGADET AS NVARCHAR) +'  UYGADET' 
		
	IF (@UYGADET>0) AND (SELECT COUNT(*) FROM #TMPMUAYENE) = 0
	BEGIN
	 PRINT '  İÇERİDE' 	
	-------*****
	 --  IF OBJECT_ID('tempdb..#TMPMUAYENE') IS NOT NULL 
		--DROP TABLE #TMPMUAYENE
        INSERT INTO #TMPMUAYENE
		SELECT	DISTINCT  -1000 AS HMUAYENE_HMUAYENEID,
				HDOSYA.SUBEID AS HDOSYA_SUBEID,
				SUBELER.SUBEADI AS SUBELER_SUBEADI,			
				HDOSYA.SNOTAKIPNO AS HDOSYA_SNOTAKIPNO,
				(SELECT COUNT(*) FROM #TMPTANI) AS ICD_SAYI,
				(SELECT COUNT(*) FROM #TMPRAPOR) AS RAPOR_SAYI,
				(SELECT COUNT(*) FROM #TMPRAPORDR) AS RAPOR_DR_SAYI,
				(SELECT COUNT(*) FROM #TMPRECETE) AS RECETE_SAYI,			
				(SELECT COUNT(*) FROM #TMPNADIRHASTALIK) AS NADIR_HASTALIK_SAYI,
				(SELECT COUNT(*) FROM #TMPMUAYENEASASKOR) AS ASA_SKOR_SAYI,
				--'' AS HMUAYENE_HMACIKLAMA,
				'' AS HMUAYENE_HMSIKAYETI,
				'' AS HMUAYENE_HMHKIKAYESI,
				'' AS HMUAYENE_HMMUAYENE,
				'' AS HMUAYENE_HMLABNOT,
				'' AS HMUAYENE_HMRADNOT,
				'' AS HMUAYENE_HMAMELNOT,
				'' AS HMUAYENE_HMTEDAVI,
				'' AS HMUAYENE_HMONERI,	
				'' AS HMUAYENE_HMSONUC,	
				--'' AS HSAGBILGI_ACIKLAMA,
				ISNULL((SELECT VALUESTR FROM CONFIG WHERE SECTIONSTR = 'Saglik-Net' AND PROGNAME = 'MEDISOFT' AND KEYNAME = CONVERT(VARCHAR, HDOSYA.SUBEID)+' numaralı şubenin kurum numarası'), '') AS KURUMKODU
		FROM HDOSYA WITH(NOLOCK)	
			INNER JOIN SUBELER WITH(NOLOCK) ON SUBELER.SUBEID = HDOSYA.SUBEID	 
	WHERE  HDOSYA.HDOSYAID =  @HDOSYAID
	------******
	PRINT 1	    
	END 	
			
	IF (SELECT COUNT(*) FROM #TMPMUAYENE) = 0
	BEGIN
		SET @SAYIHATA = 1
		SET @MESAJ = @MESAJ + CONVERT(VARCHAR,@HDOSYAID)+' Dosyasına ait ussye göndeilecek muayene kaydı bulunamadı! '		
	END



	IF (SELECT COUNT(*) FROM #TMPTANI)=0
	BEGIN
		SET @HATA = @HATA + 'Muayene kaydı için tanı girişi yapılmamış! USS sistemine kayıt yapılamaz. '
	END

	IF (SELECT COUNT(*) FROM #TMPMUAYENE WHERE HDOSYA_SUBEID IS NULL) > 0 
		SET @HATA = @HATA + 'İşlemin şube kaydı bulunamadı! '

	IF (SELECT COUNT(*) FROM #TMPMUAYENE WHERE ISNULL(HDOSYA_SNOTAKIPNO,'')='') > 0 
		SET @HATA = @HATA + 'Sağlık Net hasta takip numarası alınmadan USS sistemine muayene yapılamaz. '

	IF @HATA='' and @SAYIHATA=0
	BEGIN		
		SELECT @SUBEID = HDOSYA_SUBEID FROM #TMPMUAYENE
		DECLARE @TBLXMLBASLIK TABLE (XMLBASLIK NVARCHAR(2000), HATA NVARCHAR(2000),MESAJ NVARCHAR(2000))
		INSERT INTO @TBLXMLBASLIK (XMLBASLIK, HATA, MESAJ) EXEC SPUSS_000_XML_BASLIK @SUBEID		
		SELECT @HATA=HATA, @MESAJ=MESAJ, @XMLBASLIK=XMLBASLIK FROM @TBLXMLBASLIK		
	END
	

	IF @HATA = '' AND @SAYIHATA=0
	BEGIN



			--HER TETKIK KARTI AYRI AYRI GONDER DONGUSU
   		DECLARE @CURMUAYENEID INT
		DECLARE CURMUAYENE CURSOR FOR SELECT HMUAYENE_HMUAYENEID FROM #TMPMUAYENE
		OPEN CURMUAYENE
		FETCH NEXT FROM CURMUAYENE INTO @CURMUAYENEID
		WHILE @@FETCH_STATUS =0
		BEGIN

				SET @HATA = ''
				SET @MESAJ = ''
				SET @SAYIHATA = 0






				IF @HATA = '' AND @SAYIHATA=0
				BEGIN

					BEGIN TRY 
						INSERT INTO @GONDERIMTABLOSU (GONDERILECEKXML,HATA,MESAJ,XMLBASLIK,TABLOID,MINID,MAXID)
						SELECT							 
							(

								SELECT 
									NEWID() AS 'messageGuid/@value',
									'1' AS 'messageType/@version',
									'0a9ba485-e7e0-4abb-9c86-0a14fd364bb8' AS 'messageType/@codeSystemGuid',
									'103' AS 'messageType/@code',
									'Muayene Bilgisi Kayıt' AS 'messageType/@value',
									LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),GETDATE(),120),' ',''),'-',''),':',''),12) AS 'documentGenerationTime/@value',
									(
										SELECT 
											'1' AS 'healthcareProvider/@version',
											'c3eade04-4f91-5dab-e043-14031b0ac9f9' AS 'healthcareProvider/@codeSystemGuid',
											KURUMKODU AS 'healthcareProvider/@code',			    
											SUBELER_SUBEADI AS 'healthcareProvider/@value' FOR XML PATH('author'), TYPE
									),
	   
									'C740D0288ED4C45FE0407C0A04162BDD' AS 'firmaKodu/@value',
	   
									(/*p1*/
										SELECT 
											(SELECT ISNULL(HDOSYA_SNOTAKIPNO, '') AS 'SYSTakipNo/@value' FOR XML PATH('HASTA_TAKIP_BILGISI'), TYPE ),
											(/*p2*/
													SELECT /*s1*/
														CASE
														WHEN RECETE_SAYI>0
														THEN
																	(/*p3*/
																		SELECT /*s2*/
																			(/*p4*/
																				SELECT /*s3*/
																						ISNULL(LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),HRECETE_HRECTARIH,120),' ',''),'-',''),':',''),12), '') AS 'RECETE_TARIHI/@value',
																						ISNULL(CONVERT(VARCHAR(20), HRECETE_HRECETEID), '') AS 'RECETE_NUMARASI/@value',
																						'1' AS 'RECETE_TURU/@version',
																						'c2fbe9bb-f6b3-4cb5-8670-47890ed7ed4b' AS 'RECETE_TURU/@codeSystemGuid',
																						ISNULL(CONVERT(VARCHAR(20), HRECETE_RECETETURID), '') AS 'RECETE_TURU/@code',
																						ISNULL(RECETE_TURUADI, '') AS 'RECETE_TURU/@value',
																						ISNULL(TDRKIMLIK_DRTCKIMLIKNO, '') AS 'HEKIM_KIMLIK_NUMARASI/@value',
																						(/*p5*/
																							SELECT /*s4*/
																									ISNULL((SELECT CASE WHEN HRECETEISL_ILACBARKOD IS NULL THEN TILAC_BARKOD ELSE HRECETEISL_ILACBARKOD END), '') AS 'ILAC_BARKODU/@value',
																									ISNULL(HRECETEISL_ILACADI, '') AS 'ILAC_ADI/@value',
																									ISNULL(CONVERT(VARCHAR(20), HRECETEISL_MIKTAR), '') AS 'KUTU_ADETI/@value',
																									'1' AS 'ILAC_KULLANIM_SEKLI/@version',
																									'32d57611-4928-46da-afac-624aaaa388d8' AS 'ILAC_KULLANIM_SEKLI/@codeSystemGuid',
																									ISNULL(CONVERT(VARCHAR(20), HRECETEISL_ILACKULSEKLIID), '') AS 'ILAC_KULLANIM_SEKLI/@code',
																									ISNULL(TILACKULSEKLI_ILACKULSEKLIADI, '') AS 'ILAC_KULLANIM_SEKLI/@value',
																									CONVERT(VARCHAR, 1) AS 'ILAC_KULLANIM_SAYISI/@value',
																									ISNULL(CONVERT(VARCHAR(20), HRECETEISL_KULLANIMADET), '') AS 'ILAC_KULLANIM_DOZU/@value',
																									ISNULL(CONVERT(VARCHAR(20), HRECETEISL_KULPERIYODU), '') AS 'ILAC_KULLANIM_PERIYODU/@value',
																									'1' AS 'ILAC_KULLANIM_PERIYODU_BIRIMI/@version',
																									'64408499-b82a-4e64-805e-e821aa0c64c9' AS 'ILAC_KULLANIM_PERIYODU_BIRIMI/@codeSystemGuid',
																									ISNULL(CONVERT(VARCHAR(20), HRECETEISL_KULPERIYODBIRIMI), '') AS 'ILAC_KULLANIM_PERIYODU_BIRIMI/@code',
																									ISNULL((SELECT CASE HRECETEISL_KULPERIYODBIRIMI WHEN 1 THEN 'Dakika' WHEN 2 THEN 'Saat' WHEN 3 THEN 'Gün' WHEN 4 THEN 'Hafta'						                 WHEN 5 THEN 'Ay' ELSE '' END), '') AS 'ILAC_KULLANIM_PERIYODU_BIRIMI/@value',
																									'' AS 'ILAC_ACIKLAMA/@value'
																							FROM #TMPILAC
																							WHERE #TMPILAC.HRECETEISL_HRECETEID = #TMPRECETE.HRECETE_HRECETEID 
																							FOR XML PATH('ILAC_BILGISI'), TYPE
																							/*s4 end*/
																						)/*p5 end*/
																				FROM #TMPRECETE 
																				--WHERE #TMPRECETE.HRECETE_HMUAYENEID = #TMPMUAYENE.HMUAYENE_HMUAYENEID
																				FOR XML PATH('RECETE_BILGISI'), TYPE
																				/*s3 end*/
								
																			)/*p4 end*/ FOR XML PATH('HASTA_RECETE_BILGILERI'), TYPE
																		/*s2 end*/
																	)/*p3 end*/
														END /*case end*/
													/*s1 end*/
											)/*p2 end*/,
                                            (/*p2*/
													SELECT /*s1*/
														CASE
														WHEN RAPOR_SAYI>0
														THEN
																	(/*p3*/
																		SELECT /*s2*/
																			(/*p4*/
																				SELECT /*s3*/
																						ISNULL(LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),HRAPOR_RAPORTARIHI,120),' ',''),'-',''),':',''),12), '') AS 'RAPOR_TARIHI/@value',
																						ISNULL(LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),HRAPOR_BASLANGICTARIHI,120),' ',''),'-',''),':',''),12), '') AS 'RAPOR_BASLANGIC_TARIHI/@value',
																						ISNULL(LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),HRAPOR_BITISTARIHI,120),' ',''),'-',''),':',''),12), '') AS 'RAPOR_BITIS_TARIHI/@value',
																						ISNULL(CONVERT(VARCHAR(20), HRAPOR_RAPORNO), HRAPOR_HRAPORID) AS 'RAPOR_NUMARASI/@value',
																						'1' AS 'RAPOR_TURU/@version',
																						'3fb672ac-0675-44ef-9f91-86856dc79370' AS 'RAPOR_TURU/@codeSystemGuid',
																						ISNULL(HRAPOR_RAPORTURID, '') AS 'RAPOR_TURU/@code',
																						ISNULL(TRAPORTUR_RAPORTURADI, '') AS 'RAPOR_TURU/@value',
																						ISNULL(CONVERT(VARCHAR(20), HRAPOR_RAPORNO), '') AS 'RAPOR_TAKIP_NUMARASI/@value',
																						'' AS 'RAPOR_PDF_BILGISI/@value',
																				        (SELECT /*s1*/   --Hekim Kimlik Bilgisi
																						ISNULL(TDRKIMLIK_HEKIMKIMLIKNO, '') AS 'HEKIM_KIMLIK_NUMARASI/@value'
																						FROM #TMPRAPORDR 
																						FOR XML PATH('RAPOR_HEKIM_BILGISI'), TYPE
																						/*s1 end*/)																					  
																						
																				FROM #TMPRAPOR 
																				FOR XML PATH('RAPOR_BILGISI'), TYPE
																				/*s3 end*/
								
																			)/*p4 end*/ FOR XML PATH('HASTA_RAPOR_BILGILERI'), TYPE
																		/*s2 end*/
																	)/*p3 end*/
														END /*case end*/
													/*s1 end*/
											)/*p2 end*/,
				
											(/*p2*/
													SELECT/*s1*/
															/*  özgeçmiş kapatıldı.
															(/*p3*/
																SELECT 
																		CASE
																		WHEN HSAGBILGI_ACIKLAMA<>''
																		THEN																
																			(SELECT 'Özgeçmiş' AS 'EPIKRIZ_BILGISI_BASLIK/@value', HSAGBILGI_ACIKLAMA AS 'EPIKRIZ_BILGISI_ACIKLAMA/@value' FOR XML PATH ('EPIKRIZ_BILGISI'), TYPE)
																		END								    
															)/*p3 end*/,
															*/

															/*
															(/*p3*/ açıklama kapatıldı
																SELECT 
																		CASE
																		WHEN HMUAYENE_HMACIKLAMA<>''
																		THEN																
																			(SELECT 'Aciklama' AS 'EPIKRIZ_BILGISI_BASLIK/@value', HMUAYENE_HMACIKLAMA AS 'EPIKRIZ_BILGISI_ACIKLAMA/@value' FOR XML PATH ('EPIKRIZ_BILGISI'), TYPE)
																		END								    
															)/*p3 end*/,
															*/

															(/*p3*/
																SELECT 
																		CASE
																		WHEN HMUAYENE_HMSIKAYETI<>''
																		THEN																
																			(SELECT 'Yakınması' AS 'EPIKRIZ_BILGISI_BASLIK/@value', HMUAYENE_HMSIKAYETI AS 'EPIKRIZ_BILGISI_ACIKLAMA/@value' FOR XML PATH ('EPIKRIZ_BILGISI'), TYPE)
																		END								    
															)/*p3 end*/,
															(/*p3*/
																SELECT 
																		CASE
																		WHEN HMUAYENE_HMHKIKAYESI<>''																
																		THEN
																			(SELECT 'Hikayesi' AS 'EPIKRIZ_BILGISI_BASLIK/@value', HMUAYENE_HMHKIKAYESI AS 'EPIKRIZ_BILGISI_ACIKLAMA/@value' FOR XML PATH('EPIKRIZ_BILGISI'), TYPE)
																		END
															)/*p3 end*/,
															(/*p3*/
																SELECT 
																		CASE
																		WHEN HMUAYENE_HMMUAYENE<>''																
																		THEN
																			(SELECT 'Fiziki Bakı' AS 'EPIKRIZ_BILGISI_BASLIK/@value', HMUAYENE_HMMUAYENE AS 'EPIKRIZ_BILGISI_ACIKLAMA/@value' FOR XML PATH('EPIKRIZ_BILGISI'), TYPE)
																		END															
															)/*p3 end*/,
															(/*p3*/
																SELECT 
																		CASE
																		WHEN HMUAYENE_HMLABNOT<>''
																		THEN																
																			(SELECT 'Laboratuvar Notu' AS 'EPIKRIZ_BILGISI_BASLIK/@value', HMUAYENE_HMLABNOT AS 'EPIKRIZ_BILGISI_ACIKLAMA/@value' FOR XML PATH ('EPIKRIZ_BILGISI'), TYPE)
																		END								    
															)/*p3 end*/,
															(/*p3*/
																SELECT 
																		CASE
																		WHEN HMUAYENE_HMRADNOT<>''
																		THEN																
																			(SELECT 'Radyoloji Notu' AS 'EPIKRIZ_BILGISI_BASLIK/@value', HMUAYENE_HMRADNOT AS 'EPIKRIZ_BILGISI_ACIKLAMA/@value' FOR XML PATH ('EPIKRIZ_BILGISI'), TYPE)
																		END								    
															)/*p3 end*/,
															(/*p3*/
																SELECT 
																		CASE
																		WHEN HMUAYENE_HMAMELNOT<>''
																		THEN																
																			(SELECT 'Mudahale/Ameliyat/Medikal Tedavi' AS 'EPIKRIZ_BILGISI_BASLIK/@value', HMUAYENE_HMAMELNOT AS 'EPIKRIZ_BILGISI_ACIKLAMA/@value' FOR XML PATH ('EPIKRIZ_BILGISI'), TYPE)
																		END								    
															)/*p3 end*/,
															(/*p3*/
																SELECT 
																		CASE
																		WHEN HMUAYENE_HMTEDAVI<>''
																		THEN																
																			(SELECT 'Tedavi' AS 'EPIKRIZ_BILGISI_BASLIK/@value', HMUAYENE_HMTEDAVI AS 'EPIKRIZ_BILGISI_ACIKLAMA/@value' FOR XML PATH ('EPIKRIZ_BILGISI'), TYPE)
																		END								    
															)/*p3 end*/,
															(/*p3*/
																SELECT 
																		CASE
																		WHEN HMUAYENE_HMONERI<>''																
																		THEN
																			(SELECT 'Öneri' AS 'EPIKRIZ_BILGISI_BASLIK/@value', HMUAYENE_HMONERI AS 'EPIKRIZ_BILGISI_ACIKLAMA/@value' FOR XML PATH('EPIKRIZ_BILGISI'), TYPE)
																		END		


															
															)/*p3 end*/,
															(/*p3*/
																SELECT 
																		CASE
																		WHEN HMUAYENE_HMSONUC<>''																
																		THEN
																			(SELECT 'Sonuc' AS 'EPIKRIZ_BILGISI_BASLIK/@value', HMUAYENE_HMSONUC AS 'EPIKRIZ_BILGISI_ACIKLAMA/@value' FOR XML PATH('EPIKRIZ_BILGISI'), TYPE)
																		END	
															
															)/*p3 end*/,
															(/*p3*/
																SELECT 
																		CASE
																		WHEN ICD_SAYI>0
																		THEN
																				(
																					SELECT	'1' AS 'TANI_TURU/@version',
																							'55894edb-1a8c-4f7f-a447-0119e61c14f1' AS 'TANI_TURU/@codeSystemGuid',																																													CASE TICD_ICDTURUID
																							WHEN 1 THEN '3'
																							WHEN 2 THEN '1'
																							WHEN 3 THEN '4'
																							ELSE '2' 
																							END AS 'TANI_TURU/@code',
																							CASE TICD_ICDTURUID
																							WHEN 1 THEN 'ÖN TANI'
																							WHEN 2 THEN 'ANA TANI'
																							WHEN 3 THEN 'AYIRICI TANI'
																							ELSE 'EK TANI' 
																							END AS 'TANI_TURU/@value',
																							'1' AS 'ICD10/@version',
																							'c3eaabad-8c4c-56ee-e043-14031b0a5530' AS 'ICD10/@codeSystemGuid',
																							ISNULL(CONVERT(VARCHAR(20), TICD_ICDKOD), '') AS 'ICD10/@code',
																							ISNULL(TICD_ICDADI, '') AS 'ICD10/@value'
																					FROM #TMPTANI
																					FOR XML PATH('TANI_BILGISI'), TYPE
																				)
																		END

															)/*p3 end*/ 
															
												,(/*p2*/
													SELECT /*s1*/
														CASE
														WHEN NADIR_HASTALIK_SAYI>0
														THEN
																
																
																	(/*p3*/
																		SELECT /*s2*/


																					  
																			/*p4*/     
																			            
																					   		'1' AS 'TIBBI_GENETIK_UZMAN_GORUSU/@version',
																							'8380d552-c64b-42b4-94d8-8ca8a617ac16' AS 'TIBBI_GENETIK_UZMAN_GORUSU/@codeSystemGuid',
																							(SELECT TOP 1 UZMANGORUSKODU FROM #TMPNADIRHASTALIK) AS 'TIBBI_GENETIK_UZMAN_GORUSU/@code',
																							(SELECT TOP 1  UZMANGORUSADI FROM #TMPNADIRHASTALIK) AS 'TIBBI_GENETIK_UZMAN_GORUSU/@value',
																				       (SELECT /*s3*/

																							'1' AS 'NADIR_HASTALIK_TANI/@version',
																							'e56ff71b-82e0-4b48-adae-cf70e1912c93' AS 'NADIR_HASTALIK_TANI/@codeSystemGuid',
																							ISNULL(HMUAYENENADIRHASTALIKTANI_NADIRHASTALIKTANIID, '') AS 'NADIR_HASTALIK_TANI/@code',
																							ISNULL(TSKRSNADIRHASTALIKLISTESI_NADIRHASTALIKADI, '') AS 'NADIR_HASTALIK_TANI/@value'
																				     																				  
																						
																						FROM #TMPNADIRHASTALIK
																						FOR XML PATH('NADIR_HASTALIK_TANI_BILGISI'), TYPE
																						/*s3 end*/)
								
																			/*p4 end*/ FOR XML PATH('NADIR_HASTALIK_TANI_BILGILERI'), TYPE
																		/*s2 end*/
																	)/*p3 end*/




														END /*case end*/
													/*s1 end*/
											)/*p2 end*/
											
											,(/*p2*/
													SELECT /*s1*/
														CASE
														WHEN ASA_SKOR_SAYI>0
														THEN															
																
																	(/*p3*/
																		SELECT /*s2*/
																					  
																			/*p4*/    																			            
																					   		'1' AS 'ASA_SKORU/@version',
																							'd8b8c1b6-901c-4d9e-b756-0e460a6a9e1e' AS 'ASA_SKORU/@codeSystemGuid',
																							(SELECT TOP 1 HMUAYENEDETAY_USSASASKORID FROM #TMPMUAYENEASASKOR) AS 'ASA_SKORU/@code',
																							(SELECT TOP 1  HMUAYENEDETAY_USSASASKORADI FROM #TMPMUAYENEASASKOR) AS 'ASA_SKORU/@value'								
																			/*p4 end*/ FOR XML PATH('ASA_SKORU_BILGISI'), TYPE
																		/*s2 end*/
																	)/*p3 end*/


														END /*case end*/
													/*s1 end*/
											)/*p2 end*/

															FOR XML PATH('MUAYENE_BILGILERI'), TYPE
												)/*p2 end*/ FOR XML PATH('recordData'), TYPE
									)/*p1 end*/
							FROM #TMPMUAYENE
							WHERE #TMPMUAYENE.HMUAYENE_HMUAYENEID = @CURMUAYENEID
											
							FOR XML PATH(''), ROOT('SYSMessage')

							) AS GONDERILECEKXML,
							@HATA AS HATA, 
							@MESAJ AS MESAJ,
							@XMLBASLIK AS XMLBASLIK,
							@CURMUAYENEID AS TABLOID,
							0 AS MINID,
							0 AS MAXID

					END TRY
					BEGIN CATCH
						SET @HATA = ERROR_MESSAGE()
						IF @HATA = ''
							SET @HATA = 'Bilinmeyen bir hata oluştu'

		
						INSERT INTO @GONDERIMTABLOSU (GONDERILECEKXML,HATA,MESAJ,XMLBASLIK,TABLOID,MINID,MAXID)			
						SELECT	'' AS GONDERILECEKXML,
								@HATA AS HATA, 
								@MESAJ AS MESAJ,
								'' AS XMLBASLIK,
								@CURMUAYENEID AS TABLOID,
								0 AS MINID,
								0 AS MAXID
			

					END CATCH

				END
				ELSE
				BEGIN
		
					INSERT INTO @GONDERIMTABLOSU (GONDERILECEKXML,HATA,MESAJ,XMLBASLIK,TABLOID,MINID,MAXID)
					SELECT	'' AS GONDERILECEKXML,
							@HATA AS HATA, 
							@MESAJ AS MESAJ,
							'' AS XMLBASLIK,
							@HMUAYENEID AS TABLOID,
							0 AS MINID,
							0 AS MAXID
			
					SET @HATA = ''		 
				END

		FETCH NEXT FROM CURMUAYENE INTO @CURMUAYENEID
	END --son HER TETKIK KARTI AYRI AYRI GONDER DONGUSU


	

	CLOSE CURMUAYENE
	DEALLOCATE CURMUAYENE

	END
	ELSE
	BEGIN

		INSERT INTO @GONDERIMTABLOSU (GONDERILECEKXML,HATA,MESAJ,XMLBASLIK,TABLOID,MINID,MAXID)
		SELECT	'' AS GONDERILECEKXML,
				@HATA AS HATA, 
				@MESAJ AS MESAJ,
				'' AS XMLBASLIK,
				@HMUAYENEID AS TABLOID,
				0 AS MINID,
				0 AS MAXID
			
		SET @HATA = ''		 
	END

	
	IF OBJECT_ID('tempdb..#TMPMUAYENE') IS NOT NULL 
		DROP TABLE #TMPMUAYENE
	IF OBJECT_ID('tempdb..#TMPRECETE') IS NOT NULL 
		DROP TABLE #TMPRECETE
	IF OBJECT_ID('tempdb..#TMPILAC') IS NOT NULL 
		DROP TABLE #TMPILAC
	IF OBJECT_ID('tempdb..#TMPTANI') IS NOT NULL 
		DROP TABLE #TMPTANI


	SELECT * FROM @GONDERIMTABLOSU
	


END



