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

CREATE PROCEDURE [dbo].[SPUSS_500_YOGUN_BAKIM_DEGERLENDIRME_KAYIT] 
	@HDOSYAID INT=0,
	@HYBBYS INT=0,
	@TUMUNUTEKRAR BIT = 0
AS
BEGIN
	--SPUSS_500_YOGUN_BAKIM_DEGERLENDIRME_KAYIT 1750368,0,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..#TMPHYATIS') IS NOT NULL 
		DROP TABLE #TMPHYATIS

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

	SELECT 
			HYBBYS.HYBBYSID AS HYBBYS_HYBBYSID,			
			HYBBYS.HYATISID AS HYBBYS_HYATISID,		
			HYBBYS.HDOSYAID AS HYBBYS_HDOSYAID, 
			HYBBYS.TARIH AS HYBBYS_TARIH,
			HYBBYS.ISLEMREFERANSNUMARASI AS HYBBYS_ISLEMREFERANSNUMARASI,
			HYBBYS.ACTBALANSDEGERI AS HYBBYS_ACTBALANSDEGERI, 
			HYBBYS.AKIDURUMU AS HYBBYS_AKIDURUMU, 
			HYBBYS.ALKOLOZDURUMU AS HYBBYS_ALKOLOZDURUMU,			
			HYBBYS.ARDSGELISMEDURUMU AS HYBBYS_ARDSGELISMEDURUMU,		    
			HYBBYS.ASIDOZDURUMU AS HYBBYS_ASIDOZDURUMU, 
			HYBBYS.BESLENMEBILGISI AS HYBBYS_BESLENMEBILGISI, 
			HYBBYS.BESLENMEBILGISIID AS HYBBYS_BESLENMEBILGIID,
			HYATIS.HYYATISTARIH AS HYATIS_HYYATISTARIH, 
			HYBBYS.APACHESKORU AS HYBBYS_APACHESKORU, 
			HYBBYS.APACHEBEKLENENOLUMORANI AS HYBBYS_APACHEBEKLENENOLUMORANI, 
			HYBBYS.APACHEBEKLENENOLUMORANIDUZELTILMIS AS HYBBYS_APACHEBEKLENENOLUMORANIDUZELTILMIS, 
			HYBBYS.APACHEOLCUMZAMANI AS HYBBYS_APACHEOLCUMZAMANI, 
			HYBBYS.GLASKOWGOZLER AS HYBBYS_GLASKOWGOZLER, 
			HYBBYS.GLASKOWMOTOR AS HYBBYS_GLASKOWMOTOR, 
			HYBBYS.GLASKOWSOZEL AS HYBBYS_GLASKOWSOZEL, 
            HYBBYS.DEKUBITULSERISKORLAMATURU AS HYBBYS_DEKUBITULSERISKORLAMATURU ,
			HYBBYS.DEKUBITULSERISKORSONUCU AS HYBBYS_DEKUBITULSERISKORLAMASONUCU,
			HYBBYS.DKB  AS HYBBYS_DKB,
			HYBBYS.DRENBILGISI AS HYBBYS_DRENBILGISI,
			HYBBYS.DRENVARLIGI AS HYBBYS_DRENVARLIGI,
			HYBBYS.DUSMERISKI AS HYBBYS_DUSMERISKI,
			HYBBYS.ENFEKTEOLMADURUMBILGISI AS HYBBYS_ENFEKTEOLMADURUM ,
			HYBBYS.ECMOIHTIYACI AS HYBBYS_ECMOIHTIYACI,
			HYBBYS.FIOIIDEGERI AS HYBBYS_FIOIIDEGERI,
			HYBBYS.FREKANSDEGERI AS HYBBYS_FREKANSDEGERI,
			HYBBYS.GONDERILDI AS HYBBYS_GONDERILDI,
			HYBBYS.HAVALIHASTAYATAKDURUMU AS HYBBYS_HAVALIHASTAYATAK,
			HYBBYS.HEMODIYALIZIHTIYACI AS HYBBYS_HEMODIYALIZIHTIYACI,
			dbo.TKANGRUBU.SKRSKANGRUBUID AS HYBBYS_HKANGRUBUID,
			HYBBYS.KANGAZIHCOIII AS HYBBYS_KANGAZIHCOIII,
			HYBBYS.IBPDIASTOLIK AS HYBBYS_IBPDIASTOLIK,
			HYBBYS.IBPSISTOLIK AS HYBBYS_IBPSISTOLIK,
			HYBBYS.KANGAZIGLUKOZ AS HYBBYS_KANGAZIGLUKOZ,
			HYBBYS.KANGAZILAKTAT AS HYBBYS_KANGAZILAKTAT,
			HYBBYS.KANGAZIPACOII AS HYBBYS_KANGAZIPACOII,
			HYBBYS.KANGAZIPAOII AS HYBBYS_KANGAZIPAOII,
			HYBBYS.KANGAZIPH AS HYBBYS_KANGAZIPH,
			HYBBYS.KATATERBILGISI AS HYBBYS_KATATERBILGISI,
			HYBBYS.NABIZ AS HYBBYS_NABIZ,
			HYBBYS.PEEPDEGERI AS HYBBYS_PEEPDEGERI,
			HYBBYS.SANTRALVENOZBASINC AS HYBBYS_SANTRALVENOZBASINC,
			HYBBYS.SAPSIIBEKLENENOLUMORANI AS HYBBYS_SAPSIIBEKLENENOLUMORANI,
			HYBBYS.SAPSIIGENISLETILMIS AS HYBBYS_SAPSIIGENISLETILMIS,
			HYBBYS.SAPSIIOLCUMZAMANI AS HYBBYS_SAPSIIOLCUMZAMANI,
			HYBBYS.SAPSIISKORU AS HYBBYS_SAPSIISKORU,
			HYBBYS.SEPSISDURUMU AS HYBBYS_SEPSISDURUMU,
			HYBBYS.SEPTIKSOK AS HYBBYS_SEPTIKSOK,
			HYBBYS.SKB AS HYBBYS_SKB,
			HYBBYS.SNOTAKIPNO  AS HYBBYS_SNOTAKIPNO,
			HYBBYS.SOFABEKLENENOLUMORANI AS HYBBYS_SOFABEKLENENOLUMORANI,
			HYBBYS.SOFAOLCUMZAMANI AS HYBBYS_SOFAOLCUMZAMANI,
			HYBBYS.SOFASKORU AS HYBBYS_SOFASKORU,
			HYBBYS.SPOIIDEGERI AS HYBBYS_SPOIIDEGERI,
			HYBBYS.TEPEBASINCDEGERI AS HYBBYS_TEPEBASINCDEGERI,
			HYBBYS.TIDALVOLUMDEGERI AS HYBBYS_TIDALVOLUMDEGERI,
			HYBBYS.VENTILASYONBASLANGICZAMANI AS HYBBYS_VENTILASYONBASLANGICZAMANI,
			HYBBYS.VENTILASYONBITISZAMANI AS HYBBYS_VENTILASYONBITISZAMANI,
			HYBBYS.VENTILASYONDURUMU AS HYBBYS_VENTILASYONDURUMU,
			HYBBYS.VENTILASYONYONTEMI AS HYBBYS_VENTILASYONYONTEMI,
			HYBBYS.VENTILATORILISKILIPNOMONIVARLIGI AS HYBBYS_VENTILATORILISKILIPNOMONIVARLIGI,
			HYBBYS.VENTILATORMODU AS HYBBYS_VENTILATORMODU,
			HYBBYS.VUCUTISISI AS HYBBYS_VUCUTISISI,
			HYBBYS.YATAKBASIYARASIVARLIGI AS HYBBYS_YATAKBASIYARASIVARLIGI,
			HYBBYS.YBCIKISZAMANI AS HYBBYS_YBCIKISZAMANI,
			HYBBYS.YBGIRISZAMANI AS HYBBYS_YBGIRISZAMANI,
			HYBBYS.YBUNITEADI AS HYBBYS_YBUNITEADI,
			HYBBYS.YBUNITEID AS HYBBYS_YBUNITEID,
			HYBBYS.YBUNITESISEVIYESI  AS HYBBYS_YBUNITESISEVIYESI,
			TKANGRUBU.KANGRUBU AS TKANGRUBU_KANGRUBU,
			TDEKUBIT.DEKUBITULSERISKORLAMATURUADI AS DEKUBITSKORLAMATURUADI,
			TSKRSVENTILASYONYONTEMI.VENTILASYONYONTEMIADI AS  VENTILASYONYONTEMIADI,
			TSKRSVENTILATORMODU.VENTILATORMODUADI AS VENTILATORMODUADI,
			TSKRSBESLENME.BESLENMEADI AS BESLENMEADI,
			TSKRSKATATERBILGISI.KATATERBILGISIADI AS KATATERBILGISIADI,
			TKLINIK.KLINIKADI AS YBUNITEADI,
			TSKRSDRENBILGISI.DRENADI AS DRENADI
	INTO #TMPYOGUNBAKIM
	FROM HYATIS
	INNER JOIN	HYBBYS ON HYBBYS.HYATISID = HYATIS.HYATISID
    INNER JOIN HDOSYA ON HDOSYA.HDOSYAID=HYATIS.HDOSYAID
	LEFT JOIN dbo.TKANGRUBU ON HYBBYS.HKANGRUBUID = dbo.TKANGRUBU.KANGRUBUID
	LEFT JOIN TSKRSKATATERBILGISI ON HYBBYS.KATATERBILGISI = TSKRSKATATERBILGISI.KATATERBILGISIKODU
	LEFT JOIN TSKRSBESLENME ON HYBBYS.BESLENMEBILGISI = TSKRSBESLENME.BESLENMEKODU
	LEFT JOIN TSKRSDEKUBITULSERISKORLAMATURU TDEKUBIT ON HYBBYS.DEKUBITULSERISKORLAMATURU = TDEKUBIT.DEKUBITULSERISKORLAMATURUKODU
	LEFT JOIN TSKRSVENTILASYONYONTEMI ON HYBBYS.VENTILASYONYONTEMI = TSKRSVENTILASYONYONTEMI.VENTILASYONYONTEMIKODU
    LEFT JOIN TSKRSVENTILATORMODU ON HYBBYS.VENTILATORMODU = TSKRSVENTILATORMODU.VENTILATORMODUKODU	
	LEFT JOIN TKLINIK ON HYBBYS.YBUNITEID = TKLINIK.KLINIKKODU		
    LEFT JOIN TSKRSDRENBILGISI ON HYBBYS.DRENBILGISI = TSKRSDRENBILGISI.DRENKODU				
	WHERE HYATIS.HDOSYAID = @HDOSYAID
	AND (@HYBBYS=0 OR HYBBYS.HYBBYSID = @HYBBYS)
	AND (@TUMUNUTEKRAR=1 OR (@TUMUNUTEKRAR=0 AND ISNULL(HYBBYS.GONDERILDI,0)=0))
	ORDER BY KAYITTARIHI DESC

	
	SELECT 
	HDOSYA.SUBEID AS HDOSYA_SUBEID,
	SUBELER.SUBEADI AS SUBELER_SUBEADI,
	HDOSYA.SNOTAKIPNO AS HDOSYA_SNOTAKIPNO,
	HYATIS.HESAPISLID AS HYATIS_HESAPISLID,
	HYATIS.HYATISID AS  HYATIS_HYATISID,
	HYATIS.HASTATIPI AS HYATIS_HASTATIPI,
	ISNULL((SELECT TOP 1 HYBBYS_YBGIRISZAMANI FROM #TMPYOGUNBAKIM WHERE HYBBYS_HYATISID=HYATIS.HYATISID AND (@HYBBYS=0 OR HYBBYS_HYBBYSID = @HYBBYS) ORDER BY HYBBYS_HYBBYSID DESC),HYATIS.HYYATISTARIH) AS HYATIS_YATISTARIH,
	ISNULL((SELECT TOP 1 HYBBYS_YBCIKISZAMANI FROM #TMPYOGUNBAKIM WHERE HYBBYS_HYATISID=HYATIS.HYATISID AND (@HYBBYS=0 OR HYBBYS_HYBBYSID = @HYBBYS) ORDER BY HYBBYS_HYBBYSID DESC),HYATIS.HYCIKISTARIH) AS HYATIS_CIKISTARIH,
	ISNULL((SELECT TOP 1 HYBBYS_HKANGRUBUID FROM #TMPYOGUNBAKIM WHERE HYBBYS_HYATISID=HYATIS.HYATISID AND (@HYBBYS=0 OR HYBBYS_HYBBYSID = @HYBBYS) ORDER BY HYBBYS_HYBBYSID DESC),'') AS HYATIS_HKANGRUBUID,
	ISNULL((SELECT TOP 1 TKANGRUBU_KANGRUBU FROM #TMPYOGUNBAKIM WHERE HYBBYS_HYATISID=HYATIS.HYATISID AND (@HYBBYS=0 OR HYBBYS_HYBBYSID = @HYBBYS) ORDER BY HYBBYS_HYBBYSID DESC),'') AS HYATIS_TKANGRUBUID,
	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ı'),'') AS SAGLIKNETKURUMKODU
	INTO #TMPHYATIS
	FROM HYATIS WITH(NOLOCK)
	JOIN HESAPISL WITH(NOLOCK) ON HESAPISL.HESAPISLID = HYATIS.HESAPISLID   
	LEFT JOIN	 HDOSYA WITH(NOLOCK) ON HDOSYA.HDOSYAID = HYATIS.HDOSYAID 
	LEFT JOIN	 SUBELER WITH(NOLOCK) ON SUBELER.SUBEID = HDOSYA.SUBEID
	WHERE HESAPISL.IPTAL = 0 
	  AND HESAPISL.ONAYLI = 1 
	  AND HESAPISL.TEKLIF = 0
	  AND HESAPISL.SNKAYDET = 1
	  AND HDOSYA.HDIPTAL = 0
	  AND EXISTS (SELECT * FROM #TMPYOGUNBAKIM WHERE HYBBYS_HYATISID=HYATIS.HYATISID)
	  AND HDOSYA.HDOSYAID = @HDOSYAID


	IF (SELECT COUNT(*) FROM #TMPYOGUNBAKIM) = 0 
	BEGIN
		SET @SAYIHATA = 1
		SET @MESAJ = @MESAJ + 'Dosyaya ait yoğun bakım kaydı bulunamadı! '
	END

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

	IF (SELECT HDOSYA_SUBEID FROM #TMPHYATIS WHERE ISNULL(HDOSYA_SNOTAKIPNO,'')='') > 0 
		SET @HATA = @HATA + 'Sağlık Net hasta takip numarası alınmadan USS sistemine yoğun bakım kaydı yapılamaz. '


	IF @HATA='' AND @SAYIHATA=0
	BEGIN		
		SELECT @SUBEID = HDOSYA_SUBEID FROM #TMPHYATIS
		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 YATISIN DEGERLENDIRMELERINI AYRI AYRI GONDER DONGUSU
   		DECLARE @CURYATISID INT
		DECLARE CURYATIS CURSOR FOR SELECT HYATIS_HYATISID FROM #TMPHYATIS
		OPEN CURYATIS
		FETCH NEXT FROM CURYATIS INTO @CURYATISID
		WHILE @@FETCH_STATUS =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',
								   '500' as 'messageType/@code',
								   'Yoğun Bakım Değerlendirme 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',
											(
											SELECT 
												(
													SELECT
														ISNULL(HDOSYA_SNOTAKIPNO,'') AS 'SYSTakipNo/@value'
													FOR XML PATH('HASTA_TAKIP_BILGISI'), TYPE
												),
												(
													SELECT																											
														'1' AS 'KAN_GRUBU/@version',
														'a3d6e943-5d85-4c75-ac72-709115974fb7' AS 'KAN_GRUBU/@codeSystemGuid',
														CASE WHEN ISNULL(HYATIS_HKANGRUBUID,0) <> 0 THEN  HYATIS_HKANGRUBUID END AS 'KAN_GRUBU/@code' ,
														CASE WHEN ISNULL(HYATIS_HKANGRUBUID,0) <> 0 THEN HYATIS_TKANGRUBUID END  AS 'KAN_GRUBU/@value',
														LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),HYATIS_YATISTARIH,120),' ',''),'-',''),':',''),12) as'YOGUN_BAKIM_GIRIS_ZAMANI/@value',
														ISNULL(LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),HYATIS_CIKISTARIH,120),' ',''),'-',''),':',''),12),'') as 'YOGUN_BAKIM_CIKIS_ZAMANI/@value',

														(
															SELECT 
															    '1' AS 'HAVALI_HASTA_YATAK_DURUMU/@version',
																'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS 'HAVALI_HASTA_YATAK_DURUMU/@codeSystemGuid',
																CASE WHEN HYBBYS_HAVALIHASTAYATAK = 1 THEN '1' ELSE '2' END AS 'HAVALI_HASTA_YATAK_DURUMU/@code',
																CASE WHEN HYBBYS_HAVALIHASTAYATAK = 1 THEN 'EVET' ELSE 'HAYIR' END AS 'HAVALI_HASTA_YATAK_DURUMU/@value',
																CASE WHEN ISNULL(HYBBYS_DEKUBITULSERISKORLAMASONUCU,'') <> ''  THEN  HYBBYS_DEKUBITULSERISKORLAMASONUCU END AS 'DEKUBIT_ULSERI_SKOR_SONUCU/@value', 
																CASE WHEN ISNULL(HYBBYS_DEKUBITULSERISKORLAMATURU,0) <> 0 THEN  '1' END  AS 'DEKUBIT_ULSERI_SKORLAMA_TURU/@version',
																CASE WHEN ISNULL(HYBBYS_DEKUBITULSERISKORLAMATURU,0) <> 0 THEN '4cf55c8c-f0d8-4bc1-9613-176f4bb614b8' END AS 'DEKUBIT_ULSERI_SKORLAMA_TURU/@codeSystemGuid',
																CASE WHEN ISNULL(HYBBYS_DEKUBITULSERISKORLAMATURU,0) <> 0 THEN HYBBYS_DEKUBITULSERISKORLAMATURU END AS 'DEKUBIT_ULSERI_SKORLAMA_TURU/@code',
																CASE WHEN ISNULL(HYBBYS_DEKUBITULSERISKORLAMATURU,0) <> 0 THEN DEKUBITSKORLAMATURUADI END AS 'DEKUBIT_ULSERI_SKORLAMA_TURU/@value',
																CASE WHEN ISNULL(HYBBYS_DRENBILGISI,0) <> 0 THEN  '1' END  AS 'DREN_BILGISI/@version',
																CASE WHEN ISNULL(HYBBYS_DRENBILGISI,0) <> 0 THEN '58ee888d-ed6c-4ae4-bf25-c5ebebf48d86' END AS 'DREN_BILGISI/@codeSystemGuid',
																CASE WHEN ISNULL(HYBBYS_DRENBILGISI,0) <> 0 THEN HYBBYS_DRENBILGISI END AS 'DREN_BILGISI/@code',
																CASE WHEN ISNULL(HYBBYS_DRENBILGISI,0) <> 0 THEN DRENADI END AS 'DREN_BILGISI/@value',
															    '1' AS 'DUSME_RISKI/@version',
																'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS 'DUSME_RISKI/@codeSystemGuid',
																CASE WHEN HYBBYS_DUSMERISKI = 1 THEN '1' ELSE '2' END AS 'DUSME_RISKI/@code',
																CASE WHEN HYBBYS_DUSMERISKI = 1 THEN 'EVET' ELSE 'HAYIR' END AS 'DUSME_RISKI/@value',
																'1' AS 'YOGUN_BAKIM_UNITE_ADI/@version',
																'c04bee57-c5d4-443d-e040-7b0a6f146a3d' AS 'YOGUN_BAKIM_UNITE_ADI/@codeSystemGuid',
																CASE WHEN ISNULL(HYBBYS_YBUNITEID,0) <> 0 THEN HYBBYS_YBUNITEID END AS 'YOGUN_BAKIM_UNITE_ADI/@code',
																CASE WHEN ISNULL(HYBBYS_YBUNITEID,0) <> 0 THEN YBUNITEADI END  AS 'YOGUN_BAKIM_UNITE_ADI/@value',
																
															    '1' AS 'ENFEKTE_OLMA_DURUM_BILGISI/@version',
																'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS 'ENFEKTE_OLMA_DURUM_BILGISI/@codeSystemGuid',
																CASE WHEN HYBBYS_ENFEKTEOLMADURUM = 1 THEN '1' ELSE '2' END AS 'ENFEKTE_OLMA_DURUM_BILGISI/@code',
																CASE WHEN HYBBYS_ENFEKTEOLMADURUM = 1 THEN 'EVET' ELSE 'HAYIR' END AS 'ENFEKTE_OLMA_DURUM_BILGISI/@value',


															    '1' AS 'SEPSIS_DURUMU/@version',
																'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS 'SEPSIS_DURUMU/@codeSystemGuid',
																CASE WHEN HYBBYS_SEPSISDURUMU = 1 THEN '1' ELSE '2' END AS 'SEPSIS_DURUMU/@code',
																CASE WHEN HYBBYS_SEPSISDURUMU = 1  THEN 'EVET' ELSE 'HAYIR' END AS 'SEPSIS_DURUMU/@value',

															    '1' AS 'SEPTIK_SOK/@version',
																'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS 'SEPTIK_SOK/@codeSystemGuid',
																CASE WHEN HYBBYS_SEPTIKSOK = 1  THEN '1' ELSE '2' END AS 'SEPTIK_SOK/@code',
																CASE WHEN  HYBBYS_SEPTIKSOK = 1  THEN 'EVET' ELSE 'HAYIR' END AS 'SEPTIK_SOK/@value',

															    '1' AS 'AKI_DURUMU/@version',
																'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS 'AKI_DURUMU/@codeSystemGuid',
																CASE WHEN HYBBYS_AKIDURUMU=1 THEN '1' ELSE '2' END AS 'AKI_DURUMU/@code',
																CASE WHEN HYBBYS_AKIDURUMU=1 THEN 'EVET' ELSE 'HAYIR' END AS 'AKI_DURUMU/@value',

															    '1' AS 'VENTILASYON_DURUMU/@version',
																'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS 'VENTILASYON_DURUMU/@codeSystemGuid',
																CASE WHEN HYBBYS_VENTILASYONDURUMU =1 THEN '1' ELSE '2' END AS 'VENTILASYON_DURUMU/@code',
																CASE WHEN HYBBYS_VENTILASYONDURUMU =1 THEN 'EVET' ELSE 'HAYIR' END AS 'VENTILASYON_DURUMU/@value',

															    '1' AS 'VENTILATOR_ILISKILI_PNOMONI_VARLIGI/@version',
																'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS 'VENTILATOR_ILISKILI_PNOMONI_VARLIGI/@codeSystemGuid',
																CASE WHEN HYBBYS_VENTILATORILISKILIPNOMONIVARLIGI =1  THEN '1' ELSE '2' END AS 'VENTILATOR_ILISKILI_PNOMONI_VARLIGI/@code',
																CASE WHEN HYBBYS_VENTILATORILISKILIPNOMONIVARLIGI =1 THEN 'EVET' ELSE 'HAYIR' END AS 'VENTILATOR_ILISKILI_PNOMONI_VARLIGI/@value',

															    '1' AS 'ASIDOZ_DURUMU/@version',
																'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS 'ASIDOZ_DURUMU/@codeSystemGuid',
																CASE WHEN HYBBYS_ASIDOZDURUMU = 1 THEN '1' ELSE '2' END AS 'ASIDOZ_DURUMU/@code',
																CASE WHEN HYBBYS_ASIDOZDURUMU = 1 THEN 'EVET' ELSE 'HAYIR' END AS 'ASIDOZ_DURUMU/@value',

															    '1' AS 'ALKOLOZ_DURUMU/@version',
																'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS 'ALKOLOZ_DURUMU/@codeSystemGuid',
																CASE WHEN HYBBYS_ALKOLOZDURUMU = 1 THEN '1' ELSE '2' END AS 'ALKOLOZ_DURUMU/@code',
																CASE WHEN HYBBYS_ALKOLOZDURUMU = 1 THEN 'EVET' ELSE 'HAYIR' END AS 'ALKOLOZ_DURUMU/@value',

															    '1' AS 'ARDS_GELISME_DURUMU/@version',
																'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS 'ARDS_GELISME_DURUMU/@codeSystemGuid',
																CASE WHEN HYBBYS_ARDSGELISMEDURUMU = 1 THEN '1' ELSE '2' END AS 'ARDS_GELISME_DURUMU/@code',
																CASE WHEN HYBBYS_ARDSGELISMEDURUMU = 1 THEN 'EVET' ELSE 'HAYIR' END AS 'ARDS_GELISME_DURUMU/@value',

															    '1' AS 'HEMODIYALIZ_IHTIYACI/@version',
																'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS 'HEMODIYALIZ_IHTIYACI/@codeSystemGuid',
																CASE WHEN HYBBYS_HEMODIYALIZIHTIYACI = 1 THEN '1' ELSE '2' END AS 'HEMODIYALIZ_IHTIYACI/@code',
																CASE WHEN HYBBYS_HEMODIYALIZIHTIYACI = 1 THEN 'EVET' ELSE 'HAYIR' END AS 'HEMODIYALIZ_IHTIYACI/@value',

															    '1' AS 'ECMO_IHTIYACI/@version',
																'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS 'ECMO_IHTIYACI/@codeSystemGuid',
																CASE WHEN HYBBYS_ECMOIHTIYACI = 1 THEN '1' ELSE '2' END AS 'ECMO_IHTIYACI/@code',
																CASE WHEN HYBBYS_ECMOIHTIYACI = 1  THEN 'EVET' ELSE 'HAYIR' END AS 'ECMO_IHTIYACI/@value',

															    '1' AS 'YATAK_BASI_YARASI_VARLIGI/@version',
																'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS 'YATAK_BASI_YARASI_VARLIGI/@codeSystemGuid',
																CASE WHEN HYBBYS_YATAKBASIYARASIVARLIGI = 1 THEN '1' ELSE '2' END AS 'YATAK_BASI_YARASI_VARLIGI/@code',
																CASE WHEN HYBBYS_YATAKBASIYARASIVARLIGI = 1 THEN 'EVET' ELSE 'HAYIR' END AS 'YATAK_BASI_YARASI_VARLIGI/@value',
																
																ISNULL(HYBBYS_ACTBALANSDEGERI, '') AS 'ACT_BALANS_DEGERI/@value',

															    '1' AS 'DREN_VARLIGI/@version',
																'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS 'DREN_VARLIGI/@codeSystemGuid',
																CASE WHEN HYBBYS_DRENVARLIGI = 1 THEN '1' ELSE '2' END AS 'DREN_VARLIGI/@code',
																CASE WHEN HYBBYS_DRENVARLIGI = 1 THEN 'EVET' ELSE 'HAYIR' END AS 'DREN_VARLIGI/@value',

															    '1' AS 'BESLENME_BILGISI/@version',
																'091d867a-ade7-4e4a-8b3a-4bc917b1f799' AS 'BESLENME_BILGISI/@codeSystemGuid',
																ISNULL(HYBBYS_BESLENMEBILGISI, 0) AS 'BESLENME_BILGISI/@code',
																ISNULL(BESLENMEADI, '')AS 'BESLENME_BILGISI/@value',


																'1' AS 'YOGUN_BAKIM_UNITESI_SEVIYESI/@version',
																'3c20a43e-74ee-4a04-9649-05359b5f9c5d' AS 'YOGUN_BAKIM_UNITESI_SEVIYESI/@codeSystemGuid',
																ISNULL(HYBBYS_YBUNITESISEVIYESI,'') AS 'YOGUN_BAKIM_UNITESI_SEVIYESI/@code',
																CASE HYBBYS_YBUNITESISEVIYESI
																WHEN 1 THEN '1. BASAMAK YOĞUN BAKIM'
																WHEN 2 THEN '2. BASAMAK YOĞUN BAKIM'	
																WHEN 3 THEN '3. BASAMAK YOĞUN BAKIM'												
																ELSE ''
																END AS 'YOGUN_BAKIM_UNITESI_SEVIYESI/@value',																
																ISNULL(HYBBYS_ISLEMREFERANSNUMARASI, '') AS 'ISLEM_REFERANS_NUMARASI/@value',
																ISNULL(HYBBYS_HYBBYSID, '') AS 'DEGERLENDIRME_REFERANS_NUMARASI/@value',

																(SELECT 
																	ISNULL(HYBBYS_APACHESKORU, 0) AS 'TOPLAM_APACHE_SKORU/@value',
																	ISNULL(HYBBYS_APACHEBEKLENENOLUMORANI, 0) AS 'BEKLENEN_OLUM_ORANI/@value',
																	ISNULL(HYBBYS_APACHEBEKLENENOLUMORANIDUZELTILMIS, ISNULL(HYBBYS_APACHEBEKLENENOLUMORANI, 0)) AS 'BEKLENEN_OLUM_ORANI_DUZELTILMIS/@value',
																	ISNULL(LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),HYBBYS_APACHEOLCUMZAMANI,120),' ',''),'-',''),':',''),12),'') AS 'OLCUM_ZAMANI/@value'
																	WHERE HYATIS_HASTATIPI = 1 
																FOR XML PATH('APACHE_II_SKOR_BILGISI'),TYPE),
																
																(SELECT 
																	HYBBYS_GLASKOWGOZLER AS 'GOZLER/@value',
																	HYBBYS_GLASKOWSOZEL AS 'SOZEL/@value',
																	HYBBYS_GLASKOWMOTOR AS 'MOTOR/@value'
																	WHERE HYATIS_HASTATIPI <> 3 																	
																FOR XML PATH('GLASGOW_SKALASI'),TYPE),
																(SELECT 
																	'' AS 'GOZLER/@value',
																	'' AS 'SOZEL/@value',
																	'' AS 'MOTOR/@value'
																	WHERE HYATIS_HASTATIPI = 3 																	
																FOR XML PATH('GLASGOW_SKALASI'),TYPE),

																(SELECT 
																	ISNULL(HYBBYS_SOFASKORU, 0) AS 'SOFA_SKORU/@value',
																	ISNULL(HYBBYS_SOFABEKLENENOLUMORANI, 0) AS 'BEKLENEN_OLUM_ORANI/@value',
																	ISNULL(LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),HYBBYS_SOFAOLCUMZAMANI,120),' ',''),'-',''),':',''),12),'') AS 'OLCUM_ZAMANI/@value'
																	--WHERE HYATIS_HASTATIPI = 2
																FOR XML PATH('SOFA_SKOR_BILGISI'),TYPE),
																(SELECT 
																    ISNULL(HYBBYS_SAPSIIBEKLENENOLUMORANI, 0) AS 'BEKLENEN_OLUM_ORANI/@value',
																	ISNULL(HYBBYS_SAPSIISKORU, 0) AS 'SAPS_II_SKORU/@value',
																	ISNULL(HYBBYS_SAPSIIGENISLETILMIS, 0) AS 'SAPS_II_GENISLETILMIS/@value',
																	ISNULL(LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),HYBBYS_SAPSIIOLCUMZAMANI,120),' ',''),'-',''),':',''),12),'') AS 'OLCUM_ZAMANI/@value'
																	--WHERE HYATIS_HASTATIPI = 2
																FOR XML PATH('SAPS_II_SKOR_BILGISI'),TYPE),
																(SELECT 
																    ISNULL(HYBBYS_TIDALVOLUMDEGERI, 0) AS 'TIDAL_VOLUM_DEGERI/@value',
																	ISNULL(HYBBYS_FREKANSDEGERI, 0) AS 'FREKANS_DEGERI/@value',
																	ISNULL(HYBBYS_TEPEBASINCDEGERI, 0) AS 'TEPE_BASINC_DEGERI/@value',
																	ISNULL(HYBBYS_FIOIIDEGERI, 0) AS 'FIO_II_DEGERI/@value',
																	ISNULL(HYBBYS_PEEPDEGERI, 0) AS 'PEEP_DEGERI/@value',
																	CASE WHEN ISNULL(HYBBYS_VENTILATORMODU,0) <> 0 THEN  '1' END AS 'VENTILATOR_MODU/@version',
																	CASE WHEN ISNULL(HYBBYS_VENTILATORMODU,0) <> 0 THEN '92c86bf9-b559-4b17-9bf9-0d86ea21e0b2'END AS 'VENTILATOR_MODU/@codeSystemGuid',
																	CASE WHEN ISNULL(HYBBYS_VENTILATORMODU,0) <> 0 THEN HYBBYS_VENTILATORMODU END AS 'VENTILATOR_MODU/@code',
																	CASE WHEN ISNULL(HYBBYS_VENTILATORMODU,0) <> 0 THEN VENTILATORMODUADI END AS 'VENTILATOR_MODU/@value'															
																FOR XML PATH('VENTILATOR_BILGISI'),TYPE),
																(SELECT 
																    ISNULL(HYBBYS_SANTRALVENOZBASINC, 0) AS 'SANTRAL_VENOZ_BASINC/@value',
																	ISNULL(HYBBYS_IBPSISTOLIK, 0) AS 'IBP_SISTOLIK/@value',
																	ISNULL(HYBBYS_IBPDIASTOLIK, 0) AS 'IBP_DIASTOLIK/@value',
																	ISNULL(HYBBYS_SPOIIDEGERI, 0) AS 'SPO_II_DEGERI/@value',
																	ISNULL(HYBBYS_NABIZ, 0) AS 'NABIZ/@value',
																	ISNULL(HYBBYS_DKB, 0) AS 'DIASTOLIK_KAN_BASINCI/@value',
																	ISNULL(HYBBYS_SKB, 0) AS 'SISTOLIK_KAN_BASINCI/@value',
																	ISNULL(HYBBYS_VUCUTISISI, 0) AS 'VUCUT_ISISI/@value'															
																FOR XML PATH('MONITOR_BILGISI'),TYPE),
																(SELECT 
																    ISNULL(HYBBYS_KANGAZIPH, 0) AS 'KAN_GAZI_PH/@value',
																	ISNULL(HYBBYS_KANGAZIPAOII, 0) AS 'KAN_GAZI_PAO_II/@value',
																	ISNULL(HYBBYS_KANGAZIPACOII, 0) AS 'KAN_GAZI_PACO_II/@value',
																	ISNULL(HYBBYS_KANGAZIHCOIII, 0) AS 'KAN_GAZI_HCO_III/@value',
																	ISNULL(HYBBYS_KANGAZILAKTAT, 0) AS 'KAN_GAZI_LAKTAT/@value',
																	ISNULL(HYBBYS_KANGAZIGLUKOZ, 0) AS 'KAN_GAZI_GLUKOZ/@value'															
																FOR XML PATH('KAN_GAZI_BILGISI'),TYPE),
																(SELECT 
																	CASE WHEN ISNULL(HYBBYS_KATATERBILGISI,0) <> 0  THEN '1' END AS 'KATATER_BILGISI/@version',
																	CASE WHEN ISNULL(HYBBYS_KATATERBILGISI,0) <> 0  THEN 'ff71372a-1718-44ab-a703-61b43d647af7' END AS 'KATATER_BILGISI/@codeSystemGuid',
																	CASE WHEN ISNULL(HYBBYS_KATATERBILGISI,0) <> 0 THEN HYBBYS_KATATERBILGISI END AS 'KATATER_BILGISI/@code',
																	CASE WHEN ISNULL(HYBBYS_KATATERBILGISI,0) <> 0 THEN KATATERBILGISIADI END AS 'KATATER_BILGISI/@value'															
																FOR XML PATH('KATATER_DURUM_BILGISI'),TYPE),
																(SELECT 
																	  '1'  AS 'VENTILASYON_YONTEMI/@version',
																	  '75fa2c8c-bbd7-4e8c-b771-ad37a88aef41'   AS 'VENTILASYON_YONTEMI/@codeSystemGuid',
																	 ISNULL(HYBBYS_VENTILASYONYONTEMI,0) AS 'VENTILASYON_YONTEMI/@code',
																	 ISNULL(VENTILASYONYONTEMIADI,'')   AS 'VENTILASYON_YONTEMI/@value',
																	 ISNULL(LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),HYBBYS_VENTILASYONBASLANGICZAMANI,120),' ',''),'-',''),':',''),12),'')   as 'VENTILASYON_BASLANGIC_ZAMANI/@value',
																	 ISNULL(LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),HYBBYS_VENTILASYONBITISZAMANI,120),' ',''),'-',''),':',''),12),'')  as'VENTILASYON_BITIS_ZAMANI/@value'																														
																     WHERE HYBBYS_VENTILASYONDURUMU =1
																FOR XML PATH('VENTILASYON_BILGISI'),TYPE)

																												
															FROM #TMPYOGUNBAKIM													
															WHERE HYBBYS_HYATISID = HYATIS_HYATISID
															FOR XML PATH('DEGERLENDIRME_BILGISI'),TYPE
														)
														
													FOR XML PATH('YOGUN_BAKIM_DEGERLENDIRME_BILGILERI'),TYPE									                
												)
						
												FOR XML PATH('recordData'), TYPE)
		   
					FROM #TMPHYATIS
					WHERE HYATIS_HYATISID = @CURYATISID
					FOR XML PATH(''), ROOT('SYSMessage')
	
	
								) AS GONDERILECEKXML,
								@HATA AS HATA, 
								@MESAJ AS MESAJ,
								@XMLBASLIK AS XMLBASLIK,
								@CURYATISID AS TABLOID,
								@HYBBYS 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,
									@CURYATISID AS TABLOID,
									@HYBBYS AS MINID,
									0 AS MAXID
			

						END CATCH				



		FETCH NEXT FROM CURYATIS INTO @CURYATISID 
		END -- CURYATIS son
	

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



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

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


	SELECT * FROM @GONDERIMTABLOSU


END

