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

CREATE PROCEDURE [dbo].[SPWEB_HASTALOGIN]
@username nvarchar(20), @password  nvarchar(20), @ip nvarchar(20)
AS
BEGIN
   DECLARE 
      @KayitSayi int,
      @HastaWebsifre nvarchar(10),
      @HastaCep nvarchar(20),
      @HastaWebKullan bit, 
      @HastaWebAktif bit,
	  @userid int=0,
	  @sonucmesaj nvarchar(200)=''
      
    SET @KayitSayi = 0

	if (len(isnull(@username,''))>5 and isnull(@password,'')<>'')
	BEGIN
	
		SET @KayitSayi=(SELECT COUNT(HKIMLIKID) FROM HKIMLIK WITH(NOLOCK) WHERE HTCKIMLIKNO = @username AND HWEBSIFRE = @password)
		IF @KayitSayi=1
		BEGIN
			SELECT @userid = HKIMLIKID, @HastaWebsifre = HWEBSIFRE, @HastaCep = HCEP, @HastaWebKullan = HWEBKULLAN, @HastaWebAktif = HWEBAKTIF 
			FROM HKIMLIK WITH(NOLOCK) WHERE HTCKIMLIKNO = @username AND HWEBSIFRE = @password
		END
	
		IF @KayitSayi=0
		BEGIN
			SET @KayitSayi=(SELECT COUNT(HKIMLIKID) FROM HKIMLIK WITH(NOLOCK) WHERE HCEP = @username AND HWEBSIFRE = @password)
		
			IF @KayitSayi=1
			BEGIN
				SELECT @userid = HKIMLIKID, @HastaWebsifre = HWEBSIFRE, @HastaCep = HCEP, @HastaWebKullan = HWEBKULLAN, @HastaWebAktif = HWEBAKTIF 
				FROM HKIMLIK WITH(NOLOCK) WHERE HCEP = @username AND HWEBSIFRE = @password
			END
		END

		IF @KayitSayi=0
		BEGIN
			SET @KayitSayi=(SELECT COUNT(HKIMLIKID) FROM HKIMLIK WITH(NOLOCK) WHERE RIGHT(REPLACE(HCEP,' ',''),10) = RIGHT(REPLACE(@username,' ',''),10) AND HWEBSIFRE = @password)

			IF @KayitSayi=1
			BEGIN
				SELECT  @userid = HKIMLIKID, @HastaWebsifre = HWEBSIFRE, @HastaCep = HCEP, @HastaWebKullan = HWEBKULLAN, @HastaWebAktif = HWEBAKTIF 
				FROM HKIMLIK WITH(NOLOCK) WHERE RIGHT(REPLACE(HCEP,' ',''),10) = RIGHT(REPLACE(@username,' ',''),10) AND HWEBSIFRE = @password
			END
		END

		IF @KayitSayi=0 AND ISNULL((SELECT ISNULL(VALUESTR,'') FROM CONFIG WHERE PROGNAME='MEDISOFT' AND SECTIONSTR='Sistem' AND KEYNAME = 'Uluslar arası kullanım aktif'),'')='1'
		BEGIN
			SET @KayitSayi=(SELECT COUNT(HKIMLIKID) FROM HKIMLIK WITH(NOLOCK) WHERE RIGHT(REPLACE(HCEP,' ',''),9) = RIGHT(REPLACE(@username,' ',''),9) AND HWEBSIFRE = @password)

			IF @KayitSayi=1
			BEGIN
				SELECT  @userid = HKIMLIKID, @HastaWebsifre = HWEBSIFRE, @HastaCep = HCEP, @HastaWebKullan = HWEBKULLAN, @HastaWebAktif = HWEBAKTIF 
				FROM HKIMLIK WITH(NOLOCK) WHERE RIGHT(REPLACE(HCEP,' ',''),9) = RIGHT(REPLACE(@username,' ',''),9) AND HWEBSIFRE = @password
			END
		END

		IF @KayitSayi=0
		BEGIN
			SET @KayitSayi=(SELECT COUNT(HKIMLIKID) FROM HKIMLIK WITH(NOLOCK) WHERE HPASAPORT = @username AND HWEBSIFRE = @password)

			IF @KayitSayi=1
			BEGIN
				SELECT  @userid = HKIMLIKID, @HastaWebsifre = HWEBSIFRE, @HastaCep = HCEP, @HastaWebKullan = HWEBKULLAN, @HastaWebAktif = HWEBAKTIF 
				FROM HKIMLIK WITH(NOLOCK) WHERE HPASAPORT = @username AND HWEBSIFRE = @password
			END
		END

		IF @KayitSayi = 1   
		BEGIN
			IF ((ISNULL(@HastaWebKullan, 0) = 0) OR (ISNULL(@HastaWebAktif, 0) = 0))
			BEGIN
				SELECT TOP 1 '' as id, 'User' as role, null as adi, null as soyadi, null as cep, null as email, null as songiristarihi, 0 as sifredegismeli, 4 as sonuc FROM HKIMLIK WITH(NOLOCK)
				set @sonucmesaj = 'Login denemesi hasta web kullanımı pasif olduğundan reddedildi'			
			END
			ELSE
			BEGIN
				UPDATE HKIMLIK SET HWEBSONGIRISTARIHI= GETDATE() WHERE HKIMLIKID=@userid
				UPDATE HKIMLIK SET HWEBSIFRETARIHI= GETDATE() WHERE HKIMLIKID=@userid AND HWEBSIFRETARIHI IS NULL AND LEN(HWEBSIFRE)>7
				SELECT DBO.FN_BASIT_ID_SIFRELE(HKIMLIKID) AS id, 'User' as role, HADI AS adi, HSOYADI AS soyadi, HCEP AS cep, HEMAIL AS email, HWEBSONGIRISTARIHI AS songiristarihi, 
						(CASE WHEN ISNULL(HWEBSIFRETARIHI,DATEADD(MONTH,-14,GETDATE()))<DATEADD(MONTH,-12,GETDATE()) THEN 1 ELSE 0 END) AS sifredegismeli, 1 AS sonuc 
				FROM HKIMLIK WITH(NOLOCK) WHERE HKIMLIKID=@userid
				set @sonucmesaj = 'Webden başarılı login'			
			END
		END
		ELSE IF @KayitSayi > 1
		BEGIN
				SELECT TOP 1 '' as id, 'User' as role, null as adi, null as soyadi, null as cep, null as email, null as songiristarihi, 0 as sifredegismeli, 5 as sonuc FROM HKIMLIK WITH(NOLOCK)
				set @sonucmesaj = @username +'bilgisine karşılık gelen birden fazla kişi kayıtlı'			
		END
		ELSE IF @KayitSayi = 0
		BEGIN
				SELECT TOP 1 '' as id, 'User' as role, null as adi, null as soyadi, null as cep, null as email, null as songiristarihi, 0 as sifredegismeli, 6 as sonuc FROM HKIMLIK WITH(NOLOCK)			
				set @sonucmesaj = 'Kullanıcı adı:'+@username+' Şifre:'+@password+' bilgileri ile başarısız login denemesi'
		END
	END
	ELSE
	BEGIN
		SELECT TOP 1 '' as id, 'User' as role, null as adi, null as soyadi, null as cep, null as email, null as songiristarihi, 0 as sifredegismeli, 6 as sonuc FROM HKIMLIK WITH(NOLOCK)
		set @sonucmesaj = 'Bilgiler eksik'
	END


	IF @sonucmesaj<>''
		EXEC SPWEB_LOGLA 'login',@userid,@sonucmesaj,@ip

END
GO

IF EXISTS (SELECT [name] FROM [sys].[database_principals] WHERE [type] = N'S' AND [name] = N'mediwebuser')
BEGIN
	BEGIN TRY
		GRANT EXECUTE ON [dbo].[SPWEB_HASTALOGIN] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO


