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

CREATE PROCEDURE [dbo].[SPWEB_HASTASORGULA]
	@TcNo nvarchar(20),
	@PassportNo nvarchar(20),
	@BirthDate nvarchar(30),
	@Phone nvarchar(20),
	@Email nvarchar(100),
	@IpNo nvarchar(100)
AS
BEGIN
	DECLARE 
	  @BULUNANHKIMLIKID INT=0,
	  @BULUNANTEL NVARCHAR(20),
	  @BULUNANEMAIL NVARCHAR(100),
	  @BULUNANAD NVARCHAR(50),
	  @BULUNANSOYAD NVARCHAR(50),
	  @CNTSAYI INT,
	  @ONAYKOD NVARCHAR(10),
	  @STRKIMLIKIDSIFRELI NVARCHAR(20)

	IF (LEN(ISNULL(@TcNo,''))>0)
	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(@TcNo)=0)
			BEGIN
				SELECT 0 AS patientid, '' AS code, '' AS phone, '' AS email, '' AS patientname, '' AS patientsurname,4 as result, 'Kimlik numarası geçerli değil' as resultdesc
				RETURN
			END
		END
	END

	SET @CNTSAYI = (	SELECT COUNT(HKIMLIKID) FROM HKIMLIK WITH(NOLOCK) WHERE (		(@TcNo<>'' AND HTCKIMLIKNO=@TcNo) 
																					OR	(@PassportNo<>'' AND HPASAPORT=@PassportNo)
																				)
					)
	IF @CNTSAYI>1 
	BEGIN
		SELECT 0 AS patientid, '' AS code, '' AS phone, '' AS email, '' AS patientname, '' AS patientsurname,5 as result, 'Bilgiler birdenen çok hasta ile eşleşiyor' as resultdesc
		RETURN
	END

	IF @CNTSAYI=1
	BEGIN
		SET @CNTSAYI =	(	SELECT COUNT(HKIMLIKID) FROM HKIMLIK WITH(NOLOCK) 
							WHERE ((@TcNo<>'' AND HTCKIMLIKNO=@TcNo) OR	(@PassportNo<>'' AND HPASAPORT=@PassportNo))
								    AND @BirthDate<>'' AND HDTARIHI = CONVERT(DATETIME,@BirthDate)
						)
		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	(		(@TcNo<>'' AND HTCKIMLIKNO=@TcNo) 
						OR	(@PassportNo<>'' AND @BirthDate<>'' AND HPASAPORT=@PassportNo)
					)
				AND @BirthDate<>''
				AND HDTARIHI = CONVERT(DATETIME,@BirthDate)
		END
		ELSE
		BEGIN
			SELECT 0 AS patientid, '' AS code, '' AS phone, '' AS email, '' AS patientname, '' AS patientsurname,19 as result, 'Bilgilerinizin kontrolünde bir sorun oldu.' as resultdesc
			RETURN
		END
	END

	SET @BULUNANTEL=RIGHT(REPLACE(ISNULL(@BULUNANTEL,''),' ',''),10)

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

		
	IF @BULUNANTEL<>''-- AND @Phone='' AND @Email=''
	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,1 as result, 'Hasta bulundu onay kodu oluşturuldu' as resultdesc
	END
	ELSE
	BEGIN
		IF @Phone<>'' OR @Email<>''
		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))
			IF @Phone<>''
			BEGIN
				INSERT INTO HWEBONAYKOD (HCEP,HEMAIL,ONAYKODU,KAYITTARIHI,IPNO) VALUES (RIGHT(REPLACE(@Phone,' ',''),10),'',@ONAYKOD,GETDATE(),@IpNo)
				SELECT 0 AS patientid, @ONAYKOD AS code, @Phone AS phone, '' AS email, '' AS patientname, '' AS patientsurname,2 as result, 'Onay kodu oluşturuldu' as resultdesc
			END
			ELSE
			BEGIN
				INSERT INTO HWEBONAYKOD (HCEP,HEMAIL,ONAYKODU,KAYITTARIHI,IPNO) VALUES ('',@Email,@ONAYKOD,GETDATE(),@IpNo)
				SELECT 0 AS patientid, @ONAYKOD AS code, '' AS phone, @Email AS email, '' AS patientname, '' AS patientsurname,3 as result, 'Onay kodu oluşturuldu' as resultdesc
			END			
		END
		ELSE
		BEGIN
			SELECT 0 AS patientid, '' AS code, '' AS phone, '' AS email, '' AS patientname, '' AS patientsurname,6 as result, 'Hasya bulunamadı' as resultdesc
		END
	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_HASTASORGULA] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO

