IF NOT EXISTS (SELECT * FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.id = SYSCOLUMNS.id 
              WHERE SYSCOLUMNS.NAME = 'AKTARIMNOTLAR' AND SYSOBJECTS.name = 'HKIMLIK')



ALTER TABLE dbo.HKIMLIK ADD
	AKTARIMNOTLAR NVARCHAR(MAX) NULL

	GO

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


create view AKTARIM_VWHKIMLIK_PUSULA
AS

  SELECT ISNULL(EvAdresi,'')+' '+ ISNULL(IlceAdi,'')+' '+ISNULL(IlAdi,'') + ' '+ISNULL(UlkeAdi,'') MEDISOFT_ADRES,* FROM 
  (
	  Select /*(Select Notlar from dbo.Hasta where (case when Adi='Mustafa' and Soyadi='Türkyılmaz' and HastaId=326 then  304 else HastaId end)  =AktarimHastaId) OZGECMISNOTLAR,*/
	   MetCer.Hasta.ufns_GetHastaKurumTipi(NULL, SGKKurumId, OSSKurumId, AKKurumId) AS KurumTipi,
								 (SELECT        Adi
								   FROM            MetCer.Ortak.Ulke WITH (NOLOCK)
								   WHERE        (Id = HH.UlkeIdEv)) AS UlkeAdi,HH.UlkeIdEv UlkeIdEv_,
								 (SELECT        Adi
								   FROM            MetCer.Ortak.Il WITH (NOLOCK)
								   WHERE        (Id = HH.IlIdEv)) AS IlAdi,HH.IlIdEv IlIdEv_,
								 (SELECT        Adi
								   FROM            MetCer.Ortak.Ilce WITH (NOLOCK)
								   WHERE        (Id = HH.IlceIdEv)) AS IlceAdi,HH.IlceIdEv IlceIdEv_,case when hh.HastaTipiId=1 then 'Normal' when hh.HastaTipiId=2 then 'VIP' when hh.HastaTipiId=3 then 'Yabanci Hasta' end HastaTipiAdi,
  
	CASE WHEN HH.UlkeIdEv=1THEN'TR'  WHEN HH.UlkeIdEv=3THEN'DE' WHEN HH.UlkeIdEv=2THEN'KC' WHEN HH.UlkeIdEv=39THEN'NL'
		 WHEN HH.UlkeIdEv=66THEN'RO' WHEN HH.UlkeIdEv=40THEN'IQ' WHEN HH.UlkeIdEv=34THEN'FR' WHEN HH.UlkeIdEv=12THEN'AZ'
		 WHEN HH.UlkeIdEv=41THEN'IR' WHEN HH.UlkeIdEv=4THEN'GB' WHEN HH.UlkeIdEv=67THEN'RU' WHEN HH.UlkeIdEv=49THEN'QA'
		 WHEN HH.UlkeIdEv=11THEN'AT' WHEN HH.UlkeIdEv=14THEN'BE' WHEN HH.UlkeIdEv=7THEN'US' WHEN HH.UlkeIdEv=45THEN'CH'
		 WHEN HH.UlkeIdEv=62THEN'UZ'  WHEN HH.UlkeIdEv=10THEN'AU' WHEN HH.UlkeIdEv=18THEN'BG' WHEN HH.UlkeIdEv=79THEN'SA'
		 WHEN HH.UlkeIdEv=71THEN'TM' WHEN HH.UlkeIdEv=72THEN'UA' WHEN HH.UlkeIdEv=50THEN'KZ' WHEN HH.UlkeIdEv=23THEN'DK'
		 WHEN HH.UlkeIdEv=57THEN'MK' WHEN HH.UlkeIdEv=44THEN'SE' WHEN HH.UlkeIdEv=5THEN'US' WHEN HH.UlkeIdEv=42THEN'ES'
		 WHEN HH.UlkeIdEv=51THEN'KG' WHEN HH.UlkeIdEv=20THEN'DZ' WHEN HH.UlkeIdEv=24THEN'LY' WHEN HH.UlkeIdEv=218THEN''
		 WHEN HH.UlkeIdEv=32THEN'PS' WHEN HH.UlkeIdEv=36THEN'GE' WHEN HH.UlkeIdEv=73THEN'OM' WHEN HH.UlkeIdEv=69THEN'SY'
		 WHEN HH.UlkeIdEv=52THEN'KW' WHEN HH.UlkeIdEv=74THEN'JO' WHEN HH.UlkeIdEv=48THEN'CA' WHEN HH.UlkeIdEv=54THEN'LB'
		 WHEN HH.UlkeIdEv=61THEN'NO' WHEN HH.UlkeIdEv=70THEN'TJ' WHEN HH.UlkeIdEv=6THEN'AF' WHEN HH.UlkeIdEv=191THEN'CS'
		 WHEN HH.UlkeIdEv=33THEN'FI'  WHEN HH.UlkeIdEv=76THEN'YE' WHEN HH.UlkeIdEv=59THEN'EG' WHEN HH.UlkeIdEv=56THEN'HU'
		 WHEN HH.UlkeIdEv=25THEN'MA' WHEN HH.UlkeIdEv=208THEN'VE' WHEN HH.UlkeIdEv=77THEN'GR'  WHEN HH.UlkeIdEv=55THEN'LU'
		 WHEN HH.UlkeIdEv=63THEN'PK' WHEN HH.UlkeIdEv=9THEN'AL' WHEN HH.UlkeIdEv=15THEN'AE' WHEN HH.UlkeIdEv=46THEN'IT'
		 WHEN HH.UlkeIdEv=47THEN'JP' WHEN HH.UlkeIdEv=58THEN'MT' WHEN HH.UlkeIdEv=113THEN'SO' WHEN HH.UlkeIdEv=187THEN'PS'
		 WHEN HH.UlkeIdEv=29THEN'ID' WHEN HH.UlkeIdEv=112THEN'NG' WHEN HH.UlkeIdEv=43THEN'IL' WHEN HH.UlkeIdEv=28THEN'BY'
		 WHEN HH.UlkeIdEv=160THEN'IE' WHEN HH.UlkeIdEv=205THEN'UG' WHEN HH.UlkeIdEv=13THEN'BH' WHEN HH.UlkeIdEv=22THEN'CN'
		 WHEN HH.UlkeIdEv=16THEN'BO' ELSE NULL END Medisoft_UlkeKodu,
	 
		  dbo.AKTARIM_KIMLIK_NOTLAR(HH.Id) NOTLAR,
  
	  * from MetCer.Hasta.Hasta hh 
  ) AS T
