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=0,
	  @LoginTip int=0,
      @HastaWebsifre nvarchar(10),
      @HastaCep nvarchar(20),
      @HastaWebKullan bit, 
      @HastaWebAktif bit,
	  @userid int=0,
	  @sonucmesaj nvarchar(200)='',
	  @hata nvarchar(200)='',
	  @SmsOnayKoduDogru BIT=0
	  
	 
      
    SET @KayitSayi = 0

	SET @username=REPLACE(@username,' ','')
	SET @password=REPLACE(@password,' ','')

	if (len(isnull(@username,''))<7 OR isnull(@password,'')='')
	BEGIN
		SET @hata='Bilgiler uyu'+CHAR(254)+'muyor';
	END


	IF (LEN(ISNULL(@username,''))=11)
	BEGIN
		IF ISNULL((SELECT ISNULL(VALUESTR,'') FROM CONFIG WHERE PROGNAME='MEDISOFT' AND SECTIONSTR='Sistem' AND KEYNAME = 'Uluslar aras'+char(253)+' kullan'+char(253)+'m aktif'),'')<>'1'
		BEGIN
			IF (dbo.TcKimlikNoGecerli(@username)=1)
			BEGIN
				SET @KayitSayi = (SELECT COUNT(HKIMLIKID) FROM HKIMLIK WITH(NOLOCK) WHERE HTCKIMLIKNO=@username)
				IF @KayitSayi = 1 
				BEGIN
					SET @LoginTip=1
				END
			END
		END
	END

	IF @KayitSayi=0
	BEGIN
		SET @KayitSayi = (SELECT COUNT(HKIMLIKID) FROM HKIMLIK WITH(NOLOCK) WHERE HPASAPORT=@username)
		IF @KayitSayi = 1 SET @LoginTip=2
	END

	IF @KayitSayi=0
	BEGIN
		SET @KayitSayi = (SELECT COUNT(HKIMLIKID) FROM HKIMLIK WITH(NOLOCK) WHERE RIGHT(HCEP,10)=RIGHT(@username,10))
		IF @KayitSayi = 1 SET @LoginTip=3
	END

	IF @KayitSayi=0 AND ISNULL((SELECT ISNULL(VALUESTR,'') FROM CONFIG WHERE PROGNAME='MEDISOFT' AND SECTIONSTR='Sistem' AND KEYNAME = 'Uluslar aras'+char(253)+' kullan'+char(253)+'m aktif'),'')='1'
	BEGIN
		SET @KayitSayi = (SELECT COUNT(HKIMLIKID) FROM HKIMLIK WITH(NOLOCK) WHERE RIGHT(HCEP,9)=RIGHT(@username,9))
		IF @KayitSayi = 1 SET @LoginTip=4
	END

	IF @KayitSayi>1 
	BEGIN
		SET @hata='Bilgiler uyu'+CHAR(254)+'muyor';
	END

	IF @hata='' AND ISNULL((SELECT TOP 1 ISNULL(SMSLOGIN,0) FROM MEDIWEBAYARLAR),0)=1
	BEGIN
		SET @HastaCep = ISNULL(	(SELECT ISNULL(HCEP,'') 
								FROM HKIMLIK WITH(NOLOCK) 
								WHERE	(@LoginTip=1 AND HTCKIMLIKNO=@username) 
									OR	(@LoginTip=2 AND HPASAPORT=@username)
									OR	(@LoginTip=3 AND  RIGHT(HCEP,10)=RIGHT(@username,10))
									OR	(@LoginTip=4 AND  RIGHT(HCEP,9)=RIGHT(@username,9))
								),'')
		SET @HastaCep=REPLACE(@HastaCep,' ','')
		IF LEN(@HastaCep)<9
			SET @hata='Bilgiler uyu'+CHAR(254)+'muyor';

		IF @hata=''
		BEGIN
			IF ISNULL((SELECT COUNT(*) FROM HWEBONAYKOD WHERE HCEP=RIGHT(@HastaCep,10) AND ONAYKODU=@password AND KAYITTARIHI>DATEADD(MINUTE,-3,GETDATE())),0)=1
			BEGIN
				SET @SmsOnayKoduDogru=1
			END
			ELSE
			BEGIN
				SET @hata='Onay kodu hatal'+char(253);
			END
		END
	END

	IF @hata=''
	BEGIN
		SELECT @userid = HKIMLIKID, @HastaWebsifre = HWEBSIFRE, @HastaCep = HCEP, @HastaWebKullan = HWEBKULLAN, @HastaWebAktif = HWEBAKTIF 
		FROM HKIMLIK WITH(NOLOCK) WHERE (		(@LoginTip=1 AND HTCKIMLIKNO=@username) 
											OR	(@LoginTip=2 AND HPASAPORT=@username)
											OR	(@LoginTip=3 AND  RIGHT(HCEP,10)=RIGHT(@username,10))
											OR	(@LoginTip=4 AND  RIGHT(HCEP,9)=RIGHT(@username,9))
										) 
									AND (@SmsOnayKoduDogru=1 OR  HWEBSIFRE = @password)
	END
	   	  

	IF @KayitSayi = 1 AND @hata=''   
	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 = 'Bilgiler uyu'+CHAR(254)+'muyor'			
		END
		ELSE
		BEGIN
			UPDATE HKIMLIK SET HWEBSONGIRISTARIHI= GETDATE() WHERE HKIMLIKID=@userid
			IF @SmsOnayKoduDogru=0 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()) AND @SmsOnayKoduDogru=0 THEN 1 ELSE 0 END) AS sifredegismeli, 1 AS sonuc 
			FROM HKIMLIK WITH(NOLOCK) WHERE HKIMLIKID=@userid
			set @sonucmesaj = 'Webden ba'+char(254)+'ar'+char(253)+'l'+char(253)+' 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 = 'Birden '+char(231)+'ok e'+char(254)+'le'+char(254)+'me'			
	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 = 'Basarisiz Login ' + @hata +' '+@username+' '+@password
	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


