IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MEDWEB_HASTALOGIN]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[MEDWEB_HASTALOGIN]
GO

CREATE  FUNCTION [dbo].[MEDWEB_HASTALOGIN](@HCEP_KIMLIKNO nvarchar(20), @HWEBSIFRE  nvarchar(10)) 
RETURNS @retHASTALOGIN TABLE 
(
	HKIMLIKID int,
	HTCKIMLIKNO nvarchar(25),
	HADI nvarchar(25),
	HSOYADI nvarchar(25),
	HEMAIL nvarchar(40),
	HDTARIHI datetime,
	HCINSIYETI nvarchar(1),
	CARIANAGRUPID int,
    HWEBSONGIRISTARIHI datetime,
	SONUC int
)
AS
BEGIN
   DECLARE 
      @KAYITSAYI int,
      @HastaWebsifre nvarchar(10),
      @HastaCep nvarchar(20),
      @HastaWebKullan bit, 
      @HastaWebAktif bit
      
    SET @KAYITSAYI = 0
	
	--SELECT @KAYITSAYI = COUNT(*) FROM HKIMLIK WHERE HCEP = @HCEP_KIMLIKNO OR HTCKIMLIKNO = @HCEP_KIMLIKNO
    SELECT  @HastaWebsifre = HWEBSIFRE, @HastaCep = HCEP, @HastaWebKullan = HWEBKULLAN, @HastaWebAktif = HWEBAKTIF 
    FROM HKIMLIK WHERE (HCEP = @HCEP_KIMLIKNO OR HTCKIMLIKNO = @HCEP_KIMLIKNO ) AND HWEBSIFRE = @HWEBSIFRE
    
	SET @KAYITSAYI = @@ROWCOUNT
	
    IF @KAYITSAYI= 1   
    BEGIN
        IF (ISNULL(@HastaWebKullan, 0) = 0)
        BEGIN
	      INSERT INTO @retHASTALOGIN
		  SELECT TOP 1 0, null, null, null, null, null, null, NULL, null, 4 FROM HKIMLIK	
		END
		ELSE
        IF (ISNULL(@HastaWebAktif, 0) = 0)
        BEGIN
	      INSERT INTO @retHASTALOGIN
		  SELECT TOP 1 0, null, null, null, null, null, null, NULL, null, 5 FROM HKIMLIK	
		END
		ELSE
        IF (@HastaWebsifre = @HWEBSIFRE)
        BEGIN
			INSERT INTO @retHASTALOGIN
			SELECT HKIMLIKID, HTCKIMLIKNO, HADI, HSOYADI, HEMAIL, HDTARIHI, HCINSIYETI, CARIANAGRUPID, HWEBSONGIRISTARIHI, 0 
			FROM HKIMLIK LEFT JOIN VWKURUM ON HKCARIID = VWKURUM.CARIID
			WHERE (HCEP = @HCEP_KIMLIKNO OR HTCKIMLIKNO = @HCEP_KIMLIKNO) AND HWEBSIFRE = @HWEBSIFRE
		END
		ELSE
		BEGIN
			INSERT INTO @retHASTALOGIN
			SELECT TOP 1 0, null, null, null, null, null, null, NULL, null, 2 FROM HKIMLIK	
		END
	END
	ELSE IF @KAYITSAYI > 1  
	    INSERT INTO @retHASTALOGIN
		SELECT TOP 1 0, null, null, null, null, null, null, NULL, null, 3 FROM HKIMLIK
	ELSE IF @KAYITSAYI = 0   
	    INSERT INTO @retHASTALOGIN
		SELECT TOP 1 0, null, null, null, null, null, null, NULL, null, 1 FROM HKIMLIK	
		
    RETURN
END

GO


