麻将开发用户注册_修改

----------------------------------------------------------------------------------------------------

-- 版权：2015

-- 时间：2015-08-31

-- 用途：帐号注册

----------------------------------------------------------------------------------------------------

--select * from sys.objects where type = 'TR';

USE QPAccountsDB

GO

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

DROP PROCEDURE [dbo].NET_PW_RegisterAccounts

GO

IF (object_id('TGR_RegisterAccounts_AccountsDB', 'TR') IS NOT NULL)

DROP TRIGGER TGR_RegisterAccounts_AccountsDB

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

----------------------------------------------------------------------------------------------------

--创建用户注册校验触发器 (限制同一个IP 注册用户数量不超过5)

--何变

--2014-02-27

CREATE TRIGGER TGR_RegisterAccounts_AccountsDB

ON

[QPAccountsDB].dbo.[AccountsInfo]

WITH ENCRYPTION

AFTER INSERT

AS

DECLARE @IpRegCount INT,

@IpStr VARCHAR(20);

SELECT @IpStr=S.RegisterIP FROM INSERTED S;

SELECT @IpRegCount=COUNT(UserID) FROM AccountsInfo WHERE RegisterIP=@IpStr

IF @IpRegCount > 5

BEGIN

RaisError('注册失败，你的IP注册了太多用户！',16,1);

ROLLBACK TRAN;

END

GO

----------------------------------------------------------------------------------------------------

-- 帐号注册

CREATE PROCEDURE NET_PW_RegisterAccounts

@strAccounts NVARCHAR(31), -- 用户帐号

@strNickname NVARCHAR(31), -- 用户昵称

@strLogonPass NCHAR(32), -- 用户密码

@strInsurePass NCHAR(32), -- 用户密码

@dwFaceID INT, -- 头像标识

@dwGender TINYINT, -- 用户性别

@strSpreader NVARCHAR(31), -- 推广员名

@strCompellation NVARCHAR(16), -- 真实姓名

@strPassPortID NVARCHAR(18), -- 身份证号

@strClientIP NVARCHAR(15), -- 连接地址

@strErrorDescribe NVARCHAR(127) OUTPUT -- 输出信息

WITH ENCRYPTION AS

-- 属性设置

SET NOCOUNT ON

-- 基本信息

DECLARE @UserID INT

DECLARE @FaceID INT

DECLARE @Accounts NVARCHAR(31)

DECLARE @Nickname NVARCHAR(31)

DECLARE @UnderWrite NVARCHAR(63)

-- 扩展信息

DECLARE @GameID INT

DECLARE @SpreaderID INT

DECLARE @Gender TINYINT

DECLARE @Experience INT

DECLARE @Loveliness INT

DECLARE @MemberOrder INT

DECLARE @MemberOverDate DATETIME

DECLARE @CustomFaceVer TINYINT

DECLARE @Compellation NVARCHAR(16)

DECLARE @PassPortID NVARCHAR(18)

-- 辅助变量

DECLARE @EnjoinLogon AS INT

DECLARE @EnjoinRegister AS INT

DECLARE @IpRegCount AS INT

-- 执行逻辑

BEGIN

-- 注册暂停

SELECT @EnjoinRegister=StatusValue FROM SystemStatusInfo(NOLOCK) WHERE StatusName=N'EnjoinRegister'

IF @EnjoinRegister IS NOT NULL AND @EnjoinRegister<>0

BEGIN

SELECT @strErrorDescribe=StatusString FROM SystemStatusInfo(NOLOCK) WHERE StatusName=N'EnjoinRegister'

RETURN 1

END

-- 登录暂停

SELECT @EnjoinLogon=StatusValue FROM SystemStatusInfo(NOLOCK) WHERE StatusName=N'EnjoinLogon'

IF @EnjoinLogon IS NOT NULL AND @EnjoinLogon<>0

BEGIN

SELECT @strErrorDescribe=StatusString FROM SystemStatusInfo(NOLOCK) WHERE StatusName=N'EnjoinLogon'

RETURN 2

END

-- 效验名字

IF EXISTS (SELECT [String] FROM ConfineContent(NOLOCK) WHERE CHARINDEX(String,@strAccounts)>0 AND (EnjoinOverDate>GETDATE() OR EnjoinOverDate IS NULL))

BEGIN

SET @strErrorDescribe=N'抱歉地通知您，您所输入的帐号名含有限制字符串，请更换帐号名后再次申请帐号！'

RETURN 1

END

-- 效验昵称

IF EXISTS (SELECT [String] FROM ConfineContent(NOLOCK) WHERE CHARINDEX(String,@strNickname)>0 AND (EnjoinOverDate>GETDATE() OR EnjoinOverDate IS NULL))

BEGIN

SET @strErrorDescribe=N'抱歉地通知您，您所输入的昵称含有限制字符串，请更换昵称后再次申请帐号！'

RETURN 1

END

-- 效验IP地址

SELECT @EnjoinRegister=EnjoinRegister FROM ConfineAddress(NOLOCK) WHERE AddrString=@strClientIP AND (EnjoinOverDate>GETDATE() OR EnjoinOverDate IS NULL)

IF @EnjoinRegister IS NOT NULL AND @EnjoinRegister<>0

BEGIN

SET @strErrorDescribe=N'抱歉地通知您，系统禁止了您所在的 IP 地址的注册功能，请联系客户服务中心了解详细情况！'

RETURN 2

END

-- 限制同一个IP 注册用户数量不超过5

SELECT @IpRegCount=COUNT(UserID) FROM AccountsInfo WHERE RegisterIP=@strClientIP

IF @IpRegCount > 5

BEGIN

SET @strErrorDescribe=N'注册失败，你的IP注册了太多用户！'

RETURN 2

END

-- 查询用户

IF EXISTS (SELECT UserID FROM AccountsInfo(NOLOCK) WHERE Accounts=@strAccounts)

BEGIN

SET @strErrorDescribe=N'此帐号名已被注册，请换另一帐号名字尝试再次注册！'

RETURN 3

END

-- 昵称检查

IF EXISTS (SELECT UserID FROM AccountsInfo(NOLOCK) WHERE NickName=@strNickname)

BEGIN

SET @strErrorDescribe=N'此昵称已被注册，请换另一昵称尝试再次注册！'

RETURN 3

END

-- 查推广员

IF @strSpreader<>''

BEGIN

-- 查推广员

SELECT @SpreaderID=UserID FROM AccountsInfo(NOLOCK) WHERE Accounts=@strSpreader

-- 结果处理

IF @SpreaderID IS NULL

BEGIN

SET @strErrorDescribe=N'您所填写的推荐人不存在或者填写错误，请检查后再次注册！'

RETURN 4

END

END

ELSE SET @SpreaderID=0

-- 注册用户

INSERT AccountsInfo (Accounts,Nickname,RegAccounts,LogonPass,InsurePass,SpreaderID,Gender,FaceID,WebLogonTimes,RegisterIP,LastLogonIP,Compellation,PassPortID)

VALUES (@strAccounts,@strNickname,@strAccounts,@strLogonPass,@strInsurePass,@SpreaderID,@dwGender,@dwFaceID,1,@strClientIP,@strClientIP,@strCompellation,@strPassPortID)

-- 错误判断

IF @@ERROR<>0

BEGIN

SET @strErrorDescribe=N'帐号已存在，请换另一帐号名字尝试再次注册！'

RETURN 5

END

-- 查询用户

SELECT @UserID=UserID, @Accounts=Accounts, @Nickname=Nickname,@UnderWrite=UnderWrite, @Gender=Gender, @FaceID=FaceID, @Experience=Experience,

@MemberOrder=MemberOrder, @MemberOverDate=MemberOverDate, @Loveliness=Loveliness,@CustomFaceVer=CustomFaceVer,

@Compellation=Compellation,@PassPortID=PassPortID

FROM AccountsInfo(NOLOCK) WHERE Accounts=@strAccounts

-- 分配标识

SELECT @GameID=GameID FROM GameIdentifier(NOLOCK) WHERE UserID=@UserID

IF @GameID IS NULL

BEGIN

SET @GameID=0

SET @strErrorDescribe=N'用户注册成功，但未成功获取游戏 ID 号码，系统稍后将给您分配！'

END

ELSE UPDATE AccountsInfo SET GameID=@GameID WHERE UserID=@UserID

-- 推广提成

IF @SpreaderID<>0

BEGIN

DECLARE @Score BIGINT

DECLARE @Note NVARCHAR(512)

SET @Note = N'注册'

SELECT @Score = RegisterGrantScore FROM QPTreasureDBLink.QPTreasureDB.dbo.GlobalSpreadInfo

IF @Score IS NULL

BEGIN

SET @Score=5000

END

INSERT INTO QPTreasureDBLink.QPTreasureDB.dbo.RecordSpreadInfo(

UserID,Score,TypeID,ChildrenID,CollectNote)

VALUES(@SpreaderID,@Score,1,@UserID,@Note)

END

-- 记录日志

DECLARE @DateID INT

SET @DateID=CAST(CAST(GETDATE() AS FLOAT) AS INT)

UPDATE SystemStreamInfo SET WebRegisterSuccess=WebRegisterSuccess+1 WHERE DateID=@DateID

IF @@ROWCOUNT=0 INSERT SystemStreamInfo (DateID, WebRegisterSuccess) VALUES (@DateID, 1)

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

-- 注册赠送

-- 读取变量

DECLARE @GrantScoreCount AS BIGINT

DECLARE @GrantIPCount AS BIGINT

SELECT @GrantScoreCount=StatusValue FROM SystemStatusInfo(NOLOCK) WHERE StatusName=N'GrantScoreCount'

SELECT @GrantIPCount=StatusValue FROM SystemStatusInfo(NOLOCK) WHERE StatusName=N'GrantIPCount'

-- 赠送限制

IF @GrantScoreCount IS NOT NULL AND @GrantScoreCount>0 AND @GrantIPCount IS NOT NULL AND @GrantIPCount>0

BEGIN

-- 赠送次数

DECLARE @GrantCount AS BIGINT

SELECT @GrantCount=GrantCount FROM SystemGrantCount(NOLOCK) WHERE DateID=@DateID AND RegisterIP=@strClientIP

-- 次数判断

IF @GrantCount IS NOT NULL AND @GrantCount>=@GrantIPCount

BEGIN

SET @GrantScoreCount=0

END

END

-- 赠送金币

IF @GrantScoreCount IS NOT NULL AND @GrantScoreCount>0

BEGIN

-- 更新记录

UPDATE SystemGrantCount SET GrantScore=GrantScore+@GrantScoreCount, GrantCount=GrantCount+1 WHERE DateID=@DateID AND RegisterIP=@strClientIP

-- 插入记录

IF @@ROWCOUNT=0

BEGIN

INSERT SystemGrantCount (DateID, RegisterIP, RegisterMachine, GrantScore, GrantCount) VALUES (@DateID, @strClientIP, '', @GrantScoreCount, 1)

END

-- 赠送金币

INSERT QPTreasureDBLink.QPTreasureDB.dbo.GameScoreInfo (UserID, Score, RegisterIP, LastLogonIP) VALUES (@UserID, @GrantScoreCount, @strClientIP, @strClientIP)

END

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

-- 输出变量

SELECT @UserID AS UserID, @GameID AS GameID, @Accounts AS Accounts, @Nickname AS Nickname,@UnderWrite AS UnderWrite, @FaceID AS FaceID,

@Gender AS Gender, @Experience AS Experience, @MemberOrder AS MemberOrder, @MemberOverDate AS MemberOverDate,

@Loveliness AS Loveliness,@CustomFaceVer AS CustomFaceVer,

@Compellation AS Compellation,@PassPortID AS PassPortID

End

RETURN 0

GO