IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AKTARIM_TETKIK_PUSULA]') AND OBJECTPROPERTY(id,N'IsVIew') = 1)
BEGIN
    INSERT INTO SCRIPT_BACKUPS (SCRIPTNAME,SCRIPT,SCRIPTDATE) SELECT 'AKTARIM_TETKIK_PUSULA' SCRIPTNAME,OBJECT_DEFINITION(OBJECT_ID('AKTARIM_TETKIK_PUSULA')) AS SCRIPT,GETDATE() AS SCRIPTDATE
    DROP VIEW AKTARIM_TETKIK_PUSULA
END
go


CREATE VIEW AKTARIM_TETKIK_PUSULA
AS
SELECT 'LAB' TUR,PIT.ProtokolId,PIT.Id ProtokolIslemId,IstekTarihi,IslemTarihi,OH.Adi HIZMETADI,CASE WHEN LT.HizmetId IS  NULL THEN (SPACE(5)+LT.Adi)  ELSE LT.Adi END AltTestKodu	 

	
		,CAST(REPLACE(LTI.SonucSayisal, ',', '.') as varchar(15)) TetkikSonucuSayisal
		,SUBSTRING(LTI.SonucText, 0,4000) TetkikSonucuMetin
		,LTI.TestSonucBirim TetkikSonucuBirimi
		
		/*,CAST((SELECT TOP 1 SinirAlt FROM [LIS].[TestNormalDeger] LTND (NOLOCK) WHERE LTND.TestId = LT.Id) as varchar(15)) TetkikSonucuBaslangicReferans
		,CAST((SELECT TOP 1 SinirUst FROM [LIS].[TestNormalDeger] LTND (NOLOCK) WHERE LTND.TestId = LT.Id) as varchar(15)) TetkikSonucuBitisReferans*/
		,ReferansDeger,KritikDeger,PanikDeger,NULL Rapor,NULL RaporRtf,LTI.OnayTarihi,NUMUNEISLEMID,LT.HizmetId ,Sira,SiraNo,CASE WHEN LT.HizmetId IS  NULL THEN 'ALTTEST'  ELSE 'TEST' END TESTTUR	

	FROM Metcer.Ortak.Aktivite OA(NOLOCK)
	INNER JOIN Metcer.LIS.TestIslem LTI(NOLOCK) ON OA.Id = LTI.AktiviteId
	INNER JOIN Metcer.LIS.Test LT (NOLOCK) ON LT.Id = LTI.TestId

	INNER JOIN Metcer.Hasta.ProtokolIslem PIT(NOLOCK) ON PIT.Id = LTI.ProtokolIslemId
	INNER JOIN Metcer.ortak.hizmet OH(NOLOCK) ON OH.Id = PIT.HizmetId
	WHERE OA.STATE <> 0
		AND PIT.STATE > 1
		AND LTI.STATE = 6 --onay
		--AND OA.ProtokolId = 11970
		
UNION ALL

Select 'RAD' TUR, PIT.ProtokolId,PIT.Id,IstekTarihi,PIT.IslemTarihi,OH.Adi HIZMETADI, OH.Adi,NULL TETKIKSONUCSAYISAL, NULL  TETKIKSONUCSAYISAL, NULL TETKIKSONUCUBIRIMI,NULL REFERANSDEGER,NULL KRITIKDEGER,NULL PANIKDEGER,Rapor,  RaporRtf, RTI.OnaylanmaTarihi
,9999999,PIT.HizmetId ,9999999,SiraNo,'TEST' TESTTUR
 from Metcer.Ortak.Aktivite OA(NOLOCK) 
	INNER JOIN	Metcer.RIS.TetkikIslem RTI (NOLOCK) ON OA.Id = RTI.AktiviteId
	INNER JOIN Metcer.Hasta.ProtokolIslem PIT(NOLOCK) ON PIT.Id = RTI.ProtokolIslemId
	INNER JOIN Metcer.ortak.hizmet OH(NOLOCK) ON OH.Id = PIT.HizmetId
	WHERE OA.STATE <> 0 AND PIT.STATE > 1
	AND RTI.STATE = 6 --onay




--SELECT * FROM AKTARIM_TETKIK_PUSULA ORDER BY TUR DESC, NUMUNEISLEMID,HizmetId desc,Sira,SiraNo





	

