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

CREATE PROCEDURE [dbo].[SPWEB_SMSONAYKODUAL]
	@TcPasaportTelKurumKodu nvarchar(20),
	@KurumLogin bit,
	@IpNo nvarchar(100)
AS
BEGIN
	DECLARE 
	  @BULUNANHKIMLIKID INT=0,
	  @BULUNANTEL NVARCHAR(20),
	  @BULUNANEMAIL NVARCHAR(100),
	  @BULUNANAD NVARCHAR(50),
	  @BULUNANSOYAD NVARCHAR(50),
	  @CNTSAYI INT=0,
	  @ONAYKOD NVARCHAR(10),
	  @STRKIMLIKIDSIFRELI NVARCHAR(20),
	  @TCCHECK BIT=0,
	  @LOGINTIP SMALLINT=0


	SET @TcPasaportTelKurumKodu = REPLACE(@TcPasaportTelKurumKodu,' ','')
	IF @KurumLogin=1 
	BEGIN
		SET @LOGINTIP=4
		IF ISNUMERIC(@TcPasaportTelKurumKodu)=1
		BEGIN
			SET @CNTSAYI =  (SELECT COUNT(CARIID) FROM CARI WITH(NOLOCK) WHERE 	CARIID=@TcPasaportTelKurumKodu)
			IF @CNTSAYI=1 
			BEGIN
				SELECT TOP 1	@BULUNANHKIMLIKID=CARIID, 
							@BULUNANTEL=ISNULL(TEL1,''),
							@BULUNANEMAIL='',
							@BULUNANAD=KISAADI,
							@BULUNANSOYAD=''						
				FROM CARI WITH(NOLOCK) 
				WHERE	CARIID=@TcPasaportTelKurumKodu
			END
			ELSE
			BEGIN
				SELECT 0 AS patientid, '' AS code, '' AS phone, '' AS email, '' AS patientname, '' AS patientsurname, @LOGINTIP as logintip,2 as result, 'Bilgiler uyu'+CHAR(254)+'muyor' as resultdesc
				RETURN
			END
		END
		ELSE
		BEGIN
			SELECT 0 AS patientid, '' AS code, '' AS phone, '' AS email, '' AS patientname, '' AS patientsurname, @LOGINTIP as logintip,2 as result, 'Bilgiler uyu'+CHAR(254)+'muyor' as resultdesc
			RETURN
		END		
	END
	ELSE
	BEGIN

		IF LEN(@TcPasaportTelKurumKodu)<7  and @KurumLogin=0
		BEGIN
			SELECT 0 AS patientid, '' AS code, '' AS phone, '' AS email, '' AS patientname, '' AS patientsurname, @LOGINTIP as logintip,2 as result, 'Bilgiler uyu'+CHAR(254)+'muyor' as resultdesc
			RETURN
		END

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

		IF @CNTSAYI=0
		BEGIN
			SET @CNTSAYI = (SELECT COUNT(HKIMLIKID) FROM HKIMLIK WITH(NOLOCK) WHERE HPASAPORT=@TcPasaportTelKurumKodu)
			IF @CNTSAYI = 1 SET @LOGINTIP=2
		END

		IF @CNTSAYI=0
		BEGIN
			SET @CNTSAYI = (SELECT COUNT(HKIMLIKID) FROM HKIMLIK WITH(NOLOCK) WHERE RIGHT(HCEP,10)=RIGHT(@TcPasaportTelKurumKodu,10))
			IF @CNTSAYI = 1 SET @LOGINTIP=3
		END

		IF @CNTSAYI>1 
		BEGIN
			SELECT 0 AS patientid, '' AS code, '' AS phone, '' AS email, '' AS patientname, '' AS patientsurname, @LOGINTIP as logintip,2 as result, 'Bilgiler uyu'+CHAR(254)+'muyor' as resultdesc
			RETURN
		END

		IF @CNTSAYI=1
		BEGIN
			SELECT TOP 1	@BULUNANHKIMLIKID=ISNULL(HKIMLIKID,0), 
							@BULUNANTEL=ISNULL(HCEP,''),
							@BULUNANEMAIL=ISNULL(HEMAIL,''),
							@BULUNANAD=ISNULL(HADI,''),
							@BULUNANSOYAD=ISNULL(HSOYADI,'')						
			FROM HKIMLIK WITH(NOLOCK) 
			WHERE	(		(@LOGINTIP=1 AND HTCKIMLIKNO=@TcPasaportTelKurumKodu) 
						OR	(@LOGINTIP=2 AND HPASAPORT=@TcPasaportTelKurumKodu)
						OR	(@LOGINTIP=3 AND  RIGHT(HCEP,10)=RIGHT(@TcPasaportTelKurumKodu,10))
					)	
		END
		ELSE
		BEGIN
			SELECT 0 AS patientid, '' AS code, '' AS phone, '' AS email, '' AS patientname, '' AS patientsurname, @LOGINTIP as logintip,2 as result, 'Bilgiler uyu'+CHAR(254)+'muyor' as resultdesc
			RETURN
		END


	END

	IF LEFT(@BULUNANTEL,3)='+90' SET @BULUNANTEL = SUBSTRING(@BULUNANTEL,4,LEN(@BULUNANTEL)-3)
	IF LEFT(@BULUNANTEL,2)='90' SET @BULUNANTEL = SUBSTRING(@BULUNANTEL,3,LEN(@BULUNANTEL)-2)
	IF LEFT(@BULUNANTEL,1)='0' SET @BULUNANTEL = SUBSTRING(@BULUNANTEL,2,LEN(@BULUNANTEL)-1)

	IF @BULUNANHKIMLIKID>0
	BEGIN		
		SET @STRKIMLIKIDSIFRELI = DBO.FN_BASIT_ID_SIFRELE(@BULUNANHKIMLIKID)				 
	END

	IF @BULUNANTEL<>''
	BEGIN
		IF (SELECT COUNT(*) FROM HWEBONAYKOD WHERE HCEP=@BULUNANTEL AND KAYITTARIHI>DATEADD(MINUTE,-3,GETDATE()))>0
		BEGIN
			SELECT 0 AS patientid, '' AS code, '' AS phone, '' AS email, '' AS patientname, '' AS patientsurname, @LOGINTIP as logintip,2 as result, 'S'+char(252)+'resi dolmayan onay kodunuz var' as resultdesc
			RETURN
		END
	END

		
	IF @BULUNANTEL<>''
	BEGIN
		
		SET @ONAYKOD =  CHAR(49+CONVERT(INT,RAND()*9)) + CHAR(48+CONVERT(INT,RAND()*10)) + CHAR(48+CONVERT(INT,RAND()*10)) + 
						CHAR(48+CONVERT(INT,RAND()*10)) + CHAR(48+CONVERT(INT,RAND()*10)) + CHAR(48+CONVERT(INT,RAND()*10))
		INSERT INTO HWEBONAYKOD (HCEP,HEMAIL,ONAYKODU,KAYITTARIHI,IPNO) VALUES (RIGHT(REPLACE(@BULUNANTEL,' ',''),10),'',@ONAYKOD,GETDATE(),@IpNo)
		SELECT @STRKIMLIKIDSIFRELI AS patientid, @ONAYKOD AS code, @BULUNANTEL AS phone, @BULUNANEMAIL AS email, @BULUNANAD AS patientname, @BULUNANSOYAD AS patientsurname,@LOGINTIP as logintip,1 as result, 'Hasta bulundu onay kodu oluşturuldu' as resultdesc
	END
	ELSE
	BEGIN
		SELECT 0 AS patientid, '' AS code, '' AS phone, '' AS email, '' AS patientname, '' AS patientsurname, @LOGINTIP as logintip,2 as result, 'Bilgiler uyu'+CHAR(254)+'muyor' as resultdesc
	END      
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_SMSONAYKODUAL] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO

