防沉迷（三）

判断是否成年存储过程

USE TGAccountsDB
GO

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[GSP_GP_CheckRealName]') and OBJECTPROPERTY(ID, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[GSP_GP_CheckRealName]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

-- 检测实名认证
CREATE PROC GSP_GP_CheckRealName
	@UserID NVARCHAR(32),					--用户ID
	@ErrorDescribe NVARCHAR(128) OUTPUT		--错误提示
WITH ENCRYPTION AS

-- 属性设置
SET NOCOUNT ON

-- 变量
DECLARE @BindingPassport INT
DECLARE @PassportID NVARCHAR(18)
DECLARE @RealNameStatus INT
DECLARE @RealNameStatusString NVARCHAR(128)

-- 执行逻辑

BEGIN
	--查询防沉迷开关
	SELECT @RealNameStatus = StatusValue, @RealNameStatusString = StatusString 
	FROM TGAccountsDB.dbo.SystemStatusInfo(NOLOCK) 
	WHERE StatusName = N'RealName'
	
	IF @RealNameStatus IS NOT NULL AND @RealNameStatus <> 0
	BEGIN
		--查询身份证号(考虑保密性,该字段只截取身份证生日部分字段)
		SELECT @BindingPassport = BindingPassport, @PassportID = PassportID
		FROM [TGAccountsDB].[dbo].[AccountsProtection]
		WHERE UserID=@UserID
		
		--判断是否实名(网站配置提示语)
		IF @BindingPassport IS NULL OR @BindingPassport = 0
		BEGIN
			SET @ErrorDescribe = @RealNameStatusString
			RETURN 1000
		END
		
		--判断格式
		IF @PassportID IS NULL OR @PassportID = N'' OR LEN(@PassportID) <> 8
		BEGIN
			SET @ErrorDescribe = N'当前账号为未成年人账号，不可进行游戏和充值，请联系客服申请重新认证！(1001)'
			RETURN 1001
		END
		
		--检查是否成年(PassportID原来存的身份证，现在只保存生日)
		//IF DATEDIFF(yy,CAST(CASE
		//	WHEN LEN(@PassportID)=18
		//	THEN SUBSTRING(@PassportID,7,8) 
		//	ELSE '19' + SUBSTRING(@PassportID,7,6) 
		//	END AS DATETIME), GETDATE())  < 18
		IF DATEDIFF(YY, CAST(@PassportID AS DATETIME), GETDATE()) < 18
		BEGIN
			SET @ErrorDescribe = N'当前账号为未成年人账号，不可进行游戏和充值，请联系客服申请重新认证！'
			RETURN 1002
		END
	END
END

RETURN 0

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO