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 5631097,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..#TMPILAC') IS NOT NULL 
		DROP TABLE #TMPILAC
	IF OBJECT_ID('tempdb..#TMPTANI') IS NOT NULL 
		DROP TABLE #TMPTANI

	
	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.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	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 #TMPRECETE) AS RECETE_SAYI,			
			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.HMONERI),'') AS HMUAYENE_HMONERI,	
			ISNULL(CONVERT(NVARCHAR(MAX),HMUAYENE.HMSONUC),'') AS HMUAYENE_HMSONUC,					
			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 #TMPRECETE) AS RECETE_SAYI,			
				'' AS HMUAYENE_HMSIKAYETI,
				'' AS HMUAYENE_HMHKIKAYESI,
				'' AS HMUAYENE_HMMUAYENE,	
				'' AS HMUAYENE_HMONERI,	
				'' AS HMUAYENE_HMSONUC,					
				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*/
															(/*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_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 'Öneri' 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*/ 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



