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

CREATE PROCEDURE [dbo].[SPWEB_SIFREHATIRLAT]
	@TcNo_Pasaport nvarchar(20),
	@IpNo nvarchar(100)
AS
BEGIN
	DECLARE 
	  @BULUNANHKIMLIKID INT=0,
	  @CNTSAYI INT,
	  @HWEBSIFRE NVARCHAR(20)

	

	IF LEN(ISNULL(@TcNo_Pasaport,''))<7
	BEGIN
		SELECT 0 AS patientid, '' AS passwrd, '' AS phone, '' AS email, '' AS patientname, '' AS patientsurname,4 as result, 'Girilen kimlik no çok kısa' as resultdesc
		RETURN
	END

	SET @CNTSAYI = (SELECT COUNT(HKIMLIKID) FROM HKIMLIK WITH(NOLOCK) WHERE HTCKIMLIKNO=@TcNo_Pasaport)
	IF @CNTSAYI=1 SET @BULUNANHKIMLIKID = ISNULL((SELECT HKIMLIKID FROM HKIMLIK WITH(NOLOCK) WHERE HTCKIMLIKNO=@TcNo_Pasaport AND ISNULL(HCEP,'')<>''),-99)
	IF @CNTSAYI = 0 
	BEGIN
		SET @CNTSAYI = (SELECT COUNT(HKIMLIKID) FROM HKIMLIK WITH(NOLOCK) WHERE HPASAPORT=@TcNo_Pasaport)
		IF @CNTSAYI=1 SET @BULUNANHKIMLIKID = ISNULL((SELECT HKIMLIKID FROM HKIMLIK WITH(NOLOCK) WHERE HPASAPORT=@TcNo_Pasaport AND ISNULL(HCEP,'')<>''),-99)
	END

	IF @CNTSAYI = 0 
	BEGIN
		SET @CNTSAYI = (SELECT COUNT(HKIMLIKID) FROM HKIMLIK WITH(NOLOCK) WHERE HPASAPORT=@TcNo_Pasaport)
		IF @CNTSAYI=1 SET @BULUNANHKIMLIKID = ISNULL((SELECT HKIMLIKID FROM HKIMLIK WITH(NOLOCK) WHERE HCEP=@TcNo_Pasaport AND ISNULL(HCEP,'')<>''),-99)
	END
			
	IF @CNTSAYI>1 
	BEGIN
		SELECT 0 AS patientid, '' AS passwrd, '' AS phone, '' AS email, '' AS patientname, '' AS patientsurname,5 as result, 'Bilgiler birdenen çok hasta ile eşleşiyor' as resultdesc
		RETURN
	END

	IF @BULUNANHKIMLIKID>0
	BEGIN
			EXEC SPWEB_LOGLA 'sifrehatirlat',@BULUNANHKIMLIKID,'Hastaya şifresi gönderildi',@IpNo
			SET @HWEBSIFRE =  CHAR(65+CONVERT(INT,RAND()*26)) + CHAR(97+CONVERT(INT,RAND()*26)) + 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)) + CHAR(48+CONVERT(INT,RAND()*10))
			UPDATE HKIMLIK SET HWEBSIFRE=@HWEBSIFRE,HWEBSIFRETARIHI=DATEADD(YEAR,-1,GETDATE()) WHERE HKIMLIKID=@BULUNANHKIMLIKID
			SELECT TOP 1	ISNULL(HKIMLIKID,0) AS patientid, 
							ISNULL(HWEBSIFRE,'') AS passwrd,
							ISNULL(HCEP,'') AS phone,
							ISNULL(HEMAIL,'') AS email,
							ISNULL(HADI,'') AS patientname,
							ISNULL(HSOYADI,'') AS patientsurname,
							1 AS result,
							'Hasta bulundu' as resultdesc
			FROM HKIMLIK WITH(NOLOCK) 
			WHERE	HKIMLIKID=@BULUNANHKIMLIKID
	END
	ELSE
	BEGIN
		SELECT 0 AS patientid, '' AS passwrd, '' AS phone, '' AS email, '' AS patientname, '' AS patientsurname,6 as result, 'Hasta bulunamadı' 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_SIFREHATIRLAT] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO

