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

CREATE PROCEDURE [dbo].[SPUSS_217_DIYALIZ_HASTASI_IZLEM_KAYIT] 
	@HDOSYAID INT=0,
	@HMUAYENEID INT=0,
	@TUMUNUTEKRAR BIT = 0
	 	 
AS
BEGIN
	--SPUSS_217_DIYALIZ_HASTASI_IZLEM_KAYIT 165788,115370,1
	DECLARE @HATA NVARCHAR(2000) = ''
	DECLARE @MESAJ NVARCHAR(2000) = ''
	DECLARE @SAYIHATA BIT = 0
	DECLARE @XMLBASLIK NVARCHAR(2000) = ''
	DECLARE @SUBEID INT = 1
	DECLARE @goNDERIMTABLOSU TABLE 
		(
			goNDERILECEKXML XML,
			HATA NVARCHAR(2000),
			MESAJ NVARCHAR(2000),	
			XMLBASLIK NVARCHAR(3500),
			TABLOID INT,
			MINID INT,
			MAXID INT
		)
	
	IF OBJECT_ID('tempdb..#TMPUSSDIGER') IS NOT NULL 
	DROP TABLE #TMPUSSDIGER


SELECT 
 USS217.ID USSDIGER_ID
,USS217.HDOSYAID
,USS217.HMUAYENEID
,USS217.HKIMLIKID
,USS217.HYATISID,


(SELECT TOP 1 ICDKOD FROM HDOSYAICD WHERE HDOSYAICD.HDOSYAID=HDOSYA.HDOSYAID ORDER BY ANATANI DESC) PRIMER_TANI_KODU,
(SELECT TOP 1 (SELECT TICD.ICDADI FROM TICD WHERE TICD.ICDKOD=HDOSYAICD.ICDKOD) FROM HDOSYAICD WHERE HDOSYAICD.HDOSYAID=HDOSYA.HDOSYAID ORDER BY ANATANI DESC) PRIMER_TANI_ADI,

(SELECT TOP 1 ICDKOD FROM HDOSYAICD WHERE HDOSYAICD.HDOSYAID=HDOSYA.HDOSYAID ORDER BY ANATANI DESC) BIRLIKTE_GORULEN_EK_HASTALIKLAR_KODU,
(SELECT TOP 1 (SELECT TICD.ICDADI FROM TICD WHERE TICD.ICDKOD=HDOSYAICD.ICDKOD) FROM HDOSYAICD WHERE HDOSYAICD.HDOSYAID=HDOSYA.HDOSYAID ORDER BY ANATANI DESC)BIRLIKTE_GORULEN_EK_HASTALIKLAR_ADI,

USS217.ANTIHIPERTANSIF_ILAC_KULLANIM_DURUMU AS ANTIHIPERTANSIF_ILAC_KULLANIM_DURUMU_ID,
TANTIHIPERTANSIF_ILAC_KULLANIM_DURUMU.AD AS ANTIHIPERTANSIF_ILAC_KULLANIM_DURUMU_ADI,

USS217.DAMAR_ERISIM_YOLU AS DAMAR_ERISIM_YOLU_ID,
TDAMAR_ERISIM_YOLU.AD AS DAMAR_ERISIM_YOLU_ADI,

USS217.DIYALIZE_GIRME_SIKLIGI AS DIYALIZE_GIRME_SIKLIGI_ID,
TDIYALIZE_GIRME_SIKLIGI.AD AS DIYALIZE_GIRME_SIKLIGI_ADI,

USS217.DIYALIZOR_ALANI AS DIYALIZOR_ALANI_ID,
TDIYALIZOR_ALANI.AD AS DIYALIZOR_ALANI_ADI,

USS217.DIYALIZOR_TIPI AS DIYALIZOR_TIPI_ID,
TDIYALIZOR_TIPI.AD AS DIYALIZOR_TIPI_ADI,

USS217.KAN_AKIM_HIZI AS KAN_AKIM_HIZI_ID,
TKAN_AKIM_HIZI.AD AS KAN_AKIM_HIZI_ADI,

USS217.KULLANILAN_DIYALIZ_TEDAVISI AS KULLANILAN_DIYALIZ_TEDAVISI_ID,
TKULLANILAN_DIYALIZ_TEDAVISI.AD AS KULLANILAN_DIYALIZ_TEDAVISI_ADI,

USS217.PERITONEAL_GECIRGENLIK AS PERITONEAL_GECIRGENLIK_ID,
TPERITONEAL_GECIRGENLIK.AD AS PERITONEAL_GECIRGENLIK_ADI,

PERITON_DIYALIZI_KACINCI_KATETER AS PERITON_DIYALIZI_KACINCI_KATETER,

USS217.PERITON_DIYALIZI_KATETER_TIPI AS PERITON_DIYALIZI_KATETER_TIPI_ID,
TPERITON_DIYALIZI_KATETER_TIPI.AD AS PERITON_DIYALIZI_KATETER_TIPI_ADI,

USS217.PERITON_DIYALIZ_KATETER_YERLESTIRME_YONTEMI AS PERITON_DIYALIZ_KATETER_YERLESTIRME_YONTEMI_ID,
TPERITON_DIYALIZ_KATETER_YERLESTIRME_YONTEMI.AD AS PERITON_DIYALIZ_KATETER_YERLESTIRME_YONTEMI_ADI,

USS217.PERITON_DIYALIZ_TUNEL_YONU AS PERITON_DIYALIZ_TUNEL_YONU_ID,
TPERITON_DIYALIZ_TUNEL_YONU.AD AS PERITON_DIYALIZ_TUNEL_YONU_ADI,

SEANS_SURESI AS SEANS_SURESI,

USS217.SINEKALSET AS SINEKALSET_ID,
TSINEKALSET.AD AS SINEKALSET_ADI,

AGIRLIK AS AGIRLIK,

USS217.AGIRLIK_OLCUM_ZAMANI AS AGIRLIK_OLCUM_ZAMANI_ID,
TAGIRLIK_OLCUM_ZAMANI.AD AS AGIRLIK_OLCUM_ZAMANI_ADI,

USS217.AKTIF_VITAMIN_D_KULLANIMI AS AKTIF_VITAMIN_D_KULLANIMI_ID,
TAKTIF_VITAMIN_D_KULLANIMI.AD AS AKTIF_VITAMIN_D_KULLANIMI_ADI,

USS217.ANEMI_TEDAVISI_YONTEMI AS ANEMI_TEDAVISI_YONTEMI_ID,
TANEMI_TEDAVISI_YONTEMI.AD AS ANEMI_TEDAVISI_YONTEMI_ADI,

USS217.FOSFOR_BAGLAYICI_AJAN AS FOSFOR_BAGLAYICI_AJAN_ID,
TFOSFOR_BAGLAYICI_AJAN.AD AS FOSFOR_BAGLAYICI_AJAN_ADI,

USS217.PERITON_DIYALIZI_KOMPLIKASYON AS PERITON_DIYALIZI_KOMPLIKASYON_ID,
TPERITON_DIYALIZI_KOMPLIKASYON.AD AS PERITON_DIYALIZI_KOMPLIKASYON_ADI,
TTEDAVININ_SEYRI.AD TEDAVININ_SEYRI_ADI   , USS217.TEDAVININ_SEYRI TEDAVININ_SEYRI_ID,


HDOSYA.SUBEID HDOSYA_SUBEID
	  ,SUBELER.SUBEADI SUBELER_SUBEADI
	  ,HDOSYA.SNOTAKIPNO HDOSYA_SNOTAKIPNO
	  ,		 ISNULL((SELECT TOP 1 VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='Saglik-Net' AND PROGNAME='MEDISOFT' AND KEYNAME= CONVERT(VARCHAR,HDOSYA.SUBEID)+' numarali subede Yenidoğanların Kimliksiz fatura edilebilir gün sayısı'),'') 	AS SAGLIKNETKIMLIKSIZYENIDOGANGUN,

		CASE WHEN (SELECT TOP 1 VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='Saglik-Net' AND PROGNAME='MEDISOFT' AND /* VALUESTR='1' AND*/ KEYNAME LIKE CONVERT(VARCHAR,HDOSYA.SUBEID)+' numaral% %ubede Sa%l%k Net Online test modu aktif')='1' THEN
			'10000038' 
		ELSE
			ISNULL((SELECT TOP 1 VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='Saglik-Net' AND PROGNAME='MEDISOFT' AND KEYNAME= CONVERT(VARCHAR,HDOSYA.SUBEID)+' numaralı şubenin kurum numarası'),'') 
		END AS SAGLIKNETKURUMKODU,

			CASE WHEN (SELECT TOP 1 VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='Saglik-Net' AND PROGNAME='MEDISOFT' AND /* VALUESTR='1' AND*/ KEYNAME LIKE CONVERT(VARCHAR,HDOSYA.SUBEID)+' numaral% %ubede Sa%l%k Net Online test modu aktif')='1' THEN
			'3bc26a7d-4a19-4edf-ab9f-1413ac85e7d0' /*TEST KTS FİRMA KODU*/
		ELSE
			'C740D0288ED4C45FE0407C0A04162BDD'  /*KTS TALYA*/ 
		END AS SNOKTSFIRMAKODU,

		ISNULL((SELECT TOP 1 VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='Saglik-Net' AND PROGNAME='MEDISOFT' AND KEYNAME= CONVERT(VARCHAR,HDOSYA.SUBEID)+' numarali subede Hasta kayıt gönderiminde referans no ön Karakteri'),'') AS REFNOONEK

INTO #TMPUSSDIGER 

FROM USS217_DIYALIZ_HASTASI_IZLEM AS USS217
INNER JOIN HDOSYA ON HDOSYA.HDOSYAID = USS217.HDOSYAID
LEFT JOIN HKIMLIK ON HKIMLIK.HKIMLIKID=HDOSYA.HKIMLIKID
LEFT JOIN	SUBELER ON SUBELER.SUBEID=HDOSYA.SUBEID

LEFT JOIN 
(
SELECT 'KULLANMIYOR' AS AD, 1 AS ID
UNION ALL
SELECT '1 ADET KULLANIYOR' AS AD, 2 AS ID
UNION ALL
SELECT '2 ADET KULLANIYOR' AS AD, 3 AS ID
UNION ALL
SELECT '2''DEN FAZLA İLAÇ KULLANIYOR' AS AD, 4 AS ID
) TANTIHIPERTANSIF_ILAC_KULLANIM_DURUMU ON TANTIHIPERTANSIF_ILAC_KULLANIM_DURUMU.ID=USS217.ANTIHIPERTANSIF_ILAC_KULLANIM_DURUMU

LEFT JOIN 
(
SELECT 'ARTERİO-VENÖZ FİSTÜL' AS AD, 1 AS ID
UNION ALL
SELECT 'ARTERİO-VENÖZ GREFT' AS AD, 2 AS ID
UNION ALL
SELECT 'GEÇİCİ TÜNELSİZ KATETER' AS AD, 3 AS ID
UNION ALL
SELECT 'KALICI TÜNELLİ KATETER' AS AD, 4 AS ID

) TDAMAR_ERISIM_YOLU ON TDAMAR_ERISIM_YOLU.ID=USS217.DAMAR_ERISIM_YOLU

LEFT JOIN
(
SELECT 'HAFTADA 1 DEFA' AS AD, 1 AS ID
UNION ALL
SELECT 'HAFTADA 2 DEFA' AS AD, 2 AS ID
UNION ALL
SELECT 'HAFTADA 3 DEFA' AS AD, 3 AS ID
UNION ALL
SELECT '3''DEN BÜYÜK' AS AD, 4 AS ID
) TDIYALIZE_GIRME_SIKLIGI ON TDIYALIZE_GIRME_SIKLIGI.ID = USS217.DIYALIZE_GIRME_SIKLIGI

LEFT JOIN
(
SELECT 'DİYALİZÖR ALANI < 1.0' AS AD, 1 AS ID
UNION ALL
SELECT '1.0 <= DİYALİZÖR ALANI <= 1.2' AS AD, 2 AS ID
UNION ALL
SELECT '1.3 <= DİYALİZÖR ALANI <= 1.5' AS AD, 3 AS ID
UNION ALL
SELECT '1.6 <= DİYALİZÖR ALANI < 1.8' AS AD, 4 AS ID
UNION ALL
SELECT 'DİYALİZÖR ALANI >= 1.8' AS AD, 5 AS ID
) TDIYALIZOR_ALANI ON TDIYALIZOR_ALANI.ID = USS217.DIYALIZOR_ALANI

LEFT JOIN
(
SELECT 'SELLÜLOZİK' AS AD, 1 AS ID
UNION ALL
SELECT 'SEMİ-SENTETİK' AS AD, 2 AS ID
UNION ALL
SELECT 'SENTETİK YÜKSEK AKIŞLI' AS AD, 3 AS ID
UNION ALL
SELECT 'SENTETİK' AS AD, 4 AS ID
UNION ALL
SELECT 'YÜKSEK AKIŞLI' AS AD, 5 AS ID
) TDIYALIZOR_TIPI ON TDIYALIZOR_TIPI.ID = USS217.DIYALIZOR_TIPI

LEFT JOIN
(
SELECT 'KAN AKIM HIZI < 200 ML/DK' AS AD, 1 AS ID
UNION ALL
SELECT '200 ML/DK <= KAN AKIM HIZI < 300 ML/DK' AS AD, 2 AS ID
UNION ALL
SELECT '300 ML/DK <= KAN AKIM HIZI < 400 ML/DK' AS AD, 3 AS ID
UNION ALL
SELECT 'KAN AKIM HIZI >= 400 ML/DK' AS AD, 4 AS ID
) TKAN_AKIM_HIZI ON TKAN_AKIM_HIZI.ID = USS217.KAN_AKIM_HIZI

LEFT JOIN
(
SELECT 'HEMODİYALİZ(STANDART)' AS AD, 1 AS ID
UNION ALL
SELECT 'SÜREKLİ AYAKTAN PERİTON DİYALİZİ(SAPT)' AS AD, 2 AS ID
UNION ALL
SELECT 'ALETLİ PERİTON DİYALİZİ' AS AD, 3 AS ID
UNION ALL
SELECT 'HEMODİYAFİLTRASYON' AS AD, 4 AS ID
UNION ALL
SELECT 'EV HEMODİYALİZİ' AS AD, 5 AS ID
UNION ALL
SELECT 'YOK' AS AD, 97 AS ID
UNION ALL
SELECT 'DİĞER' AS AD, 98 AS ID
) TKULLANILAN_DIYALIZ_TEDAVISI ON TKULLANILAN_DIYALIZ_TEDAVISI.ID = USS217.KULLANILAN_DIYALIZ_TEDAVISI

LEFT JOIN
(
SELECT 'DÜŞÜK(D/P KREATİNİN <=0.50 )' AS AD, 1 AS ID
UNION ALL
SELECT 'DÜŞÜK-ORTA ( 0.50 < D/P KREATİNİN <=0.64 )' AS AD, 2 AS ID
UNION ALL
SELECT 'YÜKSEK-ORTA ( 0.65 < D/P KREATİNİN <=0.81 )' AS AD, 3 AS ID
UNION ALL
SELECT 'YÜKSEK ( 0.81 < D/P KREATİNİN )' AS AD, 4 AS ID
) TPERITONEAL_GECIRGENLIK ON TPERITONEAL_GECIRGENLIK.ID = USS217.PERITONEAL_GECIRGENLIK

LEFT JOIN
(
SELECT 'CURLED TENCKHOFF' AS AD, 1 AS ID
UNION ALL
SELECT 'DÜZ TENCKHOFF' AS AD, 2 AS ID
UNION ALL
SELECT 'KUĞU BOYNU' AS AD, 3 AS ID
UNION ALL
SELECT 'DİĞER' AS AD, 98 AS ID
) TPERITON_DIYALIZI_KATETER_TIPI ON TPERITON_DIYALIZI_KATETER_TIPI.ID = USS217.PERITON_DIYALIZI_KATETER_TIPI

LEFT JOIN
(
SELECT 'LAPAROSKOPİK' AS AD, 1 AS ID
UNION ALL
SELECT 'CERRAHİ' AS AD, 2 AS ID
UNION ALL
SELECT 'PERKÜTAN' AS AD, 3 AS ID
) TPERITON_DIYALIZ_KATETER_YERLESTIRME_YONTEMI ON TPERITON_DIYALIZ_KATETER_YERLESTIRME_YONTEMI.ID = USS217.PERITON_DIYALIZ_KATETER_YERLESTIRME_YONTEMI

LEFT JOIN
(
SELECT 'AŞAĞI' AS AD, 1 AS ID
UNION ALL
SELECT 'YUKARI' AS AD, 2 AS ID
UNION ALL
SELECT 'YAN' AS AD, 3 AS ID
) TPERITON_DIYALIZ_TUNEL_YONU ON TPERITON_DIYALIZ_TUNEL_YONU.ID = USS217.PERITON_DIYALIZ_TUNEL_YONU

LEFT JOIN
(
SELECT 'KULLANIYOR' AS AD, 1 AS ID
UNION ALL
SELECT 'KULLANMIYOR' AS AD, 2 AS ID
) TSINEKALSET ON TSINEKALSET.ID = USS217.SINEKALSET

LEFT JOIN
(
SELECT 'TRANSPLANTASYONA GİTTİ' AS AD, 3 AS ID
UNION ALL
SELECT 'DİYALİZ TEDAVİSİ DEVAM EDİYOR' AS AD, 7 AS ID
UNION ALL
SELECT 'DİYALİZ TEDAVİSİNE İHTİYACI KALMADI' AS AD, 8 AS ID
) TTEDAVININ_SEYRI ON TTEDAVININ_SEYRI.ID = USS217.TEDAVININ_SEYRI

LEFT JOIN
(
SELECT 'SEANS ÖNCESİ' AS AD, 1 AS ID
UNION ALL
SELECT 'SEANS SONRASI' AS AD, 2 AS ID
) TAGIRLIK_OLCUM_ZAMANI ON TAGIRLIK_OLCUM_ZAMANI.ID = USS217.AGIRLIK_OLCUM_ZAMANI

LEFT JOIN
(
SELECT 'İHTİYACI YOK' AS AD, 1 AS ID
UNION ALL
SELECT 'ORAL' AS AD, 2 AS ID
UNION ALL
SELECT 'INTRAVENÖZ' AS AD, 3 AS ID
UNION ALL
SELECT 'D VİTAMİN ANALOĞU' AS AD, 4 AS ID
) TAKTIF_VITAMIN_D_KULLANIMI ON TAKTIF_VITAMIN_D_KULLANIMI.ID = USS217.AKTIF_VITAMIN_D_KULLANIMI

LEFT JOIN
(
SELECT 'TEDAVİ İHTİYACI YOK' AS AD, 1 AS ID
UNION ALL
SELECT 'TRANSFÜZYON' AS AD, 2 AS ID
UNION ALL
SELECT 'ERİTROPOETİN' AS AD, 3 AS ID
UNION ALL
SELECT 'DEMİR PREPERATI' AS AD, 4 AS ID
) TANEMI_TEDAVISI_YONTEMI ON TANEMI_TEDAVISI_YONTEMI.ID = USS217.ANEMI_TEDAVISI_YONTEMI

LEFT JOIN
(
SELECT 'KALSİYUM ASETAT' AS AD, 1 AS ID
UNION ALL
SELECT 'KALSİYUM KARBONAT' AS AD, 2 AS ID
UNION ALL
SELECT 'SEVELAMER' AS AD, 3 AS ID
UNION ALL
SELECT 'KULLANMIYOR' AS AD, 4 AS ID
UNION ALL
SELECT 'DİĞER' AS AD, 98 AS ID
) TFOSFOR_BAGLAYICI_AJAN ON TFOSFOR_BAGLAYICI_AJAN.ID = USS217.FOSFOR_BAGLAYICI_AJAN

LEFT JOIN
(
SELECT 'PERİTONİT' AS AD, 1 AS ID
UNION ALL
SELECT 'KATETER ÇIKIŞ YERİ ENFEKSİYONU' AS AD, 2 AS ID
UNION ALL
SELECT 'TÜNEL ENFEKSİYONU' AS AD, 3 AS ID
UNION ALL
SELECT 'KATETER MALPOZİSYONU' AS AD, 4 AS ID
UNION ALL
SELECT 'DİYALİZAT KAÇAĞI' AS AD, 5 AS ID
UNION ALL
SELECT 'ULTRAFİLTRASYON YETERSİZLİĞİ' AS AD, 6 AS ID
UNION ALL
SELECT 'DİYALİZ YETERSİZLİĞİ' AS AD, 7 AS ID
UNION ALL
SELECT 'YOK' AS AD, 97 AS ID
UNION ALL
SELECT 'DİĞER' AS AD, 98 AS ID
) TPERITON_DIYALIZI_KOMPLIKASYON ON TPERITON_DIYALIZI_KOMPLIKASYON.ID = USS217.PERITON_DIYALIZI_KOMPLIKASYON


WHERE USS217.HMUAYENEID= @HMUAYENEID AND USS217.HDOSYAID = @HDOSYAID 


	IF (SELECT COUNT(*) FROM #TMPUSSDIGER) = 0 
	BEGIN
		SET @SAYIHATA = 1
		SET @MESAJ = @MESAJ + 'Gönderilecek kayıt bulunamadı! '
	END

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

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

   	IF @HATA='' AND @SAYIHATA=0
	BEGIN		
		SELECT @SUBEID = HDOSYA_SUBEID FROM #TMPUSSDIGER
		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 @CURUSSDIGERID INT
		DECLARE CURUSSDIGER CURSOR FOR SELECT USSDIGER_ID FROM #TMPUSSDIGER
		OPEN CURUSSDIGER
		FETCH NEXT FROM CURUSSDIGER INTO @CURUSSDIGERID
		WHILE @@FETCH_STATUS =0
		BEGIN
		

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

		
					 
					IF EXISTS(SELECT USSDIGER_ID FROM #TMPUSSDIGER WHERE  ISNULL(USSDIGER_ID,0)=0)
						SET @HATA = @HATA + ' ' + 'Kayıt seçilmemiş. '


					IF (SELECT COUNT(*) FROM #TMPUSSDIGER WHERE USSDIGER_ID=@CURUSSDIGERID) > 0
					BEGIN
						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',
										   '217' AS 'messageType/@code',
										   'Diyaliz Hastasi Izlem Veri Seti' 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',
												 SAGLIKNETKURUMKODU as 'healthcareProvider/@code',
												 SUBELER_SUBEADI as 'healthcareProvider/@value'  
												 FOR XML PATH('author'), TYPE),
												 --'C740D0288ED4C45FE0407C0A04162BDD' as 'firmaKodu/@value' ,
												 SNOKTSFIRMAKODU as 'firmaKodu/@value' ,
												 (
													SELECT 
														(
														   SELECT 
															  (
																 SELECT 
																   
																   --<DIYALIZ_TEDAVISINE_BASLAMA_TARIHI value="DIYALIZ_TEDAVISINE_BASLAMA_TARIHI" />
																   --LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),DIYALIZ_TEDAVISINE_BASLAMA_TARIHI,120),' ',''),'-',''),':',''),12)	 AS 'DIYALIZ_TEDAVISINE_BASLAMA_TARIHI/@value',
																   
																   --ANTIHIPERTANSIF_ILAC_KULLANIM_DURUMU version="1" codeSystemGuid="c3b1c7db-fc3a-4a8c-9c02-0583215eed24" code="Code"
																		'1' AS 'ANTIHIPERTANSIF_ILAC_KULLANIM_DURUMU/@version',
																		'c3b1c7db-fc3a-4a8c-9c02-0583215eed24' AS 'ANTIHIPERTANSIF_ILAC_KULLANIM_DURUMU/@codeSystemGuid',
																		ISNULL(ANTIHIPERTANSIF_ILAC_KULLANIM_DURUMU_ID,'') AS 'ANTIHIPERTANSIF_ILAC_KULLANIM_DURUMU/@code',
																		ISNULL(ANTIHIPERTANSIF_ILAC_KULLANIM_DURUMU_ADI,'') AS 'ANTIHIPERTANSIF_ILAC_KULLANIM_DURUMU/@value',
														
																	--<DAMAR_ERISIM_YOLU version="1" codeSystemGuid="74c60a72-ef10-450a-9e95-0cdae6fe8490" code="Code" value="DAMAR_ERISIM_YOLU" />																		
																		'1' AS 'DAMAR_ERISIM_YOLU/@version',
																		'74c60a72-ef10-450a-9e95-0cdae6fe8490' AS 'DAMAR_ERISIM_YOLU/@codeSystemGuid',
																			ISNULL(DAMAR_ERISIM_YOLU_ID,'') AS 'DAMAR_ERISIM_YOLU/@code',
																			ISNULL(DAMAR_ERISIM_YOLU_ADI,'') AS 'DAMAR_ERISIM_YOLU/@value',
																	
																	--<DIYALIZE_GIRME_SIKLIGI version="1" codeSystemGuid="ced10d4a-2e20-4c30-bd42-72740c66c348" code="Code" value="DIYALIZE_GIRME_SIKLIGI" /
																	    '1' AS 'DIYALIZE_GIRME_SIKLIGI/@version',
																		'ced10d4a-2e20-4c30-bd42-72740c66c348' AS 'DIYALIZE_GIRME_SIKLIGI/@codeSystemGuid',
																			ISNULL(DIYALIZE_GIRME_SIKLIGI_ID,'') AS 'DIYALIZE_GIRME_SIKLIGI/@code',
																			ISNULL(DIYALIZE_GIRME_SIKLIGI_ADI,'') AS 'DIYALIZE_GIRME_SIKLIGI/@value',

																	--<DIYALIZOR_ALANI version="1" codeSystemGuid="a96fa435-723f-43d5-b5c2-26baf2871354" code="Code" value="DIYALIZOR_ALANI" />
																		'1' AS 'DIYALIZOR_ALANI/@version',
																		'a96fa435-723f-43d5-b5c2-26baf2871354' AS 'DIYALIZOR_ALANI/@codeSystemGuid',
																			ISNULL(DIYALIZOR_ALANI_ID,'') AS 'DIYALIZOR_ALANI/@code',
																			ISNULL(DIYALIZOR_ALANI_ADI,'') AS 'DIYALIZOR_ALANI/@value',

																	--<DIYALIZOR_TIPI version="1" codeSystemGuid="ef5f5e04-7ad0-47e0-be5e-d8b508d19eda" code="Code" value="DIYALIZOR_TIPI" />
																	'1' AS 'DIYALIZOR_TIPI/@version',
																		'ef5f5e04-7ad0-47e0-be5e-d8b508d19eda' AS 'DIYALIZOR_TIPI/@codeSystemGuid',
																			ISNULL(DIYALIZOR_TIPI_ID,'') AS 'DIYALIZOR_TIPI/@code',
																			ISNULL(DIYALIZOR_TIPI_ADI,'') AS 'DIYALIZOR_TIPI/@value',

																	--<KAN_AKIM_HIZI version="1" codeSystemGuid="c4bdb87e-da85-47fd-ab05-c5f6633d22e9" code="Code" value="KAN_AKIM_HIZI" />
																	'1' AS 'KAN_AKIM_HIZI/@version',
																		'c4bdb87e-da85-47fd-ab05-c5f6633d22e9' AS 'KAN_AKIM_HIZI/@codeSystemGuid',
																			ISNULL(DIYALIZOR_TIPI_ID,'') AS 'KAN_AKIM_HIZI/@code',
																			ISNULL(DIYALIZOR_TIPI_ADI,'') AS 'KAN_AKIM_HIZI/@value',

																	--<KULLANILAN_DIYALIZ_TEDAVISI version="1" codeSystemGuid="d60416ba-f319-437b-8e02-e992d57a3612" code="Code" value="KULLANILAN_DIYALIZ_TEDAVISI" />
																	'1' AS 'KULLANILAN_DIYALIZ_TEDAVISI/@version',
																		'd60416ba-f319-437b-8e02-e992d57a3612' AS 'KULLANILAN_DIYALIZ_TEDAVISI/@codeSystemGuid',
																			ISNULL(KULLANILAN_DIYALIZ_TEDAVISI_ID,'') AS 'KULLANILAN_DIYALIZ_TEDAVISI/@code',
																			ISNULL(KULLANILAN_DIYALIZ_TEDAVISI_ADI,'') AS 'KULLANILAN_DIYALIZ_TEDAVISI/@value',

																	--<PERITONEAL_GECIRGENLIK version="1" codeSystemGuid="9b552b26-2bee-4000-8ff3-b954b9825a50" code="Code" value="PERITONEAL_GECIRGENLIK" />
																	'1' AS 'PERITONEAL_GECIRGENLIK/@version',
																		'9b552b26-2bee-4000-8ff3-b954b9825a50' AS 'PERITONEAL_GECIRGENLIK/@codeSystemGuid',
																			ISNULL(PERITONEAL_GECIRGENLIK_ID,'') AS 'PERITONEAL_GECIRGENLIK/@code',
																			ISNULL(PERITONEAL_GECIRGENLIK_ADI,'') AS 'PERITONEAL_GECIRGENLIK/@value',

																	--<PERITON_DIYALIZI_KACINCI_KATETER value="PERITON_DIYALIZI_KACINCI_KATETER" />
																	ISNULL(PERITON_DIYALIZI_KACINCI_KATETER,'')	 AS 'PERITON_DIYALIZI_KACINCI_KATETER/@value',

																	--<PERITON_DIYALIZI_KATETER_TIPI version="1" codeSystemGuid="2bf0796b-a8fd-4c51-81a3-59ef5e8d6a10" code="Code" value="PERITON_DIYALIZI_KATETER_TIPI" />
																	'1' AS 'PERITON_DIYALIZI_KATETER_TIPI/@version',
																		'2bf0796b-a8fd-4c51-81a3-59ef5e8d6a10' AS 'PERITON_DIYALIZI_KATETER_TIPI/@codeSystemGuid',
																			ISNULL(PERITON_DIYALIZI_KATETER_TIPI_ID,'') AS 'PERITON_DIYALIZI_KATETER_TIPI/@code',
																			ISNULL(PERITON_DIYALIZI_KATETER_TIPI_ADI,'') AS 'PERITON_DIYALIZI_KATETER_TIPI/@value',

																	--<PERITON_DIYALIZ_KATETER_YERLESTIRME_YONTEMI version="1" codeSystemGuid="1ba51ab0-da51-4abf-828a-ac386459d265" code="Code" value="PERITON_DIYALIZ_KATETER_YERLESTIRME_YONTEMI" />
																	'1' AS 'PERITON_DIYALIZ_KATETER_YERLESTIRME_YONTEMI/@version',
																		'1ba51ab0-da51-4abf-828a-ac386459d265' AS 'PERITON_DIYALIZ_KATETER_YERLESTIRME_YONTEMI/@codeSystemGuid',
																			ISNULL(PERITON_DIYALIZ_KATETER_YERLESTIRME_YONTEMI_ID,'') AS 'PERITON_DIYALIZ_KATETER_YERLESTIRME_YONTEMI/@code',
																			ISNULL(PERITON_DIYALIZ_KATETER_YERLESTIRME_YONTEMI_ADI,'') AS 'PERITON_DIYALIZ_KATETER_YERLESTIRME_YONTEMI/@value',

																	--<PERITON_DIYALIZ_TUNEL_YONU version="1" codeSystemGuid="3c6d5d1c-12d9-4570-a1e5-1a76d595b40a" code="Code" value="PERITON_DIYALIZ_TUNEL_YONU" />
																		'1' AS 'PERITON_DIYALIZ_TUNEL_YONU/@version',
																		'3c6d5d1c-12d9-4570-a1e5-1a76d595b40a' AS 'PERITON_DIYALIZ_TUNEL_YONU/@codeSystemGuid',
																			ISNULL(PERITON_DIYALIZ_TUNEL_YONU_ID,'') AS 'PERITON_DIYALIZ_TUNEL_YONU/@code',
																			ISNULL(PERITON_DIYALIZ_TUNEL_YONU_ADI,'') AS 'PERITON_DIYALIZ_TUNEL_YONU/@value',

																	--<SEANS_SURESI value="SEANS_SURESI" />
																	ISNULL(SEANS_SURESI,'')	 AS 'SEANS_SURESI/@value',

																	--<SINEKALSET version="1" codeSystemGuid="26abb1b8-2f2c-45ac-b8b5-fc6c572c012a" code="Code" value="SINEKALSET" />
																	'1' AS 'SINEKALSET/@version',
																		'26abb1b8-2f2c-45ac-b8b5-fc6c572c012a' AS 'SINEKALSET/@codeSystemGuid',
																			ISNULL(SINEKALSET_ID,'') AS 'SINEKALSET/@code',
																			ISNULL(SINEKALSET_ADI,'') AS 'SINEKALSET/@value',


																	--<TEDAVININ_SEYRI version="1" codeSystemGuid="850f9005-04ba-4c45-86f5-34cffdce02b1" code="Code" value="TEDAVININ_SEYRI" />
																		'1' AS 'TEDAVININ_SEYRI/@version',
																		'850f9005-04ba-4c45-86f5-34cffdce02b1' AS 'TEDAVININ_SEYRI/@codeSystemGuid',
																			ISNULL(TEDAVININ_SEYRI_ID,'') AS 'TEDAVININ_SEYRI/@code',
																			ISNULL(TEDAVININ_SEYRI_ADI,'') AS 'TEDAVININ_SEYRI/@value',


																		--<AGIRLIK_BILGISI>
																		--  <AGIRLIK value="AGIRLIK" />
																		--  <AGIRLIK_OLCUM_ZAMANI version="1" codeSystemGuid="85a98ad5-7b53-45d6-aa6b-5b84127efbcc" code="Code" value="AGIRLIK_OLCUM_ZAMANI" />
																		--  </AGIRLIK_BILGISI>
																	   (SELECT 
																	    ISNULL(AGIRLIK,'')	 AS 'AGIRLIK/@value',
																		'1' AS 'AGIRLIK_OLCUM_ZAMANI/@version',
																		'85a98ad5-7b53-45d6-aa6b-5b84127efbcc' AS 'AGIRLIK_OLCUM_ZAMANI/@codeSystemGuid',
																			ISNULL(AGIRLIK_OLCUM_ZAMANI_ID,'') AS 'AGIRLIK_OLCUM_ZAMANI/@code',
																			ISNULL(AGIRLIK_OLCUM_ZAMANI_ADI,'') AS 'AGIRLIK_OLCUM_ZAMANI/@value'
																			FOR XML PATH('AGIRLIK_BILGISI'),TYPE),

																	-- <AKTIF_VITAMIN_D_KULLANIMI_BILGISI>
																	-- <AKTIF_VITAMIN_D_KULLANIMI version="1" codeSystemGuid="092fc5cb-1718-4d9e-a084-b666b2654021" code="Code" value="AKTIF_VITAMIN_D_KULLANIMI" />
																	-- </AKTIF_VITAMIN_D_KULLANIMI_BILGISI>
																	(SELECT 
																		'1' AS 'AKTIF_VITAMIN_D_KULLANIMI/@version',
																		'092fc5cb-1718-4d9e-a084-b666b2654021' AS 'AKTIF_VITAMIN_D_KULLANIMI/@codeSystemGuid',
																			ISNULL(AKTIF_VITAMIN_D_KULLANIMI_ID,'') AS 'AKTIF_VITAMIN_D_KULLANIMI/@code',
																			ISNULL(AKTIF_VITAMIN_D_KULLANIMI_ADI,'') AS 'AKTIF_VITAMIN_D_KULLANIMI/@value'
																			FOR XML PATH('AKTIF_VITAMIN_D_KULLANIMI_BILGISI'),TYPE),

																	-- <ANEMI_TEDAVISI_YONTEMI_BILGISI>
																	-- <ANEMI_TEDAVISI_YONTEMI version="1" codeSystemGuid="6037979e-61af-473e-ac5a-f58839804c34" code="Code" value="ANEMI_TEDAVISI_YONTEMI" />
																	-- </ANEMI_TEDAVISI_YONTEMI_BILGISI>
																	(SELECT 
																		'1' AS 'ANEMI_TEDAVISI_YONTEMI/@version',
																		'6037979e-61af-473e-ac5a-f58839804c34' AS 'ANEMI_TEDAVISI_YONTEMI/@codeSystemGuid',
																			ISNULL(ANEMI_TEDAVISI_YONTEMI_ID,'') AS 'ANEMI_TEDAVISI_YONTEMI/@code',
																			ISNULL(ANEMI_TEDAVISI_YONTEMI_ADI,'') AS 'ANEMI_TEDAVISI_YONTEMI/@value'
																			FOR XML PATH('ANEMI_TEDAVISI_YONTEMI_BILGISI'),TYPE),

																	-- <FOSFOR_BAGLAYICI_AJAN_BILGISI>
																		-- <FOSFOR_BAGLAYICI_AJAN version="1" codeSystemGuid="57ac14e2-c27f-4aa1-8044-169bfc826d9e" code="Code" value="FOSFOR_BAGLAYICI_AJAN" />
																	-- </FOSFOR_BAGLAYICI_AJAN_BILGISI>
																	(SELECT 
																		'1' AS 'FOSFOR_BAGLAYICI_AJAN/@version',
																		'57ac14e2-c27f-4aa1-8044-169bfc826d9e' AS 'FOSFOR_BAGLAYICI_AJAN/@codeSystemGuid',
																			ISNULL(FOSFOR_BAGLAYICI_AJAN_ID,'') AS 'FOSFOR_BAGLAYICI_AJAN/@code',
																			ISNULL(FOSFOR_BAGLAYICI_AJAN_ADI,'') AS 'FOSFOR_BAGLAYICI_AJAN/@value'
																			FOR XML PATH('FOSFOR_BAGLAYICI_AJAN_BILGISI'),TYPE),


																	-- <PERITON_DIYALIZI_KOMPLIKASYON_BILGISI>
																	   -- <PERITON_DIYALIZI_KOMPLIKASYON version="1" codeSystemGuid="feb54346-61ae-4c83-ae69-3c3fd159f47d" code="Code" value="PERITON_DIYALIZI_KOMPLIKASYON" />
																	-- </PERITON_DIYALIZI_KOMPLIKASYON_BILGISI>
																	(SELECT 
																		'1' AS 'PERITON_DIYALIZI_KOMPLIKASYON/@version',
																		'feb54346-61ae-4c83-ae69-3c3fd159f47d' AS 'PERITON_DIYALIZI_KOMPLIKASYON/@codeSystemGuid',
																			ISNULL(PERITON_DIYALIZI_KOMPLIKASYON_ID,'') AS 'PERITON_DIYALIZI_KOMPLIKASYON/@code',
																			ISNULL(PERITON_DIYALIZI_KOMPLIKASYON_ADI,'') AS 'PERITON_DIYALIZI_KOMPLIKASYON/@value'
																			FOR XML PATH('PERITON_DIYALIZI_KOMPLIKASYON_BILGISI'),TYPE)
 		
																FROM #TMPUSSDIGER
																
																FOR XML PATH(''),TYPE
															)
														FOR XML PATH('DIYALIZ_HASTASI_IZLEM'),TYPE
													),					
													   (
														SELECT ISNULL(HDOSYA_SNOTAKIPNO, '' ) AS 'SYSTakipNo/@value'
														FOR XML PATH('HASTA_TAKIP_BILGISI'), TYPE 
													)							
												 FOR XML PATH('recordData'), TYPE
											  )
										   FROM #TMPUSSDIGER WITH(NOLOCK)
										   WHERE USSDIGER_ID = @CURUSSDIGERID
										FOR XML PATH (''), ROOT('SYSMessage')


									) AS goNDERILECEKXML,
									@HATA AS HATA, 
									@MESAJ AS MESAJ,
									@XMLBASLIK AS XMLBASLIK,
									@CURUSSDIGERID 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,
										@CURUSSDIGERID 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,
									LEFT(@HATA,2000) AS HATA, 
									@MESAJ AS MESAJ,
									'' AS XMLBASLIK,
									@CURUSSDIGERID AS TABLOID,
									0 AS MINID,
									0 AS MAXID

							SET @HATA = ''		 
						END
					END




		FETCH NEXT FROM CURUSSDIGER INTO @CURUSSDIGERID 
		END --son HER TETKIK KARTI AYRI AYRI goNDER DONGUSU
	

		CLOSE CURUSSDIGER
		DEALLOCATE CURUSSDIGER

		IF (SELECT COUNT(*) FROM @GONDERIMTABLOSU)=0
			INSERT INTO @GONDERIMTABLOSU (GONDERILECEKXML,HATA,MESAJ,XMLBASLIK,TABLOID,MINID,MAXID)
			SELECT	'' AS GONDERILECEKXML,
					'' AS HATA, 
					'Tüm KAYITLAR zaten gönderilmiş' AS MESAJ,
					'' AS XMLBASLIK,
					0 AS TABLOID,
					0 AS MINID,
					0 AS MAXID


	END
	ELSE
	BEGIN

		INSERT INTO @goNDERIMTABLOSU (goNDERILECEKXML,HATA,MESAJ,XMLBASLIK,TABLOID,MINID,MAXID)
		SELECT	'' AS goNDERILECEKXML,
				LEFT(@HATA,2000) AS HATA, 
				@MESAJ AS MESAJ,
				'' AS XMLBASLIK,
				@CURUSSDIGERID AS TABLOID,
				0 AS MINID,
				0 AS MAXID

		SET @HATA = ''		 
	END


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

	

	SELECT * FROM @goNDERIMTABLOSU


END
