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

CREATE VIEW  ENT_YBBYS_KONSULTASYON_LISTESI
AS
SELECT SUBEID, HKIMLIKID [KimlikEntegreReferansKodu],HDOSYAID [HbysDosyaId],ITEMADI ISLEMADI,HMTARIH MUAYENETARIHI,LBOLUM BOLUM,LDOKTOR DOKTOR,
        CASE WHEN ISNULL(HMACIKLAMA,'')<>'' THEN '[Açıklama]'+char(13)+HMACIKLAMA+char(13)+char(13)  ELSE '' END+   
		CASE WHEN ISNULL(CAST(HMSIKAYETI AS nvarchar(MAX)),'')<>'' THEN '[Yakınması]'+char(13)+CAST(HMSIKAYETI AS nvarchar(MAX))+char(13)+char(13)  ELSE '' END+   
		CASE WHEN ISNULL(CAST(HMHKIKAYESI AS nvarchar(MAX)),'')<>'' THEN '[Öyküsü]'+char(13)+CAST(HMHKIKAYESI  AS nvarchar(MAX))+char(13)+char(13)  ELSE '' END+   
		CASE WHEN ISNULL(CAST(HMMUAYENE  AS nvarchar(MAX)) ,'')<>'' THEN '[Fiziki Bakı]'+char(13)+CAST(HMMUAYENE AS nvarchar(MAX))+char(13)+char(13)  ELSE '' END+   
		CASE WHEN ISNULL(CAST(HMLABNOT  AS nvarchar(MAX)),'')<>'' THEN '[Lab Not]'+char(13)+CAST(HMLABNOT AS nvarchar(MAX))+char(13)+char(13)  ELSE '' END+   
		CASE WHEN ISNULL(CAST(HMRADNOT  AS nvarchar(MAX)),'')<>'' THEN '[Rad No]'+char(13)+CAST(HMRADNOT AS nvarchar(MAX))+char(13)+char(13)  ELSE '' END+  
		CASE WHEN ISNULL(CAST(HMTANI  AS nvarchar(MAX)),'')<>'' THEN '[Tanı]'+char(13)+CAST(HMTANI AS nvarchar(MAX))+char(13)+char(13)  ELSE '' END+  
		CASE WHEN ISNULL(CAST(HMAMELNOT AS nvarchar(MAX)),'')<>'' THEN '[Müdahele/Ameliyat Not]'+char(13)+CAST(HMAMELNOT AS nvarchar(MAX))+char(13)+char(13)  ELSE '' END+  
		CASE WHEN ISNULL(CAST(HMTEDAVI AS nvarchar(MAX)),'')<>'' THEN '[Tedavi]'+char(13)+CAST(HMTEDAVI AS nvarchar(MAX))+char(13)+char(13)  ELSE '' END+  
		CASE WHEN ISNULL(CAST(HMONERI AS nvarchar(MAX)),'')<>'' THEN '[Öneri]'+char(13)+CAST(HMONERI AS nvarchar(MAX))+char(13)+char(13)  ELSE '' END+  
		CASE WHEN ISNULL(CAST(HMSONUC AS nvarchar(MAX)),'')<>'' THEN '[Sonuç]'+char(13)+CAST(HMSONUC AS nvarchar(MAX))+char(13)+char(13)  ELSE '' END+   
		CASE WHEN ISNULL(CAST(HMNOTLAR  AS nvarchar(MAX)),'')<>'' THEN '[Ek Not]'+char(13)+CAST(HMNOTLAR AS nvarchar(MAX))+char(13)+char(13)  ELSE '' END +   
		CASE WHEN ISNULL(dbo.HMUAYENE_ICD_LISTE(HMUAYENEID),'')<>'' THEN '[Icd Kod]'+char(13)+dbo.HMUAYENE_ICD_LISTE(HMUAYENEID)+char(13)+char(13)  ELSE '' END  MUAYENEBILGILERI,  
		 dbo.HMUAYENE_ICD_LISTE(HMUAYENEID) AS TANILAR2,ACIKLAMA KONS_ISTEM_ACIKLAMA, 
       HMSIKAYETI,HMHKIKAYESI,HMMUAYENE,HMLABNOT,HMRADNOT,HMAMELNOT,HMTANI,HMTEDAVI,HMKLINIKIZLEM,HMACIKLAMA,HMSONUC,HMNOTLAR,HMONERI
FROM [VWMUAYENE] WITH(NOLOCK) 
 WHERE  ISNULL(HESAPISLIPTAL,0)=0 AND MUAYENETIPIID=6 AND HDIPTAL=0 
 
 --HKIMLIKID=730968


--SELECT TOP 10000  HMUAYENEID,HESAPISLID FROM [VWMUAYENEISL] WHERE MUAYENETIPIID=6 AND SUBEID=1 ORDER BY ITARIH DESC 
