User registration and tracking code (i)

User register and confirm the online ASP program.
1. SQL table and storage process
---------------------------------------------
CREATE TABLE [dbo]. [Userbaseinfo] (
[UserID] [varchar] () not NULL,
[Password] [varchar] () not NULL,
[Validcodelogin] [Char] () not NULL,
[Userlevel] [Char] (1) NULL,
[Logintime] [Char] (m) NULL
) on [PRIMARY]
Go

ALTER TABLE Userbaseinfo
Add
Constraint Pk_userbaseinfo_userid
Primary KEY (UserID)
Go

CREATE TABLE [dbo]. [Userdetailinfo] (
[UserID] [varchar] () not NULL,
[Password] [varchar] () not NULL,
[Realname] [varchar] (a) NULL,
[Sex] [Char] (a) NULL,
[Birthday] [DateTime] Null
[Idcode] [varchar] (m) NULL,
[Address] [varchar] (a) NULL,
[Email] [varchar] (m) NULL,
[Telephone] [varchar] (m) NULL
) on [PRIMARY]
Go

ALTER TABLE Userdetailinfo
Add
Constraint Pk_userdetailinfo_userid
Primary KEY (UserID)
Go

SET QUOTED_IDENTIFIER off SET ANSI_NULLS on
Go

CREATE proc Dbo.proc_getrandom_internal
--Get the check code
@minNum Integer,
@maxNum Integer,
@RandomNum Float output
As
SET NOCOUNT ON

DECLARE @numRange integer
DECLARE @ranSeed integer
DECLARE @curTime datetime

Begin

Select @numRange = @maxNum-@minNum +1

Select @curTime =getdate ()
Select @ranSeed =datediff (S, ' 2000-1-1 ', @curTime)
Select @ranSeed = @ranSeed +1
Select @RandomNum =rand () * @numRange + @minNum
--print @RandomNum
Return
End




Go
SET QUOTED_IDENTIFIER OFF
Go
SET ANSI_NULLS on
Go

SET QUOTED_IDENTIFIER OFF
Go
SET ANSI_NULLS on
Go


CREATE proc Dbo.proc_getvalidcode_internal
--Get the check code
@CodeLength Integer,
@ValidCode varchar () output
As
SET NOCOUNT ON

Declare @chrRnd char (1)
DECLARE @chrRndNo integer

Begin

Select @ValidCode = ""

while (@CodeLength >0)
Begin
exec proc_getrandom_internal 1,52, @chrRndNo output
If @chrRndNo >26
Begin
Select @chrRndNo = @chrRndNo +6
End
Select @chrRnd =char (@chrRndNo +64)
Select @ValidCode = @ValidCode + @chrRnd
Select @CodeLength = @CodeLength-1
End
Print @validCode
Return
End



Go
SET QUOTED_IDENTIFIER OFF
Go
SET ANSI_NULLS on
Go

SET QUOTED_IDENTIFIER OFF
Go
SET ANSI_NULLS on
Go


CREATE proc Dbo.proc_userinfoupdate
--User Updates personal information
@ValidCodeLogin varchar (10),
@RealName Varchar (10),
@Sex Varchar (10),
@Birthday datetime,
@IDCode Varchar (50),
@Address Varchar (300),
@eMail Varchar (50),
@Telephone Varchar (50)
As

SET NOCOUNT ON

DECLARE @UserValidFlag int
DECLARE @ValidCodeReg varchar (30)
DECLARE @UserLevel varchar (1)
DECLARE @UserID varchar (30)

Begin
exec proc_isuservalidbycode_internal @ValidCodeLogin, @UserValidFlag output
If @UserValidFlag <0
Begin
--select @UserValidFlag as ResultId
---1 users are not logged in
---2 User timeout
Return @UserValidFlag
End

Select @UserID =userid from Userbaseinfo where validcodelogin= @ValidCodeLogin

Update Userdetailinfo
Set Realname= @RealName,
Sex= @Sex,
Birthday= @Birthday,
Idcode= @IDCode,
Address= @Address,
Email= @eMail,
Telephone= @Telephone
where
Userid= @UserID;

if (@RealName = "" or @Birthday = "" or @Sex = "" or @IDCode = "" or @Address = "" or @eMail = "" or @Telephone = ")
Begin
--select-3 as ResultId
Return-3--Information not fully completed
End

Select 0 as ResultId

End


Go
SET QUOTED_IDENTIFIER OFF
Go
SET ANSI_NULLS on
Go

SET QUOTED_IDENTIFIER ON
Go
SET ANSI_NULLS on
Go

Create proc Dbo.proc_userlogout
--User exits
@ValidCodeLogin varchar (10)
As

SET NOCOUNT ON

DECLARE @UserValidFlag int
DECLARE @UserLevel varchar (9)

Begin
exec proc_isuservalidbycode_internal @ValidCodeLogin, @UserValidFlag output
if (@UserValidFlag <0)
Begin
--select @UserValidFlag as ResultId
Return @UserValidFlag
---1 users are not logged in
---2 User timeout
End

Update Userbaseinfo
Set validcodelogin= ',
Logintime= ' 1970-1-1 '
where
validcodelogin= @ValidCodeLogin

--select 0 as ResultId
return 0
End

Go
SET QUOTED_IDENTIFIER OFF
Go
SET ANSI_NULLS on
Go

SET QUOTED_IDENTIFIER OFF
Go
SET ANSI_NULLS on
Go


CREATE proc Dbo.proc_userregbase
--User basic information registration
@UserID Varchar (30),
@Password Varchar (30)
As

SET NOCOUNT ON

DECLARE @UserLevel varchar (9)
--declare @ValidCodeReg varchar (10)
DECLARE @ValidCodeLogin varchar (10)
DECLARE @LoginTime datetime
DECLARE @userExist int
DECLARE @PwdLength int

Begin
Select @UserLevel = "0"
Select @PwdLength =4
if (datalength (@Password) < @PwdLength)
Begin
Select-4 as Returnid
Return-4--The password length is not enough
End

--exec proc_getvalidcode_internal, @ValidCodeReg output--Obtain user registration checksum code
exec proc_getvalidcode_internal, @ValidCodeLogin output--Get user Login Check code
exec proc_isuserexist_internal @UserID, @userExist output-get user presence Mark
Select @LoginTime =getdate ()
Print @userExist
If @userExist =0
Begin
Select-1 as ResultId
Return-1--User already exists
End

--Insert User profile table
INSERT INTO Userbaseinfo
(Userid,password,userlevel,validcodelogin,logintime)
Values (@UserID, @Password, @UserLevel, @ValidCodeLogin, @LoginTime)

--Insert User Details table
INSERT INTO Userdetailinfo
(Userid,password) Values (@UserID, @Password)


--Obtain user registration check code, login check code
Select 0 as ResultId
Select Validcodelogin from Userbaseinfo where userid= @UserID

return 0
End


Go
SET QUOTED_IDENTIFIER OFF
Go
SET ANSI_NULLS on
Go

SET QUOTED_IDENTIFIER OFF
Go
SET ANSI_NULLS on
Go


Create proc Dbo.proc_isuserexist_internal
--Determine if the user name exists
@UserID Varchar (30),
@existFlag int Output
As

SET NOCOUNT ON
Begin

If not EXISTS (SELECT * from Userbaseinfo where userid= @UserID)
Begin
Select @existFlag =-1
Return
End
Select @existFlag =0
Return
End

Go
SET QUOTED_IDENTIFIER OFF
Go
SET ANSI_NULLS on
Go

SET QUOTED_IDENTIFIER OFF
Go
SET ANSI_NULLS on
Go


Create proc Dbo.proc_isuservalidbycode_internal
--user identity verification (based on login check code)
@ValidCodeLogin varchar (10),
@validFlag int Output
As

SET NOCOUNT ON

DECLARE @LoginTime datetime
DECLARE @curTime datetime
DECLARE @diffTime datetime

Begin
If not EXISTS (SELECT * from Userbaseinfo where validcodelogin= @ValidCodeLogin)
Begin
Select @validFlag =-1-User not logged in
Return
End

Select @LoginTime = (select Logintime from Userbaseinfo where validcodelogin= @ValidCodeLogin)
Select @curTime =getdate ()
Select @diffTime =datediff (hh, @LoginTime, @curTime)
If @diffTime >=10
Begin
Select @validFlag =-2-user timeout
Return
End

Select @LoginTime =getdate ()--Obtaining the user's last logon time
Update Userbaseinfo set logintime= @LoginTime where validcodelogin= @ValidCodeLogin

Select @validFlag =0
Return
End

Go
SET QUOTED_IDENTIFIER OFF
Go
SET ANSI_NULLS on
Go