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

CREATE PROCEDURE [dbo].[SPUSS_288_TRAFIK_KAZASI_SKORLAMA_KAYIT] 
	@HMUAYENEID INT=0,
	@TUMUNUTEKRAR BIT = 0,
	@TEKRAR BIT = 0
AS
BEGIN
   --SPUSS_288_TRAFIK_KAZASI_SKORLAMA_KAYIT 115226,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..#TMPUSS288') IS NOT NULL 
	DROP TABLE #TMPUSS288
    
	SELECT 
      USS_288TRAFIKKAZASIHSTSKORLAMA.HMUAYENEID AS USS_288TRAFIKKAZASIHSTSKORLAMA_HMUAYENEID,
 
 TRIAJ_KODU AS USS_288TRAFIKKAZASIHSTSKORLAMA_TRIAJKODU,
 CASE
    WHEN TRIAJ_KODU = 1 THEN 'YEŞİL'
    WHEN TRIAJ_KODU = 2 THEN 'SARI'
    WHEN TRIAJ_KODU = 3 THEN 'KIRMIZI'
 END USS_288TRAFIKKAZASIHSTSKORLAMA_TRIAJKODU_ADI,

 HASTAEXDUHUL AS USS_288TRAFIKKAZASIHSTSKORLAMA_HASTAEXDUHUL,
 CASE
    WHEN HASTAEXDUHUL = 1 THEN 'EVET'
    WHEN HASTAEXDUHUL = 2 THEN 'HAYIR'
 END USS_288TRAFIKKAZASIHSTSKORLAMA_HASTAEXDUHUL_ADI,
 
 KAZA_GLASGOW_KOMA_SKALASI AS USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_GLASGOW_KOMA_SKALASI,
 CASE
    WHEN KAZA_GLASGOW_KOMA_SKALASI = 1 THEN '15'
    WHEN KAZA_GLASGOW_KOMA_SKALASI = 2 THEN '13-14'
    WHEN KAZA_GLASGOW_KOMA_SKALASI = 3 THEN '9-12'
    WHEN KAZA_GLASGOW_KOMA_SKALASI = 4 THEN '<= 8'
 END USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_GLASGOW_KOMA_SKALASI_ADI,

 KAZA_SISTOLIK_KAN_BASINCI AS USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_SISTOLIK_KAN_BASINCI,
 CASE
    WHEN KAZA_SISTOLIK_KAN_BASINCI = 1 THEN '>= 90'
    WHEN KAZA_SISTOLIK_KAN_BASINCI = 2 THEN '76-89'
    WHEN KAZA_SISTOLIK_KAN_BASINCI = 3 THEN '50-75'
    WHEN KAZA_SISTOLIK_KAN_BASINCI = 4 THEN '<= 49'
 END USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_SISTOLIK_KAN_BASINCI_ADI,

 KAZA_SOLUNUM_SAYISI AS USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_SOLUNUM_SAYISI,
 CASE
    WHEN KAZA_SOLUNUM_SAYISI = 1 THEN '10-25'
    WHEN KAZA_SOLUNUM_SAYISI = 2 THEN '>= 26'
    WHEN KAZA_SOLUNUM_SAYISI = 3 THEN '<= 9'
 END USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_SOLUNUM_SAYISI_ADI,

 KAZA_PRIMER_SONLANIM AS USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_PRIMER_SONLANIM,
 CASE
    WHEN KAZA_PRIMER_SONLANIM = 1 THEN 'TABURCU'
    WHEN KAZA_PRIMER_SONLANIM = 2 THEN 'KLİNİK YATIŞ'
    WHEN KAZA_PRIMER_SONLANIM = 3 THEN 'YOĞUN BAKIM YATIŞ'
 END USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_PRIMER_SONLANIM_ADI,

 USS_288TRAFIKKAZASIHSTSKORLAMA.KAZA_TOPLAM_SKOR AS USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_TOPLAM_SKOR,
 --ISNULL((SELECT TOP 1 VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='Saglik-Net' AND PROGNAME='MEDISOFT' AND KEYNAME= CONVERT(VARCHAR,VWMUAYENE.SUBEID)+' numaralı şubenin kurum numarası'),'') AS SAGLIKNETKURUMKODU,
 	    ISNULL((SELECT TOP 1 VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='Saglik-Net' AND PROGNAME='MEDISOFT' AND KEYNAME= CONVERT(VARCHAR,VWMUAYENE.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,VWMUAYENE.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,VWMUAYENE.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,VWMUAYENE.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,VWMUAYENE.SUBEID)+' numarali subede Hasta kayıt gönderiminde referans no ön Karakteri'),'') AS REFNOONEK,

	VWMUAYENE.SNOTAKIPNO AS HDOSYA_SNOTAKIPNO,
	VWMUAYENE.SUBEID AS HDOSYA_SUBEID,
	SUBELER.SUBEADI AS SUBELER_SUBEADI,	
	VWMUAYENE.HMUAYENEID AS  HMUAYENE_HMUAYENEID

	INTO #TMPUSS288
	FROM USS_288TRAFIKKAZASIHSTSKORLAMA
		INNER JOIN VWMUAYENE ON USS_288TRAFIKKAZASIHSTSKORLAMA.HMUAYENEID=VWMUAYENE.HMUAYENEID
		LEFT JOIN SUBELER WITH(NOLOCK) ON SUBELER.SUBEID = VWMUAYENE.SUBEID
		LEFT JOIN TBOLUM TBOLUMIST WITH(NOLOCK) ON VWMUAYENE.ISTBOLUMID = TBOLUMIST.BOLUMID    
		LEFT JOIN TKLINIK TKLINIKIST WITH(NOLOCK) ON TBOLUMIST.KLINIKKODU = TKLINIKIST.KLINIKKODU 

	WHERE  VWMUAYENE.HMUAYENEID = @HMUAYENEID
	  AND (@TUMUNUTEKRAR=1 OR @TUMUNUTEKRAR=0)



	IF (SELECT COUNT(*) FROM #TMPUSS288) = 0 
	BEGIN
		SET @SAYIHATA=1
		SET @MESAJ= @MESAJ + 'Muayene ait uss 288 mesaj kaydı bulunamadı! '
	END

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

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


	IF @HATA='' AND @SAYIHATA=0
	BEGIN		
		SELECT @SUBEID = HDOSYA_SUBEID FROM #TMPUSS288
		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

					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',
							   '288' as 'messageType/@code',
							   'Trafik Kazası Yaralı Skorlama' 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
													ISNULL(HDOSYA_SNOTAKIPNO,'') AS 'SYSTakipNo/@value'
												FOR XML PATH('HASTA_TAKIP_BILGISI'), TYPE
											),
											/***/	
												(
													SELECT 																
														
														-- 1. GLASGOW KOMA SKALASI
														(SELECT 
															'1' AS '@version',
															'ad3237c9-ac34-4dc9-8c3e-8ed56fef2527' AS '@codeSystemGuid',
															USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_GLASGOW_KOMA_SKALASI AS '@code',
															USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_GLASGOW_KOMA_SKALASI_ADI AS '@value'
														 WHERE USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_GLASGOW_KOMA_SKALASI IS NOT NULL
														 FOR XML PATH('KAZA_GLASGOW_KOMA_SKALASI'), TYPE),

														-- 2. SISTOLIK KAN BASINCI
														(SELECT 
															'1' AS '@version',
															'63c8fb29-c0b8-4691-a783-09c824ed61ac' AS '@codeSystemGuid',
															USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_SISTOLIK_KAN_BASINCI AS '@code',
															USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_SISTOLIK_KAN_BASINCI_ADI AS '@value'
														 WHERE USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_SISTOLIK_KAN_BASINCI IS NOT NULL
														 FOR XML PATH('KAZA_SISTOLIK_KAN_BASINCI'), TYPE),

														-- 3. SOLUNUM SAYISI
														(SELECT 
															'1' AS '@version',
															'463dcbea-1fbb-4b73-af87-33afeb1b9784' AS '@codeSystemGuid',
															USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_SOLUNUM_SAYISI AS '@code',
															USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_SOLUNUM_SAYISI_ADI AS '@value'
														 WHERE USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_SOLUNUM_SAYISI IS NOT NULL
														 FOR XML PATH('KAZA_SOLUNUM_SAYISI'), TYPE),

														-- 4. PRIMER SONLANIM
														(SELECT 
															'1' AS '@version',
															'f3903bda-eb58-4448-a86c-e0cd85b429b2' AS '@codeSystemGuid',
															USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_PRIMER_SONLANIM AS '@code',
															USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_PRIMER_SONLANIM_ADI AS '@value'
														 WHERE USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_PRIMER_SONLANIM IS NOT NULL
														 FOR XML PATH('KAZA_PRIMER_SONLANIM'), TYPE),

														-- 5. TRIAJ
														(SELECT 
															'1' AS '@version',
															'1ddcbef5-4006-41fe-87c0-6190c9801708' AS '@codeSystemGuid',
															USS_288TRAFIKKAZASIHSTSKORLAMA_TRIAJKODU AS '@code',
															USS_288TRAFIKKAZASIHSTSKORLAMA_TRIAJKODU_ADI AS '@value'
														 WHERE USS_288TRAFIKKAZASIHSTSKORLAMA_TRIAJKODU IS NOT NULL
														 FOR XML PATH('TRIAJ'), TYPE),

														-- 6. EX DUHUL MU (Case yapısını koruyarak)
														(SELECT 
															'1' AS '@version',
															'c9d56fee-d143-4602-ad7b-ba131ef92ad9' AS '@codeSystemGuid',
															CASE WHEN USS_288TRAFIKKAZASIHSTSKORLAMA_HASTAEXDUHUL = 1 THEN 1 ELSE 2 END AS '@code',
															CASE WHEN USS_288TRAFIKKAZASIHSTSKORLAMA_HASTAEXDUHUL = 1 THEN 'EVET' ELSE 'HAYIR' END AS '@value'
														 WHERE USS_288TRAFIKKAZASIHSTSKORLAMA_HASTAEXDUHUL IS NOT NULL
														 FOR XML PATH('KAZA_EX_DUHUL_MU'), TYPE),

														-- 7. TOPLAM SKOR (Bağımsız element olduğu için basit NULL kontrolü)
														(SELECT 
															USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_TOPLAM_SKOR AS '@value'
														 WHERE USS_288TRAFIKKAZASIHSTSKORLAMA_KAZA_TOPLAM_SKOR IS NOT NULL
														 FOR XML PATH('KAZA_TOPLAM_SKOR'), TYPE)							
													
									     
													FOR XML PATH('TRAFIK_KAZASI_YARALI_SKORLAMA'),TYPE

											 )
										/***/
											  FOR XML PATH('recordData'), TYPE
										)
		   
				      FROM #TMPUSS288
				   WHERE HMUAYENE_HMUAYENEID = HMUAYENE_HMUAYENEID

				FOR XML PATH(''), ROOT('SYSMessage')
	
	
							) AS goNDERILECEKXML,
							@HATA AS HATA, 
							@MESAJ AS MESAJ,
							@XMLBASLIK AS XMLBASLIK,
							@HMUAYENEID 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,
								@HMUAYENEID 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



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

	SELECT * FROM @goNDERIMTABLOSU

END