/*
kimlik  kartına girişmiş notlar ve eski hasta özgeçmiş bilgileri burada, alan değerlerini toplayarak bizde uyarı notuna yazabiliriz!!
  
Select LEN(Aciklama),* from 	Ortak.[Not] where state>0 ORDER BY LEN(Aciklama)

SELECT name, database_id, create_date,*  FROM sys.databases ; 
*/
GO


IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AKTARIM_KIMLIK_NOTLAR]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].AKTARIM_KIMLIK_NOTLAR
go

create FUNCTION [dbo].[AKTARIM_KIMLIK_NOTLAR] (@HKIMLIKId as Integer)  
RETURNS nvarchar(MAX) AS  
BEGIN 
declare @NotListe as nvarchar(MAX),
	@KIMLIKNOT as nvarchar(MAX)
SET @NotListe = '' 
DECLARE KIMLIK_NOT_CURSOR CURSOR
  
for Select Aciklama from 	MetCer.Ortak.[Not](NOLOCK) where state>0 and NesneId=@HKIMLIKId
OPEN KIMLIK_NOT_CURSOR
FETCH NEXT FROM KIMLIK_NOT_CURSOR INTO @KIMLIKNOT
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @NotListe = @NotListe + @KIMLIKNOT + ', '
	FETCH NEXT FROM KIMLIK_NOT_CURSOR INTO @KIMLIKNOT
END
	IF CHARINDEX(',', ISNULL(@NotListe,' ')) <> 0
		SET @NotListe = LEFT(@NotListe, LEN(@NotListe)-1)
return @NotListe
END

GO


