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

CREATE PROCEDURE [dbo].[SPWEB_KURUMLOGIN]
@username nvarchar(20), @password  nvarchar(20), @ip nvarchar(20)
AS
BEGIN
   DECLARE 
      @KayitSayi int,
      @HastaWebsifre nvarchar(10),
	  @userid int=0,
	  @sonucmesaj nvarchar(200)='',
	  @hata nvarchar(200)='',
	  @SmsOnayKoduDogru BIT=0,
	  @KurumCep nvarchar(200)=''

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

	IF ISNULL((SELECT TOP 1 ISNULL(KURUMLOGINAKTIF,0) FROM MEDIWEBAYARLAR),0)=0
	BEGIN
		SET @hata='Bilgiler uyu'+CHAR(254)+'muyor';
	END

      
    SET @KayitSayi = 0

	IF @hata=''
	BEGIN
		SET @KayitSayi=ISNULL((SELECT COUNT(CARIID) FROM CARI WITH(NOLOCK) WHERE CARIID = @username),0)
		IF @KayitSayi<>1 SET @hata='Bilgiler uyu'+CHAR(254)+'muyor';
	END


	IF @hata='' AND ISNULL((SELECT TOP 1 ISNULL(KURUMSMSLOGIN,0) FROM MEDIWEBAYARLAR),0)=1
	BEGIN
		SET @KurumCep = ISNULL(	(SELECT TEL1 FROM CARI WITH(NOLOCK) WHERE CARIID = @username),'')
		SET @KurumCep=REPLACE(@KurumCep,' ','')
		IF LEN(@KurumCep)<9
			SET @hata='Bilgiler uyu'+CHAR(254)+'muyor';

		IF @hata=''
		BEGIN
			IF ISNULL((SELECT COUNT(*) FROM HWEBONAYKOD WHERE HCEP=RIGHT(@KurumCep,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

	SET @KayitSayi = 0
	IF @hata='' 
	BEGIN 
		SET @KayitSayi=(SELECT COUNT(CARIID) FROM CARI WITH(NOLOCK) WHERE CARIID = @username AND (WEBSIFRE = @password OR @SmsOnayKoduDogru=1))
		IF @KayitSayi=1
		BEGIN
			SELECT @userid = CARIID, @HastaWebsifre = WEBSIFRE
			FROM CARI WITH(NOLOCK) WHERE CARIID = @username AND WEBSIFRE = @password
		END
	END
	

    IF @KayitSayi = 1 AND @hata=''  
    BEGIN

			SELECT DBO.FN_BASIT_ID_SIFRELE(CARIID) AS id, 'Partner' as role, UNVANI AS adi, '' AS soyadi, '' AS cep, '' AS email, GETDATE() AS songiristarihi, 
					(CASE WHEN ISNULL(WEBSIFRETARIHI,DATEADD(MONTH,-5,GETDATE()))<DATEADD(MONTH,-3,GETDATE()) AND @SmsOnayKoduDogru=0 THEN 1 ELSE 0 END) AS sifredegismeli, 1 AS sonuc, (CASE WHEN CARI.OSGBKURUMU=1 THEN 1 ELSE 0 END) as osgbkurumu 
			FROM CARI WITH(NOLOCK) WHERE CARIID=@userid
			set @sonucmesaj = 'Webden ba'+char(254)+'ar'+char(253)+'l'+char(253)+' login'			
	END
	ELSE IF @KayitSayi > 1
	BEGIN
			SELECT TOP 1 '' as id, 'Partner' as role, null as adi, null as soyadi, null as cep, null as email, null as songiristarihi, 0 as sifredegismeli, 5 as sonuc, 0 as osgbkurumu 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, 'Partner' as role, null as adi, null as soyadi, null as cep, null as email, null as songiristarihi, 0 as sifredegismeli, 6 as sonuc, 0 as osgbkurumu FROM HKIMLIK WITH(NOLOCK)			
			set @sonucmesaj = 'Basarisiz Login ' + @hata +' '+@username+' '+@password
	END

	IF @sonucmesaj<>''
		EXEC SPWEB_LOGLA 'kurumlogin',@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_KURUMLOGIN] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO


