frog.raindrop.jp.knowledge

DROP FUNCTION ftblCheckLogin
GO
CREATE FUNCTION ftblCheckLogin
	( @emp_cd	CHAR(6)
	, @password	CHAR(12)
	, @ip_addr	CHAR(15)
	, @TODAY	CHAR(8))

/* 戻り値は社員表と同じ */
RETURNS @SHOKU_TBL TABLE (
	emp_cd		CHAR(6 ), 
	password	CHAR(12)
	valid_from	CHAR(8 ), 
	valid_to	CHAR(8 ), 
	section_cd	CHAR(7 ), 
	admin_flg	CHAR(1 ), 
	emp_name	VARCHAR(24))

BEGIN
	DECLARE @SHOKU_TEMP	TABLE (		-- データ取得用
		emp_cd		CHAR(6 ), 
		password	CHAR(12),
		valid_from	CHAR(8 ), 
		valid_to	CHAR(8 ), 
		section_cd	CHAR(7 ), 
		admin_flg	CHAR(1 ), 
		emp_name	VARCHAR(24), 
		term_ip		CHAR(15), 
		term_mask	CHAR(15))
	DECLARE @VALID_FROM	CHAR( 8)	-- 有効期限(自)
	DECLARE @TERM_IP	CHAR(15)	-- 認証IP
	DECLARE @TERM_MASK	CHAR(15)	-- 認証ネットマスク
	
	DECLARE @tempip		CHAR(15)
	DECLARE @oct1		INT
	DECLARE @oct2		INT
	DECLARE @mask		INT
	DECLARE @cnt		INT
	DECLARE @flg		INT
	
	/* 現在有効な社員表のレコードと、関連する端末表のレコードを取得する */
	INSERT @EMP_TEMP 
	SELECT 
		SH.emp_cd		AS emp_cd, 
		SH.password		AS password,
		SH.valid_from	AS valid_from, 
		SH.valid_to		AS valid_to, 
		SH.section_cd	AS pst_cd, 
		SH.admin_flg	AS admin_flg, 
		SH.emp_name		AS emp_name, 
		TM.term_ip		AS term_ip, 
		TM.term_mask	AS term_mask
	FROM EMP_TBL				SH
		INNER JOIN EMP_TERM_TBL	TM
		ON (SH.emp_cd		= TM.emp_cd
		AND SH.valid_from	= TM.valid_from
		AND SH.emp_cd		= @emp_cd
		AND SH.password		= @password)
	WHERE @TODAY BETWEEN 
	  	SH.valid_from AND ISNULL(NULLIF(RTRIM(SH.valid_to), ''), @TODAY)
	
	/* 端末認証のためにカーソルを開く */
	DECLARE cursor_temp CURSOR FOR
	SELECT valid_from, term_ip, term_mask
	FROM @EMP_TEMP
	OPEN cursor_temp
	
	FETCH NEXT FROM cursor_temp
	INTO @VALID_FROM, @TERM_IP, @TERM_MASK
			
	WHILE @@FETCH_STATUS = 0
	BEGIN
		/* 各オクテットを比較する */
		SET @cnt	= 15
		SET @tempip	= @ip_addr
		SET @flg	= 1
		
		WHILE @cnt > 0
		BEGIN
			
			/* ログインIPを分割 */
			SET @cnt	= CHARINDEX('.', @tempip)	-- ドットの位置を検索
			IF @cnt = 0
				SET @oct1		= CAST(@tempip AS INT)
			ELSE
			BEGIN
				SET @oct1	= CAST(SUBSTRING(@tempip, 1, @cnt - 1) AS INT)
				SET @tempip	= SUBSTRING(@tempip, @cnt + 1, 15)
			END
			
			/* 端末表IPを分割 */
			SET @cnt	= CHARINDEX('.', @TERM_IP)
			IF @cnt = 0
				SET @oct2		= CAST(@TERM_IP AS INT)
			ELSE
			BEGIN
				SET @oct2		= CAST(SUBSTRING(@TERM_IP, 1, @cnt - 1) AS INT)
				SET @TERM_IP	= SUBSTRING(@TERM_IP, @cnt + 1, 15)
			END
			
			/* ネットマスクを分割 */
			SET @cnt	= CHARINDEX('.' ,@TERM_MASK)
			IF @cnt = 0
				SET @mask		= CAST(@TERM_MASK AS INT)
			ELSE
			BEGIN
				SET @mask		= CAST(SUBSTRING(@TERM_MASK, 1, @cnt - 1) AS INT)
				SET @TERM_MASK	= SUBSTRING(@TERM_MASK, @cnt + 1, 15)
			END
			
			/* 比較する */
			IF (@oct1 & @mask) != @oct2
			BEGIN
				SET @flg = 0
				BREAK		-- 正しくなければ追加しない
			END
		END
		
		/* OKなら戻り値に追加 */
		IF @flg = 1
		BEGIN
			INSERT INTO @EMP_TBL
			SELECT 
				emp_cd, 
				password, 
				valid_from, 
				valid_to, 
				section_cd, 
				admin_flg, 
				emp_name
			FROM @EMP_TEMP
			WHERE valid_from = @VALID_FROM
		END
		
		/* 次のレコード */
		FETCH NEXT FROM cursor_temp
		INTO @VALID_FROM, @TERM_IP, @TERM_MASK

	END
	RETURN 
END
GO